ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > MySQL DML语句

MySQL DML语句

原创 MySQL 作者：starive 时间：2014-08-29 09:14:47 0 删除 编辑

MySQL  DML语句

《深入浅出MySQL：数据库开发、优化与管理维护(第二版)》
P34

插入记录


  1. insert into tablename(field1, field2, ... , fieldn) values(value1, value2, value3, ... , valuen);

  3. mysql> insert into emp(ename,hiredate,sal,deptno) values('zzx1','2000-01-01','2000',1);
  4. Query OK, 1 row affected (0.03 sec)


不指名字段：


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



缺省字段名插入：

  1. insert into emp(ename,sal) values(\'dony\',1000);

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

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

一次性插入多条记录：


  1. insert into tablename (field1, field2, ... , fieldn)
  2. values
  3. (record1_value1, record1_values2, ... , record1_valuesn),
  4. (record2_value1, record2_values2, ... , record2_valuesn),
  5. ...
  6. (recordn_value1, recordn_values2, ... , recordn_valuesn);

  8. insert into emp(ename,hiredate,sal,deptno) values('gao','2000-09-09',800.00,1),('lili','2000-09-09',300.00,1);
  1. insert into dept values(1,'tech'),(2,'sale'),(5,\'fin\'),(5,\'dept5\'),(6,\'dept6\');

更新记录

  1. update tablename set field1=value1, field2 =value2, ......, fieldn =valuen [where condition]
  1. mysql> update emp set sal=4000.00 where ename='lisa';
  2. Query OK, 1 row affected (0.05 sec)
  3. Rows matched: 1 Changed: 1 Warnings: 0
  1. update emp a, dept b set a.sal=a.sal*b.deptno, b.deptname=a.ename where a.deptno=b.deptno;


删除数据

  1. delete from tablename [where condition]
  1. mysql> delete from emp where ename='dony';
  2. Query OK, 1 row affected (0.04 sec)

一次删除多个表的数据
  1. delete t1,t2, ... , tn from t1,t2, ... , tn [ where condition]

查询记录

查询所有字段

  1. select * from tablename [where condition]
  1. select ename, hiredate, sal, deptno from emp;
上面两种等价。


查询不重复的记录： distinct


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

  12. mysql> select distinct deptno from emp;
  13. +--------+
  14. | deptno |
  15. +--------+
  16. | 1 |
  17. | 2 |
  18. +--------+
  19. 2 rows in set (0.00 sec)

条件查询

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

排序和限制

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

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

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

显示部分结果

select ...[LIMIT offset_start, row_count]

offset_start表示记录的起始偏移量（默认为 0 ），row_count表示显示的行数。


Eg: 按sal排序后显示前3条记录

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



显示emp表中按照sal排序后从第二条记录开始的3条记录：

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



聚合操作


  1. select [field1, field2, ... , fieldn] fun_name
  2. from tablename
  3. [where condition]
  4. [group by field1, field2, ... , fieldn [with rollup ] ]
  5. [Having condition]
fun_name表示聚合操作： sum， count， max， min等等
group by 表示要进行分类聚合的字段
having 关键字表示对分类后的结果再进行条件的过滤。
having 和where的区别是： having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤。


统计公司的总人数


  1. mysql> select count(1) from emp;
  2. +----------+
  3. | count(1) |
  4. +----------+
  5. | 4 |
  6. +----------+
  7. 1 row in set (0.02 sec)



在上面的基础上，统计各个部门的人数


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


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

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



统计人数大于1人的部门：

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



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

  1. mysql> select sum(sal),max(sal),min(sal) from emp;
  2. +----------+----------+----------+
  3. | sum(sal) | max(sal) | min(sal) |
  4. +----------+----------+----------+
  5. | 8300.00 | 3000.00 | 300.00 |
  6. +----------+----------+----------+
  7. 1 row in set (0.00 sec)



表连接

分为内连接和外连接，它们之间最主要的区别是：内连接仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录。我们最常用的的是内连接。

  1. mysql> select *from emp;
  2. +-------+------------+---------+--------+
  3. | ename | hiredate | sal | deptno |
  4. +-------+------------+---------+--------+
  5. | zzx1 | 2000-01-01 | 2000.00 | 1 |
  6. | gao | 2000-09-09 | 3000.00 | 1 |
  7. | lili | 2000-09-09 | 300.00 | 1 |
  8. | lisa | 2003-02-01 | 3000.00 | 2 |
  9. | dony | 2005-09-09 | 2000.00 | 4 |
  10. +-------+------------+---------+--------+
  11. 5 rows in set (0.00 sec)


  14. mysql> select * from dept;
  15. +--------+----------+
  16. | deptno | deptname |
  17. +--------+----------+
  18. | 1 | zzx1 |
  19. | 5 | fin |
  20. | 5 | dept5 |
  21. | 6 | dept6 |
  22. | 2 | sale |
  23. +--------+----------+
  24. 5 rows in set (0.01 sec)


  27. mysql> select ename,deptname from emp,dept where emp.deptno=dept.deptno;
  28. +-------+----------+
  29. | ename | deptname |
  30. +-------+----------+
  31. | zzx1 | zzx1 |
  32. | gao | zzx1 |
  33. | lili | zzx1 |
  34. | lisa | sale |
  35. +-------+----------+
  36. 4 rows in set (0.00 sec)



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

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

左连接：


  1. mysql> select ename, deptname from emp left join dept on emp.deptno=dept.deptno;
  2. +-------+----------+
  3. | ename | deptname |
  4. +-------+----------+
  5. | zzx1 | zzx1 |
  6. | gao | zzx1 |
  7. | lili | zzx1 |
  8. | lisa | sale |
  9. | dony | NULL |
  10. +-------+----------+
  11. 5 rows in set (0.00 sec)



右连接：


  1. mysql> select ename, deptname from emp right join dept on dept.deptno=emp.deptno;
  2. +-------+----------+
  3. | ename | deptname |
  4. +-------+----------+
  5. | zzx1 | zzx1 |
  6. | gao | zzx1 |
  7. | lili | zzx1 |
  8. | NULL | fin |
  9. | NULL | dept5 |
  10. | NULL | dept6 |
  11. | lisa | sale |
  12. +-------+----------+
  13. 7 rows in set (0.00 sec)


子查询

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


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



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

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




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

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



转为表连接后：

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




记录联合

将两个表的数据按照一定的查询条件查询出来后，将结果合并在一起显示出来，语法如下：

  1. select * from t1
  2. union | union all
  3. select * from t2
  4. ...
  5. union | union all
  6. select * from tn


union和union all的主要区别是union all是把结果直接合并在一起，而union是将union all后的结果进行一次distinct，去除重复记录后的结果。


  1. mysql> select deptno from emp
  2.     -> union all
  3.     -> select deptno from dept;
  4. +--------+
  5. | deptno |
  6. +--------+
  7. | 1 |
  8. | 1 |
  9. | 1 |
  10. | 2 |
  11. | 4 |
  12. | 1 |
  13. | 5 |
  14. | 5 |
  15. | 6 |
  16. | 2 |
  17. +--------+
  18. 10 rows in set (0.00 sec)


  21. mysql> select deptno from emp
  22.     -> union
  23.     -> select deptno from dept;
  24. +--------+
  25. | deptno |
  26. +--------+
  27. | 1 |
  28. | 2 |
  29. | 4 |
  30. | 5 |
  31. | 6 |
  32. +--------+
  33. 5 rows in set (0.00 sec)


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26435490/viewspace-1259907/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 从浏览器和记事本里 粘贴出来的SQL无法正确执行
下一篇： DCL语句
请登录后发表评论 登录
全部评论
starive

注册时间：2014-01-18

  • 博文量
    143
  • 访问量
    530075

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593