表操作
?
例
1?
對于表的教學管理數據庫中的表
STUDENTS
，可以定義如下：
??
CREATE? TABLE? STUDENTS
?? (SNO????? NUMERIC (6, 0) NOT NULL
?? SNAME??? CHAR (8) NOT NULL
?? AGE????? NUMERIC(3,0)
?? SEX????? CHAR(2)
?? BPLACE? CHAR(20)
?? PRIMARY KEY(SNO))
例
2?
對于表的教學管理數據庫中的表
ENROLLS
，可以定義如下：
?????
??CREATE? TABLE? ENROLLS
??????? (SNO????? NUMERIC(6,0)? NOT NULL
??????? CNO???? CHAR(4)? NOT NULL
??????? GRADE?? INT
??????? PRIMARY KEY(SNO,CNO)
??????? FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO)
??????? FOREIGN KEY(CNO) REFERENCES COURSES(CNO)
??????? CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100)))
例
3?
根據表的
STUDENTS
表，建立一個只包含學號、姓名、年齡的女學生表。
???????
CREATE TABLE GIRL
??????? AS SELECT SNO, SNAME, AGE
??????? FROM STUDENTS
??????? WHERE SEX='
女
';
?
例
4?
刪除教師表
TEACHER
。
??????? DROP? TABLE? TEACHER
例
5?
在教師表中增加住址列。
?????? ALTER TABLE TEACHERS
?????? ADD (ADDR CHAR(50))
例
6?
把
STUDENTS
表中的
BPLACE
列刪除，并且把引用
BPLACE
列的所有視圖和約束也一起刪除。
??????? ALTER TABLE STUDENTS
??????? DROP BPLACE CASCADE
例
7?
補充定義
ENROLLS
表的主關鍵字。
?????
?ALTER TABLE ENROLLS
?????? ADD PRIMARY KEY (SNO,CNO)
；
?
視圖操作（虛表）
?
例
9?
建立一個只包括教師號、姓名和年齡的視圖
FACULTY
。
(
在視圖定義中不能包含
ORDER BY
子句
)
??????? CREATE VIEW FACULTY
??????? AS SELECT TNO, TNAME, AGE
??????? FROM TEACHERS
例
10?
從學生表、課程表和選課表中產生一個視圖
GRADE_TABLE
，
它包括學生姓名、課程名和成績。
??????? CREATE VIEW GRADE_TABLE
??????? AS SELECT? SNAME,CNAME,GRADE
??????? FROM? STUDENTS,COURSES,ENROLLS
??????? WHERE? STUDENTS.SNO
＝
ENROLLS.SNO AND
??????? COURSES.CNO=ENROLLS.CNO
例
11?
刪除視圖
GRADE_TABLE
??????? DROP VIEW GRADE_TABLE RESTRICT
?
索引操作
?
例
12?
在學生表中按學號建立索引。
??????? CREATE ?UNIQUE ?INDEX ?ST
??????? ON STUDENTS (SNO,ASC)
例
13?
刪除按學號所建立的索引。
??????? DROP INDEX ST
?
數據庫模式操作
?
例
14?
創建一個簡易教學數據庫的數據庫模式
? TEACHING_DB
，屬主為
ZHANG
。
??????? CREATE SCHEMA TEACHING_DB ?AUTHRIZATION ?ZHANG
例
15?
刪除簡易教學數據庫模式
TEACHING_DB
。（（
1
）選用
CASCADE
，即當刪除數據庫模式時，則本數據庫模式和其下屬的基本表、視圖、索引等全部被刪除。（
2
）選用
RESTRICT
，即本數據庫模式下屬的基本表、視圖、索引等事先已清除，才能刪除本數據庫模式，否則拒絕刪除。）
??????? DROP SCHEMA TEACHING_DB CASCADE
單表操作
?
例
16?
找出
3
個學分的課程號和課程名。
???????? SELECT CNO, CNAME
???????? FROM
COURSES
???????? WHERE
CREDIT
＝
3
例
17?
查詢年齡大于
22
歲的學生情況。
???????? SELECT? *
???????? FROM?? STUDENTS
???????? WHERE? AGE
＞
22
例
18??
找出籍貫為河北的男生的姓名和年齡。
???????? SELECT SNAME, AGE
???????? FROM
STUDENTS
???????? WHERE
BPLACE
＝
'
河北
' ?AND? SEX
＝
'
男
'
例
19?
找出年齡在
20
～
23
歲之間的學生的學號、姓名和年齡，并按年齡升序排序。
(ASC
（升序）或
DESC
（降序）聲明排序的方式，缺省為升序。
)
???????? SELECT SNO, SNAME, AGE
???????? FROM?? STUDENTS
???????? WHERE? AGE BETWEEN 20 AND 23
???????? ORDER? BY ?AGE
例
20?
找出年齡小于
23
歲、籍貫是湖南或湖北的學生的姓名和性別。（條件比較運算符＝、＜
和邏輯運算符
AND
（與），此外還可以使用的運算符有：＞（大于）、＞＝（大于等于）、＜＝（小于等于）、＜＞（不等于）、
NOT
（非）、
OR
（或）等。
謂詞
LIKE
只能與字符串聯用，常常是
“
＜列名＞
? LIKE? pattern”
的格式。特殊字符
“_”
和
“%”
作為通配符。
謂詞
IN
表示指定的屬性應與后面的集合（括號中的值集或某個查詢子句的結果）中的某個值相匹配，實際上是一系列的
OR
（或）的縮寫。謂詞
NOT IN
表示指定的屬性不與后面的集合中的某個值相匹配。
謂詞
BETWEEN
是
“
包含于
…
之中
”
的意思。）
??????? SELECT SNAME, SEX
??????? FROM?? STUDENTS
??????? WHERE? AGE
＜
23? AND? BPLACE? LIKE'
湖％
'
???????
或
??????? SELECT SNAME, SEX
??????? FROM?? STUDENTS
??????? WHERE? AGE
＜
23? AND? BPLACE? IN?
（
'
湖南
'
，
'
湖北
'
）
例
22?
找出學生表中籍貫是空值的學生的姓名和性別。（在
SQL
中不能使用條件：＜列名＞＝
NULL
。在
SQL
中只有一個特殊的查詢條件允許查詢
NULL
值：）
?????? SELECT SNAME, SEX
?????? FROM?? STUDENTS
?????? WHERE? BPLACE IS NULL
?
多表操作
?
例
23?
找出成績為
95
分的學生的姓名。（子查詢）
??????? SELECT SNAME
??????? FROM
STUDENTS
??????? WHERE
SNO
＝
????????????? (SELECT SNO
?????????????? FROM?? ENROLLS
?????????????? WHERE? GRADE
＝
95)
例
24?
找出成績在
90
分以上的學生的姓名。
?
??????SELECT? SNAME
??????? FROM?? STUDENTS
??????? WHERE? SNO IN
??????????????? (SELECT SNO
??????????????? FROM ENROLLS
??????????????? WHERE GRADE
＞
90)
???????
或
??????? SELECT? SNAME
??????? FROM?? STUDENTS
??????? WHERE? SNO
＝
ANY
??????????????? (SELECT SNO
??????????????? FROM ENROLLS
??????????????? WHERE GRADE
＞
90)
例
25?
查詢全部學生的學生名和所學課程號及成績。（連接查詢）
??????? SELECT? SNAME, CNO, GRADE
??????? FROM?? STUDENTS, ENROLLS
??????? WHERE? STUDENTS.SNO
＝
ENROLLS.SNO
例
26?
找出籍貫為山西或河北，成績為
90
分以上的學生的姓名、籍貫和成績。（當構造多表連接查詢命令時，必須遵循兩條規則。第一，連接條件數正好比表數少
1
（若有三個表，就有兩個連接條件
)
；第二，若一個表中的主關鍵字是由多個列組成，則對此主關鍵字中的每一個列都要有一個連接條件（也有少數例外情況））
??????? SELECT? SNAME, BPLACE, GRADE
??????? FROM?? STUDENTS, ENROLLS
??????? WHERE? BPLACE IN (‘
山西
’
，
‘
河北
’)? AND? GRADE
＞＝
90? AND
STUDENTS.SNO=ENROLLS.SNO
例
28?
查出課程成績在
80
分以上的女學生的姓名、課程名和成績。（
FROM
子句中的子查詢）
??????? SELECT? SNAME,CNAME, GRADE
??????? FROM?? (SELECT SNAME, CNAME , GRADE
??????????????????????? FROM? STUDENTS, ENROLLS,COURSES
??????????????????????? WHERE? SEX
＝
'
女
')
??????? AS TEMP (SNAME, CNAME,GRADE)
??????? WHERE? GRADE
＞
80
?
?
?
?
表達式與函數的使用
?
例
29?
查詢各課程的學時數。（算術表達式由算術運算符＋、－、
*
、／與列名或數值常量所組成。）
??????? SELECT? CNAME,COURSE_TIME
＝
CREDIT*16
??????? FROM?? COURSES
例
30?
找出教師的最小年齡。（內部函數：
SQL
標準中只使用
COUNT
、
SUM
、
AVG
、
MAX
、
MIN
函數，稱之為聚集函數（
Set Function
）。
COUNT
函數的結果是該列統計值的總數目，
SUM
函數求該列統計值之和，
AVG
函數求該列統計值之平均值，
MAX
函數求該列最大值，
MIN
函數求該列最小值。）
??????? SELECT? MIN(AGE)
??????? FROM?? TEACHERS
例
31?
統計年齡小于等于
22
歲的學生人數。（統計）
??????? SELECT? COUNT(*)
??????? FROM?? STUDENTS
??????? WHERE? AGE <
＝
22
例
32?
找出學生的平均成績和所學課程門數。
??????? SELECT? SNO, AVG(GRADE), COURSES
＝
COUNT(*)
??????? FROM?? ENROLLS
??????? GROUP? BY SNO
?
例
34?
找出年齡超過平均年齡的學生姓名。
SELECT SNAME
FROM STUDENTS
WHERE AGE
＞
????? (SELECT? AVG(AGE)
??????? FROM?? STUDENTS)
?
例
35?
找出各課程的平均成績，按課程號分組，且只選擇學生超過
3
人的課程的成績。（
GROUP BY
與
HAVING
??????? GROUP BY
子句把一個表按某一指定列（或一些列）上的值相等的原則分組，然后再對每組數據進行規定的操作。
??????? GROUP BY
子句總是跟在
WHERE
子句后面，當
WHERE
子句缺省時，它跟在
FROM
子句后面。
??????? HAVING
子句常用于在計算出聚集之后對行的查詢進行控制。）
???????? SELECT CNO, AVG(GRADE), STUDENTS
＝
COUNT(*)
???????? FROM ENROLLS
???????? GROUP BY CNO
???????? HAVING COUNT(*) >= 3
?
相關子查詢
?
例
37?
查詢沒有選任何課程的學生的學號和姓名。（當一個子查詢涉及到一個來自外部查詢的列時，稱為相關子查詢（
Correlated Subquery)
。相關子查詢要用到存在測試謂詞
EXISTS
和
NOT EXISTS
，以及
ALL
、
ANY
（
SOME
）等。）
??????? SELECT SNO, SNAME
??????? FROM?? STUDENTS
??????? WHERE? NOT EXISTS
????????????? (SELECT *
????????????? FROM ENROLLS
????????????? WHERE ENROLLS.SNO=STUDENTS.SNO)
例
38??
查詢哪些課程只有男生選讀。
??????? SELECT DISTINCT CNAME
??????? FROM?? COURSES C
??????? WHERE '
男
'
＝
ALL
?????????????? (SELECT SEX
???
????????????FROM? ENROLLS
，
STUDENTS
??????????????? WHERE ENROLLS.SNO=STUDENTS.SNO AND
????????????????????? ENROLLS.CNO=C.CNO)
例
39?
要求給出一張學生、籍貫列表，該表中的學生的籍貫省份，也是其他一些學生的籍貫省份。
??????? SELECT? SNAME, BPLACE
??????? FROM?? STUDENTS A
??????? WHERE? EXISTS
???
???????????(SELECT *
???????????????? FROM STUDENTS B
???????????????? WHERE A.BPLACE=B.BPLACE AND
?????????????????????? A.SNO < > B.SNO)
?
?
例
40?
找出選修了全部課程的學生的姓名。
???????
本查詢可以改為：查詢這樣一些學生，沒有一門課程是他不選修的。
??????? SELECT? SNAME
??????? FROM?? STUDENTS
??????
?WHERE? NOT EXISTS
?????????????? (SELECT *
??????????????? FROM COURSES
??????????????? WHERE? NOT EXISTS
??????????????????????? (SELECT *
???????????????????????? FROM ENROLLS
???????????????????????? WHERE ENROLLS.SNO
＝
STUDENTS.SNO
???????????????????
?????????AND ENROLLS.CNO
＝
COURSES.CNO))
關系代數運算
?
例
41?
設有某商場工作人員的兩張表：營業員表
SP_SUBORD
和營銷經理表
SP_MGR
，其關系數據模式如下：
??????? SP_SUBORD (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)
??????? SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)
???????
其中，屬性
SALPERS_ID
為工作人員的編號
, SALPERS_NAME
為工作人員的姓名
, MANAGER_ID
為所在部門經理的編號
, OFFICE
為工作地點。
若查詢全部商場工作人員，可以用下面的
SQL
語句：
??????? (SELECT * FROM SP_SUBORD)
???????
UNION
??????? (SELECT * FROM SP_MGR)
???????
或等價地用下面的
SQL
語句：
??????? SELECT *
??????? FROM (TABLE SP_SUBORD UNION TABLE SP_MGR)
（
2
）
INTERSECT
???????? (SELECT * FROM SP_SUBORD)
???????? INTERSECT
???????? (SELECT * FROM SP_MGR)
???????
或等價地用下面的
SQL
語句：
??????? SELECT *
??????? FROM (TABLE SP_SUBORD INTERSECT TABLE SP_MGR)
???????
或用帶
ALL
的
SQL
語句：
??????? (SELECT * FROM SP_SUBORD)
?
??????INTERSECT ALL
??????? (SELECT * FROM SP_MGR)
???????
或
??????? SELECT *
??????? FROM (TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR)
（
3
）
EXCEPT
??????? (SELECT * FROM SP_MGR)
??????? EXCEPT
??????? (SELECT * FROM SP_SUBORD)
???????
或等價地用下面的
SQL
語句：
?????
??SELECT *
??????? FROM (TABLE SP_MGR EXCEPT TABLE SP_ SUBORD)
???????
或用帶
ALL
的
SQL
語句：
??????? (SELECT * FROM SP_MGR)
??????? EXCEPT? ALL
??????? (SELECT * FROM SP_SUBORD)
例
42?
查詢籍貫為四川、課程成績在
80
分以上的學生信息及其成績。（自然連接）
??????? (SELECT * FROM STUDENTS
???????? WHERE BPLACE=‘
四川
’)
??????? NATURAL JOIN
??????? (SELECT * FROM ENROLLS
???????? WHERE GRADE >=80)
例3.43?????????
列出全部教師的姓名及其任課的課程號、班級。
（外連接與外部并外連接允許在結果表中保留非匹配元組，空缺部分填以
NULL
。外連接的作用是在做連接操作時避免丟失信息。
???????
外連接有
3
類：
（
1
）左外連接（
Left Outer Join
）。連接運算謂詞為
LEFT [OUTER] JOIN
，其結果表中保留左關系的所有元組。
（
2
）右外連接（
Right Outer Join
）。連接運算謂詞為
RIGHT [OUTER] JOIN
，其結果表中保留右關系的所有元組。
（
3
）全外連接（
Full Outer Join
）。連接運算謂詞為
FULL [OUTER] JOIN
，其結果表中保留左右兩關系的所有元組。）
????????? SELECT TNAME, CNO, CLASS
????????? FROM TEACHERS LEFT OUTER JOIN TEACHING USING (TNO)
?
SQL
的數據操縱
?
例
44?
把教師李映雪的記錄加入到教師表
TEACHERS
中。（插入）
??????? INSERT INTO TEACHERS
??????? VALUES(1476
，
'
李映雪
'
，
44
，
'
副教授
')
例
45?
成績優秀的學生將留下當教師。
??????? INSERT INTO TEACHERS (TNO
，
TNAME)
??????? SELECT DISTINCT SNO
，
SNAME
??????? FROM?? STUDENTS
，
ENROLLS
??????? WHERE STUDENTS.SNO
＝
ENROLLS.SNO AND GRADE
＞＝
90
例
47?
把所有學生的年齡增加一歲。（修改）
??????? UPDATE STUDENTS
??????? SET AGE
＝
AGE+1
例
48?
學生張春明在數據庫課考試中作弊，該課成績應作零分計。
??????? UPDATE ENROLLS
??????? SET GRADE
＝
0
??????? WHERE CNO
＝
'C1' AND
???????????? '
張春明
'
＝
???????????? (SELECT SNAME
???????????? FROM STUDENTS
???????????? WHERE STUDENTS.SNO=ENROLLS.SNO)
例
49?
從教師表中刪除年齡已到
60
歲的退休教師的數據。（刪除）
??????? DELETE FROM TEACHERS
??????? WHERE? AGE
＞＝
60
?
SQL
的數據控制
?
例
50?
授予
LILI
有對表
STUDENTS
的查詢權。（表／視圖特權的授予
???????
一個
SQL
特權允許一個被授權者在給定的數據庫對象上進行特定的操作。授權操作的數據庫對象包括：表
/
視圖、列、域等。授權的操作包括：
INSERT
、
UPDATE
、
DELETE
、
SELECT
、
REFERENCES
、
TRIGGER
、
UNDER
、
USAGE
、
EXECUTE
等。其中
INSERT
、
UPDATE
、
DELETE
、
SELECT
、
REFERENCES
、
TRIGGER
有對表做相應操作的權限，故稱為表特權。）
??????? GRANT SELECT ON STUDENTS
??????? TO LILI
??????? WITH GRANT OPTION
例
51?
取消
LILI
的存取
STUDENTS
表的特權。
????
???REVOKE ALL
??????? ON STUDENTS
??????? FROM LILI CASCADE
?
不斷補充中：
1.? 模糊查找:
它判斷列值是否與指定的字符串格式相匹配。可用于char、varchar、text、ntext、datetime和smalldatetime等類型查詢。
可使用以下通配字符：
百分號%：可匹配任意類型和長度的字符，如果是中文，請使用兩個百分號即%%。
下劃線_：匹配單個任意字符，它常用來限制表達式的字符長度。
方括號[]：指定一個字符、字符串或范圍，要求所匹配對象為它們中的任一個。[^]：其取值也[] 相同，但它要求所匹配對象為指定字符以外的任一個字符。
例如：
限制以Publishing結尾，使用LIKE '%Publishing'
限制以A開頭：LIKE '[A]%'
限制以A開頭外：LIKE '[^A]%'
2.更改表格
???????? ALTER?TABLE?table_name?
????????ADD?COLUMN?column_name?DATATYPE?
????????說明：增加一個欄位（沒有刪除某個欄位的語法。)
????????ALTER?TABLE?table_name
????????ADD?PRIMARY?KEY?(column_name)
????????說明：更改表得的定義把某個欄位設為主鍵。
????????ALTER?TABLE?table_name
????????DROP?PRIMARY?KEY?(column_name)
????????說明：把主鍵的定義刪除。
3.group by
在select 語句中可以使用group by 子句將行劃分成較小的組，然后，使用聚組函數返回每一個組的匯總信息，另外，可以使用having子句限制返回的結果集。group by 子句可以將查詢結果分組，并返回行的匯總信息Oracle 按照group by 子句中指定的表達式的值分組查詢結果。
在帶有group by 子句的查詢語句中，在select 列表中指定的列要么是group by 子句中指定的列，要么包含聚組函數
select max(sal),job emp group by job;
(注意max(sal),job的job并非一定要出現，但有意義)
查詢語句的select 和group by ,having 子句是聚組函數唯一出現的地方，在where 子句中不能使用聚組函數。
select deptno,sum(sal) from emp where sal>1200 group by deptno having sum(sal)>8500 order by deptno;
當在gropu by 子句中使用having 子句時，查詢結果中只返回滿足having條件的組。在一個sql語句中可以有where子句和having子句。having 與where 子句類似，均用于設置限定條件
where 子句的作用是在對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾數據，條件中不能包含聚組函數，使用where條件顯示特定的行。
having 子句的作用是篩選滿足條件的組，即在分組之后過濾數據，條件中經常包含聚組函數，使用having 條件顯示特定的組，也可以使用多個分組標準進行分組。
查詢每個部門的每種職位的雇員數
select deptno,job,count(*) from emp group by deptno,job;
4.外連接與內連接
有時候，即使在連接的表中沒有相應的行，用戶可能想從一張表中看數據，Oracle提供了外連接實現該功能。
內連接是指連接查詢只顯示完全滿足連接條件的記錄，即等值連接，外連接的查詢結果是內連接查詢結果的擴展。外連接不僅返回滿足連接條件的所有記錄而且也返回了一個表中那些在另一個表中沒有匹配行的記錄。外連接的操作符是“+”。“+”號放在連接條件中信息不完全的那一邊（即沒有相應行的那一邊）。運算符“+”影響NULL行的建立。建一行或多行NULL來匹配連接的表中信息完全的行。
外連接運算符“+”只能出現在where子句中表達式的一邊。
假如在多張表之間有多個連接條件，外連接運算符不能使用or,in邏輯運算符與其它條件組合。
假如emp表中deptno=10的ename為空值，dept表中deptno=20的loc為空值：
1.
select
ename,dept.deptno,loc
from
emp,dept
where
emp.depno(+)=dept.deptno;
如果在dept.deptno中有的數值在emp.deptno中沒有值，則在做外連接時，
結果中ename會產生一個空值。(emp.deptno=10)
2.
select
ename,dept.deptno,loc
from
emp,dept
where
emp.depno=dept.deptno(+);
如果在emp.deptno中有的數值在dept.deptno中沒有值，則在做外連接時，
結果中loc會產生一個空值。。(dept.deptno=20)
5.自連接
自連接是指同一張表的不同行間的連接。該連接不受其他表的影響。用自連接可以比較同一張表中不同行的某一列的值。因為自連接查詢僅涉及到某一張表與其自身的連接。所以在from子句中該表名出現兩次，分別用兩個不同的別名表示，兩個別名當作兩張不同的表進行處理，與其它的表連接一樣，別名之間也使用一個或多個相關的列連接。為了區分同一張表的不同行的列，在名前永別名加以限制。
select
worker.ename,
manager.ename manager
from
emp worker,
emp manager
where
work.mgr=manager.empno;
6.集合運算
基合運算符可以用于從多張表中選擇數據。
①UNION運算
用于求兩個結果集合的并集（兩個結果集合的所有記錄），并自動去掉重復行。
select ename,sal from account where sal>2000
union
select ename,sal from research where sal>2000
union
select ename,sal from sales where sal>2000;
注：ename,sal 是必須一致的。
②UNION ALL運算
用于求兩個結果集合的并集（兩個結果集中的所有記錄），并且不去掉重復行。
select ename,sal from account where sal>2000
union
select ename,sal from research where sal>2000
union
select ename,sal from sales where sal>2000;
③INTERSECT運算
intersect運算返回查詢結果中相同的部分。
各部門中有哪些相同的職位？
select Job from account
intersect
select Job from research
intersect
select Job from sales;
④MINUS運算
minus返回兩個結果集的差集。（在第一個結果集中存在的，而在第二個結果集中不存在的行。）
有那些職位是財務部中有，而在銷售部門中沒有？
select Job from account
minus
select Job from sales;