MySQL核心知识整理

发表于 2016-09-17 分类于中间件

用了这么久的mysql数据库，是时候来一篇总结了，这里整理使用过程中觉得比较重要也比较常用的知识点。

开源数据库里面mysql的份额最高，也是最流行的，其实性能也非常高，所以一直用它。

在CentOS 7.2里面通过yum来安装MySQL 5.7，同时配置好root密码以及允许其他ip访问。

从 MySQL 官网选取合适的 MySQL 版本，获取下载地址。然后使用 wget 下载：

1	wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm

安装 yum Repository

1	yum -y install mysql57-community-release-el7-8.noarch.rpm

搜索 mysql server

1	yum search mysql-com

安装

1	yum -y install mysql-community-server.x86_64

启动、停止、查看状态、开机启动等

systemctl start mysqld.service
systemctl stop mysqld.service
systemctl status mysqld.service
systemctl enable mysqld.service

登陆数据库

MySQL5.7.6 之后会在启动 mysql 进程的时候生成一个用户密码，首次登陆需要这个密码才行。密码保存在 mysql 进程的日志里，即/var/log/mysqld.log

1
2
3

cat /var/log/mysqld.log | grep 'password'
# 登录
mysql -uroot -p

修改root密码，先登录进去后：

1	mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

最后，安装完了可以删除 MySQL 的 Repository ，这样可以减少 yum 检查更新的时间，使用下面的命令：

1	yum -y remove mysql57-community-release-el7-8.noarch

修改权限，让其他的机器也能访问：

1 2	mysql> GRANT ALL ON . TO 'root'@'%' IDENTIFIED BY 'mysql'; mysql> flush privileges;

如果还不能远程访问，就需要关闭防火墙。

常见数据库操作

创建新的数据库并制定UTF-8编码

1 2	drop database fastloan3; create database fastloan3 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

通过sql脚本文件执行sql：

1	mysql -uroot -p... fastloan3 < t_invoice.sql

导出数据库

1 2	mysqldump -u... -p... mydb > mydb_tables.sql mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sqlhe

查看表空间占用大小

1
2
3

mysql> use information_schema;
mysql> SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS
FROM TABLES WHERE TABLE_SCHEMA='数据库名' //AND TABLE_NAME='表名'

查看详细表结构，包括注释

show full columns from <table_name>
SELECT COLUMN_NAME, COLUMN_TYPE, EXTRA, COLUMN_COMMENT
FROM information_schema.columns
WHERE table_name = 'metrics' AND table_schema = 'fastloan3';

关于表连接

SQL的Join语法有很多inner的，有outer的，有left的，有时候对于Select出来的结果集是什么样子有点不是很清楚。这里我通过实际例子来演示它们的用法。

假设我们有两张表。TableA是左边的表。TableB是右边的表。其各有5条记录，其中有4条记录name是相同的，如下所示：让我们看看不同JOIN的不同

A表

id	name
1	HaHa
2	HeiHei
3	WaWa
4	SaSa
5	YaYa

B表

id	name
1	HeiHei
2	WaWa
3	SaSa
4	YaYa
5	ZaZa

INNER JOIN

INNER JOIN跟JOIN是一样的，一般INNER关键字可以省略。 INNER JOIN将只会返回相匹配的元素项，即不会返回结果为NULL的数据项。

1
2
3

SELECT *
FROM TableA
         INNER JOIN TableB ON TableA.name = TableB.name

结果集

id	name	id1	name1
2	HeiHei	1	HeiHei
3	WaWa	2	WaWa
4	SaSa	3	SaSa
5	YaYa	4	YaYa

LEFT [OUTER] JOIN

请注意这里的OUTER是可选的，LEFT JOIN 和 LEFT OUTER JOIN是一样的。左连接（LEFT OUTER JOIN）会输出左表中的所有结果，如果右表中有相应项，则会输出，否则为NULL。

1
2
3

SELECT *
FROM TableA
         LEFT OUTER JOIN TableB ON TableA.name = TableB.name

结果集

id	name	id1	name1
2	HeiHei	1	HeiHei
3	WaWa	2	WaWa
4	SaSa	3	SaSa
5	YaYa	4	YaYa
1	HaHa	NULL	NULL

RIGHT [OUTER] JOIN

右连接（RIGHT OUTER JOIN）会输出右表中的所有结果，如果左表中有相应项，则会输出，否则为NULL。

1
2
3

SELECT *
FROM TableA
         RIGHT OUTER JOIN TableB ON TableA.name = TableB.name

结果集

id	name	id1	name1
2	HeiHei	1	HeiHei
3	WaWa	2	WaWa
4	SaSa	3	SaSa
5	YaYa	4	YaYa
NULL	NULL	5	ZaZa

UNION 与 UNION ALL

UNION操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条SELECT语句中的列的顺序必须相同。UNION只选取不同的记录，而UNION ALL会列出所有记录。

SELECT name
FROM TableA
UNION
SELECT name
FROM TableB

结果集（注意下面没有重复项）

HaHa
HeiHei
WaWa
SaSa
YaYa
ZaZa

使用UNION ALL

SELECT name
FROM TableA
UNION ALL
SELECT name
FROM TableB

结果集

HaHa
HeiHei
WaWa
SaSa
YaYa
HeiHei
WaWa
SaSa
YaYa
ZaZa

分组查询GROUP BY

很多时候需要分组统计查询，这里就需要使用到GROUP BY，以及HAVING子句。

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

比如我们拥有下面一个Orders表：

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

现在，我们希望查找订单总金额少于 2000 的客户:

SELECT Customer, SUM(OrderPrice)
FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice) < 2000

现在我们希望查找客户 “Bush” 或 “Adams” 拥有超过 1500 的订单总金额。我们在 SQL 语句中增加了一个普通的 WHERE 子句：

SELECT Customer, SUM(OrderPrice)
FROM Orders
WHERE Customer = 'Bush'
   OR Customer = 'Adams'
GROUP BY Customer
HAVING SUM(OrderPrice) > 1500

结果集：

Customer	SUM(OrderPrice)
Bush	2000
Adams	2000

现在我要求”Bush” 或 “Adams” 拥有超过 1500 的订单平均金额：

SELECT Customer, SUM(OrderPrice)
FROM Orders
WHERE Customer = 'Bush'
   OR Customer = 'Adams'
GROUP BY Customer
HAVING AVG(OrderPrice) > 1500

结果集：

Customer	SUM(OrderPrice)
Adams	2000

Bush被淘汰了，因为他的平均订单金额（(1000+700+300)/3）<1500

其他有用函数

MID(str,pos,len)提取子串，pos从1开始。

1 2	SELECT MID(Customer, 1, 3) FROM Orders;