MySQL
来源：互联网 发布：java后端工作累不累 编辑：程序博客网 时间：2024/05/29 15:10
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: 0mysql> 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: 0mysql> 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)
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)
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语句主要是DBA用来管理系统中对象权限时所用的，一般开发人员很少使用。
这里不做详细笔记，有需要的，自己可以自己查阅下资料。
阅读全文
