Mysql基础01-DQL语言-查询-【2021-09-24】

1、准备

mysql的安装、配置省略。

查看版本 mysql –version 或者mysql -V

mysql --version

如果是本机，可以省略中括号中的主机和端口。端口号的-P是大小，密码的-p是小写。

注意如果想直接输入用户名密码，-p后面不要写空格，直接跟密码

mysql -u root -proot

如果希望不显示密码，-p 直接回车。在下一行输入密码。

mysql -u root -p

退出：exit 或 ctrl+C

exit

如果想链接远程的mysql

mysql -h IP地址 -P 端口号 -u root -p

常见命令：

sql语句使用分号结尾。和java是一样的。sql语句是不区分大小写的！

为了方便展示。使用可视化软件进行学习。推荐Navicat；sqlyog有点丑啊

显示所有的数据库

show databases;
// 结果
information_schema
aaop
aaopproduct
aiplatform
cloudx
cloudx_config
mysql
nacos_config
performance_schema
release
sc
sys

里面有3个库 information_schema、mysql、performance_schema 是系统自带的库，不要删除。

选中数据库进行操作

use 数据库名；

USE `release`

查看所有的表 show tables；

show TABLES;
// 输出 我电脑的表
t_ability
t_ability_category
t_ability_contract
t_ability_page_detail
t_ability_usage_api
t_ability_usage_attachment
t_ability_usage_sdk
t_app

查看表描述信息 desc 表名；

desc t_ability;

查看mysql 版本 select version();

select VERSION();
// 输出
5.7.31-log

规范：

1、SQL语句不区分大小写，但是建议关键字大写，表名、列名小写

2、建议用分号结尾

3、SQL可以缩进、换行

4、注释单行注释： #注释内容或者 –(空格)注释内容多行注释：和 java一样 /* 注释内容 */

2、Dql语言

data query language 数据查询语言主要是表内的数据查询

Data Manipulation Language 数据操作语言主要是表内的数据的增删改

Data Definition Language 数据定义语言主要是库和表的增删改

Transaction Control Language 事务控制语言

Data Control Language 数据控制语言

本小节将DQL语言，数据查询没有数据怎么行。先使用预先准备的sql脚本创建表以及数据。

myemployees.sql

导入完成后有4张表

首先看员工表 employees

CREATE TABLE `employees` (
	`employee_id` INT ( 6 ) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
	`first_name` VARCHAR ( 20 ) DEFAULT NULL COMMENT '名',
	`last_name` VARCHAR ( 25 ) DEFAULT NULL COMMENT '姓',
	`email` VARCHAR ( 25 ) DEFAULT NULL COMMENT '邮箱',
	`phone_number` VARCHAR ( 20 ) DEFAULT NULL COMMENT '电话号码',
	`job_id` VARCHAR ( 10 ) DEFAULT NULL COMMENT '工种编号',
	`salary` DOUBLE ( 10, 2 ) DEFAULT NULL COMMENT '月薪',
	`commission_pct` DOUBLE ( 4, 2 ) DEFAULT NULL COMMENT '奖金率',
	`manager_id` INT ( 6 ) DEFAULT NULL COMMENT '上级领导编号',
	`department_id` INT ( 4 ) DEFAULT NULL COMMENT '部门编号',
	`hiredate` datetime DEFAULT NULL COMMENT '入职时间',
	PRIMARY KEY ( `employee_id` ),
	KEY `dept_id_fk` ( `department_id` ),
	KEY `job_id_fk` ( `job_id` ),
	CONSTRAINT `dept_id_fk` FOREIGN KEY ( `department_id` ) REFERENCES `departments` ( `department_id` ),
CONSTRAINT `job_id_fk` FOREIGN KEY ( `job_id` ) REFERENCES `jobs` ( `job_id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 207 DEFAULT CHARSET = gb2312;

上面是sql中的ddl语言，后面会讲，这里先看一下每一个字段后面的注释，即中文部分。是每一个字段的含义。

再看部门表departments，同样是了解每一个字段的含义

CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '部门编号',
  `department_name` varchar(3) DEFAULT NULL COMMENT '部门名称',
  `manager_id` int(6) DEFAULT NULL COMMENT '部门领导的员工编号',
  `location_id` int(4) DEFAULT NULL COMMENT '位置编号',
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`),
  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;

位置表 locations

CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '位置编号',
  `street_address` varchar(40) DEFAULT NULL COMMENT '街道',
  `postal_code` varchar(12) DEFAULT NULL COMMENT '邮编',
  `city` varchar(30) DEFAULT NULL COMMENT '城市',
  `state_province` varchar(25) DEFAULT NULL COMMENT '省',
  `country_id` varchar(2) DEFAULT NULL COMMENT '国家编号',
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;

工种表 jobs

CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL COMMENT '工种编号',
  `job_title` varchar(35) DEFAULT NULL COMMENT '工种名称',
  `min_salary` int(6) DEFAULT NULL COMMENT '最低工资',
  `max_salary` int(6) DEFAULT NULL COMMENT '最高工资',
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

2.1 基础查询

SELECT 查询列表 FROM 表名

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

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

1查询单个字段如 last_name

SELECT last_name FROM employees

2查询表的多个字段

SELECT last_name, salary, email FROM employees

多个字段用逗号隔开，字段的顺序可以自己定义。

3查询所有字段

1、写所有的字段

SELECT employee_id,first_name,last_name,email,phone_number,job_id,salary,commission_pct,manager_id,department_id,hiredate 
FROM employees

2、用 * 代替

SELECT * FROM employees

上面的写法都没有加上 USE 库名。加上会比较好

USE myemployees;
SELECT * FROM employees

4查询常量值

# 查询整型
SELECT 100;

结果：

# 查询字符型
SELECT 'hello';

结果：

5查询表达式

SELECT 100%98;

结果：

6查询函数

SELECT VERSION();

结果：

7起别名方式1 as ：便于理解，可以解决重名冲突。方式2：省略 as 直接空格加上别名

SELECT 100%98 AS 结果;

结果

SELECT last_name AS 姓, first_name AS 名 FROM employees;

结果：

看方式2：

SELECT 100%98 结果;
SELECT last_name 姓, first_name 名 FROM employees;

结果和上面的一样。

注意，如果你起的别名有特殊字符比如空格，那么就需要把别名用双引号包裹，单引号也行。建议双引号

SELECT salary AS "out put" FROM employees;

结果：

8去重

# 查询员工表中涉及到的所有部门编号
SELECT DISTINCT department_id FROM departments

结果

9 +号的作用

+ 号只有一个作用，就是运算符。

# 2个数值型做加法运算
select 100+90;
# 一个为字符、一个为数值，会将字符型尝试转换为数值型,再进行加法运算。 转换失败则将字符型转换为数值0
select '123'+90;
# 只要一方为null，结果就是null
select null+10;

要想实现类似于java的字符串拼接的效果，需要使用函数 concat

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

结果

后面会详细讲函数。这里只是简单使用，这个concat函数接收可变参数，参数类型为字符型。

主要，如果一个字段的值为null，任何拼接都会变成null。

可以使用 IFNULL 函数来进行判断。

例如 employees 表的 commission_pct 为大部分为null。

SELECT
	IFNULL( commission_pct, 0 ) AS 奖金率,
	commission_pct 
FROM
	employees

IFNULL 函数第一个参数是需要进行判断的变量，第二个参数是如果是null，用来替换的值。

部分结果是：

2.2 条件查询

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

就是在基础查询的基础上增加了一个 where 关键字，写筛选的条件。

执行顺序是先走 FROM 看一看表存在不，再定位到表，然后走 WHERE 条件，最后是 select 取数据

分类：

1、条件表达式

条件运算符：大于> 小于< 等于= 不等于!= 不等于<> 安全等于<=>

#查询 工资大于12000 的员工信息
SELECT * FROM employees WHERE salary>12000;

#查询 部门编号不等于90 的员工名和部门编号
SELECT last_name, department_id FROM employees WHERE department_id != 90;
SELECT last_name, department_id FROM employees WHERE department_id <> 90;  # 推荐使用

#查询 没有奖金的员工信息
SELECT * FROM employees WHERE commission_pct <=> NULL;

#查询 工资等于12000 的员工信息
SELECT * FROM employees WHERE salary=12000;
SELECT * FROM employees WHERE salary<=>12000;

2、逻辑表达式

逻辑运算符：与 && 或|| 非！推荐使用 and or not

#查询 工资在10000到20000之间的员工名、工资以及奖金
SELECT last_name,salary,commission_pct FROM employees WHERE salary>10000 AND salary <20000;

#查询 部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT * FROM employees WHERE NOT (department_id>=90 AND department_id<=110) OR salary>15000;

SELECT * FROM employees WHERE department_id<90 OR department_id>110 OR salary>15000;

3、模糊查询

like、 between and、in、is null

#查询员工名中包含字符a的员工信息。 %是通配符

SELECT * FROM employees WHERE last_name LIKE '%a%';

like 一般和通配符一起使用。

通配符有哪些？

%百分号：任意多个字符，包含0个

_下划线: 任意单个字符

#查询 员工名中第三个字符为n，第5个字符为l的员工信息

SELECT * FROM employees WHERE last_name LIKE '__n_l';

如果需要匹配的字符就是通配符本身怎么写？

#查询 员工名中第二个字符为_的员工名   使用 转义字符\

SELECT * FROM employees WHERE last_name LIKE '_\_%';

#查询 员工名中第二个字符为_的员工名  使用自定义的转义字符，需要用上关键字 ESCAPE 指定

SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$'; 
# 推荐使用

between xxx and yyy 在xxx到yyy之间，边界值是包含的，顺序不能乱写。

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

SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

in 判断某字段的值是否属于in列表中的一项，要求in 列表的值类型必须统一或兼容比如 ‘123’ 可以转换为 123 就是兼容

#查询 员工的工种编号是 IT_PROG、AD_VP、AD_PRES 中的员工信息

SELECT * FROM employees WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES' )

is null 用于判断null值，条件表达式的等于不等于不能判断null值,但是安全等于可以判断null值

#查询 没有奖金的员工信息

SELECT * FROM employees WHERE commission_pct IS NULL;

#查询 有奖金的员工信息

SELECT * FROM employees WHERE commission_pct IS NOT NULL;

例子：

#查询 员工号为176的员工姓名和年薪

SELECT last_name, salary*12*(1 + IFNUcommission_pct) AS 年薪 FROM employees WHERE employee_id=176

#查询 员工号为176的员工姓名和年薪  完整版。增加null值判断

SELECT last_name, salary*12*(1 + IFNULL(commission_pct,0)) AS 年薪 FROM employees WHERE employee_id=176

结果

2.3 排序查询

主要是 ORDER BY 关键字还有升序 asx 降序 desc

ORDER BY 一般放在语句的最后面，除了limit 关键字以外。

# 查询员工工资从高到低排序(降序)

SELECT * FROM employees ORDER BY salary DESC;

# 查询员工工资从低到高排序(升序、默认排序方式)

SELECT * FROM employees ORDER BY salary ASC;
SELECT * FROM employees ORDER BY salary;

加上筛选的排序

# 查询部门编号>=90 的员工信息，按入职时间排序

SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate;

按表达式排序

# 查询员工的信息和年薪 并按照年薪高到低排序

SELECT *, 12*salary*(1+IFNULL(commission_pct, 0)) AS 年薪 FROM employees ORDER BY 12*salary*(1+IFNULL(commission_pct, 0)) DESC;

按别名排序

# 查询员工的信息和年薪 并按照年薪高到低排序

SELECT *, 12*salary*(1+IFNULL(commission_pct, 0)) AS 年薪 FROM employees ORDER BY 年薪 DESC;

按函数排序

# 查询员工的信息按照姓名的长度排序  这里使用了 LENGTH 函数求 长度

SELECT * FROM employees ORDER BY LENGTH(last_name) DESC;

双字段排序：用逗号隔开多个排序字段。

# 查询员工的信息 先按照工资排序，再按照员工编号排序

SELECT * FROM employees ORDER BY salary, department_id DESC

2.4 常见函数

这里的函数是 mysql 为我们提供的API ，我们也是可以自定义函数的。后面再讲自定义函数。

一般格式：SELECT 函数名(参数) 【FROM 表】

如果参数是表中的字段，就需要加上 FROM 表

2.4.1 单行函数

1、字符函数

LENGTH(str)：获取参数的字节数

SELECT LENGTH('hello');  # 结果是 5
SELECT LENGTH('洗个澡hello'); # 结果是 14

汉字所占的字节与字符集有关，可以查一下当前字符集

SHOW VARIABLES LIKE '%char%'

可以看到，客户端是 utf8mb4 字符集。

utf8是Unicode的一种，属于可变字节。字符根据编码范围使用1-4个字节存储。mysql的utf8支持汉字最大3个字节，而utf8mb4是utf8的超集，兼容了4个字节的汉字情况。

汉字一般是3个字节，所以上面的结果是14

CONCAT(str1,str2,…) ：拼接字符串接收可变参数，将所有参数按顺序拼接

# 用下划线拼接了 last_name 和 first_name, 这2个字段都来自变 employees ，所有要加上from 表名

SELECT CONCAT(last_name,'_', first_name) AS 姓名 FROM employees;

UPPER(str) ：全部变成大写； LOWER(str) ：全部变成小写

# 将姓变成大写，名变成小写，然后用下划线拼接

SELECT CONCAT(UPPER(last_name),'_', LOWER(first_name)) FROM employees;

上面说明函数可以嵌套调用，一个函数的返回值可以作为另一个函数的参数

SUBSTR(str FROM pos FOR len) ：子串

SUBSTRING(str FROM pos FOR len) 也是一样，上面的是简写

这是4个重载方法的一个。

# 注意：数据库中， 索引是从1开始。  2个参数：指定索引开始进行截取

SELECT SUBSTRING('我爱上了mysql', 5) out_put;  #结果是 mysql

# 3个参数，第2个参数是起始索引，第三个参数是 要截取的字符长度，不是字节长度！！！

SELECT SUBSTRING('我爱上了mysql', 2,3) out_put; # 结果是 爱上了

案例：将姓名中首字母大写，其他小写，用下划线拼接

SELECT CONCAT(UPPER(SUBSTR(last_name, 1, 1)), LOWER(SUBSTR(last_name, 2)),'_', LOWER(first_name)) FROM employees;

INSTR(str,substr) 返回子串在原串的第一次出现的索引，如果不存在就返回0

SELECT INSTR('我爱上了mysql', 'mysql') AS out_put;  #结果 5

TRIM([remstr FROM] str) 去除前后空格

SELECT TRIM('  mysql ') AS out_put;

原来是有空格的，输出之后去除了前后空格。

还可以用来去除前后的指定字符，同时还包括空格

SELECT TRIM('a' FROM 'aaa mysaaaql aaa') AS out_put;
# 结果是 mysaaaql

LPAD(str,len,padstr) ：左填充，第2个参数是总长度，长度不够才会进行左填充。第三个参数是用来填充的字符

RPAD(str,len,padstr) ：右填充，第2个参数是总长度，长度不够才会进行右填充。第三个参数是用来填充的字符

SELECT LPAD('hello',10, '*') AS out_put;

# 输出 *****hello

SELECT RPAD('hello',10, '*') AS out_put;
// 输出 hello*****

REPLACE(str,from_str,to_str) 替换，如果有多个，都会替换

SELECT REPLACE('mysql','s','$$') AS out_put;
// 输出  my$$ql

2、数学函数

ROUND(X) ROUND(X,D) 2个重载的方法；对数字进行4舍5入。

第一个方法不保留小数，第二个方法的第二个参数的保留几位小数

SELECT ROUND(1.65);
// 结果是： 2
SELECT ROUND(-1.65);
// 结果是： -2

SELECT ROUND(-1.65, 1);
// 结果是： -1.7

CEIL(X) 向上取整：返回大于等于原值的最小整数

FLOOR(X) 向下取整; 返回小于等于原值的最大整数

SELECT CEIL(1.52);
// 结果是： 2
SELECT FLOOR(1.52);
// 结果是： 1

TRUNCATE(X,D) 截断，第二个参数是小数后保留几位

SELECT TRUNCATE(1.68987, 2);
// 结果是： 1.68

MOD(N,M) ： N – N/M*M 取余，和java的 % 运算符是一样的

SELECT MOD(10,3);
// 结果是： 1

3、日期函数

NOW() 返回系统当前日期和时间

SELECT NOW();
// 结果是： 2021-09-15 16:09:11

CURDATE() 返回当前日期等同于 CURRENT_DATE()

SELECT CURDATE();
// 结果是： 2021-09-15

CURTIME() 返回当前时间 CURRENT_TIME()

SELECT CURTIME()
// 结果是： 16:09:11

YEAR(date) 返回日期中的年份

MONTH(date) 返回日期中的月份； MONTHNAME(date) 返回日期中的月份，英文表示

DAY(date) 返回日期中的日

SELECT YEAR(NOW());	// 结果是： 2021
SELECT YEAR('1995-1-1');	// 结果是： 1995
SELECT YEAR(hiredate) FROM employees;

SELECT YEAR('1995-1-1');	// 结果是： January

HOUR(time) 返回时间中的小时

MINUTE(time)返回时间中的分钟

SECOND(time)返回时间中的秒

SELECT HOUR(CURRENT_TIME()); // 结果是： 16
SELECT HOUR('16:09:11'); 	// 结果是： 16
SELECT HOUR(hiredate) FROM employees;

STR_TO_DATE(str,format) 将字符串转换为日期格式

DATE_FORMAT(date,format) 将日期按照指定的格式输出字符串

SELECT STR_TO_DATE('1995-10-20','%Y-%m-%d');
# 查询入职日期是 1992-4-3 的员工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('1992-4-3', '%Y-%c-%d');

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日'); // 输出  2021年09月15日
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日'); // 输出  21年09月15日

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

DATEDIFF(expr1,expr2) 计算2个日期之间的天数差值是第一个参数减去第二个参数的结果。

SELECT DATEDIFF('2021-9-23', '2021-9-15') # 结果 8

4、其他函数

VERSION() 查看mysql 版本号

SELECT VERSION();

DATABASE() 查看当前库名

SELECT DATABASE();

USER() 查看当前用户

SELECT USER();

5、流程控制函数

IF(expr1,expr2,expr3) 这个有点像三元运算符，表达式1成立返回表达式2，不成立返回表达式3

SELECT IF(10<5, '小', '大');
SELECT last_name, IF(commission_pct IS NULL, '没有奖金', '有奖金') FROM employees;

CASE 函数使用一：case函数有点像 java的 switch case。当case搭配 select使用时，then后面只能写表达式或值不能是语句。

CASE 要判断的字段或表达式 WHEN 常量1 THEN 值1或者语句1，是语句需要加上分号

WHEN 常量2 THEN 值2或者语句2，是语句需要加上分号

。。。

ELSE 默认值或默认语句，是语句需要加上分号 END

# 如果部门号=30，显示工资为1.1倍, 部门号=40，显示工资为1.2倍, 部门号=50，显示工资为1.3, 部门号=其他，显示工资为1倍。

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;

CASE 函数使用二：类似于多重IF

CASE

WHEN 条件1 THEN 要显示的值1或者语句1

WHEN 条件2 THEN 要显示的值2或者语句2

WHEN 条件3 THEN 要显示的值3或者语句3

。。。

ELSE 要显示的值n或者语句n

END

与第一个IF的区别就是 CASE 后面没有要判断的字段或者表达式。适合于区间判断

# 如果工资大于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;

2.4.2 分组函数

主要做统计使用，又称为聚合函数。输入多个值，得到一个值。

SUM(expr) 求和

# 查询工资之和

SELECT SUM(salary) FROM employees

AVG([DISTINCT] expr) 求平均值

# 查询工资的平均值

SELECT AVG(salary) FROM employees;

MIN(expr) 求最小值
MAX(expr) 求最大值
COUNT(DISTINCT expr,[expr…]) 计数，统计非空结果有多少个

# 查询工资有多少条记录

SELECT COUNT(salary) FROM employees;

可以多个一起使用

SELECT SUM(salary) 总和, AVG(salary) 平均值, MIN(salary) 最小值, MAX(salary) 最大值,COUNT(salary) 总数 FROM employees;

特点：

SUM、AVG 这2个聚合函数接收的参数类型一般是数值型的，但是放字符型的也不会报错，但是没有意义。会忽略 NULL 值

MIN、MAX 这2个只要可以比较大小的就可以支持。如数字、字母，日期，也会忽略NULL值

COUNT 支持任意类型，也会忽略NULL值

都可以搭配 DISTINCT 一起使用

SELECT SUM(DISTINCT salary), AVG(DISTINCT salary), MIN(DISTINCT salary), MAX(DISTINCT salary),COUNT(DISTINCT salary) FROM employees

COUNT 还需要强调一下：

count是统计不为null的数目。

使用count(*) 用来统计总的条目。

SELECT COUNT(*) FROM employees;

这样写的好处是，同一行，只要有一个字段的值不是null就会被统计到，除非这一行全部是null。

SELECT COUNT(1) FROM employees;

count(1) 的相当与增加一列全是1的字段，有多少行就有多少个1，统计1的个数就是统计行数。

效率上，看引擎。Innodb 下 count(1) 和count(

) 差不多，在myisam 下，count(

*)效率高。都比count(字段) 效率高。

综上，使用count(*) 最好。

其他注意事项：

和分组函数一起查询的字段有限制。

SELECT SUM(salary), employee_id FROM employees;

上面的语句虽然不会报错，但是没有什么意义。因为第二个字段是有很多值的，这里只显示了1个。

一般需要是 GROUP BY 之后的字段才可以和分组函数一起查询！

2.5 分组查询

分组查询与分组函数是不同的。

GROUP BY 字句

分组查询常常搭配分组函数一起使用。

一般格式：

SELECT 分组函数 , 列（出现在GROUP BY 的后面） FROM 表 【WHERE 条件】 【ORDER BY 字句】

查询每个工种的最高工资：

# 每个工种的最高工资

SELECT MAX(salary), job_id FROM employees GROUP BY job_id;

查询每个位置的部门个数

# 每个位置的部门个数

SELECT COUNT(*), location_id FROM departments GROUP BY location_id;

增加筛选条件1：

#查询邮箱中包含a字符的，每个部门的平均工资

SELECT AVG(salary), department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

增加筛选条件2：

#查询有奖金的每个领导手下员工的最高工资

SELECT MAX(salary), manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

增加筛选条件3：

#查询那个部门的员工个数大于2

SELECT  COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 2;

分组后的筛选使用 HAVING

#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT  job_id, MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING  MAX(salary) > 12000

# 第一步： 查询每个工种有奖金的员工的最高工资

SELECT  job_id, MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id;

# 第二步： 根据上一步结果继续筛选，最高工资大于12000，这个字段是原始表没有的

SELECT  job_id, MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING  MAX(salary) > 12000

增加筛选条件4：

#查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号，最低工资

SELECT manager_id, MIN(salary) FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;

小结：

1、分组查询的筛选条件分为2种。

一种是分组前进行筛选，数据来源原始表；另一种是分组之后进行筛选，数据来源中间表，或者是叫分组结果表。

原始表就可以进行筛选的用 where，中间表或分组结果表才能进行筛选的用 having 放在group by 之后。

这个需要筛选的字段分组前有，分组后也有，有限使用分组前筛选。放在where后面

分组函数做条件一定是放在having里面。因为分组函数就是分组后的结果。

group by 还可以放表达式或者函数

# 按员工姓名的长度分组，查询每一组的员工个数，要求员工个数大于5

SELECT COUNT(*), LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5;

按多个字段分组

# 查询每个部门的每个工种的员工的平均工资

SELECT AVG(salary), job_id, department_id FROM employees GROUP BY department_id, job_id;

将多个字段放在group by 后面，用逗号隔开。当需要分组的字段值都一样时才会放到同一个组里面。

添加排序 order by

# 查询每个部门的每个工种的员工的平均工资,并排序

SELECT AVG(salary), job_id, department_id FROM employees GROUP BY department_id, job_id ORDER BY AVG(salary);

例子

# 查询各个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 DESC;

2.6 连接查询【多表查询】

当我们要查询的字段来自于多张表时，就需要用到连接查询。

迪卡尔积

如果两张表直接查，就会做笛卡尔积。a表的每一行数据都会和b表的每一行数据进行组合。

多表查询时，就是做的笛卡尔积运算。只不过一般我们都会加上连接条件。虽然会两两匹配，但是只有满足条件的才会显示。

连接查询的分类：

1、按照标准分： 92标准（内连接）、99标准（内连接、外连接（左外、右外）、交叉连接）

括号内是该标注在mysql中的支持

2、按照功能分类：

内连接：等值连接、非等值连接、自连接

外连接：左外连接、右外连接、全外连接

交叉连接。

数据准备，这里准备了 2张表来练习。

CREATE TABLE `beauty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `sex` char(1) DEFAULT '女' COMMENT '性别',
  `borndate` datetime DEFAULT '1987-01-01 00:00:00' COMMENT '生日',
  `phone` varchar(11) NOT NULL COMMENT '电话',
  `photo` blob COMMENT '照片',
  `boyfriend_id` int(11) DEFAULT NULL COMMENT '男朋友ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

CREATE TABLE `boys` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `boyName` varchar(20) DEFAULT NULL COMMENT '男孩姓名',
  `userCP` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

beauty 表的 boyfriend_id 与boys 中的 id 字段是一一对应的。

2.6.1 sql92标准

1、等值连接

# 查询 beauty 表对应的男朋友

SELECT `name`, boyName FROM boys, beauty WHERE beauty.boyfriend_id = boys.id;

由于有2张表，为了方便区分，可以在字段前加上表名点的方式进行限定。如果上面的 where 后面的条件。

# 查询 员工名对应的部门名

SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id;

# 查询 员工名、工种号、工种名

SELECT last_name, employees.job_id, job_title FROM employees, jobs WHERE employees.job_id = jobs.job_id

上面这条语句要查询的 job_id 也用表名进行限定了。因为这个字段出现在了2张表中。必须区分。

但是我们发现，用表名限定之后，语句比较长。这个时候我们可以对表名取别名

# 查询 员工名、工种号、工种名  使用别名优化

SELECT last_name, E.job_id, job_title FROM employees AS E, jobs AS J WHERE E.job_id = J.job_id;
SELECT last_name, E.job_id, job_title FROM employees E, jobs J WHERE E.job_id = J.job_id;

注意，起了别名之后要查询的字段就不能使用原表名进行限定。

等值连接也是可以加筛选的

# 查询 有奖金的员工名、部门名

SELECT last_name, D.department_name FROM employees AS E, departments AS D WHERE E.department_id = D.department_id AND E.commission_pct IS NOT NULL;

# 查询 城市名中第二个字符为o的部门名和城市名

SELECT D.department_name, L.city FROM departments AS D, locations AS L WHERE D.location_id = L.location_id AND SUBSTR(L.city, 2, 1) = 'o';

SELECT D.department_name, L.city FROM departments AS D, locations AS L WHERE D.location_id = L.location_id AND L.city LIKE '_o%';

加分组

# 查询每个城市的部门个数

SELECT COUNT(*), L.city FROM departments D, locations L WHERE D.location_id = L.location_id GROUP BY L.city;

# 查询有奖金的每个部门名，部门的领导编号，部门的最低工资

SELECT D.department_name, E.manager_id, MIN(E.salary) FROM departments D, employees E WHERE D.department_id = E.department_id AND E.commission_pct IS NOT NULL GROUP BY D.department_name, E.manager_id

上面要查的 department_name 和 manager_id 不能保证是一一对应，需要在分组的时候全部加上。

加排序

# 查询 每个工种的工种名和员工个数，并按照员工个数降序

SELECT J.job_title, COUNT(*) FROM jobs J, employees E WHERE J.job_id = E.job_id GROUP BY job_title ORDER BY COUNT(*) DESC

三表连接

# 查询 员工名，部门名，所在的城市

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

三表连接也可以加分组，排序，筛选

多表等值连接的结果为多表的交集部分，多表的顺序没有要求，一般需要取别名。

2、非等值连接

CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

执行以上sql 脚本，就能新建一张表job_grades 表。

# 查询 员工的工资和工资级别

SELECT salary, grade_level FROM employees E, job_grades JG WHERE salary BETWEEN JG.lowest_sal AND JG.highest_sal;

3、自连接

和等值连接差不多，只不过2张表都是自己。

# 查询 员工名以及上级的名称

SELECT E1.employee_id 员工id, E1.last_name 员工姓名, E2.employee_id 领导Id, E2.last_name 领导姓名 FROM employees E1, employees E2 WHERE E1.employee_id = E2.manager_id

这里彻底体现了别名的重要性。

2.6.2 sql99标准

基本格式：

SELECT 查询列表 FROM 表1 别名 【连接类型】 JOIN 表2 别名 ON 连接条件 【WHERE 筛选条件】 【GROUP BY 分组条件】【HAVING 分组筛选条件】【ORDER BY 排序列表】

连接类型就像下面的内连接inner、外连接、交叉连接cross

与92标准的不同就是使用了join关键字，连接条件用on，与普通条件where 区分开了。

2.6.2.1 内连接 inner

SELECT 查询列表 FROM 表1 别名 INNER JOIN 表2 别名 ON 连接条件 【WHERE 筛选条件】 【GROUP BY 分组条件】【HAVING 分组筛选条件】【ORDER BY 排序列表】

1、等值连接

# 查询员工名、部门名

SELECT last_name, department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id

99标准的结果和原理和92标准的都是一样的，只是语法不同。

# 查询员工名中包含e的员工名和工种名【增加了筛选】

SELECT e.last_name, j.job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE e.last_name LIKE '%e%';

# 查询部门个数大于3的城市名和部门个数 （添加分组+筛选）

SELECT COUNT(*), l.city FROM locations l INNER JOIN departments d ON l.location_id = d.location_id GROUP BY city HAVING  COUNT(*)>3

1、先查每个城市的部门个数
2、再查部门个数大于3的城市名

# 查询哪个部门的员工个数大于3的部门名和员工个数，并按照个数排序（添加排序）

SELECT COUNT(*), department_name 
FROM departments d INNER JOIN employees e ON d.`department_id` = e.`department_id` 
GROUP BY department_name 
HAVING COUNT(*)>3 
ORDER BY COUNT(*)

# 查询员工名、部门名、工种名，并按部门名降序 （多表连接）

SELECT last_name,department_name,job_title FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN jobs j ON e.job_id = j.job_id ORDER BY d.department_id

上面的 inner 都可以省略。

99语法的内连接和92语法的等值连接是一样的，都是查询多表交集部分。

2、非等值连接

#查询员工的工资级别

SELECT salary, grade_level FROM employees e JOIN job_grades jg ON e.salary BETWEEN jg.lowest_sal AND jg.highest_sal;

上面省略了INNER关键字。连接条件ON 后面不再是等于了，而是一个区间范围。

#查询员工的工资级别,以及每个级别的个数,工资平均数保留2位小数,并按照个数降序。过滤掉个数小于 10的。

SELECT COUNT(*), ROUND(AVG(salary), 2), grade_level FROM employees e JOIN job_grades jg ON e.salary BETWEEN jg.lowest_sal AND jg.highest_sal GROUP BY grade_level HAVING COUNT(*)>10 ORDER BY COUNT(*) DESC;

3、自连接

#查询员工的名字、上级的名字

SELECT e.last_name 员工姓名, m.last_name 上级姓名 FROM employees e JOIN employees m ON e.manager_id = m.employee_id;

#查询员工的名字，姓名需要包含k,同时查询他的上级的名字

SELECT e.last_name 员工姓名, m.last_name 上级姓名 FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.last_name LIKE '%k%';

2.6.2.1 外连接

适合用于查询一个表中有，另一个表没有的场景！

这里就会分主表和从表。主表的数据都会与从表进行匹配，从表有就会显示相当于内连接，从表没有就会以null 值填充。

这里就可以发现，外连接得到的不仅仅是交集。而是主表全部内容与从表的匹配结果。

如何区分主表，从表？看连接方式

1、左外连接 left 【outer】

left join 左边的表是主表。

#查询 beauty 表与 boys 表 左连接全部数据

SELECT * FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id = bo.id

#查询 beauty 表中的男朋友不在 boys 表 中的 数据

SELECT b.* FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id = bo.id WHERE bo.id IS NULL

这里这个筛选条件实际是对 SELECT b.* FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id = bo.id 这个结果表进行筛选。

原始boys 表中是没有 id为null的记录。

#查询 查询没有员工的部门

SELECT d.* FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE employee_id IS NULL

2、右外连接 right 【outer】

right join 右边的表是主表。

#查询 beauty 表与 boys 表 右连接全部数据

SELECT * FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id = bo.id

#查询 beauty 表中的男朋友不在 boys 表 中的 数据

SELECT b.* FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id = bo.id WHERE bo.id IS NULL

可以看到，左外与右外就是表的顺序的区别。

3、全外连接 full 【outer】

mysql 不支持全外连接。这里只写原理

# mysql不支持。会报错

SELECT b.*,bo.* FROM beauty b FULL OUT JOIN boys bo ON b.boyfriend_id = bo.id;

这条语句在支持的数据库中，会查出交集部分，也会查出 beauty 表中的数据在boy表中没有的数据，还有 boy表中在beauty 表没有的数据。即全外的结果综合了左外和右外的结果。

2.6.2.1 交叉连接 cross

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

交叉连接的结果就是笛卡尔乘积的结果。

2.7 子查询

出现在其他语句的select语句，称为子查询或者内查询

子查询可以放在

select 后面：标量子查询

from 后面：表子查询

where后面、having后面：标量子查询、列子查询、行子查询

exist后面：表子查询

按照结果集的不同：

1、标量子查询【一行一列】

2、列子查询【1列多行】

3、行子查询【1列多行】

4、表子查询【多行多列】

子查询都会放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配单行操作符【> < = <= = <>】使用
列子查询，一般搭配多行操作符使用【in、any、some、all】
子查询的执行优先于主查询

2.7.1 where后面、having后面

1、标量子查询

案例1：

# 查询谁的工资比Abel高
# 1、查询 Abel的工资
SELECT salary FROM employees WHERE last_name = 'Abel'
# 2、查询比Abel高的工资
SELECT * FROM employees WHERE salary>(SELECT salary FROM employees WHERE last_name = 'Abel')

案例2：可以放多个子查询

# 查询jod_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、查询比 这个salary 多且job_id相同的的员工姓名，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');

案例3：使用了聚合函数

# 查询公司工资最少的员工的last_name,job_id,salary
# 1、查询公司工资最少是多少
SELECT MIN(salary) FROM employees;
# 2、查询最终结果
SELECT last_name, job_id, salary FROM employees WHERE salary=(SELECT MIN(salary) FROM employees);

案例4：having 后面使用子查询

# 查询最低工资大于50号部门的最低工资的部门id以及它的最低工资
# 1、查询50号部门的最低工资
SELECT MIN(salary) FROM employees WHERE department_id='50' ;
# 2、查询最终结果
SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id='50')

2、列子查询一般搭配多行操作符

In、Not In、any、some、all

in ：等于列表中的任意一个

any、some：和子查询返回的某一个值进行比较

any和some的意思一样，比如 a > any(列表)，只需要满足 a> 列表中任意一个即可。即a> 列表的最小值

all：和子查询的所有值进行比较

案例1：

# 查询 location_id 是1400 或1700 的部门中所有员工姓名
# 1、查询 location_id 是 1400 或 1700 的部门
SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400, 1700);
# 2、查询最终结果
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
# 1、查询 job_id 为 'IT_PROG' 部门 的工资
SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG';
# 2、查询最终结果
SELECT employee_id, last_name,job_id,salary FROM employees WHERE salary < ANY(SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'

# 等价于以下写法：
SELECT employee_id, last_name,job_id,salary FROM employees WHERE salary < (SELECT DISTINCT MAX(salary) FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'

3、行子查询

案例1：

# 查询员工编号最小，且工资最高的员工信息
# 1、查询最小的员工编号
SELECT MIN(employee_id) FROM employees;
# 2、查询最高的员工工资
SELECT MAX(salary) FROM employees;
# 3、查询最终结果
SELECT * FROM employees 
WHERE employee_id = (SELECT MIN(employee_id) FROM employees) 
AND salary = (SELECT MAX(salary) FROM employees)

上面的写法是以前的写法，这种写法可以被行子查询代替。见下面的写法

# 查询员工编号最小，且工资最高的员工信息
SELECT * FROM employees WHERE (employee_id, salary) = (
SELECT MIN(employee_id), MAX(salary) FROM employees
)

使用行子查询的条件比较苛刻，要求条件都是等于。

2.7.2 放在 select 后面

select 后面仅仅支持标量子查询

案例1：

# 查询每个部门的员工个数作为新的一列
# 1、按照部门查询员工个数
SELECT COUNT(*) FROM employees WHERE department_id = '';
# 2、查询最终结果
SELECT d.*, (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) AS 个数 FROM departments d;

案例2：

# 查询员工号为102 的部门名
# 1、按照 员工号为102 的部门id
SELECT department_id FROM employees WHERE employee_id = '102';
# 2、查询最终结果
SELECT department_name FROM departments WHERE department_id = (SELECT department_id FROM employees WHERE employee_id = '102')

# 用放在select 后面的子查询

SELECT ( SELECT department_name FROM departments d JOIN employees e ON d.department_id=e.department_id WHERE e.employee_id=102
)

2.7.3 from后面

子查询放在from后面就是充当一张表

# 查询每个部门的平均工资的工作等级
# 1、查询每个部门的平均工资
SELECT AVG(salary),department_id FROM employees GROUP BY department_id
# 2、查询工资对应的工资等级
SELECT grade_level FROM job_grades WHERE salary BETWEEN lowest_sal AND highest_sal
# 3、综合
SELECT j.grade_level, a.avg, a.department_id FROM (SELECT AVG(salary) AS avg, department_id FROM employees GROUP BY department_id) a JOIN job_grades j ON a.avg BETWEEN j.lowest_sal AND j.highest_sal

这里面中间表SELECT AVG(salary) AS avg, department_id FROM employees GROUP BY department_id 必须取一个别名，这里是叫a

而且里面的字段 AVG(salary) 也必须取别名。

2.7.4 exists后面

exists 后面就是

相关子查询

SELECT EXISTS(SELECT employee_id FROM employees)

结果是 1

exists 就是返回是否存在，有就是返回1，没有返回 0

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=26000)  # 结果是0

# 查询有员工的部门名

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

exists 是先执行外查询，再根据结果去过滤。

即先执行

SELECT department_name FROM departments d

然后对结果进行过滤

WHERE EXISTS (SELECT * FROM employees e WHERE e.department_id = d.department_id  )

2.7.5 案例

#查询各部门的工资比本部门的平均工资高的员工的员工号，姓名，工资
# 1、查询各部门的平均工资
SELECT AVG(salary), department_id FROM employees GROUP BY department_id;
# 2、在1的基础上连接查询结果
SELECT employee_id, last_name, salary FROM employees e JOIN (SELECT AVG(salary) AS avg_salary, department_id FROM employees GROUP BY department_id) avg_sa ON avg_sa.department_id = e.department_id WHERE e.salary > avg_sa.avg_salary

# 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
# 1、先查姓名中包含字母u的员工的部门id集合
SELECT DISTINCT department_id FROM employees WHERE last_name LIKE "%u%";
# 2、在1的基础上进行查询
SELECT last_name, department_id FROM employees WHERE department_id IN (SELECT DISTINCT department_id FROM employees WHERE last_name LIKE "%u%")

2.8 分页查询

分页查询用的非常多，重点。

使用的是 limit 关键字，放在查询语句的最后。

limit offset,size;

offset 是起始索引，从0开始

size是要显示的个数

# 查询前5条员工信息
SELECT * FROM employees LIMIT 0,5;
# 如果是第一条开始，offset 可以省略
SELECT * FROM employees LIMIT 5;

查询第11条到第25条

# 查询第11条到第25条数据
SELECT * FROM employees LIMIT 10,15;

有奖金的员工信息，并且工资较高的前10名

SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;

一般传来的是当前页page和每页个数size

limit  (page-1)*size, size;

2.9 联合查询

union 关键字

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

#查询部门编号大于90或者邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' 
UNION
SELECT * FROM employees WHERE department_id > 90;

union可以使用多个，即联合多个查询语句。

什么时候用呢？如果是2张表的结果联合成一个结果时，而且这2张表没有连接关系，用union联合比较多。

比如查询某个关键字是多张表出现的结果。

注意：

1、联合查询的多条语句的查询字段个数必须一致，如果你一个查2列，一个查3列是没法联合到一起的，这是必然要求。

2、查询结果的字段名默认是第一条语句的字段名，每一条查询语句的字段需要对应，不然结果就会乱掉，但是不会报错。

3、联合查询会自动去重！如果不想去重需要加上 ALL 关键字，即 union all

原文链接：https://blog.csdn.net/AI_study/article/details/120447663

1、准备

2、Dql语言

2.1 基础查询

2.2 条件查询

2.3 排序查询

2.4 常见函数

2.4.1 单行函数

2.4.2 分组函数

2.5 分组查询

2.6 连接查询【多表查询】

2.6.1 sql92标准

2.6.2 sql99标准

2.7 子查询

2.7.1 where后面、having后面

2.7.2 放在 select 后面

2.7.3 from后面

2.7.4 exists后面

2.7.5 案例

2.8 分页查询

2.9 联合查询

你可能也喜欢