Cara menggunakan mysql information

Database adalah kumpulan data yang terstruktur. Metadata adalah data about data. Menggunakan metode berikut, MySQL menyediakan akses ke metadata untuk database, tabel, dan objek lain yang dikelola oleh server database:

Table of Contents

Basis Data INFORMATION_SCHEMA
Tabel INFORMATION_SCHEMA
Informasi Tabel
Hak istimewa
Dukungan Set Karakter
Batasan dan Indeks
Pengaturan dan Status Server
Rutinitas dan Informasi Terkait
Menggunakan SELECT dengan INFORMATION_SCHEMA
INFORMATION_SCHEMA: Contoh
Membuat Perintah Shell dengan Tabel INFORMATION_SCHEMA
Membuat Pernyataan SQL dengan Tabel INFORMATION_SCHEMA
Pernyataan SHOW yang Didukung MySQL
TAMPILKAN Pernyataan: Contoh
Contoh Pernyataan TAMPILKAN Tambahan
JELASKAN Pernyataan
MENGGAMBARKAN
MENJELASKAN
klien mysqlshow
mysqlshow: Contoh

1. INFORMATION_SCHEMA: Server MySQL berisi kamus data yang diimplementasikan sebagai database (skema) bernama INFORMATION_SCHEMAyang mencakup sejumlah objek yang tampak seperti tabel.

2. SHOW statements: Sintaks eksklusif untuk mendapatkan data tentang statistik server, skema, dan objek skema:

SHOW DATABASES dan SHOW TABLES: Mengembalikan daftar nama database dan tabel.
SHOW COLUMNS: Menghasilkan definisi kolom dalam tabel.
Itu SELECT hak istimewa diperlukan untuk menggunakan pernyataan SHOW.

3. DESCRIBE: Pintasan pernyataan SQL yang dapat Anda gunakan untuk memeriksa struktur tabel dan properti kolom.

4. mysqlshow: Program klien yang dapat Anda gunakan sebagai ujung depan baris perintah untuk beberapa pernyataan SHOW. Argumen yang Anda tetapkan menentukan informasi yang akan ditampilkan, dan kemudian program mengeluarkan pernyataan SHOW yang sesuai dan menampilkan hasil pernyataan tersebut.

Basis Data INFORMATION_SCHEMA
Tabel INFORMATION_SCHEMA
- Informasi Tabel
- Partisi
- Hak istimewa
- Dukungan Set Karakter
- Batasan dan Indeks
- Pengaturan dan Status Server
- Rutinitas dan Informasi Terkait
- InnoDB
Menggunakan SELECT dengan INFORMATION_SCHEMA
- INFORMATION_SCHEMA: Contoh
Membuat Perintah Shell dengan Tabel INFORMATION_SCHEMA
Membuat Pernyataan SQL dengan Tabel INFORMATION_SCHEMA
Pernyataan SHOW yang Didukung MySQL
TAMPILKAN Pernyataan: Contoh
- Contoh Pernyataan TAMPILKAN Tambahan
JELASKAN Pernyataan
- MENGGAMBARKAN
- MENJELASKAN
klien mysqlshow
mysqlshow: Contoh

Basis Data INFORMATION_SCHEMA

Itu INFORMATION_SCHEMA database berfungsi sebagai pusat repositori untuk metadata database. Ini adalah sebuah virtual database dalam arti bahwa itu tidak disimpan di mana pun di disk; namun, ini berisi tabel seperti database lainnya, dan konten tabelnya dapat diakses menggunakan SELECT seperti tabel lainnya. Selanjutnya, pernyataan SELECT dapat digunakan untuk mendapatkan INFORMATION_SCHEMAtables

Karena merupakan database virtual, MySQL membangun hasil query dengan membaca tabel dan metadata objek lainnya. Saat Anda menjalankan kueri yang membaca informasi tentang banyak tabel, MySQL harus menjalankan banyak operasi tingkat disk, yang dapat memakan banyak waktu.

Tabel INFORMATION_SCHEMA

Untuk membuat daftar semua tabel di database INFORMATION_SCHEMA, gunakan kueri di bawah ini:

mysql> SELECT   TABLE_NAME
    -> FROM     INFORMATION_SCHEMA.TABLES
    -> WHERE    TABLE_SCHEMA = 'information_schema'
    -> ORDER BY TABLE_NAME; 
 +---------------------------------------+ 
 | TABLE_NAME                            | 
 +---------------------------------------+ 
 | CHARACTER_SETS                        | 
 | COLLATIONS                            | 
 | COLLATION_CHARACTER_SET_APPLICABILITY | 
 | COLUMNS                               | 
 | COLUMN_PRIVILEGES                     | 
 ... 
 | USER_PRIVILEGES                       | 
 | VIEWS                                 | 
 +---------------------------------------+

INFORMATION_SCHEMAtables berisi jenis informasi berikut:

Informasi Tabel

COLUMNS:Kolom dalam tabel dan tampilan
ENGINES:Mesin penyimpanan
SCHEMATA:Database
TABLES:Tabel dalam database
VIEWS:Tampilan di database

Partisi

PARTITIONS: Partisi tabel
FILES: File tempat tabel data disk NDB MySQL disimpan

Hak istimewa

COLUMN_PRIVILEGES: Hak istimewa kolom dipegang oleh akun pengguna MySQL
SCHEMA_PRIVILEGES: Hak istimewa basis data dipegang oleh akun pengguna MySQL
TABLE_PRIVILEGES: Hak istimewa tabel dipegang oleh akun pengguna MySQL
USER_PRIVILEGES: Hak istimewa global dipegang oleh akun pengguna MySQL

Dukungan Set Karakter

CHARACTER_SETS: Set karakter yang tersedia
COLLATIONS: Koleksi untuk setiap set karakter
COLLATION_CHARACTER_SET_APPLICABILITY: Koleksi yang berlaku untuk set karakter tertentu

Batasan dan Indeks

KEY_COLUMN_USAGE: Batasan pada kolom kunci
REFERENTIAL_CONSTRAINTS: Kunci asing
STATISTICS: Indeks tabel
TABLE_CONSTRAINTS: Batasan pada tabel

Pengaturan dan Status Server

KEY_COLUMN_USAGE: Kendala
GLOBAL_STATUS: Nilai status untuk semua koneksi ke MySQL
GLOBAL_VARIABLES: Nilai yang digunakan untuk koneksi baru ke MySQL
PLUGINS: Plugin server
ROCESSLIST: Menunjukkan utas mana yang sedang berjalan
SESSION_STATUS: Nilai status untuk koneksi saat ini ke MySQL
SESSION_VARIABLES: Nilai yang berlaku untuk koneksi saat ini ke MySQL

Rutinitas dan Informasi Terkait

EVENTS: Acara terjadwal
ROUTINES: Prosedur dan fungsi tersimpan
TRIGGERS: Pemicu dalam database
PARAMETERS: Prosedur tersimpan dan parameter fungsi, dan fungsi tersimpan

InnoDB

INNODB_CMPand INNODB_CMP_RESET: Status pada operasi yang terkait dengan tabel InnoDB terkompresi
INNODB_CMPMEMand INNODB_CMPMEM_RESET: Status pada halaman terkompresi dalam kumpulan buffer InnoDB
INNODB_LOCKS: Setiap kunci yang diminta dan ditahan oleh transaksi InnoDB
INNODB_LOCK_WAITS: Satu atau lebih kunci baris untuk setiap transaksi InnoDB yang diblokir
INNODB_TRX: Setiap transaksi yang sedang dijalankan di dalam InnoDB
TABLESPACES: tablespace aktif

Menggunakan SELECT dengan INFORMATION_SCHEMA

Saat Anda mengambil metadata dari tabel INFORMATION_SCHEMA dengan menggunakan pernyataan SELECT, Anda dapat menggunakan salah satu fitur SELECT yang biasa. Anda dapat mengambil hasil kueri INFORMATION_SCHEMA ke tabel lain dengan menggunakan pernyataan CREATE TABLE…SELECT atau pernyataan INSERT…SELECT. Ini memungkinkan Anda untuk menyimpan hasil dan menggunakannya nanti dalam pernyataan lain.

INFORMATION_SCHEMA: Contoh

mysql> SELECT TABLE_NAME, ENGINE 
    -> FROM INFORMATION_SCHEMA.TABLES 
    -> WHERE  TABLE_SCHEMA = 'world_innodb';
	
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
    -> FROM   INFORMATION_SCHEMA.COLUMNS 
    -> WHERE  DATA_TYPE = 'set';
	
mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME 
    -> FROM   INFORMATION_SCHEMA.COLLATIONS 
    -> WHERE  IS_DEFAULT = 'Yes';

mysql> SELECT TABLE_SCHEMA, COUNT(*) 
    -> FROM   INFORMATION_SCHEMA.TABLES 
    -> GROUP BY TABLE_SCHEMA;
	
mysql> DELETE FROM INFORMATION_SCHEMA.VIEWS; 
ERROR 1044 (42000): Access denied for user 
'root'@'localhost' to database 'information_schema'

Contoh di atas, menunjukkan bagaimana memanfaatkan berbagai fitur SELECT untuk menarik informasi dengan cara yang berbeda dari tabel INFORMATION_SCHEMA:

Menampilkan mesin penyimpanan yang digunakan untuk tabel dalam database tertentu
Menemukan semua tabel yang berisi kolom SET
Menampilkan susunan default untuk setiap set karakter
Menampilkan jumlah tabel di setiap database
INFORMATION_SCHEMAtables adalah read-only dan tidak dapat dimodifikasi dengan pernyataan seperti INSERT, DELETE, atau UPDATE. Server menghasilkan kesalahan jika Anda menjalankan jenis pernyataan ini dalam upaya untuk mengubah data di INFORMATION_SCHEMAtables

Membuat Perintah Shell dengan Tabel INFORMATION_SCHEMA

Kita dapat menggunakan tabel INFORMATION_SCHEMA untuk mendapatkan informasi tentang membuat perintah shell. Menggunakan fungsi CONCAT, Anda dapat menggabungkan konten string untuk membuat skrip shell yang dapat dieksekusi dari baris perintah. Seperti yang ditunjukkan pada contoh di bawah ini, pernyataan SQL menghasilkan output yang membuang hanya tabel-tabel tersebut dari world_innodbdatabase yang dimulai dengan kata Country.

mysql> SELECT CONCAT("mysqldump -uroot -p ", 
    ->   TABLE_SCHEMA," ", TABLE_NAME, " >> ", 
	->   TABLE_SCHEMA,".bak.sql") 
	-> FROM TABLES WHERE TABLE_NAME LIKE 'Country%';

– Menghasilkan perintah shell berikut:

shell> mysqldump -uroot -p world_innodb Country 
       >> world_innodb.bak.sql 
shell> mysqldump -uroot -p world_innodb 
       CountryLanguage >> world_innodb.bak.sql

Langkah selanjutnya adalah menyimpan output ini dalam file batch yang dapat dieksekusi dari baris perintah shell. Ini dilakukan dengan menambahkan klausa INTO OUTFILE:

mysql> SELECT CONCAT("mysqldump -uroot -p ", 
    -> TABLE_SCHEMA, " ",TABLE_NAME, " >> ",TABLE_SCHEMA,".sql") 
	-> FROM TABLES WHERE TABLE_NAME LIKE 'Country%' 
	-> INTO OUTFILE '/Country_Dump.sh';

File ini kemudian dapat dieksekusi dari baris perintah, yang menjalankan dua perintah mysqldump yang ditunjukkan pada slide:

shell> tmpCountry_Dump.sh 
shell> tmpmysqldump -uroot -poracle world_innodb Country >> world_innodb.sql 
shell> tmpmysqldump -uroot -poracle world_innodb Country_Language >> world_innodb.sql

Membuat Pernyataan SQL dengan Tabel INFORMATION_SCHEMA

Gunakan perintah mysql untuk membuat pernyataan SQL. Menggunakan -e untuk memasukkan pernyataan SELECT/CONCAT:

shell> mysql -uroot -p --silent --skip-column-names -e 
"SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.', 
TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.', 
TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'world_innodb';"

Permintaan di atas menghasilkan pernyataan berikut yang dikirim ke output standar:

CREATE TABLE world_innodb.City_backup LIKE 
        world_innodb.City; 
CREATE TABLE world_innodb.Country_backup LIKE 
        world_innodb.Country_backup; 
CREATE TABLE world_innodb.CountryLanguage_backup LIKE 
        world_innodb.CountryLanguage_backup;

Tabel INFORMATION_SCHEMA membuat pernyataan SQL yang dapat Anda jalankan dari baris perintah. Contoh di slide menggunakan perintah mysql untuk mengeksekusi pernyataan yang membuat salinan persis dari semua tabel di world_innodbdatabase. Perintah ini membuat keluaran SQL yang, jika dijalankan, akan membuat tiga tabel cadangan berdasarkan tabel di world_innodbdatabase.

NOTE: Perintah –silent menghapus judul kolom dari output, dan perintah –skip-column-names menghapus pemformatan di sekitar output (pemformatan yang membuat output terlihat seperti tabel). Kedua perintah ini digunakan untuk memastikan bahwa perintah itu sendiri ditafsirkan dengan benar tanpa masalah dengan pemformatan eksternal atau baris header yang mengganggu eksekusi

Menambahkan simbol pipa ( | ) diikuti dengan eksekusi perintah mysql mengirimkan pernyataan SQL ini ke server MySQL untuk dieksekusi:

shell> mysql -uroot -p --silent --skip-column-names -e 
"SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.', 
TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.', 
TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'world_innodb';" | mysql -uroot -poracle

Pernyataan SHOW yang Didukung MySQL

Selain tabel INFORMATION_SCHEMA, MySQL juga mendukung pernyataan SHOW dan DESCRIBE sebagai cara alternatif untuk mengakses metadata. Itu SHOW dan DESCRIBE sintaks tidak sefleksibel menggunakan INFORMATION_SCHEMAqueries, tetapi untuk banyak tujuan sintaks SHOW dan DESCRIBE sudah cukup. Dalam kasus tersebut, seringkali lebih cepat dan lebih mudah bagi Anda untuk menggunakan sintaks khusus MySQL ini.

Pernyataan SHOW dapat digunakan dalam berbagai bentuk, sebagai berikut:

SHOW DATABASES: Mencantumkan nama-nama database yang tersedia
SHOW TABLES: Daftar tabel dalam database default
SHOW TABLES FROM [database_name]: Daftar tabel dalam database yang ditentukan
SHOW COLUMNS FROM [table_name]: Menampilkan struktur kolom untuk tabel
SHOW INDEX FROM [table_name]: Menampilkan informasi tentang indeks dan kolom indeks pada tabel
SHOW CHARACTER SET: Menampilkan set karakter yang tersedia bersama dengan susunan defaultnya
SHOW COLLATION: Menampilkan susunan untuk setiap set karakter

TAMPILKAN Pernyataan: Contoh

Beberapa pernyataan SHOW yang umum digunakan:

mysql> SHOW DATABASES; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| test               | 
| world_innodb       | 
+--------------------+

MySQL> SHOW TABLES; 
MySQL> SHOW TABLES FROM mysql; 
MySQL> SHOW TABLES FROM INFORMATION_SCHEMA; 
MySQL> SHOW COLUMNS FROM CountryLanguage; 
MySQL> SHOW FULL COLUMNS FROM CountryLanguageG

Contoh Pernyataan TAMPILKAN Tambahan

1. TAMPILKAN dengan LIKE dan MANA:

MySQL> SHOW DATABASES LIKE 'm%'; 
MySQL> SHOW COLUMNS FROM Country 
   -> WHERE `Default` IS NULL;

2. Pernyataan SHOW lainnya:

MySQL> SHOW INDEX FROM CityG 
MySQL> SHOW CHARACTER SET; 
MySQL> SHOW COLLATION;

JELASKAN Pernyataan

MENGGAMBARKAN

DESCRIBE dapat disingkat DESC, sebagai berikut;

mysql> DESCRIBE table_name; 
and 
mysql> DESC table_name;

Contoh di bawah ini menunjukkan informasi tabel INFORMATION_SCHEMA

MySQL> DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS; 
+----------------------+-------------+------+-----+---------+-------+ 
| Field                | Type        | Null | Key | Default | Extra | 
+----------------------+-------------+------+-----+---------+-------+ 
| CHARACTER_SET_NAME   | varchar(64) | NO   |     |         |       | 
| DEFAULT_COLLATE_NAME | varchar(64) | NO   |     |         |       | 
| DESCRIPTION          | varchar(60) | NO   |     |         |       | 
| MAXLEN               | bigint(3)   | NO   |     | 0       |       | 
+----------------------+-------------+------+-----+---------+-------+

Berikut ini setara dengan contoh DESCRIBE/DESC di atas:

mysql> SHOW COLUMNS FROM table_name;

Namun, sedangkan SHOW COLUMNS mendukung klausa LIKE dan WHERE opsional, DESCRIBE tidak.

MENJELASKAN

EXPLAIN setara dengan DESCRIBE ketika diberi nama tabel sebagai parameternya:

mysql> EXPLAIN table_name;

klien mysqlshow

Klien mysqlshow menyediakan antarmuka baris perintah ke berbagai bentuk pernyataan SHOW yang mencantumkan nama database, tabel dalam database, atau informasi tentang kolom tabel atau indeks.

Sintaks umum:

shell> mysqlshow [options] [db_name [table_name [column_name]]]

Bagian opsi dari klien mysqlshow dapat menyertakan salah satu opsi parameter koneksi standar, seperti –host atau –user. Anda harus menyediakan opsi jika parameter koneksi default tidak sesuai. mysqlshowalso menerima opsi yang khusus untuk operasinya sendiri.

Aktifkan mysqlshow dengan opsi –help untuk melihat daftar lengkap opsinya. Tindakan yang dilakukan oleh mysqlshow tergantung pada jumlah argumen non-opsi yang disediakan.

mysqlshow: Contoh

Anda dapat menampilkan informasi untuk semua database, atau untuk database, tabel, dan/atau kolom tertentu menggunakan kueri mysqlshow. Sebagai contoh:

shell> mysqlshow -u -p 
+--------------------+ 
|     Databases      | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| test               | 
| world_innodb       | 
+--------------------+

shell> mysqlshow world_innodb 
shell> mysqlshow world_innodb City 
shell> mysqlshow world_innodb City CountryCode 
shell> mysqlshow "w%"

Contoh di atas menunjukkan beberapa penggunaan klien mysqlshow:

Tanpa argumen, mysqlshow menampilkan hasil yang mirip dengan SHOW DATABASES.
Dengan satu argumen, mysqlshow menafsirkannya sebagai nama database dan menampilkan hasil yang mirip dengan SHOW TABLES untuk database.
Dengan dua argumen, mysqlshow menafsirkannya sebagai database dan nama tabel dan menampilkan hasil yang mirip dengan SHOW FULL COLUMNS untuk tabel.
Dengan tiga argumen, outputnya sama dengan dua argumen kecuali bahwa mysqlshow mengambil argumen ketiga sebagai nama kolom dan menampilkan SHOW FULL COLUMNSoutput hanya untuk kolom itu.
Jika argumen terakhir pada baris perintah berisi karakter khusus, mysqlshow menginterpretasikan argumen sebagai pola dan hanya menampilkan nama yang cocok dengan pola. Karakter khusus adalah % atau * untuk mencocokkan urutan karakter apa pun, dan _ atau ? untuk mencocokkan setiap karakter tunggal. Perintah contoh ini hanya menampilkan database dengan nama yang dimulai dengan w.

NOTE Contoh-contoh ini memerlukan penggunaan parameter pengguna dan sandi sebagai bagian dari eksekusi perintah.

Bagaimana MySQL menggunakan index?

Syntax Cara Membuat Index MySQL Untuk membuat index dapat dibuat dengan menggunakan perintah: CREATE INDEX nama_index ON nama_tabel(nama_kolom); kita juga dapat melakukan pemberian index untuk lebih dari satu kolom (multiple): CREATE INDEX nama_index ON nama_tabel(nama_kolom1,nama_kolom2,nama_kolom3,….);