MySQL：DML（数据操纵语句）

MySQL：DML（数据操纵语句）

1.准备阶段

# 员工表
CREATE TABLE emp
(
    ename    VARCHAR(10) COMMENT '员工名',
    hiredate DATE COMMENT '雇用时间',
    sal      DECIMAL(10, 2) COMMENT '工资',
    deptno   INT(2) COMMENT '职位id'
);

# 部门表
CREATE TABLE dept
(
    deptno   INT(2) COMMENT '职位id',
    deptname VARCHAR(10) COMMENT '职位名'
);

2.增

# 指定字段名
INSERT INTO emp(ename, hiredate, sal, deptno)
VALUES ('张三', '2000-01-01', '2000', 1);

# 直接插入
INSERT INTO emp
VALUES ('李四', '2000-02-01', '2000', 2);

# 批量插入
INSERT INTO emp(ename, hiredate, sal, deptno)
VALUES ('张三', '2000-01-01', '2000', 1),
       ('李四', '2001-01-02', '3000', 1);

3.删

DELETE FROM emp WHERE ename='张三';

4.改

UPDATE emp
SET sal=4000
WHERE ename = '李四';

5.查

准备数据

# 指定字段名
INSERT INTO emp
VALUES ('张三', '2000-01-01', '2000', 1),
       ('李四', '2000-02-01', '2000', 2),
       ('王二', '2000-04-01', '3000', 4);

insert into dept
values (1, '客服'),
       (2, '销售'),
       (3, '人事');

5.1.按条件查询

# 按名字查找
select *
from emp
where ename = '张三'

5.2.去重查询

SELECT DISTINCT deptno FROM emp;

5.3.排序查询

SELECT * FROM emp ORDER BY sal;

5.4.多字段排序

SELECT * FROM emp ORDER BY deptno, sal DESC;

5.5.limit

# 限制数量
SELECT * FROM emp LIMIT 1;

# 跳过第1条匹配，取2个行匹配数据
SELECT * FROM emp ORDER BY sal LIMIT 1,2;

5.6.聚合

5.6.1.总人数

SELECT COUNT(*) FROM emp;

5.6.2.各部门人数

SELECT deptno, COUNT(1)
FROM emp
GROUP BY deptno;

5.6.3.统计各部门的人数和总人数

SELECT deptno, COUNT(1)
FROM emp
GROUP BY deptno
WITH ROLLUP;

5.6.4.统计人数大于0的部门

select deptno, count(1)
from emp
group by deptno
having count(1) > 0;

5.7.连接

• 内连接：选出两张表中互相匹配的记录
• 外连接：选出不匹配的记录
  • 左连接：包含所有的左边表记录，甚至是右边表中没有和它匹配的记录
  • 右连接：包含所有的右边表记录，甚至是左边表中没有和它匹配的记录

5.7.1.内连接

# 第1种
select ename, deptname
from emp,
     dept
where emp.deptno = dept.deptno;

# 第2种
SELECT ename, deptname
FROM emp
         INNER JOIN dept ON emp.deptno = dept.deptno;

5.7.2.外连接

5.7.2.1.左连接

SELECT ename, deptname
FROM emp
         LEFT JOIN dept ON emp.deptno = dept.deptno;

5.7.2.2.右连接

SELECT ename, deptname
FROM dept
         RIGHT JOIN emp ON dept.deptno = emp.deptno;

5.8.子查询

单独说明

5.9.联合

将两个表的查询出来的记录合并显示

• UNION ALL：结果合并显示，不去重
• UNION：将UNION ALL结果DISTINCT去重

5.9.1.UNION ALL

SELECT deptno
FROM emp
UNION ALL
SELECT deptno
FROM dept;

5.9.2.UNION

SELECT deptno
FROM emp
UNION
SELECT deptno
FROM dept;

6.参考

• 《深入浅出MySQL第2版》唐汉明、翟振兴、关宝军、王洪权、黄潇著