DML(数据操纵语句)
CREATE TABLE emp(ename VARCHAR(10), hiredate DATE, sal DECIMAL(10, 2), deptno INT(2));
CREATE TABLE dept(deptno INT(2), deptname VARCHAR(10));
一、插入记录
1.指定字段
INSERT INTO emp(ename, hiredate, sal, deptno) VALUES('zzx1', '2000-01-01', '2000', 1);
2.不指定字段
values后面的顺序应该和字段的排列顺序一致
INSERT INTO emp VALUES('zzx1', '2000-01-01', '2000', 1);
二、更新记录
1.单表更新
/*ename hiredate sal deptno
------ ---------- ------ --------
zzx1 2000-01-01 200.00 1*/
UPDATE emp SET sal=400 WHERE ename='zzx1';
2.多表更新
UPDATE emp a, dept b
SET a.sal=a.sal*b.deptno, b.deptname=a.ename
WHERE a.deptno = b.deptno;
三、删除记录
DELETE FROM emp WHERE ename='zzx1';
四、查询记录
1.查询
#普通查询
select * from emp;
#带条件查询
SELECT * FROM emp WHERE ename='zzx1';
2.查询指定字段
SELECT ename, sal FROM emp WHERE ename='zzx1';
3.去重查询
#查询deptno字段并去除重复
SELECT DISTINCT deptno FROM emp;
4.查询排序
#查询并将以sal排序
SELECT * FROM emp ORDER BY sal;
5.多字段排序
#对于deptno相同按sal排序
SELECT * FROM emp ORDER BY deptno, sal DESC;
6.查询数量限制
#从查询结果中取第1条返回
SELECT * FROM emp LIMIT 1;
7.指定返回结果的开始和结束索引(从0开始)
#从查询结果跳过第1条,取2条
SELECT * FROM emp ORDER BY sal LIMIT 1,2;
8.聚合
(1)统计总数
SELECT COUNT(*) FROM emp;
(2)统计各部门的总数
SELECT deptno, COUNT(1) FROM emp GROUP BY deptno;
(3)统计各部门的总数和全部总数
SELECT deptno, COUNT(1) FROM emp GROUP BY deptno WITH ROLLUP;
(4)统计人数大于1人的部门
select deptno, count(1) from emp group by deptno having count(1) > 1;
(5)统计所有员工的薪水总额、最高和最低薪水
SELECT SUM(sal), MAX(sal), MIN(sal) FROM emp;
9.表连接
- 内连接:仅选出两张表中互相匹配的记录
- 外连接:仅选出两张表中互相匹配的记录,还会包含不匹配的数据
(1)内连接
SELECT ename, deptname FROM emp, dept WHERE emp.deptno=dept.deptno;
(2)外连接
- 左连接:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
- 右连接:包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
左连接
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;
10.子查询
当进入查询的时候,需要的条件是另外一个SELECT语句的结果时候(关键字:in、not in、=、!=、exists、not exists等)
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept);
#如果查询记录为一可以使用=代替in
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept);
- MySQL4.1以前不支持子查询,需要用表连接来实现子查询的功能
- 表连接在很多情况下用于优化子查询
11.联合
将两个表的数据按照一定的查询条件查询出来后,将结果全并到一起显示出来
- UNION ALL:是把结果集直接合并在一起
- UNION:是将UNION ALL的结果进行一次DISTINCT,去除重复的结果
UNION ALL
SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept;
UNION
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;