1. MySQL基础

三气归来
143 阅读11分钟

1. MySQL数据类型

MySQL数据类型定义了数据的大小范围，因此使用时选择合适的类型，不仅会降低表占用的磁盘空间， 间接减少了磁盘I/O的次数，提高了表的访问效率，而且索引的效率也和数据的类型息息相关。

1.1 数值类型

整数类型字节最小值最大值
TINYINT1有符号-128
无符号 0		有符号 127
无符号 255
SMALLINT2有符号-32768
无符号 0		有符号 32767
无符号 65535
MEDIUMINT3有符号-8388608
无符号 0		有符号 8388607
无符号 16777215
INT、INTEGER4有符号-2147483648
无符号 0		有符号 2147483647
无符号 4294967295
BIGINT8有符号-9223372036854775808
无符号 0		有符号 9223372036854775807
无符号 18446744073709551615
浮点数类型字节最小值最大值
FLOAT4±1.175494351E-38±3.402823466E+38
DOUBLE8±2.2250738585072014E-308±1.7976931348623157E+308

浮点类型推荐使用decimal类型（保存为字符串格式）。

注：age INT(9); 整形占用内存大小是固定的，（M）只是代表整数显示的宽度

1.2 字符串类型

类型字节描述及存储需求
CHAR (M)MM 为 0~255 之间的整数，值的长度固定为 M 个字节
VARCHAR (M)MM 为 0~65535 之间的整数，值的长度+1 个字节
TINYBLOB-允许长度 0~255 字节，值的长度+1 个字节
BLOB-允许长度 0~65535 字节，值的长度+2 个字节
MEDIUMBLOB-允许长度 0~167772150 字节，值的长度+3 个字节
LONGBLOB-允许长度 0~4294967295 字节，值的长度+4 个字节
TINYTEXT-允许长度 0~255 字节，值的长度+2 个字节
TEXT-允许长度 0~65535 字节，值的长度+2 个字节
MEDIUMTEXT-允许长度 0~167772150 字节，值的长度+3 个字节
LONGTEXT-允许长度 0~4294967295 字节，值的长度+4 个字节
VARBINARY (M)M允许长度 0~M 个字节的变长字节字符串，值的长度+1 个字节
BINARY (M)M允许长度 0~M 个字节的定长字节字符串

1.3 日期和时间类型

类型字节最小值最大值
DATE41000-01-019999-12-31
DATETIME81000-01-01 00:00:009999-12-31 23:59:59
TIMESTAMP4197001010800012038 年的某个时刻
TIME3-838:59:59838:59:59
YEAR119012155

日期类型也是做项目过程中，经常使用的类型信息，尤其是TIMESTAMP和DATETIME两个类型，但是注 意TIMESTAMP会自动更新时间，非常适合那些需要记录最新更新时间的场景，而DATETIME需要手动更新。

1.4 enum和set

这两个类型，都是限制该字段只能取固定的值，但是枚举字段只能取一个唯一的值，而集合字段可以取 任意个值。

2. MySQL运算符

2.1 算术运算符

运算符作用
+加法
-减法
*乘法
/, DIV除法，返回商
%, MOD除法，返回余数

2.2 逻辑运算符

运算符作用
NOT 或!逻辑非
AND 或&&逻辑与
OR 或 ||逻辑或

2.3 比较运算符

运算符作用
=等于
<>!=不等于
<=>NULL 安全的等于 (NULL-safe)
<小于
<=小于等于
>大于
>=大于等于
BETWEEN存在于指定范围
IN存在于指定集合
IS NULL为 NULL
IS NOT NULL不为 NULL
LIKE通配符匹配
REGEXP 或 RLIKE正则表达式匹配
SELECT NULL = NULL;    -- 返回 NULL
SELECT NULL <=> NULL;  -- 返回 1

通配符：

  1. 百分号 %

    • 代表零个、一个或多个任意字符。
    • 例如，'abc%' 可以匹配 'abc', 'abcd', 'abcxyz' 等等。

  2. 下划线 _

    • 代表单个任意字符。
    • 例如，'a_c' 可以匹配 'abc', 'acc', 'adc' 等，但不会匹配 'ac' 或 'abbc'，因为下划线只代表一个字符。

3. MySQL常用函数

3.1 字符串函数

  • CONCAT(str1, str2, ...): 连接两个或多个字符串。

    SELECT CONCAT('Hello', ' ', 'World'); -- 结果：Hello World

  • LOWER(str), UPPER(str): 将字符串转换为小写或大写。

    • LOWER('HELLO') 返回 'hello'
    • UPPER('world') 返回 'WORLD'

  • SUBSTRING(str, pos, len): 提取字符串的一部分。

    SELECT SUBSTRING('HelloWorld', 6, 5); -- 结果：World

  • LENGTH(str): 返回字符串的字节长度。

    SELECT LENGTH('Hello'); -- 结果：5

3.2数值函数

  • ABS(x): 返回 x 的绝对值。

    SELECT ABS(-10); -- 结果：10

  • CEIL(x), FLOOR(x): 分别返回不小于 x 和不大于 x 的最接近整数。

    • CEIL(3.14) 返回 4
    • FLOOR(3.99) 返回 3

  • ROUND(x[, d]): 四舍五入到指定的小数位数。

    SELECT ROUND(3.1415, 2); -- 结果：3.14

3.3 日期时间函数

  • NOW(), CURRENT_TIMESTAMP(): 返回当前日期和时间。

    深色版本
SELECT NOW();

  • DATE_ADD(date, INTERVAL expr unit): 添加一个时间间隔到日期。

    SELECT DATE_ADD('2024-12-27', INTERVAL 1 DAY); -- 结果：2024-12-28

  • DATEDIFF(date1, date2): 计算两个日期之间的天数差。

    SELECT DATEDIFF('2024-12-30', '2024-12-27'); -- 结果：3

3.4 聚合函数

  • COUNT(*), COUNT(column): 计算行数或非空值的数量。

    SELECT COUNT(*) FROM table_name;

  • SUM(column): 返回数值列的总和。

    SELECT SUM(salary) FROM employees;

  • AVG(column): 返回数值列的平均值。

    SELECT AVG(salary) FROM employees;

  • MAX(column), MIN(column): 返回列中的最大值或最小值。

    SELECT MAX(salary), MIN(salary) FROM employees;

3.5 控制流函数

  • IF(expr, true_val, false_val): 如果表达式为真，则返回 true_val，否则返回 false_val。

    SELECT IF(1 > 2, 'Yes', 'No'); -- 结果：No

  • CASE WHEN ... THEN ... ELSE ... END: 根据条件返回不同的值。

    SELECT CASE WHEN age < 18 THEN 'Minor'
            WHEN age >= 18 AND age < 65 THEN 'Adult'
            ELSE 'Senior' END AS age_group
            FROM people;

4. MySQL完整性约束

4.1 主键约束 (Primary Key)

主键是一种唯一标识表中每一行记录的字段或字段组合。一个表只能有一个主键，并且主键不允许有 NULL 值。主键通常用于保证行的唯一性，并且可以加快关联查询的速度。

创建时定义：

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

添加到现有表：

ALTER TABLE table_name ADD PRIMARY KEY (id);

4.2 自增键约束 (Auto Increment)

自增键约束是用于自动为新插入的行生成唯一的标识符。通常与主键一起使用，但也可以单独使用。当新记录被插入时，如果没有提供该列的值，MySQL 将自动为其分配下一个可用的整数值。

创建时定义：

CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

注意： 自增的初始值和增量可以通过 AUTO_INCREMENT 属性进行设置，例如：

ALTER TABLE table_name AUTO_INCREMENT = 100;

4.3 唯一键约束 (Unique)

唯一键约束确保一列或多列的组合不能包含重复的值。这意味着在该列上，除了 NULL 外的所有值都必须是唯一的。一个表可以有多个唯一键。

创建时定义：

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

添加到现有表：

ALTER TABLE table_name ADD UNIQUE (email);

4.4 非空约束 (Not Null)

非空约束用于强制列不接受 NULL 值。这意味着在插入或更新记录时，必须为该列提供一个值。

创建时定义：

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

4.5 默认值约束 (Default)

默认值约束允许你为列指定一个默认值。如果在插入新记录时没有为该列提供值，则会自动使用默认值。

创建时定义：

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active'
);

4.6 外键约束 (Foreign Key)

外键约束用于建立和加强两个表之间的链接完整性。它确保引用完整性，即子表中的外键必须匹配父表中的某个主键或唯一键值，或者它可以是 NULL。

创建时定义：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

添加到现有表：

ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

删除外键约束：

ALTER TABLE orders DROP FOREIGN KEY fk_customer;

请注意，外键的名字（如 fk_customer）需要事先知道或查看信息架构来找到。

5. 关系型数据库表设计

5.1 一对一

在子表中增加一列关联父表的主键

  • 用户User（父表）
uidnameagesex
1000zhang20M
1020liu21W
2010wang22M
  • 身份信息Info（子表）
uidcardidaddrinfo
1020112233aaa
2010334455bbb
1000556677ccc

5.2 一对多

image.png

image.png

所以一对一和一对多都是子表增加一列，关联父表的主键

5.3 多对多

商品-订单

订单表拆开后，发现信息没那么冗余

image.png

可以在订单内容表中创建联合索引 orderid+pid

6. 关系型数据库范式设计

应用数据库范式可以带来许多好处，但是最重要的好处归结为三点：

1）减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）

2）消除异常（插入异常，更新异常，删除异常）

3）让数据组织的更加和谐 但是数据库范式绝对不是越高越好，范式越高，意味着表越多，多表联合查询的机率就越大，SQL的效 率就变低。

遵循范式意味着要多表查询，有性能损耗，需要综合考量，单表比多表查询毕竟快

6.1 第一范式（1NF）

每一列保持原子特性

列都是基本数据项，不能够再进行分割，否则设计成一对多的实体关系。例如表中的地址字段，可以再 细分为省，市，区等不可再分割（即原子特性）的字段。

image.png 上图的表就是把地址字段分成更详细的city，country，street三个字段，注意，不符合第一范式不能称 作关系型数据库。

6.2 第二范式（2NF）

属性完全依赖于主键-主要针对联合主键

非主属性完全依赖于主关键字，如果不是完全依赖主键，应该拆分成新的实体，设计成一对多的实体关 系。

例如：选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，（学号，课程名称）是联合 主键，但是学分字段只和课程名称有关，和学号无关，相当于只依赖联合主键的其中一个字段，不符合 第二范式。学分字段存在大量冗余， 年龄也是。

6.3 第三范式（3NF）

一般关系型数据库满足第三范式就可以了，再高带来的收益不匹配性能损耗

属性不依赖其他非主属性

示例：学生关系表为Student（学号， 姓名， 年龄， 所在学院， 学院地点， 学院电话），学号是主 键，但是学院电话只依赖于所在学院，并不依赖于主键学号，因此该设计不符合第三范式，应该把学院 专门设计成一张表，学生表和学院表，两个是一对多的关系。

image.png

6.4 BC范式

每个表中只有一个候选键（在一个数据库 中每行的值都不相同，则可称为候选键）

比如员工ID和员工Email 就不相同，所以没必要在一张表里

6.5 第四范式（4NF）

消除表中的多值依赖

简单来说，第四范式就是要消除表中的多值依赖，也就是说可以减少维护数据一致性的工作。比如图4 中的noNF表中的skill技能这个字段，有的人是“java，mysql”，有的人描述的是“Java，MySQL”，这样 数据就不一致了，解决办法就是将多值属性放入一个新表，所以满足第四范式的关系图如下

image.png

从上面对于数据库范式进行分解的过程中不难看出，应用的范式越高，表越多。表多会带来很多问题：

1、 查询时需要连接多个表，增加了SQL查询的复杂度

2 、查询时需要连接多个表，降低了数据库查询性能

因此，并不是应用的范式越高越好，视实际情况而定。第三范式已经很大程度上减少了数据冗余，并且 基本预防了数据插入异常，更新异常，和删除异常了。

avatar
文章
阅读
粉丝