MySQL第五章数据库和表的操作

第五章数据库和表的操作

5.1 基础知识

5.1.1 一条数据存储的过程

存储数据是处理数据的第一步。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。
在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

我们要先创建一个数据库，而不是直接创建数据表呢？

因为从系统架构的层次上看，MySQL 数据库系统从大到小依次是数据库服务器、数据库、数据表、数据表的行与列。

MySQL 数据库服务器之前已经安装。所以，我们就从创建数据库开始。

5.1.2 标识符命名规则

数据库名、表名不得超过30个字符，变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
数据库名、表名、字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

5.1.3 MySQL中的数据类型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

其中，常用的几类类型介绍如下：

数据类型	描述
INT	从-2³¹到2³¹-1的整型数据。存储大小为 4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，占用4个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D)	双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D)	高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。
DATE	日期型数据，格式'YYYY-MM-DD'
BLOB	二进制形式的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

5.1 数据库的操作

5.2.1 创建数据库

方式1：创建数据库

CREATE DATABASE 数据库名;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）

CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

数据库重命名?

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

5.2.2 查看数据库

(1) 查看所有数据库

SHOW DATABASES; #有一个S，代表多个数据库

(2) 查看当前正在使用的数据库

SELECT DATABASE();  #使用的一个 mysql 中的全局函数

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| atguigudb  |
+------------+
1 row in set (0.00 sec)

(3) 查看指定库下所有的表

SHOW TABLES FROM 数据库名;

(4) 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

mysql> SHOW CREATE DATABASE db3;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database
                   |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| db3      | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE DATABASE db3\G
*************************** 1. row ***************************
       Database: db3
Create Database: CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

5.2.3 切换数据库

使用/切换数据库

USE 数据库名;

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

5.2.3 修改数据库字符集

更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集;  #比如：gbk、utf8等

一般不会修改数据库，只有在一开始的时候会修改，使用后修改是非常麻烦的，一般创建数据库之前会有非常严格的文档

5.2.4 删除数据库

方式1：删除指定的数据库

DROP DATABASE 数据库名;

方式2：删除指定的数据库（推荐）

DROP DATABASE IF EXISTS 数据库名;

5.2 表的操作

5.2.1 创建表

(1) 自定义表结构

必须具备：
- CREATE TABLE权限
- 存储空间
语法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

必须指定：
- 表名
- 列名(或字段名)，数据类型，长度
可选指定：
- 约束条件
- 默认值
创建表举例1：

-- 创建表
CREATE TABLE emp (
  -- int类型
  emp_id INT,
  -- 最多保存20个中英文字符
  emp_name VARCHAR(20),
  -- 总位数不超过15位
  salary DOUBLE,
  -- 日期类型
  birthday DATE
);

DESC emp;

MySQL在执行建表语句时，将id字段的类型设置为int(11)，这里的11实际上是int类型指定的显示宽度，默认的显示宽度为11。也可以在创建数据表的时候指定数据的显示宽度。

创建表举例2：

CREATE TABLE dept(
    -- int类型，自增
	deptno INT(2) AUTO_INCREMENT,
	dname VARCHAR(14),
	loc VARCHAR(13),
    -- 主键
    PRIMARY KEY (deptno)
);

DESCRIBE dept;

在MySQL 8.x版本中，不再推荐为INT类型指定显示长度，并在未来的版本中可能去掉这样的语法。

(2) 子查询创建表

① 包括数据

使用 AS subquery 选项，将创建表和插入数据结合起来

CREATE TABLE table
	[(col1, col2, col3...)]
	AS subquery;

指定的列和子查询中的列要一一对应
通过列名和默认值定义列

CREATE TABLE emp1 AS SELECT * FROM employees;

CREATE TABLE dept80
AS 
SELECT  employee_id, last_name, salary*12 ANNSAL, hire_date
FROM    employees
WHERE   department_id = 80;

DESCRIBE dept80;

② 只包括结构

CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1 = 2; -- 创建的emp2是空表

这种创建的情况仅仅会包含NOT NULL的Key，但是其他的约束是不会有的

5.2.2 复制表

使用子查询创建表

CREATE TABLE emp1 AS SELECT * FROM employees;

这里是子查询

5.2.2 查看表

在MySQL中创建好数据表之后，可以查看数据表的结构。

MySQL支持使用DESCRIBE/DESC语句查看数据表结构

语法格式如下

DESCRIBE 表名;
DESC 表名;

使用DESCRIBE 或 DESC 命令，表示表结构。

DESCRIBE employees;
或
DESC employees;

mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id    | int(6)      | NO   | PRI | 0       |       |
| first_name     | varchar(20) | YES  |     | NULL    |       |
| last_name      | varchar(25) | NO   |     | NULL    |       |
| email          | varchar(25) | NO   | UNI | NULL    |       |
| phone_number   | varchar(20) | YES  |     | NULL    |       |
| hire_date      | date        | NO   |     | NULL    |       |
| job_id         | varchar(10) | NO   | MUL | NULL    |       |
| salary         | double(8,2) | YES  |     | NULL    |       |
| commission_pct | double(2,2) | YES  |     | NULL    |       |
| manager_id     | int(6)      | YES  | MUL | NULL    |       |
| department_id  | int(4)      | YES  | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

其中，各个字段的含义分别解释如下：

Field：表示字段名称。
Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
Null：表示该列是否可以存储NULL值。
Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，如果有，那么值是多少。
Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

也支持使用SHOW CREATE TABLE语句查看数据表结构。

语法格式如下：

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

5.2.3 修改表结构 ALTER

修改表都是ALTER

修改表指的是修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句可以实现：

向已有的表中添加列
修改现有表中的列
删除现有表中的列
重命名现有表中的列

(1) 追加一个列 ADD

语法格式如下：

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

举例：

ALTER TABLE dept80 
ADD job_id varchar(15);

(2) 修改一个列 MODIFY

可以修改列的
- 数据类型
- 长度
- 默认值
- 位置
- 约束
- 名字
修改字段数据类型、长度、默认值、位置的语法格式如下：

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

举例：

ALTER TABLE	dept80
MODIFY last_name VARCHAR(30);

ALTER TABLE	dept80
MODIFY salary double(9,2) default 1000;

对默认值的修改只影响今后对表的修改
此外，还可以通过此种方式修改列的约束。这里暂先不讲。

(3) 重命名一个列 CHANGE

使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

举例：

ALTER TABLE  dept80 CHANGE department_name dept_name varchar(15);

(4) 删除一个列 DROP

删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP 【COLUMN】字段名

举例：

ALTER TABLE  dept80
DROP COLUMN  job_id;

5.2.4 重命名表

(1) RENAME TABLE

RENAME TABLE emp TO myemp;

(2) ALTER TABLE

ALTER TABLE dept RENAME [TO] detail_dept;  -- [TO]可以省略

必须是对象的拥有者

5.2.5 删除表DROP TABLE

在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
语法格式：

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

IF EXISTS的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

举例：

DROP TABLE dept80;

DROP TABLE 语句不能回滚

5.2.6 清空表TRUNCATE TABLE

当然也可以用DELETE删除全部的

TRUNCATE TABLE语句：
- 删除表中所有的数据
- 释放表的存储空间
举例：

TRUNCATE TABLE detail_dept;

TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
对比：

SET autocommit = FALSE;
  
DELETE FROM emp2; 
#TRUNCATE TABLE emp2;
  
SELECT * FROM emp2;
  
ROLLBACK;
  
SELECT * FROM emp2;

阿里开发规范：

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

5.8 操作注意

5.8.1 清空、删除表等操作

表删除操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信息提示，因此执行删除操时应当慎重。在删除表前，最好对表中的数据进行备份，这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。
同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的备份，因为数据库的改变是无法撤销的，如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

5.8.2 MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即DDL操作要么成功要么回滚。
DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作。
通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表，结果如下：

CREATE DATABASE mytest;

USE mytest;

CREATE TABLE book1(
    book_id INT ,
    book_name VARCHAR(255)
);

SHOW TABLES;

在MySQL 5.7版本中，测试步骤如下：

删除数据表book1和数据表book2，结果如下：

mysql> DROP TABLE book1,book2;  # 这里是没有book2表的，但是有book1表，如果有原子性，book1是不会删除的
ERROR 1051 (42S02): Unknown table 'mytest.book2'

再次查询数据库中的数据表名称，结果如下：

mysql> SHOW TABLES;
Empty set (0.00 sec)

从结果可以看出，虽然删除操作时报错了，但是仍然删除了数据表book1。

在MySQL 8.0版本中，测试步骤如下：

删除数据表book1和数据表book2，结果如下：

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

再次查询数据库中的数据表名称，结果如下：

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1            |
+------------------+
1 row in set (0.00 sec)

从结果可以看出，数据表book1并没有被删除。

posted @ 2023-09-17 19:17 Crispy·Candy 阅读(49) 评论(0) 编辑收藏举报

刷新页面返回顶部

酥心糖

MySQL第五章数据库和表的操作

第五章数据库和表的操作

5.1 基础知识

5.1.1 一条数据存储的过程

5.1.2 标识符命名规则

5.1.3 MySQL中的数据类型

5.1 数据库的操作

5.2.1 创建数据库

数据库重命名?

5.2.2 查看数据库

(1) 查看所有数据库

(2) 查看当前正在使用的数据库

(3) 查看指定库下所有的表

(4) 查看数据库的创建信息

5.2.3 切换数据库

5.2.3 修改数据库字符集

5.2.4 删除数据库

5.2 表的操作

5.2.1 创建表

(1) 自定义表结构

(2) 子查询创建表

① 包括数据

② 只包括结构

5.2.2 复制表

5.2.2 查看表

5.2.3 修改表结构 ALTER

(1) 追加一个列 ADD

(2) 修改一个列 MODIFY

(3) 重命名一个列 CHANGE

(4) 删除一个列 DROP

5.2.4 重命名表

(1) RENAME TABLE

(2) ALTER TABLE

5.2.5 删除表DROP TABLE

5.2.6 清空表TRUNCATE TABLE

5.8 操作注意

5.8.1 清空、删除表等操作

5.8.2 MySQL8新特性—DDL的原子化

公告

酥心糖

MySQL第五章 数据库和表的操作

第五章 数据库和表的操作

5.1 基础知识

5.1.1 一条数据存储的过程

5.1.2 标识符命名规则

5.1.3 MySQL中的数据类型

5.1 数据库的操作

5.2.1 创建数据库

数据库重命名?

5.2.2 查看数据库

(1) 查看所有数据库

(2) 查看当前正在使用的数据库

(3) 查看指定库下所有的表

(4) 查看数据库的创建信息

5.2.3 切换数据库

5.2.3 修改数据库字符集

5.2.4 删除数据库

5.2 表的操作

5.2.1 创建表

(1) 自定义表结构

(2) 子查询创建表

① 包括数据

② 只包括结构

5.2.2 复制表

5.2.2 查看表

5.2.3 修改表结构 ALTER

(1) 追加一个列 ADD

(2) 修改一个列 MODIFY

(3) 重命名一个列 CHANGE

(4) 删除一个列 DROP

5.2.4 重命名表

(1) RENAME TABLE

(2) ALTER TABLE

5.2.5 删除表DROP TABLE

5.2.6 清空表TRUNCATE TABLE

5.8 操作注意

5.8.1 清空、删除表等操作

5.8.2 MySQL8新特性—DDL的原子化

公告

MySQL第五章数据库和表的操作

第五章数据库和表的操作