SQL 語法檢視資料庫內容 (SHOW , SHOW CREATE TABLE , DESCRIBE , SELECT)

測試環境為 Ubuntu16.04

資料庫 (DateBase) / 資料表 (Table) / 資料欄 (Column) / 資料 (Data) 要怎麼檢視這些資料呢!

  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)

資料庫 (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)

資料欄 (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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料