MySQL数据库
1、数据库相关概念
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
1.1 关系型数据库
关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。
优点：
1.都是使用表结构，格式一致，易于维护。
2.使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
3.数据存储在磁盘中，安全。
1.2 常见关系型数据库
•Oracle：收费的大型数据库，Oracle 公司的产品
•MySQL： 开源免费的中小型数据库。后来 Sun公司收购了 MySQL，而 Sun 公司又被 Oracle 收购
•SQL Server：MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用
•PostgreSQL：开源免费中小型的数据库
•DB2：IBM 公司的大型收费数据库产品
•SQLite：嵌入式的微型数据库。如：作为 Android 内置数据库
•MariaDB：开源免费中小型的数据库
简言之：关系型数据库的数据模型就是通过《数据库管理系统》管理多个数据库，每个《数据库》可以包含多个数据表，每个《数据表》又可以包含多条数据。
1.3 实体关系模型
实体关系模型对现实世界进行抽象，得出实体类型和实体间的关系，用来描述现实世界中数据的组成结构。
实体关系图（Entity Relationship Diagram）是指提供了表示实体、属性和关系的图形化表示方式，用来描述现实世界的概念模型，也简称为E-R图。
实体关系模型核心的元素
实体（Entity）：是具有相同特征和属性的现实世界事务的抽象，在E-R图中用矩形表示，矩形框内注明实体的名称
属性（Attribute）：是指实体具有的特性，一个实体可以包含若干个实体。在E-R图中属性用椭圆形表示，并使用线条将其与相应的实体连接起来。比如员工具有工号、入职日期等属性
关系（Relationship）：是指实体之间的相互联系的方式，一般具有一对一关系（1:1）、一对多关系（1:N）、多对多关系（M:N）
例子：
E-R图与数据表
| E-R图 | 数据表 |
|---|---|
| 实体 | 表 |
| 属性 | 字段 |
| 实体的一个记录 | 表的一行记录 |
比如：
学生实体 对应 学生表。
一个实体有多个属性， 对应 学生表 有多个字段（二维表的多列）。
一个实体的一条记录， 对应 学生表的一行记录（二维表的一行）。
1.4 数据库三范式
为了规范化关系型数据模型，要求数据库系统在设计时必须遵循一定的规则，这种规则就称为关系型数据库系统范式。
使用范式的主要目的是为了降低数据的冗余，设计结构合理的数据库。
三范式
第一范式（1NF）：字段必须具有单一属性特性，不可再拆分，比如姓名字段，必须具有单一的属性，不可以在一个字段中包含员工中文名或英文名，必须考虑拆分为两个字段。
第二范式（2NF）：表要具有唯一性的主键列。也就是说表中的每一行要具有一个唯一性的标识列，比如通常使用GUID或自动增长的数字编号来唯一地标识一行，或者是使用学号来唯一标识一个学生。
第三范式（3NF）：表中的字段不能包含在其他表中已出现的非主键字段，比如学生表里面不能出现班级名字字段,可以使用班级ID。
SQL
SQL (Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统（RDBMS）。 SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。
SQL 在1986年成为 ANSI（American National Standards Institute 美国国家标准化组织）的一项标准，在 1987 年成为国际标准化组织（ISO）标准。
•英文：Structured Query Language，简称 SQL
•结构化查询语言，一门操作关系型数据库的编程语言
•定义操作所有关系型数据库的统一标准
•对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”
SQL通用语法
-
SQL 语句可以单行或多行书写，以分号结尾。
-
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
- 注释
• 单行注释:
-- 注释内容 或 #注释内容(MySQL 特有) • 多行注释:
/*
注释内容
*/SQL的分类
DDL(data definition language) 数据定义语言
就是我们在创建表时用到的一些SQL语句。例如：CREATE、ALTER、DROP等。DDL主要是用在定义表 或者 改变表的物理结构、数据类型、表之间的链接和物理约束等初始化操作上。
DML(Data Manipulation Language) 数据操纵语言
主要用来对数据库的数据进行一些操作。以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入(意指新增或创建)、更新(修改)与删除(销毁)。
DQL(Data Query Language) 数据查询语言
用于查询数据库中的数据。即SELECT语句。查询语句是SQL语句中最复杂、功能最丰富的语句。
DCL(Data Control Language) 数据控制语言
是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。
TCL(Trasactional Control Languag) 事务控制语句：
主要由commit 、rollback 和 savepoint 三个关键字完成。
1. 数据库操作
-- 显示所有的数据库
SHOW DATABASES;
-- 查询数据库的创建语句
SHOW CREATE DATABASE 数据库名称;
-- 创建数据库
CREATE DATABASE 数据库名称;
-- 创建数据库（如果不存在则创建）
CREATE DATABASE IF NOT EXISTS 数据库名称;
-- 创建数据库（指定字符集）
CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;
-- 修改数据库的字符集
ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;
-- 删除数据库
DROP DATABASE 数据库名称;
-- 删除数据库(如果存在则删除)
DROP DATABASE IF EXISTS 数据库名称;
-- 使用数据库
USE 数据库名称;
-- 查看当前使用的数据库
SELECT DATABASE();
注意：数据库名称、表名称以及字段名称可以加反单引号，是为了避免你的表名和字段名 和数据库SQL中的关键字冲突。但也可以不加。
2. 数据表操作
2.1 MySQL的数据类型
整型
整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT
浮点型
MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。
MySQL允许使用 非标准语法 （其他数据库未必支持，因此如果涉及到数据迁移，则最好不要这么用）： FLOAT(M,D) 或 DOUBLE(M,D) 。这里，M称为 精度 ，D称为 标度 。(M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。
例如，定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。
定点数类型
MySQL中的定点数类型只有 DECIMAL 一种类型。
使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。
-
DECIMAL(M,D) 的最大取值范围与 DOUBLE 类型一样，但是有效的数据范围是由M和D决定的。DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。也就是说，在一些对精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数值范围可以更大一些。
-
定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。
- 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。
日期与时间类型
- YEAR 类型通常用来表示年
- DATE 类型通常用来表示年、月、日
- TIME 类型通常用来表示时、分、秒
- DATETIME 类型通常用来表示年、月、日、时、分、秒
- TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒
注意：TIMESTAMP（时间戳）如果将来不给这个字段赋值或者赋值为null，那么将采用系统当前时间自动填充。
字符串类型
CHAR和VARCHAR类型都可以存储比较短的字符串。
char类型
- CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
- 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
VARCHAR类型
- VARCHAR(M) 定义时， 必须指定 长度M，否则报错。
- MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。
- 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。
文本类型
在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、 MEDIUMTEXT 和 LONGTEXT 类型。
在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和VARCHAR类型相同。
由于实际存储的长度不确定， MySQL 不允许 TEXT 类型的字段做主键。
BLOB类型
BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。
MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如 图片 、 音频 和 视频 等。需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到 服务器的磁盘上 ，并将图片、音频和视频的访问路径存储到MySQL中。
2.2 MySQL数据表操作语句
-- 查询所有的数据表
SHOW TABLES;
-- 查询表结构
DESC 数据表名称;
-- 查询数据表字符集
SHOW TABLE STATUS FROM 数据库名称 LIKE '数据表名称';
-- 创建数据表
create table 表名 (
字段名1 数据类型 [约束][缺省值][描述],
...
字段名N 数据类型 [约束][缺省值][描述],
...
);
-- 举例：
CREATE TABLE product(
id INT comment '商品id', -- 商品id
NAME VARCHAR(20) comment '商品名称', -- 商品名称
price DOUBLE comment '商品价格', -- 商品价格
stock INT comment '商品库存', -- 商品库存
insert_time DATE comment '上架日期' -- 上架日期
);
-- 复制数据表
CREATE TABLE 新表名 LIKE 原表名;
-- ALTER TABLE 语句用于在已有的表中添加、删除或修改列。
-- 修改表名
ALTER TABLE 原数据表名称 RENAME TO 新数据表名称;
-- 设置表的字符集
ALTER TABLE 数据表名称 CHRARCTER SET 字符集名称;
-- 表中添加列
ALTER TABLE 数据表名称 ADD 列名称 数据类型;
-- 删除表中的列(注：某些数据库不支持此删除列的方式，例如db2)
ALTER TABLE 数据表名称 DROP COLUMN 列名称;
-- 修改表中的列名
ALTER TABLE 数据表名称 CHANGE 原列名称 新的列名称 数据类型;
-- 改变表中列的数据类型
-- SQL Server / MS Access：
ALTER TABLE 数据表名称 ALTER COLUMN 列名称 数据类型;
-- My SQL / Oracle：
ALTER TABLE 数据表名称 MODIFY COLUMN 列名称 数据类型;
-- DROP TABLE 语句用于删除表
-- 删除数据表
DROP TABLE 数据表名称;
-- 删除数据表（如果存在就删除）
DROP TABLE IF EXISTS 数据表名称;
-- 如果我们仅仅需要删除表内的数据，但并不删除表本身，使用 TRUNCATE TABLE 语句：
TRUNCATE TABLE 数据表名称;
3. 数据表中的数据操作DML（增删改）
-- INSERT INTO 语句用于向表中插入新记录
-- 给指定的列添加数据
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
-- 给全部列添加数据(此种方式，数据值要与表中的列一一对应)
INSERT INTO table_name
VALUES (value1,value2,value3,...);
-- 批量给指定的列添加数据
INSERT INTO table_name (column1,column2,column3,...)
VALUES
(value1,value2,value3,...),
(value1,value2,value3,...),
(value1,value2,value3,...),
...;
-- 批量给全部的列添加数据
INSERT INTO table_name
VALUES
(value1,value2,value3,...),
(value1,value2,value3,...),
(value1,value2,value3,...),
...;
-- UPDATE 语句用于更新表中已存在的记录
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
-- 注意：WHERE子句规定哪条记录或者哪些记录需要更新。如果省略了 WHERE 子句，所有的记录都将被更新！
-- DELETE 语句用于删除表中的行
DELETE FROM table_name
WHERE some_column=some_value;
-- 注意：WHERE子句规定哪条记录或者哪些记录需要删除。如果省略了 WHERE 子句，所有的记录都将被删除！4. 数据表中的数据操作DQL（查询）（重点）
4.1 查询全部
-- SELECT 语句用于从数据库中选取数据。
-- 结果被存储在一个结果表中，称为结果集。
-- 查询全部
SELECT * FROM table_name;
-- 查询指定的列
SELECT column_name,column_name
FROM table_name;
-- 在表中，一个列可能会包含多个重复值，DISTINCT 关键词用于返回唯一不同的值
SELECT DISTINCT column_name,column_name
FROM table_name;
-- 计算列的值（四则运算）
SELECT 列名1 运算符(+-*/) 列名2
FROM 数据表名称;
-- 通过使用 SQL，可以为表名称或列名称指定别名。
-- 基本上，创建别名是为了让列名称的可读性更强。
-- 列的 SQL 别名语法
SELECT 列名称 AS 别名
FROM 数据表名称;
-- 表的 SQL 别名语法
SELECT 列名称
FROM 数据表名称 AS 别名;
-- 在 MySQL 中，我们可以使用 IFNULL() 函数来处理空值，例如：
-- 在商品表中查询库存列，如果为null值，赋予0，否则取原值
SELECT IFNULL(stock,0) FROM product;
-- 在Oracle中没有 ISNULL() 函数，不过，可以使用 NVL() 函数达到相同的结果
SELECT NVL(stock,0) FROM product;
-- 如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。
-- 无法使用比较运算符来测试 NULL 值，比如 =、< 或 <>。
-- 必须使用 IS NULL 和 IS NOT NULL 操作符，例如：
SELECT * FROM product WHERE stock IS NULL;
SELECT * FROM product WHERE stock IS NOT NULL;
4.2 条件查询
-- WHERE 子句用于提取那些满足指定条件的记录
SELECT 列名
FROM 表名
WHERE 条件;| 运算符 | 描述 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| BETWEEN | 在某个范围内 |
| LIKE | 搜索某种模式（模糊查询） |
| IN | 指定针对某个列的多个可能值（多选一） |
| AND 或 && | 并且 |
| OR 或 || | 或者 |
| NOT 或 ! | 非，不是 |
举例：
-- 准备数据
-- 创建数据表
CREATE TABLE product(
id INT, -- 商品编号
NAME VARCHAR(20), -- 商品名称
price DOUBLE, -- 商品价格
brand VARCHAR(10), -- 商品品牌
stock INT, -- 商品库存
insert_time DATE -- 添加时间
);
-- 添加数据
INSERT INTO product VALUES
(1,'华为手机',3999,'华为',23,'2088-03-10'),
(2,'小米手机',2999,'小米',30,'2088-05-15'),
(3,'苹果手机',5999,'苹果',18,'2088-08-20'),
(4,'华为电脑',6999,'华为',14,'2088-06-16'),
(5,'小米电脑',4999,'小米',26,'2088-07-08'),
(6,'苹果电脑',8999,'苹果',15,'2088-10-25'),
(7,'联想电脑',7999,'联想',NULL,'2088-11-11');
-- 查询
-- 查询库存大于20的商品信息
SELECT * FROM product WHERE stock > 20;
-- 查询品牌为华为的商品信息
SELECT * FROM product WHERE brand = '华为';
-- 查询金额在4000到6000之间的商品信息
SELECT * FROM product WHERE price >= 4000 AND price <= 6000;
-- 或者
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;
-- 查询库存为 14、15、18的商品信息
SELECT * FROM product WHERE stock = 14 OR stock = 15 OR stock = 18;
-- 或者
SELECT * FROM product WHERE stock IN (14,15,18);
-- 查询库存为空的商品信息
SELECT * FROM product WHERE stock IS NULL;
-- 查询库存不为空的商品信息
SELECT * FROM product WHERE stock IS NOT NULL;
-- 模糊查询
-- 查询名称以小米开头的商品信息
SELECT * FROM product WHERE name LIKE '小米%';
-- 查询名称中包含电脑的商品信息
SELECT * FROM product WHERE name LIKE '%电脑%';
/*
关于查询的条件，SQL提供了四种匹配模式：
1） % ：表示任意0个或多个字符，可匹配任意类型和长度的字符。
例如：SELECT * FROM user WHERE u_name LIKE '%三%';
会将u_name为"张三"、"胡汉三"、"三哥"、"三脚猫"、"唐三藏"等等包含"三"字符的记录全部查询出来。
2） _ ： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
例如：SELECT * FROM user WHERE u_name LIKE '_三';
只能查询出u_name为两个字符，且第二个字符是"三"的记录，例如"张三"、"唐三"等。
例如：SELECT * FROM user WHERE u_name LIKE '_三_'
只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的记录。
3） [ ] ：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
例如：SELECT * FROM user WHERE u_name LIKE '[张李王]三';
将找出u_name为“张三”、“李三”或者“王三”（而不是“张李王三”）的记录。
如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”
SELECT * FROM user WHERE u_name LIKE '老[1-9]';
将找出u_name为“老1”、“老2”、……、“老9”的记录。
4） [^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
例如： SELECT * FROM user WHERE u_name LIKE '[^张李王]三';
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；
例如：SELECT * FROM user WHERE u_name LIKE '老[^1-4]';
将排除“老1”到“老4”，寻找“老5”、“老6”、……
*/4.3 聚合函数查询
SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。
- AVG() - 返回平均值
- COUNT() - 返回行数
- FIRST() - 返回第一个记录的值 -- MS Access 支持
- LAST() - 返回最后一个记录的值 -- MS Access 支持
- MAX() - 返回最大值
- MIN() - 返回最小值
- SUM() - 返回总和
举例：
-- COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：
SELECT COUNT(列名) FROM 表名;
-- 查询product表中的总记录条数
SELECT COUNT(*) FROM product;
-- 查询品牌为小米的商品总条数
SELECT COUNT(*) FROM product WHERE brand = "小米";
-- 查询品牌不重复的商品总条数（或者说不同的品牌数）
SELECT COUNT(DISTINCT brand) FROM product;
-- 查询最高的价格
SELECT MAX(price) FROM product;
-- 查询最低的库存
SELECT MIN(stock) FROM product;
-- 查询总库存数量
SELECT SUM(stock) FROM product;
-- 查询品牌为苹果的商品总库存
SELECT SUM(stock) FROM product WHERE brand = '苹果';
-- 查询品牌为小米的商品的平均价格
SELECT AVG(price) FROM product WHERE brand = '小米';4.4 排序查询
ORDER BY 关键字用于对结果集进行排序。
SQL ORDER BY 语法：
SELECT column_name,column_name
FROM table_name
where 子句
ORDER BY column_name,column_name ASC|DESC;
-- 其中ASC表示升序，DESC表示降序。默认是ASC举例：
-- 按照库存升序查询
SELECT * FROM product ORDER BY stock ASC;
-- 或者省略ASC
SELECT * FROM product ORDER BY stock;
-- 查询所有名称中包含手机的商品信息，按照价格降序排序
SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;
-- 查询所有产品，按照价格升序排序，如果价格相同，按照库存降序排序
SELECT * FROM product ORDER BY price ASC, stock DESC;注意：
1） ORDER BY 多列的时候，先按照第一列排序，再按照第二列排序；
2） ORDER BY 排列时，不写明ASC DESC的时候，默认是ASC；
例如：
order by A,B 这个时候都是默认按升序排列
order by A desc,B 这个时候 A 降序，B 升序排列
order by A, B desc 这个时候 A 升序，B 降序排列4.5 分组查询
HAVING 子句可以让我们筛选分组后的各组数据。
语法
SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式];
-- 如果有分组前的条件过滤，写WHERE子句
-- 如果有分组后的条件过滤，写HAVING子句
-- 如果还有排序（对结果集排序），写在分组后面举例：
-- 按照品牌分组，获取每组商品的总金额
SELECT brand, SUM(price) FROM product GROUP BY brand;
-- 注意：输出的字段，必须要在Group by子句或包含在聚合函数中
-- 例如：SELECT NAME, brand, SUM(price) FROM product GROUP BY brand; 将会报出错误，因为name字段没有包含在Group by子句或聚合函数中
-- 对金额大于4000元的商品，按照品牌分组,获取每组商品的总金额
SELECT brand , SUM(price) FROM product WHERE price > 4000 GROUP BY brand;
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的
SELECT brand, SUM(price) AS totalPrice FROM product WHERE price > 4000 GROUP BY brand HAVING totalPrice > 7000;
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列
SELECT brand, SUM(price) AS totalPrice
FROM product
WHERE price > 4000
GROUP BY brand
HAVING totalPrice > 7000
ORDER BY totalPrice DESC;
4.6 分页查询
语法：
SELECT 列名 FROM 表名
[WHERE 条件]
[GROUP BY 分组列名]
[HAVING 分组后条件过滤]
[ORDER BY 排序列名 排序方式]
LIMIT 偏移量, 每页显示的条数;LIMIT 子句可以被用于指定 SELECT 语句返回的记录数：
其中第一个参数表示：第一个返回记录行的偏移量，注意从0开始
偏移量的计算公式为：（当前页码 - 1） * 每页的条数
第二个参数表示：返回最大的记录行数目（即每页显示的数目，如果不足取实际数据条数）
即：
SELECT 列名 from 表名 limit (pageNo - 1) * pageSize, pageSize;
-- 其中pageNo指的是页码，pageSize指的是每页显示的条数举例：
-- 每页显示3条数据
-- 第1页 偏移量=(1-1) * 3
SELECT * FROM product LIMIT 0,3;
-- 第2页 偏移量=(2-1) * 3
SELECT * FROM product LIMIT 3,3;
-- 第3页 偏移量=(3-1) * 3
SELECT * FROM product LIMIT 6,3;注意：这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。
5. SQL 约束（Constraints）
SQL 约束用于规定表中的数据规则。实际上就是表中数据的限制条件。是为了保证数据的完整性而实现的一套机制。
MySQL的约束种类如下：
- 非空约束：NOT NULL
NOT NULL约束强制该字段不接受 NULL 值。
- 唯一约束：UNIQUE
UNIQUE 约束唯一标识数据库表中的每条记录。 即该字段的值不能重复，但null除外。
- 主键约束：PRIMARY KEY
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
- 主键、自动增长约束：PRIMARY KEY AUTO_INCREMENT
在每次插入新记录时，自动地创建主键字段的值。开始值是 1，每条新记录递增 1。
- 外键约束：FOREIGN KEY
FOREIGN KEY约束保证一个表中的数据匹配另一个表中的值的参照完整性。
- 默认约束：DEFAULT
DEFAULT约束用于保证该字段有默认值。
5.1 主键约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。
注：PRIMARY KEY从效果上相当于NOT NULL + UNIQUE，但本质不同，主键约束还会默认添加索引（index），索引可以快速定位特定数据，提高查询效率。并且每个表只能有一个 PRIMARY KEY 约束。
示例：
-- 创建学生表，将id设置为主键
CREATE TABLE student (
s_id INT PRIMARY KEY,
s_name VARCHAR(30),
s_age INT
);
-- 显示student表的结构
DESC student;
-- 插入数据
INSERT INTO student(s_id, s_name, s_age)
VALUES
(1, "Tom", 23),
(2, "Jerry", 24);
-- 查询所有数据
SELECT * FROM student;
-- 尝试在主键列插入重复值：报错
INSERT INTO student(s_id, s_name, s_age)
VALUES
(2, "Jhon", 25);
-- 如需撤销 PRIMARY KEY 约束
-- ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE student DROP PRIMARY KEY;
-- 如果在建表后需要添加主键
-- ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);
ALTER TABLE student ADD PRIMARY KEY (s_id);5.2 主键自增约束
在每次插入新记录时，自动地创建主键字段的值。
默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。
示例：
-- 创建教师表
CREATE TABLE teacher (
t_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增
t_name VARCHAR(30)
);
-- 显示teacher表的结构
DESC teacher;
-- 插入数据: 主键列为空，插入数据时自动生成一个自增的数字
INSERT INTO teacher
VALUES
(NULL, "张三"),
(NULL, "李四");
SELECT * FROM teacher;
-- 如果需要改变自增的起始值
ALTER TABLE teacher AUTO_INCREMENT = 100;
-- 再次插入数据查看
INSERT INTO teacher
VALUES
(NULL, "王五"),
(NULL, "赵六");
-- 如需删除自增约束
-- ALTER TABLE 表名 MODIFY 列名 数据类型;
ALTER TABLE teacher MODIFY t_id INT;
-- 如果在建表后添加自增约束
-- ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
ALTER TABLE teacher MODIFY t_id INT AUTO_INCREMENT;
-- 再次插入数据测试
INSERT INTO teacher
VALUES
(NULL, "田七");5.3 唯一约束
指示列值必须唯一，不能重复。
示例：
-- 创建用户表：规定用户名唯一
CREATE TABLE tb_user (
u_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增
u_username VARCHAR(100) UNIQUE -- 用户名唯一
);
DESC tb_user;
-- 插入数据
INSERT INTO tb_user
VALUES
(NULL, "zhangsan"),
(NULL, "lisi");
SELECT * FROM tb_user;
-- 尝试插入重复值：报错
INSERT INTO tb_user
VALUES
(NULL, "zhangsan");
-- 如需删除唯一约束
-- ALTER TABLE 表名 DROP INDEX 列名
ALTER TABLE tb_user DROP INDEX u_username;
-- 如果在创建表后，添加唯一约束
-- ALTER TABLE 表名 ADD UNIQUE (列名)
ALTER TABLE tb_user ADD UNIQUE (u_username);
-- 注意：表中该列如果已经存在重复值，不能添加唯一约束。先删除重复值5.4 非空约束
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
示例：
-- 创建员工表employee
CREATE TABLE employee (
e_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增
e_name VARCHAR(100) NOT NULL -- 姓名列不能为空
);
DESC employee;
-- 插入数据
INSERT INTO employee
VALUES
(NULL, "张三"),
(NULL, "李四");
SELECT * FROM employee;
-- 尝试插入一条空数据：报错
INSERT INTO employee
VALUES
(NULL, NULL);
-- 如需删除非空约束
ALTER TABLE employee MODIFY e_name VARCHAR(100) NULL;
-- 如需在创建表后，添加非空约束
ALTER TABLE employee MODIFY e_name VARCHAR(100) NOT NULL;5.5 默认约束
DEFAULT - 规定没有给列赋值时的默认值。
-- 创建学生信息表
CREATE TABLE student_info (
s_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增
s_name VARCHAR(100) NOT NULL, -- 姓名列不能为空
s_modify_time datetime default now() -- 当添加数据时，如果没有给该列赋值，默认去当前时间
);
insert into student_info(s_id, s_name) values (null, 'tom');
select * from student_info;5.6 外键约束
一个表中的 FOREIGN KEY （外键）指向另一个表中的 UNIQUE KEY(唯一约束的键)。
我们通过一个实例来解释外键：
订单表（orderlist）：
| id | number | uid |
|---|---|---|
| 1 | hn001 | 1 |
| 2 | hn002 | 1 |
| 3 | hn003 | 2 |
| 4 | hn004 | 2 |
| 5 | hn005 | 3 |
| 6 | hn006 | 3 |
用户表（user）：
| id | name |
|---|---|
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
订单表中的uid列指向user表中的id列，也就是说添加订单时，订单所属的用户必须是真实存在的；同理，如果要删除用户表中的数据，也必须保证订单表中已经没有该用户的订单。
外键约束能防止非法数据插入外键列，用于预防破坏表之间连接的行为。
示例：
-- 外键约束语法：CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
-- 创建用户表（主表）
CREATE TABLE USER (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
SELECT * FROM USER;
-- 创建订单表
CREATE TABLE orderlist (
id INT PRIMARY KEY AUTO_INCREMENT, -- id
number VARCHAR(20) NOT NULL, -- 订单编号
uid INT, -- 外键列
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) -- 外键列uid指向user表中的主键id列
);
-- 订单表添加数据：外键列的值必须是主表user中存在的主键值
INSERT INTO orderlist
VALUES
(NULL, "hn001", 1),
(NULL, "hn002", 1),
(NULL, "hn003", 2);
-- 如果不存在，添加失败
INSERT INTO orderlist VALUES (NULL,'hn005',3);
-- 删除李四用户，删除失败
DELETE FROM USER WHERE NAME='李四';
DESC orderlist;
-- 如需删除外键
-- 标准语法：ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
-- 在创建表后，添加外键约束
-- 标准语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);
-- 当添加数据时，如果想取消外键约束的检查
SET FOREIGN_KEY_CHECKS = 0;
-- 恢复外键约束检查
SET FOREIGN_KEY_CHECKS = 1;
5.7 外键级联操作
级联更新：
当我们想把user用户表中的某个用户id修改，我们希望订单表中该用户所属的订单用户编号也随之修改。
级联删除：
当我们想把user用户表中的某个用户删掉，我们希望该用户所有的订单也随之被删除。
-- 添加外键约束，同时添加级联更新 标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON UPDATE CASCADE;
-- 添加外键约束，同时添加级联删除 标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON DELETE CASCADE;
-- 添加外键约束，同时添加级联更新和级联删除 标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON UPDATE CASCADE ON DELETE CASCADE;示例：
-- 继续使用上一节中的数据
-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
-- 添加外键约束，同时添加级联更新和级联删除
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;
-- 将王五用户的id修改为5 订单表中的uid也随之被修改
UPDATE USER SET id=5 WHERE id=3;
-- 将王五用户删除 订单表中该用户所有订单也随之删除
DELETE FROM USER WHERE id=5;
