⽤户管理和视图

1、⽤户管理

1. 查询当前所有的⽤户信息

2. select * from mysql.user;

    

3. 其中字段的说明
   1. Host 从哪台主机登录 localhost:表⽰本地登录, %:任何主机的IP。
   2. User :登录服务器的⽤户名。
   3. authentication_string: 登录服务的⽤户名使⽤的密码。
   4. Select_priv 查询权限 Y有 N没有。
   5. insert update delete create drop reload grant alter ...。
4. 创建⼀个普通⽤户 ⽤户名user1 密码123456。

   1. -- 查询当前所有用户信息
      select * from mysql.user;
      show create table mysql.user;
      -- 创建一个普通用户 用户名user1 密码123456
      create user `user1`@localhost identified by '123456';
      -- 刷新权限
      flush privileges;
      -- 修改user1的登录主机为任意IP
      update mysql.user set Host="%" where User='user1';

      -- 创建一个普通用户 user2 密码123456 只能本机登录
      create user user2 identified by '123456';
      create user user3;
      update mysql.user set Host="localhost" where User="user2";

       

5. 权限

   1. -- grant 权限 on 数据库.表 to 用户名@主机 identified by 密码 with grant option
      grant all on *.* to `user2`@`localhost`;
      grant select on *.* to `user3`@`%`;
      grant select on myschool.student to `user1`@`%`;

       

   2. -- 创建一个普通用户xiaoming密码123456可以任意IP登录权限所有权限myschool库中所有表权限
      create user xiaoming identified by '123456';
      grant all on myschool.* to `xiaoming`@`%`;
      grant select on *.* to `xiaoming`@`%` with grant option;
      create user xiaomei identified by '123456';

       

6. 撤销权限：revoke all on *.* from `xiaomei`@`%`; 

7. -- 修改密码：dos命令
   mysqladmin -uxiaoming -p123456 password 123

    

8. 删除用户

   1. -- 删除用户
      drop user `xiaoming`@`%`;
      drop user `xiaomei`@`%`;
      drop user `user3`@`%`;

       

2、视图

1. 概念
   1. 视图是由数据库中的⼀个表或多个表导出的虚拟表，是⼀种虚拟存在的表，⽅便⽤户对数据的操作
   2. 作⽤：权限控制时可以使⽤，简化复杂的查询。

2. -- 视图 
   select * from student;
   select studentno,studentname,sex,gradeid from student;
   -- 创建视图 虚拟表 
   create view view_student as 
   select studentno,studentname,sex,gradeid from student;
   show tables;
   -- 调用视图表 
   select * from view_student;
   -- 删除视图 
   drop view view_student;
   -- 创建视图 查询学生姓名 课程名 成绩 
   create view view_ssr as 
   select stu.studentname,sub.subjectname,r.studentresult
   from student stu 
   inner join result r on stu.studentno=r.studentno
   inner join subject sub on sub.subjectno=r.subjectno;
   select * from view_ssr;
   -- 修改视图 列名为中文 
   alter view view_ssr as 
   select stu.studentname as 姓名,sub.subjectname 课程名,r.studentresult 成绩
   from student stu 
   inner join result r on stu.studentno=r.studentno
   inner join subject sub on sub.subjectno=r.subjectno;
   
   insert into view_ssr values("张三三","高等数学-1",88);
   -- 创建一个people
   create table people(
   id int,
   name varchar(20)
   );
   create view view_people as 
   select id,name from people;
   select * from people;
   select * from view_people;
   insert into view_people value(1,"张三");
   delete from view_people where id=1;