MySQL自学2018/03/30-DML语句

二、DML语句

1.插入内容

insert into emp (ename,hiredate,sal,deptno)values('zzx1','2000-01-01','2000','1');   --可以一次插入多条记录，用逗号分隔
insert into emp values('lisa','2003-02-01','3000','2');   --不指定字段名称，但values后面的顺序应与sql一样

2.查看表中值

select*from emp;  --查看表中所有值，*代表所有列
select ename,sal from emp;  --仅查看ename，sal两个字段的内容
select distinct ename from emp;  --查看ename字段去重后的内容，ename可替换
select * from emp where deptno = '1';  --条件查询，运算符：<,>,<=,>=,!=,or,and等，
select * from emp where deptno = '1' and sal > '3000';  --多条件运算
select * from emp where deptno = '1' order by sal desc;  --查询后排序，desc=降序，asc=升序
--limit 在其他数据库中可能不通用
select * from emp order by sal limit 3;  --limit：显示限制条件，显示前三条内容
select * from emp order by sal limit 1,3  --显示从第二条开始的前三条记录

3.更新表中内容

update emp set sal=4000 where ename='lisa';  --把Lisa的薪水改成4000，where的限制条件一定要加
update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;  --一次修改多个表中内容，更多用在根据一个表的字段动态的更新另外一个表的字段

4.删除表中内容

delete from emp where ename='dony';
delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;  --同时删除多个表的内容
--不加where会删除整个表的内容！！！

5.聚合语句

/*
select[field1,...,fieldn]fun_name FROM tableneme [WHERE where_contition][GROUP BY field1,...,fieldn] [WITH ROOLLUP] [HAVING where_contition]
 
fun_name：表示要做的聚合操作，常见的有sum，count(*),max,min
GROUP BY：表示对应分类聚合的字段
WITH ROLLUP：是否对分类聚合后的结果再进行汇总
HAVING：表示对分类后的结果在进行条件过滤
*/

select count(1) from emp;  --统计公司的总人数
select deptno,count(1) from emp group by deptno;  --统计各个部门的人数
select deptno,count(1) from emp group by deptno with rollup;  --统计各个部门人数并统计总人数
select deptno,count(1) from emp group by deptno with rollup having count(1)>1;  --统计人数大于1的部门
select sum(sal),max(sal),min(sal) from emp;  --统计薪水总额，最高和最低薪水

6.表连接

/*
内连接：仅选出两张表中互相匹配的记录
外连接：对比内连接，还会选出其他不匹配的记录
    左连接：包含所有左边表中的记录甚至是右边表中没有和它匹配的记录
    右连接：包含所有右边表中的记录甚至是左边表中没有和它匹配的记录
*/
select ename,deptname from emp,dept where emp.deptno = dept.deptno;  --查询雇员名称和对应部门，名字和部门名分别存放在emp和dept中
select ename,deptname from emp left join dept on emp.deptno = dept.deptno;  --左连接：查询雇员名称和对应部门，即使有的用户没有合法的部门名也会显示
select ename,deptname from dept right join emp on dept.deptno = emp.deptno;  --右连接：功能与上条语句相同 

7.子查询

/*
子查询关键字：in、not in、=、！=、exists、not exists等
*/
select * from emp where deptno in (select deptno from dept);  --从emp表中查询出所有部门在dept表中的记录
select * from emp where deptno = (select deptno from dept limit 1);  --‘=’时子查询记录数只能唯一
select emp.* from emp , dept where emp.deptno = dept.deptno;  --转换为表连接的写法

8.记录联合

/*
SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
...
UNION|UNION ALL
SELECT * FROM tn;
UNION ALL：把结果集直接合并在一起
UNION：将UNION ALL后的结果进行一次DISTINCT，去除重复记录后的结果。
*/
select deptno from emp
union all
select deptno from dept;  --将emp和dept表中的部门编号的集合显示出来
select deptno from emp
union
select deptno from dept;  --将emp和dept表中的部门编号的集合去重后显示出来