常规查询
一、查询表内容：
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);
评论