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）是数据库存储的基本对象,是描述事物的符号记录;

1.2 数据库

数据库（ dataBase,DB ）是长期储存在计算机内、有组织的、可共享的大量数据的集合;

数据库的基本特征：

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

数据库管理系统（ DBMS ）是位于用户与操作系统之间的一层数据管理软件,是一个大型复杂的软件系统;

​ DBMS 的用途：

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

数据库系统（ Database System ,DBS ）是计算机系统中引入数据库后的系统构成;

数据库系统的构成：

  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 中的数据类型有很多,主要分为三类:数值类型,字符串类型,日期时间类型;

部分数值类型 > > > > > > > >
类型 说明
TINYINT 超小整数-有效取值范围(-128, 127)
SMALLINT 小整数-有效取值范围(-32768, 32767)
INT 或 INTEGER 超小整数-有效取值范围(-128, 127)
TINYINT 整数-有效取值范围(-2 147 483 648, 2 147 483 647)
BOOL 布尔类型,只有 true 和 false 两个有效值;零值被认为是 false,非零值被认为是 true. (注意,MySQL Not really supported BOOL 类型,BOOL 是TINYINT(1)的别名)
FLOAT(size, d) 单精度浮点数类型,4 个字节大小.size The parameter is used to specify the total number of numbers,d Parameters are used to specify the fractional part（小数点后边）的数字个数.
DOUBLE(size, d) 双精度浮点数类型,size The parameter is used to specify the total number of numbers,d Parameters are used to specify the fractional part（小数点后边）的数字个数.
Partial string type > > > > > > > >
类型 说明
CHAR(size) 用于表示固定长度的字符串,该字符串可以包含数字、字母和特殊字符.size 的大小可以是从 0 到 255 个字符,默认值为 1.
VARCHAR(size) 用于表示可变长度的字符串,该字符串可以包含数字、字母和特殊字符.size 的大小可以是从 0 到 65535 个字符.
TINYTEXT Represents a maximum length of 255（28-1）的字符串文本.
TEXT(size) Represents a maximum length of 65,535（216-1）的字符串文本,也即 64KB.
MEDIUMTEXT Represents a maximum length of 16,777,215（224-1）的字符串文本,也即 16MB.
LONGTEXT Represents a maximum length of 4,294,967,295（232-1）的字符串文本,也即 4GB.
ENUM(val1, val2, val3,...) 字符串枚举类型,最多可以包含 65,535 个枚举值.Inserted data must be in a list,And only one of the values ​​can be hit;如果不在,will insert a null value.
Partial date type > > > > > >
类型 说明
DATE 日期类型,格式为 YYYY-MM-DD,取值范围从 '1000-01-01' 到 '9999-12-31'.
DATETIME(fsp) 日期和时间类型,格式为 YYYY-MM-DD hh:mm:ss,取值范围从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'.
TIMESTAMP(fsp) 时间戳类型,The value it stores is from Unix 纪元（'1970-01-01 00:00:00' UTC）到现在的秒数.TIMESTAMP 的格式为 YYYY-MM-DD hh:mm:ss,取值范围从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC.
TIME(fsp) 时间类型,格式为 hh:mm:ss,取值范围从 '-838:59:59' 到 '838:59:59'.
YEAR Four-digit year format,允许使用从 1901 到 2155 The four-digit year in between.此外,There is also a special value,就是 0000.
四、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：

> > > > > > > > > >
比较运算符 说明
> 大于
≥ 大于等于
< 小于
≤ 小于等于
<>或!= 不等于
BETWEEN...AND... 在某个范围之内（含最小、最大值）
IN(...) 在 IN Select one of the values ​​in the subsequent list
LIKE 占位符 模糊匹配（_匹配单个字符,%匹配任意个字符）
IS NULL 是 NULL
> > > >
逻辑运算符 说明
AND 或 && 并且
OR 或 || 或者
NOT 或 ! 非
  1. 聚合函数
  • 聚合函数：聚合函数又叫组函数,通常是对表中的数据进行统计和计算,一般结合分组(group by)来使用,用于统计和计算分组数据;
SELECT 聚合函数(字段列表) FROM 表名;

常见的聚合函数：

> > > > > >
函数 说明
COUNT() 其作用主要是返回每个组的行数,也会返回有NULL值的列,可用于数字和字符列.
SUM() 主要用于返回表达式中所有的总和,忽略NULL值,仅用于数字列.
AVG() 返回表达式中的最小值,忽略NULL值,可用于数字、字符和日期时间列.
MIN() 返回表达式中的最小值,忽略NULL值,可用于数字、字符和日期时间列.
MAX() 返回表达式中的最大值,忽略NULL值,可用于数字、字符和日期时间列.
  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：

Database Basics --- (continuously updated)

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

Database Basics --- (continuously updated)

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 > > > >
权限 说明
ALL,ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表
五、函数 5.1 字符串函数 常见的字符串函数 > > > > > > > > > > >
函 数 功 能
CONCAT(str1,str2,...,strn) 将str1,str2,...,strnConcatenated as a complete string
INSERT(str,x,y,instr) 将字符串str从第x开始,yA substring of string length is replaced by a stringinstr
LOWER(str) 将字符串str中的所有字母变成小写
UPPER(str) 将字符串str中的所有字母变成大写
LEFT(str,x) 返回字符串最左边的x个字符
RIGHT(str,x) Returns the rightmost of the stringx个字符
LPAD(str,n,pad) 使用字符串pad对字符串str最左边进行填充,直到长度为n个字符长度
RPAD(str,n,pad) 使用字符串pad对字符串str最右边进行填充,直到长度为n个字符长度
LTRIM(str) 去掉str左边的空格
RTRIM(str) 去掉str右边的空格
REPEAT(str,x) 返回字符串str重复x次的结果
REPLACE(str,a,b) 使用字符串b替换字符串str中所有出现的字符串a
STRCMP(str1,str2) 根据 ASCII 比较字符串str1和str2,Greater than the result is1、小于为-1、相等为0
TRIM(str) Remove spaces from the beginning and end of a string
SUBSTRING(str,x,y) 返回字符串str中从x位置起yA string of string lengths

示例：

-- 获取'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 数值函数 常见的数值函数 > > > > > > > > >
函 数 功 能
ABS(x) 返回数值x的绝对值
CEIL(x) 返回大于或等于x的最小整数值
FLOOR(x) 返回小于或等于x的最大整数值
MOD(x,y) 返回x除以y的余数
RAND() 返回0~1内的随机数
RAND(x) 返回0~1内的随机数,并xThe random number has the same value as the tag tag
ROUND(x) 返回x四舍五入后的数值
ROUND(x,y) 返回xYes after roundingy位小数的数值
TRUNCATE(x,y) 返回数值xand truncated toy位小数的数值
LEAST(NUM1,NUM2,...) Returns the smallest value in a data set
GREATEST(NUM1,NUM2,...) Returns the largest value in a data set
SIGN(x) 返回x的正负值,正数返回1,负数返回-1,0返回0
SQRT(x) 计算x的平方根

示例：

-- 返回 -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 日期函数 常见的日期函数 > > > > > > > >
函 数 功 能
NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式的日期部分
EXTRACT() 返回日期/时间按的单独部分
DATE_ADD() 给日期添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF() 返回两个日期之间的天数
DATE_FORMAT() 用不同的格式显示日期/时间
YEAR(data) 获取指定data的年份
MONTH(data) 获取指定data的月份
DAY(data) 获取指定data的日期

示例：

-- 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中的日期格式
格式 描述
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月,数值(00-12)
%p AM 或 PM
%r 时间,12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位
5.4 流程控制函数 Three flow control functions
函 数 功 能
IF(bool,value1,value2) bool为true返回value1否则返回value2
IFNULL(value1,value2) 如果value1不为空返回value1否则返回value2
CASE...WHEN...ELSE...END 多重条件判断

示例：

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

常见的约束
函 数 功 能 关键字
非空约束 限制该字段不能为NULL NOT NULL
唯一约束 Restrict this field to be unique、不可重复的 UNIQUE
主键约束 主键是一个元组的唯一标识,Primary keys are required to be non-null and unique PRIMARY KEY
自增约束 只有整型列才能设置此属性,每个表只能定义一个auto_increment列,And a primary key constraint or candidate key must be defined on that column AUTO_INCREMENT
默认约束 If the key value of this attribute is not specified when adding a tuple, the default value will be used DEFAULT
检查约束(8.0.16版本之后) 保证字段值满足某一个条件 CHECK
外键约束 It is used to establish a connection between the data between the two tables,保证数据的一致性和完整性 FOREIGN KEY

目的：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. 外键的删除和更新
删除更新行为
行 为 说明
NO ACTION When deleting in the parent table/更新对应记录时,First check whether the record has a corresponding foreign key,如果有则不允许删除
RESTRICT When deleting in the parent table/更新对应记录时,First check whether the record has a corresponding foreign key,如果有则不允许删除（与NO ACTION一致）
CASCADE When deleting in the parent table/更新对应记录时,First check whether the record has a corresponding foreign key,If there is, the records in the child table are also deleted/更新
SET NULL When the corresponding record is deleted in the parent table,First check whether the record has a corresponding foreign key,If so, set the foreign key value in the child table to NULL
SET DEFAULT 父表有变更时,子表将外键列设置成一个默认的值（Innodb不支持）

注：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 ;
你可能想看：

网页报错Message: date: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone s

实际上，从 PHP 5.1.0 ，当对使用date()等函数时，如果timezone设置不正确，在每一次调用时间函数时,都会产生E_NOTICE 或者 E_WARNING 信息。而又在php5.1.0...

be used to do, be used to doing和used to do的区别？

“used”单独做谓语动词表示过去经常，为谓语动词——一般过去时态（常态）。他曾在我们学校就读。过去我们总是在晚饭后去散步。我过去通常起到很早。以上例句中的“used”都是独立的谓语动词，所以其后跟随...

实用英语used to do/be used to doing/be used to do有什么区别

He used to be my best friend, but now we are like strangers to each other.他曾经是我最好的朋友，但现在我们彼此就像陌生人一样。...

辨析used to do, be used to do 和be used to doing

二，动词的过去式表示过去，used 为use 的过去式，所以used to do 为“过去常常做某事”，如:He used to get up late. 他以前总是很晚才起床。

用法总结，used to/be used to/get used to别再搞混拉

今天来讲一个比较简单的词汇辨析，《used to家族》。没错，就是大家经常会搞混的used to/get uesd to/be used to的应用，其中的区别辨析让罐头菌今天来说清楚吧，Let‘s ...

used to do,be used to do,be used to doing 辨析刻意练习习题

初高中重点常考知识点这是个非常容易混淆，而且就算记住了，特别容易忘的知识点。— She ______ride a bike to school, but now she ______going the...

如何区分 used to do 和 be used to do 以及 be used to doing 这三者

usedadj. 习惯于；适应；用过的；旧的；二手的v. 使用；利用；运用；消耗；说，写，使用。是 use 的过去分词和过去式。used to do 意思是“过去”经常、常常做某事。这里的 used ...

再谈be used to do, be used to doing 和used to do的区别

We used to go there every year. 我每年都去那儿。I used to smoke, but I gave up a couple of years ago.He is n...

关于淘宝店铺VIP的问题，里面的有普通 高级 vip 至尊VIP 都是什么意思？

"普通" "高级" "vip" "至尊VIP"是4个不同的级别。你可以设置不同的条件和难度，买家只有满足了你设置的条件才能才为相应的会员级别，比如你设置的普通会员是交易大道5笔或者交易金额达到100元...

MySQL update select update

But in MySQL：Solution：For this situation；update student t1 inner join(select t2.grade from student t...

PHP communication management system wamp running custom development mysql database html webpage computer software engineering

Modify and delete picturesMessage board：admin Database configuration file data.php2.The development ...

VCF：(Victory of Continuous Four的缩写)在五子棋中的含义是什么？

为五子棋残局排局解题的一种取胜技巧。利用连续不断冲四或嵌五的绝对先手，直至形成四三后变活四而取得胜利的一种方法。outlook中的联系人，可以复制粘贴到outlook的联系人当中，发信时是联系人的地址...

哈弗coupe是什么车型 哈弗h6 coupe跟哈佛h6有什么区别

文章目录：哈弗coupe是什么意思哈弗h6 coupe跟哈佛h6有什么区别哈弗h6coupe车需要磨合吗一、哈弗coupe是什么意思【太平洋汽车网】哈弗coupe是哈弗H6的掀背风格的城市SUV的意思...

you raise me up歌词 You Raise Me Up是什么意思?

文章目录：you raise me up什么意思You Raise Me Up是什么意思?一、you raise me up什么意思you raise me up你鼓舞了我双语对照词典结果：网络释义百...

[解疑]The committee is expected to vote on Thursday. Ten Republicans serve with

The committee is expected to vote on Thursday. Ten Republicans serve with eight opposition Democrats...

清军入关和张学良东北军入关中的关指的是什么关?

是长城的东端,山海关.是偶的故里--万里长城的第一个关口--名做"天下第一关"的山海关,还有当年的"四野"出“关”抢占东北所说的"关"也是山海关,是连接东北与华北的咽喉要道，它南临渤海，北靠燕山，地势...

快速解决&is marked as crashed and should be repaired&故障

Table '.\Tablename\posts' is marked as crashed and should be repaired. 提示说论坛的帖子表posts被标记有问题，需要修复。我记得...

区分be used to doing和used to do？

例句：I used to go to school .

Used to do与get(be) used to doing的区别

你知道I used to drive on the left 和 I'm used to driving on the left有什么区别吗？看看这些例子，看看used to、get used...

count和countif区别？

一、意思不同count仅仅是计数，统计值为数值型数字的单元格个数。公式为=COUNTIF（统计区域,条件）countif在指定区域中按指定条件对单元格进行计数，公式为=COUNTIFS（统计区域,条件...

标签: 数据数据库字段
分享给朋友：

Copyright © 2024 问答百科网 All Rights Reserved.
蜀ICP备11000655号-9