MySQL基础部分

数据库

发布日期: 2024-01-24

更新日期: 2024-04-04

文章字数: 3.8k

阅读时长: 15 分

阅读次数:

第10章创建和管理表

1. 基础知识

1.1 标识符命名规则

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

1.2 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^31^~2^31^-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

2. 创建和管理数据库

2.1 创建数据库

方式1：创建数据库
```
CREATE DATABASE 数据库名;
```

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

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

方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）
```
CREATE DATABASE IF NOT EXISTS 数据库名;
```

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

2.2 使用数据库

查看当前所有的数据库

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

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

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

查看指定库下所有的表
```
SHOW TABLES FROM 数据库名;
```

查看数据库的创建信息

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

使用/切换数据库
```
USE 数据库名;
```

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

2.3 修改数据库

更改数据库字符集

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

2.4 删除数据库

方式1：删除指定的数据库
```
DROP DATABASE 数据库名;
```
方式2：删除指定的数据库（推荐）
```
DROP DATABASE IF EXISTS 数据库名;
```

3. 创建表

3.1 创建方式1

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

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

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

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

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

3.2 创建方式2

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

CREATE TABLE table [(column, column...)]
AS 子查询(subquery);

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

CREATE TABLE emp1 AS SELECT * FROM employees; 

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

3.3 查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用DESCRIBE/DESC 语句查看数据表结构，也支持使用SHOW CREATE TABLE语句查看数据表结构。

语法格式如下：

SHOW CREATE TABLE 表名\G

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

4. 修改表

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

使用 ALTER TABLE 语句可以实现：

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

4.1 追加一个列

语法格式如下：

ALTER TABLE 表名
ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];

4.2 修改一个列

修改字段数据类型、长度、默认值、位置的语法格式如下：

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

对默认值的修改只影响今后对表的修改

4.3 重命名一个列

语法格式如下：

ALTER TABLE 表名
CHANGE [column] 旧列名 新列名 新数据类型;

4.4 删除一个列

语法格式如下：

ALTER TABLE 表名 
DROP [COLUMN] 字段名;

5. 重命名表

方式一：使用RENAME（推荐）
```
RENAME TABLE emp 
TO myemp;
```

方式二：

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

必须是对象的拥有者

6. 删除表

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

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

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

DROP TABLE 语句不能回滚

7. 清空表

TRUNCATE TABLE语句：
- 删除表中所有的数据(并不是删除表)
- 释放表的存储空间
举例：
```
TRUNCATE TABLE detail_dept;
```
TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
COMMIT:提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。
对比：
```
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 语句相同

DDL 和 DML 的说明

DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的。)
DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了 SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

8. 内容拓展

8.1 拓展1：阿里巴巴《Java开发手册》之MySQL字段命名

【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
- 正例：aliyun_admin，rdc_config，level3_name
- 反例：AliyunAdmin，rdcConfig，level_3_name
【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
【强制】表必备三字段：id, gmt_create, gmt_modified。
- 说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新
【推荐】表的命名最好是遵循 “业务名称_表的作用”。
- 正例：alipay_task 、 force_project、 trade_config
【推荐】库名与应用名称尽量一致。
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

8.2 拓展2：如何理解清空表、删除表等操作需谨慎？！

表删除操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信息提示，因此执行删除操作时应当慎重。在删除表前，最好对表中的数据进行备份，这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。

同样的，在使用ALTER TABLE进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的备份，因为数据库的改变是无法撤销的，如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

8.3 拓展3：MySQL8新特性—DDL的原子化

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

9. 练习1

#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作

#2. 创建表dept01
/*
字段 类型
id INT(7)
NAME VARCHAR(25)
*/

#3. 将表departments中的数据插入新表dept02中

#4. 创建表emp01
/*
字段 类型
id INT(7)
first_name VARCHAR (25)
last_name VARCHAR(25)
dept_id INT(7)
*/

#5. 将列last_name的长度增加到50

#6. 根据表employees创建emp02

#7. 删除表emp01

#8. 将表emp02重命名为emp01

#9.在表dept02和emp01中添加新列test_column，并检查所作的操作

#10.直接删除表emp01中的列 department_id

9.1 题目1

#创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作
CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET utf8mb4

9.2 题目2

#创建表dept01
/*
字段 类型
id INT(7)
NAME VARCHAR(25)
*/
CREATE TABLE IF NOT EXISTS dept01(
    id INT(7),
    `name` VARCHAR(25)
)

9.3 题目3

#将表departments中的数据插入新表dept02中
CREATE TABLE IF NOT EXISTS dept02
AS
SELECT * FROM atguigudb.departments

9.4 题目4

#创建表emp01
/*
字段 类型
id INT(7)
first_name VARCHAR (25)
last_name VARCHAR(25)
dept_id INT(7)
*/
CREATE TABLE IF NOT EXISTS emp01
(
    id INT(7),
    first_name VARCHAR (25),
    last_name VARCHAR(25),
    dept_id INT(7)
)

9.5 题目5

#将列last_name的长度增加到50
ALTER TABLE emp01
MODIFY COLUMN last_name VARCHAR(50)

9.6 题目6

#根据表employees创建emp02
CREATE TABLE IF NOT EXISTS emp02
AS 
SELECT * FROM atguigudb.employees

9.7 题目7

#删除表emp01
DROP TABLE IF EXISTS emp01

9.8 题目8

#将表emp02重命名为emp01
RENAME TABLE emp02 
TO 
emp01

9.9 题目9

#在表dept02和emp01中添加新列test_column，并检查所作的操作
ALTER TABLE dept02
ADD COLUMN test_column CHAR;
DESC dept02;

#在表dept02和emp01中添加新列test_column，并检查所作的操作
ALTER TABLE emp01
ADD COLUMN test_column CHAR;
DESC emp01;

9.10 题目10

#直接删除表emp01中的列 department_id
ALTER TABLE emp01
DROP COLUMN department_id;
DESC emp01;

10 练习2

# 1、创建数据库 test02_market

# 2、创建数据表 customers

字段名	数据类型
c_num	int
c_name	varchar(50)
c_contact	varchar(50)
c_city	varchar(50)
c_brith	date

# 3、将 c_contact 字段移动到 c_birth 字段后面

# 4、将 c_name 字段数据类型改为 varchar(70)

# 5、将c_contact字段改名为c_phone

# 6、增加c_gender字段到c_name后面，数据类型为char(1)

# 7、将表名改为customers_info

# 8、删除字段c_city

10.1 题目1

#创建数据库 test02_market
CREATE DATABASE IF NOT EXISTS test02_market;

10.2 题目2

#创建数据表 customers
USE test02_market;
CREATE TABLE IF NOT EXISTS customers
(
    c_num INT,
    c_name VARCHAR(50),
    c_contact VARCHAR(50),
    c_city VARCHAR(50),
    c_brith DATE
);
DESC customers;

10.3 题目3

#将 c_contact 字段移动到 c_birth 字段后面
ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_brith;
DESC customers;

10.4 题目4

#将 c_name 字段数据类型改为 varchar(70)
ALTER TABLE customers
MODIFY COLUMN c_name VARCHAR(70);
DESC customers;

10.5 题目5

#将c_contact字段改名为c_phone
ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);
DESC customers;

10.6 题目6

#增加c_gender字段到c_name后面，数据类型为char(1)
ALTER TABLE customers
ADD COLUMN c_gender CHAR AFTER c_name;
DESC customers;

10.7 题目7

#将表名改为customers_info
RENAME TABLE customers
TO 
customers_info;

10.8 题目8

#删除字段c_city
ALTER TABLE customers_info
DROP COLUMN c_city;
DESC customers_info;

11. 练习3

# 1、创建数据库test03_company

# 2、创建表offices

字段名	数据类型
officeCode	int
city	varchar(30)
address	varchar(50)
country	varchar(50)
postalCode	varchar(25)

# 3、创建表employees

字段名	数据类型
empNum	int
lastName	varchar(50)
firstName	varchar(50)
mobile	varchar(25)
code	int
jobTitle	varchar(50)
birth	date
note	varchar(255)
sex	varchar(5)

# 4、将表employees的mobile字段修改到code字段后面

# 5、将表employees的birth字段改名为birthday

# 6、修改sex字段，数据类型为char(1)

# 7、删除字段note

# 8、增加字段名favoriate_activity，数据类型为varchar(100)

# 9、将表employees的名称修改为 employees_info

11.1 题目1

#创建数据库test03_company
CREATE DATABASE IF NOT EXISTS test03_company;
USE test03_company;

11.2 题目2

#创建表offices
CREATE TABLE IF NOT EXISTS offices
(
    officeCode INT,
    city VARCHAR(30),
    address VARCHAR(50),
    country VARCHAR(50),
    postalCode VARCHAR(25)
);

DESC offices;

11.3 题目3

#创建表employees
CREATE TABLE IF NOT EXISTS employees
(
    empNum INT,
    lastName VARCHAR(50),
    firstName VARCHAR(50),
    mobile VARCHAR(25),
    CODE INT,
    jobTitle VARCHAR(50),
    birth DATE,
    note VARCHAR(255),
    sex VARCHAR(5)
);

DESC employees;

11.4 题目4

#将表employees的mobile字段修改到code字段后面
ALTER TABLE employees
MODIFY COLUMN mobile VARCHAR(25) AFTER CODE;
DESC employees;

11.5 题目5

#将表employees的birth字段改名为birthday
ALTER TABLE employees
CHANGE birth birthday DATE;
DESC employees;

11.6 题目6

#修改sex字段，数据类型为char(1)
ALTER TABLE employees
MODIFY COLUMN sex CHAR(1);
DESC employees;

11.7 题目7

#删除字段note
ALTER TABLE employees
DROP COLUMN note;
DESC employees;

11.8 题目8

#增加字段名favoriate_activity，数据类型为varchar(100)
ALTER TABLE employees 
ADD COLUMN favoriate_activity VARCHAR(100);
DESC employees;

11.9 题目9

#将表employees的名称修改为 employees_info
RENAME TABLE employees
TO 
employees_info;

念心卓

http://blog.cqwulyj.cn/posts/1e4215b0.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源念心卓 !

MySQL基础部分

MySQL基础—16章(变量、流程控制与游标)

2024-01-24 数据库

MySQL基础部分

MySQL基础—11章(数据处理之增删改)

2024-01-24 数据库

MySQL基础部分

MySQL基础—10章(创建和管理表)

第10章 创建和管理表

1. 基础知识

1.1 标识符命名规则

1.2 MySQL中的数据类型

2. 创建和管理数据库

2.1 创建数据库

2.2 使用数据库

2.3 修改数据库

2.4 删除数据库

3. 创建表

3.1 创建方式1

3.2 创建方式2

3.3 查看数据表结构

4. 修改表

4.1 追加一个列

4.2 修改一个列

4.3 重命名一个列

4.4 删除一个列

5. 重命名表

6. 删除表

7. 清空表

8. 内容拓展

8.1 拓展1：阿里巴巴《Java开发手册》之MySQL字段命名

8.2 拓展2：如何理解清空表、删除表等操作需谨慎？！

8.3 拓展3：MySQL8新特性—DDL的原子化

9. 练习1

9.1 题目1

9.2 题目2

9.3 题目3

9.4 题目4

9.5 题目5

9.6 题目6

9.7 题目7

9.8 题目8

9.9 题目9

9.10 题目10

10 练习2

10.1 题目1

10.2 题目2

10.3 题目3

10.4 题目4

10.5 题目5

10.6 题目6

10.7 题目7

10.8 题目8

11. 练习3

11.1 题目1

11.2 题目2

11.3 题目3

11.4 题目4

11.5 题目5

11.6 题目6

11.7 题目7

11.8 题目8

11.9 题目9

第10章创建和管理表