说明：复制表(只复制结构,源表名：a 新表名：b)SQL: select * into b from a where 1<>1说明：拷贝表(拷贝数据,源表名：a 目标表名：b)SQL: insert into b(a, b, c) select d,e,f from b;说明：显示文章、提交人和最后回复时间SQL: select a.title,ername,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b说明：外连接查询(表名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说明：两张关联表，删除主表中已经在副表中没有的信息SQL:delete from info where not exists ( select * from infobz where info.infid=infobz.infid )说明：--SQL:SELECT A.NUM, , B.UPD_DATE, B.PREV_UPD_DATEFROM TABLE1,(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATEFROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHANDFROM TABLE2WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,(SELECT NUM, UPD_DATE, STOCK_ONHANDFROM TABLE2WHERE 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 ) BWHERE A.NUM = B.NUM说明：--SQL:select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩说明：从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）SQL:SELECT erper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,说明：四表联查问题：SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....说明：得到表中最小的未使用的ID号SQL:SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)查找重复的记录select * from fish where fish_name in(select fish_name from(select fish_name,count(fish_name) from fishgroup by fish_namehaving count(fish_name)>1));统计某字段中所有不同的记录的个数select nsrbm,count(nsrbm)from t11_nsrjbqkwhere ...group by nsrbmhaving count(nsrbm)>1SELECT *FROM lettersWHERE ((.ID IN (SELECT ID FROM letters As Tmp GROUP BY ID HAVING Count(*)>1 )))ORDER BY ID;在SQL Enterprise Manager裡面查詢Access裡面的數據SELECT *FROM OPENROWSET('msdasql', 'dsn=odbc數據源名;trusted_connection=yes','select * from table')SQL中isnull函数的作用是将将两个参数字段中不为空的值取出SELECT t1.a, t1.b, ISNULL(t1.c, t2.d)FROM t1, t2WHERE t1.a = t2.a为查询的结果编上序列号select IDENTITY(int,1,1)as id,a,b,c into #1 from tableselect * from #1或SELECT rowno =(SELECT SUM(row)FROM (SELECT 1 AS row, *FROM table) AS aWHERE a.autoid <= b.autoid), *FROM table bORDER BY autoid日期型字段的操作select * from inetsrvlog wheredatepart(mm,logtime)= 3and datepart(dd,logtime)>=1and datepart(dd,logtime)<=15insert into tablename (...,..) values ...,...PL/SQL精典编程例：說明：用SCOTT/TIGER登入ORACLE，執行看看效果，可以將某個字段中的多行放入同一行，以前以為不可能用一條SQL實現在，竟然可以。
主要理解lead() over(),row_number() over()就可以啦，很奇妙的ORACLE吧，有誰有更好的辦法或SQLSERVER的實現，也請貼出。
SQL> select deptno, dname, emps2 from (3 select d.deptno, d.dname, rtrim(e.ename ||', '||4 lead(e.ename,1) over (partition by d.deptno5 order by e.ename) ||', '||6 lead(e.ename,2) over (partition by d.deptno7 order by e.ename) ||', '||8 lead(e.ename,3) over (partition by d.deptno9 order by e.ename) ||', '||10 lead(e.ename,4) over (partition by d.deptno11 order by e.ename) ||', '||12 lead(e.ename,5) over (partition by d.deptno13 order by e.ename),', ') emps,14 row_number () over (partition by d.deptno15 order by e.ename) x16 from emp e, dept d17 where d.deptno = e.deptno18 )19 where x = 120 /DEPTNO DNAME EMPS------- ----------- ------------------------------------------10 ACCOUNTING CLARK, KING, MILLER20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARDoracle FAQQ1.怎样创建表？A. CREATE TABLE ROYAL_MTABLE(RM_INT_FIELD INTEGER,RM_STR_FIELD VARCHAR2(64))CREATE TABLE ROYAL_DTABLE(RD_INT_FIELD INTEGER,RD_STR_FIELD VARCHAR2(32))Q2.怎样删除表？A. DROP TABLE ROYAL_DTABLE;Q3.怎样创建视图？A. CREATE OR REPLACE VIEW ROYAL_MDVIEW ASSELECT T1.RM_STR_FIELD AS F1, T2.RD_STR_FIELD AS F2 FROM ROYAL_MTABLE T1, ROYAL_DTABLE T2 WHERE T1.RM_INT_FIELD = T2.RM_INT_FIELDQ4.怎样删除视图？A. DROP VIEW ROYAL_MDVIEW;Q5.怎样给表添加字段？A. ALTER TABLE ROYAL_DTABLE ADD RM_INT_FIELD INTEGER;Q6.怎样删除表中某个字段？A. ALTER TABLE ROYAL_DTABLE DROP COLUMN RM_INT_FIELD;Q7.怎样给某个字段添加约束？A. ALTER TABLE ROYAL_MTABLE MODIFY RM_STR_FIELD NOT NULL;Q8.怎样去除某个字段上的约束？A. ALTER TABLE ROYAL_MTABLE MODIFY RM_STR_FIELD NULL;Q9.怎样给表加上主键？A. ALTER TABLE ROYAL_MTABLE ADD CONSTRAINT PK_ROYAL_MTABLE PRIMARY KEY (RM_INT_FIELD);Q10.怎样删除表的主键？A. ALTER TABLE ROYAL_MTABLE DROP CONSTRAINT PK_ROYAL_MTABLE CASCADE;Q11.怎样给表添加一个外键？A. ALTER TABLE ROYAL_DTABLE ADD CONSTRAINT FK_ROYAL_DTABLE FOREIGN KEY (RM_INT_FIELD) REFERENCES ROYAL_MTABLE (RM_INT_FIELD) ON DELETE CASCADE;Q12.怎样删除表的一个外键？A. ALTER TABLE ROYAL_DTABLE DROP CONSTRAINT FK_ROYAL_DTABLE;Q13.怎样给字段加上CHECK？A. ALTER TABLE ROYAL_MTABLE ADD CONSTRAINT CHK_RM_STR_FIELD CHECK (RM_STR_FIELD IN ('Y','N'));Q14.怎样去掉字段上的CHECK？A. ALTER TABLE ROYAL_MTABLE DROP CONSTRAINT CHK_RM_STR_FIELD;Q15.怎样给字段设置默认值？A. ALTER TABLE ROYAL_DTABLE MODIFY RD_STR_FIELD DEFAULT 'ROYAL';Q16.怎样移去字段的默认值？A. ALTER TABLE ROYAL_DTABLE MODIFY RD_STR_FIELD DEFAULT NULL;Q17.怎样创建索引？A. CREATE UNIQUE INDEX IDX_ROYAL_DTABLE ON ROYAL_DTABLE (RM_INT_FIELD);Q18.怎样删除索引？A. DROP INDEX IDX_ROYAL_DTABLE;Q19.怎样创建用户？A. CREATE USER TESTUSER IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT; Q20.怎样删除用户？A. DROP USER TESTUSER CASCADE;21.怎样将对象权限（object privileges）授予用户？A. GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE TO TESTUSER;GRANT INSERT, UPDATE, DELETE ON ROYAL_DTABLE TO TESTUSER;GRANT SELECT, ALTER ON ROYAL_DTABLE TO TESTUSER WITH GRANT OPTION;Q22.怎样从用户收回对象权限？A. REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_DTABLE FROM TESTUSER;Q23.怎样将角色权限（role privileges）授予用户？A. GRANT CONNECT TO TESTUSER WITH ADMIN OPTION;GRANT DBA TO TESTUSER;Q24.怎样从用户收回角色权限？A. REVOKE DBA FROM TESTUSER;Q25.怎样将系统权限（system privileges）授予用户？A. GRANT ALTER ANY TABLE TO TESTUSER WITH ADMIN OPTION;Q26.怎样从用户收回系统权限？A. REVOKE ALTER ANY TABLE FROM TESTUSER;Q27.怎样创建序列？A. CREATE SEQUENCE RM_INT_FIELD_SEQMINvalue 1MAXvalue 999999999999999999999999999START WITH 1INCREMENT BY 1CACHE 10ORDER;Q28.怎样删除序列？A. DROP SEQUENCE RM_INT_FIELD_SEQ;Q29.怎样获取序列值？A. SELECT RM_INT_FIELD_SEQ.NEXTVAL FROM DUAL;Q30.怎样创建角色？A. CREATE ROLE TESTROLE;Q31.怎样删除角色？A. DROP ROLE TESTROLE;Q32.怎样将对象权限（object privileges）授予角色？A. GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE TO TESTROLE;Q33.怎样从角色收回对象权限？A. REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER ON ROYAL_MTABLE FROM TESTROLE;Q34.怎样将角色权限（role privileges）授予角色？A. GRANT DBA TO TESTROLE;Q35.怎样从角色收回角色权限？A. REVOKE DBA FROM TESTROLE;Q36.怎样将系统权限（system privileges）授予角色？A. GRANT CREATE TABLE TO TESTROLE;Q37.怎样从角色收回系统权限？A. REVOKE CREATE TABLE FROM TESTROLE;Q38.不等于条件有哪几种写法？（茴香豆问题:)）A. SELECT * FROM ROYAL_MTABLE WHERE RM_STR_FIELD != 'Y';SELECT * FROM ROYAL_MTABLE WHERE RM_STR_FIELD ^= 'Y';SELECT * FROM ROYAL_MTABLE WHERE RM_STR_FIELD <> 'Y';Q39.like子句的用法？A. SELECT * FROM ROYAL_DTABLE WHERE RD_STR_FIELD LIKE '%Y%';SELECT * FROM ROYAL_DTABLE WHERE RD_STR_FIELD LIKE '_Y%';Q40.请举出一个where子查询简单例子？A. SELECT * FROM ROYAL_DTABLE WHERE RM_INT_FIELD IN (SELECT RM_INT_FIELD FROM ROYAL_MTABLE WHERE RM_STR_FIELD NOT IN ('Y','B'));Q41.Oracle常用字符串处理函数有哪些？A. || 连接两个字符串； LENGTH 字符串长度； TRIM/LTRIM/RTRIM 截断串左（右）指定字符串（包括空串）； LOWER/UPPER 将字符串转换为小/大写，等等。