表的操作补充

表的操作补充：

存储引擎：

innodb:mysql默认引擎

可以支持事务，行锁和外键

blackhole：黑洞，存入数据就立马消失

memory：缓存，通电就有效，断点的丢失

myisam：5.5版本以前使用默认引擎

字段类型：

整型：

tinyint：范围-128 ，127

int：范围-2147483648， 2147483647

bigint：范围：很大

宽度：

​ 指的是展示的宽度，若插入数据的宽度不足定义时的宽度，会以空格补全

浮点型：

float：范围255（总共255位），30（小数的位数）

double：范围255（总共255位），30（小数的位数）

decimal：范围65（总共65位），30（小数的位数）

三者之间最大的区别就是精确度的问题，但是我们使用float

日期类型：

date：年月日

datetime：年月日 时分秒

time： 时分秒

year: 年

timestamp：时间戳 5464842416... 插入数据或创建时，会自动更新时间

枚举与集合

enum（）里面是字符串

set（）里面是字符串

约束条件：

not null：约束不能为空

unique：约束值必须唯一

primary key： 主键 not null +unique

内部会创建索引，索引是用来快速查询某条记录的

索引： 提高查询效率

default： 约束插入数据时的默认值

auto_increment: 自增 # 默认从0开始

zerofill:用0补全空格

unsigned：无符号 指的是没有正负值符号

表与表之间的关系

外键：用来建立两张表之间的关系

一对多

多对多

一对一

修改表的操作

复制表的操作

表与表之间的关系：
一张表：

员工部门表 字段：id name gender dep_name dep_desc

将所有的数据存放在一张表中的弊端：

1.结构不清晰： 不致命

2.浪费空间： 不致命

3.可扩展性极差：致命的弊端

解决上述的问题：

给两张表建立强有力的关系，使用外键

如何确定表与表之间的关系？

员工与部门：多对一

部门对员工：不是多对一

员工表单向： 多对一 部门表

总结：凡是单向 多对一的表关系，称之为一对多的外键关系

外键的语法：

foreign key（当前表中建立关系的外键字段）references 被关联的表名（id）

创建两张表时：

必须先建立被关联表，再建立关联表

    # 被关联表:
       # dep:
            create table dep(
                id int primary key auto_increment,
                dep_name varchar(16),
                dep_desc varchar(255)
            );
            
    # 关联表:
       # emp:
            create table emp(
                id int primary key auto_increment,
                name varchar(16),
                age int,
                gender enum('male', 'female', 'others') default 'male',
                dep_id int not null,
                foreign key(dep_id) references dep(id)
            );  
            

    

插入数据时：

必须先插入被关联表的数据，再插入关联表的数据

# dep
insert into dep(dep_name, dep_desc)values ('计划生育部','管理全国各地的生育情况'),('税收统筹部','管理全国各地的税收情况'),('国家供电局','管理全国各地的供电所');
+----+-----------------+-----------------------------------+
| id | dep_name        | dep_desc                          |
+----+-----------------+-----------------------------------+
|  1 | 计划生育部      | 管理全国各地的生育情况            |
|  2 | 税收统筹部      | 管理全国各地的税收情况            |
|  3 | 国家供电局      | 管理全国各地的供电所              |
+----+-----------------+-----------------------------------+


# emp；
insert into emp(name, age, gender, dep_id) values('tank',17,'male',1),
('yyx',21,'male',2),
('sean',55,'female',2),
('egon',88,'female',3);
+----+------+------+--------+--------+
| id | name | age  | gender | dep_id |
+----+------+------+--------+--------+
|  1 | tank |   17 | male   |      1 |
|  2 | yyx  |   21 | male   |      2 |
|  3 | sean |   55 | female |      2 |
|  4 | egon |   88 | female |      3 |
+----+------+------+--------+--------+

更新数据时：

必须先删除已关联表的dep_id字段，才能修改dep表中的关联id字段

这里注意：

​ 关联表中的数据都可以可以改

​ 但是被关联表中的id字段不可以随便修改，但是其他字段可以修改

​

删除数据时：
先删除关联表中的记录，再删除被关联表中的记录

这里注意，只能用delete删除被关联表记录，truncate 不能删除

关联表的记录两者都可以删除

同时用drop删除表时，也是只能先删除关联表，再去删除被关联表

级联更新与级联删除

on update cascade

on delete cascade

放在外键后面，不用加逗号。

  # 被关联表:
       # dep:
            create table dep(
                id int primary key auto_increment,
                dep_name varchar(16),
                dep_desc varchar(255)
            );
            
    # 关联表:
       # emp:
            create table emp(
                id int primary key auto_increment,
                name varchar(16),
                age int,
                gender enum('male', 'female', 'others') default 'male',
                dep_id int not null,
                foreign key(dep_id) references dep(id)
                on update cascade
                on delete cascade
            );
这里要注意：
就算是设置了级联更新与级联删除，也不可以直接先drop被关联表，但是可以先删除被关联表中的记录。

mysql中没有多对一之说，只有一对多

多对多：

也要站在两张表的位置去思考

所以多对多关系时，必须要利用第三张表，为两张表建立“多对多外键关系”。

# book表
create table book(
	id int primary key auto_increment,
    title varchar(24),
    price int,
    book_content varchar(255)
);

# 作者表
create table author(
	id int primary key auto_increment,
    name varchar(16),
	age int
);

# 关联表
create table book_author(
	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
);

插入数据：

book

insert into book(
title, price,book_content
)values('茶花女',200,'讲述一个茶花女的一生！'),
('python从入门到精通', 2000, '学习如何一夜秃头'),
('三体', 45, '带你探究奇幻的宇宙世界！');

author

insert into author(name, age)values
('yyx',68),
('yyh',68);

book_author:

insert into book_author(
book_id, author_id) values
(1,1),
(
(2,2),
(3,1);

+----+--------------------------+-------+--------------------------------------+
| id | title                    | price | book_content                         |
+----+--------------------------+-------+--------------------------------------+
|  1 | 茶花女                   |   200 | 讲述一个茶花女的一生！               |
|  2 | python从入门到精通       |  2000 | 学习如何一夜秃头                     |
|  3 | 三体                     |    45 | 带你探究奇幻的宇宙世界！             |
+----+--------------------------+-------+--------------------------------------+

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | yyx  |   68 |
|  2 | yyh  |   68 |
+----+------+------+



+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  1 |       1 |         1 |
|  2 |       1 |         2 |
|  3 |       2 |         2 |
|  4 |       3 |         1 |
+----+---------+-----------+

更新或删除都不受约束；

一对一关系：

两张表之间的关系是，一一对应的关系，将一张数据量比较大的表，拆分成两张表

user_info:
    id,name,age,gender,hobby,id_card
user:
    id, name, age, detial_id(外键)
detial:
    id, gender, hobby, id_card
user与detial表之间建立一一对应的关系，foreign key 应该建立在使用频率比较高的一方

# 被关联表
create table customer(
id int primary key auto_increment,
name varchar(16),
media varchar(32)
);

# 关联表
# 外键必须设置为唯一
create table student(
id int primary key auto_increment,
addr varchar(255),
phone char(11),
id_card char(18),
customer_id int unique,
foreign key(customer_id) references customer(id)
on update cascade
on delete cascade
);

# 插入

   insert into customer(name, media) values
            ('hcy', 'facebook'),
            ('zsb1', 'ig'),
            ('zsb2', 'vk'),
            ('hb', '探探');
            
+----+------+----------+
| id | name | media    |
+----+------+----------+
|  1 | hcy  | facebook |
|  2 | zsb1 | ig       |
|  3 | zsb2 | vk       |
|  4 | hb   | 探探     |
+----+------+----------+

  insert into student(addr, phone, id_card, customer_id) values
            ('上海', '15214546711', '440888888888888888', 1),
            ('北京', '18888888888', '440777777777777777', 2);
            
   +----+--------+-------------+--------------------+-------------+
| id | addr   | phone       | id_card            | customer_id |
+----+--------+-------------+--------------------+-------------+
|  1 | 上海   | 15214546711 | 440888888888888888 |           1 |
|  2 | 北京   | 18888888888 | 440777777777777777 |           2 |
+----+--------+-------------+--------------------+-------------+

        

修改表的操作
- 语法：  注意: mysql 关键字不区分大小写
     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;

    只复制表结构
    # 将select * from service where 1=2; ---> 不要真实数据，需要表结构
    mysql> create table new_customer select * from customer where 1=2;