14-MySQL操作数据表

作者: 紫荆秋雪_文 | 来源:发表于2021-05-12 10:44 被阅读0次

一、创建数据表

1、创建空数据表

语法格式

create table [if not exists] 表名(
  字段1, 数据类型 [约束条件] [默认值],
  字段2, 数据类型 [约束条件] [默认值],
  字段3, 数据类型 [约束条件] [默认值],
  字段4, 数据类型 [约束条件] [默认值],
  ....
  [表约束条件]
)

表名在Linux系统上区分大小写，若希望不区分大小写则在MySQL的配置文件 my.cnf 中添加一项配置

lower_case_table_names=1

示例
切换到指定数据库

use goods;

查看当下数据库中的数据表

show tables;

image.png

创建数据表

create table t_goods_category1(
   id int(11),
   t_category varchar(30),
   t_remark varchar(100)                                                                                
 );

创建成功，但是有一条警告，在MySQL8.x版本中创建数据表时，不建议为 INT 类型指定显示长度

Query OK, 0 rows affected, 1 warning (0.04 sec)

重新创建数据表

create table if not exists t_goods_category1(
   id int,
   t_category varchar(30),
   t_remark varchar(100)                                                                                
 );

2、创建数据表时指定主键

2.1、单列主键

在定义列的同时指定主键

字段  数据类型  primary key  [默认值]

示例

create table if not exists t_goods_category2 (
  id int primary key,
  t_category varchar(30),
  t_remark varchar(100)
);

定义完数据表中的所有列之后指定主键

[constraint 约束条件名] primary key [字段名]

示例

create table if not exists t_goods_category3 (
  id int,
  t_category varchar(30),
  t_remark varchar(100),
primary key(id)
);

2.2、多列联合主键

primary key [字段1, 字段2, 字段3, ..., 字段n]

示例

create table if not exists t_goods_category4 (
  t_category_id int,
  t_shop_id int,
  t_category varchar(30),
  t_remark varchar(100),
  primary key(t_category_id, t_shop_id)
);

3、创建数据表时指定外键

语法格式

[constaraint 外键名] foreign key 字段1 [, 字段2 , 字段3 ,...]
references 主表名 主键列1 [, 主键列2 , 主键列3, ... ]

t_goods_category 商品类别表

create table if not exists t_goods_category (
  id int primary key,
  t_category varchar(30),
  t_remark varchar(100)
);

t_goods 商品信息表 t_category_id字段为外键

create table if not exists t_goods (
  id int primary key,
  t_category_id int,
  t_category varchar(30),
  t_name varchar(50),
  t_price decimal(10, 2),
  t_stock int,
  t_upper_time datetime,
  constraint foreign_category foreign key(t_category_id) references t_goods_category(id)
);

创建商品信息表t_goods时，将表t_goods 的 t_categoroy_id 字段作为外键关联到商品类别表 t_goods_category 的主键 id 上

4、创建数据表时指定字段非空

语法格式

字段名称 数据类型 not null

示例

create table if not exists t_goods_category5 (
  id int primary key,
  t_category varchar(30) not null,
  t_remark varchar(100)
);

5、创建数据表时指定默认值

语法格式

字段名称 数据类型 default 默认值

示例

create table if not exists t_goods_category9 (
  id int primary key,
  t_category_id int,
  t_shop_id int default 1,
  t_category varchar(30) not null,
    t_remark varchar(100)
);

6、创建数据表时指定主键默认递增

语法

字段名称 数据类型 auto_increment

示例

create table if not exists t_goods_category10 (
  id int primary key auto_increment,
  t_category varchar(30),
  t_remark varchar(100)
);

7、创建数据表时指定存储引擎

语法格式

engine=存储引擎名称

示例

create table if not exists t_goods_category11 (
  id int primary key auto_increment,
  t_category varchar(30),
  t_remark varchar(100)
) engine=InnoDB;

8、创建数据表时指定编码

语法格式

default character set 编码 collate 校对规则
或
default charset=编码 collate=校对规则

示例

create table if not exists t_goods_category12 (
  id int primary key auto_increment,
  t_category varchar(30),
  t_remark varchar(100)
) engine=InnoDB default character set utf8mb4 collate utf8mb4_0900_ai_ci;

或

create table if not exists t_goods_category12 (
  id int primary key auto_increment,
  t_category varchar(30),
  t_remark varchar(100)
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

二、查看数据表结构

1、使用 describe / desc 语句查看表结构

语法格式

describe 表名称
或
desc 表名称

示例

desc t_goods;

+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| id            | int(11)       | NO   | PRI | NULL    |       |
| t_category_id | int(11)       | YES  | MUL | NULL    |       |
| t_category    | varchar(30)   | YES  |     | NULL    |       |
| t_name        | varchar(50)   | YES  |     | NULL    |       |
| t_price       | decimal(10,2) | YES  |     | NULL    |       |
| t_stock       | int(11)       | YES  |     | NULL    |       |
| t_upper_time  | datetime      | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+

参数说明
- Field：数据表表中的每个字段
- Type：数据表中字段的数据类型
- Null：数据表中的当前字段值是否可以为 NULL
- Key：数据表中的当前字符是否存在索引
  - PRI：表示当前列是主键列，或者是主键的一部分
  - UNI：表示当前列是 UNIQUE 标识的唯一索引列，或者是唯一索引列的一部分
  - MUL：表示当前列中的某个值可以出现多次
- Default：数据表中当前列是否有默认值，同时会显示当前列的默认值是多少
- Extra：表示与当前列相关的附件信息

2、使用 SHOW CREATE TABLE 语句查看表结构

不仅能够查看数据表的详细建表语句，还能查看数据表的存储引擎和字符编码等信息

语法格式

SHOW CREATE TABLE 表名 \G

示例

*************************** 1. row ***************************
       Table: t_goods
Create Table: CREATE TABLE `t_goods` (
  `id` int(11) NOT NULL,
  `t_category_id` int(11) DEFAULT NULL,
  `t_category` varchar(30) DEFAULT NULL,
  `t_name` varchar(50) DEFAULT NULL,
  `t_price` decimal(10,2) DEFAULT NULL,
  `t_stock` int(11) DEFAULT NULL,
  `t_upper_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foreign_category` (`t_category_id`),
  CONSTRAINT `foreign_category` FOREIGN KEY (`t_category_id`) REFERENCES `t_goods_category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

三、修改数据表

1、修改数据表名称

语法格式

alter table 原表名 rename to 新表名

示例

alter table t_goods_category9 rename to t_goods_category6;

2、添加字段

语法格式

alter table 表名 add column 新字段名 数据类型 [not null default 默认值];

示例

alter table t_goods_category6 add column create_time datetime default null;

3、添加字段时指定位置

在表的第一列添加字段

alter table 表名 add column 新字段名 数据类型 [not null default 默认值] first

示例

alter table t_goods_category6 add column update_time datetime default now() first;

在指定字段的后面添加字段

alter table 表名 add column 新字段名 数据类型 [not null default 默认值] after 原有字段名

示例

alter table t_goods_category6 add column ares varchar(100) not null default ' ' after t_stock;

修改结果

*************************** 1. row ***************************
       Table: t_goods_category6
Create Table: CREATE TABLE `t_goods_category6` (
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `id` int(11) NOT NULL,
  `t_category_id` int(11) DEFAULT NULL,
  `t_shop_id` int(11) DEFAULT '1',
  `ares` varchar(100) NOT NULL DEFAULT ' ',
  `t_category` varchar(30) NOT NULL,
  `t_remark` varchar(100) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

4、修改字段名称

语法格式

alter table 表名 change 原有字段名 新字段名 新数据类型

示例

alter table t_goods_category6 change update_time last_modified datetime;

5、修改字段的数据类型

语法格式

alter table 表名 modify 字段名 新数据类型 [default 默认值]

示例

alter table t_goods modify t_price bigint default 0;

6、修改字段的位置

将字段的位置修改为数据库的第一个字段

alter table 表名 modify 字段名 数据类型 first

示例

alter table t_goods_category6 modify id first;

将当前字段的位置修改到某个字段的后面

alter table 表名 modify 字段1名 字段1的数据类型 after 字段2名

示例

alter table t_goods_category6 modify last_modified int after t_shop_id;

7、删除字段

语法格式

alter table 表名 drop 字段名

示例

alter table t_goods_category6 drop ares;

8、修改已有表的存储引擎

语法格式

alter table 表名 engine=存储引擎名称

示例

alter table t_goods_category6 engine=MyISAM;

9、取消数据表的外键约束

语法格式（drop foreign key）

alter table 表名 drop foreign key 外键名

示例

alter table t_goods drop foreign key foreign_category;

四、删除数据表

1、删除没有关联关系的数据表

语法格式

drop table [if exists] 数据表

示例

drop table if exists t_goods_category6;

2、删除有外键约束的主表

直接删除有外键约束的主表时，MySQL 会报错。
- 方式一：先删除有外键约束的 [从表]，在删除主表
- 方式二：先解除外键约束，再删除主表
直接删除主表（t_goods_category t_goods 具有外键约束，t_goods_category为主表，t_goods为从表）
t_goods_category

CREATE TABLE `t_goods_category` (
  `id` int(11) NOT NULL,
  `t_category` varchar(30) DEFAULT NULL,
  `t_remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

t_goods

CREATE TABLE `t_goods` (
  `id` int(11) NOT NULL,
  `t_category_id` int(11) DEFAULT NULL,
  `t_category` varchar(30) DEFAULT NULL,
  `t_name` varchar(50) DEFAULT NULL,
  `t_price` decimal(10,2) DEFAULT NULL,
  `t_stock` int(11) DEFAULT NULL,
  `t_upper_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foreign_category` (`t_category_id`),
  CONSTRAINT `foreign_category` FOREIGN KEY (`t_category_id`) REFERENCES `t_goods_category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

直接删除主表 t_goods_category

drop table t_goods_category;
ERROR 3730 (HY000): Cannot drop table 't_goods_category' referenced by a foreign key constraint 'foreign_category' on table 't_goods'.

取消外键约束

alter table t_goods drop foreign key foreign_category;

再次删除主表 t_goods_category

drop table t_goods_category;

五、MySQL 中的临时表

当需要在数据库中保存一些临时数据时，临时表就显得非常有用了

1、创建临时表

语法格式

create temporary table [ if not exists ] 表名

示例

create temporary table t_temporary_category (
    id int not null primary key auto_increment,
    t_name varchar(30)
);

使用show tables; 无法查看临时表，只能通过查看表结构命令来侧面确认临时表是否创建成功

show create table t_temporary_category \G

2、删除临时表和普通表一样

语法格式

drop table [ if exists ] 表名

简单示例

drop table t_temporary_category

网友评论

MySQL

本文标题：14-MySQL操作数据表

本文链接：https://www.haomeiwen.com/subject/euggdltx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

14-MySQL操作数据表

一、创建数据表

1、创建空数据表

2、创建数据表时指定主键

2.1、单列主键

2.2、多列联合主键

3、创建数据表时指定外键

4、创建数据表时指定字段非空

5、创建数据表时指定默认值

6、创建数据表时指定主键默认递增

7、创建数据表时指定存储引擎

8、创建数据表时指定编码

二、查看数据表结构

1、使用 describe / desc 语句查看表结构

2、使用 SHOW CREATE TABLE 语句查看表结构

三、修改数据表

1、修改数据表名称

2、添加字段

3、添加字段时指定位置

4、修改字段名称

5、修改字段的数据类型

6、修改字段的位置

7、删除字段

8、修改已有表的存储引擎

9、取消数据表的外键约束

四、删除数据表

1、删除没有关联关系的数据表

2、删除有外键约束的主表

五、MySQL 中的临时表

1、创建临时表

2、删除临时表和普通表一样

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL