14-MySQL操作数据表

2021-05-12 本文已影响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

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、删除临时表和普通表一样

猜你喜欢

热点阅读