MySQL笔记（一）：设计范式、基础概念、数据库定义语言DDL

MySQL设计范式、基础概念、数据库定义语言DDL

MySQL是一种数据库管理系统 (DBMS)，是基于客户机-服务器的数据库；

分为两个不同的部分，

服务器软件（MySQL DBMS）是负责所有数据访问和处理的一个文件，这个软件运行在称为数据库服务器的计算机上，与数据文件打交道；

客户机则是与用户打交道的软件，对于用户提出的申请提交给服务器软件，经过处理后，再返回客户机；客户机可以是MySQL提供的工具，也可以是脚本语言、web应用开发语言、程序设计语言

数据模型

属性的特点

• 属性不可再分
• 一个实体的属性可以有很多个
• 用于唯一区分不同实体的属性，称为key
• 属性取值可以有一定的约束

实体或是属性之间可以具有一定的联系

MySQL是一种关系型数据库，可以通过画ER图，来构建关系

数据库的创建

主键

主键必须非NULL，且不能重复；

可以一起使用多个列作为主键，所有列值的组合必须是唯一的，但是单列值可以不唯一

主键的好习惯

• 不更新主键列中的值
• 不重用主键列的值
• 不在主键列中使用可能会更改的值

创建出来的结果

现在我们填入表

浅蓝色部分表示未提交的

查询

# SELECT * FROM study WHERE account = 20221011

SELECT * FROM study INNER JOIN teach ON study.account = teach.sid WHERE teach.tid = (SELECT tid FROM study WHERE name = 'ALice')

设置父表

通过图表观察关系

数据库设计规范

第一范式（1NF）

第一范式是指数据库的每一列都是不可分割的基本数据项，而下面这样的就存在可分割的情况：

学生（姓名，电话号码）
电话号码实际上包括了家用座机电话和移动电话，因此它可以被拆分为：

学生（姓名，座机号码，手机号码）
满足第一范式是关系型数据库最基本的要求

第二范式（2NF）

第二范式要求表中必须存在主键，且其他的属性必须完全依赖于主键

第三范式（3NF）

在满足第二范式的情况下，所有的属性都不传递依赖于主键，满足第三范式

学生借书情况（借阅编号，学生学号，书籍编号，书籍名称，书籍作者）
实际上书籍编号依赖于借阅编号，而书籍名称和书籍作者依赖于书籍编号，因此存在传递依赖的情况，我们可以将书籍信息进行单独拆分为另一张表：

学生借书情况（借阅编号，学生学号，书籍编号）
书籍（书籍编号，书籍名称，书籍作者）
这样就消除了传递依赖，从而满足第三范式。

BCNF

BCNF作为第三范式的补充，假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：

(仓库ID, 存储物品ID) →(管理员ID, 数量)

(管理员ID, 存储物品ID) → (仓库ID, 数量)

所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：

(仓库ID) → (管理员ID)

(管理员ID) → (仓库ID)

SQL语句

结构化查询语言（Structured Query Language）简称SQL，这是一种特殊的语言，它专门用于数据库的操作。每一种数据库都支持SQL，但是他们之间会存在一些细微的差异，因此不同的数据库都存在自己的“方言”

SQL语句由子句(clause)构成，一个子句通常有一个关键字和所提供的数据构成；有些子句是必须的，有些则是可选的

SQL语句不区分大小写（关键字推荐使用大写），它支持多行，并且需要使用;进行结尾

SQL也支持注释，通过使用--或是#来编写注释内容，也可以使用/*来进行多行注释

CRUD 增删改查

• 数据查询语言（Data Query Language, DQL）基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块。
• 数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是开发以数据为中心的应用程序必定会使用到的指令。
• 数据库定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。
• DCL（Data Control Language）是数据库控制语言。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

SQL数据类型

字符串存储

1. char() 可以存储任意长度的字符串，固定长度，不满则用空格填充
2. varchar() 长度不固定，但是不能超过n

存储数字

1. small、int用于存储小的整数，范围在 (-32768，32767)
2. int用于存储一般的整数，范围在 (-2147483648，2147483647)
3. bigint 用于存储大型整数，范围在 (-9,223,372,036,854,775,808，9,223,372,036,854,775,807)
4. float 用于存储单精度小数
5. double 用于存储双精度的小数

存储时间

1. date存储日期
2. time存储时间
3. year存储年份
4. datetime用于混合存储日期+时间

列级约束条件

主键Primary key、外键foreign key 、唯一 unique、检查 check （MySQL不支持）、默认default 、非空/空值 not null/ null

表级约束条件

主键、外键、唯一、检查

例：设置外键

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

注：实在不会的话，建议在应用内操作，他会给你自动生成代码，自己再打一遍就好了

mysql> CREATE TABLE teach(
    -> tid int NOT NULL,
    -> CONSTRAINT f_tid FOREIGN KEY (tid) REFERENCES teacher(tid));

数据库定义语言 Data Definition Language

数据库操作

创建数据库+设定编码格式

CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; 

此时刷新数据库，会发现构造成功

删除数据库

DROP DATABASE 数据库名

表

创建

create table 表名(列名 数据类型[列级约束条件],
             列名 数据类型[列级约束条件],
             ...
             [,表级约束条件])

此时，就已经创建成功了

修改表

ALTER TABLE 表名[ADD 新列名 数据类型[列级约束条件]]
							 [DROP COLUMN 列名[restrict|cascade]]
							 [ALTER COLUMN 列名 新数据类型]

例

mysql> ALTER TABLE teacher add
    -> sex enum ('male', 'female') NOT NULL DEFAULT 'male';

删除表

DROP TABLE  表名;

其中restrict 和cascade 上面的效果一致