单表查询

单表查询

一. 完整语法

语法级别关键字的排列顺序:

select distinct(去重) 字段1,字段2,字段3,... from 库名.表名(相当于完整的文件路径,有打开这个文件)
where 约束条件(在整个文件过滤筛选)
group by 分组依据(分成不同的组)
having 过滤条件(在分好的组中过滤筛选)
order by 排序的字段
limit 限制显示的条数
;

必须要有的关键字:

​ select 字段 from 表名;

二. 关键字的执行优先级(重点)

Copyfrom     # 1. 找到表:from

where    # 2. 拿着where指定的约束条件，去文件/表中取出一条条记录  

group by # 3. 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

having   # 4. 将分组的结果进行having过滤

select   # 5. 执行select

distinct # 6. 去重

order by # 7. 将结果按条件排序：order by

limit    # 8. 限制结果的显示条数

python伪代码表示运行的优先级

Copydef from():
	pass
	
def where():
	pass
	
def group_by():
	pass
	
def having():
	pass
	
def distinct():
	pass
	
def order_by():
	pass
	
def limit():
	pass
	
def select():
	f = from()
	res = where(f)
	res1 = group_by(res)
	res2 = having(res1)
	res3 = distinct(res2)
	res4 = order_by(res3)
	limit(res4)

三. 准备表

Copycompany.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int
Copy# 提示: 如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk(个别比较老的windos电脑可能会出现这样的问题)

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);

四. 条件约束: where

1.概念

where字句中可以使用:

1. between num1 and num2 值在num1到num2之间包括num1和num2

2. in(num1,num2,num3...) 值为num1或者num2或者num3...

3. like ""

   like "字段"

   "_"表示匹配单个字符

   like "部分字段_"

   "%"表示匹配单个或者无穷个字符

   like "部分字段%"

   1. 逻辑运算符:and,or,not

2. 练习

查询岗位描述为空的员工姓名和岗位名(易错题) --> is null & null
select name,post from emp where post_comment is null;
select name,post from emp where post_comment is not null;-- 查询id大于等于3小于等于6的数据  --> 比较运算符和between关键字
select * from emp where id>=3 and id <=6;
select * from emp where id between 3 and 6;

-- 查询薪资是20000 或者 18000 或者 17000 的数据 --> in & 逻辑运算符or
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);

-- 查询员工姓名中包含字母o的员工的姓名和薪资 --> 模糊运算like
select name,salary from emp where name like "%o%";

-- 查询员工姓名是由四个字符组成的姓名和薪资 --> 模糊运算__ & char_length()
select name,salary from emp where name like "____";
select name,salary from emp where char_length(name)=4;

-- 查询id小于3或者id大于6的数据 --> 比较运算符 & between
select * from emp where id < 3 or id > 6;
select * from emp where id not between 3 and 6;

-- 查询薪资不在20000，18000，17000范围的数据 --> not + in
select * from emp where salary not in (20000,18000,17000);

-- 查询岗位描述为空的员工姓名和岗位名(易错题) --> is null & null
select name,post from emp where post_comment = null;  # 查出为空表,这里要注意比较null时不能用=而是用is
select name,post from emp where post_comment is null;
select name,post from emp where post_comment is not null;

-- 查看岗位是teacher的员工姓名、年龄
select name,age from emp where post='teacher';

-- 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select name,age from emp where post='teacher' and age>30;

-- 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
select name,age,salary from emp where post='teacher' and salary between 9000 and 10000;
select name,age,salary from emp where post='teacher' and salary >=9000 and salary<=10000;

-- 查看岗位描述不为NULL的员工信息
select * from emp where post_comment is not null;

-- 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from emp where post='teacher' and salary=10000 or salary=9000 or salary=30000;
select name,age,salary from emp where post='teacher' and salary in (10000,9000,30000);

-- 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from emp where post='teacher' and salary!=10000 and salary!=9000 and salary!=30000;
select name,age,salary from emp where post='teacher' and salary not in (10000,9000,30000);

-- 查看岗位是teacher且名字是j开头的员工姓名、年薪
select name,salary from emp where id='teacher' and name like "j%";

五. 分组查询: group by

强调: 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义. 只有多条记录之间的某个字段值相同时，就可以使用该字段用来作为分组的依据

1. 操作

Copy# 分组实际应用场景分组应用场景非常的多例如:
"""
男女比例  部门平均薪资  部门秃头  国家之间数据统计
"""
# 注意1: 分组之后最小可操作单位应该是组, 而不是组内的单个数据. 
# 注意2: 如果没有设置分组严格模式的时候是可正常执行, 返回的是分组之后每个组的第一条数据. 如果设置了分组严格模会直接报错. 没有设置分组严格模式的返回值是不符合分组的规范的, 分组之后不应该考虑单个数据, 而应该以组为操作单位. (补充: 分组之后, 只能查到分组的字段以及组内多条记录聚合的成果)


# 按照部门分组
select * from emp group by post;  # 注意: 没有设置分组严格模式, 这里会拿到每一个部门中的第一条数据


# 保留严格模式+追加分组严格模式(注意: 分组严格模式只和only_full_group_by有关, 严格模式的指定只是为了减轻mysql服务器存取字段时超出的压力, 保证数据的存储的严格性)
set global sql_mode='strict_trans_tables,only_full_group_by'; 


# 分组默认只能拿到分组的依据. 按照什么分组就只能拿到什么组. 其他字段不能直接获取, 需要借助于一些方法(聚合函数).
select post from emp group by post;  


"""
什么时候需要分组啊???
   关键字: 每个  平均  最高  最低
"""
# 获取每个部门的最高薪资 --> 聚合函数max() + 取别名as
select post,max(salary) as max_salary from emp group by post;


# 获取每个部门的最低薪资 --> 聚合函数min()
select post,min(salary) from emp group by post;


# 获取每个部门的平均薪资 --> 聚合函数avg() 
select post,avg(salary)  from emp group by post;


# 获取每个部门的工资总和 --> 聚合函数sum()
select post,sum(salary) from emp group by post;


# 获取每个部门的人数 --> 聚合函数count()
select post,count(id) from emp group by post;  # count最好存放可以标识唯一性的字段(注意: 最好不要放null关键字指定的字段)
select post,count(post_comment) from emp group by post; 


# 查询分组之后的部门名称和每个部门下所有的员工姓名 --> 聚合函数group_concat() & group_concat()的拼接操作
# group_concat不单单可以支持你获取分组之后的其他字段值, 还支持拼接操作.
select post,group_concat(name) from emp group by post;  # group_concat获取分组之后的字段中的值
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;


# 补充1: 定义显示格式. 不分组的时候用 concat() 或者 concat_ws() 或者 case语句
"""
concat()    函数用于连接字符串
concat_ws("定义统一字段分割符号", 字段1, 字段2)  开头定义多字段间自动分隔符
    注意: 一个concat或concat_ws只能表示一个字段, 如果把所有字段放到里面拼接显示格式, 就会显示成一个字段中的一列内容
case语句伪代码
    case
    when 条件1 then
        结果
    when 条件2 then
        结果
    else
        结果
    end
"""
select concat('姓名:', name),concat('年龄:', age) from emp;
select concat(name, ':', age, ':', sex) from emp;
select concat_ws(':', name, age, sex) from emp;  # 对于上面一条语句重复了多次的`:`, 还是使用下面的这种.
select
    (
        case
        when name='egon' then
            name
        when name='alex' then
            concat(name, '_bigsb')
        else
            concat(name, 'sb')
        end
    ) as new_name
from emp;    



# 补充2: as语法不单单可以给字段起别名还可以给表起别名, 且as语句可以省略, 但是不推荐省略, 不省略可以让语义更加的明确.
select * from emp as t1;
select * from emp t1;  # as不推荐省略
select emp.id,emp.name from emp;
select emp.id,emp.name from emp as t1;  # 报错: 因为先执行from语句,执行完了from语句当前的表名被改为了t1, 接着select语句执行, select语句无法识别emp.id


# 补充3: 查询每个人的年薪12薪
select name,salary*12 from emp;
select id, name*12 from emp;  # 注意: 字符串类型不支持乘除


"""综合练习where+group by"""
# 统计各部门年龄在30岁以上的员工平均薪资
# 1. 先求所有年龄大于30岁的员工
select * from emp where age>30;

# 2. 再对结果进行分组
select * from emp where age>30 group by post;

# 3. 得出最终结果
select post,avg(salary),group_concat(name, ':', age) from emp where age>30 group by post;

2. 分组注意事项

Copy<1> 关键字where和group by同时出现的时候group by必须在where的后面
<2> where先对整体数据进行过滤之后再分组操作
<3> 聚合函数只能在分组之后使用
    select id,name,age from emp where max(salary) > 3000;  # 错误: ERROR 1111 (HY000): Invalid use of group function(无效使用组函数)
    select max(salary) from emp;  # 可以: 不分组默认整体就是一组

3. 总结

Copy# 聚合函数
"""
max() as 别名   # as可以给字段起别名, 也可以直接省略不写, 但是不推荐. 因为使用as的话可能语意不明确易错乱. 也可以对表取别名.
min()
sum()
avg()  avg --> average
count()  # count最好存放可以标识唯一性的字段(注意: 最好不要放null关键字指定的字段)
    
group_concat()  # 不仅获取分组后的其它字段们, 还可以进行拼接操作.
concat() # 不分组时使用, 可以对查询出来的字段进行额外的拼接操作
concat_ws("定义统一字段分割符号", 字段1, 字段2)  开头定义多字段间自动分隔符
    注意: 一个concat或concat_ws只能表示一个字段, 如果把所有字段放到里面拼接显示格式, 就会显示成一个字段中的一列内容
case语句伪代码
    case
    when 条件1 then
        结果
    when 条件2 then
        结果
    else
        结果
    end
    
salary * 12  # 支持算符运算, 但是不能争对字符串
"""

# 当前语句执行优先级
from -> where -> group by -> select

# 实际语句执行优先级
from -> where -> group by -> having -> select -> distinct -> order by -> limit

4.小练习

查询公司内男员工和女员工的个数
select count(id) from emp group by gender;-- 查出所有员工的名字，薪资,格式为: <名字:xxx>    <薪资:xxx>
select concat("<名字:",name,">") as "名字",concat("<薪资:",salary,">") as "薪资" from emp;

-- 查出所有的岗位（去掉重复）
select post from emp group by post;
select distinct post from emp;

-- 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
select name,salary*12 as annual_year from emp;

-- 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) as names from emp group by post;

-- 查询岗位名以及各岗位内包含的员工个数
select post,count(name) as c from emp group by post; 

-- 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;

-- 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;

-- 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;

-- 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;

-- 查询男员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;

六. 过滤: having

where是在分组之前的过滤,即在分组之前做了一次整体性的筛选

having是分组之后的过滤,即在分组之后专门针对聚合的结果进行进一步的筛选

1. 操作

Copy# 前提: 分组之后的筛选条件
"""
having的语法和where是一致的, 只不过having是在分组之后进行的过滤操作, 即having是可以直接使用聚合函数的.
"""
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp 
    where age>30 
    group by post
    having avg(salary) > 10000; 
    
select post,avg(salary),group_concat(name, ':', age) from emp 
    where age>30 
    group by post 
    having avg(salary)>10000; 
    
    
# 当前语句的执行优先级
from -> where -> group by -> having -> select

# 实际语句执行优先级
from -> where -> group by -> having -> select -> distinct -> order by -> limit

2 小练习

-- 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(name) from emp group by post having count(name)<2;

-- 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from emp group by post having avg(salary)>10000;

-- 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from emp group by post having avg(salary) between 10000 and 20000;

七. 去重: distinct

Copy# 一定要注意必须是完全一样的数据才可以去重!!! 所以去重的时候，一定要注意去除主键, 因为主键是部位空且唯一的.
"""
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]

拓展: ORM框架的对象关系映射让不懂SQL语句的人也能够非常牛逼的操作数据库
    表          -- 映射成-->  类型
    一条条的数据 -- 映射成-->  对象
    字段对应的值 -- 映射成-->  对象的属性

实现原理: 
你在写类就意味着在创建表
你用类生成对象就意味着在创建数据
你对象点属性就是在获取数据字段对应的值
目的: 就是减轻python程序员的压力只需要会python面向对象的知识点就可以操作MySQL
"""
select distinct id,age from emp;  # 注意: 去除被设置成主键的id
select distinct age from emp;


# 当前语句的执行优先级
from -> select -> distinct

# 实际语句的执行优先级
from -> where -> group by -> having -> select -> distinct -> order by -> limit

八. 查询排序: order by

1. 操作

Copy"""
order by默认升序.  
	默认升序: 后面可以指定asc,可以省略不写
	指定降序: desc
	指定多种, 前者相等则按照后者的顺序: age desc,salary asc;
"""
select * from emp order by salary;       # 升序
select * from emp order by salary asc;   # 升序
select * from emp order by salary desc;  # 降序

# 先按照age降序排, 如果碰到age相同, 则再按照salary升序排.
select * from emp order by age desc,salary; 
select * from emp order by age desc,salary asc;  


# 统计各部门年龄在10岁以上的员工平均工资, 并且保留平均薪资大于1000的部门, 然后对平均工资降序排序
select post,avg(salary) from emp 
    where age>10 
    group by post 
    having avg(salary) > 10000
    order by avg(salary) desc;  
    
select post,avg(salary) as avg_salary,group_concat(name, ':', age) from emp 
    where age>10 
    group by post 
    having avg(salary)>10000 
    order by avg_salary desc; # 注意: 这里为什么可以使用avg_salary, 是因为执行语句的优先级select的优先级高于avg_salary. 当select语句执行完毕avg(salary)被改名成了avg(salary), 所以接着执行order by语句就可以拿到avg_salary去进行排序.
  
  
  
# 当前语句的执行优先级
from -> where -> group by -> having -> select -> order by

# 实际语句的执行优先级
from -> where -> group by -> having -> select -> distinct -> order by -> limit

2. 小练习

Copy# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from emp order by age asc,hire_date desc;

# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) as avg_salary from emp group by post having avg(salary)>10000 order by avg_salary asc;

# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) as avg_salary from emp group by post having avg(salary)>10000 order by avg_salary desc;

九. 限制查询的记录数: limit

1. 操作

Copy"""
目的: 针对数据过多的情况我们通常都是做分页处理
limit 5,5: 
    第一个参数是起始位置
    第二个参数是展示条数
"""
select * from emp limit 3;     # 只展示3条数据
select * from emp limit 0,5;   # 第0条开始再往后取5条数据
select * from emp limit 5,5;   # 第6条开始再往后取5条数据


# 当前语句的执行优先级
from -> select -> limit

# 实际语句的执行优先级
from -> where -> group by -> having -> select -> distinct -> order by 

2. 小练习

Copy# 分页显示，每页5条
select * from emp limit 5;
select * from emp limit 5,5;
select * from emp limit 10,5;
select * from emp limit 15,5;

十. 使用正则表达式查

Copyselect * from emp where name regexp '^j.*(n|y)$'  # 匹配j开头, n或者y结尾的所有字符.