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

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