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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
select #查詢語法
select [欄位] from [DB] ->查詢該DB欄位的所有資料(會有重複的)
select distinct [欄位] from [DB] ->查詢該DB欄位不同的資料
where
select * from [表]
where [欄位] ='值' ;
where not [欄位] ='值';->
where 欄位 =32 ; ->確認欄位的數值 [數字可以不用加引號
where [欄位] ='值' and [欄位2] ='數值2' ->篩選符合兩個條件的資料
where [欄位] ='值' or [欄位2] ='數值2' ->篩選符合條件的資料
order by #排列
select * from [db]
order by [欄位] ;
order by [欄位] desc ; 反向排列
order by [欄位],[欄位];
order by [欄位] desc , [欄位] asc
insert #新增欄位
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Null #空值
select [欄位]
from [表格]
where [表格] is null ;
UPDATE
update [表] set [欄位] ='[新值]' ->改變所有的值
update [表] set [欄位] ='[新值]' where [欄位2]='[欄位2值]'
update [表] set [欄位] ='[新值]',set [欄位2] ='[新值2]' where [欄位3]='[欄位3值]'
delete
delete [表] set [欄位] ='[值]' ->刪除值的所有紀錄
delete from [表] ->刪除該table
function
select [min/max/avg/sum] (欄位) from [表格]; #count= avg=平均
select count from [table] where [欄位] =[欄位的值];
like
like 'a%' ->a開頭的所有欄位的值
like '%a' ->a結尾的所有欄位的值
like '%a%' ->欄位的值有a的
like '_a%' ->第二個值為a的 ex:bagg...
like 'a__%' ->a開頭起大於三個字元 ex:abv...
like 'a%b' ->a開頭b結尾
like [bsp]% ->b.s.p開頭的所有欄位的值
like [a-c]% ->
not like 'a%'->不以a開頭的所有欄位的值
select * from [table]
where [欄位] like'a%' ;
IN
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
BETWEEN
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Aliases
join
(INNER) JOIN: 返回兩個表中具有匹配值的記錄
LEFT (OUTER) JOIN: 返回左表中的所有記錄，以及右表中匹配的記錄
RIGHT (OUTER) JOIN: 返回右表中的所有記錄，以及左表中匹配的記錄
FULL (OUTER) JOIN: 當左表或右表中存在匹配時，返回所有記錄
select 表1,表1欄位1,表2,表2欄位2,表1,表1欄位2
from 表1
inner join 表2 on 表1.表1欄位=表2,表2欄位;
GROUP BY
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
select count (欄位A),(欄位B)
from [table]
group by [欄位B]
Having
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
SQL註解
單行
多行
|