目录

数据库04 /多表查询

昨日回顾

1.增加：    insert into 表名 values(1,2),(1,2);2.删除：    delete from 表名 where id = 1;    清空表：    truncate 表名; 清空表 自增重置3.修改：    update 表名 set 要修改的字段名=改后的值 where id = 1;4.查询：    select distinct 字段1 as a,字段2... from 库名.表名 (如果没有use连接到某个库,需要写上库名)    where 条件     group by 分组依据字段    having 分组后再过滤    order by 排序  order by 字段 asc,字段 desc;    limit  limit 3   limit 5,5 从索引为5的记录(第六条)开始算上这一条取5条5.聚合函数：    sum、avg、count、group_concat()

1.笛卡尔积

将两表所有的数据一一对应，生成一张大表（可能有重复的字段名）select * from dep,emp; #两张表拼在一起select * from  ,emp where dep.id=emp.dep_id;#找到两表之间对应的关系记录select * from dep,mep where dep.id=emp.dep_id and dep.name='技术';#筛选部门名称为技术的大表中的记录select emp.name from dep,emp where dep.id = emp.dep_id and dep.name='技术'; #拿到筛选后的记录的员工姓名字段数据展示出来是虚拟的表,和平时单表的约束条件不一样,可能有重名，原表可能设置为非空，连表后可能为空

2.连表查询

2.1 inner join 内连接

第一步：连表    select * from dep inner join emp on dep.id=emp.dep_id;2.过滤    select * from dep inner join emp on dep.id=emp.dep_id where dep.name = '技术';3.找对应数据    select emp.name from dep inner join emp on dep.id=emp.dep_id where dep.name = '技术';

2.2 left join 左连接

left join  左连接(left join左边的表为主表,主表记录必须全部显示,辅表没办法对应上的,就通过null来补全)（左边显示dep，右边显示emp）select * from dep left join emp on dep.id=emp.dep_id;

2.3 right join 右连接

right join  右连接(right join右边的表为主表,主表记录必须全部显示,辅表没办法对应上的,就通过null来补全)（左边显示dep，右边显示emp）select * from dep right join emp on dep.id=emp.dep_id;

2.4 union全连接

mysql> select * from dep left join emp on dep.id=emp.dep_id    -> union    -> select * from dep right join emp on dep.id=emp.dep_id;    #注意 union与union all的区别：union会去掉相同的纪录，因为union all是left join 和right join合并，所以有重复的记录，通过union就将重复的记录去重了。

3.子查询

1.一个查询结果集作为另一个查询的条件select name from emp where dep_id = (select id from dep where name = '技术');2、带比较运算符的子查询    比较运算符：=、!=、>、>=、<、<=、<>    查询大于所有人平均年龄的员工名与年龄3、带EXISTS关键字的子查询　　EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False　　当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。还可以写not exists，和exists的效果就是反的3.总结：#1：子查询是将一个查询语句嵌套在另一个查询语句中。#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字#4：还可以包含比较运算符：= 、 !=、> 、

4.Navicat 工具使用

5.pymysql模块

5.1python代码读取mysql数据库

import pymysqlconn = pymysql.connect(    host='127.0.0.1',    port=3306,    user='root',    password='666',    database='day43',    charset='utf8',)cursor = conn.cursor(pymysql.cursors.DictCursor)#默认游标取出的数据是 ((),)# DictCursor 对应的数据结构 [{},],如果用的是fetcheone,那么结果是{}.sql = "select * from dep;"ret = cursor.execute(sql) #ret 受影响的行数print(ret)print(cursor.fetchmany())  #不写参数,默认是一条print(cursor.fetchone())print(cursor.fetchall())print('----------')cursor.scroll(2,'absolute')  #absolute 绝对移动,相对于数据最开始的位置进行光标的移动cursor.scroll(2,'relative')  #relative 相对移动,按照光标当前位置来进行光标的移动print(cursor.fetchone())

5.2python代码增删改mysql数据库

import pymysqlconn = pymysql.connect(    host='127.0.0.1',    port=3306,    user='root',    password='666',    database='day43',    charset='utf8',)cursor = conn.cursor(pymysql.cursors.DictCursor)sql = "insert into t1 values (3,'xx3',18);"ret = cursor.execute(sql)print(ret)# 增删改都必须进行提交操作(commit)conn.commit()  #提交

5.3sql注入

所谓SQL注入，就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。import pymysqlconn = pymysql.connect(    host='127.0.0.1',    port=3306,    user='root',    password='666',    database='day43',    charset='utf8',)while 1:    username = input('请输入用户名:')    password = input('请输入密码:')    cursor = conn.cursor(pymysql.cursors.DictCursor)    # sql注入    # sql = "select * from userinfo where username='%s' and password='%s';" \    #       % (username, password)    # # sql = "select * from userinfo where username='duijie '-- ' and password='%s';" \    # #       % (username, password) #知道用户名不知道密码,登录网站(输入ergou" -- "即可登录)    # sql = "select * from userinfo where username='asdfasdf' or 1=1 -- ' and password='%s';" \    #       % (username, password) #不知道用户名也不知道密码,登录网站(输入afsdffga"or 1=1 -- "即可登录)    # pymysql解决sql注入问题    sql = "select * from userinfo where username=%s and password=%s;"    ret = cursor.execute(sql,[username,password])    if ret:        print('登录成功')    else:        print('账号或者密码错误,请重新输入!!!')# 增删改都必须进行提交操作(commit)conn.commit()

5.4 总结

import pymysqlconn = pymysql.connect(    host='127.0.0.1', #主机ip    port=3306, #端口号    user='root', #用户名    password='666', #密码    database='day43' #需要连接的库    charset='utf8')cursor = conn.cursor()sql = "select * from dep;"ret = cursor.excute(sql) #ret受影响的行数print(cursor.fetchall()) #取出所有的print(cursor.fetchmany(3)) #取出多条print(cursor.fetchone()) #取出单条cursor.scroll(3,'absolute')  #绝对移动,按照数据最开始位置往下移动3条cursor.scroll(3,'relative')  #相对移动,按照当前光标位置往下移动3条conn.commit()  #增删改操作时,需要进行提交sql注入:解决方案    cursor.execute(sql,[参数1,参数2...])两种场景：    知道用户名不知道密码 or 1=1    不知道用户名也不知道密码 ' -- (--后面有一个空格)    不能通过字符串格式化来写    cursor.execute(sql,[参数1，参数2...])相当于是去掉特殊符号去掉