登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

面包会有的

... ...

 
 
 

日志

 
 

php常用SQL语句  

2012-02-03 21:12:31|  分类: PHP |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

常规查询
一、查询表内容:
     select * from `tablename`
     select `nicheng` from `tablename`
     select distinct `nicheng` from `tablename` 
二、where查询:
     select `id` from `tablename` where `id`>10 or (`id`<5)
三、in用法:
     select * from `tablename` where `nicheng` in ('萧明', '秦城') 
四、between用法:
     select * from `tablename` where `id` between '003' and '009'
五、排序查询(ASC由小往大/DESC由大往小):
     select id , mima from `tablename` 
     where `nicheng`='萧明'
     order by `id` ASC
     limit 0 , 30
六、查询表中数据项数:
     select count(*) from `tablename`
七、查询字段中内容数量:
     select `nicheng`, count(*) from `tablename` group by `nicheng`


模糊查询
     SQL的模式匹配允许使用“_”匹配任何单个字符,而“%”匹配任意数目字符(包括零个字符):
一、查询以“萧”开头的数据: 
     select * from `tablename` where `nicheng` like "萧%" 
二、查询以“城”结尾的数据: 
     select * from `tablename` where `nicheng` like "%城" 
三、查询包含一个“敬”的数据:
     select * from `tablename` where `nicheng` like "%敬%"  
四、查询正好包含3个字符的名字,使用“_”模式字符: 
     select * from `tablename` where `nicheng` like "___"   
     正则表达式查询:
五、查询以z或Z开头的数据:
     select * from `tablename` where `nicheng` regexp "^[zZ]"  
     select * from `tablename` where `nicheng` regexp "^[a-zA-Z]"  
六、查询以g或G结尾的数据:
     select * from `tablename` where `nicheng` regexp "g$"


多表查询:
一、多表查询:
       $sql=mysql_query("select * from `tb_student`,`tb_sscore` where tb_=tb_ and tb_=$sno",$conn);
二、表的别名查询统计:
       $sql=mysql_query("select * from `tb_student` as tb_s,`tb_sscore` as tb_c where tb_=tb_ and tb_=$sno",$conn);
三、合并结果集:
  $sql=mysql_query("select `userid`,`username`,`sex`,`age` from `tb_worker1` union select `ygid`,`name`,`sex`,`age` from `tb_worker2`",$conn);


联合查询:
一、多表联合查询:
$sql=mysql_query("
     select * from `tablename` where `nicheng` like "%敬%"
     union 
     select * from `info` where `nicheng` like "张%"",$conn);
二、联合查询结果排序:
$sql=mysql_query("select * from `tb_1` union all select * from `tb_2` order by `age` desc",$conn);
三、条件联合查询:
$sql=mysql_query("select `pubname` from `tb_pub` group by `pubname` having `pubname`='人民邮电出版社' or `pubname`='机械工业出版社' union all select `pubname` from `tb_pub` group by `pubname` having `pubname`<>'人民邮电出版社' and `pubname`<>'机械工业出版社'",$conn);
SELECT t1 . * , , 
FROM uchome_tagspace t1
LEFT JOIN uchome_mtag t2 ON = 
WHERE =1


多表查询

一、简约嵌套查询:
       $sql=mysql_query("select * from `tb_student_score_info` where `sid` in (select `sid` from `tb_student_info` where `sid`=$sno)",$conn);
二、复杂嵌套查询:
$sql=mysql_query("select * from `tb_laborage` where `name` in (select `name` from `tb_dept` where `name` in (select `name` from `tb_personnel` where `knowledge`='本科'))",$conn);
三、嵌套查询应用:
$sql=mysql_query("select * from `tb_laborage` where `jbgz` ".$_POST[tj]." any(select `jbgz` from `tb_laborage` where name='".$_POST[name]."')",$conn);
四、IN子查询:
     1、限定范围查询:
      $sql=mysql_query("select * from `tb_student_score` where `sid` in (select `sid` from `tb_student_score` where `sid` between $from and $to) ",$conn);
      2、查询记录信息:
$sql=mysql_query("select * from `tb_bookinfo` where `bookname` in (select `bookname` from `tb_bookinfo` where `bookname` like '%$bookname%') ",$conn);


排序分组
一、对数据进行降序查询:
      $sql=mysql_query("select * from `tb_sp` order by `sl` desc",$conn);
二、对数据进行多条件排序查询:
      $sql=mysql_query("select * from `tb_goods` order by `sl` desc,dj asc",$conn); 
三、对统计结果进行排序:
$sql=mysql_query("select *,sum(num) as totalnum from `tb_bookinfo` group by `bookname` order by `totalnum` desc",$conn); 
四、单列数据分组统计:
  $sql=mysql_query("select *,sum(num) as totalnum from `tb_bookinfo` group by `bookname` order by `totalnum` desc",$conn); 
五、多列数据分组统计:
  $sql=mysql_query("select *,sum(xcsl) as xc,sum(x ssl) as xs from `tb_book1` group by `bookname` ",$conn); 
六、多表分组统计:
  $sql=mysql_query("select *,sum(tb_xs.x ssl) as xsl,sum(tb_) as kcl from `tb_bk`,`tb_xs` where tb_=tb_ group by `bookname` ",$conn);


聚集函数
一、汇总统计查询:
       $sql=mysql_query("select sum(yy) as sumyy,sum(yw) as sumyw,sum(sx) as sumsx,sum(ls) as sumls from `tb_student_score` ",$conn);  
二、均值统计查询:
$sql=mysql_query("select avg(yy) as avgyy,avg(yw) as avgyw,avg(sx) as avgsx,avg(ls) as avgls from `tb_student_score` ",$conn);  
三、最小值统计查询:
$sql=mysql_query("select * from `tb_gemsell` where `outprice-inprice` in (select min(`outprice-inprice`) as minsell from `tb_gemsell`) ",$conn);  
四、最大值统计查询:
       $sql=mysql_query("select * from `tb_sale` where `sale` in (select max(sale) from `tb_sale` where month(xdate)=$yue and year(xdate)=2005)",$conn);  
五、统计大于某值记录:
       $sql=mysql_query("select count(*) as total from `tb_sale` where `sale`>$num",$conn);

  评论这张
 
阅读(1573)| 评论(0)

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018