主键与外键、自增、表关系之一对多、多对多、一对一

约束条件之主键

1.主键介绍
	我们在建立数据库的时候，需要为每张表指定一个主键，所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。所以数据库在设计时，主键起到了很重要的作用。
2.主键的关键字:primary key
3.主键特点
	i.单从约束角度上而言主键等价于非空且唯一  not null unique,验证：
        mysql> create database db;
        Query OK, 1 row affected (0.00 sec)

        mysql> use db;
        Database changed
        mysql> create table t1(
            ->            id int primary key,
            ->            name varchar(32)
            ->         );
        Query OK, 0 rows affected (0.03 sec)

        mysql> select * from t1;
        Empty set (0.00 sec)

        mysql> desc t1;
        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int(11)     | NO   | PRI | NULL    |       |
        | name  | varchar(32) | YES  |     | NULL    |       |
        +-------+-------------+------+-----+---------+-------+
        2 rows in set (0.03 sec)

        mysql> insert into t1(name) values('tony');
        # 'ERROR 1364 (HY000): Field 'id' doesn't have a default value'
        mysql> insert into t1(id) values(1);
        Query OK, 1 row affected (0.02 sec)

        mysql> insert into t1(id) values(1);
        ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
            
	ii.InnoDB存储引擎规定一张表必须有且只有一个主键
    	·如果创建的表中没有主键也没有非空且唯一的字段，那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键(主键可以加快数据查询:新华字典的目录)
        ·如果创建的表中没有主键但是有非空且唯一的字段，那么InnoDB存储引擎会自动将该字段设施为主键
        	mysql> create table t2(
                -> nid int not null unique,
                -> sid int not null unique,
                -> uid int not null unique,
                -> name varchar(32));
            Query OK, 0 rows affected (0.05 sec)

            mysql> desc t2;
            +-------+-------------+------+-----+---------+-------+
            | Field | Type        | Null | Key | Default | Extra |
            +-------+-------------+------+-----+---------+-------+
            | nid   | int(11)     | NO   | PRI | NULL    |       |
            | sid   | int(11)     | NO   | UNI | NULL    |       |
            | uid   | int(11)     | NO   | UNI | NULL    |       |
            | name  | varchar(32) | YES  |     | NULL    |       |
            +-------+-------------+------+-----+---------+-------+
            4 rows in set (0.03 sec)
	iii.创建表的时候都应该有一个'id'字段，并且该字段应该作为主键
    	比如说：uid、sid、pid
补充说明:争对主键有且只有一个
	单列主键
		id int primary key
	联合主键：
		primary key(sid, nid)
		实操：
		mysql> alter table t2 add primary key(nid,sid);
		Query OK, 0 rows affected (0.06 sec)
		Records: 0  Duplicates: 0  Warnings: 0

		mysql> desc t2;
		+-------+-------------+------+-----+---------+-------+
		| Field | Type        | Null | Key | Default | Extra |
		+-------+-------------+------+-----+---------+-------+
		| nid   | int(11)     | NO   | PRI | NULL    |       |
		| sid   | int(11)     | NO   | PRI | NULL    |       |
		| uid   | int(11)     | NO   | UNI | NULL    |       |
		| name  | varchar(32) | YES  |     | NULL    |       |
		+-------+-------------+------+-----+---------+-------+
		4 rows in set (0.03 sec)

没有办法更新主键，主键需先删除才能增加
alter table [表名] drop primary key;

约束条件之主键的自增

当对应的字段不给值，或者说给默认值或者给null的时候，会自动的被系统触发，系统会从当前字段中已有的最大值再进行+1操作，得到一个新的再不同的字段

自增长字段必须是数字，而且是整型


mysql> select * from t4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.01 sec)

mysql> insert into t4(id,name) values(4,'aa'),(5,'bb'),(6,'cc');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | aa   |
|  5 | bb   |
|  6 | cc   |
+----+------+
6 rows in set (0.00 sec)

mysql> insert into t4(name) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | aa   |
|  5 | bb   |
|  6 | cc   |
|  7 | aa   |
|  8 | bb   |
|  9 | cc   |
+----+------+
9 rows in set (0.00 sec)
自增长如果对应的字段输入了值，那么自增长失效；但是下一次还是能够正确的自增长（从最大值+1）

1.auto_increment
	该约束条件不能单独使用，必须跟在键后面(配合主键主键使用)
    mysql> create table t3(
        -> id int auto_increment);
    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
	'''这里只能有一个自动增加的列，并且必须是一个键'''
    
    正确的写法：
    mysql> create table t4(
        -> id int primary key auto_increment,
        -> name varchar(32));
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> desc t4;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(32) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    2 rows in set (0.01 sec)
    mysql> insert into t4(name) values('zz'),('aa'),('bb');
    Query OK, 3 rows affected (0.02 sec)
    Records: 3  Duplicates: 0  Warnings: 0
	'''主键字段不需要添加自动生成，只需要去添加数据就好'''
    mysql> select * from t4;
    +----+------+
    | id | name |
    +----+------+
    |  1 | zz   |
    |  2 | aa   |
    |  3 | bb   |
    +----+------+
    3 rows in set (0.00 sec)
    
    这里需要注意的主键只会向后增加，不管中途是怎么操作了数据，它只会向后增加，例如：如果删除了id=3的数据，接下来在向里面添加数据它也是从4开始，那么这样也好，如果中途删了数据，我们就会从id号上查看到，总而言之就是自增不会因为删除的操作而进行回退，只会向后增加
    mysql> delete from t4 where id=3;
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t4(name) values('zz'),('aa'),('bb');
    Query OK, 3 rows affected (0.02 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    mysql> select * from t4;
    +----+------+
    | id | name |
    +----+------+
    |  1 | zz   |
    |  2 | aa   |
    |  4 | zz   |
    |  5 | aa   |
    |  6 | bb   |
    +----+------+
    5 rows in set (0.00 sec)
    针对自增以上的这个特性，如果我们非要重置，那么可以使用格式化表
    	truncate 表名; 它的作用就是删除表数据并重置主键值
        

约束条件之外键

1.外键前戏
	需要创建一张员工表
		id	name gender	dep_name dep_desc
	上述表的缺陷
    	1.表结构不清晰 到底是员工表还是部门表(不严重 无所谓)
   		2.字段数据反复存取 浪费存储空间(不严重 无所谓)
        3.表的扩展性极差 牵一发动全身(很严重 效率极低)
	优化操作>>>:拆表
		id	name	  gender
		id	dep_name  dep_desc
       拆表之后解决了上述的三个问题，但是出现了一个致命的缺陷，不知道员工对应的部门，部门里也不知道存在哪些员工
   	解决措施:在员工表里增加'dep_id'
    	id		name	gender 	  dep_id
       添加一个部门编号字段填写部门数据的主键值
   	外键字段
    	专门用于记录表与表之间数据的关系

2.外键字段的创建
	外键字段是用来记录表与表之间数据的关系 而数据的关系有四种
		一对多关系、多对多关系、一对一关系、没有关系
	表数据关系的判定  >>>: '换位思考'
      针对员工表和部门表判断数据关系
		1.先站在员工表的角度
			问:一条员工数据能否对应多条部门数据
			翻:一名员工能否属于多个部门
			答:不可以
		2.再站在部门表的角度
			问:一条部门数据能否对应多条员工数据
			翻:一个部门能否拥有多个员工
			答:可以
		完成换位思考之后得出的答案 一个可以一个不可以
		那么表关系就是"一对多"
			部门是一 员工是多
		"""针对'一对多'的关系 外键字段建在多的一方 """
		ps:没有多对一 统一称为'一对多'

表关系之一对多

"""涉及到外键字段，先写普通字段，然后再写外键字段"""
create table emp(
	id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);

create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
mysql> insert into dep(dep_name,dep_desc) values('讲师部','教书育人'),('安保部','维护治安');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 讲师部    | 教书育人     |
|  2 | 安保部    | 维护治安     |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> insert into emp(name,dep_id) values('jason',1),('kevin',2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      1 |
|  2 | kevin | male   |      2 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)
"""
1.创建表的时候需要先创建被关联的表(没有外键) 然后再是关联表(有外键)
2.插入表数据的时候 针对外键字段只能填写被关联表字段已经出现过的数据值
3.被关联字段无法修改和删除
	有点不太好 操作限制性太强
"""
级联更新、级联删除
	被关联数据一旦变动 关联的数据同步变动
	on update cascade  # 级联更新 
	on delete cascade  # 级联删除
级联更新、级联删除
	被关联数据一旦变动 关联的数据同步变动
create table emp1(
	id int primary key auto_increment,
 	name varchar(32),
  	gender enum('male','female','others') default 'male',
   dep_id int,
   foreign key(dep_id) references dep1(id) 
   on update cascade
   on delete cascade
);
create table dep1(
	id int primary key auto_increment,
   	dep_name varchar(32),
    dep_desc varchar(32)
);
mysql> insert into dep1(dep_name,dep_desc) values('讲师部','教书育人'),('安保部','维护治安');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
            
mysql> insert into emp1(name,dep_id) values('jason',1),('kevin',2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
            
mysql> update dep1 set id=200 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp1;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      1 |
|  2 | kevin | male   |    200 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)
"""
扩展:
    在实际工作中 很多时候可能并不会使用外键
        因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
    我们为了能够描述出表数据的关系 又不想使用外键
        自己通过写SQL 建立代码层面的关系
	如果表少的话使用外键会方便一点，但是如果表多的话就不建议使用外键了，那样的话就会乱
"""

表关系之多对多

以书籍表与作者表为例
	1.先站在书籍表的角度
		问:一条书籍数据能否对应多条作者数据
		答:可以
	2.再站在作者表的角度
		问:一条作者数据能否对应多条书籍数据
		答:可以
	总结:两边都可以 那么表数据关系就是'多对多'
	针对多对多表关系 外键字段不能建在任意一方!!!
mysql> create table book(
    -> id int primary key auto_increment,
    ->     title varchar(32),
    ->     author_id int,
    ->     foreign key(author_id) references author(id)
    ->     on update cascade
    ->     on delete cascade
    -> );
ERROR 1215 (HY000): Cannot add foreign key constraint
    
mysql> create table author(
    -> id int primary key auto_increment,
    ->     name varchar(32),
    ->     foreign key(book_id) references book(id)
    ->     on update cascade
    ->     on delete cascade
    -> );
ERROR 1072 (42000): Key column 'book_id' doesn't exist in table
'''需要单独开设第三张表，存储数据关系'''
create table book(
	id int primary key auto_increment,
	title varchar(32)
);
create table author(
    id int primary key auto_increment,
    name varchar(32)
);
create table book2author(
    id int primary key auto_increment,
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade 
    on delete cascade,
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade 
    on delete cascade
);

表关系之一对一

以用户表和用户详情表
	1.先站在用户表的角度
		问：一个用户可以对应多个详细信息
		答：不可以
	2.再站在用户详情表的角度
		问：一份详细信息可以对应多个用户
		答：不可以
	总结：两边都不可以，那么先考虑是不是没有关系
    	如果有关系那么肯定就是'一对一'
        针对'一对一'的表关系 外键字段建在任何一张表都可以 但是建议你建在查询频率较高的表中便于后续查询
        create table user(
            id int primary key auto_increment,
            name varchar(32)
            detail_id int unique, # 重点，给detail_id加约束条件unique
            foreign key(detail_id) references userDetail(id) 
            on update cascade
            on delete cascade
        );
        create table userDetail(
            id int primary key auto_increment,
            phone bigint
        );

小练习

判断下列表数据关系 并自定义创建出表
ps:有些表数据关系不是确定 根据具体业务可能有变化

	-------------------------------[计算机表与应用程序表]-------------------------------------
         计算机表		应用程序表
    id	computer_name		id	app_name
    1	computer1		1	weichat
    2	computer2		2	QQ
    3	computer3		3	王者荣耀
    '''
    1.站在计算机表的角度下
    	问：一台计算机可以对应多个程序
    	答：可以
    2.再站在应用程序的角度
    	问：一个应用程序可以对应多个电脑
    	答：可以
    总结:两边都可以 那么表数据关系就是'多对多'
    '''

	-------------------------------[课程表与班级表]-------------------------------------
            课程表			班级表
    id	course_name		id	class_name
    1	java			1	软件193
    2	python			2	软件191
    3	MySQL			3	软件192
    '''
    1.站在课程表的角度下
    	问：一门课程可以对应多个班级
    	答：可以
    2.再站在班级的角度
    	问：一个班级可以对应多个课程
    	答：可以
    总结:两边都可以 那么表数据关系就是'多对多'
    '''
mysql> create table course(
    -> id int primary key auto_increment,
    -> course_name varchar(50));
Query OK, 0 rows affected (0.05 sec)

mysql> create table classroom(
    -> id int primary key auto_increment,
    -> class_name varchar(50));
Query OK, 0 rows affected (0.05 sec)

mysql> create table class_course(    id int primary key auto_increment,    class_id int,    foreign key(class_id ) references classroom(id)     on update cascade     on delete cascade,    course_id int,    foreign key(course_id ) references course(id)     on update cascade     on delete cascade );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into classroom(class_name) values('软件193'),('软件192'),('软件191');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from classroom;
+----+------------+
| id | class_name |
+----+------------+
|  1 | 软件193    |
|  2 | 软件192    |
|  3 | 软件191    |
+----+------------+
3 rows in set (0.00 sec)

mysql> insert into course(course_name) values('java'),('MySQL'),('python');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | java        |
|  2 | MySQL       |
|  3 | python      |
+----+-------------+
3 rows in set (0.00 sec)

mysql> insert into class_course(class_id,course_id) values(1,2),(2,3),(1,3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from class_course;
+----+----------+-----------+
| id | class_id | course_id |
+----+----------+-----------+
|  1 |        1 |         2 |
|  2 |        2 |         3 |
|  3 |        1 |         3 |
+----+----------+-----------+
3 rows in set (0.00 sec)

	-------------------------------[老师表与课程表]-------------------------------------

    	教师表					班级表
    id	student_name			id	class_name
    1	Jason				1	软件193
    2	jerry				2	软件191
    3	kerry				3	软件192
	'''
    1.站在教师表的角度下
    	问：一位老师可以对应多个班级
    	答：可以
    2.再站在班级的角度
    	问：一个班级可以对应多个老师
    	答：可以
    总结:两边都可以 那么表数据关系就是'多对多'
    '''

	书籍表与出版社表
    	书籍表			出版社表
    id	book_name		id	publish_name
    1	XXXXX			1	清华大学出版社
    2	AAAAA			2	北京大学出版设
    3	BBBBB			3	人民日报出版社
    '''
    1.站在书籍表的角度下
    	问：一本书可以对应多个出版社
    	答：可以
    2.再站在出版社的角度
    	问：一个出版社可以对应多本书
    	答：可以
    总结:两边都可以 那么表数据关系就是'多对多'
    '''

	-------------------------------[学生表与班级表]-------------------------------------
    	学生表				班级表
    id	student_name			id	class_name
    1	张小冉				1	软件193
    2	张询彬				2	软件191
    3	张海洋				3	软件192
	'''
    1.站在学生表的角度下
    	问：一名学生可以对应多个班级
    	答：不可以
    2.再站在班级的角度
    	问：一个班级可以对应多个学生
    	答：可以
    总结:完成换位思考之后得出的答案 一个可以一个不可以
		那么表关系就是"一对多"
			班级是一 学生是多
		"""针对'一对多'的关系 外键字段建在多的一方 """
    '''
	mysql> create table class(
    -> id int primary key auto_increment,
    -> className varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> create table student(
    -> id int primary key auto_increment,
    -> studentName varchar(32),
    -> class_id int,
    -> foreign key(class_id) references class(id));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into class(className) values('软件193'),('软件192'),('软件191');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc class;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| className | varchar(32) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> selelct * from class;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selelct * from class' at line 1
mysql> select * from class;
+----+-----------+
| id | className |
+----+-----------+
|  1 | 软件193   |
|  2 | 软件192   |
|  3 | 软件191   |
+----+-----------+
3 rows in set (0.00 sec)

mysql> insert into student(studentName,class_id) values('张小冉',1);
Query OK, 1 row affected (0.02 sec)

mysql> select * from student;
+----+-------------+----------+
| id | studentName | class_id |
+----+-------------+----------+
|  1 | 张小冉      |        1 |
+----+-------------+----------+
1 row in set (0.00 sec)