MySQL_表操作

• Z时代
• 2024-01-10
• 分类：综合

引擎介绍

mysql中的存储引擎（https://www.cnblogs.com/Eva-J/articles/9682063.html）

表介绍

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

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

id,name,age,sex,phone,job
1,Alex,83,female,13651054608,IT
2,Egon,26,male,13304320533,Tearcher
3,nezha,25,male,13332353222,IT
4,boss_jin,40,male,13332353333,IT

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

id　　

name　　

age　　

sex　　

phone　　

job　　

1

Alex

83

female

13651054608

IT

2

Egon

26

male

13304320533

Teacher

3

nezha

25

male

13332353222

IT

4

boss_jin

40

male

13332353333

IT

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

创建表

#语法：
createtable 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

mysql>createdatabase staff;
Query OK, 1 row affected (0.00 sec)
mysql>use staff;
Database changed
mysql>createtable 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      |
+-----------------+
1 row inset (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||
+-------+-----------------------+------+-----+---------+-------+
6 rows inset (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)

建表

mysql>insertinto staff_info (id,name,age,sex,phone,job) values (1,"Alex",83,"female",13651054608,"IT");
Query OK, 1 row affected (0.00 sec)
mysql>insertinto staff_info values (2,"Egon",26,"male",13304320533,"Teacher");
Query OK, 1 row affected (0.00 sec)
mysql>insertinto staff_info values (3,"nezha",25,"male",13332353222,"IT"),(4,"boss_jin",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| Alex     |83| female |13651054608| IT      |
|2| Egon     |26| male   |13304320533| Teacher |
|3| nezha    |25| male   |13332353222| IT      |
|4| boss_jin |40| male   |13332353333| IT      |
+------+----------+------+--------+-------------+---------+
4 rows inset (0.00 sec)

插入数据

查看表结构

查看表结构有两种方式：

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

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

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||
+-------+-----------------------+------+-----+---------+-------+
6 rows inset (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||
+-------+-----------------------+------+-----+---------+-------+
6 rows inset (0.00 sec)
mysql> show createtable staff_infoG;
***************************1. row ***************************
Table: staff_info
CreateTable: CREATETABLE `staff_info` (
  `id` int(11) DEFAULTNULL,
  `name` varchar(50) DEFAULTNULL,
  `age` int(3) DEFAULTNULL,
  `sex` enum("male","female") DEFAULTNULL,
  `phone` bigint(11) DEFAULTNULL,
  `job` varchar(11) DEFAULTNULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row inset (0.01 sec)
ERROR: 
No query specified

desc和show create table 

mysql中的数据类型

 mysql支持的数据类型（https://www.cnblogs.com/Eva-J/articles/9683316.html）

表的完整性约束

mysql表的完整性约束（https://www.cnblogs.com/Eva-J/articles/9687915.html）

修改表结构

语法：
1. 修改表名
ALTERTABLE 表名 
                      RENAME 新表名;
2. 增加字段
ALTERTABLE 表名
ADD 字段名  数据类型 [完整性约束条件…],
ADD 字段名  数据类型 [完整性约束条件…];
3. 删除字段
ALTERTABLE 表名 
DROP 字段名;
4. 修改字段
ALTERTABLE 表名 
                      MODIFY  字段名 数据类型 [完整性约束条件…];
ALTERTABLE 表名 
                      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTERTABLE 表名 
                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
5.修改字段排列顺序/在增加的时候指定字段位置
ALTERTABLE 表名
ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
ALTERTABLE 表名
ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
ALTERTABLE 表名
                     CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;
ALTERTABLE 表名
                     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约束

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

alter操作主键（了解）

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

为表添加外键（了解）

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 inset (0.00 sec)
# 表重命名
mysql>altertable 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 inset (0.00 sec)
# 删除sex列
mysql>altertable 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 inset (0.01 sec)
# 添加列
mysql>altertable staff add sex enum("male","female");
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 修改id的宽度
mysql>altertable 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 inset (0.01 sec)
# 修改name列的字段名
mysql>altertable 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 inset (0.00 sec)
# 修改sex列的位置
mysql>altertable 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 inset (0.00 sec)
# 创建自增id主键
mysql>altertable staff modify id int(4) primarykey 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 inset (0.00 sec)
# 删除主键，可以看到删除一个自增主键会报错
mysql>altertable staff dropprimarykey;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto columnand it must be defined as a key
# 需要先去掉主键的自增约束，然后再删除主键约束
mysql>altertable 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 inset (0.01 sec)
mysql>altertable staff dropprimarykey;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0
# 添加联合主键
mysql>altertable staff addprimarykey (sname,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 删除主键
mysql>altertable staff dropprimarykey;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0
# 创建主键id
mysql>altertable staff addprimarykey (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 inset (0.00 sec)
# 为主键添加自增属性
mysql>altertable 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 inset (0.00 sec)

示例

删除表

DROPTABLE 表名;

多表结构的创建与分析

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

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

建立表之间的关系

#一对多或称为多对一
三张表：出版社，作者信息，书
一对多（或多对一）：一个出版社可以出版多本书
关联方式：foreign key

=====================多对一=====================
createtable press(
id intprimarykey auto_increment,
name varchar(20)
);
createtable book(
id intprimarykey auto_increment,
name varchar(20),
press_id intnotnull,
foreignkey(press_id) references press(id)
ondeletecascade
onupdatecascade
);
insertinto press(name) values
("北京工业地雷出版社"),
("人民音乐不好听出版社"),
("知识产权没有用出版社")
;
insertinto book(name,press_id) values
("九阳神功",1),
("九阴真经",2),
("九阴白骨爪",2),
("独孤九剑",3),
("降龙十巴掌",2),
("葵花宝典",3)
;

sql示例

班级和学生
一个班级可以对应多个学生，但一个学生只能对应一个班级
主机和机房
一个机房可以有多台主机，但是一个主机只能属于一个机房

其他示例

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

=====================多对多=====================
createtable author(
id intprimarykey auto_increment,
name varchar(20)
);
#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
createtable author2book(
id intnotnullunique auto_increment,
author_id intnotnull,
book_id intnotnull,
constraint fk_author foreignkey(author_id) references author(id)
ondeletecascade
onupdatecascade,
constraint fk_book foreignkey(book_id) references book(id)
ondeletecascade
onupdatecascade,
primarykey(author_id,book_id)
);
#插入四个作者，id依次排开
insertinto author(name) values("egon"),("alex"),("yuanhao"),("wpq");
#每个作者与自己的代表作如下
egon: 
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex: 
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
insertinto 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)
;

sql示例

服务和机器
一个服务可能被部署到多台机器上，一台机器上也可以部署多个服务
学生和课程
一个学生可以选择多门课程，一门课程也可以被多个学生选择

其他示例

#一对一
两张表：学生表和客户表
一对一：一个学生是一个客户
关联方式：foreign key+unique

createtable customer(
-> id intprimarykey auto_increment,
-> name varchar(20) notnull,
-> qq varchar(10) notnull,
-> phone char(16) notnull
-> );
createtable student(
-> id intprimarykey auto_increment,
-> class_name varchar(20) notnull,
-> customer_id intunique, #该字段一定要是唯一的
->foreignkey(customer_id) references customer(id) #外键的字段一定要保证unique
->ondeletecascade
->onupdatecascade
-> );
#增加客户
mysql>insertinto customer(name,qq,phone) values
-> ("韩蕾","31811231",13811341220),
-> ("杨澜","123123123",15213146809),
-> ("翁惠天","283818181",1867141331),
-> ("杨宗河","283818181",1851143312),
-> ("袁承明","888818181",1861243314),
-> ("袁清","112312312",18811431230)
mysql> #增加学生
mysql>insertinto student(class_name,customer_id) values
-> ("脱产1班",3),
-> ("周末1期",4),
-> ("周末1期",5)
-> ;

sql示例

例一：一个用户只有一个博客
    用户表：
    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
11      xxxxx
22      yyyyy

其他示例

作业

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

以上是 MySQL_表操作 的全部内容， 来源链接： utcz.com/z/532480.html