-----------------------------------------------------------------------
第一章 SQL语言基础
-----------------------------------------------------------------------
按照SQL92、99标准 分为4大类
1：数据操作语言（DML） SELECT 查询，INSERT 插入，UPDATE 更新 DELETE 删除
2：数据定义语言（DDL） CREATE 创建， ALTER 修改结构， DROP 删除，RENAME 重命名，TRUNCATE 删除
3：事务控制语句（TCL） COMMIT 提交，ROLLBACK 回滚，SAVEPOINT 回滚点
4：数据控制语句（DCL） GRANT 授权，REVOKE 解除授权
说明：也可以把SELECT 作为数据查询语言（DQL）
-- SQL语句的语法规范
1. SQL语句的所有表名、字段名全部小写，系统保留字、内置函数名、SQL保留字大写。
2. 连接符OR、IN、AND、以及＝、<=、>=等前后加上一个空格。
3. 对较为复杂的SQL语句、存储过程、函数加上注释，说明算法、功能。
-- 单行注释
#单行注释
/*
多行注释
多行注释
*/
4. SQL语句的缩进风格
1) 一行有多列，超过80个字符时，基于列对齐原则，采用下行缩进
2) WHERE子句书写时，每个条件占一行，语句另起一行时，以保留字或者连接符开始，连接符右对齐。
5. 多表连接时，使用表的别名来引用列。
-- 其他注意事项
1. SQL 命令大小写不敏感
2. SQL 命令可写成一行或多行
3. 一个关键字不能跨多行或缩写
4. 子句通常位于独立行，以便编辑，并易读
5. SQL语句用分号作为分隔符，系统读取到分号才会执行语
-----------------------------------------------------------------------
第二章：表管理DDL语句
-----------------------------------------------------------------------
数据定义语言（DDL） CREATE 创建，ALTER修改结构，DROP 删除 等语句
1.新建表
CREATE TABLE 表名
(
列名1 数据类型 [DEFAULT 默认值][列级约束1,列级约束2 ……],
列名2 数据类型 [DEFAULT 默认值][列级约束1,列级约束2 ……],
...,
列名n 数据类型 [DEFAULT 默认值][列级约束1,列级约束2 ……],
[CONSTRAINT 约束名称 约束类型（列名）],
[CONSTRAINT 约束名称 约束类型（列名）],
...
);
-- 表名的要求
1）必须以字母开头
2）表名可以包含数字
3）也可以包含 - $ _
4）不要使用SQL的保留字作为表名
-- 数据类型（MySQL常见数据库类型.xlsx）
1）数值类型
2）日期类型
3）字符串类型
CHAR(10) 定长字符串 zhangsan 占10个字符
VARCHAR(10) 变长字符串 zhangsan 占8个字符
/*
注意：字符 中英文都算1个字符
字节 英文占1个字节
UTF8 中文占3个字节
GBK 中文占2个字节
Unicode 中文占2个字节
*/
-- 新建一张学生信息表stdinfo
CREATE TABLE stdinfo
(
学号 INT(5),
姓名 VARCHAR(20),
年龄 TINYINT(2),
性别 CHAR(1),
系名 VARCHAR(20),
身份证号 VARCHAR(18),
专业号 INT(5)
)
CHARACTER SET = utf8; -- 创建的表数据支持中文字符集样式
-- 查看学生信息表stdinfo
SELECT * FROM stdinfo;
-- 查看学生信息表的结构
DESC stdinfo;
-- 向stdinfo表中插入一行数据
INSERT INTO stdinfo
VALUES(20204,'王小明',19,'女','经济系','510839199810106670',1001);
2.修改表结构（不推荐）
-- 添加/删除列
ALTER TABLE 表名 ADD 列名 数据类型; -- 添加列
ALTER TABLE 表名 DROP 列名; -- 删除列
-- 为学生信息表添加一个列 电话
ALTER TABLE stdinfo ADD 电话 INT(11);
-- 删除学习信息表中的一个列 电话
ALTER TABLE stdinfo DROP 电话;
-- 修改列
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 新的数据类型; -- 修改列名和数据类型
ALTER TABLE 表名 MODIFY COLUMN 列名 新的数据类型;-- 仅修改数据类型
-- 将stdinfo表中的身份证号 从varchar 改成char类型
ALTER TABLE stdinfo MODIFY COLUMN 身份证号 CHAR(18);
3:删除表
TRUNCATE TABLE 表名; -- 删除表中的全部数据
DROP TABLE 表名; -- 删除表结构及表数据
TRUNCATE TABLE stdinfo;
DROP TABLE stdinfo;
-- 查看学生信息表stdinfo
SELECT * FROM stdinfo;
-----------------------------------------------------------------------
第三章：表的约束（DDL语句）
-----------------------------------------------------------------------
-- 约束是从表的层面对列中的数据进行限制，让数据满足某种规则或条件
DEFAULT -- 默认值
PRIMARY KEY -- 主键约束
FOREIGN KEY -- 外键约束
NOT NULL -- 非空约束
UNIQUE -- 唯一约束
AUTO_INCREMENT -- 自增约束
1.建表时增加约束
-- 新建学生信息表stdinfo2 学号作为主键、姓名不能为空，年龄默认20，
-- 性别非空且只能是（男，女），默认是男，身份证号要求唯一
CREATE TABLE stdinfo2
(
学号 INT(5) PRIMARY KEY, -- 主键约束
姓名 VARCHAR(20)NOT NULL, -- 非空约束
年龄 TINYINT(2) DEFAULT 20, -- 默认值
性别 ENUM('男','女') DEFAULT '男', -- ENUM枚举类型，表示一组给定的值
系名 VARCHAR(20),
身份证号 VARCHAR(18) UNIQUE, -- 唯一约束
专业号 INT(5)
)
CHARACTER SET = utf8; -- 创建的表数据支持中文字符集样式
-- 查询学生信息表stdinfo2
SELECT * FROM stdinfo2;
-- 查看学生信息表的结构
DESC stdinfo2;
--------------------------------------------
-- 【向stdinfo2表中插入数据验证表中的约束是否生效】
-- 主键约束，插入两行相同的数据（失败）
INSERT INTO stdinfo2
VALUES(20204,'王小明',19,'女','经济系','510839199810106670',1001);
INSERT INTO stdinfo2
VALUES(20204,'王小明',19,'女','经济系','510839199810106670',1001);
-- [Err] 1062 - Duplicate entry '20204' for key 'PRIMARY'
-- 非空约束，添加一行新的学生信息，但是姓名为空
INSERT INTO stdinfo2(学号,年龄,性别,系名,身份证号,专业号)
VALUES(20205,19,'女','经济系','510839199810106671',1001);
-- [Err] 1364 - Field '姓名' doesn't have a default value
-- 默认值，添加一行新的学生信息，但是年龄为空（生效）
INSERT INTO stdinfo2(学号,姓名,性别,系名,身份证号,专业号)
VALUES(20206,'王大明','女','经济系','510839199810106672',1001);
-- ENUM枚举类型，添加一行新的学生信息，其中性别 中 （失败）
INSERT INTO stdinfo2
VALUES(20207,'小明',19,'中','经济系','510839199810106673',1001);
-- [Err] 1265 - Data truncated for column '性别' at row 1
-- 唯一约束，添加一行新的学生信息，身份证号码同王小明 （失败）
INSERT INTO stdinfo2
VALUES(20208,'王明',19,'男','经济系','510839199810106670',1001);
-- [Err] 1062 - Duplicate entry '510839199810106670' for key '身份证号'
--------------------------------------------
-- 新建一张课程表course，该表包括专业号（主键，自增1001），专业名称（非空）
-- 自增约束只能出现在主键列，但主键可以不自增
-- AUTO_INCREMENT 默认从1开始自增，可以通过赋值的方式改变初始值
CREATE TABLE course
(
专业号 INT(5) PRIMARY KEY AUTO_INCREMENT, -- 自增约束
专业名称 VARCHAR(20) NOT NULL
)
AUTO_INCREMENT = 1001 -- 建表时为自增列赋初始值1001
CHARACTER SET = utf8;
-- 查看课程表信息
SELECT * FROM course;
-- 批量插入专业名称
INSERT INTO course(专业名称)
VALUES('金融学'),
('国际金融与贸易'),
('工商管理'),
('财务管理'),
('商务英语');
2.建表后增加/删除约束（01_表的约束_建表后增加删除约束.txt）
-- 建表后增加自增约束
-- 建表后增加外键约束（重点）
-- 删除约束
----------------------------------------------
-- 新建一张没有自增约束的课程表course2
CREATE TABLE course2
(
专业号 INT(5) PRIMARY KEY,
专业名称 VARCHAR(20) NOT NULL
)
CHARACTER SET = utf8;
-- 建表后增加自增约束
ALTER TABLE course2 MODIFY 专业号 INT AUTO_INCREMENT; -- 专业号必须是主键
-- 让专业号变为自增列，从1001开始
ALTER TABLE course2 AUTO_INCREMENT = 1001;
-- 批量插入专业名称
INSERT INTO course2(专业名称)
VALUES('金融学'),
('国际金融与贸易'),
('工商管理'),
('财务管理'),
('商务英语');
-- 查看课程表信息
SELECT * FROM course2;
----------------------------------------------
ALTER TABLE 从表 ADD CONSTRAINT 外键名
FOREIGN KEY (从表外键字段) REFERENCES 主表(主键字段);
-- 为std_info2表中的专业号添加外键约束
ALTER TABLE stdinfo2 ADD CONSTRAINT fk_cno
FOREIGN KEY (专业号) REFERENCES course(专业号);
-- 查询stdinfo2表的的所有约束
SELECT * FROM information_schema.TABLE_CONSTRAINTS -- 跨库查询，需要有库的前缀
WHERE TABLE_NAME = 'stdinfo2' ;
-- 查询学生信息表stdinfo2
SELECT * FROM stdinfo2;
-- 查看学生信息表的结构
DESC stdinfo2;
-- 查看课程表信息
SELECT * FROM course;
-- 验证外键是否生效，向stdinfo2表中插入专业号不存在的数据
INSERT INTO stdinfo2
VALUES(20209,'小花',21,'女','计科系','510839199810106679',1111); -- 失败
-- [Err] 1452 - Cannot add or update a child row: a foreign key constraint fails
INSERT INTO stdinfo2
VALUES(20209,'小花',21,'女','计科系','510839199810106679',1005); -- 成功
-- 【补充】创建表时直接添加外建约束 ***
CONSTRAINT 外键名 FOREIGN KEY (列名) REFERENCES 主表 (列名)
----------------------------------------------
-- 删除约束
-- 删除主键约束
ALTER TABLE std_info2 DROP PRIMARY KEY;
-- 删除外键约束
ALTER TABLE std_info2 DROP FOREIGN KEY fk_cno;
-- 删除唯一约束
ALTER TABLE std_info2 DROP INDEX 身份证号; -- 注意这里指的不是列名而是约束名
-- 删除非空约束
ALTER TABLE std_info2 MODIFY 姓名 VARCHAR(20) NULL;
------------------------------------------------------------------
--加油---下面四个表依次创建（主键）
-- Create table
create table bonus
(
ename VARCHAR(10) NOT NULL,
job VARCHAR(9),
sal FLOAT(7,2),
comm FLOAT(7,2)
);
-- Create table
create table DEPT
(
deptno INT PRIMARY KEY,
dname VARCHAR(14),
loc VARCHAR(13)
);
-- 插入数据
insert into dept (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');
insert into dept (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON');
-- Create table
create table EMP
(
empno INT PRIMARY KEY,
ename VARCHAR(10) NOT NULL,
job VARCHAR(9),
mgr INT,
hiredate DATE,
sal FLOAT(7,2),
comm FLOAT(7,2),
deptno INT,
FOREIGN KEY(deptno) REFERENCES dept(deptno)
);
-- 插入数据
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902,'1980-12-17', 800.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698,'1981-02-20', 1600.00, 300.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698,'1981-02-22', 1250.00, 500.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839,'1981-04-02', 2975.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698,'1981-09-28', 1250.00, 1400.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839,'1981-05-01', 2850.00, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839,'1981-06-09', 2450.00, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566,'1987-04-19', 3000.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null,'1981-11-17', 5000.00, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500.00, 0.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788,'1987-05-23', 1100.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698,'1981-12-03', 950.00, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566,'1981-12-03', 3000.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782,'1982-01-23', 1300.00, null, 10);
create table salgrade(grade int primary key,losal float(7,2),hisal float(7,2));
insert into salgrade (GRADE, LOSAL, HISAL)
values (1, 700, 1200);
insert into salgrade (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);
insert into salgrade (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);
insert into salgrade (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);
insert into salgrade (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);
------------基于上表操作哦-------
【第1节】SELECT的基本语法
1: 查询所有的列
SELECT * FROM 表名;
SELECT * FROM dept; -- 部门表（deptno部门编号 ，dname部门名称，loc部门地址）
SELECT * FROM emp; -- 员工表（empno员工编号，ename员工姓名，job职位，mgr上级编号，hiredate入职日期,sal薪水，comm提成，deptno部门编号）
SELECT * FROM salgrade; -- 薪资等级表（grade等级，losal最低薪水，hisal最高薪水）
2：选择指定的列进行查询
SELECT empno,ename,sal,comm FROM emp;
SELECT dname,loc FROM dept;
3：使用算术运算符（+ - * /） -- 针对数值类型
-- 计划给每个员工加薪200
SELECT empno,ename,sal,sal+200 FROM emp;
-- 查询出员工的编号，姓名，年薪（12个月），然后给每个员工发999的过节费
SELECT empno,ename,sal,sal*12+999 FROM emp;
-- 括号可以改变算术运算符的优先级
-- 查询出员工的编号，姓名，薪水，先给每个人涨薪50，然后计算年薪
SELECT empno,ename,sal,(sal+50)*12 FROM emp;
4：空值
-- NULL 空值是指不可用，不存在，不知道的取值
-- 空值不是零也不是空格
-- 空值和任何值进行计算，最终的结果也是空值（不参与运算）
-- 查询员工的薪水总和（sal+comm）
SELECT empno,ename,sal,comm,sal+comm FROM emp;
-- Q：如果想要空值参与运算？ 就需要将空值转换成0 （IFNULL函数）
5：别名
-- 别名就是用于识别查询的结果
/*
列名 别名
列名 AS 别名
列名 "别名"
*/
SELECT ename,sal,sal*12 FROM emp;
SELECT ename,sal xinshui,sal*12 nianxin FROM emp;
SELECT ename,sal AS xinshui,sal*12 AS nianxin FROM emp;
SELECT ename,sal "Xin Shui",sal*12 "Nian Xin" FROM emp; -- 别名中有空格、特殊字符等，需要用双引号取别名
【第2节】限定和排序
/*
SELECT 列名1,列名2,列名3...,列名N （3）
FROM 表名 （1）
WHERE 限定条件 （2）
ORDER BY 排序 （4）
*/
1：WHERE子句
-- 查询部门编号为20的所有员工信息
SELECT *
FROM emp
WHERE deptno = 20;
-- 注意：在WHERE子句中字符串和日期类型需要用单引号括起来
-- 查询员工的姓名，部门编号和职位，且职位是CLERK的员工信息
SELECT ename,deptno,job
FROM emp
WHERE job = 'CLERK'; -- CLERK注意大小写
-- MySQL自动进行大小写转换，而ORACLE不支持，查询结果为空
SELECT ename,deptno,job
FROM emp
WHERE job = 'clerk';
-- 查询员工的姓名，职位，且仅展示年薪大于30000的员工信息
SELECT ename,job,sal*12
FROM emp
WHERE sal*12>30000; -- 限定条件的列最好在SELECT子句中写出来
-- 注意：列的别名不能用在WHERE限定条件中
-- 错误用法
SELECT ename,job,sal*12 AS nianxin
FROM emp
WHERE nianxin>30000;
-- [Err] 1054 - Unknown column 'nianxin' in 'where clause'
-- 正确的做法
SELECT ename,job,sal*12 AS nianxin
FROM emp
WHERE sal*12 >30000;
2： 比较运算符 = < <= > >= <>(!=)
-- 查询提成比薪水还高的员工姓名，薪水，提成，职位
SELECT ename,sal,comm,job
FROM emp
WHERE comm > sal;
-- 查询不在30号部门的员工编号，姓名，薪水和职位
SELECT empno,ename,sal,job,deptno
FROM emp
WHERE deptno <> 30; -- != 限定条件的列最好在SELECT子句中写出来
3.其他比较运算符
-- BETWEEN...AND... 在两个值之间
-- 查询薪水在1800到3000之间的所有员工信息
SELECT * FROM emp
WHERE sal BETWEEN 1800 AND 3000;
-- IN 匹配列出的值
-- 查询职位是CLERK 或者是SALESMAN的所有员工信息
SELECT * FROM emp
WHERE job IN ('CLERK','SALESMAN');
-- 错误的用法
SELECT * FROM emp
WHERE job = ('CLERK','SALESMAN'); -- 比较运算符只能连接一个数据，不能连接多个
-- [Err] 1241 - Operand should contain 1 column(s)
-- LIKE 模糊匹配字符
% 表示匹配零个或多个字符
_ 表示匹配1个字符
-- 查询以字母S开头的所有员工姓名
SELECT ename
FROM emp
WHERE ename LIKE 'S%';
-- 查询名字中包含S的所有员工
SELECT ename
FROM emp
WHERE ename LIKE '%S%';
-- 查询名字第二个字母是C的所有员工姓名
SELECT ename
FROM emp
WHERE ename LIKE '_C%';
-- IS NULL 匹配空值
-- 查询没有提成的所有员工信息
SELECT * FROM emp
WHERE comm IS NULL;
-- 查询有提成的所有员工信息
SELECT * FROM emp
WHERE comm IS NOT NULL;
4.逻辑运算符 OR AND NOT
-- AND 表示所有条件都要满足才匹配出结果
-- 查询薪水大于1100，且职位是CLERK的员工编号，姓名，职位和薪水
SELECT empno,ename,job,sal
FROM emp
WHERE sal > 1100 AND job = 'CLERK';
-- OR 表示匹配任意条件就返回结果
-- 查询薪水大于1100，或职位是CLERK的员工编号，姓名，职位和薪水
SELECT empno,ename,job,sal
FROM emp
WHERE sal > 1100 OR job = 'CLERK';
-- NOT 返回条件之外的结果
-- 查询职位不是CLERK，MANAGER的所有员工姓名和职位
SELECT ename,job
FROM emp
WHERE job NOT IN ('CLERK','MANAGER');
-- 注意：优先级的规则
-- 比较运算符 高于 NOT 高于 AND 高于 OR
-- 括号可以改变运算的优先级
-- 查询职位是SALESMAN，或者职位是PRESIDENT且薪水大于1500的员工姓名，职位和薪水
SELECT ename,job,sal
FROM emp
WHERE job = 'SALESMAN' OR job = 'PRESIDENT' AND sal>1500;
-- 等同于
SELECT ename,job,sal
FROM emp
WHERE job = 'SALESMAN' OR (job = 'PRESIDENT' AND sal>1500);
-- 查询职位是SALESMAN或者职位是PRESIDENT,且薪水大于1500的员工姓名，职位和薪水
SELECT ename,job,sal
FROM emp
WHERE (job = 'SALESMAN' OR job = 'PRESIDENT') AND sal>1500;
5.排序
ORDER BY 排序
-- ASC 升序（缺省）
-- DESC 降序
-- 查询员工姓名，职位，部门编号，雇佣日期，并按照雇佣日期升序排列
SELECT ename,job,deptno,hiredate
FROM emp
ORDER BY hiredate ASC;
-- 查询员工姓名，职位，部门编号，雇佣日期，并按照雇佣日期降序排列
SELECT ename,job,deptno,hiredate
FROM emp
ORDER BY hiredate DESC;
-- 注意：作为排序的列，最好在SELECT子句中写出来
-- 查询员工的年薪，并且按照年薪降序排列
SELECT ename,sal,sal*12 AS nianxin
FROM emp
ORDER BY nianxin DESC;
-- 多列排序
-- 查询员工的姓名，部门编号，薪水，并按照部门编号升序排列，然后再按照薪水降序排列
SELECT ename,deptno,sal
FROM emp
ORDER BY deptno ,sal DESC; -- 不要用AND,OR 连接
-- 注意：参与排序的列，顺序是有先后关系的
SELECT ename,deptno,sal
FROM emp
ORDER BY sal DESC,deptno;
第2节【练习题】
1.查询emp表，显示薪水大于2000，且工作类别是MANAGER的雇员信息
SELECT * FROM emp
WHERE sal> 2000 AND job = 'MANAGER' ;
2.查询emp表，显示年薪大于30000，工作类别不是MANAGER的雇员信息
SELECT * FROM emp
WHERE sal*12 >30000 AND job != 'MANAGER';
3.查询emp表， 显示薪水在1500到3000之间，工作类别以“M”开头的雇员信息
SELECT * FROM emp
WHERE job LIKE 'M%'
AND (sal BETWEEN 1500 AND 3000);
4.查询emp表，显示佣金为空并且部门号为20或30的雇员信息(佣金=薪水SAL+津贴COMM)
SELECT * FROM emp
WHERE sal+comm IS NULL
AND deptno IN (20,30); -- deptno=20 OR deptno=30;
5.查询emp表，显示佣金不为空或者部门号为20的雇员信息，要求按照薪水降序排列 (佣金=薪水+津贴COMM)
SELECT ename,deptno,sal,comm ,sal+comm
FROM emp
WHERE sal+comm IS NOT NULL
OR deptno=20
ORDER BY sal DESC;
6.查询emp表，显示年薪大于30000工作类别不是MANAGER，且部门号不是10和40的雇员信息，要求按照雇员姓名进行排列
SELECT * FROM emp
WHERE sal*12>30000 AND job <>'MANAGER'
AND deptno NOT IN(10,40) -- deptno<>10 AND deptno<>40
ORDER BY ename;
【第3节】单行函数
https://www.cnblogs.com/guanghe/p/8463980.html
Q：什么是函数？
-- 在程序中，函数就是具有特定功能的小程序，在很多高级语言中，都有给定的函数，也可以自己编写函数
-- “MySQL函数大全.docx”
Q：什么是-- 在程序中，函数就是具有特定功能的小程序，在很多高级语言中，都有给定的函数，也可以自己编写函数？
-- 单行函数表示函数对每一行数据生效
-- 单行函数主要包括：数学函数、字符函数、日期和时间函数、条件判断函数等
1：数学函数
ROUND ,TRUNCATE ,FLOOR,MOD 函数
-- round(m,n)将数字m精确到小数点后n位（支持四舍五入），如果n不写，默认为0，表示取整
SELECT ROUND(4.222,2),ROUND(4.666,2),ROUND(4.222),ROUND(4.666) FROM DUAL; -- DUAL是一张虚表
-- 当n为负数的时候，表示精确到小数点左边第N位（支持四舍五入）
SELECT ROUND(123.45,-2),ROUND(666.45,-2) FROM DUAL;
-- TRUNCATE(m,n)将数字m精确到小数点后n位(不支持四舍五入），n不能不写，当n=0时，则表示取整
SELECT TRUNCATE(4.222,2),TRUNCATE(4.666,2),TRUNCATE(4.666,0) FROM DUAL;
-- 当n为负数的时候，表示精确到小数点左边第N位(不支持四舍五入）
SELECT TRUNCATE(123.45,-2),TRUNCATE(666.45,-2) FROM DUAL;
-- FLOOR(n) 返回小于等于n的最大整数，下向取整
SELECT FLOOR(6),FLOOR(2),FLOOR(6.2),FLOOR(6.7),FLOOR(0),FLOOR(0.67),FLOOR(-3.5) FROM DUAL;
-- MOD(x,y)返回x被y除后的余数
SELECT MOD(10,3),MOD(23.45,7.8) FROM DUAL;
2:字符函数
（1）大小写转换函数 LOWER,UPPER
-- 查询职位是CLERK的员工信息
SELECT * FROM emp
WHERE job = 'CLERK'; -- 没有隐含的数据转换
-- MySQL对数据的大小写不敏感(Oracle不支持）
SELECT * FROM emp
WHERE job = 'clerk';
-- 等同于
SELECT * FROM emp
WHERE job = UPPER('clerk');
-- 等同于
SELECT * FROM emp
WHERE LOWER(job) = 'clerk';
Q：哪种方式效率最低？
-- LOWER(job)
（2）字符处理函数 CONCAT ,LENGTH，SUBSTR等
-- CONCAT 连接多个字符串
SELECT CONCAT(ename,' is a ',job) FROM emp;
-- LENGTH(str) 返回字符串的长度
SELECT LENGTH('abcdefg') FROM DUAL;
SELECT ename,LENGTH(ename) FROM emp;
-- 查询员工姓名只有5个字符的员工姓名、编号、职位和新水
SELECT ename,LENGTH(ename),empno,job,sal
FROM emp
WHERE LENGTH(ename)=5 ;
-- SUBSTR(x,m,n)从字符串x的第m位开始截取长度为n的子字符串
SELECT SUBSTR('abcdefg',3,2) FROM DUAL;
-- 如果n不写，则表示截取m位之后的所有数据
SELECT SUBSTR('abcdefg',3) FROM DUAL;
-- 如果m为负数，则表示从右边向左边截取m位
SELECT SUBSTR('abcdefg',-3) FROM DUAL;
-- 查询ALLEN 显示ALLE（去掉所有员工名字的最后一个字符）
SELECT ename,SUBSTR(ename,1,LENGTH(ename)-1) FROM emp
-- 注意：单行函数可以嵌套使用
3. 日期和时间函数
-- NOW() 返回当前日期和时间
SELECT NOW();
-- CURDATE() 返回当前日期
SELECT CURDATE();
-- 查询员工在公司工作了多少周，结果向下取整
-- 错误用法，日期类型不能直接进行算术运算
SELECT ename,(CURDATE()-hiredate)/7 FROM emp
-- DATEDIFF(m,n) 计算日期m-n的值
SELECT ename, FLOOR(DATEDIFF(CURDATE(),hiredate)/7) FROM emp
-- YEAR(date) 返回日期所在的年份
-- MONTH(date） 返回日期所在的月份
-- DAY(date) 返回日期所在的天
-- 查询1981年入职的员工姓名和入职日期
SELECT ename,hiredate FROM emp
WHERE YEAR(hiredate) = '1981';
4.条件判断函数
-- IF(条件判断,value1,value2) 如果条件判断为真，则返回value1的值，否则返回value2的值
-- 判断员工的薪水，如果薪水不低于3000，则显示高富帅，否则显示矮矬穷
SELECT sal,IF(sal>=3000,'高富帅','矮矬穷') FROM emp;
-- 判断员工的薪水，如果薪水不低于3000，则显示高富帅，如果薪水在1000~2999之间，则显示中产，否则显示矮矬穷
-- IF嵌套
SELECT sal,IF(sal>=3000,'高富帅',IF(sal>=1000,'中产','矮矬穷')) FROM emp;
-- IFNULL(value1,value2) ,判断value1的值，如果value1不为空，则返回自己，否则返回value2
-- 让空值参与计算
-- 查询员工的薪水总和（sal+comm）
SELECT empno,ename,sal,comm,(sal+IFNULL(comm,0)) FROM emp;
SELECT empno,ename,sal,comm,IFNULL(sal+comm,sal) FROM emp;
-- CASE 语法
CASE 表达式
WHEN value1 THEN result1
WHEN value2 THEN result2
...
WHEN valueN THEN resultN
ELSE resultN+1
END
-- 公司计划调整员工的薪水，CLERK 涨薪200，SALESMAN 减薪50，MANAGER 涨薪500，其他职位薪资不变
SELECT ename,job,sal,
(CASE job
WHEN 'CLERK' THEN sal+200
WHEN 'SALESMAN' THEN sal-50
WHEN 'MANAGER' THEN sal+500
ELSE sal
END) AS xztz
FROM emp;
-- 另一种解法
SELECT ename,job,sal,
(CASE
WHEN job='CLERK' THEN sal+200
WHEN job='SALESMAN' THEN sal-50
WHEN job='MANAGER' THEN sal+500
ELSE sal
END) AS xztz
FROM emp;
第3节【练习题】
利用SQL函数完成下列要求：
1.用一个SQL语句完成在字符串”hello”左右各添加5个*，使其最终返回*****hello*****，
要求用两种方法实现（CONCAT，RPAD/LPAD函数）
SELECT CONCAT('*****','hello','*****') FROM DUAL;
SELECT LPAD(RPAD('hello',10,'*'),15,'*')FROM DUAL;
SELECT RPAD(LPAD('hello',10,'*'),15,'*')FROM DUAL;
2.写一条SQL语句返回”abcdefg”的后三位字符“efg”，
要求用多种方法实现 （SUBSTR，MID，RIGHT，TRIM函数）
SELECT SUBSTR('abcdefg',5) FROM DUAL;
SELECT SUBSTR('abcdefg',-3) FROM DUAL;
SELECT MID('abcdefg',5) FROM DUAL;
SELECT MID('abcdefg',-3) FROM DUAL;
SELECT RIGHT('abcdefg',3) FROM DUAL;
SELECT TRIM('abcd' FROM 'abcdefg')FROM DUAL;
3.查询emp表，显示删除掉第一个字符后的员工姓名（如员工ALLEN，显示为LLEN）
要求用两种方法实现 （SUBSTR ，RIGHT函数）
SELECT ename,SUBSTR(ename,2) FROM emp;
SELECT ename,RIGHT(ename,LENGTH(ename)-1) FROM emp;
4.查询emp表，显示姓名中不包含字符’A’的员工信息 （INSTR 函数）
SELECT * FROM emp
WHERE NOT INSTR(ename,'A');
-- 等同于
SELECT * FROM emp
WHERE INSTR(ename,'A')=0;
-- 等同于
SELECT * FROM emp
WHERE NOT LIKE '%A%';
5.查询emp表，使用员工姓名的第一个字符将员工姓名从左边补齐到长度为10（LPAD函数）
SELECT LPAD(ename,10,SUBSTR(ename,1,1)) FROM emp;
SELECT LPAD(ename,10,LEFT(ename,1)) FROM emp;
6.查询emp表，显示下半年入职的员工信息
SELECT * FROM emp
WHERE MONTH(hiredate)> 6; -- 按月统计
-- 等同于
SELECT * FROM emp
WHERE QUARTER(hiredate)>2; -- 按季度统计
7.查询在周一入职的员工信息 （DAYNAME 函数）
SELECT *,DAYNAME(hiredate) FROM emp
WHERE DAYNAME(hiredate)= 0;
-- 等同于
SELECT *,DAYNAME(hiredate) FROM emp
WHERE DAYNAME(hiredate)= 'Monday';
8.写一条SQL语句查询员工编号、员工姓名、工资、部门号，
要求当员工在10号部门时，显示’财务部’，20号部门时显示’研发部’，
30号部门时显示’销售部’，其余部门显示’未知部门’
SELECT empno,ename,sal,deptno,
(
CASE deptno
WHEN 10 THEN '财务部'
WHEN 20 THEN '研发部'
WHEN 30 THEN '销售部'
ELSE '未知部门'
END
) AS 部门
FROM emp;
9.将员工按empno分成4组，参加不同的活动 （MOD函数）
1组 basketball
2组 football
3组 pingpong
其余的人 running man
SELECT *,
(
CASE MOD(empno,4)
WHEN 1 THEN 'basketball'
WHEN 2 THEN 'football'
WHEN 3 THEN 'pingpong'
ELSE 'running man'
END
) AS 活动分组
FROM emp;
思考题：将员工随机分成4组，参加不同的活动 （RAND函数）
1组 basketball
2组 football
3组 pingpong
其余的人 running man
SELECT *,
(
CASE FLOOR(1+RAND()*4) -- RAND() 产生0~1之间的随机数
WHEN 1 THEN 'basketball'
WHEN 2 THEN 'football'
WHEN 3 THEN 'pingpong'
ELSE 'running man'
END
) AS 活动分组
FROM emp;
-- 另一种解法
SELECT ename,
CASE
WHEN RAND() < 0.25 THEN 'basketball'
WHEN RAND() < 0.5 THEN 'football'
WHEN RAND() < 0.75 THEN 'pingpong'
ELSE 'running man'
END AS 'activity'
FROM emp;
SELECT ename,
IF (RAND()< 0.25 ,'basketball',IF(RAND()<0.5,'football',IF(RAND()<0.75,'pingpong','running man'))) AS 'activity'
FROM emp; -- 第九题思考
【第4节】多表查询
1.多表查询的理论基础
1）笛卡尔积
-- 集合A和集合B所在元素组成的有序对称为笛卡尔积 记作A*B
-- 笛卡尔积是多表查询的理论基础，在实际的查询中要避免笛卡尔积的出现
-- 查询emp表员工姓名和dept表对于的部门名称
SELECT ename,dname
FROM emp,dept
SELECT * from emp -- 14条
SELECT * from dept -- 4条
-- 笛卡尔积14*4=56条
2）多表查询的语法规范
/*
SELECT table1.column1,table2.column2
FROM table1,table2
WHERE 连接条件 -- 两张表连接，至少要有一个连接条件;N张表连接，至少要有N-1个连接条件
*/
-- 查询emp表员工姓名和dept表对于的部门名称
SELECT emp.ename,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno; -- 连接条件（等值连接）
-- 多表查询分为 内连接（等值连接、非等值连接）、外连接（左外连接、右外连接）、自连接
2.内连接
-- 内连接是指N张表都存在的数据才会被显示出来
1）等值连接
-- 用等号连接所有条件
-- 查询员工编号，员工姓名，员工所在部门，及部门地址
SELECT emp.empno,emp.ename,emp.deptno,dept.loc
FROM emp,dept
WHERE emp.deptno=dept.deptno;
-- 使用表的别名来化简查询条件
-- 一般表的别名最好是有意义的英文单词缩写
SELECT e.empno,e.ename,e.deptno,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;
-- 查询KING的员工编号，员工所在的部门，及部门地址
SELECT e.ename,e.deptno,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno -- 连接条件
AND e.ename='KING'; -- 限定条件
2）非等值连接
-- 用除等号之外的符号进行条件的连接
BETWEEN...AND..
-- 查询每个员工的姓名，薪水，和薪水的等级
SELECT e.ename,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal; -- 连接条件
3. 外连接
-- 当需要显示某些不满足连接条件的数据时，需要使用外连接
1）左外连接
LEFT JOIN...ON... 返回所有左边表中的数据（以左表为准，右表用NULL补齐）
-- 查询员工姓名，部门编号，部门地址，要求没有员工的部门也要把部门地址展示出来
SELECT d.deptno,d.loc,e.ename,e.deptno
FROM dept d LEFT JOIN emp e
ON d.deptno=e.deptno;
2) 右外连接
RIGHT JOIN...ON...返回所有右边表中的数据（以右表为准，左表用NULL补齐）
-- 查询员工姓名，部门编号，部门地址，要求没有员工的部门也要把部门地址展示出来
SELECT e.ename,e.deptno,d.deptno,d.loc
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;
-- MySQL不支持全外连接
FULL OUTER JOIN...ON... 全外连接（两边表的数据都要展示出来，没有的数据两边相互补齐）
-- 在Oracle中还有一种左右外连接的写法（MySQL不支持）
SELECT e.ename,e.deptno,d.deptno,d.loc
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno; -- 右连接
SELECT e.ename,e.deptno,d.deptno,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+); -- 左连接
4.自连接
-- 把一张表看做是N张表，在单张表内自行连接
-- 查询每个员工姓名，及他的上级领导的姓名
SELECT * FROM emp
-- worker(empno,ename,job,mgr,hiredate,sal,comm,deptno) w
-- manager(empno,ename) m
SELECT w.ename AS 员工,m.ename AS 领导
FROM emp w,emp m
WHERE w.mgr=m.empno; -- 连接条件
第4节【练习题】
1.查询EMP、DEPT表，输出的列包含员工姓名、工资、部门编号、部门名称、部门地址.
SELECT e.ename,e.sal,e.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;
2.查询工资等级为3/4/5级的员工姓名，工资，工资等级
SELECT e.ename,e.sal,s.grade
FROM emp e ,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND s.grade IN(3,4,5);
3.显示10号部门的员工姓名，工资，工资等级，部门名称
SELECT e.ename,e.sal,s.grade,d.dname
FROM emp e,salgrade s,dept d
WHERE e.deptno=d.deptno -- 连接条件
AND e.sal BETWEEN s.losal AND s.hisal -- 连接条件
AND e.deptno=10; -- 限定条件
4.查询emp表，显示员工姓名及其经理的姓名，没有经理的员工也需要显示
SELECT w.ename AS 员工,m.ename AS 领导
FROM emp w LEFT JOIN emp m
ON w.mgr=m.empno;
5.列出EMP表中部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT d.dname, e.*
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;
【第5节】 多行函数
-- 多行函数：针对多行数据进行运算，最终得到一个结果
-- 常见的多行函数：聚合函数、分组函数
1：聚合函数
AVG 求平均
SUM 求和
MAX 求最大
MIN 求最小
COUNT 计数
-- 查询emp表中所有员工的平均工资
SELECT AVG(sal) FROM emp;
-- 查询emp表的最低工资和最高工资
SELECT MIN(sal),MAX(sal) FROM emp;
-- 聚合函数不能和普通的列一起作为SELECT子句的查询条件
-- 错误用法***
SELECT ename, MIN(sal),MAX(sal) FROM emp;
-- MAX，MIN 函数可以用于日期类型
-- 查询最早入职和最晚入职的员工
SELECT MIN(hiredate),MAX(hiredate) FROM emp;
-- 查询职位是C开头的员工的最高工资
SELECT MAX(sal) FROM emp
WHERE job LIKE 'C%';
-- Q：想要查询出该员工是谁，需要用到子查询语句
-- COUNT(*) 返回表中的记录数(不推荐）
SELECT COUNT(*) FROM emp;
SELECT * FROM emp;
-- COUNT(列名)返回表中的记录数（最好理解）
SELECT COUNT(empno) FROM emp;
SELECT empno FROM emp;
-- COUNT(1) 返回表中的记录数 （效率最高）
SELECT COUNT(1) FROM emp;
SELECT 1 FROM emp;
-- 注意：空值不参与聚合函数的计算
-- 查询所有员工提成的总和，及提成的人数统计
SELECT SUM(comm),COUNT(comm) FROM emp
-- 注意：聚合函数可以和普通的函数嵌套使用
-- 查询所有员工提成的总和，如果没有提成的员工，则先补发50,，及提成的人数统计
SELECT SUM(IFNULL(comm,50)),COUNT(IFNULL(comm,50)) FROM emp
-- 注意：聚合函数不能和聚合函数嵌套使用
SELECT SUM(COUNT(sal)) FROM emp; -- 错误用法
-- 注意：聚合函数可以进行算术运算
-- 查询职位是CLERK的最低工资和最高工资之和
SELECT MIN(sal),MAX(sal),MIN(sal) + MAX(sal) FROM emp
WHERE job = 'CLERK';
SELECT * FROM emp
2.分组函数
/*
SELECT 列名1,列名2,列名3...,列名N
FROM 表名
WHERE 限定条件
GROUP BY 分组条件 HAVING 分组限定条件
ORDER BY 排序
*/
-- HAVING 只能用在GROUP BY的后面，但是GROUP BY可以没有HAVING
1） GROUP BY
-- 查询emp表中各个部门的员工的平均工资（按部门统计平均工资）
SELECT deptno,AVG(sal) -- deptno是分组函数指定的列，不是普通的列，所以可以和聚合函数一起作为查询条件
FROM emp
GROUP BY deptno;
-- 按职位统计最高工资（统计每个职位的最高工资）
SELECT job, MAX(sal)
FROM emp
GROUP BY job;
-- 多列分组（难点）
-- 按部门分组统计emp表中各个职位的薪水总和
SELECT deptno,job ,SUM(sal)
FROM emp
GROUP BY deptno,job;
-- 注意：分组条件的列，不受先后顺序的影响，最终查询结果相同
SELECT deptno,job ,SUM(sal)
FROM emp
GROUP BY job,deptno ORDER BY deptno;
2）HAVING
-- 需要和GROUP BY一起使用
-- 求平均工资高于2000的部门（先按部门分组，然后再统计平均工资，最后展示出大于2000的部门）
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno HAVING AVG(sal)>2000;
-- 错误用法
SELECT deptno,AVG(sal)
FROM emp
WHERE AVG(sal)>2000 -- 聚合函数不能在WHERE子句中进行限定（用having限定）
GROUP BY deptno;
-- [Err] 1111 - Invalid use of group function
-- 注意：分组函数中HAVING子句的限定条件不一定要在SELECT子句中展示
-- 查询平均工资大于2900的部门并展示出该部门的最高工资
SELECT deptno,MAX(sal)
FROM emp
GROUP BY deptno HAVING AVG(sal)>2900;
-- 查询最高工资不低于3000的部门的人数
SELECT deptno,COUNT(1)
FROM emp
GROUP BY deptno HAVING MAX(sal)>3000;
-- 使用WHERE子句限定普通的条件
-- 查询emp表中各个职位的薪水总和，只显示薪水总和大于5000的职位，且职位不是SALES开头的，并按照薪水总和降序排列
SELECT job,SUM(sal)
FROM emp
WHERE job NOT LIKE 'SALES%' -- 限定普通的条件
GROUP BY job HAVING SUM(sal)>5000
ORDER BY SUM(sal) DESC;
-- 等同于
SELECT job,SUM(sal)
FROM emp
GROUP BY job HAVING SUM(sal)>5000 AND job NOT LIKE 'SALES%' -- HAVING中的限定必须是GROUP BY所对应的表
ORDER BY SUM(sal) DESC;
-- 查询emp表中各个职位的薪水总和，只显示薪水总和大于5000的职位，且销售部除外，并按照薪水总和降序排列
SELECT e.job,SUM(e.sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.dname <> 'SALES'
GROUP BY e.job HAVING SUM(e.sal)> 5000
ORDER BY SUM(e.sal) DESC;
-- 错误的写法
SELECT e.job,SUM(e.sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.dname <> 'SALES'
GROUP BY e.job HAVING SUM(e.sal)> 5000 AND d.dname <> 'SALES' -- having只针对分组限定的表
-- [Err] 1054 - Unknown column 'd.dname' in 'having clause'
第五节 【练习题】
1.查询EMP表，输出每个职位的职位名称及平均工资，按平均工资升序排列.
SELECT job,AVG(sal)
FROM emp
GROUP BY job
ORDER BY AVG(sal) ;
2.查询EMP表，输出每个部门的各个职位的平均工资，并按部门编号升序、平均工资降序排序。
SELECT deptno,job,AVG(sal)
FROM emp
GROUP BY deptno,job
ORDER BY deptno,AVG(sal) DESC;
3.查询每个工资等级所对应的人数
SELECT s.grade,COUNT(e.empno)
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade;
4.查询每个部门提成的总额，没有提成的（为NULL的）人先补发50，按照部门编号降序排序.
SELECT e.deptno,d.dname,SUM(IFNULL(e.comm,50))
FROM emp e,dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname
ORDER BY e.deptno DESC;
-- 另一种解法
SELECT deptno,SUM(IFNULL(comm,50))
FROM emp
GROUP BY deptno
ORDER BY deptno DESC;
**5.查询EMP表，要求写一条SQL语句统计出如下图所示结果 （思考题）
10号部门人数 20号部门人数 30号部门人数
------------ ------------ ------------
3 5 6
SELECT deptno,COUNT(empno)
FROM emp
GROUP BY deptno
SELECT SUM(IF(deptno=10,1,0)) AS '10号部门人数',
SUM(IF(deptno=20,1,0)) AS '20号部门人数',
SUM(IF(deptno=30,1,0)) AS '30号部门人数'
FROM emp;
-- 推荐的解法
SELECT SUM(deptno=10) AS '10号部门人数',
SUM(deptno=20) AS '20号部门人数',
SUM(deptno=30) AS '30号部门人数'
FROM emp;
-- 另一种解法
SELECT COUNT(IF(deptno=10,1,NULL)) AS '10号部门人数',
COUNT(IF(deptno=20,1,NULL)) AS '20号部门人数',
COUNT(IF(deptno=30,1,NULL)) AS '30号部门人数'
FROM emp
-- 另一种解法
SELECT COUNT(DISTINCT a.empno) AS '10号部门人数',
COUNT(DISTINCT b.empno) AS '20号部门人数',
COUNT(DISTINCT c.empno) AS '30号部门人数'
FROM emp a, emp b,emp c
WHERE a.deptno=10
AND b.deptno=20
AND c.deptno=30;
-- 另一种解法
SELECT (SELECT COUNT(1) FROM emp WHERE deptno = 10) '10号部门人数',
(SELECT COUNT(1) FROM emp WHERE deptno = 20) '20号部门人数',
(SELECT COUNT(1) FROM emp WHERE deptno = 30) '30号部门人数'
FROM DUAL;
【第6节】 子查询
-- 子查询不是多表查询***
-- 查询出薪水高于JONES的雇员
SELECT sal FROM emp WHERE ename='JONES'; -- 2975 子查询语句
SELECT * FROM emp WHERE sal > 2975; -- 主查询语句
-- 查询的结果基于未知的条件，非常适合用子查询
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');
/* 使用子查询的规则
1：子查询语句要用括号括起来
2：习惯上将子查询语句放在比较运算符的右边
3：在子查询语句中尽量不要使用Order by进行排序（没有意义）
4：对单行子查询使用单行运算符
5：对多行子查询使用多行运算符
*/
1：单行子查询
-- 子查询语句返回的结果只有一个（针对子查询语句），使用单行比较运算符 = < <= > >= <>(!=)
-- 查询同ALLEN的部门编号和职位都相同的员工
SELECT deptno FROM emp WHERE ename='ALLEN'; -- 30 子查询语句
SELECT job FROM emp WHERE ename='ALLEN'; -- SALESMAN 子查询语句
SELECT * FROM emp -- 主查询语句
WHERE deptno=(SELECT deptno FROM emp WHERE ename='ALLEN')
AND job=(SELECT job FROM emp WHERE ename='ALLEN');
-- 查询ALLEN所在的部门名称
-- 多表查询
SELECT d.dname,e.empno,e.ename
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.ename='ALLEN';
-- e.deptno = d.deptno emp表有14行数据，匹配14次
-- e.ename='ALLEN'; emp表有14行数据，匹配14次
-- 14+14=28次
-- 子查询
SELECT deptno FROM emp WHERE ename='ALLEN' -- 30 子查询语句
SELECT dname FROM dept WHERE deptno= (SELECT deptno FROM emp WHERE ename='ALLEN');
-- ename='ALLEN' emp表有14行数据，匹配14次
-- deptno=30 dept表有4行数据，匹配4次
-- 14+4=18次
总结：
-- 当最终的查询结果来自一张表的时候，只是用了其他表的数据作为中间条件，多表查询和子查询都可以实现，推荐使用子查询（效率更高）
-- 当查询结果来自多张表的时候，只能用多表查询(如果要用子查询，可以将子查询当做一张表来处理）
-- 查询的结果基于未知的条件，非常适合用子查询
-- 注意：子查询可以使用聚合函数
-- 查询emp表的最低工资和最高工资对应的员工
SELECT MIN(sal) FROM emp; -- 800 子查询语句
SELECT MAX(sal) FROM emp; -- 5000 子查询语句
SELECT * FROM emp
WHERE sal=800 OR sal=5000 ; -- sal IN (800,5000)
SELECT * FROM emp
WHERE sal=(SELECT MIN(sal) FROM emp) OR sal=(SELECT MAX(sal) FROM emp) ;
-- 注意：子查询也可以用在HAVING子句中
-- 查询部门的最低工资，要求只查询出高于30号部门最低工资的数据
SELECT MIN(sal) FROM emp WHERE deptno=30 -- 950 子查询语句
SELECT deptno,MIN(sal)
FROM emp
GROUP BY deptno HAVING MIN(sal)> (SELECT MIN(sal) FROM emp WHERE deptno=30);
2.多行子查询
-- 子查询语句返回的结果有多个(1列有多个值），使用多行比较运算符 IN, ANY,ALL
-- IN 匹配列中的多个值
-- 查询每个部门最低薪水的员工编号和员工姓名
SELECT empno,ename,sal,deptno
FROM emp
WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno); -- 1300,800,950
-- 等同于
SELECT empno,ename,sal,deptno
FROM emp
WHERE sal IN (1300,800,950); -- 30号部门 1300
-- 注意：此题的做法不够精确，没有和部门编号对应上，只是查询了3个薪水的值所对应的员工
-- ANY 满足任意条件就返回结果
-- 查询职位比CLERK最高薪水还少（小于最大值）的其他职位的员工信息
SELECT *
FROM emp
WHERE sal < (SELECT MAX(sal) FROM emp WHERE job ='CLERK')
AND job <> 'CLERK';
-- <ANY 表示小于最大值，>ANY 表示大于最小值
SELECT *
FROM emp
WHERE sal < ANY (SELECT sal FROM emp WHERE job ='CLERK')
AND job <> 'CLERK';
-- ALL 满足所有条件才返回结果
-- 查询职位比CLERK最高薪水还多（大于最大值）的其他职位的员工信息
SELECT *
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp WHERE job ='CLERK')
AND job <> 'CLERK';
-- <ALL 表示小于最小值，>ALL 表示大于最大值
SELECT *
FROM emp
WHERE sal > ALL (SELECT sal FROM emp WHERE job ='CLERK')
AND job <> 'CLERK';
3：多列子查询（多行多列子查询）
-- 子查询语句的结果可以有多列多行数据，使用IN 进行匹配
-- 查询同ALLEN的部门编号和职位都相同的员工
SELECT deptno,job FROM emp WHERE ename='ALLEN'; -- 30,SALESMAN 子查询语句
SELECT * FROM emp -- 主查询语句
WHERE (deptno,job) IN (SELECT deptno,job FROM emp WHERE ename='ALLEN')
-- 查询每个部门最低薪水的员工编号和员工姓名
SELECT empno,ename,sal,deptno
FROM emp
WHERE (deptno,sal) IN (SELECT deptno,MIN(sal) FROM emp GROUP BY deptno);
4.将子查询的结果看作是一张表再进行查询（难点）
-- 子查询看作一张表的时候，必须取一个表的别名
-- 查询每个部门的名称，所在地和人数，并且要求部门人数不低于5人的才显示
SELECT d.dname,d.loc,c.ct
FROM dept d,(SELECT deptno,COUNT(empno) AS ct FROM emp GROUP BY deptno) c -- 子查询当做一张表
WHERE d.deptno=c.deptno
AND c.ct>=5;
-- 找出低于各个部门平均工资的各个部门的员工
SELECT e1.*,e2.*
FROM emp e1,(SELECT AVG(sal) AS asal,deptno FROM emp GROUP BY deptno) e2 -- 子查询当做一张表
WHERE e1.deptno=e2.deptno
AND e1.sal<e2.asal
ORDER BY e1.deptno
5:分页子查询
-- LIMIT 在查询结果中找到其中特定的几行
-- LIMIT m,n 从m+1行开始取值，取n行数据
-- 查询emp表中第3行到第5行的数据
SELECT * FROM emp
LIMIT 2,3
-- 当m不写的时候，则表示从1行开始取n行数据
SELECT * FROM emp
LIMIT 5
-- LIMIT用在查询语句的最后（order by之后）
-- 查询工资排名前3的员工信息
SELECT * FROM emp ORDER BY sal DESC LIMIT 3;
-- 查询工资排名前3的员工信息,有并列的名次需一并展示
-- 错误的情况
SELECT *
FROM emp
WHERE sal IN (SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3);
-- [Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
-- 正确的做法（将子查询语句看作一张新表来处理LIMIT）
SELECT *
FROM emp
WHERE sal IN (SELECT sal FROM (SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3) s);
第六节【练习题】
1.使用子查询，找出哪个部门下没有员工
SELECT * FROM dept
WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp);
2.使用子查询，列出薪金比“ALLEN”多的所有员工
SELECT * FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename='ALLEN');
3.使用子查询，列出在销售部（SALES）工作的员工信息
SELECT * FROM emp
WHERE deptno =(SELECT deptno FROM dept WHERE dname='SALES');
4.使用子查询，找出那些工资低于所有平均工资的员工
SELECT * FROM emp
WHERE sal < (SELECT AVG(sal)FROM emp);
5.使用子查询，找出那些工资低于每个部门的平均工资的员工 （ALL）
SELECT * FROM emp
WHERE sal < ALL (SELECT AVG(sal) FROM emp GROUP BY deptno);
6.使用SQL语句查出各个部门工资最高的员工的部门编号、员工姓名及其工资的信息
SELECT * FROM emp
WHERE (sal,deptno) IN(SELECT MAX(sal), deptno FROM emp GROUP BY deptno);
7.列出所有部门的相应信息和部门人数，没有员工的部门则部门人数显示为0
SELECT d.*,IFNULL(c.ct,0)
FROM dept d LEFT JOIN (SELECT deptno,COUNT(empno) AS ct FROM emp GROUP BY deptno) c
ON d.deptno=c.deptno;
8.查询高于自己部门平均工资的员工名字,部门号,工资,平均工资（保留2位小数）
SELECT e.ename,e.deptno,e.sal,a.asal
FROM emp e,(SELECT deptno,AVG(sal) AS asal FROM emp GROUP BY deptno) a
WHERE e.deptno=a.deptno
AND e.sal > a.asal;
**9.查询入职日期排名后5位的员工信息，且同一天入职员工要求并列展示
SELECT * FROM emp
WHERE hiredate IN
(SELECT hiredate FROM
(SELECT DISTINCT hiredate FROM emp ORDER BY hiredate DESC LIMIT 5) h)
ORDER BY hiredate DESC ;
【第7节】表的集合操作（课外阅读）
02_表的集合操作.txt
1：合集 UNION，UNION ALL
-- 返回两个集合取并操作后的结果
-- 查询薪水大于2500或者职位是Manager的员工信息
SELECT ename, sal, job FROM EMP WHERE sal > 2500
UNION
SELECT ename, sal, job FROM EMP WHERE job = 'MANAGER'
-- 等同于 OR
SELECT ename, sal, job FROM EMP WHERE sal > 2500
OR job = 'MANAGER'
-- UNION ALL 多个查询结果都有数据，会重复展示
SELECT ename, sal, job FROM EMP WHERE sal > 2500
UNION ALL
SELECT ename, sal, job FROM EMP WHERE job = 'MANAGER'
-- 查询薪水排名前三的员工（且并列名次一并展示）；还要查询入职日期后5的员工（且并列日期一并展示）【思考题】
-----------------------------------------------------------------------
第五章：表的操作语句DML（非SELECT语句部分）
-----------------------------------------------------------------------
-- INSERT 插入，UPDATE 更新 DELETE 删除
【第1节】INSERT 插入
1）按照表的原有结构插入数据（全列插入）
INSERT INTO 表名
VALUES(value1,value2,value3,...,valueN);-- 全列插入，要注意列名和列值的对应关系
-- 向stdinfo表中插入一行数据
INSERT INTO stdinfo
VALUES(20204,'王小明',19,'女','经济系','510839199810106670',1001);
2) 按照自定义的列插入数据（部分列插入）
INSERT INTO 表名(列名1,列名2,列名3,...,列名N)
VALUES(value1,value2,value3,...,valueN);
-- 默认值，添加一行新的学生信息，但是年龄为空（生效）
INSERT INTO stdinfo2(学号,姓名,性别,系名,身份证号,专业号)
VALUES(20206,'王大明','女','经济系','510839199810106672',1001);
3）使用INSERT 插入多行数据
INSERT INTO 表名(列名1,列名2,列名3,...,列名N)
VALUES
(value1,value2,value3,...,valueN),
(value1,value2,value3,...,valueN),
...
(value1,value2,value3,...,valueN);
-- 批量插入专业名称
INSERT INTO course(专业名称)
VALUES('金融学'),
('国际金融与贸易'),
('工商管理'),
('财务管理'),
('商务英语');
4) 从A表插入数据到B表（insert+select）
INSERT INTO 表名
SELECT -- 一个完整的查询语句，且查询结果的列要在被插入的表中存在
-- Create bonus的表
create table bonus
(
ename VARCHAR(10) NOT NULL,
job VARCHAR(9),
sal FLOAT(7,2),
comm FLOAT(7,2)
);
SELECT * FROM bonus;
-- 讲emp表中有提成的员工信息导入到bonus表中
INSERT INTO bonus
(SELECT ename,job,sal,comm FROM emp WHERE comm IS NOT NULL);
【第2节】UPDATE 更新
/*
UPDATE 表名
SET 列名1=value1,列名2=value2...,列名N=valueN
WHERE 限定条件 ;-- 注意：如果不加WHERE 则表示对表中所有数据对应的列一并修改
*/
-- 为emp表中所有的员工加薪500
UPDATE emp
SET sal=sal+500;
SELECT * FROM emp;
-- 为emp表中职位是SALESMAN的员工减薪300
UPDATE emp
SET sal=sal-300 WHERE job='SALESMAN';
-- 和SELECT语句配合使用
-- 将RESEARCH部门的上级领导改成KING
SELECT * FROM dept WHERE dname='RESEARCH' -- 20
SELECT * FROM emp WHERE ename='KING' ; -- 7839
SELECT * FROM emp WHERE deptno=20 ;
KING 7839
JONES
SCOTT FORD
ADAMS SMITH
-- 错误用法
UPDATE emp
SET mgr= (SELECT empno FROM emp WHERE ename='KING')
WHERE deptno=(SELECT deptno FROM dept WHERE dname='RESEARCH');
-- [Err] 1093 - You can't specify target table 'emp' for update in FROM clause (不能既更新表又查询表）
-- 正确的用法
UPDATE emp
SET mgr= (SELECT empno FROM (SELECT empno FROM emp WHERE ename='KING') a)
WHERE deptno=(SELECT deptno FROM dept WHERE dname='RESEARCH');
-- 将所有薪水比ALLEN高的，全部改成和ALLEN一样
SELECT sal FROM emp WHERE ename='ALLEN' -- 1800
UPDATE emp
SET sal= (SELECT sal FROM (SELECT sal FROM emp WHERE ename='ALLEN') s)
WHERE sal> (SELECT sal FROM (SELECT sal FROM emp WHERE ename='ALLEN') s);
SELECT * FROM emp
【第3节】DELETE 删除
/*
DELETE [FROM] 表名
WHERE 限定条件; -- 如果不带WHERE子句，则表示删除表中所有的数据（慎用）
*/
-- 删除bonus表中所有的数据
DELETE FROM bonus;
SELECT * FROM bonus;
-- 删除SALES部门的所有员工信息
DELETE FROM emp
WHERE deptno =(SELECT deptno FROM dept WHERE dname='SALES');
SELECT * FROM emp;
-- 总结：
DELETE 删除 DML语句，支持事务，删除表中的数据，可以删除全部，也可以删除限定的数据
DROP 删除， DDL语句 不支持事务，删除表结构及数据
TRUNCATE 删除 DDL语句 不支持事务，删除表中全部的数据，不能只删除部分
DML部分【练习题】
1.在EMP表中，增加一名员工，员工信息参照现有员工构造.
2.员工SMITH调动到SALES部门，请编写SQL语句更新员工信息.
3.员工JAMES已经离职，请编写SQL语句更新数据库.
4.用户执行DELETE FROM emp;语句删除了EMP表的记录，但没有提交，请问有办法恢复EMP原来的数据吗？
-- 用事务进行处理
-- 注意：请先重新导入emp表的数据
扫一扫
2万+
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
