一、操作表的SQL语句补充
'1.修改表名'
alter table 旧表名 rename 新表名;
alter table t1 rename t2;
' 2.新增字段'
alter table t1 add 字段名 字段类型(数字) 约束条件;
alter table t1 add name varchar(32); # 约束条件可写可不写
'在某个字段下面添加字段'
alter table 表名add 字段名 字段类型(数字) 约束条件 after 一存在的字段名
alter table t1 add age int after name;
'在表格的开头添加字段'
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
alter table t1 add nid int first;
' 3.修改字段'
alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;
alter table t1 change age gender varchar(32); # 把age字段改为了gender字段
'修改字段中的字段类型'
alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;
alter table t1 modity gender varchar(64); # 把gender的字段类型修改为了 varchar(64);
' 4.删除字段'
alter table 表名 drop 字段名;
alter table t1 drop gender; # 把t1表中的gender字段给删除了
二、表查询关键字
'先准备数据'
'1.创建表'
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
);
'2.插入数据'
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);
# 得到下列数据
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
'根据这个表格学习查询关键字'
三、查询关键字之select与from
'
SQL的关键字编写顺序与执行顺序是不一致的!!!
eg:
select * from t1;
肯定是先执行from找到表t1然后在执行select找到select后面的要查找的字段
编写SQL语句针对select和form可以先编写一个固定模板
select * from 表名 其他操作;
select后面的字段可能是实际的 也有可能是SQL动态产生的 所以先用*占位最后在修改
'
select
自定义查询表中字段对应的数据
from
指定操作的对象(到底是哪张表 也有可能是多张)
四、查询关键字之where筛选
'我们可以通过做题目来看where的用法
1.查询id大于等于3小于等于6的数据'
select id,name from emp where id>=3 and id<=6;
select id,name from emp where between 3 and 6;
'两种方法都可以 where 其实就是根据where后面的条件来筛选数据'
+----+-------+
| id | name |
+----+-------+
| 3 | kevin |
| 4 | tony |
| 5 | owen |
| 6 | jack |
+----+-------+
' 2.查询薪资是20000或者18000或者17000的数据'
select name,salary from emp where salary=20000 or salary=18000 or salary=17000;
select name,salary from emp where salary in (20000,18000,17000); '简写 select 可以产看所有的数据用*即可'
+-----------+----------+
| name | salary |
+-----------+----------+
| 程咬金 | 20000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
+-----------+----------+
'3.查询员工姓名中包含o字母的员工姓名和薪资'
'想这种包含什么什么的叫做 模糊查询有点像正则 需要用到关键字和特殊符号
关键字:like
% : 匹配任意个数的任意字符 就是只要写了%那你不管写啥都会匹配
_ : 匹配单个个数的任意字符 只匹配单个
'
select name,salary form emp where name like '%o%'; '只要有o你不管有多少个字符都会匹配'
+-------+------------+
| name | salary |
+-------+------------+
| jason | 7300.33 |
| tom | 1000000.31 |
| tony | 3500.00 |
| owen | 2100.00 |
+-------+------------+
'4.查询员工姓名是由四个字符组成的员工姓名与其薪资'
select name,salary from emp where name like '____'; '就是四个下划线'
select name,salary from emp where char_length(name) = 4; '跟python的len方法一样'
+------+----------+
| name | salary |
+------+----------+
| tony | 3500.00 |
| owen | 2100.00 |
| jack | 9000.00 |
| sank | 10000.00 |
+------+----------+
'5.查询id小于3或者大于6的数据'
select * from emp where id<3 or id>6;
select * from emp where not between 3 and 6; '可以使用not取反'
'7.查询岗位描述为空的员工名与岗位名'
'当要查询为空的字段的时候不能用等于号只能使用is'
select * from emp where post_comment =Null; '报错'
select * from emp where post_comment is Null; '必须使用 is 关键字'
五、查询关键字之group by分组
分组: 就是按照指定的条件将一个一个的数据分为一个个整体
select * from emp group by post;
'意思就是emp表按照post分组查询所有
分组之后我们研究的对象应该是以组为单位 不应该再直接获取单个数据项 如果获取了应该直接报错 select后面可以直接填写的字段名只能是分组的依据(其他字段需要借助于一些方法才可以获取) 我们修改配置即可
set global sql_mode='strict_trans_tables,only_full_group_by';
'
select post from emp group by post; '按照post分组就只能获取post 其他字段使用其他办法获取'
'我们在编写SQL语句是否需要分组 其实在题目中就能知道
eg: 每个、平均、最大、最小
分组还可以搭配聚合函数使用
'
1.常见的聚合函数
2.代码实现分组
'获取每个部门的最高工资
以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
每个部门的最高工资'
select post,max(salary) from emp group by post; '就是emp表按照post分组 然后聚合函数会计算出那个是最大值'
+-----------------------------+-------------+
| post | max(salary) |
+-----------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
'我们在分组的时候我们还可以把字段名重命名'
select post as '部门',max(salary) as '最高工资' from emp group by post;
+-----------------------------+--------------+
| 部门 | 最高工资 |
+-----------------------------+--------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+--------------+
'2 每个部门的最低工资'
select post,min(salary) from emp group by post;
'3 每个部门的平均工资'
select post as '部门',avg(salary) as '平均工资' from emp group by post;
+-----------------------------+---------------+
| 部门 | 平均工资 |
+-----------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 浦东第一帅形象代言 | 7300.330000 |
+-----------------------------+---------------+
'4 每个部门的工资总和'
select post,sum(salary) from emp group by post;
'5 每个部门的人数'
select post,count(id) from emp group by post; '其实计算人数的时候随便用那个字段计算都可以 但是还是使用不会重复的最好 所以使用id计算'
'6 查询部门的名称 和每个部门下的所有员工姓名
group_concat(分组之后用) 不仅可以用来显示除分组外字段还有拼接字符串的作用'
select post,group_concat(name) from emp group by post;
+-----------------------------+------------------------------------------------+
| post | group_concat(name) |
+-----------------------------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙 |
| sale | 拉拉,乐乐,西西,呵呵,哈哈 |
| teacher | sank,jenny,jack,owen,tony,kevin,tom |
| 浦东第一帅形象代言 | jason |
+-----------------------------+------------------------------------------------+
'还可以拼接字符串'
select post,group_concat(name,": ",salary) from emp group by post;
+-----------------------------+---------------------------------------------------------------------------------------------------------+
| post | group_concat(name,": ",salary) |
+-----------------------------+---------------------------------------------------------------------------------------------------------+
| operation | 程咬铁: 17000.00,程咬铜: 18000.00,程咬银: 19000.00,程咬金: 20000.00,僧龙: 10000.13 |
| sale | 拉拉: 4000.33,乐乐: 3000.29,西西: 1000.37,呵呵: 2000.35,哈哈: 3000.13 |
| teacher | sank: 10000.00,jenny: 30000.00,jack: 9000.00,owen: 2100.00,tony: 3500.00,kevin: 8300.00,tom: 1000000.31 |
| 浦东第一帅形象代言 | jason: 7300.33 |
+-----------------------------+---------------------------------------------------------------------------------------------------------+
六、查询关键字之having过滤
'having其实跟where是一样的都是用来筛选数据的
只不过where是分组之前筛选 having是分组之后筛选
所以为了人为区分 所以叫where是筛选 having是过滤'
'1.统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
我们可以先把年龄大于30员工筛选出来然后在按照部门分组在用having过滤拼接工资大于10000的数据
'
select post,avg(salary) from emp where age>=30 group by post having avg(salary) > 10000;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
'where要在分组前筛选 having要在分组后使用'
七、查询关键字之distinct去重
'就是根据条件把字段中的数据去重'
select distinct id,age from emp;
+----+-----+
| id | age |
+----+-----+
| 1 | 18 |
| 2 | 78 |
| 3 | 81 |
| 4 | 73 |
| 5 | 28 |
| 6 | 18 |
| 7 | 18 |
| 8 | 48 |
| 9 | 48 |
| 10 | 38 |
| 11 | 18 |
| 12 | 18 |
| 13 | 28 |
| 14 | 28 |
| 15 | 18 |
| 16 | 18 |
| 17 | 18 |
| 18 | 18 |
+----+-----+
'向上方因为id是唯一字段所以id是不看呢过重复所以不能去重'
'去重的前提是数据必须要一模一样'
select distinct age from emp;
+-----+
| age |
+-----+
| 18 |
| 78 |
| 81 |
| 73 |
| 28 |
| 48 |
| 38 |
+-----+
八、查询关键字之order by排序
'MySQL默认排序是升序'
select salary from emp order by salary;
+------------+
| salary |
+------------+
| 1000.37 |
| 2000.35 |
| 2100.00 |
| 3000.13 |
| 3000.29 |
| 3500.00 |
| 4000.33 |
| 7300.33 |
| 8300.00 |
| 9000.00 |
| 10000.00 |
| 10000.13 |
| 17000.00 |
| 18000.00 |
| 19000.00 |
| 20000.00 |
| 30000.00 |
| 1000000.31 |
+------------+
select salary from emp order by salary asc; 'asc是升序可以不写'
seelct salary from emp order by salary desc; 'desc是降序必须写'
两种可以混合使用
select * from emp order by age asc,salary desc; '就是先按照年龄升序 遇到相同的年龄 再按照薪资降序排序'
'1.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序'
select post,avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by avg(salary);
+-----------------------------+---------------+
| post | avg(salary) |
+-----------------------------+---------------+
| sale | 2600.294000 |
| 浦东第一帅形象代言 | 7300.330000 |
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------------------------+---------------+
九、查询关键字之limit分页
'就是限制展示条数'
select * from emp limit 3;
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
'只显示前三条数据'
'1 查询工资最高的人的详细信息
因为是查看的是详细信息 所以分组就不能用了 所以我们可以先降序然后在值展示一条数据即可'
select * from emp order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
'limit还可以写两个参数'
select * from emp limit 0,5; '第一个参数是起始位置,第二个是展示几条 不是索引位置'
select * from emp limit 5,3;
+----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+----------+--------+-----------+
'就是从第六条开始展示总共三条数据'
十、查询关键字之regexp正则
'跟我们之前所讲的正则是一样的 都是把一些特殊符号组织起来产生特殊含义然后去字符串中筛选出符合条件的数据'
select * from emp where name regexp '^j.*(n|y)$';
'就是在name字段中筛选出符合该正则表达式的数据
该正则表达式的意思是 j开头中间不管是什么 然后n或则是y结尾都可以'
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
十一、多表查询
'
多表查询有两种:
1.子查询
就是将一张表的查询结果括号括起来给另外一条SQL语句当条件
eg:类似日常生活中解决问题的方式
第一步干什么
第二步基于第一步的结果在操作
2.链表查询
就是把所有设计到结果的表全部拼接成一张大表 然后在从大表中查询数据'
'建表'
create table dep1(
id int primary key auto_increment,
name varchar(20)
);
create table emp1(
id int primary key auto_increment,
name varchar(20),
gender enum('male','female') not null default 'male',
age int,
dep_id int
);
'插入数据'
insert into dep1 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保')
;
insert into emp1(name,gender,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
emp1:
+----+--------+--------+------+--------+
| id | name | gender | age | dep_id |
+----+--------+--------+------+--------+
| 1 | jason | male | 18 | 200 |
| 2 | dragon | female | 48 | 201 |
| 3 | kevin | male | 18 | 201 |
| 4 | nick | male | 28 | 202 |
| 5 | owen | male | 18 | 203 |
| 6 | jerry | female | 18 | 204 |
+----+--------+--------+------+--------+
dep1:
+-----+--------------+
| id | name |
+-----+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
| 205 | 安保 |
+-----+--------------+
'现在两个表是没有任何关系的 因为两证表都没有外键联系'
1.子查询
'1 查询jason的部门名称
那么我们可以先在emp1表中拿到jason的部门id号先'
select dep_id from emp1 where name='jason';
'根据查询的结果可得知jason的部门id号是200 然后我们可以拿着这个200去部门表中查询部门名字'
select name from dep1 where id = 200;
'这样我们就可以知道jason的部门是技术'
'但是这是我们人眼看到的 怎么把这两个SQL语句和在一起呢?'
select name from dep1 where id = (select dep_id from emp1 where name='jason');
+--------+
| name |
+--------+
| 技术 |
+--------+
2.链表操作
当我们直接把两张表连起来时 会出小问题
select * from emp1,dep1; '笛卡尔积'
'链接的时候一个员工数据就会对应四个部门数据 都会显示出来 这样是没有意义的'
'我们是不会用笛卡尔积来求数据的 效率太低 链表有专门的关键字 '
inner join 内链接
只拼接两边都有的字段
select * from emp1 inner join dep1 on emp1.dep_id=dep1.id;
'就是按照emp1的dep_id和dep1的id字段相等的拼接起来 没有的就不会显示'
+----+--------+--------+------+--------+-----+--------------+
| id | name | gender | age | dep_id | id | name |
+----+--------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
+----+--------+--------+------+--------+-----+--------------+
left join 左链接
以左表为基准 显示所有的数据 没有就用NULL顶替 '就是关键字在left join 的左右'
select * from emp1 left join dep1 on emp1.dep_id = dep1.id;
+----+--------+--------+------+--------+------+--------------+
| id | name | gender | age | dep_id | id | name |
+----+--------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
+----+--------+--------+------+--------+------+--------------+
right join 右链接
以右表为基准 显示所有的数据 没有就用NULL顶替
select * from emp1 right join dep1 on emp1.dep_id = dep1.id;
+------+--------+--------+------+--------+-----+--------------+
| id | name | gender | age | dep_id | id | name |
+------+--------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| NULL | NULL | NULL | NULL | NULL | 205 | 安保 |
+------+--------+--------+------+--------+-----+--------------+
union 全链接
就是显示所有
select * from emp1 left join dep1 on emp1.dep_id = dep1.id
union
select * from emp1 right join dep1 on emp1.dep_id = dep1.id;
+------+--------+--------+------+--------+------+--------------+
| id | name | gender | age | dep_id | id | name |
+------+--------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 205 | 安保 |
+------+--------+--------+------+--------+------+--------------+
'现在我们使用链表操作查找jason的部门
我们先把两张表链接起来 在操作'
select emp1.name,dep1.name from emp1 inner join dep1 on emp1.dep_id = dep1.id where emp1.name = 'jason';
+-------+--------+
| name | name |
+-------+--------+
| jason | 技术 |
+-------+--------+
'因为有时候两个字那个表链接起来的时候有可能字段名会重复 所以我们在查找的时候尽量使用 表名.字段名 的形式'
十二、作业
一、完成下列练习题
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
+-----------------------------+------------------------------------------------+
| post | group_concat(name) |
+-----------------------------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙 |
| sale | 拉拉,乐乐,西西,呵呵,哈哈 |
| teacher | sank,jenny,jack,owen,tony,kevin,tom |
| 浦东第一帅形象代言 | jason |
+-----------------------------+------------------------------------------------+
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post;
+-----------------------------+-----------+
| post | count(id) |
+-----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 浦东第一帅形象代言 | 1 |
+-----------------------------+-----------+
3. 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;
+--------+-----------+
| sex | count(id) |
+--------+-----------+
| male | 10 |
| female | 8 |
+--------+-----------+
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
+-----------------------------+---------------+
| post | avg(salary) |
+-----------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 浦东第一帅形象代言 | 7300.330000 |
+-----------------------------+---------------+
5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;
+-----------------------------+-------------+
| post | max(salary) |
+-----------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;
+-----------------------------+-------------+
| post | min(salary) |
+-----------------------------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;
+--------+---------------+
| sex | avg(salary) |
+--------+---------------+
| male | 110920.077000 |
| female | 7250.183750 |
+--------+---------------+
8. 统计各部门年龄在30岁以上的员工平均工资
select post,avg(salary) from emp where age>30 group by post;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| sale | 2500.240000 |
| teacher | 255450.077500 |
+---------+---------------+
9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary);
+-----------------------------+---------------+
| post | avg(salary) |
+-----------------------------+---------------+
| sale | 2600.294000 |
| 浦东第一帅形象代言 | 7300.330000 |
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------------------------+---------------+
