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