python 之 数据库（多表查询之连接查询、子查询、pymysql模块的使用）

10.10 多表连接查询

10.101 内连接

把两张表有对应关系的记录连接成一张虚拟表

select * from emp,dep；                                  #连接两张表的笛卡尔积
select * from emp,dep where emp.dep_id = dep.id;            # 不推荐用where连接表
select * from emp inner join dep on emp.dep_id = dep.id;    #推荐
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | egon      | male   |   18 |    200 |  200 | 技术         |
|  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
+----+-----------+--------+------+--------+------+--------------+

#应用：
select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术"; 
select * from emp inner join dep on emp.dep_id = dep.id where dep.name = "技术";
+----+-----------+------+------+--------+------+--------+
| id | name      | sex  | age  | dep_id | id   | name   |
+----+-----------+------+------+--------+------+--------+
|  1 | egon      | male |   18 |    200 |  200 | 技术   |
|  5 | liwenzhou | male |   18 |    200 |  200 | 技术   |
+----+-----------+------+------+--------+------+--------+

10.102 左连接

在内连接的基础上，保留左边没有对应关系的记录

select * from emp left join dep on emp.dep_id = dep.id;
+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | egon       | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 | NULL | NULL         |
+----+------------+--------+------+--------+------+--------------+

10.103 右连接

在内连接的基础上，保留右边没有对应关系的记录

select * from emp right join dep on emp.dep_id = dep.id;
+------+-----------+--------+------+--------+------+--------------+
| id   | name      | sex    | age  | dep_id | id   | name         |
+------+-----------+--------+------+--------+------+--------------+
|    1 | egon      | male   |   18 |    200 |  200 | 技术         |
|    2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|    5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 运营         |
+------+-----------+--------+------+--------+------+--------------+

10.104 全连接

在内连接的基础上，保留左、右边没有对应关系的记录，并去重

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;
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | egon       | male   |   18 |    200 |  200 | 技术         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|    2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
+------+------------+--------+------+--------+------+--------------+

补充：多表连接可以不断地与虚拟表连接

#查找各部门最高工资
select t1.* from emp as t1 inner join (select post,max(salary) as ms from emp group by post) as t2
on t1.post = t2.post
where t1.salary = t2.ms;

10.11 子查询

把一个查询语句用括号括起来，当做另外一条查询语句的条件去用，称为子查询

#查询技术部员工的名字
select emp.name from emp inner join dep on emp.dep_id = dep.id where dep.name="技术";#连接查询
select name from emp where dep_id =(select id from dep where name="技术");          #子查询
+-----------+
| name      |
+-----------+
| egon      |
| liwenzhou |
+-----------+
#查询平均年龄在25岁以上的部门名                                                       #子查询
select name from dep where id in (select dep_id from emp group by dep_id having avg(age) > 25);
select dep.name from emp inner join dep on emp.dep_id = dep.id                         #连接查询
    group by dep.name
    having avg(age) > 25;
+--------------+
| name         |
+--------------+
| 人力资源      |
| 销售          |
+--------------+
#查询每个部门最新入职的那位员工
select t1.id,t1.name,t1.post,t1.hire_date,t2.post,t2.max_date from (emp as t1) inner join
(select post,max(hire_date) as max_date from emp group by post) as t2   #拿到最大雇佣时间
on t1.post = t2.post
where t1.hire_date = t2.max_date;
+----+--------+-----------------------------------------+----
| id | name   | post    | hire_date  | post    |  max_date  |
+----+--------+-----------------------------------------+-----
|  1 | egon   | 外交大使 | 2017-03-01 | 外交大使 | 2017-03-01 |
|  2 | alex   | teacher | 2015-03-02 | teacher  | 2015-03-02 |
| 13 | 格格   | sale     | 2017-01-27 | sale     | 2017-01-27 |
| 14 | 张野   | operation| 2016-03-11 | operation| 2016-03-11 |
+----+--------+-----------------------------------------+-----

exists( )：括号内的值存在时满足条件

select * from emp where exists (select id from dep where id > 3);       #找到所有

10.12 pymysql模块的使用

10.121 pymysql查

import pymysql              #pip3 install pymysql
conn=pymysql.connect(        #连接
    host='127.0.0.1',
    port=3306,
    user='root',
    password='',
    database='db2',
    charset='utf8')
cursor=conn.cursor(pymysql.cursors.DictCursor)#以字典形式显示表的记录
rows=cursor.execute('show tables;')           #1 显示受影响的行数(row),此处为有表的条数
print(rows)
rows=cursor.execute('select * from emp;')      #18 此处rows为emp表内有记录的条数
print(rows)
​
print(cursor.fetchone())     #查看一条记录 一个字典{key:value}
print(cursor.fetchmany(2))   #查看多条记录 [{key:value},]
#print(cursor.fetchall())    #查看所有记录 强调：下一次查找是接着上一次查找的位置继续
​
cursor.scroll(0,'absolute')  #绝对移动，以0位置为参照显示
print(cursor.fetchone())
​
cursor.scroll(1,'relative')  #相对移动，相对当前位置移动1条记录
print(cursor.fetchone())
​
cursor.close()#光标
conn.close()

10.122 防止sql注入问题

在服务端防止sql注入问题：不要自己拼接字符串，让pymysql模块去拼接，pymysql拼接时会过滤非法字符

import pymysql 
conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='',
    database='db2',
    charset='utf8'
)
cursor=conn.cursor(pymysql.cursors.DictCursor)
​
inp_user=input('用户名>>：').strip() #inp_user=""
inp_pwd=input('密码>>：').strip() #inp_pwd=""
sql="select * from user where username=%s and password=%s"
print(sql)
            
rows=cursor.execute(sql,(inp_user,inp_pwd))#输入的用户名和密码中的非法字符会被过滤掉
if rows:
    print('登录成功')
else:
    print('登录失败')
cursor.close()
conn.close()

10.123 pymysql增删改

import pymysql 
conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='',
    database='db2',
    charset='utf8')
cursor=conn.cursor(pymysql.cursors.DictCursor)          
sql='insert into user(username,password) values(%s,%s)'         #插入单行记录
rows=cursor.execute(sql,('EGON','123456'))              
print(rows)
print(cursor.lastrowid)                                      #显示当前最后一行的id
​
sql='insert into user(username,password) values(%s,%s)'         #一次插入多行记录
rows=cursor.executemany(sql,[('lwz','123'),('evia','455'),('lsd','333')])
print(rows)
​
rows=cursor.execute('update user set username="alexSB" where id=2')#修改记录
print(rows)
​
conn.commit() # 只有commit提交才会完成真正的修改
cursor.close()
conn.close()