Khi viết truy vấn cho một cơ sở dữ liệu mà bạn có thể chưa quen hoặc truy vấn thường xuyên thay đổi, bạn có thể muốn chạy kiểm tra nhanh để tìm tất cả các bảng trong một cơ sở dữ liệu cụ thể hoặc các cột trong cơ sở dữ liệu hoặc để tìm kiếm xem bảng hay cột
Tại sao điều này là một vấn đề?
Hiểu lược đồ và những bảng nào trong đó giúp viết SQL hiệu quả và giúp tránh chạy truy vấn nhiều lần chỉ để xem tên lược đồ hoặc tên cột có đúng không
Hướng dẫn này sẽ giúp giải quyết những vấn đề này
Sử dụng lược đồ thông tin
Truy vấn siêu dữ liệu trên nguồn dữ liệu là cách dễ nhất để xác định cấu trúc của bảng nếu bạn chưa hiểu về nó. Microsoft SQL Server cung cấp dạng xem lược đồ thông tin dưới dạng một trong số các phương pháp để lấy siêu dữ liệu này. Như tài liệu hỗ trợ của họ nêu rõ, “Chế độ xem lược đồ thông tin cung cấp chế độ xem nội bộ, độc lập với bảng hệ thống của siêu dữ liệu SQL Server. Chế độ xem lược đồ thông tin cho phép các ứng dụng hoạt động chính xác mặc dù các thay đổi quan trọng đã được thực hiện đối với các bảng hệ thống cơ bản. ”
Chúng tôi sẽ sử dụng một vài dạng xem trong lược đồ thông tin để chạy các truy vấn giúp xác định cấu tạo của các bảng trong nguồn dữ liệu
Để hiển thị TABLES
và COLUMNS
trong cơ sở dữ liệu hoặc tìm TABLES
và COLUMNS
Truy vấn đầu tiên này sẽ trả về tất cả các bảng trong cơ sở dữ liệu mà bạn đang truy vấn
SELECT
TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES
Truy vấn thứ hai sẽ trả về danh sách tất cả các cột và bảng trong cơ sở dữ liệu mà bạn đang truy vấn
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
Hoặc, bạn cũng có thể truy vấn chỉ COLUMNS
từ một bảng cụ thể và trả về tên cột từ bảng 'Album' cụ thể trong cơ sở dữ liệu của chúng tôi
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Album'
Với truy vấn tiếp theo này, bạn có thể tìm hiểu xem có hay không có một
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
0 trong nguồn dữ liệu phù hợp với một số loại tham số tìm kiếmIF EXISTS[
SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME = 'Album'
]
SELECT 'found' AS search_result ELSE SELECT 'not found' AS search_result;
Truy vấn sẽ trả về từ 'đã tìm thấy' nếu bảng 'Album' tồn tại trong cơ sở dữ liệu của chúng tôi
Bây giờ để đi xa hơn một chút, bạn có thể sử dụng truy vấn này để tìm hiểu xem có hay không có ____1_______1 trong nguồn dữ liệu phù hợp với một số loại tham số tìm kiếm
IF EXISTS[
SELECT
*
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME = 'Title'
]
SELECT 'found' AS search_result ELSE SELECT 'not found' AS search_result;
Sử dụng chế độ xem
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
2 trong nguồn dữ liệu của bạn có thể là một cách đáng tin cậy để xác định những gì có trong nguồn dữ liệu trong khi bạn xây dựng các truy vấn của mìnhĐể biết thêm thông tin về Chế độ xem lược đồ thông tin hệ thống Microsoft SQL Server, vui lòng đọc thêm từ tài liệu hỗ trợ của họ
Trong phiên bản 5. 0, MySQL đã triển khai cơ sở dữ liệu INFORMATION_SCHEMA, hiển thị dưới dạng cơ sở dữ liệu bình thường. Mặc dù hành vi và chức năng của nó tương tự như cơ sở dữ liệu thông thường nhưng thông tin chứa trong cơ sở dữ liệu không phải là tất cả dữ liệu thông thường
Đây là cách tốt nhất tôi có thể mô tả cơ sở dữ liệu MySQL INFORMATION_SCHEMA. Nó là một cơ sở dữ liệu chứa thông tin về các cơ sở dữ liệu khác. Nó có sẵn trong mọi phiên bản MySQL và được sử dụng để lưu trữ thông tin siêu dữ liệu về tất cả các cơ sở dữ liệu khác trong máy chủ. Nó còn được gọi là danh mục hệ thống hoặc từ điển dữ liệu
Giống như tất cả các cơ sở dữ liệu MySQL, thông tin trong cơ sở dữ liệu INFORMATION_SCHEMA được lưu trữ trong các bảng chỉ đọc. Tuy nhiên, trên thực tế, chúng là các dạng xem chứ không phải các bảng MySQL cơ sở. Theo nghĩa đó, bạn không thể thực hiện các trình kích hoạt MySQL đối với các bảng được lưu trữ trong cơ sở dữ liệu, cũng như không có bất kỳ tệp nào được liên kết với chúng
GHI CHÚ. Bạn cũng sẽ không tìm thấy thư mục có tên INFORMATION_SCHEMA
Bất chấp tất cả những điều đó, cơ sở dữ liệu INFORMATION_SCHEMA là nơi lý tưởng để truy vấn thông tin về các cơ sở dữ liệu khác được lưu trữ trên máy chủ. Hướng dẫn này nhằm mục đích cung cấp cho bạn cái nhìn tổng quan về cơ sở dữ liệu INFORMATION_SCHEMA và cung cấp cho bạn một vài ví dụ về cách sử dụng cơ sở dữ liệu
Thông tin cơ bản về INFORMATION_SCHEMA
Kể từ MySQL 5. 0 trở lên, nếu bạn thực thi cơ sở dữ liệu hiển thị, cơ sở dữ liệu information_schema đó sẽ hiển thị và có thể được sử dụng như một cơ sở dữ liệu bình thường
mysql > hiển thị cơ sở dữ liệu;
+---------------------+
| Cơ sở dữ liệu .
+---------------------+
| information_schema .
| mysql .
| performance_schema .
| sakila .
| hệ thống .
| thế giới .
+---------------------+
6 hàng trong bộ [0.00 giây ]
Người ta có thể sử dụng cơ sở dữ liệu INFORMATION_SCHEMA đơn giản bằng cách sử dụng truy vấn sử dụng MySQL như
mysql > SỬ DỤNG information_schema;
Khi ở trong cơ sở dữ liệu, bạn có thể xem các bảng được lưu trữ bằng lệnh dưới dạng
mysql > hiển thị bảng;
+--------------------------------------- +
| Bảng_in_thông tin_lược đồ .
+--------------------------------------- +
| ADMINISTRABLE_ROLE_AUTHORIZATIONS .
| ỨNG DỤNG_ROLES .
| CHARACTER_SETS .
| CHECK_CONSTRAINTS .
| COLLATION_CHARACTER_SET_ÁP DỤNG .
| BỘ SƯU TẬP .
| COLUMN_PRIVILEGES .
| COLUMN_STATISTICS .
| CỘT .
| COLUMNS_EXTENSIONS .
| ENABLED_ROLES .
| ĐỘNG CƠ .
| SỰ KIỆN .
| TỆP .
| INNODB_BUFFER_PAGE .
| INNODB_BUFFER_PAGE_LRU .
------------DỮ LIỆU BỊ CẮT----------------
Đầu ra ở trên cho thấy một số bảng được lưu trữ trong cơ sở dữ liệu information_schema. Hãy nhớ rằng chúng không phải là bảng thực tế và do đó không thể sửa đổi. Thay vào đó, chúng được lưu trữ trong bộ nhớ và bị xóa sau khi máy chủ MySQL bị tắt. Khi khởi động lại, information_schema được xây dựng lại và điền dữ liệu hiện có trên máy chủ
Chọn từ Information_schema
Để xem thông tin được lưu trữ trong cơ sở dữ liệu information_schema, bạn có thể sử dụng câu lệnh select. Ví dụ: để liệt kê thông tin trong bảng động cơ, lệnh như
mysql > CHỌN * TỪ information_schema. động cơ;
Điều này sẽ kết xuất dữ liệu được lưu trữ trong bảng như được hiển thị
Ví dụ 1 – Hiển thị các bảng lớn nhất
Ví dụ dưới đây cho thấy cách sử dụng INFORMATION_SCHEMA để hiển thị cơ sở dữ liệu lớn nhất trên máy chủ
Khi thực hiện truy vấn trên, bạn sẽ nhận được tất cả các bảng theo kích thước của mình, bắt đầu từ lớn nhất đến nhỏ nhất
Đây là một ví dụ đầu ra
Ví dụ 2 – Hiển thị đặc quyền
Sử dụng TABLE_PRIVILEGES trong cơ sở dữ liệu lược đồ thông tin, chúng ta có thể nhận được các đặc quyền trên máy chủ MySQL. Một số cột trong bảng này là
- GRANTEE – Phần này hiển thị tài khoản MySQL được cấp đặc quyền. Điều này thường ở định dạng tên người dùng @ tên máy chủ
- TABLE_CATALOG – Chứa tên của danh mục mà bảng thuộc về. Giá trị là def theo mặc định
- TABLE_SCHEMA – Tên của cơ sở dữ liệu chứa bảng
- TABLE_NAME – Tên của bảng
- IS_GRANTEE – Giá trị Boolean nếu người dùng có đặc quyền GRANT. Thường chứa giá trị CÓ hoặc KHÔNG
Sử dụng thông tin trên, chúng ta có thể xem các đặc quyền trong máy chủ MySQL bằng truy vấn
CHỌN * TỪ information_schema. SCHEMA_PRIVILEGES;
Lệnh này sẽ cung cấp cho bạn một đầu ra như được hiển thị
Ví dụ 3 – Hiển thị các tiến trình đang chạy
Chúng ta có thể sử dụng bảng PROCESSLIST có sẵn trong bảng INFORMATION_SCHEMA để xem các hoạt động đang chạy trên máy chủ
Đây là một truy vấn ví dụ để hiển thị tất cả các quy trình đang chạy
CHỌN * TỪ information_schema. DANH SÁCH QUY TRÌNH;
Kết xuất tất cả các quy trình đang chạy sẽ hiển thị một đầu ra tương tự như được hiển thị
Bạn cũng có thể sử dụng lệnh
HIỂN THỊ ĐẦY ĐỦ PROCESSLIST, sẽ hiển thị thông tin tương tự như khi truy vấn bảng PROCESSLIST trong INFORMATION_SCHEMA
Ví dụ 4 – Hiển thị thông tin chỉ mục bảng
Sử dụng bảng THỐNG KÊ, chúng tôi có thể hiển thị thông tin về chỉ mục bảng. Đây là một truy vấn ví dụ
CHỌN * TỪ THÔNG TIN_SCHEMA. THỐNG KÊ WHERE table_schema = 'sakila' GIỚI HẠN 5;
Thông tin về tất cả các chỉ mục trong lược đồ sakila như hình bên dưới
Đó là một số ví dụ mà bạn có thể truy vấn từ cơ sở dữ liệu INFORMATION_SCHEMA
Phần kết luận
Cơ sở dữ liệu INFORMATION_SCHEMA là nguồn thông tin cho tất cả các cơ sở dữ liệu khác và chính máy chủ MySQL. Ngoài ra, nó cung cấp cho bạn rất nhiều tùy chọn để quản lý và điều chỉnh thông tin cần truy vấn. Nếu bạn kết hợp sức mạnh của INFORMATION_SCHEMA và sự kỳ diệu của SQL, bạn sẽ có nhiều sức mạnh nhất đối với bất kỳ người dùng cơ sở dữ liệu nào