[大数据]MySQL之DDL库表管理和TCL事务

1.DDL

DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。

1.1库管理

（1）新建数据库

#语法：create database [if not exists] 库名 [character set 字符集]
#示例
CREATE DATABASE
IF NOT EXISTS demo
CHARACTER SET = 'gbk';

（2）修改数据库字符集

#语法：alter database 库名 character set 字符集
#示例
ALTER DATABASE demo CHARACTER SET 'utf8';

（3）库的删除

#语法：drop database [if exists] 库名
#示例：
DROP DATABASE IF EXISTS demo;

1.2表管理

（1）表创建

#语法：
create table [if not exists] 表名(
    列名 列的类型 [(长度) 约束],
    列名 列的类型 [(长度) 约束]
    列名 列的类型 [(长度) 约束]
    ...
    列名 列的类型 [(长度) 约束]
);

USE demo;
#示例：
CREATE TABLE student(
	id int(11),
	name varchar(10),
	gender VARCHAR(1)
);

（2）表修改

#语法：
修改表字段：alter table 表名 add|drop|modify|change column 列名[ 列类型 约束];

add：添加列
drop：删除列
modify：修改列（比如字段类型、约束）
change：修改列名

修改表名：alter table 表名 rename to 新表名

--示例：
#删除列
ALTER TABLE student DROP COLUMN QQ; 
#添加列
ALTER TABLE student ADD COLUMN  QQ VARCHAR(11);
ALTER TABLE student ADD COLUMN email VARCHAR(20);
#修改列
ALTER TABLE student MODIFY COLUMN QQ CHAR(11);
#修改列名
ALTER TABLE student CHANGE COLUMN email mail VARCHAR(21);

#修改表名
ALTER TABLE student RENAME TO stu;

（3）表删除

#语法：drop table [if exists] 表名;

#示例：
DROP TABLE IF EXISTS stu;

（4）表复制

#（1）仅仅复制表结构
create table 新表名 like 复制源表名

#（2）复制表结构+数据
create table 新表名 select 查询列表 from 复制源表名 [where 筛选条件] 

#示例：
#先插入数据
INSERT INTO stu VALUES(1, '古天乐', '男'),
(2, '萱萱', '女'),
(3, '张家辉', '男');
#复制表结构
CREATE TABLE stu2 LIKE stu;
#复制结构和数据
CREATE TABLE student
SELECT * from stu;

（5）约束

（1）约束是一种表限制，用于限制表中数据，为了保证表中数据的准确性和可靠性

（2）分类：六大约束（但是mysql不支持检查约束）

• NOT NULL：非空约束，用于保证该字段的值不能为空
• UNIQUE：唯一约束，用于保证该字段的值具有唯一性，可以为空
• DEFAULT：默认约束，用于保证该字段有默认值
• PRIMARY KEY：主键约束，用于保证该字段的值具有唯一性，并且不为空（相当于非空+唯一）
• CHECK：检查约束，用于保证该字段的值在一定范围之内（mysql不支持哦）
• FOREIGN KEY：外键约束，用于限制两张表的关系，用于保证该字段的值必须来自于主表的关联列的值

（3）添加约束的时机：

• 创建表时
• 修改表时

（4）添加的约束分类

• 列级分类
• 表级分类

• 在创建表时添加

#语法：
创建表时的表级约束：[constraint 约束名] 约束类型(字段名)
创建表时的列级约束：字段名 字段类型 约束类型

#示例：
--表级约束
#1.新建课程表
CREATE TABLE IF NOT EXISTS course(
	cid INT,
	cname VARCHAR(20),
	CONSTRAINT c_pk PRIMARY KEY(cid)
);
#2.新建学生信息表
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT,
	seat INT,
	name VARCHAR(20),
	gender CHAR(1),
	age INT,
	courseId INT,
	CONSTRAINT pk PRIMARY KEY(id), #主键
	CONSTRAINT uq UNIQUE(seat), #唯一约束
	CONSTRAINT ck CHECK(gender='男' OR gender='女'), #检查约束
	CONSTRAINT fk_course FOREIGN KEY(courseId) REFERENCES course(cid) #外键约束
);

--列级约束
#1.新建课程表
CREATE TABLE IF NOT EXISTS course(
	cid INT PRIMARY KEY,
	cname VARCHAR(20)
);
#2.新建学生信息表
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	seat INT UNIQUE,
	name VARCHAR(20),
	gender CHAR(1) CHECK(gender='男' OR gender='女'),
	age INT DEFAULT 18,
	courseId INT REFERENCES course(cid)
);

• 在修改表时添加

#语法：
修改表时的添加表级约束：alter table 表名 add [constraint 约束名] 约束类型(字段名)
修改表时添加列级约束：alter table 表名 modify column 字段名 字段类型 新约束

• 删除约束

ALTER TABLE tab_name DROP PRIMARY KEY；/*删除主键*/

ALTER TABLE tab_name DROP [INDEX | KEY] index_name /*删除唯一性约束*/

ALTER TABLE tab_name DROP FOREIGN KEY fk_name/*删除外键约束*/

2.TCL

TCL语言又叫事务控制语言，事务就是指在MySQL中完成一件具体的事情，由一条或多条sql语句组成的执行单元，这个执行单元要么全部执行，要么全部不执行。

（1）事务的特点（ACID）

1. 原子性（Atomicity）：一个事务不可再分割，要么全部执行，要么全部都不执行
2. 一致性（Consistency）：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
3. 隔离性（lsolation）：一个事务执行不受其他事务的影响
4. 持久性（Durability）：一个事务一旦提交，将会永久改变数据库的数据

（2）多个事务访问相同数据，没有采取隔离机制，会容易出现不可脏读、幻读、不可重复读等问题

1. 脏读：对于两个事务T1、T2，若T1读取的数据是T2还没有提交的字段，T2进行回滚，那么T1读取的数据是无效的。
2. 幻读：对于T1、T2两个事务，若T1读取了数据，T2插入了几行新数据，T1再读取的时候就会多了几行数据。
3. 不可重复读：对于T1、T2两个事务，T1读取了数据后T2又更新了字段的数据，那么T1再读取得到的值则是T2事务更新后的值。

（3）事务的分类：

1. 隐式事务：事务没有明显的开始和结束标志，比如：delete、update、insert语句
2. 显示事务：有事务开始和结束标志，使用set autocommit = 0，把事务自动提交设置为禁用

#语法：
1.禁用自动提交
set autocommit = 0;
2.开启事务（可省略）
start transition
3.编写sql语句
语句1
...
语句n
4.结束事务
commit：提交事务
或者
rollback：回滚事务


事务可以设置回滚点：
savepoint 节点名字（保存节点）
rollback to 节点名字

#示例：
SET AUTOCOMMIT = 0;
START TRANSACTION;
DELETE FROM boys WHERE boyName='张无忌';
SAVEPOINT a;
INSERT INTO boys VALUES(10, '杨过', 1000);
ROLLBACK TO a;