从小白到小黑 python学习之旅 日常总结 46（表的完整性约束 修改表 复制表）
约束条件
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
not null 与 null
是否可空，null表示空，非字符串 not null - 不可空 null - 可空
==================not null==================== mysql> create table t1(id int); #id字段默认可以插入空 mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ mysql> insert into t1 values(); #可以插入空 mysql> create table t2(id int not null); #设置字段id不为空 mysql> desc t2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ mysql> insert into t2 values(); #不能插入空 ERROR 1364 (HY000): Field 'id' doesn't have a default value
default默认值
# 创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值 create table tb1( nid int not null defalut 2, num int not null )
==================default==================== #设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值 mysql> create table t3(id int default 1); mysql> alter table t3 modify id int not null default 1; ==================验证==================== mysql> create table student( -> name varchar(20) not null, -> age int(3) unsigned not null default 18, -> sex enum('male','female') default 'male', -> hobby set('play','study','read','music') default 'play,music' -> ); mysql> desc student; +-------+------------------------------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------------+------+-----+------------+-------+ | name | varchar(20) | NO | | NULL | | | age | int(3) unsigned | NO | | 18 | | | sex | enum('male','female') | YES | | male | | | hobby | set('play','study','read','music') | YES | | play,music | | +-------+------------------------------------+------+-----+------------+-------+ mysql> insert into student(name) values('egon'); mysql> select * from student; +------+-----+------+------------+ | name | age | sex | hobby | +------+-----+------+------------+ | egon | 18 | male | play,music | +------+-----+------+------------+
unique唯一
#单列唯一 create table department1( id int, name varchar(20) unique, comment varchar(100) ); mysql> insert into department1 values(1,'IT','技术'); Query OK, 1 row affected (0.00 sec) mysql> insert into department1 values(1,'IT','技术'); ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
#单个都可以重复 但是加载一起必须是唯一的 create table service( id int primary key auto_increment, name varchar(20), host varchar(15) not null, port int not null, unique(host,port) #联合唯一 ); mysql> insert into service values -> (1,'nginx','192.168.0.10',80), -> (2,'haproxy','192.168.0.20',80), -> (3,'mysql','192.168.0.30',3306) -> ; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80); ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
primary key主键
单单从约束效果上来看primary key等价于not null + unique 非空且唯一！！！
它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据
Innodb存储引擎在创建表的时候必须要有primary key
因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据
# 1 一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键 create table t6( id int, name char(16), age int not null unique, addr char(32) not null unique ); # 2 如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它 就无法提示查询速度 # 3 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键 # 单个字段主键 create table t5( id int primary key name char(16) ); # 联合主键(多个字段联合起来作为表的主键 本质还是一个主键) create table t7( ip char(16), port int, primary key(ip,port) ); """ 也意味着 以后我们在创建表的时候id字段一定要加primary key """
详细
============单列做主键=============== #方法一：not null+unique create table department1( id int not null unique, #主键 name varchar(20) not null unique, comment varchar(100) ); mysql> desc department1; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | UNI | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.01 sec) #方法二：在某一个字段后用primary key create table department2( id int primary key, #主键 name varchar(20), comment varchar(100) ); mysql> desc department2; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.00 sec) #方法三：在所有字段后单独定义primary key create table department3( id int, name varchar(20), comment varchar(100), constraint pk_name primary key(id); #创建主键并为其命名pk_name mysql> desc department3; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.01 sec)
==================多列做主键================ create table service( ip varchar(15), port char(5), service_name varchar(10) not null, primary key(ip,port) ); mysql> desc service; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ip | varchar(15) | NO | PRI | NULL | | | port | char(5) | NO | PRI | NULL | | | service_name | varchar(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into service values -> ('172.16.45.10','3306','mysqld'), -> ('172.16.45.11','3306','mariadb') -> ; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into service values ('172.16.45.10','3306','nginx'); ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
auto_increment自增
约束字段为自动增长，被约束的字段必须同时被key约束
# 当编号特别多的时候 人为的去维护太麻烦 create table t8( id int primary key auto_increment, name char(16) ); insert into t8(name) values('jason'),('egon'),('kevin'); # 注意auto_increment通常都是加在主键上的 不能给普通字段加 create table t9( id int primary key auto_increment, name char(16), cid int auto_increment ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
#总结 """ 以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候 id int primary key auto_increment """ #补充 delete from t1 删除表中数据后 主键的自增不会停止 truncate t1 清空表数据并且重置主键
详细
#不指定id，则自动增长 create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> desc student; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+----------------+ mysql> insert into student(name) values -> ('egon'), -> ('alex') -> ; mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | | 2 | alex | male | +----+------+------+ #也可以指定id mysql> insert into student values(4,'asb','female'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(7,'wsb','female'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+------+--------+ | id | name | sex | +----+------+--------+ | 1 | egon | male | | 2 | alex | male | | 4 | asb | female | | 7 | wsb | female | +----+------+--------+ #对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长 mysql> delete from student; Query OK, 4 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> insert into student(name) values('ysb'); mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 8 | ysb | male | +----+------+------+ #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它 mysql> truncate student; Query OK, 0 rows affected (0.01 sec) mysql> insert into student(name) values('egon'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | +----+------+------+ 1 row in set (0.00 sec)
了解知识
#在创建完表后，修改自增字段的起始值 mysql> create table student( -> id int primary key auto_increment, -> name varchar(20), -> sex enum('male','female') default 'male' -> ); mysql> alter table student auto_increment=3; mysql> show create table student; ....... ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 mysql> insert into student(name) values('egon'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 3 | egon | male | +----+------+------+ row in set (0.00 sec) mysql> show create table student; ....... ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 #也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外 create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' )auto_increment=3; #设置步长 sqlserver：自增步长 基于表级别 create table t1（ id int。。。 ）engine=innodb,auto_increment=2 步长=2 default charset=utf8 mysql自增的步长： show session variables like 'auto_inc%'; #基于会话级别 set session auth_increment_increment=2 #修改会话级别的步长 #基于全局级别的 set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效） #！！！注意了注意了注意了！！！ If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋 比如：设置auto_increment_offset=3，auto_increment_increment=2 mysql> set global auto_increment_increment=5; Query OK, 0 rows affected (0.00 sec) mysql> set global auto_increment_offset=3; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'auto_incre%'; #需要退出重新登录 +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> insert into student(name) values('egon1'),('egon2'),('egon3'); mysql> select * from student; +----+-------+------+ | id | name | sex | +----+-------+------+ | 3 | egon1 | male | | 8 | egon2 | male | | 13 | egon3 | male | +----+-------+------+
foreign key
快速理解foreign key
员工信息表有三个字段：工号 姓名 部门
公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费
解决方法：
我们完全可以定义一个部门表
然后让员工信息表关联该表，如何关联，即foreign key 外键就是用来帮助我们建立表与表之间关系的
#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一 create table department( id int primary key, name varchar(20) not null )engine=innodb; #dpt_id外键，关联父表（department主键id），同步更新，同步删除 create table employee( id int primary key, name varchar(20) not null, dpt_id int, constraint fk_name foreign key(dpt_id) references department(id) on delete cascade on update cascade )engine=innodb; #先往父表department中插入记录 insert into department values (1,'欧德博爱技术有限事业部'), (2,'艾利克斯人力资源部'), (3,'销售部'); #再往子表employee中插入记录 insert into employee values (1,'egon',1), (2,'alex1',2), (3,'alex2',2), (4,'alex3',2), (5,'李坦克',3), (6,'刘飞机',3), (7,'张火箭',3), (8,'林子弹',3), (9,'加特林',3) ; #删父表department，子表employee中对应的记录跟着删 mysql> delete from department where id=3; mysql> select * from employee; +----+-------+--------+ | id | name | dpt_id | +----+-------+--------+ | 1 | egon | 1 | | 2 | alex1 | 2 | | 3 | alex2 | 2 | | 4 | alex3 | 2 | +----+-------+--------+ #更新父表department，子表employee中对应的记录跟着改 mysql> update department set id=22222 where id=2; mysql> select * from employee; +----+-------+--------+ | id | name | dpt_id | +----+-------+--------+ | 1 | egon | 1 | | 3 | alex2 | 22222 | | 4 | alex3 | 22222 | | 5 | alex1 | 22222 | +----+-------+--------+
如何找出两张表之间的关系
分析步骤： #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id） #2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id） #3、总结： #多对一： 如果只有步骤1成立，则是左表多对一右表 如果只有步骤2成立，则是右表多对一左表 #多对多 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 #一对一: 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
建立表之间的关系
一对多关系
""" 判断表与表之间关系的时候 前期不熟悉的情况下 一定要按照我给你的建议 换位思考 分别站在两张表的角度考虑 员工表与部门表为例 先站在员工表 思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据) 不能！！！ (不能直接得出结论 一定要两张表都考虑完全) 再站在部门表 思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据) 能！！！ 得出结论 员工表与部门表示单向的一对多 所以表关系就是一对多 """ foreign key 1 一对多表关系 外键字段建在多的一方 2 在创建表的时候 一定要先建被关联表 3 在录入数据的时候 也必须先录入被关联表 # SQL语句建立表关系 create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) ); insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门'); insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
同步更新、删除
# 修改dep表里面的id字段 update dep set id=200 where id=2; 不行 # 删除dep表里面的数据 delete from dep; 不行 # 1 先删除教学部对应的员工数据 之后再删除部门 操作太过繁琐 # 2 真正做到数据之间有关系 更新就同步更新 删除就同步删除 """ 级联更新 >>> 同步更新 级联删除 >>> 同步删除 """ create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门'); insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
多对多
""" 图书表和作者表 """ create table book( id int primary key auto_increment, title varchar(32), price int, author_id int, foreign key(author_id) references author(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); create table author( id int primary key auto_increment, name varchar(32), age int, book_id int, foreign key(book_id) references book(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); """ 按照上述的方式创建 一个都别想成功！！！ 其实我们只是想记录书籍和作者的关系 针对多对多字段表关系 不能在两张原有的表中创建外键 需要你单独再开设一张 专门用来存储两张表数据之间的关系 """
""" 图书表和作者表 """ create table book( id int primary key auto_increment, title varchar(32), price int ); create table author( id int primary key auto_increment, name varchar(32), age int ); create table book2author( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade # 同步更新 on delete cascade, # 同步删除 foreign key(book_id) references book(id) on update cascade # 同步更新 on delete cascade # 同步删除 );
一对一
""" id name age addr phone hobby email........ 如果一个表的字段特别多 每次查询又不是所有的字段都能用得到 将表一分为二 用户表 用户表 id name age 用户详情表 id addr phone hobby email........ 站在用户表 一个用户能否对应多个用户详情 不能！！！ 站在详情表 一个详情能否属于多个用户 不能！！！ 结论:单向的一对多都不成立 那么这个时候两者之间的表关系 就是一对一 或者没有关系(好判断) """ #一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中 create table authordetail( id int primary key auto_increment, phone int, addr varchar(64) ); create table author( id int primary key auto_increment, name varchar(32), age int, authordetail_id int unique, foreign key(authordetail_id) references authordetail(id) on update cascade # 同步更新 on delete cascade # 同步删除 );
总结
表关系的建立需要用到foreign key
一对多
外键字段建在多的一方
多对多
自己开设第三张存储
一对一
建在任意一方都可以 但是推荐你建在查询频率较高的表中
修改表(了解)
# MySQL对大小写是不敏感的 """ 1 修改表名 alter table 表名 rename 新表名; 2 增加字段 alter table 表名 add 字段名 字段类型(宽度) 约束条件; alter table 表名 add 字段名 字段类型(宽度) 约束条件 first; alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名; 3 删除字段 alter table 表名 drop 字段名; 4 修改字段 alter table 表名 modify 字段名 字段类型(宽度) 约束条件; alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件; """
示例： 1. 修改存储引擎 mysql> alter table service -> engine=innodb; 2. 添加字段 mysql> alter table student10 -> add name varchar(20) not null, -> add age int(3) not null default 22; mysql> alter table student10 -> add stu_num varchar(10) not null after name; //添加name字段之后 mysql> alter table student10 -> add sex enum('male','female') default 'male' first; //添加到最前面 3. 删除字段 mysql> alter table student10 -> drop sex; mysql> alter table service -> drop mac; 4. 修改字段类型modify mysql> alter table student10 -> modify age int(3); mysql> alter table student10 -> modify id int(11) not null primary key auto_increment; //修改为主键 5. 增加约束（针对已有的主键增加auto_increment） mysql> alter table student10 modify id int(11) not null primary key auto_increment; ERROR 1068 (42000): Multiple primary key defined mysql> alter table student10 modify id int(11) not null auto_increment; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 6. 对已经存在的表增加复合主键 mysql> alter table service2 -> add primary key(host_ip,port); 7. 增加主键 mysql> alter table student1 -> modify name varchar(10) not null primary key; 8. 增加主键和自动增长 mysql> alter table student1 -> modify id int not null primary key auto_increment; 9. 删除主键 a. 删除自增约束 mysql> alter table student10 modify id int(11) not null; b. 删除主键 mysql> alter table student10 -> drop primary key;
复制表(了解)
""" 我们sql语句查询的结果其实也是一张虚拟表 """ create table 表名 select * from 旧表; #不能复制主键 外键 ... create table new_dep2 select * from dep where id>3;
删除表
DROP TABLE 表名;
浙公网安备 33010602011771号