MySQL基础——数据库和表的相关操作

一、数据库的操作

1、创建数据库（CREATE）

CREATE DATABASE [IF NOT EXISTS] 数据库名
1

2、使用数据库（USE）

USE 数据库名
1

3、查看已存在的数据库（SHOW）

SHOW DATABASES
1

4、查看MySQL命令行所在的数据库（SELECT）

SELECT DATABASE()
1

5、查看数据库创建信息（SHOW）

SHOW CREATE DATABASE 数据库名 \G
1

6、修改数据库名（没有直接修改的方法）
①重命名需要修改的数据库的表（RENAME）
先创建新的数据库，再将之前数据库中的表，再删除原表。其中重命名：RENAME TABLE 原数据库.表 TO 新数据库.表
②创建新表（CREATE）
将原数据库的表创建到新数据中，其代码为：CREATE TABLE IF NOT EXISTS 新数据库.表 LIKE 原数据库.表
7、删除数据库（DROP）

DROP DATABASE [IF EXISTS] 数据库名
1

二、表的创建

1、创建表的语法及相关设置

CREATE TABLE [IF NOT EXISTS] 表名(
  字段1 数据类型 [约束条件] [默认值], //例如：id INT REMARY KEY AUTO_INCREMENT
  字段2 数据类型 [约束条件] [默认值], //例如：字段1(name) 数据类型(INT) 约束条件(NOT NULL) 默认值(DEFAULT 1)
  字段3 数据类型 [约束条件] [默认值],
  ……
  [表约束条件] //例如：引擎(ENGINE=InnoDB) 编码(DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci)
  );
1
2
3
4
5
6
7

①设置字段非空（NOT NULL）

字段名称 数据类型 NOT NULL
1

②设置字段默认值（DEFAULT）

字段名称 数据类型 DEFAULT 默认值
1

③设置字段自增（AUTO_INCREMENT）
将整数类型的主键设置为默认递增类型，语法：

字段名称 数据类型 AUTO_INCREMENT
1

④指定存储引擎（ENGINE）

ENGINE=存储引擎名称
1

InnoDB（默认）：支持自动增长列AUTO_INCREMENT，支持外键。但占用很大空间，如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性可以选用该引擎。
MyISAM：占用空间小，适用于读操作和插入操作为主，只有很少的更新和删除操作。
MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。
⑤指定编码（CHARACTER SET）

DEFAULT CHARACTER SET 编码 COLLATE 校对规则，或者：DEFAULT CHARSET=编码 COLLATE=校对规则
1

2、主键（非必须）（PRIMARY KEY）
①主键：能够唯一标识表中的一行记录，不能为空。
主表（父表）：两个表具有关联关系时，关联字段中主键所在的表为主表（父表）。
从表（子表）：两个表具有关联关系时，关联字段中外键所在的表为从表（子表）。
②单列主键：可以放在定义列时（字段 INT PRIMARY KEY）或者定义完列之后（PRIMARY KEY(字段)）指定。
③多列主键：只能放在定义完列之后，语法：

PRIMARY KEY (字段1, 字段2, 字段3…., 字段n)
1

//表1 班级表
CREATE TABLE [IF NOT EXISTS] class(
  id INT,
  class_info VARCHAR(30),
  PRIMARY KEY(id) //指定id为主键，也可以放在创建id列时，语法为：id INT PRIMARY KEY
  );
1
2
3
4
5
6

3、外键（非必须）（FOREIGN KEY）
外键： 可以关联数据库中的两张表，外键可为空，当外键不为空时，外键为另一张表的主键。
主表（父表）：两个表具有关联关系时，关联字段中主键所在的表为主表（父表）。
从表（子表）：两个表具有关联关系时，关联字段中外键所在的表为从表（子表）。

//创建外键语法
(CONSTRAINT 外键名) FOREIGN KEY 字段1 (, 字段2, 字段3, …)
  REFERENCES 主表名 主键列1 (, 主键列2, 主键列3, …)
1
2
3

//表2 学生表
CREATE TABLE [IF NOT EXISTS] student(
  id INT PRIMARY KEY,
  student_class INT,
  student_name VARCHAR(30),
  //使student_class关联表1的id
  CONSTRAINT class_id FOREIGN KEY(student_class) REFERENCES class(id)
  ); 
1
2
3
4
5
6
7
8

三、表的操作

1、查看表结构（DESC/SHOW CREATE TABLE）
①采用DESCRIBE/DESC

DESCRIBE 表名，或者：DESC 表名
1

表2查询结果如下

Field：字段名。
Type：数据类型。
Null：当前字段值是否可以为NULL。
Key：数据表中的当前字段是否存在索引。PRI表示当前列是主键列，或者是主键的一部分；UNI表示当前列是UNIQUE标识的唯一索引列，或者是唯一索引列的一部分；MUL表示在当前列中的某个值可以出现多次。
Default：表示当前列是否有默认值，同时会显示当前列的默认值是多少。
Extra：附件信息。
②采用SHOW CREATE TABLE

SHOW CREATE TABLE 表名 \G
1

表2查询结果如下

对比DESCRIBE，该方法不仅能够查看数据表的详细建表语句，还能查看数据表的存储引擎和字符编码等信息，但是我感觉不是很直观。
2、修改表名（rename）

ALTER TABLE (表名) RENAME [TO] (新名)
1

3、删除没有外键约束的表（drop）

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n]
1

4、删除有外键约束的表（先删除从表或者解除外键约束）

//删除外键语法
ALTER TABLE 表名 DROP FOREIGN KEY 外键名
1
2

四、字段的操作

1、添加字段（add column）

ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值] //默认添加到第一列
ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值] AFTER 原有字段名 //添加到原有字段后面after
1
2

2、修改字段名（change）

ALTER TABLE 表名 CHANGE 原有字段名 新字段名 新数据类型 //修改后的数据类型与修改前一直不变
1

3、修改字段数据类型（modify）

ALTER TABLE 表名 MODIFY 字段名 新数据类型 [DEFAULT 默认值]
1

4、修改字段位置（modify）

ALTER TABLE 表名 MIDIFY 字段名 数据类型 FIRST //修改为第一列
ALTER TABLE 表名 MODIFY 字段1名称  字段1的数据类型 AFTER 字段2名称 //修改为指定位置after
1
2

5、删除字段（drop）

ALTER TABLE 表名 DROP 字段名
1