python之路_mysql表操作2
一、完整性约束
约束条件与数据类型宽度相似，都是可选参数，主要用于保证数据的完整性和一致性。
1、not null与default
null 为可为空，表默认即为null，可以插入空内容：
not null为非空，不可以插入空内容，输入空会报错：
设置default默认值后，无论是是null还是not null，都可以输入空，输空以后取默认设置的那个值：
设置为空，输入空的情况：
设置不为空，输入为空的情况：
2、unique
设置唯一性约束，如下例，若第二次此字段输入的数据与上一次相同则会报错。当然下例也可以通过命令：create table t22(id int,name char(10),sex char(10),constraint uk_name unique(name));实现。
设置联合唯一情况如下：
3、primary key
primary key 字段不为空且唯一，一个表中只能有一个主键primary key，not null +unique也有同样的功效，但是primary key 只能有一个，not null unique可以有多个。
============单列做主键=============== #方法一：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 | | +--------------+-------------+------+-----+---------+-------+ 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'
4、auto_increment
用于约束的字段可以自动增长，但是被约束的字段必须也被key约束，否则报错。
设置为自增字段后，该字段不输入数据时会按照默认进行自增。
对于自增的字段用delete删除后，再插入数值后，该字段仍旧按照删除前的位置进行增长：
为解决这个问题必须使用truncate进行清空表操作，此命令会将表全部内容清空：
自增字段起始值和自增步长默认均为1，在创建表的时候可以在括号外指定auto_increment的起始值：
此外可以通过如下指令设置自增字段的的起始值（auto_increment_offset）和自增步长(auto_increment_increment)，需要注意的是设置的起始值一定要小于等于自增步长，否则设置不成功。
#基于会话级的设置：此会话内创建的表有效，关闭会话后设置无效 set session auto_increment_increment=3; set session auto_increment_offset=2; #基于全局级的设置：所有会话内都有效，但是设置完后需要重新登录 set global auto_increment_increment=3; set global auto_increment_offset=2;
以全局级设置为例如下：
5、foreign key
称为外键，主要用来关联几个有关系的表，如有一个员工的信息表，员工信息表有三个字段：工号 姓名 部门，公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费。解决方法：我们完全可以定义一个部门表，然后让员工信息表关联该表，如何关联，即foreign key。
#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一 create table department( id int primary key, name varchar(20) not null )engine=innodb; create table employee( id int primary key, name varchar(20) not null, dpt_id int, 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即可
（1）一对多
如一个部门对应多个员工，员工信息一个表，部门信息一个表，则员工信息表中的部门信息就可以通过foreign key进行关联。
首先建立被关联表（部门表）：
创建员工信息表，其中部门信息通过id关联：
（2）多对多
如一个作者可以写多本书，一本书可以由多可作者写，分别有一个作者信息的表和一个书的信息表，通过一个新表和foreign key可以实现关联。如有三本书均是由三个作者一起书写，这三个作者也就写过这三本书。
创建作者信息的表：
创建书的信息表：
创建关联表：
（3）一对一
例如一个学生表，一个客户表，一个学生是从客户表中的一个客户，一个客户可能发展成为一个学生。通过foreign key 和unique可以实现。
创建被约束表格（客户表）：
创建约束表格（学生表）：
二、修改表
语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; 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;
三、复制表
复制表结构＋记录 （key不会复制: 主键、外键和索引） mysql> create table new_service select * from service; 只复制表结构 mysql> select * from service where 1=2; //条件为假，查不到任何记录 Empty set (0.00 sec) mysql> create table new1_service select * from service where 1=2; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create table t4 like employees;
四、删除表
DROP TABLE 表名;
