MySQL——SQL语句查询关键字
一、SQL语句查询关键字汇总
select 查询
from 指定表
where 成立条件
group by 分组
having 对分组经行过滤
distinct 不重复的
order by desc/asc 排序
limit 显示数据个数
regexp 正则表达
二、多表查询的两种方式
子查询
连表操作
三、前期数据准备
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
gender 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,gender,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);
四、编写SQL语句的小技巧
针对select后面的字段名可以先用*占位往后写 最后再回来修改
在实际应用中select后面很少直接写* 因为*表示所有 当表中字段和数据都特别多的情况下非常浪费数据库资源
"""
SQL语句的编写类似于代码的编写 不是一蹴而就的 也需要反反复复的修修补补
"""
"""
在MySQL中也有很多内置方法 我们可以通过查看帮助手册学习
help 方法名
"""
五、select（指定需要查询的字段信息）
select # 指定需要查询的字段信息
select * # 查询所有字段
select name # 查询name字段
select char_length(name) # 支持对字段做处理
六、from （指定需要查询的表信息）
from #（指定需要查询的表信息）
from mysql.emp
from emp
SQL语句中关键字的执行顺序和编写顺序并不是一致的，核能会错乱
eg：
select id,name from emp;
我们先写的select在from之前，但是执行的时候，先执行from后执行select
七、where （筛选成立条件）
# 1、查询id大于等于3小于等于6的数据
select * 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、查询id小于3大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;
# 4、查询员工姓名中包含字母o的员工姓名与薪资
'''
模糊查询：条件不够精确的查询
关键字：like
通配符：% (匹配任意个数的任意字符)
eg：
%o%
o jason owen loo wwoww
%o
o asdasdo asdo
_:匹配单个个数的任意字符
_o_
aox wob iok
o_
oi ok ol
'''
select * from emp where name like %o%;
# 5、查询员工姓名是由四个字符组成的员工姓名与薪资
select * from emp where name like '____';
select * from emp where char_length(name) = 4;
# 6、查询岗位描述为空的员工名和岗位名
select * from emp where post_comment is NULL;
八、group by 分组 及聚合函数
'''
分组:按照指定的条件将单个单个的数据组成一个个整体
eg:
将班级学生按照性别分组
将全国人民按照民族分组
将全世界的人按照肤色分组
分组的目的是为了更好的统计相关数据
eg:
每个班级的男女比例
每个民族的总占比
每个部门的平均薪资
聚合函数：
专门用于分组之后的数据统计
max\min\sum\avg\count
最大值、最小值、求和、平均值、计数
当出现关键字 每个、平均、最大、最小、求和的时候，一般需要我们进行分组
'''
# 1、将员工数据按照部门分组
select * from emp group by post;
'''
MySQL5.6默认不会报错
set global sql_mode='strict_trans_tables,only_full_group_by'
MySQL5.7及8.0默认都会直接报错
原因是分组之后 select后面默认只能直接填写分组的依据 不能再写其他字段
select post from emp group by post;
select age from emp group by age;
分组之后默认的最小单位就应该是组 而不应该再是组内的单个数据单个字段
'''
# 2、获取每个部门的最高工资
select post,max(sarlary) from emp group by post;
'''
对于sql语句执行之后的结果，我们可以通过as 修改字段名称，as可以省略
'''
select post as '部门',max(salary) as '最高薪资' from emp group by post;
# 3、一次获取部门薪资相关统计
select post,max(salary) '最高薪资',min(salary) '最低薪资',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
# 4、统计每个部门的人数
select post,count(id) from emp group by post;
# 5、统计每个部门的部门名称以及部门下的员工姓名
'''
分组以外的字段无法直接填写，需要借助方法
'''
select post,name from emp group by post;
select post,group_concat(name) from emp group by post;
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;
select post,group_concat(name,'_NB') from emp group by post;
select post,group_concat('Hello',name,'SB') from emp group by post;
九、having过滤
'''
having与where本质是一样的，都是用来做数据的筛选
区别：
where 用在分组之前 （首次筛选）
having 用在分组之后 (二次筛选)
'''
# 1、统计各部门年龄在30岁以上的员工平均工资并且保留大于10000的数据
'''
操作些许复杂，不熟练的前提下，可以进行分步操作
'''
# 1.1 先筛选出所有年龄大于30岁的员工数据
select * from emp where age > 30;
# 1.2 筛选出的数据按照部门分组并统计平均薪资
select post,avg(salary) from emp where age > 30 group by post;
# 1.3 针对分组统计之后的结果做二次筛选
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
十、distinct去重
'''
去重就去去除相同的数据（想要去重就必须要有一模一样的数据）
'''
# 关键字针对的是多个字段组合的结果
select distinct id,age from emp;
select distinct age from emp;
select distinct age,post from emp;
十一、order by 排序
# 可以是单个字段排序 默认情况下是升序
select * from emp order by age;
# asc 升序 desc 降序
select * from emp order by age desc;
# 可以多个字段排序
# 先按照年龄升序排序，相同的情况下再按照薪资的降序排序
select * from emp order by age,salary desc;
# 统计各部门年龄再10岁以上的员工平均工资，并保留平均工资大于1000的部门，然后对平均工资经行排序
# 1、先筛选所有年龄大于10岁的员工
select * from emp where age > 10;
# 2、对他们按照部门分组并统计平均薪资
select post,avg(salary) from emp where age > 10 group by post;
# 3、针对分组的结果做二次筛选
select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000;
# 4、最后按照指定的字段经行排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);
"""
当一条SQL语句中很多地方都需要使用聚合函数计算之后的结果 我们可以节省操作(主要是节省了底层运行效率 代码看不出来)
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;
"""
十二、limit分页
'''
当表中的数据当表中数据特别多的情况下 我们很少会一次性获取所有的数据
很多网站也是做了分页处理 一次性只能看一点点
'''
# 限制展示的条数
select * from emp limit 5;
# 从第5条开始向后读取5条
select * from emp limit 5,5;
# 查询工资最好的人的详细信息
select * from emp order by salary desc limit 1;
十三、regexp正则表达式
'''
SQL语句的模糊匹配如果用不习惯，也可以使用正则批量查询
'''
select * from emp where name regexp '^j.*?(n/y)$';
十四、多表查询的思路
# 表数据准备
create table dep(
id int primary key auto_increment,
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,'运营'),
(205,'财务');
insert into emp(name,sex,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);
# 将两张表中所有的数据对应一遍
select * from emp,dep;
'''
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
'''
# 将多张表合并到一起然后一次性获取更多的数据
select * from emp,dep where emp.dep_id=dep.ip;
浙公网安备 33010602011771号