视图

介绍：

视图是从一个或多个表中导出来的表,它是一种虚拟存在的表,并且表的结构和数据都依赖于基本表。
通过视图不仅可以看到存放在基本表中的数据,并且还可以像操作基本表一样,对视图中存放的数据进行查询、修改和删除。

优点：

与直接操作基本表相比,视图具有以下优点：

  1. 简化查询语句：视图不仅可以简化用户对数据的理解,也可以简化对数据的操作。日常开发中可以将经常使用的查询定义为视图,从而使用户避免大量重复的操作。
  2. 安全性：通过视图用户只能查询和修改他们所能见到的数据,数据库中的其他数据则既看不到也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上,但不能授权到数据库特定行和特定的列上。
  3. 逻辑数据独立性：视图可以帮助用户屏蔽真实表结构变化带来的影响。

简单点讲就是：
●简单：提高了重用性，就像一个函数。
●安全：提高了安全性能，可以针对不同的用户，设定不同的视图。
●数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

视图管理

创建视图的语法格式

CREATE [OR REPLACE] [ALGORITHM] = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS SELECT_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

上述语法格式中,创建视图的语句是由多条子句构成的。下面对语法格式中的每个部分进行详细的解释,具体如下:

  1. CREATE:表示创建视图的关键字,上述语句能创建新的视图。
  2. OR REPLACE:如果给定了此子句,表示该语句能够替换已有视图。
  3. ALGORITHM:可选,表示视图选择的算法。
  4. UNDEFINED:表示MySQL将自动选择所要使用的算法。
  5. MERGE:表示将使用视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分。
  6. TEMPTABLE:表示将视图的结果存人临时表,然后使用临时表执行语句。
  7. view_name:表示要创建的视图名称。
  8. column_list:可选,表示属性清单。指定了视图中各个属性的名,默认情况下，与 SELECT语句中查询的属性相同。
  9. AS:表示指定视图要执行的操作。
  10. SELECT_statement:是一个完整的查询语句,表示从某个表或视图中查出某些满足条件的记录,将这些记录导入视图中。
  11. WITH CHECK OPTION:可选,表示创建视图时要保证在该视图的权限范围之内。
  12. CASCADED:可选,表示创建视图时,需要满足跟该视图有关的所有相关视图和表的条件,该参数为默认值。
  13. LOCAL:可选,表示创建视图时,只要满足该视图本身定义的条件即可。

注意：

  1. 创建视图时要求具有针对视图的CREATE VIEW权限,以及针对由SELECT语句选择的每一列上的某些权限。
  2. 对于在 SELECT 语句中其他地方使用的列,必须具有SELECT权限。
  3. 如果还有OR REPLACE子句,必须在视图上具有 DROP权限。
  4. 视图属于数据库,在默认情况下,将在当前数据库创建新视图,要想在给定数据库中明确创建视图,创建时应将名称指定为db_name. view_name。

创建视图

在单表上创建视图

  1. CREATE VIEW view_name[(column_list)] AS SELECT_statement FROM 表名;
  1. view_name:表示要创建的视图名称。
  2. column_list:可选,表示属性清单。指定了视图中各个属性的名,默认情况下，与 SELECT语句中查询的属性相同。
  3. SELECT_statement:是一个完整的查询语句,表示从某个表或视图中查出某些满足条件的记录,将这些记录导入视图中。

在多表上创建视图

  1. CREATE VIEW view_name[(column_list)] 
  2. AS 
  3. SELECT 表名1.字段名1,表名1.字段名2,表名1.字段名3
  4. FROM 表名1,表名2
  5. WHERE 表名1.字段名1=表名2.字段名1;

查看视图

查看视图,是指查看数据库中已经存在的视图的定义。查看视图必须要有SHOWVIEW的权限。查看视图的方式有三种,具体如下：

使用DESCRIBE语句查看视图

在MySQL中,使用DESCRIBE语句可以查看视图的字段信息,其中包括字段名、字段类型等信息。DESCRIBE语句的基本语法格式如下所示:

  1. DESCRIBE 视图名;
  2. 或
  3. DESC 视图名;
  1. NULL:表示该列是否可以存储NULL值。
  2. Key:表示该列是否已经编制索引。
  3. Default:表示该列是否有默认值。
  4. Extra:表示获取到的与给定列相关的附加信息。


使用 SHOW TABLE STATUS 语句查看视图

“LIKE”表示后面匹配的是子符串,”视图名”表示要查看的视图的名称,视图名称需要使用单引号括起来。

  1. SHOW TABLE STATUS LIKE '视图名';

使用 SHOW CREATE VIEW 语句查看视图

在 MySQL,中,使用SHOW CREATE VIEW语句不仅可以查看创建视图时的定义语句,还可以查看视图的字符编码,SHOW CREATE VIEW语句的基本语法格式如下所示:

  1. SHOW CREATE VIEW 视图名;

修改视图

所谓修改视图是指修改数据库中存在的视图的定义,比如,当基本表中的某些字段发生变化时,可以通过修改视图的方式来保持视图与基本表的一致性。在 MySQL中,修改视图的方式有两种,具体如下:

注意：

有下列内容之一，视图不能做修改
●select子句中包含distinct
●select字句中包含组函数
●select语句中包含group by子句
●selecy语句红包含order by子句
●where子句中包含相关子查询
●from字句中包含多个表
●如果视图中有计算列，则不能更新
●如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作。

使用 CREATE OR REPLACE VIEW 语句修改视图

在使用CREATE OR REPLACE VIEW语句修改视图时,如果修改的视图存在,那么将使用修改语句对视图进行修改,如果视图不存在,那么将创建一个视图。

  1. CREATE[OR REPLACE ] [ALGORITHM = (UNDEFINED | MERGE | TEMPTABLE}]
  2. JVIEW view_name [(column_ list)]
  3. AS SELECT_statement
  4. [WITH[CASCADED | LOCAL] CHECK OPTION]

使用 ALTER 语句修改视图

  1. ALTER [ALGORITHM = {UNDEFINED | MERGE|TEMPTABLE}]
  2. VIEW view_name [(column list)]
  3. AS SELECT_statement
  4. [WITH[CASCADED | LOCAL] CHECK OPTION]

更新视图

更新视图是指通过视图来更新、插人,删除基本表中的数据。
因为视图是一个虚拟表,其中没有数据,当通过视图更新数据时其实是在更新基本表中的数据。如果对视图中的数据进行增加或者删除操作时,实际上就是在对其基本表中的数据进行增加或者删除操作。

接下来将介绍三种更新视图的方法,具体如下：

使用 UPDATE语句更新视图

在 MySQL中,可以使用UPDATE语句对视图中原有的数据进行更新。

  1. UPDATE 视图 SET 字段 = 值;

使用 INSERT语句更新视图

在 MySQL中,可以通过使用INSERT语句向表中插入一条记录。

  1. INSERT INTO 表名 VALUES(字段的值);

使用 DELETE语句更新视图

在MySQL中,可以使用DELETE语句删除视图中的部分记录。

  1. DELETE FROM 视图 WHERE 字段名= 值;

注意：

需要注意的是,尽管更新视图有多种方式,但是并非所有情况下都能执行视图的更新操作。
当视图中包含如下内容时,视图的更新操作将不能被执行。

  • 视图中包含基本表中被定义为非空的列。
  • 在定义视图的SELECT语句后的字段列表中使用了数学表达式。
  • 在定义视图的SELECT语句后的字段列表中使用了聚合函数。
  • 在定义视图的SELECT语句中使用了DISTINCT, UNION,TOP,GROUP BY或HAVING子句。

删除视图

当视图不再需要时,可以将其删除,删除视图时,只能删除视图的定义,不会删除数据。
删除一个或多个视图可以使用 DROP VIEW语句,删除视图的基本语法格式如下所示:

  1. DROP VIEW[IF EXISTS]
  2. view_name[,view_namel]…
  3. [RESTRICT | CASCADE]

注意：
在上述语法格式中, view_name是要删除的视图的名称,视图名称可以添加多个,各个名称之间使用逗号隔开,删除视图必须拥有DROP权限。

数据库的高级操作

数据备份与还原

数据备份

备份单个数据库

  1. mysqldump -uusername -ppassword dbname [tbname1 [tbname2…]]>filename.sql

上述语法格式中,-u后面的参数username表示用户名，-p后面的参数 password表示登录密码,dbname表示需要备份的数据库名称, tbname表示数据库中的表名,可以指定一个或多个表,多个表名之间用空格分隔,如果不指定则备份整个数据库,filename. sql表示备份文件的名称,文件名前可以加上绝对路径。

注意：
需要注意的是,在使用mysqldump命令备份数据库时,直接在DOS命令行窗口中执行该命令即可,不需要登录到MySQL数据库。

备份多个数据库

  1. mysqIdump -uusername -ppassword --database dbnamel [dbname2 dbname3…]>filename.sql

上述语法格式中,“-databases”参数后面至少应指定一个数据库名称,如果有多个数据库,则名称之间用空格隔开。

备份所有数据库

  1. mysgldump -uusername -ppassword --all-databases>filename.sql

注意：
需要注意的是,如果使用“—all-databases”参数备份了所有的数据库,那么在还原数据库时,不需要创建数据库并指定要操作的数据库,因为对应的备份文件中包含CREATE DATABASE语句和USE语句。

数据的还原

  1. myegl -uusername -ppassword [dbname] <filename.sql

上述语法格式中,username表示登录的用户名，password表示用户的密码,dbname表示要还原的数据库名称,如果使用mysqldump命令备份的 filename. sql 文件中包含创建数据库的语句,则不需要指定数据库。

注意：
我们知道数据库中的库是不能被还原的,因此在还原数据之前必须先创建数据库。

用户管理

user表

在安装 MySQL时,会自动安装一个名为mysql的数据库,该数据库中的表都是权限表,如 user,db ,host,tables_priv,column_priv和 procs_priv,其中user表是最重要的一个权限表,它记录了允许连接到服务器的账号信息以及一些全局级的权限信息,通过操作该表就可以对这些信息进行修改。

1．用户列

user表的用户列包括Host、User,Password,分别代表主机名、用户名和密码。其中Host和 User列为user表的联合主键,当用户与服务器建立连接时,输入的用户名、主机名和密码必须匹配user 表中对应的字段,只有这三个值都匹配的时候,才允许建立连接。当修改密码时,只需要修改user表中Password字段的值即可。

2．权限列

user表的权限列包括Select_priv、Insert_priv、Update_priv等以 priv结尾的字段，这些字段决定了用户的权限,其中包括查询权限、修改权限、关闭服务等权限。
user表对应的权限是针对所有数据库的,并且这些权限列的数据类型都是ENUM,取值只有N或Y,其中N表示该用户没有对应权限，Y表示该用户有对应权限,为了安全起见,这些字段的默认值都为N,如果需要可以对其进行修改。

3.安全列

user表的安全列用于管理用户的安全信息,其中包括6个字段,具体如下。
(1) ssl_type和ssl_cipher:用于加密。
(2）x509_issuer 和 x509_subject标准:可以用来标识用户。
(3) plugin和authentication_string:用于存储与授权相关的插件。
注意：
通常标准的发行版不支持ssl加密,初学者可以使用SHOW VARIABLES LIKE ‘have_openssl’语句查看,如果 have_openssl的取值为DISABLED,则表示不支持加密。

4.资源控制列

user 表的资源控制列是用于限制用户使用的资源,其中包括4个字段,具体如下。
(1) max_questions:每小时允许用户执行查询操作的次数。
(2) max_updates:每小时允许用户执行更新操作的次数。
(3) max_connections:每小时允许用户建立连接的次数。
(4) max__user_connections:允许单个用户同时建立连接的次数。

查看表中有哪些用户

  1. SELECT host,user,password FROM 数据库名;

创建普通用户

使用GRANT语句创建用户

GRANT语句不仅可以创建新用户,还可以对用户进行授权(将在后面讲解)，该语句会自动加载权限表,不需要手动刷新,而且安全、准确、错误少,因此,使用GRANT语句是创建用户最常用的方法。

  1. GRANT privileges ON database.table
  2. TO 'username'@'hostname'[IDENTIFIED BY [PASSWORD] 'password']
  3. [,'username'@'hostname [IDENTIEIED BY [PASSWORD]'password']]...

上述语法格式中, privilereos参数表示该用户具有的权限信息，database. table表示新用户的权限范围表，可以在指定的数据库，表上使用自己的权限，usernamne参数是新用户的名称，hostname参数是主机名,password参数是新用户的密码。

使用 CREATE USER 语句创建用户

使用 CREATE USER 语句创建新用户时,服务器会自动修改相应的授权表,但需要注意的是,该语句创建的新用户是没有任何权限的。

  1. CREATE USER 'username'@'hostname'[IDENTIFIED BY [PASSWORD]'password']
  2. [,'username'@'hostname ' [IDENTIFIED BY [PASSWORD]'password ']]…

上述语法格式中，username表示新创建的用户名，hostname表示主机名，IDENTIFIED BY关键字用于设置用户的密码， password表示用户的密码，PASSWORD关键字表示使用哈希值设置密码，该参数是可选的,如果密码是一个普通的字符串，就不需要使用 PASSWORD关键字。

使用 INSERT语句创建用户

通过前面的讲解可知,不管是CREATE USER语句还是GRANT语句,在创建用户时,实际上都是在user表中添加一条新的记录,因此,也可以使用INSERT 语句直接在该表中添加一个用户。

  1. INSERT INTO mysql.user (Host, User, Password, ssl_cipher、x509_issuer、x509_subject)
  2. VALUES('hostname','username',PASSWORD('password'),'','','');

上述语法格式中,mysql.user参数表示操作的表，Host，User，Password，sslcipher，x509_issuer，x509 subject为相应字段，PASSWORD()是一个加密函数,用于给密码加密。

注意：
需要注意的是,使用INSERT语句创建用户时,通常只需添加 Host、User和Password这三个字段即可,其他的字段取其默认值,但由于ssl_cipher , x509_issuer, x509_subject字段是没有默认值的,因此 INSERT语句创建用户时,还需要为这几个字段设置初始值。

使用 INSERT语句成功地创建一个新用户user3,但是由于INSERT语句没有刷新权限表的功能,因此,还需要手动刷新当前的权限表或重新启动MySQL服务,刷新权限表的语句如下:

  1. FLUSH PRIVILEGES;

删除普通用户

使用 DROP USER 语句删除用户

  1. DROP USER 'username'@'hostname'[,'username '@'hostname'];

上述语法格式中, username表示要删除的用户, hostname表示主机名,DROP USER语句可以同时删除一个或多个用户,多个用户之间用逗号隔开。值得注意的是,使用DROP USER 语句来删除用户时,必须拥有DROP USER的权限。

使用 DELETE语句删除用户

DELETE语句不仅可以删除普通表中的数据,还可以删除user表中的数据,使用语句删除user表中的数据时,只需指定表名为mysql.user,以及要删除的用户信息即可同样地,在使用 DELETE语句时必须拥有对 mysql.user表的DELETE权限。

  1. DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

上述语法格式中, mysql. user参数指定要操作的表,WHERE指定条件语句，Host和 User都是mysql.user表的字段,这两个字段可以确定唯一的一条记录。

修改用户的密码

修改root用户密码

（一）使用mysqladmin命令修改root用户密码
mysqladmin命令通常用于执行一些管理性的工作,以及显示服务器状态等，在MySQL中可以使用该命令修改root用户的密码。

  1. mysqladmin -u username [-h hostname] -p password new password

上述语法格式中, username为要修改的用户名,这里指的是root用户,参数-h用于指定对应的主机,可以省略不写,默认为localhost,-p后面的password为关键字,而不是修改后的密码, new_password为新设置的密码。
注意：
在使用mysqladmin命令修改root用户密码时,需要在C:\Documents and Settings\Administrator>目录下进行修改。

（二）使用UPDATE语句修改root用户密码
由于所有的用户信息都存放在mvsal.user表中,因此,只要 root用户登录到MySQL 服务器,使用UPDATE语句就可以直接修改自己的密码。

  1. UPDATE mysql.user set Password=PASSWORD('new_password')
  2. WHERE User='username' and Host='hostname';

注意:
由于 UPDATE 语句不能刷新权限表,因此一定要使用 FLUSH PRIVILEGES 语句重新加载用户权限，否则修改后的密码不会生效。

（三）使用 SET语句修改root用户的密码
root用户登录到MySQL服务器后,还可以通过 SET语句修改root用户的密码。

  1. SET PASSWORD=PASSwORD('New_password');

注意:
由干 SET语句没有对密码加密的功能,因此,新密码必须使用PASSWORD()函数加密,并目新密码需要使用引号括起。

root用户修改普通用户的密码

（一）使用GRANT语句修改普通用户密码
GRANT语句的作用比较多,不仅可以创建用户为用户授权,还可以修改用户的密码,通常情况下,为了不影响当前账户的权限,可以使用GRANT USAGE语句修改指定账户的密码。

  1. GRANT USAGE ON *.* TO 'username '@'localhost' IDENTIFIED BY [PASSWORD]' new_Password';

（二）使用UPDATE语句修改普通用户的密码
root用户具有操作数据库的所有权限,因此,它不仅可以使用UPDATE语句修改自己的密码,还可以使用UPDATE语句修改普通用户的密码

  1. UPDATE mysql.user set Password=PASSWORD('new_password')
  2. WHERE User='username' and Host="hostname';

注意：
使用上述语句修改完普通用户的密码后,还需要使用FLUSH PRIVILEGES 语句重新加载权限表。

（三）使用 SET语句修改普通用户的密码
前面讲过使用SET不仅可以修改root用户密码,而且还可以修改普通用户密码,在修改普通用户密码时,还需要增加一个 FOR子句,指定要修改哪个用户即可。

  1. SET PASSWORD FOR'username'@'hostname'=PASSWORD('new_password');

普通用户修改密码

  1. SET PASSWORD=PASSWORD('new_password');

注意：
SET语句修改普通用户密码时，和修改root用户是一样的,都需要使用PASSWORD()函数进行加密。

如何解决root用户密码丢失

1.停止MySQL服务
在“运行”对话框中,使用net命令停止 MySQL服务,具体命令如下:

  1. net stop mysql

2．使用一skip-grant-tables启动MySQL服务
MySQL服务器中有一个 skip-grant-tables选项,它可以停止 MySQL的权限判断，也就是说任何用户都可以访问数据库,并且通过该选项也可以启动MySQL服务,在“运行”对话框中执行如下命令:

  1. mysqld --skip-grant-tables

3．登录MySQL服务器
重新开启一个“运行”对话框,在“运行”对话框中登录 MySQL服务器,具体命令如下:

  1. mysql -u root

4.使用UPDATE语句设置 root 用户密码
MySQL登录成功后，可以通过UPDATE语句设置root用户的密码,具体语句如下:

  1. UPDATE mysol.user SET PaSsword=PASSWORD ('itcaat') WHERE User='root'
  2. AND Host="loealhost';

5．加载权限表
MySQL密码设置完成后,还需重新加载权限表,让设置的密码生效,具体语句如下:

  1. FLUSH PRIVILEGES;

上述步骤执行完,可以使用EXIT 或 \q 命令退出服务器,然后使用新密码重新登录。
至此,便完成了root用户的密码设置。

权限管理

MySQL的权限

(1)CREATE和DROP权限,可以创建数据库、表、索引,或者删除已有的数据库、表、索引。
(2) INSERT, DELETE、UPDATE、SELECT权限,可以对数据库中的表进行增删改查操作。
(3)INDEX 权限,可以创建或删除索引,适用于所有的表。
(4)ALTER 权限,可以用于修改表的结构或重命名表。
(5)GRANT权限,允许为其他用户授权,可用于数据库和表。
(6)FILE权限,被赋予该权限的用户能读写MySQL 服务器上的任何文件。

授予权限

  1. GRANT privileges [(columns)][,privileges[(columns)]] ON database.table
  2. TO 'username'@'hostname'[IDENTIFIED BY [PASSWORD] 'password']
  3. [,'username'@'hostname'[IDENTIFIED BY [PASSWORD] 'password']]…
  4. [WITH with_option [with_option]…]

上述语法格式中, privileges表示权限类型, columns参数表示权限作用于某一列,该参数可以省略不写,此时权限作用干整个表,username表示用户名, hostname表示主机名, IDENTIFIED BY参数为用户设置密码，PASSWORD参数为关键字, password为用户的新密码。
WITH关键字后面可以带有多个参数 withoption,这个参数有5个取值，具体如下。
(1) GRANT OPTION：将自己的权限授予其他用户。
(2) MAX_QUERIES PER HOUR count：设置每小时最多可以执行多少(count)查询。
(3) MAX_UPDATES_PER_HOUR count：设置每小时最多可以执行多少次更
(4) MAX_CONNECTIONS_PER_HOUR count：设置每小时最大的连接数量。
(5) MAX USER_CONNECTIONS：设置每个用户最多可以同时建立连接的数量

查看权限

  1. SHON GRANTS FOR 'username '@'hostname';

回收权限

  1. REVOKE privileges [columns][,privileges[(columns)]] ON database.table
  2. FROM 'username'@'hostname'[,'username'@'hostname']...

REVOKE语法格式中的参数与GRANT语句中的参数意思相同, privileges参数表示收回的权限,columns表示权限作用于哪列上,如果不指定该参数表示作用于整个表。