一、SQL中多表设计
1、一对多
create table DEPARTMENT(
 ID int primary key,
 NAME varchar(100) not null
);

create table EMPLOYEE(
 ID int primary key,
 NAME varchar(100) not null,
 SALARY float(8,2),
 DEPART_ID int,
 constraint DEPART_ID_FK foreign key (DEPART_ID) references DEPARTMENT(ID)
);
2、多对多
create table TEACHER(
 ID int primary key,
 NAME varchar(100) not null,
 SALARY float(8,2)
);
create  table STUDENT(
 ID int primary key,
 NAME varchar(100) not null,
 GRADE varchar(100)
);
create table TEACHER_STUDENT(
 T_ID int,
 S_ID int,
 primary key(T_ID,S_ID),
 constraint T_ID_FK foreign key (T_ID) references TEACHER(ID),
 constraint S_ID_FK foreign key (S_ID) references STUDENT(ID)
);
3、一对一
a、按照外键关联(用一对多模拟的)
create table PERSON1(
 ID int primary key,
 NAME varchar(100),
 GENDER varchar(100)
);
create table ID_CARD1(
 ID int primary key,
 NUMBER varchar(100),
 PERSON_ID int unique,
 constraint PERSON_ID_FK foreign key (PERSON_ID) references PERSON1(ID)
);
b、按照主键关联
create table PERSON2(
 ID int primary key,
 NAME varchar(100),
 GENDER varchar(100)
);
create table ID_CARD2(
 ID int primary key,
 NUMBER varchar(100),
 constraint PERSON_ID_FK1 foreign key (ID) references PERSON2(ID)
);
二、多表查询

+----+----------+------+
| id | name     | city |
+----+----------+------+
|  1 | 陈冠希   | 香港     |
|  2 | 李宗瑞         | 台北    |
|  3 | 苍井空        | 东京     |
|  4 | 钟欣桐        | 香港     |
|  5 | 芙蓉姐姐       | NULL |
+----+----------+------+

+----+--------------+---------+-------------+
| id | order_number | price   | customer_id |
+----+--------------+---------+-------------+
|  1 | 0001         |  100.00 |           1 |
|  2 | 0002         |  200.00 |           1 |
|  3 | 0003         |  300.00 |           1 |
|  4 | 0004         |  100.00 |           2 |
|  5 | 0005         |  200.00 |           3 |
|  6 | 0006         |  100.00 |           4 |
|  7 | 0007         | 1000.00 |        NULL |
+----+--------------+---------+-------------+

1、连接查询（面试必面）
 
 基本语法：from LEFTTABLE 连接类型(cross join|inner join|left [outer] join|right [outer] join|UNION) RIGHTTABLE [on 连接条件] [where 过滤条件]

 1.1交叉连接查询(cross join)
  结果：返回两张表的笛卡尔积。（5*7=35）
  select * from CUSTOMER cross join ORDERS;
 1.2内连接查询
  隐式内连接：不适用连接类型和on关键字。连接条件用where来连接