本文标签 : 数据库函数  约束

目录

一、函数

1.字符串函数.

2.数值函数.

3.日期函数.

4.流程函数

二、约束

1.概述

2.约束演示

3.外键约束

 1.概念 :

 2. 实现:

 3.删除/更新行为:

三、总结

1.字符串函数.

 实现:

                            -- 函数演示 ---- 语法: select 函数(参数);-- 1.字符串函数
-- concat(拼接字符串)
select concat('hello','MySQL');-- lower(转换为小写字母)
select  lower('Hello');-- upper(转换为大写字母)
select upper('hello');-- lpad(左边补齐)
select lpad('01',5,'-');-- rpad(右边补齐)
select rpad('01',5,'-');-- trim(去除头部和尾部空格,中间空格还在)
select trim(' hello MySQL ');-- substring(返回当前字符串的前len个字符)
select substring('hello MySQL',1,5);

练习:

-- 1.企业员工的工号,统一为5位数, 目前不足5位数的全部在前面补0.比如: 1号员工的工号应该是00001.
update emp set workno = lpad(workno,5,'0');

2.数值函数.

 实现:

-- 2.数值函数
-- ceil (向上取整)
select ceil(1.5);-- floor(向下取整)
select floor(1.1);-- mod(求模 7%4)
select mod(3,4);-- rand(生成 0~1 之间的随机数)
select rand();-- roune(四舍五入,保留几位小数)
select round(2.345,2);-- 需求 : 通过数据库的函数,生成一个六位数的随机验证码.(0.01*1000000=10000,所以再加个 lpad 函数,不足 6 位补 0)
select lpad(round(rand()*1000000 , 0),6,'0');

3.日期函数.

 实现:

-- 3.日期函数
-- curdate(当前日期)
select curdate();-- curtime(当前时间)
select curtime();-- now(当前日期和时间)
select now();-- year , month , day
select YEAR(now()); -- 当前日期 now ,所属年份 yearselect MONTH(now()); -- 当前日期 now ,所属月份 monthselect DAY(now()); -- 当前日期 now ,所属日期 day-- date_add(返回一个日期/时间,加上一个时间间隔后的时间值)
select date_add(now(),INTERVAL 70 DAY );   -- now 当前时间 (INTERVAL 固定写法) ,加上 70day 后的时间/日期.
select date_add(now(),INTERVAL 70 MONTH );
select date_add(now(),INTERVAL 70 YEAR );-- datediff(两个指定时间之间相差的天数),第一个时间 - 第二个时间
select datediff('2023-4-17','2022-4-17');-- 需求 : 查询所有员工的入职天数,并根据入职天数倒序排序.
select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;
-- 时间相减函数datediff( 当前时间函数curdate - 列表名 entrydate) desc降序

结果: 

4.流程函数

 实现:

-- 4.流程函数
-- if(判断是否为 true ,为 true 返回 ok ,不是返回 Error)
select if(true,'ok','Error');-- ifnull(判断非空,非空返回 ok ,空则返回后面的 default)
select ifnull('ok','default');select ifnull('','default');  -- 字符串 空,返回空字符串select ifnull(null ,'default');  -- 返回 null-- case when then else end
-- 需求:查询 emp 表的员工姓名合工作地址(如果是北京或者上海返回 ---> 一线城市 ,其他返回 ---> 二线城市 )
selectname,( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'
from emp;

结果: 

练习: 

                              -- 创建成绩表 --
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);-- 需求:统计班级各个学员的成绩, 展示的规则如下:
-- >=85,展示优秀,
-- >=60,展示及格,
-- 其他,展示不及格.实现:selectid,name,(case when math >=80 then '优秀' when math >= 60 then '及格' else '不及格' end) '数学',(case when english >=80 then '优秀' when english >= 60 then '及格' else '不及格' end) '英语',(case when chinese >=80 then '优秀' when chinese >= 60 then '及格' else '不及格' end) '语文'
from score;

结果: 

二、约束

1.概述

1.    概念:约束时作用域表中字段上的规则,用于限制存储在表中的数据.

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

3.    分类:

 注意  :  约束是作用表中字段上的,可以在创建表/求该表的时候添加约束.

2.约束演示

按照需求 完成下列表结构的创建: 

 实现:

-- 创建用户表
create table user (id int primary key auto_increment comment '主键',name varchar(10) not null unique comment '姓名',age int check ( age >0 &&age <= 120 ) comment '年龄',status char(1) default '1' comment '状态',genter char(1) comment '性别'
) comment '用户表';

3.外键约束

 1.概念 :

        外键是用来让两张表的数据之间建立联系,从而保证数据的一致性和完整性. 

 2. 实现:

        实现如下两张关系表的主键和外键约束:

 注意 : 目前上述的两张表,在数据库层面,并未建立连接,所以是无法保证数据的一致性和完整性的.

实现:

-- ---------------------------------- 约束 (外键) ---------------------------- 准备数据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, '开发',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(外键字段名) refences 主表 (主表列名)
);-- 法2.创建表后添加约束alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);-- 实现添加外键 :alter table emp add constraint dept_id foreign key (dept_id) references dept (id);

 删除外键:

-- 删除外键
-- 语法 : alter table 表名 drop foreign key 外键名称;alter table emp drop foreign key dept_id;

 3.删除/更新行为:

 no action 和 restrict 为默认行为, set default 基本用不到.

删除/更新行为的语法及实现:

-- ---------------------------------- 删除/更新行为 ------------------------------- 语法 : alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名) on update cascade on DELETE cascade ;-- 实现 :
-- cascade:alter table emp add constraint dept_id foreign key (dept_id) references dept (id) on update cascade on delete cascade;-- set null:alter table emp add constraint dept_id foreign key (dept_id) references dept (id) on update set null on delete set null ;

三、总结

数据库函数,约束相关笔记.如有不足,还望指出!