MySQL数据库学习笔记，超详细！！（2）_mysql数据库笔记电子档-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_45118352/article/details/128888282

Java小白开始学习MySQL数据库，一方面，跟着视频学习，并记录下学习笔记，方便以后复习回顾。另一方面，发布学习笔记来约束自己，学习路程还很遥远，继续加油坚持！！！希望能帮助到大家！

本次更新MySQL的函数，MySQL中的函数主要分为以下四类：字符串函数、数值函数、日期函数、流程函数；MySQL的约束，约束时作用于表中列上的规则，用于限制加入表的数据。

上期链接MySQL数据库学习笔记，超详细！！（1）

另外还有我的牛客Java专项练习笔记专栏、Spring框架学习专栏也在同步更新，希望大家多多关注，一起学习！！！

3. 函数

MySQL中的函数主要分为以下四类：字符串函数、数值函数、日期函数、流程函数。

3.1 字符串函数

函数	功能
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,strat,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 : 截取子字符串
select substring('Hello MySQL',1,5);   ----> Hello

3.2 数值函数

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

案例：

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

-- floor：向下取整
select floor(1.9);      ----> 1

-- mod：取模
select mod(7,4);        ----> 3

-- rand：获取随机数
select rand();          ----> 0.33234

-- round：四舍五入
select round(2.344,2);  ----> 2.34

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

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

3.3 日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL exprtype)	返回一个日期/时间值加上一个时间间隔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：增加指定的时间间隔
select date_add(now(), INTERVAL 70 YEAR );

-- datediff：获取两个日期相差的天数
select datediff('2021-10-01', '2021-12-01');

查询所有员工的入职天数，并根据入职天数倒序排序。
思路：入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。

select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by
entrydays desc;

3.4 流程函数

函数	功能
IF(value,t,f)	如果value为true，则返回t，否则返回f
IFNULL(value1,value2)	如果value1不为空，返回value1，否则返回value2
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(false, 'Ok', 'Error');                  ----> Error

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

-- 统计班级各个学员的成绩：>=85，展示优秀；>=60，展示及格；否则，展示不及格
select
    id,
    name,
    (case when math > = 85 then '优秀' when math > = 60 then '及格' else '不及格' end) as '数学',
    (case when english > = 85 then '优秀' when english > = 60 then '及格' else '不及格' end) as '英语',
    (case when chinese > = 85 then '优秀' when chinese > = 60 then '及格' else '不及格' end) as '语文'
from score;

4. 约束

4.1 概述

概念：约束时作用于表中列上的规则，用于限制加入表的数据

例如：可以给id列添加约束，让其值不能重复，不能为null值

目的：约束的存在保证了数据库中数据的正确性、有效性和完整性

添加约束可以在添加数据的时候就限制不正确的数据，年龄是3000，数学成绩是-5分这样无效的数据，继而保障数据的完整性。

4.2 分类

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

注意：
约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
一张表只能有**==一个主键约束==**

4.3 非空约束

非空约束用于保证列中所有的数据不能有null值

添加约束

-- 创建表时添加非空约束
CREATE TABLE 表名(
   列名 数据类型 NOT NULL,
   …
); 

-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

删除约束

ALTER TABLE 表名 MODIFY 字段名 数据类型;

4.4 唯一约束

唯一约束用于保证列中所有数据各不相同

添加约束

-- 创建表时添加唯一约束
CREATE TABLE 表名(
   列名 数据类型 UNIQUE [AUTO_INCREMENT],
   -- AUTO_INCREMENT: 当不指定值时自动增长
   …
); 

-- 建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;

删除约束

ALTER TABLE 表名 DROP INDEX 字段名;

4.5 主键约束

主键是一行数据的唯一标识，要求非空且唯一，**==一张表只能有一个主键==**

添加约束

-- 创建表时添加主键约束
CREATE TABLE 表名(
   列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
   …
); 

-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

删除约束

ALTER TABLE 表名 DROP PRIMARY KEY;

4.6 默认约束

保存数据时，未指定值则采用默认值

添加约束

-- 创建表时添加默认约束
CREATE TABLE 表名(
   列名 数据类型 DEFAULT 默认值,
   …
); 

-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

删除约束

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

4.7 约束演示

根据需求，完成表结构的创建。需求如下：

对应的建表语句：

CREATE TABLE user(
    id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
    name varchar(10) UNIQUE NOT NULL COMMENT '姓名',
    age int CHECK(age > 0 && age < = 120) COMMENT '姓名',
    ststus char(1) DEFAULT '1' COMMENT '状态'，
    gender char(1) COMMENT '性别'
);

4.8 外键约束

4.8.1 介绍

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

如何理解上面的概念呢？如下图有两张表，员工表和部门表：

员工表中的dep_id字段是部门表的id字段关联，也就是说1号学生张三属于1号部门研发部的员工。现在要删除1号部门，就会出现错误的数据（员工表中属于1号部门的数据）。而我们上面说的两张表的关系只是我们认为它们有关系，此时需要通过外键让这两张表产生数据库层面的关系，这样要删除部门表中的1号部门的数据将无法删除。

4.8.2 语法

添加外键

-- 创建表时添加外键约束
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
); 

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

案例：

-- 部门表
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
);

删除外键

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

删除外键：

alter table emp drop FOREIGN key fk_emp_dept;

4.8.3 删除/更新行为

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

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

语法

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 ;