1、表的修改复制(了解)
2、查询表-拼接操作（重要)
表的修改等操作
1、修改表名
alter table 表名 rename 新表名
2、增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first; 将字段直接添加在最前面
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;
3、删除字段
alter table 表名 drop 字段名;
4、修改字段
alter table 表名 modify 字段名 字段类型(宽度) 约束条件；
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
2、表的查询拼接---重要
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
);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘egon‘,‘male‘,18,‘20170301‘,‘老男孩驻沙河办事处外交大使‘,7300.33,401,1), #以下是教学部
(‘alex‘,‘male‘,78,‘20150302‘,‘teacher‘,1000000.31,401,1),
(‘wupeiqi‘,‘male‘,81,‘20130305‘,‘teacher‘,8300,401,1),
(‘yuanhao‘,‘male‘,73,‘20140701‘,‘teacher‘,3500,401,1),
(‘liwenzhou‘,‘male‘,28,‘20121101‘,‘teacher‘,2100,401,1),
(‘jingliyang‘,‘female‘,18,‘20110211‘,‘teacher‘,9000,401,1),
(‘jinxin‘,‘male‘,18,‘19000301‘,‘teacher‘,30000,401,1),
(‘成龙‘,‘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)
;
select * from emp\G; # \G可以单独展示每行数据
3）group分组应用练习题
1、按照部门分组
select * from emp group by post;
# 遇到了问题 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘day03.emp.id‘ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
5.7 中需要如下修改
set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;
分组之后 最下可操作单位应该是租 还不再是组内的单个数据
set global sql_mode=‘ONLY_FULL_GROUP_BY‘; 再设置回去
5.7 严格模式下 分组只能拿到分组的依据
select post from emp group by post;
按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助一些方法
什么时候需要分组？ 每个、最高、最低、平均
# 1、获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as ‘部门‘,max(salary) as ‘最高薪资‘ from emp group by post;
as 可以不写 省略，不推荐 as 也可以起别名
2、获取每个部门最低薪资
select post,min(salary) from emp group by post;
3、获取每个部门平均薪资
select post,avg(salary) from emp group by post;
4、获取每个部门工资总和
select post,sum(salary) from emp group by post;
5、获取每个部门人数
select post,count(id) from emp group by post; salary age也可以
6、查询分组之后每个部门名称和每个部门下所有员工姓名
# group_concat 不单单可以支持获取分组之后的其他字段值，还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,‘_DSB‘) from emp group by post; 拼接
select post,group_concat(name,‘:‘,salary) from emp group by post;
# concat 不分组的时候用
select concat(‘NAME:‘,name),concat(‘SAL:‘,salary) from emp;
# 补充 as 语法不单单可以给字段起别名 还可以给表起别名
select emp.id,emp.name from emp;
select emp.id,emp.name from emp as t1; 当前有效
7、查询每个人的年薪
select name,salary*12 from emp;
分组注意事项
# 关键字 where 和group by 同时出现的时候group by必须在where的后面
where 先对整体数据进行过滤之后再分组操作
聚合函数只能在分组之后使用
where 筛选不能使用聚合函数
select id,name,age from emp where max(salary) > 3000; 报错
select max(salary) from emp;
# 统计各部门年龄在30以上的员工平均薪资
先求所有年龄大于30
select * from emp where age>30;
再对结果进行分组 不是严格模式可用 5.6
select * from emp where age>30 group by post;
两步和一
select post,avg(salary) from emp where age>30 group by post;
4）having分组之后的筛选条件
having的语法 和 where是一致的
只不过having是在分组之后进行的过滤操作
having是可以直接使用聚合函数的
# 统计各部门年龄在30以上的员工平均工资 并且保留 平均薪资大于10000的部门
select post,avg(salary) from emp
where age>30
group by post
having avg(salary)>10000
;
5）distinct 去重
一定要注意，必须是完全一样的数据才可以去重 id 不一样也不行
所以 带id 不能去重 要去掉
select distinct age from emp;
6）order by 排序 默认升序 后面asc可以不写
select * from emp order by salary;
select * from emp order by salary desc; # 降序
select * from emp order by salary desc,salery asc;
# 先按照salary降序排 如果salary相同再按照age升序拍
# 同合计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000 的部门 然后平均薪资降序
select post,avg(salary) from emp
where age>10
group by post
having avg(salary)>1000
order by avg(salary) desc
;
7）limit 限制条数
select * from emp;
# 针对数据过多的情况 我们通常都是分页处理
select * from emp limit 3; # 3个一页
select * from emp limit 0,5; # 第一个参数是起始位
select * from emp limit 5,5; # 第二个参数是展示位
8）正则
select * from emp where name regexp ‘^j.*(n|y)$‘;
9）多表查询
drop table emp; # 删除之前的emp
create table dep(
id int,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum(‘male‘,‘female‘) not null default ‘male‘,
age int,
dep_id int
);
#插入数据
insert into dep values
(200,‘技术‘),
(201,‘人力资源‘),
(202,‘销售‘),
(203,‘运营‘);
insert into emp(name,sex,age,dep_id) values
(‘egon‘,‘male‘,18,200),
(‘alex‘,‘female‘,48,201),
(‘wupeiqi‘,‘male‘,38,201),
(‘yuanhao‘,‘female‘,28,202),
(‘liwenzhou‘,‘male‘,18,200),
(‘jingliyang‘,‘female‘,18,204)
;
select * from dep,emp; # 结果 笛卡尔积 了解
select * from emp,dep where emp.dep_id = dep.id; # 拼表 和内连接一样
# MySQL开设对应的方法
inner join # 内连接
select * from emp inner join dep on emp.dep_id = dep.id;
# 只拼接 两张表公有的数据部分
left join # 左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左表所有的数据都展示出来 没有对应的项就用NULL
right join # 右链接
# select * from emp right join dep on emp.dep_id = dep.id;
# 右表所有的数据都展示出来 没有对应的项就用NULL
union # 全连接
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
10）子查询 分步解决
分步骤解决问题
# 1、查询部分是技术或者人力资源的员工信息
先获取部门的id号
再去员工表里面筛选出对应的员工
select id from dep where name=‘技术‘ or name=‘人力资源‘;
# 表的查询结果可以作为其他表的查询条件
# 也可以通过起别名的方式把它作为一个虚拟表
10）知识点补充
# 查询平均年龄在25岁以上的部门名称
# 联表
select dep.name from emp inner join dep
on emp.dep_id = dep.id
group by dep.name
having avg(age)>25
;
# 子查询
select name from dep where id in
(select dep_id from emp group by dep_id
having avg(age)>25);
# any all in
any (sql查找结果) 可加判断
in (结果)
# 大于平均年龄的人
select * from emp where age>any(
select avg(age) from emp group by dep_id);
11）exist 存在
select * from dep
where exists (
select * from emp where emp.dep_id = dep.id
);
# 只返回布尔值
‘‘‘
返回True 外层查询语句执行
返回False的时候外层查询语句不在执行