01- SQL概述
SQL：Structured Query Language结构化查询语言，它是使用关系模型的数据库应用语言，由IBM上世纪70年代开 发出来。后由美国国家标准局（ANSI）开始着手制定SQL标准，先后有SQL-86，SQL-89，SQL-92，SQL-99等标 准。
SQL的语言规范
mysql对于SQL语句不区分大小写，SQL语句关键字尽量大写
SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
关键字不能被缩写也不能分行
值，除了数值型，字符串型和日期时间类型使用单引号（’ ‘）
别名，尽量使用双引号（” “），而且不建议省略as
所有标点符号使用英文状态下的半角输入方式
必须保证所有(),单引号，双引号是成对结束的
可以使用（1）#单行注释 （2）—空格单行注释 （3）/ 多行注释 /
命名规则
数据库、表名不得超过30个字符，变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
不能在对象名的字符间留空格
必须不能和用户定义的其他对象重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突
保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表
里是整数,那在另一个表里可就别变成字符型了
在命令行中的要求：
说明：一个语句可以分开多行编写，以;或\g结束
SQL分类
DDL（Data Definition Languages)：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引
等数据库对象。
主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
DML（Data Manipulation Language）：数据操作语言，用于添加、删除、更新和查询数据库记录，并检查
数据完整性。
主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。
SELECT是SQL语言的基础，最为重要。
DCL（Data Control Language）：数据控制语言，用于控制不同数据段直接的许可和访问级别的语句。这些
语句定义了数据库、表、字段、用户的访问权限和安全级别。
主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。
#如下是一个查询语句-- 使用的是select ... from/*这是多行注释Java语言的分类：1. 基本语法2. oop3. io \ 多线程 \ 网络编程 \ 反射 \ 集合 等SQL的分类：DML : INSERT \ DELETE \ UPDATE \ SELECTDDL : CREATE \ ALTER \ DROP \ RENAME \ TRUNCATEDCL : COMMIT \ ROLLBACK \ GRANT \ REVOKE \ SAVEPOINT*/SELECT *FROM employees
02-基本的SELECT语句
#02-基本的SELECT语句USE temp; #使用指定的数据库#1. 基本使用SELECT employee_id,last_name,EMAILFROM employees;SELECT department_id,manager_id,location_idFROM departments;SELECT * # * : 代表所有的字段FROM employees;SELECT *FROM countries;#2. 列的别名#as : alias#可以使用一对""，给列起别名SELECT employee_id emp_id,last_name AS lname,salary "monthly salary"FROM employees;#3. 去除重复行SELECT DISTINCT department_idFROM employees;#如下操作是错误的SELECT employee_id, DISTINCT department_idFROM employees;#4. 空值问题#空值，表示没有赋值，理解为null。#空值参与运算的问题：结果也为空。#空值，不等同于0,'','null'SELECT employee_id,commission_pct,salary,salary * (1 + commission_pct),salary * (1 + IFNULL(commission_pct,0)) "real_salary"FROM employees;#5. 显示表结构DESC employees;DESCRIBE employees;
03-过滤数据
#03-过滤数据#查询90号部门员工的信息SELECT employee_id,last_name,department_id,salaryFROM employees# 使用where实现数据的过滤# where 必须紧跟在from子句的后面WHERE department_id = 90;SELECT employee_id,last_name,department_id,salaryFROM employees#where salary > 5000;WHERE department_id <> 90;# 2. between 下边界 and 上边界 (包含边界)#查询工资大于等于6000 且小于等于 8000之间的员工信息SELECT employee_id,salaryFROM employeesWHERE salary >= 6000 AND salary <= 8000;#where salary between 6000 and 8000;#WHERE salary BETWEEN 8000 AND 6000;#3. in(set):SELECT employee_id,department_id,salaryFROM employees#where department_id = 30 or department_id = 40 or department_id = 50;#where department_id in (30,40,50);WHERE salary IN (6000,7000,8000);#4. like ： 模糊查询#精确查询SELECT employee_id,LAST_NAMEFROM employeesWHERE last_name = 'King'; #字符串、日期需要使用一对''表示。注意，不要使用""#where hire_date = '1993-01-13';#查询姓名中包含字符'a'的员工# % : 表示0个，1个或多个字符SELECT employee_id,LAST_NAMEFROM employeesWHERE last_name LIKE '%a%';# 查询姓名中包含字符'a'且包含字符'e'的员工SELECT employee_id,LAST_NAMEFROM employees#WHERE last_name LIKE '%a%e%' or last_name LIKE '%e%a%';WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';# 查询姓名中第3个字符是a的员工# _ : 表示一个不确定的字符SELECT employee_id,LAST_NAMEFROM employeesWHERE last_name LIKE '__a%';# 查询姓名中第2个字符是_且第3个字符是a的员工SELECT employee_id,LAST_NAMEFROM employees#WHERE last_name LIKE '_\_a%'; # sout("林辉很\"帅\"");WHERE last_name LIKE '_#_a%' ESCAPE '#';# 5. is null:空值SELECT last_name,commission_pctFROM employeesWHERE commission_pct IS NULL;SELECT last_name,commission_pctFROM employees#WHERE !(commission_pct IS NULL);WHERE commission_pct IS NOT NULL;# 6. 算术运算符SELECT employee_id,salary,department_idFROM employeesWHERE department_id MOD 20 = 0;#选择工资不在5000到12000的员工的姓名和工资SELECT last_name,salaryFROM employees#where salary < 5000 or salary > 12000;WHERE salary NOT BETWEEN 5000 AND 12000;#where !(salary >= 5000 and salary <= 12000);#选择公司中没有管理者的员工姓名及job_idSELECT last_name,job_idFROM employeesWHERE manager_id IS NULL;
04-排序和分页
#04-排序和分页#1. 排序# 1.1#按照员工的工资从高到低排序# desc: descend,降序# asc : ascend,升序SELECT employee_id,salaryFROM employees#order by salary desc; #降序#order by salary asc; #升序ORDER BY salary ;#如果没有显式指名asc或desc，则默认升序排列# 1.2 使用列的别名进行排序#别名可以在order by中使用SELECT employee_id,last_name,salary salFROM employeesORDER BY sal;#不能在过滤条件中使用列的别名。#如下操作报错：SELECT employee_id,last_name,salary salFROM employeesWHERE sal >= 6000;#order by要声明在where的后面SELECT employee_id,last_name,salary salFROM employeesWHERE salary > 5000ORDER BY last_name ASC;#1.3 二级排序SELECT employee_id,last_name,department_id,salaryFROM employeesORDER BY department_id,salary DESC;#2. 分页#每页显示20条记录，显示第1页数据：SELECT employee_id,last_name,salaryFROM employeesLIMIT 0,20;#每页显示20条记录，显示第2页数据：SELECT employee_id,last_name,salaryFROM employeesLIMIT 20,20;#每页显示20条记录，显示第3页数据：SELECT employee_id,last_name,salaryFROM employeesLIMIT 40,20;#每页显示pageSize条记录，显示第pageNo页数据：#limit (pageNo - 1) * pageSize,pageSize#查询工资最高的20个员工信息:top-NSELECT employee_id,last_name,salaryFROM employeesORDER BY salary DESCLIMIT 0,20; #limit 要声明在order by的后面
05-多表查询
#05-多表查询#出现了笛卡尔积的错误SELECT employee_id,last_name,department_nameFROM employees,departments; #查询出2889行数据SELECT 2889/107FROM DUAL;SELECT *FROM departments;#正确的写法:多表的查询，一定要有连接条件SELECT employee_id,last_name,department_nameFROM employees,departments#多表的连接条件WHERE employees.`department_id` = departments.`department_id`#进一步：如果查询的字段在多个表中都出现，则一定需要指明来自于哪个表。比如：department_idSELECT employee_id,last_name,department_name,departments.department_idFROM employees,departmentsWHERE employees.`department_id` = departments.`department_id`#建议开发中多表查询中，查询的字段都指明来自于哪个表。--->sql优化SELECT employees.employee_id,employees.last_name,departments.department_name,departments.department_idFROM employees,departmentsWHERE employees.`department_id` = departments.`department_id`#表的别名的使用。一旦给表起了别名，就可以在select中或where中使用。SELECT e.employee_id,e.last_name,d.department_name,d.department_idFROM employees e,departments dWHERE e.`department_id` = d.`department_id`#需求：SELECT e.employee_id,e.last_name,d.department_name,l.cityFROM employees e,departments d,locations l#连接条件WHERE e.department_id = d.department_idAND d.location_id = l.location_id;#结论：如果实现n个表的多表查询，则至少需要n - 1个连接条件。##############################################/*多表查询的分类：1. 等值连接 vs 不等值连接2. 自连接 vs 非自连接3. 内连接 vs 外连接*/#不等值连接SELECT employee_id,salary,grade_levelFROM employees e,job_grades j#where e.`salary` >= j.`lowest_sal` and e.`salary` <= j.`highest_sal`;WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;#自连接#查询员工的employee_id,last_name及其管理者的employee_id,last_nameSELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_nameFROM employees emp,employees mgrWHERE emp.`manager_id` = mgr.`employee_id`;########################################################/*内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行外连接：分为左外连接 和 右外连接左外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行这种连接称为左外连接。右外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行这种连接称为右外连接。*/#内连接的例子：SELECT employee_id,last_name,department_nameFROM employees e,departments dWHERE e.`department_id` = d.`department_id`# 要想实现外连接，需要使用sql-99语法中的相关结构。#####################################################sql-99语法实现内连接：举例1SELECT employee_id,last_name,department_nameFROM employees e INNER JOIN departments dON e.`department_id` = d.`department_id`;#举例2：SELECT employee_id,last_name,department_name,cityFROM employees e JOIN departments dON e.`department_id` = d.`department_id`JOIN locations lON d.`location_id` = l.`location_id`;#左外连接：#需求：查询所有员工的employee_id,last_name,department_nameSELECT employee_id,last_name,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`;#右外连接：SELECT employee_id,last_name,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`;#与上一个select是相同的需求。SELECT employee_id,last_name,department_nameFROM departments d LEFT JOIN employees eON e.`department_id` = d.`department_id`;# 满外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左表和右表中不满足条件的行#这种连接称为满外连接。# mysql 不支持full joinSELECT employee_id,last_name,department_nameFROM employees e FULL JOIN departments dON e.`department_id` = d.`department_id`;#课后练习# 2.查询90号部门员工的job_id和90号部门的location_idSELECT e.job_id,d.location_idFROM employees e JOIN departments dON e.`department_id` = d.`department_id`WHERE e.department_id = 90;# 3.选择所有有奖金的员工的 last_name , department_name , location_id , citySELECT e.last_name , d.department_name , d.location_id , l.cityFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`LEFT JOIN locations lON d.`location_id` = l.`location_id`WHERE e.`commission_pct` IS NOT NULL;# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_nameSELECT last_name , job_id , e.department_id , department_nameFROM employees eJOIN departments dON e.`department_id` = d.`department_id`JOIN locations lON l.`location_id` = d.`location_id`WHERE l.`city` = 'Toronto';# 5.选择所有员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employees Emp# manager Mgr#kochhar 101 king 100SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager, mgr.employee_id "Mgr#"FROM employees empLEFT JOIN employees mgrON emp.manager_id = mgr.employee_id;
06-7种JOIN
#06-7种JOIN
#中图：内连接 106条
SELECT employee_id,last_name,department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;
#左上图：左外连接 107条
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图：右外连接 122条
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图： 1条
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;
#右中图：
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#左下图：满外连接 123条
#方式一：左中图 + 右上图
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
#方式二：左上图 + 右中图 :略
#右下图：左中图 + 右中图： 17条记录
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#结论：能使用union all的场景，就不推荐使用union。因为避免去重，效率低。
07-单行函数
#07-单行函数
# 1. 字符串类型
SELECT CONCAT('hello','world','hello','beijing') "details"
FROM DUAL;
#需求： xxx worked for yyy
SELECT CONCAT(emp.last_name,' worked for ', mgr.last_name) "details"
FROM employees emp JOIN employees mgr
ON emp.`manager_id` = mgr.`employee_id`;
SELECT CONCAT_WS('-','hello','world','beijing')
FROM DUAL;
SELECT CHAR_LENGTH('hello'),LENGTH('hello'),CHAR_LENGTH('中国'),LENGTH('中国')
FROM DUAL;
#sql中索引从1开始！
SELECT INSERT('helloworld',2,3,'aaaaa')
FROM DUAL;
#下面的查询中，salary会自动的转换为字符串类型，此自动转换的行为称为：隐式转换
SELECT employee_id,LPAD(salary,10,' ') "details", RPAD(last_name,10,' ')
FROM employees;
DESC employees;
# 针对于数值类型、字符串类型、日期类型存在隐式转换。
SELECT 1 + '1'
FROM DUAL;
SELECT TRIM('aa' FROM 'aaaahelaaloa')
FROM DUAL;
SELECT REPEAT('hello',5)
FROM DUAL;
SELECT STRCMP('abc','abe')
FROM DUAL;
#索引从1 开始
SELECT SUBSTRING('hello',2,2)
FROM DUAL;
# 2. 数值类型
SELECT CEIL(123.342),FLOOR(23.999),
MOD(12,5),MOD(12,-5),MOD(-12,5),MOD(-12,-5),RAND() * 100
FROM DUAL;
SELECT ROUND(123.567),ROUND(123.567,0),ROUND(123.567,1),ROUND(123.567,-2)
FROM DUAL;
SELECT TRUNCATE(123.967,0),TRUNCATE(123.567,1),TRUNCATE(123.567,-1)
FROM DUAL;
# 3. 日期类型
SELECT employee_id,hire_date
FROM employees
WHERE hire_date = '1993-01-13';
#如何获取当前的年月日、时分秒、年月日时分秒
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME(),NOW(),SYSDATE()
FROM DUAL;
/*
insert into employees(employee_id,last_name,hire_date)
values(304,'Tom',curdate())
*/
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME())
FROM DUAL;
SELECT DAYOFWEEK(CURDATE()),WEEKDAY(NOW()),DAYNAME(CURDATE())
FROM DUAL;
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR)
FROM DUAL;
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR); #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH); #需要单引号
#显式操作
#格式化：日期 ---> 字符串
#DATE_FORMAT(datetime ,fmt)
SELECT DATE_FORMAT(NOW(),'%Y/%M/%d %h:%i:%s')
FROM DUAL;
#解析： 字符串 ---> 日期
#STR_TO_DATE(str, fmt)
SELECT STR_TO_DATE('2021/May/26 09:54:17','%Y/%M/%d %h:%i:%s')
FROM DUAL;
#隐式操作：日期类型、字符串类型、数值类型之间存在隐式的转换。
# 4. 流程控制
#结构1 ： if
SELECT employee_id,last_name,salary,IF(salary > 10000,'高工资','低工资') "details",
IF(commission_pct IS NOT NULL,commission_pct,0) "details1"
FROM employees;
#结构2： ifnull
SELECT employee_id,last_name,salary,
IF(commission_pct IS NOT NULL,commission_pct,0) "details",
IFNULL(commission_pct,0) "details1",
salary * (1 + commission_pct),salary * (1 + IFNULL(commission_pct,0))
FROM employees;
#结构3：case when ... then ... when ... then ... else ... end 类似于if-else if - .. -else 的多选一
SELECT employee_id,last_name,salary,CASE WHEN salary > 15000 THEN '高富帅'
WHEN salary > 10000 THEN '潜力股'
WHEN salary > 5000 THEN '打工人'
ELSE '小屌丝' END "details"
FROM employees;
SELECT employee_id,last_name,department_id,CASE WHEN department_id = 10 THEN '10号部门'
WHEN department_id = 20 THEN '20号部门'
WHEN department_id = 30 THEN '30号部门'
END "details"
FROM employees;
#结构4：case ... when ... then ... when ... then ... else ... end 类似于switch-case
SELECT employee_id,last_name,department_id,CASE department_id WHEN 10 THEN '10号部门'
WHEN 20 THEN '20号部门'
WHEN 30 THEN '30号部门'
ELSE '其他部门' END "details"
FROM employees;
/*
**练习：查询部门号为 10,20, 30 的员工信息,
若部门号为 10, 则打印其工资的 1.1 倍,
20 号部门, 则打印其工资的 1.2 倍,
30 号部门打印其工资的 1.3 倍数。
*/
SELECT employee_id,last_name,salary,department_id,CASE department_id WHEN 10 THEN salary * 1.1
WHEN 20 THEN salary * 1.2
WHEN 30 THEN salary * 1.3
END "details"
FROM employees
WHERE department_id IN (10,20,30);
# 5. 其它函数
SELECT DATABASE(),VERSION(),USER()
FROM DUAL;
SELECT PASSWORD('abcd'),MD5(MD5('abcd'))
FROM DUAL;
08-分组函数
#08-分组函数
#上篇：常见的5个组函数：avg() / sum() / min() / max() / count()
#1. avg() / sum(): 只适用于数值类型的字段
SELECT AVG(salary),SUM(salary)#,avg(last_name),sum(last_name),sum(hire_date)
FROM employees;
#2. min()/ max():适用于数值类型、字符串类型、日期类型的字段
SELECT MIN(salary),MAX(salary),MIN(employee_id),MIN(last_name),MIN(hire_date),MAX(hire_date)
FROM employees;
#3.count(): 适用于数值类型、字符串类型、日期类型的字段
# 在计算个数时，null值不考虑在内。
SELECT COUNT(salary),COUNT(last_name),COUNT(hire_date),COUNT(commission_pct)
FROM employees;
#需求：查询员工表中员工的个数
SELECT COUNT(employee_id),COUNT(last_name),COUNT(1),COUNT(2),COUNT(*)
FROM employees;
DESC employees;
#count 与 avg 、 sum 三者的关系: avg = sum / count
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct)/107
FROM employees;
#需求：查询员工的平均奖金级别？
SELECT SUM(commission_pct)/COUNT(IFNULL(commission_pct,10))
FROM employees;
#下篇：group by / having
#查询公司的平均工资
SELECT AVG(salary)
FROM employees;
#查询各个部门的平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
#与上一个需求执行的结果是相同的
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;
####
#如下的语句是正确的：
SELECT AVG(salary)
FROM employees
GROUP BY department_id
#错误的：
SELECT department_id,AVG(salary)
FROM employees;
#结论：select中出现了组函数和非组函数的字段，那么非组函数的字段一定要声明在group by中。
#反之，声明在group by中的字段，可以不声明在select中。
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
#需求：查询部门最高工资比10000高的部门及其最高工资
#错误的
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id
#结论：如果过滤条件中出现了组函数，则需要使用having替换where实现过滤
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
#需求：查询10,20,30,40号部门中，部门最高工资比10000高的部门及其最高工资
#写法一：推荐！优于写法二，因为效率高
#结论：如果过滤条件1中出现了组函数，那么将过滤条件1声明在having中
# 如果过滤条件2中没有出现组函数，推荐将过滤条件2声明在where中。
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000
#写法二：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
AND department_id IN (10,20,30,40);
/*
小结：
#查询语句的结构1：
SELECT ...,....,...,....
FROM ....,...,....
WHERE 多表的连接条件 AND 非组函数的过滤条件
GROUP BY ...,....
HAVING 组函数的过滤条件
ORDER BY ... ASC/DESC,... ASC/DESC
LIMIT ...,....
#查询语句的结构2：
SELECT ...,...,...
FROM ... (LEFT OUTER / RIGHT OUTER )JOIN ... ON ...
(LEFT OUTER / RIGHT OUTER )JOIN ... ON ....
WHERE 非组函数的过滤条件
GROUP BY ...,....
HAVING 组函数的过滤条件
ORDER BY ... ASC/DESC,... ASC/DESC
LIMIT ...,....
*/
09-子查询
#09-子查询
#谁的工资比Abel的高？
#方式1：
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
#方式2：自连接
SELECT e1.last_name,e1.salary
FROM employees e1 JOIN employees e2
ON e1.`salary` > e2.`salary`
WHERE e2.`last_name` = 'Abel';
#方式3：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
)
# 子查询的概念： 外层：外查询、主查询 ; 内层：内查询、子查询
# 子查询的分类： 单行子查询、 多行子查询
#1. 单行子查询
# 可以使用的比较运算符有： = > >= < <= <> !=
#题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
)
AND salary > (
SELECT salary
FROM employees
WHERE employee_id = 143
);
#题目：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);
#技巧：书写子查询的方式：①从外往里写 ②从里往外写
#题目：查询最低工资大于60号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary ) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 60
)
#空值的情况：
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
#非法使用子查询
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
#2. 多行子查询
#多行子查询可以使用的比较操作符有：in all any
#in:
SELECT employee_id, last_name
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
#题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
)
# 题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
)
###################################################
#题目：查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
#题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式一：相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
#本部门的平均工资
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.`department_id`
);
#方式二：除了在group by 和limit 之外的位置都可以编写子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id) dept_avg_sal
WHERE e.department_id = dept_avg_sal.department_id
AND e.`salary` > dept_avg_sal.avg_sal;
#另例：查询员工的employee_id,last_name,要求按照department_name从小到大排序
SELECT employee_id,last_name
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.`department_id` = d.`department_id`
) DESC;
#EXISTS的使用
#题目：查询公司管理者的employee_id,last_name,job_id,department_id信息
#方式一：
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);
#方式二：
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
SELECT 'x'
FROM employees e2
WHERE e1.`employee_id` = e2.manager_id
);
10-子查询课后练习
#10-子查询课后练习
#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1700
);
#6.查询管理者是King的员工姓名和工资
#自连接
SELECT emp.last_name,emp.salary
FROM employees emp JOIN employees mgr
ON emp.`manager_id` = mgr.`employee_id`
WHERE mgr.last_name = 'King';
#子查询
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name = 'King'
);
#8.查询平均工资最低的部门信息
#方式一：
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) dept_avg_sal
)
)
#方式二：
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)
)
#方式三：
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1
)
)
#方式四：
SELECT d.*
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1
) dept_avg_sal
WHERE d.department_id = dept_avg_sal.department_id;
#9.查询平均工资最低的部门信息和该部门的平均工资（难）
#方式一：
SELECT d.*,dept_avg_sal.avg_sal
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1
) dept_avg_sal
WHERE d.department_id = dept_avg_sal.department_id;
#方式二：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) "avg_sal"
FROM departments d
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)
)
#10.查询平均工资最高的 job 信息
#11.查询平均工资高于公司平均工资的部门有哪些?
#12.查询出公司中所有 manager 的详细信息.
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
11-创建和管理表 —DDL
#11-创建和管理表 --DDL
#数据库对象：表、视图、存储过程、函数、触发器、同义词、索引等
# 1.创建库
CREATE DATABASE database0419;
SHOW DATABASES; #查看所有的数据库
# 2. 使用指定的数据库
USE database0419;
SHOW TABLES; #查看指定的库下包含哪些表
USE temp;
SHOW TABLES;
SELECT *
FROM employees;
# 3. 删除库
DROP DATABASE database0419;
# 4. 创建表
# 方式一："白手起家"的方式
CREATE TABLE emp1(
id INT,
last_name VARCHAR(15),
email VARCHAR(25),
salary DOUBLE(10,2),
hire_date DATE
);
DESC emp1;
SELECT *
FROM emp1;
#方式二：基于现有的表，创建新的表
CREATE TABLE emp2
AS
SELECT employee_id,last_name,salary
FROM employees ;
DESC emp2;
DESC employees;
#说明：使用此种方式创建表时，还可以将原有表中的数据复制到新表中。
SELECT *
FROM emp2;
#如下结构中select中列的别名，就作为新创建的表的字段名
CREATE TABLE emp3
AS
SELECT employee_id emp_id,last_name,salary AS sal
FROM employees ;
#查询失败
SELECT employee_id
FROM emp3;
#练习1：复制employees表，包含所有数据
CREATE TABLE employees_copy
AS
SELECT *
FROM employees;
SELECT *
FROM employees_copy;
SELECT *
FROM employees;
#练习2: 复制employees表，不包含任何数据
CREATE TABLE employees_copy_blank
AS
SELECT *
FROM employees
WHERE 1 = 2;
SELECT *
FROM employees_copy_blank;
# 5. 修改表
DESC emp3;
# 5.1 增加一个列
ALTER TABLE emp3
ADD email VARCHAR(25);
SELECT *
FROM emp3;
# 5.2 删除一个列
ALTER TABLE emp3
DROP email;
ALTER TABLE emp3
DROP COLUMN sal;
# 5.3 修改字段(类型、储值范围)
DESC emp3;
ALTER TABLE emp3
MODIFY last_name VARCHAR(30);
#通常不会修改字段的类型！！
#报错
ALTER TABLE emp3
MODIFY last_name INT;
# 5.4 重命名字段
DESC emp3;
ALTER TABLE emp3
CHANGE last_name lname VARCHAR(21);
# 6. 重命名表
RENAME TABLE emp3 TO myemp3;
SELECT *
FROM myemp3;
# 7. 删除表
DROP TABLE employees_copy;
# 8. 清空表: 清空表中的数据，但是表结构保留
SELECT *
FROM myemp3;
TRUNCATE TABLE myemp3;
/*
对比TRUNCATE TABLE 和 DELETE FROM ...
结论：TRUNCATE TABLE 一旦操作，就不可以回滚数据
DETELE FROM 支持删除表中的所有数据，可以回滚数据
测试：COMMIT 与 ROLLBACK的使用
1. COMMIT:表示提交数据。数据一旦被提交，就不可回滚。
ROLLBACK:表示回滚数据。 回滚操作，也只能回滚到最近的一次COMMIT之后。
2. 默认情况下，对数据表的操作(DDL、DML),都是在执行之后，默认提交数据的。
要想测试TRUNCATE TABLE 和 DETELE FROM的区别，需要关闭默认提交的行为：
SET autocommit = FALSE;
*/
CREATE TABLE myemp
AS
SELECT *
FROM employees;
SELECT *
FROM myemp;
COMMIT;
#首先测试DELETE FROM
SET autocommit = FALSE;
DELETE FROM myemp; #删除数据
SELECT *
FROM myemp;
ROLLBACK; #回滚数据
#接着测试TRUNCATE TABLE
COMMIT;
SET autocommit = FALSE;
TRUNCATE TABLE myemp; #清空表
SELECT *
FROM myemp;
#并没有将数据回滚成功
ROLLBACK; #回滚数据
#结论：以TRUNCATE TABLE为代表的DDL操作，都会在执行完以后，自动的COMMIT提交数据。
# 而且此提交行为不受SET autocommit = FALSE;的影响。所以，ROLLBACK行为对DDL操作都失效。
# 关于COMMIT、ROLLBACK涉及到数据库事务的操作
12-数据处理之增、删、改
#12-数据处理之增、删、改
# 1. 增/添加:INSERT
#方式1：一条一条的添加
DESC emp1;
SELECT *
FROM emp1;
INSERT INTO emp1
VALUES(1,'Tom','tom@126.com',3000,CURDATE());
INSERT INTO emp1
VALUES(2,'Tom1','tom@126.com',NULL,CURDATE());
#进化一步：
INSERT INTO emp1(id,last_name,salary,hire_date,email)
VALUES(3,'Tom2',5000,'2000-10-12','Tom2@126.com');
#没有声明的字段，在添加操作执行完后，值为null
INSERT INTO emp1(id,last_name,email)
VALUES(4,'Tom3','Tom3@126.com');
#方式2：基于现有的表
INSERT INTO emp1(id,last_name)
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (10,20,30)
SELECT *
FROM emp1;
DESC emp1;
DESC employees;
#2. 删除数据:delete from .... where ...
DELETE FROM emp1
WHERE id = 1;
#3. 修改数据: update ... set ..,.. where ...
UPDATE emp1
SET salary = 7000
WHERE id = 4;
UPDATE emp1
SET salary = 8000,hire_date = CURDATE()
WHERE id = 4;
13-约束
#13-约束（constraint)
DESC employees;
/*
1. 约束，针对表中的数据，在添加、删除、修改的过程中，进行的限制。
2. 约束的分类:
角度一（从声明的位置上）：列级约束 vs 表级约束
角度二（从作用的列的数量上）：单列约束 vs 多列约束
角度三（从功能上区分）：
not null :非空约束
unique: 唯一性约束
primary key:主键约束
foreign key:外键约束
check : 检查约束
default : 默认值约束
3. 设置约束的时机：情况1：在CREATE TABLE 的同时，给表的字段添加上约束。
情况2：通过 ALTER TABLE 的方式添加、删除约束
*/
# 1. not null :非空约束
# 创建表的同时，添加约束
CREATE TABLE emp3(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
hire_date DATE
);
DESC emp3;
#添加成功
INSERT INTO emp3(id,last_name,email,hire_date)
VALUES(1,'Tom','tom@126.com',CURDATE());
SELECT * FROM emp3;
#添加失败
INSERT INTO emp3(last_name,email,hire_date)
VALUES('Tom','tom@126.com',CURDATE());
#添加失败
INSERT INTO emp3(id,last_name,email,hire_date)
VALUES(2,NULL,'tom@126.com',CURDATE());
#在ALTER TABLE时，删除非空约束
ALTER TABLE emp3
MODIFY last_name VARCHAR(15) NULL;
DESC emp3;
#添加成功
INSERT INTO emp3(id,last_name,email,hire_date)
VALUES(2,NULL,'tom@126.com',CURDATE());
SELECT *
FROM emp3;
#在ALTER TABLE时，添加非空约束
ALTER TABLE emp3
MODIFY hire_date DATE NOT NULL;
#2. unique: 唯一性约束
# 创建表的同时，添加约束
CREATE TABLE emp4(
id INT UNIQUE, #列级约束
last_name VARCHAR(15),
email VARCHAR(25),
hire_date DATE,
#表级约束
CONSTRAINT emp4_email_uk UNIQUE(email)
);
DESC emp4;
#添加成功
INSERT INTO emp4(id,last_name,email,hire_date)
VALUES(1,'Tom','tom@126.com',CURDATE());
SELECT * FROM emp4;
#添加失败
INSERT INTO emp4(id,last_name,email,hire_date)
VALUES(1,'Tom','tom@126.com',CURDATE());
#添加失败
INSERT INTO emp4(id,last_name,email,hire_date)
VALUES(2,'Tom','tom@126.com',CURDATE());
#添加成功
INSERT INTO emp4(id,last_name,email,hire_date)
VALUES(2,'Tom1',NULL,CURDATE());
#添加成功
INSERT INTO emp4(id,last_name,email,hire_date)
VALUES(3,'Tom1',NULL,CURDATE());
#结论：声明为unique约束的字段，在添加或修改数据时，允许多次设置为null.
/*
在修改表的时候，如何删除约束？
1. 在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同
2. MySQL会给唯一约束的列上默认创建一个唯一索引
3. 删除唯一约束只能通过删除唯一索引的方式删除
4. 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
5. 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同，如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
*/
DESC emp4;
#删除索引，进而删除了唯一性约束
ALTER TABLE emp4
DROP INDEX emp4_email_uk;
ALTER TABLE emp4
DROP INDEX id;
#添加唯一性约束
ALTER TABLE emp4
ADD CONSTRAINT emp4_id_uk UNIQUE(id);
#3. primary key:主键约束
# 一个表中只能声明一个主键约束
# 主键约束，既满足唯一性，也满足非空性。
# 通过声明有主键约束的字段，可以确定表中的唯一的一条记录。
# 通常，在创建表的同时，都需要指名一个主键约束。
CREATE TABLE emp5(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15),
email VARCHAR(25),
hire_date DATE,
salary DOUBLE(10,2)
);
DESC emp5;
#添加成功
INSERT INTO emp5(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',2000);
SELECT *
FROM emp5;
#添加失败
INSERT INTO emp5(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',2000);
#添加失败
INSERT INTO emp5(id,last_name,email,salary)
VALUES(NULL,'Tom','tom@126.com',2000);
#开发中常见的声明方式：
CREATE TABLE emp6(
id INT AUTO_INCREMENT,
last_name VARCHAR(15),
email VARCHAR(25),
hire_date DATE,
salary DOUBLE(10,2),
#表级约束
CONSTRAINT emp6_id_pk PRIMARY KEY(id)
);
INSERT INTO emp6(last_name,email,salary)
VALUES('Tom','tom@126.com',2000);
SELECT *
FROM emp6;
#如何删除主键
ALTER TABLE emp5
DROP PRIMARY KEY;
DESC emp5;
#添加主键约束
ALTER TABLE emp5
ADD CONSTRAINT emp5_id_pk PRIMARY KEY(id);
#4. foreign key:外键约束
#作用：在表A的字段a上声明有一个外键约束，与表B中的字段b相关联。则字段a在insert等操作时，
# 其赋的值一定是字段b中出现过的数据。
#要求：要想能关联成功，必须要求字段b声音有主键约束或唯一性约束
CREATE TABLE dept7(
dept_id INT,
dept_name VARCHAR(10)
);
#添加外键约束失败
CREATE TABLE emp7(
id INT,
last_name VARCHAR(15),
dept_id INT,
#声明外键:表级约束
CONSTRAINT emp7_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept7(dept_id)
);
#补救措施：
ALTER TABLE dept7
ADD CONSTRAINT dept7_dept_id_pk PRIMARY KEY(dept_id);
DESC dept7;
#添加外键约束成功
CREATE TABLE emp7(
id INT,
last_name VARCHAR(15),
dept_id INT,
#声明外键:表级约束
CONSTRAINT emp7_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept7(dept_id)
);
#添加失败
INSERT INTO emp7(id,last_name,dept_id)
VALUES(1,'Tom',10);
#
INSERT INTO dept7(dept_id,dept_name)
VALUES(10,'IT');
#添加成功
INSERT INTO emp7(id,last_name,dept_id)
VALUES(1,'Tom',10);
SELECT *
FROM emp7;
#结论：在实际开发中，不建议在创建表时使用外键约束。
#5. 检查约束(check)
# 对mysql失效
CREATE TABLE emp8(
id INT,
last_name VARCHAR(15),
salary DOUBLE(10,2) CHECK(salary > 3000)
);
DESC emp8;
INSERT INTO emp8
VALUES(1,'Tom',4000);
#添加成功
INSERT INTO emp8
VALUES(2,'Tom1',2000);
SELECT *
FROM emp8;
#6.默认值约束（default）
CREATE TABLE emp9(
id INT,
last_name VARCHAR(15),
salary DOUBLE(10,2) DEFAULT 2500
);
DESC emp9;
INSERT INTO emp9
VALUES(1,'Tom',5000);
INSERT INTO emp9(id,last_name)
VALUES(1,'Tom');
SELECT *
FROM emp9;
若有收获，就点个赞吧
0 人点赞
