MySQL笔记(2)——DQL

已于 2024-04-15 03:03:19 修改
阅读量941 收藏 18
点赞数 19
分类专栏： MySQL 文章标签： mysql 笔记 数据库
于 2024-01-21 09:21:50 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_59199444/article/details/135710100
版权

一、基础查询

语法：select 查询列表 from 表名;

特点：

①查询列表可以是：字段（必须跟表名）、常量值、表达式、函数。

②查询的结果是一个虚拟的表格，没有存储。

1.查询单个字段

SELECT last_name FROM employees;

2.查询表中的多个字段

SELECT last_name,salary,email FROM employees;

注：查询出的字段顺序不按照原表顺序，而按照语句中的顺序。

3.查询表中的所有字段

方式一：字段通过双击表中字段获得

缺点：需全部点击，较为麻烦；

优点：可以按照自己的需求决定字段的顺序

SELECT `employee_id`,`first_name` FROM employees;
/*

注：

①`为着重号，可以删除，在字段为关键字时可加上着重号来区分。

②选中命令，f12格式化。

*/

方式二：*（所有字段）

优点：方便；

缺点：与原表字段顺序一致。

SELECT * FROM employees;

4.查询常量值

SELECT 100;
SELECT 'john';

5.查询表达式

SELECT 100*98;

6.查询函数

SELECT VERSION();
#查询版本

7.起别名

方式一：

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

方式二：as省略

SELECT last_name 姓,first_name 名 FROM employees;

特殊情况：别名中有特殊符号（空格或#），把别名加上双引号""（单引号也可，双引号更规范）。

案例：查询salary，显示结果为out put。

SELECT salary "out put" FROM employees;

好处：

①便于理解。

②如果要查询的字段有重名的情况，使用别名区分开来。

8.去重

案例：查询员工表中涉及的所有的部门编号。

SELECT DISTINCT department_id FROM employees;

9.+的作用

java中的+号：

①运算符，两个操作数都为数值型。

②连接符，只要有一个操作符为字符串。

mysql中的+号仅仅有一个功能——运算符，分为以下几种情况：

①两个操作符都为数值型，则做加法运算：

SELECT 100+88;

②其中一方为为字符型，试图将字符型数值准换成数值型，如果转换成功，则继续做加法运算：

SELECT '100'+98;

③如果转换失败，则将字符型数值转换成0：

SELECT 'john'+98;
#结果为98

④只要其中一方为null，则结果肯定为null：

SELECT NULL+98;

那mysql中如何实现字段的拼接？使用concat函数。

案例：员工名和姓连接成1个字段，并显示为姓名。

SELECT CONCAT('a','b','c') AS 结果;
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

null和任何操作符拼接结果都为null，如果列中含有null，如何进行拼接？使用ifnull函数。

SELECT IFNULL(commission_pct,0) FROM employees;

二、条件查询

语法：select 查询列表 from 表名 where 筛选条件;

执行顺序：①表名from②筛选where③查询select

分类：

①按条件表达式筛选

        条件运算符：>   <   =   !=   <>(推荐) >=   <=

②按逻辑表达式筛选

        逻辑运算符：&&   ||   !

                             and or not（推荐使用）

③模糊查询：like、between and、in、is null/is not null

1.按条件表达式筛选

案例：查询工资>12000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE salary > 12000 ;

案例：查询部门编号不等于90号的员工名和部门编号。

SELECT 
  last_name,
  department_id 
FROM
  employees 
WHERE department_id <> 90 ;

2.按逻辑表达式筛选

用来连接条件表达式

案例：查询工资在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;

3.模糊查询

①like

a.不区分大小写

b.一般和通配符搭配使用：

%代表任意多个字符，包括0

_任意单个字符

c.转换通配符为正常字符：

方式一：'\通配符'

方式二：'任意字符通配符 ' escape '任意字符'

案例：查询员工中包含字符a的员工信息。

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

案例：查询员工名中第三个字符为n，第五个字符为l员工名和工资。

SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE last_name LIKE '__n_l%' ;

案例：查询员工名中第二个字符为_的员工名。

需转化_字符

#方法一：
SELECT 
  last_name 
FROM
  employees 
WHERE last_name LIKE '_\_%' ;

#方法二（推荐）：
SELECT 
  last_name 
FROM
  employees 
WHERE last_name LIKE '_$_%' ESCAPE '$' ;
#$可指定

②between and

a.可以提升语句的简洁度

b.包含临界值：临界值不可交换位置，等价于>=左侧值且<=右侧值。

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

SELECT 
	* 
FROM
	employees 
WHERE 
	employee_id BETWEEN 100 AND 120;

③in

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

特点：

a.使用in提高语句简洁度

b.in列表的值类型必须一致或兼容

c.不支持通配符，因为in相当于=，不支持like

案例：查询员工的工种编号是IT_PROT、AD_VP、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

案例：查询没有奖金的员工名和奖金率。

SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NULL ;

安全等于

1.<=>可以用于判断null值

SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct <=> NULL ;

2.<=>也可以用于查询数值

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

is null   VS   <=>

is null：仅可以判断null值，可读性较高，推荐

<=>：即可以判断null值，又可以判断普通数值，但可读性较差

案例：产讯员工号为176的员工的姓名、部门和年薪。

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

三、排序查询

语法：

select 查询列表

from 表名

where 筛选条件

order by 排序列表 【asc升序|desc降序】（若不写默认升序）

特点：

a.支持单个字段、多个字段，表达式、函数、别名

b.一般放在查询语句的最后面，但limit字句除外

执行顺序：from where select order by

案例：查询员工信息，要求工资从高到低排序。

SELECT 
  * 
FROM
  employees 
ORDER BY salary DESC ;

案例：查询部门编号大于等于90的员工信息，按入职时间的先后排序。

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

案例：按年薪的高低显示员工的信息和年薪。【表达式排序】

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

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

案例：按姓名长度显示员工的姓名和工资。【按函数排序】

SELECT 
  LENGTH(last_name) 字节长度,
  last_name,
  salary 
FROM
  employees 
ORDER BY 字节长度 DESC ;

案例：查询员工信息，要求先按工资排序，再按员工编号排序。【按多个字段排序】

SELECT 
  * 
FROM
  employees 
ORDER BY salary ASC,
  employee_id DESC ;

四、常见函数

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

好处：

a.隐藏了实现细节

b.提高了代码的重用性

调用：select 函数名(实参列表) 【from 表名 用到表中字段】

关注：函数名、函数功能

分类：

单行函数：作处理使用，传一个值返回一个值eg.concat、length、ifnull

分组函数：做统计使用，传一组值返回一个值，又称为统计函数，聚合函数，组函数

（1）单行函数

1.字符函数

length、concat、upper/lower、substr/instr、trim/ltrim/rtrim、lpad/rpad、replace

①length

用于获取参数值的字节。utf-8英文占一个字节，中文占三个字节；jdk中文占两个字节。

show variables like '%char%'
②concat

拼接字符串

SELECT 
  CONCAT(last_name, ' ', first_name) 
FROM
  employees ;
③upper、lower

案例：将姓变大写，名变小写，然后拼接。

SELECT 
  CONCAT(
    UPPER(last_name),
    ' ',
    LOWER(first_name)
  ) AS 姓名 
FROM
  employees ;
④substr/substring

substr(str,从哪开始,字符长度)

注：

a.索引从1开始

b.没有字符长度默认后面所有字符

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


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

案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来。

SELECT 
  CONCAT(
    UPPER(SUBSTR(last_name, 1, 1)),
    SUBSTR(last_name, 2),
    ' ',
    UPPER(SUBSTR(first_name, 1, 1)),
    SUBSTR(first_name, 2)
  ) AS 姓名 
FROM
  employees ;
⑤instr

返回子串第一次出现的索引，如果找不到返回0。

SELECT 
  INSTR(
    '杨不悔殷六侠爱上了殷六侠',
    '殷六侠'
  ) AS out_put ;
⑥trim(ltrim、rtrim)

trim(leading|both|trail remstr from str)

leading功能等于ltrim，trailing功能等于rtrim，both两边剪裁，省略默认both

#去掉前后空格
SELECT LENGTH(TRIM('   张翠山   ')) AS out_put;
#结果：9

#去掉前后a
SELECT TRIM('a' FROM 'aaaaaaaa张aaaaaa翠山aaaaa') AS out_put;
#结果：张aaaaaa翠山
⑦lpad

lpad(str,len,padstr)

用指定字符实现左填充指定长度

注：lpad和rpad函数都是在str长度<len时用padstr填充，str长度>len时从左保留len长度的字符。

SELECT LPAD('殷素素',10,'*') AS 结果;
#结果：*******殷素素

SELECT LPAD('殷素素',2,'*') AS 结果;
#结果：殷素
⑧rpad

用指定字符实现右填充指定长度

SELECT RPAD('殷素素',12,'ab') AS 结果;
#结果：殷素素ababababa

SELECT RPAD('殷素素',2,'ab') AS 结果;
#结果：殷素
⑨replace

全部替换

SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS 结果;
#结果：赵敏赵敏张无忌爱上了赵敏

2.数学函数

round/ceil/floor/truncate、mod

①round

四舍五入

SELECT ROUND(-1.55);
#结果：-2

SELECT ROUND(1.567,2);
#结果：1.57
②ceil

向上取整，返回>=该参数的最小整数

SELECT CEIL(-1.702);
#结果：-1
③floor

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

SELECT FLOOR(-9.99);
#结果：-10
④truncate

截断小数点后

SELECT TRUNCATE(1.6999,1);
#结果：1.6
⑤mod

取余

SELECT MOD(10,3);#mod(a,b)=a-a/b*b
SELECT 10%3;

#快速记忆：余数正负与被除数一致
SELECT MOD(-10,-3);
#结果：-1
SELECT MOD(-10,3);
#结果：-1
SELECT MOD(10,-3);
#结果：1

3.日期函数

now/curdate/curtime、year/month/day/hour/minute/second、str_to_date/date_format(格式)

①now

返回当前系统日期+时间

②curdate

返回当前系统日期，不包含时间

③curtime

返回当前时间，不包括日期

④获取指定的部分，年月日小时分钟秒
#年
SELECT YEAR(NOW());
SELECT YEAR(CURDATE());
SELECT YEAR('1998-1-1');
SELECT last_name,YEAR(hiredate) AS 入职时间
FROM employees;

#月
SELECT MONTH(NOW()) 月;
#结果：1
SELECT MONTHNAME(NOW()) 月;
#结果：January

#日
SELECT DAY(NOW());

#小时
SELECT HOUR(NOW());

#分钟
SELECT MINUTE(NOW());

#秒
SELECT SECOND(NOW());

str-to-date将字符通过指定的格式转换成日期

%Y四位年份
%y两位年份
%m两位月份
%c去零月份
%d两位日期
%H24小时制
%h12小时制
%i两位分钟
%s两位秒数

案例：查询入职日期为4-3 1992的员工信息

SELECT *
FROM employees
WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');

 date_format：将日期转换成字符

案例：查询有奖金的员工名和入职日期

SELECT last_name,DATE_FORMAT(hiredate,'%c月/%d日 %Y年') AS 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

4.其他函数

SELECT VERSION();#查看版本号
SELECT DATABASE();#查看当前库
SELECT USER();#查看用户

5.流程控制函数

①if函数 if else 函数
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') AS 备注
FROM employees;
②case结构

使用一：

SELECT salary AS 原始工资,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;

使用二：

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）分组函数：

功能：用做统计使用，又称为聚合函数或同级函数或组函数

分类：

sum、avg、max、min、count

语法：select sum()

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) AS 和,AVG(salary) AS 平均 FROM employees;

2.参数支持哪些类型

sum、avg：数值型

max、min、count：任何类型

3.是否忽略null值：

所有分组函数都忽略null值

4.可以和distinct搭配使用：种类个数

SELECT COUNT(DISTINCT salary) FROM employees;

5.count函数的详细介绍

SELECT COUNT(*) FROM employees;
#排除只要有一个字段为null的行，用来统计任何一个字段都不为null的行。
SELECT COUNT(1) FROM employees;
#相当于多了一个都为1的列，用来统计任何一个字段都不为null的行，1可以换成任意常量。
/*

效率：
MYISAM存储引擎下，COUNT(*)的效率高；
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些（需要筛选判断）。

*/

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

SELECT AVG(salary),employee_id FROM employees;
#语法没有错误，但是不推荐这样写，因为avg只有一行数据，employee_id一行无意义，无法形成规矩的表格。
#案例：查询公司员工工资的最大值、最小值、平均值和总和。
SELECT MAX(salary),MIN(salary),ROUND(AVG(salary),2),SUM(salary)
FROM employees;

#案例：查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;#前-后

#案例：查询部门编号为90的员工个数
SELECT COUNT(*) 个数
FROM employees
WHERE department_id=90;

五、分组查询

语法：

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

from 表名

where 筛选条件

group by 分组的列表

order by

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

#案例：查询每个工种的最高工资。
SELECT job_id,MAX(salary) 最高工资
FROM employees
GROUP BY job_id;

#案例：查询每个位置上的部门个数
SELECT location_id,COUNT(*) 部门个数#注意这里不谢department_id,排除所有null，而不是只排除该字段的null
FROM departments
GROUP BY location_id;

#案例：查询邮箱中包含a字符的，每个部门的平均工资
SELECT department_id,AVG(salary)
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

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


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

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

#案例：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

总结：

特点：

分组查询中的筛选条件分为两类 

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

①分组函数作条件肯定是放在having字句中

②能用分组前筛选的，就优先考虑使用分组前筛选，考虑性能。

2.group by子句支持单个字段分组和多个字段分组，多个字段之间用逗号隔开没有顺序要求，表达式或函数（用得较少）

3.也可以添加排序（排序放在整个分组查询最后）

#按表达式或函数分组
#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT LENGTH(last_name) 姓名长度,COUNT(*) 个数
FROM employees
GROUP BY 姓名长度
HAVING 个数>5;


#按多个字段分组和排序
#案例：查询每个部门每个工种的员工的平均工资，并按平均工资的高低显示
SELECT department_id,job_id,AVG(salary) 平均工资
FROM employees
GROUP BY department_id,job_id
ORDER BY 平均工资 DESC;
#GROUP BY、having、order by都支持别名
#案例：查询各job_id的员工工资的最大值、最小值、平均值、总和，并按job_id升序
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;

#案例：查询员工最高工资和最低工资的差距
SELECT MAX(salary)-MIN(salary) 差距
FROM employees;

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

#案例：查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
SELECT department_id, COUNT(*),AVG(salary) 平均工资
FROM employees
GROUP BY department_id
ORDER BY 平均工资 DESC;

#案例：选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;

六、连接查询

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

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

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

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

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

分类：

按年代分类：

sql92标准：仅仅支持内连接

sql99标准（推荐）：支持内连接+外连接（左加右）+交叉连接

按功能分类：

内连接：

等值 非等值 自连接

外连接

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

交叉链接

1.sql92标准

等值链接

特点：

①多表等值连接的结果为多表的交集部分

②n表链接，至少需要n-1连接条件

③多表的顺序没有要求

④一般需要为表起别名

⑤可以搭配前面介绍的所有子句，比如：排序分组筛选

#案例：查询女生名和对应的男生名
SELECT NAME,boyName
FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;\
#案例：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

为表取别名：

①提高语句简洁度

②区分多个重名字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

#案例：查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.job_id=j.job_id;
#job_id在两表中都有，不加表名会引起歧义。

3.两个表的顺序可以调换

4.可以加筛选

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

#案例：城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';

5.可以加分组

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

#案例：查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`department_id`;

6.可以加排序

#案例：查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT job_title,COUNT(*) 个数
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY 个数 DESC;

7.三表连接

#案例：查询员工名、部门名和所在城市
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.非等值连接

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

3.自连接

#案例：查询员工名和上级名称
SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`manager_id`
#案例：查询员工表中的job_id中包含a和e的，并且a在e前面
SELECT job_id
FROM employees
WHERE job_id LIKE '%a%e%';

2.sql99标准

select 查询列表

from 表名1 别名 【连接类型】

join 表2 别名

on 连接条件

where 筛选条件

group by

having

order by

【连接类型】

内连接：inner

外连接：

left outer

right outer

full outer

交叉链接 cross

内连接

inner可省略

提高分离性，便于阅读

92和99等值连接效果效果是相同的，都是查询交集部分

1.等值链接

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

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

#案例：查询部门个数大于3的城市名和部门个数
SELECT city,COUNT(*)
FROM locations l
INNER JOIN departments d
ON l.`location_id`=d.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

#案例：查询哪个部门的部门员工个数大于3的部门名和员工个数，并按照个数降序
SELECT department_name,COUNT(*)
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_name`
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#案例：查询员工名、部门名、工种名，并按部门名降序
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_name` DESC;
#三表连接时存在顺序问题，执行逻辑时先将前两个表进行连接，再将第三个表与前两个连接好的大表进行连接，所以要保证前两个表是可连接的

2.非等值链接

#查询员工的工资级别
SELECT last_name,salary,grade_level
FROM employees s
INNER JOIN job_grades j
ON salary BETWEEN lowest_sal AND highest_sal;
#查询每个工资级别个数大于20的个数,并且按工资级别降序
SELECT grade_level 级别,COUNT(*) 个数
FROM employees s
INNER JOIN job_grades j
ON salary BETWEEN lowest_sal AND highest_sal
GROUP BY grade_level
HAVING 个数>20
ORDER BY 级别 DESC;

3.自连接

#查询姓名中包含k的员工的名字以及其上级的名字
SELECT e.`last_name`,m.`last_name`
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`
WHERE e.last_name LIKE '%k%';

外连接

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

特点：

1.主表中的所有记录，如果从表中有和他匹配的，则显示匹配的值，如果从表中没有和他匹配的，则显示null，外连接结果=内连接结果+主表中有而从表中没有的记录

2.左外连接，left join左边的是主表

右外连接，right join右边的是主表

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

4.全外连接=内连接+表一有但表二没有的+表二有但表一没有的

#外连接
#左右外连接
#案例：查询哪个部门没有员工
SELECT department_name,employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE employee_id IS NULL;

交叉链接

笛卡儿乘积

92vs99

功能：sql99支持的较多

可读性：sql99实现连接条件和筛选条件的分离，可读性较高

7 子查询

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

分类：

分类方式

按照子查询

出现位置

select后面标量子查询
from后面表子查询
where/having后面标量、列、行
exists后面表子查询

按照结果集

行列数不同

一行一列标量子查询
多行一列列子查询
一行多列行子查询
多行多列表子查询

特点：

1.子查询放在小括号内

2.子查询一般放在条件的右侧

3.标量子查询，一般搭配着单行操作符使用><=

   列子查询，一般搭配着多行操作符使用：in any/some all

4.子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果

7.1 标量子查询
#案例：谁的工资比abel高？
#第一步：查询abel的工资
SELECT salary
FROM employees
WHERE last_name='abel';
#第二部：查询满足条件的员工信息
SELECT *
FROM employees
WHERE salary>(

	SELECT salary
	FROM employees
	WHERE last_name='abel'

);

#案例：查询job_id与141号相同，salary比143号多的员工的姓名、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

);

#案例：查询工资最少员工的姓名、job_Id、和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(

	SELECT MIN(salary)
	FROM employees

);

#案例：查询最低工资大于50号部门的最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(

	SELECT MIN(salary)
	FROM employees
	GROUP BY department_id
	HAVING department_id=50

);

非法使用子查询

非法使用标量子查询

1.子查询为列子查询or行子查询

2.子查询结果为null

7.2 列子查询

返回多行，要使用多行比较操作符

操作符含义
in/not in

任意一个

any|some某一个
all所有值

>any/<any集合：大于集合最小值/小于最大值

>all/<all集合：大于集合最大值/小于最小值

=any：in

<>all：not in

#案例：查询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)

);

IN等价于=ANY
NOT IN等价于<>ALL


#案例：查询其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的工号、姓名、job_id、salary
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';
3.行子查询

#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(

	SELECT MIN(employee_id),MAX(salary)
	FROM employees

);
#局限性：两个条件使用相同的判断符
select后面

仅仅支持标量子查询

#案例：查询每个部门的员工个数
SELECT d.*,(

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

) 个数
FROM departments d;

#案例：查询员工号等于102的部门名
#方法一：
SELECT (

	SELECT department_name
	FROM departments d
	WHERE d.department_id=e.`department_id`

) 部门名
FROM employees e
WHERE e.employee_id=102;
#方法二：
SELECT department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.department_id
WHERE employee_id=102;
from后面

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

#案例：查询每个部门的平均工资及其工资等级
SELECT department_id,a_salary,grade_level
FROM (

	SELECT department_id,AVG(salary) a_salary
	FROM employees
	GROUP BY department_id

) a_dep
INNER JOIN job_grades j
ON a_salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;

四、exists后面（相关子查询）

语法：exists（完整的查询语句 布尔类型）

结果：1或0

语法：

SELECT column1 FROM t1 WHERE [conditions] and EXISTS (SELECT * FROM t2 );

执行过程
1、首先进行外层查询，在表t1中查询满足条件的column1
2、接下来进行内层查询，将满足条件的column1带入内层的表t2中进行查询，
3、如果内层的表t2满足查询条件，则返回true，该条数据保留
4、如果内层的表t2不满足查询条件，则返回false，则删除该条数据
5、最终将外层的所有满足条件的数据进行返回

#案例：查询没有朋友的男生信息
#子查询
SELECT *
FROM boys
WHERE id NOT IN(

	SELECT boyfriend_id
	FROM beauty

);
#exists
SELECT *
FROM boys bo
WHERE NOT EXISTS(

	SELECT *
	FROM beauty b
	WHERE b.`boyfriend_id`=bo.`id`

);

子查询案例

#案例：查询和zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(

	SELECT department_id
	FROM employees
	WHERE last_name='zlotkey'

);

#案例：查询工资比公司平均工资高的员工号、姓名、工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(

	SELECT AVG(salary)
	FROM employees

);

#案例：查询各部门中工资比本部门平均工资高的员工号、姓名和工资
#步骤一：查询各部门平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;
#步骤二：连接employees表和部门平均工资表，并筛选
SELECT employee_id,last_name,salary
FROM employees e
INNER JOIN (

	SELECT department_id,AVG(salary) a
	FROM employees
	GROUP BY department_id

) a
ON e.department_id=a.department_id
WHERE salary>a;

#案例：查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN(

	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE "%u%"

);
#案例：查询在部门location_id为1700的部门工作的员工和员工号
SELECT employee_id
FROM employees
WHERE department_id=ANY(

	SELECT department_id
	FROM departments
	WHERE location_id=1700

);

#案例查询管理者是K_ing的员工姓名和工资。
SELECT last_name,salary
FROM employees
WHERE manager_id=ANY(

	SELECT employee_id
	FROM employees
	WHERE last_name="k_ing"

);
#不确定结果集类型的，可以直接使用in，不用考虑结果集类型。

#查询工资最高的员工姓名，要求first_name和last_name显示为一列，列名为姓名
SELECT CONCAT(first_name,last_name) 姓名
FROM employees
WHERE salary=(

	SELECT MAX(salary)
	FROM employees

);

 八、分页查询limit

应用场景：当要显示的数据一页显示不全，需要分页来提交sql请求。

语法：——执行顺序

select 查询列表——⑦

from 表——①

【join type】join 表2——②

on 连接条件——③

where 筛选条件——④

group by 分组字段——⑤

having 分组后的筛选——⑥

orderby 排序字段——⑧

limit offset,size——⑨

offset：要显示条目的索引，起始从0开始，若从0开始可不写；

size：要显示的条目个数。

每一步会形成一个虚拟表

特点:

①limit语句放在查询语句的最后，执行也在最后

②公式：第几页page，一页的条目数size

select 查询列表

from 表

limit (page-1)*size,size

#案例：查询前五条员工信息
SELECT *
FROM employees
LIMIT 0,5;
SELECT *
FROM employees
LIMIT 5;

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

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

案例：

#案例：查询工资最低的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary=(

	SELECT MIN(salary)
	FROM employees

);

#案例：查询平均工资最低的部门信息
#方式一：分组后直接排序、limit出最低工资，此方法不严谨，因为如果存在并列最低工资的部门，那么会漏项。
SELECT *
FROM departments
WHERE department_id=(

	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MIN(salary) ASC
	LIMIT 1
	
);
#方式二：

SELECT *
FROM departments
WHERE department_id=(

	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING sal_avg=(

		SELECT MIN(sal_avg)
		FROM (
		
			SELECT department_id,AVG(salary) sal_avg
			FROM employees
			GROUP BY department_id	
		
		) dep_avg

	)

);



#案例：查询平均工资最低的部门信息和该部门的平均工资
SELECT *
FROM (

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

) sal_avg
JOIN (

	SELECT *
	FROM departments
	WHERE department_id=(

		SELECT department_id
		FROM employees
		GROUP BY department_id
		ORDER BY MIN(salary) ASC
		LIMIT 1
	)
	
) dep
ON dep.department_id=sal_avg.department_id;
#案例：查询平均工资最高的job信息
SELECT *
FROM jobs
WHERE job_id=(

	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY MIN(salary) DESC
	LIMIT 1
	

);
#查询平均工资高于公司平均工资的部门有哪些？
SELECT department_id
FROM (

	SELECT department_id,AVG(salary) sal_avg
	FROM employees
	GROUP BY department_id

) dep_sal
WHERE dep_sal.sal_avg>(

	SELECT AVG(salary)
	FROM employees

);

#案例：查询公司所有manager的详细信息
SELECT *
FROM employees
WHERE employee_id=ANY(

	SELECT DISTINCT manager_id
	FROM employees

);

#案例：各部门中最高工资中最低工资的那个部门的最低工资是多少
SELECT MIN(sal_max)
FROM(

	SELECT department_id, MAX(salary) sal_max
	FROM employees
	GROUP BY department_id

) dep_max;

#案例：查询平均工资最高的部门的manager的详细信息
SELECT last_name,department_id,email,salary
FROM employees
WHERE manager_id=(

	SELECT manager_id
	FROM departments
	WHERE department_id=(
		SELECT department_id
		FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary) DESC
		LIMIT 1
	)
);

九、联合查询union

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

语法：

查询语句1

union

查询语句2

union

。。。

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：

①要求多条查询语句的查询列数时一致的！列数一致

②要求多条查询语句的每一列的类型和顺序最好一致（不会报错，但影响可读性）顺序一致

③union关键字默认去重，如果使用union all可以包含重复项

④列名取第一条查询的列名

#案例：查询部门编号》90或邮箱中包含a的员工信息
SELECT *
FROM employees
WHERE department_id>90
OR email LIKE '%a%';

SELECT *
FROM employees
WHERE department_id>90
UNION
SELECT *
FROM employees
WHERE email LIKE '%a%';

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值