数据库的快速复习（二）

7.MySQL约束、函数和运算符

7.1MySQL约束概述

约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。

1）主键约束

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。

主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

2）外键约束

外键约束经常和主键约束一起使用，用来确保数据的一致性。

3）唯一约束

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。

4）检查约束

5）非空约束

6）默认值约束

7.2MySQL主键（PRIMARY KEY）

使用主键应注意以下几点：

• 每个表只能定义一个主键。
• 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。

1）设置单字段主键

在 CREATE TABLE 语句中，通过 PRIMARY KEY 关键字来指定主键。

在定义字段的同时指定主键，语法格式如下：

<字段名> <数据类型> PRIMARY KEY [默认值]

2）在创建表时设置联合主键

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

比如，设置学生选课数据表时，使用学生编号做主键还是用课程编号做主键呢？如果用学生编号做主键，那么一个学生就只能选择一门课程。如果用课程编号做主键，那么一门课程只能有一个学生来选。显然，这两种情况都是不符合实际情况的。

 主键由多个字段联合组成，语法格式如下：

PRIMARY KEY [字段1，字段2，…,字段n]

在修改表时添加主键约束

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。但是需要注意的是，设置成主键约束的字段中不允许有空值。

在修改数据表时添加主键约束的语法格式如下：

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

删除主键约束

当一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

删除主键约束的语法格式如下所示：

ALTER TABLE <数据表名> DROP PRIMARY KEY;

 7.3MySQL主键自增长

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。语法格式如下：

字段名 数据类型 AUTO_INCREMENT

• 默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
• AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
• AUTO_INCREMENT 约束的字段只能

定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加。

7.4MySQL外键约束

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

7.5唯一约束unique key

MySQL 唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。

唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值。

 7.6MySQL检查约束（CHECK）

MySQL 检查约束（CHECK）是用来检查数据表中字段值有效性的一种手段，可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

 7.7MySQL默认值default

在创建表时设置默认值约束

创建表时可以使用 DEFAULT 关键字设置默认值约束，具体的语法格式如下：

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

在修改表时添加默认值约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

删除默认值约束

当一个表中的列不需要设置默认值时，就需要从表中将其删除。

修改表时删除默认值约束的语法格式如下：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

 7.8MySQL非空约束(not null)

在创建表时设置非空约束

创建表时可以使用 NOT NULL 关键字设置非空约束，具体的语法格式如下：

<字段名> <数据类型> NOT NULL;

 修改数据表 tb_dept4，指定部门位置不能为空，SQL 语句和运行结果如下所示。

mysql> ALTER TABLE tb_dept4
    -> CHANGE COLUMN location
    -> location VARCHAR(50) NOT NULL;

删除非空约束

修改表时删除非空约束的语法规则如下：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;

7.9查看表中的约束

在 MySQL 中可以使用 SHOW CREATE TABLE 语句来查看表中的约束。

查看数据表中的约束语法格式如下：

SHOW CREATE TABLE <数据表名>;

7.11MySQL算术运算符

8.MySQL操作表中数据

8.1MySQL SELECT：数据表查询语句

其中，各条子句的含义如下:
{*<字段列名>}包含星号通配符的字段列表，表示所要查询字段的名称。

<表1>，<表2>...，表1和表2表示查询数据的来源，可以是单个或多个。

WHERE<表达式>是可选项，如果选择该项，将限定查询数据必须满足该查询条件。
GROUP BY<字段>，该子句告诉MySQL 如何显示查询出来的数据，并按照指定的字段分组。
[ORDER BY<字段>]，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有升序(ASC)和降序(DESC)，默认情况下是升序。

[LIMIT[<offset>, ]<row count>]，该子句告诉MySQL每次显示查询出来的数据条数。
查询表中字段

SELECT　＊　FROM　表名

查询表中所有字段的数据（使用 * 的优势是，当不知道所需列的名称时，可以通过 * 获取他们）

SELECT id,name,dept_id,age,sex FROM tb_students_info;

如果需要改变字段的顺序，只需要调整SELECT的字段列表顺序即可。

查询表中指定的字段 

SELECT <列名> FROM <表名>;

8.2MySQL使用DISTINCT过滤重复数据

 在 MySQL 中使用 SELECT 语句执行简单的数据查询时，返回的是所有匹配的记录。如果表中的某些字段没有唯一性约束，那么这些字段就可能存在重复值。为了实现查询不重复的数据，MySQL 提供了 DISTINCT 关键字。

 SELECT DISTINCT <字段名> FROM <表名>;

使用 DISTINCT 关键字时需要注意以下几点：

• DISTINCT 关键字只能在 SELECT 语句中使用。
• 在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最前面。
• 如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下才会被去重。

 

 因为 DISTINCT 只能返回它的目标字段，而无法返回其它字段，所以在实际情况中，我们经常使用 DISTINCT 关键字来返回不重复字段的条数。

 8.3MySQL设置(表或者字段）别名

 为了查询方便，MySQL 提供了 AS 关键字来为表和字段指定别名。当表名很长或者执行一些特殊查询的时候，为了方便操作，可以为表指定一个别名，用这个别名代替表原来的名称。

<表名> [AS] <别名>

表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。

 在使用 SELECT 语句查询数据时，MySQL 会显示每个 SELECT 后面指定输出的字段。有时为了显示结果更加直观，我们可以为字段指定一个别名。

<字段名> [AS] <别名>

 表别名只在执行查询时使用，并不在返回结果中显示。而字段定义别名之后，会返回给客户端显示，显示的字段为字段的别名。

8.4MySQL限制查询结果的条数（LIMIT）

当数据表中有上万条数据时，一次性查询出表中的全部数据会降低数据返回的速度，同时给数据库服务器造成很大的压力。这时就可以用 LIMIT 关键字来限制查询结果返回的条数。

指定初始位置

LIMIT 关键字可以指定查询结果从哪条记录开始显示，显示多少条记录。

LIMIT 指定初始位置的基本语法格式如下：

LIMIT 初始位置，记录数

其中，“初始位置”表示从哪条记录开始显示；“记录数”表示显示记录的条数。第一条记录的位置是 0，第二条记录的位置是 1。后面的记录依次类推。

注意：LIMIT 后的两个参数必须都是正整数。

8.5MySQL ORDER BY：对查询结果排序

 通过条件查询语句可以查询到符合用户需求的数据，但是查询到的数据一般都是按照数据最初被添加到表中的顺序来显示。为了使查询结果的顺序满足用户的要求，MySQL 提供了 ORDER BY 关键字来对查询结果进行排序。

 单字段排序

 多字段排序

 对多个字段进行排序时，排序的第一个字段必须有相同的值，才会对第二个字段进行排序。如果第一个字段数据中所有的值都是唯一的，MySQL 将不再对第二个字段进行排序。

 8.6MySQL WHERE：条件查询数据

 

 

 

 8.7MySQL使用LIKE模糊查询

在 MySQL 中，LIKE 关键字主要用于搜索匹配字段中的指定内容，LIKE 关键字支持百分号“%”和下划线“_”通配符

带有“%”通配符的查询

“%”是 MySQL 中最常用的通配符，它能代表任何长度的字符串，字符串的长度可以为 0。

 

 

带有“_”通配符的查询

“_”只能代表单个字符，字符的长度不能为 0。例如，a_b可以代表 acb、adb、aub 等字符串。

 

 

LIKE 不区分大小写

默认情况下，LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写，可以加入 BINARY 关键字。

如果查询内容中包含通配符，可以使用“\”转义符。

8.8MySQL BETWEEN AND：范围查询

MySQL 提供了 BETWEEN AND 关键字，用来判断字段的数值是否在指定范围内。

BETWEEN AND 需要两个参数，即范围的起始值和终止值。如果字段值在指定的范围内，则这些记录被返回。如果不在指定范围内，则不会被返回。

 8.9MySQL IS NULL：空值查询

8.10MySQL分组查询

 在 MySQL 中，GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。

 

 

GROUP BY 与聚合函数

在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。
聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。其中，COUNT() 用来统计记录的条数；SUM() 用来计算字段值的总和；AVG() 用来计算字段值的平均值；MAX() 用来查询字段的最大值；MIN() 用来查询字段的最小值。

 

 

8.11MySQL HAVING：过滤分组

HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。

但是 WHERE 和 HAVING 关键字也存在以下几点差异：

• 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
• WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
• WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
• WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
• WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

8.12MySQL CROSS JOIN：交叉连接

前面所讲的查询语句都是针对一个表的，但是在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。

 

 

 8.13MySQL INNER JOIN：内连接

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]

语法说明如下。

• 字段名：需要查询的字段名称。
• <表1><表2>：需要内连接的表名。
• INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
• ON 子句：用来设置内连接的连接条件。

 8.15子查询

 子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。

WHERE <表达式> <操作符> (子查询)

 在完成较复杂的数据查询时，经常会使用到子查询，编写子查询语句时，要注意如下事项。

1) 子查询语句可以嵌套在 SQL 语句中任何表达式出现的位置

在 SELECT 语句中，子查询可以被嵌套在 SELECT 语句的列、表和查询条件中，即 SELECT 子句，FROM 子句、WHERE 子句、GROUP BY 子句和 HAVING 子句。

前面已经介绍了 WHERE 子句中嵌套子查询的使用方法，下面是子查询在 SELECT 子句和 FROM 子句中的使用语法。

嵌套在 SELECT 语句的 SELECT 子句中的子查询语法格式如下。

SELECT (子查询) FROM 表名;

提示：子查询结果为单行单列，但不必指定列别名。

嵌套在 SELECT 语句的 FROM 子句中的子查询语法格式如下。

SELECT * FROM (子查询) AS 表的别名;

注意：必须为表指定别名。一般返回多行多列数据记录，可以当作一张临时表。