MySQL核心知识整理

用了这么久的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

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

1
2
3
4
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='表名'

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

1
2
3
4
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 JOINJOIN是一样的,一般INNER关键字可以省略。 INNER JOIN将只会返回相匹配的元素项,即不会返回结果为NULL的数据项。

1
2
3
4
5
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 JOINLEFT OUTER JOIN是一样的。 左连接(LEFT OUTER JOIN)会输出左表中的所有结果,如果右表中有相应项,则会输出,否则为NULL。

1
2
3
4
5
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
4
5
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会列出所有记录。

1
2
3
SELECT name FROM TableA
UNION
SELECT name FROM TableB

结果集(注意下面没有重复项)

1
2
3
4
5
6
HaHa
HeiHei
WaWa
SaSa
YaYa
ZaZa

使用UNION ALL

1
2
3
SELECT name FROM TableA
UNION ALL
SELECT name FROM TableB

结果集

1
2
3
4
5
6
7
8
9
10
HaHa
HeiHei
WaWa
SaSa
YaYa
HeiHei
WaWa
SaSa
YaYa
ZaZa

分组查询GROUP BY

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

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

1
2
3
4
5
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 的客户:

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

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

1
2
3
4
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 的订单平均金额:

1
2
3
4
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
SELECT MID(Customer,1,3) FROM Orders;