什么是试图:
通俗的讲,试图就是一条select语句执行后返回的结果集。所以我们在创建试图的时候,主要工作就落在创建这条SQL查询语句上。
试图的特性:
- 试图是对若干张基本表的引用,是一张虚拟的逻辑表,由查询语句执行的结果构成
- 试图不存储具体的数据(基本表数据发生了改变,试图也会跟着改变)
- 试图可以跟基本表一样,进行增删改查操作,但是增删改查操作是有限制的
试图的作用:
- 方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性;
- 更加安全,数据库授权命令不能限定到特定行和特定列,但是通过合理创建试图,可以把权限限定到行列级别;
使用场合:
-
权限控制的时候,不希望用户访问表中某些含敏感信息的列,比如salary…
-
关键信息来源于多个复杂关联表,可以创建试图提取我们需要的信息,简化操作;
试图案例:
1.创建如下三张表
#users表的创建
CREATE TABLE `users` (
`u_id` int(4) NOT NULL auto_increment,
`username` varchar(20) default NULL,
`age` int(4) default NULL,
`country` varchar(20) default NULL,
PRIMARY KEY (`u_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
#course表的创建
CREATE TABLE `course` (
`c_id` int(4) NOT NULL auto_increment,
`coursename` varchar(20) default NULL,
`description` varchar(255) default NULL,
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
#user_course表的创建
CREATE TABLE `user_course` (
`u_c_id` int(4) NOT NULL auto_increment,
`userid` int(4) NOT NULL,
`courseid` int(4) NOT NULL,
PRIMARY KEY (`u_c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
2.分别向三张表中插入数据
users表:
course表:
user_course表:
3.要求查询tom所修的课程,显示tom的名字和课程名
常规的方法是使用外连接的方式查询:
SELECT username,coursename FROM
user_course
JOIN users
ON user_course.userid=users.u_id
JOIN course
ON user_course.courseid=course.c_id
WHERE username='tom';
结果:
可以看出,并不是很复杂的关联表的查询已经如此的复杂了,下面我再使用试图的方式做同样的查询。
1.试图的创建
CREATE VIEW user_course_view AS
SELECT * FROM user_course
JOIN users
ON user_course.userid=users.u_id
JOIN course
ON user_course.courseid=course.c_id;
查看试图"user_course_view"
再啰嗦一下,试图本身并不存储数据,它只是一个逻辑表。
2.使用试图查询tom所修的课程,显示tom的名字和课程名
SELECT username,coursename
FROM user_course_view
WHERE user_course_view.username='tom';
结果:
使用试图创建后,让本来很复杂的查询变的很简单。
3.使用试图表的行列及信息进行隐藏,从而提高数据的安全性
CREATE VIEW user_course_view1 AS
SELECT user_course.*,users.*,course.c_id,course.coursename
FROM user_course
JOIN users
ON user_course.userid=users.u_id
JOIN course
ON user_course.courseid=course.c_id;
结果:
这样就很好的隐藏了"coursename"这一列的数据。
哪些操作可以在视图上进行呢?
由于试图只是一张逻辑表,也就致使并不是所有的增删改操作都可以在视图上进行的。
- 试图与表是一对一关系情况:如果没有其它约束(如试图中没有的字段,在基本表中是必填字段情况的约束)是可以进行增删改数据操作
- 试图与表是一对多关系情况:如果只修改一张表的数据,且没有其它约束(如试图中没有的字段,在基本表中是必填字段情况的约束),是可以进行改数据操作的
tip:对试图进行修改时要小心,因为不经意间就会修改基本表中的多条数据
关于"information_schema"数据库
MySQL数据库中的"information_schema"数据库中保存了大量元数据查询的试图。"information_schema"数据库是一个虚拟库,并不占用存储空间。
为了避免有些读者不熟悉元数据的概念,这里多说一句题外话。MySQL中的元数据就是中数据库中表的属性信息,比如:表所在的库名、表名、使用的存储引擎、有多少数据行等
mysql> use information_schema; 进入information_schema数据库内,库内有很多表,这些表就是相应的试图名。其中TABLES试图,包含了整个数据中所有表的元数据。
mysql> desc tables; 查看tables试图的属性结构
选择几个常用的介绍一下:
TABLE_SCHEMA 代指表所在的库名
TABLE_NAME 代指表名
ENGINE 代指存储引擎
TABLE_ROWS 代指有多少数据行
AVG_ROW_LENGTH 代指平均行长度
INDEX_LENGTH 代指索引长度
information_schema使用案例
1.统计上面users表占用的空间大小。我们知道表占用空间大小=平均行长度*行数+索引长度
SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024
FROM information_schema.TABLES
WHERE table_schema='viewtest' AND table_name='users';
ps:"viewtest"是users表所在的数据库
2.配合concat()函数拼接语句或命令
模仿以下语句,进行数据库的分库分表备份
mysqldump -uroot -p123 viewtest users >/bak/viewtest_users.sql
select concat("mysqldunp -uroot -p123",table_schema," ",table_name," >/bak/",table_schema,"_",table_name,".sql")
from information_schema.tables;