06、单表查询
一、单表查询的语法
select 字段1,字段2,... from 表名
where 条件
group by field
having 筛选
order by feild
limit 限制条数
二、关键字的执行优先级（重点）
# 重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
regexp 正则表达式
# 解释
1.找到表: from
2.拿到 where 指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组 group by ，如果没有 group by ，则整体为一组
4.将分组的结果进行 having 过滤
5.执行 select
6.去重
7.将结果按条件排序: order by
8.限制结果的显示条数
三、简单查询
1、单表查询准备
create table emp(
id int primary key 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);
2、查询关键字
select
控制查询表中的哪些字段对应的数据
from
控制查询的表
3、查询关键字之where
where字句中可以使用：
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like '%条件%'
pattern可以是%或_，
% 表示任意多字符
_ 表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
# where其实就是对数据进行筛选
# 1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <= 6; # 比较运算符查询
select * from emp where id between 3 and 6; # 关键字: between and 查询
# 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); # 关键字 in 查询
"""
模糊查询
关键字 like
关键符号
%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
"""
# 3.查询员工姓名中包含o字母的员工姓名和薪资
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; # 比较运算符
# 5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6; # not 为非
# 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 is NULL;
select name,post from emp where post_comment is not NULL;
4、查询关键字之group by分组
分组
按照某个指定的条件将单个单个的数据分为一个个整体
应用场景
eg:
求每个部门的平均薪资
求每个国家的人均GDP
求男女平均薪资
如何对数据进行分组
关键字 group by 条件
"""
分组之后不再以单个个体为研究对象 也无法直接再获取单个个体的数据
研究对象应该是分组的整体
分组之后默认只能直接获取到分组的依据 其他字段数据无法直接获取
如果需要实现上述要求 还是修改sql_mode
set global sql_mode='only_full_group_by';
"""
# 首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
# 小窍门：‘每’这个字后面的字段，就是我们分组的依据
1.获取每个部门的最大薪资
select post,max(salary) from emp group by post;
2.统计每个部门的人数
select post,count(id) from emp group by post;
3.获取每个部门的员工姓名
select post,group_concat(name,'|',salary) from emp group by post;
# group_concat用于分组之后获取分组以外的字段数据并支持拼接
# concat用于分组之前的拼接操作
select id,concat(name,'|',salary) as '嘿嘿' from emp;
# concat_ws当多个字段连接符相同的情况下推荐使用
select id,concat_ws('|',name,sex,salary,age) from emp;
"""
在查看结果的时候可以给字段起别名
select post as '部门',max(salary) as '最高薪资' from emp group by post;
as可以省略但是为了语义更加明确建议不要省略
"""
5、聚合函数
max()
min()
sum()
count()
avg()
# 上述聚合函数都是在分组之后使用 用于操作整体数据
1. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post;
2. 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;
3. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
4. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;
5. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;
6. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;
6、查询关键字之having过滤
where与having都是用来筛选数据的
但是where用于分组之前的筛选、having用于分组之后的筛选
为了人为的区分开 我们将where用筛选来形容 having用过滤来形容
# 执行优先级从高到低：where > group by > having
# Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
# Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
# 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
mysql> select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
"""
将一个复杂的查询题拆分成多个简单的小题
"""
7、查询关键字之distinct去重
""" 去重的前提示是存在一模一样的数据 如果存在主键肯定无法去重 """ # 对有重复的展示数据进行去重操作 一定要是重复的数据 select distinct id,age from emp; select distinct post from emp;
8、查询关键字之order by排序
order by默认是升序 默认的关键字是asc # ascend
select * from emp order by salary asc;
也可以改为降序 desc # descend
select * from emp order by salary desc;
# order by排序支持多个字段组合(第一个不行 就往后继续排)
select * from emp order by age,salary;
select * from emp order by age asc,salary desc;
9、查询关键字之 limit 限制查询
# 工资降序排序，并限制前三位的工资 select * from emp order by salary desc limit 3; #默认初始位置为0 select * from emp order by salary desc limit 3,3; #从第3开始，即先查询出第4条，然后包含这一条在内往后查3条
10、使用正则表达式 regexp 查询
select * from emp where name regexp '^j.*(n|y)$';
11、exists 是否存在
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
而是返回一个真假值，True或False。
当返回True时，外层查询语句将进行查询
当返回值为False时，外层查询语句不进行查询。
select * from emp
where exists
(select id from emp where id > 3);
select * from emp
where exists
(select id from emp where id > 250);
补充：操作表的SQL语句补充
# 1.修改表名称
alter table t1 rename t2;
rename table t1 to t123; # 关键字修改
rename table t2 to t20,
t3 to t30,
t4 to t40;
# 2.添加表字段
alter table t9 add pwd int;
alter table t9 add hobby varchar(32) after age;
alter table t9 add nid int first;
# 3.删除表字段
alter table t9 drop nid;
# 4.修改字段名和字段类型
alter table t9 modify age tinyint; # modify只能修改字段类型
alter table t9 change pwd password int; # change字段名和类型
复制表(了解)
# 查询语句执行的结果也是一张表，可以看成虚拟表 # 复制表结构＋记录 （key不会复制: 主键、外键和索引） create table new_service select * from service; # 只拷贝表结构(不包含键) create table new1_service select * from service where 1=2; # 拷贝结构包含各种key create table t4 like employees;
