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類生成)
定義唯一約束
sql數據庫如何建立表間關系？??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,
CAD約束用于設計嗎、???-> name varchar(20),
???-> brithday date);
Query OK, 0 rows affected (0.61 sec)
?
mysql> select * from teacher;
Empty set (0.00 sec)
sql關聯查詢中的主表和從表，?
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)
?
sql語句創建表約束。mysql> desc teacher;
+----------+-------------+------+-----+---------+-------+
| Field???| Type??????? | Null | Key |Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id??????| int(11)???? | NO?? | PRI | NULL??? |??????|
| name????| varchar(20) | YES? |???? | NULL???|?????? |
sqlserver建立表之間的關系。| 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;
MySQL修改表結構、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)
?
