MySQL 基础总结

2021 年 11 月 11 日
本文字数：6531 字
阅读完需：约 21 分钟

| 4 | MySQL 锁机制 | 待分享 |

| 5 | MySQL JOIN 原理 | 待分享 |

| 6 | MySQL Order By 原理 | 待分享 |

| 7 | redo log、undo log、binlog | 待分享 |

| 8 | InnoDB 内存结构包含四大核心组件 | 待分享 |

| 9 | 主从复制原理 | 待分享 |

| 10 | 主从延迟解决方案 | 待分享 |

| 11 | SQL 语句查询原理 | 待分享 |

| 12 | MySQL 海量数据优化 | 待分享 |

| 13 | SQL 优化 | 待分享 |

| 14 | 数据库与缓存双写一致性方案 | 待分享 |

| 15 | 分布式锁 | 待分享 |

文章目录

一、安装 MySQL

========================================================================

检查系统是否已安装 mysql

rpm -qa | grep mysql

如果已经安装，可以选择卸载

rpm -e mysql　　// 普通删除模式

rpm -e --nodeps mysql　　// 强力删除模式

下载 mysql

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

rpm -ivh mysql-community-release-el7-5.noarch.rpm

yum update

yum install mysql-server

权限设置：

chown mysql:mysql -R /var/lib/mysql

初始化 MySQL：

mysqld --initialize

启动 MySQL：

systemctl start mysqld

查看 MySQL 运行状态：

systemctl status mysqld

验证安装是否成功

mysqladmin --version

使用 MySQL 客户端

mysql

然后再输入密码，使用 mysql 命令连接到 MySQL 服务器上，默认情况下 MySQL 服务器的登录密码为空，所以本实例不需要输入密码。

查看有哪些库

mysql> SHOW DATABASES;

+----------+

| Database |

+----------+

| mysql |

| test |

+----------+

2 rows in set (0.11 sec)

设置密码

Mysql 安装成功后，默认的 root 用户密码为空，你可以使用以下命令来创建 root 用户的密码：

mysqladmin -u root password "new_password";

二、库操作命令

======================================================================

1、创建数据库：create 命令用于创建数据库。

mysql> create database <数据库名>;

2、显示所有数据库

mysql> show databases;

3、修改数据库：ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

使用 ALTER 命令及 DROP 子句来删除【user_info】表的 age 字段：

mysql> ALTER TABLE user_info DROP age;

注意：如果数据表中只剩余一个字段则无法使用 DROP 来删除字段。

MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表【user_info】中添加 age 字段，并定义数据类型:

mysql> ALTER TABLE user_info ADD age INT;

如果需要修改字段类型及名称, 你可以在 ALTER 命令中使用 MODIFY 或 CHANGE 子句。

例如，把字段 mobile 的类型从 CHAR(1) 改为 CHAR(11)，可以执行以下命令:

mysql> ALTER TABLE user_info MODIFY mobile CHAR(11);

使用 CHANGE 子句, 语法有很大的不同。在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

修改字段名称

mysql> ALTER TABLE user_info CHANGE mobile phone CHAR(11);

修改字段名称+数据类型

mysql> ALTER TABLE user_info CHANGE mobile phone VARCHAR;

4、删除数据库：drop 命令用于删除数据库。

mysql> drop database <数据库名>;

执行以上命令后，i 字段会自动添加到数据表字段的末尾。

5、使用数据库

mysql> use <数据库名>;

【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开：qq.cn.hn/FTf 免费领取

复制代码

三、表操作命令

======================================================================

1、创建表：

创建 MySQL 数据表需要以下信息：

表名
表字段名
定义每个表字段

以下为创建 MySQL 数据表的 SQL 通用语法：

CREATE TABLE table_name (column_name column_type);

创建数据表 article：

CREATE TABLE article (

pk_id int NOT NULL AUTO_INCREMENT,

title varchar(255) NOT NULL COMMENT '文章标题',

author_id int NOT NULL COMMENT '文章作者 ID',

category_id int NULL COMMENT '文章分类',

reading_quantity int NULL COMMENT '阅读量',

status int NULL COMMENT '文章状态，0:草稿箱 1:审核中 2:已发布',

release_date timestamp NULL COMMENT '文章发布时间',

update_date timestamp NULL COMMENT '文章修改时间',

PRIMARY KEY (pk_id)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

这里为了方便演示各种 SQL 语句，所以我把所有字段全部放在在一张表里

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为 NULL ，就会报错。

AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加 1。

PRIMARY KEY 关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。

ENGINE 设置存储引擎，CHARSET 设置编码。

2、插入数据

INSERT INTO game.article(pk_id, title, author_id, category_id, reading_quantity, status, release_date, update_date) VALUES (1, 'MySQL 基础总结', 1, 1, 5000, 0, '2021-09-03 16:18:54', '2021-09-03 16:18:56');

INSERT INTO game.article(pk_id, title, author_id, category_id, reading_quantity, status, release_date, update_date) VALUES (2, 'MySQL 索引', 1, 1, 4000, 0, '2021-09-03 16:23:32', '2021-09-03 16:23:34');

INSERT INTO game.article(pk_id, title, author_id, category_id, reading_quantity, status, release_date, update_date) VALUES (3, 'MySQL 事务', 1, 1, 3000, 0, '2021-09-03 16:24:20', '2021-09-03 16:24:22');

3、查询数据

select * from article where pk_id in(1,2);

查询 pk_id 等于 1 和 2 的数据

4、修改数据

update article set title="MySQL 基础总结(精品)" where pk_id = 1;

我们把 title 设置为MySQL基础总结(精品)，只针对 pk_id=1 这行记录。

5、删除数据

delete from article where pk_id = 1;

把 pk_id 等于 1 的记录删除

6、清空表数据

truncate article;

7、修改表注释

alter table article comment '文章表';

8、表查询

我们先插入测试数据：

（1）like，模糊查询

模糊查询：like，%标识匹配任意哥字符，_表示匹配一个字符

查询 article 表标题含有 MySQL 的文章

select * from article where title like '%MySQL%';

（2）in 操作符允许您在 WHERE 子句中规定多个值。

select * from article where reading_quantity in (5000,6000);

查询阅读量是 5000 或者 6000 的文章

（3）or

select * from article where reading_quantity = 5000 or author_id = 2;

查询阅读量等于 5000 或者作者 ID 等于 2 的文章

（3）and

select * from article where author_id = 1 and reading_quantity >= 5000;

查询作者 ID 等于 1 且阅读量大于等于 5000 的文章

（4）order by，排序

select * from article order by reading_quantity asc

asc 升序、desc 降序排序

（5）limit，分页查询

从 1 开始，返回两条记录

select * from article limit 1,2;

limit(初始记录行的偏移量是 0，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目

（6）distinct，去重

select distinct title from article;

按 title 去重

（7）group by，分组查询

select author_id,count(0) from article where status = 1 group by author_id;

查询每个作者发布了多少篇文章

（8）case

select *, (

case

when reading_quantity >= 5000 then

'热点文章'

when reading_quantity >= 4000 then

'优质文章'

when reading_quantity >= 3000 then

'普通文章'

else

'低质量文章'

end

) '文章等级'

from

article

我把上述 SQL 语句转成 Java 代码大家就懂了

if(reading_quantity >= 5000){

}else if(reading_quantity >= 4000){

}else if(reading_quantity >= 3000){

}else{

}

四、数据类型

=====================================================================

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

1.数值类型

（1）整数类型

整数类型的数，默认情况下既可以表示正整数又可以表示负整数（此时称为有符号数）。如果只希望表示零和正整数，可以使用无符号关键字“unsigned”对整数类型进行修饰。
各个类别存储空间及取值范围。

| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |

| --- | --- | --- | --- | --- |

（2）小数类型

decimal(length, precision)用于表示精度确定（小数点后数字的位数确定）的小数类型，length 决定了该小数的最大位数，precision 用于设置精度（小数点后数字的位数）。
例如： decimal (5,2)表示小数取值范围：999.99～999.99 decimal (5,0)表示： -99999～99999 的整数。
各个类别存储空间及取值范围。

| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |

| --- | --- | --- | --- | --- |

浮点数值 |

| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度

浮点数值 |

2.日期和时间类型

表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的 MySQL 不能表示的值时使用"零"值。

TIMESTAMP 类型有专有的自动更新特性，将在后面描述。

| 类型 | 大小

(bytes) | 范围 | 格式 | 用途 |

| --- | --- | --- | --- | --- |

| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |

| YEAR | 1 | 1901/2155 | YYYY | 年份值 |

| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038

结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038 年 1 月 19 日凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |

3.字符串类型

字符串类型指 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

| 类型 | 大小 | 用途 |

| --- | --- | --- |

| CHAR | 0-255 bytes | 定长字符串 |

| VARCHAR | 0-65535 bytes | 变长字符串 |

| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |

| TINYTEXT | 0-255 bytes | 短文本字符串 |

| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |

| TEXT | 0-65 535 bytes | 长文本数据 |

| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |

| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |

| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |

| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

五、函数

===================================================================

MySQL 提供了众多功能强大、方便易用的函数，使用这些函数，可以极大地提高用户对于数据库的管理效率，从而更加灵活地满足不同用户的需求。本文将 MySQL 的函数分类并汇总，以便以后用到的时候可以随时查看。

准备工作：

创建表：

-- 文章表

CREATE TABLE article (

pk_id int NOT NULL AUTO_INCREMENT,

title varchar(255) NOT NULL COMMENT '文章标题',

author_id int NOT NULL COMMENT '文章作者 ID',

category_id int NULL COMMENT '文章分类',

reading_quantity int NULL COMMENT '阅读量',

status int NULL COMMENT '文章状态，0:草稿箱 1:审核中 2:已发布',

release_date timestamp NULL COMMENT '文章发布时间',

update_date timestamp NULL COMMENT '文章修改时间',

PRIMARY KEY (pk_id)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO game.article(pk_id, title, author_id, category_id, reading_quantity, status, release_date, update_date) VALUES (2, 'MySQL 索引', 1, 1, -4000, 0, '2021-09-03 16:23:32', '2021-09-03 16:23:34');

这里为了方便演示绝对值等函数，我把 pk_id 等于 2 的阅读量设置为-4000

数据列表：

1.MySQL 数字函数

（1）ABS(x)

描述：返回 x 的绝对值

案例：

select pk_id,abs(reading_quantity) from article;

pk_id 等于 1 的记录，reading_quantity 是-4000，通过 abs(reading_quantity)就变成了 4000

（2）AVG(expression)

描述：返回一个表达式的平均值，expression 是一个字段

案例：

select AVG(reading_quantity) from article;

（3）MAX(expression)

描述：返回字段 expression 中的最大值

案例：

select max(reading_quantity) from article;

（4）MIN(expression)

描述：返回字段 expression 中的最小值

案例：

select min(reading_quantity) from article;

（5）CEIL(x)

描述：向上取舍

案例：

SELECT CEIL(1.5)

-> 2

SELECT CEIL(-1.5)

-> -1

（6）FLOOR(x)

描述：向下取舍

案例：

SELECT FLOOR(1.5)

-> 1

SELECT FLOOR(-1.5)

-> -2

（7）ROUND(x)

描述：四舍五入

案例：

SELECT ROUND(1.4)

-> 1

SELECT ROUND(1.5)

-> 2

（8）SUM(expression)

描述：返回指定字段的总和

案例：

select sum(reading_quantity) from article;

发布于: 2021 年 11 月 11 日阅读数: 3

发布

暂无评论

创作场景

MySQL 基础总结

文章目录

修改字段名称

修改字段名称+数据类型

模糊查询：like，%标识匹配任意哥字符，_表示匹配一个字符

查询 article 表标题含有 MySQL 的文章

从 1 开始，返回两条记录

评论