回顾： 1.查询 完整的查询语句 select [distinct] {* | 字段 | 聚合函数 | 表达式} from 表名 where group by 字段 having order by 字段名,字段名 desc | asc limit a | b; 第一个是起始位置 默认是0 ,第二个是 显示的记录数 常用于分页查询
2. 多表查询 inner join 内连接 左右两个表仅显示匹配成功的记录
left join 左表全部显示 右边显示匹配 right join 右表全部显示 左边显示匹配
full join 左右两边 无论是否匹配都全部显示 mysql使用 union间接完成 将left 和right合并
补充子查询： 将一个查询的结果作为下一次查询的条件 或原数据 又称为内查询
有啥用? 当你的需求 一次查询无法满足需求时(一次select找不到你要的数据)
子查询能干的事 多表联查也能干
mysql 用户管理 MYSQL 是一个tcp 服务器 用于操作服务器上的文件数据, 接收用户端发送的指令, 接收指令时需要考虑安全问题 是不是谁给我发我都要相应?
atm 购物车中的用户认证和mysql的用户认证原理是一样的 mysql中把文件称为表
在mysql自带的mysql数据库中有4个表用于用户管理的 分别是: 优先级从高到低 user -> db -> tables_priv -> columns_priv
1.创建用户的语句 create user 用户名@"主机地址" identified by "密码";
create user scote@"127.0.0.1" identified by "123"; 此处的主机地址 不是服务器地址 而是表示 这个账户可以在那台电脑上登录
2. 授权的语句 ******* 语法: grant [权限的名称 select insert.... | all ] on 数据库.表名 to 用户名@主机地址; # 授予 scote 这个用户所有权限 在所有数据库所有表中 grant all on *.* to scote@"localhost"; 可以访问 所有库和表 grant all on day41.* to scote@"localhost"; 可以访问day41库的所有表 grant all on day41.stu to scote@"localhost"; 可以访问day41库的stu表 grant select(id,name),insert(id,name) on day41.stu to scote@"localhost"; 仅能查看和添加 day41库的stu表中的 id和name字段
grant all on mydb1.* to testDBA@"%" identified by "123";
3.grant [权限的名称 select insert.... | all ] on 数据库.表名 to 用户名@主机地址 with grant option; with grant option 这个用户可以将他有的权限授予别的账户 特点: 如果授权时 用户不存在 直接自动创建用户
4.删除权限 revoke 权限的名称 on 数据库.表名 from 用户名@"主机名" ; revoke all on *.* from scote@"localhost";
update mysql.user set Grant_priv = "N" where user ="scote" and host = "localhost";
*.刷新权限表 flush privileges;
5.删除用户 drop user 用户名@"主机地址";
pymysql模块： 如何能在python中去使用数据库存取数据? 使用pymysql模块
如何使用 1.导入
pymysql_test import pymysql
"""pymysql使用步骤 核心类 Connect链接用 和Cursor读写用 1.与数据库服务器建立链接 2.获取游标对象 (用于发送和接收数据) 3.用游标执行sql语句 4.使用fetch方法来获取执行的结果 5.关闭链接 先关游标 再关链接 游标的常用方法 1.创建游标 conn.cursor(指定查询结果的数据类型) 2.excute 执行sql 3.fetchone(当sql只有一条记录时) many(sql有多条并且需要指定条数) all(多条) 4.scroll 用于修改游标的当前位置 注意: pymysql 默认不提交修改 但是注意(指的是对表中记录的操作不提交) 像删库 删表 是无法撤销的 """ # 创建链接得到一个链接对象 conn = pymysql.Connect( host="127.0.0.1", # 数据库服务器主机地址 user="root", # 用户名 password="admin", # 密码 database="day42", #数据库名称 port=3306, # 端口号 可选 整型 charset="utf8" # 编码 可选 ) # 获取游标对象 pymysql.cursors.DictCursor指定 返回的结果类型 为字典 默认是元祖类型 cursor = conn.cursor(pymysql.cursors.DictCursor)
# 查询数据 sql = "select *from emp"
# 执行sql 如果是select 语句返回的是 查询的条数 res = cursor.execute(sql) print(res)
# 获取查询的结果 # print(cursor.fetchall()) # print(cursor.fetchone()) # print(cursor.fetchone()) # print(cursor.fetchmany(1)) # print(cursor.fetchall())
# scroll print(cursor.fetchone()) cursor.scroll(-1) print(cursor.fetchall())
# 关闭链接 cursor.close() conn.close()
commit与roback import pymysql # 创建链接得到一个链接对象 conn = pymysql.Connect( host="127.0.0.1", # 数据库服务器主机地址 user="root", # 用户名 password="admin", # 密码 database="day42", #数据库名称 port=3306, # 端口号 可选 整型 charset="utf8" # 编码 可选 ) # 获取游标对象 pymysql.cursors.DictCursor指定 返回的结果类型 为字典 默认是元祖类型 cursor = conn.cursor(pymysql.cursors.DictCursor)
# # 添加数据 # res = cursor.execute("insert into emp values(100,'胡歌','男',30,1,'job',60000)") # if res: # print("插入成功") # else: # print("插入失败")
# 提交修改 因为pymysql 模块默认是启用事务的 你的sql语句 如果不提交 相当于没有执行
# conn.commit() # res = cursor.execute("drop database day42")
# res = cursor.execute("delete from t1 where id = 1") # print(res)
try: cursor.execute("update moneyTable set money = money - 50 where name = '小明'") #如果小花的账户出问题了 无法更新数据 那就需要回滚 cursor.execute("update moneyTable set money = money + 50 where name = '小花'") conn.commit() except: conn.rollback()
cursor.close() conn.close()
# 小明有100块 准备给小花转50 # update moneyTable set money = money - 50 where name = "小明"; # 发生一些别错误 如果发生了错误 就执行撤销操作 rollback; # update moneyTable set money = money + 50 where name = "小花";
file 上节回顾 单标查询的完整语法 select [distinct] [* | 字段 | 聚合函数| 表达式] from table where group by having distinct order by limit 各关键字的执行顺序 where 与 having的区别? where 关键字支持的运算符? 分组前分组后的区别 limit 0,5 分别代表什么含义? 分页查询 如何实现? concat函数 聚合函数有哪些? 分组与聚合函数 group_concat
多表查询 inner left right full
何时需要多表查询? 表之间如何关联? 有几种关联关系?
今日内容: 子查询 正则表达式匹配 用户管理 pymysql 可视化客户端 sql注入攻击
一 子查询 什么是子查询? 当一个查询是另一个查询的条件时,这个查询称之为子查询(内层查询) 什么时候用? 当查询需求比较复杂,一次性查询无法得到结果,需要多次查询时, 例如:给出一个部门名称,需要获得该部门所有的员工信息 需要先确定部门的id, 然后才能通过id确定员工 解决问题的方式是把一个复杂的问题拆分为若干个简单的问题
如何使用? 首先明确子查询就是一个普通的查询,当一个查询需要作为子查询使用时,用括号包裹即可 案列: 准备数据: create table emp (id int,name char(10),sex char,age int,dept_id int,job char(10),salary double); insert into emp values (1,"刘备","男",26,1,"总监",5800), (2,"张飞","男",24,1,"员工",3000), (3,"关羽","男",30,1,"员工",4000), (4,"孙权","男",25,2,"总监",6000), (10,"刘备2","男",26,2,"总监",5800), (5,"周瑜","男",22,2,"员工",5000), (6,"小乔","女",31,2,"员工",4000), (7,"曹操","男",19,3,"总监",10000), (8,"司马懿","男",24,3,"员工",6000);
create table dept(id int primary key,name char(10)); insert into dept values(1,"市场"),(2,"行政"),(3,"财务");
需求:财务部有哪些人: 数据在两张表中 可以使用链接查询: select *from emp inner join dept on emp.dept_id = dept.id where dept.name = "财务";
子查询方式: 数据在两张表中,先查询那张? emp? 不行 不知道部门名 查dept 第一步 需要知道财务部的id select id from dept where name = "财务"; 第二步 用查询的到的id作为判断条件查询emp select name from emp where dept_id = 3; 3不能写死 是上一个查询的结果 所以直接写在后面 加上括号就变成了子查询 select name from emp where dept_id = (select id from dept where name = "财务");
in 关键字子查询 查询平均年龄大于25的部门名称 1.求每个部门的平均年龄 select avg(age) from emp group by dept_id; 2.筛选结果 select dept_id,avg(age) as a from emp group by dept_id having a > 25; select name from dept where id in (select dept_id from emp group by dept_id having avg(age) > 25);
子查询方式: 平均年龄大于25的部门有哪些? 先要求出每个部门的平年龄!每个表示什么? 分组 select name from dept where id in (select dept_id from emp group by dept_id having avg(age) > 25);
多表查询方式: 先把数据拼接到一起 在加以筛选 select dept.name from emp inner join dept on emp.dept_id = dept.id group by dept.name having avg(age) >25;
exists关键字子查询 exists 后跟子查询 子查询有结果是为True 没有结果时为False 为true时外层执行 为false外层不执行 select *from emp where exists (select *from emp where salary > 1000); select (exists (select *from emp where salary > 10000));
综合练习: 查询每个部门工资最高的员工信息
先查询每个部门的最高工资 select dept_id,max(salary) from emp group by dept_id; select *from emp inner join (select dept_id,max(salary) maxs from emp group by dept_id) as t2 on emp.dept_id = t2.dept_id where emp.salary = t2.maxs;
子查询的语法特点 就是一个select a中 包含另一个select b b 只能位于 a 的where 后面
二 正则表达式匹配 正则表达式用于模糊查询,模糊查询已经讲过了 like 仅支持 % 和 _ 远没有正则表达式灵活 当然绝大多数情况下 like足够使用 语法: insert into emp values(1,"laowangba","男",26,1,"总监",5800); insert into emp values(1,"laoliba","男",26,1,"总监",5800); insert into emp values(1,"laocheng","男",26,1,"总监",5800); select *from table where name regexp "正则表达式";
三 mysql用户管理 !这是dba的活儿!,但是万一公司没有dba? mysql用户指的是什么? 我们每一次在操作前都需要指定账号和密码,这个账号就是mysql的用户; 为什么要管理? 一个公司不可能只有一个工程师,大公司,不不仅有很多工程师 还有很多不同部门,但是数据库服务器只有一个,大家都要访问,这就涉及到用户和权限问题了 一个工程师对应一个账户, 哪些工程师可以操作哪些数据库,哪些表,甚至哪些字段,都可以进行控制,例如,腾讯,有qq和微信不同项目,qq的工程师,就不应该去访问微信项目的数据;
mysql是如何管理的 mysql本质上是一款cs软件,它具备用户认证! 我们有没有做过用户认证呢? ATM! 购物车,都做过, 我们是如何实现的?写入文件,mysql也是一样的, 登录流程简述! 只不过它把文件称为表 那我们需要添加账户,要怎么添加呢? 把用户信息写入表中就可以了, 我们来看看它都把数据放在哪个表中了! 自带的mysql数据库, user db table_priv columns_priv 四个表用于存储账户信息以及权限
权限优先级: user->db->table_priv->columns_priv desc查看结构 内置root账户字段信息解析
创建账号: create user 用户名@"ip地址" "identified" by 密码; create user tom@"192.168.101" identified by "123"; 该语句表面tom只能在101机器上使用,别的机器就无法登录 用%可以表示在任意机器可用 注意:该方式创建的账号没有任何权限 需要使用授权语句
授权: 授权语句执行时如果账号不存在会自动创建账号 所以推荐使用 注意:默认只有root才能为其他账号授权
grant all on *.* to tom@"localhost" identified by "123"; 该语句中的all 也不包括grant权限 *.* 表示任何数据库 任何表 存储在user表
grant all on db.* to tom@"localhost" identified by "123" db.* 该用户可以操作db数据库的任何表 存储在 db表
grant all on db.t1 to tom@"localhost" identified by "123" db.* 该用户可以操作db数据库的t1表 存储在 table_privi表
精确到字段 和 操作级别 grant select(id) on db.t1 to tom@"localhost" identified by "123" 该用户只能查询 db下的t1表
grant all on *.* to tom@"localhost" identified by "123" with grant option; with grant option 表示该账户可以将权限授予其他用户
收回权限 REVOKE all privileges [column] on db.table from user@"host";
删除用户 drop user@"host"
flush privileges; 刷新权限表
四 pymysql 是什么? python编写的mysql客户端 pip install pymysql
无论是什么客户端第一件事干啥? c/s程序 socket 链接服务器 指定相关的参数 获取链接对象 获取游标对象 执行sql语句 获取执行结果 提取数据 fetchone fetchall fetchmany scroll
增删改查 指定查询结果为字典类型 pymysql.cursor.DictCursor
sql 注入 实现用户登录注册 登录时 演示sql注入 如何避免 客户端限制 模拟请求怎么破? 服务器端验证
五 可视化客户端