MySQL教程基础篇（六）：数据类型|约束|事务|视图|存储过程

数据类型

一、整型

分类：tinyint、smallint、mediumint、int、integer、bigint

特点：

①默认为有符号，如果要设置成无符号，需要添加 unsigned

②如果插入的数据超出了整型的范围，会报错：out of range，并且会插入一个临界值

③如果不设置长度，会有默认的长度

【例】：

CREATE TABLE book (

    t1 INT,

    t2 INT UNSIGNED

)

二、小数

浮点型

float（M,D）

double（M,D）

定点型：

dec（M,D）

decimal（M,D）

特点：

①

M：整数部位+小数部位

D：小数部位

如果超过范围，则插入临界值

②

M和D都可以省略

如果是Decimal，则M

原则：

类型越简单越好，能保存数值的类型越少越好

三、字符型

较短的文本：

char（M）：M∈[0,255]

varchar（M）：M∈[0,65535]

特点：

                            写法                       M的意思                                            长度                 空间占用      效率

char                  char（M）          最大字符数，可以省略，默认为1    固定长度的字符       比较消耗      较高

varchar            varchar（M）      最大字符数，不可以省略                 可变长度的字符       比较节省      较低

较长的文本：

text

blob（较大的二进制）

二进制字符

binary和varbinary，类似char和varchar，不同的是binary和varbinary包含二进制字符串，不包含非二进制字符串

枚举

Enum：插入的值必须是列表的值之一

【例】：

create table tab_char(

    c1 enum('A','B','C')

)

Set类型

和Enum类似，可保存0~64个成员，不同的是，Set可以一次选举多个成员，Enum一次只能选一个

四、日期型

                          字节                      最小值                                最大值

date                      4                      1000-01-01                        9999-12-31       

datetime               8                1000-01-01 00:00:00        9999-12-31 23:59:59

time                      4                  19700101080001             2038年的某一时刻

timestamp            4                      -838:59:59                          838:59:59

year                     1                           1901                                  2155

分类：

date只保存日期

time只保存时间

year只保存年

datetime和timestamp都保存日期+时间

特点：

timestamp和实际的时区有关，更能反映实际的日期，datetime只能反映插入时的当地时区

timestamp还收MySQL版本和SQLMode的影响

【例】：

create table tab_date (

    t1 DATETIME，

    t2 TIMESTAMP

)；

                      字节            范围            是否受时区的影响

datetime          8          1000-9999                否

timestamp       4          1970-2038                是

 

约束

【含义】：为保证表中数据的准确性和可靠性而实行的一种限制

【分类】：

NOT NULL：非空，用于保证该字段的值不为空，如ID、姓名、学号等

DEFAULT：默认值，用于保证该字段有默认的值

PRIMARY KEY：主键，用于保证该字段的唯一性，不能为空

UNIQUE：唯一约束，用于保证该字段的值具有唯一性，可为空

CHECK：检查约束，（MySQL不支持）

FOREIGN KEY：外键，限制两个表的关系，保证该字段的值必须来自主表的关联列的值

 

【添加约束的时机】：

1、创建表时

2、修改表时

 

【约束的添加分类】：

1、列级约束：语法上六大约束都支持，但外键约束没有效果（默认、非空、主键、唯一）

2、表级约束：除了NOT NULL、DEFAULT，其他的都支持

 

【使用模板】

CREATE TABLE 表名 (

    列名 类型 列级约束,

    列名 类型，

    表级约束

)

 

1、添加列级约束

【例】：

CREATE TABLE stuinfo (

    id INT PRIMARY KEY，#主键

    stuname VARCHAR(20) NOT NULL，#非空

    gender CHAR(1) CHECK(gender='男' or gender='女')，#检查

    seat INT UNIQUE，#唯一

    age INT DEFAULT 18，#默认值

    majorId INT FOREIGN KEY REFERENCES major(id)  #外键

)；

CREATE TABLE major (

    id INT,

    majorName VARCHAR(20)

)；

 

2、添加表级约束

【语法模板】：[CONSTRAINT 约束名] 约束类型(字段名)

【例】：

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）#外键

)；

SHOW INDEX FROM stuinfo；

 

【列级约束和表级约束的通用写法】：

CREATE TABLE stuinfo (

    id INT PRIMARY KEY，#主键

    stuname VARCHAR(20) NOT NULL，#非空

    gender CHAR(1) ，

    seat INT UNIQUE，#唯一

    age INT DEFAULT 18，#默认值

    CONSTRAINT fk_stuinfo_major FOREIGN KEY （majorId）REFERENCES major（id）#外键

)；

 

【主键与UNIQUE的对比】

                                  唯一性          是否允许为空        一个表中可以有多少个         是否允许组合（联合主键|组合唯一键）

PRIMARY KEY             √                      ×                             最多一个                                √

UNIQUE                       √                      √                              可以多个                                √

 

【外键的特点】

1、要求在从表中设置外键关系

2、从表的外键列的类型要和主表的关联列的类型一致或者兼容，名称不要求一致

3、要求主表中关联列必须是一个 key （主键或者唯一键）

4、插入数据时，必须先插入主表，再插入从表；删除数据时，必须先删除从表，再删除主表

 

3、修改表时添加约束

/*

列级约束： ALTER TABLE 表名 MODIFY COLUMN 列名 类型 新约束；

表级约束：ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型（列名）[外键的引用]；

*/

 

CREATE TABLE stuinfo (

    id INT ，

    stuname VARCHAR(20) ，

    gender CHAR(1) ，

    seat INT ，

    age INT ，

    majorId INT

)；

1、添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname INT NOT NULL；

2、添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18；

3、添加主键约束

列级约束：ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY；

表级约束：ALTER TABLE stuinfo ADD PRIMARY KEY (id)；

4、添加唯一约束

列级约束：ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE；

表级约束：ALTER TABLE stuinfo ADD UNIQUE (seat)；

5、添加外键约束

ALTER TABLE stuinfo ADD [CONSTRAINT fk_stuinfo_major] FOREIGN KEY（majorId）REFRENCES major（id）； 

 

4、修改表时删除约束

1、删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname INT；

2、删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT；

3、删除主键约束

ALTER TABLE stuinfo DROP PRIMARY KEY；

4、删除唯一约束

ALTER TABLE stuinfo DROP INDEX (seat)；

SHOW INDEX FROM stuinfo；

5、删除外键约束

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major；

 

【列级约束和表级约束的对比 】

                                 位置                                 支持的约束类型                                   是否可以起约束名

列级约束：           列的后面               语法上都支持，但对外键没有效果                             不可以

表级约束：        所有列的后面           默认值和非空不支持，其他的都支持               可以（主键没有效果）

 

标识列（自增长列）

【含义】：可以不用手动插入值，系统提供默认的序列值

【特点】：

1、标识列必须和key（主键或者 unique）搭配使用

2、一个表最多只能有一个标识列

3、标识列的类型必须是数值型（INT、FLOAT、DOUBLE都行）

4、标识列可以修改步长

一、创建表时设置标识列

CREATE TABLE tab_identity (

    id INT PRIMARY KEY AUTO_INCREMENT，

    NAME VARCHAR（20）

)；

INSERT INTO tab_identity VALUES（null，'John'）；

SELECT * FROM tab_identity；

SHOW VARIABLES LIKE '%auto_increment%'；

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |   步长。即每次增长多少
| auto_increment_offset    | 1     |   偏移量，即起始值，在MySQL中这个值不可修改
+--------------------------+-------+
2 rows in set (0.00 sec)

SET AUTO_INCREMENT_INCREMENT = 3； #修改步长值

二、修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT；

三、修改表时删除标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY；

 

TCL（事务控制语言）

【含义】：Transaction Control Language，事务控制语言

【事务】：一个或一组SQL语句组成的一个执行单元，这个执行单元要么全部执行（顺利执行完成），要么全部不执行（回滚到事务开始之前的状态）

【事务的特点】：ACID

1、原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

2、一致性（Consistency）：事务必须使数据库从一个一致性状态切换到另一个一致性状态

3、隔离性（Isolation）：一个事务的执行不受其他事务的干扰，并发的多个事务之间不能相互干扰

4、持久性（Durability）：事务一旦被提交，其对数据库的改变是永久性的，之后发生的其他操作或数据库故障等不会对其产生影响。

一、事务的创建

1、隐式事务：事务没有明显的开启和结束的标记

比如 insert、update、delete语句

2、显式事务：事务具有明显的开启和结束的标记

【前提】：必须禁用事务的自动提交功能

SET autocommit = 0；

SHOW VARIABLES LIKE 'autocommit'；

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |   ON=1，OFF=0
+---------------+-------+
1 row in set (0.00 sec)

步骤1：开启事务

SET autocommit = 0；

start transaction；可选

步骤2：编写事务中的SQL语句（增删改查）

语句1；

语句2；

...

步骤3：结束事务

步骤4：提交事务

commit；

rollback； #事务回滚

 

数据库的隔离设置

 

查询事务设置的级别

select @@tx_isolation;

修改事务的级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  #设置为最低级别

 设置事务的保存点

savepoint 节点名称

演示savepoint的使用

SET autocommit = 0；

start transaction；可选

DELETE FROM account WHERE id=10;

savepoint a； #设置保存点

DELETE FROM account WHERE id=25;

rollback；#回滚到保存点

 

【DELETE与TRUNCATE在事务中的区别】：DELETE可以在事务回滚后还原数据，TRUNCATE则不行

 

视图

【含义】：虚拟表，和普通表一样使用，通过表动态生成的数据

比如：舞蹈班和普通班的区别

视图的优点：

1、常用的SQL查询的重复使用

2、简化复杂的SQL操作，且不用关注查询细节

3、保护数据，提高安全性

【语法】：CREATE VIEW 视图名 AS 查询语句

【例】：将姓张的学生的姓名和专业名作为一个视图创建出来

CREATE VIEW v1 AS SELECT stuname, majorname FROM stuinfo s INNER JOIN major m ON s.'major_id' = m.'id';

【视图的修改】

/*

方式一：

CREATE OR REPLACE VIEW 视图名

AS

查询语句

*/

/*

方式二：

ALTER VIEW 视图名

AS

查询语句

*/

【视图的删除】

【语法】：DROP VIEW 视图名，视图名，视图名......

可一条语句删除多个视图

【视图的查看】

【语法1】：DESC 视图名；

【语法2】：SHOW CREATE VIEW 视图名；--可查看视图创建的语句

【视图的更新】

语法同数据表的增删改：INSERT、UPDATE、DELETE，视图的更新会改变原数据表的数据内容

【例】：

①包含以下SQL关键字的语句：分组函数、distinct、group by、having、union

CREATE OR REPLACE VIEW myv1

AS

SELECT MAX(salary) m FROM employees GROUP BY department_id；

②常量视图

CREATE OR REPLACE VIEW myv2

AS

SELECT 'Terry' NAME；

③SELECT中包含子查询

CREATE OR REPLACE VIEW myv3

AS

SELECT (SELECT MAX(salary)  FROM employees) '最高工资'；

④JOIN查询，或者是没有用JOIN的多表查询

CREATE OR REPLACE VIEW myv4

AS

SELECT * FROM employees e JOIN deparrment d ON e.department_id=d.department_id；

⑤FROM一个不能更新的视图

CREATE OR REPLACE VIEW myv5

AS

SELECT * FROM myv3；

⑥WHERE字句的子查询引用了FROM字句中的表

CREATE OR REPLACE VIEW myv6

AS

SELECT * FROM myemployees WHERE employee_id IN（SELECT manager_id FROM employees WHERE manager_id IS NOT NULL）；

 

视图和表的对比

	语法关键字	物理空间的占用情况	使用特性
视图	view	只保存了SQL逻辑	可增删改查，一般只使用查询，不做增删改
表	table	保存了数据	可增删改查

 

 

未完待续

https://www.bilibili.com/video/BV12b411K7Zu?p=148&spm_id_from=pageDriver

 

MySQL的规范化注意事项：

https://mp.weixin.qq.com/s/rf0RqHQDHQqE0K07ApCltw