封面图片来源:沙沙野
01. DDL 数据定义语言
1). 创建表的基本语法
CREATE TABLE 表名(
字段名1 数据类型 约束规则,
字段名2 数据类型,
字段名3 数据类型,
.....
字段名N 数据类型,
PRIMARY KEY(一个 或 多个 字段名)
);
# 注意:最后一个小句子后面不能有英文逗号出现,前面的小句子必须加上英文逗号
2). 字段名
定义字段名,表名、数据库名、规范:在 64 个字符以内,建议简短,如果不够清晰,可以使用前缀
不能是关键字或者保留字
采用变量命名方式 [ 由字母、数字、下划线组成,不能以数字开头 ]
3). 数据类型
数据库里面的数据在保存时,也要通过数据类型来告诉系统,这些数据的用途,所以也会有对应的数据类型:数值类型 [整数和浮点数]、字符串 和 日期
4). 约束规则是否唯一 [数据在同一个表中的同一列中是否可以出现多个]
是否无符号 [约束当前是否可以填写负数,有符号可以填写,无符号不能填写 ]
是否设置为当前表的主键 [主键是一个表记录不同行数据之间的唯一字段,这个字段必须是唯一的]
是否自动增长 [添加数据的时候,如果不填写这个字段,那么这个字段会自动在之前已有的值基础上 +1 填充]
设置默认值 [ 添加/修改数据时,如果值没有填写或者被清空了,采用指定的值作为字段值 ]
是否可以填写空 (NULL,等同于 Python里面的 None) 值
5) 用法示例创建班级表
CREATE TABLE classes(
id INT UNSIGNED auto_increment primary key NOT NULL,
name VARCHAR(10)
);
2. 创建学生表 [如果数据库中已经有了这张表,则改个表名即可]
CREATE TABLE student(
id INT UNSIGNED AUTO_INCREMENT NOT NULL, # 字段名 整型 无符号 自动增长 不能是空,
name CHAR(10), # 字段名 字符串(长度:10)
sex INT DEFAULT 1, # 字段名 整型 默认值为 1
class INT, # 字段名 整型
age INT, # 字段名 整型
description TEXT, # 字段名 文本[可以填写65535个字符]
PRIMARY KEY(id) # 设置主键(id) 每个表必须都有主键
) ENGINE=INNODB CHARSET=utf8; # 表引擎=innodb 编码=utf8;
# 查看表结构
DESC student;
3. 显示建表语句
SHOW CREATE TABLE 表名 \G;
4. 修改表-添加字段
ALTER TABLE 表名 ADD 列名 类型;
# 示例:
ALTER TABLE students ADD birthday DATETIME;
5. 修改表-修改字段:重命名版
ALTER TABLE 表名 CHANGE 原名 新名 类型及约束;
# 示例:
ALTER TABLE students CHANGE birthday birth DATETIME NOT NULL;
6. 修改表-修改字段:不重命名版
ALTER TABLE 表名 MODIFY 列名 类型及约束;
# 示例:
ALTER TABLE students MODIFY birth DATE NOT NULL;
7. 修改表-删除字段
ALTER TABLE 表名 DROP 列名;
# 示例:
ALTER TABLE students DROP birthday;
8. 删除表
DROP TABLE 表名;
# 示例:
DROP TABLE student;
9. 查看表的创建语句
SHOW CREATE TABLE 表名;
# 示例:
SHOW CREATE TABLE student;
02. 数据类型
1). 了解数据类型
? 查询关键词
# 了解关于 int 的可以填值范围
? INT使用数据类型的原则:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间
常用数据类型如下:整数:bit [0-64],tinyint,smallint,int
小数:decimal
字符串:varchar,char
日期时间: date,time,datetime
枚举类型 (enum) 多选一,例如性别字段 enum ('男', '女'),后面添加数据时,只能填写 '男' 和 '女' 这两项,其他值填写进来会报错
3. 特别说明的类型如下:decimal 表示浮点数,如 decimal(5,2) 表示共存 5 位数,小数占 2 位
char 表示固定长度的字符串,如 char(5),如果填充 'ab' 时会补三个空格为 'ab ' ,即 char(5) 实际在数据库中占用的空间为 5 个字符
varchar 表示可变长度的字符串,如 varchar(5),填充 'ab' 时就会存储 'ab%',即varchar(5) 实际在数据库中占用的空间为 3 个字符
4. char 类型与 varchar 类型的比较:虽然 char() 的用法看起来比 varchar() 更占空间
但是从执行效率来看,char 类型的数据比 varchar 类型的要高
这是因为 varchar 数据必须时刻判断是否每个 varchar() 里面有结束符
5. 字符串 text 表示存储大文本,当字符大于 4000 时推荐使用
6. 对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个文件管理服务器上,然后在表中存储这个文件的保存路径
2). 数值类型 (常用)
| 类型 | 字节大小 | 有符号范围 (Signed) | 无符号范围 (Unsigned) |
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
| SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
| MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
| INT/INTEGER | 4 | -2147483648 ~2147483647 | 0 ~ 4294967295 |
| BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
3). 小数类型
| 类型 | 使用 | 描述 |
| decimal(M,D) | decimal (5,2),表示只能有 5 个数,其中最多设置 2 个数字在小数点后面,可以存储的数值:1000.5,123.56,不可以存储的数值:1000.51,100000, 1.345 | 十进制小数,用于表示商品的价格 |
注意:开发中,一般 QQ 号或者手机号都是使用字符串来保存的,这是因为这两种的数值太大了,用数值类型来存储太占空间
4). 字符串
| 类型 | 字节大小 | 示例 |
| CHAR | 0-255 |
定长字符串,类型: char(3)
输入 'ab',实际存储为 'ab '
输入 'abcd',实际存储为 'abc' |
| VARCHAR | 0-255 |
不定长字符串,类型: varchar(3)
输 'ab',实际存储为 'ab'
输入 'abcd',实际存储为 'abc' |
| TEXT | 0-65535 | 大文本 |
在 5.5 版本的 MySQL 以后,varchar 类型可以存储的数据,可以达到 65535 个字符
5). 日期时间类型
| 类型 | 字节大小 | 示例 | 场景 | | --------- | -------- | ----------------------------------------------------- | ------------------------------------ | | DATE | 4 | '2020-01-01' | 日期记录,会员过期时间,活动时间范围 | | TIME | 3 | '12:29:59' | 餐厅的餐牌 | | DATETIME | 8 | '2020-01-01 12:29:59' | 会员登录时间 | | YEAR | 1 | '2017' | 电影的年份.... | | TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC | 基本用不上 |
DATETIME 和 TIMESTAMP,很多时候会使用程序中的时间戳来代替,后面在数据库中保存时设置字段的类型是数值型。这样的话,可以节省存储空间,同时还可以提高数据的读取速度
03. 约束规则主键 primary key:在表中区分每一行数据的唯一性的标志服,数据在物理上存储的顺序
非空 not null:此字段不允许填写空值,如果允许填写空值,则直接不填 not null
惟一 unique:此字段的值不允许重复
默认 default:当不填写此值时会使用默认值,如果填写时以填写的为准
外键 foreign key:用于连接两个表的关系,对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询时是否此值是否存在。如果存在则填写成功;如果不存在则填写失败并抛出异常
说明:虽然外键约束可以保证数据的有效性,但是在进行数据的 crud( create 增加、update 修改、delete 删除、read 查询)时,都会降低数据库的性能,所以不推荐使用。那么数据的有效性怎么保证呢?可以在 Python 的逻辑层进行判断控制 [用代码控制]
04. 数据库设计关系型数据库建议在 E-R 模型的基础上,我们需要根据产品经理的设计策划,抽取出来模型与关系,制定出表结构,这是项目开始的第一步
在开发中有很多设计数据库的软件,常用的如 power designer,db desinger 等,这些软件可以直观的看到实体及实体间的关系
设计数据库,可能是由专门的数据库设计人员完成,也可能是由开发组成员完成,一般是项目经理带领组员来完成
现阶段不需要独立完成数据库设计,但是要注意积累一些这方面的经验
05. 实体实体就是我们根据开发需求,要保存到数据库中作为一张表存在的事物。实体的名称最终会变成表名
实体会有属性,实体的属性就是描述这个事物的内容,实体的属性最终会在表中作为字段存在
实体与实体之间会存在关系,这种关系一般就是根据三范式提取出来的主外键
06. 三范式范式理论【在总结了经验以后,得出规范我们数据库设计的一些理论】数据要保证不可分割
数据不能冗余 (多余)
数据不能重复.重复的数据,新建一张表存储
经过研究和对使用中问题的总结,对于设计数据库提出了一些规范,这些规范被称为范式 (Normal Form)
目前有迹可寻的共有8种范式,一般需要遵守3范式即可
2. 第一范式(1NF):强调的是列的原子性,即列不能够再分成其他几列考虑这样一个表:
1.【联系人】(姓名,性别,电话)
2.如果在实际场景中,一个联系人有家庭电话和公司电话,那么这种表结构设计就没有达到 1NF
3.要符合 1NF 我们只需把列(电话)拆分,即:【联系人】(姓名,性别,家庭电话,公司电话)
4.1NF 很好辨别,但是 2NF 和 3NF 就容易搞混淆
3. 第二范式(2NF):首先是 1NF,另外包含两部分内容,一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分考虑一个订单明细表:
1.【OrderDetail】(OrderID,ProductID,UnitPrice,Discount,Quantity,ProductName)
2.在一个订单中可以订购多种产品,所以一个 OrderID 是不足以成为主键的,主键应该是(OrderID,ProductID)
3.另外, Discount(折扣),Quantity(数量)完全依赖(取决)于主键(OderID,ProductID)
4.而 UnitPrice,ProductName 只依赖于 ProductID
5.所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据
6.可以把【OrderDetail】表拆分为【OrderDetail】(OrderID,ProductID,Discount,Quantity)和【Product】(ProductID,UnitPrice,ProductName)来消除原订单表中UnitPrice,ProductName多次重复的情况
4. 第三范式(3NF):首先是 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况考虑一个订单表
1.【Order】(OrderID,OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity)主键是(OrderID)
2.其中 OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity 等非主键列都完全依赖于主键(OrderID),所以符合 2NF
3.不过问题是 CustomerName,CustomerAddr,CustomerCity 直接依赖的是 CustomerID(非主键列),而不是直接依赖于主键,它是通过传递才依赖于主键,所以不符合 3NF
4.通过拆分【Order】为【Order】(OrderID,OrderDate,CustomerID)和【Customer】(CustomerID,CustomerName,CustomerAddr,CustomerCity)从而达到 3NF
5.第二范式(2NF)和第三范式(3NF)的概念很容易混淆,区分它们的关键点在于:
2NF:非主键列是否完全依赖于主键,还是依赖于主键的一部分
3NF:非主键列是直接依赖于主键,还是直接依赖于非主键列
07. 逻辑删除对于重要数据,并不希望物理删除,一旦删除,数据无法找回
删除方案:设置 isDelete 的列,类型为 bit,表示逻辑删除,默认值为 0
对于非重要数据,可以进行物理删除
数据的重要性,要根据实际开发决定
08. 消除重复行在 SELECT 后面列前使用 DISTINCT 可以消除重复的行
SELECT DISTINCT 列1,... FROM 表名;
# 示例:
SELECT DISTINCT gender FROM students;
09. WHERE 条件的运算符进阶
1). 空判断注意:NULL 与 '' 是不同的
查询没有填写身高的学生
SELECT * FROM students WHERE height IS NULL;判非空 is not null
查询填写了身高的学生
SELECT * FROM students WHERE height IS NOT NULl;查询填写了身高的男生
SELECT * FROM students WHERE height IS NOT NULL AND gender=1;
2). 优先级优先级由高到低的顺序为:小括号,not,比较运算符,逻辑运算符
and 比 or 先运算,如果同时出现并希望先算 or,需要结合 () 使用
10. 连接查询当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回
mysql 支持三种类型的连接查询,分别为:
内连接查询:查询的结果为两个表匹配到的数据
右连接查询:查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用 NULL填充
左连接查询:查询的结果为两个表匹配到的数据,左表特有的数据,对于右表中不存在的数据使用 NULL 填充
SELECT * FROM 表1
INNER\LEFT\RIGHT JOIN 表2
ON 表1.列 = 表2.列
6. 示例01:使用内连接查询班级表与学生表
SELECT * FROM students
INNER JOIN classes
ON students.cls_id=classes.id;
7. 示例02:使用左连接查询班级表与学生表
SELECT * FROM students AS s
LEFT JOIN classes AS c
ON s.cls_id=c.id;
8. 示例03:使用右连接查询班级表与学生表
SELECT * FROM students AS s
RIGHT JOIN classes AS c
ON s.cls_id = c.id;
9. 示例04:查询学生姓名及班级名称
SELECT s.name,c.name FROM students AS s
INNER JOIN classes AS c
ON s.cls_id = c.id;
11. 子查询
1). 子查询在一个 SELECT 语句中,嵌入了另外一个 SELECT 语句, 那么被嵌入的 SELECT 语句称之为子查询语句
2). 主查询主要查询的对象,第一条 SELECT 语句
3). 主查询和子查询的关系子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 SELECT 语句
4). 示例
查询大于平均年龄的学生
SELECT * FROM students WHERE age>(SELECT avg(age) FROM students);