day46——约束条件、表与表建 关系、修改表的完整语法
今日内容
- 约束条件
- 表与表之间建立关系
- 修改表的完整语法大全
- 复制表
一、约束条件
-
default默认值
# 插入数据的时候可以指定字段 create table t1( id int, name char(16) ); insert into t1(name,id) values('tom',1); # 创建表的时候为性别设定默认值为男性 create table t2( id int, name char(16), gender enum('male','female') default 'male' # 默认为男性 ); insert into t2(id,name) values(1,'tom'); insert into t2 values(2,'marry','female'); select * from t2; """ +------+------------------+--------+ | id | name | gender | +------+------------------+--------+ | 1 | tom | male | | 2 | marry | female | +------+------------------+--------+""" -
unique 唯一
单例唯一
create table t3( id int unique, name char(16) ); insert into t3 values(1,'tom'),(1,'egon'); # 报错，id 字段限制为单列唯一，不能重复 """ Duplicate entry '1' for key 'id'""" inert into t3 values(1,'tom'),(2,'jerry'); # 正常联合唯一
""" 单个可以重复，但是多个联合在一起必须是唯一的""" create table t4( id int, ip char(16), port int, unique(ip,port) ); insert into t4 values(1,'127.0.0.1',8080); insert into t4 values(2,'127.0.0.1',8081); insert into t4 values(3,'127.0.0.2',8080); insert into t4 values(4,'127.0.0.1',8080); # 报错 Duplicate entry '127.0.0.1 -8080' for key 'ip'
primary key 主键
"""
1.单从约束效果上看primary key 等价于 not null + unique
非空且唯一"""
# 验证
create table t5(ind int primary key);
insert into t5 values(null); # 报错 Column 'ind' cannot be null
insert into t5 values(1),(1); # 报错 Duplicate entry '1' for key 'PRIMARY'
insert into t5 values(1),(2); # 正常
"""
2. 主键除了有约束效果之外 它还是innodb存储引擎组织数据的依据，
因为innodb存储引擎在创建表的时候必须要有 primary key
类似于书的目录，能帮助提高查询的效率并且也是建表的依据"""
主键的特性
-
每张表中都有且只有一个主键，如果没有设置主键，那么会从上往下搜索直到遇到非空且唯一的字段自动的升级位主键
create table t6( id int, name char(16), age int not null unique, addr char(32) not null unique ); desc t6; """ age | int(11) | NO | PRI | NULL | |""" -
如果表中没有主键也没有任何的非空且唯一的字段，那么innodb会采用自己内部提供的一个隐藏的字段作为主键，也是因为innodb创建表的时候必须有一个主键，但是隐藏的我们无法使用到它，就无法提升查询的速度。
-
每张表中都应该有一个主键字段，并且通常将id/uid/sid/字段作为主键
# 单个字段主键 create table t7( ip char primary key, name char(16)); desc t7; """ | ip | char(1) | NO | PRI | NULL | |""" # 联合主键（多个字段联合起来作为表的主键 本质还是一个主键） create table t8( ip char(16), port int, primary key(ip,port) ); desc t8; """ | ip | char(16) | NO | PRI | | | | port | int(11) | NO | PRI | 0 | |"""我们在创建表的时候id字段一定要加primary key将其设置为主键
auto_increment自增
"""
当编号特别多的时候，每天数据都手动的去添加id，因为主键不能重复且不能为null，
这样人为的去维护太过于麻烦，我们可以将其设置为自增，每插入一个数据的时候在上一次的基础上自动生成一个唯一的id,提高写的效率"""
create table t9(
id int primary key auto_increment,
name char(16)
);
insert into t9(name) values('tom'),('jerr'),('jack');
select * from t9;
"""
+----+------------------+
| id | name |
+----+------------------+
| 1 | tom |
| 2 | jerr |
| 3 | jack |
+----+------------------+"""
# 注意auto_increment通常都是加到主键上的，不能给普通字段加
create table t10(
id int primary key auto_increment,
name char(16),
cid int auto_increment
);
"""
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 # 删除表中数据后，主键的自增不会停止
# 验证
create table t11(
id int primary key auto_increment,
name char(16)
);
insert into t11(name) values('tom'),('jerry');
select * from t11;
"""
+----+------------------+
| id | name |
+----+------------------+
| 1 | tom |
| 2 | jerry |"""
delete from t11 where id=2;
select * from t11;
"""
+----+------------------+
| id | name |
+----+------------------+
| 1 | tom |"""
insert into t11(name) values('jack');
select * from t11;
"""
+----+------------------+
| id | name |
+----+------------------+
| 1 | tom |
| 3 | jack | # 自增没有停止，在上一次插入数据的基础上加一
+----+------------------+"""
truncate t11; # 清空数据并且重置主键
insert into t11(name) values('jack');
select * from t11;
"""
+----+------------------+
| id | name |
+----+------------------+
| 1 | jack |"""
表与表之间建关系
"""
定义一张员工表 表中有很多字段
id name gender dep_name dep_desc
"""
# 1 该表的组织结构不是很清晰(可忽视)
# 2 浪费硬盘空间(可忽视)
# 3 数据的扩展性极差(无法忽视的)
# 如何优化？
"""上述问题就类似于你将所有的代码都写在了一个py文件中"""
将员工表拆分 员工表和部门表
外键
"""
外键就是用来帮助我们建立表与表之间关系的
foreign key
"""
表关系
表与表之间最多有四种关系
-
一对多
（在mysql中一对多，多对一都叫一对多。）
-
多对多
-
一对一
-
没有关系
一对多
"""
判断表与表之间的关系的方法：
换位思考，站在表A的角度看表A的一个数据能否对应上表B的多个数据，反过来站在表B的角度看表B的一个数据能否对应表A的多个数据
如：
员工表与部门表：
先站在员工表：思考一个员工能否对应多个部门 不能 （此时还不能得出结论，一定要两张表都考虑完全才能得出结论
在站在部门表：思考一个不能能否对应多个员工 能
得出结论：员工表和部门表是单向的一对多，所以两边的关系是一对多。"""
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('人力资源部','招人开人'),('技术部','接线换灯泡'),('服务部','换水');
insert into emp(name,dep_id) values('tom',2),('jerry',1),('marry',1),('jack',3);
# 虽热我们将两表建立起了关系，但是我们不能对表进行改和删操作，这并不是我们想要的
update dep set id=200 where id=2; 不行
delete from dep; 不行
# 解决办法一 人为的按逻辑先删教学部对应的员工数据之后在删这些员工对应的部门，但是这样操作太过繁琐。
# 解决办法二 让数据做到真正的有关系，你变我也变。
"""
级联更新--》同步更新
级联删除--》同步删除
"""
# 代码实现
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') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
多对多
"""
图书表和作者表
"""
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 author1(
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)
总结：
判断表关系，换位思考，单向一对多为一对多关系，外键建在多的一方；双向一对多为多对多关系，另外建一张表，存储两张表间的数据关系；双向都不满足一对多为一对一关系（或者没有关系），外键建在查询频率较高的一方。
修改表
# 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 旧字段名 新字段名 字段类型(宽度) 约束条件;
"""
复制表
"""
我们sql语句查询的结果其实也是一张虚拟表
"""
create table 表名 select * from 旧表; 不能复制主键 外键 ...
create table new_dep2 select * from dep where id>3;
