文章目录
一、SQL操作表的语句补充
修改表名
alter table 表名 rename 新表名;
新增字段
alter table 表名 add 字段名 字段类型(数字) 约束条件;
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已经存在的字段; # 再创建之后添加
alter table 表名 add 字段名 字段类型(数字) 约束条件 first; # 在第一个添加
修改字段
alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;
删除字段
alter table 表名 drop 字段名;
二、表查询关键字Select与from
SQL语句的关键字编写顺序与执行顺序是不一致的~
eg: select name from emp;
肯定是先获取到表emp再去执行select想要的内容name
编写SQL语句针对select和from有一个固定模版
select * from 表名 其他操作
select后面的字段可能是实际的字段 也可以是通过SQL动态产生的 所以*占位再修改
select 自定义查询表中对应的数据 from指定操作的对象(来自哪一张表 可以是多张表)
like 模糊查询
% 匹配任意个数的任意字符
_ 匹配单个个数的任意字符
创建一个大数量的表格
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
('Like','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('Lebron','male',78,'20150302','teacher',1000000.31,401,1),
('James','male',81,'20130305','teacher',8300,401,1),
('live','male',73,'20140701','teacher',3500,401,1),
('love','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);
三、关键字Where筛选
1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
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字母的员工姓名和薪资
select * from emp where name like '%o%';
select name,salary from emp where name like '%o%'; # like 模糊查询 %什么都相当于贪婪匹配
4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like'_ _ _ _';
select name,salary from char_length(name) = 4;
5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
select * from emp where id 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; # 获得name post
select * from emp where post_comment is NULL; # 获得全部数据
四、关键字Group by分组
分组: 按照一些指定的条件单个单个的数据分为一个个整体
select post from emp group by post;
分组之后我们研究的对象应该是一组为单位 不应该直接获取单个数据
如果获取了应该直接报错
select后面可以直接填写的字段名只能是分组的依据(其他字段需要借助于一些方法才可以获取)
set global sql_mode='strict_trans_tables,only_full_group_by';
我们写SQL是否需要使用分组 可以在题目中得到答案
需要使用有聚合函数 max(最大值)、min(最小值)、sum(总和)、count(技术)、avg(平均)
1.获取每个部门的最高工资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高工资' from emp group by post;
# 为了辨识 在显示的时候还可以给字段去别名 用到as 看自己的需求
- 获取每个部门的最低工资
select post,min(salary) from emp group by post;
- 获取每个部门的平均工资
select post,avg(salary) from emp group by post;
- 获取每个部门的平均工资
select post,sum(salary) from emp group by post;
- 获取每个部门的人数
select post,count(id) from emp group by post;
6.查询分组之后的部门名称和每个部门下所有的学生姓名
select post,group_concat(name) from emp group by post;
select post,group_concat(name,"_SB") from emp group by post;
select post,group_concat(name,": ",salary) from emp group by post;
select post,group_concat(salary) from emp group by post;
五、关键字Having过滤
where与having的功能其实是一样的 都是用来筛选数据
只不过where用于分组之前的筛选 而 having用于分组之后的筛选
为了人为的区分 所以叫where是筛选 having是过滤
统计各部门年龄在30岁以上的员工平均工资,并且保留平均工资大于10000的部门
select post,avg(salary) from emp where age>=30 group by post;
select post,avg(salary) from emp where age>=30 group by post having avg(salary)>10000;
六、关键字distinct去重
去重见名知意 但是去重的前提是数据必须一摸一样
select distinct age from emp;
七、关键字 Order by排序
asc 默认升序 desc 降序
select * from emp order by salary asc; # 升序
select * from emp order by salary desc; # 降序
select * from emp order by age desc; # 降序
按照age降序排,在年轻相同的情况下再按照薪资升序排
select * from emp order by age desc,salary asc; # 不同的先按照年龄往下排 如果相同就升序排
统计各部门年龄在10岁以上的员工平均工资,并且保留平均工资大于1000的部门,然后对平均工资进行排序
select post,avg(salary)from emp where age>10 group by post; # 先拿到数据
select post,avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by avg(salary); # 再进行过滤
八、关键字Limit分页
限制展示条数
select * from emp limit 7;
查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
分页展示 从第五条开始展示 总共展示十条
select * from emp limit 5,10; # 第一个参数表示起始位置 第二个表示展示条数 不是索引
九、关键字Regexp正则
select * from emp where name regexp '^j.*'; # 前面标准格式 后面引号内正则表达式
十、多表查询思路
子查询
将一张表的查询结果用括号括起来 当做另外一条SQL语句的条件跟在后面
eg: 类似于日常生活中的解决问题的方式
第一步做什么 第二步在第一步的基础上做操作
连表操作
先将所有涉及到结果的表全部拼接到一起形成一张大表 然后从大表中查询想要的数据
#建表
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);
获取Jason的部门名称
正常流程 先获取Jason的部门编号 再根据部门编号查找部门名称
select dep_id from emp1 where name='jason'; # 200
select name from dep1 where id = 200; # 技术
子查询
select name from dep1 where id = (select dep_id from emp1 where name = 'jason');
十一、连表操作
我们正常的想法就是将两个表一起倒入就好了 但是我们看一下结果
select * from emp1,dep1;
一个数据有五个条 这是因为中间产生了笛卡尔积现象
会讲几个表里面的数据全部对一遍 对多少遍取决于表里面的内容
我们不会使用笛卡尔积来求数据 效率太低 连表有专门的语法
select * from emp1,dep1 where emp1.dep_id = dep1.id;
select * from emp1,dep1 where dep1.id = emp1.dep_id;
以上的方法推荐不要使用 应该使用表专门的连接方式总共分为四种
inner join 内连接
只拼接两边都有的字段数据
left join 左连接
以左表为基准 展示所有的数据 没有对应则NULL填充
right join 右连接
以右表为基准 展示所有的数据 没有对应则NULL填充
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;
技术小白记录学习过程,有错误或不解的地方请指出,如果这篇文章对你有所帮助请
点点赞收藏+关注
谢谢支持 !!!