列属性完整性

列表属性问题

  1. auto_increment 必定是primary key

  2. primary key不一定是auto_increment，也可以是手动增加

  3. 把主键值为3的值数据删掉了，就不能再添加了

主键的作用及企业用途：

 // 第一种
 mysql> create table t_1(
     -> id int(20) primary key,
     -> name varchar(30));
     
 // 第二种    
 mysql> create table t_2(
     -> id int(20),            
     -> name varchar(20)
     -> );
 mysql> alter table t_2 add primary key (id);  
 mysql> desc t_2;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(20)     | NO   | PRI | NULL    |       |
 | name  | varchar(20) | YES  |     | NULL    |       |
 +-------+-------------+------+-----+---------+-------+
 2 rows in set (0.00 sec)

主键：大型数据中，用来区分数据，而且是最为关键的一环，叫做主键（primary key），具有唯一性，且能够确认此数据存在，不可重复性。如：身份证号。

增加查询速度，相互关联，有可能对其他的表起到作用，在其他表里不叫主键。

主键不能重复，不能为NULL，除非为auto_increment(自增)

删除，组合键，选择主键

一张表里可以有多个主键，但是用处不广，不易维护。

选择一些很少改动的东西做主键，比如身份证号

 // 删除主键
 mysql> alter table t_2 drop primary key;
 ​
 // 组合键
 mysql> alter table t_2 add primary key (id,name);
 mysql> desc t_2;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(20)     | NO   | PRI | NULL    |       |
 | name  | varchar(20) | NO   | PRI | NULL    |       |
 +-------+-------------+------+-----+---------+-------+

复合主键到底有什么用

有的网站昵称和编号都要求是唯一，符合主键也是只有一个主键

没有主键的就设置一个主键，加一个auto_increment

unique唯一键的作用和添加介绍

唯一键不是用来区分数据的，不和其他的表有关联，可以为空，只在此张表中唯一。一个表里可以多个唯一键，保证数据不能重复

主键可能和其他的数据库有关联，不能为空

 mysql> create table t_3(
     -> id int primary key,
     -> phone varchar(20) unique
     -> );
 mysql> desc t_3;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(11)     | NO   | PRI | NULL    |       |
 | phone | varchar(20) | YES  | UNI | NULL    |       |
 +-------+-------------+------+-----+---------+-------+  
 // 添加数据，报错的原因不同
 mysql> insert into t_3 values(1, "123456"); 
 Query OK, 1 row affected (0.01 sec)
 ​
 mysql> insert into t_3 values(1, "123456");
 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
 mysql> insert into t_3 values(2, "123456"); 
 ERROR 1062 (23000): Duplicate entry '123456' for key 'phone'
 mysql> create table t_4(
     -> id int(4),        
     -> phone varchar(20) 
     -> );
 Query OK, 0 rows affected (0.03 sec)
 ​
 mysql> desc t_4;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(4)      | YES  |     | NULL    |       |
 | phone | varchar(20) | YES  |     | NULL    |       |
 +-------+-------------+------+-----+---------+-------+
 2 rows in set (0.01 sec)
 ​
 // 添加主键
 mysql> alter table t_4 add unique(phone);
 mysql> desc t_4;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(4)      | YES  |     | NULL    |       |
 | phone | varchar(20) | YES  | UNI | NULL    |       |
 +-------+-------------+------+-----+---------+-------+
 2 rows in set (0.00 sec)

唯一键扩展

 mysql> create table t_5(
     -> id int(4),
     -> name varchar(20) unique,
     -> phone varchar(20) unique);
 Query OK, 0 rows affected (0.04 sec)
 ​
 mysql> desc t_5;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(4)      | YES  |     | NULL    |       |
 | name  | varchar(20) | YES  | UNI | NULL    |       |
 | phone | varchar(20) | YES  | UNI | NULL    |       |
 +-------+-------------+------+-----+---------+-------+
 3 rows in set (0.01 sec)
 ​
 // 组合唯一键，意义不大
 mysql> alter table t_5 add unique(name,phone);
 ​
 // 删除唯一键
 mysql> alter table t_5 drop index phone;      
 Query OK, 0 rows affected (0.03 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 ​
 mysql> desc t_5;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(4)      | YES  |     | NULL    |       |
 | name  | varchar(20) | YES  | UNI | NULL    |       |
 | phone | varchar(20) | YES  |     | NULL    |       |
 +-------+-------------+------+-----+---------+-------+

sql内注释和代码注释

 mysql> create table t_6(
     -> id int(4) primary key,       # this is a primary key! 单行注释
     -> phone varchar(20)       -- this is.......单行注释
     -> /*多行注释
    /*> dasdas
    /*> dasdasd
    /*> */
     -> );
 Query OK, 0 rows affected (0.05 sec)
 # 一般不采用

 

常用注释为sql内注释：comment关键字

 mysql> create table t_7(
     -> id int(4),
     -> name varchar(20) comment '姓名' 
     -> );
 Query OK, 0 rows affected (0.04 sec)
 ​
 mysql> show create table t_7;
 +-------+------------------------------------------------------------------------------------------------------------------------------------------+
 | Table | Create Table
                                                                 |
 +-------+------------------------------------------------------------------------------------------------------------------------------------------+
 | t_7   | CREATE TABLE `t_7` (
   `id` int(4) DEFAULT NULL,
   `name` varchar(20) DEFAULT NULL COMMENT '姓名'
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 +-------+------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

数据库完整性

设计数据库时：

  1. 保证字段都应当是完整的，如一张表里应当有主键约束，可能是自增长的

  2. 保证数据类型是对的，或者说选对数据类型，如电话号码；有的数据要允许default类型，考试学生没来，要给default，不能直接给0分

  3. 可能需要被外部引用

  4. 自定义元素

即预完整性，实体完整性，引用完整性，自定义完整性

引用数据表的完整性问题，抛出外键的概念

在学校中，学生的studentID与食堂是有关联的，

主表：学生表

从表：食堂订单数据表

外键

主表：学生表

 mysql> create table stu(     
     -> stuId int(4) primary key,
     -> name varchar(20)
     -> );

从表：食堂订单表

 mysql> create table eatery(
     -> id int primary key,  
     -> money decimal(10,4),
     -> stuId int(4),
     -> foreign key (stuId) references stu(stuId)
     -> );
 Query OK, 0 rows affected (0.04 sec)
 ​
 mysql> desc eatery;
 +-------+---------------+------+-----+---------+-------+
 | Field | Type          | Null | Key | Default | Extra |
 +-------+---------------+------+-----+---------+-------+
 | id    | int(11)       | NO   | PRI | NULL    |       |
 | money | decimal(10,4) | YES  |     | NULL    |       |
 | stuId | int(4)        | YES  | MUL | NULL    |       |
 +-------+---------------+------+-----+---------+-------+

利用外键俩张表进行关联，实际开发中，对于并发的项目，禁止使用外键

 

不仅创建表时可以创建外键，在修改表的时候也可以添加外键

 mysql> create table eatery_2(
     -> id int(4) primary key,
     -> money decimal(10,4),
     -> stuId int(4)
     -> );
 ​
 mysql> alter table eatery_2 add foreign key (stuId) references stu(stuId);
 ​
 mysql> desc eatery_2;
 +-------+---------------+------+-----+---------+-------+
 | Field | Type          | Null | Key | Default | Extra |
 +-------+---------------+------+-----+---------+-------+
 | id    | int(4)        | NO   | PRI | NULL    |       |
 | money | decimal(10,4) | YES  |     | NULL    |       |
 | stuId | int(4)        | YES  | MUL | NULL    |       |
 +-------+---------------+------+-----+---------+-------+

什么时候设计

开发期间，对于关系型数据库，很少在后期改变其结构，在设计的时候直接设计好

更正错误，删除外键

 mysql> show create table eatery;
 +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table  | Create Table
 ​
 ​
                                             |
 +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | eatery | CREATE TABLE `eatery` (
   `id` int(11) NOT NULL,
   `money` decimal(10,4) DEFAULT NULL,
   `stuId` int(4) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `stuId` (`stuId`),
   CONSTRAINT `eatery_ibfk_1` FOREIGN KEY (`stuId`) REFERENCES `stu` (`stuId`)      
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 ​
 #删除外键
 mysql> alter table eatery drop foreign key eatery_ibfk_1;

外键三种操作严格、置空和级联的使用情景介绍

  1. 外键的基础操作（严格性操作）

  2. 置空操作

  3. 级联操作

若主表中的stuId=5被删除，那么从表应当如何做？

置空操作：将从表中的订单id改为NULL

级联操作：将从表中有关stuId=5的订单全部删除

删除数据的时候多使用置空操作，更新数据的时候多使用级联操作

置空和级联操作

 mysql> create table eatery(
     -> id int(20) primary key,
     -> money decimal(10,4),
     -> stuId int(4),
     -> foreign key(stuId) references stu(stuId) on delete set null on update cascade
     -> );
     
 mysql> insert into stu values(1, 'leo');
 ​
 mysql> insert into stu values(2, 'jerry');
 ​
 mysql> select stuId,name from stu; 
 +-------+-------+
 | stuId | name  |
 +-------+-------+
 |     1 | leo   |
 |     2 | jerry |
 +-------+-------+    
 ​
 mysql> insert into eatery values(1, 20.5, 2);
 mysql> insert into eatery values(2, 78.5, 1); 
 mysql> insert into eatery values(3, 28, 1);  
 mysql> insert into eatery values(4, 40.8, 2);
 ​
 ​
 mysql> select * from eatery;
 +----+----------+-------+
 | id | money    | stuId |
 +----+----------+-------+
 |  1 |  20.5000 |     2 |
 |  2 |  78.5000 |     1 |
 |  3 |  28.0000 |     1 |
 |  4 |  40.8000 |     2 |
 |  5 | 453.1230 |     1 |
 +----+----------+-------+
 ​
 mysql> update stu set stuId='4' where name='leo';
 mysql> select * from stu;   
 +-------+-------+
 | stuId | name  |
 +-------+-------+
 |     2 | jerry |
 |     4 | leo   |
 +-------+-------+
 ​
 # 级联
 mysql> select * from eatery; 
 +----+----------+-------+
 | id | money    | stuId |
 +----+----------+-------+
 |  1 |  20.5000 |     2 |
 |  2 |  78.5000 |     4 |
 |  3 |  28.0000 |     4 |
 |  4 |  40.8000 |     2 |
 |  5 | 453.1230 |     4 |
 +----+----------+-------+
 ​
 # 置空操作，先删除2号学生
 mysql> delete from stu where stuId='2';
 ​
 mysql> select * from stu;    
 +-------+------+
 | stuId | name |
 +-------+------+
 |     4 | leo  |
 +-------+------+
 ​
 # 置空操作
 mysql> select * from eatery;
 +----+----------+-------+
 | id | money    | stuId |
 +----+----------+-------+
 |  1 |  20.5000 |  NULL |
 |  2 |  78.5000 |     4 |
 |  3 |  28.0000 |     4 |
 |  4 |  40.8000 |  NULL |
 |  5 | 453.1230 |     4 |
 +----+----------+-------+

 

 

 

posted @ 2020-07-31 19:23  leoIOIO  阅读(112)  评论(0)    收藏  举报