操作表的sql补充、表关键字查询以及多表查询思路
操作表的sql语句补充
- 修改表名
alter table 表名 rename 新表名;
- 新增字段
alter table 表名 add 字段名 字段类型(数字) 约束条件;
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已经存在的字段;
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
- 修改字段
alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;
- 删除字段
alter table 表名 drop 字段名;
表关键字
数据准备
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
('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);
查询关键字之select与from
sql语句的关键字编写顺序和执行顺序是不一致的
select name from emp;
该sql语句的执行顺序是：先通过from确定表，之后执行select确定字段
在编写sql语句针对select和from可以先写个固定模版
select * from 表名 其他操作
select 后的字段可能是确定的，也可能是通过sql动态产生的，所以可以先用*占位最后再修改
select 自定义查询表中字段对应的数据 from 指定操作对象(到底是哪张表，也可能是多张)
查询关键字之where筛选
通过emp表，创建一下查询数据
- 查询id大于3小于等于6的数据
select * from emp where id between 3 and 6;
- 查询薪资是20000或者18000或者17000的数据
select * from emp where salary in(17000,18000,20000);
- 查询员工中包含o字母的员工姓名和薪资
select name,salary from emp where name like '%o%';
- 查询员工姓名是由四个字符组成的员工姓名和薪资
select name,salary from emp where name like '____';
- 查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
- 查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (17000,18000,20000);
- 查询岗位描述为空的员工名与岗位名，针对null不能用等号，只能用is
select name,post from emp where post_comment is null;
查询关键字之group by分组
分组，就是按照一些指定的条件将单个单个的数据分为一个个整体
分组之后我们研究的对象应该是以组为单位，不应该再直接获取单个数据项，如果获取了应该直接报错，select后面填写的字段名只能是分组的依据(其他字段需要借助一些方法才可以获取)
set global sql_mode ='strict_trans_tables,only_full_group_by'; -- only_full_group_by
我们可以将emp表的部门分组
select post from emp group by post;
我们写上去了是否需要分组，可以在需求中得到答案：每个、平均、最大、最小
配合分组常见使用的有聚合函数
max -- 最大值
min -- 最小值
sum -- 总和
count -- 计数
avg -- 平均
配合聚合函数使用分组的sql
- 获取每个部门的最高工资
select post,max(salary) from emp group by post;
- 每个部门的最低工资
select post,min(salary) from emp group by post;
- 每个部门的平均工资
select post,min(salary) from emp group by post;
- 每个部门的工资总和
select post,sum(salary) from emp group by post;
- 每个部门的人数
select post,count(id) from emp group by post;
- 查询分组之后的部门名称和每个部门下所有的学生姓名(group_concat用法)
select post,group_concat(name) from emp group by post;
group_concat(分组之后用)，不仅可以用来显示除分组之外字段还有拼接字符串的作用
select post,group_concat(name,'666') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
查询关键字之having过滤
where与having的功能其实是一样的，都是用来筛选数据
只不过where用于分组之前的筛选，而having用于分组之后的筛选，为了人为的区分，所以叫where是筛选，having是过滤
'统计各部门年龄在30岁以上的员工平均工资，并且平均工资大于10000的部门'\
-- where 用在分组之前，having用在分组之后
select post,avg(salary) from emp where age>30 group by post having avg(salary) >10000;
查询关键字之order by排序
select * from emp order by salary ; -- 默认是升序 asc
select * from emp order by salary desc -- 降序
-- order by 后面两个参数情况
select * from emp order by age,salary; -- 先按年龄升序排序，年龄相同按薪资升序排序
'统计各部门年龄在10岁以上的员工平均工资，并保留平均工资大于1000的部门，然后对平均工资进行排序'
select post,avg(salary) from emp where age>10 group by post
having avg(salary)>1000
order by avg(salary);
查询关键字之limit分页
1. 限制展示条数
select * form emp limit 2;
2.查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
3.分页显示
select * from emp limit 0,10; -- 第一个参数表示起始位置，第二个参数表示的是条数
查询关键字之regexp正则
select * from emp where name regexp '^s.*(k)$'; -- 查询 name以s开头，k结尾的数据
多表查询思路
子查询：将一张表的查询结果括号括起来当作另外一条sql语句的条件
连表操作：先将所有涉及到结果的表全部拼接到一起形成一张大表，然后从大表中查询数据
后续数据准备
#建表
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);
子查询
1. 查询jason的部门名称
select dept_id from emp1 where name='jason'
2.根据部门编号获取部门名称
select name from dep1 where id =(select dep_id from emp1 where name='jason');
连表操作
当进行连表操作的时候如果，不加任何条件就会出现笛卡尔积现象。
select * from emp1,dep1; -- 这时候数据量就是两表数据量的乘积
连表操作可以分为一下四种
- 内连接
inner join -- 只拼接两边都有的字段数据
select * from emp1 inner join dep1 on emp1.dep_id = dep1.id;
- 左连接
left join -- 以左表为基准，展示所有的数据，没有对应则NULL填充
select * from emp1 left join dep1 on emp1.dep_id =dep1.id;
- 右连接
right join -- 以右表为基准，展示所有的数据没有对应则NULL填充
select * from emp1 right join dep1 on emp1.dep_id =dep1.id;
- 全连接
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;
作业
添加salary薪资字段，添加好数据
alter table emp1 add salary decimal(10,2) after age;
update emp1 set salary=10000 where id=1;
update emp1 set salary=12222 where id=2;
update emp1 set salary=3500 where id=3;
update emp1 set salary=4800 where id=4;
update emp1 set salary=26000 where id=5;
update emp1 set salary=15000 where id=6;
-- 1. 查询岗位名以及岗位包含的所有名字员工
select d.name,group_concat(e.name)from emp1 e right join dep1 d on e.dep_id = d.id group by d.name;
-- 2.查询岗位名以及哥岗位内包含的员工个数
select d.name,count(e.id)from emp1 e right join dep1 d on e.dep_id = d.id group by d.name;
-- 3.查询公司内男员工和女员工的个数
select gender,count(id) from emp1 group by gender;
-- 4.查询岗位名以及各岗位的平均薪资
select d.name,avg(e.salary) from emp1 e right join dep1 d on e.dep_id=d.id group by d.name
-- 5.查询岗位名以及各岗位的最高薪资
select d.name,max(e.salary) from emp1 e right join dep1 d on e.dep_id=d.id group by d.name
-- 6.查询岗位名以及各岗位的最低薪资
select d.name,min(e.salary) from emp1 e right join dep1 d on e.dep_id=d.id group by d.name
-- 7.查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select gender,avg(emp1.salary) from emp1 group by gender;
-- 8.统计各部门年龄在30岁以上的员工平均工资
select d.name,avg(salary) from emp1 e right join dep1 d on e.dep_id=d.id and age>30 group by d.name;
-- 9.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select d.name,avg(e.salary) from emp1 e right join dep1 d on e.dep_id= d.id and age>10
group by d.name
having avg(e.salary)>1000
order by avg(e.salary);
