755 瀏覽數

SQL 語法 (INSERT , SELECT , UPDATE , DELETE)

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

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

這次用了 INSERT , SELECT , UPDATE , DELETE 等 SQL 語法

先建立一個資料庫 (sbtest) , 與 tables (employee) 格式為 Name char(20), Dept char(20), jobTitle char(20)

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

mysql> use sbtest;
Database changed

mysql> create table employee (Name char(20),Dept char(20),jobTitle char(20));
Query OK, 0 rows affected (0.10 sec)

mysql> 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)


mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| employee         |
+------------------+
1 row in set (0.00 sec)
  • INSERT

    INSERT 就是新增資料,新增加了四筆資料.

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

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

    mysql> SELECT * from employee;
    +------+---------+-----------------+
    | Name | Dept    | jobTitle        |
    +------+---------+-----------------+
    | Ben  | Testing | Engineer        |
    | Afa  | Testing | Engineer        |
    | Boss | Testing | Manager         |
    | Cars | Testing | Senior Engineer |
    +------+---------+-----------------+
    4 rows in set (0.01 sec)
    
    mysql> SELECT * from employee where jobTitle='Engineer';
    +------+---------+----------+
    | Name | Dept    | jobTitle |
    +------+---------+----------+
    | Ben  | Testing | Engineer |
    | Afa  | Testing | Engineer |
    +------+---------+----------+
    2 rows in set (0.01 sec)
    
  • UPDATE

    因為 Afa 變成資深工程師 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  | Testing | Senior Engineer |
    | Boss | Testing | Manager         |
    | Cars | Testing | Senior Engineer |
    +------+---------+-----------------+
    4 rows in set (0.00 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  | Testing | Senior Engineer |
    | Boss | Testing | Manager         |
    +------+---------+-----------------+
    3 rows in set (0.00 sec)
    

發表迴響