MySQL数据库基础详细教程

Smile丶Life丶

已于 2022-11-14 18:03:27 修改

阅读量1.2k

点赞数

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

于 2022-11-05 20:14:57 首次发布

本文链接：https://blog.csdn.net/qq_43125235/article/details/125753382

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前言

MySQL 是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。
如需在Windows系统安装请参考安装教程。本教程适用于MySQL8.0以后版本。
本文为基础教程，后续更新中级、高级教程。

一、数据库的数据类型

MySQL 支持多种类型，常用的可以分为三类：数值、字符串和日期/时间类型。另外还有空间数据类型、JSON数据类型，这里不做介绍。
[]表示类型定义的可选属性，没有[]表示必选属性。

1.1、数值类型

MySQL 支持所有标准 SQL 数值数据类型。
这些类型包括严格数值数据类型(TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT)，浮点数值数据类型(FLOAT、DOUBLE），定点类型 DECIMAL ，位值数据类型BIT。
1、整数类型
对于整数数据类型，M表示最大显示宽度；UNSIGNED表示无符号，不允许负值，默认有符号；ZEROFILL零填充，默认无填充。最大显示宽度为255。

类型及属性	大小	范围（有符号）	范围（无符号）	类型描述
`TINYINT[(M)] [UNSIGNED] [ZEROFILL]`	`1 bytes`	`(-128,127)`	`(0,255)`	极小整数
`SMALLINT[(M)] [UNSIGNED] [ZEROFILL]`	`2 bytes`	`(-32768,32767)`	`(0,65535)`	小整数
`MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]`	`3 bytes`	`(-2` ²³`,2`²³`-1)`	`(0,2`²⁴ `)`	中等整数
`INT[(M)] [UNSIGNED] [ZEROFILL]`或 `INTEGER[(M)] [UNSIGNED] [ZEROFILL]`	`4 bytes`	`(-2` ³¹`,2`³¹`-1)`	`(0,2`³² `)`	整数
`BIGINT[(M)] [UNSIGNED] [ZEROFILL]`	`8 bytes`	`(-2` ⁶³`,2`⁶³`-1)`	`(0,2`⁶⁴ `)`	大整数

从MySQL 8.0.17开始，整数数据类型的显示宽度(M)属性已弃用。

2、浮点数类型
对于浮点数据类型，M是可以存储的总位数，D是小数点后的位数，UNSIGNED表示无符号，不允许负值，默认有符号，从MySQL 8.0.17开始，该属性已被弃用；ZEROFILL零填充，默认无填充。

类型	大小	描述
`FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]`	`4 bytes`	一个小（单精度）浮点数。如果省略 M 和 D，则值将存储到硬件允许的限制。单精度浮点数精确到大约`7`位小数。`FLOAT(M,D)`是一个非标准的 MySQL 扩展。从MySQL 8.0.17开始，对浮点数类型，此语法已被弃用。
`FLOAT(p) [UNSIGNED] [ZEROFILL]`	`4 bytes`	浮点数。`p`表示以位为单位的精度，但MySQL仅使用此值来确定是使用`FLOAT`还是`DOUBLE`作为结果数据类型。如果`p`介于`0`到 `24`之间，则数据类型变为`FLOAT`，没有`M`或`D`值。如果`p`从`25`到`53`，则数据类型变为`DOUBLE`，没有`M`或`D`值。结果列的范围与单精度`FLOAT`或双精度`DOUBLE`数据类型的范围相同。
`DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]`	`8 bytes`	正常大小（双精度）浮点数。如果省略 `M`和`D`，则值将存储到硬件允许的限制。双精度浮点数精确到大约`15`位小数。`DOUBLE(M,D)`是一个非标准的 MySQL 扩展。从MySQL 8.0.17开始，此语法已被弃用，
`DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]`或`REAL[(M,D)] [UNSIGNED] [ZEROFILL]`	`8 bytes`	这些类型是`DOUBLE`的同义词。例外：如果启用了`REAL_AS_FLOAT SQL`模式，则`REAL`是`FLOAT`而不是`DOUBLE`的同义词。

3、定点类型

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]或DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
一个打包的“精确”定点数。M是总位数（精度），D是小数点后的位数（小数位数）。小数点和（对于负数）符号不以M为单位计算。如果D为0，则值没有小数点或小数部分。十进制的最大位数 M 为65。支持的最大小数位数 D 为30。如果省略D，则默认值为0。如果省略M，则默认值为10。

4、位值类型

BIT[(M)]
位值类型。M表示每个值的位数，从 1到64。如果省略M，则默认值为1。

1.2、字符串类型

字符串数据类型为 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET。
1、普通字符串类型
对于字符串列（CHAR，VARCHAR和TEXT类型）的定义，MySQL以字符单位解释长度规范。共有属性：CHARACTER SET指定字符集，CHARSET等同于CHARACTER SET；COLLATE指定字符集排序规则。

类型及属性	类型描述
`[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]`	`NATIONAL CHAR`等同于`NCHAR`,MySQL用作此预定义字符集。一个固定长度的字符串，在存储时始终用指定长度的空格右填充。`M`表示以字符为单位的列长度。`M`的范围是`0`到`255`。如果省略`M`，则长度为`1`。
`[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]`	`NATIONAL VARCHAR`是定义`VARCHAR`列应使用某些预定义字符集的标准SQL方法。MySQL用作此预定义字符集。`NVARCHAR`是`NATIONAL VARCHAR`的简写。可变长度字符串。`M`表示最大列长度（以字符为单位）。`M`的范围是`0`到`65535`。`VARCHAR`的有效最大长度取决于最大行大小（`65535`字节，在所有列之间共享）和使用的字符集。例如，字符每个字符最多需要三个字节，因此可以使用字符集的`VARCHAR`列声明为最多`21844`个字符。
`TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]`	最大长度为 `65535` （2¹⁶ − 1）个字符的`TEXT` 列。如果值包含多字节字符，则有效最大长度较小。每个`TEXT`值都使用`2`字节长度前缀进行存储，该前缀指示值中的字节数。对于此类型，可以给出可选的长度`M`，MySQL会将列创建为最小的`TEXT`类型，该类型足够大，可以容纳`M`个字符长的值。
`TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]`	最大长度为 `255`（2⁸−1）个字符的`TEXT`列。如果值包含多字节字符，则有效最大长度较小。每个`TINYTEXT`值都使用`1`字节长度前缀进行存储，该前缀指示值中的字节数。
`MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]`	最大长度为`16777215`（2²⁴−1）字节的`TEXT`列。如果值包含多字节字符，则有效最大长度较小。每个`MEDIUMTEXT`值都使用`3`字节长度前缀进行存储，该前缀指示值中的字节数。
`LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]`	最大长度为`4294967295`或`4GB`（2³²−1）个字符的`TEXT`列。如果值包含多字节字符，则有效最大长度较小。`LONGTEXT`列的有效最大长度还取决于客户端/服务器协议中配置的最大数据包大小和可用内存。每个`LONGTEXT`值都使用`4`字节长度前缀进行存储，该前缀指示值中的字节数。

指定字符串数据类型的属性会导致将列创建为相应的二进制字符串数据类型：CHAR变为BINARY，VARCHAR变为VARBINARY，TEXT变为BLOB。对于ENUM 和SET数据类型，不会发生这种情况它们是按照声明创建的。
2、二进制字符串类型
对于二进制字符串列（BINARY，VARBINARY和BLOB类型）的定义，MySQL以字节为单位解释长度规范。在mysql客户端中，二进制字符串使用十六进制表示法显示。

类型及属性	类型描述
`BINARY[(M)]`	`BINARY`类型类似于`CHAR`类型，但存储二进制字节字符串而不是非二进制字符串。可选长度`M`表示列长度（以字节为单位）。如果省略，`M`默认为`1`。
`VARBINARY(M)`	`VARBINARY`类型类似于`VARCHAR`类型，但存储二进制字节字符串而不是非二进制字符串。`M`表示最大列长度（以字节为单位）。
`BLOB[(M)]`	最大长度为`65535`（2¹⁶−1）字节的`BLOB`列。每个`BLOB`值都使用`2`字节长度前缀进行存储，该前缀指示值中的字节数。对于此类型，可以给出可选的长度`M`，MySQL会将列创建为最小的`BLOB`类型，该类型足够大，可以容纳`M`字节长的值。
`TINYBLOB`	最大长度为`255` （2⁸−1）字节的`BLOB`列。每个`TINYBLOB`值都使用`1`字节长度前缀进行存储，该前缀指示值中的字节数。
`MEDIUMBLOB`	最大长度为`16777215`（2²⁴−1）字节的`BLOB`列。每个`MEDIUMBLOB`值都使用`3`字节长度前缀进行存储，该前缀指示值中的字节数。
`LONGBLOB`	最大长度为`4294967295` 或 `4GB`（2³²−1）字节的`BLOB`列。`LONGBLOB`列的有效最大长度取决于客户端/服务器协议中配置的最大数据包大小和可用内存。每个`LONGBLOB`值都使用`4`字节长度前缀进行存储，该前缀指示值中的字节数。

3、枚举类型

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
枚举。一个字符串对象，它只能有一个值，从值、、、或特殊错误值列表中选择。ENUM值在内部表示为整数。一个ENUM列最多可以有65535个不同的元素。单个元素支持的最大长度为M <= 255和（M x w） <= 1020，其中M是元素文本长度，w是字符集中最大长度字符所需的字节数。

4、集合类型

SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
集合，一个字符串对象，它可以有零个或多个值，每个值都必须从值列表中选择，SET值在内部表示为整数。一个SET列最多可以有64个不同的成员。单个元素支持的最大长度为M <= 255和（M x w） <= 1020，其中M是元素文本长度，w是字符集中最大长度字符所需的字节数。

1.3、日期和时间类型

用于表示时态值的日期和时间数据类型为DATE，TIME，DATETIME，TIMESTAMP和YEAR。每个时态类型都有一个有效值范围，以及当你指定MySQL无法表示的无效值时可以使用的“零”值。TIMESTAMP和DATETIME类型具有特殊的自动更新行为。

数据类型	范围	描述	零值
`DATE`	`1000-01-01~9999-12-31`	日期，MySQL以格式`YYYY-MM-DD`显示`DATE`值，允许使用字符串或数字将值分配给DATE列。	`0000-00-00`
`TIME[(fsp)]`	`-838:59:59.000000~838:59:59.000000`	时间，MySQL以格式`hh:mm:ss[.fraction]`显示`TIME`值，允许使用字符串或数字将值分配给`TIME`列。	`00:00:00`
`DATETIME[(fsp)]`	`1000-01-01 00:00:00.000000~9999-12-31 23:59.999999`	日期和时间组合。MySQL以格式`YYYY-MM-DD hh:mm:ss[.fraction]`显示`DATETIME`值，允许使用字符串或数字将值分配给`DATETIME`列。	`0000-00-00 00:00:00`
`TIMESTAMP[(fsp)]`	`1970-01-01 00:00:01.000000~2038-01-19 03:14:07.999999`	时间戳。`TIMESTAMP`值存储为自纪元（UTC）`1970-01-01 00:00:00`以来的秒数。`TIMESTAMP` 不能表示该值`1970-01-01 00:00:00`，因为这相当于纪元的`0`秒，并且值`0`保留用于表示“零” TIMESTAMP 值。	`0000-00-00 00:00:00`
`YEAR[(4)]`	`1901~2155`	4位数格式的年份,MySQL以`YYYY`格式显示`YEAR`值，允许使用字符串或数字将值分配给`YEAR`列。	`0000`

从 MySQL 8.0.19开始，不推荐使用具有显式显示宽度的YEAR(4)数据类型，请使用不带显示宽度的YEAR，这具有相同的含义。

二、数据库操作

登录数据库

mysql -uroot -p

按Enter输入密码，在按Enter。

2.1、数据库操作

1、创建数据库
创建名为map_information_of_china的数据库。

CREATE DATABASE map_information_of_china;

创建名为map_information_of_china的数据库，并设置字符集为gbk。

CREATE DATABASE map_information_of_china CHARACTER SET gbk;

2、查看数据库
查看所有数据库。

SHOW DATABASES;

查看数据库map_information_of_china的详情；

SHOW CREATE DATABASE map_information_of_china;

3、选择数据库
选择数据库map_information_of_china。

USE map_information_of_china;

4、删除数据库
删除数据库map_information_of_china。

DROP DATABASE map_information_of_china;

2.2、数据表操作

1、创建数据表
创建表之前要先选择数据库，表示在该数据库下创建表。要在其它数据库下创建，重新选择其它数据库即可。
（1）在数据库map_information_of_china下创建名为province_information的数据表，并添加字段province_id（省份编号）、province_name（省份名称）、province_abbreviation（省份简称）、province_area（省份面积）。在创建数据表时，字段后面要说明字段的数据类型。一个字段为一列，一个表只少要有一列，否则无法创建表。

USE map_information_of_china;
CREATE TABLE province_information(
province_id INT,
province_name VARCHAR(12),
province_abbreviation char(4),
province_area int);

（2）创建数据表province_information,若数据表存在则不创建。

CREATE TABLE IF NOT EXISTS province_information(
province_id INT,
province_name VARCHAR(12),
province_abbreviation char(4),
province_area int);

（3）创建数据表province_information,并设置数据引擎为innoDB，默认字符集为utf8。

CREATE TABLE province_information(
province_id INT,
province_name VARCHAR(12),
province_abbreviation char(4),
province_area int)
ENGINE = innoDB DEFAULT CHARACTER SET = utf8;

（4）创建数据表时，字段类型后可添加属性（或叫约束）。用来约束该字段（或叫该列）的值。常用的几种约束类型见下表。

属性名称	描述
`NOT NULL`	表示该列值不能为空。
`DEFAULT 'XX'`	在没有给定值时，将该列默认值设置为`XX`。默认值格式按对应数据类型书写。
`PRIMARY KEY`	将该列字段设置为主键。一个数据表中只能存在一个主键约束。
`AUTO_INCREMENT`	将该列值设置为自增，一般用于主键约束的列。
`COMMENT 'xx'`	注释，对该列的说明注释。

创建数据表province_information，将字段province_id设置为主键自增，将字段province_name设置不能为空，将字段province_area默认值设置为0,并将每一列进行注释说明。

CREATE TABLE province_information(
province_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '省份编号',
province_name VARCHAR(12) NOT NULL COMMENT '省份名称',
province_abbreviation char(4) COMMENT '省份简称',
province_area int DEFAULT 0 COMMENT '省份面积') ;

2、查看数据表
（1）查看当前数据库下所有表。

SHOW TABLES;

（2）查看指定表province_information的表结构。
第一种方式：

DESC province_information;

第二种方式：

SHOW COLUMNS FROM province_information;

（3）查看表province_information的创建详情。

SHOW CREATE TABLE province_information;

3、修改数据表
（1）修改数据表名。
将数据表province_information名称修改为province_info。

RENAME TABLE province_information TO province_info;

（2）修改数据表的引擎和字符集。
将数据表privince_infomation的引擎修改为innoDB,字符集修改为gbk。

ALTER TABLE province_information ENGINE=innoDB CHARSET=gbk;

4、删除数据表
（1）删除数据表province_information的表结构及表中数据。

DROP TABLE province_information;

（2）删除数据表province_information的表结构及数据并安原表结构重新创建此表。

TRUNCATE TABLE province_information;

（3）仅删除数据表province_information的表中数据，保留其表结构。

DELETE FROM province_information;

注意：此方法仅删除表中数据，并不能清除设置自增列的数值，新添加数据并不能从0开始自增。

1.3、表字段操作

1、添加数据表字段
（1）在最后一列添加字段
在数据表province_information的最后一列添加字段create_time（创建时间）。

ALTER TABLE province_information ADD create_time  DATETIME COMMENT '创建时间';

（2）在第一列添加字段
在数据表province_information的第一列添加字段id。

ALTER TABLE province_information ADD id int FIRST;

（3）在省份简称province_abbreviation字段后面添加字段population_quantity（人口数量）。

ALTER TABLE province_information ADD population_quantity int COMMENT '人口数量' AFTER province_abbreviation;

2、查看数据表字段
查看表字段有两种方式：
（1）通过查看表结构查看。
查看数据表province_information的表字段。

DESC province_information;

（2）通过查看表的列信息查看。
查看数据表province_information的表字段。

SHOW COLUMNS FROM province_information;

3、修改数据表字段
（1）修改表字段名称和类型。
将数据表province_information的字段province_abbreviation修改为abbreviation,类型为varchar。

ALTER TABLE province_information CHANGE province_abbreviation abbreviation varchar(4);

（2）修改数据表的字段类型
将数据表province_information的字段province_abbreviation的类型修改为varchar。

ALTER TABLE province_information MODIFY province_abbreviation varchar(4);

（3）调整数据表中某个字段的位置
将数据表province_information 中province_abbreviation字段调整至第一列。

ALTER TABLE province_information MODIFY province_abbreviation char(4) FIRST;

将数据表province_information 中province_abbreviation字段调整至province_name后面。

ALTER TABLE province_information MODIFY province_abbreviation varchar(4) AFTER province_name;

在更改位置的同时可以修改字段的类型。
（4）修改字段默认值
将数据表province_information中字段population_quantity的默认值修改为0。

ALTER TABLE province_information ALTER population_quantity SET DEFAULT 0;

（5）删除字段默认值
删除数据表province_information中字段population_quantity的默认值。

ALTER TABLE province_information ALTER population_quantity DROP DEFAULT;

4、删除数据表字段
删除数据表province_information中字段population_quantity。

ALTER TABLE province_information DROP population_quantity;

1.4、表数据操作

在数据库map_information_of_china中创建数据表province_information，包含以下字段id（编号）、province_number（省份编号）、province_name（省份名称）、province_abbreviation（省份简称）、province_area（省份面积）。

USE map_information_of_china;
CREATE TABLE province_information(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '编号',
province_number INT NOT NULL COMMENT '省份编号',
province_name VARCHAR(12) NOT NULL COMMENT '省份名称',
province_abbreviation CHAR(4) NOT NULL COMMENT '省份简称',
province_area INT DEFAULT 0 COMMENT '省份面积');

1、向数据表中插入数据
（1）全字段（或叫全列）插入数据。
向数据表province_information中全字段插入数据：（1，41，河南，豫，170000）、（2，13，河北，冀，190000）。

INSERT INTO province_information VALUES
(1,41,'河南','豫',170000),
(2,13,'河北','冀',190000);

插入多条数据依次往后即可。
（2）指定字段插入数据。
向数据表province_information中的字段province_number、province_name、province_abbreviation插入数据：（43，湖南省，湘）、（42，湖北省，鄂）。

INSERT INTO province_information 
(province_number,province_abbreviation,province_name) 
VALUES
(43,'湘','湖南省'),
(42,'鄂','湖北省');

注意： 字段顺序可以打乱，但数据必须与插入语句中的字段顺序对应。多条数据依次往后添加即可。

2、查看数据表数据
（1）查看数据表中所有数据。
查看数据表province_information中的所有数据。

SELECT * FROM province_information;

（2）查看数据表中指定字段中的数据。
查看数据表province_information中字段province_name和province_abbreviation的数据。

SELECT province_name,province_abbreviation FROM province_information;

注：多个字段逗号隔开即可。

（3）高级查询
请关注后续中、高级教程。
3、修改数据表中数据
将数据表province_information中id=1的行的province_name的值修改为：河南省。

UPDATE province_information SET province_name = '河南省' where id = 1;

where后跟条件，条件后续会详细介绍。
4、删除数据表中数据
（1）删除表中所有数据
删除数据表province_information中的所有数据。

DELETE FROM province_information;

此方法不会删除表中设置自增的数据。
（2）删除表中数据，包括自增数据。
删除数据表provice_information的数据，包括字段id的自增数据。

TRUNCATE TABLE province_information;

此方法实际是删除此表又重新按原表结构创建数据表。

Smile丶Life丶

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录