SQL skills: quickly learn some exceptional and exquisite/& amp; quot; SQL/& amp; q_MySQL

SQL tips: quickly learn some of the exceptional and exquisite statements: amp; quot; SQLamp; amp; quot; bitsCN.com

Exquisite "SQL" statements:

◆ Copy table (only copy structure, source table name: a new table name: B)

SQL: select * into B from a where 1 <> 1

◆ Copy a table (copy data, source table name: a target table name: B)

SQL: insert into B (a, B, c) select d, e, f from B;

◆ Show articles, submissions, and last reply time

SQL: select a. title, a. username, B. adddate from table,

(Select max (adddate) adddate from table where table. title = a. title) B

◆ Description: external join query (table name 1: table a name 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

◆ Schedule reminder five minutes in advance

SQL: select * from schedule where datediff ('Minute ', f start time, getdate ()> 5

◆ Two associated tables: delete information that is not in the secondary table in the primary table

SQL:

Delete from info where not exists

(Select * from infobz where info. infid = infobz. infid)

◆ Note:

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') comment '/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

◆ Note:

SQL:

Select * from studentinfo where not exists (select * from student where

Studentinfo. id = student. id) and department name = '"& strdepartmentname &"' and professional name

= '"& Strpolicsionname &" 'Order by gender, student origin, total score of the college entrance examination.

BitsCN.com