表格之间的关系 一对多+多对多+一对一

一对多的关系

#添加一端的主键约
mysql> alter table dept add primary key(deptno) ;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0
#添加多端的逐渐啊约束
mysql> alter table emp add primary key(empno) ;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
#添加多端的外键约束
mysql> alter table emp add constraint fk_emp_dept foreign key(deptno) references dept(deptno) ;
Query OK, 14 rows affected (0.15 sec)
Records: 14  Duplicates: 0  Warnings: 0
#都添加成功了 这两个表格就产生了一对多的关系
#接下来就可以 做联合查询的事
mysql> desc emp ;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| empno    | int(5)      | NO   | PRI | NULL    |       |
| ename    | varchar(10) | YES  |     | NULL    |       |
| job      | varchar(10) | YES  |     | NULL    |       |
| mgr      | int(5)      | YES  |     | NULL    |       |
| hiredata | date        | YES  |     | NULL    |       |
| sal      | int(10)     | YES  |     | NULL    |       |
| comm     | int(5)      | YES  |     | NULL    |       |
| deptno   | int(5)      | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

mysql> desc dept ;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(5)      | NO   | PRI | NULL    |       |
| dname  | varchar(10) | YES  |     | NULL    |       |
| loc    | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#添加一个主键约束 teacher
mysql> alter table teacher add primary key(tid) ;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
#添加一个主键约束 student
mysql> alter table student add primary key(sid) ;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
#创建中间表 # 用来当作外键来存储上两表格的主键
mysql> create table teacher_student (
    -> tid int(5),
    -> sid int(5)
    -> ) ;
Query OK, 0 rows affected (0.05 sec)
#在中间的tid列设置成外键约束 参考 teacher 表中tid列
mysql> alter table teacher_student add constraint fk_tid foreign key(tid) references teacher(tid) ;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
#在中间的sid列设置成外键约束 参考 student 表中sid列
mysql> alter table teacher_student add constraint fk_sid foreign key(sid) references student(sid) ;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
#中间表的两个列添加为主键约束
mysql> alter table teacher_student add primary key(tid,sid) ;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher_student;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| tid   | int(5) | NO   | PRI | NULL    |       |
| sid   | int(5) | NO   | PRI | NULL    |       |
+-------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)

##创建两个表格
mysql> create table person (
    -> pid int(5),
    -> pname varchar(5),
    -> psex varchar(5),
    -> cardid int(18)
    -> )character set = utf8 ;
Query OK, 0 rows affected (0.06 sec)

mysql> create table idcard (
    -> cardid int(18),
    -> address varchar(20)
    -> )character set = utf8 ;
Query OK, 0 rows affected (0.07 sec)

#### idcard 表格中的cardid列设置为主键约束
mysql> alter table idcard add primary key(cardid) ;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
#person 表格中的cardid列设置z为主键约束
mysql> alter table person add primary key(cardid) ;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
#person 表格中的cardid列设置z为外键约束
mysql> alter table person add constraint fk_person foreign key(cardid) references idcard(cardid) ;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0