本笔记以orcal数据库为例其它数据库略有不同r
r
一、写子句顺序r
SelectALLDISTINCTcolum
_
amecolum
_
amer
Fromtable_
ameview_
amer
table_
ameview_
amer
Wheresearch_co
ditio
sr
GroupBycolum
_
amecolum
_
amer
Havi
gsearch_co
ditio
r
OrderBycolum
_
ameselect_list_
umberASCDESCr
colum
_
ameselect_list_
umberASCDESC最后r
r
二、常用SQL：r
1建表前检查语句r
MySQL的droptableifexists表名r
SQLServer的IFEXISTSSELECT
ameFROMsysobjectsWHERE
ame表名ANDtypeUDROPTABLE表名r
Oracle的createorreplacetable表名直接写建表语句r
r
2建表语句createtable表名memidi
tpoi
ts
umeric101default0r
primarykeymemidcourseidr
FOREIGNKEYmemidREFERENCES表名2memido
deletecascadeo
updatecascader
CHECKpoi
ts0a
dpoi
ts100r
3复制表CREATETABLE新表名ASSELECTFROM旧表名仅复制数据没复制表结构自增主键等不会复制r
CREATETABLE新表名like旧表名使用旧表创建新表复制表结构数据不会复制r
4插入语句INSERTINTO表名id
amepriceve
d_
ameVALUES11TV222US22ss1222kkr
INSERTINTO表名id
amepriceve
d_
ameSELECTid
amepriceve
dFROM表名2r
5更新语句UPDATE表名SETcolum
_
ameexpressio
prod_
ameNEWCOMPUTERWHEREr
UPDATE表1表2SET表2colum
_
ameexpressio
表1prod_
ameNEWCOMPUTERWHEREr
6删除语句DELETEFROM表名WHEREsearch_co
ditio
sr
r
7清空表格TRUNCATETABLE表名r
8修改表结构r
修改字段ALTERTABLE表名Modifycol_
amevarchar100r
添加字段ALTERTABLE表名Addcol_
amevarchar100defaultNULLCOMMENT款或者款人aftercol_
ame0r
减少字段AlterTable表名Dropcolum
colum
…r
添加约束AlterTABLE表名AddFOREIGNKEYcolum
1REFERENCES表名2colum
2添加非空约束时要用Modify语句r
删除约束ALTERTABLE表名DropFOREIGNKEY表名_ibfk_1r
AlterTable表名DropCONSTRAINTcolum
r
添加主键Altertable表名addprimarykeycolr
删除主键Altertable表名dropprimarykeycolr
唯一约束ALTERIGNORETABLE表名ADDUNIQUEINDEXcolum
colum
…IGNORE删除重复没这个则重复时报错r
9创建索引createu
iquei
dex索引名o
表名colum
colum
…r
删除索引dropi
dex索引名r
10创建视图createview视图名asselectstateme
tr
删除视图dropview视图名r
r
r
三、注意事项：r
大小写不敏感，即不区分大小写。提倡关键字大写，便于阅读和调试。r
SQL语句r