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 表名;
以上是 python之路_mysql表操作2 的全部内容， 来源链接： utcz.com/z/389390.html
