SQL 增、删、改、查、数据处理、类型转换、sql关键字、select运算及字符处理（二）
数据的查找
注意
SQL中等于判断用单个=，而不是==。
Where中可以使用的其他逻辑运算符：(||)or、(&&)and、(!)not、<、>、>=、<=、 <>（或!=）等
1.查找全部、查找一列、按条件查找
查找全部 select * from myclass 查找一列 select classname from myclass 按条件查找 select classname from myclass where classid=3
2.修改列的显示名称（单引号可以有可以没有）
as 第一种 select tSId as ID, tEnglish as 英语成绩,tMath as '数学成绩' from TblScore 第二种 select ID=tSId,数学成绩=tMath,'英语成绩'=tEnglish from TblScore
3.关键字
1）Top
查询前多少条 select Top 5 * from TblScore
2）排序 注意： 无法对汉字进行排序
order by 列名 asc desc 降序 select * from TblScore order by tEnglish asc 升序 select * from TblScore order by tEnglish desc
3）去重distinct
去除重复项 select distinct * from MyClass 查询所有数据的时候不一定去掉列中的重复项 select distinct ClassDesc from MyClass 查询单列数据的时候可以去掉重复项
4）聚合函数 注意：NULL不参与聚合函数的运算
COUNT : count()表示的是条数 select COUNT(*) from MyClass 注意： 当其中有一列使用了聚合函数，其他要显示的列也需要使用聚合函数 MAX： 最大值 select MAX(tEnglish) from TblScore MIN：最小值 select MIN(tEnglish) from TblScore SUM：和 select SUM(tEnglish) from TblScore AVG：平均值 select AVG(tEnglish) from TblScore
5）条件取值
范围取值 and 和 between and
and select * from TblScore where tEnglish>120 and tEnglish<140 between --between 值一 and 值二 之间的数据 select * from TblScore where tEnglish between 120 and 140
指定数据 or 和 in
or select * from Customers where Country='UK' or Country='USA' in select * from Customers where Country IN('UK','USA')
6) LIKE 模糊查询
--以“小”开头，后面任意 select * from TblStudent where tSName like '小%' --中间有“小”的数据 select * from TblStudent where tSName like '%小%' --以“小”开头后面2个字符，“_”表示一个占位 select * from TblStudent where tSName like '小__' --同上 LEN 字符的长度 select * from TblStudent where tSName like '张%' and LEN(tSName)=3 --以“张”开头，后面以任意小写字母，在加一个字符 select * from TblStudent where tSName like '张[a-z]_' --以“张”开头，加任意字符，加一个%，在加上任意字符 select * from TblStudent where tSName like '张%[%]%'
7) NULL和 IS NULL
--数据是null select * from TblScore where tEnglish is null --数据不是null
select * from TblScore where tEnglish is not null
查找数据NULL 将其修改为.....
select *, ISNULL(convert(varchar(10),tEnglish),'缺考') from TblScore
8) group by 分组， having 分组之后筛选条件
use kuyi select 数量=count(*),ClassDesc from MyClass group by ClassDesc --分组 having COUNT(*)>2 --分组后在筛选条件
9）合并数据表
union去除重复项 select uname,uage from T1 union select uname,uage from T2
union all不去除重复项 select uname,uage from T1 union all select uname,uage from T2
10）复制表
复制数据，结构，
select * into NewMyClass1 from MyClass where 1=1 select * into NewMyClass2 from MyClass
复制数据表结构，不复制数据
select * into NewMyClass3 from MyClass where 1<>1 select Top 0 * into NewMyClass4 from MyClass
11）select运算与处理字符
1.select '字符串'+'字符串'
select 125+125
2.select 时间=GETDATE(); --当前时间3.DATEADD 当前时间下，往后时间
select DATEADD(YEAR,-261,GETDATE()) --261年前 注意，年的极限就是-261，-262就崩了 select DATEADD(MONTH,10,GETDATE()) --10月后 select DATEADD(DAY,10,GETDATE()) --10日后 select DATEADD(hour,10,GETDATE()) --10时后 select DATEADD(minute,10,GETDATE()) --10分后 select DATEADD(second,10,GETDATE()) --10秒后
3.DATEDIFF 两个时间段所经过的时间
select DATEDIFF(SECOND,'2000-01-01',GETDATE());
4.DATEPART 日期的指定部分 select DATEPART(YEAR,GETDATE())
5.select '哈哈'+null
结果是null
6.LEN 字符串的个数 select LEN('中国abc') as 字符个数
7.DATALENGTH 字节的个数 select DATALENGTH('asd中国') as 字节个数
8.UPPER 小写转大写 select UPPER('abc')
9.LOWER 大写转小写 select LOWER('ABC');
10.LTRIM 去掉前空格 select '=='+LTRIM(' 中国')+'==';
11.RTRIM去掉后空格 select '=='+RTRIM('中国 ')+'==';
12.去掉两边空格 select '=='+LTRIM(RTRIM(' 中国 '))+'=='
13.截取字符 LEFT 从开始截取？个字符 select LEFT('中国人abc',2); SUBSTRING 从？截取？个字符 select SUBSTRING('a中国cdefg',2,3);
14.percent 查找前？%几的数据 查找前10%的数据 1进制 select Top 10 percent * from student
15.取整数 CEILING（） select CEILING(2.2) 结果是3
12）选择判断
(
case
when A>B then A
end
)
--称号= --( -- case -- when SUM(销售数量*销售价格)>6000 then '金牌' -- when SUM(销售数量*销售价格)>5500 then '银牌' -- when SUM(销售数量*销售价格)>4500 then '铜牌' -- else '普通' -- end --)
13）将负数转换成正数 abs
abs(-10) 结果是10
14）分页
方式一 not in
--每页8条数据，查询第四页的数据 select top 8 * from Customers where CustomerID not in (select top ((4-1)*8) CustomerID from Customers)
方式二 ROW_NUMBER()over(order by CustomerID)
select * from (select *,序号=ROW_NUMBER()over(order by CustomerID) from Customers) as newCustomers where 序号 between (4-1)*8+1 and 4*8
15）开窗函数
Over()
1.over子句与排名开窗函数一起用
语法：over([partition by 列1] order by 列2)。必须有order by 子句
2.over子句与聚合开窗函数一起用
语法：over([partition by 列1])不能使用order by子句了。
rank()
相同数据显示并列排号
ROW_NUMBER()
不能用在where子句中，所以将带行号的执行结果作为子查询，就可以将结果当成表一样用了
16）分类 partition
先对商品进行分类，然后对每一类商品的id进行排序
select id,商品名称,行号=ROW_NUMBER() over(partition by 商品名称 order by id asc) from MyOrders
17）行内数据转换列名
select * from (SELECT * FROM ROLE_PROPERTIES_CELLS(nolock) where ROLE_ID = '6d51fc9b-c8d5-abf5-4b1b-682af64cba3d') t PIVOT( MAX(String_Value) FOR Property_Name IN (Operator,Condition,CorporationCode,NcCompanyCode, CostCenterCode,ProjectCode,OperatorType,ProjectCityCode))b
