一、常用函数
- 字符串函数
1)常用字符串函数
一般用于数据库的字符串操作。
函数 | 功能 |
---|---|
COMCAT(s1,s2…) | 字符串拼接成一个字符串 |
LOWER(str) | 转小写 |
UPPER(str) | 转大写 |
LPAD(str,n,pad) | 左填充,将字符串pad对str的左边进行填充,达到n个字符串长度 |
RPAD(str,n,pad) | 右填充,将字符串pad对str的右边进行填充,达到n个字符串长度 |
TRIM(str) | 去掉字符串头部和尾部的空格 |
SUBSTRING(str,start,len) | 返回字符串str从start位置起的len个长度的字符串 |
- 常用数值函数
一般用于操作数据数值。
函数 | 功能 |
---|---|
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0-1内随机数 |
ROUND(x,y) | 求x的四舍五入,保留y位小数 |
- 日期函数
一般用于操作日期时间等。
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取date的年份 |
MONTH(date) | 获取月份 |
DAY(date) | 获取日期 |
DATE_ADD(date,INTERVASL expr type) | 返回日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1,date2) | 返回date1~date2之间的天数 |
- 流程控制函数
一般用于实现条件筛选,提高语句效率。
函数 | 功能 |
---|---|
IF(value,t,f) | 若value位true,返回t,否则返回f |
IFNULL(value1,value2) | 若value1不为空,返回value1,否则返回value2 |
case when [val1] then [res1] <when [val2] then [res2]> … else [default] end | 若val1为true,则返回res1,…否则返回default的默认值,when…then可以有多个 |
case [expor] when [val1] then [res1]…else [default] end | 若expr的值等于val1,返回res1…否则返回默认值 |
用法案例:
1)直接使用。
select COMCAT(s1,s2…);
2)配合语句使用.
select concat(<字段 或 字符串>,<字段 或 字符串>) from 表名;
update 表名 set 字段 = lpad(字段,10,“<填充值>”) ;
3)生成6位随机数
select lpad(round(rand()*1000000,0),6,“0”);
4)获取当前时间年、月、日。
select year(now());
select month(now());
select day(now());
5)获取当前时间往后10年的时间。
select date_add(now(),INTERVAL 10 YEAR);
6)case一般语法。
select
字段1,
(case 字段2 when '条件1' then "结果1" when '条件2' then "结果2"...) as ”别名“
from 表名;
二、约束
- 概念
用于表中字段上的规则,用于限制存储在表中的数据。 - 目的
保证数据库中数据的正确、有效、完整性。 - 分类
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段不能为空 | not null |
唯一约束 | 保证字段数据的唯一,无重复 | unique |
主键约束 | 唯一标识,非空且唯一 | primary key |
默认约束 | 保存数据时,若未指定,采用默认值 | default |
检查约束(8.0.16以上版本) | 保证字段值满足某一个条件 | check |
外键约束 | 用来两张表的数据之间建立连接,保证数据唯一性与完整性 | foreign key |
4.使用
1)创建外键
create table 表名(
字段名 数据类型,
[constraint][外键名称] foreign key (外键字段名) references 主表(主表列名)
)
或者
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);
2)删除外键
alter table 表名 drop foreign key 外键别名
如果创建外键时忘记命名、不知道外键别名,可以用以下语句查看信息。
show create table 表名;
3)删除、更新数据行为。
行为 | 说明 |
---|---|
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;