MySQL基础-函数和约束

àì夳堔傛蜴生んèń 2024-05-24 01:51 2阅读 0赞

> 本文介绍MySQL的SQL语句中常用函数以及表字段的约束

#### 文章目录 ####

*  函数分类
 *   *  字符串函数
     *  数值函数
     *  日期函数
     *  流程函数
 *  约束
 *   *  常用约束
     *  外键约束
     *  删除/更新行为

## 函数分类 ##

*  字符串函数
 *  数值函数
 *  日期函数
 *  流程函数

### 字符串函数 ###

![在这里插入图片描述][d35d470b905d4333b283d2783b55f587.jpeg_pic_center]  
例子：

-- 拼接
    SELECT CONCAT('Hello', 'World');
    
    -- 小写
    SELECT LOWER('Hello');
    
    -- 大写
    SELECT UPPER('Hello');
    
    -- 左填充
    SELECT LPAD('01', 5, '-');
    
    -- 右填充
    SELECT RPAD('01', 5, '-');
    
    -- 去除空格
    SELECT TRIM(' Hello World ');
    
    -- 切片（起始索引为1）
    SELECT SUBSTRING('Hello World', 1, 5);
    
    -- 由于业务需求变更，企业员工工号统一为5位数，不足5位数的全部在前面补0.
    UPDATE emp SET workno = LPAD(workno,5,'0');

### 数值函数 ###

![在这里插入图片描述][bfc7f4ae711741dab97fa3b09f64eec3.jpeg_pic_center]  
例子：

#通过函数，生成一个六位数的随机验证码
    SELECT LPAD(ROUND(RAND()*1000000,0),6,0);

### 日期函数 ###

![在这里插入图片描述][d1ef52a1fc8b41a4b1546a69ce4fc5a4.jpeg_pic_center]  
例子：

#查询所有员工的入职天数
    SELECT name,DATEDIFF(CURDATE(),entrydate) AS entryday  FROM EMP ORDER BY entryday DESC;

### 流程函数 ###

![在这里插入图片描述][543d3bba1573417989321c0304b0a911.jpeg_pic_center]

select
        name,
        (case when age > 30 then '中年' else '青年' end)
    from employee;
    select
        name,
        (case workaddress when '北京市' then '一线城市' when '上海市' then '一线城市' else '二线城市' end) as '工作地址'
    from employee;

## 约束 ##

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。  
目的：保证数据库中数据的正确、有效和完整性。

![在这里插入图片描述][60bf786a590e4366a8bb429c85cb4f4a.jpeg_pic_center]  
注意：

*  **约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束**

### 常用约束 ###

![在这里插入图片描述][16c7ab4ba5d545358aef7b3da6bc7fc2.jpeg_pic_center]

create table user(
        id int primary key auto_increment,
        name varchar(10) not null unique,
        age int check(age > 0 and age < 120),
        status char(1) default '1',
        gender char(1)
    );

### 外键约束 ###

添加外键：

CREATE TABLE 表名(
        字段名 字段类型,
        ...
        [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
    );
    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
    -- 例子
    alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

删除外键：  
`ALTER TABLE 表名 DROP FOREIGN KEY 外键名;`

例子：

#为emp表的dept_id字段添加外键，关联dept表的id字段。
    ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id);
    
    #删除名为fk_emp_dept_id的外键
    ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id;

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

![在这里插入图片描述][71568eadac914c97946057fac8d2a8be.jpeg_pic_center]  
更改删除/更新行为：  
`ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;`

[d35d470b905d4333b283d2783b55f587.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/fb3e2a223f5e4d57ac0fda80c8aedfe4.jpeg
[bfc7f4ae711741dab97fa3b09f64eec3.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/797ea9c42c37467a9e1aeb4fbca61f66.jpeg
[d1ef52a1fc8b41a4b1546a69ce4fc5a4.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/04bbc1a613c64af4add383f218eef2c9.jpeg
[543d3bba1573417989321c0304b0a911.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/31ac44be8b044ab4973e44c75e44ccf4.jpeg
[60bf786a590e4366a8bb429c85cb4f4a.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/1afec1227c4f445ab247b0165ff6f6c2.jpeg
[16c7ab4ba5d545358aef7b3da6bc7fc2.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/7ad4363ea6b74525973fa5267dec6dd9.jpeg
[71568eadac914c97946057fac8d2a8be.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/213bf50936544aadab762bedf3e8d546.jpeg