MySQL数据库基础（数据类型，表的结构，约束）

这里操作mysql使用的是命令行和workbench 5.7版本的图形化界面的客户端。

1️⃣预备知识

• SQL中不区分大小写。
• 库名、表名、列名建议不要使用中文，表中的数据可以使用中文。
• 区分三种引用符：
  反引号( ` ),在库名、表名、列名上使用。一般不用反引号，但是名称和关键字重复了就必须带上。
  单引号( ')，双引号( " )，在数据上使用，两者有细小差别，这里不做区分。
  例如：

SELECT `id`,`name` FROM `student_management_system`.`students` WHERE `id` > 10 AND name = `小A`;

• 列名的完整形式：库.表.字段（列名），例如：sys.students.id
  只要没有歧义的情况下，就可以省略库名和表名，例如id
• 为了省略库名，都有个默认库的概念。
  使用命令行：use 数据库名;
  使用workbench，将鼠标移至想要设置的库上，点击鼠标右键，再点击Set as Default Schema，即可将其设置为默认库。

2️⃣数据库的基本操作

1.显示当前数据库

语法

SHOW DATABASES;

示例

2.创建数据库

语法

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,create_specification] ...]

create_specification:
	[DEFAULT] CHARACTER SET charset_name
	[DEFAULT] COLLATE collation_name

注意：库名全部为英文；用下划线，不能用横线；如果出现数字，不要出现在最开始。

说明

• 大写的表示关键字
• [] 是可选项
• CHARACTER SET: 指定数据库采用的字符集
• COLLATE: 指定数据库字符集的校验规则

示例

1.创建名为db_test1 的数据库

CREATE DATABASE db_test1;

说明：当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则
是：utf8_ general_ ci

2.如果系统没有db_test2 的数据库，则创建一个名叫db_test2 的数据库，如果有则不创建

CREATE DATABASE IF NOT EXISTS db_test2;

3.如果系统没有db_test 的数据库，则创建一个使用utf8mb4字符集的db_test 数据库，如果有则不创建

CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4;

说明：MySQL的utf8编码不是真正的utf8，没有包含某些复杂的中文字符。MySQL真正的utf8是使用utf8mb4，建议大家都使用utf8mb4，否则中文就是乱码！

使用workbench

3.使用数据库

语法

use 数据库名;

使用该命令行后，该数据库就变为了默认库。

4.删除数据库

语法

DROP DATABASE [IF EXISTS] db_name;

数据库删除以后，内部看不到对应的数据库，里边的表和数据全部被删除.

示例

drop database if exists db_test1;
drop database if exists db_test2;

3️⃣常用数据类型

1.数值类型

分为整型和浮点型：

数据类型	大小	说明	对应java类型
BIT[ (M) ]	M指定位数，默认为1	二进制数，M范围从1到64，存储数值范围从0到2^M-1	常用Boolean对应BIT，此时默认是1位，即只能存0和1
TINYINT	1字节		Byte
SMALLINT	2字节		Short
INT	4字节	数值类型可以指定为无符号（unsigned），表示不取负数。	Integer
BIGINT	8字节		Long
FLOAT(M, D)	4字节	单精度，M指定长度，D指定小数位数。会发生精度丢失	Float
DOUBLE(M,D)	8字节		Double
DECIMAL(M,D)	M/D最大值+2	双精度，M指定长度，D表示小数点位数。精确数值	BigDecimal
NUMERIC(M,D)	M/D最大值+2	和DECIMAL一样	BigDecimal

2.字符串类型

数据类型	大小	说明	对应java类型
VARCHAR (SIZE)	0-65,535字节	可变长度字符串	String
TINYTEXT	0-255字节	最小长度文本数据	String
TEXT	0-65,535字节	长文本数据	String
MEDIUMTEXT	0-16 777 215字节 对应int的最大值16M字节	中等长度文本数据	String
LONGTEXT	0-4294967295字节 对应long的最大值4G字节	最长文本数据	String
BLOB	0-65,535字节	二进制形式的长文本数据	byte[]

注意：

• varchar是变长的，char是定长的。两者都是字符串
• varchar预分配的空间是不固定的，而char是固定的
• varchar的长度过长后，会自动转为text
• blob用于存储二进制的大对象（图片数据、音乐数据、视频数据）

至于varchar的长度问题，和char之间的区别；什么时候转为text，和text的区别等详细细节，可以点击下面的传送门：
👇
MySQL中varchar和char的区别以及和text的关系

3.日期时间类型

数据类型	大小	说明	对应java类型
DATETIME	8字节	范围从1000到9999年，不会进行时区的检索及转换。	java.util.Date、 java.sql.Timestamp
TIMESTAMP	4字节	范围从1970到2038年，自动检索当前时区并进行转换。	java.util.Date、 java.sql.Timestamp

说明：

• DATE表示年月日，TIME表示时分秒。数据格式表示为"2022-03-06 15:45:38"
• TIMESTAMP 表示时间戳，从1970-01-01经过的秒数

4.重点掌握的三个类型

掌握这三个类型，能满足大部分的场景：

1. 以 INT 为代表的数字类型
2. VARCHAR 为代表的字符串类型
3. DATETIME为代表的日期时间类型

4️⃣表的操作

需要操作数据库中的表时，需要先使用该数据库：

use db_test;

1.查看表结构

desc 表名;

示例：

2.创建表

命令行实现

CREATE TABLE table_name (
列名称 列类型 列约束 列默认值 列注释
);

示例创建一个用户表：

CREATE TABLE `lib_0326`.`customers` (
-- 列名称    列类型       列约束                                   列默认值    列注释
  `cid`     INT          PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name`    varchar(20)  NOT NULL                                           COMMENT '如何称呼客户',
  `address` varchar(200) NOT NULL                                           COMMENT '送货地址',
  `phone`   varchar(20)  NOT NULL                                           COMMENT '客户的联系电话，不会重复',
  `sex`     INT          NOT NULL                               DEFAULT 0   COMMENT '0 代表男，1 代表女',
  UNIQUE KEY `phone_UNIQUE` (`phone`)
) COMMENT='客户表';

workbench实现

3.删除表

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

示例：

--删除 stu_test 表
droptable stu_test;
--如果存在 stu_test 表，则删除 stu_test 表
droptable if exists stu_test;

5️⃣数据库的约束

• NOT NULL - 指示某列不能存储 NULL 值。
• AUTO INCREMENT - 自增长。对于整数类型的主键，常配搭自增长来使用。插入数据对应字段不给值时，使用最大值+1。
• UNIQUE - 保证某列的每行必须有唯一的值。
• DEFAULT - 规定没有给列赋值时的默认值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

对于初学者的行为准则

1. 每张表都有一个 id，具体叫什么无所谓。没有太大的业务含义，只是为了编号的主键，类型是 int，自增。
   勾选pk、nn、ai。
2. 针对每个字段，全部带上Not Null
3. 如果表中还有其他字段，也需要保持唯一，选上uq

6️⃣还原事件

表中的数据记录了逻辑上的数据，需要大概能将其还原到现实发生的事件。

例如现在有三张表：
1.超市货物表

2.会员等级表

3.购买记录表

还原购买记录1为：

贾宝玉在 2022-03-26 17:14:38 时，购买了雪花牛肉。
虽然雪花牛肉的的单价是103块（实际存储的是分，这里换算成元了），但是贾宝玉以139.1的价格购买了三份，只支付了25元。