执行顺序
from--where--group by--having--select--order by,
Select
Select *from tableTemp where temp=’@temp’ order by temp
Select *from tableTemp where temp=’@temp’ group by temp having temp=’@temp’ order by temp
(having很耗资源,尽量不用)
Select *from bigTable b
Left join smallTable s
On b.id=s.fid
(表关联，是自右向左解析的 so.,尽量把数据量大的表放在最右边来进行关联,oracle 相反)
(where条件的解析顺序是自下而上的,so,要把能筛选出大量数据的条件放在where语句的最下面)
( oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行)
Seletct *from tableTemp1
Union
Seletct *from tableTemp2
Select *from bigTable b where b.id in (Select fid from smallTable )
Select *from bigTable b
Left join (Select fid from smallTable where 先查出一部分可以提高效率)s
On b.id=s.fid
Select a.id,b.name into tableBackup from tableTemp1 a left join tableTemp2 b where a.id=b.fid
SELECT DISTINCT name from tableBackup 姓名去重
Insert
[]表示如果字段对应可以省略
Insert into TableTemp [(tempID,tempName)] values (@tempID,@tempName)
insert into TableBackup (tempID, tempName) select (tempID, tempName) from TableTemp
Select 具体语句参照章节Select
Delete
Delete *from TableTemp where id=@id
Limit 1 -- 只删除先找到的一行
DELETE t1, t2 FROM tablechird AS t1 left join tableparent t2 AS t2 on t1.id=t2.fid
Where t1.id=@id
Update
update tableUpdate set temp1=@temp1，tempName=@tempName where tempName!=@tempName
UPDATE a SET a.id= b.fid, a.name= b.name
FROM A a , B b WHERE a.id = b.id