含义:虚拟表,和普通表一样使用
MySQL 5.0.1 版本出现的新特性,是通过表动态生成的数据
比如:舞蹈班和普通班级的对比,领导检查想看舞蹈班,各班临时紧急抽调,领导走了临时组的舞蹈班就散了
创建语法的关键字 是否实际占用物理空间 使用
视图 createview 只是保存了sql逻辑 增删改查,只是一般不能增删改
表 createtable 保存了数据 增删改查
特点:
• 重用sql语句
• 简化复杂的sql操作,不必知道它的查询细节
• 保护数据,提高安全性
#案例:查询姓张的学生名和专业名#没用视图SELECT stuname,majorname
FROM stuinfo s
INNERJOIN major m ON s.`majorid`= m.`id`WHERE s.`stuname`LIKE'张%';#用了视图CREATEVIEW v1
ASSELECT stuname,majorname
FROM stuinfo s
INNERJOIN major m ON s.`majorid`= m.`id`;SELECT*FROM v1 WHERE stuname LIKE'张%';
1.2 视图的创建【CREATE VIEW…】
语法:
createview 视图名
as
查询语句;# 查询姓名中包含a字符的员工名、部门名和工种信息#①创建CREATEVIEW 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%';
1.3 视图的修改【CREATE OR REPLACE… \ ALTER VIEW…】
语法一:
createorreplaceview 视图名
as
查询语句;CREATEORREPLACEVIEW myv3
ASSELECTAVG(salary),job_id
FROM employees
GROUPBY job_id;
语法二:
alterview 视图名
as
查询语句;ALTERVIEW myv3
ASSELECT*FROM employees;
1.4 视图的删除【DROP…】
语法:
dropview 视图名,视图名,...;DROPVIEW myv1,myv2,myv3;
1.5 视图的更新【INSERT \ UPDATE \ DELETE】
插入
INSERTINTO myv1 VALUES('张飞','zf@qq.com');
修改
UPDATE myv1 SET last_name ='张无忌'WHERE last_name='张飞';
删除
DELETEFROM myv1 WHERE last_name ='张无忌';
具备以下特点的视图不允许更新:
① 包含以下关键字的sql语句:分组函数、distinct、groupby、having、union或者unionallCREATEORREPLACEVIEW myv1
ASSELECTMAX(salary) m,department_id
FROM employees
GROUPBY department_id;SELECT*FROM myv1;#更新UPDATE myv1 SET m=9000WHERE department_id=10;#失败
② 常量视图
CREATEORREPLACEVIEW myv2
ASSELECT'john' NAME;SELECT*FROM myv2;#更新UPDATE myv2 SET NAME='lucy';#失败
③ Select中包含子查询
CREATEORREPLACEVIEW myv3
ASSELECT department_id,(SELECTMAX(salary)FROM employees) 最高工资
FROM departments;SELECT*FROM myv3;#更新UPDATE myv3 SET 最高工资=100000;#失败
④ joinCREATEORREPLACEVIEW 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_name='Whalen';#成功INSERTINTO myv4 VALUES('陈真','xxxx');#失败
⑤ from一个不能更新的视图
CREATEORREPLACEVIEW myv5
ASSELECT*FROM myv3;SELECT*FROM myv5;#更新UPDATE myv5 SET 最高工资=10000WHERE department_id=60;#失败
⑥ where子句的子查询引用了from子句中的表
CREATEORREPLACEVIEW myv6
ASSELECT last_name,email,salary
FROM employees
WHERE employee_id IN(SELECT manager_id
FROM employees
WHERE manager_id ISNOTNULL);SELECT*FROM myv6;#更新UPDATE myv6 SET salary=10000WHERE last_name ='k_ing';#失败
根据输入的女神名,返回对应的男神名和魅力值
CREATEPROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)BEGINSELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
JOIN beauty b
ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;END $
#调用CALL myp5('小昭',@name,@cp)$
SELECT@name,@cp$
3.2.4 创建带inout模式参数的存储过程
传入a和b两个值,最终a和b都翻倍并返回
CREATEPROCEDURE myp6(INOUT a INT,INOUT b INT)BEGINSET a=a*2;SET b=b*2;END $
#调用SET@m=10$
SET@n=20$
CALL myp6(@m,@n)$
SELECT@m,@n$
3.3 存储过程的删除、查看【DROP PROCEDURE… \ SHOW CREATE PROCEDURE…】
返回公司的员工个数
DELIMITER $;CREATEFUNCTION myf1()RETURNSINTBEGINDECLARE c INTDEFAULT0;#定义局部变量SELECTCOUNT(*)INTO c #赋值FROM employees;RETURN c;END $
SELECT myf1()$
4.2.2 有参有返回
根据部门名,返回该部门的平均工资
CREATEFUNCTION myf3(deptName VARCHAR(20))RETURNSDOUBLEBEGINDECLARE sal DOUBLE;SELECTAVG(salary)INTO sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=deptName;RETURN sal;END $
SELECT myf3('IT')$
4.3 函数的删除、查看【DROP FUNCTION… \ SHOW CREATE FUNCTION…】