MYSQL：第四篇 常用函数和约束

MySQL中有很多内置函数，之前介绍的有count、avg、sum、min、max

接下来再介绍几种常用函数

数值函数

函数	函数说明
ABS（x）	返回X的绝对值
SIGN（X）	返回x的符号，正数返回1，负数返回-1,0返回0
SQRT(X)	返回X的平方根，当X的值为负数时，返回NULL
LEAST(x1,x2,x3...)	返回列表中的最小值

 字符串函数——字符串拼接

函数	函数说明
CONCAT(S1,S2,Sn)	连接S1,S2...Sn为一个字符串
CONCAT_WS(X,S!,S2,Sn)	同CONCAT，每个字符串之间要加上X
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a

日期函数——处理时间相关

函数	函数说明
YEAR(date) / MONTH(date) / DAY(date)	返回具体日期值
HOUR(time) / MINUTE(time) / SECOND(time)	返回具体时间值
QUARTER(date)	返回日期对应的极度，范围1-4

流程函数——控制SQL执行顺序

函数	函数说明
IF(value,value1,value2)	如果value值为true，返回value1，否则返回value2
IFNULL(value1,value2)	如果value1不为NULL,返回value1,否则返回value2

约束

概念：约束就是用来作用表中字段的规则，用于存储在表中的数据

目的：保证数据库中数据的正确性，有效性和完整性

约束	关键字	约束说明
非空约束	NOT NULL	限制该字段的数据不能为NULL
唯一约束	UNIQUE	保证该字段所有数据唯一，不重复
主键约束	PRIMARY KEY	主键是一行数据的唯一标识，非空且唯一
默认约束	DEFAULT	保存数据时，若未指定该字段，则采用默认值
外键约束	FOREIGN KEY	用来让两张表之间建立连接，保证数据的一致性和完整性
检查约束	CHECK	保证字段值满足某一个条件

案例：定义一个学生表，表中要求如下：

        sn表示学号，使用int类型，主键并且自动递增

        name表示姓名，不为空

        age表示年龄，18-30之间

        gender表示性别

        study-status表示学习状态，0表示挂科，1表示通过，默认为1

   SQL：create table student(

                sn int AUTO_INCRENT PRIMARY KEY,

                name varchar(10) NOTNULL,

                age int CHECK(age >= 18,age <= 30),

                gender char(1),

                study_status TINYINT DEFAULT 1

        ) comment '学生表'；

外键约束

1、概念：用户建立两张表之间的联系，为了保证数据的一致性和完整性；

2、创建时，没有使用外键真正的来管理数据，可能会存在数据的丢失。

3、添加外键

1. 创建表时，创建外键

      create table 表名（

                字段名 数据类型 [CONSTRAINT] [外键名称] FOREIGN KEY （外键字段名） REFERENCES 主表 （主表列名）

        ）；

        2.完成创建表后，添加外键

        ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY   (外键字段) REFERENCES 主表 (主表列名)

4、删除外键

        ALTER TABLE 表名 DROP FOREIGN KEY 外键名称；

5、删除更新外键

添加外键后，再删除父表数据时会产生约束行为，就称为删除和更新行为。简单说，在添加外键后，在删除父表数据时，会对子表产生影响，进而需要对删除数据或者更新的行为进行约束。

删除和更新行为	说明
NO ACTION	在父表中删除/更新对应记录时，首先检查该记录是否有对应的外键，若有则不允许删除更新/操作（默认行为）
RESTRICT	与 NO ACTION 一致，默认行为
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，若有，则同时删除/更新在子表中的记录
SET NULL	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则设置子表中该外键值为null(该外键允许为null)
SET DEFAULT	父表在变更时，子表将外键列设置成一个默认值（innodb不支持）

语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(字段名) ON UPDATE CASCADE ON DELETE CASCADE;