199 MySQL表操作

一、表介绍

表就相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段

还记得我们之前写过的‘员工信息表作业’么？存储这员工信息的文件是这样的：

id,name,age,sex,phone,job
1,Tank,83,female,13651054608,IT
2,Nick,26,male,13304320533,Tearcher
3,Sean,25,male,13332353222,IT
4,Jason,40,male,13332353333,IT

如果把上面这个文件改成一张表,应该是下面这个样子

id	name	age	sex	phone	job
1	Tank	83	female	13651054608	IT
2	Nick	26	male	13304320533	Tearcher
3	Sean	25	male	13332353222	IT
4	Jason	40	male	13332353333	IT

id,name,age,sex,phone,job称为字段，其余的，一行内容称为一条记录。

二、创建表

2.1 语法

create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] );

注意：

在同一张表中，字段名是不能相同
宽度和约束条件可选
字段名和类型是必须的

2.2 建表

mysql> create database staff;
Query OK, 1 row affected (0.00 sec)
mysql> use staff;

Database changed

mysql> create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11));

Query OK, 0 rows affected (0.02 sec)
mysql> show tables;

+-----------------+

| Tables_in_staff |

+-----------------+

| staff_info      |

+-----------------+

row in set (0.00 sec)
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    |       |

+-------+-----------------------+------+-----+---------+-------+

rows in set (0.00 sec)
mysql> select id,name,sex from staff_info;

Empty set (0.00 sec)
mysql> select * from staff_info;

Empty set (0.00 sec)

2.3 插入数据

mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'Tank',83,'female',13651054608,'IT');
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff_info values (2,'Nick',26,'male',13304320533,'Teacher');

Query OK, 1 row affected (0.00 sec)
mysql> insert into staff_info values (3,'Sean',25,'male',13332353222,'IT'),(4,'Jason',40,'male',13332353333,'IT');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from staff_info;

+------+----------+------+--------+-------------+---------+

| id   | name     | age  | sex    | phone       | job     |

+------+----------+------+--------+-------------+---------+

|    1 | Tank     |   83 | female | 13651054608 | IT      |

|    2 | Nick     |   26 | male   | 13304320533 | Teacher |

|    3 | Sean    |   25 | male   | 13332353222 | IT      |

|    4 | Jason |   40 | male   | 13332353333 | IT      |

+------+----------+------+--------+-------------+---------+

rows in set (0.00 sec)

三、查看表结构

查看表结构有两种方式：

describe [tablename];这种方法和desc [tablename];效果相同；可以查看当前的表结构

虽然desc命令可以查看表的定义，但是其输出的信息还不够全面，为了得到更全面的表定义信息，有时候就需要查看创建表的SQL语句，使用show create table语法。除了可以看到表定义之外，还可以看到engine（存储引擎）和charset（字符集）等信息。（\G选项的含义是是的记录能够竖向排列，以便更好的显示内容较长的记录。）

3.1 desc和show create table

mysql> describe 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    |       |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00 sec)
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    |       |

+-------+-----------------------+------+-----+---------+-------+

rows in set (0.00 sec)
mysql> show create table staff_info\G;

*************************** 1. row ***************************

Table: staff_info

Create Table: CREATE TABLE staff_info (

id int(11) DEFAULT NULL,

name varchar(50) DEFAULT NULL,

age int(3) DEFAULT NULL,

sex enum('male','female') DEFAULT NULL,

phone bigint(11) DEFAULT NULL,

job varchar(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

row in set (0.01 sec)
ERROR:

No query specified

四、MySQL支持的数据类型

MySQL支持的数据类型：https://www.cnblogs.com/nickchen121/p/11146370.html

五、MySQL表的完整性约束

MySQL表的完整性约束：https://www.cnblogs.com/nickchen121/p/11146439.html

六、修改表结构

语法：
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 字段名;

6.1 alter操作非空和唯一（了解）

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约束

6.2 alter操作主键（了解）

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删除掉。

6.3 为表添加外键（了解）

创建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;

6.4 示例

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    |       |
+-------+-----------------------+------+-----+---------+-------+
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    |       |

+-------+-----------------------+------+-----+---------+-------+

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    |       |

+-------+-------------+------+-----+---------+-------+

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    |       |

+-------+-----------------------+------+-----+---------+-------+

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    |       |

+-------+-----------------------+------+-----+---------+-------+

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    |       |

+-------+-----------------------+------+-----+---------+-------+

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    |                |

+-------+-----------------------+------+-----+---------+----------------+

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    |       |

+-------+-----------------------+------+-----+---------+-------+

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    |       |

+-------+-----------------------+------+-----+---------+-------+

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    |                |

+-------+-----------------------+------+-----+---------+----------------+

rows in set (0.00 sec)

七、删除表

DROP TABLE 表名;

八、多表结构的分析和创建

8.1 如何找出两张表之间的关系

分析步骤：

先站在左表的角度去找：是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
再站在右表的角度去找：是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
总结：
1. 多对一：
  - 如果只有步骤1成立，则是左表多对一右表
  - 如果只有步骤2成立，则是右表多对一左表
2. 多对多
  - 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
3. 一对一:
  - 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

8.2 建立表之间的关系——一对多

一对多或称为多对一

三张表：出版社，作者信息，书

一对多（或多对一）：一个出版社可以出版多本书

关联方式：foreign key

8.2.1 sql示例

=====================多对一=====================
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)

;

8.2.2 其他示例

班级和学生：一个班级可以对应多个学生，但一个学生只能对应一个班级

主机和机房：一个机房可以有多台主机，但是一个主机只能属于一个机房

8.3 建立表之间的关系——多对多

三张表：出版社，作者信息，书

多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
　　
关联方式：foreign key+一张新的表

8.3.1 sql示例

=====================多对多=====================
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('Nick'),('Tank'),('Oscar'),('Mac');
#每个作者与自己的代表作如下

Nick:

九阳神功

九阴真经

九阴白骨爪

独孤九剑

降龙十巴掌

葵花宝典

Tank:

九阳神功

葵花宝典

Oscar:

独孤九剑

降龙十巴掌

葵花宝典

Mac:

九阳神功
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)

;

8.3.2 其他示例

服务和机器：一个服务可能被部署到多台机器上，一台机器上也可以部署多个服务

学生和课程：一个学生可以选择多门课程，一门课程也可以被多个学生选择

8.4 建立表之间的关系——一对一

两张表：学生表和客户表

一对一：一个学生是一个客户

关联方式：foreign key+unique

8.4.1 sql示例

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)

-> ;

8.4.2 其他示例

例一：一个用户只有一个博客

例二：一个管理员唯一对应一个用户

九、作业

根据表结构合理设计表与表之间的主外键关系和约束，并完成表结构的创建。

197-MySQL表操作-01.png?x-oss-process=style/watermark

posted @ 2019-12-02 18:31 ABDM 阅读(328) 评论(0) 编辑收藏举报

刷新页面返回顶部

登鹳雀楼

白日依山尽，黄河入海流。欲穷千里目，更上一层楼。

199 MySQL表操作

一、表介绍

二、创建表

2.1 语法

2.2 建表

2.3 插入数据

三、查看表结构

3.1 desc和show create table

四、MySQL支持的数据类型

五、MySQL表的完整性约束

六、修改表结构

6.1 alter操作非空和唯一（了解）

6.2 alter操作主键（了解）

6.3 为表添加外键（了解）

6.4 示例

七、删除表

八、多表结构的分析和创建

8.1 如何找出两张表之间的关系

8.2 建立表之间的关系——一对多

8.2.1 sql示例

8.2.2 其他示例

8.3 建立表之间的关系——多对多

8.3.1 sql示例

8.3.2 其他示例

8.4 建立表之间的关系——一对一

8.4.1 sql示例

8.4.2 其他示例

九、作业

公告

登鹳雀楼

白日依山尽，黄河入海流。 欲穷千里目，更上一层楼。

199 MySQL表操作

一、表介绍

二、创建表

2.1 语法

2.2 建表

2.3 插入数据

三、查看表结构

3.1 desc和show create table

四、MySQL支持的数据类型

五、MySQL表的完整性约束

六、修改表结构

6.1 alter操作非空和唯一（了解）

6.2 alter操作主键（了解）

6.3 为表添加外键（了解）

6.4 示例

七、删除表

八、多表结构的分析和创建

8.1 如何找出两张表之间的关系

8.2 建立表之间的关系——一对多

8.2.1 sql示例

8.2.2 其他示例

8.3 建立表之间的关系——多对多

8.3.1 sql示例

8.3.2 其他示例

8.4 建立表之间的关系——一对一

8.4.1 sql示例

8.4.2 其他示例

九、作业

公告

白日依山尽，黄河入海流。欲穷千里目，更上一层楼。