Quickly master some extremely sophisticated SQL statements-Mysql Tutorial-php.cn

Quickly master some extremely sophisticated SQL statements

黄舟

Release： 2017-02-20 11:43:12

Original

1040 people have browsed it

[Introduction] Exquisite "SQL" statement: ◆Copy table (only copy structure, source table name: a New table name: b) SQL: select * into b from a where 1<>1　 ◆Copy table ( Copy data, source table name: a target table name: b) SQL: insert into b(a, b, c) se

　Exquisite "SQL" statement:

◆Copy table (only copy structure, source table name: a New table name: b)

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

Copy after login

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

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

Copy after login

◆Display article, submitter and last reply time

SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

Copy after login

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

Copy after login

◆Schedule reminder five minutes in advance

SQL: select * from 日程安排 where datediff(&#39;minute&#39;,f开始时间,getdate())>5

Copy after login

◆Two related tables, delete the main Information in the table that is not in the sub-table

SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid )

Copy after login

◆ Description:

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,&#39;YYYY/MM&#39;) = TO_CHAR(SYSDATE, &#39;YYYY/MM&#39;))
 X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,&#39;YYYY/MM&#39;) = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, &#39;YYYY/MM&#39;)
  &brvbar;&brvbar; &#39;/01&#39;,&#39;YYYY/MM/DD&#39;) - 1, &#39;YYYY/MM&#39;) ) Y, WHERE X.NUM = Y.NUM （+）AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) 
  <> X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM

Copy after login

◆ Description:

SQL: select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) 
and 系名称=&#39;"&strdepartmentname&"&#39; and 专业名称=&#39;"&strprofessionname&"&#39; order by 性别,生源地,高考总成绩

Copy after login

The above is the content of quickly mastering some extremely sophisticated SQL statements. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!