MySql – 我系cgh

mysql常用日期、时间查询

select curdate();  --获取当前日期
select last_day(curdate());  --获取本月最后一天。
select DATE_ADD(curdate(),interval -day(curdate())+1 day);  -- 获取本月第一天
select date_add(curdate()-day(curdate())+1,interval 1 month);  -- 获取下个月的第一天
select date_sub(curdate()-day(curdate())+1,interval 1 month);  -- 上个月第一天
select last_day(date_sub(curdate(),interval 1 month)); -- 上个月最后一天
select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual; --获取当前月的天数
select subdate(curdate(),date_format(curdate(),'%w')-1) -- 本周一
SELECT DATE_SUB(now(),INTERVAL WEEKDAY(now()) day); -- 本周一
select subdate(curdate(),date_format(curdate(),'%w')-7) -- 本周日

-- 前一秒
select SUBDATE(now(),interval 1 second);
-- 前一分钟
select SUBDATE(now(),interval 1 minute);
-- 前一个小时
SELECT date_sub(NOW(), interval 1 hour)
-- 前一天某个时间点
SELECT date_sub(DATE_FORMAT(NOW(),'%Y-%m-%d 18:00:00'), interval 1 day)
-- 前一天
SELECT date_sub(now(),interval 1 day)
-- 前一月 后一月
date_sub(curdate(),interval 1 month) 表示 2013-04-20
date_sub(curdate(),interval -1 month) 表示 2013-06-20
-- 前一年 后一年
date_sub(curdate(),interval 1 year) 表示 2012-05-20
date_sub(curdate(),interval -1 year) 表示 2014-05-20
-- 字符串转日期
select str_to_date('2017-11-20', '%Y-%m-%d %H:%i:%s');
-- 时间转字符串
select date_format(now(), '%Y-%m-%d');
-- 字符串转时间戳
select unix_timestamp('2016-01-02');
-- 时间戳转时间
select from_unixtime(1451997924);
-- 时间戳转字符串
select from_unixtime(1451997924,'%Y-%d');
-- 时间转时间戳
select unix_timestamp(now());

-- 查询前一天数据
select * from user_info where join_time BETWEEN date_sub(CURDATE(), INTERVAL 1 DAY) AND date_sub(DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59'),INTERVAL 1 DAY);
-- 上周一
select subdate(  date_add(subdate( date_add(curdate(), interval -1 day),date_format(date_add(curdate(), interval -1 day),'%w')-1), interval -2 day),date_format( date_add(subdate( date_add(curdate(), interval -1 day),date_format(date_add(curdate(), interval -1 day),'%w')-1), interval -2 day),'%w')-1);
-- 上周日
select date_add(subdate(  date_add(subdate( date_add(curdate(), interval -1 day),date_format(date_add(curdate(), interval -1 day),'%w')-1), interval -2 day),date_format( date_add(subdate( date_add(curdate(), interval -1 day),date_format(date_add(curdate(), interval -1 day),'%w')-1), interval -2 day),'%w')-1), interval 6 day);

原文转自
https://www.cnblogs.com/huangxiaoxue/p/8870839.html

MySql执行过程、执行顺序、执行计划

前言：MySql是我们平常用的比较多的数据库，但是时间久了后总会或多或少忘记了一条sql完整的执行过程，今天查资料，重新理解了一下MySql语句执行的全过程。

导引：
一、执行过程
二、执行过程中的状态
三、执行的顺序
四、执行的计划
五、EXPLAIN的局限
六、总结

一、执行过程

MySql整体的执行过程如下图所示:

1.1:连接器

连接器的主要职责就是:

①负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据,而不能同时进行，即同一时间只能进行一个动作

②验证请求用户的账户和密码是否正确,如果账户和密码错误,会报错:Access denied for user ‘root’@’localhost’ (using password: YES)

③如果用户的账户和密码验证通过,会在mysql自带的权限表中查询当前用户的权限:

mysql中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表,mysql权限表的验证过程为：

1:User表:存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例；
Db表:存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库；
Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表；
Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段；
Procs_priv表：存放存储过程和函数级别的权限。

2:先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在，存在则通过验证。

3:通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推

4:如果在任何一个过程中权限验证不通过,都会报错

1.2:缓存

mysql的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的sql语句，value是结果的集合。如果无法命中缓存,就继续走到分析器的的一步,如果命中缓存就直接返回给客户端。不过需要注意的是在mysql的8.0版本以后，缓存被官方删除掉了。之所以删除掉,是因为查询缓存的失效非常频繁,如果在一个写多读少的环境中,缓存会频繁的新增和失效。对于某些更新压力大的数据库来说，查询缓存的命中率会非常低,mysql为了维护缓存可能会出现一定的伸缩性的问题,目前在5.6的版本中已经默认关闭了，比较推荐的一种做法是将缓存放在客户端，性能大概会提升5倍左右。

1.3:分析器

分析器的主要作用是将客户端发过来的sql语句进行分析，这将包括预处理与解析过程，在这个阶段会解析sql语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树。具体的关键词包括不限定于以下：select/update/delete/or/in/where/group by/having/count/limit等.如果分析到语法错误，会直接给客户端抛出异常:ERROR:You have an error in your SQL syntax.

比如：select * from user where userId =1234;

在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来,mysql会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验:比如校验当前数据库是否存在user表，同时假如User表中不存在userId这个字段同样会报错：unknown column in field list.

1.4:优化器

能够进入到优化器阶段表示sql是符合mysql的标准语义规则的并且可以执行的，此阶段主要是进行sql语句的优化，会根据执行计划进行最优的选择,匹配合适的索引,选择最佳的执行方案。比如一个典型的例子是这样的：

表T,对A、B、C列建立联合索引，在进行查询的时候，当sql查询到的结果是:select xx where B=x and A=x and C=x.很多人会以为是用不到索引的，但其实会用到,虽然索引必须符合最左原则才能使用,但是本质上,优化器会自动将这条sql优化为:where A=x and B=x and C=X,这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理,mysql会计算各个执行方法的最佳时间,最终确定一条执行的sql交给最后的执行器。

1.5:执行器

在执行器的阶段,此时会调用存储引擎的API,API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb:

引擎以前的名字叫做:表处理器(更直观)负责对具体的数据文件进行操作,对sql的语义比如select或者update进行分析,执行具体的操作。在执行完以后会将具体的操作记录到binlog中,需要注意的一点是:select不会记录到binlog中,只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式,记录都redolog中。

二、执行过程中的状态

可以通过命令:show full processlist,展示所有的处理进程,主要包含了以下的状态，表示服务器处理客户端的状态，状态包含了从客户端发起请求到后台服务器处理的过程，包括加锁的过程、统计存储引擎的信息，排序数据、搜索中间表、发送数据等。囊括了所有的mysql的所有状态,其中具体的含义如下图

三、执行的顺序

事实上,sql并不是按照我们的书写顺序来从前往后、左往右依次执行的,它是按照固定的顺序解析的,主要的作用就是从上一个阶段的执行返回结果来提供给下一阶段使用，sql在执行的过程中会有不同的临时中间表，一般是按照如下顺序：
例子:
select distinct s.id from T t join S s on t.id=s.id where t.name="Yrion" group by t.mobile having count(*)>2 order by s.create_time limit 5;

3.1:from

第一步就是选择出from关键词后面跟的表,这也是sql执行的第一步:表示要从数据库中执行哪张表。

实例说明:在这个例子中就是首先从数据库中找到表T

3.2:join on

join是表示要关联的表，on是连接的条件，先通过join确认要关联的表，再通过on确认关联条件。通过from和join on选择出需要执行的数据库表T和S,产生笛卡尔积,生成T和S合并的临时中间表Temp1。on:确定表的绑定关系,通过on产生临时中间表Temp2。

实例说明：找到表S,生成临时中间表Temp1,然后找到表T的id和S的id相同的部分组成成表Temp2,Temp2里面包含着T和Sid相等的所有数据。

3.3:where

where表示筛选,根据where后面的条件进行过滤,按照指定的字段的值(如果有and连接符会进行联合筛选)从临时中间表Temp2中筛选需要的数据,注意如果在此阶段找不到数据，会直接返回客户端,不会往下进行.这个过程会生成一个临时中间表Temp3。注意在where中不可以使用聚合函数，聚合函数主要是(min\max\count\sum等函数)。

实例说明:在temp2临时表集合中找到T表的name=”Yrion”的数据,找到数据后会成临时中间表Temp3,temp3里包含name列为”Yrion”的所有表数据。

3.4:group by

group by是进行分组，对where条件过滤后的临时表Temp3按照固定的字段进行分组,产生临时中间表Temp4，这个过程只是数据的顺序发生改变,而数据总量不会变化,表中的数据以组的形式存在。

实例说明:在temp3表数据中对mobile进行分组,查找出mobile一样的数据,然后放到一起，产生temp4临时表。

3.5:Having

对临时中间表Temp4进行聚合,这里可以为count等计数，然后产生中间表Temp5，在此阶段可以使用select中的别名。

实例说明:在temp4临时表中找出条数大于2的数据,如果小于2直接被舍弃掉，然后生成临时中间表temp5

3.6:select

对分组聚合完的表挑选出需要查询的数据,如果为*会解析为所有数据,此时会产生中间表Temp6

实例说明：在此阶段就是对temp5临时聚合表中S表中的id进行筛选产生Temp6,此时temp6就只包含有s表的id列数据,并且name=”Yrion”，通过mobile分组数量大于2的数据。

3.7:Distinct

distinct对所有的数据进行去重,此时如果有min、max函数会执行字段函数计算，然后产生临时表Temp7。

实例说明:此阶段对temp5中的数据进行去重,引擎API会调用去重函数进行数据的过滤,最终只保留id第一次出现的那条数据,然后产生临时中间表temp7。

3.8:order by

会根据Temp7进行顺序排列或者逆序排列，然后插入临时中间表Temp8，这个过程比较耗费资源。

实例说明：这段会将所有temp7临时表中的数据按照创建时间(create_time)进行排序,这个过程也不会有列或者行损失。

3.9:limit

limit对中间表Temp8进行分页,产生临时中间表Temp9,返回给客户端。

实例说明:在temp7中排好序的数据,然后取前五条插入到Temp9这个临时表中，最终返回给客户端

ps:实际上这个过程也并不是绝对这样的，中间mysql会有部分的优化以达到最佳的优化效果，比如在select筛选出找到的数据集。

四、执行的计划

4.1:什么是执行计划

执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数

4.2:执行计划的内容

id

包含一组数字，表示查询中执行select子句或操作表的顺序。id一样，按照顺序执行；id越大，执行的优先级就越高（如子查询）

id相同，执行顺序由上至下

如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

表示查询中每个select子句的类型（简单 OR复杂）

a.SIMPLE：查询中不包含子查询或者UNION
b.查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
c.在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
d.在FROM列表中包含的子查询被标记为：DERIVED（衍生）
e.若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED
f.从UNION表获取结果的SELECT被标记为：UNION RESULT

type

MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下：

由左至右，由最差到最好

a.ALL：
Full Table Scan， MySQL将遍历全表以找到匹配的行

b.index：
Full Index Scan，index与ALL区别为index类型只遍历索引树

c.range：
索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询，但是要控制查询的时间范围，一般查询数据不要超过数据总数的 15%

range访问类型的不同形式的索引访问性能差异

d.ref：
非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找。

e.eq_ref：
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

f.const、system：
当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

system是const类型的特例，当查询的表只有一行的情况下，使用system

g.NULL：
MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

possible_keys

指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。
TIPS：查询中若使用了覆盖索引，则该索引仅出现在key列表中。
覆盖索引：查询数据只需要通过索引就可以查询出，如55万条数据，使用索引，立刻可以查询出 2000条数据，同时Extra字段是Using index

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

包含不适合在其他列中显示但十分重要的额外信息

a.Using index

该值表示相应的select操作中使用了覆盖索引（Covering Index）

TIPS：覆盖索引（Covering Index）

MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件

包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index）

注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

b.Using where

表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,
如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集

c.Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

d.Using filesort

MySQL中无法利用索引完成的排序操作称为“文件排序”
很多场景都是索引是一个字段，order by 排序的字段与索引字段不一致，导致的Using fileSort;
此时可以给排序字段和where条件字段，添加为组合索引，同时保证索引查询的数据不超过总量的15%，避免fileSort

注：回表的含义是，先根据索引查询数据，然后在根据确定的数据id和查询条件再去查询具体的数据的过程

五、EXPLAIN的局限

•EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
•EXPLAIN不考虑各种Cache
•EXPLAIN不能显示MySQL在执行查询时所作的优化工作
•部分统计信息是估算的，并非精确值
•EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

六、总结

本文从MySql执行过程、执行过程的状态，到MySql的执行顺序、MySql的执行计划，一路做了详细的总结，理解这些有助于提高我们对mysql的总体理解和把握，有助于我们对sql语句进行优化，以及明白mysql中的sql语句从写出来到最终执行的轨迹。希望能帮到大家。

参考链接：
1.mysql执行过程以及顺序
2.MySql执行顺序及执行计划
3.MySQL执行计划解读

记一次数据库误删后恢复过程

故事背景:搭建测试站点时候,忘记修改数据库连接,删除数据时候直接把正式数据一锅端了。
跑路是不可能跑路的,那只能想办法恢复数据了;
登上服务器一看,上一次备份已经是3天前了。好家伙,自动备份也没设。╮(╯▽╰)╭
但是恢复还是可以恢复的，现在理清思路：
1.先把现在误删后的数据库备份一份（还是有部分记录没删的）
2.找到服务器上mysql二进制文件，查找上一次备份对应节点，和刚才删除操作前一个节点位置
3.找到到对应节点后，导入3天前备份，并利用二进制文件恢复这3天数据；
理清思路后，开干！

首先，我们把数据库上的mysql-bin拷贝到本地-.-
内心os:不然在服务器上看几百M的文件真的耗不起……….

cd /www/server/data  //cd到mysql data目录
ls(ll)指令查看data目录下文件信息  //cd到data目录后执行该指令

可见,最新的mysql-bin文件是000005
到此为止我们已经完美的查询到了mysql-bin文件，尔后我们对所需要的文件进行转存，代码如下：

//先进入到mysql bin目录
cd /www/server/mysql/bin
//将000005文件转存到/root目录中，且重命名为log05.log
./mysqlbinlog /www/server/data/mysql-bin.000005 > /root/log05.log

获取到log日志后,下载到本地,并找到对应节点后,先把3天前的备份导入
然后就可以开始恢复数据了

./mysqlbinlog  --start-position=31758225 --stop-position=53741915 -d youdatasbase /www/server/data/mysql-bin.000005|mysql -uroot -pxxx youdatasbase

自此,数据恢复完成

Mysql查询某个字段包含某个值

查询字段某一字段中包含某一ID的记录我们可以用以下的方法：

首先创建表：

CREATE TABLE users(id int(6) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(20) NOT NULL,limits VARCHAR(50) NOT NULL);

添加数据：

1、 INSERT INTO users(name, limits) VALUES('小张','1,2,12');

2、 INSERT INTO users(name, limits) VALUES('小王','11,22,32');

如何查询出limits字段中含有ID=2的记录呢，我们可以用以下的方法：

第一种方法：

SELECT * FROM users WHERE limits like "%2%";

执行后您会发现两条数据都被查询了出来，而第2条数据是我们不想要的，所以我们需要对查询进行优化，考虑所有情况：

SELECT * FROM users WHERE limits like "%,2,%," or "2,%" or "%,2" or "2";

另一种方法就是利用mysql 字符串函数 find_in_set()。

第二种方法：
SELECT * FROM users WHERE find_in_set('2', limits);

这一句即可解决。。

另需注意：mysql字符串函数 find_in_set(str1,str2)函数是返回str2中str1所在的位置索引，str2必须以”,”分割开。

Mysql中存储过程或函数使用select into语句给变量赋值没有匹配记录时的结果

前言

对select into语句感兴趣是因为看了项目中的一个存储过程引起的，在程序运行之前看了存储过程的逻辑，本以为没有数据时会报错，结果程序却正常运行，这说明我对select into语句理解的问题，同时也暴露了一个知识盲点，所以写了个小例子测试一下，并把测试的过程记录方便日后查找。

创建测试表格

为了更清楚的表明问题，我们创建的表格尽可能的简单，同时为了测试空值的情况，数据列我们不设置默认值，表格命名为’intotest’，创建语句如下：

CREATE TABLE `intotest` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `number` int(4),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=DYNAMIC;

插入测试数据

表格建立完成可以使用可视化工具或者insert语句插入测试数据，插入测试数据后查询结果如下：

mysql> select * from intotest;
+----+--------+
| id | number |
+----+--------+
|  1 |      1 |
|  2 |      2 |
|  3 |   NULL |
+----+--------+
3 rows in set (0.00 sec)

建立一个存储过程

我们建立一个用于测试的存储过程，主要的逻辑就是看看当select into语句找不到匹配记录时，被赋值的变量会怎么样，建立存储过程的代码如下：

CREATE PROCEDURE `select_into_value2`()
BEGIN
    DECLARE _value INT DEFAULT 0;

    SELECT number FROM intotest WHERE id=1 INTO _value;
    SELECT _value;
END

这个存储过程运行正常，配合刚才我们插入表格的记录可以知道，运行后的结果为1:

mysql> call select_into_value();
+--------+
| _value |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

测试过程

当查询结果中不存在符合条件的记录时会怎样，修改存储过程定义，然后查看运行结果：

CREATE PROCEDURE `select_into_value2`()
BEGIN
    DECLARE _value INT DEFAULT 0;

    SELECT number FROM intotest WHERE id=5 INTO _value;
    SELECT _value;
END

mysql> call select_into_value();
+--------+
| _value |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

结果为0，也就是说当查不到匹配结果时，不会执行select into的赋值效果。

当匹配到查询结果但是查询出来的数值为null会怎样，修改存储过程定义，然后查看运行结果：

CREATE PROCEDURE `select_into_value2`()
BEGIN
    DECLARE _value INT DEFAULT 0;

    SELECT number FROM intotest WHERE id=3 INTO _value;
    SELECT _value;
END

mysql> call select_into_value();
+--------+
| _value |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

结果为NULL，也就是说当查到匹配结果时，不管结果时什么都会赋值到指定的变量中（类型不匹配的sql错误除外）。

当连续查询赋值中间出现不匹配会怎样，修改存储过程定义，然后查看运行结果：

CREATE PROCEDURE `select_into_value2`()
BEGIN
    DECLARE _value INT DEFAULT 0;

    SELECT number FROM intotest WHERE id=2 INTO _value;
    SELECT number FROM intotest WHERE id=5 INTO _value;
    SELECT _value;
END

mysql> call select_into_value();
+--------+
| _value |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

想必明白了前两种情况，这第三种也应该明白了，两条语句顺序执行，找到匹配的就赋值，找不到就放弃操作，结果就保留了上一次成功赋值的结果。

总结

关于select into语句赋值的规则就一句话，找到了符合条件的记录就赋值，找不到就算了。
在找到记录的前提下，如果类型不匹配会导致赋值失败并报错，比如查询到字符串赋值给整型变量。
————————————————
版权声明：本文为CSDN博主「AlbertS」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/albertsh/article/details/84176421

MySQL的 FOUND_ROWS() 函数和 ROW_COUNT() 函数

1.先填个坑
如果你要测试 FOUND_ROWS() 和 ROW_COUNT() 这两个函数，最好就不要用那些MySQL的图形化管理工具软件了（例如，SQLYog）。因为当你使用些工具软件执行某条SQL语句时，可能实际上并不仅仅是执行了这条SQL，这些软件同时会在后台自己执行一些其他SQL语句。所以有时你可能会发现这两个函数返回的结果和你预期的并不一样。所以呢，最好还是用 cmd 窗口来执行SQL进行测试。

2.FOUND_ROWS() 函数
（1） FOUND_ROWS()函数返回的是上一条 SELECT 语句（或 SHOW语句等）查询结果集的记录数。

注意，是上一条 SELECT 语句（即执行该函数前的最近一条SELECT语句），而不是上一条 SQL 语句；因为上一条SQL语句不一定是 SELECT 语句。

且，像 SELECT ROW_COUNT() 这种语句也是 SELECT 语句，它们的结果集也会被 FOUND_ROWS() 函数查出来。

（2）如果上一条 SELECT 语句查询结果为空，则返回 0。

（3）SHOW XXX（例如，show tables、show databases、show status）语句也会被 FOUND_ROWS() 函数查出来。

ROW_COUNT() 函数
（1）FOUND_ROWS()函数返回的是上一条SQL语句，对表数据进行修改操作后影响的记录数。

如果上一条SQL语句不是修改操作语句（INSERT/UPDATE/DELETE 等），而是查询语句（SELECT/SHOW 等）则返回-1。如果是修改操作语句，则返回修改（增/删/该）影响的记录数。

注意，这里是上一条SQL语句（即执行该函数前的上一条SQL语句），和上面有所区别。

（2）如果上一条SQL语句是UPDATE语句，但是UPDATE后所有数据的值并没有改变，则返回 0。

（3）如果上一条SQL语句是建表语句（创建表或临时表），但创建的是空表，则返回 0。

如果是删除表（DROP语句），则返回的还是 0。

（4）如果是创建临时表，但使用的是 AS 关键字直接将查询出来的值赋值给新建的临时表的话（其实就相当于新建了一个空表，紧接着使用了一条INSERT语句而已），则返回插入的记录数。

CREATE TEMPORARY TABLE tmp_sal AS
SELECT 
    *
FROM 
    employee 
WHERE 
    salary < 12000;

————————————————

原文链接：https://blog.csdn.net/zhou520yue520/article/details/81155248

MySql基础回顾之表定义

1.创建表

CREATE [TEMPORARY] TABLE tbl_name
(
字段名1 数据类型 [列级完整性约束条件] [默认值],
[字段名2 数据类型 [列级完整性约束条件] [默认值]]
[,…]
[表级完整性约束]
)[ENGINE=引擎类型]

CREATE TABLE  user
(
    `uid` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名',
    `age` int(4) NOT NULL DEFAULT 0 COMMENT '年龄'
)

注：其中“TEMPORARY”为临时表选项，临时表不永久保留，断开连接即自动删除

2.更新表

在MySQL中，通过ALTER TABLE语句来更改原有表结构，常见操作有增加或删减列，创建或取消索引，更改原有列的数据类型、默认值，重命名表名或列名，更改标记注释或引擎类型，更改索引、外键等等

(1)ADD[COLUMN]子句

用于向表中增加列，可同时增加多列

ALTER TABLE USER
ADD `sex` CHAR(1) DEFAULT 1 COMMENT '1为男，0为女' AFTER 'age',
ADD `idcard` VARCHAR(20) COMMENT '身份证'

注：可用关键字“FIRST”将新列置于第一列，也可用关键字“AFTER” 使新列置于某列之后，如“AFTER ”

(2)CHANGE[COLUMN]子句

用于修改列的名称或数据类型

ALTER TABLE user
CHANGE age ages int(4)

注：使用change子句必须写上修改后的数据类型和数据长度，修改列的数据类型可能导致数据丢失或字符串长度不足被截断

(3)ALTER[COLUMN]子句

用于修改或删除列的默认值

ALTER TABLE user
ALTER age SET DEFAULT '1'

(4)MODIFY[COLUMN]子句

与CHANGE子句类型，但MODIFY只修改列数据类型，不修改列名；且可通过关键字“FIRST”,”AFTER”修改列的位置

ALTER TABLE user
MODIFY sex char(2)

(5)DROP[COLUMN]子句

用于删除列

ALTER TABLE user
DROP sex

(6)RENAME[TO]子句

用于为表重新命名

ALTER TABLE user
RENAME TO userinfo

3重命名表

(1)RENAME TABLE

除了使用上面ALTER TABLE语句，还可直接使用RENAME TABLE语句重命名表

RENAME TABLE tbl_name TO new_tbl_name
[,tbl_name2 TO new_tbl_name2]

4删除表

DROP[TEMPOPARY] TABLE[IF EXISTS]
tbl_name[,tal_name2][,...]

5查看表

(1)显示表名

SHOW[FULL] TABLES [{FROM|IN} db_name]
[LIKE 'pattern'|WHERE expr]

(2)显示表结构

SHOW [FULL] COLUMN {FROM|IN} tbl_name [{FROM|IN} db_name]
[LIKE 'pattern'|WHERE expr]
#或者
{DESCRIBE|DESC} tbl_name [col_name|wild]
#例如
DESC user;
DESC user name

Mysql中IF()函数使用

mysql中if()函数具体语法如下：

IF(expr1,expr2,expr3)
#如果expr1的值为true，则返回expr2的值。
#如果expr1的值为false，则返回expr3的值。

其经常判断查询出来的值，示例；

mysql> select name,if(sex=0,'女','男') as sex from student;
+-------+-----+
| name  | sex |
+-------+-----+
| name1 | 女  |
| name2 | 女  |
| name3 | 男  |
| name4 | 女  |
+-------+-----+

其也经常用到判断的关联条件中，其示例如下：

SELECT  s.SCHOOL_CITY as schoolCity,
        s.SCHOOL_COUNTY as schoolCounty,
        count(DISTINCT `s`.`SCHOOL_ID`) as schoolNum,
        sum(m.duration) as sumDuration,
        sum(`m`.`VIEWERCOUNT`) as viewLiveSum,
        sum(m.replayViewerCount)as reViewSum,
        sum(m.praisecount) as sumpraise,
        sum(`m`.`VIEWERCOUNT`+m.replayViewerCount) as viewSum
         from ((tbl_hbb_mobile_live_statistics m join tbl_hbb_resource_visit_map v) join tbl_school_info s)
    where`m`.`RESOURCEID`= `v`.`RESOURCEID` 
        and if((`v`.`rangeTYPE`= '2'),(`v`.`rangeID`= `s`.`SCHOOL_ID`),(`v`.`parentId`= `s`.`SCHOOL_ID`))

转载至：https://www.cnblogs.com/zjdxr-up/p/8383609.html

Mysql用户变量@

用户变量

可以先在用户变量中保存值留待以后引用，可以将值从一个语句中传递到另一个语句；用户变量和连接有关，所以客户端连接断开后自动销毁。
用户变量的形式为@var_name，其中变量名var_name可以由当前字符集的文字数字字符、‘.’、‘_’和‘$’组成。默认字符集是cp1252 (Latin1)。可以用mysqld的–default-character-set选项更改字符集。参见5.10.1节，“数据和排序用字符集”。用户变量名对大小写不敏感。

默认值

没有初始化的用户变量默认为 NULL
如果用户变量分配了一个字符串值，其字符集和校对规则与该字符串的相同。用户变量的可压缩性（coercibility）是隐含的。(即为表列值的相同的可压缩性（coercibility）。

设置用户变量方法

设置用户变量方法1

设置用户变量的一个途径是执行SET语句：

SET @var_name = expr [, @var_name = expr] ...

对于SET，可以使用=或:=作为分配符。分配给每个变量的expr可以为整数、实数、字符串或者NULL值。

设置用户变量方法2

也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较操作符：

mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

用户变量的表达式用法

用户变量可用在表达式中。目前不包括明显需要文字值的上下文中，例如SELECT语句的LIMIT子句，或者LOAD DATA语句的IGNORE number LINES子句。

注释：在SELECT语句中，表达式在发送到客户端后才计算，这说明在HAVING,GROUP BY,ORDER BY 字句中，不能使用包含SELECT列表中所设的变量的表达式。
例如，下面的语句不能按期望工作：

mysql> SELECT (@aa:=id) AS a，(@aa+3) AS b FROM table HAVING b=5；

HAVING子句中引用了SELECT列表中的表达式的别名，使用@aa。不能按期望工作：@aa不包含当前行的值，而是前面所选的行的id值。

一般原则是不要在语句的一个部分为用户变量分配一个值而在同一语句的其它部分使用该变量。可能会得到期望的结果，但不能保证。

设置变量并在同一语句中使用它的另一个问题是变量的默认结果的类型取决于语句前面的变量类型。
下面的例子说明了该点：

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

对于该 SELECT语句，MySQL向客户端报告第1列是一个字符串，并且将@a的所有访问转换为字符串，即使@a在第2行中设置为一个数字。执行完SELECT语句后，@a被视为下一语句的一个数字。

要想避免这种问题，要么不在同一个语句中设置并使用相同的变量，要么在使用前将变量设置为0、0.0或者”以定义其类型。

未分配的变量有一个值NULL，类型为字符串

用户变量实践：

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/consecutive-numbers
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

题解：

#方法1 
#连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 
#3 次，我们使用 Logs 并检查是否有 3 个连续的相同数字。
#并添加关键字 DISTINCT ，去除重复元素。
SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
#但是以上方法存在一个问题就是，当数据出现物理删除导致ID不连贯的时候会出现异常

#方法2
#使用用户变量，用cnt和pre两个变量来统计
select DISTINCT a.Num ConsecutiveNums from (
    select t.Num,
    @cnt:=IF(@pre=t.Num,@cnt+1,1) cnt,
    @pre:=t.Num pre 
    from Logs t,(select @cnt:=0,@pre:=NULL) b ORDER BY t.Id asc) a
where a.cnt>=3
#满足题意，并解决了数据物理删后ID不连续的问题

MySQL中的describe命令-查看设计信息

describe命令
一、describe命令用于查看特定表的详细设计信息，例如为了查看guestbook表的设计信息，可用：
describe guestbook

describe ol_user userid

二、可通过”show columns ”来查看数据库中表的列名，有两种使用方式：
show columns form 表名 from 数据库名

或者：

show columns from 数据库名.表名

三、用describe命令查询具体列的信息
describe guestbook id

就是查询guestbook中id字段的列信息

{DESCRIBE | DESC} tbl_name [col_name | wild]

DESCRIBE 是 SHOW COLUMNS FROM 的缩写。

DESCRIBE 提供有关一个表的列信息。col_name 可以是一个列名或是一个包含 SQL 通配符字符 “%” 和 “_” 的字符串。没有必要用引号包围字符串。

转载至MySQL中的describe命令