MySQL常用DQL命令(day01-03)

最新推荐文章于 2022-10-20 07:00:00 发布

snake_seeker

最新推荐文章于 2022-10-20 07:00:00 发布

阅读量156

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/snake_seeker/article/details/111610409

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

常用命令总结

1 基础阶段

作为例子的四张表

在这里插入图片描述

MySQL的语法规范

	1.不区分大小写,但建议关键字大写，表名、列名小写
	2.每条命令最好用分号结尾
	3.每条命令根据需要，可以进行缩进 或换行
	4.注释
		单行注释：#注释文字
		单行注释：-- 注释文字（有空格）
		多行注释：/* 注释文字  */
	5. ``着重号用来区分关键字的
	6. 在SQLyog中选中哪句SQL语句才会执行那条
	7. 在执行操作前一般会在首行加上	use 库名
	8. 注意SQLyog左上角的库的选择
	9. SQL不区分字符和字符串的，只要是字符都认为是字符型，都是用单引号建议	''
	10. 查询的结果是一个虚拟的表 
	11. 字符型和日期型的常量必须用单引号引起来
	12. SQL 只支持简单的加减乘除取模运算，不支持 ++，--,-=,+=
	13. SQL语言中索引是从1 开始的 （limit 【offset,】size;	offset 要显示条目的起始索引（起始索引从0开始））
	14. length 获取参数值的字节个数(除此之外的没有特殊说明的都指字符)
	15. 按多个字段分组，只有每条记录在多个字段上都相等，才看做一组，且多个字段可换位置
	16. mysql group by having后面可以加别名，但是有的数据库不支持，eg:orcal

SELECT `name` FROM employees; # 否则会变成
SELECT NAME FROM employees; 
SELECT 100 FROM employees;  # 查询结果是与employees同行数的全部为100的一个虚拟表

2 DQL语言使用

1 基础查询

1.1查询列表

可以是：表中的字段、常量值、表达式、函数；查询的结果是一个虚拟的表格

 #4.查询常量值
 SELECT 100;
 SELECT 'john';
 
 #5.查询表达式
 SELECT 100%98;
 
 #6.查询函数
 SELECT VERSION();

1.2起别名：

①便于理解 ②如果要查询的字段有重名的情况，使用别名可以区分开来

#方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;
#案例：查询salary，显示结果为 out put
SELECT salary AS "out put" FROM employees;

1.3去重DISTINCT

在关键字前面加上DISTINCT，后只能跟着一个字段名

#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;
# SELECT DISTINCT department_id, DISTINCT last_name FROM employees; 错误

1.4+号的作用

java中的+号：
①运算符，两个操作数都为数值型
②连接符，只要有一个操作数为字符串
mysql中的+号：
仅仅只有一个功能：运算符
select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型
				如果转换成功，则继续做加法运算
select 'john'+90;	如果转换失败，则将字符型数值转换成0
select null+10; 	只要其中一方为null，则结果肯定为null

1.5concat 实现连接

SELECT CONCAT(str1, str2, …) AS 结果, 如果有数值会将其转为字符型;

#案例：查询员工名和姓连接成一个字段，并显示为 姓名
SELECT CONCAT('a','b','c') AS 结果;
SELECT 
	CONCAT(last_name,first_name) AS 姓名
FROM
	employees;

1.6. desc查看表的结构

1.7 IFNULL(exp1, exp2) & ISNULL(exp)

where id IS NULL

SELECT 
	ISNULL(commission_pct),commission_pct # 如果为null,就返回1
FROM 
	employees;

SELECT employee_id,first_name AS 名, last_name AS 姓
FROM employees
WHERE `commission_pct` IS NULL;

在这里插入图片描述

SELECT 
	IFNULL(commission_pct,1) AS 奖金率, # 如果为null,就返回1.00
	commission_pct
FROM 
	employees;

在这里插入图片描述

需求： 6.显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT，但是commission_pct列有的为null，有的有值

SELECT
	CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0)) AS out_put
FROM
	employees;

在这里插入图片描述

2.条件查询

语法：
	select	查询列表	from 表名	where 筛选条件;
	
分类：
	一、按条件表达式筛选
	简单条件运算符：> < =（注意） != <>（不等于，推荐） >= <=
	二、按逻辑表达式筛选
	逻辑运算符：
	作用：用于连接条件表达式
		&& || !
		and or not(推荐)
	&&和and：两个条件都为true，结果为true，反之为false
	||或or： 只要有一个条件为true，结果为true，反之为false
	!或not： 如果连接的条件本身为false，结果为true，反之为false
	三、模糊查询
		like 可以匹配int类型
		between and
		in
		is null

like

注意点

`%`是一个通配符，可代表任意一个字符，默认大小写不区分,5.5及以上版本也可以判断数值型了，参考案例四
①一般和通配符搭配使用
	通配符：
	% 任意多个字符,包含0个字符
	_ 任意单个字符
	\ 转义符使用情景:eg:查询员工名中第二个字符为_的员工名
	ESCAPE 设置转义符:

SELECT first_name, last_name
FROM `employees`
WHERE `last_name` LIKE '%\_%'  本身自带的
;  或者

SELECT first_name, last_name
FROM `employees`
WHERE `last_name` LIKE '%A_%'
ESCAPE 'A';

#案例1：查询员工名中包含字符a的员工信息
select 
	*
from
	employees
where
	last_name like '%a%';#

#案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资
select
	last_name,
	salary
FROM
	employees
WHERE
	last_name LIKE '__n_l%';

#案例3：查询员工名中第二个字符为_的员工名
SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_\_%'; 
	# last_name LIKE '_$_%' ESCAPE '$';

#案例4： 查询department_id 以1开头的三位数,注意其类型为int
SELECT
	*
FROM
	employees
WHERE
	department_id LIKE '1__'

between and

提高语句的简洁性
且于包含两个边界employee_id BETWEEN 100 AND 120==100≤employee_id≤120，左右两个边界不可以互换，大的放前面会查不到结果

#案例1：查询员工编号在100到120之间的员工信息

SELECT
	*
FROM
	employees
WHERE
	employee_id >= 120 AND employee_id<=100;
#----------------------
SELECT
	*
FROM
	employees
WHERE
	employee_id （NOT） BETWEEN 100 AND 120;

in

含义：判断某字段的值是否属于in列表中的某一项
特点：
	①使用in提高语句简洁度
	②in列表的值类型必须一致或兼容 eg:'123'->123（可隐形转换）
	③in列表中不支持通配符

#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';
#------------------
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');

is null & is not null

= 不能用于判断是否为NULL，eg: 如果下面的案例改为 = 号则结果不对，无结果返回
=或<>不能用于判断NULL值
IS NOT NULL或IS NULL 可以判断NULL值

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

安全等与<=>

IS NULL 和 <=> 比较
	IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
	<=>    :既可以判断NULL值，又可以判断普通的数值，可读性较低

#案例1：查询没有奖金的员工名和奖金率
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct <=>NULL;
	
#案例2：查询工资为12000的员工信息
SELECT
	last_name,
	salary
FROM
	employees
WHERE 
	salary <=> 12000;

3. 排序查询

语法：
	select
		要查询的东西
	from
		表
	where 
		条件
	order by 排序的字段|表达式|函数|别名 [ASC|DESC] 默认是ASC升序排列
特点
	1、asc代表的是升序，可以省略desc代表的是降序
	2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
	3、order by子句在查询语句的最后面，除了limit子句

SELECT * FROM employees ORDER BY salary DESC;

#4、按别名排序
#案例：查询员工信息 按年薪升序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC;

#5、按函数排序
#案例：查询员工名，并且按名字的长度降序
SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;

#6、按多个字段排序
#案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;

4. 常见单行函数

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

4.1 字符函数

1.length 获取参数值的字节个数(除此之外的没有特殊说明的都指字符)

SELECT LENGTH('john'); # 4
SELECT LENGTH('张三丰hahaha');  # 15
SHOW VARIABLES LIKE '%char%' # 查询客户服务端所用字符集

在这里插入图片描述
2.concat 拼接字符串
3.upper，lower

#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;

4.substr、substring

注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put; # 陆展元

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put; # 李莫愁

5.instr

#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put; # 0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷六侠') AS out_put; # 3

6.trim

SELECT LENGTH(TRIM('    张翠山    ')) AS out_put; # 9
SELECT TRIM('a' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put; # 张aaaaaaaaaaaa翠山
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put; # a张aaaaaaaaaaaa翠山a

7.LAPD

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

8.RPAD

#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put; # 殷素素ababababa

9.replace

#9.replace 替换
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put; # 赵敏赵敏赵敏赵敏张无忌爱上了赵敏

4.2 数学函数

1.round

#round 四舍五入
SELECT ROUND(-1.55); # -2
SELECT ROUND(1.567,2); # 保留小数点后2位，1.57

2.ceil
ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(-1.02); # -1

3.floor
floor 向下取整，返回<=该参数的最大整数

SELECT FLOOR(-9.99); # -10

4.truncate 截断

SELECT TRUNCATE(1.69999,1); # 1.6 只保留一位小数

5.取余

mod(a,b) ：  a-a/b*b
mod(-10,-3):    -10- (-10)/(-3)*（-3）=-1

SELECT MOD(10,-3); # 1
SELECT 10%3; #1

6.取0~1之间的一个数

SELECT RAND();

4.3 日期函数

4.3.1.获取时间的函数

#now 返回当前系统日期+时间
SELECT NOW(); # 2020-12-24 16:26:59

#curdate 返回当前系统日期，不包含时间
SELECT CURDATE(); # 2020-12-24
#curtime 返回当前时间，不包含日期
SELECT CURTIME(); # 16:26:37
#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;	# 2020
SELECT YEAR('1998-1-1') 年; # 1998
SELECT  YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月; # 9
SELECT MONTHNAME(NOW()) 月; # September

4.3.2字符和日期的转换

在这里插入图片描述

#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put; # 1998-03-02

#查询入职日期为1992--4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3'; # 默认的日期格式就是这样写的，也能够得出正确的结果
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

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

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

4.3.3 计算日期之间差了多少天

#2.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE） 毫秒
SELECT MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE
FROM employees;

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM employees; # 8735

SELECT DATEDIFF('1995-2-7','1995-2-6'); # 1

4.4 其他函数

SELECT VERSION(); # 查看mysql版本
SELECT DATABASE(); # 查看当前所在的数据库
SELECT USER(); # 查看当前用户
SELECT PASSWORD('张三'); # 进行加密
SELECT MD5('张三'); # 进行MD5加密

4.5 流程控制函数

4.5.1 if（类似三元运算符）

#1.if函数： if else 的效果
SELECT IF(10<5,'大','小') AS 比较; # 列 IF(10<5,'大','小') 值 小
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;

4.5.1 case(switch case)

使用1

java中
switch(变量或表达式){
	case 常量1：语句1;break;
	...
	default:语句n;break;
}

mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1; # 如果是值就不用了加分号;否则会认为是结束了
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;

案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为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;

使用二：类似多重if

java中：
if(条件1){
	语句1；
}else if(条件2){
	语句2；
}
...
else{
	语句n;
}
mysql中：

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;

5. 常见分组函数

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

5.1 简单使用

SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

分组函数和distinct搭配

SELECT SUM(DISTINCT salary) distinct_salary FROM employees; # NULL类型也算一种
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

count函数详细使用
在这里插入图片描述
比COUNT(字段) 表达式更常用

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees; # 107
SELECT COUNT(1) FROM employees; # 统计了1的个数
效率：
MYISAM 存储引擎下  ，COUNT(*)的效率高，内部有个计数器，可快速返回count(*)
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些(有个判断NULL的过程)
INNODB是安装的时候默认的存储引擎；5.5版本之前默认的存储引擎是MYISAM

#6、和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id  FROM employees;

6. 分组查询

先判断是否分组；如果分组写出select from where 模板，然后查看筛选条件是全局筛选，还是分组筛选，分配到having 或者 where

语法
SELECT columun,group_function(colum)
FROM table
[WHERE condition]  	分组前的筛选
[GROUP BY group_by_expression] 		[having] 	分组后的筛选
[ORDER BY column]  
where 一定放在from的后面 

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
			   针对的表			 位置	  	  连接的关键字
分组前筛选	   原始表			group by前		where	
分组后筛选	group by后的结果集   group by后		having
问题1：分组函数做筛选能不能放在where后面
答：不能
问题2：where——group by——having
一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个位置的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

#2、可以实现分组前的筛选
#案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#案例2：查询有奖金的每个领导手下员工的平均工资
SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

6.1 having

#3、分组后筛选
案例：查询哪个部门的员工个数>5
①查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
② 筛选刚才①结果
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;

案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;


案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

5.按多个字段分组，只有每条记录在多个字段上都相等，才看做一组，且多个字段可换位置
案例：查询每个工种每个部门的最低工资,并按最低工资降序
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

6.2 分组查询案例讲解（比较复杂的练习）

1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;

2.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) DIFFRENCE
FROM employees;

3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUP BY department_id
ORDER BY a DESC;
#5.选择具有各个job_id的员工人数
SELECT COUNT(*) 个数,job_id
FROM employees
GROUP BY job_id;

7. sql92 连接查询（多表查询）

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

7.1 等值连接

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

关于别名，对于不混淆的，别名可带上可不带
但如果为表起了别名，则查询的字段就不能使用原来的表名去限定
SELECT e.last_name, e.job_id, j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

加上筛选条件
案例：查询有奖金的员工名、部门名
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;

添加分组
#案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

三表连接
案例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;

7.2 非等值连接

#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

7.3 自连接

#案例：查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

8. sql99 连接查询

语法：
	select 查询列表
	from 表1 别名 【连接类型】 /不写连接类型，只有join默认是内连接
	join 表2 别名 
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】
分类：
内连接（★）：inner
外连接
	左外(★):left 【outer】
	右外(★)：right 【outer】
	全外：full【outer】
交叉连接：cross

eg: 内连接

8.1 外连接

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

在这里插入图片描述

 引入：查询男朋友 不在男神表的的女神名(这两个结果并不一样)
 SELECT b.name, b.boyfriend_id,bo.boyName
 FROM beauty AS b
 LEFT JOIN boys AS bo
 ON b.boyfriend_id=bo.id
 WHERE  bo.boyName IS NULL;
 #group by  b.boyfriend_id
 #having bo.boyName is null;
  
案例1：查询哪个部门没有员工
左外
 SELECT d.*,e.employee_id
 FROM departments d
 LEFT OUTER JOIN employees e
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
右外
 SELECT d.*,e.employee_id
 FROM employees e
 RIGHT OUTER JOIN departments d
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;

多表的左外链接

select username,psw,gname,tel 
from (at1 left join at2 on at1.t1_id=at2.t1_id) 
left join at3 on at1.t1_id=at3.t1_id

8.2 全外连接

全外连接（mysql也不支持）
简单描述一下：全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

8.3 交叉连接

实际上就是一个笛卡尔积，对应于sql92中的，连接

一个表11行一个表4行，结果44行
 SELECT b.*,bo.*
 FROM beauty b
 CROSS JOIN boys bo;

9. 子查询

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

分类：
按子查询出现的位置：
	select后面：
		仅仅支持标量子查询
	from后面：
		支持表子查询
	where或having后面：★
		标量子查询（单行） √
		列子查询  （多行） √
		行子查询
	exists后面（相关子查询）
		表子查询
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）

9.1 where或having后面

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

9.1标量子查询★

案例1：谁的工资比 Abel 高?
①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel' # 1行1列
②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);

9.2 列子查询（多行子查询）★

ANY、SOME 经常可用MIN或者MAX代替
=ANY可用 IN 代替
<>ALL可用NOT IN代替

在这里插入图片描述

案例1：返回location_id是1400或1700的部门中的所有员工姓名
①查询location_id是1400或1700的部门编号
②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id  IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
①查询job_id为‘IT_PROG’部门任一工资
②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

9.3 行子查询（结果集一行多列或多行多列）

案例：查询员工编号最小并且工资最高的员工信息
SELECT * 
FROM employees
特点①和②条件都是用的等于号（大于小于，要求一致）
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees
②查询最高工资
SELECT MAX(salary)
FROM employees
③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);

9.2 select后面

9.2.1 仅仅支持标量子查询（不太懂）

可参考《数据库系统概论》P106

案例：查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;  从外层查询即d表查取一个d.`department_id`传入内查询
 
 案例2：查询员工号=102的部门名
SELECT (
	SELECT department_name,e.department_id
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
) 部门名;

9.3 from后面

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

9.3.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;

9.4 放在exits后面的子查询（相关子查询）

子查询涉及到了主查询的相关字段，主查询先执行，某一个字段的值再去根据它过滤
和前三种子查询都不一样，他们是先执行子查询的

9.4.1 表子查询（多行多列）

语法：
exists(完整的查询语句)，括号中的查询语句可能为 单行单列，也可能为多行多列，或者没有符合条件的查询结果
结果：（存在数据为1，否则为0）
1或0

案例1：查询所有员工的部门名

exists
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
)

10 分页查询limit

offset：起始的条目索引（起始索引从0开始）
size：要显示的条目数

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

下面两条语句相同，默认从索引为0的地方开始
SELECT * FROM  employees LIMIT 0,5;
SELECT * FROM  employees LIMIT 5;

案例2：查询第11条——第25条
SELECT * FROM  employees LIMIT 10,15;

案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM  employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;

11 联合查询 union（intersect交\except差）

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

语法：
查询语句1
union
查询语句2
union
...

应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：★
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致，只是简单连接（看下图1）
3、union关键字默认去重，如果使用union all 可以包含重复项 （看下图2,3，4）
4、列数一致的情况下会以第一条查询语句的表头	一致

在这里插入图片描述

案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

snake_seeker

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL常用DQL命令(day01-03)

目录常用命令总结基础阶段作为例子的四张表MySQL的语法规范1 基础查询1.1查询列表1.2起别名：1.3去重DISTINCT1.4+号的作用1.5concat 实现连接1.6. desc查看表的结构1.7 IFNULL(exp1, exp2) & ISNULL(exp)2.条件查询likebetween andinis null & is not null安全等与<=>3. 排序查询4. 常见单行函数4.1 字符函数4.2 数学函数4.3 日期函数4.3.1.获取时间的函数4.
复制链接

扫一扫