请选择 进入手机版 | 继续访问电脑版

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 205|回复: 4

[mysql] mysql经典sql语句汇集

[复制链接]
发表于 2024-1-24 11:30 | 显示全部楼层 |阅读模式

mysql case when then+sql子句使用示例一

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

×
 楼主| 发表于 2024-1-24 11:31 | 显示全部楼层

数据库库表之间的数据导入

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
 楼主| 发表于 2024-1-24 11:33 | 显示全部楼层

随机读取数据库中的记录

如何用MYSQL随机查询几条数据?   一直以为mysql随机查询几条数据,就用

SELECT * FROM `table` ORDER BY RAND() LIMIT 5

就可以了。
但是真正测试一下才发现这样效率非常低。一个15万余条的库,查询5条数据,居然要8秒以上
查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.


搜索Google,网上基本上都是查询max(id) * rand()来随机获取数据。

SELECT *   FROM `table` AS t1 JOIN   ( SELECT ROUND  ( RAND  () *   ( SELECT   MAX  ( id  )  FROM >` table >`  ))  AS id  )  AS t2
 WHERE t1 . id >= t2 . id   ORDER  BY t1 . id  ASC  LIMIT  5 ; 

但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。即便如此也值得,因为15万条的表,查询只需要0.01秒不到。
上面的语句采用的是JOIN,mysql的论坛上有人使用

SELECT *   FROM >` table >`   WHERE id >=   ( SELECT FLOOR  (   MAX  ( id  ) * RAND  ())  FROM >` table >` )  ORDER  BY id  LIMIT   1 ; 

我测试了一下,需要0.5秒,速度也不错,但是跟上面的语句还是有很大差距。总觉有什么地方不正常。
于是我把语句改写了一下。

SELECT *  FROM >` table >`
 WHERE id >=   ( SELECT floor  ( RAND  () *   ( SELECT   MAX  ( id  )  FROM >` table >`  )))   
 ORDER  BY id  LIMIT   1 ; 

这下,效率又提高了,查询时间只有0.01秒
最后,再把语句完善一下,加上MIN(id)的判断。我在最开始测试的时候,就是因为没有加上MIN(id)的判断,结果有一半的时间总是查询到表中的前面几行。
完整查询语句是:
SELECT *  FROM >` table >`
 WHERE id >=   ( SELECT floor  ( RAND  () *   (( SELECT   MAX  ( id  )  FROM >` table >`  ) -  ( SELECT   MIN  ( id  )  FROM >` table >`  )) +  ( SELECT   MIN  ( id  )  FROM >` table >`  )))   
 ORDER  BY id  LIMIT   1 ; 

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
 WHERE t1 . id >= t2 . id
 ORDER  BY t1 . id  LIMIT   1 ; 
最后在php 中对这两个语句进行分别查询10次,
前者花费时间 0.147433 秒
后者花费时间 0.015130 秒
看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。

 最终结果如下:

SELECT s_url,title FROM 表名 AS t1
JOIN (
SELECT ROUND( RAND( ) * (SELECT MAX( id ) FROM 表名 ) ) AS id
) AS t2
WHERE t1.id >= t2.id and t1.z_type = $z_type ORDER BY t1.id asc LIMIT 10



access:select top 4 * from Trade  order by cint(rnd(ID)*10) desc
(但似乎有点问题,理论上可以,但实际上只能选取一次,以后也就不会再重新随机选择了。可能是随机种子的问题吧,因为第一次生成种子以后,再进行选择,到第二次随机选取的时候,还是用的第一次生成的种子,下面的例子是将种子变了,目前来说这是一种比较好的方法。
试一下下面的代码:
      Randomize
   icount = conn.execute("select count(ID) from wittings")(0)
   imin   = conn.execute("select min(ID) from wittings")(0)
   irange = icount - imin
   ii     = cint(Hour()&Minute()&Second())
   i      = rnd(ii)
   i      = abs(csng("0."&right(i,3)))
   'response.write(i)
   if left(i,3)<1 then
      i = i + 0.1
   elseif left(i,3)<1 then
      i =  i + 0.11
   end if
   'response.write("<br>"&i)
   i      = abs(csng(i) )
   i      = cint(i * irange)
   if i>=icount then i=i-2
   if i<=imin  then i=imin+2
   'response.write("<br>"&i)
   set rs = server.createobject("adodb.recordset")
   'sql="select top 2 ID,CompanyName from wittings where isShow=True order by cint(rnd()*"&icount&") desc ,ID desc"
   sql="SELECT TOP 2 ID, CompanyName FROM wittings WHERE isShow=True and ID in ("&(i+2)&","&(i-2)&")"

)

 

SQLserver: select top 4 *  from Trade order by cint(NEWID()) desc

 楼主| 发表于 2024-1-24 11:34 | 显示全部楼层

经典SQL语句

17、mysql给select结果加一个序号

        SELECT @row := @row +1 AS ROWID, login_type, COUNT(*) AS con FROM userlog, (SELECT @row := 0) r where logintime BETWEEN 1410364800 AND 1410451200
 

16、统计商品表中各个分类总有多少记录

         select cate_id,count(id) from goods where isshow=1 group by cate_id
 

15、mysql查找某个字符串出现多次的方法

           select  * from  all_email  where email regexp '(.*@){2}'
 

14、mysql查询及删除重复记录的方法

查找表中多余的重复记录,重复记录是根据单个字段(email)来判断:

select * from email1 where email in 
(select email from email1 group by email having count(email) > 1)
这个语句在网上某专家网站上能找到原型,可以直接告诉你,效率好差劲,在我这里跑不动。
select * from email1 where length(sname) > 7 and length(sname) <= 9 group by email having count(`email`)>1 
就是这样的语句也要跑十几秒。

在删除重复数据的时候,在网上找了好多,但多数都不管用,在我的数据库中,真实不重复数据可能是35W左右,也就是说还有1W的重复数据,重复次数不等。如果想一次列出来重复的数据,速度异常的慢。几十万数据要整理到什么时候呀。于是就想到了一个折中的办法,distinct,知道了吧,直接将不重复的数据写到新表里,立马不就效果出来了:
insert into email2(email, id, sname) select distinct(email),id, sname from email1,你觉得这个语句能按照你的想象得到新表不重复数据吗??留给你试验。

 insert into email2 select * from email1 group by email having count(*)>=1

这样1分钟多点,不重复的数据全到了email2表中,来个重命名表名不就行了。鄙视网上所谓的专家,不测试直接发到网上。

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来查一下,最大重复的有多少个,然后慢慢删除,具体语句还没有想起来 -_-

13、将文章表里的文章总数更新到分类统计字段中

update category  set   totalurl= (select count(id) from article where article.pid=category.id and article.isclose=0)  ;
 

12、mysql导出查询的数据为csv格式

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'

11、查找表中不连续的ID且大于156

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
 

10、查询每个分类的最新五条文章

select t.* from article t where t.id in
(select id from article where tid=t.tid order by addtime desc limit 5) order by t.tid desc,  t.addtime desc

查询每个分类的最新一条文章:
select * from (select id,cate_id, title from article where cate_id > 154 order by id desc) as tt group by tt.cate_id
 

9、mysql生成300到500随机数

select floor(300+rand()*500);
 

8、将第二天的price更新到前一天去

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;
 

7、mysql 从 表 A 中往 表 B 中插入在表B中不存在且“不固定字段”记录集 的语法

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)

6、mysql 统计某字段重复数并统计重复总记录数

SELECT COUNT(*),字段一 FROM 表A GROUP BY 字段一 HAVING COUNT(字段一)>1;
 

5、同一个表中重复数据的最大值

现在我有一张表

name     grade
张三    80
张三    79
张三    81
李四    89
李四    92

需要查询张三、李四的最大分数

方法一:
select * from tb as a where not exists(select 1 from tb name=a.name and grade>a.grade)

方法二:
select a.* from tb  a join (select MAX(grade) AS grade,name from tb group by name) b on a.name=b.name and a.grade=b.grade

 

4、汇总一年中各个月分的信息

select distinct(substr(f_date,1,7)) as cc, count(*) dd  from t_abc group by cc order by cc desc
 

3。在同一表中取出ID和 ID-1 的记录

假设ID唯一
select * from tt where f_type=1 and f_name like '%京东广告%'
union all
select a.* from tt a inner join (
select * from tt where f_type=1 and f_name like '%京东广告%') b
on a.id=b.id-1
    


2。从access导出查询数据到excel中(详情请查看:将access查询出的数据导出

     select * into jmdcw in \'D:\\php\\Apache2.2\\htdocs\\asp\' \'excel 4.0;\' from A order by ID
 

1。只复制表结构(详情请查看:整理了一些SQL数据库技巧

     select   *   into   B   from   A  where   1<>1  (不能复制主键、索引、约束)
 楼主| 发表于 2024-1-24 11:35 | 显示全部楼层

mysql语法积累


4、convert
在查询中加上convert转换。。。
convert(varchar(10),字段名,转换格式)

比如:
select user_id,convert(varchar(10),date,11) as date from tb_user

转换格式:
0或100 month dd yyyy hh:miAMPM
1  mm/dd/yy
2 yy.mm.dd
3 dd/mm/yy
4 dd.mm.yy
5 dd-mm-yy
6 dd month yy
7 month dd,yy
8 hh:mi:ss
9或109 month dd yyyy hh:mi:ss:mmmAMPM
10 mm-dd-yy
11 yy/mm/dd
12 yymmdd
101 mm/dd/yyyy
102 yyyy.mm.dd
103 dd/mm/yyyy
104 dd.mm.yyyy
105 dd-mm-yyyy
106 dd month yyyy
107 month dd,yyyy
108 hh:mi:ss
110 mm-dd-yyyy
111 yyyy/mm/dd
112 yyyymmdd

3、字符串截取应用
select count(*) as cc from t_xxx_log where substring_index(f_register_ip,".",3)="191.18.0";

2、unix时间戳转换
SELECT a.*,FROM_UNIXTIME(a.f_addtime,'%Y-%m-%d %H:%i:%S') from xxx

1、添加多个列
ALTER TABLE  t_props
add f_is_useable TINYINT DEFAULT 0 COMMENT '是否可用:0不可使用,1可使用',
add f_currency_type TINYINT DEFAULT 0 COMMENT '货币类型:0金币,1游戏币',
add f_gamecoin   INT UNSIGNED  DEFAULT 0 COMMENT '游戏币价格';
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|学习笔记

GMT+8, 2024-7-19 22:49 , Processed in 0.028785 second(s), 13 queries , APCu On.

Powered by Discuz! X3.5

© 2001-2024 Discuz! Team.

快速回复 返回顶部 返回列表