• mysql数据库基础：DQL数据查询语言

    文章目录

    本章纯笔记

    DQL语言

    简介

    DQL（data query language）数据查询语言，专门用来查询数据。

    1、基础查询

    1.1 语法

    查询指定表中的指定字段、常量等

    SELECT 查询列表 FROM 表名;

    • 1

    查询指定表中的所有字段、常量等

    SELECT * FROM 表名;

    • 1

    显示表结构

    DESC 表名;

    • 1

    1.2 基础查询特点

    1、查询列表可以是表中的字段、常量、表达式、函数

    2、查询的结果是一个虚拟的表格

    3、查询列表中的字段等都可以有多个，之间用逗号隔开

    1.3 着重号``（双反引号）的使用

    可以用来区分字段和关键字，如：

    SELECT `NAME` FROM 某表;

    • 1

    1.4 查询常量值

    SELECT 100;
SELECT 'John';

    • 1
    • 2

    1.5 查询表达式

    SELECT 100%98;

    • 1

    1.6 查询函数

    SELECT VERSION(); #VERSION()是函数，作用是显示mysql版本

    • 1

    1.7 为字段起别名

    #方式一 使用AS
SELECT 100%98 AS 结果;  # 结果：2

SELECT last_name AS 姓, first_name AS 名 FROM 表名; 

#方式二 可以直接省略AS
SELECT last_name 姓，first_name 名 FROM 表名;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    起别名好处与易错点

    1. 便于理解
    2. 如果查询的字段重名，使用别名可以区分
    3. 起的别名最好不要带空格，如果带了空格，则可以用双引号修饰
      例如：
    #假设要查询工资salary,显示结果为out put,中间带空格,则要用双引号将out put 引起来
SELECT salary AS "out put" FROM 表名;

    • 1
    • 2

    1.8 去重—distinct

    #将字段中重复出现的多余数据去除
SELECT DISTINCT 字段 FROM 表名;

    • 1
    • 2

    1.9 “+”号的使用

    作用：只有一个功能，作为运算符做加法

    #两个操作数均为数值型，则直接做加法运算
SELECT 100+100; 

#其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则作加法运算，如果失败，则将字符型数值转换成0
SELECT '123' + 90;

#只要其中一方为null,则结果肯定为null
SELECT null + 10;   #结果为null

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    2、条件查询

    2.1 语法

    where关键字用来给出查询条件。

    SELECT 
	查询列表;
FROM 
	表名;
WHERE
	筛选条件;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    2.2 分类

    2.2.1 按条件表达式筛选

    条件运算符：>（大于）< （小于） =（等于） !=(不等于) <>(不等于)

    #案例1：查询工资>12000的员工信息
SELECT
		*
FROM
		employees
WHERE
		salary>12000;  #条件
		
#案例2：查询部门编号不等于90号的员工名和部门编号
SELECT 
		last_name,
		department_id
FROM	
		employees
WHERE 	
		department_id <> 90;    #<> 也可以写成!=

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    2.2.2 按照逻辑表达式筛选

    逻辑运算符：

    逻辑运算符作用
    &&与
    ||或
    !非
    and单词形式的“与”
    or单词形式的“或”
    not单词形式的”非“

    作用：连接条件表达式

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

# 或者如下
select
		*
FROM
		employees
WHERE
		department_id NOT BETWEEN 90 AND 110
OR
		salary>15000;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    2.2.3 模糊查询

    1、like

    特点：一般和通配符搭配使用。

    通配符作用
    %表示任意多个字符
    _下划线，代表任意单个字符
    /转义字符
    #案例1：查询员工命中包含字符a的员工信息
SELECT
		*
FROM
		employees
WHERE
		last_name LIKE '%a%';

#案例2：查询员工名中第三个字符为e，第五个字符为a的员工名
SELECT
		last_name
FROM
		employees
WHERE
	last_name LIKE '__e_a%';

#查询员工名中第二个字符为_的员工名
SELECT
		last_name
FROM
		employees
WHERE
		last_name LIKE '_\_%';
#或者可以写成：last_name LIKE '_$_%' ESCAPE '$';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    2、between…and…

    含义：表示在两个表达式索取值的中间，如果前面加上not则表示不在这中间。

    特点：

    • 使用between and可以提高语句的简洁度
    • 包含临界值
    SELECT
		*
FROM	
		employees
WHERE
		employee_id >= 100 AND employee_id <= 120;
#上面代码和下面代码用处一样

SELECT	
		*
FROM
		employees
WHERE
		emoloyees_id between 100 AND 120;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    3、in

    含义：判断某字段的值是否属于in列表中的某一项。
    特点：

    • 可以提高语句简介度
    • in列表的值类型必须统一或兼容
    • 不支持in列表中的值加上通配符
    #查询员工的工种编号是IT_PROT,AD_VP中的一个员工名和工种编号
SELECT
		last_name,job_id
FROM
		employees
WHERE
		job_id IN('IT_PROT','AD_VP');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    4、is null 和is not null

    注意：
    = 或 != 或<>都不能用来判断null值，而通过is null和is not null 可以判断null值

    #查询没有奖金的员工名和奖金率
SELECT
		last_name,commission_pct
FROM
		employees
WHERE	
		commission_pct IS NULL;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    5、安全等于 <=>

    SELECT
		last_name,salary
FROM	
		employees
WHERE	
		salary <=> 12000;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    is null 和<=>区别：

    IS NULL：可以判断NULL值，可读性高
    <=>：既可以判断NULL值，也可以判断普通数值，可读性差。

    3、排序查询

    3.1 语法

    SELECT
		查询列表
FROM	
		表名
WHERE	
		筛选条件
ORDER BY
		排序列表(asc \ desc)
		#asc升序、desc降序，默认升序

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    3.2 实例演示

    #案例1：查询部门编号大于等于90的员工信息，按照入职时间的先后进行排序【按筛选条件】
SELECT
		 *
FROM
		employees
WHERE
		department_id>=90
ORDER BY
		hiredate  ASC;
		
#案例2：按年薪的高低显示员工的信息和 年薪【按表达式查询】
SELECT
		*,salary*12*(1 + IFNULL(commission_pct, 0)) 年薪
FROM
		employees
ORDER BY
		salary*12*(1 + IFNULL(commission_pct, 0)) DESC;
		
#案例3：按年薪的高低显示员工的信息和 年薪【按别名查询】
SELECT
		*,salary*12*(1 + IFNULL(commission_pct, 0)) 年薪
FROM
		employees
ORDER BY
		年薪 DESC;
		
#案例4：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT 
		LENGTH(last_name) 字节长度, last_name, salary
FROM 
		employees
ORDER BY
		LENGTH(last_name);

#案例5：查询员工的信息，要求先按照工资升序，再按照员工编号降序排序【按多个字段排序】
SELECT 
		*
FROM 
		employees
ORDER BY 	
		salary ASC, employees_id DESC;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    4、常见函数

    4.1 功能

    类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。

    4.2 好处

    1、隐藏实现细节，
    2、提高代码复用性

    4.3 调用语法

    SELECT 函数名(实参列表) 【FROM 表】;

    • 1

    4.4 分类：

    1、单行函数

    如：concat、length、isfull等

    2、分组函数

    做统计使用，又称为统计函数，聚合函数，组函数。

    4.5 单行函数介绍

    4.5.1 字符函数

    #length 获取参数值的字节个数
#----------------------------------------------
SELECT LENGTH('john');  #4
SELECT LENGTH('张三丰hahaha');  #15

#concat 拼接字符
#----------------------------------------------
SELECT CONCAT(last_name,'_',first_name) FROM employees;

#upper\lower  转换字母大小写
#----------------------------------------------
SELECT UPPER('john');
SELECT LOWER('joHN');

#substr\substring
#-----------------------------------------------
#注意索引从1开始，即第一个索引号为1
#截取指定索引后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) AS out_put;  #输出：陆展元
#截取指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS out_put;   #输出：李莫愁

#instr 返回子串第一次出现的索引，如果找不到则返回0
#-----------------------------------------------
SELECT INSTR('别瞎几把乱努力，多用用脑子','脑子') AS out_put; # 结果：12

#trim  去除两边指定字符
#-----------------------------------------------
SELECT LENGTH(TRIM('    张翠山'    )) AS out_put;  #结果：长度9，两边的空格去掉了，汉字字节为3byte，所以长度为9
SELECT TRIM('a' FROM 'aaaaaa张aaaaaaa翠山aaaaa') AS out_put; #结果：张aaaaaaa翠山   （去除了两边的a）

#lpad 用指定的字符实现左填充指定长度
#-----------------------------------------------
SELECT LPAD('殷素素',10,'*') AS out_put; # 结果：*******殷素素

#rpad 用指定的字符实现右填充指定长度
#-----------------------------------------------
SELECT RPAD('殷素素',10,'*') AS out_put; # 结果：殷素素*******

#replace 替换
#-----------------------------------------------
SELECT REPLACE('周芷若周芷若','周芷若','赵敏') AS out_put; /*用赵敏替代了周芷若*/
# 结果：赵敏赵敏

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43

    4.5.2 数学函数

    #round 四舍五入
#------------------------------------------------
SELECT ROUND(1.45); /*四舍五入*/ # 结果：1

SELECT ROUND(1.567,2); /*取小数点后两位*/ # 结果：1.57

#ceil 向上取整，返回大于等于该参数的最小整数
#------------------------------------------------
SELECT CEIL(-1.02); # 结果：-1

#floor 向下取整，返回小于等于该参数的最大整数
#------------------------------------------------
SELECT FLOOR(-9.99); # 结果：-10

#truncate 从小数位截断，不四舍五入
#------------------------------------------------
SELECT TRUNCATE(1.69999,1); # 结果：1.6

#mod 取余
#------------------------------------------------
SELECT MOD(10,3); # 1
# 补充：求余数的方法：假设MOD(a,b) = a-a/b*b;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    4.5.3 日期函数

    # now 返回当前系统日期+时间
SELECT NOW();
# 或这
SELECT SYSDATE();

# curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

# curtime 返回当前系统时间，不含日期
SELECT CURTIME();

# 获取指定部分，年，月，日，小时，分钟，秒
SELECT YEAR(NOW()) 年; # 结果：2022
SELECT YEAR('2022-1-1') 年; # 结果：2022

SELECT MONTH(NOW()) 月; # 4
SELECT MONTHNAME(NOW()) 月; # April

# last_day 获取当前月份的最后一天
SELECT LAST_DAY(SYSDATE());

# str_to_date:将字符通过指定的格式转换成日期（一般返回的都是字符类型，所以要用到这个函数将字符转换为标准的日期
SELECT STR_TO_DATE('1994-3-3','%Y-%c-%d') AS out_put; #输出：1994-03-3

# 例题
SELECT * FROM employees where hiredate = STR_TO_DATE('4-3 1994','%c-%d %Y');
#就是将4-3 1994转换成1994-4-3这种形式

# date_format：将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;

# 例题 查询有奖金的员工名和入职日期（xx月xx日 %y年）
SELECT last_name, DATE_FORMAT(hiredate, '%m月%d日 %y年') 入职日期;
FROM employees
WHERE commission_pct IS NOT NULL;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    4.5.4 其他函数

    #查询当前版本号
SELECT VERSION();

#查询用户
SELECT USER();

    • 1
    • 2
    • 3
    • 4
    • 5

    4.5.5 流程控制函数

    # if函数：
# 比较结果为真选左边，为假选右边
SELECT IF(10<5,'大','小'); # 小
# 例如：
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，喜喜') 备注 FROM employees; 

# case 函数
#使用1： case类似于switch的效果
/*
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1(结尾没有分号)
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end  AS 命名
FROM 表名;
*/
# 例如：
/* 案例：
查询员工的工资，有要求如下：
部门号=30，显示的工资为1.1倍
部门号=30，显示的工资为1.2倍
部门号=40，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
# 实现：
SELECT 
		salary 原始工资,department_id,
CASE 
		department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE 
		salary
END AS 新工资
FROM 
		employees;
# 新工资排在查询出来的表格最后

#使用2：类似于多重if
/*
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
*/

# 例如：
/*案例：
查询员工的工资情况
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示C级别
否则显示D级别
*/
# 实现
SELECT 
		salary 工资,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 
		'D'
END AS 工资级别
FROM 
		employees;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70

    4.6 分组函数

    sum求和，avg平均值，max最大值，min最小值，count计算个数

    #1、简单使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

#2、参数支持哪些类型
#sum和avg适合数值类型
#max，min，count适合任何类型

#3、分组函数都忽略null值

#4、和distinct搭配去重
SELECT SUM(DISTINCT salary) FROM employees;

#5、count函数的单独介绍
#一般使用count(*)用作统计行数，或者count(1)
SELECT COUNT(*) FROM employees;

#6、和分组函数一同查询的字段有限制，要求是group by后的字段

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    5、分组查询

    5.1 语法

    select 分组函数，列（要求同时出现在group by的后面）

from 表名

group by 分组的列表

order by 子句;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    5.2 简单使用

    #查询所有工作的最高工资
SELECT 
	MAX(salary),job_id
FROM 
	employees
GROUP BY 
	job_id;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    5.3 添加筛选条件

    #案例1
#查询邮件中存在a字母，所有部门的平均工资
SELECT 
	AVG(salary), department_id
FROM 
	employees
WHERE 
	email LIKE "%a%"  #添加筛选条件
GROUP BY 
	department_id;

#案例2
#查询每个部门的员工个数>2
SELECT 
	COUNT(*), department_id
FROM 
	employees
GROUP BY 
	department_id
HAVING 
	count(*)>2; #添加筛选条件

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    5.4 特点：

    1、筛选条件分为两类

    使用的关键字筛选时机数据源位置
    where分组前筛选原始表group by子句的前面
    having分组后筛选分组后的结果集group by子句的后面

    2、分组函数的条件肯定放在having子句中，能用分组前筛选就用分组前筛选

    3、where和having同样是用来筛选条件的，有什么区别
    （1）where和having都是做条件筛选的
    （2）where执行的时间比having要早
    （3）where后面不能出现组函数
    （4）having后面可以出现组函数
    （5）where语句要紧跟from后面
    （6）having语句要紧跟group by后面

    4、注意：
    （1）where后面一定【不能】出现组函数
    （2）如果select、having语句后面出现了组函数，那么select、having后面没有被组函数修饰的列，就必须出现在group by 后面 （非常重要）

    6、连接查询

    6.1 含义

    又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。

    笛卡尔积现象：表1 有m行，表2有n行，结果=m*n行

    发生原因：没有有效的连接条件

    如何避免：添加有效的连接条件

    6.2 分类

    按年代分类

    • sql192标准：仅支持内连接
    • sql199标准（推荐）：支持内连接，外连接（右外连接和左外连接，mysql中不支持全外连接，交叉连接）

    按功能分类

    • 内连接
    1. 等值连接
    2. 非等值连接
    • 外连接
    1. 右外连接
    2. 左外连接
    3. 全外连接
    • 交叉连接

    6.3 sql192标准

    6.3.1 等值连接

    #案例1
#查询员工名和对应的部门名
SELECT 
	last_name,department_name
FROM 
	employees,departments
WHERE 
	employees.`department_id`=departments.`department_id`;
#注意是反引号

#案例2
#为表起别名
/*
1、提高语句简洁度
2、区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能够使用原来的表名
*/
#查询员工名，公众号，工种名

SELECT 
	e.last_name,e.job_id,j.job_title
FROM 
	employees e, jobs j
WHERE 
	e.`job_id`=j.`job_id`;

#案例3
#加筛选
#查询有奖金的员工名，部门名
SELECT 
	last_name, department_name,commission_pct
FROM 
	employees e, departments d
WHERE 
	e.`department_id`=d.`department_id`
AND 
	e.`commission_pct` IS NOT NULL;

#案例3
#加分组
SELECT 
	count(*) 个数,city
FROM 
	departments d,locations l
WHERE 
	d.`location_id`=l.`location_id`
GROUP BY
	city;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48

    6.3.2 非等值连接

    就是将等值连接中的“=”换成了别的如>,<,between …and…等

    6.4 sql199标准

    6.4.1 语法

    select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组条件】
【having 筛选条件】
【order by 排序列表】

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    6.4.2 分类

    6.4.2.1 内连接： inner

    1、语法（inner可以省略）

    select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

    • 1
    • 2
    • 3
    • 4

    2、分类
    （1）等值连接

    #案例1：查询员工名，部门名
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
on e.`department_id` = d.`department_id`;

#案例2：查询名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE e.`last_name` LIKE '%e%';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    （2）非等值连接

    #查询员工的工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#工资在lowest_sal和highest_sal之间

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    （3）自连接

    #查询员工的名字，上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

    • 1
    • 2
    • 3
    • 4
    • 5

    6.4.2.2 外连接 outer

    1、使用情景：用于查询一个表中有，另一个表没有的记录。
    2、特点：
    外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null。
    在这里插入图片描述
    在这里插入图片描述

    外连接查询结果=内连接结果+主表中有而从表中没有的记录

    测试表

    #创建两个表
CREATE TABLE test_1(
	id INT,
	`name` VARCHAR(20),
	girl_id INT
);

CREATE TABLE test_2(
	id INT,
	`name` VARCHAR(20)
);

INSERT INTO test_1(id,name,girl_id)VALUE (1,"刘烨",4);
INSERT INTO test_1(id,name,girl_id)VALUE (2,"胡军",9);
INSERT INTO test_1(id,name,girl_id)VALUE (3,"邓超",7);
INSERT INTO test_1(id,name,girl_id)VALUE (4,"刘奕君",91);

INSERT INTO test_2(id,name) VALUE (6,"小红");
INSERT INTO test_2(id,name) VALUE (7,"孙俪");
INSERT INTO test_2(id,name) VALUE (8,"小黑");
INSERT INTO test_2(id,name) VALUE (2,"小绿");

desc test_1;
desc test_2;
show tables;

SELECT * from test_1;
SELECT * from test_2;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    3、分类：
    （1）左外：left 【outer】join
    left join左边的是主表。

    # 左外连接
SELECT a.girl_id, b.*
FROM test_1 a
LEFT OUTER JOIN test_2 b
ON a.`girl_id` = b.`id`

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述

    （2）右外：right 【outer】join
    right join右边的是从表。

    # 右外连接
SELECT a.girl_id, b.*
FROM test_1 a
RIGHT OUTER JOIN test_2 b
ON a.`girl_id` = b.`id`

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述
    （3）全外：full 【outer】join
    表取并集

    左外和右外交换两个表的顺序，可以实现同样的效果

    6.4.2.3 交叉连接 cross

    语法：

    SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

    • 1
    • 2
    • 3

    7、子查询

    7.1 含义

    出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句称为主查询或外查询

    7.2 特点

    子查询的思路就是，把第一个sql语句的查询结果，在第二个sql语句中使用，这时候第一条sql语句的结果，在第二条sql中就可以充当一个where条件中的一个值，或者充当一张虚拟的表。

    （1）子查询放在小括号内
    （2）子查询一般放在条件的右侧
    （3）子查询的执行优先于主查询执行

    7.3 分类

    • 子查询出现的位置
      （1）where或having后面*（标量子查询、列子查询、行子查询）
      （2）select 后面（仅支持标量子查询）
      （3）from后面（支持表子查询）
      （4）exists后面（支持表子查询）

    where/having后面*

    • 标量子查询（结果只有一行一列）
    # 例题一：查询job_id与141号员工相同，salary比143号员工多的员工的姓名，job_id，和工资

# 第(1)步：查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

# 第(2)步：查询143号的salary
SELECT salary
FROM employees
WHERE employee_id=143

# 第(3)步：查询员工的姓名，job_id，和工资，要求job_id=(1),并且salary>(2)
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
)

------------------------------------------------------
# 例题二：查询最低工资大于50号部门最低工资的部门id和其最低工资

# 第(1)步：查询50号部门的最低工资
SELECT 
	MIN(salary)
FROM 
	employees
WHERE 
	department_id=50

# 第(2)步：查询每个部门的最低工资
SELECT 
	MIN(salary)
FROM
	employees
GROUP BY
	department_id

# 第(3)步：在（2）基础上筛选，min(slary)>(1)
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 列子查询（结果只有一列多行）

    特点：返回多行
    使用多行比较操作符，例如：IN，NOT IN，ANY，SOME，ALL

    # 返回location_id是1400或1700的部门中的所有员工姓名

# 第(1)步：查询location_id是1400或1700的部门编号
SELECT department_id
FROM departments
WHERE location_id in (1400,1700)

# 第(2)步：要求部门号是(1)列表中的某一个
SELECT last_name
FROM employees
WHERE department_id in(
	SELECT department_id
	FROM departments
	WHERE location_id in (1400,1700)
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 行子查询（结果一行多列或者多行多列）
    # 查询员工编号最小并且工资最高的员工信息

# 查询最小的员工编号
SELECT MIN(employee_id)
FROM employees

# 查询最高工资
SELECT MAX(salary)
FROM employees

# 最终结果
SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    select后面

    # 查询每个部门的员工个数
SELECT d.*,(
	SELECT count(*)
	FROM employees
)
FROM departments d

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    from后面

    # 查询每个部门的平均工资的工资等级

# 第(1)步：查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

# 第(2)步：连接(1)的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
SELECT ag_dep.*, g.`grade_level`
FROM(
	SELECT AVG（salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal and highest_sal

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    exists后面（相关子查询）

    exists的结果只有0和1

    # 查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    8、分页查询

    • 应用场景
      当要显示的数据一页显示不全，需要进行分页提交sql请求

    • 语法

    select 查询条件
from 表1
[join type join 表2]
on 连接条件
where 筛选条件
group by 分组字段
having 分组组后的筛选
order by 排序的字段
limit offset,size;

# offset，要显示条目的起始索引，索引从0开始
# size，要显示的条目个数
# 注意limit关键字一定在最后

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 例题
    # 查询前五条员工信息
# 查询前五条员工信息
SELECT * 
FROM employees
LIMIT 0,5

    • 1
    • 2
    • 3
    • 4
    • 5

    公式
    假设：要显示的页数：page，每页的条目数size
    那么：limit (page-1)*size,size

    9、联合查询

    定义

    union联合，合并，将多条查询语句的结果合并成一个结果。

    # 查询部门编号>90或邮箱包含a的员工信息
# 第一种方式：OR
SELECT * 
FROM employees
WHERE email
LIKE '%a%'
OR
department_id>90;

# 第二种方式UNION联合查询
SELECT * 
FROM employees
WHERE email LIKE '%a%'
UNION
SELECT *
FROM employees
WHERE department_id>90;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    联合查询特点

    1. 要求多条查询语句的查询列数是一致的
    2. 要求多条查询语句的查询的每一列的类型和顺序最好一致
    3. UNION关键字默认去重，可以使用union all可以不去重
  • 相关阅读:
    word2vec包中cbow模型--学习笔记
    多态and内部类（java）
    Linux文件系统 struct file 结构体解析
    技术分享 | Web测试方法与技术实战演练
    JSON概念
    基于 iframe 的微前端框架 —— 擎天
    【水果派不吃灰】Raspberry Pi树莓派Linux系统下替换国内apt软件更新源
    这一定是前端导出Excel界的天花板~
    Entity FrameWork Core教程，从基础应用到原理实战
    SQLITE3【1】-SQLite向arm-imx6ull板子的移植
  • 原文地址：https://blog.csdn.net/lalala_dxf/article/details/123625146