助您的企业征战互联网 - 苜蓿草网络

数据库设计

时间:2014-10-08 12:05     热度:563    作者:SkyGood     来源:互联网

一、定义表的约束

一】表的完整性

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

详见图!!!

 

二、表的关联设计

一】一对一(人和身份证)

mysql数据库

创建:先创建被引用的表,再创建引用的表。

删除:先删除引用的表,再删除被引用的表。

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)
);

 二】一对多/多对一(部门和员工)外键放置在多方。

 

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='软件部'
    );

三】多对多(老师和同学)    外键放置在关联表中,即:单独创建一个关系表。即将一个多对多拆分成两个一对多关系。


  

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..)

标签:  MYSQL  设计  数据库  主键