MYSQL三、MYSQL的函数与约束

一、函数

1. 函数：是指一段可以直接被另一段程序调用的程序或代码。
2. MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。

1、字符串函数

--  concat : 字符串拼接 
select concat('Hello' , ' MySQL');  -- 输出： Hello MySQL


--  lower : 全部转小写
 select lower('Hello');  -- 输出：hello

-- upper : 全部转大写
select upper('hello'); -- 输出：HELLO

--  lpad : 左填充（向01的左边填充-，使它达到5个字符的长度）
 select lpad('01', 5, '-');  --  输出：---01

--  rpad : 右填充（向01的右边填充-，使它达到5个字符的长度）
select rpad('01', 5, '-');  -- 输出：01---

-- trim : 去除空格（去掉字符串头部和尾部的空格）
select trim(' Hello  MySQL '); -- 输出：Hello  MySQL


--  substring : 截取子字符串（从字符串'Hello MySQL'的第1个字符起，截取5个字符的长度）
 select substring('Hello MySQL',1,5);  -- 输出：Hello

2、数值函数

-- ceil：向上取整
select ceil(1.1); -- 输出：2

-- floor：向下取整
 select floor(1.9);  -- 输出：1
 
-- mod：取模
 select mod(7,4); -- 输出：3
 
-- rand：获取0-1内的随机数
select rand();

-- round：四舍五入（取小数点后2位数）
select round(2.344,2); -- 输出：2.34

测试：通过数据库的函数，生成一个六位数的随机验证码。
思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0

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

3、日期函数

--  curdate：当前日期
select curdate();

--  curtime：当前时间
 select curtime(); 
 
-- now：当前日期和时间
 select now(); 
 
--  YEAR , MONTH , DAY：当前年、月、日
 select YEAR(now()); 
 select MONTH(now());
 select DAY(now());
 
-- date_add：增加指定的时间间隔 （70的值为正表示日期时间增加，为负表示日期时间减少）
select date_add(now(), INTERVAL 70 YEAR );

--  datediff：获取两个日期相差的天数
 select datediff('2024-10-01', '2024-11-01'); -- 输出：-31

4、流程函数

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

-- 如果false的值为true，则返回Ok，否则返回Error
select if(false, 'Ok', 'Error'); -- Error


-- ifFull，如果参数1不为null，则返回参数1，否则返回参数2
 select ifnull('Ok','Default'); -- Ok
 select ifnull('','Default'); -- 返回空的字符串，即前面的参数
 select ifnull(null,'Default'); -- 返回Default

-- case when then else end
-- 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)

 select
    name,
    ( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'
 from emp;  -- 如果workaddress的值='北京'或者'上海'则返回一线城市，否则返回二线城市

二、约束

1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
2. 目的：保证数据库中数据的正确、有效性和完整性。
   
   约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

案例演示

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  '性别'
 );

在为字段添加约束时，我们只需要在字段之后加上约束的关键字即可，需要关注其语法。

外键约束

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

1.1、添加外键

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


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

--CONSTRAINT关键字用于为外键约束指定一个名称，以便对其进行引用和操作

 create table tb_user(
 id int auto_increment primary key comment '主键ID',
 name varchar(10) comment '姓名',
 age int comment '年龄',
 gender char(1) comment '1: 男 , 2: 女',
 phone char(11) comment '手机号'
 ) comment '用户基本信息表';
 
 create table tb_user_edu(
 id int auto_increment primary key comment '主键ID',
 degree varchar(20) comment '学历',
 major varchar(50) comment '专业',
 primaryschool varchar(50) comment '小学',
 middleschool varchar(50) comment '中学',
 university varchar(50) comment '大学',
 userid int unique comment '用户ID',
 constraint fk_userid foreign key (userid) references tb_user(id)
 ) comment '用户教育信息表';

为表添加数据时，先添加没有外键的表，然后有外键的表再根据主表的的数据添加数据

-- 为emp表的dept_id字段添加外键约束,外键名称为fk_emp_dept_id,关联dept表的主键id。
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

1.2、删除外键

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

-- 删除emp表的外键fk_emp_dept_id。
 alter table emp drop foreign key fk_emp_dept_id;

1.3、 删除/更新行为

1. 添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种:
   

 ALTER TABLE  表名  ADD CONSTRAINT  外键名称  FOREIGN KEY  (外键字段)   REFERENCES   
主表名 (主表字段名)   ON UPDATE CASCADE ON DELETE CASCADE;

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


-- 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 ;