Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL view     - Install the free open source financial software GnuCash 2.6.6 under Ubuntu (Linux)

- Jetty JNDI Development combat (Linux)

- Java threads in the life cycle (Programming)

- Optimized to minimize the installation of CentOS 5.8 (Linux)

- Detailed reference Oracle 11g Partition (Database)

- Java Concurrency - processes and threads (Programming)

- Oracle LONG RAW BLOB CLOB type of presentation (Database)

- Bitmap memory footprint of computing Android memory optimization (Linux)

- Linux, Firefox unloading and installation and upgrade (Linux)

- How do you access Dropbox Linux command line (Linux)

- To create a secure network firewall with iptables Under Linux (Linux)

- Reset CentOS / RHEL root account password 7 (Linux)

- Ubuntu is expected to allow you to install the latest Nvidia Linux drivers easier (Linux)

- Java exception handling mechanism (Programming)

- socket busy poll of Linux kernel 3.11 to avoid sleep switch (Linux)

- JavaScript file loader LABjs API Explanation (Programming)

- Command filter MySQL slow query log (Database)

- CentOS7 installation GAMIT and GMT (Linux)

- How to configure MongoDB replica set (Database)

- Linux program analysis tool: ldd and nm (Linux)

 
         
  MySQL view
     
  Add Date : 2018-11-21      
         
         
         
  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 ...
     
         
         
         
  More:      
 
- Android timer use (Programming)
- CentOS7 virtual machine settings, and bridging problems (Linux)
- ORA-04031 Error Resolution (Database)
- Monitor traffic Linux Shell Edition (Programming)
- Linux compiler installation Redis (Database)
- Linux file and directory management - ls, cp, mv (Linux)
- Ora-14450: attempt to access ah transactional temptable already in use (Database)
- Java Generics (Programming)
- Android basics summary article (Programming)
- Mac OS X systems create Ubuntu USB boot disk for the Mac (Linux)
- Linux --- process handle limit summary (Linux)
- Ubuntu 14.04 installed Nvidia CUDA 7.5 and build Python Theano deep learning development environment (Linux)
- GitLab issued Merge Request return error 500 when the two solutions log (Linux)
- Linux Shell Scripting (Programming)
- Sniffer Linux Environment (Linux)
- ORA-12547: TNS: lost contact error Solution (Database)
- Zabbix configure DataGuard monitoring (Database)
- Ubuntu install perfectly handsome terminal Guake 0.8.1 (Linux)
- Installation and configuration of phpMyAdmin under CentOS (Database)
- Seven Steps to Help Google Chrome Speed - (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.