Database Basics — (continuously updated)

• 一、数据库基本概念
• • 1.1 数据
  • 1.2 数据库
  • 1.3 数据库管理系统
  • 1.4 数据库系统
• 二、数据库模型
• • 2.1 数据库系统的三种模式
  • 2.2 三个级别
• 三、SQLBasic rules of language
• • 3.1 SQL 语言基本定义
  • 3.2 SQL 语言分类
  • 3.3 数据类型
• 四、SQL 通用语言
• • 4.1 启动和连接 SQL 服务
  • 4.2 数据定义语言（ DDL ）
  • 4.3 数据操作语言（DML）
  • 4.4 数据查询语言（DQL）
  • 4.5 数据控制语言（DCL）
• 五、函数
• • 5.1 字符串函数
  • 5.2 数值函数
  • 5.3 日期函数
  • 5.4 流程控制函数
• 六、约束
• • 6.1 概述
  • 6.2 约束演示

一、数据库基本概念 1.1 数据

数据（data）是数据库存储的基本对象,是描述事物的符号记录;文章源自玩技e族-https://www.playezu.com/748598.html

1.2 数据库文章源自玩技e族-https://www.playezu.com/748598.html

数据库（ dataBase,DB ）是长期储存在计算机内、有组织的、可共享的大量数据的集合;文章源自玩技e族-https://www.playezu.com/748598.html

数据库的基本特征：文章源自玩技e族-https://www.playezu.com/748598.html

1. 数据按一定的数据模型组织、描述和储存;
2. 可为各种用户共享;
3. 冗余度小;
4. 数据独立性搞;
5. 易扩展;

1.3 数据库管理系统文章源自玩技e族-https://www.playezu.com/748598.html

数据库管理系统（ DBMS ）是位于用户与操作系统之间的一层数据管理软件,是一个大型复杂的软件系统;文章源自玩技e族-https://www.playezu.com/748598.html

​ DBMS 的用途：文章源自玩技e族-https://www.playezu.com/748598.html

• 科学地组织和存储数据;
• 高效地获取和维护数据;

1.4 数据库系统文章源自玩技e族-https://www.playezu.com/748598.html

数据库系统（ Database System ,DBS ）是计算机系统中引入数据库后的系统构成;文章源自玩技e族-https://www.playezu.com/748598.html

数据库系统的构成：文章源自玩技e族-https://www.playezu.com/748598.html

1. 数据库;
2. 硬件平台;
3. 软件（ 应用程序 ）;
4. 数据库管理员;

数据库系统的设计目标是允许用户逻辑地处理数据,而不必涉及这些数据在计算机中是如何存放的,在数据组织和用户应用之间提供某种程度的独立性;

二、数据库模型 2.1 数据库系统的三种模式

​ 数据库系统可分为：

1. 外模式（子模式、用户模式）：

（1）数据库用户（ 包括应用程序员和最终用户 ）使用的局部数据的逻辑结构和特征的描述;

（2）数据库用户的数据视图,是与某一应用有关的数据的逻辑表示;

（3）外模式的地位：介于模式与应用之间;

（4）模式与外模式的关系：一对多;

（5）外模式通常是模式的子集;

（6）一个数据库可以有多个外模式,反映了不同的用户的应用需求、看待数据的方式、对数据保密的要求;

（7）外模式的用途：

• 保证数据库安全性的一个有力措施;
• 每个用户只能看见和访问所对应的外模式中的数据;

1. 模式（概念模式、逻辑模式）：

• 数据库中全体数据的逻辑结构和特征的描述;

• 所有用户的公共数据视图,综合了所有用户的需求

• 一个数据库只有一个模式;

1. 内模式（存储模式）：

• 是数据物理结构和存储方式的描述;
• 是数据在数据库内部的表示方式;
• 一个数据库只有一个内模式;

2.2 三个级别

与三级模式相对应,数据库系统可以划分为三个抽象级：

1. 用户级数据库：对应于外模式,是用户看到和使用的数据库,又称用户视图,一个数据库可以有多个不同的用户视图;
2. 概念级数据库：对应于概念模式,是所有的用户视图的最小并集,一个数据库应用系统只有一个 DBA 视图;
3. 物理级数据库：对应于内模式,是数据库的低层表示,它描述数据的实际存储组织,是最接近于物理存储的,又称内部视图;

三、SQLBasic rules of language 3.1 SQL 语言基本定义

1. SQL 是结构化查询语言（ Structured Query Language ）的缩写,其功能包括数据查询、数据操控、数据定义和数据控制四个部分;

• SQL 语言简洁、方便实用、功能齐全,已成为目前应用最广的关系数据库语言;
• SQL 既是自含式语言（ 联机交互 ）,又是嵌入式语言（ 宿主语言 ）;

1. 两个基本概念

基本表：

是独立存在的表,不是由其它的表导出的表,一个关系对应一个基本表,一个或多个基本表对应一个存储文件;

视图：

视图是一个虚拟的表,是一个或几个基本表导出的表,数据库中只存放视图的定义而不存放视图对应的数据,这些数据仍存放在导出视图的基本表中,当基本表中的数据发生变化时,从视图中查询出来的数据也随之改变;

3.2 SQL 语言分类

1. DDL（Data Definition Language）：数据定义语言,用来定义数据库对象（ 数据库,表,字段 ）;
2. DML（ Data Manipulation Language）：数据操作语言,用来对数据表中的数据进行增删改;
3. DQL（ Data Query Language）：数据查询语言,用来查询数据库中表的记录;
4. DCL（ Data Control Language）：数据控制语言,用来创建数据库用户、控制数据库的访问权限;

3.3 数据类型

MySql 中的数据类型有很多,主要分为三类:数值类型,字符串类型,日期时间类型;

部分数值类型 > > > > > > > >

Partial string type > > > > > > > >

Partial date type > > > > > >

四、SQL 通用语言 4.1 启动和连接 SQL 服务

1. MySQL服务启动（以管理员运行）：

-- 启动服务
net start mysql80
-- 关闭服务
net stop mysql80

1. 客户端连接：

-- 写法一
mysql [-h 127.0.0.1] [-p 3306] -u root -p
-- 写法二
MySQL -u root -p

4.2 数据定义语言（ DDL ）

1. 查询：

-- 查询所有数据库
SHOW DATABASES;
-- 查询当前数据库
SELECT DATABASE();
-- 查询当前数据库所有表
SHOW TABLES;
-- 查询表结构
DESC 表名;
-- 查询指定表的建表语句
SHOW CREATE TABLE 表名;

1. 创建：

/* IF NOT EXISTS：如果不存在 DEFAULT CHARSET：指定默认字符集 COLLATE 指定排序规则 */
-- 创建一个数据库
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集 ] [ COLLATE 排序规则 ]
-- 创建一个表
CREATE TABLE 表名(
字段1 字段1类型[ COMMENT 字段1注释 ],
字段2 字段2类型[ COMMENT 字段2注释 ],
字段3 字段3类型[ COMMENT 字段3注释 ],
......
字段n 字段n类型[ COMMENT 字段n注释 ],
)[ COMMENT 表注释 ];

1. 删除：

-- IF EXISTS：如果存在
DROP DATABASE [ IF EXISTS ] 数据库名;
-- 删除字段
ALTER TABLE 表名 DROP 字段名;
-- 删除表
DROP TABLE [IF EXISTS] 表名;
-- 删除指定表,and recreate this table
TRUNCATE TABLE 表名;

1. 使用：

USE 数据库名;

1. 修改：

-- 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
-- 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
-- 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束];
-- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

4.3 数据操作语言（DML）

1. 添加数据（INSERT）：

• 插入数据时,指定的字段顺序需要与值的顺序是一一对应的;
• 字符串和日期类型数据应该包含在引号中;
• The inserted data size should be within the specified range;

-- 给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUE(值1,值2,...);
-- 给全部字段添加数据
INSERT INTO 表名 VALUE (值1,值2,...);
-- 批量添加数据
#写法一
INSERT INTO 表名 (字段1,字段2,...) VALUE (值1,值2,...),(值1,值2,...),...,(值1,值2,...);
#写法二
INSERT INTO 表名 VALUE (值1,值2,...),(值1,值2,...),...,(值1,值2,...);

1. 修改数据（UPDATE）：

• If you do not set the conditions to modify the table,It means to modify the data of the entire table;

-- 修改一个值
UPDATE 表名 SET 字段名=值 [WHERE 条件];
-- 修改多个值
UPDATE 表名 SET 字段1=值1,字段2=值2,...,字段n=值n [WHERE 条件];

1. 删除数据（DELETE）：

• A delete statement cannot only delete the value of a certain field（可以利用UPDATE将值设置为 null It is equivalent to deleting the value of a field）;
• The delete statement will delete the data of the entire table if there is no condition;

-- 删除一条数据
DELETE FROM 表名 [WHERE 条件];

4.4 数据查询语言（DQL）

1. DQL 语法：

SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
Group field query
HAVING
分组后条件查询
ORDER BY
排序字段列表
LIMIT
分页参数

1. 基础查询

-- 查询多个字段
#查询指定字段
SELECT 字段1,字段2,字段3... FROM 表名;
#查询所有字段
SELECT * FROM 表名;
-- Set the alias after the query AS
SELECT 字段1[AS 别名1],字段2[AS 别名2],...,字段n[AS 别名n] FROM 表名;
-- 去除重复记录 DISTINCT
SELECT DISTINCT 字段列表 FROM 表名;

1. 条件查询

• WHERE Conditions support comparison operators and logical operators;

SELECT 字段列表 FROM 表名 WHERE 条件列表;

The following are commonly used conditional operators：

> > > > > > > > > >

> > > >

1. 聚合函数

• 聚合函数：聚合函数又叫组函数,通常是对表中的数据进行统计和计算,一般结合分组(group by)来使用,用于统计和计算分组数据;

SELECT 聚合函数(字段列表) FROM 表名;

常见的聚合函数：

> > > > > >

1. 分组查询

• 分组查询就是将查询结果按照指定字段进行分组,字段中数据相等的分为一组;
• GRPUP BY 分组;
• HAVING 过滤;

SELECT 字段列表 [分组别名] FROM 表名 [WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件];

WHERE 和 HAVING 区别：

执行时机：WHERE 是分组之前进行过滤,不满足 WHERE 条件不参与分组,而 HAVING 是分组之后对结果进行过滤;

判断条件不同：WHERE 不能对聚合函数进行判断,而 HAVING 可以;

1. 排序查询

• Sorting query is to display the query result set in ascending or descending order according to the specified field;
• 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序;
• ORDER BY 排序;
• ASC：升序（默认值）;
• DESC：降序;

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

1. 分页查询

• 当要显示的数据,一页显示不全,需要分页提交 SQL 请求;
• 起始索引从 0 开始,起始索引 = (查询页码-1) * 每页显示记录数;
• Paging queries have different implementations in different databases,MySql 中是 LIMIT 关键字;
• If you are querying the first page of data, you can omit the starting index;

SELECT 字段列表 FROM 表名 LIMT 起始索引,查询记录数;

1. DQL 执行顺序

FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY => LIMIT;

4.5 数据控制语言（DCL）

1. 管理用户

-- 用户信息是存储在mysql数据库中的,To be used before managing usersmysql数据库
USE mysql;
-- 查询user（mysql 数据库中的 user All users of the current database are stored）表的所有信息
SELECT * FROM user;

Through the query, you can see that there are four users in the database as follows：

The difference between the four users：

• mysql.infoschema 用户：是 MySQL 数据库的系统用户,用来管理和访问系统自带实例 information_schema （信息数据库,保存着关于 MySQL 服务器所维护的所有其他数据库的信息）示例的;
• mysql.session 用户：插件内部使用来访问服务器,该用户已被锁定,客户端无法连接;
• mysql.sys 用户：用于 sys schema 中对象的定义,使用 mysql.sys Users can avoid DBA 重命名或者删除 root The problem occurs when the user,该用户已被锁定,客户端无法连接;
• root 用户：用户拥有所有权限,可执行任何操作;

1. 创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

示例：

create user 'caterpillar'@'localhost' identified by '123456';

localhost Indicates the current machine,If you want it to be able to access the database on any host, write the host name as % ;

1. 修改用户密码

-- mysql_native_password 本地密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

1. 删除用户

DROP USER'用户名'@'主机名';

1. 权限控制

• 多个权限之间,使用逗号分隔;
• Wildcards can be used for databases and tables when authorizing * 表示所有;

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
-- 授予权限
GRANTS 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

MySql Common permissions in > > > >

五、函数 5.1 字符串函数
常见的字符串函数 > > > > > > > > > > >

示例：

-- 获取'hello'和'world'的拼接形式
SELECT CONCAT('hello','world');
/* 输出： mysql> SELECT CONCAT('hello','world'); +-------------------------+ | CONCAT('hello','world') | +-------------------------+ | helloworld | +-------------------------+ 1 row in set (0.00 sec) */
-- 将 'caterpillar' 从第2character start count6个替换成 'hello'
SELECT INSERT('caterpillar',2,6,'hello');
/* 输出： mysql> SELECT INSERT('caterpillar',2,6,'hello'); +-----------------------------------+ | INSERT('caterpillar',2,6,'hello') | +-----------------------------------+ | chellollar | +-----------------------------------+ 1 row in set (0.00 sec) */
-- 获取 'HELLO'的小写形式
SELECT LOWER('HELLO');
/* 输出： mysql> SELECT LOWER('HELLO'); +----------------+ | LOWER('HELLO') | +----------------+ | hello | +----------------+ 1 row in set (0.00 sec) */
-- 获取'hello'的大写形式
SELECT UPPER('hello');
/* 输出： mysql> SELECT UPPER('hello'); +----------------+ | UPPER('hello') | +----------------+ | HELLO | +----------------+ 1 row in set (0.00 sec) */
-- 获取 'caterpillar' 的前5个字符
SELECT LEFT('caterpillar',5);
/* 输出： mysql> SELECT LEFT('caterpillar',5); +-----------------------+ | LEFT('caterpillar',5) | +-----------------------+ | cater | +-----------------------+ 1 row in set (0.00 sec) */
-- 获取 'caterpillar' 的后6个字符
SELECT RIGHT('caterpillar',6);
/* 输出： mysql> SELECT RIGHT('caterpillar',6); +------------------------+ | RIGHT('caterpillar',6) | +------------------------+ | pillar | +------------------------+ 1 row in set (0.00 sec) */
-- 在 'caterpillar' 前填充 'AA' 直到长度达到20
SELECT LPAD('caterpillar',20,'AA');
/* 输出： mysql> SELECT LPAD('caterpillar',20,'AA'); +-----------------------------+ | LPAD('caterpillar',20,'AA') | +-----------------------------+ | AAAAAAAAAcaterpillar | +-----------------------------+ 1 row in set (0.00 sec) */
-- 在 'caterpillar' 后填充 'ZZ' 直到长度达到20
SELECT RPAD('caterpillar',20,'ZZ');
/* 输出： mysql> SELECT RPAD('caterpillar',20,'ZZ'); +-----------------------------+ | RPAD('caterpillar',20,'ZZ') | +-----------------------------+ | caterpillarZZZZZZZZZ | +-----------------------------+ 1 row in set (0.00 sec) */
-- 去除 ' hello world ' 左边的空格
SELECT LTRIM(' hello world ');
/* 输出： mysql> SELECT LTRIM(' hello world '); +--------------------------+ | LTRIM(' hello world ') | +--------------------------+ | hello world | +--------------------------+ 1 row in set (0.00 sec) */
-- 去除 ' hello world ' 右边的空格
SELECT RTRIM(' hello world ');
/* 输出： mysql> SELECT RTRIM(' hello world '); +--------------------------+ | RTRIM(' hello world ') | +--------------------------+ | hello world | +--------------------------+ 1 row in set (0.00 sec) */
-- 将 'caterpillar ' 重复五次
SELECT REPEAT('caterpillar ',5);
/* 输出： mysql> SELECT REPEAT('caterpillar ',5); +--------------------------------------------------------------+ | REPEAT('caterpillar ',5) | +--------------------------------------------------------------+ | caterpillar caterpillar caterpillar caterpillar caterpillar | +--------------------------------------------------------------+ 1 row in set (0.00 sec) */
-- 使用 'CATER' 替换 在 'caterpillar caterpillar' 出现的所有 'cater'
SELECT REPLACE('caterpillar caterpillar','cater','CATER');
/* 输出： mysql> SELECT REPLACE('caterpillar caterpillar','cater','CATER'); +----------------------------------------------------+ | REPLACE('caterpillar caterpillar','cater','CATER') | +----------------------------------------------------+ | CATERpillar CATERpillar | +----------------------------------------------------+ 1 row in set (0.00 sec) */
-- 比较 'abcc' 的 'accc' 大小
SELECT STRCMP('abcc','accc');
/* 输出： mysql> SELECT STRCMP('abcc','accc'); +-----------------------+ | STRCMP('abcc','accc') | +-----------------------+ | -1 | +-----------------------+ 1 row in set (0.00 sec) */
-- 去除 ' hello world ' 头尾的空格
SELECT TRIM(' hello world ');
/* 输出： mysql> SELECT TRIM(' hello world '); +-------------------------+ | TRIM(' hello world ') | +-------------------------+ | hello world | +-------------------------+ 1 row in set (0.00 sec) */
-- 获取 'caterpillar' 从第6个字符开始长度为4的字符串
SELECT SUBSTRING('caterpillar',6,4);
/* 输出： mysql> SELECT SUBSTRING('caterpillar',6,4); +------------------------------+ | SUBSTRING('caterpillar',6,4) | +------------------------------+ | pill | +------------------------------+ 1 row in set (0.00 sec) */

5.2 数值函数
常见的数值函数 > > > > > > > > >

示例：

-- 返回 -1 的绝对值
SELECT ABS(-1);
/* 输出： mysql> SELECT ABS(-1); +---------+ | ABS(-1) | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) */
-- 将1.5向上取整
SELECT CEIL(1.5);
/* 输出： mysql> SELECT CEIL(1.5); +-----------+ | CEIL(1.5) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) */
-- 将1.5向下取整
SELECT FLOOR(1.5);
/* 输出： mysql> SELECT FLOOR(1.5); +------------+ | FLOOR(1.5) | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) */
-- 求 32 除以 3 的余数
SELECT MOD(32,3);
/* 输出： mysql> SELECT MOD(32,3); +-----------+ | MOD(32,3) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) */
-- 返回一个 0 - 1 之间的随机数
SELECT RAND();
/* 输出： mysql> SELECT RAND(); +--------------------+ | RAND() | +--------------------+ | 0.8252376120772563 | +--------------------+ 1 row in set (0.00 sec) */
-- 标记 1、2 两个 0 - 1 的随机数
SELECT RAND(1),RAND(1),RAND(2),RAND(2);
/* 输出： mysql> SELECT RAND(1),RAND(1),RAND(2),RAND(2); +---------------------+---------------------+--------------------+--------------------+ | RAND(1) | RAND(1) | RAND(2) | RAND(2) | +---------------------+---------------------+--------------------+--------------------+ | 0.40540353712197724 | 0.40540353712197724 | 0.6555866465490187 | 0.6555866465490187 | +---------------------+---------------------+--------------------+--------------------+ 1 row in set (0.00 sec) */
-- 对 4.56 四舍五入
SELECT ROUND(4.56);
/* 输出： mysql> SELECT ROUND(4.56); +-------------+ | ROUND(4.56) | +-------------+ | 5 | +-------------+ 1 row in set (0.00 sec) */
-- 对 4.51624 保留两位小数四舍五入
SELECT ROUND(4.51624,2);
/* 输出： mysql> SELECT ROUND(4.51624,2); +------------------+ | ROUND(4.51624,2) | +------------------+ | 4.52 | +------------------+ 1 row in set (0.00 sec) */
-- 对 2.3456 保留3位小数
SELECT TRUNCATE(2.3456,3);
/* 输出： mysql> SELECT TRUNCATE(2.3456,3); +--------------------+ | TRUNCATE(2.3456,3) | +--------------------+ | 2.345 | +--------------------+ 1 row in set (0.00 sec) */
-- Return the data set (4,2,5,1,4,2) 中的最小值
SELECT LEAST(4,2,5,1,4,2);
/* 输出： mysql> SELECT LEAST(4,2,5,1,4,2); +--------------------+ | LEAST(4,2,5,1,4,2) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) */
-- Return the data set (4,2,5,1,4,2) 中的最大值
SELECT GREATEST(4,2,5,1,4,2);
/* 输出： mysql> SELECT GREATEST(4,2,5,1,4,2); +-----------------------+ | GREATEST(4,2,5,1,4,2) | +-----------------------+ | 5 | +-----------------------+ 1 row in set (0.00 sec) */
-- 返回 -5 的正负值
SELECT SIGN(-5);
/* 输出： mysql> SELECT SIGN(-5); +----------+ | SIGN(-5) | +----------+ | -1 | +----------+ 1 row in set (0.00 sec) */
-- 返回 9 的平方根
SELECT SQRT(9);
/* 输出： mysql> SELECT SQRT(9); +---------+ | SQRT(9) | +---------+ | 3 | +---------+ 1 row in set (0.00 sec) */

5.3 日期函数
常见的日期函数 > > > > > > > >

示例：

-- Returns the current date and time combination、当前日期、当前时间
SELECT NOW(),CURDATE(),CURTIME();
/* 输出： mysql> SELECT NOW(),CURDATE(),CURTIME(); +---------------------+------------+-----------+ | NOW() | CURDATE() | CURTIME() | +---------------------+------------+-----------+ | 2022-11-28 14:21:10 | 2022-11-28 | 14:21:10 | +---------------------+------------+-----------+ 1 row in set (0.00 sec) */
-- Returns the date portion of the current datetime expression
SELECT DATE(NOW());
/* 输出： mysql> SELECT DATE(NOW()); +-------------+ | DATE(NOW()) | +-------------+ | 2022-11-28 | +-------------+ 1 row in set (0.00 sec) */
-- Returns the year of the current time、月、日、时、分、秒;
SELECT
EXTRACT(YEAR FROM NOW()) AS '年',
EXTRACT(MONTH FROM NOW()) AS '月',
EXTRACT(DAY FROM NOW()) AS '天',
EXTRACT(HOUR FROM NOW()) AS '时',
EXTRACT(MINUTE FROM NOW()) AS '分',
EXTRACT(SECOND FROM NOW()) AS '秒';
/* 输出： mysql> SELECT -> EXTRACT(YEAR FROM NOW()) AS '年', -> EXTRACT(MONTH FROM NOW()) AS '月', -> EXTRACT(DAY FROM NOW()) AS '天', -> EXTRACT(HOUR FROM NOW()) AS '时', -> EXTRACT(MINUTE FROM NOW()) AS '分', -> EXTRACT(SECOND FROM NOW()) AS '秒'; +------+------+------+------+------+------+ | 年 | 月 | 天 | 时 | 分 | 秒 | +------+------+------+------+------+------+ | 2022 | 11 | 28 | 14 | 29 | 10 | +------+------+------+------+------+------+ 1 row in set (0.00 sec) */
-- The current time in one month
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH ) AS '一月后';
/* 输出： SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH ) AS '一月后'; +---------------------+ | 一月后 | +---------------------+ | 2022-12-28 14:30:57 | +---------------------+ 1 row in set (0.00 sec) */
-- 十年前的今天
SELECT DATE_SUB(NOW(),INTERVAL 10 YEAR ) AS '十年前';
/* 输出： mysql> SELECT DATE_SUB(NOW(),INTERVAL 10 YEAR ) AS '十年前'; +---------------------+ | 十年前 | +---------------------+ | 2012-11-28 14:33:32 | +---------------------+ 1 row in set (0.00 sec) */
-- '2012-10-20' 减去 '2022-01-05' 的天数
SELECT DATEDIFF('2012-10-20','2022-01-05');
/* 输出： mysql> SELECT DATEDIFF('2012-10-20','2022-01-05'); +-------------------------------------+ | DATEDIFF('2012-10-20','2022-01-05') | +-------------------------------------+ | -3364 | +-------------------------------------+ 1 row in set (0.00 sec) */
-- 根据 '%Y %c %a %D' format to output the current time
SELECT DATE_FORMAT(NOW(),'%Y %c %a %D');
/* 输出： mysql> SELECT DATE_FORMAT(NOW(),'%Y %c %a %D'); +----------------------------------+ | DATE_FORMAT(NOW(),'%Y %c %a %D') | +----------------------------------+ | 2022 11 Mon 28th | +----------------------------------+ 1 row in set (0.00 sec) */
-- 获取当前时间的年月日
SELECT
YEAR(NOW()) AS '年',
MONTH(NOW()) AS '月',
DAY(NOW()) AS '日';
/* 输出： mysql> SELECT -> YEAR(NOW()) AS '年', -> MONTH(NOW()) AS '月', -> DAY(NOW()) AS '日'; +------+------+------+ | 年 | 月 | 日 | +------+------+------+ | 2022 | 11 | 28 | +------+------+------+ 1 row in set (0.00 sec) */

MySql中的日期格式

5.4 流程控制函数
Three flow control functions

示例：

-- 如果 4 > 5 成立返回 'A' 否则返回 'B'
SELECT IF(4>5,'A','B');
/* 输出： mysql> SELECT IF(4>5,'A','B'); +-----------------+ | IF(4>5,'A','B') | +-----------------+ | B | +-----------------+ 1 row in set (0.00 sec) */
-- Because the first item is empty,所有返回 'caterpillar'
SELECT IFNULL(NULL,'caterpillar');
/* 输出： mysql> SELECT IFNULL(NULL,'caterpillar'); +----------------------------+ | IFNULL(NULL,'caterpillar') | +----------------------------+ | caterpillar | +----------------------------+ 1 row in set (0.00 sec) */
-- 表名设为 list ,如果 50>55 表数据为 'A',否则如果 40>45 表数据为 'B',Otherwise the table data is 'C'
SELECT CASE WHEN 50>55 THEN 'A' WHEN 40>45 THEN 'B' ELSE 'C' END 'list';
/* 输出： mysql> SELECT CASE WHEN 50>55 THEN 'A' WHEN 40>45 THEN 'B' ELSE 'C' END 'list'; +------+ | list | +------+ | C | +------+ 1 row in set (0.00 sec) */
-- 表名设为 list ,如果有跟 'caterpillar' The same condition is the first table data 'caterpillar' The value to follow with the condition
SELECT CASE 'caterpillar' WHEN 'hello' THEN 'A' WHEN 'caterpillar' THEN 'B' END 'list';
/* 输出： mysql> SELECT CASE 'caterpillar' WHEN 'hello' THEN 'A' WHEN 'caterpillar' THEN 'B' END 'list'; +------+ | list | +------+ | B | +------+ 1 row in set (0.00 sec) */

六、约束 6.1 概述

概念：Constraints are rules that act on tables,用于限制存储在表中的数据,约束是作用于表中字段上的,Constraints can be added when creating and modifying tables;

常见的约束

目的：Ensure the correctness of the data in the database、有效性和完整性;

6.2 约束演示

1. 基础约束

示例：

/* 要求： 1. 建立一个表 tb_personnel; 2. There is data in the table(id, name, age, srarus, opender); 3. id Requires primary key and auto-increment; 4. name 要求不为空且不能重复; 5. age 要求大于 0 小于 120; 6. srarus Requires defaults to '1'; */
CREATE TABLE tb_personnel(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
name VARCHAR(20) NOT NULL UNIQUE COMMENT '姓名',
age INT CHECK ( 120 > age && age > 0 ) COMMENT '年龄',
srarus CHAR(1) DEFAULT 1 COMMENT '状态',
opender CHAR(1) COMMENT '性别'
) COMMENT '人员表';

1. 外键约束

语法：

-- 建表时添加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
-- 建表后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

示例：

-- 建立外键表
CREATE TABLE tb_srarus(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
srarus varchar(10) NOT NULL UNIQUE COMMENT '状态'
);
-- 建表时添加外键
CREATE TABLE tb_personnel(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
name VARCHAR(20) NOT NULL UNIQUE COMMENT '姓名',
age INT CHECK ( 120 > age && age > 0 ) COMMENT '年龄',
srarus INT DEFAULT 1 COMMENT '状态',
opender CHAR(1) COMMENT '性别',
CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id)
) COMMENT '人员表';
-- 建表后添加外键
ALTER TABLE tb_personnel ADD CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id);
-- 删除外键
ALTER TABLE tb_personnel DROP FOREIGN KEY additional;

1. 外键的删除和更新

删除更新行为

注：RESTRICT与 NO ACTION 是默认行为

语法：

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

示例：

-- Specify constraints as SET NULL
ALTER TABLE tb_personnel ADD CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id) ON UPDATE CASCADE ON DELETE SET NULL ;
-- Specify constraints as CASCADE
ALTER TABLE tb_personnel ADD CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id) ON UPDATE CASCADE ON DELETE CASCADE ;