常用函數(shù)
數(shù)學函數(shù)
select ABS(-9)
select CEILING(9.00001)
select FLOOR(9.9)
select PI()
select POWER(2,3)
select SORT(9)
select SIGN(0)
select ROUND(892.35,1,7)
select ROUND(892.35,1,0)
select 1+FLOOR(LOG10(48901))
select FLOOR(RAND()*10)

字符串函數(shù)
select LEFT('aaaaaa',2)
select RIGHT('123456',2)
select LEN('1')
select LOWER('aaBN')
select UPPER('aaBN')
select rtrim(LTRIM('aaa'))+'**'-trim().Itrim()
select SUBSTRING('abcdefg',2,3)
select REPLACE('aba','a','ccc')
select STUFF('aaaaa',1,2,'bbbbbb')

日期時間
select GETDATE()
select YEAR(GETDATE())
select MONTH(GETDATE())
select DAY(GETDATE())
select DATEADD(mm,100,'1990-10-10')
select DATEDIFF(yy,'1988-8-8',getdate())
查詢老師的年齡
select teacher_name , DATEDIFF(yy,出生日期，getdate())
from dbo.teacher

轉(zhuǎn)換函數(shù)
select cast('666' as int )+100
select convert(varchar(13),999)+'***'

insert into用法
1.向教師表中加入一個記錄，字段的順序與數(shù)目和表中的一樣
insert into teacher values()
2.字段的數(shù)目與順序自己指定
insert into teacher(name , sex_id) values('張三',1)
3.從select檢索中插入成批記錄
insert into dbo.student
select teacher
from dbo.teacher
delete from dbo.student ming like'張%'

update用法
1.更新一個字段
Update teacher
set teacher_name='里斯'
where teacher_id=2
2.教師的基本工資上調(diào)5%
Update teacjer
set 基本工資=基本工資*(1.05)
3.多字段更新，每一個用，隔開
Update teacher
set teacher_name='李四' ,
teacjer_id = 1
where teacher_id=3