【无标题】MYSQL数据库SQL复习加面试

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

提示：该文章用于复习mysql基础加高频面试题复习

该文章优势：有免费且全方面的数据库sql基础知识、数据库练习题。并在练习题下方有相应的答案即参考资料。适合小白学习与面临找工作的宝子们参考学习。博主第一次在该平台上发布自己的学习笔记。觉得有用的宝子们可以给博主点点关注。博主后续会不间断的发布对宝子们学习与面试有用的文章。良心制作，很期待爱学习的宝子们的每一个关注，你们的关注和点赞 就是我更新的动力。

---

提示：以下是一些工作中常用且笔试中会考到的一些sql题

一、DDL(SQL练习题)

1.DDL与DML区别

DML(data Manipulation Language)数据库操纵语言: 对数据库中的数据进行一些简单的操作。 如：IDUS(增删改查)
DDL(Data Definiton Language)数据库定义语言：对数据库中的某些对象(例如，database,table)进行管理，如Create,Alter和Drop.
区别:1.DML操作是可以手动控制事务的开启、提交和回滚的。2.DDL操作是隐性提交的，不能rollback!

2.数据库（database）SQL练习题

• 查看所有数据库
• 切换数据库
• 查看当前使用的数据库
• 查看数据库基本信息 注：该数据库必须存在
• 创建数据库[且设置数据库字符集为 utf8/gbk 等]
• 修改数据库的字符集
• 删除数据库

---

------------------- 数据库SQL练习题答案-------------------------
1.显示所有数据库：一个连接下面数据库通常有多个，所以database记住要加s
show databases ;

2.切换数据库：切换的目标数据库必须存在
use 数据库名;

3.查看当前使用的数据库：
select database();

4.查看数据库基本信息
show create database 数据库名

5.创建数据库[且设置数据库字符集为 utf8/gbk 等]
create database 数据库名 character set utf8;

6.修改数据库的字符集
alter database 数据库名 character set gbk;

7.删除数据库
drop 数据库名.

理解：看到关键词眼想到常用单词
1.'数据库'database,
2.'表' table
3.'修改' alter
4.设置字符 character set
5.基本信息 show create ,且记住show create 后面必须加database 或table
5.显示 show
6.创建 create
7.查看 selcet
8.删除 drop
注：DDL的删除与修改是drop与alter。DML的删除与修改是delete与update

---

3.数据库表 SQL练习题

3.1 常用表约束

约束类型	约束关键字
主键约束（约束字段值唯一）	primary key
非空约束	not null
l唯一性约束	unique
外键约束	foreign key
自增约束	auto_increment
默认约束（用户设置字段默认值）	default 默认值

外键约束：foreign key (从表外键字段名) references 主表名（主表主键字段名）
注：从表：外键字段所在的表叫从表，外键所关联的表是主表，外键一般都是关联主表的主键字段。且外键必须与主表主键数据类型保持一致

3.2 表操作 SQL 练习

1. 创建一个student表。
   要求如下：

   id 整数类型 自增约束 主键约束
   name 字符串类型 非空约束
   sex 字符串类型 默认值为 男
   

2. 显示当前数据库中所有数据表
3. 查看表的基本信息
4. 查看表的字段信息
5. 修改表名
6. 修改字段（可以修改字段名或字段类型）
7. 向表中添加字段
8. 删除字段
9. 删除表
10. 外键约束练习
    要求如下：

1.创建一个class(班级表)
classid 整数类型 主键约束
studentid 字符串类型 
2.为studentid添加外键约束，外键关联student表的主键id字段,并设置外键名称fk_stuID

11.删除studentid外键字段

1.create table student(
	id int auto_increment primary key,
	name varchar(50) not null,#数据长度自定义，这里设置50，字符串类型必须设置字符长度
	sex varchar(10) default '男' #注，最后一个字段不需要加,
)
2.show table
3.show create table 表名
4.desc 表名
5.alter table 旧表名 rename 新表名
6.alter table chang 旧字段名 新字段名 字段类型(取值长度) #字段类型 可以修改也可不修改
7.alter table 表名 add [column] 字段名 字段类型（取值长度）
8.alter table 表名 drop [column] 字段名
9.drop table 表名

理解：添加字段和删除字段都是对表的修改所以使用alter 且alter属于DDL 范围，所以删除使用drop

---

二、DML（SQL练习题）

1.增删改练习题

如下有一张学生表，完成学生表CRUD操作
create table student(
id int,
name varchar(30),
age int,
gender varchar(30)
)

1.1 INSERT

1.向学生表中插入一条完整 数据：1,‘张三’,18,‘男’
2.向学生表中同时插入多条数据： 2,‘李四’,19,‘女’ 3,‘王五’,18,‘男’
3.向学生表中插入数据：4,''赵六,‘女’

insert练习题答案
1. insert into student (id,name,age,gender) values(1,'张三',18,'男')
2. insert into student (id,name,age,gender) values(2,'李四',19,'女'),(3,'王五',18,'男')
3. insert into student(id,name,gender) values(4,'赵六','女')

1.2 UPDATE

1.更新name为张三的age 为90（更新部分数据）
2.更新student表所有记录的gender为男（更新所有数据）

update练习题答案
4. update student set age=90 where name='张三'
5. update student set gender='男'

1.3 DELETE

1.删除name为张三的数据（部分删除）
2.删除student表中所有数据（全部删除）

1. delete from student where name='张三'
2. delete from student 

注：删除表数据一共有两种方式：delete与truncate 都可以删除表中所有数据功能。
区别:
1.delete 可以添加where条件来进行数据的部分删除
2.truncate 语句只能删除表的所有数据不能实现表数据部分删除功能。 truncate删除表数据后，表自增约束默认初始化值重新由1开始递增，delete删除表中所有数据，当再次向表中插入数据。自增字段的值会从之前旧数据中最大的值开始+1，并不会重新由1开始排序
3.delete语句是DML语句，truncate语句是DDL语句

---

2 SQL查询练习题（单表查询）

2.1 聚合函数

作用：将多行记录汇总计算，输入多行输出一行，聚合函数具有自动过滤NULL值功能，若某行记录值为NULL,则直接过滤该NULL值，该行记录不参与运算。
使用规则：只能在select、having、orderby字句中使用聚合函数。where字句中不能使用聚合函数

聚合函数	作用描述
count（表达式）	主要用于计算行数，表达式不同，计算结果不同
max(表达式)	主要用于从一组值中选择最大值，表达式的值通常指定为数据表中的某个字段
min(表达式)	主要用于从一组值中选择最小值，表达式的值通常指定为数据表中的某个字段
sum(表达式)	用于计算某列中所有值的总和，表达式的值通常指定为数据表中的某个字段
avg(表达式)	用于计算某列的平均值，表达式的值通常指定为数据表中的某个字段

注：聚合函数自动过滤NULL值，必须满足表达式的值是计算指定的字段，否则不会自动过滤NULL值。
例1 :select count(*)：计算表中的所有行数，包括所有列都为 NULL 的行,当count()聚合函数的表达式为*，就算某一行记录所有字段值都为NULL,该行记录也不会被过滤掉，因为MySQL 不需要实际读取每一行的数据，只需要计算行数即可。
例2：select count(column_name) ：计算指定字段的非 NULL 值数量。如果某行的 column_name 字段值为 NULL，则该行不会被计数。比 COUNT(*) 稍慢一些，因为 MySQL 需要检查每一行的 column_name 值是否为 NULL。

---

2.2 聚合函数练习题

在数据库中创建如下student学生表，练习题使用创建好的学生表

create table student(
sno int primary key, #学号
sname varchar(100), #姓名
age int, #年龄
gender varchar(10) #性别
);
#插入数据
insert into student(sno,sname,age,gender) 
values(1001,'张三',17,'男'),(1002,'李四',20,'女'),(1003,'王五',26,'男'),(1004,'麻子',21,'女')
#插入值为NULL的测试数据
insert into student(sno) values(1005),(1006)
insert into student(sno,sname,age) values(1007,'赵六',33),(1008,'陈七',29)

1. 计算学生表中总记录数
2. 计算学生表中sname字段的总行数，要求NULL值不参与计算，且对sname的值去重统计
3. 计算学生表中最大年龄、最小年龄、平均年龄
4. 计算学生表中男生的最大年龄
5. 计算学生表中年龄于大于18岁的学生平均年龄
6. 计算学生表中男生和女生的平均年龄

答案：
1.select count(*) from student
2.select count(distinct sname) from student
3.select max(age) as max_age, min(age) as min_age, avg(age) as avg_age from student
4. select max(age) as max_age  from student where gender='男'
5.select avg(age)  as avg_age from student where age>18
6.select avg(age) as avg_age  from student where gender is not null group by gender

as 可以为查询的结果列取别名，也可以为查询的表取别名

2.3 条件查询 where

在select 查询语句中对from 表 对象使用where 字句来对表进行数据过滤即为条件查询。[where 表达式]
where 通常与 关系运算符、关键字 来进行表的列值运算进行表数据过滤。
1.关系运算符

关系运算符	解释
=	等于
<>	不等于
！=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于

例如：查询年龄为18岁的学生信息

select * from student where age=18

2.关键字：关键字一般作用对象是表中的某个字段

关键字	作用
in	判断字段值是否在指定集合中,指定集合包含字段值，条件成立
not in	判断字段值是否在指定集合中，指定集合不包含字段值，条件成立
between 值1 and 值2	判断字段值是否在指定[值1，值2] 范围内。注：两边都是闭区间,比较的字段可以是字符串但是字符串的值必须是纯数字，不符合规范的值会被自动过滤但sql语句不会报错
is null	空值查询，判断字段值是否为空值NULL。等于NULL则符合条件空值NULL不同于，也不同于空字符串
is not null	非空值查询，与空值查询同理但条件相反
and	and 关键字可以连接两个或者多个查询条件 ，相当于java中的&（与）运算
or	or也可以连接两个或多个查询条件，相当于java中的 | (或)运算
like	模糊匹配，判断字段值是否与 给定匹配规则的字符串值相等

2.1 关键字sql练习

1. 查询年龄为18 和20岁的学生信息
2. 查询年龄在18 -20岁之间的学生信息
3. 查询年龄不在18-20岁之间的学生信息
4. 查询性别为空值的学生信息
5. 查询年龄大于18且性别为女的学生信息
6. 查询年龄小于30 或者性别为女的学生的名字
7. 查询姓王的学生
8. 查询学生名字中有’六’字的学生信息
9. 查询名字中以 五 结尾且名字只有两个字的学生

代码演示：

1.查询年龄为18 和20岁的学生信息. 
注：（）集合里面的值类型根据字段类型确定。当age为整数型int,则集合元素值 18 20 也是int型，若字段为字符串类型则以 （'18','20'）来定义集合
select * from student where age in (18,20)

2.查询年龄在18 到20岁之间的学生信息
select * from student where age between 18 and 20

3.查询年龄不在18-20岁之间的学生信息
select * from student where age not between 18 and 20

4.查询性别为空值的学生信息
select * from student  where gender is null;

5.查询年龄大于18且性别为女的学生信息
select * from student where age>18 and gender='女'

6.查询年龄小于30 或者性别为女的学生的名字
select sname from student where age<30 or gender='女'

7.查询姓王的学生
select * from student where sname like '王%'

8.查询学生姓名中有'六'字的学生信息
select * from student where sname like '%六%'

9.查询姓名中以 五 结尾且名字只有两个字的学生
select * from student where sname like '_五'

通配符 % ：匹配任意长度的字符，可以是零个、一个或多个。 mysql中一个汉字占一个字符。一个字符占 三个字节 因为mysql默认采用utf-8编码。

通配符 _ ： 匹配单个任意字符。一个_只占一个字符，可以灵活使用，ab_ 、 a_ _b 、 _ _ a

---

2.4 group by 分组查询

分组查询执行在条件查询(where)之后，用于将表中数据按照指定条件进行分组。当查询语句中有where语句时，where先过滤表数据，将过滤后的表数据再进行group by语句分组。分组后的数据想继续过滤，可以通过having 语句来继续过滤分组后的数据。

注：having不能单独使用，如要结合group by 一起使用，且在使用 GROUP BY 子句时，SELECT 语句中可以包含非聚合列（指列没有使用到聚合函数），但这些非聚合列必须出现在 GROUP BY 子句中。SELECT 语句中的其他列则必须被聚合函数（如 COUNT(), SUM(), AVG(), MAX(), MIN() 等）包围。

例如：
select gender, avg(age) as avg_age 
from student
where age>18
group by gender  
having gender='男';

#sql执行顺序分析: from>where>group by>having>select
理解：
from 确定要执行的数据表student，
where 筛选出年龄大于18的所有符合条件的数据 此时表发送变化 
mysql底层为我们创建临时视图  假如为student2表，
group by语句在student2 上进行数据分组 
因为gender的取值为男和女 可以理解为group by 语句 通过gender列的值 将student2 表分成了两份
having 再过滤条件拿到gender='男' 这一份数据
最后select 再到最终结果表中计算返回结果

如上sql:gender是非聚合列，但该列在group by中出现，所以可以将该列的值做为查询结果，age由于未在group by中出现，所以需要使用聚合函数处理该列的值。

2.5 order by 对查询结果排序

排序语句在查询语句select 后面执行，当查询出表中数据后order by用于对查询结果进行排序。

语法： order by 字段1[,...字段n]   [desc | asc]
说明：当排序字段有多个 则代表将所有字段组合在一起进行排序，例如字段1值相同比较字段2...依此类推
asc 升序排序 desc 降序排序

例如：查询所有学生信息 要求按照年龄降序
select * from student order by age

---

2.6 limit 分页查询

limit子句在 SQL 查询中用于限制返回的记录数。通常用于分页查询。

语法： limit [offset,] count
offset:偏移量，当偏移量为0 则代表从查询结果集数据第一行开始返回数据
count: 行数，表示从当前偏移量的位置开始往下一共读取多少行数据
当limit 后面只有一个参数时，该参数默认指定行数，偏移量默认为0

例如：查询学生表中两位年龄最大的学生信息
select * from student where age is not null order by age desc limit 0,2

说明：因为按例表中age字段有保存控制NULL，
当我们按照年龄排序时因该排除空值的干扰。
所以使用where条件过滤空值NULl,
由于要查询年龄最大的学生 这里我们使用降序，
通过limit控制返回的数据行数

注：limit语句是优先级最低的，低于排序语句

---

3.SQL查询练习题（多表查询）

表之间的对应关系：
1.一对多：亦称为多对一。例如：部门与员工，一个部门可以有多个员工，一个员工只能属于一个部门。一对多关系通常通过定义外键来实现，且在多的一方定义外键。且一对多关系也可以通过定义中间表来维护一对多关系。这种方式也更加合理。
2.多对多：例如：学生与课程，一个学生可以选择多门课程，一个课程也可以被多门学生选择。一般实现多对多关系需要定义一张中间表，中间表定义两表之间各自的主键。例如在中间表中创建学生表学生id字段，课程表课程id字段。通过这两个字段来维护多对多表之间的关系。
3.一对一：一对一的关联关系在数据库中通常会在一张表中定义。开发过程中并不常见，例如：学生表与学生学历信息表，一个人只能对应一条学历信息。所以可以选择在任意一张表中设置外键。一般可以将学历信息表合并到学生表中，只有当数据量庞大时才选择拆表定义一对一关系表。

---

多表查询 SQL练习题

概述：多表查询即为从多张表中查询数据。
笛卡尔积：表数据的乘积，

例如: a(3),b(3),c(3)  
1.select count(*) from a,b; 
输出9行数据 表a 一共三条数据 表b也有三条数据 查询表a与表b 返回3x3=9 条数据

2.select count(*) from a,b,c
输出27，与上同理 axbxc =3x3x3=27

注：出现笛卡尔积的情况：指查询多张表，查询时未添加查询条件，此时便会出现笛卡尔积。

在多表查询中消除笛卡尔积需要添加表的连接条件，添加连接条件的sql语句也叫连接查询

3.1连接查询

连接查询分类如下：

内连接：
	相当于查询AB两张表取交集部分
外连接：
	左外连接：
		查询A的所有数据，同时拼接上B对应的数据，此时A在左外连接语句左边
	右外连接：
		查询B的的所有数据，同时拼接上A对应的数据，此时B在右外连接语句右边
自连接：
	表与自身连接查询
	自连接表必须取别名来区分
			

连接查询方式	语法
隐式内连接	select 字段列表 from A，B where 条件
显示内连接	select 字段列表 from 表1 [inner] join 表2 on 连接条件
左外连接	select 字段列表 from 表1 left [outer] join 表2 on 条件
右外连接	select 字段列表 from 表1 right [outer] join 表2 on 条件
自连接	select 字段列表 from 表a 别名a join 表a 别名b on 条件

3.1.1 连接查询SQL练习题

1.数据准备

create table dept (
id int auto_increment primary key comment 'id',
name varchar(50) not null comment '部门名称'
) comment '部门表'; 
#COMMENT 关键字用于为表、列或其他数据库对象添加注释或描述

insert into dept (id, name)
values (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办'),
       (6, '人事部');

create table emp(
    id int auto_increment primary key ,
    name varchar(50) not null ,
    age int,
    job varchar(20) comment '职位',
    salary int ,
    entrydate date comment '入职时间',
    managerid int comment '直属领导id',
    dept_id int comment '所在部门id'
) comment '员工表';

insert into emp
values ( 1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5 ),
       ( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
       ( 3, '杨晓', 33, '开发', 8400, '2000-11-03', 2, 1 ),
       ( 4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1 ),
       ( 5, '陈玉存', 43, '开发', 10500, '2004-09-07', 3, 1 ),
       ( 6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1 ),
       ( 7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),
       ( 8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3 ),
       ( 9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),
       ( 10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),
       ( 11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),
       ( 12, '何碧文', 19, '职员', 3750, '2007-05-09', 10, 2 ),
       ( 13, '东方白', 19, '职员', 5500, '2009-02-12', 10, 2 ),
       ( 14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),
       ( 15, '鱼梁洲', 38, '销售', 4600, '2004-10-12', 14, 4 ),
       ( 16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),
       ( 17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null );

---

2.连接查询SQL练习题

1.查询每一个员工的姓名及关联的部门的名称
2.查询emp表的所有数据和对应的部门信息
3.查询dept表的所有数据和对应的员工信息
4.查询员工的名字及其对应的领导名字

答案：

#1.查询每一个员工的姓名及关联的部门的名称
select emp.name, dept.name from emp, dept where emp.dept_id=dept.id;#隐式内连接写法
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id; #显示内连接写法

#2.查询emp表的所有数据，和应于的部门信息(左)
select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id;

#3.查询dept表的所有数据，和对于的员工信息(右)
select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;

#4.查询员工及其所属领导的名字
#自连接可以看成两张一样的表进行连接查询
select a.name, b.name from emp a join emp b on a.managerid=b.id;

---

3.2 联合查询

union[all] ：联合查询就是把多次查询的结果合并起来，形成一个新的查询结果集

语法： select 字段列表 from 表A
		union [all]
	  select 字段列表 from 表B

注：有all 则表A的查询结果与表B的查询结果不会做对比去重，反之则重复的记录只能出现一次

联合查询SQL练习题

# 查询薪资低于4000和年龄大于40的员工信息
#有all 不会去重
1.select * from emp where salary<4000
union all
select * from emp where age>40 

2.select * from emp where salary<4000
union 
select * from emp where age>40

注：联合查询是将结果集合并起来，并不是向笛卡尔积那样表与表之间字段拼接在一起记录数交叉相乘取乘积。
联合查询SQL之间返回的字段列表必须保持一致。
union all 会将全部的数据合并在一起。（不去重）
union 会对合并的结果集数据去重

3.3 子查询

概述：SQL语句中嵌套select 语句为嵌套查询，又称子查询。
select * from 表A
where 字段 in (select 字段 from 表B)
子查询外的语句可以是insert、update、delete、select 中的一个
按子查询的结构分：

结构名称	描述
标量子查询	子查询的结果为单个值
列子查询	子查询的结果为一列
行子查询	子查询的结果为一行
表子查询	子查询的结果为多行多列

按子查询的位置分：

1. where 之后
2. from 之后
3. select 之后

子查询练习题
1.查询销售部门下的所有员工信息（通过标量子查询获取数据）
2.查询销售部与市场部的所有员工信息（通过列子查询获取数据）
3.查询与张无忌的薪资及直属领导相同的员工信息（通过行子查询获取数据）
4.查询与鹿杖客和宋远桥的职位和薪资相同的员工信息（通过表子查询获取数据）
5.查询入职日期是’2006-01-01‘之后的员工信息和部门信息(表子查询做为子表使用）

# 1.查询销售部门下的所有员工信息（通过标量子查询获取数据）
select * from emp where dept_id=(select id from dept where dept.name='销售部')

# 2.查询销售部与市场部的所有员工信息（通过列子查询获取数据）
select * from emp where dept_id in (select id from dept where dept.name='销售部' or dept.name='市场部')

# 3.查询与张无忌的薪资及直属领导相同的员工信息（通过行子查询获取数据）
select * from emp where (salary,managerid)=(select (salary,managerid) from emp where name='张无忌')

# 4.查询与鹿杖客和宋远桥的职位和薪资相同的员工信息
select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋远桥'));

# 5.查询入职日期是’2006-01-01‘之后的员工信息和部门信息
# 先查询出入职在’2006-01-01‘之后员工的所有信息
# 再与部门表左连接（表子查询的子表做为临时表使用）
select e.*, dept.* from (select * from emp where entrydate>'2006-01-01') e left outer join dept on e.dept_id=dept.id;

多表查询综合按例练习题

表添加

create table salgrade (
    grade int,
    losal int comment '本薪资等级的最低界限',
    hisal int comment '最高界限'
) comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,025001,30000);

题目如下：
1.查询员工的姓名、年龄、职位、部门信息。
2.查询年龄小于30的员工姓名、年龄、职位、部门信息
3.查询拥有员工的部门id、部门名称、员工数量
4.查询所有年龄小于40的员工姓名、年龄，及其所属部门名称。没有部门的员工也需显示
5.查询所有员工的姓名、工资、工资等级
6.查询研发部所有员工的姓名、薪资、工资等级、部门名称
7.查询研发部员工的平均工资
8.查询工资比张无忌低的员工信息
9.查询工资比员工平均工资高的员工信息
10.查询低于本部门平均工资的员工信息
11.查询所有部门信息，并统计部门的员工人数

# 1.查询员工的姓名、年龄、职位、部门信息。
select e.name,e.age,e.job,d.name 
from emp e 
join dept d 
on e.dept_id =d.id

# 2.查询年龄小于30的员工姓名、年龄、职位、部门信息
select e.name,e.age,e.job,d.name 
from emp e 
join dept d 
on e.dept_id=d.id 
where e.gea<30
# 3.查询拥有员工的部门id、部门名称、员工数量
select d.id,d.name,count(*) 
from emp e 
join dept d 
on e.dept_id=d.id 
group by d.name

# 4.查询所有年龄小于40的员工姓名、年龄，及其所属部门名称。没有部门的员工也需显示
select e.name,e.age,d.name 
from emp e left 
join dept d 
on e.dept_id=d.id 
where e.age<40;

# 5.查询所有员工的姓名、工资、工资等级
select e.name,e.salary,s.grade 
from emp e 
join salgrade s 
on e.salary between s.losal 
and s.hisal

# 6.查询研发部所有员工的姓名、薪资、工资等级、部门名称
select e.name,e.salary,s.grade,d.name as deptName 
from emp e 
join salgrade s on e.salary between s.losal and s.hisal
join dept d on d.id = e.dept_id
where d.name='研发部';

# 7.查询研发部员工的平均工资
select avg(e.salary),d.name 
from emp e 
join dept d 
on e.dept_id=d.id 
group by d.name
having d.name='研发部'

# 8.查询工资比张无忌低的员工信息
select * from emp e 
where e.salary<(select salary from emp where name='张无忌')

# 9.查询工资比员工平均工资高的员工信息
select * from emp e 
where e.salary>(select avg(salary) from emp)

# 10.查询低于本部门平均工资的员工信息
select e.*,a.name,a.avgSalary 
from emp e 
join 
(select d.id as id ,d.name as name ,avg(e.salary) as avgSalary 
from emp e join dept d 
on e.dept_id=d.id 
group by d.name) a
on e.dept_id=a.id
where e.salary<a.avgSalary;

11.查询所有部门信息，并统计部门的员工人数
select d.*,a.deptRS 
from dept d 
join 
(select count(*) as deptRS,d.id as id 
from emp e 
join dept d 
on e.dept_id=d.id 
group by d.name) a
on d.id=a.id

注意别踩坑：在第四题中：
查询所有年龄小于40的员工姓名、年龄，及其所属部门名称。没有部门的员工也需显示
正确SQL：

select e.name,e.age,d.name 
from emp e left 
join dept d 
on e.dept_id=d.id 
where e.age<40;

错误SQL:

select e.name,e.age,d.name 
from emp e left 
join dept d 
on e.dept_id=d.id  and  e.age<40

很多人习惯在 on 通过and 来指定多个连接条件，但是在第四题中使用and 添加连接条件来对emp （左连接查询主表） 过滤是错误的 ，因为 通过左连接（LEFT JOIN）来选取员工的信息，同时连接条件包含员工年龄小于 40 的条件。但是，直接在 ON 子句中写 e.age < 40 是不符合左连接（LEFT JOIN）的通常逻辑的。左连接（LEFT JOIN）会返回左表（这里是 emp 表）中的所有记录，以及与右表（这里是 dept 表）中匹配的记录。如果在 ON 子句中加入了 e.age < 40 这样的条件，那么实际上这个条件会限制左表（emp）中哪些记录会被加入到结果集中，而不是仅仅限制哪些记录会进行连接。如果你想要选取所有年龄小于 40 的员工以及他们的部门信息（如果有的话），你应该在 WHERE 子句中而不是在 ON 子句中加入这个条件。