MySQL数据库（二）

数据约束

在 MySQL 中，为了减少输入错误和保证数据的完整性，可以对字段设置约束。所谓约束就是一种命名规则和机制，通过对数据的增、删、改操作进行一些限制，以保证数据库中数据的完整性。常见的表约束有五种，分别为主键约束、非空约束、默认约束、唯一约束和外键约束。
MySQL 提供了两种定义约束的方式：列约束和表约束。列约束定义在一个列上，只能对该列起约束作用，表约束一般定义在一个表的多个列上，要求被约束的列满足一定的关系。
接下来将对这几种常用的数据约束进行讲解。其中，由于外键约束的使用比较复杂，涉及到多表操作，将在后面的项目中讲解。

非空约束NOT NULL

非空约束就是指被约束的当前字段的值不能为空值 NULL。在 MySQL 中，非空约束是通过 NOT NULL 定义的，其基本语法格式如下：

字段名 数据类型 NOT NULL
在 MySQL 中，所有数据类型的值都可以是 NULL，包括 INT、FLOAT 等数据类型。需要注意的是，空字符串和 0 皆不属于空值 NULL。

示例：创建数据表 user，指定表中的 name 字段不能为空：

CREATE TABLE `user`( 
  `id` INT NOT NULL,
  `name` VARCHAR(20) NOT NULL
)DEFAULT CHARSET=utf8;
从上述示例中可以看出，同一个数据表中可以定义多个非空字段。

唯一约束

唯一约束用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。唯一约束是通过 UNIQUE 定义的，语法格式如下：

# 列级约束
字段名 数据类型 UNIQUE;
# 表级约束
UNIQUE(字段名1, 字段名2, ……);
从上述语法可知，唯一约束既支持列约束也支持表约束。其中，在创建唯一约束时，虽然不允许出现重复的值，但是可以出现多个空值 NULL。且同一个表中可以有多个唯一约束。

示例：创建数据表 user，要求用户名 name 字段不能为空，且不能重复，具体 SQL 语句如下：

CREATE TABLE `user`( 
  `id` INT,
  `name` VARCHAR(20) NOT NULL UNIQUE
)DEFAULT CHARSET=utf8;

主键约束

在创建数据表时，通常利用 PRIMARY KEY 定义主键约束，用于唯一标识表中的记录，类似指纹、身份证用于标识人的身份一样。
主键约束相当于唯一约束和非空约束的组合，要求被约束字段不允许重复，也不允许出现空值；每个表最多只允许含有一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。

默认约束

默认约束用于指定数据表中字段的默认值，即当在表中插入一条新记录时，如果没有给这个字段赋值，会自动使用默认值。默认值是通过 DEFAULT 关键字定义的。

字段名 数据类型 DEFAULT 默认值;

示例：创建数据表 user，指定 area 字段默认值为空字符串。

CREATE TABLE `user`( 
  `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL UNIQUE,
  `area` VARCHAR(100) DEFAULT ''
)DEFAULT CHARSET=utf8;

插入记录

在对数据库进行管理时，若想要操作数据，需要先保证数据表中存在数据。MySQL 使用 INSERT 语句向数据表中添加数据，其基本语法格式如下：

INSERT INTO 表名 (字段名1, 字段名2, ……) VALUES (值1, 值2, ……);

为所有字段插入
在MySQL中，为所有字段插入记录时，有两种写法，一是在插入时带上字段列表，另一种是省略字段列表，具体SQL语句如下。
① 带字段列表

INSERT INTO `cms_category`(`id`, `pid`, `name`, `sort`)
VALUES (1, 1, '社科', 0);

在上述字段列表中字段名称的书写顺序可以随意更改，只要保证值列表中的数据与其相对应即可。例如，上述SQL语句也可以改成如下形式。

INSERT INTO `cms_category`(`name`, `id`, `pid`, `sort`)
VALUES ('社科', 1, 1, 0);

② 省略字段列表

INSERT INTO `cms_category` VALUES (2, 1, '文艺', 0)

当省略字段列表执行插入操作时，则必须严格按照数据表定义字段时的顺序，在值列表中为字段指定相应的数据。

为指定字段添加记录

在数据表 cms_category 中，id 字段是一个自动增加的整数，因此在执行时可以不对其进行插入操作，具体 SQL 语句如下：

INSERT INTO `cms_category`(`pid`, `name`, `sort`) VALUES (1, '生活', 0);

除了上述的方式外，在 MySQL 中，INSERT 语句还有一种语法格式，可以为表中指定的字段或者全部字段添加数据，其语法格式如下：

INSERT INTO 表名 SET 字段名1=值1 [, 字段名2=值2, ……]

在上述的语法格式中，“字段名1”、“字段名2”表示待添加数据的字段名称，“值1”、“值2”表示添加的数据。若在 SET 关键字后，为表中多个字段指定数据，每对“字段名=值”之间使用逗号（,）分隔。具体示例如下：

INSERT INTO `cms_category` SET `pid`=1, `name`='学术', `sort`=0

修改记录—有条件更新数据

有条件更新记录，就是利用 WHERE 子句来指定更新表中的某一条或者某几条记录。例如，修改表 cms_category 中 id=7 的记录，将其 name 字段的值更改为“生活”，具体 SQL 语句如下：

UPDATE `cms_category` SET `name`='生活' WHERE id=7

无条件更新数据

在执行 UPDATE 语句时，若没有使用 WHERE 子句，则会更新表中所有记录的指定字段。例如，修改表 cms_category 的排序字段 sort，将表中所有记录的字段值都更新为 50，具体 SQL 语句如下：

UPDATE `cms_category` SET `sort`= 50;

删除记录

DELETE FROM 表名 [WHERE 条件表达式]
在上面的语法格式中，“表名”指的是待执行删除操作的表，WHERE 子句为可选参数，用于指定删除的条件，满足条件的记录才会被删除。

删除部分数据

DELETE FROM `cms_category` WHERE `pid`=1;

删除全部数据

DELETE FROM `cms_category`;

字段查询—所有字段

在查询所有字段时，可以使用通配符（*），其语法格式如下

SELECT * FROM 表名;

字段查询—指定字段

SELECT 字段名1, 字段名2, …… FROM 表名;

条件查询—比较运算符

条件查询—DISTINCT关键字

DISTINCT 还可以作用于多个字段，且仅一条记录中多个字段的值与另一条记录中对应字段的值都相同时，才被认为是重复记录。
示例：利用 DISTINCT 作用于数据表 cms_category 中的 pid 和 sort 字段。

SELECT DISTINCT `pid`, `sort` FROM `cms_category`;

条件查询—IN关键字

IN 关键字，用于判断某个字段的值是否在指定集合中。若字段的值在集合中，则满足条件，该字段所在的记录将被查询出来。否则，不满足条件则不会被查询出来。其基本语法格式如下所示：

SELECT  *|{字段名1, 字段名2, 字段名3,……} 
FROM 表名
WHERE [NOT] IN(元素1, 元素2, ……)

示例：查询数据表 cms_category 中 pid 值为 0 和 3 的记录。

SELECT * FROM `cms_category` WHERE `pid` IN (0, 3);

条件查询—BETWEENAND关键字

BETWEEN AND用于判断某个字段的值是否在指定的范围之内，如果字段的值在指定范围内，则满足条件，该字段所在的记录将被查询出来，反之则不会被查询出来。其语法格式如下所示：

SELECT *|{字段名1, 字段名2, ……}
FROM 表名
WHERE 字段名 [NOT] BETWEEN 值1 AND 值2

条件查询—BETWEENAND关键字

示例：查询数据表 cms_category 中 pid 值在 0 到 1 之间的记录。

SELECT * FROM `cms_category` WHERE `pid` BETWEEN 0 AND 1;

条件查询——带LIKE的字符匹配查询

在进行数据搜索时，更多的是对字符串进行模糊查询。例如：查询cms_category 表中 name 字段值中含有“书”的记录。为了完成这种功能，MySQL 中提供了 LIKE 关键字，可以判断两个字符串是否相匹配。

SELECT *|{字段名1, 字段名2, ……}
FROM 表名
WHERE 字段名 [NOT] LIKE '匹配字符串';

条件查询——带AND的多条件查询

有时根据项目开发需要，为了使查询结果更加精确，需同时满足多个查询条件。MySQL 中提供的 AND 关键字可以用于连接两个或者多个查询条件，只有满足所有条件的记录才会被返回。其语法格式如下。

SELECT *|{字段名1, 字段名2, ……}
FROM 表名
WHERE 条件表达式1 AND 条件表达式2 […… AND 条件表达式n];

示例：在 cms_category 数据表中查询 pid 在 1 和 3 之中，name 字段值中以“P”开始的记录。

SELECT * FROM `cms_category` WHERE `pid` IN (1, 3) AND `name` LIKE 'P%';

条件查询——带OR的多条件查询

OR 关键字也可以连接多个查询条件，但与 AND 不同的不同之处在于，使用 OR 关键字时，只要记录满足任意一个条件就会被查询出来。
其语法格式如下：

SELECT *|{字段名1, 字段名2, ……}
FROM 表名
WHERE 条件表达式1 OR 条件表达式2 […… OR 条件表达式n];

示例：在 cms_category 数据表中，查询 pid 字段等于 3，或 name 字段值以 P 开始的记录。

SELECT *`在这里插入代码片` FROM `cms_category` WHERE `pid`=3 OR `name` LIKE 'P%';

排序与限量——ORDER BY排序查询

从表中查询出来的数据可能是无序的，或者其排列顺序不是用户期望的。为了使查询结果满足用户的要求，可以使用 ORDER BY 对查询结果进行排序，其语法格式如下所示：
其中，参数 ASC 表示按照升序进行排序，DESC 表示按照降序进行排序。默认情况下，按照 ASC 方式进行排序。

SELECT 字段名1, 字段名2, ……
FROM 表名
ORDER BY 字段名1 [ASC | DESC] [, 字段名2 [ASC | DESC]……];

示例：对数据表 cms_category 进行 sort 字段降序、name 字段升序排序。

SELECT * FROM `cms_category` ORDER BY `sort` DESC, 
`name` ASC;

排序与限量——LIMIT限量

对于一次性查询出的大量记录，不仅不便于阅读查看，还会浪费系统效率。为此，MySQL 中提供了一个关键字 LIMIT，可以指定查询结果从哪一条记录开始，以及每次查询出的记录数量。其语法格式如下：

SELECT 字段名1, 字段名2, ……
FROM 表名
LIMIT [OFFSET, ] 记录数;

在上面的语法格式中，LIMIT 后面可以跟 2 个参数，第一个参数为可选值，默认值为 0，用于表示偏移量，如果偏移量为 0 则从查询结果的第一条记录开始，偏移量为 1 则从查询结果的第二条记录开始，以此类推；第二个参数“记录数”表示返回查询记录的条数。
示例：从数据表 cms_category 中第 2 条记录开始，查询出 4 条记录。

SELECT * FROM `cms_category` LIMIT 1, 4;
需要注意的是，查询结果中记录的数量可以少于或等于设定的记录数

聚合函数与分组——聚合函数

示例：演示聚合函数的使用。

# 统计表中 pid = 0 的记录数
SELECT COUNT(*) FROM `cms_category` WHERE `pid`=0;
# 计算表中sort字段值的和
SELECT SUM(`sort`) FROM `cms_category`;
# 计算表中id大于1且小于5的记录中sort字段的平均值
SELECT AVG(`sort`) FROM `cms_category` WHERE `id`>1 AND `id`<5

聚合函数与分组——GROUP BY 分组

在对表中数据进行统计时，也可能需要按照一定的类别进行统计。例如，统计 cms_category 表中具有相同 pid 的分类各有多少个。在 MySQL 中，可以使用 GROUP BY 按某个字段或者多个字段中的值进行分组，字段中值相同的为一组，其语法格式如下：

SELECT 字段名1, 字段名2, ……
FROM 表名
GROUP BY 字段名1 [, 字段名2, …… [HAVING 条件表达式]];

在上面的语法格式中，指定的字段名1、字段名2等是对查询结果分组的依据。HAVING 关键字指定条件表达式对分组后的内容进行过滤。
值得一提的是，HAVING 与 WHERE 虽然作用相同，但是它们还是有一定的区别，HAVING 关键字后可以跟聚合函数，而 WHERE 则不可以。通常情况下，HAVING 关键字与 GROUP BY 一起使用，对分组后的结果进行过滤
示例：对数据表 cms_category 以 pid 进行分组查询，并完成对每个组数量的统计。

SELECT `pid`, COUNT(*) FROM `cms_category` GROUP BY `pid`;

需要注意的是，使用 GROUP BY 直接进行分组查询后，显示的结果是分组后的第一条记录的值。因此，搭配 COUNT() 或 GROUP_CONCAT() 等聚合函数一起使用，才能获得每个组的查询结果。

多表查询

在实际开发中，除了单表查询，还会经常遇到多表查询的需求。例如，文章和栏目是两张表，如果要求查询结果中既有文章标题又有栏目名称，就需要对两张表进行查询。接下来，本任务将对多表查询的相关内容进行详细讲解。
合并查询就是将多个 SELECT 语句的查询结果合并到一起

SELECT  ……
UNION [ALL | DISTINCT]
SELECT  ……
[UNION [ALL | DISTINCT]
SELECT  ……
……];
在上述语法中，ALL 用于将查询结果简单的合并到一起；DISTINCT 是默认值，可以省略，表示将所有的查询结果合并到一起，并去除相同的记录。

示例：对文章表和栏目表进行交叉连接查询。

SELECT a.`id`, a.`name`, a.`cid`, c.`cname` FROM `article` a
CROSS JOIN `category` c WHERE a.`cid`=c.`cid`;

值得一提的是，在 SQL 中还有一种多表查询的语法，与交叉连接等价。

SELECT a.`id`, a.`name`, a.`cid`, c.`cname`
FROM `article` a, `category` c WHERE a.`cid`=c.`cid`;

连接查询—内连接

内连接（INNER JOIN）又称简单连接或自然连接，是一种常见的连接查询。在连接时，使用 ON 关键字指定连接条件，并返回满足条件的记录数据。基本语法格式如下：

SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段 WHERE 条件;

在上述语法中，ON 与 WHERE 虽然都是用于连接查询条件，但是它们的使用是有区别的。ON 用于过滤两表连接的条件，WHERE 用于过滤中间表的记录数据。其中，由于内连接查询是默认的连接方式，因此可以省略INNER 关键字。

下面对文章表和栏目表进行内连接查询，具体 SQL 语句如下：
SELECT a.`id`, a.`name`, a.`cid`, c.`cname` FROM `article` a
JOIN `category` c ON a.`cid`=c.`cid`;
值得一提的是，在内连接查询中还有一种特殊的查询：自连接查询。它是指相互连接的表在物理上为同一个表，但逻辑上分为两个表。

示例：要查询 id 为 2 的文章的所属分类下还有哪些文章，就可以使用自连接查询。

SELECT a1.* FROM `article` a1 JOIN `article` a2
ON a1.`cid`=`a2`.`cid` WHERE a2.`id`=2;

关键字“LEFT|RIGHT [OUTER] JOIN”左边的表被称为左表，关键字右边的表被称为右表。其中，OUTER在查询时可以省略。

为了更好的学习外连接查询，接下来就针对左连接、右连接查询分别进行讲解。
① 左连接查询（LEFT JOIN 或 LEFT OUTER JOIN）
左连接查询用于返回左表中的所有记录，以及右表中符合连接条件的记录。当左表的某行记录在右表中没有匹配的记录时，右表中相关的记录将设为空值。
① 左连接查询（LEFT JOIN 或 LEFT OUTER JOIN）
下面为文章表添加一条记录，具体 SQL 语句如下：

INSERT INTO `article` (`cid`, `name`) VALUES (999, '测试文章');

② 右连接查询（RIGHT JOIN 或 RIGHT OUTER JOIN）
右连接与左连接相反，它返回右表中的所有记录，以及左表中符合连接条件的记录。如果左表中没有与右表匹配的记录，则将左表相关的记录设为空值。
② 右连接查询（RIGHT JOIN 或 RIGHT OUTER JOIN）
下面利用右连接对栏目表和文章表进行查询，具体 SQL 语句如下：

SELECT a.`id`, a.`name`, a.`cid`, c.`cname` FROM `category` c
RIGHT JOIN `article` a ON c.`cid` = a.`cid`;

子查询

子查询就是包含在一条 SQL 语句中的 SELECT 语句。当遇到多层子查询时，首先会从最里层的子查询开始执行，然后将返回的结果作为外层查询的过滤条件。需要注意的是，子查询必须书写在括号内。
使用子查询时，外层语句的 WHERE 后面除了比较运算符外，还可以使用 IN、EXISTS、ANY、ALL 等操作符。接下来将对子查询的使用进行讲解。

单行子查询

使用 IN 关键字时，子查询将返回一个结果集，作为外层 SQL 语句的判断条件。
示例：根据栏目表和文章表，查询栏目名称为“科技”、生活的文章。

SELECT * FROM `article` WHERE `cid` IN
(SELECT `cid` FROM `category` WHERE `cname` IN('科技', '生活'));

EXISTS 关键字后面连接的子查询语句不返回查询记录，而是返回一个真假值。当子查询语句查询到满足条件的记录时，就返回 TRUE，执行外层SQL 语句；否则返回 FALSE，不执行外层的 SQL 语句。

使用 ANY 关键字时，只要其后的子查询满足其中任意一个判断条件，就返回结果作为外层 SQL 语句的执行条件。
示例：到文章表中查询与栏目表相对应的记录。

SELECT * FROM `article` WHERE `cid` = ANY 
(SELECT `cid` FROM `category`);
上述 SQL 语句执行后，查询出了在栏目表中有相应记录的文章信息。如果文章表中有一些文章的 cid 在栏目表中不存在相应记录，则不会被查询出来。

子查询

ALL 关键字在使用时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
示例：在文章表中查询 cid 在栏目表中没有相应记录的文章。

SELECT * FROM `article`
WHERE `cid` <> ALL (SELECT `cid` FROM `category`);