数据库之表关系

一 表的详细操作

1.修改表名
alter table 旧表名  rename  新表名；
2.修改表中的引擎与字符编码
alter table  表名  engine = "引擎名" charset ="编码名"；

3.复制表  
#3.1只复制表结构以及约束条件
create table 新表名 like  旧表名；
eg1:
select * from t1;
+----+------------+
| id | name       |
+----+------------+
|  0 | guer       |
+----+------------+
#================================================desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | 0       |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
#================================================create table nt like t1;
select * from nt;   #为空表，Empty set (0.04 sec)
desc t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | 0       |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
#================================================

#3.2只复制表的结构，约束不复制
eg2: 
create table nt1 select * from tt where 1=2;  #（where 后面时False的条件均可）
select * from nt1;  #为空表
desc nt1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | 0       |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
#================================================

#3.3复制结构+数据，不复制约束
create table 新表名 select * from 旧表名;
eg3:
create table nt2 select * from t1;
select * from nt2;
+----+------------+
| id | name       |
+----+------------+
|  0 | guer       |
+----+------------+
#================================================
desc nt2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | 0       |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
#================================================

4.清空表
truncate  表名；  #注意：表被重置后，自增字段也被重置，即重头开始。

二 表中字段的详细操作

#创建一个有主键并且主键自增的表t2
create table t2(
    id int primary key auto_increment,
    x int,
    y int
);
#插入数据
insert into t2(x,y) values (10,20),(100,200),(1000,2000);
+----+------+------+
| id | x    | y    |
+----+------+------+
|  1 |   10 |   20 |
|  2 |  100 |  200 |
|  3 | 1000 | 2000 |
+----+------+------+
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| x     | int(11) | YES  |     | NULL    |                |
| y     | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
#================================================
1.修改字段信息
alter table 表名 modify 字段名 类型[(宽度) 约束];
alter table t2 modify x bigint default 0;  # 模式不同, 涉及精度问题
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| x     | bigint(20) | YES  |     | 0       |                |
| y     | int(11)    | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
#================================================
2.修改字段名及信息
alter table 表名 change 旧字段名 新字段名 类型[(宽度) 约束];
alter table t2 change y c char(10) not null; # 模式不同, 涉及类型转换问题
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| x     | bigint(20) | YES  |     | 0       |                |
| c     | char(10)   | NO   |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
+----+------+------------+
| id | x    | c          |
+----+------+------------+
|  1 |   10 | 20         |
|  2 |  100 | 200        |
|  3 | 1000 | 2000       |
+----+------+------------+
3.添加字段名
# 末尾添加
alter table 表名 add 字段名 类型[(宽度) 约束], ..., add 字段名 类型[(宽度) 约束];
alter table t2 add age int, add gender enum("male", "female", "wasai") default "wasai";

# 首尾添加
alter table 表名 add 字段名 类型[(宽度) 约束] first;
# 指定位添加：指定字段后
alter table 表名 add 字段名 类型[(宽度) 约束] after 旧字段名;
alter table t2 add y int after x;
+----+------+------+------+--------+
| id | x    | y    | age  | gender |
+----+------+------+------+--------+
|  1 |   10 |   20 | NULL | wasai  |
|  2 |  100 |  200 | NULL | wasai  |
|  3 | 1000 | 2000 | NULL | wasai  |
+----+------+------+------+--------+
+--------+-------------------------------+------+-----+---------+----------------+
| Field  | Type                          | Null | Key | Default | Extra          |
+--------+-------------------------------+------+-----+---------+----------------+
| id     | int(11)                       | NO   | PRI | NULL    | auto_increment |
| x      | bigint(20)                    | YES  |     | 0       |                |
| y      | int(11)                       | YES  |     | NULL    |                |
| age    | int(11)                       | YES  |     | NULL    |                |
| gender | enum('male','female','wasai') | YES  |     | wasai   |                |
+--------+-------------------------------+------+-----+---------+----------------+
#================================================
4.删除字段名
alter table 表名 drop 字段名;
alter table t2 drop y;
+----+------+------+--------+
| id | x    | age  | gender |
+----+------+------+--------+
|  1 |   10 | NULL | wasai  |
|  2 |  100 | NULL | wasai  |
|  3 | 1000 | NULL | wasai  |
+----+------+------+--------+
+--------+-------------------------------+------+-----+---------+----------------+
| Field  | Type                          | Null | Key | Default | Extra          |
+--------+-------------------------------+------+-----+---------+----------------+
| id     | int(11)                       | NO   | PRI | NULL    | auto_increment |
| x      | bigint(20)                    | YES  |     | 0       |                |
| age    | int(11)                       | YES  |     | NULL    |                |
| gender | enum('male','female','wasai') | YES  |     | wasai   |                |
+--------+-------------------------------+------+-----+---------+----------------+

三 特殊表（mysql.user）=> 用户管理

# 操作前提：登录root用户

1.重要字段
Host | User | Password
root 用户查看当前所有的用户： select Host,User,Password from mysql.user;

2.新建用户
create user 用户名@主机名 identified by '密码'; # 正确
create user zero@localhost identified by 'zero';
+--------------------------------------------------------------+------------------+-------------------------------------------+
| Host                                                         | User             | Password                                  |
+--------------------------------------------------------------+------------------+-------------------------------------------+
| localhost                                                    | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1                                                    | root             |                                           |
| ::1                                                          | root             |                                           |
| localhost                                                    |                  |                                           |
| localhost                                                    | zero             | *98F19B394B58FAC4B01A3D9037B93CA2A17199CF |
+--------------------------------------------------------------+------------------+-------------------------------------------+
#================================================
注：insert into mysql.user(Host,User,Password) values("主机名","用户名",password("密码")); #这种方法是错误

3.设置用户权限
grant 权限们 on 数据库名.表名 to 用户名@主机名 [with grant option];
grant create on db1.* to zero@localhost with grant option;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| test               |
+--------------------+
#=============================================
注：权限有select,delete,update,insert,drop..., all代表所有权限
注：数据库名，表名可以用*替换，代表所有
注：设置权限时如果没有当前用户，会自动创建用户，提倡使用
重点: grant all on db1.* to owen@localhost identified by 'owen'; # (创建用户)设置权限   （推荐使用这个）

4.撤销权限
revoke 权限名 on 数据库名.表名 from 用户名@主机名;
revoke delete on db1.* from owen@localhost;

5.修改密码
set password for 用户名@主机名 = password('新密码'); #在root用户下
set password for owen@localhost = password('123');

set password = password("新密码")  #在当前用户修改自己的密码
6.删除用户  #在root用户下
drop user 用户名@主机名;

四 表关系

社会中存储需要可以构建成表的数据，它们形成的表，往往之间存储某种或某些社会关系，mysql数据库建立表结构就是社会中产生的各种数据，分门别类管理，但Mysql建立的（代码层次的）表之间，同样需要处理表与表之间的关系，形成了多对一|多对多|一对一  3种关系。

多对一

'''
案例：员工employees表 | 部门department表

建表规则：
先建立主表，再建立从表，在从表中设置主表的唯一字段(通常为主键)作为外键

建表语法：
create table 主表(
    id int primary key auto_increment,
    ...
);
create table dep(
    id int primary key auto_increment,
    name varchar(16),
    work varchar(16)
);
create table 从表(
    id int primary key auto_increment,
    ...
    主表_id int, # 只是在从表中起了一个名字, 该名字和主表主键对应,所有起了个见名知义的名字
    foreign key(主表_id) references 主表(唯一字段名id)
    on update cascade
    on delete cascade
);
create table emp(
    id int primary key auto_increment,
    name varchar(16),
    salary float,
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade # 设置级联
    on delete cascade
);

插入记录规则：
先插入主表数据，再插入从表数据
insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课');
insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1);

更新删除数据：
两表间相互影响，先从依赖数据入手，再进行更新删除操作
eg:1
删除主表dep中一个部门
delete from dep where id=1; => 从表emp中属于该部门的员工都被删除了

更新从表emp中一个员工的部门
update emp set dep_id=3 where name='egon'; <= 部门必须存在
insert into dep values(3, '管理部', '吃饭睡觉打豆豆, 明确团队方针');
'''

多对多

案例：作者author表 | 书book表
建表规则：
新建第三张表，通过两个外键形成多对多关系

建表语法：
create table 表1(
    id int primary key auto_increment,
    ...
);
create table book(
    id int primary key auto_increment,
    name varchar(16),
    price int
);
create table 表2(
    id int primary key auto_increment,
    ...
);
create table author(
    id int primary key auto_increment,
    name varchar(16)
);
create table 关系表(
    id int primary key auto_increment,
    表1_id int,
    表2_id int,
    foreign key(表1_id) references 表1(id)
    on update cascade
    on delete cascade,
    foreign key(表2_id) references 表2(id)
    on update cascade
    on delete cascade
);
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
);

一对一

案例：丈夫husband表 | 妻子wife表
建表规则：
未存放外键的表被依赖，称之为左表；存放外键的表示依赖表，称之为右表；先操作左边再操作右表

建表语法：
create table 左表(
    id int primary key auto_increment,
    ...
);
create table husband(
    id int primary key auto_increment,
    name varchar(16)
);
create table 右表(
    id int primary key auto_increment,
    ...
    左表_id int unique, # 一对一的外键需要唯一性
    foreign key(左表_id) references 左表(id)
    on update cascade
    on delete cascade
);
create table wife(
    id int primary key auto_increment,
    name varchar(16),
    husband_id int unique, # 一对一的外键需要唯一性
    foreign key(husband_id) references husband(id)
    on update cascade
    on delete cascade
);