03_01 _ notes

Song lixing _ 2011/09/13

Select [distinct] column name .... From table name

Notes

It can replace column names, indicating all columns. However, to improve code readability, we do not use *

DISTINCT filters duplicate records

1. Table Design 1.1. Separate entities

Public classUser { Private intId; PrivateString name; Private intAge; }

 

User

 

Id	Name	Age
1	Zhang San	18
2	Li Si	20
3	Wang Wu	19

 

1.2. One-to-multiple, multiple-to-one

Foreign key

Public classEmployee { Private intId; PrivateString name; PrivateDepartment department; }

Public classDepartment { Private intId; PrivateString name; PrivateSet <Employee> employees; }

One-to-multiple

Many-to-one

 

 

Department employee

 

Id	Name
1	Development Department
2	Marketing Department

Id	Name	Department_id
1	Zhang San	1
2	Li Si	1
3	Wang Wu	2

 

Set Foreign keys for multiple parties

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 ('dashboard ');

Insert into department (name) values ('marketing department ');

Insert into employee (name, department_id) values ('zhang san', 1 );

Insert into employee (name, department_id) values ('Li si', 1 );

Insert into employee (name, department_id) values ('wang wu', 2 );

1.3. One-to-one

Public classHusband { Private intId; PrivateString name; PrivateWife wife; }

Public classWife { Private intId; PrivateString name; PrivateHusb and; }

One-to-one

 

Foreign key

 

 

Unique

Husband wife

 

Id	Name
1	Zhang San
2	Li Si

Id	Name	Husband_id
1	Bingbing	2
2	Zhiling	1

 

Independent Foreign keys with no dependency. Objects in both tables can exist independently.

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 ('zhang san ');

Insert into husband (name) values ('Li si ');

Insert into wife (name, husband_id) values ('bingbing ', 2 );

Insert into wife (name, husband_id) values ('ziling ', 1 );

 

 

Public classPerson { Private intId; PrivateString name; PrivateIdCard idCard; }

Public classIdCard { Private intId; PrivateString num; PrivatePerson person; }

One-to-one

 

Foreign key

 

Person idcard

 

Id	Name
1	Zhang San
2	Li Si

Id	Num
2	110123199009091234
1	120123200001011234

 

A primary key is a foreign key. It is divided into the master table and the slave table. The slave table depends on the master table, and the objects in the slave table cannot exist independently.

Note that the primary key of the slave table cannot be automatically increased.

 

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 ('zhang san ');

Insert into person (name) values ('Li si ');

Insert into idcard (id, num) values (2, '20140901 ');

Insert into idcard (num, id) values ('20140901', 1 );

1.4. many-to-many

Public classStudent { Private intId; PrivateString name; PrivateSet <Teacher> teachers; }

Public classTeacher { Private intId; PrivateString name; PrivateSet <Student> students; }

Many-to-many

 

Foreign key

Foreign key

 

 

Student student_teacher teacher

 

Id	Name
1	Zhang San
2	Li Si
3	Wang Wu

Student_id	Teacher_id
1	1
2	1
2	2
3	2

Id	Name
1	Zhang Xiaoxiang
2	Li fuming

 

 

 

A relational table stores many-to-many relationships. Two columns reference the primary keys of the two tables respectively, and these two columns are combined to form the joint primary key.

 

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 ('zhang san ');

Insert into student (name) values ('Li si ');

Insert into student (name) values ('wang wu ');

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