mysql入门（二）——函数与约束

mysql中的函数

之前我们介绍过聚合函数，在MySQL中，聚合函数是用于对多行数据进行计算和统计的函数。它们将多行数据作为输入，并返回单个值作为输出。聚合函数可以对数据进行汇总、计数、求和、平均值、最大值、最小值等操作，用于生成有关数据集的摘要信息。

函数： 是指一段可以最直接被另一段程序调用的程序或代码，在mysql中早已经在内部写好了

字符串函数

我们这里介绍常用的几种字符串函数

函数	描述
CONCAT（s1,s2…sn）	将多个字符串连接起来。
SUBSTRING (str,start,len)	提取字符串的子串。返回字符串str从start位置起的len个长度的字符串
UPPER	将字符串转换为大写。
LOWER	将字符串转换为小写。
LENGTH	返回字符串的长度。
TRIM	去除字符串两端的空格。
LPAD(str,n,pad)	左填充，用字符串pad对str左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充，用字符串pad对str右边进行填充，达到n个字符串长度

例子：

updata 表名 set 字段 = 函数（）;//使用函数更新emp表中的字段

由于业务需求变更，企业员工的工号统一号为5位数，目前不足5位数的全部在前面补0.比如：1号员工的工号应该为00001；

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

字符串函数的基本应用

1. 数据清洗和格式化：字符串函数可用于去除多余的空格、修剪字符串、删除特定字符，以及格式化字符串的大小写等。例如，使用函数如TRIM、UPPER、LOWER和REPLACE可以清理和规范化输入数据。

2. 字符串拼接和连接：使用函数如CONCAT和CONCAT_WS可以将多个字符串连接成一个字符串，常用于生成完整的文本内容或动态生成SQL查询语句。

3. 字符串分割和提取：函数如SUBSTRING、LEFT、RIGHT和MID用于提取字符串的子串，常用于从文本中提取特定的信息或截取部分字符串。

4. 字符串比较和匹配：使用函数如LIKE、INSTR和STRCMP可以进行字符串的模糊匹配、查找特定子串，或者比较字符串的大小或顺序。

5. 字符串长度和计数：函数如LENGTH和CHAR_LENGTH用于计算字符串的长度，常用于验证输入长度或限

向左填充5位数，不足5位数向前补0;

数值函数

函数	描述
ROUND(x,y)	对数字进行四舍五入。求参数x的四舍五入的值,保留y位小数
CEILING	返回大于等于给定数字的最小整数。向上取整
FLOOR	返回小于等于给定数字的最大整数。向下取整
ABS	返回给定数字的绝对值。
MOD （x/y）	返回两个数字相除的余数。

案例：通过数据库的·函数，生成一个六位数的随机验证码。先使用rand乘以1000000 再进行取整；

select rand()*1000000;

因为rand是生成0-1直接的随机数，所以可能会出现0。016563这种数字，导致乘之后不满足6位数的要求，这时候我们可以对其进行补零；

select lpad（round（rand（）*1000000，0），6，‘0’）;

日期函数

日期函数在sql语句中实现对日期类型的查询计算

函数	描述
NOW	返回当前日期和时间。
DATE_FORMAT	将日期格式化为指定的字符串格式。
DATE_ADD（data，INTERVAL expr type）	在给定日期上添加指定的时间间隔expr后的值。
DATEDIFF(date1,date2)	计算date1与date2两个日期之间的天数差。
CURDATE()	返回当前日期
CURTIME()	返回当前时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定日期的月份
DAY(data)	获取指定data的日期

流程函数

流程控制函数在sql中实现条件筛选，从而提高语句的效率

函数	描述
IF (val,t,f)	根据条件返回不同的值。 如果value为true，则返回t，否则返回f
CASE WHEN [val1] THEN [res1] [val2][res2]ELSSE[desault] END	在查询中实现条件逻辑。如果val1为true则返回res1…否则返回default的值
IFNULL(val1,val2)	如果val1不为空，则返回val1，否则返回val2
CASE[expr] WHEN [val1] THEN [res1] [val2][res2]ELSSE[desault] END	如果val1为expr则返回res1…否则返回default的值

注意：ifnull中的条件是如果为null值，并非是为空串，如果是空串，也会返回空串， 其中第二个函数常用于范围判断，返回true或false的情况，第四个常用于固定当某值等于的条件的时候返回

举例： 查询emp员工表中的员工姓名和工作地址（如果是北京上海就返回一线城市，如果是其他则返回二线城市）

select  name  case workaddress when '北京' then '一线城市' when '上海' then '一线城市'  else '二线城市' end from emp;

案例：统计函数各个学院的成绩，展示的规则如下：
如果成绩>=80；展示优秀
如果成绩>=60；展示及格
否则；展示不及格

原表格：

select id,name,
    (case when english <=80 then '及格'when english <= 60 then '及格' else '不及格' end) as '英语成绩'from student;

约束

约束就是限制，束缚，它是用来限制表结构中的数据的，是用于定义和强制表中数据的完整性和规则的规范。它们用于确保表中的数据满足特定的条件，以维护数据的一致性和有效性。

概述

概念： 是用于作用表中字段的规则，用于限制储存在表中的结构。
目的： 保证数据库中数据的正确，有效性和完整性
分类：

名字	作用	关键字
主键约束	唯一标识表中的每一行数据，要求非空且唯一，注意复合主键	PRIMARY KEY
唯一约束	确保列中的数据值是唯一的，不重复的	UNIQUE
非空约束	确保列中的数据值不为空	NOT NULL
外键约束	建立两张表之间的关系并强制执行引用完整性	FOREIGN KEY
检查约束	定义必须满足的插入或更新数据的条件 ，	CHECK
默认约束	保存数据时，如果未指定盖字段的值，则采用默认值	DEFAULT

唯一约束：比如用户创建的用户名，身份证我们就可以加上唯一约束。在很多规范中要求要有主键约束；默认约束：假设我们现在有个状态值，未设置就为1，这时候就可以使用默认约束。一旦出现外键约束，就标志着至少有两张表。对于一个字段可以添加多个约束条件，多个约束使用空格进行隔开。检查约束中有多个条件的时候可以使用&&进行连接。主键可以不给，会进行自动增长。

约束的演示

案例：更具需求，完成表结构的创建
（自动增长：在mysql数据库中：auto_increment）

create table  user(
    id int primary key auto_increment,
    name varchar(10) not null unique ,
    age int check ( age>0&&age<=120 ),
    status char(1) default '1',
    gender char(1) 
)

外键约束

外键约束是让两张表来建立数据连接的，从而保证数据的一致性和完整性。

我们注意观察员工表中的dep_id和部门表中的id（主键），我们就说dep_id是部门表的外键，通过外键进行将两张表连接起来。我们一般叫部门表是主表（父表），员工表是从表（子表）注意：以上的两张表，在数据库上没有建立外键关联， 所以无法保证数据的一致性和完整性，即我在改变一个表中的数据时候，另外一张表中的数据并不会跟着改变。

我们观察以上两张表，他们是通过id进行关联的，所以我们可以通过为两张表依靠id进行相连，创建外键约束。

建立外键关联：

语法：
1.在 创建表结构的时候就进行添加

create table 表名（
	字段名 数据类型
	...
	...
	[constraint][外键名称] foreign key (外键字段名) references 主表（主表列名）
）

2.表创建好之后进行关联

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);

我们现在给上边两个表添加外键约束

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

添加了外键之后，我们就可以保证数据的完整性，所以我们在删除数据的时候，他会进行判断，父表中的数据是否与字表中的数据存在关联情况，如果关联则不能删除，很容易理解，比如我删除了父表，那字表中的那么多数据怎么办，现在两张表已经建立了链接。

删除外键

alter table 表名 drop foreign key 外键名称;

外键的删除/更新行为

行为	描述
CASCADE DELETE	删除主表记录时，关联的从表记录也会被自动删除。
SET NULL	删除主表记录时，关联的从表记录的外键值会被设置为 NULL。
SET DEFAULT	父表有变更时，字表将外键列设置成一个默认值
CASCADE UPDATE	删除主表记录时，关联的从表记录的外键值会被更新为另一个合适的值。
NO ACTION 或者RESTIRICT	如果主表记录被删除时，关联的从表记录仍存在，则阻止删除操作。

注意：后两个是默认情况

语法：

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名)  on update cascade on delete cascade;

就是在上边添加外键的语句后边加上on update与on delete ，代表在更新和删除的时候进行的操作。

为什么要采取外键约束

1. 建立表之间的关系： 当存在多个表之间的关联关系时，使用外键约束可以确保关联的完整性。例如，一个订单表和一个顾客表，可以使用外键约束将订单表中的顾客ID列与顾客表中的主键关联起来，以确保订单表中的顾客ID值必须存在于顾客表中。

2. 维护引用完整性： 外键约束可以确保在插入或更新数据时，引用关系的完整性得到维护。它可以防止在关联表中引用不存在的数据，从而保持数据的一致性。

3. 防止意外删除或修改关联数据： 外键约束可以防止意外删除或修改关联表中的数据。当一个表有外键约束关联到其他表时，如果尝试删除或修改被关联的数据，则会触发外键约束，阻止操作或根据定义的操作行为执行相应的操作。

4. 简化查询和维护操作： 外键约束可以简化查询和维护操作。通过外键约束，可以轻松地执行联接查询、级联删除和更新等操作，而不需要手动处理数据的引用关系。

5. 提高数据完整性和一致性： 外键约束有助于提高数据的完整性和一致性。它可以强制执行数据的关联关系和依赖性，防止不一致或不合法的数据插入或更新。