MySQL组织表设计_mysql表的设计

宋利兴__2011/09/13

select[distinct]列名….from表名

注意事项

可以替代列名, 表示所有列, 但是通常我们为了提高代码的可读性, 不使用*

DISTINCT为过滤重复记录

1.      表的设计

1.1.    单独的实体

public class User{

private int id;

private String name;

private int age;

}

user

id

name

age

1

张三

18

2

李四

20

3

王五

19

1.2.    一对多、多对一

foreign key

public class Employee {

private int id;

private String name;

private Department department;

}

public class Department {

private int id;

private String name;

private Set employees;

}

一对多

多对一

department                                     employee

id

name

1

开发部

2

市场部

id

name

department_id

1

张三

1

2

李四

1

3

王五

2

多的一方设置外键

create table department(

id int primary key auto_increment,

name varchar(20)

);

create table employee(

id int primary key auto_increment,

name varchar(20),

department_id int,

constraint department_id_fk foreign key(department_id) references department(id)

);

insert into department(name) values('开发部');

insert into department(name) values('市场部');

insert into employee(name, department_id) values('张三',1);

insert into employee(name, department_id) values('李四',1);

insert into employee(name, department_id) values('王五',2);

1.3.    一对一

public class Husband {

private int id;

private String name;

private Wife wife;

}

public class Wife {

private int id;

private String name;

private Husband husband;

}

一对一

foreign key

unique husband                                      wife

id

name

1

张三

2

李四

id

name

husband_id

1

冰冰

2

2

志玲

1

独立外键, 没有依赖关系, 两个表的对象都可以独立存在.

create table husband(

id int primary key auto_increment,

name varchar(20)

);

create table wife(

id int primary key auto_increment,

name varchar(20),

husband_id int,

constraint husband_id_fk foreign key(husband_id) references husband(id)

);

insert into husband(name) values('张三');

insert into husband(name) values('李四');

insert into wife(name, husband_id) values('冰冰',2);

insert into wife(name, husband_id) values('志玲',1);

public class Person {

private int id;

private String name;

private IdCard idCard;

}

public class IdCard {

private int id;

private String num;

private Person person;

}

一对一

foreign key

person                                        idcard

id

name

1

张三

2

李四

id

num

2

110123199009091234

1

120123200001011234

主键即外键, 分为主表和从表, 从表依赖于主表, 从表中的对象不能单独存在.

注意从表的主键, 不能自动增长.

create table person (

id int primary key auto_increment,

name varchar(20)

);

create table idcard (

id int primary key,

num varchar(20),

constraint id_fk foreign key(id) references person(id)

);

insert into person(name) values('张三');

insert into person(name) values('李四');

insert into idcard(id,num) values(2,'110123199009091234');

insert into idcard(num,id) values('110123199009091234', 1);

1.4.    多对多

public class Student {

private int id;

private String name;

private Set teachers;

}

public class Teacher {

private int id;

private String name;

private Set students;

}

多对多

foreign key

foreign key

student                                                  student_teacher                             teacher

id

name

1

张三

2

李四

3

王五

student_id

teacher_id

1

1

2

1

2

2

3

2

id

name

1

张孝祥

2

黎活明

用一张关系表保存多对多的关系, 有两列分别引用两张表的主键, 并且这两列组合起来成为联合主键

create table student (

id int primary key auto_increment,

name varchar(20)

);

create table teacher (

id int primary key auto_increment,

name varchar(20)

);

create table student_teacher(

student_id int,

teacher_id int,

primary key(student_id, teacher_id),

constraint student_id_fk foreign key(student_id) references student(id),

constraint teacher_id_fk foreign key(teacher_id) references teacher(id)

);

insert into student(name) values('张三');

insert into student(name) values('李四');

insert into student(name) values('王五');

insert into teacher(name) values('zxx');

insert into teacher(name) values('lhm');

insert into student_teacher values(1,1);

insert into student_teacher values(2,1);

insert into student_teacher values(2,2);

insert into student_teacher values(3,2);