1.整理今日内容
2.完成下列分组查询练习题(以课上建表代码为参考)
mysql> create table emp( -> id int not null unique auto_increment, -> name varchar(20) not null, -> sex enum('male','female') not null default 'male', #大部分是男的 -> age int(3) unsigned not null default 28, -> hire_date date not null, -> post varchar(50), -> post_comment varchar(100), -> salary double(15,2), -> office int, #一个部门一个屋子 -> depart_id int -> ); Query OK, 0 rows affected (0.00 sec) mysql> insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values -> ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部 -> ('tom','male',78,'20150302','teacher',1000000.31,401,1), -> ('kevin','male',81,'20130305','teacher',8300,401,1), -> ('tony','male',73,'20140701','teacher',3500,401,1), -> ('owen','male',28,'20121101','teacher',2100,401,1), -> ('jack','female',18,'20110211','teacher',9000,401,1), -> ('jenny','male',18,'19000301','teacher',30000,401,1), -> ('sank','male',48,'20101111','teacher',10000,401,1), -> ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 -> ('呵呵','female',38,'20101101','sale',2000.35,402,2), -> ('西西','female',18,'20110312','sale',1000.37,402,2), -> ('乐乐','female',18,'20160513','sale',3000.29,402,2), -> ('拉拉','female',28,'20170127','sale',4000.33,402,2), -> ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 -> ('程咬金','male',18,'19970312','operation',20000,403,3), -> ('程咬银','female',18,'20130311','operation',19000,403,3), -> ('程咬铜','male',18,'20150411','operation',18000,403,3), -> ('程咬铁','female',18,'20140512','operation',17000,403,3); Query OK, 18 rows affected (0.00 sec) Records: 18 Duplicates: 0 Warnings: 0 mysql>
1. 查询岗位名以及岗位包含的所有员工名字
mysql> select post as "部门名称",group_concat(name) as "员工" from emp group by post; +-----------------------------+------------------------------------------------+ | 部门名称 | 员工 | +-----------------------------+------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙 | | sale | 拉拉,乐乐,西西,呵呵,哈哈 | | teacher | sank,jenny,jack,owen,tony,kevin,tom | | 张江第一帅形象代言 | jason | +-----------------------------+------------------------------------------------+ 4 rows in set (0.00 sec) mysql>
2. 查询岗位名以及各岗位内包含的员工个数
mysql> select post as "部门名称",count(id) as "员工人数" from emp group by post; +-----------------------------+--------------+ | 部门名称 | 员工人数 | +-----------------------------+--------------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 张江第一帅形象代言 | 1 | +-----------------------------+--------------+ 4 rows in set (0.00 sec) mysql>
3. 查询公司内男员工和女员工的个数
mysql> select sex as "性别",count(id) as "人数" from emp group by sex; +--------+--------+ | 性别 | 人数 | +--------+--------+ | male
4. 查询岗位名以及各岗位的平均薪资
mysql> select post as "岗位",avg(salary) as "平均薪资" from emp group by post; +-----------------------------+---------------+ | 岗位 | 平均薪资 | +-----------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 张江第一帅形象代言 | 7300.330000 | +-----------------------------+---------------+ 4 rows in set (0.00 sec) mysql>
5. 查询岗位名以及各岗位的最高薪资
mysql> select post as "岗位",max(salary) as "最高薪资" from emp group by post; +-----------------------------+--------------+ | 岗位 | 最高薪资 | +-----------------------------+--------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 张江第一帅形象代言 | 7300.33 | +-----------------------------+--------------+ 4 rows in set (0.00 sec) mysql>
6. 查询岗位名以及各岗位的最低薪资
mysql> select post as "岗位",min(salary) as "最高薪资" from emp group by post; +-----------------------------+--------------+ | 岗位 | 最高薪资 | +-----------------------------+--------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | 张江第一帅形象代言 | 7300.33 | +-----------------------------+--------------+ 4 rows in set (0.00 sec) mysql>
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
mysql> select sex as "性别",avg(salary) as "平均薪资" from emp group by sex; +--------+---------------+ | 性别 | 平均薪资 | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+ 2 rows in set (0.00 sec) mysql>
3.练习拼表操作并理解其意义
4.理解子查询思路体会其意义