MySQL表的操作

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> use db1
Database changed

# 建表
mysql> create table user_info(id int,name varchar(50),age int(3),sex enum('男','女'),phone bigint(11),job varchar(11));
Query OK, 0 rows affected (0.24 sec)

# 在数据库外建表
# mysql> create table db1.user_info(id int,name varchar(50),age int(3),sex enum('男','女'),phone bigint(11),job varchar(11));

# 查看表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| user_info     |
+---------------+
1 row in set (0.00 sec)

#查看表结构
mysql> desc user_info;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| id    | int(11)         | YES  |     | NULL    |       |
| name  | varchar(50)     | YES  |     | NULL    |       |
| age   | int(3)          | YES  |     | NULL    |       |
| sex   | enum('男','女') | YES  |     | NULL    |       |
| phone | bigint(11)      | YES  |     | NULL    |       |
| job   | varchar(11)     | YES  |     | NULL    |       |
+-------+-----------------+------+-----+---------+-------+
6 rows in set (0.34 sec)

#查看表内记录
mysql> select * from user_info;
Empty set (0.00 sec)

2.3插入数据

# 插入一条记录
mysql> insert into user_info(id,name,age,sex,phone,job) values(1,'Tank',83,'female',13651054608,'IT');
Query OK, 1 row affected, 1 warning (0.02 sec)

# 根据字段插入数据
mysql> insert into user_info values(2,'Nick',26,'male',13304320533,'Teacher');
Query OK, 1 row affected, 1 warning (0.11 sec)

# 插入多条数据
mysql> insert into user_info values(3,'Sean',25,'male',13332353222,'IT'),(4,'Jason',40,'male',13332353333,'IT');
Query OK, 2 rows affected, 2 warnings (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 2

#查看表内数据
mysql> select * from user_info;
+------+-------+------+------+-------------+---------+
| id   | name  | age  | sex  | phone       | job     |
+------+-------+------+------+-------------+---------+
|    1 | Tank  |   83 |      | 13651054608 | IT      |
|    2 | Nick  |   26 |      | 13304320533 | Teacher |
|    3 | Sean  |   25 |      | 13332353222 | IT      |
|    4 | Jason |   40 |      | 13332353333 | IT      |
+------+-------+------+------+-------------+---------+
4 rows in set (0.00 sec)

三、查看表结构

查看表结构有两种方式：

方法一：
describe [表名];

方法二：
desc [表名];

# 两种方法效果相同,可以查看当前的表结构

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

3.1desc和show create table

# describe查看表结构
mysql> describe user_info;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| id    | int(11)         | YES  |     | NULL    |       |
| name  | varchar(50)     | YES  |     | NULL    |       |
| age   | int(3)          | YES  |     | NULL    |       |
| sex   | enum('男','女') | YES  |     | NULL    |       |
| phone | bigint(11)      | YES  |     | NULL    |       |
| job   | varchar(11)     | YES  |     | NULL    |       |
+-------+-----------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

#desc查看表结构
mysql> desc user_info;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| id    | int(11)         | YES  |     | NULL    |       |
| name  | varchar(50)     | YES  |     | NULL    |       |
| age   | int(3)          | YES  |     | NULL    |       |
| sex   | enum('男','女') | YES  |     | NULL    |       |
| phone | bigint(11)      | YES  |     | NULL    |       |
| job   | varchar(11)     | YES  |     | NULL    |       |
+-------+-----------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

# 查看创建表的sql
mysql> show create table user_info\G;
*************************** 1. row ***************************
       Table: user_info
Create Table: CREATE TABLE `user_info` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `phone` bigint(11) DEFAULT NULL,
  `job` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

四、修改表结构

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

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

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

4.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;

4.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 表名;

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

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

分析步骤：

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

6.2简历表之间的关系——一对多

一对多或称为多对一

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

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

关联方式：foreign key

6.2.1sql示例

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

6.2.2其他示例

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

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

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

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

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

6.3.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('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)
;

6.3.2其他示例

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

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

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

两张表：学生表和客户表

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

关联方式：foreign key+unique

6.4.1示例

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

6.4.2其他示例

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

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

posted @ 2019-10-07 17:54 半哑Lin 阅读(381) 评论(0) 编辑收藏举报

刷新页面返回顶部

半哑Lin