当我们的一条记录 分散不同的表中时,就需要进行多表查询
例如 一对一 一对多 多对多
1.笛卡尔积查询 意思就是将两个表中的所有数据 全部关联在一起
例如A表有两条 B表有三条 一共有6条
会产生大量的错误数据 需要用添加来过滤
select * from 表1 表2.... where 过滤条件
on 专门用于筛选出正确的匹配关系 只能与join一起使用
但是在join中可以吧on换成where
反过来在普通查询中不可以使用on
通常在连接查询 join中推荐使用on
外连接查询不常用(不应该出现这种没有正确关联的数据)
连接查询
内连接查询 inner join
select *from 表1 join 表2 on(专门筛选关联关系)关联过滤条件
两边的数据必须完全匹配成功才显示
左外连接 left join
左边的表的数据必须完全匹配成功才显示
右外连接 right join
右边的表的数据必须完全匹配成功才显示
全外连接
mysql不支持 可以使用合并查询union来将左外连接和右外连接合并
连接查询解决问题的思路
1,先联合查询
2,on 来筛选正确关系
3,where 来进行额外的条件过滤
子查询 指的是当一个查询语句被作为另一个查询语句的条件时,该查询语句就称之为子查询(内层查询)
可以将一个大的问题拆分几个小的问题 然后一步一步来查询
子查询语法:将子查询(内层查询)用括号包裹即可
连接查询;
select * from emp join dept where emp.dept_id=dept.id where dept.name='财务';
子查询的实现思路
select * from emp where emp.dept_id=(select id from dept where name ='财务');
连接查询:
1.先连接在一起
2. on 筛选正确匹配关系
3.where 条件
exists后跟子查询 成立为True 不成立为False
查询每个部门工资最高的员工信息
1.查询出每个部门的最高工资是多少
select dept_id,max(salary),dept.name from emp group by dept_id;
2.拿着最高工资查询员工信息
select *from emp join
(select dept_id,max(salary) as maxs from emp group by dept_id) as t1
on emp.dept_id = t1.dept_id
where emp.salary = t1.maxs;
查询语句无论多长 其实都是一步一步做出来的 先写一段测试一下没问题 再接着写
select t1.day_id,success,fail from
(select day_id,count(*) as success from test group by day_id,result having result = "success") as t1
join
(select day_id,count(*) as fail from test group by day_id,result having result = "fail") as t2
on t1.day_id = t2.day_id;
用户管理
mysql用户指的是客户端连接服务器时使用的账户
在一些公司中,很多项目的数据 可能会放在同一个服务器 那么就必须要为每一个用户明确其所拥有的权限
通常到公司之后 都会给你一个账号名称和密码 并且为你指定你可以访问哪些数据库或表
对用户账号的增删改查 以及权限的增删改查
mysql与权限相关的表:
user columns_priv tables_priv db
select * from user \G;#当字段太多 一行显示不了 可以\G
create user 用户名@主机名称 identified by '123';
该语句只是单纯创建一个用户 后续还要分配权限 稍微麻烦
推荐使用grant 语句 可以在创建账户的同事分配权限
grant all on *.* to 用户名@主机名 identified by '密码';
grant all on *.* to jack@localhost identified by "123";
# 授予 所有库的所有表的所有权限 给 jack这个账户 密码为123 只允许在127.0.0.1上登录
主机地址是% 以为这这个用户可以在任何主机上登陆服务器 需要加上双引号
#这个用户不能其他用户授权 默认只有root可以为其他账户授权
grant all on day45.* to jerry@localhost identified by "123"; # day45所有表
grant all on day45.dept to tom@localhost identified by "123"; # day45的dept表
mysql的权限可以精确到列(某个字段的某种权限)
但是不能精确到某一行(需要使用视图)
可以将权限授予其他账户
grant all on day45.dept to bgon@localhost identified by "123" with grant option;
收回权限
REVOKE all privileges [column] on db.table from user@"host";
REVOKE all privileges on day45.dept from bgon@"localhost";
删除用户
drop user username@host;
刷新权限
flush privileges
正常开发时 我们的数据库服务器也会运行在云服务器上,经常需要从本地远程操作数据库
但是默认情况root 只能在服务器本机上登录
所以我们可以grant 语句来授权所有主机
grant all on *.* to root@192.168.111 identified by "123321" with grant option;
pymysql 是有mysql提供的一个模块 需要单独安装
pymysql的作用就是可以使用python代码来操作数据库服务器上的数据
本质上还是一个cs的客户端
1.连接服务器
2.发送sql指令给服务器
3.接受服务器返回的结果
# import pymysql
# try:
# # 1.conn是一个表示连接的对象
# conn=pymysql.connect(
# host='127.0.0.1',
# port=3306,
# user='root',
# password='root',
# database='db4')
#
#
# print('连接服务器成功')
#
# #2.查询数据 需要借助cursor类 游标 默认游标返回值类型为元组
# #pymysql.cursors.DictCursor 可以将结果转为字典
# cursor=conn.cursor(pymysql.cursors.DictCursor)
# #3.执行sq语句
# sql='select *from user;'
# res=cursor.execute(sql)#res为本次查询得到的记录条数
# print(res)
#
# #4.提取数据
# print(cursor.fetchall())#提取本次查询所有结果
# # print(cursor.fetchone())#提取本次查询第一条记录
# # print(cursor.fetchmany(2))#提取记录的条数
#
# #如果游标已经达到了最后 将无法在读取到数据 可以使用scroll来移动游标位置
# cursor.scroll(0,mode='absolute')
# #mode参数表示 是相对位置relative 还是绝对位置absolute
# print(cursor.fetchall())#提取本次查询所有结果
#
#
#
# except Exception as e:
# print(type(e),e)
#
# finally:
# # 无论是否执行成功 最后都需要关闭连接
# cursor.close()#关闭游标
# conn.close()#关闭连接
View Code
登陆 客户端在网页输入用户名密码 浏览器要把接受的数据传给后台服务器
再由后台服务器 交给数据库服务器
可能会遇到sql注入攻击
什么sql注入攻击 一些不法分子可能会在输入的数据中 添加一系列sql语句 来跳过认证环节
甚至直接删除数据
解决方案
1.在客户端接受数据时 做一个re的判断 如果包含sql相关的字符 就直接报错
2.在服务器收到某一个客户端 发送的数据时 做一个判断
其实pymysql 已经封装好了相关的判断逻辑 只要将参数交给pymysql来拼接即可
import pymysql
# 1.conn是一个表示连接的对象
conn = pymysql.connect(
host="127.0.0.1",
port = 3306,
user = "root",
password = "123321",
database = "db4")
print("连接服务器成功!")
#登录
name = input(">>>>用户名:").strip()
password = input(">>>>密码:").strip()
sql = "select *from user where name = %s and password = %s"
print(sql)
# "select *from user where name = ''
cursor = conn.cursor(pymysql.cursors.DictCursor)
count = cursor.execute(sql,(name,password)) # 解决注入攻击的关键在此
if count:
print("登录成功!")
else:
print("登录失败!")
pymysql登陆
pymysql 使用
1.用pymysql.connect(参数) 建立连接 得到连接对象
2.通过连接对象 拿到游标 conn.cursor(pymysql.cursor.Dictcursor)
3.通过调用游标对象的 excute 或者excutemany来执行sql
4.调用有标的fetch(one/many/all)相关函数 来提取执行结果
强调:pymysql默认不会提交修改 需要手动调用conn,commit 或是在创建时指定自动提交
scroll 移动游标 不常用
import pymysql
# 1.conn是一个表示连接的对象
conn = pymysql.connect(
host="127.0.0.1",
port = 3306,
user = "root",
password = "123321",
database = "db4",
autocommit = True)
print("连接服务器成功!")
# 增加数据
# sql = "insert into user values(null,'tom','123')"
# 删除
#sql = "delete from user where name = 'tom'"
# 更新
# sql = "update user set id = 10000 where id = 1"
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 返回的是 本次sql语句执行后 受到影响行数
# count = cursor.execute(sql)
# 用来批量添加数据 可提高效率
count = cursor.executemany("insert into user values (null,%s,%s)",[("tom1","123"),("tom2","123"),("tom3","321")])
if count:
print("修改成功!")
else:
print("修改失败!")
# 需要调用commit来提交修改 或者在创建连接时 指定自动提交修改
# conn.commit()
