一、模型和数据模型
(一)模型
是现实世界特征的模拟和抽象,例如地图、建筑设计沙盘、模型飞机、汽车模型等。
(二)数据模型(Data Model)
是现实世界数据特征的抽象,具有以下特点:
- 从计算机实现的观点对数据建模;
- 是信息世界概念和联系在计算机世界的表示方法;
- 有严格的形式化定义,便于在计算机上实现;
- 描述内容包含三部分:数据结构、数据操作、数据约束。
二、数据模型抽象层次
数据模型从抽象到具体分为三个层次:
层次 | 全称 | 特点与作用 |
---|---|---|
CDM | 概念模型(Concept Data Model) | 面向用户、客观世界,描述概念化结构;按用户观点建模,表现现实世界事物及联系;语义表达能力强,如 E-R 模型。 |
LDM | 逻辑数据模型(Logical Data Model) | 用图形反映业务过程;定义实体、属性、关系;是 IT 与业务人员沟通的桥梁。 |
PDM | 物理数据模型(Physical Data Model) | 是 CDM 和 LDM 在计算机中的具体表示;实现数据在数据库的存放,定义表、列、外键等。 |
三、数据库范式
范式是数据库表设计的规范,用于减少数据冗余、保证数据一致性,主要有 1NF、2NF、3NF。
(一)第一范式(1NF)
- 要求:表的每一列都是不可分割的基本数据项(字段具有原子性,不可再分),同一列不能有多个值。
- 例子:“课程时间(开始 / 结束)” 是可分割的,需拆分为 “课程开始时间” 和 “课程结束时间”,拆分后满足 1NF。
(二)第二范式(2NF)
- 前提:满足 1NF。
- 要求:每一个非主属性完全函数依赖于候选键,解决部分依赖问题(若复合主键中,字段仅依赖主键的某一个部分,即为部分依赖)。
- 例子:学生课程表中,主键为(学号,课程名称),但 “姓名” 仅依赖 “学号”、“学分” 仅依赖 “课程名称”,存在部分依赖。需添加逻辑主键(如 ID),使非主属性完全依赖主键。
(三)第三范式(3NF)
- 前提:满足 2NF。
- 要求:解决传递依赖问题(若存在 “关键字段→非关键字段 X→非关键字段 Y” 的依赖,即为传递依赖)。
- 例子:学生课程表中,“姓名” 依赖 “学号”,“学号” 依赖主键 ID,存在传递依赖。需拆分表(如学生信息表、课程信息表、成绩表),使字段直接依赖主键。
四、规范化的缺点与逆范式
(一)规范化的缺点
会降低数据库性能,因为需要更多 CPU、内存和 I/O 来关联多张表以获取信息。
(二)逆范式
- 目的:提高查询效率,刻意在表中保存其他表的具体数据(而非主键)。
- 缺点:增加数据冗余。
- 本质:磁盘利用率与查询效率的对抗,适用于查询效率优先的场景。
五、SQL 插入数据操作
(一)情况 1:为所有字段按默认顺序插入数据
- 语法:
INSERT INTO table_name VALUES (value1, value2, ...)
- 注意:
- 值的顺序需与表中字段定义顺序一致;
- 数值型数据单引号可选,字符型等其他类型必须用单引号;
- 习惯上数值型不用单引号,以提高可读性。
CREATE TABLE northwind.tbl_product (prod_id varchar(10) not null,prod_desc varchar(25) not null,cost numeric(6, 2) not null
);
INSERT INTO northwind.tbl_product VALUES('1234', 'Leather Gloves', 24.99);
(二)情况 2:为指定字段插入数据
- 语法:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
- 注意:
- 字段列表次序可与表定义不同,但值的次序必须与字段列表次序一致;
- 未指定的字段取表定义的默认值;
- 数据类型必须匹配,否则报错。
例子:
INSERT INTO hr.employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, manager_id, department_id
) VALUES (300, 'Smith', 'John', 'SJOHN', '317.299.6868', '1998-06-19', 'ST_CLERK', 3600, 123, 50
);
(三)情况 3:同时插入多条记录
- 语法:
方式 1:INSERT INTO table_name VALUES (value_list1), (value_list2), ...;
方式 2:INSERT INTO table_name(column1, column2, ...) VALUES (value_list1), (value_list2), ...;
- 特点:
- 多个值列表以逗号分隔;
- 效率比多次单行插入更高;
- MySQL 会返回额外信息(Records:插入条数;Duplicates:重复忽略的记录;Warnings:数据问题提示)。
示例:
INSERT INTO emp(emp_id,emp_name)
VALUES (1001,'shkstart'),(1002,'atguigu'),(1003,'Tom');
六、SQL 数据操作
(一)插入 NULL 值
- 场景:当字段值不确定时,插入NULL值(避免存储错误值浪费空间)。
- 语法:
INSERT INTO schema.table_name VALUES (column1_value, NULL, column3_value);
- 注意:NULL需位于对应字段的正确次序上。
(二)更新现有数据(UPDATE语句)
用于修改表中现有数据,支持更新一列 / 多列、一行 / 多行。
- 更新一列的数据
语法:UPDATE table_name SET column_name = new_column_value [WHERE conditions];
示例:
UPDATE order_tbl
SET qty = 1
WHERE ord_num = '23A16'
注意:省略WHERE子句会更新表中所有记录,务必谨慎。
- 更新一条或多条的多个字段
语法:UPDATE table_name SET column1 = value1, column2 = value2, ... [WHERE conditions];
示例:
UPDATE order_tbl
SET cust_id = '110',prod_id = '7726',qty = 1
WHERE ord_num = '23A16'
注意:多个列的赋值表达式用逗号分隔,SET关键字仅使用一次。
(三)从表中删除数据(DELETE语句)
用于删除表中整行数据,无法单独删除某一列。
- 基本语法
DELETE FROM table_name [WHERE conditions];
示例:
DELETE FROM orders_tbl
WHERE ord_num = '23A16';
关键注意:必须使用WHERE子句,否则会删除表中所有数据(不可逆)。建议先通过SELECT测试WHERE条件。
- DELETE vs TRUNCATE
对比项 | DELETE 语句 | TRUNCATE TABLE 语句 |
---|---|---|
功能 | 可删除部分 / 全部数据(带WHERE子句) | 删除表中所有数据,释放存储空间 |
事务回滚 | 支持回滚 | 不支持回滚 |
事务日志 | 占用较多日志资源 | 占用较少日志资源 |
触发触发器 | 会触发 | 不会触发 |
阿里规范建议:无特殊限制,不建议在开发代码中使用(易造成事故)
示例:
DELETE FROM hr.departments_tmp WHERE ...;
TRUNCATE TABLE hr.departments_tmp;
- 删除中的数据完整性错误
当要删除的行的主键被其他表作为外键引用时,会因外键约束失败无法删除。
示例:删除departments表中department_id = 60的记录时,若employees表的department_id外键引用该值,会报错误(代码 1451)。
说明:需先处理关联数据,否则无法删除被外键引用的主键行。
七、SELECT查询语句
(一)SELECT 子句
- 作用:与FROM子句联合使用,从数据库提取数据并指定需要的字段。
- 语法:
SELECT [ * | ALL | DISTINCT column1, column2, ... ]
- 关键说明:
- *:查询表中所有字段,便捷但实际场景建议明确指定字段(提升可读性与性能)。
- ALL:显示列的全部值(包含重复值),是默认选项,可省略。
- DISTINCT:去除结果中的重复行。
(二)FROM 子句
- 作用:指定数据的来源表,需与SELECT联合使用,可指定一个或多个表。
- 语法:
FROM table_name[, table_name2, ... ]
查询系统信息或函数结果时,可省略FROM,例如:
SELECT version() AS 数据库版本, now() AS 当前时间, user() AS 登录用户;
(三)WHERE 子句
- 作用:为查询添加过滤条件,仅返回满足条件(结果为TRUE)的数据。
- 语法:
WHERE [condition1 | expression1] [AND | OR condition2 | expression2]
- 可通过AND(且)、OR(或)连接多个条件,实现复杂过滤逻辑。
(四)ORDER BY 子句
- 作用:对查询结果进行排序,默认升序(ASC),降序需显式指定DESC。
- 语法:
ORDER BY column1|integer [ASC | DESC]
column1:指定排序的字段名。
integer:指定结果列的位置索引(从1开始计数),需注意:若SELECT子句字段顺序改变,排序规则需同步调整。
示例:
-- 按cost字段升序排序
SELECT prod_id, prod_desc, cost FROM sales.products_tbl ORDER BY cost;-- 按第3列(cost)升序排序
SELECT prod_id, prod_desc, cost FROM sales.products_tbl ORDER BY 3;-- 按cost字段降序排序
SELECT prod_id, prod_desc, cost FROM sales.products_tbl ORDER BY cost DESC;
(五)简单查询例子
(1)查询所有字段:SELECT * FROM sales.products_tbl;
说明:*列出表中所有字段,输出顺序与表结构一致;
(2)查询单个字段(含ALL默认行为)
SELECT prod_desc FROM sales.candy_tbl;
SELECT ALL prod_desc FROM sales.candy_tbl; -- ALL可省略,效果相同
(3)使用DISTINCT去除重复
SELECT DISTINCT prod_desc FROM sales.candy_tbl;
SELECT DISTINCT(prod_desc) FROM sales.candy_tbl; -- 括号提高可读性,效果同上
(4)字段别名(AS)
语法:SELECT column_name [AS] alias_name
示例:
SELECT prod_desc, prod_desc AS prod_name FROM sales.products_tbl;`
说明:别名仅在当前查询中有效,不修改物理表字段名;可简化代码、提升结果可读性。
八、字符大小写敏感性
(一)概述
- SQL 命令和关键字不区分大小写,支持大小写混用。
- MySQL 表名大小写敏感控制:通过lower_case_table_names参数,1表示不敏感,0表示敏感。
- Oracle:默认大小写敏感;
- MySQL、Microsoft SQL Server:默认大小写不敏感;
注:MySQL 字符集与排序规则(COLLATION)可在server、database、table、column4 个级别设置,优先级:server < database < table < column(列级别优先级最高)。
- 常用命令:
查询支持的字符集:SHOW CHARACTER SET;(或SHOW CHAR SET;、SHOW CHARSET;)
,
常用字符集:GBK、UTF8、UTF8MB4;
查询支持的排序规则:SHOW COLLATION;
(二)COLLATION 结尾标识含义
COLLATION 名称以_ci、_cs、_bin结尾,含义如下:
标识 | 含义 | 示例说明 |
---|---|---|
_ci | case insensitive(大小写不敏感) | a和A视为相同 |
_cs | case sensitive(大小写敏感) | a和A视为不同 |
_bin | binary(二进制比较) | a和A视为不同 |
示例(查询 GBK 相关排序规则):
SHOW COLLATION LIKE 'gbk%';
(三)设置 CHARACTER SET 和 COLLATION
- server 级别
默认字符集utf8mb4,排序规则默认utf8mb4_0900_ai_ci,启动时可修改:
mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci
- database 级别
CREATE DATABASE database_name CHARACTER SET utf8 COLLATE utf8_bin;
ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_bin;
- table 级别
CREATE TABLE TABLE_NAME (...) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
- column 级别
column_name [CHAR | VARCHAR | TEXT] (length) CHARACTER SET character_set_name COLLATE collation_name;
ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;
(四)查询时显式指定 collation 或使用 binary
除了在数据库层次设置,查询时可显式指定 collation 覆盖表 / 列设置,或使用binary:
- 显式指定 collation
示例:
SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;-- 覆盖排序规则的查询示例
SELECT DISTINCT t1.col1 COLLATE utf8mb4_unicode_ci AS col1
FROM (SELECT 'A' AS col1 UNION ALL SELECT 'a' AS col1) AS t1;
- 使用 binary 关键字或函数
条件前加binary关键字:
SELECT host, user, authentication_string
FROM mysql.user
WHERE binary user = 'admin' AND binary authentication_string = '用户密码';
使用binary()函数:
SELECT host, user, authentication_string
FROM mysql.user
WHERE username LIKE binary('root') AND password LIKE binary('用户密码');
二进制过滤示例:
SELECT 'binary过滤' filter_typ, t1.*
FROM (SELECT 'A' AS col1 UNION ALL SELECT 'a' AS col1) AS t1
WHERE BINARY t1.col1 = 'a'
UNION ALL
SELECT '默认过滤' filter_typ, t1.*
FROM (SELECT 'A' AS col1 UNION ALL SELECT 'a' AS col1) AS t1
WHERE t1.col1 = 'a';
九、utf8mb4 说明
MySQL 的utf8最多支持 3 字节字符,utf8mb4支持 4 字节字符(如 emoji),建议使用 utf8mb4。
utf8mb4的排序规则:
- utf8mb4_bin:二进制比较,大小写敏感;
- utf8mb4_unicode_ci、utf8mb4_general_ci:大小写不敏感;
两者选择:
- 准确性:utf8mb4_unicode_ci基于 Unicode 标准排序,更准确;utf8mb4_general_ci特殊字符排序可能不符合预期;
- 性能:utf8mb4_general_ci理论上更快,但现代 CPU 性能差异可忽略,推荐 utf8mb4_unicode_ci(未来版本可能成为默认)。