约束视图储存流程控制

一、约束
1、列级约束
#常见约束
/*
NOT NULL：非空
该字段的值是必填的，如果不设置该约束，该字段默认是可以为空
DEFAULT:默认
该字段有默认值
CHECK:检查，mysql语法支持，但效果不支持
该字段值可以加以限制，比如年龄可以控制在0-120之间

PRIMARY KEY:主键
该字段值不可以重复
1、不可以为空
2、一个表中只能有一个主键，但是可以多个字段做组合主键
UNIQUE:唯一约束
该字段值不可以重复
1、可以为空
2、一个表中可以有多个唯一键，可以有组合唯一键
FOREIGN KEY:外键
用于限制多表的关系
1、从表的该字段值必须来自于主表的关联列的值
2、从表和主表的关联列的类型必须一样
3、主表的关联列必须是主键
4、在从表中设置外键
5、一个表中可以添加多个外键
6、插入数据时，先插入主表
删除数据时，先删除从表

分类：
位置 支持
列级约束 列的后面 除了外键
表级约束 表的后面 除了非空和默认

#CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型 列级约束,
表级约束

2、表级约束
#添加表级约束
#语法
CREATE TABLE stuinfo(
id INT ,
stuName VARCHAR(20),
gender CHAR,
age INT UNSIGNED,
【CONSTRAINT 约束名】约束类型(字段)
);

3、修改表时添加约束
#1.添加非空
ALTER TABLE stuinfo MODIFY COLUMN gender CHAR NOT NULL;

#2.添加默认
ALTER TABLE stuinfo MODIFY COLUMN age INT UNSIGNED DEFAULT 18;

#3.添加主键
#①方式一：列级约束的做法
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

#②方式二：表级约束的做法
#语法：
#ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型 (字段);

4、修改表时删除约束
#1.删除非空
ALTER TABLE stuinfo MODIFY COLUMN gender CHAR ;

#2.删除默认
ALTER TABLE stuinfo MODIFY COLUMN age INT UNSIGNED;

#3.删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT ;
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);
ALTER TABLE stuinfo DROP INDEX uq_stuinfo;

#查看指定表的所有索引
SHOW INDEX FROM stuinfo;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_grade;

二、事物概念
1、事务的概念
事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。

事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

为确保数据库中数据的一致性,数据的操纵应当是离散的成组的逻辑单元:当它全部完成时,数据的一致性可以保持,而当这个单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。

2、事务的特点
事务的ACID(acid)属性
（1） 原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

（2）一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

（3）隔离性（Isolation）
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

（4）持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

3、事物的使用

以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
COMMIT 或 ROLLBACK 语句
DDL 或 DCL 语句（自动提交）
用户会话正常结束
系统异常终了

SET autocommit=0;#取消自动提交
START TRANSACTION;#开启事务
ROLLBACK;#回滚事务
COMMIT;#提交事务

4、数据库的隔离级别
（1）对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.

不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.

幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.

（2）数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.

（3）一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱.

5、数据库提供的 4 种事务隔离级别

Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED
Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ

6、在 MySql 中设置隔离级别
（1）每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别

（2）查看当前的隔离级别: SELECT @@tx_isolation;

（3）设置当前 mySQL 连接的隔离级别:
set transaction isolation level read committed;

（4）设置数据库系统的全局的隔离级别:
set global transaction isolation level read committed;

三、事物实操
1、事物-sql
概念：数据库区分于其他文件系统的一种特征
好处：
希望执行一些操作时，要么同时执行，要么同时不执行，最终达到数据的一致性
特性：【★】
ACID
分类：
1、隐式事务
比如：insert、update、delete本身就是一个事务
2、显式事务
特征：具有明显的开启和结束的标记
set autocommit=0; 取消隐式事务自动提交的功能
start transaction;开启事务
…增删改查sql语句
commit;提交事务
rollback;回滚事务

并发事务容易出现的问题：
脏读：一个事务读到了另一个事务未提交的数据
不可重复读：在一个事务执行期间，两次查询读到的结果不一致
幻读：一个事务进行插入操作，没有提交。另一个事务读到了没有提交的数据

如何解决？
read uncommitted： 脏读、不可重复、幻读
read committed:不可重复读、幻读
repeatable read:幻读（mysql5.5版本之前会出现幻读，但5.5之后不会出现）
serializable：串行化（性能极低）
mysql默认的是repeatable read

如何查看隔离级别？
select @@tx_isolation;
如何设置隔离级别？
set @@tx_isolation=‘read-uncommitted’;
set [global] transaction isolation level read uncommitted;
*/

SHOW VARIABLES LIKE ‘autocommit’;

#开启事务
SET autocommit=0;#取消自动提交
START TRANSACTION;#开启新事务，可选
INSERT INTO beauty VALUES(NULL,‘付明明2’,‘女’,NOW(),‘112’,NULL,NULL);

ROLLBACK;#回滚事务
COMMIT;#提交事务

SELECT * FROM beauty;

#演示delete和truncate在事务中的区别
delete可以回滚， truncate不能回滚

2、并发事物 dos 演示

SELECT * FROM admin
INSERT INTO admin VALUES(1,‘xiaoming’,‘000000’)

dos1
mysql -uroot -proot
mysql > use test;
mysql> set autocommit = 0;
mysql> insert into account values(11,’john’,’000’)
mysql>commit
mysq> set transaction isolation level read uncommitted; //最小的隔离级别 //没有效果需要重启dos
mysql> set autocommit = 0;
mysql > insert into account values(11,’jhok,’8989’’)

dos2
mysql -uroot -proot
mysql > use test;
mysql > select @@tx_isolation
SERIALIZABLE
mysq> set transaction isolation level read uncommitted;
mysql> set global transaction isolation level read uncommitted; //第二种设置方式// 没有效果需要重开dos
mysql> set autocommit = 0;
mysql > select * from account;

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

2、视图应用场景
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂

3、示例

CREATE VIEW my_v1
AS
SELECT studentname,majorname
FROM student s
INNER JOIN major m
ON s.majorid=m.majorid
WHERE s.majorid=1;

4、视图的好处
使用简单
安全
数据独立

5、创建或者修改视图
创建视图的语法：
create [or replace] view view_name
As select_statement
[with|cascaded|local|check option]

修改视图的语法
alter view view_name
As select_statement
[with|cascaded|local|check option]

6、视图更新性
视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。
（1）包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
（2）常量视图
（3）Select中包含子查询
（4）join
（5）from一个不能更新的视图
（6）where子句的子查询引用了from子句中的表

7、删除视图的语法
用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限。
drop view [if exists] view_name,view_name …[restrict|cascade]

8、查看视图结构的语法
show tables;
如果需要查询某个视图的定义，可以使用show create view命令进行查看
show create view view_name \G

视图实操
1、视图的创建和使用
#视图
/*
视图：基于原始表得到的一个虚拟的查询结果集
视图和表的区别：

1. 表中的数据占用物理空间，视图不占用。
2. 表中的数据是实际存在的，视图的数据是执行查询时动态生成的

视图的好处：
1、提高查询语句的重用性，调用简单
2、安全
3、独立

视图的应用场景：
1、多个地方用到相同的查询结果
2、查询结果使用了比较复杂的sql语句
一、创建视图：
create [or replace] view 视图名
as
select 语句;

特点：
1、视图的使用和表的使用是一致的，都支持增删改查，而且语法一样
2、如果视图的查询语句中包含一些特殊关键字，则不支持增删改
分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表

二、修改视图
#方式1.如果视图存在，则修改；如果视图不存在，则创建

	CREATE OR REPLACE VIEW 视图名
	AS
	新的查询语句
#方式2.修改视图

	ALTER VIEW 视图名 AS 
	新的查询语句

三、删除视图
可以同时删除多个视图。
语法：drop view 视图1，视图2;

*/

#一、创建视图
CREATE VIEW myv1
AS
SELECT * FROM beauty
WHERE id>3;

#二、使用视图

#1.查询
SELECT * FROM myv1;
SELECT * FROM beauty;

#2.插入
INSERT INTO myv1 VALUES(14,‘罗大磊’,‘男’,‘1998-1-1’,‘111’,NULL,NULL);
INSERT INTO myv1 VALUES(2,‘马小宁’,‘男’,‘1998-1-1’,‘111’,NULL,NULL);

#3.更新
UPDATE myv1 SET NAME=‘宋茜’ WHERE id=6;

#4 .删除
DELETE FROM myv1 WHERE id=14;

#具备以下特点的视图不允许更新
/*包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表
*/

#① 关键字
CREATE VIEW myv2
AS
SELECT COUNT(*),boyfriend_id
FROM beauty
GROUP BY boyfriend_id;

SELECT * FROM myv2;
UPDATE myv2 SET boyfriend_id = 10 WHERE boyfriend_id=1;

#②常量视图
DROP VIEW myv3;
CREATE VIEW myv3
AS
SELECT ‘china’ c;

SELECT * FROM myv3;
UPDATE myv3 SET c=‘Jap’;

#③select中包含子查询
CREATE VIEW myv4
AS
SELECT (
SELECT MAX(boyfriend_id)
FROM beauty
) a;
SELECT * FROM myv4;

UPDATE myv4 SET a=100;

#④join update好用 但是insert 不好用
CREATE VIEW myv5
AS
SELECT NAME,boyname
FROM beauty b
JOIN boys bo ON b.boyfriend_id=bo.id;

SELECT * FROM myv5;

UPDATE myv5 SET NAME=‘安娜’ WHERE NAME=‘热巴’;

INSERT INTO myv5 VALUES(‘刘晓婷’,‘刘婷婷’);

#⑤from一个不能更新的视图

CREATE VIEW myv6
AS
SELECT * FROM myv5;

SELECT * FROM myv6;

INSERT INTO myv6 VALUES(‘刘晓婷’,‘刘婷婷’);

#⑥where子句的子查询引用了from子句中的表

2、视图的修改和删除
#修改视图
#方式一：
CREATE OR REPLACE VIEW myv1
AS
SELECT * FROM beauty;
SELECT * FROM myv1;

#方式二：
ALTER VIEW myv1
AS
SELECT id,NAME
FROM beauty;

#四、删除视图
#DROP VIEW 视图名1，视图名2,…
DROP VIEW myv1,myv2,myv3,myv4,myv5;

#五、查看视图结构
DESC myv1;
SHOW CREATE VIEW myv1;

五、存储过程
1、什么是存储过程？
存储过程：
事先经过编译并存储在数据库中的一段sql语句的集合。

使用好处：
1、简化应用开发人员的很多工作
2、减少数据在数据库和应用服务器之间的传输
3、提高了数据处理的效率
2、创建存储过程和函数
（1）创建存储过程
create procedure 存储过程名 ([proc_parameter[,…]])
[characteristic…]routine_body

（2）创建函数
create function 函数名([func_parameter[,…]])
returns type
[characteristic…]routine_body

proc_parameter:
[in|out|inout] param_name type
Func_paramter:
param_name type
Type:
任何有效的mysql数据类型
Characteristic:
language sql(默认，且推荐)
|[not] deterministic
|{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definer|invoker}
|comment ‘string’
Rountine_body:
有效的sql 过程语句

3、调用存储过程或函数

（1）调用存储过程
call 存储过程名(参数列表)

（2）调用函数
Select 函数名(参数列表)

4、修改存储过程或函数

修改存储过程：
alter procedure 存储过程名 [charactristic…]

修改函数：
alter function 函数名 [charactristic…]

characteristic:
{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definer|invoker}
|comment ‘string’

5、删除存储过程或函数
说明：一次只能删除一个存储过程或者函数，并且要求有该过程或函数的alter routine 权限

删除存储过程：
drop procedure [if exists] 存储过程名

删除函数：
drop function [if exists] 函数名

6、查看存储过程或函数
1.查看存储过程或函数的状态：
show {procedure|function} status like 存储过程或函数名

2.查看存储过程或函数的定义：
show create {procedure|function} 存储过程或函数名

3.通过查看information_schema.routines了解存储过程和函数的信息（了解）
select * from rountines where rounine_name =存储过程名|函数名

sqlyog 这个第三方客户端，不支持BEGIN 和 END，但是navicat这个第三方客户端支持begin和end
存储过程是不能修改的，非要修改，只能删除了，重新建
#存储过程
#一、创建

#语法：
#CREATE PROCEDURE 存储过程的名称(【IN/OUT/INOUT】 参数名 参数类型)
#BEGIN

主体

#END

#类似于java的方法

#修饰符 返回类型 方法名(参数列表){

方法体

#}

###1.参数对应的in、out、inout的意思
#IN 代表该参数 作为输入（传统意义的参数）
#OUT 代表该参数 作为输出 （传统意义的返回值）
#INOUT 代表该参数既可以作为输入又可以作为输出 （参数+返回）
#支持：多个IN、多个OUT、多个INOUT
#支持：无参

###2.主体
#增删改查语句都可以、变量、循环、条件等

#每条SQL语句结尾分号
#DELIMITER 结束符
#默认结束符：分号，假如不想分号结尾，则可以自己设置

#二、调用存储过程
/*
①调用无参的存储过程
CALL 存储过程名()
②调用in类型参数的存储过程
CALL 存储过程名(常量值)
③调用out类型参数的存储过程
call 存储过程名（@变量名） $$select@变量名$$
④调用inout类型参数的存储过程
set @变量名=值 $$CALL存储过程名（@变量名）$$
SELECT @变量名 $$

#三、用户变量：
定义语法： set @变量名 ;
赋值语法：
方式一：普通赋值
set @变量名:=值;或set @变量名=值;
select @变量名:=值;

	方式二：通过查询结果为变量赋值
	select 字段|表达式 into 变量名
	from 表名 【where 条件】	

六、标识列
#标识列
/*
又称为自增长列

语法：
AUTO_INCREMENT

特点：设置为标识列的字段，值不用手动插入，自动会有序列值,不用担心重复问题
①标识列字段的类型必须为数值型
②标识列字段必须为一个key
主键或唯一或外键
③一个表中至多有一个标识列
④ 如果用delete删除，标识列的值从断点开始
如果用truncate删除，标识列的值从1开始

七、流程控制
1、变量
#流程控制结构
/*
顺序
分支
循环
一、变量
二、分支
三、循环
*/

#一、变量
#1、用户变量
#声明并赋值语法：

SET @变量名=值;

或

SET @变量名:=值;

或

SELECT @变量名:=值;

#赋值：

方式一：

同上述三种

方式二：

SELECT 字段|常量|表达式 INTO 变量

【FROM 表】;

#使用语法：

SELECT @变量名;

#2、局部变量
#声明的语法：
DECLARE 变量名 类型 【DEFAULT 值】;

#赋值的语法：
方式一：
同上述三种
方式二：
SELECT 字段|常量|表达式 INTO 变量
【FROM 表】;
#使用语法：
SELECT 变量名;

#注意：用户变量和局部变量的区别

		作用域					声明位置						
用户变量	比较大					可以在BEGIN END 的外面也可以在里面
局部变量	只能在声明该变量的BEGIN END中		只能在BEGIN END 的里面

2、分支
#分支结构
#1、IF函数
#语法：
SELECT IF(10>3,‘哈哈’,‘嘎嘎’);

#应用在：BEGIN END 的里面和外面都行

#2、CASE END
#应用在：BEGIN END 的里面和外面都行

#3、CASE END
#类似于switch
#4、IF ELSEIF ELSE
#类似于多重IF
#语法：
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
ELSEIF 条件3 THEN 语句3;
ELSE 语句n;
END IF ;

3、循环
循环结构
#1、WHILE结构
#语法：

【标签:】WHILE 循环条件 DO
	循环体;
END WHILE 【标签】;

#2、REPEAT结构
#语法：

【标签:】REPEAT
循环体;
UNTIL  结束条件 END REPEAT 【标签】;

#while和repeat的对比
执行顺序 循环条件 语法不同
#WHILE 先判断后执行 循环成立的条件 WHILE关键字
#REPEAT 先执行后判断 循环结束的条件 REPEAT关键字