一 规范化数据库
1.1 函数依赖
函数依赖:在一张表内,两个字段值之间的一一对应关系成为函数依赖
在形式上,函数依赖通常表示为X→Y,其中 X 是属性集合,Y 是属性集合。这表示在给定 X 的值时,可以唯一确定 Y 的值。
例如,考虑一个关系模式(表)R,其中有属性集合 {A, B, C}。如果对于每个可能的 A 的值,都存在唯一的 B 和 C 的值,那么我们可以表示为 A→{B,C}。这表示 A 的值决定了 B 和 C 的值。
- 简单来说,知道一个属性就能知道其他属性,如:
1.1.1 完全函数依赖
如果 X 的任意真子集不能唯一决定 Y,但 X 本身能够唯一决定 Y,那么称 X 对 Y 有完全函数依赖。
考虑一个关系模式(表)R,其中有属性集合 {A, B, C}。如果对于每个可能的 A 的值,都存在唯一的 B 和 C 的值,但如果我们去掉 A 中的任何一个属性,就不能唯一确定 B 和 C 的值,那么我们可以表示为A→{B,C} 是一个完全函数依赖。
- 简单来说:一个属性只依靠另一个属性或者多个属性确认
完全函数依赖的性质
-
唯一性: 如果 X→Y 是一个完全函数依赖,那么在关系模式 R 中不能存在其他属性集合 Z,使得 X→Z 也是一个函数依赖。
-
合并性: 如果 X→Y 是一个完全函数依赖,而 Y 是属性集合 Z 的子集,那么X→Z 也是一个完全函数依赖。
1.1.2 部分函数依赖
如果 X 的某个真子集能够唯一决定 Y,但 X 本身不能唯一决定 Y,那么称 X 对 Y 有部分函数依赖。
考虑一个关系模式(表)R,其中有属性集合 {A, B, C}。如果对于每个可能的 A 的值,都存在唯一的 B 和 C 的值,但如果我们去掉 A 中的任何一个属性,就不能唯一确定 B 和 C 的值,那么我们可以表示为 A→{B,C} 是一个部分函数依赖。
简单来说,一个集合的属性只部分取决于另一个集合的部分属性
部分函数依赖的性质:
-
唯一性: 如果X→Y 是一个部分函数依赖,那么在关系模式 R 中不能存在其他属性集合 Z,使得 X→Z 也是一个函数依赖。
-
合并性: 如果 X→Y 是一个部分函数依赖,而 Y 是属性集合 Z 的子集,那么X→Z 也是一个部分函数依赖。
1.1.3 传递函数依赖
如果 X 对 Y 有部分函数依赖,而 Y 又对 Z 有函数依赖,那么称 X 对 Z 有传递函数依赖。
考虑一个关系模式(表)R,其中有属性集合 {A, B, C}。如果A→B 且 B→C,但 A!→C,那么我们可以说 C 对 A 有传递函数依赖。
传递函数依赖的性质:
-
传递性: 如果 X→Y 且 Y→Z,那么 Z 对 X 有传递函数依赖。
-
自反性: 如果 X→Y,那么 Y 对 X 有传递函数依赖。
1.2 范式
1.2.1 第一范式
在同一张表中同类字段不重复出现
不符合规范:学科和成绩重复出现
符合规范:
1.2.2 第二范式
一张表在满足第一范式的基础上,每个“非关键字”字段仅仅函数依赖于主键
不符合规范:主键是(学号,学科号),课程名不止函数依赖于(学号,学科号),还函数依赖于学科号,不满足第二范式
符合规范:
1.2.3 第三范式
一张表在满足第三范式的基础上,不存在“非关键字”字段函数依赖于任何其他“非关键字”字段
不符合规范:非关键字“邮编”函数依赖于非关键字“居住地”
符合规范:
二 DDL 数据定义语言
2.1 创建数据库
2.1.1 创建数据库
方式一:
CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];create database test1 character set utf8mb4 collate utf8mb4_general_ci;
方式二:
CREATE DATABASE [IF NOT EXISTS] database_name
[CHARSET=charset_name]
[COLLATE=collation_name];create database test2 charset=utf8mb4 collate=utf8mb4_general_ci;
注:
- charset为字符集
字符集 字符占的空间(字节) 说明 GBK 2 支持中文,但是不是国际通用字符集 UTF-8 3 支持中英文混合场景,是国际通用字符集 latin1 1 MySQL默认字符集 utf8mb4 4 完全兼容UTF-8,用四个字节存储更多的字符
- collate为字符集的校对规则
2.1.2 使用数据库
use 数据库名;
use test;
2.1.3 显示数据库
显示创建的数据库:
show create database 数据库名;
show create database test;
显示创建的数据库:
show databases;
2.1.4 修改数据库
使用alter database,其他的和create一致
alter database test
charset=utf8mb4
collate=utf8mb4_general_ci;
2.1.5 删除数据库
drop database [if exists] 数据库名;
drop database test;
2.2 创建表结构
2.1.1 创建全新表
create table 表名
列名 列属性;
方式一:在字段后面直接新增主键和外键
create table teacher( name char(6) not null primary key, age int not null default '25', gender varchar(2) references gender(gender), decribe text);
方式二:在末尾添加主键和外键
create table teacher( name char(6) not null, age int not null default 25, gender varchar(2), description text, primary key(name), constraint FK_Gender foreign key(gender) references gender(gender));
2.1.2 显示表结构
查看当前数据库的所有表
show tables;
显示表结构
describe 表名;
2.1.3 复制表结构
复制结构
create table 表名
like 已有表名
create table copy_student like student;
复制结构和数据(不会复制索引和完整性约束)
create table 表名
as (select * from 已有表名)
create table copy_gender as(select * from gender);
2.3 修改表结构
alter table 表名
add 列名 列属性 //增加新列
| modify 列名 列属性 //修改列的数据类型
| alter 列名 {set default值 | drop default值} //修改列的默认值
| change column 列名 原列名 列属性 //修改列名
| drop column 列名 //删除列
| rename {to} 新表名 //重命名表
alter table student
add address varchar(30);
alter table student
modify name varchar(8);
alter table student
alter age set default 1;
alter table student
change column id sid int;
alter table student
drop column gender;
alter table new_student rename to student;
注:重命名表还有一种方式
rename table 表名 to 新表名
rename table student to new_student;
注:添加约束也可以修改
alter table copy_student add constraint FK_Gender foreign key(gender) references gender(gender); alter table gender add primary key(gender); alter table gender drop primary key; alter table gender drop foreign key FK_Gender;
2.4 删除表结构
drop table 表名;
drop table copy_gender;
2.5 设置索引
2.5.1 索引的概念
索引本质上是数据库表中字段值的复制,该字段成为索引的关键字
2.5.2 索引的特点
- 一个表可以有多个索引
- 索引可以是字段的组合
- 索引只能来自于一个表内的字段
- 需要额外的存储空间
2.5.3 创建索引
创建表的同时创建索引
create table 表名(
字段名 数据类型[约束条件]
[unique | fulltext] index [索引名] (字段名[(长度)] [asc | desc])
)engine=存储引擎类型 default charset=字符集类型;
三 DML数据操纵语言
3.1 增
3.2 改
3.3 删
四 DQL数据查询语言
4.1 基本语法
select 字段列表
from 数据源
[where 条件表达式]
[group by 分组字段 [having 条件表达式] ]
[order by 排序字段 [asc |desc]]
- from用于指定检索的数据源,可以是表或者视图
- where用于指定记录的过滤条件
- group by用于对检索的数据进行分组
- having通常和group by搭配使用,用于过滤分组后的统计信息
- order by用于对检索的数据进行排序处理,默认升序asc
4.2 select...from
4.2.1 查询一个字段
select name from student;
4.2.2 查询多个字段
使用逗号分隔字段
select sid,name,age from student;
4.2.3 查询所有字段
使用*表示全部
select * from student;
4.2.4 别名
使用as进行命名(as可省略)
select s.sid,s.name from student as s;
4.3 where
4.3.1 单一条件
select name from student where sid=1001;
4.3.2 多条件
(1)逻辑非!
select sid,name from student where sid!=1001;
(2) 逻辑与and(&&)
select * from student where sid=1004 and name='何岚'; 或者 select * from student where sid=1004 && name='何岚';
(3)逻辑或or(||)
select * from student where sid=1001 or sid=1002; 或者 select * from student where sid=1001 || sid=1002;
(4)is null
- 判断表达式的值是否为空值
select * from student where address is null;
- 判断表达式的值是否不为空
select * from student where address is not null;
4.4 聚合函数
4.4.1 count()
用于统计结果集中记录的行数
假设我要统计班级成员人数(sid在表中总共有六个,所以结果就是六个)
一般使用id统计,因为id一般是唯一的
select count(sid) from student;
注:聚合函数一般都是添加别名,方便查看
select count(sid) as number from student;
上述语句是统计sid字段不为 NULL 的记录有多少个。也就是说,如果某一条记录中的 sid字段的值为 NULL,则就不会被统计进去。
如果要统计有多少行记录的情况下,可以使用*或者1
count(*)相当于count(0),在效果上和count(*)是一样的
select count(*) from student; 或者 select count(1) from student;
4.4.2 sum()
用于对数值型字段的值累加求和
比如我要查询学号为1001的成绩
select sum(score) from score where sid=1001;
查询全部学生的成绩(group by后续会讲到)
select sid,sum(score) from score group by sid;
4.4.3 avg()
查询学号为1001的学生的平均成绩
select avg(score) from score where sid=1001;
4.4.4 max()和min
max:统计数值型字段值的最大值
查询语文最高分
select max(score) from score where subject='语文';
min:统计数值型字段值的最小值
查询数学最低分
select min(score) from score where subject='数学';
4.5 group by
将查询结果按照某个字段(或多个字段)进行分组(字段值相同的记录作为一个分组)
4.5.1 group by与聚合函数
查询学生的平均成绩
select sid,avg(score) from score group by sid;
4.5.2 group by 与having子句
having子句用于设置分组或聚合函数的筛选条件
查询总分大于188的学生信息
select sid,sum(score) from score group by sid having sum(score)>185;
在完整的句子中,很容易把where和having的功能弄混
首先where子句对结果集进行筛选,接着group by子句对where子句的输出分组,最后having子句从分组的结果中再进行筛选。
查询学号不为1006且总分大于186的学生信息
select sid,sum(score) from score where sid!=1006 group by sid having sum(score)>186;
4.6 order by
对结果集进行排序
4.6.1 asc
对结果集进行升序排序
注:order by默认的排序方式就是升序asc
查询班级成员语文成绩(按升序排序)
select * from score where subject='语文' order by score; 等同于 select * from score where subject='语文' order by score asc;
4.6.2 desc
对结果集进行降序排序
查询班级成员的数学成绩(按降序排序)
select * from score where subject='数学' order by score desc;
4.7 distinct和limit
4.7.1 distinct
过滤结果集中的记录
使用方式:再字段名前加上distinct关键字
如:
select distinct * from score where sid=1001;
4.7.2 limit
查询某几行记录
使用方式:limit start,length
- start就是起始记录
- length就是长度
查询score表的前三行记录
select * from score limit 0,3;
4.8 模糊查询
4.9 多表查询
4.10 合并结果集
4.11 子查询