目录

函数

字符串函数

数值函数

日期函数

流程函数

约束

主键约束

外键约束

1). 添加外键

2). 删除外键

删除/更新行为

1.CASCADE

2.SET NULL

函数

字符串函数

# A. concat : 字符串拼接
select concat('Hello' , ' MySQL');
# B. lower : 全部转小写
select lower('Hello');
# C. upper : 全部转大写
select upper('Hello');
# D. lpad : 左填充
select lpad('01', 5, '-');
# E. rpad : 右填充
select rpad('01', 5, '-');
# F. trim : 去除空格
select trim('   Hello MySQL ');
# G. substring : 截取子字符串
select substring('Hello MySQL',1,5);

update emp set workno = lpad(workno, 5, '0');

数值函数

# A. ceil：向上取整
select ceil(1.1);
# B. floor：向下取整
select floor(1.9);
# C. mod：取模
select mod(7,4);
# D. rand：获取随机数
select rand();
# E. round：四舍五入
select round(2.344,2);

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

日期函数

# A. curdate：当前日期
select curdate();
 
# B. curtime：当前时间
select curtime();
 
# C. now：当前日期和时间
select now();
 
# D. YEAR , MONTH , DAY：当前年、月、日
select YEAR(now());
select MONTH(now());
select DAY(now());
 
# E. date_add：增加指定的时间间隔
select date_add(now(), INTERVAL 70 YEAR );
 
# F. datediff：获取两个日期相差的天数
select datediff('2021-10-01', '2021-12-01');
 
 

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

流程函数

# A. if
select if(false, 'Ok', 'Error');
 
# B. ifnull
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
 
# C. case when then else end
select
name,
( case workaddress when '北京' or '上海'  then '一线城市'else
'二线城市' end ) as '工作地址'
from emp;
 
 

约束

主键约束

 建表时提供约束

CREATE TABLE tb_user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
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 '性别'
);

外键约束

创建两表

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 empp(
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 empp (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, '开 发',11000, '2002-02-05', 2,1), (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程 序员鼓励师',6600, '2004-10-12', 2,1);

1). 添加外键

CREATE TABLE 表名 (

字段名 数据类型 ,

...

[CONSTRAINT] [ 外键名称 ] FOREIGN KEY ( 外键字段名 ) REFERENCES 主表 ( 主表列名 )

);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY ( 外键字段名 )

REFERENCES 主表 ( 主表列名 ) ;

alter table empp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

2). 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称 ;

alter table emp drop foreign key fk_emp_dept_id;

删除/更新行为

语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY ( 外键字段 ) REFERENCES

主表名 ( 主表字段名 ) ON UPDATE CASCADE ON DELETE CASCADE;

1.CASCADE

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on 
update cascade on delete cascade ;

现象是主副表同步

2.SET NULL

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on 
update set null on delete set null ;

我们删除id为1的数据，发现父表的记录是可以正常的删除的，父表的数据删除之后，再打开子表 empp，我们发现子表empp 的dept_id字段，原来dept_id为1的数据，现在都被置为NULL了。

over