[大数据]MySQL学习标识列、事务、视图、变量

一、标识列

#标识列
/*
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值


特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值，设置起始值

*/

#一、创建表时设置标识列


DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT  ,
	NAME FLOAT UNIQUE AUTO_INCREMENT,
	seat INT 


);
TRUNCATE TABLE tab_identity;


INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
INSERT INTO tab_identity(NAME) VALUES('lucy');
SELECT * FROM tab_identity;


SHOW VARIABLES LIKE '%auto_increment%';


SET auto_increment_increment=3;

二、事务

#TCL
/*
Transaction Control Language 事务控制语言

事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。


事务的特性：
ACID
原子性（Atomicity）：一个事务不可再分割，要么都执行要么都不执行
一致性（Consistency）：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性（Isolation）：一个事务的执行不受其他事务的干扰
持久性（Durability）：一个事务一旦提交，则会永久的改变数据库的数据.



事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句

delete from 表 where id =1;

显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用

set autocommit=0;

步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...

步骤3：结束事务
commit;提交事务
rollback;回滚事务

savepoint 节点名;设置保存点



事务的隔离级别：
		  脏读		不可重复读	幻读
read uncommitted：√		√		√
read committed：  ×		√		√
repeatable read： ×		×		√
serializable	  ×             ×               ×



mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
# mysql 5 使用
SELECT @@tx_isolation; 
# mysql 8 使用
SELECT @@TRANSACTION_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;




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

update 表 set 郭襄的余额=1500 where name='郭襄' 
结束事务的语句;

*/
# 查看存储引擎
SHOW ENGINES;

# 查看事务是否开启
SHOW VARIABLES LIKE 'autocommit';

#1.演示事务的使用步骤

#开启事务
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE ACCOUNT SET balance = 1000 WHERE username='张无忌';
UPDATE ACCOUNT SET balance = 1000 WHERE username='赵敏';

#结束事务
ROLLBACK;
COMMIT;


#2.演示事务对于delete和truncate的处理的区别

# truncate 不支持回滚  delete支持回滚


#3.演示savepoint 的使用，回滚到指定的点，回滚部分sql语句
SET autocommit=0;
START TRANSACTION;
DELETE FROM ACCOUNT WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM ACCOUNT WHERE id=28;
ROLLBACK TO a;#回滚到保存点


SELECT * FROM ACCOUNT;

三、视图

#视图
/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

MySQL从5.0.1版本开始提供视图功能。一种虚拟
存在的表，行和列的数据来自定义视图的查询中使用的表
，并且是在使用视图时动态生成的，只保存了sql逻辑，不
保存查询结果

视图	create view		只是保存了sql逻辑	增删改查，只是一般不能增删改

表	create table		保存了数据		增删改查


应用场景；
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂
*/

#案例：查询姓张的学生名和专业名
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
查询语句;

*/
USE myemployees;

#1.查询姓名中包含a字符的员工名、部门名和工种信息
SELECT e.last_name,d.department_name,j.job_title
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE e.last_name LIKE '%a%'


CREATE VIEW v1
AS 
SELECT e.last_name,d.department_name,j.job_title
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN jobs j
ON e.job_id = j.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 department_id,ag,grade_level
FROM v2
INNER JOIN job_grades
ON v2.ag BETWEEN lowest_sal AND highest_sal;


#3.查询平均工资最低的部门信息
SELECT *
FROM v2
ORDER BY ag
LIMIT 1

#4.查询平均工资最低的部门名和工资
SELECT d.*,v2.ag
FROM departments d
INNER JOIN v2
ON d.department_id = v2.department_id
ORDER BY v2.ag
LIMIT 1;


#二、视图的修改

#方式一：
/*
create or replace view  视图名
as
查询语句;

*/
SELECT * FROM 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 v2;

SHOW CREATE VIEW v2;

#五、视图的更新

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
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 = '张无忌';



#具备以下特点的视图不允许更新

#①包含以下关键字的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;

#②常量视图
CREATE OR REPLACE VIEW myv2
AS

SELECT 'john' NAME;

SELECT * FROM myv2;

#更新
UPDATE myv2 SET NAME='lucy';


#③Select中包含子查询

CREATE OR REPLACE VIEW myv3
AS

SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;

#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;


#④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';
INSERT INTO myv4 VALUES('陈真','xxxx');



#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS

SELECT * FROM myv3;

#更新

SELECT * FROM myv5;

UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;



#⑥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';

四、变量

#变量
/*
系统变量：
	全局变量
	会话变量

自定义变量：
	用户变量
	局部变量

*/
#一、系统变量
/*
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加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;
SET GLOBAL autocommit=0;


#2》会话变量
/*
作用域：针对于当前会话（连接）有效
*/
#①查看所有会话变量
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、使用（查看、比较、运算等）
*/

#1》用户变量
/*
作用域：针对于当前会话（连接）有效，作用域同于会话变量
*/
#赋值操作符：=或:=
#①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

#②赋值（更新变量的值）
#方式一：
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
#方式二：查询出来的字段是一个值
	SELECT 字段 INTO @变量名
	FROM 表;
#③使用（查看变量的值）
SELECT @变量名;


SET @name = 'lily'
SELECT @name

#2》局部变量
/*
作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
*/
#①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;


#②赋值（更新变量的值）

#方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT 局部变量名:=值;
#方式二：
	SELECT 字段 INTO 具备变量名
	FROM 表;
#③使用（查看变量的值）
SELECT 局部变量名;

#用户变量和局部变量的对比

		作用域			定义位置		语法
用户变量	当前会话		会话的任何地方		加@符号，不用指定类型
局部变量	定义它的BEGIN END中 	BEGIN END的第一句话	一般不用加@,需要指定类型

# 案例

#案例：声明两个变量，求和并打印
#用户变量
SET @m = 1;
SET @n = 2;
SET @sum = @n + @m;
SELECT @sum;

# 局部变量
BEGIN 
DECLARE n INT DEFAULT 1;
DECLARE m INT DEFAULT 1;
DECLARE SUM INT;
SET SUM = n + m;
SELECT SUM;
END