| |
First, what is the view
Popular speaking, the view is the result set of a SELECT statement is executed returned. So we create the view, the main work falls on this to create SQL queries.
Second, the characteristics of the view
A view is a reference to the number of sheets the base table, a virtual table, query results statements executed, do not store specific data (data base table is changed, the view will also change);
You can follow the same basic form, perform CRUD operations (ps: CRUD operations conditional);
Three functions, views
Easy to operate, especially in query operations, reduce the complexity of SQL statements, and enhance readability;
More security, database authorization command can not be limited to a specific row and a specific column, but reasonable by creating a view, you can define the ranks of the authority level;
Fourth, using the occasion
When access control, you do not want users to access some of the columns in the table containing sensitive information, such as salary ...
Critical information from multiple complex association table, you can create a view to extract the information we need, streamline operations;
Fifth, create a view and the view of Example 1 query data manipulation
Existing three tables: the user (user), curriculum (course), intermediate course user table (user_course), table structure and data is as follows:
Table definition:
- ----------------------------
- Table structure for `course`
- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`Id` bigint (20) NOT NULL AUTO_INCREMENT,
`Name` varchar (200) NOT NULL,
`Description` varchar (500) NOT NULL,
PRIMARY KEY ( `id`)
) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8;
- ----------------------------
- Records of course
- ----------------------------
INSERT INTO `course` VALUES ( '1', 'JAVA', 'JAVA program');
INSERT INTO `course` VALUES ( '2', 'C ++', 'C ++ program');
INSERT INTO `course` VALUES ( '3', 'C language', 'C language courses');
- ----------------------------
- Table structure for `user`
- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`Id` bigint (20) NOT NULL AUTO_INCREMENT,
`Account` varchar (255) NOT NULL,
`Name` varchar (255) NOT NULL,
`Address` varchar (255) DEFAULT NULL,
`Others` varchar (200) DEFAULT NULL,
`Others2` varchar (200) DEFAULT NULL,
PRIMARY KEY ( `id`)
) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8;
- ----------------------------
- Records of user
- ----------------------------
INSERT INTO `user` VALUES ( '1', 'user1', 'Chen', 'America', '1', '1');
INSERT INTO `user` VALUES ( '2', 'user2', 'Sally', 'Japan', '2', '2');
INSERT INTO `user` VALUES ( '3', 'user3', 'Wang', 'China', '3', '3');
- ----------------------------
- Table structure for `user_course`
- ----------------------------
DROP TABLE IF EXISTS `user_course`;
CREATE TABLE `user_course` (
`Id` bigint (20) NOT NULL AUTO_INCREMENT,
`Userid` bigint (20) NOT NULL,
`Courseid` bigint (20) NOT NULL,
PRIMARY KEY ( `id`)
) ENGINE = InnoDB AUTO_INCREMENT = 7 DEFAULT CHARSET = utf8;
- ----------------------------
- Records of user_course
- ----------------------------
INSERT INTO `user_course` VALUES ( '1', '1', '2');
INSERT INTO `user_course` VALUES ( '2', '1', '3');
INSERT INTO `user_course` VALUES ( '3', '2', '1');
INSERT INTO `user_course` VALUES ( '4', '2', '2');
INSERT INTO `user_course` VALUES ( '5', '2', '3');
INSERT INTO `user_course` VALUES ( '6', '3', '2');
At this time, when we want to query information about Zhang curriculum so on, you need to write a long SQL statement as follows:
SELECT
`Uc`.`id` AS` id`,
`U`.`name` AS` username`,
`C`.`name` AS` coursename`
FROM
`User`` u`
LEFT JOIN `user_course`` uc` ON (( `u`.`id` =` uc`.`userid`))
LEFT JOIN `course`` c` ON (( `uc`.`courseid` =` c`.`id`))
WHERE
u.`name` = 'Sally'
But we can simplify operations through the view, for example, we create a view view_user_course as follows:
- ----------------------------
- View structure for `view_user_course`
- ----------------------------
DROP VIEW
IF EXISTS `view_user_course`;
CREATE ALGORITHM = UNDEFINED
DEFINER = `root` @` localhost`
SQL SECURITY DEFINER
VIEW `view_user_course` AS (
SELECT
`Uc`.`id` AS` id`,
`U`.`name` AS` username`,
`C`.`name` AS` coursename`
FROM
(
(
`User`` u`
LEFT JOIN `user_course`` uc` ON (( `u`.`id` =` uc`.`userid`))
)
LEFT JOIN `course`` c` ON (( `uc`.`courseid` =` c`.`id`))
)
);
Some explanations (MySQL in view of the above on the basis of standard SQL extensions do):
ALGORITHM = UNDEFINED: processing algorithms specified view;
DEFINER = `root` @` localhost`: Specifies the view creator;
SQL SECURITY DEFINER: Specifies the view query data security authentication;
After creating the view, we can use the following SQL statement to query information about the course so Zhang on the direct view, the same can get the desired results:
SELECT
vuc.username,
vuc.coursename
FROM
view_user_course vuc
WHERE
vuc.username = 'Sally'
VI View Example 2 CRUD data operations
Continue, we can try to do CRUD operations on the data view view_user_course, as follows:
update view_user_course set username = 'test', coursename = 'JAVASCRIPT' where id = 3
Unfortunately, the operation fails, an error message is as follows:
[SQL] update view_user_course set username = 'test', coursename = 'JAVASCRIPT' where id = 3
[Err] 1393 - Can not modify more than one base table through a join view 'demo.view_user_course'
Because you can not do in a view from multiple connected in the associated table is modified while the operation of the two tables;
So what operations can be performed on a view of it?
Table view is one to one relationship: if there are no other constraints (such as view, there is no field in the base table is a required field case), it can be additions and deletions to data manipulation;
As we create a user view critical information view_user_keyinfo, as follows:
- ----------------------------
- View structure for `view_user_keyinfo`
- ----------------------------
DROP VIEW
IF EXISTS `view_user_keyinfo`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @` localhost` SQL SECURITY DEFINER VIEW `view_user_keyinfo` AS SELECT
`U`.`id` AS` id`,
`U`.`account` AS` account`,
`U`.`name` AS` username`
FROM
`User`` u`;
Additions and deletions to the operation follows the success of the operation (note that user table to allow other fields empty, otherwise the operation fails):
INSERT INTO view_user_keyinfo (account, username)
VALUES
( 'Test1', 'test1');
DELETE
FROM
view_user_keyinfo
WHERE
username = 'test1';
UPDATE view_user_keyinfo
SET username = 'updateuser'
WHERE
id = 1
Many relationship table view is: If only modify data in a table, and no other constraints (such as view, there is no field in the base table is a required field case), it can be changed data manipulation, such as The following statement, the success of the operation;
update view_user_course set coursename = 'JAVA' where id = 1;
update view_user_course set username = 'test2' where id = 3;
The following operation failed:
delete from view_user_course where id = 3;
insert into view_user_course (username, coursename) VALUES ( '2', '3');
7. Other
Query performance views to be adjusted to the optimum;
Be careful when modifying the operation, you have inadvertently changed the basic list of a plurality of data;
Other aspects to be performance-related, practice and experience ... |
|
|
|