MySQL-基础篇 ( 函数 + 约束 )

MySQL-基础篇 ( 函数 + 约束 )

目录

• MySQL-基础篇 ( 函数 + 约束 )
  • 函数
    • 字符串函数
    • 数值函数
    • 日期函数
    • 流程函数
  • 约束
    • 概述
    • 约束演示
    • 外键约束
      • 外键的删除 / 更新行为

函数

• 是指一段可以直接被另一段程序调用的程序或代码
• 解决情况：
  • 表内存储的是入职日期，通过函数快速计算出入职天数
  • 表内存储的是学生的分数值，通过函数快速判定分数属于不及格、及格还是优秀

字符串函数

• MySQL 内置了很多字符串函数，常用的是以下几个

  函数	功能
  CONCAT(S1, S2, ... Sn)	字符串拼接，将 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', ' MySQL'); -- Hello MySQL 
  
  -- lower
  select lower('Hello'); -- hello
  
  -- upper
  select upper('Hello'); -- HELLO
  
  -- lpad
  select lpad('01', 5, '*'); -- ***01 
  
  -- rpad
  select rpad('01', 5, '*'); -- 01***
  
  -- trim
  select trim(' Hello MySQL '); -- Hello MySQL
  
  -- substring (其索引值是从1开始的)
  select substring('Hello MySQL', 1, 5); -- Hello
  
  -- 由于业务需求变更，企业员工的工号，统一为五位数，目前不足五位数的全部在前面补零
  update emp set workno = lpad(workno, 5, '0');
  
  -- 函数内可直接套入字段，字段也可直接与函数结果比较或被赋值
  

数值函数

• 常用如下：

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

• 练习

  -- ceil (只要小数不是零，就进位，负数同理，可理解为坐标轴上都向右取整)
  select ceil(1.5); -- 2
  select ceil(1.1); -- 2
  select ceil(-1.1); -- -1
  
  -- floor (似ceil，只要是非零小数就坐标轴向左取整)
  select floor(1.1); -- 1
  select floor(-1.1); -- -2
  
  -- mod (模：相处后的余数)
  select mod(3, 4); -- 3
  select mod(9, 4); -- 1
  
  -- rand (random 的缩写，返回 0~1 随机数)
  select rand();
  
  -- round 
  select round(2.345, 2); -- 2.35
  select round(2.344, 2); -- 2.34
  
  -- 通过数据库函数，生成一个六位数的随机验证码
  select substring(rand(), 3, 6); -- 写法之一
  -- ceil 可能会造成溢出，floor 概率不均等，简单的左右拼接概率也不均等
  

日期函数

• 常见的函数如下：

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

• 练习

  -- curdate
  select curdate(); -- 2023-07-22
  
  -- curtime
  select curtime(); -- 15:56:54
  
  -- now
  select now(); -- 2023-07-22 15:57:50
  
  -- YEAR, MONTH, DAY (大写会更明显，小写也通用)
  select YEAR(now()); -- 2023
  select month(curdate()); -- 7
  select day('2023-03-30'); -- 30 超出月份规定的天数不会显示
  
  -- date_add (返回的仍是日期格式的)
  select date_add(now(), INTERVAL 70 DAY ); -- 2023-09-30 16:05:35
  select date_add(now(), INTERVAL -2 YEAR ); -- 2021-07-22 16:05:26 负数是向前推
  
  -- datediff (返回的是前减后的天数值)
  select datediff(now(), '2023-01-01'); -- 202
  select datediff('2001-01-01', '2023-01-01'); -- -8035
  
  -- 查询所有员工的入职天数，并根据入职天数倒序排序
  select name, datediff(curdate(), entrydate) as 'time' from emp order by time desc;
  

流程函数

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

  函数	功能
  IF(value, t, f)	如果 value 为 true，则返回 t，否则返回 f
  IFNULL(value1, value2)	如果 value1 不为空，返回 value1，否则返回 value2 ( 注意：此处的空是指 null )
  CASE WHEN [ val1 ] THEN [ res1 ] ... ELSE [ default ] END	如果 val1 为 true，返回 res1，... 否则返回 default 默认值
  CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] .. ELSE [ default ] END	如果 expr 的值等于 val1，返回 res1，... 否则返回 default 默认值

• 上述表格的函数第一个和第二个可视为一种语句不同写法

• 练习

  -- if
  select if(true, 'ok', 'error'); -- true 处应为一个条件表达式
  
  -- ifnull
  select ifnull('ok', 'default');
  select ifnull('', 'default'); -- 不写值的空并非判断的“空”，返回的仍是空白
  select ifnull(null, 'default'); -- 只有值为null时才是判断的“空”，返回的才是default
  
  -- case when then else end
  -- 需求：查询 emp 表的员工姓名和工作地址(北京/上海 ————> 一线城市， 其他的城市 ————> 二线城市)
  select
      name,
      ( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'
  from emp;
  -- 需求：
  --      统计班级各个学员的成绩，展示的规则如下
  --      >= 85的，展示为优秀
  --      >= 60 且 < 85的，展示为及格
  --      否则展示为不及格
  select
      id,
      name,
  #     ( case when math >= 85 then '优秀' when ( math >=60 and math < 85 ) then '及格' else '及格' end ) '数学',
      ( case when math >= 85 then '优秀' when math >=60 then '及格' else '及格' end ) '数学', -- 语句先后顺序，大于85的都筛选过了，能到判断是否大于60的都是小于85的了
      ( case when english >= 85 then '优秀' when english >=60 then '及格' else '及格' end ) '英语',
      ( case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '及格' end ) '语文'
  from score;
  

约束

概述

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

• 目的：保证数据库中数据的正确、有效和完整性

• 分类：

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

• MySQL 所有 —— 自动增长：AUTO_INCREMENT

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

• 在创建时，一个字段若是有多个约束的话，就用空格分开，写于字段类型与注释之间

约束演示

• 例：

  • 新创建一个表，含有字段 id、name、age、status
    • id：int，主键，自动递增
    • name：varchar(10)，不为空，并且唯一
    • age：int，大于 0，并且小于等于 120
    • status：char(1)，如果没有指定该值，默认为 1

• 即：

  create table user(
      id int primary key auto_increment comment '主键',
      name varchar(10) not null unique comment '姓名',
      age int check ( age > 0 and age <= 120 ) comment '年龄',
  #     因为是字符串char类型，所以1 要用引号引起来
      status char(1) default '1' comment '状态'
  ) comment '用户表';
  

  • 但是有个 bug，如果名字重复的话会依照约束不能添加，但是再次成功添加后，就会发现跳了一个 id，即上一个虽然没有成功添加，但是已经申请了 id

• 一般的图形化界面都有约束的选择项，下图是旧版本的 modify 显示界面，单选框更直观

  

外键约束

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

  • 例如，dept 表的 id 为主键，所关联的 emp 表的 dept_id 为外键，外键值选于 dept 表的主键中，拥有主键的叫主表 ( 父表 )，有外键的叫从表 ( 子表 )

• 添加外键：

  • 创建表时 ( 可有多个外键 )

    CREATE TABLE 表名(

    ​ 字段名 数据类型,

    ​ ...

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

    ​ ...

    );

  • 已建表额外增加

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

• 删除外键：

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

• 图形化界面中，主键字段图标会有一个金色钥匙，外键则是蓝色钥匙

外键的删除 / 更新行为

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

• 使用 ( update 与 delete 之间空格隔开 )：

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

  • 即，当更新时 ( on update ) 为 cascade，子表外键也跟着更改，
  • 当删除时 ( on delete ) 为 cascade，相关子表也都删除

• 同样可在图形化界面直接操作