SQL中的函数、约束

1.SQL中的函数

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

数据库表中，存储的是入职日期，如2000-11-12，如何快速计算入职天数？？？

数据库表中，存储的是学生的分数值，如98、75，如何快速判定分数的等级呢？？？

1.1字符串函数

函数	功能
CONCAT(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个长度的字符串

-- concat
select concat('hello','world');
-- lower
select lower('HELLO');
-- upper
select upper('hello');
-- lpad
select lpad('hello',10,'ab');
-- rpad
select rpad('hello',10,'ab');

-- trim
select trim('  he l lo  ');

-- substring
select substring('hello world',1,3);


-- 1.由于业务需求变更，企业员工的工号，统一为5位数，，目前不足5位数的全部在前面补0，比如：1号员工的工号应该为00001
update emp set workno=concat(lpad(id,5,'0'));

1.2数值函数

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

-- 数值函数
-- ceil
select ceil(3.14);

-- floor
select floor(3.14);

-- mod
select mod(9,3);

-- rand
select rand();

-- round
select round(3.14159,2);

-- 案例：通过数据库的函数，生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,'0');

1.3日期函数

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

-- 日期函数
-- curdate()
select curdate();
-- curtime()
select curtime();
-- now()
select now();

-- YEAR,MONTH,DAY
select yEAR(now());

select MONTH(now());

select DAY(now());

-- date_add(date,interval)
select date_add('2023-01-01',interval 1 year);
select date_add('2023-01-01',interval 1 month);
select date_add('2023-01-01',interval 1 day);

-- datediff
select datediff('2023-01-01','2022-12-01');

-- 案例：查询所有员工的入职天数，并根据入职天数倒序排序
select name,datediff(curdate(),entrydate) from emp order by datediff(now(),entrydate) desc;

1.4流程控制函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

函数	功能
if（value，t，f）	如果value为true，则返回t，否则返回f
ifnull（value1，value2）	如果value1不为空，返回value1，否则返回value2
case when[vall] then[res1]… else[default] end	如果vall为true，返回res1，…否则返回default默认值
case[expr]when[vall]then[res1]…else[default]end	如果expr的值等于vall，返回res1，…否则返回default默认值

-- 流程控制函数
-- if(test,true,false)
select if(1>2,'true','false');

-- ifnull
select ifnull(null,not null);
-- case when then else end
-- 需求：查询emp表中的员工姓名和工作地址（北京/上海----->一线城市，其他---->二线城市）
select name,
       (case when workadress='北京' or workadress='上海' then '一线城市'
            else '二线城市'
       end )as "工作地址"
from emp;

– 案例：统计班级各个学员的成绩，展示的规则如下：
– 成绩>=85，优秀
– 成绩>=60，及格
– 否则，不及格

create table score(
    id int comment 'id',
    name varchar(20) comment '姓名',
    math int comment '数学',
    chinese int comment '语文',
    english int comment '英语'
)comment '分数表';
insert into score values(1,'张三',80,90,100),(2,'李四',70,80,90),(3,'王五',60,70,80), (4,'赵六',50,60,70);
-- 案例：统计班级各个学员的成绩，展示的规则如下：
-- 成绩>=85，优秀
-- 成绩>=60，及格
-- 否则，不及格
select
    id,
    name,
    (case when math>=85 then '优秀'
          when math>=60 then '及格'
          else '不及格'
    end ) as "数学",
    (case when chinese>=85 then '优秀'
          when chinese>=60 then '及格'
          else '不及格'
    end ) as "语文",
    (case when english>=85 then '优秀'
          when english>=60 then '及格'
          else '不及格'
    end ) as "英语"
from score;

2.约束

1.概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据

2.目的：保证数据库中数据的正确，有效性和完整性

3.分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一，不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果为指定该字段的值，则采用默认值	DEFAULT
检查约束（8.16版本之后）	保证字段值满足一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

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

案例：

字段名	字段含义	字段类型	约束条件
id	ID唯一标识	int	主键，并且自动增长
name	姓名	varchar（10）	不为空，并且唯一
age	年龄	int	大于0，并且小于等于120
status	状态	char（1）	如果没有指定改值，默认为1
gender	性别	char（1）	无

create table user(
    id int primary key auto_increment comment '主键',
    name varchar(20) 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 ('张三',18, '1' ,'男'),('李四',25,'1','男'), ('王五',20,'1','男');

2.1外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

语法：

添加外键

CREATE TABLE表名（
字段名 数据类型，
....
[CONSTRAINT][外键名称]FOREIGN KEY(外键字段名) REFERENCES 主表（主表列名）
）；

ALTER TABLE 表名 ADD CONSTRAINT外键名称FOREIGN KEY(外键字段名)REFERENCES主表（主表列名）；

删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称；

create table dept(
    id int primary key auto_increment comment '主键',
    name varchar(20) not null unique comment '部门名称'
) comment '部门表';
insert into dept(id, name) values (1, '研发部') ,(2, '市场部') ,(3, '财务部'), (4, '销售部'),(5, '总经办');

create table emp(
    id int primary key auto_increment comment '主键',
    name varchar(20) not null unique comment '姓名',
    age int check ( age>0 && age<=120 ) comment '年龄',
    salary double comment '薪资',
    job varchar(20) comment '职位',
    entrydate date comment '入职时间',
    manager_id int comment '直属领导ID',
    dept_id int comment '部门ID'
) comment '员工表';
insert into emp(name,age,salary,job,entrydate,manager_id,dept_id) values('张三', 48, 30000, '总裁', '2020-01-01', null, 5),
('李四', 38, 20000, '项目经理', '2020-01-01', 1, 1), ('王五', 28, 15000, '开发', '2020-01-01', 2, 1),
('赵六', 28, 15000, '开发', '2020-01-01', 2, 1), ('钱七', 28, 15000, '开发', '2020-01-01',3, 1),
('孙八', 28, 15000, '程序员鼓励师', '2020-01-01', 2, 1);

-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id;

外键约束：

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 RESTRICT 一致)
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 NO ACTION一致)
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null (这就要求该外键允许取nl)。
SET DEFAULT	父表有变更时，子表将外键列设置成一一个默认的值(lnnodb不支持)

-- 外键的删除和更新行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;

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

-- 外键的删除和更新行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;

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