二、单表查询
!!下列练习使用db10库中数据,选择db10库,如果没有先创建(参考sql脚本)
07、列出emp表中的所有员工,显示所有列
select * from emp;
±—±-------±-------±-----------±-------±-----±-----±------+
| id | name | gender | birthday | dept | job | sal | bonus |
±—±-------±-------±-----------±-------±-----±-----±------+
| 1 | 王海涛 | 男 | 1995-03-25 | 培优部 | 讲师 | 1800 | 400 |
| 2 | 齐雷 | 男 | 1994-04-06 | 培优部 | 讲师 | 2500 | 700 |
| 3 | 刘沛霞 | 女 | 1996-06-14 | 培优部 | 讲师 | 1400 | 400 |
| 4 | 陈子枢 | 男 | 1991-05-18 | 培优部 | 总监 | 4500 | 600 |
| 5 | 刘昱江 | 男 | 1993-11-18 | 培优部 | 讲师 | 2600 | 600 |
| 6 | 王克晶 | 女 | 1998-07-18 | 就业部 | 讲师 | 3700 | 700 |
| 7 | 苍老师 | 男 | 1995-08-18 | 就业部 | 总监 | 4850 | 500 |
| 8 | 范传奇 | 男 | 1999-09-18 | 就业部 | 讲师 | 3200 | 700 |
| 9 | 刘涛 | 男 | 1990-10-18 | 就业部 | 讲师 | 2700 | 500 |
| 10 | 韩少云 | 男 | 1980-12-18 | NULL | CEO | 5000 | NULL |
| 11 | 董长春 | 男 | 1988-02-05 | 培优部 | 讲师 | 3200 | 300 |
| 12 | 张久军 | 男 | 1989-01-11 | 培优部 | 讲师 | 4200 | 500 |
±—±-------±-------±-----------±-------±-----±-----±------+
08、列出emp表中的所有的男员工,显示姓名、性别
select name 姓名, gender 性别 from emp where gender = '男';
±-------±-----+
| 姓名 | 性别 |
±-------±-----+
| 王海涛 | 男 |
| 齐雷 | 男 |
| 陈子枢 | 男 |
| 刘昱江 | 男 |
| 苍老师 | 男 |
| 范传奇 | 男 |
| 刘涛 | 男 |
| 韩少云 | 男 |
| 董长春 | 男 |
| 张久军 | 男 |
±-------±-----+
09、列出emp表中的’培优部’的所有员工,显示部门名称, 员工姓名
select dept 部门, name 姓名 from emp where dept = '培优部';
±-------±-------+
| 部门 | 姓名 |
±-------±-------+
| 培优部 | 王海涛 |
| 培优部 | 齐雷 |
| 培优部 | 刘沛霞 |
| 培优部 | 陈子枢 |
| 培优部 | 刘昱江 |
| 培优部 | 董长春 |
| 培优部 | 张久军 |
±-------±-------+
10、列出emp表中员工的奖金(bonus),仅显示奖金,并剔除重复的值
select distinct bonus 奖金 from emp;
±-----+
| 奖金 |
±-----+
| 400 |
| 700 |
| 600 |
| 500 |
| NULL |
| 300 |
±-----+
11、列出emp表中所有奖金高于500的员工,显示姓名、奖金、职位
select name 姓名, bonus 奖金, job 职位 from emp where bonus > 500;
±-------±-----±-----+
| 姓名 | 奖金 | 职位 |
±-------±-----±-----+
| 齐雷 | 700 | 讲师 |
| 陈子枢 | 600 | 总监 |
| 刘昱江 | 600 | 讲师 |
| 王克晶 | 700 | 讲师 |
| 范传奇 | 700 | 讲师 |
±-------±-----±-----+
12、列出emp表中薪资在1000~2000之间的所有员工,显示姓名,薪资
select name 姓名, sal 薪资 from emp where sal between 1000 and 2000;
±-------±-----+
| 姓名 | 薪资 |
±-------±-----+
| 王海涛 | 1800 |
| 刘沛霞 | 1400 |
±-------±-----+
13、列出emp表中奖金为300、500、700的所有员工,显示姓名、奖金
select name 姓名, bonus 奖金 from emp where bonus in (300, 500, 700);
±-------±-----+
| 姓名 | 奖金 |
±-------±-----+
| 齐雷 | 700 |
| 王克晶 | 700 |
| 苍老师 | 500 |
| 范传奇 | 700 |
| 刘涛 | 500 |
| 董长春 | 300 |
| 张久军 | 500 |
±-------±-----+
14、什么是 数据库服务器、数据库、表、表记录?
1)什么是数据库服务器?
答:数据库服务器就是一个软件(比如MySQL),将这个软件安装在电脑上,这台电脑就是数据库服务器,就可以对外提供数据的存取服务。
在一个数据库服务器中可以创建多个数据库,每一个数据库都是单独的存取数据的仓库。
2)什么是数据库?
答:数据库就是存储数据的仓库。通常来说,一个网站的所有数据就存储在一个数据库中。
3)什么是表?
答:一个数据库中可以创建多张表,每个表用来存储一类信息。
4)什么是表记录?
答:一张表中可以包含多行表记录,每一行表记录用于存储某个具体的信息。
15、char和varchar的区别?
答:因为char是固定长度,无论输入的数据是多长都是占用固定的字节,造成了空间的浪费。
而varchar的长度是可变的,会根据输入的数据长度自动调整大小,不会造成空间的浪费,但是性能上会受到一定的影响。
对于要求查询速度的应用来讲,char会更合适。varchar更节省空间。
16、什么是主键约束(特点)、什么是唯一约束、什么是非空约束?
1)什么是主键约束(特点)?
答:如果为一个列添加了主键约束,那么这个列就是主键。主键的特点是:唯一且不为空。
2)什么是唯一约束?
答:如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的(既不能重复),但可以为空(NULL)。
3)什么是非空约束?
答:如果为一个列添加了非空约束,那么这个列的值就不能为空,但可以重复。
17、列出emp表中姓名以’王’开头的员工,显示员工姓名
select name 姓名 from emp where name like '王%';
±-------+
| 姓名 |
±-------+
| 王海涛 |
| 王克晶 |
±-------+
18、列出emp表中姓名以’涛’结尾的员工,显示员工姓名
select name 姓名 from emp where name like '%涛';
±-------+
| 姓名 |
±-------+
| 王海涛 |
| 刘涛 |
±-------+
19、统计emp表中的所有的男员工的人数。
select gender 性别, count(*) 人数 from emp where gender = '男';
±-----±-----+
| 性别 | 人数 |
±-----±-----+
| 男 | 10 |
±-----±-----+
20、统计每个职位的人数, 显示职位和对应人数
select job 职位, count(*) 人数 from emp group by job;
±-----±-----+
| 职位 | 人数 |
±-----±-----+
| CEO | 1 |
| 总监 | 2 |
| 讲师 | 9 |
±-----±-----+
21、统计emp表中所有员工的总薪资(包含奖金)
select sum(sal) + sum(bonus) 总薪资 from emp;
±-------+
| 总薪资 |
±-------+
| 45550 |
±-------+
22、统计emp表中所有员工奖金的平均值
select avg(bonus) 所有员工奖金的平均值 from emp;
±---------------------+
| 所有员工奖金的平均值 |
±---------------------+
| 536.3636363636364 |
±---------------------+
!!下列练习使用db40库中数据,选择db40库,如果没有先创建(参考sql脚本)
±-----±-------±-------±------±-----------±-----±------±--------+
| id | name | job | topid | hdate | sal | bonus | dept_id |
±-----±-------±-------±------±-----------±-----±------±--------+
| 1001 | 王克晶 | 办事员 | 1007 | 1990-12-17 | 800 | 500 | 20 |
| 1003 | 齐雷 | 分析员 | 1011 | 1991-02-20 | 1900 | 300 | 10 |
| 1005 | 王海涛 | 推销员 | 1011 | 1991-02-22 | 2450 | 600 | 10 |
| 1007 | 刘苍松 | 经理 | 1017 | 1991-04-02 | 3675 | 700 | 20 |
| 1009 | 张慎政 | 推销员 | 1011 | 1991-09-28 | 1250 | 1400 | 10 |
| 1011 | 陈子枢 | 经理 | 1017 | 1991-05-01 | 3450 | 400 | 10 |
| 1013 | 张久军 | 办事员 | 1011 | 1991-06-09 | 1250 | 800 | 10 |
| 1015 | 程祖红 | 分析员 | 1007 | 1997-04-19 | 3000 | 1000 | 20 |
| 1017 | 韩少云 | 董事长 | NULL | 1991-11-17 | 5000 | NULL | NULL |
| 1019 | 刘沛霞 | 推销员 | 1011 | 1991-09-08 | 1500 | 500 | 10 |
| 1021 | 范传奇 | 办事员 | 1007 | 1997-05-23 | 1100 | 1000 | 20 |
| 1023 | 赵栋 | 经理 | 1017 | 1991-12-03 | 950 | NULL | 30 |
| 1025 | 朴乾 | 分析员 | 1023 | 1991-12-03 | 3000 | 600 | 30 |
| 1027 | 叶尚青 | 办事员 | 1023 | 1992-01-23 | 1300 | 400 | 30 |
±-----±-------±-------±------±-----------±-----±------±--------+
23、列出所有员工的入职日期,由新到旧排列员工信息,显示姓名、总薪资
select name 姓名, sal + ifnull(0, bonus) 总薪资 from emp order by hdate desc;
±-------±-------+
| 姓名 | 总薪资 |
±-------±-------+
| 范传奇 | 1100 |
| 程祖红 | 3000 |
| 叶尚青 | 1300 |
| 朴乾 | 3000 |
| 赵栋 | 950 |
| 韩少云 | 5000 |
| 张慎政 | 1250 |
| 刘沛霞 | 1500 |
| 张久军 | 1250 |
| 陈子枢 | 3450 |
| 刘苍松 | 3675 |
| 王海涛 | 2450 |
| 齐雷 | 1900 |
| 王克晶 | 800 |
±-------±-------+
24、若把hdate看作员工的出生日期,查询下个月过生日的所有员工,显示员工姓名和出生日期
select name 姓名, hdate 出生日期 from emp where (month(now()) + 1) % 12 = month(hdate) % 12;
±-------±-----------+
| 姓名 | 出生日期 |
±-------±-----------+
| 韩少云 | 1991-11-17 |
±-------±-----------+
25、求1997年入职的员工信息。
select * from emp where year(hdate) = 1997;
±-----±-------±-------±------±-----------±-----±------±--------+
| id | name | job | topid | hdate | sal | bonus | dept_id |
±-----±-------±-------±------±-----------±-----±------±--------+
| 1015 | 程祖红 | 分析员 | 1007 | 1997-04-19 | 3000 | 1000 | 20 |
| 1021 | 范传奇 | 办事员 | 1007 | 1997-05-23 | 1100 | 1000 | 20 |
±-----±-------±-------±------±-----------±-----±------±--------+
26、求emp表中薪资最高的前3名员工的信息,显示姓名和薪资
select name 姓名, sal 薪资 from emp order by sal desc limit 0 , 3 ;
±-------±-----+
| 姓名 | 薪资 |
±-------±-----+
| 韩少云 | 5000 |
| 刘苍松 | 3675 |
| 陈子枢 | 3450 |
±-------±-----+
三、子查询、多表查询
!!下列练习使用db40库中数据,选择db40库,如果没有先创建(参考sql脚本)
±-----±-------±-------±------±-----------±-----±------±--------+
| id | name | job | topid | hdate | sal | bonus | dept_id |
±-----±-------±-------±------±-----------±-----±------±--------+
| 1001 | 王克晶 | 办事员 | 1007 | 1990-12-17 | 800 | 500 | 20 |
| 1003 | 齐雷 | 分析员 | 1011 | 1991-02-20 | 1900 | 300 | 10 |
| 1005 | 王海涛 | 推销员 | 1011 | 1991-02-22 | 2450 | 600 | 10 |
| 1007 | 刘苍松 | 经理 | 1017 | 1991-04-02 | 3675 | 700 | 20 |
| 1009 | 张慎政 | 推销员 | 1011 | 1991-09-28 | 1250 | 1400 | 10 |
| 1011 | 陈子枢 | 经理 | 1017 | 1991-05-01 | 3450 | 400 | 10 |
| 1013 | 张久军 | 办事员 | 1011 | 1991-06-09 | 1250 | 800 | 10 |
| 1015 | 程祖红 | 分析员 | 1007 | 1997-04-19 | 3000 | 1000 | 20 |
| 1017 | 韩少云 | 董事长 | NULL | 1991-11-17 | 5000 | NULL | NULL |
| 1019 | 刘沛霞 | 推销员 | 1011 | 1991-09-08 | 1500 | 500 | 10 |
| 1021 | 范传奇 | 办事员 | 1007 | 1997-05-23 | 1100 | 1000 | 20 |
| 1023 | 赵栋 | 经理 | 1017 | 1991-12-03 | 950 | NULL | 30 |
| 1025 | 朴乾 | 分析员 | 1023 | 1991-12-03 | 3000 | 600 | 30 |
| 1027 | 叶尚青 | 办事员 | 1023 | 1992-01-23 | 1300 | 400 | 30 |
±-----±-------±-------±------±-----------±-----±------±--------+
±—±---------±-----+
| id | name | loc |
±—±---------±-----+
| 10 | 培优部 | 北京 |
| 20 | 就业部 | 上海 |
| 30 | 大数据部 | 广州 |
| 40 | 销售部 | 深圳 |
±—±---------±-----+
27、(子查询)列出emp表中高于平均工资的所有员工,显示姓名、薪资
select name 姓名, sal 薪资 from emp where sal > (select avg(sal) from emp);
±-------±-----+
| 姓名 | 薪资 |
±-------±-----+
| 王海涛 | 2450 |
| 刘苍松 | 3675 |
| 陈子枢 | 3450 |
| 程祖红 | 3000 |
| 韩少云 | 5000 |
| 朴乾 | 3000 |
±-------±-----+
28、(子查询)查询emp表中比’齐雷’薪资高的所有员工,显示姓名、薪资
select name 姓名, sal 薪资 from emp where sal > (select sal from emp where name = '齐雷');
±-------±-----+
| 姓名 | 薪资 |
±-------±-----+
| 王海涛 | 2450 |
| 刘苍松 | 3675 |
| 陈子枢 | 3450 |
| 程祖红 | 3000 |
| 韩少云 | 5000 |
| 朴乾 | 3000 |
±-------±-----+
29、(子查询)查询emp表中和’齐雷’从事相同职位的所有员工,显示姓名、职位
select name 姓名, job 职位 from emp where job = (select job from emp where name = '齐雷');
±-------±-------+
| 姓名 | 职位 |
±-------±-------+
| 齐雷 | 分析员 |
| 程祖红 | 分析员 |
| 朴乾 | 分析员 |
±-------±-------+
30、(子查询)查询emp表中’陈子枢’所有下属员工,假设不知道陈子枢的编号(1011)
– 查询陈子枢的编号
– 查询上级编号为 1011 的员工
select * from emp where topid = (select id from emp where name = '陈子枢');
±-----±-------±-------±------±-----------±-----±------±--------+
| id | name | job | topid | hdate | sal | bonus | dept_id |
±-----±-------±-------±------±-----------±-----±------±--------+
| 1003 | 齐雷 | 分析员 | 1011 | 1991-02-20 | 1900 | 300 | 10 |
| 1005 | 王海涛 | 推销员 | 1011 | 1991-02-22 | 2450 | 600 | 10 |
| 1009 | 张慎政 | 推销员 | 1011 | 1991-09-28 | 1250 | 1400 | 10 |
| 1013 | 张久军 | 办事员 | 1011 | 1991-06-09 | 1250 | 800 | 10 |
| 1019 | 刘沛霞 | 推销员 | 1011 | 1991-09-08 | 1500 | 500 | 10 |
±-----±-------±-------±------±-----------±-----±------±--------+
31、列出所有员工和员工对应的部门,如果员工没有对应的部门, 显示为null
(左外连接查询)
select dept.name 部门, emp.name 员工 from dept left join emp on dept.id = emp.dept_id;
(右外连接查询)
select dept.name 部门, emp.name 员工 from emp right join dept on dept.id = emp.dept_id;
32、(关联查询)列出在’就业部’任职的员工,假定不知道’就业部’的部门编号,显示部门名称和员工姓名
select dept.name 部门, emp.name 员工 from dept left join emp on dept.id = emp.dept_id where (select id from dept where name = '就业部') = emp.dept_id;
源文件: