SQL高手篇 精妙SQL語句介紹

2022-06-23 21:14:04 字數 1829 閱讀 6423

select *

from bbs_forums

where (left(ltrim(name), 3) = '嘉年華')

【重點】like我就不多說了,先看left函式:

left(要查詢的列名,左起的前幾個字元)='前幾個字元為(二聲)什麼'

再看ltrim函式,這個簡單了,去除某欄位的左邊的空格

1. 說明:複製表(只複製結構,源表名:a,新表名:b)

sql: select * into b from a where 1<>1;

2. 說明:拷貝表(拷貝資料,源表名:a,目標表名:b)

sql: insert into b(a, b, c) select d, e, f from b;

sql: select a.title, a.username, b.adddate

from table a,(

select max(adddate) adddate

from table where table.title=a.title) b  

4. 說明:外連線查詢(表名1:a,表名2:b)

sql: select a.a, a.b, a.c, b.c, b.d, b.f

from a left out join b on a.a = b.c;  

sql: select *

from 日程安排

where datediff(''minute'', f開始時間, getdate())>5   

6. 說明:兩張關聯表,刪除主表中已經在副表中沒有的資訊

sql: delete from info

where not exists(

select *

from infobz

where info.infid=infobz.infid );

7. 說明:——

sql: select a.num, a.name, b.upd_date, b.prev_upd_date

from table1,(select x.num, x.upd_date, y.upd_date prev_upd_date

from (select num, upd_date, inbound_qty, stock_onhand

from table2

where to_char(upd_date,''yyyy/mm'') =

to_char(sysdate, ''yyyy/mm'')) x,

(select num, upd_date, stock_onhand

from table2

where to_char(upd_date,''yyyy/mm'') =

to_char(to_date(to_char(sysdate, ''yyyy/mm'')

|| ''/01'',''yyyy/mm/dd'') - 1, ''yyyy/mm'') ) y,

where x.num = y.num (+)and x.inbound_qty

+ nvl(y.stock_onhand,0) <> x.stock_onhand ) b

where a.num = b.num; 

8. 說明:——

sql: select *

from studentinfo

where not exists(select * from student where studentinfo.id=student.id)

and 系名稱=''"&strdepartmentname&"''

and 專業名稱=''"&strprofessionname&"''