创建用户与授权

亦凉 2024-03-26 18:19 50阅读 0赞

#  作业
    
    #(2)查询没有学过“张三”老师课的同学的学号、姓名
    /*
     #5.查询学过“张三”老师课的同学的学号、姓名(student)
     select so.sid,s.sname
     from sorce as so
     inner join student as s  on so.sid = s.sid
     inner join course as c on so.cid = c.cid
     inner join teacher as t  on t.tid = c.tid
     where t.tname="张红";
    */ 
    select s.sid,s.sname
    from student as s where s.sid not in (
    select  so.sid
    from sorce as so
    inner join student as s on so.sid = s.sid
    inner join course as c on so.cid = c.cid
    inner join teacher as t  on t.tid = c.tid
    where t.tname ="张红"
    );
    
    #(3)查询(课程成绩小于60分的同学的)学号、姓名
    select sid, sname
    from student
    where sid in (
     select sid from sorce where score <60
    );
    #(4)查询工资最低的员工信息
    select * from employee where salary =(select min(salary) from employee);
    #(5)查询(平均工资最低)的部门信息
    select id,dname from depart where id =(
      select department_id from employee group by department_id
      order by avg(salary) asc 
      limit 0,1
    );
    # 方法二
    select d.* from employee as e 
    inner join depart as d on d.id = e.department_id
    group by department_id order by avg(salary) asc limit 0,1
    
    # eg1:from后面：支持表子查询
    #(a) 查询平均工资最低的部门信息和该部门的平均工资
    select id,dname ,new.avg as 平均工资 from depart as d
    inner join (select department_id,avg(salary) as avg from employee group by 
    department_id order by avg asc limit 0,1) as new on d.id = new.department_id;
    #(选)
    select avg(salary) as avg ,d.* from employee as e 
    inner join depart as d on d.id = e.department_id
    group by department_id order by avg asc limit 0,1
    #(b) :查询'1'课程比'2'课程成绩高的所有学生的学号
    select a.sid,a.score,b.score
    from (select sid,score from sorce where cid=1) as a
    inner join (select sid,score from sorce where cid=2) as b
    on a.sid =b.sid 
    where a.score > b.score;
    # eg2:select后面：仅仅支持标量子查询
    # 查询每个部门的员工个数及部门所有信息
    select count(e.department_id) as 人数,d.id,d.dname
    from employee as e
    right join depart as d on e.department_id = d.id
    group by department_id
    # 了解
    select count(*) from employee as e where e.department_id = 1;
    select (select count(*) from employee as e where e.department_id =d.id) as 人数 ,d.*
    from depart as d;
    
    # DCL: grant授权, revoke 撤销授权
    /*
    (1)grant 权限 on 数据库名.表名 to 用户名@登陆方式 【identified by 'password1'】;
    
    #案例1：root 中
              #（1）创建用户
    		  # CREATE USER 'jerry'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; 8.0
               CREATE USER 'jerry'@'localhost' IDENTIFIED BY '123456';
              #（2）赋予权限  all - select,insert...
               GRANT ALL ON java1.* TO 'jerry'@'localhost'
              # （3）查看用户权限
                SHOW GRANTS FOR jerry@localhost;
              # (4) 在 root 用户下查看所有 有哪些用户
                SELECT User, Host FROM mysql.user;
    			
    # 案例2 ： 更新用户密码（知道旧密码）
             
              use mysql； # 进入 mysql 这数据库中（注意）
    		  #ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456' 8.0 要写
              ALTER USER 'root'@'localhost' IDENTIFIED  BY '123456';
              FLUSH PRIVILEGES;
    (2)revoke 权限 on 数据库名.表名 from 用户名@登陆方式；
    
    # 案例3：REVOKE  ALL ON java1.* from jerry@localhost;
    */
    # eg1. 
    # a.创建 jerry 的用户
    create user 'jerry'@'localhost' identified by '123456';
    # b.  授权 j01数据下的所有表的所有命令权限； *.* 所有数据的所有表的所有命令权限
    grant all on j01.* to 'jerry'@localhost;
    # c.查看用户权限
    show grants for jerry@localhost;
    # d.  在 root 用户下查看所有 有哪些用户
    select user,host from mysql.user;
    
    # eg2.更新用户密码（知道旧密码）
    use mysql;
    alter user 'root'@localhost identified by '123456';
    flush privileges; # 刷新权限
    # 重新登录root
    
    # eg3 revoke 撤销权限
    revoke all on j01.* from jerry@localhost;