【MySQL】库表管理+事务视图+流程控制

库表管理

创建： create
修改： alter
删除： drop

1.库管理

1.创建：

#create database  [if not exists]库名;
CREATE DATABASE IF NOT EXISTS books ;

2.修改：

RENAME DATABASE books TO 新库名; #更改库名
ALTER DATABASE books CHARACTER SET gbk; #更改库的字符集

3.删除：

DROP DATABASE IF EXISTS books;

2.表管理

1.创建：
create table 表名(
列名 列的类型【(长度) 约束】,
…
列名 列的类型【(长度) 约束】
)
#案例：创建表author

CREATE TABLE IF NOT EXISTS author(
 id INT,
 au_name VARCHAR(20),
 nation VARCHAR(10)
 )
DESC author; #查询表结构（列+类型+约束）

2.1数据类型

1.数值型
整型：默认是有符号，无符号徐添加unsigned关键字；插入的数值超范围，则返回边界值；

小数：
M为整数部分+小数部分的总位数 D为小数部分的位数
浮点型：float(M,D) double(M,D) 默认是根据插入数值的精度来决定精度
定点型：decimal(M,D) 其中M默认为10，D默认为0 PS该类型小数精度较高

字符型：
char(M) 其中M为最大字符数，默认为1。一旦指定空间就固定为M
varchar(M) 其中M为最大字符数，不可以省略。根据插入字符长度动态调整

日期型：
data保存日期；time保存时间；year保存年；
datatime以及timestamp是保存日期+时间，那有什么不同呢？
timestamp受时区影像：SHOW VARIABLES LIKE 'time_zone';SET time_zone='+9:00';

2.2约束

NOT NULL：非空，用于保证该字段的值不能为空，比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值，比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空，如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空，比如座位号
CHECK:检查约束【mysql中不支持】比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值，比如学生表的专业编号

约束名	保证唯一性	允许内容为空	一个表可以添加多个
PRIMARY KEY	Yes	No	至多一个
UNIQUE	Yes	Yes	可以有多个

外键：要求在从表设置外键关系；主表的关联列一般是主键或唯一；插入数据，先主后从，删除数据，先从后主。

列级约束：六大约束语法上都支持，但外键约束没有效果
表级约束：除了非空、默认，其他的都支持

SHOW INDEX FROM stuinfo; #查看约束

创建表时：

CREATE TABLE stuinfo( #列级约束的方式
 id INT PRIMARY KEY,#主键
 stuName VARCHAR(20) NOT NULL UNIQUE,#非空
 gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
 seat INT UNIQUE,#唯一
 age INT DEFAULT  18,#默认约束
 majorId INT REFERENCES major(id)#外键
 )

 CREATE TABLE stuinfo( #表级约束的方式
 id INT,
 stuname VARCHAR(20),
 gender CHAR(1),
 seat INT,
 age INT,
 majorid INT,
 CONSTRAINT pk PRIMARY KEY(id),#主键
 CONSTRAINT uq UNIQUE(seat),#唯一键
 CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
 CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
 );

修改表时：
alter table 表名 modify column 字段名 字段类型 新约束; #添加列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】; #添加表级约束

ALTER TABLE stuinfo ADD UNIQUE(seat);

删除约束：

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

2.3标识列

即自增长列，可以不用手动的插入值，系统提供默认的序列值
约束 AUTO_INCREMENT
特点：
标识列的类型只能是数值型，不一定非得和主键搭配，但通常是；一个表中至多一个标识列；设置步长：SET auto_increment_increment=3;起始值可以在插入数据时指定，默认为1

CREATE TABLE tab_identity(
 id INT  ,
 NAME FLOAT UNIQUE AUTO_INCREMENT,
 seat INT 
 )

2.修改语法：alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

①修改列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

②修改列的类型或约束

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

③添加新列

ALTER TABLE author ADD COLUMN annual DOUBLE; 

④删除列

ALTER TABLE book_author DROP COLUMN  annual;

⑤修改表名

ALTER TABLE author RENAME TO book_author;

3.删除

DROP TABLE IF EXISTS book_author;
SHOW TABLES; #查该库中表单名

4.复制
仅复制表结构：

CREATE TABLE copy LIKE author;

仅复制某些结构字段：

CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;

复制表的结构+数据：

CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='中国';

3.视图

含义：虚拟表，和普通表一样使用

	创建关键字	是否实际占用物理空间	使用
视图	create view	只是保存了sql逻辑	查，一般不能增删改
表	create table	保存了数据	增删改查

PS：包含以下关键字的sql语句构建的视图不可以增删改更新：分组函数、distinct、group by、having、union或者union all

1.创建视图：
create view 视图名
as
查询语句;

CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
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 myv1 WHERE last_name LIKE '%a%';

2.视图的修改
方式一：create or replace view 视图名 as 查询语句;
方式二：alter view 视图名 as 查询语句;
3.删除视图：drop view 视图名,视图名,…;
4.查看：DESC myv3;或者SHOW CREATE VIEW myv3;

事务

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

显示事务：
步骤1：开启事务
set autocommit=0; #禁用自动提交功能
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
…

步骤3：结束事务
commit;提交事务 或 rollback;回滚事务 PS:savepoint 节点名;设置保存点 可以回滚至指定节点

SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点 要不回滚该事务操作前的状态

隔离级别：

	允许类型	脏读	不可重复读	幻读	数据库默认
read uncommitted	允许事务读取未被其它事务提交的变更	√	√	√
read committed	只允许事务读取已经被其它事务提交的变更	×	√	√	Oracle
repeatable read	确保事务可以多次从一个字段中读取相同的值	×	×	√	MySQL
serializable	确保事务可以从一个表中读取相同的行	×	×	×

PS：对于A,B两事务重叠，访问数据库中相同的数据。A在更新数据data，B要获取数据data。
脏读：A还没提交，B读取了A未提交的字段变更。若A回滚，则B读取了临时无效的内容
不可重复读：B只读取已提交的字段，若A提交该字段变更，B再次读取同一个字段，值不一样
幻读：B从表中读取了一个字段，行插入变更，B再读同一个表，发现多了几行

查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;

存储过程+函数

1.变量

1.1系统变量

系统变量	关键字	作用域
全局变量	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.2自定义变量

	作用域	定义位置	语法	声明并初始化
用户变量	当前会话	会话的任何地方	加@符号，不用指定类型	SET @变量名=值
局部变量	定义它的BEGIN END中	BEGIN END的第一句话	一般不用加@,需要指定类型	DECLARE 变量名 类型 【DEFAULT 值】;

①声明并初始化
②赋值（更新变量的值）：SET 自定义变量=值;或SELECT 字段 INTO 自定义变量 FROM 表;
③查看：SELECT 自定义变量;

2.存储过程

一组预先编译好的SQL语句的集合，理解成批处理语句

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
 		存储过程体（一组合法的SQL语句）
END

参数列表中参数包含三部分：参数模式 参数名 参数类型
参数模式：in（输入）、out（输出）、inout（输入输出）

PS：存储过程体仅仅只有一句话，begin end可以省略；存储过程体中的每条sql语句的结尾要求必须加分号；存储过程的结尾可以使用 delimiter 重新设置

#根据输入的女神名，返回对应的男神名和魅力值 注cmd中好使
DELIMITER $
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
	 SELECT boys.boyname ,boys.usercp INTO boyname,usercp
	 FROM boys 
	 RIGHT JOIN
	 beauty b ON b.boyfriend_id = boys.id
	 WHERE b.name=beautyName ;
		
END $

#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$
#查看存储过程的信息
SHOW CREATE PROCEDURE  myp7;
#删除存储过程
DROP PROCEDURE myp7;

3.函数

一组预先编译好的SQL语句的集合。
PS函数有且仅有1 个返回，适合做处理数据后返回一个结果。而过程存储可以有0个或多个返回，适合做批量插入、批量更新

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
 	函数体
END

函数的参数列表中的参数只有(参数名 参数类型)这两部分；函数体肯定会有return语句

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	 DECLARE sal DOUBLE ;
	 SELECT AVG(salary) INTO sal
	 FROM employees e
	 JOIN departments d ON e.department_id = d.department_id
	 WHERE d.department_name=deptName;
	 RETURN sal;
END $

#调用
SELECT myf3('IT')$
#查看函数
SHOW CREATE FUNCTION myf3;
#删除函数
DROP FUNCTION myf3;

流程控制

1.分支结构

1.if函数： if else 的效果
SELECT IF(10<5,‘大’,‘小’); #if(条件,值1，值2)

2.1 case函数的使用一： switch case 的效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end

/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salarEND AS 新工资
FROM employees;

2.2 case 函数的使用二：类似于 多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
end

/*案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/
SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

3.if结构：功能类似于多重if PS只能应用在begin end 中
if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
else 语句n;
end if;

#创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
	 IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
	 ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
	 ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
	 END IF;
END $

CALL test_if_pro(2100)$

2.循环结构

iterate类似于 continue，继续，结束本次循环，继续下一次
leave 类似于 break，跳出，结束当前所在的循环
1.while循环：

【标签:】while 循环条件 do
循环体;
end while【标签】;

#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
 DECLARE i INT DEFAULT 0;
 a:WHILE i<=insertCount DO
  SET i=i+1;
  IF MOD(i,2)!=0 THEN ITERATE a;
  END IF;
  INSERT INTO admin(username,`password`)
  VALUES(CONCAT('xiaohua',i),'0000'); 
 END WHILE a;
END $

CALL test_while1(100)$