Mysql表的约束设计和关联关系设计
https://blog.csdn.net/u012750578/article/details/15026677
Mysql表的约束设计和关联关系设计
======================表的完整性======================
(1)实体完整性：每条记录有一个唯一标识符，通常用无任何业务含义的字段表示(主键)
(2)参照完整性：一张表的某个字段必须引用另一张表的某个字段值(外键)
(3)域完整性：域即单元数据，域中的数值必须符合一定的规则
定义主键约束
primary key:不允许为空，不允许重复
删除主键：alter table tablename drop primary key ;
定义主键自动增长(id,没有业务含义)
auto_increment(MySQL特有/UUID类生成)
定义唯一约束
unique
定义非空约束
not null
定义外键约束
constraint ordersid_FKforeign key(ordersid) references orders(id),
2 键的概念
(1)主键：只有唯一字段
(2)组合主键：由多个字段组合起来，形成唯一字段
(3)外键：针对多张表之间的关联
3 主键的特点
(1)主键不能重复
(2)主键不能为NULL
(3)auto_increment是MySQL特有的，默认从1开始，该ID值与表同生亡
(4)多人项目中，通常使用UUID来生成唯一的主键值，便于多个合并数据时依然保持实体完整性
--id主键
mysql> create table teacher(id intprimary key,
-> name varchar(20),
-> brithday date);
Query OK, 0 rows affected (0.61 sec)
mysql> select * from teacher;
Empty set (0.00 sec)
mysql> insert into teachervalues(1,'jack','1992-2-12');
Query OK, 1 row affected (0.11 sec)
mysql> select * from teacher;
+----+------+------------+
| id | name | brithday |
+----+------+------------+
| 1| jack | 1992-02-12 |
+----+------+------------+
1 row in set (0.00 sec)
mysql> desc teacher;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key |Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| brithday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
--主键不能重复,主键冲突,1代表第一列，即id列
mysql> insert into teachervalues(1,'marry','1996-2-12');
ERROR 1062 (23000): Duplicate entry '1' forkey 'PRIMARY'
mysql>
--主键不能为null
mysql> insert into teachervalues(null,'marry','1996-2-12');
ERROR 1048 (23000): Column 'id' cannot benull
-- 删除主键，主键在表只有一个，要么是一列，要么是多列
mysql> alter table teacher drop primarykey;
Query OK, 1 row affected (2.13 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc teacher;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key |Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| brithday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
--主键id自动增长
mysql> drop table if exists teacher;
Query OK, 0 rows affected (0.32 sec)
mysql> create table teacher(id intprimary key auto_increment,
-> name varchar(20),
-> birthday date);
Query OK, 0 rows affected (0.96 sec)
mysql> desc teacher;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key |Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into teacher(name,birthday)values('jack','1992-02-12');
Query OK, 1 row affected (0.06 sec)
mysql> insert intoteacher(name,birthday) values('marry','1992-4-8');
Query OK, 1 row affected (0.09 sec)
mysql> insert intoteacher(name,birthday) values('arry','1982-4-8');
Query OK, 1 row affected (0.14 sec)
mysql> select * from teacher;
+----+-------+------------+
| id | name | birthday |
+----+-------+------------+
| 1| jack | 1992-02-12|
| 2| marry | 1992-04-08 |
| 3| arry | 1982-04-08|
+----+-------+------------+
3 rows in set (0.00 sec)
--检查主键id增长的序列
mysql> delete from teacher whereid<=3;
Query OK, 3 rows affected (0.08 sec)
mysql> select * from teacher;
Empty set (0.00 sec)
mysql> insert intoteacher(name,birthday) values('jack','1992-02-12');
Query OK, 1 row affected (0.13 sec)
mysql> select * from teacher;
+----+------+------------+
| id | name | birthday |
+----+------+------------+
| 4| jack | 1992-02-12 |
+----+------+------------+
1 row in set (0.00 sec)
--多人项目中，通常使用UUID来生成唯一的主键值，便于多个合并数据时依然保持实体完整性
4 唯一约束的特点
(1)非NULL值不能重复
(2)可以插入多个NULL值
(3)'NULL'空串和NULL是不同的概念
mysql> drop table teacher;
Query OK, 0 rows affected (0.42 sec)
--唯一
mysql> create table teacher(id intprimary key auto_increment,
-> name varchar(20) unique,
-> birthday date);
Query OK, 0 rows affected (1.49 sec)
mysql> insert intoteacher(name,birthday) values('marry','2011-2-2');
Query OK, 1 row affected (0.11 sec)
mysql> insert intoteacher(name,birthday) values(NULL,'2011-1-1');
Query OK, 1 row affected (0.04 sec)
mysql> desc teacher;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key |Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | UNI | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
--违反唯一
mysql> insert intoteacher(name,birthday) values('marry','2011-2-2');
ERROR 1062 (23000): Duplicate entry 'marry'for key 'name'
5 非空约束特点
(1)不能插入NULL值
(2)主键约束=非NULL约束+唯一约束
--违返不为空约束
mysql> drop table if exists teacher;
Query OK, 0 rows affected (0.19 sec)
mysql> create table teacher(
-> id int primary keyauto_increment,
-> name varchar(20) not nullunique,
-> birthday date
-> );
Query OK, 0 rows affected (1.45 sec)
mysql> insert intoteacher(name,birthday) values(NULL,'2011-1-1');
ERROR 1048 (23000): Column 'name' cannot benull
6 外健特点
(1)外键值必须来源于所引用别一个表主键值，或NULL
*7 关联关系
(1)一对一(外健根业务有关)
(2)一对多或多对一(外键放置在多方)
(3)多对多(外健放置在关联表中，即将一个多对多拆分成二个一对多关系)
一对一关系一:
mysql> create table person(
-> id int primary keyauto_increment,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (1.16 sec)
mysql> insert into person(name)values('jack');
Query OK, 1 row affected (0.15 sec)
mysql> insert into person(name)values('marry');
Query OK, 1 row affected (0.06 sec)
--card中pid为外person的外键
mysql> create table card(
-> id int primary key auto_increment,
-> location varchar(20) notnull,
-> pid int,
-> constraint pid_FK foreignkey(pid) references person(id)
-> );
Query OK, 0 rows affected (1.55 sec)
mysql> insert into card(location,pid)values('BJ',1);
Query OK, 1 row affected (0.11 sec)
mysql> insert into card(location,pid)values('GZ',2);
Query OK, 1 row affected (0.39 sec)
--外键pid可以插入NULL
mysql> insert into card(location,pid)values('CS',NULL);
Query OK, 1 row affected (0.05 sec)
--显示person与card表中的数据
mysql> select * from person;
+----+-------+
| id | name |
+----+-------+
| 1| jack |
| 2| marry |
+----+-------+
2 rows in set (0.00 sec)
mysql> select * from card;
+----+----------+------+
| id | location | pid |
+----+----------+------+
| 1| BJ | 1 |
| 2| GZ | 2 |
| 3| CS | NULL |
+----+----------+------+
3 rows in set (0.00 sec)
--插入pid为3的出错误，外键pid必须来源另一表的主键值或为NULL
mysql> insert into card(location,pid)values('NJ',3);
ERROR 1452 (23000): Cannot add or update achild row: a foreign key constraint fails (`mydb2`.`card`, CONSTRAINT `pid_FK`FOREIGN KEY (`pid`) REFERENCES `person` (`id`))
注意:外键值必须来源于所引用别一个表主键值，或NULL
--有外键关系的行不能删除，需先删除其关联的外键关系,再进行删除主键关系的数据，与创建的时候相反
mysql> deletefrom person where name='jack';
ERROR 1451(23000): Cannot delete or update a parent row: a foreign key constraint fails(`mydb2`.`card`, CONSTRAINT `pid_FK` FOREIGN KEY (`pid`) REFERENCES `person`(`id`))
一对一关系方式二:主键当作另一个表的外键
例下面的card表的id既是主键又是外键
mysql> drop table if exists card;
Query OK, 0 rows affected (0.22 sec)
mysql> drop table if exists person;
Query OK, 0 rows affected (0.22 sec)
mysql> create table person(
-> id int primary key auto_increment,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.90 sec)
mysql> insert into person(name)values('jack');
Query OK, 1 row affected (0.07 sec)
mysql> insert into person(name)values('marry')
-> ;
Query OK, 1 row affected (0.10 sec)
--使用主键作为外键
mysql> create table card(
-> id int primary keyauto_increment,
-> location varchar(20) notnull,
-> constraint id_FK foreignkey(id) references person(id)
-> );
Query OK, 0 rows affected (0.86 sec)
mysql> insert into card(location)values('BJ');
Query OK, 1 row affected (0.06 sec)
mysql> insert into card(location)values('CS');
Query OK, 1 row affected (0.18 sec);
--id为3, 不符合参照完整性,3在person表中打不到
mysql> insert into card(location) values('GZ');
ERROR 1452 (23000): Cannot add or update achild row: a foreign key constraint fails (`mydb2`.`card`, CONSTRAINT `id_FK`FOREIGN KEY (`id`) REFERENCES `person` (`id`))
--NULL也不行，因为这里id为一个非空的属性
mysql> insert into card(location)values(NULL);
ERROR 1048 (23000): Column 'location'cannot be null
==========================一对多关系=============================
设计方案:
一方做一个容器，多方做一个引用
mysql> create table department(
-> id int primary keyauto_increment,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (1.70 sec)
mysql> insert into department(name)values('软件部');
Query OK, 1 row affected (0.10 sec)
mysql> insert into department(name)values('销售部');
Query OK, 1 row affected (0.06 sec)
mysql> create table employee(
-> id int primary keyauto_increment,
-> name varchar(20) not null,
-> did int,
-> constraint did_FK foreignkey(did) references department(id)
-> );
Query OK, 0 rows affected (1.02 sec)
mysql> insert into employee(name,did)values('jack',1);
Query OK, 1 row affected (0.12 sec)
mysql> insert into employee(name,did)values('marry',1);
Query OK, 1 row affected (0.13 sec)
mysql>
mysql> select * from department;
+----+-----------+
| id | name |
+----+-----------+
| 1| 软件部 |
| 2| 销售部 |
+----+-----------+
2 rows in set (0.00 sec)
mysql> select * from employee;
+----+-------+------+
| id | name | did |
+----+-------+------+
| 1| jack | 1 |
| 2| marry | 1 |
+----+-------+------+
2 rows in set (0.00 sec)
--查询软件部的员工(利用子查询)
mysql> select * from employee wheredid=(select id from department where name='软件部');
+----+-------+------+
| id | name | did |
+----+-------+------+
| 1| jack | 1 |
| 2| marry | 1 |
+----+-------+------+
2 rows in set (0.05 sec)
--利用多表查询
mysql> select e.name as 员工姓名,d.name as 部门名称 from employee e,department d where d.name='软件部';
+--------------+--------------+
| 员工姓名 | 部门名称 |
+--------------+--------------+
| jack | 软件部 |
| marry | 软件部 |
+--------------+--------------+
2 rows in set (0.01 sec)
============================多对多方案=============================
--创建的时候，先创建两边的，再创建中间的
--删除的时候先删除中间的，再删除两边的
mysql> drop table if exists middle;
Query OK, 0 rows affected, 1 warning (0.00sec)
mysql> drop table if exists student;
Query OK, 0 rows affected (0.25 sec)
mysql> drop table if exists teacher;
Query OK, 0 rows affected (0.33 sec)
mysql> create table if not existsstudent(
-> id int primary keyauto_increment,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (1.19 sec)
mysql> insert into student(name)values('jack');
Query OK, 1 row affected (0.08 sec)
mysql> insert into student(name)values('marry');
Query OK, 1 row affected (0.04 sec)
mysql> create table if not existsteacher(
-> id int primary key auto_increment,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (1.43 sec)
mysql> insert into teacher(name)values('赵');
Query OK, 1 row affected (0.34 sec)
mysql> insert into teacher(name)values('蔡');
Query OK, 1 row affected (0.07 sec)
--多对多(外健放置在关联表中，即将一个多对多拆分成二个一对多关系)
-- 组合主键
mysql> create table if not existsmiddle(
-> sid int,
-> tid int,
-> constraint sid_FK foreignkey(sid) references student(id),
-> constraint tid_FK foreignkey(tid) references teacher(id),
-> primary key(sid,tid)
-> );
Query OK, 0 rows affected (1.59 sec)
mysql> insert into middle(sid,tid)values(1,1);
Query OK, 1 row affected (0.14 sec)
mysql> insert into middle(sid,tid)values(1,2);
Query OK, 1 row affected (0.06 sec)
mysql> insert into middle(sid,tid)values(2,1);
Query OK, 1 row affected (1.07 sec)
mysql> insert into middle(sid,tid)values(2,2);
Query OK, 1 row affected (0.07sec)mysql> insert into middle(sid,tid) values(2,2);
--查询赵教师教过的所有学员,组合查询
--t.name='赵' 条件
-- and m.sid=s.id andm.tid=t.id --表连接起来
模式：
select 列出需要显示的字段
from 列出所涉及到的所有表，建议写别名
where 业务条件and 表关联条件
--sql
mysql> selectt.name as 老师, s.name as 学员
-> from teacher as t,student as s,middleas m
-> where t.name = '赵'and m.sid=s.id and m.tid=t.id;
+--------+--------+
| 老师 | 学员 |
+--------+--------+
| 赵 | jack |
| 赵 | marry |
+--------+--------+
2 rows in set(0.00 sec)
