數據庫ORACLE基本語句集錦

--建表FAMILYINF

CREATE  TABLE  FAMILYINFO(
     FNO NUMBER CONSTRAINT FC001 PRIMARY KEY,--把字段fno約束為主鍵
     FNAME VARCHAR2(20) CONSTRAINT FC002 NOT NULL,--把字段FNAME的約束條件設為“不能為空”
     SEX VARCHAR2(20) DEFAULT 'MALE'CONSTRAINT FC003 CHECK(SEX IN('MALE','FEMALE'))

,--把字段’SEX‘的約束條件設為’性別默認條件下為’MALE‘，如果自己填寫只能填寫’MALE‘或’FEMALE‘

     FAGE NUMBER,
     REL VARCHAR2(20),--家庭成員關系字段
     HOBITNO NUMBER CONSTRAINT FC004 REFERENCES HOBITINFO(HNO) ON DELETE SET NULL);

 --約束條件設為，表’HOBITINFO‘中字段’HNo‘的外鍵，刪除引用表中字段時，設為空值 

--建興趣表HOBITINF

CREATE TABLE HOBITINFO(
    HNO NUMBER CONSTRAINT HC001 PRIMARY KEY,   --將‘ HNO’設為主鍵
    HNAME VARCHAR2(20) CONSTRAINT HC002 NOT NULL--將‘HNAME’約束條件設為‘不能為空’
);

--刪除表HOBITINFO

DROP TABLE HOBITINFO;

--添加信息

INSERT INTO HOBITINFO VALUES(1,'MOVIE');
INSERT INTO HOBITINFO VALUES(2,'CG');;

--DDL ALTER TABLE (修改表)

ALTER TABLE FAMILYINFO RENAME TO PCFAMILY;--將FAMILYINFO的表名重命名為PCFAMILY
ALTER TABLE PCFAMILY ADD(--添加列即字段及其屬性
    HEIGH VARCHAR2(5)，
    SX VARCHAR2(10) CONSTRAINT PC002 NOT NULL--字段SX創建“不能為空”的約束條件
);
ALTER TABLE PCFAMILY MODIFY(--修改字段的屬性
    HEIGH CHAR(20)
);
ALTER TABLE PCFAMILY DROP COLUMN HEIGH;--刪除字段HEIGH
ALTER TABLE PCFAMILY RENAME COLUMN SX TO SX1;--修改約束字段名
ALTER TABLE PCFAMILY DROP COLUMN SX1;--刪除約束字段
--DDL 之 DROP TABLE(刪除表)
DROP TABLE HOBITINFO CASCADE CONSTRAINTS;
--DDL 之 TRUNCATE TABLE 清空表記錄
TRUNCATE TABLE PCFAMILY;

--DML 之 INSERT(插入，添加記錄）

INSERT INTO PCFAMILY(FNO,SEX,FNAME,FAGE) VALUES(4,'NANE','LIU'||'DAYE',45+20);

--如果指定插入字段順序，則values里的信息就要對照前面指定的字段填寫

INSERT INTO PCFAMILY VALUES();
--學生表
CREATE TABLE STUDENT(
    SNO NUMBER CONSTRAINT SC001 PRIMARY KEY,
    SNAME VARCHAR2(10) CONSTRAINT SC002 NOT NULL,
    SSEX VARCHAR(10) CONSTRAINT S003 CHECK(SSEX IN ('M','F'))
);

--老師表

CREATE TABLE TEACHER(
     TNO NUMBER CONSTRAINT TC001 PRIMARY KEY,
     TNAME VARCHAR2(10) CONSTRAINT TC002 NOT NULL
);

--課程表

CREATE TABLE COURSE(
     CNO NUMBER CONSTRAINT CC001 PRIMARY KEY,
     CNAME VARCHAR2(20) CONSTRAINT CC002 NOT NULL,
     TNO NUMBER CONSTRAINT CC003 REFERENCES TEACHER(TNO)

);

--成績表

CREATE TABLE SCORE(
     SNO NUMBER CONSTRAINT SSC001 REFERENCES STUDENT(SNO),
     CNO NUMBER CONSTRAINT SSC002 REFERENCES COURSE(CNO),
     GRADE VARCHAR2(20),
     CONSTRAINT SSC003 PRIMARY KEY(SNO,CNO)
);

--查詢表是否建立成功已經存在

SELECT * FROM STUDENT;
SELECT * FROM TEACHER;
SELECT * FROM COURSE;
SELECT * FROM SCORE;

DROP TABLE STUDENT;
DROP TABLE TEACHER;
DROP TABLE COURSE;
DROP TABLE SCORE;

INSERT INTO STUDENT VALUES(1,'aaaa','M');
INSERT INTO STUDENT VALUES(2,'bbbb','M');
INSERT INTO STUDENT VALUES(3,'cccc','F');
INSERT INTO TEACHER VALUES(1,'AAAA');
INSERT INTO TEACHER VALUES(2,'BBBB');
INSERT INTO TEACHER VALUES(3,'CCCC');
INSERT INTO COURSE VALUES(1,'yw',2);
INSERT INTO COURSE VALUES(2,'sx',3);
INSERT INTO COURSE VALUES(3,'yy',2);
INSERT INTO SCORE VALUES(1,2,89);
INSERT INTO SCORE VALUES(2,3,120);
INSERT INTO SCORE VALUES(3,1,110);
--DML 之UPDATE
UPDATE SCORE SET GRADE=GRADE-1;
UPDATE SCORE SET GRADE=GRADE+1 WHERE SNO=2;
UPDATE SCORE SET GRADE=GRADE+1 WHERE SNO=2 AND CNO=3;

-------------------------------------------------------------------------------------------

DROP刪除為物理刪除，delete刪除為邏輯刪除。

-- DQL 之 SELECT

SELECT CHR(107) HAHA FROM DUAL;
SELECT CURRENT_DATE FROM DUAL;
SELECT TRUNC(ABS(MONTHS_BETWEEN(DATE'1998-08-08',CURRENT_DATE)/12)) AGE FROM DUAL; 
SELECT SNO AS 學號, SNAME 學生姓名 FROM STUDENT;
SELECT ALL SSEX FROM STUDENT;
SELECT DISTINCT SSEX FROM STUDENT;
SELECT UNIQUE SSEX FROM STUDENT;
SELECT * FROM SCORE WHERE GRADE < 60;
SELECT ROWID,ROWNUM,STUDENT.* FROM STUDENT;
SELECT * FROM STUDENT WHERE ROWNUM = 1;
SELECT * FROM STUDENT WHERE ROWNUM <= 2;
SELECT * FROM (SELECT ROWNUM AS RN,STUDENT.* FROM STUDENT) WHERE RN = 2;
SELECT CNO,AVG(GRADE) FROM SCORE GROUP BY CNO;
SELECT CNO,AVG(GRADE) FROM SCORE GROUP BY CNO HAVING AVG(GRADE) > 80;
SELECT SSEX,COUNT(SNO) FROM STUDENT GROUP BY SSEX;
SELECT SNO FROM SCORE WHERE CNO = 'C001' AND GRADE = (SELECT MAX(GRADE) FROM SCORE WHERE CNO = 'C001');
SELECT SNO FROM SCORE WHERE CNO = 'C001' AND GRADE >= ALL(SELECT GRADE FROM SCORE WHERE CNO = 'C001');
SELECT * FROM SCORE ORDER BY CNO ASC,GRADE DESC;
SELECT * FROM STUDENT,SCORE WHERE STUDENT.SNO = SCORE.SNO;
SELECT SNAME FROM STUDENT,SCORE
WHERE STUDENT.SNO = SCORE.SNO AND CNO = 'C001' AND GRADE = (SELECT MAX(GRADE) FROM SCORE WHERE CNO = 'C001');
SELECT * FROM STUDENT INNER JOIN SCORE ON STUDENT.SNO = SCORE.SNO;
SELECT * FROM STUDENT INNER JOIN SCORE USING(SNO);
SELECT * FROM STUDENT CROSS JOIN SCORE WHERE STUDENT.SNO = SCORE.SNO;
SELECT * FROM STUDENT NATURAL INNER JOIN SCORE;
SELECT * FROM STUDENT LEFT OUTER JOIN SCORE ON STUDENT.SNO = SCORE.SNO;
SELECT * FROM STUDENT RIGHT OUTER JOIN SCORE USING(SNO);
SELECT * FROM STUDENT FULL OUTER JOIN SCORE USING(SNO);

-- 查詢考試不及格的學生姓名和掛科的科目名稱

SELECT SNAME,CNAME
FROM (SCORE LEFT JOIN STUDENT USING(SNO))
     LEFT JOIN COURSE USING(CNO)
WHERE GRADE < 60;

-- 查詢所有java考試成績高于平均分的學生的姓名和成績
SELECT SNAME,GRADE
FROM (SCORE LEFT JOIN STUDENT USING(SNO))
     LEFT JOIN COURSE USING(CNO)
WHERE CNAME = 'JAVA'
AND GRADE > (
    SELECT AVG(GRADE) FROM SCORE LEFT JOIN COURSE USING(CNO)
    WHERE CNAME = 'JAVA'
);
select * from (SELECT  *  from   test   RIGHT   JOIN   t_grade  using(ID) )  a  where `數學` > 90

-- 查詢同時參加了C001和C002科目考試的學生編號
SELECT SNO FROM
(SELECT SNO,CNO FROM SCORE WHERE CNO = 'C001')
INNER JOIN
(SELECT SNO,CNO FROM SCORE WHERE CNO = 'C002')
USING(SNO);

-- 查詢lili同學參加的所有科目考試中成績最高的那門科目的授課老師名字
SELECT TNAME,CNAME FROM
TEACHER LEFT JOIN COURSE USING(TNO)
WHERE CNO IN (
      SELECT CNO FROM
      SCORE LEFT JOIN STUDENT USING(SNO)
      WHERE SNAME = 'LILI'
      AND GRADE = ( SELECT MAX(GRADE) FROM
                    SCORE LEFT JOIN STUDENT USING(SNO)
                    WHERE SNAME = 'LILI'
      )
);

-- 查詢庫存目前還有多少
CREATE TABLE CLOTHER_STORE(
    CTYPE VARCHAR2(10),
    STORENUM NUMBER
);
CREATE TABLE CLOTHER_SALE(
    CTYPE VARCHAR2(10),
    SALENUM NUMBER
);
INSERT INTO CLOTHER_STORE VALUES('T-SHIRT',600);
INSERT INTO CLOTHER_STORE VALUES('COAT',700);
INSERT INTO CLOTHER_SALE VALUES('T-SHIRT',140);
INSERT INTO CLOTHER_SALE VALUES('T-SHIRT',165);
INSERT INTO CLOTHER_SALE VALUES('COAT',90);
INSERT INTO CLOTHER_SALE VALUES('COAT',78);
SELECT CTYPE,STORENUM-SNUM AS KCSY FROM
CLOTHER_STORE LEFT JOIN
(SELECT CTYPE,SUM(SALENUM) SNUM FROM CLOTHER_SALE GROUP BY CTYPE)
USING(CTYPE);

-- 切換到scott用戶
SELECT * FROM EMP;
SELECT * FROM DEPT;

-----------------------------華麗分割線-----------------------------------

以下是SQL練習內容

--1． 列出至少有一個員工的所有部門。

SELECT DISTINCT DEPTNO,DNAME FROM
EMP LEFT JOIN DEPT USING(DEPTNO);

--2． 列出薪金比"SMITH"多的所有員工。

SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');

--3． 列出所有員工的姓名及其直接上級的姓名。

SELECT E.ENAME 員工姓名,B.ENAME 上級姓名 FROM
EMP E LEFT JOIN EMP B ON E.MGR = B.EMPNO;

--4． 列出受雇日期早于其直接上級的所有員工。

SELECT E.ENAME 員工姓名,E.HIREDATE 員工受雇日期,B.ENAME 上級姓名,B.HIREDATE 上級受雇日期
FROM EMP E LEFT JOIN EMP B ON E.MGR = B.EMPNO
WHERE E.HIREDATE < B.HIREDATE;

--5． 列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門。

SELECT DNAME,EMP.* FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

--6． 列出所有"CLERK"（辦事員）的姓名及其部門名稱。

SELECT ENAME,DNAME FROM EMP LEFT JOIN DEPT USING(DEPTNO) WHERE JOB = 'CLERK';

--7． 列出最低薪金大于1500的各種工作。

SELECT JOB FROM EMP GROUP BY JOB HAVING MIN(SAL) > 1500;

--8． 列出在部門"SALES"（銷售部）工作的員工的姓名，假定不知道銷售部的部門編號。

SELECT ENAME FROM EMP LEFT JOIN DEPT USING(DEPTNO) WHERE DNAME = 'SALES';

--9． 列出薪金高于公司平均薪金的所有員工。

SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);

--10．列出與"SCOTT"從事相同工作的所有員工。

SELECT * FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'SCOTT') AND ENAME <> 'SCOTT';

--11．列出薪金等于部門30中員工的薪金的所有員工的姓名和薪金。

SELECT ENAME,SAL FROM EMP WHERE SAL = ANY(SELECT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO <> 30;

--12．列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金。

SELECT ENAME,SAL FROM EMP WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO

<> 30;

--13．列出在每個部門工作的員工數量、平均工資和平均服務期限。

SELECT DEPTNO,COUNT(EMPNO) 員工數量,ROUND(AVG(SAL),2) 平均工資,
       ROUND(AVG(MONTHS_BETWEEN(CURRENT_DATE,HIREDATE))/12,2) 平均服務年限
FROM EMP RIGHT JOIN DEPT USING(DEPTNO)
GROUP BY DEPTNO;

--14．列出所有員工的姓名、部門名稱和工資。

SELECT ENAME,DNAME,SAL FROM EMP LEFT JOIN DEPT USING(DEPTNO);

--15．列出所有部門的詳細信息和部門人數。

SELECT DEPT.DEPTNO,DNAME,LOC,COUNT(EMPNO) FROM
DEPT LEFT JOIN EMP ON EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DEPT.DEPTNO,DNAME,LOC;

--16．列出各種工作的最低工資。

SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB;

--17．列出各個部門的MANAGER（經理）的最低薪金。

SELECT MIN(SAL) FROM EMP WHERE JOB = 'MANAGER';

--18．列出所有員工的年工資,按年薪從低到高排序。

SELECT ENAME,SAL*12+NVL(COMM,0) 年薪 FROM EMP ORDER BY 年薪;

--19．列出經理人的名字。

SELECT ENAME FROM EMP WHERE JOB = 'MANAGER' OR JOB = 'PRESIDENT';

--20．不用組函數，求出薪水的最大值。

SELECT SAL FROM (SELECT SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM = 1;

--21．列出薪資第2高到第8高的員工。

SELECT SAL FROM(
SELECT ROWNUM RN,SAL FROM
(SELECT SAL FROM EMP ORDER BY SAL DESC))
WHERE RN >= 2 AND RN <= 8;

-- 切換回普通用戶

-- union, INTERSECT,MINUS
CREATE TABLE A1 (
    V1 NUMBER,
    V2 VARCHAR2(10)
);
CREATE TABLE A2 (
    V3 NUMBER,
    V4 VARCHAR2(10),
    V5 VARCHAR2(10)
);
INSERT INTO A1 VALUES(10,'A001');
INSERT INTO A1 VALUES(11,'A002');
INSERT INTO A2 VALUES(10,'A001','HAHA');
INSERT INTO A2 VALUES(12,'A003','HEIHEI');
INSERT INTO A2 VALUES(13,'A004','HOHO');
SELECT * FROM A1 UNION (SELECT V3，V4 FROM A2);
SELECT * FROM A1 UNION ALL (SELECT V3，V4 FROM A2);
SELECT * FROM A1 INTERSECT (SELECT V3，V4 FROM A2);
SELECT * FROM A1 MINUS (SELECT V3，V4 FROM A2);

-- 帶鎖查詢

UPDATE CLOTHER_STORE SET STORENUM = 600 WHERE CTYPE = 'T-SHIRT';
SELECT * FROM CLOTHER_STORE FOR UPDATE WAIT 5;
UPDATE CLOTHER_STORE SET STORENUM = STORENUM - 400 WHERE CTYPE = 'T-SHIRT';
-- DCL 之 COMMIT
SELECT * FROM STUDENT;
DELETE FROM STUDENT WHERE SNO = 'S011';
COMMIT;
-- DCL 之 ROLLBACK 和 SAVEPOINT
SELECT * FROM CLOTHER_SALE;
UPDATE CLOTHER_SALE SET SALENUM = SALENUM + 10;
ROLLBACK;
UPDATE CLOTHER_SALE SET SALENUM = SALENUM + 10;
SAVEPOINT CPD;
UPDATE CLOTHER_SALE SET SALENUM = SALENUM + 10;
ROLLBACK TO SAVEPOINT CPD;
-- DCL 之 GRANT
GRANT CREATE VIEW TO LUYY124;
GRANT UPDATE ANY TABLE TO LUYY124;
-- DDL 之 REVOKE
REVOKE CREATE VIEW FROM LUYY124;
-- CREATE VIEW
CREATE OR REPLACE VIEW STUDENT_VIEW AS
SELECT * FROM (STUDENT LEFT JOIN SCORE USING(SNO)) LEFT JOIN COURSE USING(CNO);
SELECT * FROM STUDENT_VIEW;
SELECT SNAME,CNAME FROM STUDENT_VIEW WHERE GRADE < 60;
SELECT * FROM SCORE;
UPDATE SCORE SET GRADE = GRADE + 1 WHERE SNO = 'S001' AND CNO = 'C001';
CREATE OR REPLACE VIEW STUDENT_VIEW AS SELECT * FROM SCORE;
UPDATE STUDENT_VIEW SET GRADE = GRADE - 1 WHERE SNO = 'S001' AND CNO = 'C001';
-- CREATE INDEX
CREATE INDEX SNAME_INDEX ON STUDENT(SNAME ASC) ;
SELECT * FROM STUDENT WHERE SNAME = 'LILI';
DROP INDEX SNAME_INDEX;