mysql recordcount 1_Mysql-自用技巧

Mysql查询某个字段(有时候想找某个字段在哪里，但是不知道表名等信息)

SELECTTABLE_SCHEMA,

TABLE_NAMEFROMinformation_schema.`COLUMNS`WHERECOLUMN_NAME= '要找的表名'

理解Left Join, Inner Join

TableA

aid adate1a12a23a3

TableB

bid bdate1b12b24 b4

两个表a,b相连接,要取出id相同的字段

select * from a inner join b on a.aid = b.bid这是仅取出匹配的数据.

此时的取出的是:

1 a1 b1

2 a2 b2

那么left join 指:

select * from a left joinb on a.aid = b.bid

首先取出a表中所有数据,然后再加上与a,b匹配的的数据

此时的取出的是:

1 a1 b1

2 a2 b2

3 a3 空字符

同样的也有right join

指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据

此时的取出的是:

1 a1 b1

2 a2 b2

4 空字符 b4

count(*)，count(1)与count(column)区别

count(*)返回组中的项数。包括 NULL 值和重复项。

count(1)指的并不是计算1的个数，而是指表的第一个字段，如果第一个字段没有建立索引，他的效率是很低的

count(column name)默认查询的是指定字段非空的个数，如果你想查询数据的所有行数，恰巧指定字段又是一个可存在空库数据的字段，那么得到的数据就不会是期望的值。

如果表没有主键，那么count(1)比count(*)快。

如果有主键，那么count(主键，联合主键)比count(*)快。

如果表只有一个字段，count(*)最快。

英文资料中：“ On MyISAM, doing a query that does SELECT COUNT(*) FROM {some_table}, is very fast, since MyISAM keeps the information in the index”

也就是说MyISAM中，表的总数缓存在索引中。而现在的Mysql引擎一般为InnoDB,执行count(*)就要全表查询，所以不推荐用count(*)。

查找mysql总数

SELECT COUNT(*) AS recordCount FROM ( " +你需要找的sql语句+") A

查找重复

SELECT name,age,sex FROMtable1UNION

SELECT name,age,sex FROM table2

查找非重复

SELECT

*

FROMtable1asA,

table2asBWHEREA.createTime=B.createTimeAND A.name=B.nameAND A.sex =B.sex条件若干~

查找表中某个字段是否有重复的快速方法

SELECT 字段名 from 表名 GROUP BY字段名

出现的条数和统计的不一致就是有重复的，否则应该一样

查询日期(在数据库转换好)

DATE_FORMAT(now(),'%Y-%m-%d')

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')

DATE_FORMAT(NOW(),'%m-%d-%Y')

DATE_FORMAT(NOW(),'%d %b %y')

DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

结果类似：

Dec 29 2008 11:45 PM

12-29-2008

29 Dec 08

29 Dec 2008 16:25:46.635

Mysql连接创建时配置

参数名称

参数说明

缺省值

最低版本要求

user

数据库用户名(用于连接数据库)

所有版本

password

用户密码(用于连接数据库)

所有版本

useUnicode

是否使用Unicode字符集，如果参数characterEncoding设置为gb2312或gbk，本参数值必须设置为true

false

1.1g

characterEncoding

当useUnicode设置为true时，指定字符编码。比如可设置为gb2312或gbk

false

1.1g

autoReconnect

当数据库连接异常中断时，是否自动重新连接？

false

1.1

autoReconnectForPools

是否使用针对数据库连接池的重连策略

false

3.1.3

failOverReadOnly

自动重连成功后，连接是否设置为只读？

true

3.0.12

maxReconnects

autoReconnect设置为true时，重试连接的次数

3

1.1

initialTimeout

autoReconnect设置为true时，两次重连之间的时间间隔，单位：秒

2

1.1

connectTimeout

和数据库服务器建立socket连接时的超时，单位：毫秒。 0表示永不超时，适用于JDK 1.4及更高版本

0

3.0.1

socketTimeout

socket操作(读写)超时，单位：毫秒。 0表示永不超时

0

3.0.1

通常mysql连接URL可以设置为：

jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false

用户名和密码一般是配置在XML中

在使用数据库连接池的情况下，最好设置如下两个参数：

autoReconnect=true&failOverReadOnly=false

需要注意的是，在xml配置文件中，url中的&符号需要转义成&。比如在tomcat的server.xml中配置数据库连接池时，mysql jdbc url样例如下：

jdbc:mysql://localhost:3306/test?user=root&password=&useUnicode=true&characterEncoding=gbk

&autoReconnect=true&failOverReadOnly=false

合并结果

date　　　　　flag

2015-12-12 23:48:54 　　1

2015-12-12 23:51:02　　2

2015-12-12 23:52:16　　4

2015-12-13 00:18:24　　6

2015-12-13 00:21:16　　7

SELECT DATE_FORMAT(date,'%Y-%m-%d'),GROUP_CONCAT(flag ORDER BY flag DESC) from grouptest GROUP BY DATE_FORMAT(date,'%Y-%m-%d');

2015-12-12 　　4,2,1

2015-12-13　　7,6

MySQL事务隔离级别

MySQL查询当天、昨天数据

SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=0 -- 当天

SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=-1 -- 昨天，如果now()在前面，则1为昨天

SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())<=0 AND DATEDIFF(字段,NOW())>=n --包含当天SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())<0 AND DATEDIFF(字段,NOW())>=n --不包含当天

DATEDIFF() 函数用于返回两个日期之间的天数。 语法：DATEDIFF(date1,date2) date1 和 date2

参数是合法的日期或日期/时间表达式。 注释：

1. 只有值的日期部分参与计算。

2. 当日期date1date2 时函数返回值为负数。

3. Mysql的DATEDIFF只有两个参数。SQL Server有三个参数

日期处理

where DATE(createTime) = DATE(?)

WHERE DATE_FORMAT(createTime, '%Y-%m-%d') = DATE_FORMAT(CURDATE(), '%Y-%m-%d')

单独处理某个记录

比如我需要获取日志记录，其中启动时间按照创建时间获取，而其他字段按修改时间获取

SELECTcode, name, status,CASE WHEN name= 'start' THENcreateTimeELSEIFNULL(lastModifiedDatetime,createTime)END AStimeFROM表名WHERE 条件

MYSQL日志

show engine innodb status;

exists和not exists的用法

EXISTS或者NOT EXISTS是把主查询的字段传到后边的查询中作为条件，返回值是TRUE或者FALSE。EXISTS TRUE，那么就是查询条件成立，结果会显示出来。NOT EXISTS TRUE，则为FALSE，查询连接条件不成立。

例子：A表中有个字段不为空。然后匹配不存在于B表的记录

SELECT * FROM(SELECT * FROM tableA WHERE createDate IS NOT NULL) aWHERE

NOT EXISTS(SELECT * FROM tableB b WHERE b.id= a.id)

mark一种not exists的left join的写法

SELECT * FROM tableA a LEFT JOIN(SELECT * FROM tableB) b on a.id = b.id WHERE b.id IS NULL

关联更新

SELECT * FROMA；SELECT * FROMB；--A和B都有customerID，需要将B的name赋值给A

UPDATE A a LEFT JOIN B b on a.customerID= b.customerID SET a.name=b.name--后面可以补充条件，例如名字为空的，才赋值

WHERE a.name is null;

表结构输出

做设计时经常需要将数据库的结构对应成word或者excel，如果一个个手动敲，会很浪费时间，可以通过一个查询将其展示出来：

SELECTCOLUMN_NAME 字段名,

COLUMN_COMMENT 注释,

COLUMN_TYPE 类型,CASE WHEN IS_NULLABLE ='NO' THEN '是' else '否' END必传,

COLUMN_COMMENTFROMINFORMATION_SCHEMA.COLUMNSwhere

--table_schema 为数据库名称，到时候只需要修改成你要导出表结构的数据库即可

table_schema ='DB_NAME'

AND

--table_name表名，到时候换成你要导出的表的名称--如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了，所以还是建议写上要导出的名名称

table_name = 'TABLE_NAME'