一些SQL的基本概念和用法

目录

1 关系型数据库的“三大范式”

1.1 第一范式（1NF）：字段具有“原子性”,不可再分。

1.2 第二范式（2NF）：在1NF的基础上，非主键字段完全依赖于主键字段。

1.3 第三范式（3NF）：在2NF基础上，任何非主键字段不依赖于其它非主字段。

2 如何改善关系型数据库的性能。（《MySQL必知必会》P227）备份数据库和清除垃圾数据。

3 必须熟练掌握的SQL语法（DDL、DML、TCL、DCL）

3.1 DDL（数据定义语言）

3.2 TCL（事务控制语言）

                  ​​​​​​​3.3 DML（数据操纵语言）

3.4 DCL（数据控制语言）

---

1 关系型数据库的“三大范式”

设计关系数据库时，需要根据情况遵从不同的规范要求，这些不同的规范要求被称为不 同的范式，范式越高冗余越小，但可能代理性能压力也会增加。

关系数据库有6种范式，但实际中我们往往要求遵从前三范式：第一范式（1NF）、第二 范式（2NF）、第三范式（3NF）。

1.1 第一范式（1NF）：字段具有“原子性”,不可再分。

（1）解析： 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数 据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。关系数据库中，第一范式 （1NF）是对关系模式的设计基本要求。

（2）示例： “学生姓名” 字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名； 如果要区分出姓和名，必须设计成两个独立的字段。

1.2 第二范式（2NF）：在1NF的基础上，非主键字段完全依赖于主键字段。

（1）解析： 2NF 要求数据库表中的每个记录必须可以被唯一地区分。选取一个能区分每个实体的属 性或属性组，作为实体的唯一标识，在找不到候选时，可额外增加属性以实现区分。

2NF 要求实体的属性完全依赖于主键属性。不能存在仅依赖主关键字一部分的属性，如 果存在，那么这个属性和主键属性的这一部分应该分离出来形成一个新的实体，新实体与原 实体之间是一对多的关系。

（2）示例： 考虑一个常见的 “学生选课信息表”

学生选课信息表（学号，学生姓名、年龄、性别、课程、课程学分、系别、学科成绩， 系办地址、系办电话）

问题： 姓名和年龄不依于课程，即不完全依赖于主属性因此不满足第二范式的要求，会产生数据冗 余：同一门课程由n个学生选修，"学分"就重复n­1次；同一个学生选修了m门课程，姓名和 年龄就重复了m­1次。 学生选课信息表（学号，学生姓名、年龄、性别、课程、课程学分、系别、学科成绩， 系办地址、系办电话）

解决方案： 把“学生信息表格”改为以下三张表格： 

学生表（学号，姓名，年龄，性别，系别，系办地址、系办电话） 课程表（课程名称,学分） 选课关系表（学号，课程名称，成绩）

1.3 第三范式（3NF）：在2NF基础上，任何非主键字段不依赖于其它非主字段。

（1）解析： 第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字信息。

（2）示例： 

学生表（学号，姓名，年龄，性别，系别，系办地址、系办电话）

问题： 学生表中主键为学号，但学号、系别、系办地址、系办电话形成了一下依赖关系。 （学号） → (系别）→（系办地点，系办电话）

解决方案： 把学生表拆解为： 

学生表（学号，姓名，年龄，性别，系别） 系别表（系别，系办地址、系办电话）

   

2 如何改善关系型数据库的性能。（《MySQL必知必会》P227）备份数据库和清除垃圾数据。

（1）总有不止一种方法编写同一条select语句，应该试验连接（join）、并（union）、子 查询               等，找出最佳方法。

（2）决不检索比需要多的数据，不要使用select *，除非真的需要每个列。

（3）创建索引，索引可以避免全表扫描。为经常出现在where和order by子句中的字段创建 索               引。同时更新索引是要付出额外代价的，索引并非越多越好。

（4）复杂的or条件可以通过多条select语句用union合并来实现，这样会较大提升性能。

（5）like模糊查询很慢，使用全文索引替代like。

（6）使用存储过程或预编译SQL语句来提高多条语句的执行效率。

（7）经常备份数据库和清除垃圾数据。 

3 必须熟练掌握的SQL语法（DDL、DML、TCL、DCL）

3.1 DDL（数据定义语言）

# 使用率最高的DDL语句
CREATE DATABASE db_name; #建库
USE db_name; #选择库
DROP DATABASE db_name; #删库
CREATE TABLE table_name( #建表
column_name_1 column_type_1 constraints,
column_name_2 column_type_2 constraints,
......
column_name_n column_type_n constraints
);
DROP TABLE table_name; #删表
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY
KEY(pk_column_name); #添加主键
ALTER TABLE table_name ADD CONSTRAINT uk_name unique(uk_column_name);
#添加唯一键
ALTER TABLE table_name ADD CONSTRAINT fk_name foreign key
(fk_column_name) references ref_table_name (ref_column_name); #添加
外键
ALTER TABLE table_name ADD CONSTRAINT ck_name check(ck_expression);
#添加检查约束
#创建索引
CREATE INDEX index_name ON table_name(column_name);
#以下使用率略低一些
ALTER TABLE tabl_ename MODIFY [COLUMN] column_definition
#修改字段
ALTER TABLE table_name ADD [COLUMN] column_definition [FIRST | AFTER
col_name]; #增加字段
ALTER TABLE tablename DROP [COLUMN] column_name;
#删除字段
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition
[FIRST | AFTER col_name]; #字段改名

3.2 TCL（事务控制语言）

（1）事务处理（Transaction）： 数据库事务用来维护数据完整性，它保证一批SQL操作的原子性，要么完全执行，要么完全 不执行。

（2）MySQL中的数据库引擎 MySQL支持几种数据库引擎，并非所有引擎都支持事务处理，MyISAM和InnoDB是最常用 的MySQL引擎，前者不支持事务处理，后者支持。

（3）事务的常用关键字：

               transaction（事务）：指一组原子性的SQL语句。

               rollback（回滚）：值撤销指定的SQL语句的执行。

               commit（提交）：将未存储的SQL语句写入数据库。

               savepoint（保存点）：事务处理中设置的临时位置，可以对其进行提交或回滚。

（4）示例：

START TRANSACTION; #开始事务
DELETE FROM USER WHERE id=4;
DELETE FROM USER WHERE id=5;
ROLLBACK; #回滚事务
START TRANSACTION; #开始事务
DELETE FROM USER WHERE id=4;
SAVEPOINT deleteA; #创建保存点deleteA
DELETE FROM USER WHERE id=5;
ROLLBACK TO deleteA; #回滚到保存点deleteA
COMMIT;

（5）更改默认提交行为：

           默认设置下，MySQL是自动提交事务的。任何一条MySQL语句所做的更改都是立即生效 的。如果希望MySQL不自动提交更改，需要设置autocommit标记值。

SET autocommit=0;

3.3 DML（数据操纵语言）

3.3.1 增改删

INSERT INTO table_name(column1,column2...columnN)
VALUES(value1,value2,...valueN); #insert
UPDATE table_name SET column1=value1,column2=value2,...columnN=valueN
[WHERE CONDITION]; #update
DELETE FROM table_name [WHERE CONDITION];
#delete

3.3.2 查询

SELECT 字段/表达式列表 FROM 表名 [LEFT/INNER JOIN 表名 ON 关联字段] WHERE
字段/表达式条件 GROUP BY 分组 ORDER BY 排序 [ASC/DESC] HAVING 聚合函数条
件;

3.4 DCL（数据控制语言）

DCL用来定义访问的权限和安全级别，对用户的创建，及授权。DCL通常由数据库管理员 （DB Administrator）使用，程序员使用得不多。

（1）创建用户

create user 用户名@IP地址 identified by '密码'；

（2）分配权限

grant 权限1，权限2，... on 数据库.数据库对象 to 用户名@IP地址

（3）撤销权限

revoke 权限1，...，权限n on 数据库.* from 用户名@IP地址