MySQL 数据库 day-03

2401_84814920

于 2024-05-16 15:13:44 发布

阅读量374

点赞数 4

分类专栏：程序员文章标签：数据库 mysql oracle

本文链接：https://blog.csdn.net/2401_84814920/article/details/138962518

版权

程序员专栏收录该内容

116 篇文章 0 订阅

订阅专栏

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;

四、分组函数

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

分类： sum 求和

avg 平均值

max 最大值

min 最小值

count 计算个数

**特点：

1、sum avg 只用来处理数值型

max min count 可以用来处理所有类型

2、以上分组函数都忽略null值

3、和distinct搭配

4、count(*) 一般用来统计行数

5、和分组函数一同查询的字段要求是group by后的字段**

1、简单使用

SELECT SUM(salary) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT MIN(salary) FROM employees;

SELECT AVG(salary) FROM employees;

SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和,MAX(salary) 最大,MIN(salary) 最小,AVG(salary) 平均值

FROM employees;

2、和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

3、count函数的详细介绍

count() 一般用来统计行数*

SELECT COUNT(*) FROM employees; #107 统计行数

SELECT COUNT(1) FROM employees; #107 统计1的个数即统计行数

4、和分组函数一同查询的字段有限制

SELECT AVG(salary),employee_id FROM employees; #报错

五、分组查询

**语法：

select 分组函数,列(要求出现在group by的后面)

from 表

[where 筛选条件]

group by 分组的列表

[order by 子句]**

**注意:

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

**特点：

1、分组查询终的筛选条件分为两类

数据源　　　　　　位置　　　　　　　　关键字

分组前筛选　原始表　　　　　 group by子句的前面　　 where

分组后筛选　分组后的结果表　group by子句的后面　　　having**

①分组条件做条件肯定是放在having子句中

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

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

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

#案例:查询每个部门的平均工资**

SELECT AVG(salary),department_id

FROM employees

GROUP BY department_id;

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

SELECT MAX(salary),job_id

FROM employees

GROUP BY job_id;

案例：查询每个位置上的部门个数

SELECT COUNT(*),location_id

FROM departments

GROUP BY location_id;

添加分组前的筛选条件

案例：查询邮箱终包含a字符的，每个部门的平均工资

SELECT AVG(salary),department_id

FROM employees

WHERE email LIKE ‘%a%’

GROUP BY department_id

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

SELECT MAX(salary),manager_id

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY manager_id;

添加分组后的筛选条件

_案例1：查询哪个部门的员工个数>2

①查询每个部门的员工个数

②根据①的结果进行筛选，查询哪个部门的员工个数>2_

SELECT COUNT(*),department_id

FROM employees

GROUP BY department_id

HAVING COUNT(*)>2;

案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

_①查询每个工种有奖金的员工的最高工资

②根据①的结果进行筛选，查询哪个员工的最高工资>12000_

SELECT MAX(salary),job_id

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING MAX(salary>12000);

_案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号以及其最低工资

①查询领导编号>102的每个领导手下的最低工资_

SELECT MIN(salary),manager_id

FROM employees

WHERE manager_id>102

GROUP BY manager_id

HAVING MIN(salary)>5000;

按表达式或函数分组

_#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

#①查询每个长度的员工个数

#②查询哪一组的员工个数大于5_

SELECT COUNT(*),LENGTH(last_name) len_name

FROM employees

GROUP BY len_name;

HAVING COUNT(*)>5;

按多个字段分组

案例：查询每个部门每个工种的员工的平均工资

SELECT AVG(salary),department_id,job_id

FROM employees

GROUP BY department_id,job_id;

添加排序

案例：查询部门编号部位mull每个工种的员工平均工资>10000的，并按平均工资从小到大排序

SELECT AVG(salary),department_id,job_id

FROM employees

WHERE department_id IS NOT NULL

GROUP BY department_id,job_id

HAVING AVG(salary)>10000

ORDER BY AVG(salary) DESC;

六、连接查询

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

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

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

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

分类：

按年代分类：

sql92标准:仅仅支持内连接

sql99标准[推荐]：支持内连接+外连接(左外+右外)+交叉连接

按功能分类：

内连接：

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

外连接：

左外连接
右外连接
全外连接
交叉连接

SELECT * FROM beauty;

SELECT * FROM boys;

SELECT NAME,boyName FROM boys,beauty

WHERE beauty.boyfriend_id=boys.id;

(一)、sq192标准

1、等值连接

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

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

③ 一般需要为表名起别名

④ 多表的顺序没有要求

⑤ 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选**_

(1)、简单使用

案例1：查询女神名字和对应的男神名

SELECT NAME,boyName

FROM boys,beauty

WHERE beauty.boyfriend_id=boys.id;

案例2：查询员工名和对应的部门名

SELECT last_name,department_name

FROM employees,departments

WHERE employees.department_id=departments.department_id;

(2)、为表起别名

_**好处： ①提高语句的简洁度

②区分多个重名的字段**_

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

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

SELECT last_name,e.job_id,job_title #这里只能用e.,使用employess.报错

FROM employees AS e,jobs j

WHERE e.job_id=j.job_id;

(3)、可以加筛选

案例1：查询有奖金的员工名和部门名

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;

案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city

FROM departments d,locations l

WHERE d.location_id=l.location_id

AND city LIKE ‘_o%’;

(4)、加分组

案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数,city

FROM departments d,locations l

WHERE d.location_id=l.location_id

GROUP BY city;

案例2：查询有奖金的每个部门的部门名和领导编号和该部门的最低工资

SELECT department_name,e.manager_id,MIN(salary)

FROM departments d,employees e

WHERE d.department_id=e.department_id

AND commission_pct IS NOT NULL

GROUP BY department_name,d.manager_id;

(5)、加排序

案例：查询每个工种的工种名和员工的个数，并且按照员工个数降序

SELECT job_title,COUNT(*)

FROM employees e,jobs j

WHERE e.job_id=j.job_id

GROUP BY job_title

ORDER BY COUNT(*) DESC;

(6)、实现三表查询

案例：查询员工名、部门名和所在的城市

SELECT last_name department_id,city

FROM employees e,departments d,location l

WHERE e.department_id=d.department_id

AND d.location_id=l.location_id

2、非等值连接

案例1：查询员工的工资和工资级别

SELECT * FROM job_grades;

SELECT salary,grade_level

FROM employees e,job_grades g

WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

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;

(二)、sql99语法

**语法：

select 查询列表

from 表1 别名 [连接类型]

join 表2 别名 on 连接条件

[where 筛选条件]

[group by 分组]

[order by 排序列表]**

_**特点：连接条件(on)和筛选条件(where)相分离

<92语法放在where后>**_

**内连接(★):　inner

外连接：

左外(★):left [outer]

右外(★):right [outer]

全外:full [outer]

交叉连接:　crose**

1、内连接

**分类：

等值

非等值

自连接**

_**特点： ①添加排序、分组、筛选

②inner 可以省略

③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

④inner join连接和sql92的等值连接效果是一样的，都是查询多表的交集**_

(1)、等值连接

案例1：查询哪个部门个数>3的部门名和部门个数并按个数降序

SELECT department_name,COUNT(*)

FROM employees e

INNER JOIN departments d

ON e.department_id=d.department_id

GROUP BY department_name

HAVING COUNT(*)>3

ORDER BY COUNT(*) DESC;

案例2：查询员工名、部门名、工种名、并按部门名降序(添加三表连接)

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 department_name DESC;

(2)、非等值连接

案例1：查询员工的工资级别

SELECT salary,grade_level

FROM employees e

INNER JOIN job_grades g

ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

案例2：查询工资级别个数>20的个数, 并且按工资级别降序

SELECT COUNT(*),grade_level

FROM employees e

INNER JOIN job_grades g

ON e.salary BETWEEN g.lowest_sal AND g.highest_sal

GROUP BY grade_level

HAVING COUNT(*)>20

ORDER BY grade_level DESC;

(3)、自连接

案例：查询姓名中包含字符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、外连接

应用场景：用于查询一个表中有，另一表中没有的

_**特点：

1、　外连接的查询结果为主表中的所有记录

如果从表中有和他匹配的，则显示匹配值

如果从表中没有和他匹配的，则显示null值

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

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

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

3、左外和右外交换两个表的顺序可以实现相同的效果

4、全外连接=内连接的结果+表1中、有表2中没有+表1中没有、表2中有 <并集>**_

案例：查询没有男朋友的女神名

#查询的是主要是女神所以beauty表为主表

USE girls;

#左外连接

SELECT b.name

FROM beauty b

LEFT OUTER JOIN boys bo

ON b.boyfriend_id=bo.id

WHERE bo.id IS NULL;

案例1：查询那个部门没有员工

SELECT d.*,employee_id

FROM departments d

LEFT OUTER JOIN employees e

ON d.department_id=e.department_id

WHERE e.employee_id IS NULL;

3、交叉连接

即笛卡尔乘积

SELECT b.,bo.

FROM beauty b

CROSS JOIN boys bo;

(三)、总结：连接查询

1、sql92 与 sql99 比较

_功能：sql99支持的功能较多

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

推荐使用sql99标准

2、图形解决内连接、外连接以及交叉连接

(1)、左外连接

在这里插入图片描述

(2)、右外连接

在这里插入图片描述

(3)、内连接

即 取交集

最后

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Java开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
在这里插入图片描述

(2)、右外连接

在这里插入图片描述

(3)、内连接

即 取交集

最后

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

[外链图片转存中…(img-pF73NQsI-1715843607506)]

[外链图片转存中…(img-zyuwLvfz-1715843607507)]

[外链图片转存中…(img-XK6tzUT2-1715843607507)]

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

2401_84814920

关注

4
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL 数据库 day-03

功能：sql99支持的功能较多可能性：sql99实现连接条件和筛选条件的分离,可读性较高_推荐使用sql99标准自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
复制链接

扫一扫