Shulou(Shulou.com)06/01 Report--

1) is a virtual existence table

2) The content is similar to a real table, containing a series of named columns and rows of data.

3) Views do not exist in the database as stored data.

4) The row and column data comes from the underlying tables referenced by the query when the view is defined, and is generated dynamically when the view is specifically referenced.

Updating the view data is updating the base table data

Update base table data, view data will also change

What are the advantages of 2 views?

Simplicity: Users of views don't care what queries the data in the view is derived from.

The data in the view is already a filtered, eligible result set for the user.

Security:

Users can only see the data in the view.

Data independence:

Once the structure of the view is determined, users can be masked from changes in the table structure

Basic use of views

1) Store the contents of the/etc/passwd file into the user table under db library

[root@mysql_59 ~]# cp /etc/passwd /var/lib/mysql-files/

[root@mysql_59 ~]# ls /var/lib/mysql-files/

mysql> create table db.user(

-> username char(64),

-> password char(1),

-> uid int(2),

-> gid int(2),

-> comment char(64),

-> homedir char(64),

-> shell char(64));

Query OK, 0 rows affected (0.30 sec)

mysql> load data infile "/var/lib/mysql-files/passwd" into table db.user fields terminated by ":" lines terminated by "\n";

Query OK, 41 rows affected (0.18 sec)

Records: 41 Deleted: 0 Skipped: 0 Warnings: 0

mysql> alter table db.user add id int(2) primary key auto_increment first;

Query OK, 0 rows affected (0.72 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> use db;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> desc user;

+----------+----------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+----------+------+-----+---------+----------------+

| id | int(2) | NO | PRI | NULL | auto_increment |

| username | char(64) | YES | | NULL | |

| password | char(1) | YES | | NULL | |

| uid | int(2) | YES | | NULL | |

| gid | int(2) | YES | | NULL | |

| comment | char(64) | YES | | NULL | |

| homedir | char(64) | YES | | NULL | |

| shell | char(64) | YES | | NULL | |

+----------+----------+------+-----+---------+----------------+

8 rows in set (0.00 sec)

Create View Syntax

create view reputation as SQL query statements;

create view name (field list) as SQL query statement;

View the information status of all tables in the current library

show table status\G;

show table status where comment="view"\G;

mysql> create

3) Create view v1 structure and data user table fields, records are the same

mysql> create view v1 as select * from user;

Query OK, 0 rows affected (0.10 sec)

View View Specific Command

show create view name\G;

Use of Views:

query record

select field name list from view name where condition;

mysql> select username from v1 where id=10;

+----------+

| username |

+----------+

| operator |

+----------+

1 row in set (0.00 sec)

insert records

insert into view name (field list) values(field value list);

mysql> insert into user(username,uid) values("jack",88);

Query OK, 1 row affected (0.15 sec)

Update View Name set Field Name = Value where Condition;

update history

update view name set field name = value where condition;

ysql> update v1 set comment="AAAA" where username="root";

Query OK, 1 row affected (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from v1 where username="root";

+----+----------+----------+-------+------+---------+---------+-----------+

| id | username | password | uid | gid | comment | homedir | shell |

+----+----------+----------+-------+------+---------+---------+-----------+

| 1 | root | x | 44444 | 0 | AAAA | /root | /bin/bash |

+----+----------+----------+-------+------+---------+---------+-----------+

1 row in set (0.00 sec)

+----+----------+----------+-------+------+---------+---------+-----------+

| id | username | password | uid | gid | comment | homedir | shell |

+----+----------+----------+-------+------+---------+---------+-----------+

| 1 | fuck | x | 44444 | 0 | AAAA | /root | /bin/bash |

+----+----------+----------+-------+------+---------+---------+-----------+

1 row in set (0.00 sec)

delete records

delete from view name where condition

mysql> delete from v1 where username="fuck";

Query OK, 1 row affected (0.07 sec)

4) Create view v2 only user table shell is/bin/bash user information

mysql> create view v2 as select shell from user;

Query OK, 0 rows affected (0.12 sec)

5) Delete view drop view name;

mysql> drop view v2;

Query OK, 0 rows affected (0.19 sec)

Set field alias

Field aliases in the view cannot be repeated, so the top alias is required.

Format:

create view name

as

select table alias. Source field name as field alias

from source table name table alias left join source table name table alias

on condition|;

delimiter //

create procedure db.p11()

begin

if 1=2 then

select from db.user where id=2;

else

select from db user where id=2;

end if;

end

//

delimiter ;call dbp11(1)

mysql> delimiter //

mysql> create procedure db.p18()

-> begin

-> declare x int default 1;

-> while x select x;

-> set x=x+1;

-> end while;

-> end

-> //

Query OK, 0 rows affected (0.06 sec)

mysql> call db.p18();

mysql> delimiter //

mysql> create procedure db.p23()

-> begin

-> declare x int default 1;

-> loab1:while x select x;

-> set x=x+1;

-> if x=3 then

-> leave loab1;

-> end if;

-> end while;

-> end

-> //

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.