首页 > 代码库 > 数据库设计
数据库设计
一、定义表的约束
一】表的完整性
1》实体完整性：每条记录有一个唯一标识符，通常用无任何业务含义的字段表示
2》参照完整性：一张表的某个字段必须引用另一张表的某个字段值
3》域完整性：域即单元数据，域中的数值必须符合一定的规则
二】键的概念
1》主键：唯一的字段
2》组合主键：由多个字段组合起来，形成唯一字段
3》外键：针对多张表之间的关联，引用外来字段的本地字段叫做外键。
三】主键
语法：
1》定义主键约束
primary key:不允许为空，不允许重复
code:
1）单个主键
create table teacher( id int primary key, name varchar(20), birthday date );
2）组合主键
create table if not exists middle( sid int, tid int, constraint sid_FK foreign key(sid) references student(id), constraint tid_FK foreign key(tid) references teacher(id), primary key(sid,tid) //设置组合主键 );
特点：
1》主键不能重复
2》主键不能为空
3》主键在表中只有一个，要么1列，要么多列
2》删除主键
alter table tablename drop primary key；
3》定义主键自动增长(MySQL特有)
auto_increment:MySQL特有/UUID类生成
注意：
1》默认从1开始，该ID值与表同生亡。删除完记录并不影响序号。
2》多人项目中通常由UUID类生成唯一的主键值，便于多个表合并时依然保持实体完整性。
code:
create table teacher( id int primary key auto_increment, name varchar(20), birthday date );
4》定义唯一约束
关键字：
unique:非NULL值外，不能重复。
code:
create table teacher( id int primary key auto_increment, name varchar(20) unique,//唯一 birthday date );
注意：
1》非NULL值外，不能重复
2》可以插入多个NULL。
3》‘null‘空串和null是不同的。
5》定义非空约束
关键字：
not null：不能插入null值。
code：
create table teacher( id int primary key auto_increment, name varchar(20) not null,//非空 birthday date );
注意：
主键约束=非null约束+唯一约束。
6》定义外键约束
关键字：
constraint pid_FK foreign key(pid) references Person(id)
pid_FK是给数据库的系统看的！！一般格式就是如此：外键名_FK
详见图！！！
二、表的关联设计
一】一对一(人和身份证)
创建：先创建被引用的表，再创建引用的表。
删除：先删除引用的表，再删除被引用的表。
1》(方案一)单独用一个字段来表示外键：
create table card( id int primary key auto_increment, location varchar(20) not null, pid int, constraint pid_FK foreign key(pid) references person(id) );
2》(方案二)用主键来表示外键:此时，id既是主键，又是外键。
create table card( id int primary key auto_increment, location varchar(20) not null, constraint id_FK foreign key(id) reference person(id) );
二】一对多/多对一(部门和员工)
外键放置在多方。
code：
create table department( id int primary key auto_increment, name varchar(20) not null ); insert into department(name) values(‘软件部‘); insert into department(name) values(‘销售部‘); create table employee( id int primary key auto_increment, name varchar(20) not null, did int, constraint did_FK foreign key(did) references department(id) ); insert into employee(name,did) values(‘jack‘,1); insert into employee(name,did) values(‘marry‘,1);
问题：查询"软件部"的所有员工（组合式）
select d.name as 部门名,e.name as 员工名
from department as d,employee as e
where d.name = ‘软件部‘ and e.did=d.id;
思考：还有没有其它方法？
分解：
(1)select id from department where name=‘软件部‘;
(2)select name from employee where did = 1;
(总)嵌入式SQL
select name as 员工
from employee
where did = (
select id
from department
where name=‘软件部‘
);
三】多对多(老师和同学)
外键放置在关联表中，即：单独创建一个关系表。
即将一个多对多拆分成两个一对多关系。
code:
drop table if exists middle; drop table if exists student; drop table if exists teacher; create table if not exists student( id int primary key auto_increment, name varchar(20) not null ); insert into student(name) values(‘jack‘); insert into student(name) values(‘marry‘); create table if not exists teacher( id int primary key auto_increment, name varchar(20) not null ); insert into teacher(name) values(‘赵‘); insert into teacher(name) values(‘蔡‘); create table if not exists middle( sid int, tid int, constraint sid_FK foreign key(sid) references student(id), constraint tid_FK foreign key(tid) references teacher(id), primary key(sid,tid)//组合主键 ); insert into middle(sid,tid) values(1,1); insert into middle(sid,tid) values(1,2); insert into middle(sid,tid) values(2,1); insert into middle(sid,tid) values(2,2);
问题？查询"赵"所教过的所有学员
select t.name as 老师, s.name as 学员
from teacher as t,student as s,middle as m
where t.name = ‘赵‘and m.sid=s.id and m.tid=t.id;
注意：
1）外键的值必须来源于所引用的表的任一主键值,或者为NULL。
【重点：】
1》组合查询的模式:
select 列出需要显示的字段
from 列出所设计到的所有表，建议写别名
while 业务条件 表关联条件
2》组合主键
primary key(column1,column2..)
数据库设计