操作表的SQL语句补充
1.修改表名
alter table 表名 rename 新表名;
2.新增字段
alter table 表名 add 字段名 字段类型(数字) 约束条件;
# 添加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已存在的字段;
# 在已存在的字段后面增加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
# 在最上面增加字段
3.修改字段
alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;
# 修改字段的类型
4.删除字段
alter table 表名 drop 字段名;
表查询
表查询关键字
1.先准备好表的数据
create table emp(id int 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
# 以下是教学部
('barry','male',18,'20170301','flash',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语句的关键字编写顺序与执行顺序是不一致的
eg：select name from emp;
先执行from确定表，在执行select确定字段
'''
select 自定义查询表中字段对应的数据
from 指定操作的对象(到底是哪张表，也可能是多张)
查询关键字之where筛选
1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
# MySQL支持逻辑运算符
select * from emp where id between 3 and 6;
# between是在什么之间
2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);
# 简写
3.查询员工姓名中包含o字母的员工姓名和薪资
'''
like 模糊查询
%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
'''
select name,salary from emp where name like '%o%';
4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
# char_length 统计字段的长度
5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);
7.查询岗位描述为空的员工名与岗位名(针对null不能用等号，只能用is)
select name,post from emp where post_comment=NULL;
select name,post from emp where post_comment is NULL;
查询关键字之group by分组
1.分组
分组按照指定条件将单个单个数据分为一个个整体
'''
分组之后我们操作对象应该以组为单位，不能在直接获取单个数据，如果直接获取数据应该报错，select后面可以直接填写的字段名只能是分组的依据(其他字段需要借助运行方法才可以获取)
set global sql_mode='strict_trans_tables,only_full_group_by';
如果在操作表数据数据不符合表的字段或表的约束条件的要求直接报错
分组以后只能拿分组的依据
'''
select post from emp group by post;
'''
每个、平均、最大、最小
当出现这些需求是就需要使用分组
分组常见的聚合函数
max 最大值
min 最小值
sum 总和
count 计数
avg 平均
'''
2.获取每个部门的最高工资
#
select post,max(salary) from emp group by post;
3.每个部门的最低工资
select post,min(salary) from emp group by post;
4.每个部门的平均工资
select post,avg(salary) from emp group by post;
5.每个部门的工资总和
select post,sum(salary) from emp group by post;
6.每个部门的人数
select post,count(id) from emp group by post;
7.查询分组之后的部门名称和每个部门下所有的学生姓名
# group_concat 分组之后可以用来显示除分组外字段还有拼接字符串的作用
select post,group_concat(name) from emp group by post;
select post,group_concat(salary) from emp group by post;
select post,group_concat(name,":",salary) from emp group by post;
查询关键字之having过滤
1.having和where的功能其实是一样的，都是用来筛选数据，只是where是用于分组之前的筛选，而having是用于分组之后的筛选
'为了好区分，所以叫where筛选，叫having过滤'
2.统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp where age >= 30
group by post having avg(salary) > 10000;
查询关键字之distinct去重
去重的前提是数据必须一样
select age from emp; # 像这样数据必须是一样的才可以去重
select distinct age from emp;
查询关键字之order by排序
1.将年龄默认升序排列
select * from emp order by age asc;
2.将年龄默认降序排列
select * from emp order by age desc;
3.先按照age降序排，在年纪相同的情况下再按照薪资升序排
select * from emp order by age desc,salary asc;
查询关键字之limit分页
'limit可以限制展示的条数'
1.限制展示条数
select * from emp limit 3;
2.查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
3.分页显示
select * from emp limit 0,5;
# 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;
查询关键字之regexp正则
select * from emp where name regexp 'j.*(n|y)$';
'''
正则表达式
.匹配除了换行符以外的任意字符
*重复零次或更多次(默认尽可能多)
|匹配管道符|左侧的或右侧的数据
$匹配字符串的结束
'''
多表查询
多表查询思路
1.子查询
将一张表的查询结果用括号括起来当做另一条SQL语句的条件
2.连表操作
先将所有涉及到结果的表全部拼接到一起形成一张大表，然后在从大表中查询数据
3.先准备好表和数据
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
('barry','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
子查询
查询barry的部门名称
1.先获取barry的部门编号
select dep_id from emp1 where name = 'barry';
# 200
2.根据部门编号获取部门名称
select name from dep1 where id = 200;
2.1子查询
select name from dep1 where id = (select dep_id from emp1 where name = 'barry');
连表操作
select * from emp1,dep1; # 笛卡尔积
'一般是不会使用笛卡尔积的，效率太低了'
1.内连接
inner join 只拼接两边都有的字段数据
select * from emp1 inner join dep1 on emp1.dep_id = dep1.id;
2.左连接
left join 以左表为基准，没有对应则NULL填充
select * from emp1 left join dep1 on emp1.dep_id = dep1.id;
3.右连接
right join 以右表为基准，没有对应则NULL填充
select * from emp1 right join dep1 on emp1.dep_id = dep1.id;
4.全连接
union 展示左右两边所有的数据，没有对应则NULL填充
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;
作业
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,sum(id) from emp group by post;
3. 查询公司内男员工和女员工的个数
select sex,sum(id) from emp group by sex;
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;
8. 统计各部门年龄在30岁以上的员工平均工资
select post,avg(salary) from emp where age > 30 group by post;
9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age >10 group by post having avg(salary) > 1000;
浙公网安备 33010602011771号