MySQL_Ch5

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;-- 复制表结构

上一篇:seata实战


下一篇:Sqlserver取分组后的第一条数据