mysql经典sql语句汇集
<h2>mysql case when then+sql子句使用示例一</h2><p><img alt="" id="last" rev="0" src="data/attachment/forum/202401/24/46cd3e1c48a4391ee80b2d92ae6a29fb.jpg" aid="758" style="height:791px; width:1422px" /></p>
<h2>数据库库表之间的数据导入</h2>
<pre>
INSERT INTO t_region(f_id,f_parentid,f_region_name,f_region_type)
SELECT region_id,parent_id,region_name,region_type
FROM ecs_region</pre>
<h2>随机读取数据库中的记录</h2>
如何用MYSQL随机查询几条数据？ 一直以为mysql随机查询几条数据，就用<br />
<br />
SELECT * FROM `table` ORDER BY RAND() LIMIT 5<br />
<br />
就可以了。<br />
但是真正测试一下才发现这样效率非常低。一个15万余条的库，查询5条数据，居然要8秒以上<br />
查看官方手册，也说rand()放在ORDER BY 子句中会被执行多次，自然效率及很低。<br />
<br />
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.<br />
<br />
<br />
搜索Google，网上基本上都是查询max(id) * rand()来随机获取数据。<br />
<br />
SELECT * FROM `table` AS t1 JOIN ( SELECT ROUND ( RAND () * ( SELECT MAX ( id ) FROM >` table >` )) AS id ) AS t2<br />
WHERE t1 . id >= t2 . id ORDER BY t1 . id ASC LIMIT 5 ; <br />
<br />
但是这样会产生连续的5条记录。解决办法只能是每次查询一条，查询5次。即便如此也值得，因为15万条的表，查询只需要0.01秒不到。<br />
上面的语句采用的是JOIN，mysql的论坛上有人使用<br />
<br />
SELECT * FROM >` table >` WHERE id >= ( SELECT FLOOR ( MAX ( id ) * RAND ()) FROM >` table >` ) ORDER BY id LIMIT 1 ; <br />
<br />
我测试了一下，需要0.5秒，速度也不错，但是跟上面的语句还是有很大差距。总觉有什么地方不正常。<br />
于是我把语句改写了一下。<br />
<br />
SELECT * FROM >` table >`<br />
WHERE id >= ( SELECT floor ( RAND () * ( SELECT MAX ( id ) FROM >` table >` ))) <br />
ORDER BY id LIMIT 1 ; <br />
<br />
这下，效率又提高了，查询时间只有0.01秒<br />
最后，再把语句完善一下，加上MIN(id)的判断。我在最开始测试的时候，就是因为没有加上MIN(id)的判断，结果有一半的时间总是查询到表中的前面几行。<br />
完整查询语句是：<br />
SELECT * FROM >` table >`<br />
WHERE id >= ( SELECT floor ( RAND () * (( SELECT MAX ( id ) FROM >` table >` ) - ( SELECT MIN ( id ) FROM >` table >` )) + ( SELECT MIN ( id ) FROM >` table >` ))) <br />
ORDER BY id LIMIT 1 ; <br />
<br />
SELECT * FROM ` table` AS t1 JOIN ( SELECT ROUND ( RAND() * (( SELECT MAX ( id ) FROM >` table >` ) - ( SELECT MIN ( id ) FROM `table` )) + ( SELECT MIN ( id ) FROM `table` )) AS id ) AS t2<br />
WHERE t1 . id >= t2 . id<br />
ORDER BY t1 . id LIMIT 1 ; <br />
最后在php 中对这两个语句进行分别查询10次，<br />
前者花费时间 0.147433 秒<br />
后者花费时间 0.015130 秒<br />
看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。
<p> 最终结果如下：</p>
SELECT s_url,title FROM 表名 AS t1<br />
JOIN (<br />
SELECT ROUND( RAND( ) * (SELECT MAX( id ) FROM 表名 ) ) AS id<br />
) AS t2<br />
WHERE t1.id >= t2.id and t1.z_type = $z_type ORDER BY t1.id asc LIMIT 10<br />
<br />
<br />
<br />
access：select top 4 * from Trade order by cint(rnd(ID)*10) desc<br />
（但似乎有点问题，理论上可以，但实际上只能选取一次，以后也就不会再重新随机选择了。可能是随机种子的问题吧，因为第一次生成种子以后，再进行选择，到第二次随机选取的时候，还是用的第一次生成的种子，下面的例子是将种子变了，目前来说这是一种比较好的方法。<br />
试一下下面的代码：<br />
Randomize<br />
icount = conn.execute("select count(ID) from wittings")(0)<br />
imin = conn.execute("select min(ID) from wittings")(0)<br />
irange = icount - imin<br />
ii = cint(Hour()&Minute()&Second())<br />
i = rnd(ii)<br />
i = abs(csng("0."&right(i,3)))<br />
'response.write(i)<br />
if left(i,3)<1 then<br />
i = i + 0.1<br />
elseif left(i,3)<1 then<br />
i = i + 0.11<br />
end if<br />
'response.write("<br>"&i)<br />
i = abs(csng(i) )<br />
i = cint(i * irange)<br />
if i>=icount then i=i-2<br />
if i<=imin then i=imin+2<br />
'response.write("<br>"&i)<br />
set rs = server.createobject("adodb.recordset")<br />
'sql="select top 2 ID,CompanyName from wittings where isShow=True order by cint(rnd()*"&icount&") desc ,ID desc"<br />
sql="SELECT TOP 2 ID, CompanyName FROM wittings WHERE isShow=True and ID in ("&(i+2)&","&(i-2)&")"<br />
<br />
)
<p> </p>
<p>SQLserver： select top 4 * from Trade order by cint(NEWID()) desc</p>
<h2>经典SQL语句</h2>
<h3>17、mysql给select结果加一个序号</h3>
SELECT @row := @row +1 AS ROWID, login_type, COUNT(*) AS con FROM userlog, (SELECT @row := 0) r where logintime BETWEEN 1410364800 AND 1410451200<br />
<h3>16、统计商品表中各个分类总有多少记录</h3>
select cate_id,count(id) from goods where isshow=1 group by cate_id<br />
<h3>15、mysql查找某个字符串出现多次的方法</h3>
select * from all_email where email regexp '(.*@){2}'<br />
<h3>14、mysql查询及删除重复记录的方法</h3>
<p>查找表中多余的重复记录，重复记录是根据单个字段（email）来判断:</p>
<pre>
select * from email1 where email in
(select email from email1 group by email having count(email) > 1)
这个语句在网上某专家网站上能找到原型，可以直接告诉你，效率好差劲，在我这里跑不动。
</pre>
select * from email1 where length(sname) > 7 and length(sname) <= 9 group by email having count(`email`)>1 <br />
就是这样的语句也要跑十几秒。
<p>在删除重复数据的时候，在网上找了好多，但多数都不管用，在我的数据库中，真实不重复数据可能是35W左右，也就是说还有1W的重复数据，重复次数不等。如果想一次列出来重复的数据，速度异常的慢。几十万数据要整理到什么时候呀。于是就想到了一个折中的办法，distinct，知道了吧，直接将不重复的数据写到新表里，立马不就效果出来了：<br />
insert into email2(email, id, sname) select distinct(email),id, sname from email1，你觉得这个语句能按照你的想象得到新表不重复数据吗？？留给你试验。<br />
<br />
insert into email2 select * from email1 group by email having count(*)>=1</p>
<p>这样1分钟多点，不重复的数据全到了email2表中，来个重命名表名不就行了。鄙视网上所谓的专家，不测试直接发到网上。<br />
<br />
PS：如果不嫌麻烦，也可以用select id, email, count(*) as c from email2 where length(sname) > 7 and length(sname) <= 9 group by email having count(`email`)>1 order by c desc来查一下，最大重复的有多少个，然后慢慢删除，具体语句还没有想起来 -_-</p>
<h3>13、将文章表里的文章总数更新到分类统计字段中</h3>
update category set totalurl= (select count(id) from article where article.pid=category.id and article.isclose=0) ;<br />
<h3>12、mysql导出查询的数据为csv格式</h3>
select * from t_gupiao_code where f_bankuai_id=0 and f_name is not null order by f_code into outfile 'c:/a.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
<h3><strong>11、查找表中不连续的ID且大于156</strong></h3>
<pre>
select *from (select uid from pre_common_member order by uid desc) t
where not exists (select 1 from pre_common_member where uid=t.uid-1) and uid>156</pre>
<h3>10、查询每个分类的最新五条文章</h3>
select t.* from article t where t.id in<br />
(select id from article where tid=t.tid order by addtime desc limit 5) order by t.tid desc, t.addtime desc<br />
<br />
查询每个分类的最新一条文章：<br />
select * from (select id,cate_id, title from article where cate_id > 154 order by id desc) as tt group by tt.cate_id<br />
<h3>9、mysql生成300到500随机数</h3>
select floor(300+rand()*500);<br />
<h3>8、将第二天的price更新到前一天去</h3>
update t_ a left join t_ b on a.f_gupiao_id=b.f_gupiao_id and a.f_gupiao_closed_time=b.f_gupiao_closed_time-86400 set a.f_yesterday_closed_price= b.f_yesterday_closed_price;<br />
<h3>7、mysql 从 表 A 中往 表 B 中插入在表B中不存在且“不固定字段”记录集 的语法</h3>
<p>INSERT INTO 表B SELECT NULL as id, uname,passwd,'2' as u_type,ids as id,'1' as status FROM 表A WHERE NOT EXISTS (SELECT 1 FROM 表B WHERE 表B.id = 表A.id AND 表B.u_type=2)</p>
<h3>6、mysql 统计某字段重复数并统计重复总记录数</h3>
SELECT COUNT(*),字段一 FROM 表A GROUP BY 字段一 HAVING COUNT（字段一）>1;<br />
<h3>5、同一个表中重复数据的最大值</h3>
<p>现在我有一张表</p>
<p>name grade<br />
张三 80<br />
张三 79<br />
张三 81<br />
李四 89<br />
李四 92</p>
<p>需要查询张三、李四的最大分数</p>
<p><strong>方法一：</strong><br />
<strong>select * from tb as a where not exists(select 1 from tb name=a.name and grade>a.grade)</strong></p>
<p>方法二：<br />
select a.* from <strong>tb </strong> a join (select MAX(grade) AS grade,name from tb group by name) b on a.name=b.name and a.grade=b.grade</p>
<p> </p>
<h3>4、汇总一年中各个月分的信息</h3>
select distinct(substr(f_date,1,7)) as cc, count(*) dd from t_abc group by cc order by cc desc<br />
<h3>3。在同一表中取出ID和 ID－1 的记录</h3>
假设ID唯一<br />
select * from tt where f_type=1 and f_name like '%京东广告%'<br />
union all<br />
select a.* from tt a inner join (<br />
select * from tt where f_type=1 and f_name like '%京东广告%') b<br />
on a.id=b.id-1<br />
<h3><br />
2。从access导出查询数据到excel中（详情请查看：<a href="http://blog.apptj.net/html/show-3972-1-1.html" target="_blank">将access查询出的数据导出</a>）</h3>
select * into jmdcw in \'D:\\php\\Apache2.2\\htdocs\\asp\' \'excel 4.0;\' from A order by ID<br />
<h3>1。只复制表结构（详情请查看：<a href="http://blog.apptj.net/html/show-2727-1-1.html" target="_blank" title="整理了一些SQL数据库技巧">整理了一些SQL数据库技巧</a>）</h3>
select * into B from A where 1<>1 （不能复制主键、索引、约束） <h2>mysql语法积累</h2>
<br />
4、convert<br />
在查询中加上convert转换。。。<br />
convert(varchar(10),字段名,转换格式)<br />
<br />
比如：<br />
select user_id,convert(varchar(10),date,11) as date from tb_user<br />
<br />
转换格式：<br />
0或100 month dd yyyy hh:miAMPM<br />
1 mm/dd/yy<br />
2 yy.mm.dd<br />
3 dd/mm/yy<br />
4 dd.mm.yy<br />
5 dd-mm-yy<br />
6 dd month yy<br />
7 month dd,yy<br />
8 hh:mi:ss<br />
9或109 month dd yyyy hh:mi:ss:mmmAMPM<br />
10 mm-dd-yy<br />
11 yy/mm/dd<br />
12 yymmdd<br />
101 mm/dd/yyyy<br />
102 yyyy.mm.dd<br />
103 dd/mm/yyyy<br />
104 dd.mm.yyyy<br />
105 dd-mm-yyyy<br />
106 dd month yyyy<br />
107 month dd,yyyy<br />
108 hh:mi:ss<br />
110 mm-dd-yyyy<br />
111 yyyy/mm/dd<br />
112 yyyymmdd<br />
<br />
3、字符串截取应用<br />
select count(*) as cc from t_xxx_log where substring_index(f_register_ip,".",3)="191.18.0";<br />
<br />
2、unix时间戳转换<br />
SELECT a.*,FROM_UNIXTIME(a.f_addtime,'%Y-%m-%d %H:%i:%S') from xxx<br />
<br />
1、添加多个列<br />
ALTER TABLE t_props<br />
add f_is_useable TINYINT DEFAULT 0 COMMENT '是否可用：0不可使用，1可使用',<br />
add f_currency_type TINYINT DEFAULT 0 COMMENT '货币类型：0金币，1游戏币',<br />
add f_gamecoin INT UNSIGNED DEFAULT 0 COMMENT '游戏币价格';
页:
[1]