函数
定义:函数是指一段可以直接被另一段程序调用的程序或代码.
1.字符串函数
常用如下:
函数 | 作用 |
---|---|
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) | 去掉字符串头部和尾部的空格 |
subsrting(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
如:
-- concat
select concat('Hello','World');
-- lower
select lower('HeLLo');
-- upper
select upper('HeLLo');
-- lpad
select lpad('hello',10,'123');
-- rpad
select rpad('hello',10,'123');
-- trim
select trim(' hello world ');
-- substring
select substring('hello,world',2,5);
2.数值函数
函数 | 作用 |
---|---|
ceil(x) | 向上取整 |
floor(x) | 向下取整 |
mod(x,y) | 返回xy的模 |
rand() | 返回0~1内的随机数 |
round(x,y) | 求参数x的四舍五入的值,保留y位小数 |
如:
-- ceil
select ceil(1.1);
-- floor
select floor(1.5);
-- mod
select mod(4,3);
-- rand
select rand();
-- round
select round(4.256,2);
3.日期函数
函数 | 作用 |
---|---|
curdate() | 返回当前日期 |
curtime() | 返回当前时间 |
now() | 返回当前日期和时间 |
year(date) | 获取指定date的年份 |
month(date) | 获取指定date的月份 |
day(date) | 获取指定date的日份 |
date_add(date,interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
datediff(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
如:
-- curdate()
select curdate();
-- curtime()
select curtime();
-- now()
select now();
-- year
select year(now())
-- month
select month(now())
-- day
select day(now())
-- date_add
select date_add(now(),interval 70 day);
select date_add(now(),interval 70 month);
-- datediff 第一个日期值-第二个二日期值
select datediff(now(),date_add(now(),interval 70 day));
select datediff('2024-9-15','2024-7-15');
4.流程控制函数
函数 | 作用 |
---|---|
if(value,t,f) | 如果value为true,则返回t,否则返回f |
ifnull(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
case when [val1] then [res1] … else [default] end | 如果val1为true,返回res1,…否则返回default默认值 |
case [expr] when [val1] then [res1] … else [default] end | 如果expr的值等于vall,返回resl1…否则返回default默认值 |
-- if
select if(true,'success','fail');
select if(false,'success','fail');
-- ifnull
select ifnull('success','fail');
select ifnull('','fail');
select ifnull(null,'fail');
-- case [expr] when [val1] then [res1] ... else [default] end
select username ,(case gender when 1 then '女' when 0 then '男' else '待定' end) as '性别' from user;
如:
-- 案例:统计班级各个学员的成绩,展示的规则如下:
-- >=85,展示优秀
-- >=60,展示及格
-- 否则,使示不及格
create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学',
english int comment'英语',
chinese int comment'语文'
)comment'学员成绩表';
insert into score(id,name,math,english,chinese)VALUES (1,'Tom',67,88,95 ),(2,'Rose',23,66,90),(3,'Jack',56,98,76);
select
id,
name,
case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end as '数学',
case when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end as '英语',
case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end as '语文'
from score;
小结
1.字符串函数
- concat 字符串拼接
- lower 将字符串全部转为小写
- upper 将字符串str全部转为大写
- lpad 左填充
- rpad 左填充
- trim 去掉字符串头部和尾部的空格,字符串中间空格不去掉
- substring 返回从字符串长度
2.数值函数
- ceil 向上取整
- floor 向下取整
- mod 取模运算(简单来说就是取余数)
- rand 0到1之间生成随机数
- round 四舍五入
3.日期函数
- curdate 获取当前日期 如2024-7-15
- curtime 获取当前时间 如 16:05:00
- now 获取当前日期和时间 如 2024-07-15 16:05:57
- year 获取年份
- month 获取月份
- day 获取天
- date_add 添加日期
- datediff 第一个日期-第二个日
4.流程函数
- if 判断第一个条件表达式 如果为true 返回第二个参数,否则返回地三个参数
- ifnull 判断第一个参数是否为null 如果为null 返回第二个参数,如果不为null 返回第一个参数
- case when… then…else…end条件分离的判断
约束
1.概念:约束是作用于表中字段上的规侧,用于限制存储在表中的数据。
2.目的:保证数据库中数据的正确、有效性和完整性。
1.分类
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | not null |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | primary key |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | default |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | check |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | foreign key |
2.案例: 创建users表
字段名 | 字段含义 | 字段类型 | 约束条件 |
---|---|---|---|
id | ID唯一标识 | int | 主键,并且自动增长 |
name | 姓名 | varchar(10) | 不为空,并且唯一 |
age | 年龄 | int | 大于0,并且小于等于120 |
status | 状态 | char(1) | 如果没有指定该值,默认为1 |
gender | 性别 | char(1) | 无 |
create table users(
id int primary key auto_increment comment'主键',
name varchar(10) not null unique comment '姓名',
age int check(age>0 and age<=120 ) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别'
)comment '用户表';
insert into users(name,age,status,gender) values('张三',18,1,'男'),('李四',19,1,'男')
-- 插入空姓名
insert into users(name,age,status,gender) values(null,18,1,'男')
-- > 1048 - Column 'name' cannot be null
-- 插入姓名为李四的数据
insert into users(name,age,status,gender) values('李四',19,1,'男');
-- >1062 - Duplicate entry '李四' for key 'users.name'
-- 插入王五 年龄为130
insert into users(name,age,status,gender) values('王五',130,1,'男');
-- >3819 - Check constraint 'users_chk_1' is violated.
-- 测试status默认值
insert into users(name,age,gender) values('赵六',80,'男');
3.外键约束
3.1数据准备
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50)not null comment'部门名称'
)comment'部门表';
insert into dept(id,name)VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');
-- ==============================================
create table emp(
id int auto_increment comment 'ID'primary key,
name varchar(50)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 emp(id,name,age,job,salary,entrydate,managerid,dept_id)values(1,'金所',66,'总裁',20000,'2000-01-01',null,5),(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
(3,'杨迫',33,'开发',8400,'2000-11-03',2,1),(4,'韦一笑',48,'开发',11006,'2002-02-05',2,1),
(5,'常遇春',43,'开发',10500,'2064-09-07',3,1),(6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);
添加外键
建表时添加
create table 表名(
字段名 数据类型,
....
[constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名)
);
建完表用alter添加
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)
如:
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);
-==========================
delete from dept where id=1;
-- 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`student`.`emp`, CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
删除外键
alter table 表名 drop foreign key 外键名称;
如:
alter table emp drop foreign key fk_emp_dept_id;
3.2删除/更新行为
行为 | 说明 |
---|---|
no action | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与restrict一致) |
restrict | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与no action一致) |
cascade | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
set null | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(要求该外键允许取null) |
set default | 父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持) |
级联更新/删除
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update cascade on delete cascade;
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id) on update cascade on delete cascade;
小结
1.非空约束:not null
2.唯一约束:unique
3.主键约束:primary key(自增 auto_increment (mysql中) )
4.默认约束:default
5.检查约束:check
6.外键约束:foreign key