508 瀏覽數

SQL 檢視資料庫內容

測試環境為 Ubuntu16.04

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

先建立一個資料庫範本.

root@ubuntu:~# mysql -u root -p
MariaDB [(none)]> create database sbtest;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> use sbtest;
Database changed
 
MariaDB [sbtest]> create table employee (Name char(20),Dept char(20),jobTitle char(20));
Query OK, 0 rows affected (0.10 sec)

MariaDB [sbtest]> INSERT INTO employee VALUES ('Ben','Testing','Engineer');
Query OK, 1 row affected (0.02 sec)
 
MariaDB [sbtest]> INSERT INTO employee VALUES ('Afa','Power','Engineer');
Query OK, 1 row affected (0.01 sec)
 
MariaDB [sbtest]> INSERT INTO employee VALUES ('Boss','Testing','Manager');
Query OK, 1 row affected (0.01 sec)
 
MariaDB [sbtest]> INSERT INTO employee VALUES ('Cars','Testing','Senior Engineer');
Query OK, 1 row affected (0.00 sec)
SELECT

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

MariaDB [sbtest]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sbtest             |
+--------------------+
4 rows in set (0.00 sec)

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

MariaDB [sbtest]> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| employee         |
+------------------+
1 row in set (0.00 sec)

或是

MariaDB [(none)]> show tables from sbtest; 

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

MariaDB [sbtest]> DESCRIBE employee; 
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)

或是

MariaDB [(none)]> DESCRIBE sbtest.employee;

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

MariaDB [sbtest]> 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 [(none)]> SELECT * from sbtest.employee;
MariaDB [sbtest]> SELECT * from employee where jobTitle='Engineer';
+------+---------+----------+
| Name | Dept    | jobTitle |
+------+---------+----------+
| Ben  | Testing | Engineer |
| Afa  | Power   | Engineer |
+------+---------+----------+
2 rows in set (0.01 sec)

發表迴響