视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。
临时表搜索
SELECT
*
FROM
(
SELECT
nid,
NAME
FROM
tb1
WHERE
nid > 2
) AS A
WHERE
A. NAME > 'alex';
1、创建视图
--格式：CREATE VIEW 视图名称 AS SQL语句
CREATE VIEW v1 AS
SELET nid,
name
FROM
A
WHERE
nid > 4
2、删除视图
--格式：DROP VIEW 视图名称 DROP VIEW v1
3、修改视图
-- 格式：ALTER VIEW 视图名称 AS SQL语句
ALTER VIEW v1 AS
SELET A.nid,
B. NAME
FROM
A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
A.id > 2
AND C.nid < 5
4、使用视图
使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。
select * from v1
实践操作
MariaDB [sqlexample]> select * from score; +-----+------------+-----------+-----+ | sid | student_id | course_id | num | +-----+------------+-----------+-----+ | 1 | 1 | 1 | 10 | | 2 | 1 | 2 | 9 | | 5 | 1 | 4 | 66 | | 6 | 2 | 1 | 8 | | 8 | 2 | 3 | 68 | | 9 | 2 | 4 | 99 | | 10 | 3 | 1 | 77 | | 11 | 3 | 2 | 66 | | 12 | 3 | 3 | 87 | | 13 | 3 | 4 | 99 | | 14 | 4 | 1 | 79 | | 15 | 4 | 2 | 11 | | 16 | 4 | 3 | 67 | | 17 | 4 | 4 | 100 | | 18 | 5 | 1 | 79 | | 19 | 5 | 2 | 11 | | 20 | 5 | 3 | 67 | | 21 | 5 | 4 | 100 | | 22 | 6 | 1 | 9 | | 23 | 6 | 2 | 100 | | 24 | 6 | 3 | 67 | | 25 | 6 | 4 | 100 | | 26 | 7 | 1 | 9 | | 27 | 7 | 2 | 100 | | 28 | 7 | 3 | 67 | | 29 | 7 | 4 | 88 | | 30 | 8 | 1 | 9 | | 31 | 8 | 2 | 100 | | 32 | 8 | 3 | 67 | | 33 | 8 | 4 | 88 | | 34 | 9 | 1 | 91 | | 35 | 9 | 2 | 88 | | 36 | 9 | 3 | 67 | | 37 | 9 | 4 | 22 | | 38 | 10 | 1 | 90 | | 39 | 10 | 2 | 77 | | 40 | 10 | 3 | 43 | | 41 | 10 | 4 | 87 | | 42 | 11 | 1 | 90 | | 43 | 11 | 2 | 77 | | 44 | 11 | 3 | 43 | | 45 | 11 | 4 | 87 | | 46 | 12 | 1 | 90 | | 47 | 12 | 2 | 77 | | 48 | 12 | 3 | 43 | | 49 | 12 | 4 | 87 | | 52 | 13 | 3 | 87 | | 53 | 2 | 2 | 65 | +-----+------------+-----------+-----+ 48 rows in set (0.00 sec) MariaDB [sqlexample]> create view tem1 as select * from score where course_id in (1,2); Query OK, 0 rows affected (0.02 sec) MariaDB [sqlexample]> select * from tem1; +-----+------------+-----------+-----+ | sid | student_id | course_id | num | +-----+------------+-----------+-----+ | 1 | 1 | 1 | 10 | | 2 | 1 | 2 | 9 | | 6 | 2 | 1 | 8 | | 10 | 3 | 1 | 77 | | 11 | 3 | 2 | 66 | | 14 | 4 | 1 | 79 | | 15 | 4 | 2 | 11 | | 18 | 5 | 1 | 79 | | 19 | 5 | 2 | 11 | | 22 | 6 | 1 | 9 | | 23 | 6 | 2 | 100 | | 26 | 7 | 1 | 9 | | 27 | 7 | 2 | 100 | | 30 | 8 | 1 | 9 | | 31 | 8 | 2 | 100 | | 34 | 9 | 1 | 91 | | 35 | 9 | 2 | 88 | | 38 | 10 | 1 | 90 | | 39 | 10 | 2 | 77 | | 42 | 11 | 1 | 90 | | 43 | 11 | 2 | 77 | | 46 | 12 | 1 | 90 | | 47 | 12 | 2 | 77 | | 53 | 2 | 2 | 65 | +-----+------------+-----------+-----+ 24 rows in set (0.01 sec) MariaDB [sqlexample]> show tables; +----------------------+ | Tables_in_sqlexample | +----------------------+ | class | | course | | score | | student | | teacher | | tem1 | | userinfo | +----------------------+ 7 rows in set (0.00 sec) MariaDB [sqlexample]> create view tem2 as select score.sid,score.course_id,score.num,student.sname from score left join student on score.student_id = student.sid where course_id in (1,2); Query OK, 0 rows affected (0.01 sec) MariaDB [sqlexample]> show tables; +----------------------+ | Tables_in_sqlexample | +----------------------+ | class | | course | | score | | student | | teacher | | tem1 | | tem2 | | userinfo | +----------------------+ 8 rows in set (0.00 sec) MariaDB [sqlexample]> select * from tem2; +-----+-----------+-----+-----------+ | sid | course_id | num | sname | +-----+-----------+-----+-----------+ | 1 | 1 | 10 | 马大狗 | | 2 | 2 | 9 | 马大狗 | | 6 | 1 | 8 | 钢蛋 | | 10 | 1 | 77 | 张三 | | 11 | 2 | 66 | 张三 | | 14 | 1 | 79 | 张一 | | 15 | 2 | 11 | 张一 | | 18 | 1 | 79 | 张二 | | 19 | 2 | 11 | 张二 | | 22 | 1 | 9 | 张四 | | 23 | 2 | 100 | 张四 | | 26 | 1 | 9 | 铁锤 | | 27 | 2 | 100 | 铁锤 | | 30 | 1 | 9 | 李三 | | 31 | 2 | 100 | 李三 | | 34 | 1 | 91 | 李一 | | 35 | 2 | 88 | 李一 | | 38 | 1 | 90 | 李二 | | 39 | 2 | 77 | 李二 | | 42 | 1 | 90 | 李四 | | 43 | 2 | 77 | 李四 | | 46 | 1 | 90 | 如花 | | 47 | 2 | 77 | 如花 | | 53 | 2 | 65 | 钢蛋 | +-----+-----------+-----+-----------+ 24 rows in set (0.00 sec) MariaDB [sqlexample]> alter view tem2 as select * from student; Query OK, 0 rows affected (0.00 sec) MariaDB [sqlexample]> select * from tem2; +-----+--------+----------+-----------+ | sid | gender | class_id | sname | +-----+--------+----------+-----------+ | 1 | 男 | 1 | 马大狗 | | 2 | 女 | 1 | 钢蛋 | | 3 | 男 | 1 | 张三 | | 4 | 男 | 1 | 张一 | | 5 | 女 | 1 | 张二 | | 6 | 男 | 1 | 张四 | | 7 | 女 | 2 | 铁锤 | | 8 | 男 | 2 | 李三 | | 9 | 男 | 2 | 李一 | | 10 | 女 | 2 | 李二 | | 11 | 男 | 2 | 李四 | | 12 | 女 | 3 | 如花 | | 13 | 男 | 3 | 刘三 | | 14 | 男 | 3 | 刘一 | | 15 | 女 | 3 | 刘二 | | 16 | 男 | 3 | 刘四 | | 17 | 女 | 1 | 鸭蛋 | | 18 | 女 | 1 | 鸭蛋1 | | 19 | 女 | 1 | 鸭蛋2 | | 20 | 女 | 1 | 鸭蛋3 | +-----+--------+----------+-----------+ 20 rows in set (0.00 sec) MariaDB [sqlexample]> select * from tem1; +-----+------------+-----------+-----+ | sid | student_id | course_id | num | +-----+------------+-----------+-----+ | 1 | 1 | 1 | 10 | | 2 | 1 | 2 | 9 | | 6 | 2 | 1 | 8 | | 10 | 3 | 1 | 77 | | 11 | 3 | 2 | 66 | | 14 | 4 | 1 | 79 | | 15 | 4 | 2 | 11 | | 18 | 5 | 1 | 79 | | 19 | 5 | 2 | 11 | | 22 | 6 | 1 | 9 | | 23 | 6 | 2 | 100 | | 26 | 7 | 1 | 9 | | 27 | 7 | 2 | 100 | | 30 | 8 | 1 | 9 | | 31 | 8 | 2 | 100 | | 34 | 9 | 1 | 91 | | 35 | 9 | 2 | 88 | | 38 | 10 | 1 | 90 | | 39 | 10 | 2 | 77 | | 42 | 11 | 1 | 90 | | 43 | 11 | 2 | 77 | | 46 | 12 | 1 | 90 | | 47 | 12 | 2 | 77 | | 53 | 2 | 2 | 65 | +-----+------------+-----------+-----+ 24 rows in set (0.00 sec) MariaDB [sqlexample]> select * from tem1 where sid > 16; +-----+------------+-----------+-----+ | sid | student_id | course_id | num | +-----+------------+-----------+-----+ | 18 | 5 | 1 | 79 | | 19 | 5 | 2 | 11 | | 22 | 6 | 1 | 9 | | 23 | 6 | 2 | 100 | | 26 | 7 | 1 | 9 | | 27 | 7 | 2 | 100 | | 30 | 8 | 1 | 9 | | 31 | 8 | 2 | 100 | | 34 | 9 | 1 | 91 | | 35 | 9 | 2 | 88 | | 38 | 10 | 1 | 90 | | 39 | 10 | 2 | 77 | | 42 | 11 | 1 | 90 | | 43 | 11 | 2 | 77 | | 46 | 12 | 1 | 90 | | 47 | 12 | 2 | 77 | | 53 | 2 | 2 | 65 | +-----+------------+-----------+-----+ 17 rows in set (0.00 sec)
浙公网安备 33010602011771号