SQL 語法 基礎 INSERT , SELECT , UPDATE , DELETE

adminLinux, MariaDB, Mysql

Loading

之前 Database 只會新增 個別資料庫,或是一些簡單的設定 ,但是在使用 sysbench – https://benjr.tw/95251 進行 oltp 測試時,裡面的模式使用了很多的 SQL 語法,不是很了解 SQL 所以惡補一下.

關於 Linux 下的 Mysql (MariaDB) 設定請參考 https://benjr.tw/12461

與 SQL 資料語法相關的有 INSERT , SELECT , UPDATE , DELETE

先建立一個資料庫 (sbtest) , 與 tables (employee) 格式為 NName VARCHAR(20), Dept VARCHAR(20), jobTitle VARCHAR(20) 各 20 個字元.

root@ubuntu:~# mysql -u root -p
mysql> CREATE DATABASE sbtest;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE sbtest;
Database changed

mysql> CREATE TABLE employee (Name VARCHAR(20),Dept VARCHAR(20),jobTitle VARCHAR(20));
Query OK, 0 rows affected (0.10 sec)

mysql> DESCRIBE employee; 
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Name     | varchar(20) | YES  |     | NULL    |       |
| Dept     | varchar(20) | YES  |     | NULL    |       |
| jobTitle | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

  • INSERT

    新增資料的常用格式如下.

    mysql> INSERT INTO employee VALUES ('Ben','Testing','Engineer');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (Name , Dept) VALUES ('Afa','Power');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (Name , Dept , JobTitle) VALUES ('Boss','Testing','Manager');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (Name , JobTitle , Dept) VALUES ('Cars','Senior Engineer' ,'Testing');
Query OK, 1 row affected (0.00 sec)

    一次新增多筆資料.

    mysql> INSERT INTO employee VALUES ('Ben','Testing','Engineer') , ('Boss','Testing','Manager');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO employee (Name , Dept) VALUES ('Afa','Power') , ('Ben','Testing');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO employee (Name , Dept , JobTitle) VALUES ('Boss','Testing','Manager') , ('Ben','Testing','Engineer') ;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO employee (Name , JobTitle , Dept) VALUES ('Cars','Senior Engineer' ,'Testing') , ('Ben','Testing','Engineer') ;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

  • SELECT

    透過 SELECT 來檢視看看資料,可以透過 where 來做篩選.

    SELECT "欄位" FROM "表格" WHERE "欄位" [比較]

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

    mysql> SELECT * FROM employee WHERE jobTitle='Engineer';
+------+---------+----------+
| Name | Dept    | jobTitle |
+------+---------+----------+
| Ben  | Testing | Engineer |
+------+---------+----------+
2 rows in set (0.01 sec)

    where 比對字串時可以加入 like 與 % 來做字串樣本 pattern 篩選.

    mysql> SELECT * FROM employee WHERE jobTitle like '%Engineer%';
+------+---------+-----------------+
| Name | Dept    | jobTitle        |
+------+---------+-----------------+
| Ben  | Testing | Engineer        |
| Cars | Testing | Senior Engineer |
+------+---------+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM employee WHERE jobTitle like '%engineer%';
+------+---------+-----------------+
| Name | Dept    | jobTitle        |
+------+---------+-----------------+
| Ben  | Testing | Engineer        |
| Cars | Testing | Senior Engineer |
+------+---------+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM employee WHERE jobTitle like '%Engineer';
+------+---------+-----------------+
| Name | Dept    | jobTitle        |
+------+---------+-----------------+
| Ben  | Testing | Engineer        |
| Cars | Testing | Senior Engineer |
+------+---------+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM employee WHERE jobTitle like 'Engineer%';
+------+---------+----------+
| Name | Dept    | jobTitle |
+------+---------+----------+
| Ben  | Testing | Engineer |
+------+---------+----------+
1 row in set (0.00 sec)

    除了 like 外,還可以使用 not like 來反向搜尋.

    mysql> SELECT * FROM employee WHERE jobTitle NOT LIKE 'engineer';
+------+---------+-----------------+
| Name | Dept    | jobTitle        |
+------+---------+-----------------+
| Boss | Testing | Manager         |
| Cars | Testing | Senior Engineer |
+------+---------+-----------------+
2 rows in set (0.01 sec)

    SELECT 其他參數 FROM , DISTINCT , WHERE , ORDER BY , LIMIT 使用請參考 – https://benjr.tw/98988

  • UPDATE

    原本 Afa 的 jobtile 沒有輸入資料 (NULL) ,把它資料更新成為資深工程師 Senior Engineer,這時候我們可以透過 UPDATE 來更新資料(搭配 WHERE NAME 來使用)..

    mysql> UPDATE employee SET jobTitle='Senior Engineer' WHERE name='Afa';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

    如果是同時要更新多欄位,直接指定欄位與數值即可.

    mysql> UPDATE employee SET jobTitle='Manger',Dept='PM' WHERE name='Afa';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * from employee;
+------+---------+-----------------+
| Name | Dept    | jobTitle        |
+------+---------+-----------------+
| Ben  | Testing | Engineer        |
| Afa  | PM      | Manger          |
| Boss | Testing | Manager         |
| Cars | Testing | Senior Engineer |
+------+---------+-----------------+
4 rows in set (0.01 sec)

  • DELETE

    Cars 離職了,就透過 DELETE 來刪除(搭配 WHERE NAME 來使用).

    mysql> DELETE FROM employee WHERE NAME='Cars';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * from employee;
+------+---------+-----------------+
| Name | Dept    | jobTitle        |
+------+---------+-----------------+
| Ben  | Testing | Engineer        |
| Afa  | Power   | Senior Engineer |
| Boss | Testing | Manager         |
+------+---------+-----------------+
3 rows in set (0.00 sec)

剛剛在 Mysql 裡面執行 Insert , Select , Update , Delete ,我們在使用 mysql 指令時就可以指定 SQL 語法,不需要登入到 Mysql 裡面.

root@ubuntu:~# mysql -u root -p -e "SELECT * FROM sbtest.employee;"
Enter password: 
+------+---------+----------+
| Name | Dept    | jobTitle |
+------+---------+----------+
| Ben  | Testing | Engineer |
| Afa  | PM      | Manger   |
| Boss | Testing | Manager  |
+------+---------+----------+

參數 –execute=statement, -e statement
Execute the statement and quit.

密碼 (我的密碼 : 111111)可以直接接在 -p 後面,就不需要後面再次輸入.

root@ubuntu:~# mysql -u root -p111111 -e "SELECT * FROM sbtest.employee;"
+------+---------+----------+
| Name | Dept    | jobTitle |
+------+---------+----------+
| Ben  | Testing | Engineer |
| Afa  | PM      | Manger   |
| Boss | Testing | Manager  |
+------+---------+----------+

或是下面的方式.

root@ubuntu:~# echo "SELECT * FROM sbtest.employee;" | mysql -u root -p111111; 
Name	Dept	jobTitle
Ben	Testing	Engineer
Afa	PM	Manger
Boss	Testing	Manager
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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