MySQL的查询语言合集

DQL语言的学习（data query language）

基础查询

从某个表中查询某个/些字段名
select 字段名，字段名...   from 表名；、
遍历所有数据
select * from 表名;
查询常量值
select 值;
查询表达式
select 100%90;
查询函数
select version();

常用关键字

#为查询结果取名
#as关键字
select 100%98 as 名字;
#空格
select 100%98 名字；
#如果名字中有关键字，如#或者空格，需要用双引号包含
#为查询结果去重
#关键字distinct
SELECT DISTINCT department_id FROM employees ;  
#转义字符
#数据库自带专义字符
\
#自定义转义字符
escape '任意字符'

常用方法

+的作用：

#运算符	
#两个操作数都为数值型就做加法运算
select 100+90;
#其中一个为字符型，试图将字符型转化为数值型
#如果转化成功则继续做加法
select '123'+90;
#如果转化失败（字符串类型的不是数字），则把字符串看为0，并直接输出整形
select 'zhl'+90;

concat拼接方法

#例：
select concat('department_id','department_name');
#如果被拼接的某个字段是null，则结果会是null
#例：
SELECT CONCAT(`employee_id`,',',`first_name`,',',`last_name`,',',IFNULL(`commission_pct`,"0")) AS '奖金率' FROM`employees` ;
SELECT CONCAT(`last_name`,`first_name`) AS "姓名" FROM`employees` WHERE `salary`>12000;

ifnull(j1,j2)判断方法

#如果j1字段为null就返回j2，如果不为null就返回j1
#例
SELECT IFNULL(`commission_pct`,'此人是垃圾没有奖金') AS '奖金率' FROM employees;
#条件查询
#格式
select 查询列表 from 表名 where 筛选条件

​

条件查询

一、按条件表达式筛选

条件运算符
大于>
小于<
等于=
不等于!=或<>
大于等于>=
小于等于<=
案例
#案例1:查询工资在1w到2w之间的员工名、工资
SELECT `last_name`,`salary` FROM `employees` WHERE `salary`>=10000 AND `salary`<=20000;

二、按逻辑表达式筛选

与或非&& || ！
and or not（推荐）

三、模糊查询

关键字：like
特点
	1、一般和通配符搭配使用
		通配符
			%：任意多个字符，包含0个字符
			_:任意单个字符
案例
	#案例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 '_\_%';
	SELECT `last_name`FROM `employees` WHERE `last_name` LIKE '_$_%' ESCAPE '$';
关键字：between...and...
特点
	1、使用between and可以提高语句的简洁度
	2、包含收尾（完全等于>=左边的值且<=右边的值）
	#案例1：查询员工编号在100到200之间的员工信息
	SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
关键字：in
	含义：判断某字段的值是否属于in列表中的某一项
	特点：
		1、使用in提高语句简洁度
		2、in列表的值类型必须一致或兼容（如'123'和123兼容）
	#案例1：查询员工职业属于AC_ACCOUNT,FI_MGR,PU_MAN中任意一个的员工信息
	SELECT * FROM employees WHERE job_id IN('AC_ACCOUNT','FI_MGR','PU_MAN');
关键字：is null
	特点：=或者<>不能用于判断空值，is null或is not null可以判断null值
	#案例1：查询没有奖金的员工名和奖金率
	SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
	SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
关键字：安全等于<=>
	特点：只能用于判断等于，但可以同时判断null和数据
	#案例1：查询没有奖金的员工名和奖金率
	SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;、

排序查询

格式

​ select 查询列表
​ from 表
​ 【where 筛选条件】
​ order by 排序列表 【asc||desc】

特点

1：asc升序（默认）
desc降序
2：order by 子句中可以支持单个字段，多个字段，表达式，函数，别名。
3：order by 子句一般是放在查询语句的最后面，除了limit子句

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

分组查询

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

语法：

select 分组函数，列（要求出现在groupby的后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】

注意：

查询列表必须特殊，要求是分组函数和group by后出现的字段

特点

一、分组查询中的筛选条件可以分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
1、分组函数做条件肯定是放在having子句中
2、能考虑用分组前筛选的就尽量用分组前筛选
二、group by支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用的较少）
三、也可以添加排序（在分组查询的后面）

案例

#分组查询基础
#案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id; 
#案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
#添加筛选条件
#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT email,AVG(salary) AS 平均工资,department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
#案例2：查询有奖金的每个领导手下员工的最高工资
SELECT manager_id,
MAX(salary) 
AS 有奖金的最高工资 
FROM employees 
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#添加复杂的筛选条件(分组后筛选)
#案例1：查询那个部门的员工个数>2
SELECT department_id,COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*)>2;
#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary),commission_pct 
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 LENGTH(CONCAT(last_name,first_name)) AS 姓名长度,COUNT(*) AS 人数
FROM employees
GROUP BY LENGTH(CONCAT(last_name,first_name))
HAVING COUNT(*)>5;

连接查询

sql92语法

含义：

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果有m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：

按年代分类：

sql92标准：仅仅支持内连接+外连接（左外和右外）+交叉连接
sql99标准【推荐】

按功能分类

内连接：

​ 等值连接
​ 非等值连接
​ 自连接

外连接：

​ 左外连接
​ 右外连接
​ 全外连接

交叉连接

等值连接

特点

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

案例

#案例1：查询女神名和对应的男神名
SELECT beauty.name,boyName 
FROM beauty,boys 
WHERE beauty.boyfriend_id=boys.id;
#案例2：查询员工名和对应的部门名
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 e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.job_id=j.job_id
#4：可以加筛选
#案例1：查询有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.salary IS NOT NULL 
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_id,l.city
FROM departments d,locations l
WHERE l.location_id=d.location_id
AND l.city LIKE'_o%'

#5:可以加分组

#案例1：查询每个城市的部门个数
SELECT l.city,COUNT(*)
FROM locations l,departments d
WHERE d.location_id=l.location_id
GROUP BY l.city;
#案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE commission_pct IS NOT NULL
AND d.department_id=e.department_id
GROUP BY department_name,manager_id;
#6:可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#7:实现3表连接

#案例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND l.`location_id`=d.`location_id`

非等值连接

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

自连接

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

sql99语法

语法：

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

连接分类

一：内连接

语法：

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

图示[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Glpt2tAX-1620273623020)(C:\Users\asus\AppData\Roaming\Typora\typora-user-images\image-20210506114522063.png)]

​

#案例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%'
#案例3：查询部门个数>3的城市名和部门个数（分组+筛选）
SELECT city,COUNT(*)
FROM locations l
JOIN departments d
ON d.location_id= l.location_id
GROUP BY l.city
HAVING COUNT(*)>3;
#案例4：查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序（排序）
SELECT department_name,COUNT(*) 
FROM departments d
INNER JOIN employees e
ON e.department_id=d.department_id
GROUP BY d.department_id
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
#案例5：查询员工名、部门名、工种名、并按照部门名降序
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d,jobs j
ON e.department_id=d.department_id,e.job_id=j.job_id
ORDER BY d.department_name

非等值

#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
#查询员工的工资级别的个数>2,并且按工资级别降序
SELECT salary,grade_level,COUNT(*)
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY g.grade_level
HAVING COUNT(*)>2
ORDER BY g.grade_level DESC;

自连接

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

二：外连接

应用场景：

用于查询一个表中有，另一个表中没有的记录

特点：

1：外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有与它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2：左外连接，left join左边的主表 右外连接，right join右边的是主表
3：左外右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2有但表1没有的
5：交叉连接：本质上就是笛卡尔积，关键字：cross join

#左外连接

图示[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cvJAvWGm-1620273623025)(C:\Users\asus\AppData\Roaming\Typora\typora-user-images\image-20210506114631409.png)]

​

#案例：查询没有男朋友的女神名 
SELECT b.name,bo.*FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;
#案例1：查询哪个部门没有员工
USE myemployees;
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;

#右外连接

图示[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-afYYcRBJ-1620273623027)(C:\Users\asus\AppData\Roaming\Typora\typora-user-images\image-20210506114707108.png)]

​

#案例：查询没有男朋友的女神名 
SELECT b.name,bo.*FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL

#三：全外连接

图示[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OQaZaAMs-1620273623029)(C:\Users\asus\AppData\Roaming\Typora\typora-user-images\image-20210506114739588.png)]

​

分为三部分：
第一部分beauty表的和boys表的交集
第二部分beauty表独有的部分
第三部分boys表独有的部

#四：交叉连接

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

区别

​ 功能：99支持的较多
​ 可读性：99实现连接条件和筛选条件的分离，可读性较高

常用函数

单行函数

字符函数

length获取参数值的字节个数

例：SELECT LENGTH(‘john’);

concat拼接字符串

例：SELECT CONCAT(last_name,’_’,first_name) AS 姓名 FROM employees;

upper、lower大小写转化

例：SELECT UPPER(‘john’);
SELECT LOWER(‘JOHN’);
示例：将姓变大写，将名变小写
SELECT CONCAT(UPPER(first_name),LOWER(last_name)) 名字 FROM employees;

substr、substring截取字符串

例：
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;#截取从指定所有处后的所有参数
SELECT SUBSTR('李莫愁',1,3);#截取从指定索引开始的n位数；
注意：
#数据库的索引计算方式都是从1开始
instr 返回子串第一次出现的索引，如果找不到，返回0
例：
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;
trim 删除空格/指定字符
例：
SELECT TRIM('   张翠山')AS out_put;
SELECT TRIM('a'FROM'aaaa张a翠a山aaaaa');
lpad 用指定字符实现左填充指定长度
例：
SELECT LPAD('殷素素',12,'ab')AS out_put;
rpad 用指定的字符实现右填充指定长度
例：
SELECT RPAD('殷素素',10,'ab')AS out_put;
replace替换
例：
SELECT REPLACE('张无忌爱上了周芷若周芷若周芷若周芷若','周芷若','赵敏');
数学函数
#round 四舍五入
例：
SELECT ROUND(1.45); #默认取整
SELECT ROUND(1.456,2);#保留x位小数
#ceil向上取整
例：
SELECT CEIL(1.0000001);
#floor 向下取整 返回<=该参数的最大整数
例：
SELECT FLOOR(-9.99);
#truncate 截断 从第x位后的全部截断
例：
SELECT TRUNCATE(1.69999,1);
#mod 取余
例：
SELECT MOD(10,-3);
SELECT 10%3;
日期函数
#now返回当前系统日期+时间
例：
SELECT NOW();
#curdate返回当前系统日期不包含时间
例：
SELECT CURDATE();
#curtime返回当前系统时间，不包含日期
例：
SELECT CURTIME();
#可以获取指定的部分，年，月，日，小时，分钟，秒
例：
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT MONTHNAME();#返回月份的英文
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
#str_to_date：将日期格式的字符转换成指定格式的日期
例:
SELECT STR_TO_DATE('9-13-1999')

其他函数【补充】

SELECT VERSION();#查询版本号
SELECT DATABASE();#查询数据库
SELECT USER();#查询用户

流程控制函数【补充】

#1.if函数：if else的效果

SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'垃圾','大佬')FROM employees;

#2:case函数的使用1：类似switch case的效果

java中

switch(变量或表达式){
case 常量1：语句;break;
…
}

mysql中

case 要判断的字段或表达式
when 常量1 then 要显示的值或者语句;
…
else …;
end;

案例：查询员工的工资，要求
部门号=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;
				

#3：case函数的使用二：类似于多重if

java中

if（条件）{
语句1；
}else if（）{
语句2；
}…

mysql中：

case
when 条件1 then 要显示的值1或者语句1
when 条件2 then 要显示的值2或者语句2
…
else要显示的值或语句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;

分组函数

功能：

用作统计使用，又称为聚合函数或统计函数或组函数

分类：

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

特点：

1：sum，avg一般用于处理数值型max、min、count可以处理任何类型
2：以上分组函数都是忽略null值的
3：可以和distinct搭配使用实现去重
4：count函数的单独介绍，一般使用count(*)做统计行数
5：和分组函数一同查询的字段要求是group by后的字段

#1、基础应用

SELECT SUM(salary) 和,AVG(salary) 均,MIN(salary) 小,MAX(salary) 大,COUNT(salary) 量 FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
#去重
SELECT SUM(DISTINCT salary) 和,AVG(DISTINCT salary) 均,MIN(DISTINCT salary) 小,MAX(DISTINCT salary) 大,COUNT(DISTINCT salary) 量 FROM employees;

效率

MYSAM存储引擎下，count(*)的效率高
AS 级别
FROM employees;

##### 分组函数

##### 功能：

用作统计使用，又称为聚合函数或统计函数或组函数

##### 分类：

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

##### 特点：

1：sum，avg一般用于处理数值型max、min、count可以处理任何类型
2：以上分组函数都是忽略null值的
3：可以和distinct搭配使用实现去重
4：count函数的单独介绍，一般使用count(*)做统计行数
5：和分组函数一同查询的字段要求是group by后的字段

###### #1、基础应用

```mysql
SELECT SUM(salary) 和,AVG(salary) 均,MIN(salary) 小,MAX(salary) 大,COUNT(salary) 量 FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
#去重
SELECT SUM(DISTINCT salary) 和,AVG(DISTINCT salary) 均,MIN(DISTINCT salary) 小,MAX(DISTINCT salary) 大,COUNT(DISTINCT salary) 量 FROM employees;

效率

MYSAM存储引擎下，count()的效率高
INNODB存储引擎下，count()和count(1)效率差不多，比count(字段)要高一些