【数据库】MySQL基础知识：数据类型、基本操作、列的属性

程序员李林

于 2022-09-24 14:51:27 发布

阅读量459

点赞数 1

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/weixin_42659457/article/details/127025256

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文参考《MySQL是怎样使用的》对MySQL的使用进行总结。

第一章 MySQL概述

1.1 简介

使用数据库的原因：持久化（persistence），把数据保存到可掉电式存储设备中以供之后使用；

数据库与数据库管理系统：

DB：数据库（Database）
即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。
DBMS：数据库管理系统（Database Management System）
是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。
SQL：结构化查询语言（Structured Query Language）
专门用来与数据库通信的语言。

关系型数据库（RDBMS）：以行和列的形式存储数据，表与表之间的数据记录有关系（如Oracle、MySQL、MS SQL Server、DB2）；
非关系型数据库（非RDBMS）：基于键值对存储数据，不需要经过SQL层的解析；
- 键值型数据库：通过Key-Value键值的方式来存储数据（如Redis）；
- 文档型数据库：可存放并获取文档，可以是XML、JSON等格式，如MongoDB；
- 搜索引擎数据库：应用在搜索引擎领域的数据存储形式，核心原理是“倒排索引”（如Solr、Elasticsearch）；

表、记录、字段
- E-R（entity-relationship，实体-联系）模型中有三个主要概念是：实体集、属性、联系集；
- 一个实体集对应于数据库中的一个表（table），一个实体（instance）对应于数据库表中的一行（row），也称为一条记录（record）；一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。
表的关联关系：一对一关联、一对多关联、多对多关联、自我引用。

1.2 MySQL的安装

第二章 MySQL使用

2.1 目录结构

主要目录结构：

MySQL的目录结构	说明
bin目录	所有MySQL的可执行文件。如：mysql.exe
MySQLInstanceConfig.exe	数据库的配置向导，在安装时出现的内容
data目录	系统数据库所在的目录
my.ini文件	MySQL的主要配置文件
c:\ProgramData\MySQL\MySQL Server 8.0\data\	用户创建的数据库所在的目录

环境变量的作用：当在命令行解释器中输入一个命令时，命令解释器便会在环境变量Path代表的各个路径下依次查找是否有该可执行文件；

2.2 MySQL语句使用的注意事项

基本规则：
- SQL 可以写在一行或者多行，为了提高可读性，各子句分行写，必要时使用缩进；
- 每条命令以 ; 或 \g 或 \G 结束；
- 使用 \c 放弃本次操作；
- 字符串型和日期时间类型的数据可以使用单引号（’ ‘）表示；
- 列的别名，尽量使用双引号（” “），而且不建议省略as；
SQL大小写规范
- MySQL 在 Windows 环境下是大小写不敏感的，在 Linux 环境下是大小写敏感的；
- 数据库名、表名、表的别名、变量名严格区分大小写；
- 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 忽略大小写；
- 采用统一的书写规范：数据库名、表名、表别名、字段名、字段别名等都小写，SQL 关键字、函数名、绑定变量等都大写；

注释

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

第三章 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数组

MySQL关键字	含义
NULL	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
NOT NULL	FLOAT、DOUBLE
DEFAULT	DECIMAL
PRIMARY KEY	BIT
AUTO_INCREMENT	YEAR、TIME、DATE、DATETIME、TIMESTAMP
UNSIGNED	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

3.1 数值类型

整数类型

整数类型	字节	有符号数取值范围	无符号数取值范围
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT、INTEGER	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

UNSIGNED : 无符号类型（非负），int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)；
ZEROFILL : 0填充，表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可；

浮点类型
- FLOAT 表示单精度浮点数，占用4字节；
- DOUBLE 表示双精度浮点数，占用8字节；
- 精度显示：FLOAT(M,D) 或 DOUBLE(M,D) ，M表示该小数最多包含的有效数字个数，D表示该小数保留小数点后十进制数字的个数；
- 浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准；
定点数类型
- DECIMAL(M,D) ：表示高精度小数，占用M+2字节，取值范围取决于M和D，默认为DECIMAL(10,0)；
- 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及金额计算的场景）；

3.2 日期和时间类型

类型	名称	字节	日期格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

3.3 字符串类型

字符串类型

类型	最大长度	存储空间要求	含义
CHAR(M)	M个字符	M×W字节	固定长度字符串
VARCHAR(M)	M个字符	L+1或L+2字节	可变长度字符串
TINYTEXT	2^8 - 1个字节	L+1字节	非常小型的字符串
TEXT	2^16 - 1个字节	L+2字节	小型的字符串
MEDIUMTEXT	2^32 - 1个字节	L+3字节	中等大小的字符串
LONGTEXT	2^64 - 1个字节	L+4字节	大型的字符串

其中，M代表该数据类型最多能存储的字符数，L代表实际向该类型的列中存储的字符串在特定字符集下所占的字节数，W代表在特定字符集下编码一个字符最多需要的字节数；
- ASCII字符集编码一个字符最多需要1字节；
- GBK字符集编码一个字符最多需要2字节；
- UTF-8字符集编码一个字符最多需要3字节；

ENUM类型和SET类型
- ENUM 类型也叫枚举类型，该列的值只允许从成员中选取单个值，不能一次选取多个；
```
ENUM('str1', 'str2', 'str3'......)
```
- SET 类型，该列的值可以从成员中选取一个或多个值；
```
SET('str1', 'str2', 'str3'......)
```

3.4 二进制类型

BIT类型

二进制字符串类型长度长度范围占用空间
BIT(M) M 1 <= M <= 64 约为(M + 7)/8个字节
**BINARY(M)和VARBARNARY(M)**类型
- BINARY(M)和VARBARNARY(M)用来存放字节，前者是固定长度，后者是可变长度。

二进制字符串类型	长度	长度范围	占用空间
BIT(M)	M	1 <= M <= 64	约为(M + 7)/8个字节

第四章数据库的基本操作

4.1 数据库的基本操作

展示数据库
```
SHOW DATABASES;
```
创建数据库
```
CREATE DATABASE 数据库名;
```
切换默认数据库
```
USE 数据库名;
```

删除数据库

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;

第五章表的基本操作

5.1 展示数据库中的表

展示数据库中的表

SHOW TABLES;
SHOW TABLES FROM 数据库名;

5.2 创建表

基本语法

CREATE TABLE [IF NOT EXISTS] 表名(
列名1, 数据类型 [列的属性],
列名1, 数据类型 [列的属性],
列名1, 数据类型 [列的属性],
……
[表约束条件]
);

为建表语言添加注释

CREATE TABLE [IF NOT EXISTS] 表名(
	各列信息……
)COMMENT '注释';

5.3 删除表

删除表

DROP TABLE 表1, 表2, ...表n;
DROP TABLE IF EXISTS 表名;

5.4 查看表结构

以下查看表结构的语句效果相同：

DESCRIBE 表名;
DESC 表名;
EXPLAIN 表名;
SHOW COLUMNS FROM 表名;
SHOW FIELDS FROM 表名;
SHOW CREATE TABLE 表名\G

5.5 直接使用某个数据库中的某个表

```
数据库名.表名
```

5.6 修改表

修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;
RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2, ... 旧表名n TO 新表名n;

增加列

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

删除列

ALTER TABLE 表名 DROP [COLUMN] 列名;

修改列信息

ALTER TABLE 表名 MODIFY 列名 新数据类型 [新属性];
ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 [新属性];

#修改列的排列位置
ALTER TABLE 表名 MODIFY 列名 数据类型 FIRST; #将某列设置为第一列
ALTER TABLE 表名 MODIFY 列名 数据类型 AFTER 指定列名;#将某列放到指定列的后面

一条语句包含多个修改操作

ALTER TABLE 表名 操作1, 操作2, 操作....;

第六章列的属性

6.1 列的属性

默认值
```
列名 列的类型 DEFAULT 默认值
```

要求表中的某些列必须有值，不能存放NULL

列名 列的类型 NOT NULL #建表时
ALTER TABLE 表名称 MODIFY 列名 列的类型 NOT NULL; #建表后
ALTER TABLE 表名称 MODIFY 列名 列的类型 NULL; #删除非空约束

主键约束PRIMARY KEY
- 在表中可以通过某个列或某些列的组合来唯一确定一条记录，称为候选键；
- 一个表可能有多个候选键，选择一个候选键作为表的主键；
```
直接在列后声明 PRIMARY KEY #建表时指定主键约束
[CONSTRAINT 约束名] PRIMARY KEY (列名1, 列名2...) #多列组合的复合主键约束必须如此声明
ALTER TABLE 表名称 ADD PRIMARY KEY(列列表) #建表后增加主键约束
```
- 如果在创建表的时候声明了主键，MySQL会对插入的记录进行校验，若主键值已经在表中存在，那就会报错；
- 主键列默认具有NOT NULL属性，如果填入NULL会报错；
- 删除主键约束
```
ALTER TABLE 表名称 DROP PRIMARY KEY
```

**唯一约束UNIQUE **

用来限制某个列名/某列的值不能重复；

同一个表可以有多个唯一约束；

直接在列后声明 UNIQUE #建表时指定唯一约束
[CONSTRAINT 约束名] UNIQUE (列名1, 列名2...) #多列组合的复合唯一约束必须如此声明
ALTER TABLE 表名称 ADD UNIQUE KEY(列列表) #建表后增加唯一约束
ALTER TABLE 表名称 MODIFY 列名 列的类型 UNIQUE;

唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一；
唯一约束的列可以存放NULL，这一点与主键约束不同；

外键约束FOREIGN KEY
- 如果A表中的某个列或某些列依赖于B表中的某个列或某些列，则称A表为子表，B表为父表，子表和父表可以用外键关联起来；
```
[CONSTRAINT 外键名称] FOREIGN KEY（列名1, 列名2...) REFERENCE 主表名(列名1, 列名2...)
```
自增列AUTO_INCREMENT
```
ALTER TABLE 表名称 MODIFY 列名 列的类型 AUTO_INCREMENT;#给这个字段增加自增约束
ALTER TABLE 表名称 MODIFY 列名 列的类型; #去掉auto_increment相当于删除
```
- 一个表最多只能有一个自增长列;
- 自增约束的列的数据类型必须是整数类型；
- 如果自增列手动指定了具体值，直接赋值为具体值；
- 具有自增约束的列必须建立索引；
- 具有自增属性的列不能再通过指定DEFAULT来设置默认值；

DEFAULT约束

列名 列的类型 DEFAULT 默认值 #建表时
ALTER TABLE 表名称 MODIFY 列名 列的类型 DEFAULT 默认值;

显示宽度与ZEROFILL
- 对于无符号整形，如果想在查询结果中让数字左边补0，那就得给该列加ZEROFILL属性，若未声明UNSIGNED属性，则MySQL会自动生成；
```
i1 INT UNSIGNED ZEROFILL
```
- 不同的整数类型支持不同的显示宽度，INT 的默认显示宽度是11，加了UNSIGNED则变为10，但显示宽度并不会影响到列所需的存储空间及存储范围；
- 只有列的实际值的位数小于显示宽度时才会在左侧补零；