基本sql语句

执行顺序

 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

 

转载于:https://www.cnblogs.com/ptisagoodman/p/9473023.html