vip2-day23 SQL 语句进阶

掌握 MySQL 的方法

1. 掌握基本的数据结构和 SQL 使用方法。
2. 掌握 MySQL 的基本架构，比如单机的安装部署、主从复制的几种模式，MGR 集群等等。
3. 解决日常生产环境的问题，积累经验。
4. 研究 MySQL 源码和分析数据库的性能。

推荐书籍

1. 《sql 必知必会》
2. 《mysql 技术内幕》
3. 《高性能 mysql》

MySQL 命令

mysql

功能： 登录 mysql 系统。

格式：

$ mysql [param] [database] 

param：

• -h [host]：主机。可选项，默认为 localhost。
• -P [prot]：端口。可选项，默认为 3306。
• -u[username]：用户名。可选项，默认为 root。
• -p/-p[passwd]：密码。
  • -p[passwd]：明码模式登录。
  • -p：暗码模式登录，进入交互模式输入密码。
• -A：禁用预读表功能，加快切换库的速度。
• [database]：数据库。可选项。
• -e [sql]： SQL 语句。可选项。

示例：

# 暗码模式登录到指定库并执行 sql 语句
$ mysql -uroot -p -h localhost -P 3306 mysql -e "select host,user from user"

mysqldump

功能：转储工具。

格式：

$ mysqldump [param] [options] > [filename].sql 

param：

• -h [host]：主机。可选项，默认为 localhost。
• -P [prot]：端口。可选项，默认为 3306。
• -u[username]：用户名。可选项，默认为 root。
• -p/-p[passwd]：密码。
  • -p[passwd]：明码模式登录。
  • -p：暗码模式登录，进入交互模式输入密码。

options：

• [dbname]：导出单个数据库。
• [dbname] [tablename1] [tablename2] ...：导出指定库中的表。
• --databases [dbname1] [dbname2] ...：导出多个数据库。
• --all-database, -A：导出全部数据库。
• --all-tablespaces, -Y：导出全部表空间。
• -d [dbname]：导出指定库中的表结构。

示例：

# 导出指定库
$ mysqldump -uroot -p123456 book > /root/test/book_01.sql

# 导出所有库
$ mysqldump -uroot -p123456 --all-databases > /root/test/all.sql

# 导出所有库及所有表空间
$ mysqldump -uroot -p123456 -A -Y > /root/test/all_tables.sql

# 导出 book 库中的 books 表和 caregory 表
$ mysqldump -uroot -p book books category > bools.sql

SQL 语句

create

功能：新建。

格式：

--创建用户
> create user [username]@[host] identified by [passwd];

示例：

-- 创建用户 默认登录权限为'%' 可远程登录
> create user tp404 identified by '123456';

-- 创建用户并指定登录权限为只允许本地登录
> create user zhangsanfeng@'localhost' identified by '123456';

update

功能：修改。

格式：

> update [table] set [aimcolumn]=[newvalue] where [condition]

示例：

-- 修改用户名 将 zhangsanfeng 改为 tp110
> update mysql.user set user='tp110' where user='zhangsanfeng';

alter

功能：修改。

格式：

-- 修改用户密码
> alter user [username] identified by [passwd];

示例：

-- 修改密码 将用户 tp404 的密码改为 123456
> alter user tp404 identified by '123456';

select

功能：查询。

格式：

-- 执行函数
> select [func];

-- 查询指定字段的值
> select [column] from [table] where [condition]

示例：

-- 查询当前日期和时间
> select now();

-- 查询当前日期
> select curdate();

-- 查询当前用户
> select user();

-- 查看所有用户的登录权限
> select user,user.host from mysql.user;

show

功能：查询。

格式：

> show [options] [condition];

options：

• variables：系统变量。
• engines：存储引擎。
• status：系统状态变量当前值。
• global status：系统状态变量累计值。

示例：

-- 查询所有系统变量的名称和值
> show variables;

-- 查询存储引擎相关系统变量
> show variables like '%storage_engine%';

-- 查询存储引擎相关信息
> show engines;

-- 查询系统状态变量当前值
> show status;

-- 查询线程相关的系统状态变量累计值
> show global status like 'Thread%';

数据类型

基础概念

计量单位：

• 位：
  • 计算机存储的最小单位
  • 取值 0 或者 1
  • 单位 bit
• 字节：
  • 单位 byte
  • 1 byte = 8 bit
  • 1 kb = 1024 b
  • 1 M = 1024 kb

数值类型

类型	大小（byte）	范围（有符号）	范围（无符号）	描述
TINYINT	1	(-128, 127)	(0, 255)	小整数值
SMALLINT	2	(-32 768, 32 767)	(0, 65 535)	大整数值
MEDIUMINT	3	(-8 388 608, 8 388 607)	(0, 16 777 215)	大整数值
INT/INTEGER	4	(-2 147 483 648, 2 147 483 647)	(0, 4 294 967 295)	大整数值
BIGINT	8	(-9 233 372 036 854 775 808, 9 233 372 036 854 775 807)	(0, 18 446 744 073 709 551 615)	极大整数值
FLOAT	4	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0, (1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8	(-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)	双精度浮点数值
DECIMAL	对 DECIMAL(M,D) ，如果M>D，为M+2 否则为 D+2	依赖于 M 和 D 的值	依赖于 M 和 D 的值	小数值

BOOL：布尔类型。

• 等价于 TINYINT(1)。
• 取值：“真”或“假”。

DECIMAL ：以字符串形式表示的浮点数。

日期类型

类型	大小（byte）	范围	格式	描述
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

字符串类型

类型	大小（byte）	描述
CHAR	0-255	定长字符串
VARCHAR	0-65535	变长字符串
TINYBLOB	0-255	二进制字符串
TINYTEXT	0-255	短文本字符串
BLOB	0-65 535	二进制形式的长文本数据
TEXT	0-65 535	长文本数据
MEDIUMBLOB	0-16 777 215	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215	中等长度文本数据
LONGBLOB	0-4 294 967 295	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295	极大文本数据

枚举类型：

• 字段中实际存储的是成员索引。
• 成员全部用字符表示。
• 成员字符不可重复。

类型	大小	取值
SET (value1, value2,…)	每个成员占 1bit，不足 1byte 按 1byte 计算，最多占 4byte	可取单个成员或多个成员的组合
ENUM (value1, value2,…)	每 255 个成员占 1byte，不足 1byte 按 1byte 计算，最多占 2byte	只能取单个成员

运算符

逻辑运算符

运算符	作用
and	逻辑与
or	逻辑或
not	逻辑非

示例：

-- 查询价格是 30,40,50,60的书
> select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60;

算术运算符

运算符	作用
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
in	在集合中
not in	不在集合中
between and	在两值之间
not between and	不在两值之间
like	模糊匹配
not like	模糊不匹配

示例：

-- 查询价格等于 60 的书籍
> select bName,price from books where price = 60;

-- 查询价格大于 60 的书籍
> select bName,price from books where price > 60;

-- 查询价格不等于 60 的书籍
> select bName,price from books where price <> 60;

-- 查询价格是 50,60,70 的书
> select bName,price from books where price in (50,60,70);

-- 查询价格不是 50,60,70 的书
> select bName,price from books where price not in (50,60,70);

-- 查询价格不在 30 与 60 之间的书 查询结果以降序排列
> select bName,price from books where price not between 30 and 60 order by price desc;
-- 等效于
> select bName,price from books where price<30 or price>60 order by price desc;

-- 查询价格在 30 与 60 之间的书 查询结果以降序排列
> select bName,price from books where price between 30 and 60 order by price desc;
-- 等效于
> select count(*) from books where price>=30 and price<=60 order by price desc;

-- 查询书名中包含"程序"的书
> select bName from books where bName like '%程序%';

-- 查询书名中不包含"程序"的书
> select bName from books where bName not like '%程序%';

排序

asc

功能：升序排列。
格式：

> select [columns] from [table] where [condition] order by [column] asc;

示例：

-- 查询价格不在指定集合中的书 查询结果以升序排列
> select bName,price from books where price not in (50,60,70,80,90,100) order by price asc;
-- 或
> select bName,price from books where price not in (50,60,70,80,90,100) order by price;

desc

功能：降序排列。
格式：

> select [columns] from [table] where [condition] order by [column] desc;

示例：

-- 查询价格不在指定集合中的书 查询结果以降序排列
> select bName,price from books where price not in (50,60,70,80,90,100) order by price desc;

-- 查询价格在指定集合中的书 查询结果以降序排列 相同价格按照 bId 升序排列
> select bId,bName,price from books where price in (50,60,70,80,90,100) order by price desc,bId asc;

查询语句进阶

嵌套查询

格式：

> select [columns1] from [table1] where [condition1] = (select [columns2] from [table2] where [condition2]);

示例：

-- 查询类型是网络技术的书
> select bName,bTypeId from books where bTypeId=(select bTypeid from category where bTypeName='网络技术');

分页查询

格式：

> select [columns] from [table] limit [offset],[rows];

解析：

• offset：偏移量。
• rows：条目数。

示例：

-- 从结果集首条开始返回两条结果
> select * from category limit 0,2;

-- 从结果集第二条开始返回四条结果
> select * from category limit 1,4;

-- 查询电子工业出版社出版的最便宜的书
> select bName,price from books where publishing='电子工业出版社' order by price asc limit 0,1;

-- 查询比电子工业出版社出版的最便宜的书还要便宜的书
> select bName,price from books where price < (select price from books where publishing='电子工业出版社' order by price asc limit 0,1);

联合查询

内连接

格式：

> select [columns] from [table1] inner join [table2] on [table1].[column]=[table2].[column];

示例：

-- 查询书名、价格、类目
> select a.bName,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;

聚合查询

格式：

-- 查询字段综和
> select sum([columns]) from [table] where [condition];

-- 查询字段平均值
> select avg([columns]) from [table] where [condition];

-- 查询字段最大值
> select max([columns]) from [table] where [condition];

-- 查询字段最小值
> select min([columns]) from [table] where [condition];

示例：

-- 查询所有书的总价
> select sum(price) from books;

-- 查询 bId 小于等于 3 的所有书的平均价格
> select avg(price) from books where bId <= 3;

-- 查询最贵的书
> select bName,price from books where price=(select max(price) from books);

-- 查询最便宜的书
> select bName,price from books where price=(select min(price) from books);

备份还原

实例：备份数据库

数据库文件：/root/book_utf8.sql

方法一：

1. 创建数据库：$ mysql -e 'create database book' -uroot -p'123456';
2. 导入库：$ mysql -uroot -p'123456' book < /root/book_utf8.sql

方法二：

1. 登录数据库：$ mysql -uroot -p123456
2. 创建数据库：> create database book;
3. 进入数据库：> use book;
4. 导入库：> source /root/book_utf8.sql;

实例：还原数据库

命令：$ mysqldump -uroot -p123456 book > /root/test/book_01.sql

工具解析

mysqldump

备份方式：逻辑备份，冷备份。

适用场景：数据量较小时适用，常用于增量备份。

缺点：

• 数据量较大（超过 20G）时，备份数据比较慢。
• 一定程度上会影响数据库本身的性能。
• 备份过程中 cpu 占用较高。
• 还原速度较慢，需要加载解释语句，重建引擎，转化存储格式等。

优点：

• 还原操作比较简单。
• 与存储引擎没有关系，兼容性较好。

xtrabackup

备份方式：物理备份，热备份。

适用场景：数据量较大时适用，常用于全量备份。

缺点：还原时要注意备份文件的路径，版本，配置等与源文件是否一致，否则可能造成数据丢失。

优点：

• 基于文件的物理备份。
• 容易跨平台，跨操作系统和 MySQL 版本。
• 还原较快，不需要执行任何的 mysql 语句，不需要构建索引，innodb 表无需完全缓存到内容。