MySQL DML語句

starive發表於2014-08-29

原文網址 : http://blog.itpub.net/26435490/viewspace-1259907/

MySql

MySQL DML語句

《深入淺出MySQL：資料庫開發、優化與管理維護(第二版)》
P34

插入記錄

insert into tablename(field1, field2, ... , fieldn) values(value1, value2, value3, ... , valuen);
mysql> insert into emp(ename,hiredate,sal,deptno) values('zzx1','2000-01-01','2000',1);
Query OK, 1 row affected (0.03 sec)

不指名欄位：

mysql> insert into emp values('lisa','2003-02-01','3000',2);
Query OK, 1 row affected (0.02 sec)

預設欄位名插入：

insert into emp(ename,sal) values(\'dony\',1000);
mysql> insert into emp(ename,sal) values(\'dony\',1000);
Query OK, 1 row affected (0.07 sec)

mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
| dony | NULL | 1000.00 | NULL |
+-------+------------+---------+--------+
3 rows in set (0.01 sec)

一次性插入多條記錄：

insert into tablename (field1, field2, ... , fieldn)
values
(record1_value1, record1_values2, ... , record1_valuesn),
(record2_value1, record2_values2, ... , record2_valuesn),
...
(recordn_value1, recordn_values2, ... , recordn_valuesn);
insert into emp(ename,hiredate,sal,deptno) values('gao','2000-09-09',800.00,1),('lili','2000-09-09',300.00,1);

insert into dept values(1,'tech'),(2,'sale'),(5,\'fin\'),(5,\'dept5\'),(6,\'dept6\');

更新記錄

update tablename set field1=value1, field2 =value2, ......, fieldn =valuen [where condition]

mysql> update emp set sal=4000.00 where ename='lisa';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

update emp a, dept b set a.sal=a.sal*b.deptno, b.deptname=a.ename where a.deptno=b.deptno;

刪除資料

delete from tablename [where condition]

mysql> delete from emp where ename='dony';
Query OK, 1 row affected (0.04 sec)

一次刪除多個表的資料

delete t1,t2, ... , tn from t1,t2, ... , tn [ where condition]

查詢記錄

查詢所有欄位

select * from tablename [where condition]

select ename, hiredate, sal, deptno from emp;

上面兩種等價。

查詢不重複的記錄： distinct

mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| gao | 2000-09-09 | 3000.00 | 1 |
| lili | 2000-09-09 | 300.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
+-------+------------+---------+--------+
4 rows in set (0.00 sec)
mysql> select distinct deptno from emp;
+--------+
| deptno |
+--------+
| 1 |
| 2 |
+--------+
2 rows in set (0.00 sec)

條件查詢

mysql> select *from emp where deptno=1 and sal<3000;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| lili | 2000-09-09 | 300.00 | 1 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)

排序和限制

select * from tablename [where condition] [order by field1 [Desc | Asc], field2 [Desc | Asc],... ,fieldn [Desc | Asc]]

預設是Asc

mysql> select * from emp order by sal;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| lili | 2000-09-09 | 300.00 | 1 |
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| gao | 2000-09-09 | 3000.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
+-------+------------+---------+--------+
4 rows in set (0.01 sec)

mysql> select * from emp order by deptno, sal desc;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| gao | 2000-09-09 | 3000.00 | 1 |
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| lili | 2000-09-09 | 300.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
+-------+------------+---------+--------+
4 rows in set (0.00 sec)

顯示部分結果

select ...[LIMIT offset_start, row_count]

offset_start表示記錄的起始偏移量（預設為 0 ），row_count表示顯示的行數。

Eg: 按sal排序後顯示前3條記錄

mysql> select * from emp order by sal limit 3;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| lili | 2000-09-09 | 300.00 | 1 |
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| gao | 2000-09-09 | 3000.00 | 1 |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)

顯示emp表中按照sal排序後從第二條記錄開始的3條記錄：

mysql> select *from emp order by sal limit 1,3;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| gao | 2000-09-09 | 3000.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)

聚合操作

select [field1, field2, ... , fieldn] fun_name
from tablename
[where condition]
[group by field1, field2, ... , fieldn [with rollup ] ]
[Having condition]

fun_name表示聚合操作： sum， count， max， min等等
group by 表示要進行分類聚合的欄位
having 關鍵字表示對分類後的結果再進行條件的過濾。
having 和where的區別是： having是對聚合後的結果進行條件的過濾，而where是在聚合前就對記錄進行過濾。

統計公司的總人數

mysql> select count(1) from emp;
+----------+
| count(1) |
+----------+
| 4 |
+----------+
1 row in set (0.02 sec)

在上面的基礎上，統計各個部門的人數

mysql> select deptno, count(1) from emp group by deptno;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 3 |
| 2 | 1 |
+--------+----------+
2 rows in set (0.00 sec)

既要統計各部門人數，又要統計總人數：

mysql> select deptno,count(1) from emp group by deptno with rollup;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 3 |
| 2 | 1 |
| NULL | 4 |
+--------+----------+
3 rows in set (0.00 sec)

統計人數大於1人的部門：

mysql> select deptno,count(1) from emp group by deptno having count(1)>1;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 3 |
+--------+----------+
1 row in set (0.00 sec)

最後統計公司所有員工的薪水總額、最高和最低薪水：

mysql> select sum(sal),max(sal),min(sal) from emp;
+----------+----------+----------+
| sum(sal) | max(sal) | min(sal) |
+----------+----------+----------+
| 8300.00 | 3000.00 | 300.00 |
+----------+----------+----------+
1 row in set (0.00 sec)

表連線

分為內連線和外連線，它們之間最主要的區別是：內連線僅選出兩張表中互相匹配的記錄，而外連線會選出其他不匹配的記錄。我們最常用的的是內連線。

mysql> select *from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| gao | 2000-09-09 | 3000.00 | 1 |
| lili | 2000-09-09 | 300.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
| dony | 2005-09-09 | 2000.00 | 4 |
+-------+------------+---------+--------+
5 rows in set (0.00 sec)
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | zzx1 |
| 5 | fin |
| 5 | dept5 |
| 6 | dept6 |
| 2 | sale |
+--------+----------+
5 rows in set (0.01 sec)
mysql> select ename,deptname from emp,dept where emp.deptno=dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| zzx1 | zzx1 |
| gao | zzx1 |
| lili | zzx1 |
| lisa | sale |
+-------+----------+
4 rows in set (0.00 sec)

外連線分為左連線和右連線：

左連線：包含所有的左邊表中的記錄甚至是右邊表中沒有和它匹配的記錄。
右連線：包含所有的右邊表中的記錄甚至是左邊表中沒有和它匹配的記錄。

左連線：

mysql> select ename, deptname from emp left join dept on emp.deptno=dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| zzx1 | zzx1 |
| gao | zzx1 |
| lili | zzx1 |
| lisa | sale |
| dony | NULL |
+-------+----------+
5 rows in set (0.00 sec)

右連線：

mysql> select ename, deptname from emp right join dept on dept.deptno=emp.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| zzx1 | zzx1 |
| gao | zzx1 |
| lili | zzx1 |
| NULL | fin |
| NULL | dept5 |
| NULL | dept6 |
| lisa | sale |
+-------+----------+
7 rows in set (0.00 sec)

子查詢

從emp表中查詢所有部門在dept表中的所有記錄：

mysql> select * from emp where deptno in(select deptno from dept);
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| gao | 2000-09-09 | 3000.00 | 1 |
| lili | 2000-09-09 | 300.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
+-------+------------+---------+--------+
4 rows in set (0.01 sec)

如果子查詢記錄數唯一，還可以用 = 代替 in :

mysql> select * from emp where deptno = (select deptno from dept);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select * from emp where deptno = (select deptno from dept limit 1);
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| gao | 2000-09-09 | 3000.00 | 1 |
| lili | 2000-09-09 | 300.00 | 1 |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)

某些情況下，子查詢可以轉化為表連線，例如：

mysql> select *from emp where deptno in(select deptno from dept);
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| gao | 2000-09-09 | 3000.00 | 1 |
| lili | 2000-09-09 | 300.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
+-------+------------+---------+--------+
4 rows in set (0.00 sec)

轉為表連線後：

mysql> select *from emp ,dept where emp.deptno=dept.deptno;
+-------+------------+---------+--------+--------+----------+
| ename | hiredate | sal | deptno | deptno | deptname |
+-------+------------+---------+--------+--------+----------+
| zzx1 | 2000-01-01 | 2000.00 | 1 | 1 | zzx1 |
| gao | 2000-09-09 | 3000.00 | 1 | 1 | zzx1 |
| lili | 2000-09-09 | 300.00 | 1 | 1 | zzx1 |
| lisa | 2003-02-01 | 3000.00 | 2 | 2 | sale |
+-------+------------+---------+--------+--------+----------+
4 rows in set (0.01 sec)

記錄聯合

將兩個表的資料按照一定的查詢條件查詢出來後，將結果合併在一起顯示出來，語法如下：

select * from t1
union | union all
select * from t2
...
union | union all
select * from tn

union和union all的主要區別是union all是把結果直接合並在一起，而union是將union all後的結果進行一次distinct，去除重複記錄後的結果。

mysql> select deptno from emp
-> union all
-> select deptno from dept;
+--------+
| deptno |
+--------+
| 1 |
| 1 |
| 1 |
| 2 |
| 4 |
| 1 |
| 5 |
| 5 |
| 6 |
| 2 |
+--------+
10 rows in set (0.00 sec)
mysql> select deptno from emp
-> union
-> select deptno from dept;
+--------+
| deptno |
+--------+
| 1 |
| 2 |
| 4 |
| 5 |
| 6 |
+--------+
5 rows in set (0.00 sec)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26435490/viewspace-1259907/，如需轉載，請註明出處，否則將追究法律責任。

入門MySQL——DML語句篇
2020-06-03
MySql
MySQL基礎之DML語句
2021-12-16
MySql
MySQL 的資料管理及 DML 語句
2020-07-27
MySql
ORACLE 資料庫查詢語句與DML語句
2019-10-16
Oracle資料庫
6.3. 基本SQL語句——6.3.2. DML
2020-02-15
SQL
mysql語句
2020-12-31
MySql
MySQL的DDL和DML操作語法
2024-07-27
MySql
【解決DML 語句包含不帶 INTO 子句的 OUTPUT 子句】
2024-08-16
KunlunDB對MySQL私有DML語法的支援
2022-04-22
MySql
mySql常用語句
2018-07-09
MySql
MySQL的語句
2018-06-25
MySql
MySQL replace語句
2021-12-21
MySql
【MySQL】MySQL語句最佳化
2024-05-28
MySql
初識mysql語句
2018-10-15
MySql
mysql查詢語句
2024-04-04
MySql
Mysql日期常用語句
2020-08-22
MySql
MySQL基礎語句
2020-10-26
MySql
Mysql小白語句整理
2019-05-10
MySql
【MySQL】常用拼接語句
2019-06-05
MySql
mysql的常用語句
2020-12-01
MySql
MySQL迴圈語句
2021-01-27
MySql
【必知必會的MySQL知識】③DML語言
2023-05-02
MySql
MySQL——優化ORDER BY語句
2018-10-31
MySql優化
MYSQL SQL語句優化
2019-02-22
MySql優化
Mysql之查詢語句
2024-03-09
MySql
Mysql中的DQL語句
2024-05-03
MySql
MySQL 三種新增語句
2019-11-03
MySql
MySQL 四種新增語句
2019-11-03
MySql
mysql handler語句之一
2019-12-01
MySql
mysql查詢語句集
2020-04-05
MySql
寫一個“特殊”的查詢構造器 – (七、DML 語句、事務)
2019-02-16
Mysql DML 新增資料
2024-12-06
MySql
MySQL -update語句流程總結
2019-03-04
MySql
MySQL檢視建表語句
2019-01-22
MySql
學習MySQL的select語句
2018-12-21
MySql
MySQL語句執行分析（一）
2018-09-26
MySql
MySQL語句執行分析（二）
2018-09-28
MySql
mysql建立外來鍵語句
2020-04-06
MySql
mysql高階查詢語句
2020-11-04
MySql

MySQL DML語句

MySQL DML語句

插入記錄

不指名欄位：

預設欄位名插入：

一次性插入多條記錄：

更新記錄

刪除資料

查詢記錄

查詢所有欄位

查詢不重複的記錄： distinct

條件查詢

排序和限制

顯示部分結果

聚合操作

統計公司的總人數

在上面的基礎上，統計各個部門的人數

既要統計各部門人數，又要統計總人數：

統計人數大於1人的部門：

表連線

外連線分為左連線和右連線：

左連線：

右連線：

子查詢

從emp表中查詢所有部門在dept表中的所有記錄：

記錄聯合

相關文章