SQL Server：知识点（1）

char(10) 定长 非Unicode 数据经常变的情况下使用，因为不会导致数据页更改
varchar(10) 变长 非Unicode
nchar(10) 定长 Unicode 适用于多国语言的情况
nvchar(10) 变长 Unicode

执行sql语句的顺序
from>where>group by>having>select>order by

升序 order by asc
降序 order by desc
默认升序

case 表达式

select staff.id,
    case gender 
        when '男' then 'male'
    else 'female' end
from staff

取前几条记录

select top (3) with ties id from staff order by...
       top (10)percent

跳过第一条 取之后两条

select id from staff order by id offset 1 rows fetch next 2 rows only

转化
标准SQL cast
T-SQL convert

--代码转自互联网

select CAST('123' as int)   -- 123
select CONVERT(int, '123')  -- 123

select CAST(123.4 as int)   -- 123
select CONVERT(int, 123.4)  -- 123 

select CAST('123.4' as int)
select CONVERT(int, '123.4')
-- Conversion failed when converting the varchar value '123.4' to data type int.

select CAST('123.4' as decimal)  -- 123
select CONVERT(decimal, '123.4') -- 123 


select CAST('123.4' as decimal(9,2))  -- 123.40
select CONVERT(decimal(9,2), '123.4') -- 123.40


declare @Num money
set @Num = 1234.56
select CONVERT(varchar(20), @Num, 0)  -- 1234.56
select CONVERT(varchar(20), @Num, 1)  -- 1,234.56
select CONVERT(varchar(20), @Num, 2)  -- 1234.5600