SQL 日常工作总结

1.如何找出第二名？

#偏移法 limit offset(快)

#排除法 先找到最大值，再找小于最大值的数据中的最大值（慢）

2.日常固定搭配

#group by + having聚合函数做筛选只能用 having（快）

#group by + Where 做嵌套模式（慢）

3.#删除表 用delete

4.#求气温比昨天高的日期

5.#mod 余数为1是基数，为0是偶数

6#求年龄

7#求本周过生日的学生

日期的运算

1、现有日期进行偏移（推荐）

date_sub(日期 ，要减少偏移的间隔)

date_sub(date,INTERVAL expr type)

date_add(日期 ，要增加偏移的间隔)

date_add(date,INTERVAL expr type)

expr 是要偏移的数值

type 是要偏移的方式

时间格式的转换

1、字符串转换为日期格式

str_to_date (时间字符串，字符串日期格式) ：能够把字符串转换为标准日期格式

日期的格式

格式描述

%Y 年，4 位

%y年，2 位

%M英文名称的月名（一月为January，二月为February）

%m 数值月份 (00-12)

%D带有英文后缀的天（不太直观）

%d 数值天 (00-31)

%H 小时 (00-23)

%h小时 (01-12)

%I小时 (01-12)

%i 分钟，数值(00-59)

%S秒(00-59)

%s 秒(00-59)

%a英文缩写星期名

%b英文缩写月名

%c月，数值

%f微秒

%p显示是 AM 还是 PM

%r显示时分秒时间，12小时制（hh:mm:ss AM 或 PM）

%T显示时分秒时间, 24小时制 (hh:mm:ss)

%j显示当前日期是今年的第几天 (001-366)

eg：

select str_to_date('08/09/2008', '%m/%d/%Y')-- 2008-08-09

select str_to_date('08/09/08' , '%m/%d/%y')-- 2008-08-09

select str_to_date('08.09.2008', '%m.%d.%Y')-- 2008-08-09

select str_to_date('08:09:30', '%h:%i:%s')-- 08:09:30

select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s')--2008-08-09 08:09:30

2、日期转换为特殊字符串形式

date_format (日期，字符串格式)：能够把一个日期转换为各种样式的字符串

eg：

select date_format(now(),'%Y-%M-%d %H') -- 2020-May-23 17

select date_format('2020-02-03 13:45:06.676','%Y-%M-%D %I:%i:%S-%T') --2020-February-3rd 01:45:06-13:45:06

获取当前时间

1、获取年月日时分秒

now 函数：获取当前时间信息

eg：select now()

sysdate 函数：

eg：select sysdate()

区别:

    now() 在执行开始时值就得到了

    sysdate() 在函数执行时动态得到值。

eg:

    select now(), sleep(3),now()

    select sysdate(), sleep(3) , sysdate()

2、获取年月日

current_date 函数

eg：

        select current_date()

        select curdate()

3、获取时分秒

current_time 函数

eg：

        select current_time()

        select curtime()

日常工作需要用到Mysql数据分析的案例：

涉及表：

  orderinfo 订单详情表

    | orderid  订单id

    | userid   用户id

    | isPaid   是否支付

    | price    付款价格

    | paidTime 付款时间

  userinfo  用户信息表

    | userid    用户id

    | sex       用户性别

    | birth     用户出生日期

1、统计不同月份的下单人数

2、统计用户三月份的回购率和复购率

复购率：当月购买了多次的用户占当月用户的比例

回购率：上月购买用户中有多少用户本月又再次购买

a、先筛选出3月份的消费情况

b、统计一下每个用户在3月份消费了多少次

c、对购买次数做一个判断，统计出来那些消费了多次（大于1次）的用户数

本月回购率：本月购买用户中有多少用户下个月又再次购买

3月份的回购率=  3月用户中4月又再次购买的人数 / 3月的用户总数

a、统计每年每月的一个用户消费情况

b、相邻月份进行关联，能关联上的用户说明就是回购

c、统计每个月份的消费人数情况及格得到回购率

3、统计男女用户消费频次是否有差异

1、统计每个用户的消费次数，注意要带性别

2、对性别做一个消费次数平均计算

4、统计多次消费的用户，第一次和最后一次消费间隔是多少天

1、取出多次消费的用户

2、取出第一次和最后一次的时间

5、统计不同年龄段，用户的消费金额是否有差异

a、计算每个用户的年龄，并对年龄进行分层:0-10:1,11-20:2,21-30:3

b、关联订单信息，获取不同年龄段的一个消费频次和消费金额

c、再对年龄分层进行聚合，得到不同年龄层的消费情况

6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额

1、统计每个用户的消费金额，并进行一个降序排序

2、统计一下一共有多少用户，以及总消费金额是多少

3、取出前20%的用户进行金额统计

在MySQL日常运维工作中，经常会用到各种管理工具，这些工具属于mysql自带的管理工具，存储在mysql目录下的bin目录中，例如对象查看，备份，日志分析等，熟练使用是运维开发人员的必备工作，这些工具参数很多，这里介绍常用选项，更多详细可参考帮助文件。

在mysql工具集中，管理员使用最频繁的就是mysql命令了，它是连接数据库的客户端工具，类似oracle中的sqlplus，通过它可以进入mysql控制台界面。在大部分情况下，使用简单，命令语法如下：

常用选项：选项一般有两种表达方式，一种是"-"+选项单词缩写和选项值；另一种是“--”+选项的完整单词“=”选项实际值。例如我们连接数据库的两种命令如下：

myisampack是一个表压缩工具，它对MyISAM存储引擎表能进行高度压缩，可以很大的节省磁盘空间，但是压缩后的表只能读，不能写，不能进行DML *** 作，所以它的使用场景一般是归档 历史 数据。命令如下：

当对一个压缩表进行增加 *** 作时会报一个错误：ERROR 1036 Table is read only,但时对查询和统计时可以正常 *** 作的。

mysqladmin是一个对数据库进行管理 *** 作的客户端工具，可用来检查服务器是否可用、显示数据库版本号和状态，还可以直接新增一个数据库，也可对数据库进行关闭，功能和mysql类似，它的参数和mysql差异不大，命令如下：

它还可以修改root密码，命令如下

MySQL自带的mysqlbinlog工具的作用是解析二进制binlog的日志内容，把二进制数据还原成mysql可以执行的SQL语句。我有篇文章专门介绍该工具的使用，请具体参考：

传送门：mysql运维管理（七）：使用Mysqlbinlog工具恢复增量数据

mysqlcheck工具可以用来检查和修复MyISAM存储引擎的表,还能做优化的工作，例如check、repair、analyze、optimize等等功能。具体命令如下：

注意，如果是innodb引擎的表，不能用上述优化工具。

mysqldump工具用来逻辑备份数据库，或者数据迁移。该工具是最常用的备份工具。

我有篇文章专门介绍该工具的使用，请具体参考：

传送门：mysql运维管理（五）：掌握MySQLdump逻辑备份工具使用

它是数据导入工具，专门用来处理mysqldump 加-T选项后导出的文本文件，基本用法很简单，命令如下：

客户端对象查找工具，用来查找数据库，数据库的表，表中列或者索引，具体使用命令如下:

不加任何选项，默认显示所有数据库。

常用参数：

--count ,用来显示数据库和表的统计信息，不指定数据库的话，显示所有库信息

-k或者--keys，用来显示指定表中所有索引，例如查看employees库中employees表的索引信息，

在使用mysql使用过程中，会经常出现错误，错误信息都会带有一个编码，具体编码代表什么意思，就需要perror来查看。用法很简单：

举个例子，我们故意写错一个查询语句，例如：

现在有一个编码1054，我们可以用perror查看下

结果跟用工具显示的内容差不多，当然第三方工具也会显示错误信息。

本章做了一个常用工具的使用汇总，并举例说明了基本用法，熟练使用是每个运维人员必修内容，当然还有很多参数没有一一列举，可以参考相关帮助文档。

---