SQL 語法 SELECT Last Row (Column)

如何找到最後一筆 (Last Row , Last Column or Latest Record) 輸入的資料.

測試環境為 CentOS 8 x86_64 (虛擬機)

先建立一個測試用資料庫 (testdb) , 與 tables (employee) 格式為 K1 int(11) – auto_increment & PRIMARY KEY , Name char(20), Dept char(20), jobTitle char(20) 各 20 個字元以及 Salary int(11).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

將資料庫字元編碼 ( Character Sets ) 設定為 utf8 (8-bit Unicode Transformation Format) 與 文字排序 ( Collations ) 設定為utf8_general_ci .

MariaDB [(none)]> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (K1 int(11) NOT NULL auto_increment, Name char(20) NOT NULL, Dept char(20) NOT NULL, JobTitle char(20) NOT NULL, Salary int(11) NOT NULL, PRIMARY KEY (K1)) ;
Query OK, 0 rows affected (0.010 sec)

MariaDB [testdb]> DESCRIBE employee; 
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| K1       | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name     | char(20) | NO   |     | NULL    |                |
| Dept     | char(20) | NO   |     | NULL    |                |
| JobTitle | char(20) | NO   |     | NULL    |                |
| Salary   | int(11)  | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
5 rows in set (0.005 sec)

透過 INSERT 新增加了四筆資料.

MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary) VALUES ('Ben','Testing','Engineer','45000') , ('Afa','Power','Engineer','48000') , ('Boss','Testing','Manager','75000') , ('Cars','Testing','Senior Engineer','42000');
Query OK, 4 rows affected (0.003 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM employee;
+----+------+---------+-----------------+--------+
| K1 | Name | Dept    | JobTitle        | Salary |
+----+------+---------+-----------------+--------+
|  1 | Ben  | Testing | Engineer        |  45000 |
|  2 | Afa  | Power   | Engineer        |  48000 |
|  3 | Boss | Testing | Manager         |  75000 |
|  4 | Cars | Testing | Senior Engineer |  42000 |
+----+------+---------+-----------------+--------+
4 rows in set (0.001 sec)

SELECT ORDER BY LIMIT

來對查詢後的資料內容加以來篩選,分類,限制.

MariaDB [testdb]> SELECT * FROM employee ORDER BY K1 DESC LIMIT 1;
+----+------+---------+-----------------+--------+
| K1 | Name | Dept    | JobTitle        | Salary |
+----+------+---------+-----------------+--------+
|  4 | Cars | Testing | Senior Engineer |  42000 |
+----+------+---------+-----------------+--------+
1 row in set (0.001 sec)

• ORDER BY 預設使用 ASC (Ascending) 從小到大,也可以設定從大到小 DESC (Descending).
• LIMIT 可以限制查詢回應的行數,使用方式有兩種 N (只需要前面 N 行的資料) 或是 N,M (只顯示從 N+1 到 M 行的資料).

子查詢 (sub-query)

子查詢 (sub-query) 為 一段 SQL 語法中內還包含一段 SELECT ( SELECT 敘述需置於 左右刮號 中).

MariaDB [testdb]> SELECT * FROM employee WHERE k1=(SELECT MAX(k1) FROM employee);
+----+------+---------+-----------------+--------+
| K1 | Name | Dept    | JobTitle        | Salary |
+----+------+---------+-----------------+--------+
|  4 | Cars | Testing | Senior Engineer |  42000 |
+----+------+---------+-----------------+--------+
1 row in set (0.003 sec)

MAX() 函數會返回數值中最大的.

LAST_INSERT_ID()

當資料欄位為 AUTO_INCREMENT 時,新增資料後才會知道該數值,透過 LAST_INSERT_ID 函數,可以得知剛剛新增資料 AUTO_INCREMENT 的 ID 數值是多少 (當有多筆資料新增時,並不會影響彼此讀取自己新增資料的 ID).

這樣不確定是否會影響到讀取到的 LAST_INSERT_ID .

MariaDB [testdb]>  SELECT * FROM employee WHERE k1=LAST_INSERT_ID();
+----+------+---------+-----------------+--------+
| K1 | Name | Dept    | JobTitle        | Salary |
+----+------+---------+-----------------+--------+
|  4 | Cars | Testing | Senior Engineer |  42000 |
+----+------+---------+-----------------+--------+
1 row in set (0.001 sec)