本文详细介绍了一个包含多个数据库脚本的示例，包括数据库创建、表结构定义、数据插入等操作，以及基于这些数据进行的各类SQL查询练习。
数据库脚本准备
-- -----------------------------------
-- 创建db10库、emp表并插入记录
-- -----------------------------------
-- 删除db10库(如果存在)
drop database if exists db10;
-- 重新创建db10库
create database db10 charset utf8;
-- 选择db10库
use db10;
-- 删除员工表(如果存在)
drop table if exists emp;
-- 创建员工表
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(50), -- 员工姓名
gender char(1), -- 员工性别
birthday date, -- 员工生日
dept varchar(50), -- 所属部门
job varchar(50), -- 所任职位
sal double, -- 薪资
bonus double -- 奖金
);
-- 往员工表中插入记录
INSERT INTO `emp` VALUES (null, '王海涛', '男', '1995-03-25', '培优部', '讲师', 1800, 400);
INSERT INTO `emp` VALUES (null, '齐雷', '男', '1994-04-06', '培优部', '讲师', 2500, 700);
INSERT INTO `emp` VALUES (null, '刘沛霞', '女', '1996-06-14', '培优部', '讲师', 1400, 400);
INSERT INTO `emp` VALUES (null, '陈子枢', '男', '1991-05-18', '培优部', '总监', 4500, 600);
INSERT INTO `emp` VALUES (null, '刘昱江', '男', '1993-11-18', '培优部', '讲师', 2600, 600);
INSERT INTO `emp` VALUES (null, '王克晶', '女', '1998-07-18', '就业部', '讲师', 3700, 700);
INSERT INTO `emp` VALUES (null, '苍老师', '男', '1995-08-18', '就业部', '总监', 4850, 500);
INSERT INTO `emp` VALUES (null, '范传奇', '男', '1999-09-18', '就业部', '讲师', 3200, 700);
INSERT INTO `emp` VALUES (null, '刘涛', '男', '1990-10-18', '就业部', '讲师', 2700, 500);
INSERT INTO `emp` VALUES (null, '韩少云', '男', '1980-12-18', NULL, 'CEO', 5000, null);
INSERT INTO `emp` VALUES (null, '董长春', '男', '1988-02-05', '培优部', '讲师', 3200, 300);
INSERT INTO `emp` VALUES (null, '张久军', '男', '1989-01-11', '培优部', '讲师', 4200, 500);
-- -----------------------------------
-- 创建db20库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db20库(如果存在)
drop database if exists db20;
-- 重新创建db20库
create database db20 charset utf8;
-- 选择db20库
use db20;
-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
id int primary key auto_increment, -- 部门编号
name varchar(20) -- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');
-- 删除员工表, 如果存在
drop table if exists emp;
-- 创建员工表, 要求id, name, dept_id
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(20), -- 员工姓名
dept_id int -- 部门编号
-- ,foreign key(dept_id) references dept(id)
);
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 4);
insert into emp values(null, '刘能', 4);
-- -----------------------------------
-- 创建db30库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db30库(如果存在)
drop database if exists db30;
-- 重新创建db30库
create database db30 charset utf8;
-- 选择db30库
use db30;
-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
id int primary key auto_increment, -- 部门编号
name varchar(20) -- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');
-- 删除员工表, 如果存在
drop table if exists emp;
-- 创建员工表（员工编号、员工姓名、所在部门编号）
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(20), -- 员工姓名
dept_id int -- 部门编号
);
-- 往员工表中插入记录
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 5);
-- -----------------------------------
-- 创建db40库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db40库(如果存在)
drop database if exists db40;
-- 重新创建db40库
create database db40 charset utf8;
-- 选择db40库
use db40;
-- 创建部门表
create table dept( -- 创建部门表
id int primary key, -- 部门编号
name varchar(50), -- 部门名称
loc varchar(50) -- 部门位置
);
-- 创建员工表
create table emp( -- 创建员工表
id int primary key, -- 员工编号
name varchar(50), -- 员工姓名
job varchar(50), -- 职位
topid int, -- 直属上级
hdate date, -- 受雇日期
sal int, -- 薪资
bonus int, -- 奖金
dept_id int, -- 所在部门编号
foreign key(dept_id) references dept(id)
);
-- 往部门表中插入记录
insert into dept values ('10', '培优部', '北京');
insert into dept values ('20', '就业部', '上海');
insert into dept values ('30', '大数据部', '广州');
insert into dept values ('40', '销售部', '深圳');
-- 往员工表中插入记录
insert into emp values ('1001', '王克晶', '办事员', '1007', '1990-12-17', '800', 500, '20');
insert into emp values ('1003', '齐雷', '分析员', '1011', '1991-02-20', '1900', '300', '10');
insert into emp values ('1005', '王海涛', '推销员', '1011', '1991-02-22', '2450', '600', '10');
insert into emp values ('1007', '刘苍松', '经理', '1017', '1991-04-02', '3675', 700, '20');
insert into emp values ('1009', '张慎政', '推销员', '1011', '1991-09-28', '1250', '1400', '10');
insert into emp values ('1011', '陈子枢', '经理', '1017', '1991-05-01', '3450', 400, '10');
insert into emp values ('1013', '张久军', '办事员', '1011', '1991-06-09', '1250', 800, '10');
insert into emp values ('1015', '程祖红', '分析员', '1007', '1997-04-19', '3000', 1000, '20');
insert into emp values ('1017', '韩少云', '董事长', null, '1991-11-17', '5000', null, null);
insert into emp values ('1019', '刘沛霞', '推销员', '1011', '1991-09-08', '1500', 500, '10');
insert into emp values ('1021', '范传奇', '办事员', '1007', '1997-05-23', '1100', 1000, '20');
insert into emp values ('1023', '赵栋', '经理', '1017', '1991-12-03', '950', null, '30');
insert into emp values ('1025', '朴乾', '分析员', '1023', '1991-12-03', '3000', 600, '30');
insert into emp values ('1027', '叶尚青', '办事员', '1023', '1992-01-23', '1300', 400, '30');
-- ------------------- 执行完毕 -----------------------
下列练习使用db10库中数据，选择db10库，如果没有先创建(参考sql脚本)
单表查询
1、列出emp表中的所有员工，显示所有列
select name,gender from emp where gender='男';
+--------+--------+
| name | gender |
+--------+--------+
| 王海涛 | 男 |
| 齐雷 | 男 |
| 陈子枢 | 男 |
| 刘昱江 | 男 |
| 苍老师 | 男 |
| 范传奇 | 男 |
| 刘涛 | 男 |
| 韩少云 | 男 |
| 董长春 | 男 |
| 张久军 | 男 |
+--------+--------+
2、列出emp表中的'培优部'的所有员工，显示部门名称, 员工姓名
select dept,name from emp where dept='培优部';
+--------+--------+
| dept | name |
+--------+--------+
| 培优部 | 王海涛 |
| 培优部 | 齐雷 |
| 培优部 | 刘沛霞 |
| 培优部 | 陈子枢 |
| 培优部 | 刘昱江 |
| 培优部 | 董长春 |
| 培优部 | 张久军 |
+--------+--------+
3、列出emp表中员工的奖金(bonus)，仅显示奖金，并剔除重复的值
select distinct bonus from emp;
+-------+
| bonus |
+-------+
| 400 |
| 700 |
| 600 |
| 500 |
| NULL |
| 300 |
+-------+
4、列出emp表中所有奖金高于500的员工，显示姓名、奖金、职位
select name,bonus,job from emp where bonus>500;
+--------+-------+------+
| name | bonus | job |
+--------+-------+------+
| 齐雷 | 700 | 讲师 |
| 陈子枢 | 600 | 总监 |
| 刘昱江 | 600 | 讲师 |
| 王克晶 | 700 | 讲师 |
| 范传奇 | 700 | 讲师 |
+--------+-------+------+
5、列出emp表中薪资在1000~2000之间的所有员工，显示姓名，薪资
select name,sal from emp where sal between 1000 and 2000;
+--------+------+
| name | sal |
+--------+------+
| 王海涛 | 1800 |
| 刘沛霞 | 1400 |
+--------+------+
6、列出emp表中奖金为300、500、700的所有员工，显示姓名、奖金
select name,bonus from emp where bonus in(300,500,700);
+--------+-------+
| name | bonus |
+--------+-------+
| 齐雷 | 700 |
| 王克晶 | 700 |
| 苍老师 | 500 |
| 范传奇 | 700 |
| 刘涛 | 500 |
| 董长春 | 300 |
| 张久军 | 500 |
+--------+-------+
7、列出emp表中姓名以'王'开头的员工，显示员工姓名
select name from emp where name like '王%';
+--------+
| name |
+--------+
| 王海涛 |
| 王克晶 |
+--------+
8、列出emp表中姓名以'涛'结尾的员工，显示员工姓名
select name from emp where name like '%涛';
+--------+
| name |
+--------+
| 王海涛 |
| 刘涛 |
+--------+
9、统计emp表中的所有的男员工的人数。
select count(*) 男员工人数 from emp where gender='男';
+------------+
| 男员工人数 |
+------------+
| 10 |
+------------+
10、统计每个职位的人数, 显示职位和对应人数
select ifnull(dept,'无部门') 部门,count(*) 人数 from emp group by dept;
+--------+------+
| 部门 | 人数 |
+--------+------+
| 无部门 | 1 |
| 培优部 | 7 |
| 就业部 | 4 |
+--------+------+
11、统计emp表中所有员工的总薪资(包含奖金)
select sum(sal) 总薪资 from emp;
+--------+
| 总薪资 |
+--------+
| 39650 |
+--------+
12、统计emp表中所有员工奖金的平均值
select avg(ifnull(bonus,0)) 平均奖金 from emp;
+-------------------+
| 平均奖金 |
+-------------------+
| 491.6666666666667 |
+-------------------+
13、按照员工年龄从小到大排序，显示姓名、出生年月、总薪资（薪资+奖金）
select name,birthday,sal+ifnull(bonus,0) 总薪资 from emp order by birthday desc;
+--------+------------+--------+
| name | birthday | 总薪资 |
+--------+------------+--------+
| 范传奇 | 1999-09-18 | 3900 |
| 王克晶 | 1998-07-18 | 4400 |
| 刘沛霞 | 1996-06-14 | 1800 |
| 苍老师 | 1995-08-18 | 5350 |
| 王海涛 | 1995-03-25 | 2200 |
| 齐雷 | 1994-04-06 | 3200 |
| 刘昱江 | 1993-11-18 | 3200 |
| 陈子枢 | 1991-05-18 | 5100 |
| 刘涛 | 1990-10-18 | 3200 |
| 张久军 | 1989-01-11 | 4700 |
| 董长春 | 1988-02-05 | 3500 |
| 韩少云 | 1980-12-18 | 5000 |
+--------+------------+--------+
14、查询下个月过生日的所有员工，显示员工姓名和出生日期
select name,birthday from emp where month(birthday)=month(now())+1;
+--------+------------+
| name | birthday |
+--------+------------+
| 陈子枢 | 1991-05-18 |
+--------+------------+
15、求1995年入职的员工信息。
select * from emp where year(birthday)=1995;
+----+--------+--------+------------+--------+------+------+-------+
| id | name | gender | birthday | dept | job | sal | bonus |
+----+--------+--------+------------+--------+------+------+-------+
| 1 | 王海涛 | 男 | 1995-03-25 | 培优部 | 讲师 | 1800 | 400 |
| 7 | 苍老师 | 男 | 1995-08-18 | 就业部 | 总监 | 4850 | 500 |
+----+--------+--------+------------+--------+------+------+-------+
16、求emp表中薪资最高的前3名员工的信息，显示姓名和薪资
select name,sal from emp order by sal desc limit 0,3;
+--------+------+
| name | sal |
+--------+------+
| 韩少云 | 5000 |
| 苍老师 | 4850 |
| 陈子枢 | 4500 |
+--------+------+
多表查询
-- 准备数据： 以下练习将使用db30库中的表及表记录，请先进入db30数据库!!!
17、查询【所有部门】及部门对应的员工，如果某个部门下没有员工，员工显示为null
- 【左外连接查询】：可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可。
select t1.name 员工姓名,t2.name 部门 from emp t1 left join dept t2 on t1.dept_id=t2.id;
+----------+--------+
| 员工姓名 | 部门 |
+----------+--------+
| 张三 | 财务部 |
| 李四 | 人事部 |
| 老王 | 科技部 |
| 赵六 | NULL |
+----------+--------+
18、查询【所有员工】及员工所属部门，如果某个员工没有所属部门，部门显示为null
- 【右外连接查询】：可以将右边表中的所有记录都查询出来，左边表只显示和右边相对应的数据，如果右边表中某些记录在左边没有对应的数据，可以显示为null。
select emp.name 员工姓名,dept.name 部门 from emp right join dept on dept.id=emp.dept_id;
+----------+--------+
| 员工姓名 | 部门 |
+----------+--------+
| 张三 | 财务部 |
| 李四 | 人事部 |
| 老王 | 科技部 |
| NULL | 销售部 |
+----------+--------+
子查询
-- 准备数据：以下练习将使用db40库中的表及表记录，请先进入db40数据库!!!
19、列出薪资比'王海涛'的薪资高的所有员工，显示姓名、薪资
select emp.name,emp.sal from emp,(select sal wht_sal from emp where name='王海涛') wht_table where emp.sal>wht_table.wht_sal;
+--------+------+
| name | sal |
+--------+------+
| 刘苍松 | 3675 |
| 陈子枢 | 3450 |
| 程祖红 | 3000 |
| 韩少云 | 5000 |
| 朴乾 | 3000 |
+--------+------+
20、列出与'刘沛霞'从事相同职位的所有员工，显示姓名、职位。
select emp.name,emp.job from emp,(select job lpx_job from emp where name='刘沛霞') lpx_table where emp.job=lpx_table.lpx_job;
+--------+--------+
| name | job |
+--------+--------+
| 王海涛 | 推销员 |
| 张慎政 | 推销员 |
| 刘沛霞 | 推销员 |
+--------+--------+
21、列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名
select e1.name,e1.topid,e2.name
-> from emp e1,emp e2
-> where e1.topid=e2.id;
+--------+-------+--------+
| name | topid | name |
+--------+-------+--------+
| 王克晶 | 1007 | 刘苍松 |
| 齐雷 | 1011 | 陈子枢 |
| 王海涛 | 1011 | 陈子枢 |
| 刘苍松 | 1017 | 韩少云 |
| 张慎政 | 1011 | 陈子枢 |
| 陈子枢 | 1017 | 韩少云 |
| 张久军 | 1011 | 陈子枢 |
| 程祖红 | 1007 | 刘苍松 |
| 刘沛霞 | 1011 | 陈子枢 |
| 范传奇 | 1007 | 刘苍松 |
| 赵栋 | 1017 | 韩少云 |
| 朴乾 | 1023 | 赵栋 |
| 叶尚青 | 1023 | 赵栋 |
+--------+-------+--------+
22、查询emp表中比'齐雷'薪资高的所有员工，显示姓名、薪资
select emp.name 姓名,emp.sal 薪资 from emp,(select sal ql_sal from emp where name='齐雷') ql_table where emp.sal>ql_table.ql_sal;
+--------+------+
| 姓名 | 薪资 |
+--------+------+
| 王海涛 | 2450 |
| 刘苍松 | 3675 |
| 陈子枢 | 3450 |
| 程祖红 | 3000 |
| 韩少云 | 5000 |
| 朴乾 | 3000 |
+--------+------+
23、查询emp表中和'齐雷'从事相同职位的所有员工，显示姓名、职位
select emp.name 姓名,emp.job 职位 from emp,(select job ql_job from emp where name='齐雷') ql_table where emp.job=ql_table.ql_job;
+--------+--------+
| 姓名 | 职位 |
+--------+--------+
| 齐雷 | 分析员 |
| 程祖红 | 分析员 |
| 朴乾 | 分析员 |
+--------+--------+
24、查询emp表中'陈子枢'所有下属员工，假设不知道陈子枢的编号(1011)
-- 查询陈子枢的编号
-- 查询上级编号为 1011 的员工
select * from emp,(select id czs_id from emp where name='陈子枢') czs_table where emp.topid=czs_table.czs_id;
+------+--------+--------+-------+------------+------+-------+---------+--------+
| id | name | job | topid | hdate | sal | bonus | dept_id | czs_id |
+------+--------+--------+-------+------------+------+-------+---------+--------+
| 1003 | 齐雷 | 分析员 | 1011 | 1991-02-20 | 1900 | 300 | 10 | 1011 |
| 1005 | 王海涛 | 推销员 | 1011 | 1991-02-22 | 2450 | 600 | 10 | 1011 |
| 1009 | 张慎政 | 推销员 | 1011 | 1991-09-28 | 1250 | 1400 | 10 | 1011 |
| 1013 | 张久军 | 办事员 | 1011 | 1991-06-09 | 1250 | 800 | 10 | 1011 |
| 1019 | 刘沛霞 | 推销员 | 1011 | 1991-09-08 | 1500 | 500 | 10 | 1011 |
+------+--------+--------+-------+------------+------+-------+---------+--------+
多表查询
25、列出在'培优部'任职的员工，假定不知道'培优部'的部门编号，显示部门名称，员工名称。
select dept.name 部门名称,emp.name 员工姓名 from dept,emp where emp.dept_id=dept.id and dept.name='培优部';
+----------+----------+
| 部门名称 | 员工姓名 |
+----------+----------+
| 培优部 | 齐雷 |
| 培优部 | 王海涛 |
| 培优部 | 张慎政 |
| 培优部 | 陈子枢 |
| 培优部 | 张久军 |
| 培优部 | 刘沛霞 |
+----------+----------+
26、列出最低薪资大于1500的各种职位，显示职位和该职位的最低薪资
select job,min(sal) from emp group by job having min(sal)>1500;
+--------+----------+
| job | min(sal) |
+--------+----------+
| 分析员 | 1900 |
| 董事长 | 5000 |
+--------+----------+
27、列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。
select dept_id 部门编号,count(*) 员工数量,avg(sal) 平均薪资 from emp group by dept_id;
+----------+----------+-----------+
| 部门编号 | 员工数量 | 平均薪资 |
+----------+----------+-----------+
| NULL | 1 | 5000.0000 |
| 10 | 6 | 1966.6667 |
| 20 | 4 | 2143.7500 |
| 30 | 3 | 1750.0000 |
+----------+----------+-----------+
28、列出受雇日期早于直接上级的所有员工，显示员工编号、员工姓名、部门名称。
select e1.id 员工编号,e1.name 员工姓名,d.name 部门名称
-> from emp e1,emp e2,dept d
-> where e1.topid=e2.id and e1.dept_id=d.id and e1.hdate<e2.hdate;
+----------+----------+----------+
| 员工编号 | 员工姓名 | 部门名称 |
+----------+----------+----------+
| 1003 | 齐雷 | 培优部 |
| 1005 | 王海涛 | 培优部 |
| 1011 | 陈子枢 | 培优部 |
| 1001 | 王克晶 | 就业部 |
| 1007 | 刘苍松 | 就业部 |
+----------+----------+----------+
29、(左外连接)列出所有员工和员工对应的部门，如果员工没有对应的部门, 显示为null
select t1.name 员工姓名,t2.name 部门 from emp t1 left join dept t2 on t1.dept_id=t2.id;
+----------+----------+
| 员工姓名 | 部门 |
+----------+----------+
| 王克晶 | 就业部 |
| 齐雷 | 培优部 |
| 王海涛 | 培优部 |
| 刘苍松 | 就业部 |
| 张慎政 | 培优部 |
| 陈子枢 | 培优部 |
| 张久军 | 培优部 |
| 程祖红 | 就业部 |
| 韩少云 | NULL |
| 刘沛霞 | 培优部 |
| 范传奇 | 就业部 |
| 赵栋 | 大数据部 |
| 朴乾 | 大数据部 |
| 叶尚青 | 大数据部 |
+----------+----------+
30、(关联查询)列出在'就业部'任职的员工，假定不知道'就业部'的部门编号，显示部门名称和员工姓名
select t1.name,t2.name
-> from dept t1,emp t2
-> where t1.id=t2.dept_id and t1.name='就业部';
+--------+--------+
| name | name |
+--------+--------+
| 就业部 | 王克晶 |
| 就业部 | 刘苍松 |
| 就业部 | 程祖红 |
| 就业部 | 范传奇 |
+--------+--------+
31、(子连接查询)列出上级及上级对应的下属员工，显示上级编号(id)，上级姓名、员工姓名、上级编号(topid)
select e1.name 上级姓名,e2.name 员工姓名,e2.topid 上级编号
-> from emp e1,emp e2
-> where e1.id=e2.topid;
+----------+----------+----------+
| 上级姓名 | 员工姓名 | 上级编号 |
+----------+----------+----------+
| 刘苍松 | 王克晶 | 1007 |
| 陈子枢 | 齐雷 | 1011 |
| 陈子枢 | 王海涛 | 1011 |
| 韩少云 | 刘苍松 | 1017 |
| 陈子枢 | 张慎政 | 1011 |
| 韩少云 | 陈子枢 | 1017 |
| 陈子枢 | 张久军 | 1011 |
| 刘苍松 | 程祖红 | 1007 |
| 陈子枢 | 刘沛霞 | 1011 |
| 刘苍松 | 范传奇 | 1007 |
| 韩少云 | 赵栋 | 1017 |
| 赵栋 | 朴乾 | 1023 |
| 赵栋 | 叶尚青 | 1023 |
+----------+----------+----------+
32、（分组、聚合函数）列出最低薪资大于1500的各种职位及从事此职位的员工人数。
-- 先查询出各种职位的最低薪资
-- 提示：对分组后的记录筛选过滤请使用having替换where，并且having书写在最后
-- 再查询出最低薪资>1500的职位
-- 最后查询出每个职位对应的员工人数
select job,min(sal),count(*)人数 from emp group by job having min(sal)>1500;
+--------+----------+------+
| job | min(sal) | 人数 |
+--------+----------+------+
| 分析员 | 1900 | 3 |
| 董事长 | 5000 | 1 |
+--------+----------+------+
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
