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

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

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.idunionselect * 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 t2on t1.post = t2.postwhere 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.postwhere 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 pymysqlconn=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()

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