单表查询

昨日内容回顾

主题：表与表之间建立关系

• 外键

  foreign key
  就是用来创建表与表之间的关系的字段
  # 如果表与表之间需要建立关系，则需要使用外键
  

  换位思考
  1.先站在一张表判断关系
  2.再站在另一张表判断关系
  3.根据结论得出关系
  

• 一对多

  结论：
  	只有一方成立多个相对条件，即为一对多
  针对一对多的表关系，外键字段建在多的一方
  
  foreign key（*） references *（*）
  

• 多对多

  结论是双方都符合条件，关系即为多对多
  针对多对多，外键字典不建在任何一方，而是直接创建一张新表用来记录关联关系
  

• 一对一

  结论：
  	双方都不符合条件，name表关系就是一对一（一对一可以用膝盖判断）
  针对一对一的表关系，外键字段可以建在任何一方，但是推荐建在热数据的一方
  
  # unique字段 唯一
  

• 外键字段的约束

  1.创建表的时候需要先创建被关联表
  2.初始化插入数据的时候也应该先插入被关联表
  	外键字段只能填写被关联中关联字段的值
  3.外键字段默认五大直接修改和删除
  

• 级联更新与级联删除

  foreign key(dep_id) references dep(id)
  
  

今日内容概要

• 单表查询的数据准备
• 查询关键字之where筛选
• 查询关键字之group by分组
• 补充
• 分组练习题
• 查询关键字之having过滤
• 查询关键字之distinct去重
• 查询关键字之old by排序
• 查询关键字之limit分页
• 查询关键字之 regexp 正则（了解）
• 多表查询
• 查询补充
• sql语句的执行顺序
• 分组练习题
• 作业

今日内容详细

单表查询的数据准备

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

查询关键字之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的员工姓名和薪资
"""
模糊查询
 1.关键字 
  like
 2.符合
  %:匹配任意个数的任意字符
   eg:
    %jason%  只要文本中含有jason即可
     符合条件:asdjahsjasonjashdj
    jason%  只要文本的开头是jason即可
     符合条件:jasonasjdjuuwq
  _:匹配单个个数的任意字符
   eg:
    _jason_  jason前后都有一个任意字符
     符合条件:ojason$
    jason_   jason后面有一个任意字符
     符合条件:jason*
    
"""
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类似于python里面的len方法，用来统计长度"""

# 5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
select * from emp where id<3 or id>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;
select name,post from emp where post_comment is not NULL;

查询关键字之group by分组

分组：将一个个个体，按照一定的条件组成一个个整体

'''在分组之后默认只能拿到分组的依据，不能直接获取分组以外的数据'''

针对mysql5.6及之前的版本，需要我们自己设置严格模式
set global sql_mode='ONLY_FULL_GROUP_BY';

'''
函数
	max
	min
	sum
	count
	avg
'''
# 1.获取每个部门的最高工资
	1.应该先按照部门先分组
	2.在利用聚合函数查询
select post,max(salary) from emp group by post;

# 2.每个部门的最低工资
select post,min(salary) from emp group by post;

# 3.每个部门的平均工资
slect post,avg(salary) from emp group by post;

# 4.每个部门的工资总和
select post sum(salary) from emp group by post;

# 5.每个部门的人数
select post count(id) from emp group by post;

补充

# 1.查询每个部门名称及部门下的员工姓名
"""1.group_concat()  可以获取非分组字段以外的数据"""
select post,group_concat(name) from emp group by post;

# 2.查询每个部门名称及部门下的员工姓名和薪资
select post,group_concat(name),group_concat(salary) from emp group by post;
select post,group_concat(name,salary) from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
select post,group_concat(name,':',salary,'|',sex) from emp group by post;

"""2.针对查询出来的数据还可以使用as自定义字段名称"""
select post,group_concat(name) as '员工姓名' from emp group by post;
select name as '姓名',post as '岗位' from emp;
# as其实也可以直接省略,但是不推荐!


"""3.针对不分组的情况，可以使用concat做拼接"""
select concat(name,":",post,":",sex) from emp;

"""4.如果多个字段拼接的符号是一样的 还可以使用concat_ws"""
select concat_ws(':',name,sex,post) from emp;

"""5.针对select后面的字段名称还可以直接做运算"""
select name,salary*12 from emp;

分组练习题

# 1.统计各部门年龄在30岁以上的员工平均工资
	1.先筛选出年龄是30岁以上的数据
    	select * from emp where age > 30;
    2.再对筛选出来的数据做分组
    	select * from emp where age > 30 group by post;
    3.求平均薪资
		select post,avg(salary) from emp where age > 30 group by post;
"""where的筛选在group by分组之前执行"""

查询关键字之having过滤

'''
having在功能上与where一模一样，都是对数据进行后期的筛选
但是区别于
	where用在分组之前
    having用在分组之后
'''
1.统计各部门年龄在18岁以上的员工的平均工资，并且保留平均工资大于10000的部门
select post,avg(slaary) from emp
	where age > 18 			 # 分组前使用
    group by post
    having avg(salary) >10000 # 分组后使用

查询关键字之distinct去重

distinct去重有一个非常苛刻的条件：数据必须是完全一模一样的才可以
# 如果数据包含了主键，那么数据一定没有重复项

select distinct sex,post from emp;

查询关键字之old by排序

select * from emp order by salary;
# 默认是升序
select * from emp order by salary asc；
# asc就是升序的意思 默认
select * from emp order by salary desc；
# 降序
'''
"按照单个字段排序可能会出现相同值从而无法绝对顺序，这个时候我们还可以设置第二 第三...更多的比较字段
'''
# 1.先按照年龄升序排序如果比较不出来大小则再按照薪资降序排序
select * from emp order by age asc,salary desc;

# 2.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于10000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
	where age>10 
    group by post
    having avg(salary) >10000
    order by avg(salary);

查询关键字之limit分页

'''当表中的数据量过大，一次查询所有结果可能导致内存崩溃'''

select *from emp limit 5;
# 一个数字表示数据条数
select * from emp limit 5,5;
# 两个数字第一个数字表示起始位置 第二个数字表示数据条数

查询关键字之 regexp 正则

'''正则：使用一些特殊符号去字符串中筛选出符合条件的内容'''
select * from emp where name regexp '^j.*(n|y)$';

多表查询

有时候我们需要查询的数据并不单单来源于一张表
这时候我们就需要多表查询

针对多表查询有以下两种查询策略
	1.子查询
    	分步操作
    2.连表操作
		直接将需要查询的表都拼接到一起
		然后使用单表查询

查询补充

当查询字段较多，展示错乱的情况下，我们可以采取下列措施
1.\G
	select * from mysql.user\G;
2.修改select后面的*号
	select name from mysql.user;

sql语句的执行顺序

select * from emp where id=1;
# 书写顺序
select
from
where
# 执行顺序
from
where
select
'''
虽然select并不是第一个执行的，但是我们在书写的时候确实第一个写的我们书写sql语句的时候会直接书写select *
之后把后面的语法不全再来考虑 * 是否需要换成指定的字段名字
'''

分组练习题

# 1.统计各部门年龄在30岁以上的员工平均工资
	1.先筛选出年龄是30岁以上的数据
    	select * from emp where age > 30;
    2.再对筛选出来的数据做分组
    	select * from emp where age > 30 group by post;
    3.求平均薪资
		select post,avg(salary) from emp where age > 30 group by post;
"""where的筛选在group by分组之前执行"""

作业

1.脱稿来一遍
2.敲一遍今日笔记
3.完成下列分组练习题
	 # 刚开始查询表，一定要按照最基本的步骤，先确定是哪张表，再确定查这张表也没有限制条件，再确定是否需要分类，最后再确定需要什么字段对应的信息
    1. 查询岗位名以及岗位包含的所有员工名字
    	select post,name from emp;
        
    2. 查询岗位名以及各岗位内包含的员工个数
    	select post,count(id) from emp
        	group by post;
            
    3. 查询公司内男员工和女员工的个数
    	select sex,count(id) from emp
        	group by sex;
        	
    4. 查询岗位名以及各岗位的平均薪资
    	select post,avg(salary) from emp
        	group by post
            having avg(salary);
    5. 查询岗位名以及各岗位的最高薪资
    	select post,max(salary) from emp
        	group by post
            having max(salary);
    6. 查询岗位名以及各岗位的最低薪资
    	select post,min(salary) from emp
        	group by post
            having min(salary);
    7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
    	select sex,avg(salary) from emp
        	group by sex
            having avg(salary);