Mysql-TCL事务 视图 存储过程和函数 流程结构控制

标识列

#标识列
/*
又称自增长列
含义：可以不用手动的插入值，系统提供默认的序列值
特点：
	标识列必须和主键搭配一般为(非空，主键)
	标识列只能有一个
	标识列的类型只能时数值型
	可以通过set auto_increment=3设置步长	可以设置开始的index值

*/
#一 创建表时设置表示列
DROP TABLE tab;
SELECT * FROM tab;
CREATE TABLE IF NOT EXISTS tab(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO tab VALUES(10,'join');
INSERT INTO tab VALUES(NULL,'join');
INSERT INTO tab(NAME) VALUES('盖世凯');

SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment=3;

#二 修改表时设置标识列
ALTER TABLE tab MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#三 修改表时删除标识列
ALTER TABLE tab MODIFY COLUMN id INT;

mysql事务

#TCL
/*
Transaction Control Language 事务控制语言
事务：
一个或者多个sql语句组成的一个执行单元，这个执行单元要么全部执行，要么全部不执行
事务特点：
ACID
原子性：一个事务不可以在分割，要么执行要么不执行
一致性：一个事务执行会使数据从一个一直状态到另一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据
eg:微信转账；
*/
#事务的创建
/*
隐式事务；事务没有明显的开启和结束标记 eg：insert，update ,delete语句
显示事务：事务具有明显的开启和结束标记 

前提：必须先设置自动提交功能为禁用  
SET autocommit=0;
步骤1：开启事务
set autocommit=0
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句一；
语句二；
....
步骤三：结束事务
commit:提交事务
rollback：回滚事务

eg:
开启事务的语句
update 表 set 张三丰的余额=500 where name='张三丰’

update 表 set 盖世凯的余额=1500 where name='盖世凯’
结束事务的语句

*/
#修改事务
SET autocommit=0;
#展示事务的形态
SHOW VARIABLES LIKE 'autocommit'
#展示各个事务的状态
SHOW ENGINES;

#案例
SELECT * FROM account
CREATE TABLE IF NOT EXISTS account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);
INSERT INTO account(username,balance)
VALUES('盖世凯',1000),('孟津',500);

#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=500 WHERE username='盖世凯';
UPDATE account SET balance=1000 WHERE username='孟津';
#结束事务
COMMIT;
ROLLBACK;
#以上演示 可以得出会出现
/*
脏读 不可重复读 幻读

事务的隔离级别
			脏读		不可重复读	幻读
read uncommitted	y		y		y	
read committed		n		y		y
repeatable read		n		n		y
serializable		n		n		n

mysql中默认是第三个隔离级别 repeatable read
oracle中默认第二个隔离级别  read committed
查看隔离级别
select @@tx_isolation
设置隔离级别
set session|global transaction isolation level 隔离级别；
*/
/*
类似于断点的使用
savepoint

*/
#演示savepoint的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存电脑
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

视图

USE student;
#视图
/*
含义：虚拟表，和普通的表一样使用
mysql5.1之后出现了新特性，是通过表的动态生成数据
比如：舞蹈班和普通版的对比
	创建语法的关键字	是否实际占用了物理空间	使用
视图	create view		只保存了sql逻辑		增删改查，一般只是用查
表	create table		保存了数据		增删改查

*/
#案例：查询姓张的学生名和专业名(普通)
SELECT studentname,majorname
FROM student s
JOIN major m ON s.`majorid`=m.`majorid`
WHERE s.`studentname` LIKE '张%';
#用视图的方法
CREATE VIEW v1
AS
SELECT studentname,majorname
FROM student s
JOIN major m ON s.`majorid`=m.`majorid`;

SELECT * FROM v1 WHERE studentname LIKE '张%';
#一：创建视图
/*
语法:
	create view 视图名
	as
	查询语句;
*/
#练习
USE myemployees
#1查询邮箱只能够包含a字符的员工名，部门名和工种信息
CREATE VIEW v1
AS
SELECT last_name,department_name,e.job_id
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 v1 WHERE last_name LIKE '%a%';
#2查询各部门的平均工资级别
CREATE VIEW v2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

SELECT v2.ag,g.grade_level
FROM v2
JOIN job_grades g
ON v2.ag BETWEEN g.lowest_sal AND g.highest_sal;
#3查询平均工资最低的部门信息
CREATE VIEW v3 
AS 
SELECT AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
SELECT * FROM v3;
#方法二
SELECT * FROM v2 ORDER BY ag LIMIT 1;
#查询平均工资最低的部门名和工资
CREATE VIEW v4
AS
SELECT * FROM v2 ORDER BY ag LIMIT 1;

SELECT d.*,v.ag
FROM departments d
JOIN v4 v ON v.`department_id`=d.`department_id`
#视图的好处
/*
重用sql语句
简化复杂的sql操作，不必知道他的查询细节
保护数据，提高安全性
*/

#二：修改视图
#方式一：
/*
create or replace view 视图名
as
查询语句；
*/
SELECT * FROM v3
CREATE OR REPLACE VIEW v3
AS 
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
#方式二：
/*
alter view 视图名
as
查询语句
*/
ALTER VIEW v3
AS
SELECT * FROM employees

#三：删除视图
/*
语法：drop view 视图名，视图名....
*/
DROP VIEW v3;

#四：查看视图
DESC v2;
SHOW CREATE VIEW v2

#视图总练习
#1创建视图emp_v1，要求查询电话号码以’011‘开头的员工姓名和工资，邮箱
CREATE OR REPLACE VIEW emp_v1
AS 
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%'
SELECT * FROM emp_v1
#2创建视图v2,要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW v2
AS
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;

SELECT d.*
FROM departments d
JOIN v2 v ON v.`department_id`=d.`department_id`
#5视图的更新
CREATE OR REPLACE VIEW v1
AS
SELECT last_name,email,salary*12*(1+ IFNULL(commission_pct,0))
FROM employees;
SELECT * FROM v1;


#1插入
INSERT INTO v1 VALUES('盖世凯','gs.com',0)
#2修改
UPDATE v1 SET last_name='孟津' WHERE last_name='盖世凯'
#3删除
DELETE FROM v1 WHERE last_name='盖世凯'

#具备一下特点的视图不允许更新
/*
1：包含一下关键字的sql语句：分组函数，distinct,group by,having,union 或者union all
2：select 中包含子查询
3:常量视图
4：join
5：from一个不能更新的视图
6：where子句的子查询引用了from子句中的表
*/
#1：包含一下关键字的sql语句：分组函数，distinct,group by,having,union 或者union all
CREATE OR REPLACE VIEW v1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;

SELECT *FROM v1;
#更新
UPDATE v1 SET m=9000 WHERE department_id=10;
#2常量视图
CREATE OR REPLACE VIEW v2
AS
SELECT 'join' NAME;
SELECT * FROM v2;
#更新
UPDATE v2 SET NAME='盖世凯'
#3：select 中包含子查询
CREATE OR REPLACE VIEW v2
AS
SELECT (SELECT MAX(salary) FROM employees) 最高工资
#更新
SELECT * FROM v2
SELECT v2 SET 最高工资 =10000
#4：join
CREATE OR REPLACE VIEW v2
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
#更新
SELECT * FROM v2
UPDATE v2 SET last_name='张飞' WHERE last_name='Whalen';
INSERT INTO v2 VALUES('盖世凯','xxx')
#5：from一个不能更新的视图
CREATE OR REPLACE VIEW v3
AS
SELECT * FROM v2
#更新
SELECT * FROM v3
UPDATE v3 SET last_name='张飞' WHERE last_name='Whalen';
#6：where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW v3
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 v3
UPDATE v3 SET salary=100000 WHERE last_name='Kochhar'

#演示delect 和 truncate 再事务使用时的区别
#演示delect(可以回滚)
SET autocommit=0
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
#演示truncate(永久删除，不可以回滚)
SET autocommit=0
START TRANSACTION;
TRUNCATE FROM account;
ROLLBACK;

视图，事务练习

#视图，事务，约束的练习
#1约束联系
CREATE TABLE book(
	bib INT PRIMARY KEY,
	bname VARCHAR(20) UNIQUE NOT NULL,
	price FLOAT DEFAULT 10,
	btypeId INT,
	FOREIGN KEY(btypeId) REFERENCES bookType(id)
)
#2开启事务 向表中插入1行数据，并结束
SET autocommit=0
START TRANSACTION 
INSERT INTO book(bib,bname) VALUES('1','盖世凯')
ROLLBACK
#3创建视图，实现价格大于100的书名字
CREATE OR REPLACE v1
AS
SELECT bname
FROM book b
JOIN bookType t ON b.bypeid=t.id
WHERE price>100
#4修改视图，实现查询价格再90-120之间的书名和价格
CREATE OR REPLACE v1
AS
SELECT bnname,price
FROM book
WHERE price BETWEEN 90 AND 120;
#5删除刚才创建的视图
DROP VIEW v1

变量

#变量
/*
系统变量：
	全局变量
	会话变量
自定义变量：
	用户变量
	局部变量
*/
/*
一：系统变量
注意：
如果是全局级别，则需要加global,如果是绘画级别的，则需要加session，如果不写默认时session
系统变量由系统提供，不是用户定义，属于服务器层面的
使用的语法：
1，查看所有的系统变量
show GLOBAL | 【session】variables;
2，查看满足条件的部分系统变量
show GLOBAL | 【session】variables like '%char%';
3，查看指定的某个系统变量的值
select @@GLOBAL | 【session】 .系统变量名
4，为某系统变量赋值
方式一：
set GLOBAL | 【session】系统变量名=值;
方式二：
set @@GLOBAL | 【session】.系统变量名=值;
*/
#1全局变量
/*
作用域；服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话(连接)有效，但不能跨重启
*/
#查看所有
SHOW GLOBAL VARIABLES;
#查看部分
SHOW GLOBAL VARIABLES LIKE '%char%';
#查看指定的全局变量的值
SELECT @@global.autocommit
#为某一个指定的全局变量赋值
SET @@global.autocommit=0;

#2会话变量
/*
作用域：仅仅针对于当前的会话(连接)有效
*/
#查看所有
SHOW  VARIABLES;
#查看部分
SHOW VARIABLES LIKE '%char%';
#查看指定的全局变量的值
SELECT @@session.autocommit
#为某一个指定的全局变量赋值
SET @@global.autocommit=0;

#自定义变量
/*
说明：变量是用户自定的，不是由系统
使用步骤：
声明
赋值
使用(查看，比较，运算等)
*/
#1，用户变量
/*
作用域：针对于当前会话(连接)有效，同于会话变量的作用域
赋值操作符： =或:=
1声明并初始化
set @用户变量名=值；
set @用户变量名:=值
select @用户变量名 :=值
2赋值
方式一：通过set或select
	set @用户变量名=值；
	set @用户变量名:=值
	select @用户变量名 :=值
方式二：通过select into
	select 字段 into @变量名
	from 表;
3使用(查看用户变量的值)
select @用户变量名
*/
#案例
SET @Gs=123
SET @Gs='盖世凯'
SELECT COUNT(*) INTO @Gs
FROM employees

SELECT @Gs

#2 局部变量
/*
作用域：仅仅在定义的begin end 中有效
1声明：
DECLARE 变量名 类型
DECLARE 变量名 类型 default 值
2赋值
方式一：通过set或select
	set @用户变量名=值；
	set @用户变量名:=值
	select @用户变量名 :=值
方式二：通过select into
	select 字段 into @变量名
	from 表;
3使用(查看用户变量的值)
select 局部变量名
*/
#用户变量和局部变量pk
/*
		作用域		定义和使用的位置		语法
用户变量	当前会话	会话中的任何地方		必须加@符号 不用限定类型
局部变量	begin end 中 	只能再begin end中且为第一句话	一般不用加@ 用限制类型
*/
#用户变量
SET @m=1;
SET @n=2;
SET @sum=@m+@n;
SELECT @sum;

#局部变量(这里应该防砸begin end)
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

存储过程和函数

#存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
	提高代码的重用性
	简化操作
	
区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量的插入批量的更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果
*/
/*
存储过程：
含义：一组预先编译好的sql语句的集合，理解成批处理语句
1提高代码的重用性
2简化操作
3减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
*/
/*
一：创建语法
crate procedure 存储过程名(参数列表)
begin
	存储过程体(一组合法的sql语句)
end
注意：
1，参数包含三部分
参数模式 参数名 参数类型
eg：
in stuname varchar(20)
参考模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout:该参数可以作为输入又可以作为输出，也就是该参数既需要调用方传入值，又可以返回值
2，如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每一条sql语句都必须加分号
存储过程的结尾可以使用delimiter 重新设置
语法：
delimiter 结束标记
delimiter $

二：调用语法
call 存储过程名(实参列表)；
*/
#测试案例
SELECT * FROM admin

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,PASSWORD)
	VALUES('john1',777),('john2',777),('john3',777),('john4',777),('john5',777);
END  $

CALL myp1()$

#调用in模式参数的存储过程
#案例一：创建存储过程实现，根据女神名，查询对应的男神信息
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()$
#案例二：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN 
	DECLARE result VARCHAR(20) DEFAULT '';#声明并初始化
	SELECT COUNT(*) INTO result#赋值
	FROM admin
	WHERE admin.`username`=username
	AND admin.`password`=PASSWORD;
	SELECT IF (result>0,'成功','失败'); #使用
END$

CALL myp3('john',8888)$

#案例三：根据女神名，返回对应的男神名
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	JOIN beauty b ON bo.id=b.boyfriend_id
	WHERE b.name=beautyName;
END$
CALL myp4('小昭',@bName)$
SELECT @bName$

#案例 创建带inout模式的参数的存储过程
#传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp5(INOUT a INT,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;

END$

#二：删除存储过程
#语法：drop procedure 存储过程名
DROP PROCEDURE myp1;

DROP PROCEDURE myp3,myp2; #一次只能删除一个

#三：查看存储过程的信息
DESC myp2;  #错误
SHOW CREATE PROCEDURE myp2;

#函数
/*
创建语法：
create function 函数名(参数列表) returns 返回类型
begin
	函数体
end

注意：
1参数列表包含两部分：参数名，参数类型
2函数体：可定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不会报错，但不支持
3函数体中进有一句话时，则可以省略begin end
4使用delimiter语句设置结束标记(标记应当对应)

调用语法
select 函数名(参数列表)

*/
#无参有返回值 返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN 
	DECLARE c INT DEFAULT 0;#定义变量
	SELECT COUNT(*) INTO c#赋值
	FROM employees;
	RETURN c;
END$

SELECT myf1()$
#有参有返回值 根据员工名，返回他的工资
CREATE FUNCTION myf2(ename VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;
	SELECT salary INTO @sal
	FROM employees
	WHERE last_name=ename;
	RETURN @sal;
END $
SELECT myf2('kochhar')$
#根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON d.department_id=e.department_id
	WHERE d.department_name=deptName;
	RETURN sal;
END$
SELECT myf3('IT')$
#传进来两个参数，返回两者之和
CREATE FUNCTION test(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN 
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM=num1+num2;
	RETURN SUM;
END$
SELECT test(1,2)$

#三：查看函数
SHOW CREATE FUNCTION myf3;
#四：删除函数
DROP FUNCTION myf3;

流程控制结构

#流程控制函数
/*
顺序结构：程序从上向下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序再男足一定条件的基础上一直执行
*/
#分支结构
/*
if(判断语句，表达式1，表达式2)  还有 if-elseif-else-end
case 结构两种 一个类似于switch 一个类似于 if-else if 都有end结束
所以可以放在 函数中 或着 存储语句中
case 【变量|表达式|字段】
when 判断的值|条件语句 then 返回的值或语句
else 要返回的值
end case;
*/

#循环结构
/*
分类：
while loop repeat
循环控制
iterate 类似于 continue
leave 类似于 break
*/

/*
while 语法
【标签：】while 循环条件 do
		循环体；
	end while 【标签】；

*/
#案例 批量插入，根据次数插入到admin表中多条记录
CREATE PROCEDURE test_while(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('rose',i),'666');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END$
CALL test_while(100)$





/*
loop语法
【标签】loop
		循环体；
	end loop 【标签】
可以用来模拟简单的死循环
*/

/*
repeat语法
【标签】repeat
		循环体
	until 结束循环的条件
	end repeat 【标签】
*/