day45

（1）多表查询

内链接

左链接

右链接

全外链接

 

子查询

 

（2）pymysql模块

# 多表联合查询
# ============= 1、链表 =============
# 把多张物理表合并成一张虚拟表，再进行后续查询

# 建表
create table department(
id int,
name varchar(20)
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('Welt','male',18,200),
('Einstein','female',48,201),
('Tom','male',38,201),
('Tes','female',28,202),
('Durandal','female',18,200),
('Joyce','male',18,204)
;


alter table employee rename emp;
alter table department rename dep;

# 内链接：保留两张表有对应关系的记录
# select * from emp,dep;      # 笛卡尔积
# select * from emp,dep where emp.dep_id=dep.id;（不推荐）
select * from emp inner join dep on emp.dep_id = dep.id;
select * from emp inner join dep on emp.dep_id = dep.id where dep.name='技术';

# 左链接：在内链接的基础上保留左表的记录
select * from emp left join dep on emp.dep_id=dep.id;

# 右链接：在内链接的基础上保留右表的记录
select * from emp right join dep on emp.dep_id=dep.id;

# 全外链接：在内链接的基础上保留左右表的记录
# mysql中不支持full join
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;

# 示例1：查询所有部门名及对应的员工个数
select dep.name,count(emp.id) from emp right join dep on emp.dep_id = dep.id group by dep.name;
#示例2：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select emp.name,emp.age,dep.name from emp inner join dep on emp.dep_id = dep.id where emp.age > 25;
#示例3：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select * from emp inner join dep on emp.dep_id = dep.id order by age;
# 查看人数小于等于1的部门名
select dep.name,count(emp.id) from emp right join dep on emp.dep_id = dep.id group by dep.name having count(emp.id) <= 1;

# 多表链接
select * from
(select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id) as t1
inner join dep on t1.dep_id = dep.id;

select * from
(select t1.* from
(select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id) as t1
inner join dep on t1.dep_id = dep.id) as t2
inner join dep on t2.dep_id = dep.id;




# ============= 2、子查询 =============
# 从一张表中查询结果，用该结果作为查询下一张表的过滤条件

# 建表
create table employee(
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 employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('Yang','male',18,'20170301','外交大使',7300.33,401,1), #以下是教学部
('Tes','male',78,'20150302','teacher',1000000.31,401,1),
('Einstein','male',81,'20130305','teacher',8300,401,1),
('Durandal','female',73,'20140701','teacher',3500,401,1),
('otto','male',28,'20121101','teacher',2100,401,1),
('Joyce','female',18,'20110211','teacher',9000,401,1),
('Joachim','male',18,'19000301','teacher',30000,401,1),
('Bron','female',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)
;
update employee set hire_date=20170301 where id=5;
update employee set hire_date=20160311 where id=9;
select * from employee;
# 查询最新入职员工信息
select max(hire_date) from employee;        # 将结果作为过滤条件
select * from employee where hire_date=(select max(hire_date) from employee);
# 查询每个部门最新入职员工信息
select * from employee
    inner join
    (select hire_date,depart_id from employee group by depart_id) as t1
        on employee.depart_id=t1.depart_id
where employee.hire_date=t1.hire_date;
# select * from employee
#     inner join
#     (select hire_date,depart_id from employee group by depart_id) as t1
#         on employee.hire_date=t1.hire_date;       # 这种方式如果有两个不同部门的最新时间相同则会发生混乱

# 查询平均年龄在25岁以上的部门
select depart_id from employee group by depart_id having avg(age)>25;
# 查询小于等于1人的部门名(子查询得到的是有人的部门id)
select dep.name,count(emp.id) from emp right join dep on emp.dep_id = dep.id group by dep.id having count(emp.id)<=1;
select * from dep where id not in (select distinct dep_id from emp);    # 没有人的部门
select * from dep where exists (select * from emp where id>3);      # exists有结果就成立

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='2499', database='pycharmdata',
                       charset='utf8mb4')
# 游标
cursor = conn.cursor()
#
# cursor.execute("insert into user(name,password) values('aaa','123'),('bbb','456');")        # 执行
# # %s不要加引号（如果加了引号就会报错，因为execute拼接时自动加引号）
# cursor.execute("insert into user(name,password) values(%s,%s);", ('kkk', '123'))

username = input('username>>>:').strip()
password = input('password>>>:').strip()
# sql = 'select * from user where name="%s" and password="%s"' % (username, password)       # 会出现SQL注入问题！
# rows = cursor.execute(sql)
rows = cursor.execute('select * from user where name=%s and password=%s', (username, password))
print(rows)
if rows:
    print('ok')
else:
    print('no')

conn.commit()  # 提交

cursor.close()  # 关闭游标
conn.close()    # 关闭链接