--SQL 版本 |
select @@version |
--与表无关的数据 |
select newid() as TGUID |
--聚合函数,对查询结果进行聚合求值 |
select count (*) from |
select max (Salary) from |
select min (Salary) from |
select Avg (Salary) from |
select sum (salary) from |
--排序 |
order by Age DESC , Salary ASC |
--通配符匹配(半角),模糊匹配 |
where EmpName like '_N' or '%n%' |
--NULL:不知道 |
select NULL +1 |
where EmpName is NULL |
--in: 多值匹配 |
-- 范围 |
select * from EmpLoyee where Age between 20 and 30 |
--把结果分组 Group By |
SELECT [newsID], count (*) from [news] group by [newsID] |
SELECT [newsID], max ([classID]), count (*) from [news] group by [newsID] |
--对分组后的结果进行筛选,Having 字段必须在Select 中,而Where是对原始数据进行过滤 |
SELECT [newsID], max ([classID]), count (*) from [news] group by [newsID] having max ([classID])>2 |
--常用函数 |
select ABS (-1) --求绝对值 |
select CEILING(-3.8) --天花板 取大于这个数字的最大整数 |
select FLOOR(5.3) --地板 取小鱼这个数字的最大整数 |
select ROUND(3.1415,2) --四舍五入 取离这个数字最近的数,2 是精确的位数 |
select len( 'ni hao' ) --字符串长度 |
select LOWER ( 'HELlo' ) --转小写 |
select UPPER ( 'helLO' ) --转大写 |
select LTRIM( ' kong' ) --去左空格 |
select RTRIM( 'kong ' ) --去右空格 |
select LTRIM(RTRIM( ' kong ' )) --去两边空格 |
select substring ( 'nihao' ,2,3) --从二个位子截取三个字符 |
--日期函数 |
select dateadd(dd,3,getdate()) |
select dateadd(mm,-3,getdate()) |
select dateadd(hh,3,getdate()) |
select datediff( day ,getdate(), '20120203' ) --后面减去前面 |
select datepart( day ,getdate()) --选出时间中特定的部分 |
--员工工龄以及对应的人数 |
select datediff( year ,InDate,getdate()), count (*) From Employee |
group by datediff( year ,InDate,getdate()) |
--每一年入职人数 |
select datepart( year ,InDate), count (*) From Employee |
group by datepart( year ,InDate) |
--数据类型转换 |
select cast ( '1' as int ) |
select convert ( int , '1' ) |
select convert (datetime, '20120203' ) |
select convert ( varchar (10),123) |
--TOP 选出Top3到5 限制结果集 |
select top 3 * from [news] where ClassID not in |
( select top 2 ClassID from [news] order by ClassID desc ) |
order by ClassID desc |
--按照ClassID升序,并给选出的每一行记录进行排序 |
select newsID,NewsName, row_number() over( order by ClassID ) as rows from [news] |
--DISTINCT 消除完全重复的行 |
select distinct NewsName from [NEWS] |
--UNION,会把重复数据合并 |
SELECT [newsID],[newsName] FROM [SchoolNews].[dbo].[news] |
UNION |
SELECT [classID], 'CLASSNAME' FROM [SchoolNews].[dbo].[newsClass] |
--把两张表选出的内容合并 |
SELECT '最大NEWSID' , max (newsID) FROM [SchoolNews].[dbo].[news] |
UNION |
SELECT '最大CLASSNAME' , max ([classID]) FROM [SchoolNews].[dbo].[newsClass] |
--统计查询结果 |
select [newsID],ClassID FROM [SchoolNews].[dbo].[news] |
UNION |
select '评论总数' , Sum (ClassID) FROM [SchoolNews].[dbo].[news] |
--当月 |
select datediff( month , '2012-12-10' ,getdate()) |
--ISNULL |
select ISNULL (EmpName, '佚名' ) as 姓名 from Employee |
--case:就是对查询的结果进行判断、分类、转化(其他数据或者多行) |
select EmpName, |
( |
case Sex |
when 'M' then 'man' |
when 'F' then 'woman' |
else '性别错误' |
END |
) as 性别 From Employee |
--case 支持范围 |
select EmpNo, |
( |
case |
when datediff( year ,InDate,getdate())<2 then '一年' |
when datediff( year ,InDate,getdate())>=2 and datediff( year ,InDate,getdate())<3 then '两年' |
else '两年以上' |
END |
) AS 工龄 from Employee |
--Case 选择两列中比较大的一列的值,把值按照某种条件分成两列 |
select |
( |
case |
when a>b then a |
else b |
END |
) as 第一列, |
( |
case |
when b>c then b |
else c |
END |
) as 第二列 from |
--收入,支出 分开显示 |
select FNumber, |
( |
case |
when FAmount>0 then FAmount |
else 0 |
END |
) as 收入, |
( |
case |
when FAmount<0 then ABS (FAmount) |
else 0 |
END |
) as 支出 from T_TEST |
--球队胜负积分:先把胜负逐条分开再进行分组 |
select FName |
SUM ( |
case Score |
when N '胜' then 1 |
else 0 |
END |
) as 胜 |
SUM ( |
case Score |
when N '平' then 1 |
else 0 |
END |
) as 平 |
SUM ( |
case score |
when N '负' then 1 |
else 0 |
END |
) as 负 from T_Test |
group by FName --以名字进行分组 |
--电话号码的设计要为varchar 类型 |
--Index 索引,直接在字段右键添加索引:提高查询效率 对于经常检索的字段建立索引 |
--插入,删除,更新数据的时候速度变慢 占用空间 |
--Like,函数(ABS),类型转换(CAST)等依旧需要全表扫描 |