MySQL --- 函数&约束

Bertha 。 2024-03-31 14:39 70阅读 0赞

**目录**

函数

定义

字符串函数

常见字符串函数

语法

数值函数

常见数值函数

语法

案例

日期函数

常见日期函数

语法

流程函数

常见流程函数

约束

概念

目的

分类

案例

外键约束

概念

语法

添加外键

删除外键

删除/更新行为

--------------------

## 函数 ##

### 定义 ###

一段可以直接被另一字段程序调用的程序或代码。

### 字符串函数 ###

#### 常见字符串函数 ####

<table style="width:612px;"> 
 <thead> 
  <tr> 
   <th style="text-align:center;"><span style="color:#0d0016;"><strong>函数</strong></span></th> 
   <th style="text-align:center;width:430px;"><span style="color:#0d0016;"><strong>功能</strong></span></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONCAT(S1,S2,…,Sn)</td> 
   <td style="width:430px;">字符串拼接，将S1,…,Sn拼接成一个字符串</td> 
  </tr> 
  <tr> 
   <td>LOWER(str)</td> 
   <td style="width:430px;">将字符串str全部转成小写</td> 
  </tr> 
  <tr> 
   <td>UPPER(str)</td> 
   <td style="width:430px;">将字符串str全部转成大写</td> 
  </tr> 
  <tr> 
   <td>LPAD(str,n,pad)</td> 
   <td style="width:430px;">左填充，用字符串pad对str的左边进行填充，达到n个字符串长度</td> 
  </tr> 
  <tr> 
   <td>RPAD(str,n,pad)</td> 
   <td style="width:430px;">右填充，用字符串pad对str的右边进行填充，达到n个字符串长度</td> 
  </tr> 
  <tr> 
   <td>TRIM(str)</td> 
   <td style="width:430px;">去掉字符串头部和尾部的空格</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING(str,start,len)</td> 
   <td style="width:430px;">返回字符串str从start位置起的len个字符串长度</td> 
  </tr> 
 </tbody> 
</table>

#### 语法 ####

> SELECT 函数（参数）;

### 数值函数 ###

#### 常见数值函数 ####

<table style="width:458px;"> 
 <thead> 
  <tr> 
   <th style="text-align:center;"><strong>函数</strong></th> 
   <th style="width:318px;text-align:center;"><strong>功能</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CEIL(X)</td> 
   <td style="width:318px;">向上取整</td> 
  </tr> 
  <tr> 
   <td>FLOOR(X)</td> 
   <td style="width:318px;">向下取整</td> 
  </tr> 
  <tr> 
   <td>MOD(x,y)</td> 
   <td style="width:318px;">返回x/y的模</td> 
  </tr> 
  <tr> 
   <td>RAND()</td> 
   <td style="width:318px;">返回0~1内的随机数</td> 
  </tr> 
  <tr> 
   <td>ROUND(x,y)</td> 
   <td style="width:318px;">求参数x的四舍五入的值，保留y位小数</td> 
  </tr> 
 </tbody> 
</table>

#### 语法 ####

> SELECT 函数（参数）;

### 案例 ###

利用数据库函数，生产一个六位数随机数验证码

select rpad(round(rand()*1000000,0),6,'0');
    
    #rpad  补零
    #round  保留小数
    #rand   生成随机数

### 日期函数 ###

#### 常见日期函数 ####

<table style="width:608px;"> 
 <thead> 
  <tr> 
   <th style="width:250px;">函数</th> 
   <th style="width:357px;">功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:250px;">CURDATE()</td> 
   <td style="width:357px;">返回当前日期</td> 
  </tr> 
  <tr> 
   <td style="width:250px;">CURTIME()</td> 
   <td style="width:357px;">返回当前时间</td> 
  </tr> 
  <tr> 
   <td style="width:250px;">NOW()</td> 
   <td style="width:357px;">返回当前日期和时间</td> 
  </tr> 
  <tr> 
   <td style="width:250px;">YEAR(date)</td> 
   <td style="width:357px;">获取指定date的年份</td> 
  </tr> 
  <tr> 
   <td style="width:250px;">MONTH(date)</td> 
   <td style="width:357px;">获取指定date的月份</td> 
  </tr> 
  <tr> 
   <td style="width:250px;">DAY(date)</td> 
   <td style="width:357px;"> <p>获取指定date的日期</p> </td> 
  </tr> 
  <tr> 
   <td style="width:250px;">DATE_ADD(date,INTEVAL expr type)</td> 
   <td style="width:357px;">返回一个日期/时间值加上一个时间间隔expr后的时间值</td> 
  </tr> 
  <tr> 
   <td style="width:250px;">DATEIFF(date1,date2)</td> 
   <td style="width:357px;">返回起始时间date1和结束时间date2之间的天数</td> 
  </tr> 
 </tbody> 
</table>

#### 语法 ####

> SELECT 函数（参数）;

### 流程函数 ###

**在SQL语句中实现条件筛选，提高语句的效率**

#### 常见流程函数 ####

<table style="width:660px;"> 
 <thead> 
  <tr> 
   <th style="width:400px;">函数</th> 
   <th style="width:258px;">功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:400px;"><span style="color:#0d0016;">IF(value,t,f)</span></td> 
   <td style="width:258px;">如果value为true，则返回t，否则返回f</td> 
  </tr> 
  <tr> 
   <td style="width:400px;"><span style="color:#0d0016;">IFNULL(value1,value2)</span></td> 
   <td style="width:258px;"> <p>如果value1不为空，返回value1，否则返回value2</p> </td> 
  </tr> 
  <tr> 
   <td style="width:400px;"><span style="color:#0d0016;">CASE WHEN [val1] THEN [res1]...ELSE [default] END</span></td> 
   <td style="width:258px;">如果val1位true，返回res1，...否则返回default默认值</td> 
  </tr> 
  <tr> 
   <td style="width:400px;"><span style="color:#0d0016;">CASE [expr] WHEN [val1] THEN [res1] ...ELSE [default] END</span></td> 
   <td style="width:258px;">如果expr的值等于val1，返回res1，...否则返回default默认值</td> 
  </tr> 
 </tbody> 
</table>

## 约束 ##

### 概念 ###

约束是作用于表中字段上的规则，用于限制存储表中的数据

### 目的 ###

保证数据库中数据的正确性、有效性和完整性。

### 分类 ###

<table style="width:652px;"> 
 <thead> 
  <tr> 
   <th style="width:88px;text-align:center;">约束</th> 
   <th style="width:415px;text-align:center;">描述</th> 
   <th style="width:147px;text-align:center;">关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:88px;">非空约束</td> 
   <td style="width:415px;">限制该字段的数据不能为null</td> 
   <td style="width:147px;">NOT NULL</td> 
  </tr> 
  <tr> 
   <td style="width:88px;">唯一约束</td> 
   <td style="width:415px;">保证该字段的所有数据都是唯一、不重复的</td> 
   <td style="width:147px;">UNIQUE</td> 
  </tr> 
  <tr> 
   <td style="width:88px;">主键约束</td> 
   <td style="width:415px;">主键是一行数据的唯一标识，要求非空且唯一</td> 
   <td style="width:147px;">PRIMARY LEY</td> 
  </tr> 
  <tr> 
   <td style="width:88px;">默认约束</td> 
   <td style="width:415px;">保存数据时，如果未指定该字段的值，则采用默认值</td> 
   <td style="width:147px;">DEFAULT</td> 
  </tr> 
  <tr> 
   <td style="width:88px;">检查约束</td> 
   <td style="width:415px;">保证该字段的值满足一个条件</td> 
   <td style="width:147px;">CHECK</td> 
  </tr> 
  <tr> 
   <td style="width:88px;">外键约束</td> 
   <td style="width:415px;">用来让两张表的数据之间建立连接，保证数据的一致性和完整性</td> 
   <td style="width:147px;">FOREIGN KEY</td> 
  </tr> 
 </tbody> 
</table>

> **检查约束在8.0.16版本之后才有**
> 
> **约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束**

### 案例 ###

create table user(
        id   int primary key auto_increment comment '主键', #primary key主键
        name varchar(10) not null unique comment '姓名', #not null 非空
        age  int check ( age >= 0 && age <= 120 ) comment '年龄',  #check 满足条件0-120
        status char(1) default '1' comment '状态', #default 默认值 
        gender char(1) comment '性别'
    )comment '用户表';

## 外键约束 ##

### 概念 ###

用来让两张表的数据之间建立连接，保证数据的一致性和完整性

### 语法 ###

#### 添加外键 ####

> CREATE TABLE 表名(
> 
> 字段名 数据类型,
> 
> ...
> 
> \[CONSTRAINT\] \[外键名称\] FOREIGN KEY(外键字段名)REFERENCES 主表(主表列名)
> 
> );

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

#### 删除外键 ####

> ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

#### 删除/更新行为 ####

<table style="width:612px;"> 
 <thead> 
  <tr> 
   <th style="width:125px;text-align:center;"><span style="color:#0d0016;"><strong>行为</strong></span></th> 
   <th style="width:486px;text-align:center;"><span style="color:#0d0016;"><strong>说明</strong></span></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:125px;">NO ACTION</td> 
   <td style="width:486px;">当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与RESTRICT一致)</td> 
  </tr> 
  <tr> 
   <td style="width:125px;">RESTRICT</td> 
   <td style="width:486px;">当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与NO ACTION一致)</td> 
  </tr> 
  <tr> 
   <td style="width:125px;">CASCADE</td> 
   <td style="width:486px;">当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。</td> 
  </tr> 
  <tr> 
   <td style="width:125px;">SET DEFAULT</td> 
   <td style="width:486px;">当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(这就要求该外键允许取nul)。</td> 
  </tr> 
  <tr> 
   <td style="width:125px;">SET DEFAULT</td> 
   <td style="width:486px;">父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)</td> 
  </tr> 
 </tbody> 
</table>

> ALTER TABLE表名 ADD CONSTRANT 外键名称 FOREIGN KEY (外键字段) REERENCES主表名 (主表字段名）ONUPDATE CASCAODE ONDELETE CASCADE;