1. SQL 的概念

1.1 什么是 SQL

Structured Query Language 结构化查询语言

1.2 SQL 作用

  1. 是一种所有关系型数据库的查询规范，不同的数据库都支持
  2. 通用的数据库操作语言，可以用在不同的数据库中
  3. 不同的数据库 SQL 语句有一些区别

SQL规范(普通话) MySQL特有(方言) Oracle特有(方言)

1.3 SQL 语句分类

  1. Data Definition Language (DDL 数据定义语言)
    如：建库，建表
  2. Data Manipulation Language(DML 数据操纵语言)
    如：对表中的记录操作增删改
  3. Data Query Language(DQL 数据查询语言)
    如：对表中的查询操作
  4. Data Control Language(DCL 数据控制语言)
    如：对用户权限的设置

1.4 MySQL 的语法

  1. 每条语句以分号结尾
  2. SQL 中不区分大小写，关键字中认为大写和小写是一样的
  3. 3 种注释：
注释的语法 说明
–空格 单行注释
/* */ 多行注释
# 这是 mysql 特有的注释方式

2. DDL 操作数据库

2.1 创建数据库

创建数据库的几种方式

1
2
3
4
5
6
-- 创建数据库
create database 数据库名;
-- 判断数据库是否已经存在，不存在则创建数据库
create database if not exists 数据库名;
-- 创建数据库并指定字符集
create database 数据库名 character set 字符集;

2.2 查看数据库

1
2
3
4
5
-- 查看所有的数据库
show databases;
-- 查看某个数据库的定义信息
show create database db3;
show create database db1;

2.3 修改数据库

1
2
3
-- 修改数据库默认的字符集
-- ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
alter database db3 character set utf8;

2.4 删除数据库

1
drop database 数据库名;

2.5 使用数据库

1
2
3
4
-- 查看正在使用的数据库  使用的一个 mysql 中的全局函数
select database();
-- 使用/切换数据库
use 数据库名

3. DDL 操作表结构

3.1 MySQL 数据类型

常用数据类型

类型 描述
int 整形
double 浮点型
varchar 字符串型
date 日期类型
格式为 yyyy-MM-dd,只有年月日,没有时分秒

详细的数据类型

分类 类型名称 类型说明
整形 tinyInt 微整型：很小的整数(占 8 位二进制)
smallint 小整型：小的整数(占 16 位二进制)
mediumint 中整型：中等长度的整数(占 24 位二进制)
int(integer) 整型：整数类型(占 32 位二进制)
小数 float 单精度浮点数，占 4 个字节
double 双精度浮点数，占 8 个字节
日期 double 双精度浮点数，占 8 个字节
time 表示时间类型
datetime 同时可以表示日期和时间类型
字符串 char(m) 固定长度的字符串，无论使用几个字符都占满全部，M 为 0~255 之间的整数
varchar(m) 可变长度的字符串，使用几个字符就占用几个，M 为 0~65535 之间的整数
大二进制 tinyblob Big Large Object 允许长度 0~255 字节
blob 允许长度 0~65535 字节
mediumblob 允许长度 0~167772150 字节
longblob 允许长度 0~4294967295 字节
大文本 tinytext 允许长度 0~255 字节
text 允许长度 0~65535 字节
mediumtext 允许长度 0~167772150 字节
longtext 允许长度 0~4294967295 字节

3.2 创建表

1
2
3
4
5
6
7
8
9
10
11
create table 表名 (
字段名 1 字段类型 1,
字段名 2 字段类型 2
);

-- 创建 student 表包含 id,name,birthday 字段
create table student (
id int, -- 整数
name varchar(20), -- 字符串
birthday date -- 生日，最后没有逗号
);

3.3 查看表

1
2
3
4
5
6
7
use 表名称;
-- 查看某个数据库中的所有表
show table;
-- 查看表结构
desc 表名;
-- 查看创建表的 SQL 语句
show create table 表名;

3.4 快速创建一个表结构相同的表

1
2
3
4
5
show create 新表名 like 旧表名

-- 创建一个 s1 的表与 student 结构相同
create table s1 like student;
desc s1;

3.5 删除表

1
2
3
4
5
6
7
8
9
10
-- 直接删除表
drop table 表名;
-- 判断表是否存在，如果存在则删除表
drop table if exists `create`;


-- 直接删除表 s1 表
drop table s1;
-- 判断表是否存在并删除 s1 表
drop table if exists `create`;

与直接删除的区别 如果表不存在，不删除，存在则删除

3.6 修改表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 添加表列 ADD
alter table 表名 add 列名 类型;

-- 修改列类型 MODIFY
alter table 表名 modify 列名 新的类型;

-- 修改列名 CHANGE
alter table 表名 change 旧列名 新列名 类型;

-- 删除列 DROP
alter table 表名 drop 列名;

-- 修改表名
rename table 表名 to 新表名;

-- 修改字符集 character set
alter table 表名 character set 字符集;

4. 数据库备份和还原

4.1 备份的应用场景

在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏
这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的

4.2 备份与还原的语句

备份格式： DOS 下，未登录的时候。这是一个可执行文件 exe，在 bin 文件夹

1
mysqldump -u 用户名 -p 密码 数据库 > 文件的路径

还原格式：mysql 中的命令，需要登录后才可以操作

1
2
3
4
5
6
7
8
9
USE 数据库；
SOURCE 导入文件的路径;

-- 备份 day21 数据库中的数据到 d:\day21.sql 文件中
mysqldump -uroot -proot day21 > d:/day21.sql

-- 还原 day21 数据库中的数据到 d:\day21.sql 文件中
use day21;
source d:/day21.sql;

5. 数据库约束的概述

5.1 数据库约束的概述

约束的作用

对表中的数据进行限制，保证数据的正确性、有效性和完整性

一个表如果添加了约束，不正确的数据将无 法插入到表中。约束在创建表的时候添加比较合适

约束种类

约束名 约束关键字
主键 primary key
唯一 unique
非空 not null
外键 foreign key
检查约束 check 注mysql不支持

5.2 主键约束

主键的作用

用来唯一标识数据库中的每一条记录

哪个字段应该作为表的主键？

通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键
主键是给数据库和程序使用的，不是给最终的客户使用的
所以主键有没有含义没有关系，只要不重复，非空就行

创建主键

1
2
3
4
5
6
7
8
9
10
11
12
-- 在创建表的时候给字段添加主键
字段名 字段类型 primary key
-- 在已有表中添加主键
alter table 表名 add primary key(字段名);

-- 创建表学生表 st5, 包含字段(id, name, age)将 id 做为主键
create table st5 (
 id int primary key, -- id 为主键
 name varchar(20),
 age int
)
desc st5;

删除主键

1
2
3
4
5
-- 删除 st5 表的主键
alter table st5 drop primary key;

-- 添加主键
alter table st5 add primary key(id);

主键自增

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
auto_increment 表示自动增长(字段类型必须是整数类型)

-- 默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下列 SQL 语法

-- 创建表时指定起始值
create table 表名(
列名 int primary key auto_increment
) auto_increment=起始值;
-- 指定起始值为 1000
create table st4 (
 id int primary key auto_increment,
 name varchar(20)
) auto_increment = 1000;

-- 创建好以后修改起始值
alter table 表名 auto_increment=起始值;
alter table st4 auto_increment = 2000;

DELETE 和 TRUNCATE 对自增长的影响

  • DELETE：删除所有的记录之后，自增长没有影响
  • TRUNCATE：删除以后，自增长又重新开始

5.3 唯一约束

什么是唯一约束： 表中某一列不能出现重复的值

1
2
3
4
5
6
7
字段名 字段类型 unique
-- 创建学生表 st7, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学生
create table st7 (
 id int,
 name varchar(20) unique
)
-- 如果插入相同的数据就会变成null,null没有数据不存在重复的问题

5.4 非空约束

什么是非空约束：某一列不能为 nul

1
2
3
4
5
6
7
字段名 字段类型 not null
-- 创建表学生表 st8, 包含字段(id,name,gender)其中 name 不能为 NULL
create table st8 (
id int,
name varchar(20) not null,
gender char(1)
)

默认值

1
2
3
4
5
6
7
字段名 字段类型 default 默认值
-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州
create table st9 (
 id int,
 name varchar(20),
 address varchar(20) default '广州'
)

如果一个字段设置了非空与唯一约束，该字段与主键的区别?

  1. 主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列
  2. 自增长只能用在主键上

5.5 外键约束

什么是外键：在从表中与主表主键对应的那一列

  • 主表： 一方，用来约束别人的表
  • 从表： 多方，被别人约束的表

在从表中通过外键去主表中获取信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 新建表时增加外键：
[constraint] [外键约束名称] foreign key(外键字段名)  references 主表名(主键字段名)
-- 已有表增加外键：
alter table 从表 add [constraint] [外键约束名称] foreign key (外键字段名) references 主表(主
键字段名);

-- 1) 删除副表/从表 employee
drop table employee;

-- 2) 创建从表 employee 并添加外键约束 emp_depid_fk
-- 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references department(id)
)

删除外键

1
2
3
4
5
6
7
alter table 从表 drop foreign key 外键名称;

-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;

-- 在 employee 表存在的情况下添加外键
alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id);

外键的级联

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

级联操作语法 描述
on update cascade 级联更新，只能是创建表的时候创建级联关系
更新主表中的主键，从表中的外键列也自动同步更新
on delete cascade 级联删除 
1
2
3
4
5
6
7
8
9
10
11
-- 删除 employee 表，重新创建 employee 表，添加级联更新和级联删除
drop table employee;
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references 
department(id) on update cascade on delete cascade
)

6. 表与表之间的关系

6.1 表关系的概念

表与表之间的三种关系

三种关系 栗子
一对多 最常用的关系部门和员工
多对多 学生选课表 和 学生表， 一门课程可以有多个学生选择，一个学生选择多门课程
一对一 相对使用比较少。员工表 简历表， 公民表 护照表

6.2 一对多

一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品

一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

6.3 多对多

多对多（m:n） 例如：老师和学生，学生和课程，用户和角色

多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

6.4 一对一

一对一（1:1） 在实际的开发中应用不多.因为一对一可以创建成一张表

两种建表原则

一对一的建表原则 说明
外键唯一 主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE
外键是主键 主表的主键和从表的主键，形成主外键关系

7. 数据库设计

7.1 数据规范化

什么是范式

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响
建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式

三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）
满足最低要求的范式是第一范式（1NF）
在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推
一般说来，数据库只需满足第三范式(3NF）就行了

7.2 1NF

概念

数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项
即表中的某个列有多个值时，必须拆分为不同的列
简而言之，第一范式每一列不可再拆分，称为原子性

学号 姓名 班级
1 张三 一班
2 李四 二班
3 王五 三班

7.3 2NF

概念

在满足第一范式的前提下，表中的每一个字段都完全依赖于主键
所谓完全依赖是指不能存在仅依赖主键一部分的列
简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列
当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况
比如有一个主键有 两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式

第二范式的特点：

  1. 一张表只描述一件事情
  2. 表中的每一列都完全依赖于主键

借书证表

学生证号 学生证名称 学生证办理时间 借书证号 借书证名称 借书证办理时间

分成两张表

学生证号 学生证名称 学生证办理时间
借书证号 借书证名称 借书证办理时间

7.4 3NF

概念

在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键
简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递依赖于主键
所谓传递依赖，指的是如果存在”A → B → C”的决定关系，则 C 传递依赖于 A
因此，满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列 x → 非主键列 y

学生信息表

学号 姓名 年龄 所在学院 学院地点

存在传递的决定关系： 学号--->所在学院--->学院地点

拆分成两张表

学号 姓名 年龄 所在学院的编号(外键)
学院编号 所在学院 学院地点

7.5 三大范式小结

范式 特点
1NF 原子性：表中每列不可再拆分
2NF 不产生局部依赖，一张表只描述一件事情
3NF 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键