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

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• SQL Server 检测是不是数字型数据的两种方法
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 深入理解spring框架：剖析多线程模式下数据库连接
• 数据可视化是如何帮助大型企业提高效率的？
• redis 数据库
• openpyxl模块--------------------------------重复数据
• Django客户端应用1向服务端应用2发送POST请求并接收解析数据
• 数据可视化是如何帮助我们高效了解数据的？

数据库 数据 MySQL数据mysql 数据库 数据mysql elasticsearch数据mysql mybatis数据mysql pymysql数据mysql 备份 数据库 数据mysql 语法 数据库 数据mysql 进度 数据库 数据mysql mysql数据库 数据 系统 性能 数据库 数据mysql