MySQL表的操作
修改表结构
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
5.修改字段排列顺序/在增加的时候指定字段位置
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
ALTER TABLE 表名
CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
create table t(id int unique,name char(10) not null);
#去掉null约束
alter table t modify name char(10) null;
# 添加null约束
alter table t modify name char(10) not null;
# 去掉unique约束
alter table t drop index id;
# 添加unique约束
alter table t modify id int unique;
alter处理null和unique约束
#去掉null约束
alter table t modify name char(10) null;
# 添加null约束
alter table t modify name char(10) not null;
# 去掉unique约束
alter table t drop index id;
# 添加unique约束
alter table t modify id int unique;
alter处理null和unique约束
1、首先创建一个数据表table_test：
create table table_test(
`id` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`name`)
);
2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。
先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);
注:在增加主键之前,必须先把反复的id删除掉。
create table table_test(
`id` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`name`)
);
2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。
先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);
注:在增加主键之前,必须先把反复的id删除掉。
创建press表
CREATE TABLE `press` (
`id` int(11) NOT NULL,
`name` char(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ；
创建book表
CREATE TABLE `book` (
`id` int(11) DEFAULT NULL,
`bk_name` char(12) DEFAULT NULL,
`press_id` int(11) NOT NULL,
KEY `press_id` (`press_id`)
) ；
为book表添加外键
alter table book add constraint fk_id foreign key(press_id) references press(id);
删除外键
alter table book drop foreign key fk_id;
CREATE TABLE `press` (
`id` int(11) NOT NULL,
`name` char(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ；
创建book表
CREATE TABLE `book` (
`id` int(11) DEFAULT NULL,
`bk_name` char(12) DEFAULT NULL,
`press_id` int(11) NOT NULL,
KEY `press_id` (`press_id`)
) ；
为book表添加外键
alter table book add constraint fk_id foreign key(press_id) references press(id);
删除外键
alter table book drop foreign key fk_id;
mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) # 表重命名 mysql> alter table staff_info rename staff; Query OK, 0 rows affected (0.00 sec) mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) # 删除sex列 mysql> alter table staff drop sex; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec) # 添加列 mysql> alter table staff add sex enum('male','female'); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 # 修改id的宽度 mysql> alter table staff modify id int(4); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec) # 修改name列的字段名 mysql> alter table staff change name sname varchar(20); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) # 修改sex列的位置 mysql> alter table staff modify sex enum('male','female') after sname; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) # 创建自增id主键 mysql> alter table staff modify id int(4) primary key auto_increment; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) # 删除主键，可以看到删除一个自增主键会报错 mysql> alter table staff drop primary key; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key # 需要先去掉主键的自增约束，然后再删除主键约束 mysql> alter table staff modify id int(11); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec) mysql> alter table staff drop primary key; Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 # 添加联合主键 mysql> alter table staff add primary key (sname,age); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 # 删除主键 mysql> alter table staff drop primary key; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 # 创建主键id mysql> alter table staff add primary key (id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | NO | | | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | NO | | 0 | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) # 为主键添加自增属性 mysql> alter table staff modify id int(4) auto_increment; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | NO | | | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | NO | | 0 | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) # 表重命名 mysql> alter table staff_info rename staff; Query OK, 0 rows affected (0.00 sec) mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) # 删除sex列 mysql> alter table staff drop sex; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec) # 添加列 mysql> alter table staff add sex enum('male','female'); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 # 修改id的宽度 mysql> alter table staff modify id int(4); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec) # 修改name列的字段名 mysql> alter table staff change name sname varchar(20); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) # 修改sex列的位置 mysql> alter table staff modify sex enum('male','female') after sname; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) # 创建自增id主键 mysql> alter table staff modify id int(4) primary key auto_increment; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) # 删除主键，可以看到删除一个自增主键会报错 mysql> alter table staff drop primary key; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key # 需要先去掉主键的自增约束，然后再删除主键约束 mysql> alter table staff modify id int(11); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec) mysql> alter table staff drop primary key; Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 # 添加联合主键 mysql> alter table staff add primary key (sname,age); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 # 删除主键 mysql> alter table staff drop primary key; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 # 创建主键id mysql> alter table staff add primary key (id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | NO | | | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | NO | | 0 | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) # 为主键添加自增属性 mysql> alter table staff modify id int(4) auto_increment; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | NO | | | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | NO | | 0 | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
删除表
DROP TABLE 表名;
多表结构的创建与分析
如何找出两张表之间的关系
分析步骤： #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id） #2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id） #3、总结： #多对一： 如果只有步骤1成立，则是左表多对一右表 如果只有步骤2成立，则是右表多对一左表 #多对多 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 #一对一: 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
建立表之间的关系
#一对多或称为多对一 三张表：出版社，作者信息，书 一对多（或多对一）：一个出版社可以出版多本书 关联方式：foreign key
=====================多对一=====================
create table press(
id int primary key auto_increment, name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
;
create table press(
id int primary key auto_increment, name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
;
班级和学生
一个班级可以对应多个学生，但一个学生只能对应一个班级
主机和机房
一个机房可以有多台主机，但是一个主机只能属于一个机房
一个班级可以对应多个学生，但一个学生只能对应一个班级
主机和机房
一个机房可以有多台主机，但是一个主机只能属于一个机房
#多对多 三张表：出版社，作者信息，书 多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多 关联方式：foreign key+一张新的表
=====================多对多=====================
create table author(
id int primary key auto_increment, name varchar(20)
);
#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
#每个作者与自己的代表作如下
egon:
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
create table author(
id int primary key auto_increment, name varchar(20)
);
#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
#每个作者与自己的代表作如下
egon:
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
服务和机器
一个服务可能被部署到多台机器上，一台机器上也可以部署多个服务
学生和课程
一个学生可以选择多门课程，一门课程也可以被多个学生选择
一个服务可能被部署到多台机器上，一台机器上也可以部署多个服务
学生和课程
一个学生可以选择多门课程，一门课程也可以被多个学生选择
#一对一 两张表：学生表和客户表 一对一：一个学生是一个客户 关联方式：foreign key+unique
create table customer(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> qq varchar(10) not null,
-> phone char(16) not null
-> );
create table student(
-> id int primary key auto_increment,
-> class_name varchar(20) not null,
-> customer_id int unique, #该字段一定要是唯一的
-> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
-> on delete cascade
-> on update cascade
-> );
#增加客户
mysql> insert into customer(name,qq,phone) values
-> ('韩蕾','31811231',13811341220),
-> ('杨澜','123123123',15213146809),
-> ('翁惠天','283818181',1867141331),
-> ('杨宗河','283818181',1851143312),
-> ('袁承明','888818181',1861243314),
-> ('袁清','112312312',18811431230)
mysql> #增加学生
mysql> insert into student(class_name,customer_id) values
-> ('脱产1班',3),
-> ('周末1期',4),
-> ('周末1期',5)
-> ;
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> qq varchar(10) not null,
-> phone char(16) not null
-> );
create table student(
-> id int primary key auto_increment,
-> class_name varchar(20) not null,
-> customer_id int unique, #该字段一定要是唯一的
-> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
-> on delete cascade
-> on update cascade
-> );
#增加客户
mysql> insert into customer(name,qq,phone) values
-> ('韩蕾','31811231',13811341220),
-> ('杨澜','123123123',15213146809),
-> ('翁惠天','283818181',1867141331),
-> ('杨宗河','283818181',1851143312),
-> ('袁承明','888818181',1861243314),
-> ('袁清','112312312',18811431230)
mysql> #增加学生
mysql> insert into student(class_name,customer_id) values
-> ('脱产1班',3),
-> ('周末1期',4),
-> ('周末1期',5)
-> ;
例一：一个用户只有一个博客
用户表：
id name
1 egon
2 alex
3 wupeiqi
博客表
fk+unique
id url name_id
1 xxxx 1
2 yyyy 3
3 zzz 2
例二：一个管理员唯一对应一个用户
用户表：
id user password
1 egon xxxx
2 alex yyyy
管理员表：
fk+unique
id user_id password
1 1 xxxxx
2 2 yyyyy
用户表：
id name
1 egon
2 alex
3 wupeiqi
博客表
fk+unique
id url name_id
1 xxxx 1
2 yyyy 3
3 zzz 2
例二：一个管理员唯一对应一个用户
用户表：
id user password
1 egon xxxx
2 alex yyyy
管理员表：
fk+unique
id user_id password
1 1 xxxxx
2 2 yyyyy
练习
根据表结构合理设计表与表之间的主外键关系和约束，并完成表结构的创建。
