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 '张%';
创建视图
语法:
1 2 3
create view 视图名 as 查询语句;
案例1:查询姓名中包含a字符的员工名、部门名和工种信息
1 2 3 4 5 6 7 8 9 10 11 12 13
USE myemployees; #①创建 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.查询各部门的平均工资级别
1 2 3 4 5 6 7 8 9 10 11 12
#①创建视图查看每个部门的平均工资 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.查询平均工资最低的部门信息
1
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
4.查询平均工资最低的部门名和工资
1 2 3 4 5 6 7 8 9
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`;
修改视图
方式一:
1 2 3
create or replace view 视图名 as 查询语句;
1 2 3 4 5 6 7
SELECT * FROM myv3
CREATE OR REPLACE VIEW myv3 AS SELECT AVG(salary),job_id FROM employees GROUP BY job_id;
方式二:
1 2 3 4
语法: alter view 视图名 as 查询语句;
1 2 3
ALTER VIEW myv3 AS SELECT * FROM employees;
删除视图
语法:drop view 视图名,视图名,...;
1
DROP VIEW emp_v1,emp_v2,myv3;
查看视图
1 2 3
DESC myv3;
SHOW CREATE VIEW myv3;
视图的更新
1 2 3 4 5 6 7 8 9 10 11 12 13
CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email,salary*12*(1+IFNULL(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.插入
1
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
2.修改
1
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
3.删除
1
DELETE FROM myv1 WHERE last_name = '张无忌';
具备以下特点的视图不允许更新
包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
1 2 3 4 5 6 7 8 9 10
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;
常量视图
1 2 3 4 5 6 7 8 9
CREATE OR REPLACE VIEW myv2 AS
SELECT 'john' NAME;
SELECT * FROM myv2;
#更新 UPDATE myv2 SET NAME='lucy';
select中包含子查询
1 2 3 4 5 6 7 8 9
CREATE OR REPLACE VIEW myv3 AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资 FROM departments;
#更新 SELECT * FROM myv3; UPDATE myv3 SET 最高工资=100000;
join
1 2 3 4 5 6 7 8 9 10 11 12
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'; INSERT INTO myv4 VALUES('陈真','xxxx');
from一个不能更新的视图
1 2 3 4 5 6 7 8 9 10
CREATE OR REPLACE VIEW myv5 AS
SELECT * FROM myv3;
#更新
SELECT * FROM myv5;
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
where子句的子查询引用了from子句中的表
1 2 3 4 5 6 7 8 9 10 11 12 13 14
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';
视图案例
案例1:创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
1 2 3 4 5
CREATE OR REPLACE VIEW emp_v1 AS SELECT last_name,salary,email FROM employees WHERE phone_number LIKE '011%';
案例2:创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
1 2 3 4 5 6 7 8 9 10 11 12
CREATE OR REPLACE VIEW emp_v2 AS SELECT MAX(salary) mx_dep,department_id FROM employees GROUP BY department_id HAVING MAX(salary)>12000;
SELECT d.*,m.mx_dep FROM departments d JOIN emp_v2 m ON m.department_id = d.`department_id`;
查看满足条件的部分系统变量show global|【session】 variables like '%char%';
查看指定的系统变量的值elect @@global|【session】系统变量名;
为某个系统变量赋值 方式一: et global|【session】系统变量名=值; 方式二: et @@global|【session】系统变量名=值;
全局变量
作用域:针对于所有会话(连接)有效,但不能跨重启。
1 2 3 4 5 6 7 8 9
#①查看所有全局变量 SHOW GLOBAL VARIABLES; #②查看满足条件的部分系统变量 SHOW GLOBAL VARIABLES LIKE '%char%'; #③查看指定的系统变量的值 SELECT @@global.autocommit; #④为某个系统变量赋值 SET @@global.autocommit=0; SET GLOBAL autocommit=0;
会话变量
作用域:针对于当前会话(连接)有效
1 2 3 4 5 6 7 8 9 10
#①查看所有会话变量 SHOW SESSION VARIABLES; #②查看满足条件的部分会话变量 SHOW SESSION VARIABLES LIKE '%char%'; #③查看指定的会话变量的值 SELECT @@autocommit; SELECT @@session.tx_isolation; #④为某个会话变量赋值 SET @@session.tx_isolation='read-uncommitted'; SET SESSION tx_isolation='read-committed';
自定义变量
说明:变量由用户自定义,而不是系统提供的。 使用步骤:
声明
赋值
使用(查看、比较、运算等)
用户变量
作用域:针对于当前会话(连接)有效,作用域同于会话变量。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
#赋值操作符:=或:= #①声明并初始化 SET @变量名=值; SET @变量名:=值; SELECT @变量名:=值;
#②赋值(更新变量的值) #方式一: SET @变量名=值; SET @变量名:=值; SELECT @变量名:=值; #方式二: SELECT 字段 INTO @变量名 FROM 表; #③使用(查看变量的值) SELECT @变量名;
局部变量
作用域:仅仅在定义它的begin end块中有效,应用在begin end中的第一句话。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
#①声明 DECLARE 变量名 类型; DECLARE 变量名 类型 【DEFAULT 值】;
#②赋值(更新变量的值) #方式一: SET 局部变量名=值; SET 局部变量名:=值; SELECT 局部变量名:=值; #方式二: SELECT 字段 INTO 具备变量名 FROM 表; #③使用(查看变量的值) SELECT 局部变量名;
案例1:声明两个变量,求和并打印
1 2 3 4 5 6 7 8 9 10 11 12
#用户变量 SET @m=1; SET @n=1; SET @sum=@m+@n; SELECT @sum;
#局部变量 DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 1; DECLARE SUM INT; SET SUM=m+n; SELECT SUM;
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:创建存储过程实现 根据女神名,查询对应的男神信息
1 2 3 4 5 6 7 8 9 10 11 12
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 :创建存储过程实现,用户是否登录成功
1 2 3 4 5 6 7 8 9 10 11 12 13 14
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:根据输入的女神名,返回对应的男神名
1 2 3 4 5 6 7 8 9
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 $
案例2:根据输入的女神名,返回对应的男神名和魅力值
1 2 3 4 5 6 7 8 9 10 11 12 13
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都翻倍并返回
1 2 3 4 5 6 7 8 9 10 11
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 存储过程名
1
DROP PROCEDURE p1;
查看存储过程的信息
1
SHOW CREATE PROCEDURE myp2;
存储过程案例
案例1:创建存储过程实现传入用户名和密码,插入到admin表中
1 2 3 4 5
CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20)) BEGIN INSERT INTO admin(admin.username,PASSWORD) VALUES(username,loginpwd); END $
案例2:创建存储过程实现传入女神编号,返回女神名称和女神电话
1 2 3 4 5 6 7 8
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 $
案例3:创建存储存储过程或函数实现传入两个女神生日,返回大小
1 2 3 4
CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT) BEGIN SELECT DATEDIFF(birth1,birth2) INTO result; END $
案例4:创建存储过程或函数实现传入一个日期,格式化成xx年xx月xx日并返回
1 2 3 4 5 6 7
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 $
案例5:创建存储过程或函数实现传入女神名称,返回:女神 and 男神 格式的字符串
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
如 传入 :小昭 返回: 小昭 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;
SET str= END $
CALL test_pro5('柳岩',@str)$ SELECT @str $
案例6.创建存储过程或函数,根据传入的条目数和起始索引,查询beauty表的记录
1 2 3 4 5 6 7
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)$
函数
含义:一组预先编译好的SQL语句的集合,理解成批处理语句。
提高代码的重用性;
简化操作;
减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率。
区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新;
函数:有且仅有1 个返回,适合做处理数据后返回一个结果。
创建语法
1 2 3 4
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 BEGIN 函数体 END
DECLARE c INT DEFAULT 0;#定义局部变量 SELECT COUNT(*) INTO c#赋值 FROM employees; RETURN c;
END $
SELECT myf1()$
2.有参有返回
案例1:根据员工名,返回它的工资
1 2 3 4 5 6 7 8 9 10 11
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:根据部门名,返回该部门的平均工资
1 2 3 4 5 6 7 8 9 10 11
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')$
案例3:创建函数,实现传入两个float,返回二者之和
1 2 3 4 5 6 7 8
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)$
查看函数
SHOW CREATE FUNCTION myf3;
删除函数
DROP FUNCTION myf3;
流程控制结构
顺序、分支、循环
分支结构
if函数
语法:if(条件,值1,值2) 功能:实现双分支 应用在begin end中或外面
case结构
语法: 情况1:类似于switch
1 2 3 4 5 6
case 变量或表达式 when 值1 then 语句1; when 值2 then 语句2; ... else 语句n; end
情况2:
1 2 3 4 5 6
case when 条件1 then 语句1; when 条件2 then 语句2; ... else 语句n; end
应用在begin end 中或外面
if结构
语法:
1 2 3 4 5
if 条件1 then 语句1; elseif 条件2 then 语句2; .... else 语句n; end if;
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR BEGIN DECLARE ch CHAR DEFAULT 'A'; IF score>90 THEN SET ch='A'; ELSEIF score>80 THEN SET ch='B'; ELSEIF score>60 THEN SET ch='C'; ELSE SET ch='D'; END IF; RETURN ch;
CREATE PROCEDURE test_if_pro(IN sal DOUBLE) BEGIN IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal; ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal; ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal; END IF;
CREATE FUNCTION test_case(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)$
循环结构
分类: while、loop、repeat
循环控制:
iterate类似于 continue,继续,结束本次循环,继续下一次
leave 类似于 break,跳出,结束当前所在的循环
*/
while
语法:
1 2 3
【标签:】while 循环条件 do 循环体; end while【 标签】;
联想:
1 2 3 4
while(循环条件){
循环体; }
loop
语法:
1 2 3
【标签:】loop 循环体; end loop 【标签】;
可以用来模拟简单的死循环
repeat
语法:
1 2 3 4
【标签:】repeat 循环体; until 结束循环的条件 end repeat 【标签】;
1.没有添加循环控制语句
案例1:批量插入,根据次数插入到admin表中多条记录
1 2 3 4 5 6 7 8 9 10 11 12
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语句
案例1:批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
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语句
案例1:批量插入,根据次数插入到admin表中多条记录,只插入偶数次
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
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');
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 SET startIndex=FLOOR(RAND()*26+1);#代表初始索引,随机范围1-26 SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取长度,随机范围1-(20-startIndex+1) INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len)); SET i=i+1; END WHILE;