一. 补充子查询 (内查询) ****
将一个查询的结果作为下一个查询的条件 或者原始数据
有啥用? 当你的需求一次查询无法满足需求时(一次select找不到你要的数据)
子查询能干的事 多表查询也能干
1. 查看财务部的人数的信息
select id from dept where name ="财务" >> 先拿到id号
select * from emp where dept_id = (select id from dept where name ="财务")
2. in 关键字 : 平均年龄大于25部门的名称
select dept_id,avg(age) from emp group by dept_id having avg(age)>25 ; >>>>拿到部门 id
select name from dept where id in (select dept_id from emp group by dept_id having avg(age)>25)
3. exists 关键字: 子查询的结果为 Ture(外层执行) or false(外层不执行)
select (exists (select * from emp where salary > 1000)); >>>>0
False 用 0 表示, Ture 用 1 表示
4. 查询每个部门的最高工资的员工
先查询每个部门的最高工资
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 = dept .id
where emp.salary = t2.maxs;
二. 正则表达式匹配 ***(了解)
1. select * from emp where name like "刘%";
select * from emp where name regexp "司.*岗$"; 所有有刘的字段信息
三. mysql用户管理 ****
mysql是一个tcp的服务器 用于服务器上的文件数据
接收用户端发送的指令,接收指令时需要考虑安全问题 --->用户管理认证(mysql把文件成为表)---->
1.查看数据库中的关于用户的文件:
show databases; -----> mysql ----> show tables;
在mysql自带的mysql数据库中有4个表用于管理用户的:(权限越来越小,user 权限最高)
1. user
2.db
3.tables_priv 一开始看不到任何信息
4.columns_priv 一开始看不到任何信息
2. 创建用户的语句:
create user 用户名@"主机地址" identited by "密码"
eg : create user jxl @"127.0.0.1" identified by "123"
注意: 主机地址 不是服务器的地址 指的是这个账号可以在哪台电脑上登录
3. 授权的语句(*******更好):
语法: grant [权限的名称 select insert.....|all ] on (mydb.* | *.* ) to 用户名@"主机地址";
需求:
1. 授予scote 这个用户所有的权限 在所有的数据库所有表中
1.grant all on *.* scote@"localhost"
2. 刷新权限: flush privileges;
特点: 如果创建时 用户不存在 直接自动创建用户
grant [权限的名称 select insert.....|all ] on (mydb.* | *.*) to 用户名@"主机地址"
eg: grant all on mysql.* to testDBA@"%" identified by "123"
登录:
mysql -utestDBA -P123 -h("服务器主机的IP地址")
举例:
1. grant all on *.* to scote @"localhost" identtified by "123";
注释: 该语句中的all也不包括grant权限 *.*表示哦任何数据库的任何表 存储在user表中
2. grant all on day41.* to scote @"localhost"identtified by "123";
注释: day41.* 该用户可以操作day41数据库的任何表 存储在 day41表中
3. grant all on day41.stu to scote @"localhost" identtified by "123";
注释: day41.stu 该用户可以操作数据库的stu表 存储在 table_priv 表
4. grant select(id ,name ),insert (id,name) on day41.stu to scote @"localhost" identtified by "123";
注释: 该用户只能在 day41 下 查询和添加 stu 表
2. 将这个用户的权限授予别人
grant [权限的名称 select insert.....|all ] on mydb.* | *.* to 用户名@"主机地址" with grant option;
eg : grant all on *.* to tom.@"localhost" identtified by "123" with grant option
update user set Grant_priv ="N" where uaer = "scote" and host="localhost" :
4.删除(收回)权限:
revoke 权限的名称 on 数据库.表名 from 用户名@"主机名"
revoke all on *.* from scote@"localhost"
revoke all privileges [column] on db.table from user@"host";
5. 删除用户:
drop user@"host"
6. 刷新语句:
flush privileges;
四.基于Python对mysql的使用(pymysql模块)
"""
使用模块的步骤:
1. 连接到数据库
2. 获取游标对象 : 用于发送和接收数据 cursor
3. 用游标对象执行sql语句
4. 使用fetch方法来获取执行的结果
5. 关闭连接 先关闭游标 再关闭连接
游标的常用方法:
1.创建游标 conn.curson(指定查询结果的数据类型)
2. excute 执行sql
3. fetchone(当sql只有一条数据) may (sql有多条数据) all
4. scroll 用于修改游标的当前位置
注意: pymysql a默认不提交修改 但是注意()
"""
#创建连接得到一个连接对象
conn = pymysql.Connect(
host="127.0.0.1", #主机地址
user="root", #用户名
password = "admin", #密码
database = "mydb", #数据库名称
port =3306, #端口号 可选
charset='utf8', #编码
)
#获取游标对象 pymysql.cursors.DictCursor 指定返回的结果类型为字典 默认是元祖类型
cursor=conn.cursor(pymysql.cursors.DictCursor)
#查询语句:
sql = "select *from emp "
# sql1 = "delete from emp where id =2 "
#执行sql 如果是select语句 返回的是 查询的条数
res= cursor.execute(sql)
# print(res)
#获取查询的结果
print(cursor.fetchall() ) #所有
# curson.fetchmany(2) #多个
# curson.fetchone() #一个 类似于迭代循环获取
# scroll
# res1 = curson.scroll(1)
#关闭连接
cursor.close()
conn.close()
补充:commit(提交修改)/roback(回滚)
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()
