mysql 脱产_MySQL表的操作

最新推荐文章于 2022-03-01 21:44:41 发布

Tech-IN

最新推荐文章于 2022-03-01 21:44:41 发布

阅读量102

点赞数

文章标签： mysql 脱产

本文链接：https://blog.csdn.net/weixin_36437537/article/details/113195114

版权

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

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

name

age

sex

phone

job

Tank

female

13651054608

Nick

male

13304320533

Tearcher

Sean

male

13332353222

Jason

male

13332353333

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;

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

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

| id | int(11) | YES | | NULL | |

| age | int(3) | YES | | NULL | |

| sex | enum('男','女') | 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;

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

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

| id | int(11) | YES | | NULL | |

| age | int(3) | YES | | NULL | |

| sex | enum('男','女') | YES | | NULL | |

| job | varchar(11) | YES | | NULL | |

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

6 rows in set (0.01 sec)

#desc查看表结构

mysql> desc user_info;

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

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

| id | int(11) | YES | | NULL | |

| age | int(3) | YES | | NULL | |

| sex | enum('男','女') | 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;

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

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

| id | int(11) | YES | | NULL | |

| age | int(3) | YES | | NULL | |

| sex | enum('male','female') | 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;

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

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

| id | int(11) | YES | | NULL | |

| age | int(3) | YES | | NULL | |

| sex | enum('male','female') | 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;

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

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

| id | int(11) | YES | | NULL | |

| age | int(3) | 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;

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

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

| id | int(4) | YES | | NULL | |

| age | int(3) | 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;

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

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

| id | int(4) | YES | | NULL | |

| age | int(3) | 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;

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

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

| id | int(4) | YES | | NULL | |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | 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;

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

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

| id | int(4) | NO | PRI | NULL | auto_increment |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | 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;

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

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

| id | int(11) | NO | PRI | 0 | |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | 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;

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

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

| id | int(11) | NO | PRI | 0 | |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | NO | | 0 | |

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

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

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

| id | int(4) | NO | PRI | NULL | auto_increment |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | NO | | 0 | |

| job | varchar(11) | YES | | NULL | |

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

rows in set (0.00 sec)

五、删除表

语法：

drop table 表名;

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

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

分析步骤：

先站在左表的角度去找：是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段(通常是id)

再站在右表的角度去找：是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段(通常是id)

总结：

多对一：

如果只有步骤1成立，则是左表多对一右表

如果只有步骤2成立，则是右表多对一左表

多对多

如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

一对一:

如果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其他示例

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

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

Tech-IN

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 脱产_MySQL表的操作

MySQL表操作一、表介绍表就相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段还记得我们之前写过的‘员工信息表作业’么？存储这员工信息的文件是这样的：id,name,age,sex,phone,job1,Tank,83,female,13651054608,IT2,Nick,26,male,13304320533,Tearcher3,Sean,2...
复制链接

扫一扫