MS SQL 2008笔记之万事开头难(4)
介绍基本的DML （data manipulation language）
——————————————
Select / change data
1. select data:
select [xxx] from [yyy]
distinct col: select distinct [xxx], [zzz] from: get rid of duplicated rows
top n / top n percent (only show first several results from the select statement): n percent will round to integer using a ceiling rule, i.e. 6*10% = 1.
set rowcount n (0, default value — all rows)
as key word — aliasing (as is optional)
“+“: combine two strings
order by [xxx] desc/ asc
where clause (logical order: not > and > or)
where [xxx] like (wild cards)
functions on string: lower, upper, etc.
select … into [tmp_table] from …
2. insert / update / delete data:
(*reminder: primary key doesn’t allow null values)
(1) insert (into) [table] (cols) values (vals1), (vals2)
(*note: values shoule be consistent with each column)
(2) update [table] set [column] = xx
where [col] = / between / not in / in …
(*note: between includes two ends)
(3) delete from [table] where…
3. transactions: (explicite / implicite)
batch different statement in one transaction and if one of them fails, all the changes get rolled back.
begin tran
[statements]
…
commit tran
use of rollback / cube
