MySQL基础知识总结1/3（查询语句DQL）

最新推荐文章于 2024-05-08 23:48:40 发布
最新推荐文章于 2024-05-08 23:48:40 发布
阅读量390 收藏 1
点赞数
分类专栏： MySQL数据库与JDBC 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gjs935219/article/details/105015649
版权

目录

一、基础查询介绍

1、起别名

2、去重

3、+号的作用

4、使用concat实现连接

5、IFNULL

二、条件查询

一、按条件表达式筛选

二、按逻辑表达式筛选

 三、模糊查询

1、like

2、between  and

3、in关键字

4、  is null

5、安全等于 <=>

测试题：

三、排序查询

四、常见函数

一、字符函数

二、数学函数

三、日期函数

四、流程控制函数

五、case结构

案例练习

六、分组函数    

1、  简单的使用：

2、分组函数的特点

3、和distinct搭配

4、count函数的详细介绍

5、分组函数的其他注意事项 

案例复习

五、分组查询的介绍 

分组查询的简单使用 

添加筛选条件 

分组前筛选

分组后筛选

按表达式或函数分组 

按多个字段分组

添加排序

小结

案例练习

六、连接查询

笛卡尔乘积

连接查询的分类

等值连接

等值连接的示例 

小结 

非等值连接 

自连接

测试题

SQL练习

sql99语法介绍

内连接

等值连接 

非等值连接

自连接 

左右连接

全外连接

交叉连接

查询总结

​案例讲解

子查询 

where和having后面的子查询

标量子查询

列子查询（多行子查询）

行子查询

exits后面的子查询（相关子查询）

案例

分页查询

测试题

子查询经典案例

七、联合查询 

 

一、基础查询介绍

1、起别名

优点：

①便于理解

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

#方式一

‘As‘形式

SELECT first_name As "名字", salary As "薪水" FROM `employees`;

#方式二 

’空格’形式

SELECT first_name "名字", salary "薪水" FROM employees; #MySQL建议别名加双引号

2、去重

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

SELECT department_id FROM `employees`

加入DISTINCT关键字

SELECT DISTINCT department_id FROM `employees`

3、+号的作用

Java中的+号

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

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

MySQL中的+号

仅有一个功能：运算符

~两个操作数为数值型，则做加法运算

select 100+20

~其中一方为字符型，试图将字符型数值转成数值型

如果转换成功，则继续做加法运算，如果转换失败，则将字符型数值转成0

SELECT '100'+20
SELECT 'jiusen'+20

 

 ~只要一方为null，则结果肯定为null

SELECT  NULL + 20

4、使用concat实现连接

SELECT CONCAT('a', 'b', 'c')

#案例：查询员工名和姓连接成一个字段，并显示为姓名

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

5、IFNULL

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

二、条件查询

语法：

SELECT 
  查询列表 
FROM
  表名 
WHERE 筛选条件 

分类：

一、按条件表达式筛选

简单条件运算符：  >   <   =   !=   <>   >=   <=

二、按逻辑表达式筛选

&&   ||    !    and  or   not 

三、模糊查询

like 

between  and 

in

is  null 

一、按条件表达式筛选

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

SELECT 
  * 
FROM
  employees 
WHERE salary > 12000

 #案例2： 查询部门编号不等于30号的员工名和部门编号

select 
  `employee_id`,
  `department_id` 
from
  `employees` 
where `department_id` <> 30

二、按逻辑表达式筛选

#案例1：查询工资在10000到20000之间的员工名、工资以及奖金

SELECT 
  `last_name`,
  `salary`,
  `commission_pct` 
FROM
  `employees` 
WHERE salary >= 10000 
  AND salary <= 20000

 

#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息

SELECT 
  * 
FROM
  `employees` 
WHERE AND(
    `department_id` >= 90 
    AND `department_id` <= 110
  ) 
  OR `salary` > 15000

 三、模糊查询

1、like

特点①一般和通配符搭配使用

通配符： %任意多个字符，包含0个字符

              _任意单个字符

#案例1:：查询员工名中包含字符a的员工信息

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

 #案例2：查询员工名中第三个字符为n,第五个字符为a的员工名

SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `last_name` LIKE '__n_r%'

 #案例3：查询员工名中第三个字符为_的员工名

-- 使用 '\'转义字符进行转义
SELECT 
  `last_name` 
FROM
  `employees` 
WHERE `last_name` LIKE '_\_%';

-- 使用ESCAPE关键字
SELECT 
  `last_name` 
FROM
  `employees` 
WHERE `last_name` LIKE '_$_%' ESCAPE '$';

2、between  and

①使用between and  可以提高语句的简介度

②包含临界值

③两个临界值不能更换位置

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

select 
  * 
from
  `employees` 
where `employee_id` between 100 and 120

3、in关键字

#案例：查询员工的工种编号是IT_PROG 、AD_VP、  AD_PRES中的一个员工名和工种编号

SELECT 
  `last_name`,
  `job_id` 
FROM
  `employees` 
WHERE `job_id` IN ('IT_PROG', 'AD_PRES', 'AD_VP') ;

4、  is null

=或者<>不能用于判断null值

is null  或者 is not null可以判断 null 值

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

SELECT 
  `last_name`,
  `commission_pct` 
FROM
  `employees` 
WHERE `commission_pct` IS NULL ;

5、安全等于 <=>

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

#案例：查询员工号为176的员工号、姓名、月薪、奖金率、年薪

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

测试题：

#测试1： 查询没有奖金，且工资小于18000的salary， last_name

SELECT 
  `salary`,
  `last_name` 
FROM
  `employees` 
WHERE `commission_pct` IS NULL 
  AND `salary` < 18000 ;

#测试2：查询employees表中，job_id不为'IT'或者工资为12000的员工信息

SELECT 
  * 
FROM
  `employees` 
WHERE `job_id` <> 'IT' 
  OR `salary` = 12000 ;

#测试3：查看部门departments表的结构

DESC `employees`;

 

#测试4：查询部门departments表中涉及到了哪些位置编号

SELECT DISTINCT 
  `location_id` 
FROM
  `departments`

## 经典面试题：

试问 SELECT * FROM `employees` ;  和 SELECT * FROM `employees` WHERE `commission_pct` LIKE '%%' AND `last_name` LIKE '%%';  结果是否一样？并说明原因

SELECT * FROM `employees`;

SELECT * FROM `employees` WHERE `commission_pct` LIKE '%%' AND `last_name` LIKE '%%';

 第二种不能够查询到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` DESC

# 案例2：查询部门编号 >= 90的员工信息，按入职时间的先后进行排序

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

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

select 
  *,
  salary * 12 * (1+ ifnull(commission_pct, 0)) as '年薪' 
from
  `employees` 
order by 年薪;

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

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

 

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

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

 ## 排序案例

#1、查询员工的姓名和部门号和年薪，按年薪降序  按姓名升序

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

#2、选择工资不在8000到17000的员工的姓名和工资，按工资降序

SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE salary NOT BETWEEN 8000 
  AND 17000 
ORDER BY salary DESC ;

#3、查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT 
  * 
FROM
  `employees` 
WHERE `email` LIKE '%e%' 
ORDER BY LENGTH(email) DESC,
  `department_id` ASC ;

四、常见函数

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

好处：1、隐藏了实现细节， 2、提高了代码的重用性

调用：select 函数名(实参列表) 【from表】;

特点：①叫什么（函数名） ②干什么（函数功能）

分类：

1、单行函数   如  concat 、length 、ifnull等

2、分组函数  功能：做统计使用，又称为统计函数 、 聚合函数、组函数

一、字符函数

#1、length 获取参数值的字节个数

SELECT LENGTH('john');
SELECT LENGTH('森哥哥521');

#2、concat拼接字符串

SELECT CONCAT('郭', 'love', '张');

#3、upper、lower

SELECT UPPER('love'), LOWER('AK');  -- 字符串记得加上''或者""

#4、substr、substring

MySQL中索引从1开始

#截取从指定索引后面的所有字符

select substr('郭久森爱上了张莹莹', 7) output;

#截取从指定索引处指定字符长度的字符

select substr('郭久森爱上了张莹莹',1,3) output;

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

SELECT 
  CONCAT(
    UPPER(SUBSTR(`last_name`, 1, 1)),
    '-',
    LOWER(SUBSTR(`last_name`, 2))
  ) 姓名 
FROM
  `employees`

 #5、instr

返回指定字符串第一次出现的索引，如果找不到就返回0

SELECT 
  INSTR(
    '郭久森爱上了张莹莹',
    '张莹莹'
  ) AS out_put ;

#6、trim

去除字符串中的空格

select 
  trim('aa' from 'aaaaaaaa森') as out_put ;

#7、lpad用指定的字符实现左填充指定长度

SELECT LPAD('森啊啊啊', 5, '*') output

#8、右填充与左填充类似

 

#9、replace替换指定字符串

SELECT 
  REPLACE(
    '郭久森爱上了计算机',
    '计算机',
    '张莹莹'
  ) AS output ;

二、数学函数

#round 四舍五入

SELECT ROUND(-1.56)
SELECT 
  ROUND(- 1.567, 2) 四舍五入 ;

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

SELECT 
  CEIL(- 1.567) 向上取整 ;

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

SELECT 
  FLOOR(- 1.567) 向下取整 ;

#截断 truncate

SELECT 
  TRUNCATE(1.6654, 2)

#mod取余 ： a*a/b*b

SELECT 
  MOD(10, 3)

三、日期函数

#now返回当前的系统日期 + 时间

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

#curtime返回当前时间，不包含日期

#年、月、日

SELECT 
  YEAR(NOW()); -- 年
SELECT 
  MONTH(NOW()); -- 月
SELECT 
  DAY(NOW());  -- 日

#str_to_date 将字符通过指定的格式转换成日期

SELECT 
  STR_TO_DATE('4-3 1992', '%m-%d %Y')

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

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

#date_format 将日期转换成字符

SELECT 
  DATE_FORMAT(NOW(), '%Y年%m月%d日')

 

#案例：查询有奖金的员工名和入职日期（xx月/xx日  xx年）

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

四、流程控制函数

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

SELECT IF(10>5,'大','小');

#查询有奖金的员工： 

SELECT 
  `last_name`,
  `commission_pct`,
  IF(
    `commission_pct` IS NULL,
    '没奖金,呵呵',
    '有奖金,嘻嘻'
  ) AS 备注 
FROM
  `employees` ;

五、case结构

MySQL中

1、类似于switch  ...   case...   default.....

case  要判断的字段或者表达式

when 常量1  then要显示的值1或语句1

when 常量2  then要显示的值1或语句2

when 常量3  then要显示的值1或语句3

......

else 要显示的值n或语句n

end

 

## 案例：查询员工的工资，要求：

部门号=30，显示的工资为1.1倍

部门号=40，显示的工资为1.2倍

部门号=50，显示的工资为1.3倍

其他部门，显示的工资为原工资

SELECT 
  `salary` 薪资,
  `department_id`,
  CASE
    `department_id` 
    WHEN 30 
    THEN 1.1 * `salary` 
    WHEN 40 
    THEN 1.2 * `salary` 
    WHEN 50 
    THEN 1.3 * `salary` 
    ELSE `salary` 
  END AS 新工资 
FROM
  `employees` ;

 

2、

case 

when  条件1  then  要显示的值1或语句1

when  条件2  then  要显示的值2或语句2

......

else   要显示的值n或语句n

end

区别：

1比较适合于处理等值判断。2、比较适合区间的判断

 

## 查询员工的工资的情况

如果工资>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` ;

案例练习

#1、显示系统时间（注：日期+时间）

SELECT 
  NOW() AS 系统时间 ;

#2、查询员工号、姓名、工资、以及工资提高百分之20后的结果（new  salary）

SELECT 
  `employee_id`,
  `last_name`,
  `salary`,
  `salary` * (1+0.02) AS 'new salary' 
FROM
  `employees` ;

#3、将员工的姓名按首字母排序，并写出姓名的长度（LENGTH）

SELECT 
  `last_name`,
  SUBSTR(`last_name`, 1, 1) AS 首字母,
  LENGTH(`last_name`) 
FROM
  `employees` 
ORDER BY `last_name`

#4、做一个查询，产生下面的结果

SELECT 
  CONCAT(
    `last_name`,
    ' earns ',
    salary,
    ' monthly but wants ',
    salary * 3
  ) AS 'Dream Salary' 
FROM
  `employees` 
WHERE salary = 24000

#5、使用case-when，按照下面的条件：

job_id        Grade
AD_PRES           A
ST_MAN              B
IT_PROG           C
SA_REP            D
ST_CLERK            E

产生下面结果：

SELECT 
  `job_id` AS job,
  CASE
    `job_id` 
    WHEN 'AD_PRES' 
    THEN 'A' 
    WHEN 'ST_MAN' 
    THEN 'B' 
    WHEN 'IT_PROG' 
    THEN 'C' 
    WHEN 'SA_REP' 
    THEN 'D' 
    WHEN 'ST_CLERK' 
    THEN 'E' 
  END AS 等级 
FROM
  `employees` 
WHERE job_id = 'AD_PRES'

六、分组函数    

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

   分类：

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

 

1、  简单的使用：

1、sum求和：

SELECT 
  SUM(`salary`) 总薪资
FROM
  `employees`

2、avg  平均值

SELECT 
  AVG(`salary`) 平均薪资
FROM
  `employees`

3、max最大值

select 
  max(`salary`) 最高薪资
from
  `employees`

4、min最小值

SELECT 
  MIN(`salary`) 最低薪资
FROM
  `employees`

5、count计算个数

SELECT 
  COUNT(`last_name`)  总人数
FROM
  `employees`

 ** 另外也可以这些分组函数一起使用

SELECT 
  COUNT(`last_name`) 总人数,
  SUM(`salary`) 总薪资,
  AVG(`salary`) 平均薪资,
  MAX(`salary`) 最高薪资,
  MIN(`salary`) 最低薪资 
FROM
  `employees`

 

2、分组函数的特点

1、sum、avg一般用于处理数值型

     max、min、count可以处理任何类型

2、是否忽略null值

sum、avg、max、min、count忽略了null值

 

3、和distinct搭配

 例：

select 
  sum(distinct `salary`) 去重求和,
  sum(`salary`) 求和
from
  `employees`

4、count函数的详细介绍

SELECT 
  COUNT(`commission_pct`) 非空的总行数 
FROM
  `employees` 
SELECT 
  COUNT(*) 表的总行数
FROM
  `employees` 
SELECT 
  COUNT(1) 总行数 -- 常量代表将表的列置为一个常量，然后统计一列中该常量的个数
FROM
  `employees`

效率：

NYISAM存储引擎下，COUNT(*)的效率最高

INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些 ，字段的话要进行一下判断是否为null

一般使用count(*)用作统计行数

5、分组函数的其他注意事项 

 和分组函数一同查询的字段要求是group by后的字段（group by知识点后面总结）

案例复习

#1、查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）

SELECT 
  DATEDIFF(MAX(`hiredate`), MIN(`hiredate`))  DIFFERENT
FROM
  `employees`

#2、查询部门编号为90的员工个数

SELECT 
  COUNT(*) 员工号为90的个数
FROM
  `employees` 
WHERE `department_id` = 90

五、分组查询的介绍 

 

语法：

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

from   表

【where   筛选条件】

group   by   子组的列表

【order   by   子句】

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

分组查询的简单使用 

#  案例：查询每个部门的平均薪资 

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`

添加筛选条件 

分组前筛选

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

SELECT 
  AVG(`salary`) 平均薪资,
  `department_id` 部门,
  `email` 邮箱 
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`

分组后筛选

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

SELECT 
  COUNT(*) 员工个数,
  `department_id` 部门 
FROM
  `employees` 
GROUP BY `department_id` 
HAVING COUNT(*) > 2

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

对于稍微复杂的查询，可以分部来解决

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

SELECT 
  MAX(salary) 最高工资,
  `job_id` 工种 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL 
GROUP BY job_id

②根据①结果继续筛选，最高工资>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的领导编号，以及其最低工资

思路：先分组，再加筛选条件

SELECT 
  MIN(salary) 最低工资,
  manager_id 领导编号 
FROM
  employees 
WHERE manager_id > 102 
GROUP BY manager_id 
HAVING MIN(salary) > 5000

 

按表达式或函数分组 

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

分步解决：

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

SELECT 
  COUNT(*),
  LENGTH(last_name) 
FROM
  employees 
GROUP BY LENGTH(last_name)

②添加筛选条件

select 
  count(*)员工个数,
  length(last_name)名字长度 
from
  employees 
group by length(last_name)
having count(*)>5

 

按多个字段分组

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

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

添加排序

#案例：查询每个部门每个工种的平均工资，并且按平均工资的高低显示

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

小结

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

 

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

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

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

 

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

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

案例练习

#1、查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序

SELECT 
  MAX(salary) 最大值,
  MIN(salary) 最小值,
  AVG(salary) 平均值,
  SUM(salary) 总和 
FROM
  `employees` 
GROUP BY job_id 
ORDER BY job_id

#2、查询员工最高工资和最低工资之间的差距 DIFFERENT

SELECT 
  MAX(salary) - MIN(salary) DIFFERENT 
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 
  COUNT(*) 员工数量,
  AVG(salary) 平均薪资,
  `department_id` 部门编号 
FROM
  `employees` 
GROUP BY `department_id`

5、选择具有各个job_id的员工数量

SELECT 
  COUNT(*)员工数量,
  job_id 
FROM
  `employees` 
GROUP BY `job_id`

六、连接查询

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

笛卡尔乘积

假设表1有m行数据，表2有n行数据，则下方SQL处理后，则产生m*n条数据 

SELECT 
  `name`,
  `boyName`
FROM
  `beauty`,
  `boys`

连接查询的分类

按照年代分类：

sql92标准：仅仅支持内连接

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

按功能分类：

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

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

            交叉连接

等值连接

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

先查询出所有的，然后再加上条件 

select 
  `name`,
  `boyName` 
from
  `beauty`,
  `boys` 
where beauty.id = boys.id

#2、查询员工名和对应的部门名

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

 

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

SELECT 
  `last_name`,
  a.`job_id`,
  `job_title` 
FROM
  `employees` AS a,  -- 由于表名字段太长，这里起别名
  `jobs` AS b 
WHERE a.`job_id` = b.`job_id`

 

为表起别名

①、提高语句的简介度

②、区分多个重名的子段

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

等值连接的示例 

#案例1、表名是否可以交换：

可以

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

SELECT 
  `last_name`,
  `department_name` ,
  `commission_pct`
FROM
  `departments` d,
  `employees` e 
WHERE d.`department_id` = e.`department_id` 
  AND e.`commission_pct` IS NOT NULL

#案例3：查询城市名中第二个字符为o的部门名和城市名（加筛选条件）

SELECT 
  `department_name`,
  `city` 
FROM
  `departments` d,
  `locations` l 
WHERE d.`location_id` = l.`location_id`   -- 两个筛选条件之间用  and进行连接
  AND l.`city` LIKE '_o%'

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

SELECT 
  COUNT(*) 部门个数,
  city 城市 
FROM
  `locations` l,
  `departments` d 
  WHERE l.`location_id`=d.`location_id`
  GROUP BY l.`city`

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

SELECT 
  d.`department_name`,
  d.`manager_id`,
  MIN(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`

#案例6：查询每个工种的工种号和员工的个数，并且按员工个数降序（排序）

SELECT 
  j.`job_title` 工种名,
  COUNT(*) 员工个数 
FROM
  `employees` e,
  `jobs` j 
WHERE e.`job_id` = j.`job_id` 
GROUP BY j.`job_title`
ORDER BY 员工个数 DESC

#案例7：查询员工名、部门名和所在的城市（多表连接查询）

SELECT 
  `last_name`,
  `department_name`,
  `city` 
FROM
  `departments` d,
  `employees` e,
  `locations` l 
WHERE d.`department_id` = e.`department_id` 
  AND d.`location_id` = l.`location_id`

小结 

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

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

③多表的顺序没有要求

④一般需要为表起别名

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

非等值连接 

 #案例：查询员工的工资和工资级别并按薪资升序

SELECT 
  `salary`,
  `grade_level` 
FROM
  `employees` e,
  `job_grades` j 
WHERE e.`salary` BETWEEN j.`lowest_sal` 
  AND j.`highest_sal` 
ORDER BY e.`salary`

自连接

意思也就是表自己连接自己

比如下表

#案例：查询员工名和上级的名称

思路：把这一个表看做一个员工表、一个领导表进行连接

select 
  e1.`last_name` 员工名,
  e2.`last_name` 领导名 
from
  `employees` e1,
  `employees` e2 
where e1.`manager_id` = e2.`employee_id`

测试题

一、显示员工表的最大工资，平均工资

SELECT 
  MAX(salary) 最大工资,
  AVG(salary) 平均工资 
FROM
  `employees`

二、查询员工表的employee_id，job_id，last_name,按department_id降序、salary升序

SELECT 
  `employee_id`,
  `job_id`,
  `last_name` 
FROM
  `employees` 
ORDER BY `department_id` DESC,
  `salary` ASC

三、查询员工表的job_id中包含'a和‘e’的，并且  a  在  e  的前面

SELECT 
  DISTINCT `job_id` 
FROM
  `employees` 
WHERE `job_id` LIKE '%a%e%'

四、

已知表student,里面有id（学号），name， gradeId（年级编号）

已知表grade，里面有id（年级编号），name（年级号）

已知表result，里面有id，score，studentNo（学号）

要求查询姓名、年级名、成绩

SELECT 
  s.`name`,
  g.`name`,
  r.`score` 
FROM
  student s,
  grade g,
  result r 
WHERE s.gradeId = g.id 
  AND s.id = r.studentId

五、显示当前日期、以及去前后空格、截取子字符串的函数

SELECT NOW();

SELECT TRIM(' ' FROM 'asfass  ');

SELECT SUBSTR(str,startIndex);

SELECT SUBSTR(str, startIndex, slength)

SQL练习

1、显示所有的员工的姓名，部门号和部门名称

SELECT 
  e.`last_name`,
  e.`department_id`,
  d.`department_name` 
FROM
  `employees` e,
  `departments` d 
WHERE e.`department_id` = d.`department_id`

2、查询90号部门员工的job_id和90号部门的location_id

SELECT 
  d.`department_id`,
  e.`job_id`,
  d.`location_id` 
FROM
  `employees` e,
  `departments` d 
WHERE e.`department_id` = d.`department_id` 
  AND e.`department_id` = 90

3、选择所有有奖金的员工的last_name，department_name，location_id，city

SELECT 
  e.`last_name`,
  d.`department_name`,
  l.`location_id`,
  e.`commission_pct`,
  l.`city` 
FROM
  `locations` l,
  `employees` e,
  `departments` d 
WHERE l.`location_id` = d.`location_id` 
  AND e.`department_id` = d.`department_id` 
  AND e.`commission_pct` IS NOT NULL

4、选择city在Toronto工作的员工的last_name，job_id, department_id， department_name,city

select 
  e.`last_name`,
  e.`job_id`,
  d.`department_id`,
  d.`department_name`,
  l.`city`
from
  `employees` e,
  `departments` d,
  `locations` l 
where e.`department_id` = d.`department_id` 
  and d.`location_id` = l.`location_id` 
  and l.`city` = 'Toronto'

5、查询每个工种、每个部门的部门名、工种名和最低工资

select 
  e.`job_id`,
  d.`department_name`,
  j.`job_title`, 
  min(salary) 最低工资 
from
  `employees` e,
  `departments` d,
  `jobs` j 
where e.`department_id` = d.`department_id` 
  and e.`job_id` = j.`job_id` 
group by e.`job_id`

6、查询每个国家下的部门个数大于2的国家的编号、部门号以及部门个数

SELECT 
  `country_id`,
  `department_id`,
  COUNT(*) 部门个数 
FROM
  `locations` l,
  `departments` d 
WHERE l.`location_id` = d.`location_id` 
GROUP BY l.`country_id` 
HAVING 部门个数 > 5

7、选择指定员工的姓名、员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees         Emp#        manager       Mgr#

kochhar              101              king           100

SELECT 
  e1.`last_name` employees,
  e1.`employee_id` "emp#",
  e2.`last_name` manager,
  e2.`employee_id` "Mgr#" 
FROM
  `employees` e1,
  `employees` e2 
WHERE e1.`manager_id` = e2.`employee_id`

sql99语法介绍

语法：

select  查询列表

from  表1  别名 【连接类型】

join  表2  别名

on  连接条件

【where  筛选条件】

【group by 分组】

【having 筛选条件】

【order by  排序条件】

内连接（*）：inner

外连接 ：

                 左外（*）：left 【outer】

                 右外（*）：right 【outer】

                 全外：full【outer】

交叉连接 ：cross

内连接

语法：

select  查询列表 

from 表1 别名

inner  join  表2  别名

on  连接条件 

分类：  等值、非等值、自连接

等值连接 

#案例一：查询员工名、部门名（调换表位置也可以）

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

案例二、查询名字中包含a的员工名和工种名（筛选）

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

 

案例三、查询部门个数>3的城市名和部门个数（添加分组+筛选）

思路：1、查询每个城市的部门个数，2、在1结果上筛选满足条件的

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 
  d.`department_name`,
  COUNT(*) 
FROM
  `employees` e 
  INNER JOIN `departments` d 
    ON e.`department_id` = d.`department_id` 
GROUP BY e.`department_id` 
HAVING COUNT(*) > 3 
ORDER BY COUNT(*) DESC

案例五、查询员工名、部门号、工种名，并按部门号降序（多变内联）

SELECT 
  `last_name`,
  d.`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 e.`department_id` DESC

非等值连接

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

SELECT 
  `salary`,
  `grade_level` 
FROM
  `employees` e 
  INNER JOIN `job_grades` j 
    ON salary BETWEEN `lowest_sal` 
    AND `highest_sal`

案例2：查询每个工资级别大于20的个数，并且按工资级别降序

SELECT 
  `grade_level`,
  COUNT(*) 级别个数 
FROM
  `employees` e 
  INNER JOIN `job_grades` j 
    ON salary BETWEEN `lowest_sal` 
    AND `highest_sal` 
    GROUP BY grade_level
    HAVING 级别个数 > 20
    ORDER BY COUNT(*) DESC

 

自连接 

案例：查询员工的姓名、上级的名字

SELECT 
  e1.`last_name`员工,
  e2.`last_name` 上级
FROM
  `employees` e1 
  INNER JOIN `employees` e2 
    ON e1.`manager_id` = e2.`employee_id`

案例2：查询姓名中包含字符k的员工的姓名、上级的名字

SELECT 
  e1.`last_name` 员工,
  e2.`last_name` 上级 
FROM
  `employees` e1 
  INNER JOIN `employees` e2 
    ON e1.`manager_id` = e2.`employee_id` 
WHERE e1.`last_name` LIKE '%k%'

左右连接

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

特点：

1、

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

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

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

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

2、左外连接：left join左边的是主表    右外连接：right join右边的是主表

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

4、全连接=内连接的结果+表1中有但表2没有的+表2有但是表1没有的

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

引入：查询男朋友不在男神表的女神名 

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

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

左连接之后，查询员工id为null的，另外

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

全外连接

MySQL不支持全外连接 

交叉连接

也就是表之间的笛卡尔积 

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

查询总结

案例讲解

 一、查询编号>3的女神编号、名字以及男朋友的信息，如果有则列出详细信息，如果没有，用null填充

SELECT 
  b.`id`,
  b.`name`,
  bo.* 
FROM
  `beauty` b 
  LEFT OUTER JOIN `boys` bo 
    ON b.`boyfriend_id` = bo.`id` 
    WHERE b.`id`>3

二、查询哪个城市没有部门

SELECT 
  l.*,d.* 
FROM
  `locations` l 
  LEFT OUTER JOIN `departments` d 
    ON l.`location_id` = d.`location_id` 
    WHERE d.`department_id` IS NULL

三、查询部门名为SAL或IT的员工信息

SELECT 
  e.*, d.`department_name`, d.`department_id`
FROM
  `employees` e 
  RIGHT OUTER JOIN `departments` d 
    ON e.`department_id` = d.`department_id` 
WHERE d.`department_name` IN('SAL', 'IT')

注：有重名部门的情况，内连接的话，少两条null的信息，就是这个部门没有员工

子查询 

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

外部的查询语句，称之为主查询或外查询

where和having后面的子查询

特点：

①子查询放在小括号内

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

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

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

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

标量子查询

案例1：谁的工资比Abel高？

①查询Abel的工资

②查询员工薪资>①的

select 
  `employee_id`,
  `last_name`,
  salary 薪资 
from
  `employees` 
where salary > 
  (select 
    salary 
  from
    `employees` 
  where `last_name` = 'Abel')

 

案例2：返回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)

案例3：返回公司工资最少的员工的last_name，job_id和salary

select 
  `last_name`,
  `job_id`,
  `salary` 
from
  `employees` 
where salary = 
  (select 
    min(salary) 
  from
    `employees`)

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

思路：

①查询50号部门最低工资

②查询各个部门的最低工资

③查询各个部门的最低工资 > ①的的部门id和其最低工资

SELECT 
  MIN(salary) 最低工资,
  department_id 
FROM
  `employees` 
GROUP BY `department_id` 
HAVING 最低工资 > 
  (SELECT 
    MIN(salary) 
  FROM
    `employees` 
  WHERE `department_id` = 50)

列子查询（多行子查询）

案例一、返回location_id是1400或1700的部门中的所有员工姓名 

SELECT 
  `last_name` 
FROM
  `employees` 
WHERE `department_id` IN 
  (SELECT 
    `department_id` 
  FROM
    `departments` 
  WHERE `location_id` IN (1700, 1400))

 

此题也可以使用内连接：

SELECT 
  `last_name` 
FROM
  `employees` e 
  INNER JOIN `departments` d 
WHERE e.`department_id` = d.`department_id` 
  AND d.`location_id` IN (1700, 1400)

案例2:返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工号、姓名、job_id以及salary 

注：比X部门任一工资低：小于X部们的任何一个就OK，所以用any关键字就OK

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：返回其他部门中比job_id为‘IT_PROG’部门所有工资都低的员工的  员工号、姓名、job_id  以及salary

注：任一，所以使用ALL关键字

SELECT 
  `employee_id` 员工号,
  `last_name` 姓名,
  `job_id`,
  `salary` 
FROM
  `employees` 
WHERE salary < ALL 
  (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 
    MIN(salary) 
  FROM
    `employees` 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG'

 

行子查询

...

exits后面的子查询（相关子查询）

语法：

exists(完整的查询语句)

结果：

1或0

 案例1：查询员工名和部门名

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 DISTINCT 
    `department_id` 
  FROM
    `employees`)

#案例：查询没有女朋友的男神信息

使用exists

SELECT 
  bo.* 
FROM
  `boys` bo 
WHERE NOT EXISTS 
  (SELECT 
    * 
  FROM
    `beauty` b 
  WHERE b.`boyfriend_id` = bo.`id`)

使用   in

SELECT 
  bo.* 
FROM
  `boys` bo
WHERE bo.`id` NOT IN
  (SELECT DISTINCT 
    boyfriend_id 
  FROM
    `beauty`)

案例

#1、查询和zlotkey相同部门的员工姓名和工资

SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `department_id` = 
  (SELECT 
    `department_id` 
  FROM
    `employees` 
  WHERE `last_name` = 'zlotkey')

#2、查询工资比公司平均工资高的员工的员工号、姓名和工资

SELECT 
  `employee_id`,
  `last_name`,
  `salary`
FROM
  `employees` 
WHERE salary > 
  (SELECT 
    AVG(salary) 
  FROM
    `employees`)

#3、查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资

-- 方法一：
SELECT 
  `employee_id`,
  `last_name`,
  `salary` 
FROM
  `employees` e1 
WHERE salary > 
  (SELECT 
    AVG(salary) AVG,
    department_id 
  FROM
    `employees` e2 
  WHERE e2.`department_id` = e1.`department_id`) 
  


-- 方法二：
SELECT 
  `employee_id`,
  `last_name`,
  `salary` 
FROM
  `employees` e 
  INNER JOIN 
    (SELECT 
      AVG(salary) ag,
      department_id 
    FROM
      `employees` 
    GROUP BY department_id) ag_dep 
    ON e.`department_id` = ag_dep.`department_id` 
WHERE e.`salary` > ag_dep.ag

#4、查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT 
  `employee_id`,
  `last_name` 
FROM
  `employees` 
WHERE `department_id` IN 
  (SELECT DISTINCT
    `department_id` 
  FROM
    `employees` 
  WHERE `last_name` LIKE '%u%')

#5、查询在部门的location_id为1700的部门工作的员工的员工号

SELECT 
  `employee_id` 
FROM
  `employees` 
WHERE `department_id` = ANY   -- 或者 in
  (SELECT 
    `department_id` 
  FROM
    `departments` 
  WHERE `location_id` = 1700)

#6、查询管理者是K_ing的员工姓名和工资

SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `manager_id` IN 
  (SELECT 
    `employee_id` 
  FROM
    `employees` 
  WHERE `last_name` LIKE 'k_ing')

#7、查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓.名

SELECT 
  CONCAT(`first_name`, '.', `last_name`) '姓.名' 
FROM
  `employees` 
WHERE salary = 
  (SELECT 
    MAX(salary) 
  FROM
    `employees`)

总结：对于子查询来说，，SQL书写步骤可以为：先写子SQL语句，再写主查询

分页查询

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

语法：

select   查询列表

from   表

【join  type   join  表2

on   连接条件

where   筛选条件

group  by 分组字段

having  分组后的筛选

order   by   排序的字段】 

limit  offset, size

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

案例一：查询前5条员工信息

SELECT 
  * 
FROM
  `employees` 
LIMIT 0, 5

案例二：查询第11条到第25条

SELECT 
  * 
FROM
  `employees` 
LIMIT 10, 15

 案例三：有奖金的员工的信息，并且工资较高的前10名显示出来

SELECT 
  * 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL 
ORDER BY salary DESC
LIMIT 0, 10

特点

①limit语句放在查询语句的最后

②公式

要显示的页数page，每页的条目数size

select *  from  表  limit  (page-1)*size, size

测试题

一、查询所有学员的邮箱的用户名（注：邮箱中@前面的字符）

select 
  substr(email, 1, instr(email, '@') - 1) 用户名 -- instr，获取字符的索引，substr，从指定索引开始截取指定长度的字符串
from
  stuinfo

二、查询男生和女生的个数

SELECT 
  COUNT(*) 个数, sex 
FROM
  stuinfo 
GROUP BY sex

三、查询年龄>18岁的所有学生的姓名和年级名称

SELECT 
  `name`,
  gradeName 
FROM
  stuinfo s
  INNER JOIN grade g
  ON s.gradeId = g.id
  WHERE s.age > 18

四、查询哪个年级的学生的最小年龄>20岁

SELECT 
      MIN(age),
      gradeId 
    FROM
      stuinfo 
    GROUP BY gradeId 
    HAVING MIN(age) > 20

五、试说出查询语句中涉及到的所有的关键字，以及执行先后顺序

select   查询列表                         ⑦

from  表                                       ①

连接类型  join  表2                      ②

on   连接条件                               ③

where   筛选条件                         ④

group  by  分组列表                    ⑤

having  分组后的筛选                  ⑥

order  by   排序列表                    ⑧

limit  起始索引， 条目数              ⑨

子查询经典案例

#1、查询工资最低的员工信息：last_name, salary 

select 
  `last_name`,
  `salary` 
from
  `employees` 
where salary = 
  (SELECT 
    MIN(salary) 
  FROM
    `employees`)

#2、查询平均工资最低的部门信息（@@）

SELECT 
  * 
FROM
  `departments` 
WHERE `department_id` = 
  (SELECT 
    department_id 
  FROM
    `employees` 
  GROUP BY `department_id` 
  ORDER BY AVG(salary) 
  LIMIT 1)

                                                                                        #3、查询平均工资最低的部门信息和该部门的平均工资

SELECT 
  d.*, af.ag 平均薪资 
FROM
  `departments` d 
  INNER JOIN 
    (SELECT 
      AVG(salary) ag,
      `department_id` 
    FROM
      `employees` 
    GROUP BY `department_id` 
    ORDER BY ag 
    LIMIT 1) AS af 
    ON d.`department_id` = af.department_id

#4、查询平均工资最高的job信息

SELECT 
  j.* 
FROM
  `jobs` j 
WHERE `job_id` = 
  (SELECT   -- 等于最高平均工资的job_id
    `job_id` 
  FROM
    `employees` 
  GROUP BY `job_id` 
  HAVING AVG(salary) = 
    (SELECT 
      AVG(salary) ag 
    FROM
      `employees` 
    GROUP BY `job_id` 
    ORDER BY ag DESC 
    LIMIT 1)) -- 最高平均工资

#5、查询平均工资高于公司平均工资的部门有哪些

		

SELECT 
  AVG(salary) 平均薪资,
  `department_id` 部门号 
FROM
  `employees` 
  GROUP BY `department_id`
HAVING AVG(salary) > 
  (SELECT 
    AVG(salary) 
  FROM
    `employees`) -- 公司的平均工资

 

#6、查询出公司中所有manager的详细信息

SELECT 
  e1.* 
FROM
  `employees` e1 
WHERE e1.`department_id` IN 
  (SELECT DISTINCT 
    `manager_id` 
  FROM
    `employees` e2)

#7、各个部门中最高工资中最低的那个部门的最低工资是多少

SELECT 
   * 
FROM
  `employees` 
WHERE `employee_id` IN
  (SELECT DISTINCT 
    `manager_id` 
  FROM
    `employees`)

 #8、查询平均工资最高的部门的manager的详细信息：last_name，department_id，email，salary

select 
  `last_name`,
  `department_id`,
  `email`,
  `salary` 
from
  `employees` 
where `employee_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)) -- 各部门平均薪资最高的部门id))

七、联合查询 

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

 引入的案例：查询部门编号>90或邮箱包含a的员工信息

select * from `employees` where email like '%a%'
union
SELECT * FROM `employees` WHERE `department_id`>90

 应用场景：要查询的结果来自多个表，且多个表没有直接的联系关系

特点：

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

②要求多条查询语句的查询的每一列的类型和顺序最好一致

③union关键字默认去重，可以使用union  all不去重

查询的为两个表的并集，重复项会被去重，如果不想去重，可以使用  union  all关键字

 

 

 

 

 

 

 

 

 

  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

           

 

 

森-Js
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL中的DQL查询语句
03-30
在MySQL中对表中的数据的简单查询语句
【MySQL】mysql范式一二三
qq_45461593的博客
08-02 131
在设计数据库的时候切记遵守范式规则，才能更好的解决数据冗余，数据有效性检查，提高存储效率。 第一范式 属性不可分 错误示例 正确示例 第二范式 每个非主属性完全函数依赖于键码。 什么是依赖 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。 如果 {A1，A2，… ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。 对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分
mysql如何进行算术运算_MySQL教程53-MySQL算术运算符
weixin_39966941的博客
01-26 3157
算术运算符是 SQL 中最基本的运算符，MySQL支持的运算符包括加、减、乘、除和取余运算，它们是最常用、最简单的一类运算符。下表列出了这些运算符的作用和使用方法。MySQL中的算术运算符运算符作用使用方法+加法运算用于获得一个或多个值的和-减法运算用于从一个值中减去另一个值*乘法运算使数字相乘，得到两个或多个值的乘积/除法运算，返回商用一个值除以另一个值得到商%，MOD求余运算，返回余数用一个...
Mysql中BETWEEN的用法
weixin_44901808的博客
09-08 9661
between
MySQL 常用语句
wgchen
02-25 430
MySQL 常用语句...
数据库MySQL基础知识点1
12-09
sql语句含义：结构化查询语言。客户端跟服务端通信的“特殊”语言 关系型数据库核心元素：数据库、数据表、记录、字段 客户端链接服务端 数据完整性 客户端Navicat使用 MySQL数据类型：数值型、decimal浮点...
mysql语句03(DQL多表+子查询)
03-17
mysql语句03(DQL多表+子查询)
mysql的DML和DQL和相关sql语句总结.emmx
07-18
使用mindmaster打开
mysql基础知识小结
06-01
# DQL数据查询语言 select .......[where|group by|order by|having].... # DML数据操作语言 处理表内的数据 insert update delete # TPL事务处理语言 运维常用 commit提交实务，mysql默认commit开启 ...
MySQL中BETWEEN子句的用法详解
09-10
主要介绍了MySQL中BETWEEN子句的用法详解,是MySQL入门学习中的基础知识,需要的朋友可以参考下
WHERE语句中BETWEEN与IN的使用教程-MySQL
php菜鸟技术天地
09-19 1万+
MySQL BETWEEN 用法 MySQL BETWEEN 语法 BETWEEN 运算符用于 WHERE 表达式中，选取介于两个值之间的数据范围。BETWEEN 同 AND 一起搭配使用，语法如下： WHERE column BETWEEN value1 AND value2 WHERE column NOT BETWEEN value1 AND value2 通常 val
outer和left outer join有什么区别
greytree的专栏
07-19 4129
outer和left outer join有什么区别??使用关系代数合并数据1 关系代数合并数据集合的理论基础是关系代数，它是由E.F.Codd于1970年提出的。在关系代数的形式化语言中：        用表、或者数据集合表示关系或者实体。        用行表示元组。        用列表示属性。关系代数包含以下8个关系运算符        选取――返回满足指定条件的行。     
mysql between详解_MySQL中BETWEEN子句的用法实例详解
weixin_35690449的博客
02-07 2390
这篇文章主要介绍了MySQL中BETWEEN子句的用法详解,是MySQL入门学习中的基础知识,需要的朋友可以参考下可以使用IN子句来代替相结合的“大于等于和小于等于”的条件。要了解BETWEEN 子句考虑的EMPLOYEE_TBL表有以下记录：mysql> SELECT * FROM employee_tbl;+------+------+------------+-------------...
mysql使用between and处理时间区间不包括右边界问题（请先看最下面的更正）
热门推荐
我怀念的
04-05 10万+
mysql使用between and处理时间区间不包括右边界问题最近自己写项目发现mysql使用between and处理时间区间不包括右边界。 SELECT * FROM timeline WHERE username = ‘Allen’ AND logTime BETWEEN ‘2017-04-01’ AND DATE_ADD(‘2017-04-04’,INTERVAL 1 DAY);
mysql语句中的between and_SQL语句中between and 范围
weixin_42502537的博客
02-05 859
经过验证 SQL语句中的between 范围1 and 范伟2语句是包括的范围1和范围2的Oracle代码 SQL>createtabletest2(3test1number(2)4);表已创建。SQL>insertintotestvalues(1);已创建1行。SQL>insertintotestvalues(2);已创建1行。SQL>in...
Mysql常用sql语句（11）- between and 范围查询
qq_33801641的博客
05-11 1881
测试必备的Mysql常用sql语句系列 https://www.cnblogs.com/poloyy/category/1683347.html 前言 between and可以判断值是否在指定范围内 实际场景：可以查询年龄段、工资水平等 between and的语法格式 BETWEEN 取值1 AND 取值2 NOT BETWEEN 取值1 AND 取值2 取值1：...
Mysql sql语句回顾1
u011374590的专栏
10-28 878
回顾一下mysql sql语句的基本用法，主要是查询语句，其中包括一些操作符的使用以及注意事项
MySQL基础--DQL-1
qq_35437032的博客
04-12 116
1.查询表中数据基本语句 首先了解一下SQL查询语句的执行顺序 ***FROM ---> WHERE-->GROUP BY-->HAVING-->SELECT-->ORDER BY*** select 字段名 from 数据表名; ps: select 后可以接想要查询数据表中的某一字段， 或者接 * 查询数据表中全部字段信息 实例： 准备数据，依旧拿数据表tb1举例 create table tb1 ( cid int, cname varchar(10)
java项目之智慧图书管理系统设计与实现(springboot+vue+mysql)
最新发布
sinat_26552841的博客
05-08 354
智慧图书管理系统设计与实现的主要使用者分为： 管理员权限操作的功能包括对注册读者信息的管理，对图书，对图书留言，对图书借阅记录，对论坛帖子等信息的管理。 读者权限操作的功能包括借阅图书，对图书进行留言或收藏图书，查看论坛帖子信息，查看借阅的图书等。
简述 ：MySQL日常使用的DML/DDL/DQL，窗口函数等常用SQL命令，MySQL的触发器，事务，锁机制，Innodb存储引擎及简单的MySQL优化
05-25
MySQL是一种关系型数据库管理系统，支持多种操作语言，包括DML（数据操作语言）、DDL（数据定义语言）和DQL（数据查询语言）。以下是MySQL日常使用的DML/DDL/DQL和窗口函数等常用SQL命令： 1. DML（数据操作语言）：包括INSERT、UPDATE和DELETE。这些命令用于向数据库中添加、更新和删除数据。 2. DDL（数据定义语言）：包括CREATE、ALTER和DROP。这些命令用于创建、修改和删除数据库的结构，如表、视图、索引等。 3. DQL（数据查询语言）：包括SELECT。这个命令用于从数据库中查询数据。 4. 窗口函数：窗口函数是一种用于在查询结果中对特定窗口的数据进行聚合操作的函数。MySQL 8.0之后支持窗口函数。 5. 触发器：触发器是一种在数据库中自动执行的程序。当指定的事件发生时，触发器会被触发执行。 6. 事务：事务是一组SQL语句，它们被视为单个操作。在MySQL中，可以使用START TRANSACTION、COMMIT和ROLLBACK命令来控制事务。 7. 锁机制：MySQL支持多种锁机制，包括读锁和写锁。这些锁用于保证数据的一致性和完整性。 8. Innodb存储引擎：Innodb是MySQL的一种存储引擎，它支持事务和行级锁等高级特性。 9. MySQL优化：MySQL优化包括索引优化、查询优化、表结构优化等。索引优化是提高查询效率的关键，查询优化包括使用合适的查询语句和避免全表扫描，表结构优化包括合理设计表结构和分区表等。 以上就是MySQL日常使用的DML/DDL/DQL和窗口函数等常用SQL命令，MySQL的触发器，事务，锁机制，Innodb存储引擎及简单的MySQL优化的简介。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值