1、基础命令

不区分大小写；但建议关键字大写，表名、列名小写

  1. select version();#查看版本 在cmd中：mysql --version或-V
  3. show databases;#查看所有数据库
  5. use (database);#使用数据库
  7. show tables;#查看所有表
  9. show tables from (database);#从当前数据库查看另一个数据库的表
  11. select database();#显示当前数据库
  13. creat table 表名 (
  14.   id int,
  15.   name varchar(20)
  16. );#建表
  18. desc 表名;#查看表名
  19. show index from 表名；#查看表中的所有索引，包括主键、外键、唯一

单行注释：#注释文字 或
— 注释文字
多行注释：/ 注释文字/

2、DDL

2.1、库的管理

2.2.1、库的创建

语法：create database [if not exists]库名;

  1. #创建库books
  2. CREATE DATABASE books;
  3. CREATE DATABASE IF NOT EXISTS books;

2.2.2、库的修改

  1. #更改库的字符集
  2. ALTER DATABASE books CHARACTER SET gbk;

2.2.3、库的删除

  1. DROP DATABASE books;
  2. DROP DATABASE IF EXISTS books;

2.2、表的管理

2.2.1、创建：create

create table 表名(
列名 列的类型【（长度）约束】,
列名 列的类型【（长度）约束】,
…
列名 列的类型【（长度）约束】
);

  1. #创建表book
  2. CREATE TABLE book(
  3.     id INT,
  4.     bName VARCHAR(20),
  5.     author_id INT,
  6.     publishDate DATETIME
  7. );

2.2.2、修改：alter

  1. #修改列名
  2. ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
  4. #修改列的类型或约束
  5. ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
  7. #添加新列
  8. ALTER TABLE book ADD COLUMN price DOUBLE;
  10. #删除列
  11. ALTER TABLE book DROP COLUMN price;
  13. #修改表名
  14. ALTER TABLE book RENAME TO book_author;

2.2.3、删除：drop

  1. DROP TABLE book;
  2. DROP TABLE IF EXISTS book;

通用的写法：

  1. DROP DATABASE IF EXISTS 旧库名;
  2. CREATE DATABASE 新库名;
  4. DROP TABLE IF EXISTS 旧表名;
  5. CREATE TABLE 表名();

2.2.4、表的复制

  1. #1、仅仅复制表的结构
  2. CREATE TABLE copy LIKE book;
  4. #2、复制表的结构和数据
  5. CREATE TABLE copy2
  6. SELECT * FROM book;
  8. #3、只复制部分数据
  9. CREATE TABLE copy3
  10. SELECT id,bName
  11. FROM book WHERE id=5; 
  13. #4、仅仅复制某些字段
  14. CREATE TABLE copy4
  15. SELECT id,bName
  16. FROM book WHERE 0;

3、DML

3.1、插入语句

方式一：经典的插入
语法：insert into 表名（列名,…）values(值1,…);

  1. #1、插入的值的类型要与列的类型一致或兼容
  2. INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
  3. VALUES(13,'杨幂','女','1990-4-8','18545615645',NULL,5);
  5. #2、不可以为null的列必须插入值，可以为null的列如何插入值？
  6. #方式一：
  7. INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
  8. VALUES(13,'杨幂','女','1990-4-8','18545615645',NULL,5);
  9. #方式二：
  10. INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id)
  11. VALUES(13,'杨幂','女','1990-4-8','18545615645',5);
  13. #3、列的顺序可以调换
  14. INSERT INTO beauty(id,sex,NAME,boyfriend_id)
  15. VALUES(13,'女','杨幂',5);
  17. #列数和值的个数必须一致
  18. INSERT INTO beauty(id,sex,NAME,boyfriend_id)
  19. VALUES(13,'女','杨幂',5);
  21. #可以省略列名，默认所有列，而且列的顺序和表中的顺序一致
  22. INSERT INTO beauty 
  23. VALUES(13,'杨幂','女','1990-4-8','18545615645',NULL,5);

方式二：
语法：
insert into 表名
set 列名=值，列名=值…

  1. INSERT INTO beauty
  2. SET id=19,NAME='杨幂',phone='1995454545';

方式一和方式二比较：

  1. #方式一支持插入多行，方式二不支持
  2. INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
  3. VALUES(13,'杨幂','女','1990-4-8','18545615645',NULL,5)
  4. ,(14,'杨幂1','女','1990-4-8','18545615645',NULL,5)
  5. ,(15,'杨幂2','女','1990-4-8','18545615645',NULL,5);
  6. #方式一支持子查询，方式二不支持
  7. INSERT INTO beauty(id,NAME,phone)
  8. SELECT id,boyname,'545454545'
  9. FROM boys WHERE id<3;

3.2、修改语句

  1. #修改单表的记录
  2. #案例：修改beauty表中的姓杨的女神的电话为55555555
  3. UPDATE beauty SET phone='55555555'
  4. WHERE NAME LIKE '杨%';
  5. #案例：修改beauty表中的姓杨的女神的电话为55555555，id为5
  6. UPDATE beauty SET phone='55555555',SET id=5
  7. WHERE NAME LIKE '杨%';
  9. #修改多表的记录
  10. #案例：修改张无忌的女朋友的手机号为114
  11. UPDATE boys bo
  12. INNER JOIN beauty b ON bo.id=b.boyfriend_id 
  13. SET b.phone=114
  14. WHERE bo.boyName='张无忌';
  15. #修改没有男朋友的女神的男朋友编号为2
  16. UPDATE boys bo
  17. RIGHT JOIN beauty b ON bo.id=b.boyfriend_id 
  18. SET b.boyfriend_id=2
  19. WHERE b.id IS NULL;

3.3、删除语句

方式一：delete（支持回滚）
语法：
1、单表的删除【*】
delete from 表名 where 筛选条件
2、多表的删除【补充】
sql92语法：
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件；
sql99语法：
delete 表1的别名,表2的别名
from 表1 别名
inner | left | right join 表2 别名 on 连接条件
where 筛选条件

  1. #单表的删除
  2. #删除手机号以9结尾的女神信息
  3. DELETE FROM beauty WHERE phone LIKE '%9';
  5. #多表的删除
  6. #删除张无忌女朋友的信息
  7. DELETE b
  8. FROM beauty b
  9. INNER JOIN boys bo ON b.boyfriend_id=bo.id
  10. WHERE bo.boyName='张无忌';
  11. #删除黄晓明以及它女盆友的信息
  12. DELETE b,bo
  13. FROM beauty b
  14. INNER JOIN boys bo ON b.boyfriend_id=bo.id
  15. WHERE bo.boyName='黄晓明';

方式二：truncate（不支持回滚）
语法：truncate table 表名；

  1. #案例：将男神信息全部删除 
  2. TRUNCATE TABLE boys;

delete和truncate的区别：
1、delete可以加where条件，truncate不能加
2、truncate删除，效率高一点
3、假如要删除的表中有自增长列，如果用delete删除后，在插入数据，自增长列的值从断点开始，而truncate删除后在插入数据，自增长列的值从1开始
4、truncate 删除没有返回值，delete有返回值
5、truncate 删除不能回滚，delete可以回滚

4、DQL

进阶1：基础查询

语法：select 查询列表 from 表名;
特点：
1.查询列表可以是：表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格

  1. #1.查询表中的单个字段
  2. SELECT last_name FROM employees;
  4. #2.查询表中的多个字段
  5. SELECT last_name,salary,email FROM employees;
  7. #3.查询表中的所有字段
  8. SELECT * FROM employees;

折中号 `，用于区分字段和关键字，可以不要。<br />SELECTNAME` FROM employees;

  1. #4.查询常量
  2. SELECT 100;
  3. SELECT 'jonh';
  5. #5.查询表达式
  6. SELECT 100%98;
  8. #6.查询函数
  9. SELECT VERSION();
  10. SELECT DATABASE();
  1. #7.起别名 (AS可以省略)
  2. SELECT 100%98 AS 结果; 
  3. SELECT last_name AS 姓,first_name AS 名 FROM employees;
  4. SELECT last_name 姓,first_name 名 FROM employees;
  5. #案例：查询salary，显示结果为 out put (out为关键字用引号引起来)
  6. SELECT salary AS "out put" FROM employees;

别名作用：
1.便于理解
2.如果要查询的字段有重名的情况，使用别名可以区分开来

  1. #8.去重
  2. #案例：查询员工表中涉及的所有部门编号
  3. SELECT DISTINCT department_id FROM employees;
  5. #9.+号的作用   CONCAT连接字符串
  6. #案例：查询员工的姓和名连接成一个字段，并显示为姓名
  7. SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

mysql中的+号仅仅只有一个功能：运算符
select 100+90; 190
select ‘123’+90; 213
select ‘john’+90; 90
select null+10; null
两个操作数都为数值型，则做加法运算,只要其中一方为字符型，则试图将字符型转换为数值型如果转换成功，则继续做加法运算，否则将字符型数值转换为0.只要其中一方为null，则结果为null.

  1. #如果commission_pct为null，则结果为0.
  2. SELECT IFNULL (commission_pct,0) AS 奖金率，commission_pct FROM employees;

进阶2：条件查询

语法：select 查询列表 from 表名 where 筛选条件;
分类：
1.按条件表达式筛选
条件运算符：> < = != <>(不等) >= <=
2.按逻辑表达式筛选
逻辑运算符：
作用：用于连接条件表达式
&& || ！
and or not
建议使用：and or not
3.模糊查询
like :与通配符使用
%:任意多个字符，包含0个
_：任意单个字符
between and：
1.使用between and 可以提高语句的简洁度
2.包含临界值
3.两个临界值不要调换
in：含义：判断某字段的值是否属于in列表中的某一项
特点：
1.使用in提高语句的简洁度
2.in列表的值类型必须一致或兼容
is null：仅仅可以判断null值，可读性较高
<=>:既可以判断null值,又可以判断普通值，可读性较低
=或<>不能用于判断null值
is null 或is not null 可以判断

1.按条件表达式筛选

  1. #案例：查询工资>12000的员工信息
  2. SELECT * FROM employees WHERE salary>12000;
  3. #案例2：查询部门编号不等于90号的员工名和部门编号
  4. SELECT last_name,department_id FROM employees WHERE department_id != 90;
  5. SELECT last_name,department_id FROM employees WHERE department_id <> 90;

2.按逻辑表达式筛选

  1. #案例1：查询工资在10000到20000之间的员工名、工资以及奖金
  2. SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary <=20000;
  3. #案例2：查询部门编号不是在90到100之间，或者工资高于15000的员工信息
  4. SELECT * FROM employees WHERE department_id<90 OR department_id>100 OR salary>15000;
  5. SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=100) OR salary>15000;

3.模糊查询

  1. #1.like
  2. #案例1：查询员工名中包含字符a的员工信息
  3. SELECT * FROM employees WHERE last_name LIKE '%a%';
  4. #案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
  5. SELECT last_name,salary FROM employees WHERE last_name LIKE '__e_a%'; 
  6. #案例3:查询员工名中第二个字符为_的员工名
  7. SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
  9. #2.between and
  10. #案例1：查询员工编号在100到120之间的员工信息
  11. SELECT * FROM employees WHERE employee_id >=100 AND employee <=120;
  12. SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
  14. #3.in
  15. #案例：查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
  16. SELECT last_name,job_id FROM employees WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');
  18. #4.is null
  19. #案例1：查询没有奖金的员工名和奖金率
  20. SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
  21. #案例2：查询有奖金的员工名和奖金率
  22. SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
  24. #安全等于 <=>
  25. #案例1：查询没有奖金的员工名和奖金率
  26. SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
  27. #案例2：查询工资等于12000的员工信息
  28. SELECT * FROM employees WHERE salary<=>12000;

进阶3：排序查询

语法：select 查询列表 from 表名 [where 筛选条件] order by 排序列表[asc|desc];
特点：
1、asc代表升序，desc代表降序，如果不写，默认升序。
2、order by子句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by子句一般放在查询语句的最后面，limit子句除外

  1. #案例1：查询员工信息，要求工资从低到高排序
  2. SELECT * FROM employees ORDER BY salary ASC;
  3. SELECT * FROM employees ORDER BY salary ;
  4. #案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序
  5. SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;
  6. #案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】
  7. SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
  8. #案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
  9. SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;
  10. #案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
  11. SELECT LENGTH(last_name) 字节长度,last_name,salary FROM employees ORDER BY LENGTH(last_name) DESC;
  12. #案例6：查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
  13. SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

进阶4：常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高了代码的重用性
调用：select 函数名（实参列表）【from 表名】
特点：1、叫什么（函数名） 2、干什么（函数功能）
分类：
1、单行函数 如concat、length、ifnull等
2、分组函数 功能：做统计使用，又称为统计函数、聚合函数、组函数

1.字符函数

  1. #1.length 获取参数值的字节个数
  2. SELECT LENGTH('john');#4
  3. SELECT LENGTH('张三丰h');#10    utf8一个汉字占三个字节，gbk占两个
  5. #2.concat 拼接字符串
  6. SELECT CONCAT (last_name,'_',first_name) 姓名 FROM employees;
  8. #3.upper、lower
  9. SELECT UPPER('john');#JOHN
  10. SELECT LOWER('JOHN');#john
  11. #示例：将姓变大写，名变小写，然后拼接
  12. SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
  14. #4.substr、substring
  15. #截取从指定索引处后面的所有字符，注意索引从1开始
  16. SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;#陆展元
  17. #截取从指定索引处指定字符长度的字符，
  18. SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;#李莫愁
  19. #示例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
  20. SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put;
  22. #5.instr 返回子串第一次出现的索引，如果找不到返回0
  23. SELECT INSTR('李莫愁爱上了陆展元','陆展元') AS out_put;
  25. #6.trim  
  26. #去除前后空格
  27. SELECT TRIM('  陆展元    ') AS out_put;
  28. #去除前后指定字符
  29. SELECT TRIM('a' FROM 'aaa陆展元aaa') AS out_put;
  31. #7.lpad用指定的字符实现左填充指定长度
  32. SELECT LPAD('陆展元',5,'*') AS out_put;#**陆展元
  33. #长度不够从左截取指定长度字符
  34. SELECT LPAD('陆展元',2,'*') AS out_put;#陆展
  36. #8.rpad用指定的字符实现右填充指定长度
  37. SELECT RPAD('陆展元',5,'*') AS out_put; #陆展元**
  39. #9.replace 替换
  40. SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;#张无忌爱上了赵敏

2.数学函数

  1. #round 四舍五入
  2. SELECT ROUND(-1.55);#-2
  3. SELECT ROUND(1.567,2)#1.57
  5. #ceil向上取整，返回>=该参数的最小整数
  6. SELECT CEIL(1.02)#2
  7. SELECT CEIL(-1.02)#-1
  9. #floor向下取整，返回<=该参数的最大整数
  10. SELECT FLOOR(1.02)#1
  11. SELECT FLOOR(-1.02)#-2
  13. #truncate 截断
  14. SELECT TRUNCATE(1.6669,1)#1.6
  16. #mod取余数 mod(a,b):a-a/b*b
  17. SELECT MOD(10,-3);#1
  18. SELECT 10%3;

3.日期函数

  1. #now 返回当前系统日期+时间
  2. SELECT NOW();
  4. #curdate 返回当前系统日期，不包含时间
  5. SELECT CURDATE();
  7. #curtime 返回当前时间，不包含日期
  8. SELECT CURTIME();
  10. #可以获取指代的部分，年、月、日、小时、分钟、秒
  11. SELECT YEAR(NOW()) 年;
  12. SELECT YEAR('1998-4-1') 年;
  13. SELECT  YEAR(hiredate) 年 FROM employees;
  14. SELECT MONTH(NOW()) 月;#9
  15. SELECT  MONTHNAME(NOW()) 月;#September
  1. #str_to_date 将字符通过指定的格式转换成日期
  2. SELECT STR_TO_DATE('1998-4-8','%Y-%c-&d') AS out_put;
  4. #查询入职日期为1992-4-3的员工信息
  5. SELECT * FROM employees WHERE hiredate = '1992-4-3';
  6. SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
  8. #date_format 将日期转换成字符
  9. SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
  10. #查询有奖金的员工名和入职日期（xx月/xx日 xx年）
  11. SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期 FROM employees WHERE commission_pct IS NOT NULL;

image.png

4.其他函数

  1. SELECT VERSION();
  2. SELECT DATABASE();
  3. SELECT USER();

5.流程控制函数

  1. #1.if函数：if ease 的效果
  2. SELECT IF(10>5,'大','小');
  3. SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注 FROM employees;
  1. #2.case函数的使用一：switch case的效果
  2. #案例：查询员工的工资，要求
  3. #部门号=30，显示的工资为1.1倍，
  4. #部门号=40，显示的工资为1.2倍，
  5. #部门号=50，显示的工资为1.3倍，
  6. #其他部门，显示的工资为原工资。
  7. SELECT salary 原始工资,department_id,
  8. CASE department_id
  9. WHEN 30 THEN salary*1.1
  10. WHEN 40 THEN salary*1.2
  11. WHEN 50 THEN salary*1.3
  12. ELSE salary
  13. END AS 新工资 FROM employees;
  15. #3.case函数的使用二：类似于多重if
  16. #案例：查询员工的工资情况，要求
  17. #如果工资>20000,显示A级别
  18. #如果工资>15000,显示B级别
  19. #如果工资>10000,显示C级别
  20. #否则，显示D级别
  21. SELECT salary
  22. CASE 
  23. WHEN salary >20000 THEN 'A'
  24. WHEN salary >15000 THEN 'B'
  25. WHEN salary >10000 THEN 'C'
  26. ELSE 'D'
  27. END AS 工资级别
  28. FROM employees;

6.分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum求和、avg平均值、max最大值、min最小值、count计算个数
特点：
1、sum、avg一般用于处理数值型，max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独使用：一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是管group by后的字段

  1. #1.简单的使用
  2. SELECT SUM(salary) FROM employees;
  3. SELECT AVG(salary) FROM employees;
  4. SELECT MAX(salary) FROM employees;
  5. SELECT MIN(salary) FROM employees;
  6. SELECT COUNT(salary) FROM employees;
  8. #2.和distinct搭配
  9. SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
  11. #3.count函数的详细介绍
  12. SELECT COUNT(salary) FROM employees;
  13. SELECT COUNT(*) FROM employees;
  14. SELECT COUNT(1) FROM employees;
  15. #效率：
  16. MYISAM 存储引擎下，COUNT(*)的效率高
  17. INNODB 存储引擎下,COUNT(*)和COUNT(1)效率差不多，比COUNT(字段)要高一些
  19. #查询员工表中的最大入职日期和最小入职日期的相差天数
  20. SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;

进阶5：分组查询

语法：
select 分组函数,列（要求出现在group by的后面）
from 表名
[where 筛选条件]
group by 分组的列表
[order by 子句];
注意：查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
1、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
#分组函数做条件肯定是放在having子句中
#能用分组前筛选的，就优先考虑使用分组前筛选
2、group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用的较少）
3、也可以添加排序（排序放在整个分组查询的最后）

  1. #案例1:查询每个工种的最高工资
  2. SELECT MAX(salary),job_id
  3. FROM employees
  4. GROUP BY job_id;
  5. #案例2：查询每个位置上的部门个数
  6. SELECT COUNT(*),location_id
  7. FROM departments
  8. GROUP BY location_id;
  10. #添加分组前的筛选条件
  11. #案例1：查询邮箱中包含a字符的，每个部门的平均工资
  12. SELECT AVG(salary),department_id
  13. FROM employees
  14. WHERE email LIKE '%a%'
  15. GROUP BY department_id;
  16. #案例2：查询有奖金的每个领导手下员工的最高工资
  17. SELECT MAX(salary),manager_id
  18. FROM employees
  19. WHERE commission_pct IS NOT NULL
  20. GROUP BY manager_id;
  22. #添加复杂的筛选条件
  23. #案例1：查询哪个部门的员工个数>2
  24. #1、查询每个部门的员工个数
  25. SELECT COUNT(*),department_id
  26. FROM employees
  27. GROUP BY department_id;
  28. #2、根据1的结果进行筛选，查询哪个部门的员工个数>2
  29. SELECT COUNT(*),department_id
  30. FROM employees
  31. GROUP BY department_id
  32. HAVING COUNT(*)>2;
  34. #案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
  35. #1、查询每个工种有奖金的员工的最高工资
  36. SELECT MAX(salary),job_id
  37. FROM employees
  38. WHERE commission_pct IS NOT NULL
  39. GROUP BY job_id;
  40. #2、根据1的结果继续筛选，最高工资>12000
  41. SELECT MAX(salary),job_id
  42. FROM employees
  43. WHERE commission_pct IS NOT NULL
  44. GROUP BY job_id
  45. HAVING MAX(salary)>12000;
  47. #案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
  48. #1、查询每个领导手下的员工最低工资
  49. SELECT MIN(salsry),manager_id
  50. FROM employees
  51. GROUP BY manager_id;
  52. #2、添加筛选条件：编号>102
  53. SELECT MIN(salsry),manager_id
  54. FROM employees
  55. WHERE manager_id>102
  56. GROUP BY manager_id;
  57. #3、添加筛选条件：最低工资>5000
  58. SELECT MIN(salsry),manager_id
  59. FROM employees
  60. WHERE manager_id>102
  61. GROUP BY manager_id
  62. HAVING MIN(salary)>5000;
  64. #按表达式或函数分组
  65. #案例：按员工姓名的长度分组。查询每一组的员工个数，筛选员工个数>5的有哪些
  66. #1、查询每个长度的员工个数
  67. SELECT COUNT(*),LENGTH(last_name) len_name
  68. FROM employees
  69. GROUP BY len_name;
  70. #添加筛选条件
  71. SELECT COUNT(*),LENGTH(last_name) len_name
  72. FROM employees
  73. GROUP BY len_name
  74. HAVING COUNT(*)>5;
  76. #按多个字段分组
  77. #案例：查询每个部门每个工种的员工的平均工资
  78. SELECT AVG(salary),department_id,job_id
  79. FROM employees
  80. GROUP BY department_id,job_id;
  81. #添加排序
  82. #案例：查询每个部门每个工种的员工的平均工资，并按平均工资的高低显示
  83. SELECT AVG(salary),department_id,job_id
  84. FROM employees
  85. GROUP BY department_id,job_id
  86. ORDER BY AVG(salary) DESC;

进阶6：连接查询

含义：又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔积现象：表1有m行，表2有n行，结果=mn行
发生现象：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
按年代分类：
sql92标准：仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
*比较：sql99支持的较多，sql99实现连接条件和筛选条件的分离
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接（mysql不支持）
交叉连接

1.sql92标准

1、等值连接

多表等值连接的结果为多表的交集部分
#n表连接，至少需要n-1个连接条件
#多表的顺序没有要求
#一般需要为表起别名
#可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

  1. #案例：查询员工名和对应的部门名
  2. SELECT last_name,department_name
  3. FROM employees,departments
  4. WHERE employees.`department_id`=departments.`department_id`;

为表起别名
#1、提高语句的简洁度
#2、区分多个重名的字段
#注意：如果为表起别名，则查询的字段就不能使用原来的表名去限定

  1. #查询员工名、工种号、工种名
  2. SELECT last_name,e.job_id,job_title
  3. FROM employees e,jobs j
  4. WHERE e.`job_id`=j.`job_id`;
  1. #添加筛选
  2. #查询有奖金的员工名和部门名
  3. SELECT last_name,department_name,commission_pct
  4. FROM employees e,departments d
  5. WHERE e.`department_id`=d.`department_id`
  6. AND e.`commission_pct` IS NOT NULL
  7. #查询城市名中第二个字符为o部门名和城市名
  8. SELECT department_name,city
  9. FROM departments d,locations l
  10. WHERE d.location_id=l.loation_id
  11. AND city LIKE '_o%';
  13. #添加分组
  14. #查询每个城市的部门个数
  15. SELECT COUNT(*) 个数，city
  16. FROM departments d,locations l
  17. WHERE d.location_id=l,location_id
  18. GROUP BY city;
  19. #查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
  20. SELECT department_name,MIN(salary)
  21. FROM departments d,employees e
  22. WHERE d.department_id=e.department_id
  23. AND commission_pct IS NOT NULL
  24. GROUP BY department_name;
  26. #添加排序
  27. #查询每个工种的工种名和员工的个数，并且按员工的个数降序
  28. SELECT job_title,COUNT(*)
  29. FROM employees e,jobs j
  30. WHERE e.job_id=j.job_id
  31. GROUP BY job_title
  32. ORDER BY COUNT(*) DESC;
  34. #实现三表连接
  35. #查询员工名、部门名和所在的城市
  36. SELECT last_name,department_name,city
  37. FROM employees e,departments d,locations l
  38. WHERE e.department_id=d.departmnet_id 
  39. AND d.location_id=l.location_id;

2、非等值连接

  1. #查询员工的工资和工资级别
  2. SELECT salary,grade_level
  3. FROM employees e,job_grades g
  4. WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

3、自连接

  1. #查询员工名和上级的名称
  2. SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
  3. FROM employees e,employees m
  4. WHERE e.manager_id=m.manager_id;

2.sql99标准

语法：
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表];
分类：
内连接：inner
外连接：
左外：left [outer]
右外*：right [outer]
全外：full [outer]
交叉连接：cross

1、内连接

语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
特点：
1、添加排序、分组、筛选
2、inner可以省略
3、筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
4、inner join 连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

1.1、等值连接
  1. #查询员工名、部门名
  2. SELECT last_name,department_name
  3. FROM employees e
  4. INNER JOIN departments d
  5. ON e.department_id=d.department_id;
  7. #添加筛选
  8. #查询名字中包含a的员工名和工种名
  9. SELECT last_name,job_title
  10. FROM employees e
  11. INNER JOIN jobs j
  12. ON e.job_id=j.job_id
  13. WHERE e.last_name LIKE '%a%';
  15. #添加分组+筛选
  16. #查询部门个数>3的城市名和部门个数
  17. #1、查询每个城市的部门个数
  18. #2、在1的结果上筛选满足条件的
  19. SELECT city,COUNT(*) 部门个数
  20. FROM departments d
  21. INNER JOIN locations l
  22. ON d.location_id=l.location_id
  23. GROUP BY city
  24. HAVING COUNT(*)>3;
  26. #添加排序
  27. #查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序
  28. #1、查询每个部门的员工个数
  29. SELECT COUNT(*),department_name
  30. FROM employees e
  31. INNER JOIN departments d
  32. ON e.department_id= d.department_id
  33. GROUP BY department_name;
  34. #2、在1的结果上筛选员工个数大于3的记录，并排序
  35. SELECT COUNT(*),department_name
  36. FROM employees e
  37. INNER JOIN departments d
  38. ON e.department_id= d.department_id
  39. GROUP BY department_name
  40. HAVING COUNT(*)>3
  41. ORDER BY COUNT(*) DESC; 
  43. #查询员工名、部门名、工种名、并按部门名排序
  44. SELECT last_name,department_name,job_title
  45. FROM employees e
  46. INNER JOIN departments d ON e.department_id=d.department_id
  47. INNER JOIN jobs j ON e.job_id=j.job_id
  48. ORDER BY department_name DESC;

1.2、非等值连接
  1. #查询员工的工资级别
  2. SELECT salary,grade_level
  3. FROM employees e
  4. JOIN job_grades g
  5. ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;
  7. #查询工资级别的个数>20的个数，并且按工资级别按摩
  8. SELECT COUNT(*),grade_level
  9. FROM employees e
  10. JOIN job_grades g
  11. ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
  12. GROUP BY grade_level
  13. HAVING COUNT(*)>20
  14. ORDER BY grade_level DESC;

1.3、自连接
  1. #查询员工的名字、上级的名字
  2. SELECT e.last_name,m.last_name
  3. FROM employees e
  4. JOIN employees m
  5. ON e.manager_id=m.employee_id;
  7. #查询姓名中包含字符k的员工的名字、上级的名字
  8. SELECT e.last_name,m.last_name
  9. FROM employees e
  10. JOIN employees m
  11. ON e.manager_id=m.employee_id
  12. WHERE e.last_name LIKE '%k%';

2、外连接

应用场景：用于查询一个表中有，另一个表中没有的记录
特点：
1、外连接的查询结果为主表中的所有记录，
如果从表中有和它匹配的，则显示匹配的值，
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有的（mysql不支持全外连接）

  1. #查询哪个部门没有员工
  2. #左外
  3. SELECT d.*,e.employee_id
  4. FROM departments d
  5. LEFT OUTER JOIN employees e
  6. ON e.department_id=d.department_id
  7. WHERE e.employee_id IS NULL;
  8. #右外
  9. SELECT d.*,e.employee_id
  10. FROM employees e
  11. RIGHT OUTER JOIN departments d
  12. ON e.department_id=d.department_id
  13. WHERE e.employee_id IS NULL;
  15. #全外
  16. SELECT d.*,e.employee_id
  17. FROM employees e
  18. FULL OUTER JOIN departments d
  19. ON e.department_id=d.department_id

3、交叉连接

  1. #相当于笛卡尔乘积
  2. SELECT b.*,bo.*
  3. FROM beauty b
  4. CROSS JOIN boys bo;

4、总结
image.png
image.png

进阶7：子查询

含义：出现在其他语句的select语句，称为子查询或内查询，外部出现的查询语句，称为外查询或是主查询。
分类：
按子查询出现的位置：
selecth后面：
仅仅支持子查询
from后面：
支持表子查询
where或having后面：
1、标量子查询（单行）
2、列子查询（多行） *
3、行子查询
exists后面(相关子查询)：
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

一、where或having后面

1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
列子查询，一般搭配着多行操作符使用
in、any/some、all
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1、标量子查询

  1. #案例1：谁的工资比Abel高？
  2. #①查询Abel的工资
  3. SELECT salary
  4. FROM employees 
  5. WHERE last_name='Abel';
  6. #②查询员工的信息，满足salary>①的结果
  7. SELECT *
  8. FROM employees
  9. WHERE salary>(
  10.     SELECT salary
  11.     FROM employees 
  12.     WHERE last_name='Abel';
  13. )
  15. #案例2、返回job_id与141号员工相同，salary比143号员工多的姓名，job_id和工资
  16. #1、查询141号员工的job_id
  17. SELECT job_id 
  18. FROM employees
  19. WHERE employee_id=141;
  20. #2、查询143号员工的salary
  21. SELECT salary
  22. FROM employees
  23. WHERE employee_id=143;
  24. #3、查询员工的姓名，job_id=#1并且salary>#2;
  25. SELECT last_name,job_id,salary
  26. FROM employees
  27. WHERE job_id=(
  28.     SELECT job_id 
  29.     FROM employees
  30.     WHERE employee_id=141
  31. ) AND salary >(
  32.     SELECT salary
  33.     FROM employees
  34.     WHERE employee_id=143
  35. )
  37. #案例3、返回公司工资最低员工的last_name,job_id和salary
  38. #1、查询公司的最低工资
  39. SELECT MIN(salary)
  40. FROM employees;
  41. #2、查询last_name,job_id和salary,要求salary=#1
  42. SELECT last_name,job_id,salary
  43. FROM employees
  44. WHERE salary=(
  45.     SELECT MIN(salary)
  46.     FROM employees
  47. )
  49. #案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
  50. #1、查询50号部门的最低工资
  51. SELECT MIN(salary)
  52. FROM employees
  53. WHERE department_id=50;
  54. #2、查询每个部门的最低工资
  55. SELECT MIN(salary),department_id
  56. FROM employees
  57. GROUP BY department_id;
  58. #3、在#2的基础上筛选，满足min(salary)>#1
  59. SELECT MIN(salary),department_id
  60. FROM employees
  61. GROUP BY department_id
  62. HAVING MIN(salary)>(
  63.     SELECT MIN(salary)
  64.     FROM employees
  65.     WHERE department_id=50
  66. )

2、列子查询

  1. #案例1：返回location_id是1400或1700的部门中的所有员工姓名
  2. #1、查询location_id是1400或1700的部门编号
  3. SELECT DISTINCT department_id
  4. FROM departments
  5. WHERE location_id IN(1400,1700);
  6. #2、查询员工的姓名，要求部门号是#1列表中的某一个
  7. SELECT last_name
  8. FROM employees
  9. WHERE department_id IN(
  10.     SELECT DISTINCT department_id
  11.     FROM departments
  12.     WHERE location_id IN(1400,1700)
  13. );
  14. #或
  15. SELECT last_name
  16. FROM employees
  17. WHERE department_id =ANY(
  18.     SELECT DISTINCT department_id
  19.     FROM departments
  20.     WHERE location_id IN(1400,1700)
  21. );
  23. #案例2:返回其他工种中比job_id为'IT_prog'工种任一工资低的员工的员工号、姓名、job_id以及salary
  24. #1、查询job_id为'IT_prog'工种任一工资
  25. SELECT DISTINCT salary
  26. FROM employees
  27. WHERE job_id='IT_prog';
  28. #查询员工号、姓名、job_id以及salary，salary<(#1)的任意一个
  29. SELECT last_name,employee_id,job_id,salary
  30. FROM employees
  31. WHERE salary<ANY(
  32.     SELECT DISTINCT salary
  33.     FROM employees
  34.     WHERE job_id='IT_prog'
  35. ) AND job_id <> 'IT_PROG';
  36. #或
  37. SELECT last_name,employee_id,job_id,salary
  38. FROM employees
  39. WHERE salary<(
  40.     SELECT MAX(salary)
  41.     FROM employees
  42.     WHERE job_id='IT_prog'
  43. ) AND job_id <> 'IT_PROG';
  45. #案例3:返回其他工种中比job_id为'IT_prog'工种所有工资都低的员工的员工号、姓名、job_id以及salary
  46. SELECT last_name,employee_id,job_id,salary
  47. FROM employees
  48. WHERE salary<ALL(
  49.     SELECT DISTINCT salary
  50.     FROM employees
  51.     WHERE job_id='IT_prog'
  52. ) AND job_id <> 'IT_PROG';
  53. #或
  54. SELECT last_name,employee_id,job_id,salary
  55. FROM employees
  56. WHERE salary<(
  57.     SELECT MIN(salary)
  58.     FROM employees
  59.     WHERE job_id='IT_prog'
  60. ) AND job_id <> 'IT_PROG';

3、行子查询

  1. #查询员工编号最小并且工资最高的员工信息
  2. SELECT *
  3. FROM employees
  4. WHERE (employee_id,salary)=(
  5.     SELECT MIN(employee_id),MAX(salary)
  6.     FROM employees
  7. );
  8. #1、查询最小的员工编号
  9. SELECT MIN(employee_id)
  10. FROM employees;
  11. #2、查询最高的工资
  12. SELECT MAX(salary)
  13. FROM employees;
  14. #3、查询员工编号最小并且工资最高的员工信息
  15. SELECT *
  16. FROM employees
  17. WHERE employee_id=(
  18.     SELECT MIN(employee_id)
  19.     FROM employees
  20. )AND salary=(
  21.     SELECT MAX(salary)
  22.     FROM employees
  23. );

二、select的后面

#仅仅支持标量子查询

  1. #查询每个部门的员工个数
  2. SELECT d.*,(
  3.     SELECT COUNT(*)
  4.     FROM employees e
  5.     WHERE e.department_id=d.department_id
  6. ) 个数
  7. FROM departments d;
  9. #查询员工号=102的部门名
  10. SELECT (
  11.     SELECT department_name
  12.     FROM departments d
  13.     INNER JOIN employees e
  14.     ON d.department_id=e.department_id
  15.     WHERE e.employee_id =102
  16. );

三、from的后面

将子查询结果充当一张表，要求必须起别名

  1. #查询每个部门的平均工资的工资等级
  2. #1、查询每个部门的平均工资
  3. SELECT AVG(salary),department_id
  4. FROM employees
  5. GROUP BY department_id;
  6. #2、连接#1的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
  7. SELECT ag_dep.*,grade_level
  8. FROM (
  9.     SELECT AVG(salary) ag,department_id
  10.     FROM employees
  11.     GROUP BY department_id
  12. ) ag_dep
  13. INNER JOIN job_grades g
  14. ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

四、exists的后面(相关子查询)

语法：
exists(完整的查询语句)
结果：1或0

  1. #查询有员工的部门名
  2. #exists
  3. SELECT department_name
  4. FROM departments d
  5. WHERE EXISTS(
  6.     SELECT *
  7.     FROM employees e
  8.     WHERE d.department_id=e.department_id
  9. );
  10. #in
  11. SELECT department_name
  12. FROM departments d
  13. WHERE d.department_id IN(
  14.     SELECT *
  15.     FROM employees 
  16. );

进阶8：分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表 ⑦
from 表 ①
【join type join 表2 ②
on 连接条件 ③
where 筛选条件 ④
group by 分组字段 ⑤
having 分组后的筛选 ⑥
order by 排序的字段】 ⑧
limit 【offset】size; ⑨
offset：要显示条目的起始索引（从0开始）
size：要显示的条目个数
特点：
1、limit语句放在查询语句的最后
2、公式:
要显示的页数page，每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;

  1. #查询前五条的员工信息
  2. SELECT * FROM employees LIMIT 0,5;
  3. SELECT * FROM employees LIMIT 5;
  5. #查询第11条到第25条
  6. SELECT * FROM employees LIMIT 10,15;
  8. #查询有奖金的员工信息，并且工资较高的前十名显示出来
  9. SELECT * FROM employees
  10. WHERE commission_pct IS NOT NULL
  11. ORDER BY salary DESC
  12. LIMIT 10;

进阶9：联合查询

union:联合 合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
…
应用场景：
要查询的结果来自于多个表，且表之间没有之间的连接关系，但查询的信息一致时
特点：
1、要求多条查询语句的查询列数是一致的
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项

  1. #查询中国用户中男性的信息以及外国用户中男性的信息
  2. SELECT id, cname,csex FROM t_ca WHERE csex='男'
  3. UNION 
  4. SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';