3、函数和约束

# 提供的数据sql
CREATE TABLE IF NOT EXISTS student(no BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '学号',name VARCHAR(20) NOT NULL COMMENT '姓名',sex VARCHAR(2) DEFAULT '男' COMMENT '性别', age INT(3) DEFAULT 0 COMMENT '年龄',score DOUBLE(5,2) COMMENT '成绩'
)ENGINE = InnoDB CHARSET = UTF8 COMMENT = '学生表';
#插入数据
INSERT INTO student(no,name,sex,age,score) VALUES(DEFAULT ,'张三','男',20,59);
INSERT INTO student(no,name,sex,age,score) VALUES(DEFAULT ,'李四','女',19,62);
INSERT INTO student(no,name,sex,age,score) VALUES(DEFAULT ,'王五','其他',28,81);
INSERT INTO student(no,name,sex,age,score) VALUES(DEFAULT ,'龙华','男',22,75);
INSERT INTO student(no,name,sex,age,score) VALUES(DEFAULT ,'金凤','女',18,80);
INSERT INTO student(no,name,sex,age,score) VALUES(DEFAULT ,'张华','其他',27,88);
INSERT INTO student(no,name,sex,age,score) VALUES(DEFAULT ,'李刚','男',30,88);
INSERT INTO student(no,name,sex,age,score) VALUES(DEFAULT ,'潘玉明','女',28,81);
INSERT INTO student(no,name,sex,age,score) VALUES(DEFAULT ,'凤飞飞','其他',32,90);

一、计算函数

函数	说明	示例
ABS(x)	返回X的绝对值
FLOOR(X)	返回<=X的最大整数
CEIL(X)	返回>=X的最小整数
TRUNCATE(X,D)	返回数值X，保留到小数点后D位的值不进行四舍五入
ROUND(X)	返回离X 最近的整数，四舍五入
ROUND(X,D)	保留X小数点后D位的值，四舍五入
RAND()	返回0-1的随机数 。
MOD(N，M)	返回N/M 以后的余数

• 示例如下

--查询绝对值大于60的分数
SELECT * FROM student WHERE ABS(score)>60;
--查询小于或等于90.1向下取整的分数
SELECT * FROM student WHERE score<=FLOOR(90.1);
--查询大于或等于90.1向上取整的分数
SELECT * FROM student WHERE score>=CEIL(90.1);
--查询大于或等于90.0截断为一位小数的分数
SELECT * FROM student WHERE score>=TRUNCATE(90.0,1);
--查询大于或等于四舍五入到一位小数的91.1的分数
SELECT * FROM student WHERE score>=ROUND(91.1);
--查询大于或等于四舍五入到一位小数的90.0的分数
SELECT * FROM student WHERE score>=ROUND(90.0,1);
--查询分数大于一个0到100之间的随机数的记录
SELECT * FROM student WHERE SCORE>RAND()*100; SELECT RAND();
--查询分数除以10的余数大于6的记录
SELECT * FROM student WHERE MOD(score,10)>6;

二、字符串函数

函数	说明	示例
CHAR_LENGTH(str)	字符串的字符个数
LENGTH(str)	字符串长度
LOWER(str)	小写
UPPER(str)	大写
TRIM(str)	去2边空格
LEFT(str,n) 和 RIGHT(str,n)	分别从字符串的左边和右边提取指定数量的字符。
LTRIM(str)和RTRIM(str)	分别去除字符串左边和右边的空格。
REPLACE(s,s1,s2)	s2替换s中的s1部分。
SUBSTR(str)	从字符串中提取子字符串。
CONCAT(str1,str2…)	用于连接两个或多个字符串。

三、日期和时间函数

函数	说明
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(d)	返回给定日期d的年
MONTH(d)	返回给定日期D是当月的第几天
HOUR(d)	返回给定日期D的小时数
MINUTE(d)	返回给定日期D的分钟数
SECOND(d)	返回给定日期D的秒数
ADDDATE(d，n)	返回给起始日期d加上n天的日期
TIMESTAMPDIFF(type,d1,d2)	返回给定日期d1和d2的时间差
DATE_FORMAT(d,fomat)	返回给定日期格式的字符串

四、条件判断函数

1、IF(条件, 表达式1, 表达式2)

如果条件满足，则使用表达式1,否则使用表达式2

SELECT (IF( score>0 ,score,name)) from student;+----------------------------+
| (IF( score>0 ,score,name)) |
+----------------------------+
| 59.00                      |
| 62.00                      |
| 81.00                      |
| 75.00                      |
| 80.00                      |
| 88.00                      |
| 88.00                      |
| 81.00                      |
| 90.00                      |
+----------------------------+

2、IFNULL(字段, 表达式)

如果字段值为空，则使用表达式，否则，使用字段值

SELECT (IFNULL(name,age)) FROM student;
--name 不为空，执行的name
+--------------------+
| (IFNULL(name,age)) |
+--------------------+
| 张三               |
| 李四               |
| 王五               |
| 龙华               |
| 金凤               |
| 张华               |
| 李刚               |
| 潘玉明             |
| 凤飞飞             |
+--------------------+

3、CASE WHEN 条件1 THEN 表达式1 [WHEN 条件2 THEN 表达式2 …] ELSE 表达式n END

CASE WHEN 条件1 THEN 表达式1 [WHEN 条件2 THEN 表达式2 ...] ELSE 表达式n END 

如果条件1满足，则使用表达式1；【如果条件2满足，则使用表达式2， … 】否则，使用表达式n。相当 于Java/cpp中的多重if…else语句。

select (CASE WHEN score>0 THEN score WHEN name is null THEN name ELSE no END) from student ;+------------------------------------------------------------------------+
| (CASE WHEN score>0 THEN score WHEN name is null THEN name ELSE no END) |
+------------------------------------------------------------------------+
| 59.00                                                                  |
| 62.00                                                                  |
| 81.00                                                                  |
| 75.00                                                                  |
| 80.00                                                                  |
| 88.00                                                                  |
| 88.00                                                                  |
| 81.00                                                                  |
| 90.00                                                                  |
+------------------------------------------------------------------------+

4、CASE … WHEN

CASE 表达式 WHEN 值1 THEN 表达式1 [WHEN 值2 THEN 表达式2 ...] ELSE 表达式n END

如果表达式的执行结果为值1，则使用表达式1；【执行结果为值2，则使用表达式2， … 】否则，使用 表达式n。相当于Java/cpp中的switch语句。

 select (CASE name WHEN "张三" THEN name ELSE no END) from student ;
+-------------------------------------------------+
| (CASE name WHEN "张三" THEN name ELSE no END)   |
+-------------------------------------------------+
| 张三                                            |
| 2                                               |
| 3                                               |
| 4                                               |
| 5                                               |
| 6                                               |
| 7                                               |
| 8                                               |
| 9                                               |
+-------------------------------------------------+

五、约束

1、了解约束

• 约束是用于确保数据表中数据的完整性和一致性的规则。它们定义了表中数据的限制条件，避免不符合业务逻辑的错误数据被插入或修改。以下是 MySQL 中常见的约束类型及详细说明：

约束	描述	关键字
非空约束	限制该字段的数据不能为空	NOT NULL
唯一约束	保证该字段的所有数据都是唯一不重复的	UNIQUE
主建约束	组件是一行数据的唯一标识要求非空且唯一	PRIMARY KEY
默认约束	保存数据时如果未指定该字段的值则采用默认值	DEFAULT
检查约束（8.0.16后才有）	保证字段值满足某一个条件	CHECK
外键约束	用于两张表的数据之间建立连接保证数据的一致性和完整性。引用另一个表的主键，用于维护表之间的关系。	FOREIGN KEY

2、约束的创建（部分举例）

• 创建表 user

	CREATE TABLE user(id int PRIMARY KEY AUTO_INCREMENT COMMENT  "主键",name VARCHAR(10) NOT NULL UNIQUE COMMENT  "姓名",age int CHECK(age>0&& age<=120) COMMENT  "年龄",status CHAR(1) DEFAULT "1" COMMENT  "状态",gender CHAR(1) COMMENT "性别")engine = INNODB CHARSET= UTF8 COMMENT= "约束练习";

• 插入数据

insert into user(name,age,status,gender) values("Tom1",19,"1","男"),("Tom2",19,"0","男");insert into user(name,age,status,gender) values("Tom3",19,"1","男");

• 让后开始判断约束的性质了

insert into user(name,age,status,gender) values(NULL,19,"1","男");
--我把主键改NULL，会报错
--1048 - Column 'name' cannot be null--我再次插入Tom3，会报错，主键是唯一且不重复的
insert into user(name,age,status,gender) values("Tom3",19,"1","男");

3、再次介绍（详细）

3.1、插入和删除主键

• 注意：AUTO_INCREMENT下无法删除主键

-- 添加主键约束：保证数据的唯一性
ALTER TABLE 表名 ADD PRIMARY KEY(字段名1,字段名2, ..., 字段名n);
-- 删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;

主键的添加和删除

3.2、插入和删除外键

CREATE TABLE 表名字([CONSTRAINT] 外键名字 FOREIGN KEY(外键字段名字) REFERENCES 主表(主表字段名)
);
ALTER TABLE 表名字 ADD CONSTRAINT 外键名字 FOREIGN KEY(外键字段名) REFERENCES 主表(主表字段名);
ALTER TABLE 表名字 DROP FOREIGN KEY 外键名字;

外键的添加和删除

注意：ADD添加时，与主键不同 。

3.3、插入和删除唯一约束

-- 添加唯一约束
ALTER TABLE 表名字 ADD CONSTRAINT 唯一约束的名字 UNIQUE (字段);
-- 删除唯一约束
ALTER TABLE 表名字 DROP INDEX 字段名字;

注意：删除有INDEX

3.4、插入和删除检查约束

-- 添加检查约束
ALTER TABLE 表名字 ADD CONSTRAINT  检查约束名字 CHECK (条件);
-- 删除检查约束(MySQL 8.0+)
ALTER TABLE 表名字 DROP CONSTRAINT 检查约束名字;

3.5、插入和删除默认约束

ALTER TABLE stu ALTER COLUMN 字段 SET DEFAULT 默认值;
ALTER TABLE stu ALTER COLUMN 字段 DROP DEFAULT;

3.6、插入和删除自增约束

-- 为字段添加自增约束
ALTER TABLE 表名 MODIFY 字段名 列类型 AUTO_INCREMENT;
-- 为字段删除自增约束
ALTER TABLE 表名 MODIFY 字段名 列类型;

3.7、插入和删除非空约束

-- 为字段添加非空约束
ALTER TABLE 表名 MODIFY 字段名 列类型 NOT NULL;
-- 删除字段非空约束
ALTER TABLE 表名 MODIFY 字段名 列类型 NULL;