函数
字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(S1, S2, …, Sn) | 字符串拼接，将 S1, S2, … Sn 拼接成一个字符串 |
| LOWER(str) | 将字符串 str 全部转换为小写 |
| UPPER(str) | 将字符串 str 全部转换为大写 |
| LPAD(str, n, pad) | 左填充，用字符串 pad 填充 str 的左边直到指定长度 |
| RPAD(str, n, pad) | 右填充，用字符串 pad 填充 str 的右边直到指定长度 |
| TRIM(str) | 去掉字符串前后两端的空格 |
| SUBSTRING(str, start, len) | 返回从字符串 str 开始位置 start 到 len 长度的子字符串 |
sql">-- 字符串函数
-- CONCAT(str1,str2,...)
select concat('hello',' mysql'); -- 'hello mysql'-- LOWER(str)
select lower('HelLO'); -- 'hello'-- UPPER(str)
select upper('Hello'); -- 'HELLO'-- LPAD(str,len,padstr)
select lpad('hua',6,'*'); -- '***hua'-- RPAD(str,len,padstr)
select rpad("hua",6,'*'); -- 'hua***'-- TRIM([remstr FROM] str)
select trim(" my sql "); -- 'my sql'-- SUBSTRING(str FROM pos FOR len)
select substring('I love mysql',1,8); -- 'I love m'
案例：
企业员工的工号，统一为4位数，不足4位数的全部在前面补0。比如：1号员工的工号应该为0001
sql">-- 企业员工的工号，统一为4位数，不足4位数的全部在前面补0。比如：1号员工的工号应该为0001
update emp set workno = lpad(workno,4,'0');
数值函数
| 函数 | 功能 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x, y) | 返回 x/y 的模 |
| RAND() | 返回 0~1 内的随机数 |
| ROUND(x, y) | 求参数 x 四舍五入的值，保留 y 位小数 |
sql">-- 数值函数
-- CEIL(X)
select ceil(1.25); -- 2-- FLOOR(X)
select floor(1.59); -- 1-- MOD(N,M)
select mod(9,5); -- 4-- RAND()
select rand();-- ROUND(X)
select round(3.49,1); -- 3.5
案例：
通过数据库函数生成一个7位数验证码
sql"> -- 生成7位数验证码select lpad(round(rand()*10000000,0),7,0);日期函数
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定 date 的年份 |
| MONTH(date) | 获取指定 date 的月份 |
| DAY(date) | 获取指定 date 的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔 expr 后的时间值 |
| DATEDIFF(date1, date2) | 返回开始时间 date1 （晚）和结束时间 date2（早） 之间的天数 |
sql">
-- 日期函数
-- CURDATE()
select curdate(); -- 2025-01-16-- CURTIME()
select curtime(); -- 12:29:50-- NOW()
select now(); -- 2025-01-16 15:14:43-- YEAR(date)
select year(now()); -- 2025-- MONTH(date)
select month(now()); -- 1-- `DAY`(date)
select day(curdate()); -- 16-- DATE_ADD(date,INTERVAL expr unit)
select date_add(curdate(),interval 20 day); -- 2025-02-05
select date_add(curdate(),interval 30 month); -- 2027-07-16
select date_add(curdate(),interval 10 year); -- 2035-01-16-- DATEDIFF(expr1,expr2)
select datediff(curdate(),'2018-11-08'); -- 2261
案例：
查询所以员工的入职天数，并根据天数倒序排序
sql">-- 查询所以员工的入职天数，并根据天数倒序排序
select name,datediff(curdate(),entrydate) as 'date' from emp order by date desc;
| 函数 | 功能 |
|---|---|
| IF(value, t, f) | 如果 value 为 true，则返回 t，否则返回 f |
| IFNULL(value1, value2) | 如果 value1 不为 null，返回 value1，否则返回 value2 |
| CASE WHEN [val] THEN [res] … ELSE [default] END | 如果 val 为 true，则返回 res，否则返回 default 默认值 |
| CASE [expr] WHEN [val] THEN [res] … ELSE [default] END | 如果 expr 的值等于 val，返回 res，否者返回 default 默认值 |
sql">-- 流程控制函数
-- IF(expr1,expr2,expr3)
select if(true,'yes','error'); -- yes
select if(false,'yes','error'); -- no-- IFNULL(expr1,expr2)
select ifnull('yang','hua'); -- yang
select ifnull('','hua'); --
select ifnull(null,'hua'); -- hua-- case when then else end
-- 需求：查询emp表的员工姓名和工作地址（北京/上海--->一线城市，其他---->二线城市）
select name,(case workaddress when '北京' then '一线城市' when '上海' then "一线城市" else '二线城市' end) as '工作地址'
from emp; -- 需求：查询emp表的员工姓名和性别（女--->女士，男--->先生）
select name,(case gender when '女' then '女士' else '先生' end) as '尊称'
from emp;
案例：建立一个学生表：统计班级各个学生的成绩，展示规则：
– 100 - 85,优秀
– 85 - 70,良好
– 70 - 60,及格
– else,不及格
sql">-- 创建student表
create table student(id int comment 'ID',name varchar(15) comment '姓名',chinese int comment '语文成绩',english int comment '英语成绩',math int comment '数学成绩') comment '学生成绩表';
insert into student(id,name,chinese,english,math) values(1,'yangyang',85,91,79),(2,'huahua',90,91,96),(3,"taotao",88,72,53);
insert into student values(4,'tangtang',5,80,60)
-- 统计成绩
select name,(case when chinese between 85 and 100 then '优秀' when chinese between 70 and 84 then '良好' when chinese between 60 and 69 then '及格' else '不及格' END) as '语文',(case when english between 85 and 100 then '优秀' when english between 70 and 84 then '良好' when english between 60 and 69 then '及格' else '不及格' END) as '英语',(case when math between 85 and 100 then '优秀' when math between 70 and 84 then '良好' when math between 60 and 69 then '及格' else '不及格' END) as '数学'
from student;
约束概述：
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为 NULL | NOT NULL |
| 唯一约束 | 保证该字段的所有值都是唯一的，不重复 | UNIQUE |
| 主键约束 | 每一行必须有唯一的主键，要求不能为空 | PRIMARY KEY |
| 默认约束 | 给字段指定默认值，如果没有指定值时使用默认值 | DEFAULT |
| 检查约束 | 限制字段的值符合某些条件（例如，值范围等） | CHECK |
| 外键约束 | 用于确保表中字段的数据值在其他表中存在，以维护数据完整性 | FOREIGN KEY |
注意：
约束是作用于数据库表字段的，可以在创建或修改表时添加约束。
该表格是数据库设计中的重要组成部分，能够帮助数据库管理员确保数据的一致性和完整性。
示例：
| 字段名 | 字段含义 | 字段类型 | 约束条件 | 约束关键字 |
|---|---|---|---|---|
| id | ID唯一标识符 | int | 主键，并且自增增长 | PRIMARY KEY, AUTO_INCREMENT |
| name | 姓名 | varchar(10) | 不为空，并且唯一 | NOT NULL, UNIQUE |
| age | 年龄 | int | 大于0，并且小于等于120 | CHECK ,仅在5.7以上版本才能使用 |
| status | 状态 | char(1) | 如果没有指定值时，默认值为1 | DEFAULT |
| gender | 性别 | char(1) | 无 | - |
sql">CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',age INT CHECK (age BETWEEN 0 AND 120) COMMENT '年龄',status CHAR(1) DEFAULT '1' COMMENT '状态',gender CHAR(1) COMMENT '性别'
) COMMENT '员工表' ENGINE=InnoDB;
-- 插入信息
insert into user(name,age,status,gender) values ('Anna',26,'0','女');
insert into user(name,age,status,gender) values ('Tina',28,'2','女');
insert into user(name,age,gender) values ('Tom',38,'男');
外键约束
sql">-- 建立部门表
create table dept(id int primary key auto_increment comment 'ID',name varchar(20) not null comment '部门名称'
)comment '部门表';
insert into dept (id,name) values (1,'研发部'),(2,'销售部'),(3,'财务部'),(4,'市场部'),(5,'经理部');-- 建立新的员工表
create table employee(id int auto_increment comment 'ID' primary key,name varchar(20) not null comment '姓名',age int comment '年龄',job varchar(20) comment '职位',salary int comment '薪资',entrydate date comment '入职时间',managerid int comment '直属领导ID',dept_id int comment '部门ID') comment '员工表';INSERT INTO employee (name, age, job, salary, entrydate, managerid, dept_id)
VALUES
('张三', 25, '开发工程师', 8000, '2020-01-01', 1, 1),
('李四', 28, '测试工程师', 7000, '2019-05-15', 1, 1),
('王五', 30, '产品经理', 10000, '2018-03-20', 2, 2),
('赵六', 32, '项目经理', 12000, '2017-08-10', 3, 3),
('孙七', 26, 'UI 设计师', 7500, '2021-02-18', 2, 2),
('周八', 27, '运维工程师', 8500, '2020-11-05', 1, 1),
('吴九', 35, '架构师', 15000, '2016-12-01', 3, 3);
概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
语法：
添加外键
create table 表名( 字段名 数据类型, ... [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名) )；
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);
删除外键：
alter table 表名 drop foreign key 外键名称;
sql">-- 添加外键
alter table employee add constraint fk_employee_dept_id foreign key (dept_id) references dept(id);-- 删除外键
alter table employee drop foreign key fk_employee_dept_id;
外键删除更新行为
| 行为 | 说明 |
|---|---|
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与RESTRICT一致） |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与NO ACTION一致） |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。 |
| SET NULL | 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。 |
| SET DEFAULT | 父表有变更时，子表将外键列设置成一个默认的值（innodb不支持） |
sql">-- alter table 表名 add constraint 外键名称 foreign key (外键字段) references （主表字段名） on update cascade on delete cascade;
alter table employee add constraint fk_employee_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;
alter table employee add constraint fk_employee_dept_id foreign key (dept_id) references dept (id) on update set null on delete set null;
