10000*365=27.3972603

0%

数据库设计

Posted on

目录

表设计

  • DDL（Data Definition Language）数据库模式定义语言
  • 表字段类型选择。
  • 3范式，三种表间关系：一对一，一对多，多对多。

表使用

  • 基本查询语句。
  • SQL语句优先级。
  • 链接查询：子查询，组合查询。
  • select…from…where…group by…having…order by..limit

DDL

库的创建
create database 库名；
create database if not existe 库名；

更改库的字符集
alter database books character set gbk；

库的删除
drop database if exists 库名；

表的创建

1
2
3
4
5
6
7
8
9
10
11
12
create table 表名（
    列名  列的类型【（长度）约束】
    列名  列的类型【（长度）约束】
   .......
    列名  列的类型【（长度）约束】）

create table book(
    id  int,
    bname varchar(20),
    prince double,
    authorid int,
    publishbate datetime)

表的修改
alter table 表名 add/drop/modify/change column 列名 列类型 约束；

  • 修改列名 alter table 表名 change column 旧列名 新列名 类型；
  • 修改列的类型及约束 alter table 表名 modify column 列名 要修改的类型
  • 添加新列 alter table author add column annual double；
  • 删除列 alter table author drop column annual ；
  • 修改表名 alter table author rename to bookauthor;
  • 表的删除 drop table if exists 表名；
  • 查看 show tables

  • 通用的写法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    drop database if exists 旧库名；

    create batabase 新库名；

    drop table if exists 旧表名；

    create table 表名（）；

    desc 表名 （查看表结构）

  • 表的复制 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    #1.仅仅复制表的结构
    create table 新表名  like  author；
    #2.复制表的结构+数据
    create table 新表名
    select *from 旧表名；
    #3.只复制部分数据
    create teble 新表名
    select 列名，..
    from 旧表名
    where 筛选条件；
    #4.仅仅复制某些结构
    create table 新表名 
    select 列名，..
    from 旧表名
    where  0（谁都不满足的条件）；

表字段类型

数值型

场景 类型 字节 举例 描述
- tinyint 1 - -
- smallint 2 - -
- mediumint 3 - -
- int
integer		 4
4		 - -
- bigint 1 8 -

  • 如何设置有符号和无符号
    默认是有符号，设置无符号追加unsigned

  • 插入数值超出整型范围
    报异常：out of range，并且插入的是临界值

  • 如果不设置长度会有默认的长度
    zerofill（零填充，默认无符号），显示结果的最大宽度，不过会在左边用0填充

小数

场景 类型 举例 描述
- dec
decimal		 dec(M,D)
decimal(M,D)		 定点型
- float
double		 float(M,D)
double(M,D)		 浮点型

  • M是整数部位加小数部位的总长度，D是小数点后保留的位数
    超过插入的是临界值

  • M和D都可省略，如果是decimal，M默认为10，D默认为0
    如果是float和double，则会根据插入的数值决定

  • 定点型精确度较高，要求插入数值精度较高优先用，如货币运算等则等
    其他一般用float就可以。

  • 选择类型越简单越好，保存数值越小越好

字符型

场景 类型 举例 描述
较短的文本 char
varchar		 char(M)
varchar(M)		 M表示最大的字符数，可省略，默认为1 固定长度的字符 比较耗费空间
M表示最大的字符数，不可省略 可变长度的字符 比较节省空间
较长的文本 text
blob		 - 较长的二进制数据

日期

场景 类型 举例 描述
- date - -
- datetime - datetime范围1000-1-1—-9999-12-31，不受时区影响
- timestamp - 受时区影响
- time - -
- year - -

常见约束

场景 字段 举例 描述
- not null - 非空，用于保证该字段的值不能为空，如姓名、学号
- default - 默认，用于保证该字段有默认值，如性别
- primary key - 主键，用于保证该字段的值具有唯一性，并且非空，如学号、员工编号
- unique - 唯一、用于保证该字段的值具有唯一性，可以为空，如座位号
- check - 检查约束，mysql中不支持，如年龄、性别
- foreign key - 外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。

  • 添加约束的时机：创建表时/修改表时

  • 约束的添加分类：

    • 列级约束：六大约束语法都支持但外键约束没有效果
    • 表级约束;除了非空、默认，其他的都支持

创建表时添加约束

添加列级约束

直接在字段名和类型后面追加 约束类型即可
只支持：默认、非空、主键、唯一

1
2
3
4
5
6
7
8
9
10
11
12
create table 表名（
         字段名   字段类型   列级约束，
       字段名  字段类型，
       表级约束
）
create table stuinfo（
    id  int primary  key,
    stuname  varchar(20)  not  null,
    gender   char(1)  check(gender='男' or gender = '女'），
    age  int default  18
   majorid  int foreign key references major（id）
）；

添加表级约束

1
2
3
4
5
6
7
8
9
10
11
12
13

create table stuinfo（
    id  int ,
    stuname  varchar(20) ,
    gender   char(1) ，
    age  int，
   majorid  int，
   
   constraint pk primary  key（id）,#主键
   constraint uq unique（seat），#唯一键
   constraint ck check(gender='男' or gender = '女'），
   constraint fk_syuinfo_major   foreign key(majorid) references major（id）#外键
);

在各个字段的最下面添加：<constraint 约束名> 约束类型（字段名）

主键与唯一的对比

  • 主键：保持唯一性 / 不允许为空/一个表中只至少有一个/两个列可以组合为一个主键但不推荐
  • 唯一：保持唯一性 / 允许为空 /可以有多个/可以组合但不推荐

外键

  • 要求在从表设置外键关系

  • 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求

  • 主表的关联列必须是一个key（一般是主键或唯一）

  • 插入数据时，先插入主表数据再插入从表

  • 删除数据先删除从表再删除主表

修改表时添加约束

列级约束：alter table 表名 modify column 字段名 字段类型 新约束；

表级约束：alter table 表名 add <constriaint 约束名> 约束类型（字段名）<外键的引用>；

  • 添加非空约束 alter table 表名 modify column 列名 要修改的类型 not null

  • 默认约束 alter table 表名 modify column 列名 int default 18

  • 添加主键
    列级约束 alter table 表名 modify column 列名 int default key；
    表级约束 alter table 表名 add primary key(列名)

  • 添加唯一
    列级约束 alter table 表名 add primary key(列名) 要修改的类型 notnull
    表级约束 alter table 表名 add unique（列名）

  • 添加外键 alter table 表名 add foreign key（majorid） references major（id）

修改表时删除约束

删除非空 alter table 表名 modify column 列名 varchar（20） null；
删除默认约束 alter table 表名 modify column 列名 int；
删除主键 alter table 表名 drop primary key；
删除唯一 alter table 表名 drop index 列名；
删除外键 alter table 表名 drop foreign key 列名；

3范式

第一范式（1NF）

保证每列的原子性。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库满足了第一范式。

例如将地址的拆分：

编号 姓名 地址
1 张三 广东广州天河
2 李四 广东佛山禅城
3 王五 广东广州科学城
编号 姓名 省份 城市 地址
1 张三 广东 广州 天河
2 李四 广东 佛山 禅城
3 王五 广东 广州 科学城

第二范式（2NF）

保证一张表只描述一件事情。
在满足第一范式的基础上，数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖，也即所有非关键字段都完全依赖于任一组候选关键字。

举例：
| 学号 | 姓名 | 年龄 | 课程 | 成绩 | 学分 |
| :—–| :—–| :—-| :—-| :—-| :—-|
| 1 | 张三 | 20 | 数学 | 90 | 1 |
| 2 | 李四 | 20 | 语文 | 90 | 1 |
| 3 | 王五 | 20 | 英语 | 50 | 1 |

上表满足第一范式，即每个字段不可再分，但这张表的设计并不满足第二范式。因为这张表里面描述了两件事情：学生信息、课程信息，”学分”完全依赖于”课程名称”、”姓名”与”年龄”完全依赖于”学号”。这么做的后果是：

  • 数据冗余：同一门课程由n个学生选修，”学分”重复n-1次；同一个学生选修了m门课程，姓名和年龄重复m-1次

  • 更新异常：若调整了某门课程的学分，数据表中所有行的”学分”值都需要更新，否则会出现同一门课程学分不同的情况

  • 插入异常：假设要开一门新课程，暂时没有人选修，那么由于没有”学号”关键字，”课程”与”学分”也无法记录入数据库

  • 删除异常：假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，”课程”和”学分”也被删除了，显然，这最终可能会导致插入异常

优化后的表设计如下：
| 学号 | 姓名 | 年龄 |
| :—–| :—–| :—-|
| 1 | 张三 | 20 |
| 2 | 李四 | 20 |
| 3 | 王五 | 20 |

课程 学分
数学 1
语文 1
英语 1
学号 课程 成绩
1 数学 90
2 语文 90
3 英语 50

增加了表，将学生信息与课程信息通过一张中间表关联，很好地解决了上面的几个问题，这就是第二范式的中心—-保证一张表只讲一件事情。

第三范式（3NF）

数据库表中如果不存在非关键字段任一候选关键字段的传递函数依赖则符合第三范式，所谓传递函数依赖指的是如果存在”A–>B–>C”的决定关系，则C传递函数依赖于A。也就是说中的字段和主键直接对应不依靠其他中间字段，说白了就是，决定某字段值的必须是主键。

举例：

学号 姓名 年龄 学院地点 学院电话
1 张三 20 计算机学院 9号楼
2 李四 20 计算机学院 9号楼
3 王五 20 计算机学院 9号楼

从这张数据库表结构中可以看出：

  • “姓名”、”年龄”、”学院”和主键”学号”直接关联。

  • 但是”学院地点”、”学院电话”却不直接和主键”学号”相关联，和”学院电话”直接相关联的是”学院”。

  • 如果表结构这么设计，同样会造成和第二范式一样的数据冗余、更新异常、插入异常、删除异常的问题。

优化后的表设计：

学院 学院地点 学院电话
计算机学院 9号楼 11111
艺术学院 8号楼 11112
机电学院 7号楼 11113
学号 姓名 年龄 学院
1 张三 20 计算机学院
2 李四 20 计算机学院
3 王五 20 计算机学院

文章参考：
MySQL数据库三范式