内容回顾：
1.外键的变种
表和表之间的关系
一对多或多对一
多对多
一对一
左表 右表
多 一
一 多
多对多，使用第三张表 建立关系
2.补充
核心： 数据驱动视图
json-server
设计模式： MVC ===> MTV
Model - View -Controller
3.单表查询
关键字的优先级
select * from user where id > 2 group by post having count(1) > 10 order by age desc limit 0,2;
from
where
group by
having
select
order by
limit
where
>,< >=,<=, !=
between xxx and ooo
in(20,30,40)
and or not
sql_mode = ONLY_FULL_GROUP_BY
分组之后只能获取分组的字段，如果想获取组内的信息，是通过聚合函数
聚合函数：
count(1) 计算总个数
avg()
sum()
max()
min()
今日内容：
1、单表查询
having
二次筛选
having后面的字段只能是分组（group by）之后字段
1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(1) from employee group by post having count(1) < 2;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select * from employee where salary not in (select avg(salary) as A from employee group by post having A > 10000);
select post,avg(salary) as A from employee group by post having A > 10000
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
order by
asc 升序
desc 降序
limit 0,5 第一个参数起始位置，第二个参数显示的个数
2、多表查询
select employee.name as a,department.name as b from employee,department where employee.dep_id = department.id and employee.name = 'egon';
select * from (select employee.name as a,department.name as b from employee,department where employee.dep_id = department.id) as A where A.a ='egon';
select * from employee,department where employee.dep_id = department.id
外链接操作
内连接： 符合条件查询 只连接匹配的行
select * from employee inner join department on employee.dep_id = department.id;
左连接： 优先显示左表记录
select * from employee left join department on employee.dep_id = department.id;
右连接: 优先显示右边记录
select * from employee right join department on employee.dep_id = department.id;
全外连接：
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
3.符合条件查询
以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，
即找出年龄大于25岁的员工以及员工所在的部门
select department.name,employee.name from employee inner join department on employee.dep_id = department.id where age > 25
4.子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
一个查询语句查询的结果作为另一个查询语句的条件
1、查询平均年龄在25岁以上的部门名
select name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
2、查看不足1人的部门名
select name from department where id not in (select dep_id from employee group by dep_id);
3、查询大于所有人平均年龄的员工名与年龄
select name,age from employee where age > (select avg(age) from employee);
4、查询大于部门内平均年龄的员工名、年龄
思路：
（1）先对员工表(employee)中的人员分组(group by)，查询出dep_id以及平均年龄。
(2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。
(3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。
select * from employee inner join (select dep_id,avg(age) as b from employee group by dep_id) as A on employee.dep_id = A.dep_id where employee.age > A.b;
5.查询每个部门最新入职的那位员工
select name from employee
inner join
(select post,max(hire_date) as newTime from employee group by post) as A
on employee.post = A.post
where employee.hire_date = A.newTime;
5.pymysql模块的使用
（1）pip install pymysql
import pymysql
username = input('请输入用户名：')
pwd = input('请输入密码：')
# 建立连接
conn = pymysql.connect(
host='localhost',
user='root',
password="",
database='db13',
port=3306,
charset='utf8'
)
# 创建游标
cur = conn.cursor()
sql = 'select * from userinfo where name="%s" and pwd="%s"'%(username,pwd)
print(sql)
res = cur.execute(sql)
print(res)
# 游标关闭 连接关闭
cur.close()
conn.close()
if res:
print('登录成功')
else :
print('登录失败')
