1、将所有的数据都放在一张表内产生的弊端:
1.表的组织结构不清晰
2.浪费存储空间
3.可扩展性极差(修改某一个部门的信息的时候~)
2、如何查找表与表之间的关系
书籍和作者 一本书可不可以被多个作者写 可以 一个作者可不可以写多本书 可以 多对多关系 书籍和出版社 一本书可不可以被多个出版社出版 不可以 一个出版社可不可以出版多本书 可以 一对多关系 作者与作者详情表 两个不可以就是 一对一
① 一对多关系:
外键(foreign key) 1.必选要先建被关联表 create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(64) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female','others') not null default 'male', # default后面的默认值空格直接书写即可 dep_id int, foreign key(dep_id) references dep(id) ); 2.插入数据 2.新增数据的时候,要先增被关联表中的数据 insert into dep(dep_name,dep_desc) values ('外交部','形象代言人'), ('教学部','教书育人'), ('技术部','技术能力有限部门'); insert into emp(name,gender,dep_id) values ('jason','male',1), ('egon','male',2), ('kevin','male',2), ('tank','male',2), ('jerry','female',3); 3.修改emp表中的dep_id字段 update emp set dep_id=100 where id=1; update dep set id=100 where id=1; delete from dep where id=2; delete from emp where id>1 and id<5; delete from dep where id=2;
# 给外键字段新增功能 同步更新同步删除(级联删除级联更新)
create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(64) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female','others') not null default 'male', # default后面的默认值空格直接书写即可 dep_id int, foreign key(dep_id) references dep(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); insert into dep(dep_name,dep_desc) values ('外交部','形象代言人'), ('教学部','教书育人'), ('技术部','技术能力有限部门'); insert into emp(name,gender,dep_id) values ('jason','male',1), ('egon','male',2), ('kevin','male',2), ('tank','male',2), ('jerry','female',3); update dep set id=100 where id=2; delete from dep where id=100;
② 多对多关系:
图书与作者 create table book( id int primary key auto_increment, title char(16), price int, author_id int, foreign key(author_id) references author(id) ); create table author( id int primary key auto_increment, name char(16), gender char(16), book_id int, foreign key(book_id) references book(id) ); create table book( id int primary key auto_increment, title char(16), price int ); create table author( id int primary key auto_increment, name char(16), gender char(16) ); create table book2author( id int primary key auto_increment, book_id int, author_id int, foreign key(book_id) references book(id) on update cascade # 同步更新 on delete cascade, # 同步删除 foreign key(author_id) references author(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); insert into book(title,price) values ('jingpinmei','69.96'), ('围城','99.99'), ('python全栈开发','21000'); insert into author(name,gender) values ('jason','male'), ('egon','female'), ('kevin','male'); insert into book2author(book_id,author_id) values (1,1), (1,2), (1,3), (2,1), (2,3), (3,1), (3,2);
③ 一对一关系:
user name password userdetail gender phone addr hobby ... create table customer( id int primary key auto_increment, name char(20) not null, qq char(10) not null, phone char(16) not null ); create table student( id int primary key auto_increment, class_name char(20) not null, customer_id int unique, #该字段一定要是唯一的 foreign key(customer_id) references customer(id) #外键的字段一定要保证unique on delete cascade on update cascade );
3、表的操作
语法:
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段 # modify只能改字段数据类型完整约束,不能改字段名,但是change可以!
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
4,复制表
# 查询语句执行的结果也是一张表,可以看成虚拟表 # 复制表结构+记录 (key不会复制: 主键、外键和索引) create table new_service select * from service; # 只复制表结构 select * from service where 1=2; //条件为假,查不到任何记录 create table new1_service select * from service where 1=2; create table t4 like employees;