MySQL_Ch5

1.数据库设计规范 —— 偏向于数据库研究（表结构）

范式（Normal Farmat）

• 范式（Normal Farmat）（三个）目的：减少冗余数据（重复性数据）
  – 宏观对应整体的数据库系统来说，是为了解决数据库的存储和优化

  • 范式1：
    – 例如：设计一个学校假期时间表

    编号	学校名称	起始日期和结束日期
    1	x1	20000724, 20000928
    2	x2	20000729, 20000929

  • 起始日期和结束日期 属性可以分割的，属性（列）具有原子性

    编号	学校名称	起始日期	结束日期
    1	x1	20000724	20000928
    2	x2	20000729	20000929

  • 范式1：要求属性（列、字段）具有原子性，不可再分割

    • 范式2：解决的是表设计中不允许出现部分依赖
      – 教师授课表

教师id	姓名	性别	课程名称	授课的地点
1	张三	男	oracle	xx1
2	李四	女	java	xx2

• 每一张表的第一列（通常都是id） 主键约束（简称为主键）
• 后面的所有列都要依赖主键
• 解决方式1 将教师和性别组成一张表，课程和授课地点组成另一张表
• 解决方式2 复合主键（两列组成主键） 实际中很少使用复合主键
  • 采用第一种方式，分离原有表结构
    — 教师表

教师id	姓名	性别	课程id
1	张三	男	001
2	李四	女	002

— 授课地点表

课程id	课程名称	授课的地点
001	oracle	xx1
002	java	xx2

第二中表示法：
— 教师表

教师id	姓名	性别
1	张三	男
2	李四	女

— 授课地点表

课程id	课程名称	授课的地点
001	oracle	xx1
002	java	xx2

– 教师与授课地点关联表

id	教师id	课程id
0101	1	002
0202	2	001

– 范式3：需要满足于第一和第二的基础之上
– 解决是表设计中出现传递依赖的问题
A <-- B
C
D
A<—B<—C

— 教师表

教师id	姓名	性别
1	张三	男
2	李四	女

— 授课地点表

课程id	课程名称	授课的地点
001	oracle	xx1
002	java	xx2

– 教师与授课地点关联表

id	教师id	课程id
0101	1	002
0202	2	001

• 实际中表结构最终设计为范式2或者范式3其实都是可以的
• 如果表比较多，建议遵循三个范式，如果表比较少，可以遵循范式2
• 总结
  • 范式1属性不可再分割
  • 范式2所有列全部依赖主键
  • 范式3必须是直接依赖

– 2、数据定义语句（DDL） 数据操作语句（DML）
– 针对数据库中所有的结构 针对具体的数据的
– 基本操作

• 创建数据库

create database if not exists test;

• 切换数据库

use mydata;

• 删除数据库

drop database test; 

– 新增表结构
– 格式 ：

create table [if not exists] 表名(
    列名1 数据类型,
    列名2 数据类型,
    列名3 数据类型,
    ...
    列名n 数据类型 -- 最后一行不要有逗号
)[表选项];

• 表选项：
  – 1）字符集
  – 2）校对集设定
  – 3）存储引擎

– 隐式写法，不需要指明表所在的数据库

create table if not exists student(
    sid int,
    sname varchar(10),
    age int,
    score int
)charset utf8;

– 显式写法，需要指明表所在的数据库

create table if not exists mydata.student(
    sid int,
    sname varchar(10),
    age int,
    score int
)charset utf8;

– 修改表结构

• 1）修改表本身
  – 修改表名 rename table 旧表名字 to 新名字

rename table student to stu; 

• 2）修改表的属性

alter table 表名 add [column] 列名 数据类型 [位置]

– 增加一列

alter table stu add grade int;

– 修改原有列

alter table stu modify sname char(10) after sid;

– char 固定长度字符类型
– varchar 可变长度字符类型
– 修改列名

alter table stu change grade class varchar(10);

– 删除列

alter table stu drop age;

– 如果表中有数据，那么删除列会清空该列所有的数据，
– 而且数据不可恢复，慎重使用

• 3）删除表
  – 数据不可恢复，慎重使用
  – drop table 表名1, 表名2,…;

drop table stu;

– 3、约束
– 是一种表的结构，是为了约束列中的数据的
– 通常来说，一般每张表的第一列（编号）我们会设置为主键+自增
– mysql
– 约束类型
主键 primary key
– 主键约束等价于 唯一+非空组合
– 主键列中数据是不允许重复且不允许出现空值
– 在所在的列上直接建立对应的约束

create table temp(
   id int primary key,
   name varchar(20)   
);

– 复合主键

create table temp(
   id int,
   name varchar(20),
   pwd varchar(20),
   primary key(id,name)
);

– 删除主键约束，叫做修改表的结构中的主键

alter table temp drop primary key;

– 追加主键约束

alter table temp add primary key(id);

– 修改 实际中一般不使用

alter table temp modify name varchar(20) primary key;

外键 foreign key
– 保证一个或者两个表之间的参照完整性
– 通常来讲，一张表中的最后一列（外键）是另一张表中的主键
– 主表

create table temp(
   id int primary key,
   name varchar(20)   
);

– 副表

create table temp2(
   id int,
   name varchar(20),
   classes_id int,
   foreign key(classes_id) references temp(id)
); 

– dept表应该是emp中deptno列数据的一个参考表

– 删除外键约束

alter table temp2 drop foreign key classes_id;

– 追加外键

alter table temp2 add foreign key(classes_id) 
references temp(id);

唯一 unique
– 数据不能重复

create table temp(
   id int primary key,
   name varchar(20),
   pwd varchar(20),
   age int unique,
   unique(name,pwd)
);

alter table temp add unique(name);
alter table temp modify name varchar(20) unique;
alter table temp drop index name;

– 有两个null ，不算重复

非空 not null

create table temp(
   id int primary key,
   name varchar(20) not null  
);

– 增加非空 区别于前面的

alter table temp modify name varchar(20) not null;

– 取消

alter table temp modify name varchar(20) null;

alter table temp modify name varchar(20) default 'abc' null;

自增 auto_increment
– DML insert into 将数据添加到表中
– 如果设置了自增约束，则添加数据后，
– 主键的值会按照一定的规律自增

默认 default 实际中一般没有实际意义

create table emp1 like emp;-- 复制表结构