MySQL基础（三）

三分稚气 2021-04-14

457

本系列是基于B站尚硅谷MySQL李玉婷教程的学习笔记，形成学习内容的文字版本

视频链接：https://www.bilibili.com/video/BV12b411K7Zu?t=7&p=304

主要内容：

一、连接查询

二、子查询

三、分页查询

四、联合查询

一、连接查询

/*
多表查询，多表连接


**内连接：**
  等值连接
  非等值连接
  自连接
**外连接**：
  左外连接
  右外连接
  全外连接


**交叉连接**
*/


一、内连接  P281


/*
语法：
  select 查询列表
  from 表名1 别名1，表名2 别名2...
  where 等值连接条件
特点：
  1.为解决重名问题，往往会起别名
  2.表的顺序无要求
*/

1.等值连接

/*
语法：
  select 查询列表
  from 表名1 别名1，表名2 别名2...
  where 等值连接条件
特点：
  1.为解决重名问题，往往会起别名
  2.表的顺序无要求
*/


1.简单的两表连接
USE myemployees ;


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


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


2.添加筛选条件
案例1：查询部门编号>100的部门和所在的城市名


SELECT `department_name`,`city`
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND d.`department_id`>100;


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


SELECT `first_name`,`department_name`
FROM `employees` e , `departments` d
WHERE e.`department_id`=d.`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 city LIKE '_o%';


3.添加分组+筛选
案例1：查询每个城市的部门个数


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


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


SELECT d.`department_name`,e.`manager_id`,MIN(salary)
FROM `departments` d,`employees` e
WHERE d.`department_id`=e.`department_id`
GROUP BY e.`department_id`;


4.添加分组、筛选、排序
案例：查询那个部门的员工数>5，并按员工数降序


SELECT `department_name`,COUNT(*) 个数
FROM `employees` e,`departments` d
WHERE e.`department_id`=d.`department_id`
GROUP BY e.`department_id`
HAVING COUNT(*)>5
ORDER BY COUNT(*) DESC;

-------------------SQL99语法---------------------

1.内连接

/*
语法：
select 查询表名
from 表名1 别名


 **inner join**  表名2. 别名。。。
on 连接条件
where 筛选
group by 分组
having 分组后筛选
order by 排序
*/


1.简单的两表连接
USE myemployees ;


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


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


2.添加筛选条件
案例1：查询部门编号>100的部门和所在的城市名


SELECT `department_name`,`city`
FROM departments d INNER JOIN locations l
WHERE d.`location_id`=l.`location_id`
AND d.`department_id`>100;


3.添加分组+筛选
案例1：查询每个城市的部门个数


SELECT COUNT(*) 部门个数,lo.`city`
FROM `departments` d  JOIN `locations` lo
ON d.`location_id`=lo.`location_id`
GROUP BY lo.`city`;


4.添加分组、筛选、排序
案例：查询那个部门的员工数>5，并按员工数降序


SELECT `department_name`,COUNT(*) 个数
FROM `employees` e JOIN `departments` d
ON e.`department_id`=d.`department_id`
GROUP BY e.`department_id`
HAVING COUNT(*)>5
ORDER BY COUNT(*) DESC;

2.非等值连接

案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组


SELECT * FROM sal_grade;
FROM employees e
JOIN sal_grade g
ON e.salary BETWEEN g.`min_salary` AND g.`max_salary`
WHERE e.department_id BETWEEN 10 AND 90
GROUP BY g.grade;

3.自连接

案例：查询员工名和对应的领导名


SELECT e.`last_name`,m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

4.外连接

/*


说明：查询结果为主表中所有记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null
应用场景：一般用于查询主表中有但从表没有的记录
特点：
1.外连接分主从表，两表顺序不能调换
2.左连接的话，左为主表，右链接右为主表


语法：
SELECT 查询表名
FROM 表一 别名
LEFT  RIGHT OUTER JOIN 表2 别名
ON 连接条件
WHERE 筛选条件；


特点：
1.外连接的查询结果 = 内连接的查询结果 + 主表有但从表没有的记录
2.区分主从表  left join,左边为主表；right join，右边为主表
3.外连接用于查询主表有但从表没有的记录




*/


USE girls;
#案例1：查询所有女神记录，以及对应的男神名称，如果没有对应，显示为null
#左连接
SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`;
#右连接
SELECT b.*,bo.*
FROM boy bo
LEFT JOIN beauty b ON b.`boyfriend_id` = bo.`id`;




#案例2：哪个女神没有男朋友
#左连接
SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;


#案例3：查询那个部门没有员工，并显示其部门编号和部门名,并计数 
SELECT d.`department_id`, d.`department_name`,COUNT(*)
FROM departments d
LEFT JOIN employees e
ON D.`department_id`=E.`department_id`
WHERE e.`employee_id` IS NULL;

外连接的逻辑，如图：

二、子查询

出现在其他语句的内部的select语句，称为子查询或内查询；里面嵌套其他select语句的查询语句，称为主查询或外查询。

子查询不一定必须出现在select语句内部，只是出现在select语句内部的时候较多！

*分类：*

*单行子查询*

特点：子查询的结果集只有一行一列

*多行子查询*

特点：子查询的结果集有多行一列

按位置分类：

select 后面
要求子查询的结果为单行单列（标量子查询）
FROM后面
要求自持挨训的结果可以为多行多列
WHERE / HAVING 后面
要求：自查询的结果必须为单列
单行子查询
多行子查询
EXISTS后面
要求：子查询结果必须为单列（相关子查询）

*说明：*

1、子查询语句需要放在小括号内，提高代码的阅读性

2、子查询先于主查询执行，一般来讲，主查询会用到子查询的结果

3、如果子查询放在条件中，一般来讲，子查询需要放在条件的右侧

示例：where job_id>(子查询)

不能写成：where (子查询)<job_id

4、单行子查询对应的使用单行操作符：> < >= <= = <>

多行子查询对应的使用多行操作符:in 、any 、all 、not in

LIMIT用来限定查询结果的起始行，以及总行数。

1.放在WHERE后面

a.单行子查询

#案例1：查询Zlotkey的部门编号
#步骤1.查询部门编号
SELECT department_id
FROM employees
WHERE last_name='Zlotkey';


#步骤2.查询department_id=1中的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id =(


  SELECT department_id
  FROM employees
  WHERE last_name='Zlotkey'


)


#案例2：查询工资比公司平均工资高的员工的员工号，姓名和工资
#步骤1：先算出公司平均工资，做步骤2的筛选项
SELECT AVG(salary)
FROM employees;


#步骤2：选出工号姓名工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary >(


  SELECT AVG(salary)
  FROM employees


);


#练习题：
#案例1：谁的工资比 Abel 高?


SELECT 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和其最低工资
SELECT department_id,MIN(salary)
FROM employees
WHERE salary > (
  SELECT MIN(salary)
  FROM employees
  WHERE department_id = 50
);

b.多行子查询

/*
in:判断某字段是否在指定列表内
x in(10,30,50)
any/some：判断某字段是否满足其中任意一个
x>any(10,20,60) 就等于 x >min(10,20,60)


x = any(10,20,60) 就等于 x in (10,20,60)


all : 是否满足所有




*/


#多行子查询
#题目：返回location_id是1400或1700的部门中的所有员工姓名
#步骤1
SELECT department_id
FROM departments
WHERE location_id IN (1400,1700)
#步骤2
SELECT last_name
FROM employees
WHERE department_id IN(
  SELECT department_id
  FROM departments
  WHERE location_id IN (1400,1700)
);


#题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
#步骤1:选出工资


#步骤2：比较


```mysql
SELECT salary
FROM employees
WHERE job_id = 'It_PROG';
```


#步骤2：比较


```mysql
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id !='IT_PROG'
AND salary < ANY(


  SELECT salary
  FROM employees
  WHERE job_id = 'It_PROG'


);
```


#题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
#步骤1:选出工资
SELECT salary
FROM employees
WHERE job_id = 'It_PROG';
#步骤2：比较
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id !='IT_PROG'
AND salary < ALL(


  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'


);

2.放在SELECT后面

#案例：查询部门编号是50的员工个数


SELECT (
  SELECT COUNT(*)
  FROM employees
  WHERE department_id = 50
  )
个数;

3.放在FROM后面

#案例：查询每个部门的平均工资的工资级别


#步骤1
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;


#步骤2:非等值连接，
SELECT dep_ag.department_id,dep_ag.ag,g.grade
FROM sal_grade g
JOIN (
  SELECT AVG(salary) ag,department_id
  FROM employees
  GROUP BY department_id
) dep_ag
ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary;

4.放在EXISTS后面

#案例1：查询有误张三丰的员工信息
SELECT EXISTS(
  SELECT *
  FROM employees
  WHERE last_name = '张三丰'
) 有无张三丰;


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


SELECT bo.*
FROM boys bo
WHERE bo.`id` IN(
  SELECT boyfriend_id
  FROM beauty b
  );

子查询经典案例（练习题）

#练习题：
#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 e
JOIN (
  SELECT department_id,AVG(salary) ag
  FROM employees
  GROUP BY department_id
)dep_ag
ON e.department_id =dep_ag.department_id
WHERE e.`salary` > dep_ag.ag;






#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM employees
  WHERE last_name LIKE '%u%'
);




#5.查询在部门的 location id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (
  SELECT DISTINCT department_id
  FROM departments 
  WHERE location_id = 1700
);


#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
  SELECT employee_id
  FROM employees
  WHERE last_name = 'K_ing'
);






#7.查询工资最高的员工的姓名，要求 firstname和 last name显示为一列，列名为姓名


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

三、分页查询

/*
应用场景：当页面上的数据一页显示不全，则需要分页显示


语法：
select 
from 
join
on
where
group by
having
order by 
limit  起始条目索引，显示条目数


执行顺序：
1.from
2.join
3.on
4.where
5.group by
6.having
7.select
8.order by
9.limit


特点：
1.起始条目索引从0开始，不写默认从0
2.参数1：起始条目索引；参数2：显示条目数


公式：
加入要显示的页数的page,每页显示的条目数为size


select *
from 表名
limit (page-1)*size , size;
# -1主要就是因为从计数


*/


# 案例1 ：查询员工信息表的前5条
SELECT * FROM employees LIMIT 0,5;
等价于
SELECT * FROM employees LIMIT 5;


# 案例2：查询有奖金的且工资较高的第11名到第20名
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10,10;

四、union联合查询

/*
说明：当查询结果来自于多张表，但多张表之间没有关系,这个时候就用联合查询


语法：
select 查询 from 表1   where 筛选条件
  union
select 查询 from 表2   where 筛选条件


特点：
1.需要注意查询列表的数量一致
2.union可以自动去重，加all 可以取消自动去重
*/


# 案例1：查询所有国家的年龄》20岁的用户信息


SELECT * FROM chinese WHERE age>20
UNION
SELECT * FROM usa WHERE uage >20;


# 案例2：查询所有国家的用户姓名和年龄
SELECT uname,uage FROM usa
UNION
SELECT 'name',age FROM chinese;
# 需要注意查询列表的数量一致


# 案例3：union可以自动去重
SELECT 1,'haha'
UNION
SELECT 1,'haha'
UNION
SELECT 1,'haha'
UNION
SELECT 1,'haha';
# 加all 可以取消自动去重
SELECT 1,'haha'
UNION ALL
SELECT 1,'haha'
UNION ALL
SELECT 1,'haha'
UNION ALL
SELECT 1,'haha';

先写这么多，仅供参考，欢迎指点。

如果你有希望实现的内容，或者在过程中遇到了某些困难，欢迎交流探讨！

关注公众号，回复“尚硅谷MySQL”关键字，获取课程配套软件、课件、资料

/如有纰漏请联系我十分感谢/

长按图片，点击关注

mysql

文章转载自三分稚气，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL基础（三）

一、连接查询

1.等值连接

-------------------SQL99语法---------------------

1.内连接

2.非等值连接

3.自连接

4.外连接

二、子查询

1.放在WHERE后面

a.单行子查询

b.多行子查询

2.放在SELECT后面

3.放在FROM后面

4.放在EXISTS后面

三、分页查询

四、union联合查询

评论