目录
1. 视图的概念和规则限制
视图概念:
- 视图是一个虚拟表,其内容由查询定义,同真实的表一样,视图包含一系列带有名称的列和行数据。
- 视图中的数据并不会单独存储在数据库中,其数据来自定义视图时查询所引用的表(基表),在每次引用视图时动态生成。
- 由于视图和基表用的本质是同一份数据,因此对视图的修改会影响到基表,对基表的修改也会影响到视图。
视图规则和限制:
- 与普通表一样,视图的命名也必须是唯一的,不能出现同名视图或表名。
- 创建视图的数目无限制,但要考虑复杂查询创建为视图之后的性能影响。
- 视图不能添加索引,也不能有关联的触发器或者默认值。
- 视图可以提高安全性,在访问视图时必须具有足够的访问权限。
- 创建视图时可以使用order by子句,但如果从该视图检索数据时也含有order by子句,那么该视图中的order by将被覆盖。
- 视图可以和普通表一起使用,比如进行多表查询,内外连接等。
2. 视图的基本使用
下面用员工表和部门表作为测试表,员工表中的ename代表的是员工的姓名,deptno代表的是员工所在部门的部门号。如下:
部门表中的dname代表的是部门名,deptno代表的是部门的部门号。如下:
2.1 创建视图
创建视图的SQL如下:
create view view_name as select ...;
- 创建视图时会先执行select语句,然后用查询得到的结果来创建视图。
当我们要查询每个员工及其对应的部门名称时,需要使用员工表和部门表进行多表查询,并筛选出员工的部门号等于部门的部门号的记录。如下:
如果该查询结果会被频繁用到,那我们就可以给上述查询结果创建视图,创建完毕后通过show命令就能看到这个视图。如下:
并且在数据库对应的目录下,会增加一个对应的xxx.frm文件,但并没有与之对应的xxx.ibd文件,这也证明了视图和基表使用的是同一份数据。
创建视图后就可以直接通过查询视图,来查看每个员工及其对应的部门名称了。如下:
2.2 修改视图影响基表
通过查询员工表,可以看到员工CLARK所在部门的部门号为10。如下:
进一步查询部门表,可以看到10号部门的部门名称为ACCOUNTING。如下:
在视图中将员工CLARK所在部门的部门名改为HR后,会看到其他一些员工所在部门的部门名也变为HR了。如下:
根本原因就是因为视图和基表使用的是同一份数据,将视图中CLARK所在部门的部门名改为HR后,部门表中10号部门的部门名也就变成HR了。如下:
而位于10号部门的员工同时还有KING和MILLER,因此修改后再次查询视图时,这两个员工对应的部门名也会变为HR。如下:
2.3 修改基表影响视图
通过查询员工表,可以看到员工JAMES所在部门的部门号为30。如下:
30号部门的部门名为SALES,因此查询视图时可以看到JAMES所在的部门名为SALES。如下:
现在将员工表中,员工JAMES对应的部门号改为20。如下:
修改后再查询视图,就会发现JAMES所在部门的部门名,变成了20号部门的部门名RESEARCH。如下:
2.4 删除视图
删除视图的SQL如下:
drop view view_name;
比如将刚才创建的视图删除后,在数据库中就看不到这个视图了。并且该视图在数据库目录下对应的xxx.frm文件也会被删除。如下:
3. MySQL用户管理
- 与Linux操作系统类似,MySQL中也有超级用户和普通用户之分。
- 如果一个用户只需要访问MySQL中的某一个数据库,甚至数据库中的某一个表,那么可以为其创建一个普通用户,并为该用户赋予对应的权限,而不让该用户看到数据库中的其他数据,防止该用户对其他数据进行误操作。
3.1 用户信息
MySQL当中默认有一个名为mysql的数据库。如下:
查看该数据库中的表,可以看到其中有一个名为user的表。如下:
user表中存储的就是MySQL中用户相关的信息。如下:
部分字段说明:
- user: 表示该用户的用户名。
- host: 表示该用户可以从哪个主机登录,localhost表示只能从本机登录,%表示可以从任意地方登录。
- authentication_string: 表示该用户的密码经过password函数加密后的值。
- xxx_priv: 表示该用户是否拥有对应权限。
在查看用户信息时为了避免刷屏,可以只选择其中的部分字段进行显示。如下:
需要注意的是,MySQL中可以存在同名的用户,只要这些同名用户对应的登录主机不同即可,因为user表中的主键是复合主键,由表中的user列和host列共同承担。如下:
3.2 创建用户
创建用户的SQL如下:
create user '用户名'@'登录主机' identified by '密码';
- 创建用户的SQL当中包含用户的密码,因此该SQL不会被历史记录下来,所以不能通过上下键进行追溯。
- MySQL本身的认证级别比较高,因此创建用户时设置的密码不能太简单,否则会出现报错,这时可以选择将密码设置复杂一些,也可以对密码相关的设置进行调整。
通过show命令查看全局变量,可以看到密码设置相关的要求。如下:
比如下面创建一个用户名为GR,并且可以从任意地方登录的用户。如下:
这时便可以用新创建的普通用户来连接MySQL服务器了。如下:
此外,由于我们创建的这个用户可以从任意地方登录,因此如果你在Windows下也安装了MySQL,那么也可以在Windows的cmd窗口进行远程登录。
3.3 修改用户密码
用户自己修改自己的密码:
用户可以自己通过调用password函数,将新密码加密后的值设置到自己password当中。如下:
超级用户可以通过调用password函数,将新密码加密后的值设置到指定用户的password当中。如下:
3.4 删除用户
删除用户的SQL如下:
drop user '用户名'@'登录地址';
比如将刚才创建的用户删除后,该用户在user表中对应的记录也就不存在了。如下:
- 删除用户时如果不指明待用户的登录地址,则默认删除的是登录地址为%的用户。
4. 用户权限
4.1 MySQL权限
MySQL数据库提供的权限如下:
权限 | 列名 | 上下文 |
---|---|---|
CREATE | Create_priv | 数据库、表或索引 |
DROP | Drop_priv | 数据库或表 |
GRANT OPTION | Grant_priv | 数据库、表或保存的程序 |
REFERENCES | References_priv | 数据库或表 |
ALTER | Alter_priv | 表 |
DELETE | Delete_priv | 表 |
INDEX | Index_priv | 表 |
SELECT | Select_priv | 表 |
UPDATE | Update_priv | 表 |
CREATE VIEW | Create_view_priv | 视图 |
SHOW VIEW | Show_view_priv | 视图 |
ALTER ROUTINE | Alter_routine_priv | 保存的程序 |
CREATE ROUTINE | Create_routine_priv | 保存的程序 |
EXECUTE | Execute_priv | 保存的程序 |
FILE | File_priv | 服务器主机上的文件访问 |
CREATE TEMPORARY TABLES | Create_tmp_table_priv | 服务器管理 |
LOCK TABLES | Lock_tables_priv | 服务器管理 |
CREATE USER | Create_user_priv | 服务器管理 |
PROCESS | Process_priv | 服务器管理 |
RELOAD | Reload_priv | 服务器管理 |
REPLICATION CLIENT | Repl_client_priv | 服务器管理 |
REPLICATION SLAVE | Repl_slave_priv | 服务器管理 |
SHOW DATABASES | Show_db_priv | 服务器管理 |
SHUTDOWN | Shutdown_priv | 服务器管理 |
SUPER | Super_priv | 服务器管理 |
需要注意的是,新创建的用户没有任何权限,因此创建用户后需要给用户授权。
4.2 给用户授权
给用户授权的SQL如下:(to 后面跟用户,可以是已创建的,未创建的就是下面的写法)
grant 权限列表 on 库名.对象名 to '用户名'@'登录地址' [identified by '密码'];
- '用户名'@'登录地址':表示给哪一个用户授权。
- 库名.对象名:表示要授予用户哪个数据库下的哪个对象的权限。
- 权限列表:表示要授予用户何种权限,多个权限之间用逗号隔开。
- IDENTIFIED BY '密码'可选:如果用户存在,则在授予权限的同时修改该用户的密码,如果用户不存在,则创建该用户。
比如下面创建用户GR,并授予用户在 scott 数据库下所有对象的select权限。授权后通过show grants for '用户名'@'登录地址'命令,可以查看该用户现有的权限。如下:
- 创建用户后该用户默认会有USAGE权限,该权限只能用于数据库登录,不能执行任何操作。
- *.*表示所有数据库的所有对象,库名.*表示某个数据库的所有对象(表、视图、存储过程等)。
此时该用户查看数据库时,就能查看到 scott 数据库了。如下:
- 创建用户后该用户默认只能看到information_schema数据库,该数据库中保存的是MySQL服务器所维护的所有其他数据库的信息。
进入user_management数据库后,也能查看其中的所有表。如下:
因为我们只授予了该用户select权限,该用户目前只能查看表中的信息,而不能对表中的数据进行修改。如下:
下面另开一个窗口登录root,将 scott 数据库下的所有权限都授予该用户。如下:
这时该用户重新登录就可以对表中的数据进行其他操作。如下:
4.3 回收权限
回收权限的SQL如下:
revoke 权限列表 on 库名.对象名 from '用户名'@'登录地址';
- 回收权限的语法与授权一样,只不过将to关键字改为了from,并且没有了indentified by '密码'字段。
比如下面将GR用户在 scott 数据库下的所有权限回收。如下:
- 回收用户在某一数据库下的权限后,在该用户下一次进入该数据库时才会起作用。
- 如果回收权限时该用户正在使用对应数据库,那么回收权限后该用户仍然拥有对应的权限。
本篇完。
下一篇:C语言链接MySQL数据库并对数据库进行增删查改。