day 37

一. 补充子查询 (内查询) ****
    将一个查询的结果作为下一个查询的条件 或者原始数据
    有啥用?   当你的需求一次查询无法满足需求时(一次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()