《常用plsql语句示例》由会员分享，可在线阅读，更多相关《常用plsql语句示例（13页珍藏版）》请在金锄头文库上搜索。
1、1说明：复制表(只复制结构,源表名：a 新表名：b)SQL: select * into b from a where 15说明：两张关联表，删除主表中已经在副表中没有的信息SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 说明：-SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATEFROM TABLE1, (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_D
2、ATEFROM (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) )
3、 Y, WHERE X.NUM = Y.NUM （+）AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) 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 Manage
4、r裡面查詢 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 rown
5、o =(SELECT SUM(row)FROM (SELECT 1 AS row, *FROM table) AS aWHERE a.autoid =1and datepart(dd,logtime) select deptno, dname, emps 32 from ( 3 select d.deptno, d.dname, rtrim(e.ename |, | 4 lead(e.ename,1) over (partition by d.deptno 5 order by e.ename) |, | 6 lead(e.ename,2) over (partition by d.deptn
6、o 7 order by e.ename) |, | 8 lead(e.ename,3) over (partition by d.deptno 9 order by e.ename) |, | 10 lead(e.ename,4) over (partition by d.deptno 11 order by e.ename) |, | 12 lead(e.ename,5) over (partition by d.deptno 13 order by e.ename), ) emps, 14 row_number () over (partition by d.deptno 15 orde
7、r by e.ename) x 16 from emp e, dept d 17 where d.deptno = e.deptno 18 ) 19 where x = 1 20 / DEPTNO DNAME EMPS - - - 10 ACCOUNTING CLARK, KING, MILLER 20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH 30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARDoracle FAQQ1.怎样创建表？A. CREATE TABLE ROYAL_MTABLE
8、(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 T2WHERE
9、 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 T
10、ABLE ROYAL_MTABLE MODIFY RM_STR_FIELD NULL;4Q9.怎样给表加上主键？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 FOR
11、EIGN 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 ROY
12、AL_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 IND
13、EX 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
14、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
15、 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
16、 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;5Q31.怎样删除角色？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,
