数据库之单表查询
一，表之间的关系
1、把所有数据都存放于一张表的弊端
1、表的组织结构复杂不清晰
2、浪费空间
3、扩展性极差
二，、寻找表与表之间的关系的套路
举例：emp表 dep表 步骤一： part1： 1、先站在左表emp的角度 2、去找左表emp的多条记录能否对应右表dep的一条记录 3、翻译2的意义： 左表emp的多条记录==》多个员工 右表dep的一条记录==》一个部门 最终翻译结果：多个员工是否可以属于一个部门？ 如果是则需要进行part2的流程 part2： 1、站在右表dep的角度 2、去找右表dep的多条记录能否对应左表emp的一条记录 3、翻译2的意义： 右表dep的多条记录==》多个部门 左表emp的一条记录==》一个员工 最终翻译结果：多个部门是否可以包含同一个员工 如果不可以，则可以确定emp与dep的关系只一个单向的多对一 如何实现？ 在emp表中新增一个dep_id字段，该字段指向dep表的id字段 一 快速理解foreign key 员工信息表有三个字段：工号 姓名 部门 公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费 解决方法： 我们完全可以定义一个部门表 然后让员工信息表关联该表，如何关联，即foreign key 二 ，多对一 例子 三张表：出版社，作者信息，书 一对多（或多对一）：一个出版社可以出版多本书 关联方式：foreign key #1、约束1：在创建表时，先建被关联的表dep，才能建关联表emp create table dep( id int primary key auto_increment, dep_name char(10), dep_comment char(60) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) ); #2、约束2：在插入记录时，必须先插被关联的表dep，才能插关联表emp insert into dep(dep_name,dep_comment) values ('sb教学部','sb辅导学生学习，教授python课程'), ('外交部','老男孩上海校区驻张江形象大使'), ('nb技术部','nb技术能力有限部门'); insert into emp(name,gender,dep_id) values ('alex','male',1), ('egon','male',2), ('lxx','male',1), ('wxx','male',1), ('wenzhou','female',3); #3、约束3：更新与删除都需要考虑到关联与被关联的关系 解决方案： 1、先删除关联表emp，再删除被关联表dep，准备重建 mysql> drop table emp; Query OK, 0 rows affected (0.11 sec) mysql> drop table dep; Query OK, 0 rows affected (0.04 sec) 2、重建：新增功能，同步更新，同步删除 create table dep( id int primary key auto_increment, dep_name char(10), dep_comment char(60) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade on delete cascade ); insert into dep(dep_name,dep_comment) values ('sb教学部','sb辅导学生学习，教授python课程'), ('外交部','老男孩上海校区驻张江形象大使'), ('nb技术部','nb技术能力有限部门'); insert into emp(name,gender,dep_id) values ('alex','male',1), ('egon','male',2), ('lxx','male',1), ('wxx','male',1), ('wenzhou','female',3); # 同步删除 mysql> select * from dep; +----+------------------+------------------------------------------------------------------------------------------+ | id | dep_name | dep_comment | +----+------------------+------------------------------------------------------------------------------------------+ | 1 | sb教学部 | sb辅导学生学习，教授python课程 | | 2 | 外交部 | 老男孩上海校区驻张江形象大使 | | 3 | nb技术部 | nb技术能力有限部门 | +----+------------------+------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 1 | alex | male | 1 | | 2 | egon | male | 2 | | 3 | lxx | male | 1 | | 4 | wxx | male | 1 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 5 rows in set (0.00 sec) mysql> delete from dep where id=1; Query OK, 1 row affected (0.02 sec) mysql> select * from dep; +----+------------------+------------------------------------------------------------------------------------------+ | id | dep_name | dep_comment | +----+------------------+------------------------------------------------------------------------------------------+ | 2 | 外交部 | 老男孩上海校区驻张江形象大使 | | 3 | nb技术部 | nb技术能力有限部门 | +----+------------------+------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 2 | egon | male | 2 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 2 rows in set (0.00 sec) #同步更新 mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 2 | egon | male | 2 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 2 rows in set (0.00 sec) mysql> update dep set id=200 where id =2; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dep; +-----+------------------+------------------------------------------------------------------------------------------+ | id | dep_name | dep_comment | +-----+------------------+------------------------------------------------------------------------------------------+ | 3 | nb技术部 | nb技术能力有限部门 | | 200 | 外交部 | 老男孩上海校区驻张江形象大使 | +-----+------------------+------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 2 | egon | male | 200 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 2 rows in set (0.00 sec)
补齐清空表
delete from tb1;
强调：上面的这条命令确实可以将表里的所有记录都删掉，但不会将id重置为0，
所以收该条命令根本不是用来清空表的，delete是用来删除表中某一些符合条件的记录
delete from tb1 where id > 10;
如果要清空表，使用truncate tb1；
作用：将整张表重置
三，多对多
1、什么是多对多两张表之间是一个双向的多对一关系，称之为多对多
如何实现？ 三张表：出版社，作者信息，书 多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多 关联方式：foreign key+一张新的表 建立第三张表，该表中有一个字段fk左表的id，还有一个字段是fk右表的id create table author( id int primary key auto_increment, name char(16) ); create table book( id int primary key auto_increment, bname char(16), price int ); insert into author(name) values ('egon'), ('alex'), ('wxx') ; insert into book(bname,price) values ('python从入门到入土',200), ('葵花宝典切割到精通',800), ('九阴真经',500), ('九阳神功',100) ;
四，一对一
#一对一： 张表：学生表和客户表 一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系 关联方式：foreign key+unique 左表的一条记录唯一对应右表的一条记录，反之也一样 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 ); insert into customer(name,qq,phone) values ('李飞机','31811231',13811341220), ('王大炮','123123123',15213146809), ('守榴弹','283818181',1867141331), ('吴坦克','283818181',1851143312), ('赢火箭','888818181',1861243314), ('战地雷','112312312',18811431230) ; #增加学生 insert into student(class_name,customer_id) values ('脱产3班',3), ('周末19期',4), ('周末19期',5) ;
五，修改表的相关操作
语法： 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 旧字段名 新字段名 新数据类型 [完整性约束条件…]; 六，复制表
六，复制表
复制表结构＋记录 （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;
