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;

转载于:https://www.cnblogs.com/wangtenghui/p/10940868.html