MySQL学习笔记

环境安装配置

本次操作以  Linux 系统为例

安装

#更新源及安装
apt update && apt install mysql-server -y

查看版本

#验证安装
mysqladmin --version

客户端方式连接服务

使用 MySQL 客户端方式连接服务,并执行简单命令验证

mysql

-u : 代表进行连接的用户是  root 

-p : 代表该用户的密码，默认为空

-h : 代表远程连接的 IP 地址/域名

-P : 代表远程连接的端口号

查看数据库

show databases;

查看数据表

show tables;

创建数据

# 创建一个空的数据库并使用它
CREATE DATABASE IF NOT EXISTS classicmodels DEFAULT CHARSET utf8;
# 切换数据库 classicmodels
USE classicmodels;

导入数据：

# 导入数据
source /share/datasets/mysql-classicmodels.sql;

# 查询导入数据库classicmodels中的表offices
select city,phone,country from `offices`;

上面语句首先将当前数据库切换到 classicmodels 数据库下，并从 office 表查询数据。

如果您看到返回的客户数据，说明已成功将示例数据库( classicmodels )导入MySQL 数据库服务器了。

MySQL 示例数据库结构

MySQL 示例数据库模式由以下表组成：

• customers: 存储客户的数据。
• products: 存储汽车的数据。
• productLines: 存储产品类别数据。
• orders: 存储客户订购的销售订单。
• orderDetails: 存储每个销售订单的订单产品数据项。
• payments: 存储客户订单的付款数据信息。
• employees: 存储所有员工信息以及组织结构，例如，直接上级(谁向谁报告工作)。
• offices: 存储销售处数据，类似于各个分公司。

表与表之间的关系，请参考以下ER图 -

网络图片地址：

MySQL 数据类型( datatype )

MySQL 中的每种数据类型都可以通过以下特征来确定：

• 它用来表示数据值。
• 占用的空间以及值是固定长度还是可变长度。
• 数据类型的值可以被索引。
• MySQL如何比较特定数据类型的值。

数据类型	指定值和范围
char	String(0~255)
varchar	String(0~255)
tinytext	String(0~255)
text	String(0~65536)
blob	String(0~65536)
mediumtext	String(0~16777215)
mediumblob	String(0~16777215)
longblob	String(0~4294967295)
longtext	String(0~4294967295)
tinyint	Integer(-128~127)
smallint	Integer(-32768~32767)
mediumint	Integer(-8388608~8388607)
int	Integer(-214847668~214847667)
bigint	Integer(-9223372036854775808~9223372036854775807)
float	decimal(精确到23位小数)
double	decimal(24~54位小数)
decimal	将double转储为字符串形式
date	YYYY-MM-DD
datetime	YYYY-MM-DD HH:MM:SS
timestamp	YYYYMMDDHHMMSS
time	HH:MM:SS
enum	选项值之一
set	选项值子集
boolean	tinyint(1)

MySQL 数值数据类型

下表显示了MySQL中数字类型的总结：

数字类型	描述
TINYINT	一个很小的整数
SMALLINT	一个小的整数
MEDIUMINT	一个中等大小的整数
INT	一个标准整数
BIGINT	一个大整数
DECIMAL	定点数
FLOAT	单精度浮点数
DOUBLE	双精度浮点数
BIT	一个字节字段

MySQL 字符串数据类型

在 MySQL 中，字符串可以容纳从纯文本到二进制数据(如图像或文件)的任何内容。可以通过使用 LIKE 运算符，正则表达式 和 自然语言全文搜索 ，根据模式匹配来比较和搜索字符串。

下表显示了 MySQL 中的字符串数据类型：

字符串类型	描述
char	固定长度的非二进制(字符)字符串
varchar	可变长度的非二进制字符串
BINARY	一个固定长度的二进制字符串
VARBINARY	一个可变长度的二进制字符串
TINYBLOB	一个非常小的BLOB(二进制大对象)
BLOB	一个小的BLOB(二进制大对象)
MEDIUMBLOB	一个中等大小的BLOB(二进制大对象)
LONGBLOB	一个大的BLOB(二进制大对象)
TINYTEXT	一个非常小的非二进制字符串
TEXT	一个小的非二进制字符串
MEDIUMTEXT	一个中等大小的非二进制字符串
LONGTEXT	一个很大的非二进制字符串
ENUM	枚举; 每个列值可以被分配一个枚举成员
SET	集合; 每个列值可以分配零个或多个SET成员

MySQL 日期和时间数据类型

下表说明了MySQL日期和时间数据类型：

字符串类型	描述
DATE	YYYY-MM-DD 格式的日期值
TIME	hh:mm:ss 格式的时间值
DATETIME	YYYY-MM-DD hh:mm:ss 格式的日期和时间值
TIMESTAMP	YYYY-MM-DD hh:mm:ss 格式的时间戳记值
YEAR	YYYY 或 YY 格式的年值

MySQL空间数据类型

MySQL 支持许多包含各种几何和地理值的空间数据类型，如下表所示：

字符串类型	描述
GEOMETRY	任何类型的空间值
POINT	一个点(一对X-Y坐标)
LINESTRING	曲线(一个或多个 POINT 值)
POLYGON	多边形
GEOMETRYCOLLECTION	GEOMETRY 值的集合
MULTILINESTRING	LINESTRING 值的集合
MULTIPOINT	POINT 值的集合
MULTIPOLYGON	POLYGON 值的集合

创建数据库

CREATE DATABASE IF NOT EXISTS mysqlslap;

我们来更详细地看看 CREATE DATABASE 语句：

• CREATE DATABASE 语句的后面是要创建的数据库名称。（建议数据库名称尽可能是有意义和具有一定的描述性）
•  IF NOT EXISTS 是语句的可选子句。 IF NOT EXISTS 子句可防止创建数据库服务器中已存在的新数据库的错误。不能在 MySQL 数据库服务器中具有相同名称的数据库。

例：要创建一个名称为 mytestdb 数据库，可以执行 CREATE DATABASE 语句后接数据库名称： mytestdb ，如果当前 MySQL 服务器中没有数据库：mytestdb，则创建成功，

显示数据库

SHOW DATABASES;

选择数据库

USE mytestdb;

删除数据库

删除数据库，请使用 DROP DATABASE 语句，遵循 DROP DATABASE 是要删除的数据库名称。

CREATE DATABASE IF NOT EXISTS mytestdb;
SHOW DATABASES;
DROP DATABASE IF EXISTS mytestdb;
SHOW DATABASES;

三个语句的说明如下：

• 首先，使用 CREATE DATABASE语句创建了一个名为mytestdb的数据库。
• 第二，使用 SHOW DATABASES语句显示所有数据库。
• 第三，使用 DROP DATABASE语句删除了名为mytestdb的数据库。

创建表

语法： CREATE TABLE

CREATE TABLE [IF NOT EXISTS] table_name(
        column_list
) engine=table_type;

举例：

create database testdb;
use testdb;

CREATE TABLE IF NOT EXISTS tasks (
  task_id INT(11) NOT NULL AUTO_INCREMENT,
  subject VARCHAR(45) DEFAULT NULL,
  start_date DATE DEFAULT NULL,
  end_date DATE DEFAULT NULL,
  description VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (task_id)
) ENGINE=InnoDB;

查看表

show tables;

删除表数据

语法：

DELETE FROM table_name
WHERE condition;

添加表字段

alter table ccm_redis_resource_templet
add  templet_category varchar(24) null,
add  templet_tag varchar(24) null;

删除表字段

alter table ccm_redis_resource_templet 
drop column templet_category, 
drop column templet_tag;

MySql 相关含义

名称	含义
mysqld	命令
mysqld_safe	进程
mysql.server	服务
mysqladmin	程序
mysql service	脚本

性能测试工具

mysqlslap

基准测试主要分为两种：

• 针对整个系统的基准测试：通过http请求进行测试，如通过浏览器、APP或postman等测试工具。该方案的优点是能够更好的针对整个系统，测试结果更加准确；缺点是设计复杂实现困难。
• 只针对MySQL的基准测试：优点和缺点与针对整个系统的测试恰好相反

性能指标主要主要关注：

• TPS ：Transactions Per Second ，即数据库每秒执行的事务数，以 commit 成功次数为准。
• QPS ：Queries Per Second ，即数据库每秒执行的 SQL 数（含 insert、select、update、delete 等）。
• RT ：Response Time ，响应时间。包括平均响应时间、最小响应时间、最大响应时间、每个响应时间的查询占比。比较需要重点关注的是，前 95-99% 的最大响应时间。因为它决定了大多数情况下的短板。
• Concurrency Threads ：并发量，每秒可处理的查询请求的数量。

举例：

mysqlslap --concurrency=16,32 --iterations=3 --number-int-cols=1 --number-char-cols=2 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=10000 -h 30.203.150.83 -udbmonopr -p'AAbb$1314520' -P3714

mysqlslap 是 MySQL 自带的一个负载测试工具，用于测试 MySQL 服务器的性能和响应能力。它可以模拟多个客户端同时对 MySQL 服务器进行查询，从而帮助开发者评估数据库的性能，并找出潜在的瓶颈。

运行结果的参数含义：

Average number of seconds to run all queries：运行所有语句的平均秒数
Minimum number of seconds to run all queries：运行所有语句的最小秒数
Maximum number of seconds to run all queries：运行所有语句的最大秒数
Number of clients running queries：客户端数量
Average number of queries per client：每个客户端运行查询的平均数

命令参数

序号	参数	含义
1	--engines	代表要测试的引擎，可以有多个，用分隔符隔开
2	--iterations	代表要运行这些测试多少次
3	--auto-generate-sql	代表用系统自己生成的 SQL 脚本来测试
4	--auto-generate-sql-load-type	代表要测试的是读还是写还是两者混合的（read,write,update,mixed）
5	--number-of-queries	代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算
6	--debug-info	代表要额外输出 CPU 以及内存的相关信息
7	--number-int-cols	创建测试表的 int 型字段数量
8	--auto-generate-sql-add-autoincrement	代表对生成的表自动添加 auto_increment 列，从 5.1.18 版本开始
9	--number-char-cols	创建测试表的 char 型字段数量
10	--create-schema	测试的 schema，MySQL中 schema 也就是 database
11	--query	使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者 sql 语句来执行测试
12	--only-print	如果只想打印看看 SQL 语句是什么，可以用这个选项

其他性能测试工具：

1. sysbench

2. tpcc-mysql

3. The MySQL Benchmark Suite

4. MySQL super-smack

5. MyBench: A Home-Grown Solution