MySQL 基础day2——多表查询、单行、多行函数、分组、过滤、子查询

最新推荐文章于 2024-07-25 17:05:51 发布

liang79096

最新推荐文章于 2024-07-25 17:05:51 发布

阅读量736

点赞数 23

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/liang79096/article/details/138258306

版权

1.多表查询

多表查询：当查询的多个字段不在同一张表时就需要用到多表查询。

连接条件分类：
   自连接 vs 非自连接
   内连接 vs 外连接
   等值连接 vs 非等值连接

连接语句分类：sql92语法和sql99语法

sql92语法
   select 表名.字段名1，表名.字段名2,...
   from 表1 表别名1，表2 表别名2,......
   where 连接条件

/*
多表查询：当查询的多个字段不在同一张表时就需要用到多表查询。

连接条件分类：
	自连接 vs 非自连接
	内连接 vs 外连接
	等值连接 vs 非等值连接
	
连接语句分类 ：sql92语法和sql99语法


sql92语法
	select 表名.字段名1，表名.字段名2,...
	from 表1 表别名1，表2 表别名2,......
	where 连接条件

*/
#sql92语法
#需求：查询每个员工的姓名和所在的部门名称
SELECT first_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;#连接条件

#sql92语法
#需求：查询每个员工的姓名,部门号和所在的部门名称
/*
  在多表查询中如果某一个字段只出现在某一张表中那么该字段前的表名可加可不加。
	如果某一个字段出现在多张表中那么该字段前面的表名必须加
  结论：在多表查询中字段名前最好加上表名因为效率高
*/
SELECT employees.first_name,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#给表起别名
SELECT e.first_name,d.department_name,e.department_id
FROM employees e,departments d
WHERE e.department_id=d.department_id;

#缺少连接条件 发生了笛卡尔集错误
SELECT e.first_name,d.department_name
FROM employees e,departments d


#需求：查询每个员工的姓名，部门名称和部门所在城市的名称
SELECT e.first_name,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;

多表查询：当查询的多个字段不在同一张表时就需要用到多表查询。

连接条件分类：
   自连接 vs 非自连接
   内连接 vs 外连接
   等值连接 vs 非等值连接

连接语句分类：sql92语法和sql99语法

sql92语法
   select 表名.字段名1，表名.字段名2,...
   from 表1 表别名1，表2 表别名2,......
   where 连接条件

sql99语法
   select 字段名1,字段名2,.....
   from 表名1 join/left join/right join/full join 表名2
   on 连接条件
   join/left join/right join/full join 表名3
   on 连接条件
   ......
   where 过滤条件
   order by 字段名1 asc/desc,字段名2 asc/desc,......

/*
多表查询：当查询的多个字段不在同一张表时就需要用到多表查询。

连接条件分类：
	自连接 vs 非自连接
	内连接 vs 外连接
	等值连接 vs 非等值连接
	
连接语句分类 ：sql92语法和sql99语法


sql92语法
	select 表名.字段名1，表名.字段名2,...
	from 表1 表别名1，表2 表别名2,......
	where 连接条件
	
sql99语法
	select 字段名1,字段名2,.....
	from 表名1 join/left join/right join/full join 表名2
	on 连接条件
	join/left join/right join/full join 表名3
	on 连接条件
	......
	where 过滤条件
	order by 字段名1 asc/desc,字段名2 asc/desc,......

*/
/*
用来说明select是字段名而非关键字
select `select` ,`from`
*/

#内连接：获取两张表中匹配的内容
#等值连接：连接的条件用的是等号
#非自连接：连接的表不是同一张表
#sql99语法
#需求：查询每个员工的姓名和所在的部门名称
SELECT e.`first_name`,d.`department_name`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#sql99语法
#需求：查询每个员工的姓名和所在的部门名称及部门所在的城市名称
SELECT e.`first_name`,d.`department_name`,l.`city`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;



#自连接：连接的表是同一张表
#需求：查询员工的名字及领导的名字
SELECT e1.`first_name` 员工名字,e2.`first_name` 领导的名字
FROM employees e1 JOIN employees e2   #e1当成员工表 e2当成领导表
ON e1.`manager_id`= e2.`employee_id`;

SELECT e2.`first_name` 员工的名字,e1.`first_name` 领导的姓名
FROM employees e1 JOIN employees e2 #e1当成领导表 e2当成员工表
ON e1.`employee_id` =  e2.`manager_id`;


#非等值连接：连接条件用的不是等号
#需求：查询员工的姓名，薪水和薪水等级
SELECT e.`first_name`,e.`salary`,j.`GRADE`
FROM employees e JOIN job_grades j
#on e.`salary` >= j.`LOWEST_SAL` and e.`salary` <= j.`HIGHEST_SAL`;
ON e.`salary` BETWEEN j.`LOWEST_SAL` AND j.`HIGHEST_SAL`;


#左外连接：除了两张表中匹配的内容外还包括左表中不匹配的内容
#需求：查询所有员工的姓名和他们的部门名称
SELECT e.`first_name`,e.`department_id`,d.`department_id`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;


#右外连接：除了两张表中匹配的内容外还包括右表中不匹配的内容
#需求：查询所有的部门名称及部门中的员工姓名
#注意：右外连接完全可以通过左外连接实现（只需要交换两张表的位置即可）
SELECT e.`first_name`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#满外连接：除了两张表中匹配的内容还包括左表和右表中不匹配的内容
#mysql不支持（oracle支持）
/*
实现full join:

左外连接
union
右外连接;

union（去重）:将两张表的结果合成一张表。
union all(不去重):将两张表的结果合成一张表。
*/
#需求：查询所有的员工姓名及所有的部门名称（除了匹配的 还包括 没有部门的员工  及 没有员工的部门）
/*
使用union时要注意的点：
	1.union去重
	2.两张表合成一张表那么这两张表的字段的个数和类型要保持一致。
*/
SELECT e.`employee_id`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION
SELECT e.`employee_id`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#union all:不去重
SELECT e.`employee_id`,d.`department_name/*
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION all
SELECT e.`employee_id`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;


#注意：如果select后面是一个常量（字面量）那么每条数据后面都会加一个该值
SELECT employee_id,first_name,1
FROM employees;

2.单行函数

/*
LOWER('SQL Course') ：将内容全部转成小写
UPPER('SQL Course') ：将内容全部转成大写
*/

SELECT LOWER('aAbBcC'),UPPER('aAbBcC');

SELECT LOWER(first_name),UPPER(last_name)
FROM employees;

/*
CONCAT('Hello', 'World') : 字符串拼接
SUBSTR('HelloWorld',1,5) : 截取子串 1是索引位置  5是内容的长度
	注意：索引是从1开始
LENGTH('HelloWorld') : 字符串的长度
INSTR('HelloWorld', 'W') : w在字符串中首次出现的位置-从左向右查找
LPAD(salary,10,'*') ：
	当字段的内容的长度不够10的时候用*补在左边补
RPAD(salary, 10, '*')：
	当字段的内容的长度不够10的时候用*补在右边补
TRIM('H' FROM 'HelloWorld') ：去除字符串两端指定的字符
REPLACE('abcd','b','m') : 将当前字符串中b替换成m
*/
SELECT CONCAT(first_name,last_name)
FROM employees;

SELECT SUBSTR('abcdef',2,2);

SELECT first_name,LENGTH(first_name)
FROM employees;

SELECT INSTR('abwccwc','w');

SELECT LPAD(first_name,10,"*"),RPAD(last_name,10,"*")
FROM employees;


SELECT TRIM('#' FROM '####a####c####');


SELECT REPLACE('abcdaaa','a','A');#mysql不区分大小写-关键字（sql语句） 数据（内容）是区分大小写的



/*
ROUND: 四舍五入
ROUND(45.926, 2)			45.93

TRUNCATE: 截断
TRUNCATE(45.926,0)      		45

MOD: 求余
MOD(1600, 300)		          100

*/
SELECT ROUND(45.926, 2),ROUND(45.926, 1),ROUND(45.926, 0),ROUND(45.926, -1);

SELECT TRUNCATE(45.926,2), TRUNCATE(45.926,1), TRUNCATE(45.926,0), TRUNCATE(45.926,-1);

#结果的正负和第一个参数的正负有关
SELECT MOD(3,2),MOD(-3,2),MOD(3,-2),MOD(-3,-2);


SELECT NOW();#当前日期和时间

SELECT VERSION(); #查看mysql版本

/*
ifnull(字段名,默认值)：如果字段的内容为null就用默认值替换
*/
#需求：查询所有员工的薪水（薪水+奖金）
SELECT first_name,salary,commission_pct,salary+salary*commission_pct#null参与运算
FROM employees;

SELECT first_name,salary,commission_pct,salary+salary*IFNULL(commission_pct,0)
FROM employees;


/*
case表达式：

格式1：
  case 字段名
	  when 值1 then 返回值1
	  when 值2 then 返回值2
	  when 值3 then 返回值3
	  else 返回值n
  end

格式2：
  case 
	  when 表达式1 then 返回值1
	  when 表达式2 then 返回值1
	  when 表达式3 then 返回值1
	  else 返回值n
  end	  
*/

#需求：练习：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 
#	20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数

SELECT first_name,department_id,salary,CASE department_id
					WHEN 10 THEN salary*1.1
					WHEN 20 THEN salary*1.2
					WHEN 30 THEN salary*1.3
					ELSE salary
					END "别名1"
FROM employees
WHERE department_id IN(10,20,30);



#需求：查询所有员工的姓名，薪水及描述信息（如果薪水大于10000输出会所嫩模，
#	小于10000输出下海干活 等于10000输出再接再厉）

SELECT first_name,salary,CASE 
			 WHEN salary>10000 THEN "会所嫩模"
			 WHEN salary<10000 THEN "下海干活"
			 ELSE "再接再厉"
			 END "别名"
FROM employees;

3.多行函数

/*
AVG()：求平均数 
SUM()：求和
注意：只能对数值类型（整型，浮点型）的数据操作

MAX()：求最大值 
MIN() ：求最小值

COUNT() ：求数据的条数
*/
#需求：求员工薪水的总和，平均值，最高薪水，最低薪水
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary)
FROM employees;

#注意：select后面一旦出现组函数（聚合函数，分组函数）就不能再出现其它字段。
#	除非该字段在group by的后面
/*
下面的写法都是错的
select first_name,avg(salary)
from employees;

select first_name,min(salary)
from  employees;

SELECT first_name,max(salary)
FROM  employees;
*/


/*
count(*) : 用来统计查询结果的数据的总条数。
count(字段名) ：用来统计查询结果中该字段不为null的有多少条数据
count(数值) : 理解成count(*)
	count(*):如果一条数据中所有字段全为null 不统计该条数据
	count(1):如果一条数据中所有字段全为null 统计该条数据
*/
#查询employees表中有多少条数据
SELECT COUNT(*)
FROM employees;

#查询50号部门有多少人
SELECT COUNT(*)
FROM employees
WHERE department_id=50;


SELECT COUNT(employee_id),COUNT(commission_pct)
FROM employees;

#奖金率不为null的有多少人
SELECT COUNT(*)
FROM employees
WHERE commission_pct IS NOT NULL;

SELECT COUNT(salary),COUNT(commission_pct)
FROM employees
WHERE department_id=60


SELECT first_name,salary,COUNT(2)
FROM employees;



#注意：avg在求平均值时有没有包括null? 不包括null
SELECT AVG(commission_pct),SUM(commission_pct)/107,SUM(commission_pct)/35
FROM employees;

4.分组和过滤

select 字段名1,字段名2,.......
from 表名
where 过滤条件
group by 字段名1,字段名2,.....
having 过滤条件
order by 字段名1 asc/desc,字段名2 asc/desc,......

where和having的区别？
1.where在分组前过滤。having是在分组后过滤。
2.where后面不可以用组函数。having后面可以用组函数。
3.如果没有分组(group by)不要使用having进行过滤用where
*/
/*
#注意：select后面一旦出现组函数（聚合函数，分组函数）就不能再出现其它字段。

/*
格式：
select 字段名1,字段名2,.......
from 表名
where 过滤条件
group by 字段名1,字段名2,.....
having 过滤条件
order by 字段名1 asc/desc,字段名2 asc/desc,......

where和having的区别？
1.where在分组前过滤。having是在分组后过滤。
2.where后面不可以用组函数。having后面可以用组函数。
3.如果没有分组(group by)不要使用having进行过滤用where
*/
/*
#注意：select后面一旦出现组函数（聚合函数，分组函数）就不能再出现其它字段。
#	除非该字段在group by的后面
*/
#需求：查询各部门平均薪水-没有部门的不要。
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id; #按照部门分组

#需求：查询50号部门不同工种的最高薪水
SELECT job_id,MAX(salary)
FROM employees
WHERE department_id = 50
GROUP BY job_id;

#需求：查询不同部门不同工种的最低薪水是多少
SELECT department_id,job_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id;


#需求：查询不同部门不同工种的最低薪水是多少并按照最低薪水排序-升序
SELECT department_id,job_id,MIN(salary) min_salary
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
ORDER BY min_salary ASC;


#需求：查询各部门的平均薪水 并且只要平均薪水大于5000的部门
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > 5000; #最好不要用别名-和hive有关系

SELECT department_id,AVG(salary) avg_sal
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING avg_sal > 5000;


#需求：查询各部门最高薪水 部门为null的不要
#方式一：效率高-能放在where后面过滤的尽量放在where后面过滤掉
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;

#方式二：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING department_id IS NOT NULL;



#需求：查询各部门的平均薪水 并且只要平均薪水大于5000的部门
#该方式效率高
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > 5000; #注意：AVG(salary)是在后组后得到的 所以要对此字段过滤只能在分组后过滤


SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING department_id IS NOT NULL AND AVG(salary) > 5000;

5.子查询

子查询：在一条查询语句a中再嵌套一条查询语句b。那么b语句叫作子查询（内查询）a语句叫作主查询（外查询）

子查询的分类：单行子查询 vs 多行子查询
   单行子查询：子查询返回的结果只有一条。
   多行子查询：子查询返回的结果有多条

单行子查询所使用运算符：> >= < <= = <>
多行子查询所使用运算符: in any all

子查询是由内向往去写


/*
子查询：在一条查询语句a中再嵌套一条查询语句b。那么b语句叫作子查询（内查询）a语句叫作主查询（外查询）

子查询的分类：单行子查询 vs 多行子查询
	单行子查询：子查询返回的结果只有一条。
	多行子查询：子查询返回的结果有多条
	
单行子查询所使用运算符 ：> >= < <= = <>
多行子查询所使用运算符: in any all	


子查询是由内向往去写。
*/


#需求:谁的工资比 Abel 高?
#方式一：
#1.先查询Abel工资
SELECT salary
FROM employees
WHERE last_name='Abel'; #11000
#2.查询比11000高的薪水的员工信息
SELECT last_name,salary
FROM employees
WHERE salary > 11000;

#方式二：多表查询
SELECT e1.`last_name`,e1.`salary`
FROM employees e1 JOIN employees e2
ON e1.`salary` > e2.`salary` AND e2.`last_name`='Abel';

#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
	#单行子查询：子查询结回的结果只有一条
	#注意：子查询查询的结果只能有一个字段
	SELECT salary
	FROM employees
	WHERE last_name='Abel'
);

#===================================================

#题目：返回job_id与141号员工相同，salary比143号员工多的员工
#            姓名，job_id 和工资
#方式一：
#1.查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id=141;#ST_CLERK
#2.查询143号员工的薪水
SELECT salary
FROM employees
WHERE employee_id=143;#2600
#3.查询job_id为ST_CLERK薪水比2600高的员工信息
SELECT first_name,job_id,salary
FROM employees
WHERE job_id='ST_CLERK' AND salary>2600;

#方式二：子查询
SELECT first_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
);

#==================================================

#题目：返回公司工资最少的员工的last_name,job_id和salary
#方式一
#1.最少工资是多少
SELECT MIN(salary)
FROM employees; #2100
#2.查询工资为2100的员工信息
SELECT last_name,job_id,salary
FROM employees
WHERE salary=2100;

#方式二:
#2.查找最低薪水的员工信息
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	#1.先查找最低薪水
	SELECT MIN(salary)
	FROM employees
)
/*
#注意：下面的写法不对
SELECT last_name,job_id,salary
FROM employees
WHERE salary=min(salary) #where后面不要使用组函数
*/

#===========================================================

#题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

#2.查询各部门最低工资比2100低的
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
	#1.查询50号部门最低工资
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
);



#注意：子查询返回的是一个空值（不是null）不是错误只不过是没有查询到匹配的内容。
SELECT first_name,job_id,salary
FROM employees
WHERE job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id=1410
)

#================================================================

#下面的sql语句会报错
SELECT first_name
FROM employees
WHERE salary > ( #子查询返回的结果为多条数据 但是使用的运算符是单行子查询使用的运算符
	SELECT salary
	FROM employees
	WHERE department_id = 50
)


#需求：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员
#              工号、姓名、job_id 以及salary
#2.查询其它工种员工的薪水比工种为IT_PROG的员工的薪水任意一个低
SELECT employee_id,first_name,job_id,salary
FROM employees
WHERE salary <ANY(
	#1.查询工种为IT_PROG的员工的薪水
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';


#题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工
 #           的员工号、姓名、job_id 以及salary
SELECT employee_id,first_name,job_id,salary
FROM employees
WHERE salary <ALL(
	#1.查询工种为IT_PROG的员工的薪水
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';

#7. 查询工资比80号部门平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,first_name,salary
FROM employees
WHERE salary > (
	#80号部门平均工资
	SELECT AVG(salary)
	FROM employees
	WHERE department_id=80
);


#10.查询姓名中包含f的员工的相同部门的员工有哪些
SELECT first_name,department_id
FROM employees
WHERE department_id IN(
	#查找姓名中包含f的员工所在的部门
	SELECT department_id
	FROM employees
	WHERE first_name LIKE '%f%'
);

#11.查询各工种最低员工薪水的薪水等级
SELECT t1.*,t2.grade
FROM(
	#查询各工种最低薪水
	SELECT job_id,MIN(salary) min_salary
	FROM employees
	GROUP BY job_id
) t1 JOIN job_grades t2
ON  t1.min_salary BETWEEN t2.lowest_sal AND t2.Highest_sal;





#12.查询各部门(没有部门不要)员工最高薪水的员工的信息。
SELECT t1.*,t2.`department_id`,t2.`salary`,t2.`first_name`
FROM(
	#查询各部门最高薪水
	SELECT department_id,MAX(salary) max_sal
	FROM employees
	WHERE department_id IS NOT NULL
	GROUP BY department_id
) t1 JOIN employees t2
ON t1.department_id = t2.`department_id` AND t1.max_sal = t2.`salary`;







#13.查询30号部门薪水最低的员工的邮箱是多少
SELECT t2.*
FROM(
	#查询号部门最低薪水
	SELECT MIN(salary) min_salary
	FROM employees
	WHERE department_id=30 #2500
) t1 JOIN employees t2
ON t1.min_salary=t2.`salary` AND t2.`department_id`= 30;


SELECT *
FROM employees
WHERE salary = (
	SELECT MIN(salary) min_salary
	FROM employees
	WHERE department_id=30
) AND department_id = 30;

liang79096

关注

23
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
MySQL 基础day2——多表查询、单行、多行函数、分组、过滤、子查询

子查询：在一条查询语句a中再嵌套一条查询语句b。那么b语句叫作子查询（内查询）a语句叫作主查询（外查询）order by 字段名1 asc/desc,字段名2 asc/desc,......order by 字段名1 asc/desc,字段名2 asc/desc,......from 表1 表别名1，表2 表别名2,......from 表1 表别名1，表2 表别名2,......多表查询：当查询的多个字段不在同一张表时就需要用到多表查询。多表查询：当查询的多个字段不在同一张表时就需要用到多表查询。
复制链接

扫一扫