sql 执行顺序
from -> where -> group by -> having -> select -> order by
select子句可以识别 别名
使用cast函数转换数据类型
1
2SELECT name + CAST(gender as VARCHAR(10) ) ,age
FROM dbo.student使用* 查询结果
1
2
3select bookname ,quantity,book_price ,quantity*book_price as total_price
from bookitem
order by bookname使用case进行条件查询
1
2
3
4
5
6
7
8
9select name,time,redit=
case
when time>=40 then 5
when time>=30 then 4
when time>=20 then 3
else 2
end
from course
order by redit
sql函数
字符串运算符
1 | ASCII,CHAR,LOWER,UPPER,LTRIM,RTRIM, |
将结果转成大写
1
2select upper(bookname) as name ,quantity,book_price
from bookitem去空格
1
2select rtrim(name) + rtrim(dname) as info ,age
from teacher
算术运算符 ABA,SIGN(调试参数的正负号),CEILING(最大),FLOOR
ROUND(四舍五入),COS,PI,RAND(0-1 之间浮点数)
1 select rand()
时间函数
DAY,MONTH,YEAR,DATEADD,DATEDIFF,DATENAME,DATEPART,GETDATE
-CONVERT 函数转换日期时间
1
2
3 CONVERT (data_type [(length), expression,style)
select CONVERT(VARCHAR ,GETDATE(),8) AS now_time