【mysql】mysql中FIND_IN_SET的使用方法

Continue Read..

mysql中,有时我们在做数据库查询时,需要得到某字段中包含某个值的记录,但是它也不是用like能解决的,使用like可能查到我们不想要的记录,它比like更精准,这时候mysql的FIND_IN_SET函数就派上用场了,下面来具体了解一下。

FIND_IN_SET(str,strlist)函数

str 要查询的字符串

strlist 字段名 参数以”,”分隔 如 (1,2,6,8)

查询字段(strlist)中包含(str)的结果,返回结果为null或记录

下面举例说明

test表中有如下字段及值

下面我想查询area中包含”1″这个参数的记录

SELECT * from test where FIND_IN_SET('1',area)

返回值

下面查询btype字段中包含”15″这个参数的值

SELECT * from test where FIND_IN_SET('15',btype)

返回值

下面查询btype字段中包含”5″这个参数的值

SELECT * from test where FIND_IN_SET('5',btype)

返回值为null,因为btype中没有”5”这个值,它不同于 like 模糊查询,它是以“,”来分隔值

接下面查询btype字段中包含”20″这个参数的值

SELECT * from test where FIND_IN_SET('20',btype)

当然它的返回值为null,因为字段中没有这个值

FIND_IN_SET和like的区别

like是广泛的模糊匹配,字符串中没有分隔符,Find_IN_SET 是精确匹配,字段值以英文”,”分隔,Find_IN_SET查询的结果要小于like查询的结果。

更多参考:http://www.devdo.net/mysql-find_in_set.html

转载 :http://www.cnblogs.com/manongxiaobing/p/4682698.html

声明:此文系舞林cuznwww.wulinlw.org)原创稿件,转载请保留版权

【Mysql】linux下mysql主从配置

Continue Read..

用虚拟机搭建了2个mysql

192.168.31.222 (主)

192.168.31.224 (从)

 

 

然后在测试能不能互相连接

./mysql -h 192.168.31.224 -uroot -proot   在主上连接从

./mysql -h 192.168.31.222 -uroot -proot   在从上连接主  

 

如果互相连接不上  就查找下原因 我的是没有开放外部访问

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";

FLUSH PRIVILEGES;

互相连接成功之后

 

修改 /etc/my.cnf文件

主:

log-bin=mysql-bin

binlog_format=mixed

server-id   = 1   #(主)

expire_logs_days = 10

early-plugin-load = ""

 

 

#cuzn

auto_increment_offset=1

auto_increment_increment=2

#log-error=/mysqldata/mysqld.log

#pid-file=/mysqldata/mysqld.pid

#master-host=192.168.31.222

#master-user=root

#master-password=root

#master-port=3306

replicate-do-db=cuzn

replicate-ignore-db=mysql

#master-connect-retry=60



从:

 

log-bin=mysql-bin

binlog_format=mixed

server-id   = 2 #从数据库

expire_logs_days = 10

early-plugin-load = ""

#cuzn

#binlog-do-db = cuzn #需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行

#binlog-ignore-db=mysql   #不同步mysql系统数据库

sync-binlog=1

auto_increment_offset=1

auto_increment_increment=2

#log-error=/mysqldata/mysqld.log

#pid-file=/mysqldata/mysqld.pid

#master-host=192.168.31.222

#master-user=root

#master-password=root

#master-port=3306

replicate-do-db=cuzn

replicate-ignore-db=mysql



然后重启mysql服务

 

然后各自连接各自的mysql服务

在主里面运行

grant replication slave on *.* to root@"192.168.31.222" identified by "root"; #分配账号权限

 

show master status;

 

点击查看原图

 

获取到我用红框 款住的数据
然后在从的mysql里面执行

stop slave;

change master to master_host='192.168.31.222',master_user='root',master_password='root',master_log_file='mysql-bin.000011' ,master_log_pos=154;

stop slave;

执行show slave status\G  后可以看到 

点击查看原图

 

2个都是yes 就是成功了  然后就可以去测试了  

一般 主写从读 分别设置2个账号 主里面基本是全部权限  从里面的账号设置成只读权限 (如果从也能写的话 会主从错误的 )

 

 

参考网址 

http://blog.chinaunix.net/uid-25266990-id-3465550.html

最后感谢 范明童鞋的帮助!

 

 

 

 


声明:此文系舞林cuznwww.wulinlw.org)原创稿件,转载请保留版权

wamp记录所有mysql执行日志

Continue Read..

查看系统服务,wampmysqld64,

查看详细信息,可以看到启动路径类似e:\wamp\bin\mysql\mysql5.6.17\bin\mysqld.exe wampmysqld64

可以看到带了参数,我们在my.ini里面加一段

[wampmysqld64]

general_log=1

general_log_file="e:/wamp/logs/mysql_sqllog.log"

 

这样就会记录所有的sql语句,类似下面的样子

 151203 11:04:26    1 Query SET PROFILING=1

   1 Query SHOW STATUS

   1 Query SHOW STATUS

   1 Query SELECT * FROM `user`

   1 Query SHOW STATUS

   1 Query SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID

   1 Query SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.000419*100,3), '%') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=12 GROUP BY STATE

   1 Query SELECT * FROM `yii2_test`.`user` LIMIT 0

   1 Query SHOW COLUMNS FROM `yii2_test`.`user`

声明:此文系舞林cuznwww.wulinlw.org)原创稿件,转载请保留版权

【Mysql】随机取几条数据

Continue Read..
SELECT  LOGO,MID,TITLE,PRICE,LINKNAME 
FROM t_secondinfo AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(ID) FROM t_secondinfo)) AS ID) AS t2 
WHERE t1.ID >= t2.ID 
ORDER BY t1.ID ASC LIMIT 5;

声明:此文系舞林cuznwww.wulinlw.org)原创稿件,转载请保留版权

[转载]mysql分组取每组前几条记录(排名) 附group by与order by的研究

Continue Read..

原文链接:http://www.cnblogs.com/mo-beifeng/archive/2012/02/07/2341886.html


--按某一字段分组取最大(小)值所在行的数据

复制代码
/*
数据如下:
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5

*/  

复制代码

 

--创建表并插入数据:

 

复制代码
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')

go 

复制代码

 --一、按name分组取val最大的值所在行的数据。

复制代码
 --方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*from tb where name = a.name and val > a.val ) order by a.name
/*
name       val         memo                
---------- ----------- --------------------
a          3           a3:a的第三个值
b          5           b5b5b5b5b5

*/ 

复制代码

 本人推荐使用1,3,4,结果显示1,3,4效率相同,2,5效率差些,不过我3,4效率相同毫无疑问,1就不一样了,想不搞了。

--二、按name分组取val最小的值所在行的数据。

复制代码
--方法1:
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3:
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*from tb where name = a.name and val < a.val) order by a.name
/*
name       val         memo                
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值

*/ 

复制代码

 --三、按name分组取第一次出现的行所在的数据。

复制代码
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name       val         memo                
---------- ----------- --------------------
a          2           a2(a的第二个值)
b          1           b1--b的第一个值

*/  

复制代码

 

--四、按name分组随机取一条数据。
复制代码
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name       val         memo                
---------- ----------- --------------------
a          1           a1--a的第一个值
b          5           b5b5b5b5b5

*/ 

复制代码

 --五、按name分组取最小的两个(N个)val

复制代码
select a.* from tb a where 2 > (select count(*from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*from tb where name = a.name and val < a.val having Count(*< 2order by a.name
/*
name       val         memo                
---------- ----------- --------------------
a          1           a1--a的第一个值
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
b          2           b2b2b2b2

*/ 

复制代码

 --六、按name分组取最大的两个(N个)val

复制代码
select a.* from tb a where 2 > (select count(*from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val descorder by a.name,a.val
select a.* from tb a where exists (select count(*from tb where name = a.name and val > a.val having Count(*< 2order by a.name
/*
name       val         memo                
---------- ----------- --------------------
a          2           a2(a的第二个值)
a          3           a3:a的第三个值
b          4           b4b4
b          5           b5b5b5b5b5

*/  

复制代码

 

--七,假如整行数据有重复,所有的列都相同(例如下表中的第5,6两行数据完全相同)。
        按name分组取最大的两个(N个)val
复制代码
 1  /*
 2 数据如下:
 3 name val memo
 4 a    2   a2(a的第二个值)
 5 a    1   a1--a的第一个值
 6 a    1   a1--a的第一个值
 7 a    3   a3:a的第三个值
 8 a    3   a3:a的第三个值
 9 b    1   b1--b的第一个值
10 b    3   b3:b的第三个值
11 b    2   b2b2b2b2
12 b    4   b4b4
13 b    5   b5b5b5b5b5
14 
15 */ 
16

 

复制代码


 

 附:mysql “group by ”与"order by"的研究--分类中最新的内容

 这两天让一个数据查询难了。主要是对group by 理解的不够深入。才出现这样的情况

这种需求,我想很多人都遇到过。下面是我模拟我的内容表

我现在需要取出每个分类中最新的内容
select * from test group by category_id order by `date`

结果如下

明显。这不是我想要的数据,原因是msyql已经的执行顺序是

引用

写的顺序:select ... from... where.... group by... having... order by..
执行顺序:from... where...group by... having.... select ... order by...

所以在order by拿到的结果里已经是分组的完的最后结果。
由from到where的结果如下的内容。

到group by时就得到了根据category_id分出来的多个小组


到了select的时候,只从上面的每个组里取第一条信息结果会如下

即使order by也只是从上面的结果里进行排序。并不是每个分类的最新信息。
回到我的目的上 --分类中最新的信息
根据上面的分析,group by到select时只取到分组里的第一条信息。有两个解决方法
1,where+group by(对小组进行排序)
2,从form返回的数据下手脚(即用子查询)

由where+group by的解决方法
对group by里的小组进行排序的函数我只查到group_concat()可以进行排序,但group_concat的作用是将小组里的字段里的值进行串联起来。
select group_concat(id order by `date` desc) from `test` group by category_id


再改进一下
select * from `test` where id in(select SUBSTRING_INDEX(group_concat(id order by `date` desc),',',1) from `test` group by category_id ) order by `date` desc



子查询解决方案
select * from (select * from `test` order by `date` desc) `temp`  group by category_id order by `date` desc


 

 

【Mysql】使用DBExportDoc V1.0 For MySQL生成数据库字典

Continue Read..

1.进入系统控制面板,打开管理工具,然后打开数据源

点击查看原图

2.点击添加选择 Mysql ODBC 5.3 Unicode  Driver

点击查看原图

 

点击查看原图

 

这个是前期配置

 

 

下面打开 我附件里面的 DBExportDoc V1.0 For MySQL.doc文件  

点击查看原图

 

点击连接数据库  新建连接 选择 ODBC 

点击查看原图点击查看原图

 

  测试通过后 点击确定 然后回到 新建连接的那个框  点击连接

点击查看原图

 

选择要导出的数据表 然后点击导出 就OK了 

 

 

这里要注意  数据库里面写了注释 导出的时候才会有注释

声明:此文系舞林cuznwww.wulinlw.org)原创稿件,转载请保留版权

mysql重复记录取最后一条记录方法

Continue Read..

记录一下,网上一堆错的。。。

versions表的version字段,有重复的,但是我要取出重复version的最后一条

SELECT * FROM (select * from versions order by id desc) as t group by version ;

 

SELECT * FROM `versions_gjol` group by version ;

这条语句会按version分组,但是有重复的数据的时候,取的是第一条的

原数据如下:

 id  version  detail
 1  1.1  aaa
 2  1.2  bbb
 3  1.2  ccc
 4  1.3  ddd
SELECT * FROM `versions_gjol` group by version ;

查询结果:

注意红色部分

 id  version  detail
 1  1.1  aaa
 2  1.2  bbb
 3

 1.3

 ddd

 

 所以我们需要先把数据倒序,在从结果集中group by 。

需要注意限制条件,避免全表扫描。

SELECT * FROM (select * from versions order by id desc) as t group by version ;

查询结果:

注意红色部分

 id  version  detail
 1  1.1  aaa
 3  1.2  ccc
 4

 1.3

 ddd

 

声明:此文系舞林cuznwww.wulinlw.org)原创稿件,转载请保留版权

【Mysql】Mysql中EXPLAIN用法

Continue Read..
  写程序的时候,往往需要去查看sql语句的执行效率,以方便我们如何优化我们的程序,设计数据库。所以我们就不得不了解一些explain的用法。
   首先看看输出地参数:Mysql中EXPLAIN用法

这些参数中,我们常常需要关心的是这几个参数
1,select_type,就是select类型.
主要有这几种,
SIMPLE:这个是简单的sql查询,不使用UNION或者子查询
PRIMARY:子查询中最外层的select
UNION:UNION中的第二个或后面的SELECT语句
DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT:UNION的结果。
SUBQUERY:子查询中的第一个SELECT.
DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
DERIVED:派生表的SELECT(FROM子句的子查询) 
2,table 输出行所引用的表
3,
type 结类型。各种类型的信息在下面给出。
system
表仅有一行(=系统表)。这是const联结类型的一个特例。
const
表有最多一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被剩下的优化器认为是常数。 const表很快,因为它们只读取一次!
eq_ref
对于每个来自于先前的表的行组合,从该表中读取一行。这可能是最好的联结类型,除了const类型。它用在一个索引的所有部分被联结使用并且索引是UNIQUE或PRIMARY KEY。
ref
对于每个来自于先前的表的行组合,所有有匹配索引值的行将从这张表中读取。
如果联结只使用键的最左面前缀,不或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联结能基于键值选择单个行的话),使用ref。如果被使用的键仅仅匹配一些行,该联结类型是不错的。
range
只有在一个给定范围的行将被检索,使用一个索引选择行。ref列显示哪个索引被使用。
index
这与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL
对于每个来自于先前的表的行组合,将要做一个完整的表扫描。
如果表格是第一个没标记const的表,这通常不好,并且通常在所有的其他情况下很差。你通常可以通过增加更多的索引来避免ALL,使得行能从早先的表中基于常数值或列值被检索出。

从上面可以发现,从上至下依次是越来越坏的结果,当然最好的还是Null,没有查询本表。


4,possible_keys:possible_keys列指出MySQL能使用哪个索引在该表中找到行
5,key:key列显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。
6,key_len key_len列显示MySQL决定使用的键长度。如果键是NULL,长度是NULL。注意这告诉我们MySQL将实际使用一个多部键值的几个部分。
7 ref:ref列显示哪个列或常数与key一起用于从表中选择行。
8,rows rows列显示MySQL相信它必须检验以执行查询的行数。
9,Extra
如果是Only index,这意味着信息只用索引树中的信息检索出的。通常,这比扫描整个表要快。
如果是where used,它意味着一个WHERE子句将被用来限制哪些行与下一个表匹配或发向客户。
如果是impossible where 表示用不着where
如果是Using filesort表示用到了文件排序,通常在数据量大的情况下,要减少这种查询

mysql切换状态字段的小技巧

Continue Read..

用户,商品之类的表通常有状态字段status,

0为不可用, 1为正常

一条语句切换状态如下

UPDATE `user` SET `status`=1^status WHERE uid=123
与1异或得到相反状态

声明:此文系舞林cuznwww.wulinlw.org)原创稿件,转载请保留版权

【MYSQL】in和like的关系

Continue Read..

我的数据库结构是这样的..有一个字段..里面都是 1,2,4,45,43  的数据  而且必须是5到15个

用like  "%XX%"统计的时候是400多个

用in(XX)统计的时候是190多个

我开始以为in和like查询的结果应该一样的...测试之后才晓得

in  =  like    "XX%"  

in   != like   "%XX%"

声明:此文系舞林cuznwww.wulinlw.org)原创稿件,转载请保留版权