【MySQL自学】必备知识：函数与约束详解

最新推荐文章于 2025-09-02 14:01:19 发布

原创最新推荐文章于 2025-09-02 14:01:19 发布 · 658 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql #学习方法 #数据分析

数据分析自学专栏收录该内容

9 篇文章

订阅专栏

函数

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

字符串函数
CONCAT(S1,S2,…Sn）字符串拼接，将s1,s2,…sn拼接成一个字符串
)
LOWER (str)将字符串str全部转为小写
UPPER（str)将字符串str全部转为大写
LPAD（str,n,pad）左填充，用字符串pad对左边str的左边进行填充，达到n个字符串长度
RPAD（str,n,pad）右填充，用字符串pad对左边str的右边进行填充，达到n个字符串长度
TRIM（str）去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)返回从字符串str从start位置起的len个长度的字符串

SELECT CONCAT('HELLO','MYSQL');
SELECT LOWER('Hello');
SELECT LPAD('01',5,'-');
SELECT TRIM(' hello mysql ')中间空格会保留
SELECT SUBSTRING('Hello mysql',1,5)

案例:
由于业务变更，企业员工的工号，统一为5位数，目前不足五位数的全部在前面补0。比如:1号员工的工号应该是00001。

update emp set worknumber＝lpad(worknumber,5,'0');

数值函数
常见的数值函数如下:
CEIL(X)向上取整
FLOOR(X)向下取整
MOD(X,Y)返回x/y的模
RAND(X)返回0~1内的随机数
ROUND(X,Y)求参数x的四舍五入的值，保留y位小数

SELECT ceil(1.5) 2
SELECT FLOOR(1.5) 1
SELECT MOD(3,4) 3
SELECT RAND();
SELECT ROUND(2.345,2);  2.35

练习：
通过数据库函数，生成一个六位数的随机验证码

SELECT lpad(round(rand()*1000000,0),6,'0');

日期函数
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定的date的年份
MONTH(date)获取指定date的月份
DAY(date)获取指定的日期
DATE_ADD(date,INTERVAL expr type)
返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数

SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY);
SELECT DATEDIFF('2021-10-01','2021-12-01');

练习：
查询所有员工的入职天数，并根据入职天数倒序排序

SELECT name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;

流程函数
IF(value,t,f) 如果value为true,则返回t,否则返回f;
IF NULL(value1,value2)如果value不为空，返回value1，否则返回value2；
CASE WHEN[val1] TNEN [res1]…ELSE[default] END
如果val1为true,返回res1,…否则返回default默认值

SELECT IF(true,'OK','Error');
SELECT ifnull('OK','Default');
SELECT ifnull('','Default');
SELECT ifnull(null,'Default');

练习1：
查询 emp表中的员工姓名和工作地址（北京/上海—>一线城市，其他----->二线城市）

SELECT name,(case workaddress when '北京'then'一线城市' when '上海'then'二线城市'else'二线城市' end) as '工作地址' from emp;

练习2：
统计班级各个学员的成绩，展示的规则如下；>=85,展示优秀>=60，否则展示不及格

SELECT
id，
name,
(case when math >=85 then '优秀' when math >=60 then '及格' else '不及格' end) '数学' from score;

约束

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

分类：
非空约束限制该字段的数据不能为null NOT NULL
唯一约束保证该字段的所有数据都是唯一、不重复的 UNIQUE
主键约束主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY
主键自动增长 AUTO_INCREMENT
默认约束保存数据时，如果未指定该字段的值，则采用默认值 DEFAULT
检查约束保证字段值满足某个条件 CHECK
外键约束用来让两张表的数据之间建立联系，保证数据的唯一性和完整性 FOREIGN KEY

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

案例举例
创建表

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 '性别'
)comment '用户表';

插入数据

INSERT INTO user(name,age,status,gender) values('Tom1',19,'1','男')，('Tom2',25,'0','男');

2.外键约束
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
语法：
添加外键

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 外键名称;

删除更新行为
NO ACTION/RESTRICT 行为不允许删除/更新
CASCADE 有外键也删除/更新外键在子表中的记录
SET NULL 有外键则设置子表中该外键值为null

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表（主表列名）ON UPDATE CASCADE ON DELETE CASCADE;