day45链接查询和多表查询-优快云博客

本周内容
    day01 多表查询
        内链接
        左链接
        右链接
        全外链接

        子查询
        navicat工具
        pymysql
        存储过程
        事务
    day02
        视图
        触发器
        函数
        流程控制

        索引原理与慢查询优化

    7-8天
        一个项目整个开发流程
        前端开发
            html
            css
            js
            jq
            bootstrap

多表联合查询

# 方案1：链表
把多张物理表合并成一张虚拟表，再进行后续查询

#======>内链接：保留两张表有对应关系的记录
select * from emp,dep 
where emp.dep_id=dep.id;
select dep.name,emp.name 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;

#======>全外链接：在内链接的基础上保留左右表的记录
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
;

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

#示例2：即找出年龄大于25岁的员工以及员工所在的部门
select emp.name,dep.name from emp 
inner join dep 
on emp.dep_id = dep.id 
where age > 25;


#示例3：以内连接的方式查询employee和department表，并且以age字段的升序方式显示

把多张表链接到一起：

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 emp
inner join dep
on emp.dep_id = dep.id

inner join dep as t1
on t1.id = dep.id;

查询部门内最新入职的员工

select * from employee
inner join
(select depart_id,max(hire_date) as maxd from employee group by depart_id) as t1
on employee.depart_id = t1.depart_id
where employee.hire_date = t1.maxd
;

方案2：子查询

从一张表中查询出结果，用该结果作为查
询下一张表的过滤条件
select * from employee
where hire_date =  (select max(hire_date) from employee);


#查询平均年龄在25岁以上的部门名
select * from dep where id in
(select dep_id from emp group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select * from emp where dep_id in
(select id from dep where name="技术");

#查看不足1人的部门名(子查询得到的是有人的部门id)
select * from dep where id not in (select distinct dep_id from emp);

select * from dep where exists (select * from emp where id>3);

pymysql

import pymysql

conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='123',database='db4',charset='utf8mb4')
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示

# cursor.execute("insert into user(name,pwd) values('egon','123'),('tom','456'),('jack','111');")

# sql="insert into user(name,pwd) values('%s','%s');" %('lili','123')
# cursor.execute(sql)

# %s不要加引号
# cursor.execute("insert into user(name,pwd) values(%s,%s);",('kkk','123'))


username = input("username>>>: ").strip()
password = input("password>>>: ").strip()
# sql = "select * from user where name='%s' and pwd='%s'" %(username,password)
# select * from user where name='egon' -- hello' and pwd='%s'
# select * from user where name='xxx' or 1=1 -- hello' and pwd='%s';
# rows=cursor.execute(sql)

rows=cursor.execute("select * from user where name=%s and pwd=%s",(username,password))

if rows:
    print('ok')
else:
    print('no')

conn.commit()
cursor.close()
conn.close()