03_01_笔记
宋利兴__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<Employee> 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 |
|
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<Teacher> teachers; } |
|
public class Teacher { private int id; private String name; private Set<Student> 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);
