basic frequent Sql commends in sql server

description: sql comments for basic query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
-- use 
use testdb
go

select * from people
select top 1 * from people

-- insert into people values (1, 'lily', 'f', 12)
-- insert into people values (2, 'chao', 'm', 16)
-- insert into people values (3, 'alin', 'f',null)

select id, name from people

-- order by
select name, age from people
order by age desc,id

-- order by number
select name, age from people
order by 2 --2 means age

-- isnull()
select name, ISNULL (age, '') from people

--alias
select name, ISNULL (age, '') as a from people

-- concat strings
select 'people ' + TRIM (name) + ' is age of ' + convert(varchar, isnull(age, '')) as [description]
from people

-- round()
select age, age * 10 as 'age10',
round(convert(float,age)/8, 1) as 'ageD8',
round(age/3, 0) as 'ageD3'
from people

-- where
select * from people
where age <> 12

-- and or
-- select date from time where data between '2005-10-10' and '1/1/2006'
select * from people where id = 2 or id = 3
select * from people where name = 'chao' and age =16

-- wildcard % like
select * from people where name like '%h%'

-- _ single character
select * from people where name like '_hao%'

-- in
select * from people
where age in (14,16)

-- not in
select * from people
where age not in (16)

-- is null
select * from people
where age is null

-- is not null
select * from people
where age is not null

more on search4fan.github.io