MySQL从5.0.1版本开始提供视图功能。一种 虚拟存在的表，行和列的数据来自定义视图的查询中使用的表 ，并且是在使用视图时 动态生成 的，只 保存了sql逻辑，不保存查询结果。（临时的，可重复利用的）
视图的应用场景：
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂
视图的好处
保护数据，提高了安全性
简化复杂的sql操作，不必知道它的查询细节
保护数据，提高了安全性
6.2 使用视图 -- 视图 /* 创建语法的关键字 是否实际占用物理空间 使用 视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改 表 create table 保存了数据 增删改查 */ #案例：查询姓张的学生名和专业名 #不使用视图 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 查询语句; */ #1.查询姓名中包含a字符的员工名、部门名和工种信息 # ①创建 CREATE VIEW myv1 AS SELECT 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_id`=d.`department_id`; -- 二、视图的修改 #方式一： /* 语法： create or replace view 视图名 as 查询语句; */ SELECT * FROM myv3; # 创建或修改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 emp_v1,emp_v2,myv3; -- 四、查看视图 DESC myv3; SHOW CREATE VIEW myv3; -- 五、视图的更新(更改视图中的数据) #视图1(包含新增的annual salary列) CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary" FROM employees; #视图2(只包含原始表中的列) CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email FROM employees; SELECT * FROM myv1; SELECT * FROM employees; #1.插入(插入后原始表也会插入，所以不能插入原始表中没有的列) INSERT INTO myv1 VALUES('张飞','zf@qq.com'); #2.修改(修改后原始表也会修改) UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞'; #3.删除(删除后原始表也会修改) DELETE FROM myv1 WHERE last_name = '张无忌'; ### 具备以下特点的视图不允许更新 # 1.包含以下关键字的sql语句：分组函数、distinct、group by, # having、union或者union all CREATE OR REPLACE VIEW myv1 AS SELECT MAX(salary) m,department_id FROM employees GROUP BY department_id; SELECT * FROM myv1; UPDATE myv1 SET m=9000 WHERE department_id=10;#更新 # 2.常量视图 CREATE OR REPLACE VIEW myv2 AS SELECT 'john' NAME; SELECT * FROM myv2; UPDATE myv2 SET;#更新 # 3.Select中包含子查询 CREATE OR REPLACE VIEW myv3 AS SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资 FROM departments; SELECT * FROM myv3; UPDATE myv3 SET 最高工资=1000;#更新 # 4.join CREATE OR REPLACE VIEW myv4 AS SELECT 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';#可以update INSERT INTO myv4 VALUES('陈真','xxxx');#不可以insert # 5.from一个不能更新的视图 CREATE OR REPLACE VIEW myv5 AS SELECT * FROM myv3; SELECT * FROM myv5; UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;#更新 # 6.where子句的子查询引用了from子句中的表 CREATE OR REPLACE VIEW myv6 AS SELECT last_name,email,salary FROM employees WHERE employee_id IN( SELECT manager_id FROM employees WHERE manager_id IS NOT NULL ); SELECT * FROM myv6; UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';#更新 7. 存储过程和函数 7.1 存储过程 -- 存储过程和函数 /* 存储过程和函数： 类似于java中的方法 好处： 提高代码的重用性 简化操作 */ -- 存储过程 /* 含义：一组预先编译好的SQL语句的集合，理解成批处理语句 好处: 提高代码的重用性 简化操作 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率 */ #一、创建语法 CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体（一组合法的SQL语句） END #注意： /* 1.参数列表包含三部分： 参数模式 参数名 参数类型 举例：in stuname varchar(20) 2.参数模式： ① in：该参数可以作为输入，也就是该参数需要调用方传入值 ② out：该参数可以作为输出，也就是该参数可以作为返回值 ③ inout：该参数既可以作为输入又可以作为输出，也就是该 参数既需要传入值，又可以返回值 3.如果存储过程体仅仅只有一句话，begin end可以省略 4.存储过程体中的每条sql语句的结尾要求必须加分号。 存储过程的结尾可以使用 delimiter 重新设置 =>语法： delimiter 结束标记 =>案例： delimiter $ */ #二、调用语法 CALL 存储过程名(实参列表); -- 创建和调用存储过程案例演示 #情况1：空参列表 #案例：插入到admin表中五条记录 DELIMITER $ #设置结束标记 CREATE PROCEDURE myp1() BEGIN INSERT INTO admin(username,`password`) VALUES('john1','0000'),('lily','0000'), ('rose','0000'),('jack','0000'),('tom','0000'); END $ #调用 CALL myp1()$ #情况2：创建带in模式参数的存储过程 #案例1：创建存储过程实现根据女生名，查询对应的男生信息 CREATE PROCEDURE myp2(IN beautyName VARCHAR(20)) BEGIN SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name=beautyName; END $ #调用 CALL myp2('柳岩')$ #案例2 ：创建存储过程实现，用户是否登录成功 CREATE PROCEDURE myp4(IN username VARCHAR(20),IN `PASSWORD` VARCHAR(20)) BEGIN DECLARE result INT DEFAULT 0;#声明并初始化 SELECT COUNT(*) INTO result#赋值 FROM admin WHERE admin.username = username AND admin.password = `PASSWORD`; SELECT IF(result>0,'成功','失败');#使用 END $ #调用 CALL myp3('张飞','8888')$ #情况3：创建out 模式参数的存储过程 #案例1：根据输入的女神名，返回对应的男神名 CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20)) BEGIN SELECT bo.boyname INTO boyname FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id = bo.id WHERE b.name=beautyName ; END $ #调用 CALL myp7('小昭',@name)$ SELECT @name$ #案例2：根据输入的女神名，返回对应的男神名和魅力值 CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) BEGIN SELECT boys.boyname ,boys.usercp INTO boyname,usercp FROM boys RIGHT JOIN beauty b ON b.boyfriend_id = boys.id WHERE b.name=beautyName ; END $ #调用 CALL myp7('小昭',@name,@cp)$ SELECT @name,@cp$ #情况4：创建带inout模式参数的存储过程 #案例1：传入a和b两个值，最终a和b都翻倍并返回 CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT) BEGIN SET a=a*2; SET b=b*2; END $ #调用 SET @m=10$ SET @n=20$ CALL myp8(@m,@n)$ SELECT @m,@n$ #三、删除存储过程 #语法：drop procedure 存储过程名 DROP PROCEDURE p1; DROP PROCEDURE p2,p3;#错误，一次只能删除一个 #四、查看存储过程的信息 DESC myp2;#错误，只能查看表或视图 SHOW CREATE PROCEDURE myp2; -- 案例题 #一、创建存储过程实现传入用户名和密码，插入到admin表中 CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20)) BEGIN INSERT INTO admin(admin.username,`PASSWORD`) VALUES(username,loginpwd); END $ #二、创建存储过程实现传入女神编号，返回女神名称和女神电话 CREATE PROCEDURE test_pro2(IN id INT,OUT `name` VARCHAR(20),OUT phone VARCHAR(20)) BEGIN SELECT b.`name`,b.phone INTO `name`,phone FROM beauty b WHERE b.id = id; END $ #三、创建存储存储过程或函数实现传入两个女神生日，返回大小 CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT) BEGIN SELECT DATEDIFF(birth1,birth2) INTO result; END $ #四、创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回 CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50)) BEGIN SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate; END $ CALL test_pro4(NOW(),@str)$ SELECT @str $ #五、创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串 # 如 传入 ：小昭 # 返回： 小昭 AND 张无忌 DROP PROCEDURE test_pro5 $ CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20),OUT str VARCHAR(50)) BEGIN SELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO str FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id = bo.id WHERE b.name=beautyName; END $ CALL test_pro5('柳岩',@str)$ SELECT @str $ #六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录 DROP PROCEDURE test_pro6$ CREATE PROCEDURE test_pro6(IN startIndex INT,IN size INT) BEGIN SELECT * FROM beauty LIMIT startIndex,size; END $ CALL test_pro6(3,5)$ 7.2 函数 -- 函数 /* 含义： 一组预先编译好的SQL语句的集合，理解成批处理语句 优点： 提高代码的重用性 简化操作 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率 函数与存储过程区别： 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、 批量更新 函数：有且仅有1个返回，适合做处理数据后返回一个结果 */ #一、创建语法 CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 BEGIN 函数体 END /* 注意： 1.参数列表 包含两部分：参数名 参数类型 2.函数体： 肯定会有return语句，如果没有会报错，如果return语句没有放 在函数体的最后也不报错，但不建议。如最后写：RETURN 值; 3.函数体中仅有一句话，则可以省略begin end 4.使用 delimiter语句设置结束标记 */ #二、调用语法 SELECT 函数名(参数列表) # 创建与调用函数案例演示 #情况1：无参有返回 #案例：返回公司的员工个数 CREATE FUNCTION myf1() RETURNS INT BEGIN DECLARE c INT DEFAULT 0; #定义局部变量 SELECT COUNT(*) INTO c #赋值 FROM employees; RETURN c; END $ SELECT myf1()$ #情况2：有参有返回 #案例1：根据员工名，返回它的工资 CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE BEGIN SET @sal=0;#定义用户变量 SELECT salary INTO @sal #赋值 FROM employees WHERE last_name = empName; RETURN @sal; END $ SELECT myf2('k_ing') $ #案例2：根据部门名，返回该部门的平均工资 CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE BEGIN DECLARE sal DOUBLE ; SELECT AVG(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')$ #三、查看函数 SHOW CREATE FUNCTION myf3; #四、删除函数 DROP FUNCTION myf3; -- 案例题 #一、创建函数，实现传入两个float，返回二者之和 CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT BEGIN DECLARE SUM FLOAT DEFAULT 0; SET SUM=num1+num2; RETURN SUM; END $ SELECT test_fun1(1,2)$ 8. 流程控制结构 #流程控制结构 /* 分类： 顺序 分支 循环 */ -- 一、分支结构 /* 分类： if函数 case结构 if结构 */ # 1.if函数 /* 语法： if(条件,值1,值2) 功能： 实现双分支 执行顺序： 如果条件成立，则返回值1，否则返回值2 应用位置： 任意地方 */ # 2.case结构 /* 语法： 情况1：类似于switch，一般用于等值判断 case 变量或表达式 when 值1 then 语句1; when 值2 then 语句2; ... else 语句n; end case; 情况2：类似多重if，一般用于区间判断 case when 条件1 then 语句1; when 条件2 then 语句2; ... else 语句n; end case; 使用位置： 作为表达式嵌套在其他语句中可以放在任何位置， 作为独立的语句使用就只能放在BEGIN END中 */ #案例：创建函数，实现传入成绩，如果成绩>90,返回A， # 如果成绩>80,返回B，如果成绩>60,返回C，否则返回D CREATE FUNCTION test_case(IN score FLOAT) RETURNS CHAR BEGIN DECLARE ch CHAR DEFAULT 'A'; CASE WHEN score>90 THEN SET ch='A'; WHEN score>80 THEN SET ch='B'; WHEN score>60 THEN SET ch='C'; ELSE SET ch='D'; END CASE; RETURN ch; END $ SELECT test_case(56)$ #3.if结构 /* 语法： if 条件1 then 语句1; elseif 条件2 then 语句2; .... 【else 语句n;】 end if; 功能： 类似于多重if 使用位置： 只能应用在begin end 中 */ #案例：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80, # 返回B，如果成绩>60,返回C，否则返回D CREATE FUNCTION test_if(INT score FLOAT) RETURNS CHAR BEGIN IF score>90 THEN SET ch='A'; ELSEIF score>80 THEN SET ch='B'; ELSEIF score>60 THEN SET ch='C'; ELSET ch='D'; END IF; END $ SELECT test_if(87)$ #二、循环结构 /* 分类： weile loop repeat 循环控制： iterate:类似于continue leave:类似于break */ #1.while结构 /* 语法： 【标签:】while 循环条件 do 循环体; end while【 标签】; */ #2.loop结构 /* 语法： 【标签:】loop 循环体; end loop 【标签】; 特点： 可以用来模拟简单的死循环 不想形成死循环就要搭配leave使用 */ #3.repeat结构 /* 语法： 【标签：】repeat 循环体; until 结束循环的条件 end repeat 【标签】; */ -- 演示while的使用 #1.没有添加循环控制语句 #案例：批量插入，根据次数插入到admin表中多条记录 DROP PROCEDURE pro_while1$ CREATE PROCEDURE pro_while1(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i<=insertCount DO INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666'); SET i=i+1; END WHILE; END $ CALL pro_while1(100)$ #2.添加leave语句 #案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止 TRUNCATE TABLE admin$ DROP PROCEDURE test_while1$ CREATE PROCEDURE test_while1(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; a:WHILE i<=insertCount DO INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000'); IF i>=20 THEN LEAVE a; END IF; SET i=i+1; END WHILE a; END $ CALL test_while1(100)$ #3.添加iterate语句 #案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次 TRUNCATE TABLE admin$ DROP PROCEDURE test_while1$ CREATE PROCEDURE test_while1(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 0; a:WHILE i<=insertCount DO SET i=i+1; IF MOD(i,2)!=0 THEN ITERATE a; END IF; INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000'); END WHILE a; END $ -- 案例题 /*一、已知表stringcontent 其中字段： id 自增长 content varchar(20) 向该表插入指定个数的，随机的字符串 */ DROP TABLE IF EXISTS stringcontent; CREATE TABLE stringcontent( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(20) ); DELIMITER $ CREATE PROCEDURE test_randstr_insert(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz'; DECLARE startIndex INT;#代表初始索引 DECLARE len INT;#代表截取的字符长度 WHILE i<=insertcount DO #代表初始索引，随机范围1-26 SET startIndex=FLOOR(RAND()*26+1); #代表截取长度，随机范围1-（20-startIndex+1） SET len=FLOOR(RAND()*(20-startIndex+1)+1); INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len)); SET i=i+1; END WHILE; END $ CALL test_randstr_insert(10)$ CALL test_while1(100)$