宝盒官方网站,软件开发培训学校三八妇女节,vs2008做网站,优秀品牌设计案例分析视图
含义#xff1a;理解成一张虚拟的表#xff0c;和普通表一样使用 mysql5.1版本出现的新特性#xff0c;是通过表动态生成的数据 比如#xff1a;舞蹈班和普通班级的对比
视图和表的区别#xff1a;
名称创建语法的关键字使用占用物理空间视图create view增删改查理解成一张虚拟的表和普通表一样使用 mysql5.1版本出现的新特性是通过表动态生成的数据 比如舞蹈班和普通班级的对比
视图和表的区别
名称创建语法的关键字使用占用物理空间视图create view增删改查只是一般不能增删改不占用仅仅保存的是sql逻辑表create table增删改查占用
视图的好处
1、sql语句提高重用性效率高
2、和表实现了分离提高了安全性视图的创建
语法
CREATE VIEW 视图名
AS
查询语句;视图的增删改查
1、查看视图的数据 ★SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_namePartners;2、插入视图的数据
INSERT INTO my_v4(last_name,department_id) VALUES(虚竹,90);3、修改视图的数据UPDATE my_v4 SET last_name 梦姑 WHERE last_name虚竹;4、删除视图的数据
DELETE FROM my_v4;某些视图不能更新
包含以下关键字的sql语句分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表视图逻辑的更新
#方式一
CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id100;方式二:
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;SELECT * FROM test_v7;视图的删除
DROP VIEW test_v1,test_v2,test_v3;视图结构的查看
DESC test_v7;
SHOW CREATE VIEW test_v7;一、创建视图
语法 create view 视图名 as 查询语句;
#案例查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid m.id
WHERE s.stuname LIKE 张%;CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid m.id;SELECT * FROM v1 WHERE stuname LIKE 张%;#一、创建视图
/*
语法
create view 视图名
as
查询语句;*/
USE myemployees;#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
ASSELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id d.department_id
JOIN jobs j ON j.job_id e.job_id;#②使用
SELECT * FROM myv1 WHERE last_name LIKE %a%;#2.查询各部门的平均工资级别#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;#②使用
SELECT myv2.ag,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.ag BETWEEN g.lowest_sal AND g.highest_sal;#3.查询平均工资最低的部门信息SELECT * FROM myv2 ORDER BY ag LIMIT 1;#4.查询平均工资最低的部门名和工资CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.department_idd.department_id;
二、视图的修改
方式一
语法 create or replace view 视图名 as 查询语句;
方式二
语法 alter view 视图名 as 查询语句;
#二、视图的修改#方式一
/*
create or replace view 视图名
as
查询语句;*/
SELECT * FROM myv3 CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;#方式二
/*
语法
alter view 视图名
as
查询语句;*/
ALTER VIEW myv3
AS
SELECT * FROM employees;三、删除视图
语法drop view 视图名,视图名,…;
#三、删除视图/*语法drop view 视图名,视图名,...;
*/DROP VIEW emp_v1,emp_v2,myv3;
四、查看视图
#四、查看视图DESC myv3;SHOW CREATE VIEW myv3;
五、视图的更新
#五、视图的更新CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1IFNULL(commission_pct,0)) annual salary
FROM employees;CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;SELECT * FROM myv1;
SELECT * FROM employees;
#1.插入INSERT INTO myv1 VALUES(张飞,zfqq.com);#2.修改
UPDATE myv1 SET last_name 张无忌 WHERE last_name张飞;#3.删除
DELETE FROM myv1 WHERE last_name 张无忌;#具备以下特点的视图不允许更新#①包含以下关键字的sql语句分组函数、distinct、group by、having、union或者union allCREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;SELECT * FROM myv1;#更新
UPDATE myv1 SET m9000 WHERE department_id10;#②常量视图
CREATE OR REPLACE VIEW myv2
ASSELECT john NAME;SELECT * FROM myv2;#更新
UPDATE myv2 SET NAMElucy;#③Select中包含子查询CREATE OR REPLACE VIEW myv3
ASSELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资100000;#④join
CREATE OR REPLACE VIEW myv4
ASSELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id d.department_id;#更新SELECT * FROM myv4;
UPDATE myv4 SET last_name 张飞 WHERE last_nameWhalen;
INSERT INTO myv4 VALUES(陈真,xxxx);#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
ASSELECT * FROM myv3;#更新SELECT * FROM myv5;UPDATE myv5 SET 最高工资10000 WHERE department_id60;#⑥where子句的子查询引用了from子句中的表CREATE OR REPLACE VIEW myv6
ASSELECT last_name,email,salary
FROM employees
WHERE employee_id IN(SELECT manager_idFROM employeesWHERE manager_id IS NOT NULL
);#更新
SELECT * FROM myv6;
UPDATE myv6 SET salary10000 WHERE last_name k_ing;