sql基础查询语句
数据库文件百度云地址:www.pan.baidu.com
脚步:下载博客园文件:select_learn.rar
1、TOP限制返回行数[percent]
select top 5 * from book_info --显示前5行数据 select top 30 percent * from book_info --显示前30%行数据 [percent]:结果行集的百分比
2、[distinct]消除重复数据行
select distinct publishName from book_info --[distinct]:消除重复数据
3、使用表达式
STR(表达式数,长度,小数点)(+)连接符
select name,STR(stocknum,3,2)+'本' from book_info --STR(表达式数,长度,小数点)(+)连接符
4、聚合函数
SQL Server 2008 R2提供了14个聚合函数,常用函数如下:
--count:查询输出行数 --sum:总和 --AVG:平均值 --max:最大值 --min:最小值 select count(*) as '记录', sum(price) as '总和', avg(price) as '平均值', max(price) as '最大值', min(price) as '最小值' from book_info
5、关系运算符,逻辑运算符,范围运算符,模糊查询,列表运算符,空值判断符;
--关系运算:=(等于),<>(不等于),>(大于),<(小于),>=(大于等于),<=(小于等于); 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 --介绍为空值
6、group by分组统计查询
--group by分组统计查询 select publishName,count(*) from book_info group by publishName
7、having子句限定查询(对group by统计后结果进行筛选,用法与where一样,但where不能出现在group by后面)
select publishName,count(*) from book_info group by publishName having COUNT(*)<3
8、order by排序查询,无法对varchar(max)使用
--asc:升序(默认) desc:降序 select * from book_info order by price desc --价格从高到底 select * from book_info order by publishName asc,price desc --先排序出版社在价格从高到底
9、内连接:inner join
select * from book_info inner join sys_code on publishName=sys_code.type_bm where sys_code.type=9
10、外连接:left[outer]join(左外连接);right[outer]join(右外连接);full[outer]join(全外连接)
--翻译--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
11、多表连接:from table1 join table2 on 连接表达式 join table2 on 连接表达式
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
12、子查询:[out]in
--子查询[not]in select * from sys_code where type=9 and --查询出版社 type_bm in (select style from book_info)--图书信息里已有的出版社
13、比较子查询:all、any、some(不是太重要,需要再学习)
--比较子查询: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)
14、联合查询:union。。必须在目标列表中有相同数目的表达式
union和join的区别和联系:union是将相同列的若干条数据进行合并,而join是将两个个或者多个表的若干列进行连接,一个是对行进行操作,一个是对列进行操作
--联合查询:union[all] select * from book_info where author='王伟平' union --并运算,不包括重复 --union all并运算,包括重复 select * from book_info where author='向忠宏'
15、查看重复值
在borrow表中查Bid重复值
select * from borrow where Bid in (select Bid from borrow group by Bid having COUNT(1)>=2)
16、隐藏手机号码
select REPLACE(CusID,SUBSTRING(CusID,4, 4),'****') from Ord_Comment--手机号码处理
17、经纬度算距离
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()))