9/7

今日考题
1.列举所知道的所有约束条件

unsigned
zerofill
not null
default
unique
primary key
auto_increment
foreign key

2.什么是外键，如何判断外键关系

外键就是用于记录表与表之间的联系
通过从两个表的不同层面思考能否与多个数据对应
有一个YES一个NO是一对多
两个YES是多对多
两个NO是一对一(有可能是一眼就能看出的没有关系)

3.外键约束及各自特点

外键约束都要加上
级联更行 on update cascade
级联删除 on delete cascade
一对多:
    1.先创建被关联的表
    2.再创建有外键的表
多对多:
    1.两个表都要加入外键约束
    2.两者的外键关系存放在另一个表中
一对一:
    1.可以将外键创在任意表中 一般创在常用的那个表里
    2.通过在外键关联的数据后加上unique标签来与一对多区分

复习巩固

• 主键

primary key
# InnoDB规定了表必须有且仅有一个主键

• 自增

auto_increment
# 主要配合主键使用 减少录入数据过程中对主键的关注度

• 主键自增特性

自增特性不会因为数据的删除和插入报错而停止或回退重置
如果一定需要重置 可以使用关键字truncate

• 外键

记录表与表之间的数据关系

• 表关系

# 从A表和B表两个层面开始思考 能否对应多个
一个行一个不行'一对多'
两个都行'多对多'
两个都不行'一对一'

• 修改表SQL语句

modify # 少见
change  # modify的尚未替代
add
drop
rename
...

内容概要

主题:查询数据为主

• 单表查询关键字

  where
  group by
  having
  distinct
  orderby
  limit
  regexp
  ...
  

• 多表查询两种策略

  子查询
  联表操作
  

今日内容详细

数据准备

"""
涉及到使用命令操作的时候最好在命令结束之后检查一下 
涉及到表字段较多窗口展示错乱的情况可以使用\G分行展示
"""
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);

查询关键字之where筛选

# where 筛选条件
1.查询id大于等于3 小于等于6的数据
select id,name from emp where id>=3 and id<=6;  # 这种和python里逻辑运算一样比较好记
select id,name from emp where id between 3 and 6;

2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 2000 or salary = 18000 or salary = 17000;  # 这个也像是python里的逻辑运算
select * from emp where salary in (20000,18000,17000);  # 很像python中的成员运算但是可能会和区间搞混

# 模糊查询
'''
关键字是	like
关键符号
	%  匹配任意个数任意字符
	_  匹配单个个数的任意字符
show variables like '%mode%';
比如这个就是查看mode前面可以有任意字符后面也可以有任意字符的数据
言下之意就是查看带有mode的数据
'''
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.查询薪资不是20000，18000，17000的数据
select * from emp where salary not in (20000,18000,17000);
# 这个也类似于成员运算

6.查询岗位描述为空的员工名与岗位
select name,post from emp where post_comment = NULL;  # 查询为空！
select name,post from emp where post_comment is NULL;  # 因为null是关键字如果用=是查不到的一定要用is
'''
在书写SQL语句的时候可以先写select * from 表名 
之后根据需求添加关键字
最后根据需要的数据去修改*的语句
'''

查询关键字之group by分组

'''
分组:按照指定的条件将单个的个体分成一个个整体
	eg:
	按照性别把人分成男性和女性
	按照班级把学生分成多个班 
'''
7.按照部门分组
select * from emp group by post;  
# 但是既然你已经分组那就只希望拿到分组后的整体而不需要去以个体再为研究单位
# 所以前面的语句并不合理应该改成
select post from emp group by post;
'''
但是前一个没有报错是因为缺了5.7之前的版本缺了严模式格
set global sql_mode="strict_trans_tables,only_full_group_by";
	分组后默认只可以直接获取到分组依据 无法再获取内部单个个体数据
	如果想要获取得借助一些方法
'''
# 在查询的时候是否需要分组会有关键字
eg: 每个国家 各省份 每个城市 各地区
    就按每个或者各之后的那个东西分组

聚合函数

# 主要用于分组后的数据处理 
max		最大值
min		最小值
avg		平均值
sum		求和
count	计数
# 用起来非常简单在select后面要找的数据前放上聚合函数
# 再括号套上需要的数据
8.查看各部门最高薪资
select post,max(salary) from emp group by post;
9.查看各部门最低薪资
select post,min(salary) from emp group by post;
10.各部门平均薪水
select post,avg(salary) from emp group by post;
11.各部门薪资总和
select post,sum(salary) from emp group by post;
12.获取每个部门员工人数
select post,count(id) from emp group by post;
# 主键作为每个人的唯一标识所以推荐使用id

查询关键词之having过滤

"""
having与where功能其实都是筛选数据 
区别在于
	where用于分组之前的筛选
	having用于分组之后的过滤
"""
13.统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
看到问题不要着急一步步处理
# 第一步看到关键字各 按照部门分类 分类分都分了顺手加上聚合函数
select post,avg(salary) from emp group by post;
# 第二步按照要求 用where把分类前所有30岁以上的老家伙都筛出来
select post,avg(salary) from emp where age > 30 group by post;
# 最后一步用having过滤得到最后的数据
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
# 为了思路看起来更清晰把每个要求放在一行整理一下
select post,avg(salary) from emp 
	where age > 30 
	group by post 
	having avg(salary) > 10000;

查询关键字之distinct去重

select distinct 表头字段 from 表名;
'''去重的前提是数据必须一摸一样才可以'''
那有主键在数据不就肯定不重复了嘛?
# 所以要配合字段名去重
select distinct age from emp;
# 还可以用逗号隔开字段名做到类似联合唯一的去重效果
select distinct gender,age from emp;

查询关键字之order by排序

select * from emp order by salary;  # 看结果会发现默认是升序
select * from emp order by salary desc;  # 加上desc变成降序
select * from emp order by salary asc;  # asc是升序关键字
对于根据多个数据排序可以逗号隔开要求
select * from emp order by age desc,salary;

14.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
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 5; # 拿前5条数据
select * fron emp limit 2,5; # 跳过前2个人开始取后5个

15.查询工资最高的人的所有信息
select * from emp 
	order by salary desc 
	limit 1;

查询关键字之regexp正则

'''
正则: 使用一些特殊符号的组合去字符串里筛选出符合条件的数据(爬虫里继续讲)
'''
select * from emp where name regexp '^j.*(n|y)$';
# 查询姓名是以字母j开头 n或者y结尾的数据