SQL 語法檢視資料庫內容

測試環境為 Ubuntu16.04

資料庫 (DateBase) / 資料表 (Table) / 資料欄 (Column) / 資料 (Data) 要怎麼檢視這些資料呢! 可以透過 SHOW , SHOW CREATE TABLE , DESCRIBE , SELECT 來檢視.

1. 資料庫 (DateBase) / 資料表 (Table) – SHOW TABLE , SHOW CREATE TABLE
2. 資料欄 (Column) – DESCRIBE
3. 資料 (Data) – SELECT

建立一個資料庫 (DateBase).

root@ubuntu:~# mysql -u root -p
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE testdb;
Database changed

建立一個資料表 (Table).

MariaDB [testdb]> CREATE TABLE employee ( K1 INT(11) NOT NULL AUTO_INCREMENT , Name VARCHAR(20) , Dept VARCHAR(20) , jobTitle VARCHAR(20) , PRIMARY KEY (K1));
Query OK, 0 rows affected (0.10 sec)

寫入4筆資料.

MariaDB [testdb]> INSERT INTO employee(Name , Dept , jobTitle) VALUES ('Ben','Testing','Engineer') , ('Afa','Power','Engineer') , ('Boss','Testing','Manager') , ('Cars','Testing','Senior Engineer');
Query OK, 4 row affected (0.02 sec)

建立一個暫時資料表 (Temporary Table).

MariaDB [testdb]> CREATE TEMPORARY TABLE temp_employee ( K1 INT(11) NOT NULL AUTO_INCREMENT , Name VARCHAR(20) , Dept VARCHAR(20) , jobTitle VARCHAR(20) , PRIMARY KEY (K1));
Query OK, 0 rows affected (0.10 sec)

寫入4筆資料.

MariaDB [testdb]> INSERT INTO temp_employee(Name , Dept , jobTitle) VALUES ('Ben','Testing','Engineer') , ('Afa','Power','Engineer') , ('Boss','Testing','Manager') , ('Cars','Testing','Senior Engineer');
Query OK, 4 row affected (0.02 sec)

資料庫 (Datebase)
檢視剛剛建立好的資料庫 (Datebase).

MariaDB [testdb]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)

資料表 (Table)
檢視剛剛建立好的資料表 (Table).

MariaDB [testdb]> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| employee         |
+------------------+
1 row in set (0.00 sec)

或是

MariaDB [testdb]> SHOW TABLES FROM testdb; 

或是

MariaDB [testdb]> SHOW CREATE TABLE employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `K1` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) DEFAULT NULL,
  `Dept` varchar(20) DEFAULT NULL,
  `jobTitle` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`K1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.000 sec)

暫時資料表 (Temporary Table) 從 SHOW TABLES 看不到.

MariaDB [testdb]> SHOW CREATE TABLE temp_employee\G
*************************** 1. row ***************************
       Table: temp_employee
Create Table: CREATE TEMPORARY TABLE `temp_employee` (
  `K1` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) DEFAULT NULL,
  `Dept` varchar(20) DEFAULT NULL,
  `jobTitle` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`K1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.000 sec)

資料欄 (column)
檢視剛剛建立好的資料欄 (column).

MariaDB [testdb]> DESCRIBE employee; 
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| K1       | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name     | varchar(20) | YES  |     | NULL    |                |
| Dept     | varchar(20) | YES  |     | NULL    |                |
| jobTitle | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)

或是

MariaDB [testdb]> DESCRIBE testdb.employee; 

資料 (Data)
檢視剛剛建立好的資料 (Data).

MariaDB [testdb]> SELECT * FROM employee;
+------+---------+-----------------+
| Name | Dept    | jobTitle        |
+------+---------+-----------------+
| Ben  | Testing | Engineer        |
| Afa  | Power   | Engineer        |
| Boss | Testing | Manager         |
| Cars | Testing | Senior Engineer |
+------+---------+-----------------+
4 rows in set (0.00 sec)

或是

MariaDB [testdb]> SELECT * FROM testdb.employee;

MariaDB [testdb]> SELECT * FROM employee WHERE jobTitle='Engineer';
+------+---------+----------+
| Name | Dept    | jobTitle |
+------+---------+----------+
| Ben  | Testing | Engineer |
| Afa  | Power   | Engineer |
+------+---------+----------+
2 rows in set (0.01 sec)