mysql知识整理_MySQL核心知识整理

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

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

安装和配置

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

从 MySQL 官网选取合适的 MySQL 版本，获取下载地址。然后使用 wget 下载：1wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm

安装 yum Repository1yum -y install mysql57-community-release-el7-8.noarch.rpm

搜索 mysql server1yum search mysql-com

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

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

2

3

4systemctl start mysqld.service

systemctl stop mysqld.service

systemctl status mysqld.service

systemctl enable mysqld.service

登陆数据库

MySQL5.7.6 之后会在启动 mysql 进程的时候生成一个用户密码，首次登陆需要这个密码才行。

密码保存在 mysql 进程的日志里，即/var/log/mysqld.log1

2

3cat /var/log/mysqld.log | grep 'password'

# 登录

mysql -uroot -p

修改root密码，先登录进去后：1mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

最后，安装完了可以删除 MySQL 的 Repository ，这样可以减少 yum 检查更新的时间，使用下面的命令：1yum -y remove mysql57-community-release-el7-8.noarch

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

2mysql> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'mysql';

mysql> flush privileges;

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

常见数据库操作

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

2drop database fastloan3;

create database fastloan3 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

通过sql脚本文件执行sql：1mysql -uroot -p... fastloan3 < t_invoice.sql

导出数据库1

2mysqldump -u... -p... mydb > mydb_tables.sql

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

查看表空间占用大小1

2

3mysql> use information_schema;

mysql> SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS

FROM TABLES WHERE TABLE_SCHEMA='数据库名' //AND TABLE_NAME='表名'

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

2

3

4show full columns from

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表idname1HaHa

2HeiHei

3WaWa

4SaSa

5YaYa

B表idname1HeiHei

2WaWa

3SaSa

4YaYa

5ZaZa

INNER JOIN

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

2

3

4

5SELECT

*

FROM

TableA

INNER JOIN TableB ON TableA.name = TableB.name

结果集idnameid1name12HeiHei1HeiHei

3WaWa2WaWa

4SaSa3SaSa

5YaYa4YaYa

LEFT [OUTER] JOIN

请注意这里的OUTER是可选的，LEFT JOIN 和 LEFT OUTER JOIN是一样的。

左连接(LEFT OUTER JOIN)会输出左表中的所有结果，如果右表中有相应项，则会输出，否则为NULL。1

2

3

4

5SELECT

*

FROM

TableA

LEFT OUTER JOIN TableB ON TableA.name = TableB.name

结果集idnameid1name12HeiHei1HeiHei

3WaWa2WaWa

4SaSa3SaSa

5YaYa4YaYa

1HaHaNULLNULL

RIGHT [OUTER] JOIN

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

2

3

4

5SELECT

*

FROM

TableA

RIGHT OUTER JOIN TableB ON TableA.name = TableB.name

结果集idnameid1name12HeiHei1HeiHei

3WaWa2WaWa

4SaSa3SaSa

5YaYa4YaYa

NULLNULL5ZaZa

UNION 与 UNION ALL

UNION操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。

列也必须拥有相似的数据类型。同时，每条SELECT语句中的列的顺序必须相同。UNION只选取不同的记录，而UNION ALL会列出所有记录。1

2

3SELECT name FROM TableA

UNION

SELECT name FROM TableB

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

2

3

4

5

6HaHa

HeiHei

WaWa

SaSa

YaYa

ZaZa

使用UNION ALL1

2

3SELECT name FROM TableA

UNION ALL

SELECT name FROM TableB

结果集1

2

3

4

5

6

7

8

9

10HaHa

HeiHei

WaWa

SaSa

YaYa

HeiHei

WaWa

SaSa

YaYa

ZaZa

分组查询GROUP BY

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

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

2

3

4

5SELECT 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_IdOrderDateOrderPriceCustomer12008/12/291000Bush

22008/11/231600Carter

32008/10/05700Bush

42008/09/28300Bush

52008/08/062000Adams

62008/07/21100Carter

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

2

3SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

HAVING SUM(OrderPrice)<2000

现在我们希望查找客户 “Bush” 或 “Adams” 拥有超过 1500 的订单总金额。

我们在 SQL 语句中增加了一个普通的 WHERE 子句：1

2

3

4SELECT Customer,SUM(OrderPrice) FROM Orders

WHERE Customer='Bush' OR Customer='Adams'

GROUP BY Customer

HAVING SUM(OrderPrice)>1500

结果集：CustomerSUM(OrderPrice)Bush2000

Adams2000

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

2

3

4SELECT Customer,SUM(OrderPrice) FROM Orders

WHERE Customer='Bush' OR Customer='Adams'

GROUP BY Customer

HAVING AVG(OrderPrice)>1500

结果集：CustomerSUM(OrderPrice)Adams2000

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

其他有用函数

MID(str,pos,len)提取子串，pos从1开始。1SELECT MID(Customer,1,3) FROM Orders;