SQL基础第二弹-CSDN博客

本文链接：https://blog.csdn.net/2401_86223056/article/details/147894547

sql函数部分

# 字符串函数
-- 拼接
SELECT CONCAT('hello', 'world') AS result; -- concat
-- lower
SELECT LOWER('HELLO WORLD') AS result; -- lower
-- upper
SELECT UPPER('hello world') AS result; -- upper
-- lpad用来填充字符串到指定长度
SELECT LPAD('hello', 10, '*') AS result; -- 左填充
-- rpad用来填充字符串到指定长度
SELECT RPAD('hello', 10, '*') AS result; -- 右填充
-- trim去除字符串两端的空格
SELECT TRIM('   hello world   ') AS result; -- trim
-- substring 用来截取字符串（索引从1开始）
SELECT SUBSTRING('hello world', 1, 5) AS result; -- substring

-- length
SELECT LENGTH('hello world') AS result; -- length（字节长度）
-- left
SELECT LEFT('hello world', 5) AS result; -- left（左边截取）
-- right
SELECT RIGHT('hello world', 5) AS result; -- right（右边截取）
-- locate（查找字符串位置）
SELECT LOCATE('world', 'hello world') AS result; -- locate（查找字符串位置）

-- 给id补全为三位，不足的前面补0,要为STRING类型才能补全
update employees set emp_name = LPAD(emp_name, 10, 'A');
select * from employees;

# 数值函数
-- ceil 向上取整
SELECT CEIL(3.14) AS result; -- ceil
select floor(3.14)as result; -- floor 向下
-- mod 求余数
SELECT MOD(10, 3) AS result; -- mod
-- round 四舍五入
SELECT ROUND(3.14159, 2) AS result; -- round、
select round(3.9,0) as result; -- round
-- 切换到 test 数据库
USE test;


-- rand 随机数（0-1之间，不包含1）
SELECT RAND() AS result; -- rand

-- 案例
-- 获得一个六位随机数 若不满足六位数，前面补0
SELECT LPAD(FLOOR(RAND() * 1000000), 6, '0') AS result; -- 六位随机数
# 返回结果如：591207 663150...



# 日期函数
-- current_date
SELECT CURRENT_DATE() AS result; -- current_date 2025-05-11
-- current_time
SELECT CURRENT_TIME() AS result; -- current_time 21:26:25
-- now
SELECT NOW() AS result; -- now 2025-05-11 21:26:25
-- year
SELECT YEAR(NOW()) AS result; -- year
-- month
SELECT MONTH(NOW()) AS result; -- month
-- day
SELECT DAY(NOW()) AS result; -- day
-- data_add (interval代表时间单位，可以是day、month、year等)
SELECT date_add(NOW(), INTERVAL 1 DAY) AS result; -- data_add
-- datadiff 计算两个日期之间的差值(前面的日期 - 后面的日期)
SELECT DATEDIFF('2025-05-11', '2025-05-01') AS result; -- datadiff

-- 查询员工入职年数（向下取整）并升序排序
#起别名也可以方便后面再次使用
SELECT emp_name, FLOOR(DATEDIFF(NOW(), hire_date) / 365) AS years FROM employees ORDER BY years; -- 查询员工入职年数
SELECT emp_name, DATEDIFF(NOW(), hire_date) AS years FROM employees; -- 查询员工入职天数


#流程控制函数 感觉和三元运算符差不多
-- if if(条件, t, f),条件成立返回t，否则返回f
SELECT IF(1 > 2, 'yes', 'no') AS result;
-- ifnull（value1,value2) 如果第一个参数不为null，返回第一个参数，否则返回第二个参数（判断是否为null）
SELECT IFNULL(NULL, 'default') AS result; -- ifnull
-- case(条件1, t1, 条件2, t2, ..., f),条件成立返回t，否则返回f （类似于switch语句）
-- 员工的工资大于5000，返回高收入，否则返回低收入
SELECT emp_name,
      salary, CASE WHEN salary > 8000 THEN '高收入' ELSE '低收入' END AS income
FROM employees;

-- 统计学生成绩，分为abcd四个等级展示
-- 切换到 test 数据库
USE test;

-- 创建一个名为 student_scores 的表
CREATE TABLE student_scores (
                                id INT PRIMARY KEY AUTO_INCREMENT, -- 自增主键
                                student_id INT NOT NULL,           -- 学生编号
                                student_name VARCHAR(50) NOT NULL, -- 学生姓名
                                chinese_score DECIMAL(5, 2),       -- 语文成绩
                                math_score DECIMAL(5, 2),          -- 数学成绩
                                english_score DECIMAL(5, 2)        -- 英语成绩
);

-- 插入一些示例数据
INSERT INTO student_scores (student_id, student_name, chinese_score, math_score, english_score) VALUES
     (1, '张三', 85.5, 92.0, 88.0),
     (2, '李四', 78.0, 81.5, 79.0),
     (3, '王五', 90.0, 95.0, 93.0),
     (4, '赵六', 65.0, 70.0, 68.0);

-- 查询学生成绩，并根据成绩等级进行分类
SELECT student_name,
       chinese_score,
       math_score,
       english_score,
       CASE
           WHEN chinese_score >= 90 THEN 'A'
           WHEN chinese_score >= 80 THEN 'B'
           WHEN chinese_score >= 70 THEN 'C'
           ELSE 'D'
       END AS chinese_grade,
       CASE
           WHEN math_score >= 90 THEN 'A'
           WHEN math_score >= 80 THEN 'B'
           WHEN math_score >= 70 THEN 'C'
           ELSE 'D'
       END AS math_grade,
       CASE
           WHEN english_score >= 90 THEN 'A'
           WHEN english_score >= 80 THEN 'B'
           WHEN english_score >= 70 THEN 'C'
           ELSE 'D'
       END AS english_grade
from student_scores;

大致有四类函数

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

约束：

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

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

常见约束

非空约束
唯一约束(如，身份证唯一)
主键约束自增
默认约束
检查约束相当于设置了规则
外键约束

注意：约束是作用于表中字段上的，可以在建表或修改表时添加约束

约束演示

字段名	字段含义	字段类型	约束条件	约束关键字
id	id唯一标识	int	主键，自动增长	PRIMARY KEY AUTO_INCREMENT
name	姓名	varchar（10)	不为空，且唯一	NOT NULL UNIQUE
age	年龄	int	>0&&<=120	CHECK
status	状态	char(1)	未指定值时默认为1	DEFAULT
gender	性别	char(1)	无

实际开发中勾选对应选项即可

-- 创建表
CREATE TABLE person (
                        id INT PRIMARY KEY AUTO_INCREMENT, -- 主键，自动增长
                        name VARCHAR(10) NOT NULL UNIQUE,  -- 姓名，不为空且唯一
                        age INT CHECK (age > 0 AND age <= 120)comment '年龄', -- 年龄，范围约束
                        status CHAR(1) DEFAULT '1' comment '状态',        -- 状态，默认值为1
                        gender CHAR(1)                     -- 性别，无约束
);

外键约束：让两张表之间建立连接，保证数据的一致性和完整性

-- 添加外键  FOREIGN KEY
ALTER TABLE 子表名
ADD CONSTRAINT 外键名称 FOREIGN KEY (子表列名) REFERENCES 父表名(父表列名);

-- 删除外键
ALTER TABLE 子表名
DROP FOREIGN KEY 外键名称;


-- 添加外键示例
ALTER TABLE orders
ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 删除外键示例
ALTER TABLE orders
DROP FOREIGN KEY fk_customer_id;

外键行为

外键的删除和更新行为通过 ON DELETE 和 ON UPDATE 子句定义，常见的行为如下：

CASCADE （cascade)
- 删除行为：当父表中的记录被删除时，子表中引用该记录的行也会被删除。
- 更新行为：当父表中的主键被更新时，子表中引用该主键的外键值也会被更新。
SET NULL
- 删除行为：当父表中的记录被删除时，子表中引用该记录的外键值会被设置为 NULL（前提是外键列允许为 NULL）。
- 更新行为：当父表中的主键被更新时，子表中引用该主键的外键值会被设置为 NULL。
SET DEFAULT
- 删除行为：当父表中的记录被删除时，子表中引用该记录的外键值会被设置为默认值（需要定义默认值）。
- 更新行为：当父表中的主键被更新时，子表中引用该主键的外键值会被设置为默认值。
NO ACTION
- 删除/更新行为：如果父表中的记录被删除或更新，而子表中存在引用该记录的行，则会阻止操作（与 RESTRICT 类似，但行为依赖于数据库引擎）。
RESTRICT
- 删除/更新行为：与 NO ACTION 类似，直接阻止删除或更新操作。

示例：

-- 创建带有外键的表，定义删除和更新行为
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE
    ON UPDATE SET NULL
);