MySQL操作数据库

操作数据库

1. 创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] <数据库名> CHARACTER SET <字符集名> COLLATE <校对规则名>;

其中，[ ]内容表示可选。

• IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。【此选项可以用来避免数据库已经存在而重复创建的错误。】

• **<数据库名>**：创建数据库的名称。【数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。】

• [DEFAULT] CHARACTER SET：指定数据库采用的字符集，默认utf8

• [DEFAULT] COLLATE：指定数据库字符集的校对规则

• • utf8_bin 区分大小写
  • utf8_general_ci 不区分大小写（默认）
  • …

【字符集】是用来定义 MySQL 存储字符串的方式，【校对规则】定义了比较字符串的方式。

在创建数据库/表的时候，为了规避关键字，可以使用反引号（），且使用反引号命名更符合规范。如：CREATE DATABASE database;`

2. 查看数据库的定义信息

可以使用SHOW CREATE DATABASE查看 db01 数据库的定义声明，

mysql> CREATE DATABASE IF NOT EXISTS `db01` CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE DATABASE db01;
+----------+--------------------------------------------------------------------------------+
| Database | Create Database                                                                |
+----------+--------------------------------------------------------------------------------+
| db01     | CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+--------------------------------------------------------------------------------+
1 row in set (0.03 sec)

发现该数据库的指定字符集为 utf8，校对规则为utf8_bin。

如果 MySQL 的版本 ≥ 5.5.3，可以把编码设置为 utf8mb4，utf8mb4 和 utf8 完全兼容，但它支持最新的 Unicode 标准，可以显示 emoji 字符。😀

3. 删除数据库

慎用！

DROP DATABASE [IF EXISTS] db_name;

4. 备份 / 恢复数据库

备份数据库（在 DOS 执行）：mysqldump

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 路径\\文件名.sql

恢复数据库（在 mysql 命令行执行）：source

mysql> source 路径\\文件名.sql;

备份数据库表：mysqldump（不要-B参数）

mysqldump -u 用户名 -p 数据库名 表1 表2 表n > 路径\\文件名.sql

数据类型

1.1 数值类型

可选参数：[UNSIGNED]（无符号）， [ZEROFILL]（补零）

类型名称		字节	类型名称	存储需求（无符号）	有符号	说明
整型		1	tinyint	0-2^8-1（255）	-2^7~2^7-1
		2	smallint	0-2^16-1（65535）	-2^15~2^15-1
		3	mediumint	0-2^24-1（16777215）
		4	int	0-2^32-1
		8	bigint	0-2^64-1
小数	浮点型		float [(M,D)]	单精度 4个字节		- M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度（精度）和小数位数（标度）； - M 和 D 在 FLOAT 和DOUBLE 中是可选的； - 浮点型不指定精度，默认会按照实际的精度（由计算机硬件和操作系统决定）
			double [(M,D)]	双精度 8个字节
	定点型		decimal [(M,D)]	变长，取决于M和D，参见下面讨论		- M：精度，表示有效数字数的总数； - D：标度，表示小数点后的位数； - 要求：M（1~65），D（0~30），D<=M； - D省略默认是0，M省略默认是10，即默认(10,0)； - 常用于货币数据等高精度数据。
位字段类型			BIT(M)	大约(M+7)/8个字节

UNSIGNED

默认为有符号，如果指定unsigned关键字，则代表无符号：

CREATE TABLE t1 (id TINYINT UNSIGNED);

定点类型 DECIMAL 详解

DECIMAL(M,D) 解释示例：

amount DECIMAL(6,2);

表示amount列最多可以存储6位数字，小数位数为2位; 因此，amount列的范围是从-9999.99到9999.99。

DECIMAL默认值：

mysql> CREATE TABLE t3 (d1 DECIMAL);

mysql> desc t3;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| d1    | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

DECIMAL存储需求

DECIMAL（和 NUMERIC）的存储需求与具体版本有关（https://www.mysqlzh.com/doc/110.html）

（https://juejin.cn/post/6952527628616073230）

• 使用二进制格式将9个十进制(基于10)数压缩为4个字节，来表示 DECIMAL 列值。

• 每个值的整数和分数部分的存储分别确定。

• 每个9位数的倍数需要4个字节，并且“剩余的”位需要4个字节的一部分。下表给出了超出位数的存储需求：

  组中包含的十进制位数	占用存储空间大小（单位：字节）
  1或2	1
  3或4	2
  5或6	3
  7或8或9	4

提示：不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

BIT

• BIT(M)。M指定位数，默认值1，范围1~64；
• BIT(M)字段按 位的方式（二进制）显示；
• 查询使用 添加的数值。

1.2 字符串类型

类型名称	说明	存储需求	存储需求	详细说明
CHAR(M)	固定长度非二进制字符串	M 字符数，0<=M<=255	0-2^8-1（255）	查询速度：char > varchar
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M且 0<=M<=255	0-2^16-1（65535）	实际占用空间：实际数据大小 + 13个字节。（本身需要占用13个字节记录存放内容长度）VARCHAR列的有效最大长度为65,532字符。
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB		L+1字节，在此，L<2^8
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8	0-2^8-1
BLOB	小的二进制字符串(字节字符串)	L+2字节，在此，L<2^16		不能有默认值
TEXT	小的非二进制字符串（字符字符串）	L+2字节，在此，L<2^16	0-2^16-1	如果varchar不够用，可以使用texttext不能有默认值
MEDIUMBLOB		L+3字节，在此，L<2^24
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24	0-2^24-1
LONGBLOB		L+4字节，在此，L<2^32
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32	0-2^32-1
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或 多个SET成员	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

详细说明

• char(m)、varchar(m)：m表示字符数，不管是中文还是字母都是按m计算，都以定义好的表的编码来存放。
• 要想计算用于保存具体CHAR、VARCHAR或者TEXT列值的字节数，需要考虑该列使用的字符集。
• VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度(用前面的表中的L表示)，而不是该类型的最大可能的大小。例如，VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串(L)的长度，加上一个记录字符串长度的字节。对于字符串’abcd’，L是4，存储需要5个字节。
• NDBCLUSTER引擎只支持固定宽度的列。这说明MySQL簇中的表中的VARCHAR列的行为如同类型CHAR(不同的是每个记录仍然有一个额外字节空间)。例如，在Cluster表中，声明为VARCHAR(100)的列中的每个记录存储时将占用101个字节，无论实际存储的记录中的字符串的长度为多少。
• BLOB和TEXT类需要 1、2、3或者4个字节来记录列值的长度，取决于该类的最大可能的长度。参见11.4.3节，“BLOB和TEXT类型”。
• 在NDB Cluster存储引擎中，TEXT和BLOB列的实施是不同的，其中TEXT列中的每个记录由两个单独部分组成。一个是固定大小(256字节)，并且实际上保存在原表中。另一个包括超出256字节的任何数据，保存在隐含的表中。第2个表中的记录总是2,000字节长。这说明如果size<= 256，TEXT列的大小为256(其中size表示记录的大小)；否则，大小是256 +size+(2000–(size–256)%2000)。
• ENUM对象的大小由不同的枚举值的数目确定。枚举用一个字节，可以有255个可能的值。当枚举的值位于256和65,535之间时，用两个字节。见11.4.4节，“ENUM类型”。
• SET对象的大小由不同的set成员数量确定。如果set大小是N，对象占(N+7)/8个字节，四舍五入到1、2、3、4或者8个字节。SET最多可以有64个成员。见11.4.5节，“SET类型”。

1.4 日期时间类型

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901 ~ 2155	1 个字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 个字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 个字节

CREATE TABLE T3 (
  birthday DATE,
  job_time DATETIME,
  login_time TIMESTAMP
    NOT NULL 
    DEFAULT CURRENT_TIMESTAMP  -- 可以为【初始值】使用当前的时间戳
    ON UPDATE CURRENT_TIMESTAMP);  -- 可以为【自动更新的值】使用当前的时间戳，TIMESTAMP在INSERT和UPDATE时，自动更新。

在CREATE TABLE语句中，可以使用下列语句声明第1个TIMESTAMP列：

• 用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句，列为默认值使用当前的时间戳，并且自动更新。
• 不用DEFAULT子句但用ON UPDATE CURRENT_TIMESTAMP子句，列有默认值0并自动更新。
• ……

MAXDB 模式

当服务器以MAXDB模式运行时，TIMESTAMP与DATETIME相等。也就是说，如果创建表时服务器以MAXDB模式运行，TIMESTAMP列创建为DATETIME列。结果是，该列使用DATETIME显示格式，有相同的值范围，并且没有自动对当前的日期和时间进行初始化或更新。

要想启用MAXDB模式，在启动服务器时使用--sql-mode=MAXDB服务器选项或在运行时通过设置全局sql_mode变量将SQL服务器模式设置为MAXDB：

mysql> SET GLOBAL sql_mode=MAXDB；

客户端可以按照下面方法让服务器为它的连接以MAXDB模式运行：

mysql> SET SESSION sql_mode=MAXDB;

表的操作

1 创建表

CREATE TABLE table_name (
    field1 datatype,
    field2 datatype,
    field3 datatype
) CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 存储引擎

field：指定列名（字段名） datatype：指定列类型（字段类型）

character set：缺省为所在数据库字符集

collate：缺省为所在数据库校对规则

engine：引擎

字段设置

1. NOT NULL：把字段设置为 “不允许为空”。

2. DEFAULT 'value'：为字段设置默认值。

3. AUTO_INCREMENT：把字段设置为自增。

4. 1. 自增主键的分配，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会通过这种方式初始化：SELECT MAX(ai_col) FROM table_name FOR UPDATE;（来源：https://www.jianshu.com/p/68b2cae7e5ed）
   2. 若中途删除表中记录使自增id断层，可通过ALTER TABLE 表名 AUTO_INCREMENT=n;把自增id重置为指定的值，重新开始计数。

5. PRIMARY KEY (字段名)：把字段设置为主键。

2 修改表

使用ALTER TABLE语句追加(ADD)、修改（MODIFY）、删除（DROP）列，修改字符集（CHARSET / CHARACTER SET），修改列名（CHANGE）

修改表名：RENAME TABLE 表名 TO 新表名，

# 在`resume`列后增加`image`列，设置不允许为空，默认值为空
ALTER TABLE table_name
    ADD [COLUMN] image VARCHAR(32) NOT NULL DEFAULT '' AFTER resume;  -- [COLUMN]可省略

# 把`image`列类型长度修改为60
ALTER TABLE table_name
    MODIFY [COLUMN] image VARCHAR(60);

# 删除`image`列
ALTER TABLE table_name
    DROP [COLUMN] image;
    
# 修改表`emp`字符集为utf8mb4
ALTER TABLE employee 
    CHARSET utf8mb4;
    
# 修改表`emp`名为`employee`
RENAME TABLE emp TO employee;

# 列名`name`修改为`user_name`
ALTER TABLE employee
    CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT '';

3 查看表

DESC table_name;

4 删除表

DROP TABLE table_name;

增删改查

示例：创建一个商品表goods，添加id、goods_name、price字段：

CREATE TABLE `goods` (
    id INT,
    goods_name VARCHAR(10),
    price DOUBLE);

INSERT（增加）

基本语法：INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …);

INSERT [IGNORE] INTO `goods` (id, goods_name, price)  -- [IGNORE]表示如果记录已经存在，就啥事也不干直接忽略
    VALUES(10, 'iphone13 pro max', 9000);

注意事项

1. 插入的数据值类型、长度和顺序要满足字段的要求，且需要一一对应；
2. 列可以插入空值，前提是该字段允许为空，INSERT INTO 表名 (列名1, 列名2, 列名3) VALUE(数据1, 数据2, null);：

INSERT INTO `goods`(id, goods_name, price) VALUES(110, null, null);

1. 添加多条记录：INSERT INTO 表名 (列名1, 列名2, ...) VALUES (), (), ()...;：
2. 如果是给表中所有字段添加数据，可以省略(字段名)：INSERT INTO 表名 VALUES(数据1, 数据2, 数据3);：
3. 默认值，当没有给某个字段赋值，如果该字段有默认值则自动赋默认值，如果该字段没有指定默认值，且没有指定not null，则默认赋null；

另外，自增主键也可以在INSERT中省略

1. 避免插入重复数据的方法（前提条件：插入的数据字段设置了主键或唯一索引）