MySQL - 2 - DML语句和DCL语句

DML语句

DML 操作是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）删除（delete）和查询（select），是开发人员日常使用最频繁的操作。

插入操作

insert into table_name ('字段1','字段2'....) values ('对应值1','对应值2'...);

按照表中字段插入

mysql> INSERT INTO temp_users 
    -> (u_count,u_namess,u_createdate,u_age)
    -> values
    -> (1,'cyx','2017-11-20 22:47:00',18);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from temp_users;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx      | 2017-11-20   |    18 |
+---------+----------+--------------+-------+
1 row in set (0.01 sec)

换种写法，不用写字段的

mysql> INSERT INTO temp_users values (2,'cyx2','2017-11-20',19);
Query OK, 1 row affected (0.01 sec)

mysql> select * from temp_users;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx      | 2017-11-20   |    18 |
|       2 | cyx2     | 2017-11-20   |    19 |
+---------+----------+--------------+-------+
2 rows in set (0.00 sec)

MySQL中还有一个特性，可以一次性插入多条记录：

mysql> INSERT INTO temp_users (u_count,u_namess,u_createdate,u_age) 
    -> values 
    -> (3,'cyx3','2017-11-20',20),
    -> (4,'cyx4','2017-11-20',21),
    -> (4,'cyx5','2017-11-20',22),
    -> (4,'cyx6','2017-11-20',23);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from temp_users;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx      | 2017-11-20   |    18 |
|       2 | cyx2     | 2017-11-20   |    19 |
|       3 | cyx3     | 2017-11-20   |    20 |
|       4 | cyx4     | 2017-11-20   |    21 |
|       4 | cyx5     | 2017-11-20   |    22 |
|       4 | cyx6     | 2017-11-20   |    23 |
+---------+----------+--------------+-------+
6 rows in set (0.00 sec)

更新数据

update table_name set field1=value1 , field2=value2...where ..

将u_age=22那一行u_count更新为5 

mysql> select * from temp_users;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx      | 2017-11-20   |    18 |
|       2 | cyx2     | 2017-11-20   |    19 |
|       3 | cyx3     | 2017-11-20   |    20 |
|       4 | cyx4     | 2017-11-20   |    21 |
|       4 | cyx5     | 2017-11-20   |    22 |
|       4 | cyx6     | 2017-11-20   |    23 |
+---------+----------+--------------+-------+
6 rows in set (0.00 sec)

mysql> update temp_users set u_count=5 where u_age=22;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from temp_users;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx      | 2017-11-20   |    18 |
|       2 | cyx2     | 2017-11-20   |    19 |
|       3 | cyx3     | 2017-11-20   |    20 |
|       4 | cyx4     | 2017-11-20   |    21 |
|       5 | cyx5     | 2017-11-20   |    22 |
|       4 | cyx6     | 2017-11-20   |    23 |
+---------+----------+--------------+-------+
6 rows in set (0.00 sec)

MySQL中，update命令还可以同时更新多个表中的数据：

update table_name1 t1 , table_name2 t2 set t1.field1='xx',t2.field1='xx' where ..

删除记录

delete from table_name where ...

删除u_age=23的那条数据

mysql> delete from temp_users where u_age = 23;
Query OK, 1 row affected (0.00 sec)

mysql> select * from temp_users;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx      | 2017-11-20   |    18 |
|       2 | cyx2     | 2017-11-20   |    19 |
|       3 | cyx3     | 2017-11-20   |    20 |
|       4 | cyx4     | 2017-11-20   |    21 |
|       5 | cyx5     | 2017-11-20   |    22 |
+---------+----------+--------------+-------+
5 rows in set (0.00 sec)

删除全部数据(慎用)，不加where全部数据都会删除

mysql> delete from temp_users ;
Query OK, 5 rows affected (0.00 sec)

mysql> select * from temp_users;
Empty set (0.00 sec)

查询记录

select语法很复杂，我们先看简单的，复杂的后面说。

select * from table_name;

普通查询

查询全部数据，'*'星号代表查询所有数据

mysql> select * from temp_users;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx1     | 2017-11-20   |    20 |
|       2 | cyx2     | 2017-11-20   |    21 |
|       3 | cyx3     | 2017-11-20   |    22 |
|       4 | cyx4     | 2017-11-20   |    23 |
+---------+----------+--------------+-------+
4 rows in set (0.00 sec)

也可以使用逗号分隔，查找想要的字段数据

mysql> select u_namess,u_age from temp_users;
+----------+-------+
| u_namess | u_age |
+----------+-------+
| cyx1     |    20 |
| cyx2     |    21 |
| cyx3     |    22 |
| cyx4     |    23 |
+----------+-------+
4 rows in set (0.00 sec)

去重查询

查询不重复的记录(去重)，用distinct关键字

mysql> select distinct u_count from temp_users;
+---------+
| u_count |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
+---------+
4 rows in set (0.01 sec)

或者这样

mysql> select distinct u_count ,u_namess , u_createdate , u_age from temp_users;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx1     | 2017-11-20   |    20 |
|       2 | cyx2     | 2017-11-20   |    21 |
|       3 | cyx3     | 2017-11-20   |    22 |
|       4 | cyx4     | 2017-11-20   |    23 |
+---------+----------+--------------+-------+
4 rows in set (0.02 sec)

条件查询

很多情况下，我们并不需要所有的记录，通过where关键字实现

略...

排序

根据某个字段排序：

mysql> select * from temp_users;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx1     | 2017-11-20   |    20 |
|       2 | cyx2     | 2017-11-20   |    21 |
|       3 | cyx3     | 2017-11-20   |    22 |
|       4 | cyx4     | 2017-11-20   |    23 |
|       4 | cyx4     | 2017-11-20   |    23 |
+---------+----------+--------------+-------+
5 rows in set (0.00 sec)

mysql> select * from temp_users order by u_count;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx1     | 2017-11-20   |    20 |
|       2 | cyx2     | 2017-11-20   |    21 |
|       3 | cyx3     | 2017-11-20   |    22 |
|       4 | cyx4     | 2017-11-20   |    23 |
|       4 | cyx4     | 2017-11-20   |    23 |
+---------+----------+--------------+-------+
5 rows in set (0.00 sec)

mysql> select * from temp_users order by u_count desc;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       4 | cyx4     | 2017-11-20   |    23 |
|       4 | cyx4     | 2017-11-20   |    23 |
|       3 | cyx3     | 2017-11-20   |    22 |
|       2 | cyx2     | 2017-11-20   |    21 |
|       1 | cyx1     | 2017-11-20   |    20 |
+---------+----------+--------------+-------+
5 rows in set (0.01 sec)

限制数量

mysql> select * from temp_users limit 2;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx1     | 2017-11-20   |    20 |
|       2 | cyx2     | 2017-11-20   |    21 |
+---------+----------+--------------+-------+
2 rows in set (0.00 sec)

注意观察下面的：

mysql> select * from temp_users limit 1,3;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       2 | cyx2     | 2017-11-20   |    21 |
|       3 | cyx3     | 2017-11-20   |    22 |
|       4 | cyx4     | 2017-11-20   |    23 |
+---------+----------+--------------+-------+
3 rows in set (0.01 sec)

mysql> select * from temp_users limit 0,3;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx1     | 2017-11-20   |    20 |
|       2 | cyx2     | 2017-11-20   |    21 |
|       3 | cyx3     | 2017-11-20   |    22 |
+---------+----------+--------------+-------+
3 rows in set (0.00 sec)

limit第一个参数表示起始偏移量，第二个参数表示显示的行数。

默认情况下起始偏移量为0，只需要写记录行数就可以。

聚合

官方语法：

SELECT [field1,field2,……fieldn] fun_name

FROM tablename

[WHERE where_contition]

[GROUP BY field1,field2,……fieldn

[WITH ROLLUP]]

[HAVING where_contition]

参数解释：

fun_name 表示要做的聚合操作，也就是聚合函数，常用的有 sum（求和）、count(*)（记录数）、max（最大值）、min（最小值）

GROUP BY 关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在 group by 后面。

WITH ROLLUP 是可选语法，表明是否对分类聚合后的结果进行再汇总。

HAVING 关键字表示对分类后的结果再进行条件的过滤。

注意：having 和 where 的区别在于 having 是对聚合后的结果进行条件的过滤，而 where 是在聚合前就对记录进行过滤，

如果逻辑允许，我们尽可能用 where先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用 having 进行再过滤。

统计总数：

mysql> select * from temp_users;
+---------+----------+--------------+-------+
| u_count | u_namess | u_createdate | u_age |
+---------+----------+--------------+-------+
|       1 | cyx1     | 2017-11-20   |    20 |
|       2 | cyx2     | 2017-11-20   |    21 |
|       3 | cyx3     | 2017-11-20   |    22 |
|       4 | cyx4     | 2017-11-20   |    23 |
|       4 | cyx4     | 2017-11-20   |    23 |
+---------+----------+--------------+-------+
5 rows in set (0.00 sec)

mysql> select count(*) from temp_users;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.02 sec)

统计各个年龄有多少人

mysql> select u_age, count(1) from temp_users group by u_age;
+-------+----------+
| u_age | count(1) |
+-------+----------+
|    20 |        1 |
|    21 |        1 |
|    22 |        1 |
|    23 |        2 |
+-------+----------+
4 rows in set (0.02 sec)

--------------------------------------------------------------------

mysql> select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 3000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      5 | fin      |
|      5 | dept5    |
|      6 | dept6    |
+--------+----------+
5 rows in set (0.00 sec)

统计各个部门的人数：

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

既要统计各部门人数，又要统计总人数：

mysql> select deptno ,count(1) from emp group by deptno with rollup;
+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        2 |
|      2 |        1 |
|      3 |        1 |
|   NULL |        4 |
+--------+----------+
4 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 |        2 |
+--------+----------+
1 row in set (0.00 sec)

最后统计公司所有员工的薪水总额、最高最低薪水：

mysql> select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 3000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+
4 rows in set (0.02 sec)

mysql> select sum(sal),max(sal),min(sal) from emp;
+----------+----------+----------+
| sum(sal) | max(sal) | min(sal) |
+----------+----------+----------+
| 14000.00 |  5000.00 |  2000.00 |
+----------+----------+----------+

1 row in set (0.00 sec)

表连接

当需要同时显示多个表中的字段时，就可以用表连接来实现这样的功能。

从大类上讲，表连接分为内连接和外链接，它们之间最主要的区别是

内连接仅选出两张表中互相匹配的数据。而外链接会选出其他不匹配的记录。

mysql> select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 3000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+
4 rows in set (0.01 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.01 sec)

查询所有雇员的名字和部门名称，因为雇员名称和部门分别存放在不同的表，因此需要表连接查询。

mysql> select ename , deptname from emp ,dept where emp.deptno=dept.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| zzx    | tech     |
| lisa   | sale     |
| bjguan | tech     |
| bzshen | hr       |
+--------+----------+
4 rows in set (0.01 sec)

外链接又分为左连接和右连接：

左连接：包含所有的左边表中的记录甚至是右边表中没有和他匹配的记录。

右连接：包含所有的右边表中的记录甚至是左边表中没有和他匹配的记录。

查询emp表中所有用户名和所在部门的名称：

mysql> select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 3000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select ename , deptname from emp left join dept on emp.deptno=dept.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| zzx    | tech     |
| lisa   | sale     |
| bjguan | tech     |
| bzshen | hr       |
| dony   | NULL     |
+--------+----------+

5 rows in set (0.00 sec)

上面出现了一个null，因为用户名(dony)部门4，在dept表中不存在。

右连接和左连接类似：

mysql> select ename , deptname from dept right join emp on dept.deptno=emp.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| zzx    | tech     |
| lisa   | sale     |
| bjguan | tech     |
| bzshen | hr       |
| dony   | NULL     |
+--------+----------+
5 rows in set (0.00 sec)

子查询

某些情况下，当我们查询的时候，需要的是另一个select语句的结果，这时候，就要用到子查询了。

用于子查询的关键字主要包括：in、not in、=、!=、exists、not exists等。

例如，从emp表中查询所有部门在dept表中的记录：

mysql> select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 3000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
5 rows in set (0.01 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.01 sec)

mysql> select * from emp where deptno in(select deptno from dept);
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 3000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+

4 rows in set (0.02 sec)

如果子查询记录数唯一，还可以使用=代替in：

mysql> select * from emp where deptno = (select deptno from dept limit 1);
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
+--------+------------+---------+--------+
2 rows in set (0.03 sec)

某些情况下，子查询可以转换为表连接，例如：

mysql> select * from emp where deptno in (select deptno from dept);
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 3000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+
4 rows in set (0.02 sec)

转换为表连接之后：

mysql> select emp.* from emp ,dept where emp.deptno = dept.deptno;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 3000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+
4 rows in set (0.02 sec)

记录联合

例如：我们要将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来，

这时候，就需要用union和union all关键字来实现这样的功能。

具体语法如下：

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 * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 3000.00 |      2 |
| bjguan | 2004-04-02 | 5000.00 |      1 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.03 sec)

mysql> select deptno from emp union all select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      1 |
|      3 |
|      4 |
|      1 |
|      2 |
|      3 |
+--------+
8 rows in set (0.02 sec)

我们将上面重复的数据去重处理：

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

DCL语句

DCL语句主要是DBA用来管理系统中对象权限时所用的，一般开发人员很少使用。

这里不做详细笔记，有需要的，自己可以自己查阅下资料。