MySQL数据库-3
第14章_视图
#第14章_视图
/*
1.视图的理解
视图,可以看做是一个虚拟表,本身是不存储数据的。
视图的本质,就可以看做是存储起来的SELECT语句
视图中SELECT语句中涉及到的表,称为基表
针对视图做DML操作,会影响到对应的基表中的数据。反之亦然。
视图本身的删除,不会导致基表中数据的删除。
视图的应用场景:针对于小型项目,不推荐使用视图。
针对于大型项目,可以考虑使用视图。
视图的优点:简化查询;控制数据的访问
*/
CREATE TABLE emps
AS
SELECT *
FROM atguigudb.`employees`;
CREATE TABLE depts
AS
SELECT *
FROM atguigudb.`departments`;
SELECT *
FROM emps;
#针对于单表
CREATE VIEW vu_emps
AS
SELECT employee_id,last_name,salary
FROM emps;
SELECT *
FROM vu_emps;
#5.修改视图
CREATE OR REPLACE VIEW vu_emps
AS
SELECT employee_id,last_name,salary,email
FROM emps
WHERE salary>5000;
#6。册除视图
DROP VIEW IF EXISTS vu_emps
第15章_存储过程与函数
#第15章_存储过程与函数
#1.创建存储过程
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END $
DELIMITER ;
#2.存储过程的调用
CALL select_all_data()
#类型2:带OUT
#举例4:创建存储过程show_min_salary(),查看"emps“表的最低薪资值。并将最低薪资
#通过oUT参数"ms"输出
DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms
FROM emps;
END $
DELIMITER ;
CALL show_min_salary(@ms);
#查看变量值
SELECT @ms
#举例5:创建存储过程show_someone_salary(),查看"emps"表的某个员工的薪资,
#并用IN参数empname输入员工姓名。
DELIMITER $
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
SELECT salary
FROM emps
WHERE last_name=empname;
END $
DELIMITER ;
CALL show_someone_salary('Abel');
##类型4:带IN和oUT
#举例6:创建存储过程show_someone_salary2(),查看"emps"表的某个员工的薪资,
#并用IN参数empname输入员工姓名,用ouT参数empsalary输出员工薪资。
DELIMITER $
CREATE PROCEDURE show_someone_salary1(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN
SELECT salary INTO empsalary
FROM emps
WHERE last_name=empname;
END $
DELIMITER ;
SET @empname='Abel';
CALL show_someone_salary1(@empname,@empsalary);
SELECT @empsalary;
#2.存储函数
#举例1:创建存储函数,名称为email by_name (),参数定义为空,
#该函数查询Abel的email,并返回,数据类型为字符串型。
DESC emps
DELIMITER $
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGIN
RETURN(SELECT email FROM emps WHERE last_name='Abel');
END $
DELIMITER ;
SELECT email_by_name()
#创建函数前执行此语句,保证函数的创建会成功
SET GLOBAL log_bin trust_function_creators = l;
#3.存储过程、存储函数的查看
#1.使用sHOw CREATE语句查看存储过程和函数的创建信息
SHOW CREATE PROCEDURE show_mgr_name ;
#5.存储过程、函数的册除
DROP FUNCTION IF EXISTS count_by_idd;
第16章 变量、流程控制与游标
#第16章变量、流程控制与游标
#1.变量:系统变量(全局系统变量、会话系统变量)v3用户自定义变量
#2.查看系统变量
#查询全局系统变量
SHOW GLOBAL VARIABLES;#633
#查询会话系统变量
SHOW SESSION VARIABLES;#657
SHOW VARIABLES;#默认查询的是会话系统变量
#l.3查看指定系统变量
SELECT @@global.max_connecttions;
SELECT @@session.max_connecttions;
SELECT @@global.character_set_client;
#1.4修改系统变量的值
#方式l:
SET @@global.max_connections = 16l;
#方式2:
SET GLOBAL max_connections = 171;
#全局系统变量:针对于当前的数据库实例是有效的,一旦重启mysql服务,就失效了。
#会话系统变量:
#方式l:
SET@@session.character _set_client = ' gbk ' ;
#方式2:
SET SESSION character_set_client = ' gbk ';
#针对于当前会话是有效的,一旦结束会话,重新建立起新的会话,就失效了。
#l.5用户变量
/*
用户变量:会话用户变量vs 局部变量
会话用户变量:使用"@"开头,作用域为当前会话。
局部变量:只能使用在存储过程和存储函数中的。*/
#l.6会话用户变量
#方式1
SET @m1=1;
SET @m2=2;
SET @m3=@m1+@m2;
SELECT @m3;
#方式2
SELECT @count:=COUNT(*) FROM emps;
SELECT AVG(salary) INTO @avg_sal FROM emps;
SELECT @avg_sal
#1.7局部变量
/*
1、局部变量必须满足:
-使用DECLARE声明
-声明并使用在BEGIN ...END中(使用在存储过程、函数中)
-DECLARE的方式声明的局部变量必须声明在BEGIN中的首行的位置。
2、声明格式:
DECLARE变量名类型[default 值];#如果没有DEFAULT子句,初始值为NULL
*/
DELIMITER $
CREATE PROCEDURE test_var()
BEGIN
#声明局部变量
DECLARE a INT DEFAULT 0;
DECLARE b INT;
#DECLARE a,b INT DEFAULT 0;
DECLARE emp_name VARCHAR(25);
#赋值
SET a=1;
SET b:=2;
SELECT last_name INTO emp_name FROM emps WHERE employee_id=101;
#使用
SELECT a,b,emp_name;
END $
DELIMITER ;
CALL test_var();
#程序出错的处理机制
#2.2定义条件
#格式:DECLARE错误名称CONDITION FOR错误码(或错误条件)
#重新定义存储过程,体现错误的处理程序
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition ()
BEGIN
#声明处理程序
#处理方式l:
DECLARE CONTINUE HANDLER FOR 1048 SET prc_value = -l;
#处理方式2:
#DECLARE CONTINUE HANDLER FOR sqlstate '23000'SET @prc_value = -l;
SET @x = l;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3 ;
END //
DELIMITER ;
3.流程控制
#3.流程控制
#3.1 分支结构之iF
DELIMITER $
CREATE PROCEDURE test_if()
BEGIN
DECLARE email VARCHAR(25) DEFAULT 'aaa';
IF email IS NULL
THEN SELECT 'email is null';
ELSE
SELECT 'email is not null';
END IF;
END $
DELIMITER ;
CALL test_if();
#3.2分支结构之case
#演示1:case ... when ...then ... 类似Java的switch
#演示2:case when ... then ....类似Java的if else
#举例2:声明存储过程"update_salary _by_eid4",定义IN参数emp_id,输入员工编号。
#判断该员工薪资如果低于9000元,就更新薪资为9000元;薪资大于等于9000元且低于10000的,
#但是奖金比例为NULL的,就更新奖金比例为0.01;其他的涨薪100元。
DELIMITER $
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
#局部变量的声明
DECLARE emp_sal DOUBLE;#记录员工的工资
DECLARE bonus DOUBLE; #记录员工的奖金率
#局部变量的赋值
SELECT salary INTO emp_sal FROM emps WHERE employee_id=emp_id;
SELECT commission_pct INTO bonus FROM emps WHERE employee_id=emp_id;
CASE
WHEN emp_sal<9000 THEN UPDATE emps SET salary=9000 WHERE employee_id=emp_id;
WHEN emp_sal<10000 AND bonus IS NULL THEN UPDATE emps SET commission_pct=0.01 WHERE employee_id=emp_id;
ELSE UPDATE emps SET salary=salary+100 WHERE employee_id=emp_id;
END CASE;
END $
DELIMITER ;
SELECT *
FROM emps
WHERE employee_id IN(103,104,105);
CALL update_salary_by_eid4(103)
CALL update_salary_by_eid4(104)
CALL update_salary_by_eid4(105)
#4.l循环结构之LOOP
DELIMITER $
CREATE PROCEDURE test_loop()
BEGIN
DECLARE num INT DEFAULT 1;
loop_lable:LOOP
SET num= num + 1;
IF num>=10 THEN LEAVE loop_lable;
END IF;
END LOOP loop_lable;
SELECT num;
END $
DELIMITER ;
CALL test_loop()
#4.2循环结构之WHiLE
#举例2:市场环境不好时,公司为了渡过难关,决定暂时降低大家的薪资。
#声明存储过程"update_salary_while ()",声明ouT参数num,输出循环次数
#存储过程中实现循环给大家降薪,薪资降为原来的90%。直到全公司的平均薪资
#达到5000结束。并统计循环次数。
DELIMITER $
CREATE PROCEDURE update_salary_while (OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE;#记录平均工资
DECLARE while_count INT DEFAULT 0;#记录循环次数
SELECT AVG(salary) INTO avg_sal FROM emps;
WHILE avg_sal>5000
DO
UPDATE emps SET salary=0.9*salary;
SET while_count=while_count+1;
#更新数据
SELECT AVG(salary) INTO avg_sal FROM emps;
END WHILE;
SET num=while_count;
END $
DELIMITER ;
CALL update_salary_while(@num);
SELECT @num;
SELECT AVG(salary)
FROM emps
#4.3 循环结构之REPEAT
/*
[repeat_label: ]REPEAT
循环体的语句
UNTIL结束循环的条件表达式
END REPEAT[repeat_label]
*/
DELIMITER $
CREATE PROCEDURE test_repeat()
BEGIN
DECLARE num INT DEFAULT 1;
REPEAT
SET num=num+1;
UNTIL num>=10
END REPEAT;
SELECT num;
END $
DELIMITER ;
CALL test_repeat()
#leave与Java的break相同
#iterate与Java的continue相同
#4.游标
#与Java的迭代器相同
#举例:创建存储过程"get_count by_limit total_salary ()",声明IN参数limit total_salary,
#DOUBLE类型;声明ouT参数total_count,INr类型。函数的功能可以实现累加薪资最高的几个员工的薪资值,
#直到薪资总和达到limit_total_salary参数的值,返回累加的人数给total_count
DELIMITER $
CREATE PROCEDURE get_count_by_limit_total_salary (IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
#声明局部变量
DECLARE sum_sal DOUBLE DEFAULT 0;#记录累加的工资总额
DECLARE emp_sal DOUBLE;#记录每一个员工的工资
DECLARE emp_count INT DEFAULT 0;#记录累加的人数
#1.声明游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM emps ORDER BY salary DESC;
#2.打开游标
OPEN emp_cursor;
REPEAT
#3.使用游标
FETCH emp_cursor INTO emp_sal;
SET sum_sal=sum_sal+emp_sal;
SET emp_count=emp_count+1;
UNTIL sum_sal>=limit_total_salary
END REPEAT;
SET total_count=emp_count;
#4.关闭游标
CLOSE emp_cursor;
END $
DELIMITER ;
CALL get_count_by_limit_total_salary(200000,@total_count);
SELECT @total_count
第17章触发器
#第17章触发器
#o创建触发器
#创建名称为before insert_test_tri的触发器,向test_trigger数据表插入数据之前,
#|向test trigger log数据表中插入before insert的日志信息。
delimiter $
create trigger before_test_tri
before insert on dept1
for each row
begin
insert into test2(a,b)
values(1,2);
end $
delimiter ;
insert into dept1(dept_name)
values('aaa')
select *
from dept1
desc dept1
select *
from test2
DESC test2
drop TRIGGER before_test_tri
#2.查看触发器
show triggers;
第18章_MySQL8其它新特性
#第18章_MySQL8其它新特性
#新特性1:窗口函数
#1.ROW_NUMBER()函数
#ROW_NUMBER()函数能够对数据中的序号进行顺序显示。
#2.RANK()函数
#使用RANK()函数能够对序号进行并列排序,并且会跳过重复的序号,比如序号为1、1、3。
#1. PERCENT_RANK()函数
#PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。
#二、新特性2:公用表表达式
WITH cte_emp
AS(SELECT DISTINCT department_id FROM emps)
SELECT *
FROM depts d JOIN cte_emp e
ON d.department_id=e.department_id
SELECT *
FROM cte_emp
标签:salary,SET,数据库,DELIMITER,emps,MySQL,SELECT,emp
From: https://www.cnblogs.com/xin-zhi-suo-xiang/p/17931260.html