mysql级联删除_Mysql笔记系列，DQL基础复习，Mysql的约束与范式

阅读量：788 次

发布时间：2023-02-13

本文共 4032 字，大约阅读时间需要 13 分钟。

DQL: 查询语句

排序查询

在数据库查询中，排序查询是为了对结果进行排列，按照特定的顺序输出数据。这对于分析数据趋势、排序数据等场景非常有用。

排序语法

排序查询的语法在大多数数据库中类似，常用语句为 ORDER BY。具体语法如下：

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

排序方式

排序方式主要有两种：

ASC：默认排序方式，表示按升序排列。

DESC：表示按降序排列。

注意事项

如果有多个排序条件，数据库在执行时会遵循以下优先级规则：

先比较第一个排序字段。

当第一个排序字段的值相同时，比较第二个排序字段。

以此类推，直到排序结果确定。

聚合函数

聚合函数是用来对数据库中多个记录进行统计和计算的操作，常见的聚合函数包括 COUNT、MAX、MIN、SUM 和 AVG 等。这些函数会将一列数据作为一个整体进行计算。

常用聚合函数

COUNT: 计算记录的个数

通常选择主键字段作为计算对象。

COUNT(*) 会统计所有记录，包括 NULL 值。

MAX: 计算最大值

用于找出某一字段的最大值。

MIN: 计算最小值

用于找出某一字段的最小值。

SUM: 计算和

用于对数值字段求和。

AVG: 计算平均值

用于对数值字段计算平均值。

注意事项

聚合函数的默认行为是排除 NULL 值。

如果需要包含 NULL 值，需要使用 IFNULL 函数处理。

分组查询

分组查询用于将数据按照一定条件进行分组，通常用于统计和汇总操作。

分组语法

分组查询的语法如下：

SELECT [分组字段] , [聚合函数] , ...FROM [表名]GROUP BY [分组字段];

注意事项

分组字段：可以是单个字段或多个字段的组合。

聚合函数：必须在 GROUP BY 之后使用。

WHERE 与 HAVING 的区别：

WHERE：在分组之前执行过滤条件，符合条件的记录才会参与分组。

HAVING：在分组之后执行过滤条件，仅对分组后的结果进行过滤。

示例

按照性别分组，分别查询男、女同学的平均分：

SELECT sex, AVG(math) FROM student GROUP BY sex;

按照性别分组，分别查询男、女同学的平均分和人数：

SELECT sex, AVG(math), COUNT(id) FROM student GROUP BY sex;

按照性别分组，分别查询男、女同学的平均分和人数（要求分数大于70分）：

SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

按照性别分组，分别查询男、女同学的平均分和人数（要求分数大于70分，且人数大于2）：

SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

分页查询

分页查询用于对结果集进行分割，限制返回的记录数量，以方便展示。

分页语法

分页的语法在不同数据库中有所不同，但大多数数据库支持 LIMIT 关键字。语法如下：

SELECT * FROM [表名] LIMIT [起始索引] , [每页显示的记录数];

起始索引：计算公式为 (当前页码 - 1) * 每页显示的记录数。

每页显示的记录数：决定每页显示多少条记录。

示例

每页显示3条记录：

SELECT * FROM student LIMIT 0, 3; -- 第1页

第2页：

SELECT * FROM student LIMIT 3, 3; -- 第2页

第3页：

SELECT * FROM student LIMIT 6, 3; -- 第3页

注意事项

LIMIT 是MySQL 的特有语法，不同数据库可能有所不同。

分页查询会影响性能，建议在实际应用中谨慎使用。

数据库约束

数据库约束用于对表中的数据进行限定，确保数据的正确性和完整性。

常见约束类型

主键约束：
- 每个表只能有一个主键字段。
- 主键字段必须唯一且非空。
- 主键字段通常用于表的记录唯一标识。

非空约束：
- 确保字段值不为 NULL。
- 创建表时可以手动定义，表创建完成后也可以通过 ALTER TABLE 修改。

唯一约束：
- 确保字段值唯一性。
- 可以与 INDEX 结合使用，提高查询效率。
- 需要注意的是，唯一约束允许 NULL 值存在。

外键约束：
- 用于建立表与表之间的关系。
- 确保外键字段的值在对应的主表中存在。
- 外键约束可以单独定义，也可以通过 INDEX 和 CONSTRAINT 组合使用。

外键约束的实现

在创建表时添加外键：

CREATE TABLE stu (    id INT PRIMARY KEY AUTO_INCREMENT,    name VARCHAR(20) NOT NULL UNIQUE,    FOREIGN KEY (phone_number) REFERENCES phone_table(phone_number));

在表已存在的情况下添加外键：

ALTER TABLE stu ADD CONSTRAINT phone_fk FOREIGN KEY (phone_number) REFERENCES phone_table(phone_number);

级联操作：

ALTER TABLE stu ADD CONSTRAINT phone_fk FOREIGN KEY (phone_number) REFERENCES phone_table(phone_number) ON UPDATE CASCADE ON DELETE CASCADE;

删除约束

删除主键约束：

ALTER TABLE stu DROP PRIMARY KEY;

删除唯一约束：

ALTER TABLE stu DROP INDEX phone_number;

删除外键约束：

ALTER TABLE stu DROP FOREIGN KEY phone_fk;

数据库设计

数据库设计是数据库开发中的关键环节，涉及数据结构的规划和关系的建立。

数据库关系

一对一关系：
- 例如：学生和身份证。
- 一个学生对应一个身份证，一个身份证只能对应一个学生。

一对多关系：
- 例如：部门和员工。
- 一个部门可以有多个员工，一个员工只能属于一个部门。

多对多关系：
- 例如：学生和课程。
- 一个学生可以选择多个课程，一个课程也可以被多个学生选择。

数据库表设计

以下是根据旅游线路系统设计的数据库表结构：

分类表 tab_category：

cid：分类主键，自增。

cname：分类名称，非空且唯一。

CREATE TABLE tab_category (    cid INT PRIMARY KEY AUTO_INCREMENT,    cname VARCHAR(100) NOT NULL UNIQUE);

线路表 tab_route：

rid：线路主键，自增。

rname：线路名称，非空且唯一。

price：价格。

rdate：上架时间。

cid：所属分类。

CREATE TABLE tab_route (    rid INT PRIMARY KEY AUTO_INCREMENT,    rname VARCHAR(100) NOT NULL UNIQUE,    price DOUBLE,    rdate DATE,    cid INT,    FOREIGN KEY (cid) REFERENCES tab_category(cid));

用户表 tab_user：

uid：用户主键，自增。

username：用户名，非空且唯一。

password：密码，非空。

name：姓名。

birthday：生日。

sex：性别，默认为男。

telephone：手机号码。

email：电子邮箱。

CREATE TABLE tab_user (    uid INT PRIMARY KEY AUTO_INCREMENT,    username VARCHAR(100) UNIQUE NOT NULL,    password VARCHAR(30) NOT NULL,    name VARCHAR(100),    birthday DATE,    sex CHAR(1) DEFAULT '男',    telephone VARCHAR(11),    email VARCHAR(100));

收藏表 tab_favorite：

rid：线路ID，外键。

uid：用户ID，外键。

date：收藏时间。

CREATE TABLE tab_favorite (    rid INT,    date DATETIME,    uid INT,    PRIMARY KEY (rid, uid),    FOREIGN KEY (rid) REFERENCES tab_route(rid),    FOREIGN KEY (uid) REFERENCES tab_user(uid));