MySQL数据库函数与约束详解

迷南。 2024-03-17 13:06 66阅读 0赞

## MySQL数据库函数与约束详解 ##

MySQL数据库是一种功能强大的关系型数据库管理系统，提供了丰富的函数和约束来处理和保护数据。本篇博客将详细介绍MySQL数据库中的函数和约束，并提供实例演示其用法。

### 函数 ###

#### 字符串函数 ####

##### 字符串拼接函数 #####

`CONCAT(str1, str2, ...)`: 将多个字符串拼接成一个字符串。

示例：

SELECT CONCAT('Hello', ' ', 'World') AS Result;
    -- 输出: Hello World

##### 把字符串全部变为小写 #####

`LOWER(str)`: 将字符串转换为小写形式。

示例：

SELECT LOWER('Hello World') AS Result;
    -- 输出: hello world

##### 把字符串全部变为大写 #####

`UPPER(str)`: 将字符串转换为大写形式。

示例：

SELECT UPPER('Hello World') AS Result;
    -- 输出: HELLO WORLD

##### 字符串左填充 #####

`LPAD(str, len, padstr)`: 在字符串左侧填充指定字符，使其长度达到指定长度。

示例：

SELECT LPAD('Hello', 10, '*') AS Result;
    -- 输出: *****Hello

##### 字符串右填充 #####

`RPAD(str, len, padstr)`: 在字符串右侧填充指定字符，使其长度达到指定长度。

示例：

SELECT RPAD('Hello', 10, '*') AS Result;
    -- 输出: Hello*****

##### 去掉字符串头部和尾部的空格 #####

`TRIM([BOTH | LEADING | TRAILING] trimstr FROM str)`: 去掉字符串头部和尾部的指定字符（默认为空格）。

示例：

SELECT TRIM('   Hello   ') AS Result;
    -- 输出: Hello

##### 字符串截取 #####

`SUBSTRING(str, start, length)`: 截取字符串的子串。

示例：

SELECT SUBSTRING('Hello World', 7, 5) AS Result;
    -- 输出: World

#### 应用 ####

以上是一些常用的字符串函数，它们可以帮助我们处理和转换字符串数据，例如拼接字符串、转换大小写、填充空白等操作。

### 数值函数 ###

#### 向上取整 ####

`CEILING(x)`: 返回不小于x的最小整数。

示例：

SELECT CEILING(3.14) AS Result;
    -- 输出: 4

#### 向下取整 ####

`FLOOR(x)`: 返回不大于x的最大整数。

示例：

SELECT FLOOR(3.14) AS Result;
    -- 输出: 3

#### 返回x/y的模 ####

`MOD(x, y)`: 返回x除以y的余数。

示例：

SELECT MOD(10, 3) AS Result;
    -- 输出: 1

#### 求随机数 ####

`RAND()`: 返回一个0到1之间的随机数。

示例：

SELECT RAND() AS Result;
    -- 输出
    
    : 0.123456789

#### 四舍五入，并且保留n位小数 ####

`ROUND(x, d)`: 对x进行四舍五入，保留d位小数。

示例：

SELECT ROUND(3.14159, 2) AS Result;
    -- 输出: 3.14

#### 应用 ####

数值函数在进行数值计算和处理时非常有用。我们可以利用这些函数进行数值的舍入、取整、求余等操作，以满足各种需求。

### 日期函数 ###

#### 返回当前日期 ####

`CURDATE()`: 返回当前日期。

示例：

SELECT CURDATE() AS Result;
    -- 输出: 2023-06-23

#### 返回当前时间 ####

`CURTIME()`: 返回当前时间。

示例：

SELECT CURTIME() AS Result;
    -- 输出: 12:34:56

#### 返回当前日期+时间 ####

`NOW()`: 返回当前日期和时间。

示例：

SELECT NOW() AS Result;
    -- 输出: 2023-06-23 12:34:56

#### 获取指定date的年份 ####

`YEAR(date)`: 返回指定日期的年份。

示例：

SELECT YEAR('2022-01-01') AS Result;
    -- 输出: 2022

#### 获取指定date的月 ####

`MONTH(date)`: 返回指定日期的月份。

示例：

SELECT MONTH('2022-01-01') AS Result;
    -- 输出: 1

#### 获取指定date的天 ####

`DAY(date)`: 返回指定日期的天数。

示例：

SELECT DAY('2022-01-01') AS Result;
    -- 输出: 1

#### 返回一个时间，是date向后推迟number个DAY（或MONTH，YEAR） ####

`DATE_ADD(date, INTERVAL number DAY | MONTH | YEAR)`: 返回指定日期向后推迟一定时间后的日期。

示例：

SELECT DATE_ADD('2022-01-01', INTERVAL 1 MONTH) AS Result;
    -- 输出: 2022-02-01

#### 两个指定时间中相差的天数 ####

`DATEDIFF(date1, date2)`: 返回两个日期之间的天数差。

示例：

SELECT DATEDIFF('2022-01-01', '2022-01-10') AS Result;
    -- 输出: -9

#### 应用 ####

日期函数提供了对日期和时间进行处理和计算的功能。我们可以使用这些函数获取当前日期和时间，提取日期的年、月、日，进行日期的加减运算等操作，满足各种时间相关的需求。

### 流程控制函数 ###

#### 进行判断 ####

`IF(condition, value_if_true, value_if_false)`: 根据条件进行判断并返回相应的值。

示例：

SELECT IF(1 < 2, 'Yes', 'No') AS Result;
    -- 输出: Yes

#### 如果第一个值为null，那么返回第二个值，否则返回第一个值 ####

`COALESCE(value1, value2, ...)`: 返回第一个非null值。

示例：

SELECT COALESCE(NULL, 'Hello') AS Result;
    -- 输出: Hello

#### case语句 ####

`CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result END`: 根据条件进行分支判断。

示例：

SELECT
        CASE
            WHEN score >= 90 THEN '优秀'
            WHEN score >= 80 THEN '良好'
            WHEN score >= 60 THEN '及格'
            ELSE '不及格'
        END AS Result
    FROM
        students;

以上是一些常用的流程控制函数，它们可以帮助我们进行条件判断、空值处理和分支选择等操作，以满足各种复杂的数据处理需求。

### 约束 ###

约束是用于保证数据完整性和一致性的规则。MySQL数据库提供了多种约束类型，包括主键约束、唯一约束和外键约束。

#### 主键约束 ####

主键约束用于标识表中的唯一记录，保证表中每条记录的唯一性。

示例：

CREATE TABLE students (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        age INT
    );

#### 唯一约束 ####

唯一约束用于保证表中某列的值的唯一性。

示例：

CREATE TABLE employees (
        emp_id INT,
        emp_name VARCHAR(50),
        emp_email VARCHAR(50) UNIQUE,
        ...
    );

#### 外键约束 ####

外键约束用于关联两个表的数据，保证数据的一致性。

示例：

CREATE TABLE orders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        order_date DATE,
        ...
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    );

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

ALTER TABLE orders
    ADD FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id);

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

ALTER TABLE orders
    DROP FOREIGN KEY fk_orders_customers;

#### 检测约束 ####

通过定义约束，可以确保数据库中的数据满足预期的规则和关系。如果数据不符合约束条件，将会产生错误或警告，以保证数据的完整性。

#### 非空约束 ####

非空约束用于限制表中某列的值不能为空。

示例：

CREATE TABLE employees (
        emp_id INT PRIMARY KEY,
        emp_name VARCHAR(50) NOT NULL,
        ...
    );

##### 样例 #####

以上是MySQL数据库中常用的函数和约束的介绍。通过这些函数，我们可以对数据进行各种处理和计算；而约束则可以确保数据的完整性和一致性。合理地运用函数和约束，可以使我们的数据库更加强大和可靠。