select top 5 * from book_info --显示前5行数据

select top 30 percent * from book_info --显示前30%行数据 [percent]:结果行集的百分比

select distinct publishName from book_info --[distinct]:消除重复数据

select name,STR(stocknum,3,2)+'本' from book_info --STR(表达式数，长度，小数点)（+）连接符

--count:查询输出行数

--sum:总和

--AVG:平均值

--max:最大值

--min:最小值

select count(*) as '记录',

sum(price) as '总和',

avg(price) as '平均值',

max(price) as '最大值',

min(price) as '最小值'

from book_info

--关系运算：=(等于),<>(不等于),>(大于),<(小于),>=(大于等于),<=(小于等于);

select * from book_info where publishName=3--等于

select * from book_info where publishName<>3--不等于

select * from book_info where price>40--大于

--...........

--逻辑运算符：not(非),and(与),or(或)

select * from book_info where publishName=3 and price>40 and not(style=1471)

--...........

--范围运算符：between 开始 and 结束

select * from book_info where price between 40 and 50 --价格在40到50之间

select * from book_info where publishDate between '2012-01-01' and '2013-12-31' --出版时间在2012年到2013年

--模糊查询：[not]like

select * from book_info where name like '%系统%'

--列表运算符：[not]in

select * from book_info where author in ('曹忠明','王伟平')

--空值判断符：

select * from book_info where introduction is null --介绍为空值

--group by分组统计查询

select publishName,count(*) from book_info group by publishName

select publishName,count(*) from book_info group by publishName having COUNT(*)<3

--asc:升序（默认） desc:降序

select * from book_info order by price desc --价格从高到底

select * from book_info order by publishName asc,price desc --先排序出版社在价格从高到底

select * from book_info inner join sys_code on publishName=sys_code.type_bm where sys_code.type=9

--翻译--outer:外面的

--left join显示“left join”左边表所有数据，如果在“left join”右边找不到，相应列为null值

select * from book_info left outer join sys_code on publishName=sys_code.type_bm where sys_code.type=9

--right join显示“right join”右边表所有数据，如果在“right join”左边表找不到，相应列为null值

select * from book_info right outer join sys_code on publishName=sys_code.type_bm where sys_code.type=9

--right join显示“full outer join”左右边表所有数据，如果找不到，相应列为null值

select * from book_info full outer join sys_code on publishName=sys_code.type_bm where sys_code.type=9

select * from book_info b

join sys_code s on b.publishName=s.type_bm

join book_type t on b.style=t.id

where s.type=9

--子查询[not]in

select * from sys_code

where type=9 and --查询出版社

type_bm in (select style from book_info)--图书信息里已有的出版社

--比较子查询：all、any、some

select * from sys_code where type=9 and type_bm>=all(select style from book_info)

select * from sys_code where type=9 and type_bm=any(select style from book_info)

--联合查询：union[all]

select * from book_info where author='王伟平'

union --并运算，不包括重复   --union all并运算，包括重复

select * from book_info where author='向忠宏'

select * from borrow where Bid in (select Bid from borrow group by Bid having COUNT(1)>=2)

select REPLACE(CusID,SUBSTRING(CusID,4, 4),'****') from Ord_Comment--手机号码处理

declare

@GPSLng DECIMAL(12,6)=106.661443,

@GPSLat DECIMAL(12,6)=26.728694,

@Lng  DECIMAL(12,6)=106.661518,

@Lat DECIMAL(12,6)=26.720831

SELECT 6378137.0*ACOS(SIN(@GPSLat/180*PI())*SIN(@Lat/180*PI())+COS(@GPSLat/180*PI())*COS(@Lat/180*PI())*COS((@GPSLng-@Lng)/180*PI()))