MySQL用户管理

MySQL的用户主要包括普通用户和root用户。root用户权限包括创建用户、删除用户和修改普通用户密码等管理权限。而普通用户只拥有创建该用户时赋予它的权限。

权限表

安装MySQL时会自动安装一个名为mysql的数据库。数据库下面存储的都是权限表。用户登录以后,MySQL数据库会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要是user表、db表和host表。

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

user表

  1. 用户列
    user表的用户列包括host|user|Password,分别表示主机名、用户名和密码。用户登录首先要判断着三个字段。修改用户密码实际就是修改user表的password字段。
  2. 权限列
    user表的权限列包括Select_priv,Insert_Priv等以priv结尾的字段。这些字段决定了用户的权限,其中包括查询、修改等普通权限,也包括饿了关闭服务的权限、超级权限和加载用户等高级管理权限。
    普通权限用于操作数据库。高级管理权限用于对数据库进行管理。
    这些字段值只有Y和N,Y表示该权限可以用到所有数据库上,N表示不能用到所有数据库。默认为N。
  3. 安全列
    user表的安全列只有4个字段,分别是ssl_type、ssl_cipher、X509_issur和X509_subject。ssl用于加密,x509用来标识用户。
  4. 资源控制列
    user表的资源控制列是max_questions、max_updates、max_connections、max_user_connections。max_questions和max_updates分别规定每小时可以允许执行多少次查询和更新;max_connections规定每小时可以建立多少链接;max_user_connections规定单个用户可以同时具有的连接数。默认值为0,表示没有限制。

db表和host表

db表和host表也是MySQL中重要的权限表。db表中存储了某个用户对一个数据库的权限。

  1. 用户列
    db表有三个字段:host、db和user。表示主机名、数据库名和用户名。
    host表有两个字段:host和db。
    host表示db表的扩展。
  2. 权限列
    db表和host表的权限列几乎一样,只是db表中多了一个create_routine_priv字段和alter_routine_priv字段。它决定用户是否具有创建和修改存储的权限。
    user表中的权限是针对所有数据库。如果user表中的select_priv字段取值Y,那么该用户可以查询所有数据库中的表。由此可知,用户先根据user表的内容获取权限,然后再根据db表的内容获取权限。

tables_priv表和columns_priv表

在MySQL中权限分配是按照user表、db表、tables_priv表和columns_priv表的顺序进行分配的。

tables_priv表对单个表权限进行限制;columns_priv表对单个数据列进行权限限制。tables_priv表包含8个字段,host、db、user、table_name、table_priv、column_priv、timestamp\、grantor.columns_priv表包括7个字段,是host、db、user、table_name、Column_name和Column_priv和timestamp。

procs_priv表

procs_priv表对存储过程和存储函数进行权限设置。

procs_priv表包含8个字段,分别是Host、DB、User、Routine_name、Routine_type、Routine_priv、timestamp、grantor。routine_name字段表示存储过程或函数的名称。Routine_type表示存储的类型。该字段有两个值,function(函数)和procdeure(存储过程)。proc_priv字段表示拥有的权限。权限分为三类,分别为execute、alter routine、grant。timestamp存储更行时间、grantor字段存储谁设置的权限。

账户管理

账户管理是MySQL用户管理的最基本的内容。账户管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。通过账户管理,可以保证MySQL数据库的安全性。

登录和退出MySQL服务器

mysql -h hostname|IP -P prot -u username -p Database -e "SQL语句"
\\在shell中执行sql命令,完成后自动退出
sujx@TC8304:~$ mysql -h 192.168.31.25 -P 3306 -u sujx -p sujx -e "show tables;"
Enter password: 
+----------------+
| Tables_in_sujx |
+----------------+
| department     |
| employee       |
| product        |
| t1             |
+----------------+
\\使用指定用户登录制定数据库
sujx@TC8304:~$ mysql -h 192.168.31.25 -P 3306 -u sujx -p sujx 
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+----------------+
| Tables_in_sujx |
+----------------+
| department     |
| employee       |
| product        |
| t1             |
+----------------+
4 rows in set (0.00 sec)

新建普通用户

  1. 使用create user来创建用户;
mysql> create user 'test1'@'localhost' identified by 'test1';
  1. 使用insert语句来新建用户;
mysql> insert into mysql.user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject) values('localhost','test1','test1','','','');
Query OK, 1 row affected (0.00 sec)

tips:从5.7版本开始mysql.user表中密码字段不再是password,而是authentication_string
3. 使用grant来新建普通用户;

mysql> grant select on sujx.* to 'test2'@'localhost' identified by 'test2';

删除普通用户

  1. 使用drop user来删除用户;
mysql> drop user 'test1'@'localhost';
Query OK, 0 rows affected (0.00 sec)
  1. 使用delete来删除
    可以使用delete直接从mysql.user表中删除用户信息。

注意,要使用 flushprivileges 来刷新权限。

root修改密码

  1. 使用mysqladmin命令来修改root用户密码
sujx@TC8304:~$ mysqladmin -u root -p password "*******"
Enter password: **********
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
  1. 修改mysql.user表
update mysql.user set authentication_string='******' where user='root';
  1. 使用set语句来修改root用户密码
mysql> set password=password("******");
Query OK, 0 rows affected, 1 warning (0.00 sec)

修改普通用户密码

  1. 使用set语句
  2. 直接修改mysql.user表
  3. 用grant语句来修改

root用户密码丢失

  1. 使用--skip-grant-tables选项启动MySQL;
sujx@TC8304:~$ sudo service mysql stop
sujx@TC8304:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 
\\在[mysqld]字段下增加:skip-grant-tables
sujx@TC8304:~$ sudo service mysql start
sujx@TC8304:~$ mysql
  1. 无密码登陆root用户,必须使用update修改密码;
update user set authentication_string=password('******') where user='root' and host='localhost';
  1. 加载权限表
  2. 去除配置文件中过的skip_grant_tables项

权限管理

  1. 授权
mysql> grant all privileges on sujx.* to 'sujx'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'sujx';
+------------------------------------------------+
| Grants for sujx@%                              |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'sujx'@'%'               |
| GRANT ALL PRIVILEGES ON `sujx`.* TO 'sujx'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)
  1. 收回
mysql> revoke all privileges, grant option from 'sujx';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'sujx';
+----------------------------------+
| Grants for sujx@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'sujx'@'%' |
+----------------------------------+
1 row in set (0.00 sec)
  1. 查看
mysql> show grants for 'sujx';
+------------------------------------------------+
| Grants for sujx@%                              |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'sujx'@'%'               |
| GRANT ALL PRIVILEGES ON `sujx`.* TO 'sujx'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)

MySQL运算符

运算符是用来连接表达式中各个操作数的符号,其作用是用来指明对操作数所进行的运算。MySQL数据库支持使用4种运算符,分别是算术运算符、比较运算符、逻辑运算符和位运算符。

运算符简介
算术运算符包括加减乘除和余这几种,主要用于数值计算
比较运算符包括>、<、=、!=、is null运算符,主要用于数值比较、字符串匹配
逻辑运算符包括与或非和异或等逻辑运算,运算结果只返回真假。
位运算符包括按位与、按位或、按位取反、按位异或、按位左移和按位右移。这些运算必须先变数值为二进制,然后在二进制数上操作。

算术运算符

\\创建一个字段为a值为24的t1表
mysql> create table t1(a int not null primary key);
mysql> insert into t1 values(24);
\\执行加减乘运算
mysql> select a,a+5+2,a-5-2,a*5*2 from t1;
+----+-------+-------+-------+
| a  | a+5+2 | a-5-2 | a*5*2 |
+----+-------+-------+-------+
| 24 |    31 |    17 |   240 |
+----+-------+-------+-------+
\\除运算和取余运算
mysql> select a,a/3,a div 3,a%3,mod(a,3) from t1;
+----+--------+---------+------+----------+
| a  | a/3    | a div 3 | a%3  | mod(a,3) |
+----+--------+---------+------+----------+
| 24 | 8.0000 |       8 |    0 |        0 |
+----+--------+---------+------+----------+

比较运算符

当使用select语句进行查询时,MySQL允许用户对表达式的左边操作数和右边操作数进行比较,结果为真,则返回1,为假则返回0,不确定则返回null.

符号形式作用
=x1=x2判断x1是否和x2相等
<>或!=x1<>x2/x1!=x2判断x1是否和x2不等
<=>x1<=>x2判断x1是否等于x2
>x1>x2判断x1是否大于x2
>=x1>=x2判断x1是否大于等于x2
<x1<x2判断x1是否小于x2
<=x1<=x2判断x1是否大于等于x2
is nullx1 is null判断x1是否等于null
is not nullx1 is not null判断x1是否不等于null
between andx1 BET判断x1是否在前后值之间
inx1 in(a,b,c,d,e,f)判断x1是否在列表之内
likex1 like 表达式判断x1是否与表达式匹配
regexpx1 regexp 正则表达式判断x1是否与正则表达式匹配
mysql> select 1=0,1=1,null=null;
+-----+-----+-----------+
| 1=0 | 1=1 | null=null |
+-----+-----+-----------+
|   0 |   1 |      NULL |
+-----+-----+-----------+
1 row in set (0.00 sec)

逻辑运算符

逻辑运算符又被称为布尔运算符,返回的结果只有1和0.

符号名称符号名称
&&/and!/NOT
II/ORXOR异或
mysql> select not 0,not 1,not null;
+-------+-------+----------+
| not 0 | not 1 | not null |
+-------+-------+----------+
|     1 |     0 |     NULL |
+-------+-------+----------+
mysql> select (1 and 1),(0 and 1),(3 and 1),(1 and null);
+-----------+-----------+-----------+--------------+
| (1 and 1) | (0 and 1) | (3 and 1) | (1 and null) |
+-----------+-----------+-----------+--------------+
|         1 |         0 |         1 |         NULL |
+-----------+-----------+-----------+--------------+

位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数,然后进行位运算,最后再将计算结果从二进制数变回十进制数。

符号名称符号名称
&按位与^按位异或
I按位或<<按位左移
~按位取反>>按位右移

tips:I替代了|,II替代||

mysql> select 2&3;
+-----+
| 2&3 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

MySQL的增删改

数据库通过插入、更新和删除等方式来改变表中的记录。插入数据是向表中插入新的记录,通过insert语句实现。更新数据是改变表中已经存在的数据,使用update语句来实现。删除数据是删除表中不再使用的数据,通过delete语句来实现。

插入数据

mysql> create table product(id int(10) primary key not  null, name varchar(20) not null, function varchar(50), company varchar(20) not null, address varchar(50));
\\为所有字段插入数据
mysql> insert into product values(1001,'ABC药物','治疗感冒','ABC制药厂','北京市昌平区十三陵');
\\列出所有字段
mysql> insert into product(id,name,function,company,address) values(1002,'BCD','治.头疼 ','BCD制药厂','北京市海淀区蓝靛厂');
\\变更顺序,不影响输入
mysql> insert into product(id,function,name,address,company) values(1003,'治疗癌症','AB康 复丸','北京市顺义区牛栏山','AB康复制药厂');
\\为表的指定字段插入数据
mysql> insert into product(id,name,company) values(1004,'EF咳嗽灵','EF健康公司');
mysql> insert into product(id,company,name) values(1005,'北京市制药厂','OK创可贴');
\\同时插入多条数据
mysql> insert into product values (1006,'头疼灵1号','治疗头疼','廊坊大厂制药厂','河北省廊 坊市大厂县'),  (1007,'头疼灵2号','延缓头疼','廊坊香河制药厂','河北省廊坊市香河县'),  (1008,'头疼灵3号','镇痛安眠','廊坊三河制药厂','河北省廊坊市三河县');
mysql> select * from product;
+------+---------------+--------------+-----------------------+-----------------------------+
| id   | name          | function     | company               | address                     |
+------+---------------+--------------+-----------------------+-----------------------------+
| 1001 | ABC药物       | 治疗感冒     | ABC制药厂             | 北京市昌平区十三陵          |
| 1002 | BCD           | 治疗头疼     | BCD制药厂             | 北京市海淀区蓝靛厂          |
| 1003 | AB康复丸      | 治疗癌症     | AB康复制药厂          | 北京市顺义区牛栏山          |
| 1004 | EF咳嗽灵      | NULL         | EF健康公司            | NULL                        |
| 1005 | OK创可贴      | NULL         | 北京市制药厂          | NULL                        |
| 1006 | 头疼灵1号     | 治疗头疼     | 廊坊大厂制药厂        | 河北省廊坊市大厂县          |
| 1007 | 头疼灵2号     | 延缓头疼     | 廊坊香河制药厂        | 河北省廊坊市香河县          |
| 1008 | 头疼灵3号     | 镇痛安眠     | 廊坊三河制药厂        | 河北省廊坊市三河县          |
+------+---------------+--------------+-----------------------+-----------------------------+

更新数据

修改单行数据
mysql> update product set function='治疗咽炎、咳嗽',address='北京市通州区马驹桥' where id=1004;
\\同时修改多个数据
mysql> update product set address=='河北省廊坊市香河县燕郊镇',company='廊坊制药厂' where id>1005 and id<=1008;
mysql> select * from product;
+------+---------------+-----------------------+--------------------+--------------------------------------+
| id   | name          | function              | company            | address                              |
+------+---------------+-----------------------+--------------------+--------------------------------------+
| 1001 | ABC药物       | 治疗感冒              | ABC制药厂          | 北京市昌平区十三陵                   |
| 1002 | BCD           | 治疗头疼              | BCD制药厂          | 北京市海淀区蓝靛厂                   |
| 1003 | AB康复丸      | 治疗癌症              | AB康复制药厂       | 北京市顺义区牛栏山                   |
| 1004 | EF咳嗽灵      | 治疗咽炎、咳嗽        | EF健康公司         | 北京市通州区马驹桥                   |
| 1005 | OK创可贴      | NULL                  | 北京市制药厂       | NULL                                 |
| 1006 | 头疼灵1号     | 治疗头疼              | 廊坊制药厂         | 河北省廊坊市香河县燕郊镇             |
| 1007 | 头疼灵2号     | 延缓头疼              | 廊坊制药厂         | 河北省廊坊市香河县燕郊镇             |
| 1008 | 头疼灵3号     | 镇痛安眠              | 廊坊制药厂         | 河北省廊坊市香河县燕郊镇             |
+------+---------------+-----------------------+--------------------+--------------------------------------+
8 rows in set (0.00 sec)

删除数据

mysql> delete from product where id=1005;
\\默认删除所有行数据
mysql> delete from product;

MySQL的触发器

触发器(Trigger)是由事件来触发某个操作。当数据库系统执行这些事件时,就会激活触发器执行相应的操作。

创建触发器

创建只有一个执行语句的触发器

\\create trigger 触发器名 before|after 触发事件 on 表名
\\      for each row 执行语句
mysql> create trigger user_trig1 before insert on user for each row insert into  trigger_time values(now());

mysql> insert into user(id,name,num,phone,address,age,sex) values(2,'su bowen',2,1861225,'tongzhou',4,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+----------+------+---------+----------+-----+------+
| id | name        | num  | phone   | address  | age | sex  |
+----+----------+------+---------+----------+-----+------+
|  1 | su jingxuan |    1 | 1861109 | beijing  |  35 |    1 |
|  2 | su bowen    |    2 | 1861225 | tongzhou |   4 |    1 |
mysql> select * from trigger_time;
+---------------------+
| exec_time           |
+---------------------+
| 2017-08-13 16:22:14 |
+---------------------+

创建有多个执行语句的触发器

一般情况下,MySQL默认使用;作为结束执行语句。在创建触发器过程中需要用到;。为了解决这个问题可以用DELIMITER语句。如"delimiter&&",可以将结束符号变成&&。当触发器创建完成之后,可以用命令delimiter;来将结束符号还原。

mysql> delimiter&&
mysql> create trigger user_trig2 after delete on user for each row begin insert into trigger_time values('21:00:01'); insert into trigger_time values('22:00:02'); end&&
mysql>delimiter ;
mysql> create table trigger_time(exec_time time);
mysql> delete from user where id=2;
mysql> select * from trigger_time;
+-----------+
| exec_time |
+-----------+
| 21:00:01  |
| 22:00:02  |
+-----------+
mysql> select * from user;                                                            
+----+-------------+------+---------+---------+-----+------+
| id | name        | num  | phone   | address | age | sex  |
+----+-------------+------+---------+---------+-----+------+
|  1 | su jingxuan |    1 | 1861109 | beijing |  35 |    1 |
+----+-------------+------+---------+---------+-----+------+

查看触发器

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: user_trig1
               Event: INSERT
               Table: user
           Statement: insert into  trigger_time values(now())
              Timing: BEFORE
             Created: 2017-08-13 16:20:33.95
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: sujx@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: user_trig2
               Event: DELETE
               Table: user
           Statement: begin
insert into trigger_time values('21:00:01');
insert into trigger_time values('22:00:02');
end
              Timing: AFTER
             Created: 2017-08-13 16:30:24.44
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: sujx@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)
\\可以在trigger表中查看触发器信息
mysql> select * from information_schema.triggers\G

触发器的使用

在MySQL中,触发器执行的顺序是before触发器、表操作、after触发器。

删除触发器

mysql> drop trigger user_trig1;

MySQL的视图

视图是从过一个或者多个表中导出来的表,是一种虚拟存在的表。试图可以使用用户的操作更方便,并且可以保障数据库系统的安全。

简介

视图可以从已经存在的视图基础上定义。数据库中存放了视图的定义,而并没有视图中的数据。使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。视图的数据以来于原来表中的数据。
MySQL的视图不支持输入参数的功能。

作用

  1. 操作简单化
  2. 增加数据的安全性
  3. 提高表的逻辑独立性

创建视图

创建视图是指在已存在的数据库表上建立视图。视图可以建立在一张表中,也可以建立在多张表中。
创建视图时,需要有create view的权限。同时,应该具有查询设计的列的select权限。在MySQL数据库下面的user表中保存这些权限的信息,可以使用select查询。
algorithem,表示试图选择的算法,包括undefined、merge和temptable。undefined表示自动选择算法,merge表示合并,temptable表示存入临时表。
cascaded是可选参数,表示更新试图要满足所有视图和表的条件,为默认值。local表示更新视图时,要满足该试图本身的条件即可。

\\在已有表上创建视图user_view1
mysql> create view user_view1 as select * from user;
mysql> desc user;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  | UNI | NULL    |       |
| num     | int(8)      | YES  |     | NULL    |       |
| phone   | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | NO   | MUL | NULL    |       |
| age     | int(4)      | NO   |     | NULL    |       |
| sex     | tinyint(1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
mysql> desc user_view1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| num     | int(8)      | YES  |     | NULL    |       |
| phone   | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | NO   |     | NULL    |       |
| age     | int(4)      | NO   |     | NULL    |       |
| sex     | tinyint(1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
\\两者显示出来的表的结果是一样的
\\在user表中创建只包含姓名、性别、年龄的视图
mysql> create view user_view2(name,sex,age) as select name,sex,age from user;
mysql> desc user_view2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
\\在多个表中建立跨表的视图
mysql> create algorithm=merge view stu_view( name,age,sex,stu_id,grade) as select name,age,sex,stu_id,grade from user,example2 where user.id = example2.id with local check option;
mysql> desc stu_view;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(30) | YES  |     | NULL    |       |
| age    | int(4)      | NO   |     | NULL    |       |
| sex    | tinyint(1)  | YES  |     | NULL    |       |
| stu_id | int(11)     | NO   |     | NULL    |       |
| grade  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

查看视图

查看视图是指查看数据库中已存在的视图的定义。查看视图必须要有show view的权限。

\\使用describe语句
mysql> desc stu_view;
\\使用show table status语句查看视图基本信息,注意like参数和单引号''
\\stu_view是虚拟表,所以属性值大多为空
mysql> show table status like 'stu_view'\G
*********** 1. row ********
           Name: stu_view
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)
\\使用show create view查看视图详细信息
mysql> show create view stu_view\G
*************** 1. row **************
                View: stu_view
         Create View: CREATE ALGORITHM=MERGE DEFINER=`sujx`@`%` SQL SECURITY DEFINER VIEW `stu_view` AS select `user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`sex` AS `sex`,`example2`.`stu_id` AS `stu_id`,`example2`.`grade` AS `grade` from (`user` join `example2`) where (`user`.`id` = `example2`.`id`) WITH LOCAL CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
\\在系统views表中查看视图详细信息
\\在MySQL中,所有视图的定义都存在information_schema数据库下的views表中。
mysql> select * from information_schema.views\G
*************** 1. row **************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sujx
          TABLE_NAME: stu_view
     VIEW_DEFINITION: select `sujx`.`user`.`name` AS `name`,`sujx`.`user`.`age` AS `age`,`sujx`.`user`.`sex` AS `sex`,`sujx`.`example2`.`stu_id` AS `stu_id`,`sujx`.`example2`.`grade` AS `grade` from `sujx`.`user` join `sujx`.`example2` where (`sujx`.`user`.`id` = `sujx`.`example2`.`id`)
        CHECK_OPTION: LOCAL
        IS_UPDATABLE: YES
             DEFINER: sujx@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
**************** 2. row ***************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sujx
          TABLE_NAME: user_view1
     VIEW_DEFINITION: select `sujx`.`user`.`id` AS `id`,`sujx`.`user`.`name` AS `name`,`sujx`.`user`.`num` AS `num`,`sujx`.`user`.`phone` AS `phone`,`sujx`.`user`.`address` AS `address`,`sujx`.`user`.`age` AS `age`,`sujx`.`user`.`sex` AS `sex` from `sujx`.`user`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: sujx@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
************** 3. row **************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sujx
          TABLE_NAME: user_view2
     VIEW_DEFINITION: select `sujx`.`user`.`name` AS `name`,`sujx`.`user`.`sex` AS `sex`,`sujx`.`user`.`age` AS `age` from `sujx`.`user`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: sujx@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
3 rows in set (0.01 sec)

修改视图

修改视图是指修改数据库中存在的表的定义。

\\使用create or replace view语句修改视图
mysql> create or replace algorithm=temptable view stu_view(name,sex,grade) as select name,sex,grade from user,example2 where user.id = example2.id;
Query OK, 0 rows affected (0.01 sec)

mysql> desc stu_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| grade | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
\\alter语句修改视图
mysql> alter view stu_view(stu_id,name,sex,grade) as select stu_id,name,sex,grade from user,example2 where user.id=example2.id;
mysql> desc stu_view;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stu_id | int(11)     | NO   |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| sex    | tinyint(1)  | YES  |     | NULL    |       |
| grade  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

更新视图

更新视图是指通过视图来插入、更新和删除表中的数据。因为视图是一个虚拟表,其中没有数据。通过视图来更新时,都是转换到基本表来更新。更新视图时,只能更新权限范围内的数据。超过范围,就不能更新。

删除视图

mysql> drop view if exists stu_view;