Mysql进阶指南：基础与数据库设计实战

原创已于 2024-09-21 15:50:53 修改 · 304 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2020-06-24 23:06:53 首次发布

MySQL 专栏收录该内容

10 篇文章

订阅专栏

本文深入探讨SQL的注释技巧、数据完整性原则，重点讲解主表与从表的概念及外键的应用，包括严格限制、置空操作和级联操作等三种外键处理方式，并介绍了解决主键冲突的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

在数据库管理与开发的广阔领域中，SQL（Structured Query Language）作为与数据库交互的标准语言，其重要性不言而喻。本指南不仅深入解析SQL的基础语法，如注释、数据完整性、表关系与外键操作等，还细致探讨了数据库设计的核心原则与步骤，旨在帮助读者从理论到实践全面掌握SQL与数据库设计的精髓。从基础的SQL注释开始，我们逐步深入到数据完整性、主从表关系、外键操作及其实体间复杂关系的理解，再到数据库设计的规范化与实战例题，每一章节都精心编排，力求让读者在理解理论知识的同时，能够迅速应用于实际开发中。此外，本指南还涵盖了查询语句的高级技巧，如模糊查询、联合查询等，助您轻松应对各种复杂的数据库查询需求。无论您是SQL初学者，还是希望提升数据库设计与查询技能的开发者，本指南都将是您不可或缺的参考资源。

1、SQL注释

单行注释
-- 注释说明或 # 注释说明
多行注释
/* */

在这里插入图片描述

2、数据完整性

实体完整性：
- 主键约束
- 唯一约束
- 标识列
域完整性：
- 数据类型约束
- 非空约束
- 默认值约束
引用完整性
- 主外键约束
自定义完整性
- 存储过程
- 触发器

3、主表和从表

主表中没有的，从表不允许插入。
从表中有的，主表中不允许删除。
删除主表中，先删从表。

4、外键

外键只有在nnodb引擎才能支持外键。外键是从表中的公共字段。

创建主表：
创建从表：

主表中没有的，从表不允许插入：

先插入主表的数据，再插入从表数据：

从表中有的，主表中不允许删除（要先删从表再删主表）：

通过修改表的时候添加外键：
alter table 从表名 add foreign key(公共字段) references 主表名(公共字段)。

删除外键，通过外键的名字删除外键：

4.1 三种外键操作

4.1.1 严格限制

主表中没有的，从表不允许插入。
从表中有的，主表中不允许删除。
删除主表中，先删从表。

4.1.2 置空操作（set null）

如果主表记录删除，或关联字段更新，则从表外键字段被设置为null。

4.1.3 级联操作（cascade）

如果主表记录删除，则从表记录也被删除，主表更新，从表外键字段也更新。
语法： foreign key(外键字段) references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
一般说删除时置空，更新时级联。

4.1.4 例题与小结

例题

现在来试试主表删除和更新，从表是否也会删除和更新：
更新：

删除：
小结
置空级联操作中外键不能是从表的主键。

4.2 解决插入数据时主键冲突

创建表：

create table ct(
	id char(4) primary key,
	name varchar(20)
	) engine=innodb;

在这里插入图片描述

插入数据：

insert into ct values ('ct01', 'jack');

在这里插入图片描述
当再次插入数据：

如果插入的主键重复就会报错。

解决方法一：如果插入的主键重复就执行替换。
语法： replace into 表名 values ()

使用此语法如果数据不存在，可以直接插入：
解决方法二：
语法：on duplicate key update
当插入的值与主键或唯一键有冲突执行update的操作：

推荐使用方法二。

5、实体之间的关系

5.1 一对多（1：N）

在这里插入图片描述

5.2 多对一（N：1）

在这里插入图片描述

5.3 一对一（1：1）

在这里插入图片描述

5.4 多对多（N：N）

在这里插入图片描述

5.5 小结

实现一对一：主键和主键建关系；
实现一对多：主键和非主键建关系；
实现多对多：引入第三张关系表；

6、数据库设计

6.1 数据库设计步骤

收集信息：与该系统有关人员进行交流、充分理解数据库需要完成的任务
标识对象（实体 - Entity）：标识数据库要管理的关键对象或实体
标识每个实体的属性（Attribute）
标识对象之间的关系（Relationship）
将模型转换成数据库
规范化

6.2 数据规范化

第一范式：确保每列原则性
第一范式的目标是确保每列的原子性，一个字段表示一个含义
第二范式：非键字段必须依赖于键字段
第二范式在满足第一范式的前提下，要求每个表只描述一件事情
第三范式：消除传递依赖
第三范式在满足第二范式的前提下，除了主键以外的其他列消除传递依赖
反三范式
范式越高，数据冗余越少，表越来越多，但是效率又是就越底下为了提高运行效率，可以适当让数据冗余。

6.3 例题

需求：
假设某建筑公司要设计一个数据库，公司的业务规则说明如下：

公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等。
公司有多名职工，每一名职工有：职工号、姓名、性别、职务。
公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定

标识实体：

工程；
职工；
工时；
小时工资率；

共有四个表：
在这里插入图片描述

7、查询语句

语法： select [选项] 列名 [from 表名] [where 条件] [group by 分组] [order by 排序] [having 条件] [limit 限制]
测试代码：

create table stutable (
  stuNo char(6) primary key,
  stuName varchar(10) not null,
  stuSex char(2) not null,
  stuAge tinyint not null,
  stuSeat tinyint not null,
  stuAddress varchar(10) not null,
  ch tinyint,
  math tinyint
) charset=gbk;

insert into stutable values ('01','康康', '男', 20,1, '厦门', 90, null);
insert into stutable values ('02','西西', '女', 20,2, '上海', 88, 99);
insert into stutable values ('03','张三', '男', 20,3, '福州', 88, 99);
insert into stutable values ('04','李四', '男', 21,4, '龙岩', 90, 90);
insert into stutable values ('05','王五', '男', 19,5, '北京', 91, 90.5);
insert into stutable values ('06','婷婷', '女', 19,6, '河南', 91.5, 90.3);
insert into stutable values ('07','芳芳', '女', 19,7, '厦门', 99.5, 99.5);
insert into stutable values ('08','赵六', '男', 21,8, '福州', 93.5, 92.5);

在这里插入图片描述

7.1 字段表达式

可以直接输出内容：
在这里插入图片描述

可以输出mysql自带的一些函数，例如随机数rand()：

可以通过as给字段去别名：

7.2 from子句

from：来自，from后面跟的是数据源。数据源可以有多个，返回笛卡尔积。
插入测试表：
在这里插入图片描述
测试多个数据源：
多个数据源返回笛卡尔积：

7.3 dual表

dual表是一个伪表，在有些特定情况下，没有具体的表的参与，但是为了包装select语句的完整又必须要一个表名，这时候就使用伪表。
在这里插入图片描述

7.4 where子句

where后面跟的是条件，在数据源中进行筛选。返回条件为真记录。where后面跟着是条件语句，如果为真则返回，例如where 1将会把所有记录返回，where 0 则一条都不会返回。

MySQL支持的运算符	含义

| 大于
< | 小于
= | 大于等于
<= | 小于等于
= | 等于
!= | 不等于
and | 与
or | 或
not | 非
in 、not in | （不在）在
between and 、 not between and | （不）在什么的什么之间
is null 、 is not null | （不）是为空
例题：

查找语文成绩高于90的学生：
查找语文和数学都高于90的：
查询语文或数学低于90分的同学：
查找厦门的学生（sql语句不区分大小写）：

通过in：
查找不是厦门的学生（sql语句不区分大小写）：
查找年龄在19 - 20之间的学生：

利用between and：
查找年龄不在19 - 20之间的学生：
查找缺考的学生：

7.5 聚合函数和分组查询（group by）

将查询的结果分组，分组查询目的在于统计数据。

7.5.1 聚合函数

sum() 求和
avg() 求平均数
max() 求最大值
min() 求最小值
count() 求记录数

例题：
1、求语文总分：
在这里插入图片描述
2、求数学最大值：

3、语文平均分：

7.5.2 分组查询

语法：select 分组字段,取值函数 as 别名 from 表名 group by 分组字段;
1、查询男生和女生的各自语文平均分：
在这里插入图片描述
2、查询男生和女生各自多少人：

3、查询每个地区有多少人：

4、每个地区的数学平均分：

5、如果分组字段中放普通字段：

可以发现只会返回第一项查询到的数据。
总结：