mysql 数据库之表操作
一、表与表之间建关系
(1) 将所有的数据放在一张表内的弊端
表的组织结构不清晰
浪费存储时间
可扩展性极差
---> 类似于将所有的代码写入到一个py文件中 -->解耦部分(2) 如何查找表之间的关系
以员工表和部门表为例：查找表关系需要做到换位思考
(a) 先站在员工表的角度：
找员工表的多条数据能够对应部门表的一条数据
=>多个员工能够属于同一个部门
可以，但不能直接下结论，还需要站在部门表的角度
(b) 再站在部门表的角度：
找部门表的多条数据能够对应员工表的一条数据
=>多个部门表的多条数据能否有同一个员工
不可以
只有站在两表的角度都分析过，才能够下结论：员工表单项多对一部门表二、外键(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.插入数据
新增数据的时候，要先增被关联表中的数据insert into dep(dep_name,dep_desc) values
('外交部','形象代言人'),
('教学部','教书育人'),
('技术部','技术能力有限部门');"""
mysql> select * from dep;
+----+-----------+--------------------------+
| id | dep_name | dep_desc |
+----+-----------+--------------------------+
| 1 | 外交部 | 形象代言人 |
| 2 | 教学部 | 教书育人 |
| 3 | 技术部 | 技术能力有限部门 |
+----+-----------+--------------------------+
"""
insert into emp(name,gender,dep_id) values
('son','male',1),
('gon','male',2),
('vin','male',2),
('ank','male',2),
('rry','female',3);
"""
| id | name | gender | dep_id |
+----+------+--------+--------+
| 1 | son | male | 1 |
| 2 | gon | male | 2 |
| 3 | vin | male | 2 |
| 4 | ank | male | 2 |
| 5 | rry | female | 3 |
+----+------+--------+--------+
5 rows in set (0.00 sec)
"""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;
"""
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (`day39`.`emp`,
CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
上述语句都会报错，原因是 dmp 表设置了外键
"""4. 给外键字段新增功能，同步更新删除（级联删除、级联更新）
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);"""
mysql> select * from emp;
+----+-------+--------+--------+
| id | name | gender | dep_id |
+----+-------+--------+--------+
| 1 | jason | male | 1 |
| 2 | egon | male | 2 |
| 3 | kevin | male | 2 |
| 4 | tank | male | 2 |
| 5 | jerry | female | 3 |
+----+-------+--------+--------+
5 rows in set (0.00 sec)
mysql> select * from dep;
+----+-----------+--------------------------+
| id | dep_name | dep_desc |
+----+-----------+--------------------------+
| 1 | 外交部 | 形象代言人 |
| 2 | 教学部 | 教书育人 |
| 3 | 技术部 | 技术能力有限部门 |
+----+-----------+--------------------------+
"""执行命令：
update dep set id=100 where id=2;
delete from dep where id=100;
结果：
"""
mysql> select * from dep;
+-----+-----------+--------------------------+
| id | dep_name | dep_desc |
+-----+-----------+--------------------------+
| 1 | 外交部 | 形象代言人 |
| 3 | 技术部 | 技术能力有限部门 |
| 100 | 教学部 | 教书育人 |
+-----+-----------+--------------------------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+----+-------+--------+--------+
| id | name | gender | dep_id |
+----+-------+--------+--------+
| 1 | jason | male | 1 |
| 2 | egon | male | 100 |
| 3 | kevin | male | 100 |
| 4 | tank | male | 100 |
| 5 | jerry | female | 3 |
+----+-------+--------+--------+
5 rows in set (0.00 sec)
"""
"""
mysql> select * from emp;
+----+-------+--------+--------+
| id | name | gender | dep_id |
+----+-------+--------+--------+
| 1 | jason | male | 1 |
| 5 | jerry | female | 3 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)
mysql> select * from dep;
+----+-----------+--------------------------+
| id | dep_name | dep_desc |
+----+-----------+--------------------------+
| 1 | 外交部 | 形象代言人 |
| 3 | 技术部 | 技术能力有限部门 |
+----+-----------+--------------------------+
2 rows in set (0.00 sec)
"""5.多对多
图书与作者的例子
一本书可以有多个作者
一个作者可以写多本书
直接创建两个相互更新的表是不可以的，可以通过中间表来实现
即中间表建立两个表的对应关系，两个表内部可以完全不相关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
('活着','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);6.一对一关系
客户表和学生表(客户报名之后就成为了学生)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
);
# 三种外键关系都是用foreign key,区别在于如何使用以及其他条件限制即可做出三种关系三、修改表
mysql 对大小写不敏感
1.语法：
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 旧字段名 新字段名 新数据类型 [完整性约束条件…];四、复制表
# 查询语句执行的结果也是一张表，可以看成虚拟表
# 复制表结构＋记录 （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;