Database System Principle Database System Principle 1
SQL 3.1 SQL 3.2-3.3 3.4 3.5 3.6 Database System Principle 2
3.1 SQL SQL Structured Query Language SQL Database System Principle 3
SQL 3.1.1 SQL 3.1.2 SQL 3.1.3 SQL Database System Principle 4
SQL SQL/86 1986.10 SQL/89(FIPS 127-1) 120 1989 SQL/92 622 1992 SQL99 1700 1999 SQL2003 2003 ANSI (American National Standard Institute) ISO (International Organization for Standardization) Database System Principle 5
3.1 SQL 3.1.1 SQL 3.1.2 SQL 3.1.3 SQL Database System Principle 6
1. 3.1.2 SQL DDL DML DCL Database System Principle 7
2. SQL SQL Database System Principle 8
3. SQL Database System Principle 9
4. SQL SQL SQL C C++ Java Database System Principle 10
5. SQL 9 SQL 3.1 SQL SELECT CREATE DROP ALTER INSERT UPDATE DELETE GRANT REVOKE Database System Principle 11
3.1 SQL 3.1.1 SQL 3.1.2 SQL 3.1.3 SQL Database System Principle 12
SQL SQL SQL 1 2 1 2 3 4 1 2 Database System Principle 13
SQL SQL ( ) Database System Principle 14
SQL 3.1 SQL 3.2-3.3 3.4 3.5 3.6 Database System Principle 15
3.2 - - S-T : Student(Sno,Sname,Ssex,Sage,Sdept) Course(Cno,Cname,Cpno,Ccredit) SC(Sno,Cno,Grade) Database System Principle 16
Student Sno Sname Ssex Sage Sdept 200215121 200215122 200215123 200515125 20 19 18 19 CS CS MA IS Database System Principle 17
Course Cno Cname Cpno Ccredit 1 5 4 2 2 3 1 4 4 6 3 5 7 4 6 7 PASCAL 6 2 4 Database System Principle 18
SC Sno 200215121 200215121 200215121 200215122 200215122 Cno 1 2 3 2 3 Grade 92 85 88 90 80 Database System Principle 19
SQL 3.1 SQL 3.2-3.3 3.4 3.5 3.6 Database System Principle 20
3.3 SQL : 3.2 SQL CREATE SCHEMA DROP SCHEMA CREATE TABLE DROP TABLE ALTER TABLE CREATE VIEW DROP VIEW CREATE INDEX DROP INDEX Database System Principle 21
3.3 3.3.1 3.3.2 3.3.3 Database System Principle 22
3.3.1 [ 1] - S-T CREATE SCHEMA S-T AUTHORIZATION WANG; WANG S-T [ 2]CREATE SCHEMA AUTHORIZATION WANG < > WANG < > < > < > Database System Principle 23
CREATE SCHEMA CREATE TABLE CREATE VIEW GRANT CREATE SCHEMA < > AUTHORIZATION < >[< > < > < >] Database System Principle 24
[ 3] CREATE SCHEMA TEST AUTHORIZATION ZHANG CREATE TABLE TAB1(COL1 SMALLINT COL2 INT COL3 CHAR(20) COL4 NUMERIC(10 3) COL5 DECIMAL(5 2) ) ZHANG TEST TAB1 Database System Principle 25
DROP SCHEMA < > <CASCADE RESTRICT> CASCADE( ) RESTRICT( ) Database System Principle 26
[ 4] DROP SCHEMA ZHANG CASCADE ZHANG TAB1 Database System Principle 27
3.3 3.3.1 3.3.2 3.3.3 Database System Principle 28
3.3.2 CREATE TABLE < > < > < >[ < > ] [ < > < >[ < >] ] [ < > ] Database System Principle 29
Student [ 5] Student CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY /* */ Sname CHAR(20) UNIQUE /* Sname */ Ssex CHAR(2) Sage SMALLINT Sdept CHAR(20) ) Database System Principle 30
Course CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY Cname CHAR(40) Cpno CHAR(4) Ccredit SMALLINT FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); Cpno Course Cno Database System Principle 31
SC 7 CREATE TABLE SC (Sno CHAR(9) Cno CHAR(4) Grade SMALLINT PRIMARY KEY (Sno Cno) /* */ FOREIGN KEY (Sno) REFERENCES Student(Sno) /* Sno Student */ FOREIGN KEY (Cno) REFERENCES Course(Cno) /* Cno Course*/ ); Database System Principle 32
SQL Database System Principle 33
CHAR(n) VARCHAR(n) INT SMALLINT NUMERIC(p d) REAL Double Precision FLOAT(n) DATE TIME n n INTEGER p d n YYYY-MM-DD HH:MM:SS Database System Principle 34
Create table S-T.Student... ; /* S-T*/ Create table S-T.Cource... ; Create table S-T.SC... ; Database System Principle 35
RDBMS SHOW search_path; $user PUBLIC Database System Principle 36
DBA SET search_path TO S-T PUBLIC Create table Student... ; S-T.Student RDBMS S-T Student Database System Principle 37
ALTER TABLE < > [ ADD < > < > [ ] ] [ DROP < > ] [ ALTER COLUMN< > < > ] Database System Principle 38
[ 8] Student ALTER TABLE Student ADD S_entrance DATE [ 9] ALTER TABLE Student ALTER COLUMN Sage INT [ 10] ALTER TABLE Course ADD UNIQUE(Cname); Database System Principle 39
DROP TABLE < > RESTRICT CASCADE RESTRICT ( ) CASCADE Database System Principle 40
( ) [ 11] Student DROP TABLE Student CASCADE ; Database System Principle 41
DROP TABLE SQL99 3 RDBMS SQL99 R C Kingbase ES R C ORACLE 9i C MS SQL SERVER 2000 1. 2. 3. DEFAULT PRIMARY KEY CHECK NOT NULL 4. Foreign Key 5. TRIGGER 6. R RESTRICT, C CASCADE ' ' ' ' Database System Principle 42
3.3 3.3.1 3.3.2 3.3.3 Database System Principle 43
3.3.3 DBA DBMS PRIMARY KEY UNIQUE DBMS DBMS Database System Principle 44
RDBMS B+ HASH B+ HASH B+ HASH RDBMS CREATE INDEX Database System Principle 45
CREATE [UNIQUE] [CLUSTER] INDEX < > ON < >(< >[< >][,< >[< >] ] ) UNIQUE CLUSTER ASC/DESC ASC Database System Principle 46
[ 13] CREATE CLUSTER INDEX Stusname ON Student(Sname) Student Sname Database System Principle 47
[ 14] - Student Course SC CREATE UNIQUE INDEX Stusno ON Student(Sno) CREATE UNIQUE INDEX Coucno ON Course(Cno) CREATE UNIQUE INDEX SCno ON SC(Sno ASC Cno DESC) Student Course SC Database System Principle 48
DROP INDEX < > [ 15] Student Stusname DROP INDEX Stusname Database System Principle 49
Database System Principle 50
3.4 (*) DBMS create database drop database database use Database System Principle 51
SQL Server Database System Principle 52
Database System Principle 53
SQL 3.1 SQL 3.2-3.3 3.4 3.5 3.6 Database System Principle 54
3.4 3.4.1 3.4.2 3.4.3 3.4.4 Database System Principle 55
SELECT 3.4.1 SELECT SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC DESC ]] UNION Database System Principle 56
3.4.2 1. SQL 2. SELECT 3. 4. FROM 5. WHERE 6. 7. 8. 9. 10. Database System Principle 57
3.4.2 1. SQL SELECT A 1, A 2,, A n FROM R 1,R 2,, R m WHERE P A1, A2,, An (σ p (R 1 R 2 R m )) Database System Principle 58
3.4.2 Ex: SELECT pname FROM Prof pname (Prof) Database System Principle 59
2. SELECT 3.4.2 * Ex 1: SELECT p#, pname, age FROM Prof Ex 2: SELECT * FROM Prof Ex 3: SELECT * FROM Dept Database System Principle 60
3.4.2 2. SELECT * Ex 4 : SELECT pname sal * 0.9 FROM Prof Ex 5: SELECT AVG(sal) FROM Prof Database System Principle 61
3. 3.4.2 ALL DISTINCT Ex: SELECT DISTINCT sno FROM SC Database System Principle 62
3.4.2 4. FROM FROM Ex: SELECT ALL pname, sal, dname FROM Prof, Dept WHERE Prof.DNO = Dept.DNO Database System Principle 63
3.4.2 Agents(aid, aname,city,percent) Customers(cid, cname,discount) Prouducts(pid, pname,city,quantity,price) Orders(ordno, cid,aid,pid,qty) Ex: cname, aname (( cid, cname, aid (Customers) Orders ) Agents) cname, aname (σ Customers.cid = orders.cid Orders.aid = Agents.aid ((Customers Orders) Agents) ) Database System Principle 64
3.4.2 SELECT cname,aname (σ Customers.cid = orders.cid Orders.aid = Agents.aid ((Customers Orders) Agents) ) SELECT DISTINCT Customers.cname, Agents.aname FROM Customers, Orders, Agents WHERE Customers.cid = Orders.cid AND Orders.aid = Agents.aid Database System Principle 65
3.4.2 FROM Ex: SELECT Prof. pno, Prof.pname FROM Prof, PC, Course WHERE Prof.pno = PC.pno AND PC.Cno = Course.cno AND Course.cname = Database System Principle 66
5. WHERE 3.4.2 < < = > >= = <> AND OR NOT BETWEEN IN LIKE IS NULL Database System Principle 67
3.4.2 5. WHERE Ex 1: SELECT pname, sal, dname FROM Prof, Dept WHERE sal < 2000 AND Prof.dno = Dept.dno Ex 2: SELECT FROM pname Prof WHERE sal BETWEEN 1000 AND 2000 Database System Principle 68
6. 3.4.2 old_name AS new_name SELECT FROM AS Ex1 : SELECT pname sal 0.05 AS tax FROM sal * 0.95 AS incoming Prof Database System Principle 69
Ex 2: DEPT(D#, DNAME, DEAN) PROF(P#, PNAME, AGE, D#, SAL) S(S#, SNAME, SEX, AGE, D#) COURSE(C#, CN, PC#, CREDIT) SC(S#, C#, SCORE) TEACH(P#, C#) Database System Principle 70
Database System Principle 71
Database System Principle 72
Database System Principle 73
3.4.2 7. [NOT] LIKE < > [ESCAPE < > ] % _ ESCAPE ESCAPE \ \ \% % \_ _ Database System Principle 74
3.4.2 7. Ex 1: SELECT * FROM Prof WHERE pname LIKE % Ex 2: SELECT sname FROM S WHERE sname LIKE Database System Principle 75
3.4.2 7. Ex 3: _ SELECT * FROM Prof WHERE pname LIKE % _d \ ESCAPE \ Database System Principle 76
8. 3.4.2 ORDER BY [ASC DESC] Ex 1: SELECT FROM dname, pname Prof, Dept WHERE Prof.d# = Dept.d# ORDER BY dname ASC pname DESC Database System Principle 77
Database System Principle 78
3.4.2 9. COUNT ([DISTINCT] A) / COUNT([DISTINCT]*) SUM ([DISTINCT] A) AVG ([DISTINCT] A) MAX (A) MIN (A) Database System Principle 79
3.4.2 Ex 1: SELECT AVG(sal), MIN(sal), MAX(sal) FROM Prof Database System Principle 80
9. 3.4.2 GROUP BY [HAVING ] GROUP BY + Group-list HAVING + Group-qualification Database System Principle 81
9. SC(S#, C#, SCORE) Ex 3: SELECT S#, AVG(score) FROM SC GROUP BY S# 90 85 92 S# C# s1 c1 84 s1 c2 90 s1 c3 96 s2 c1 80 s2 c2 90 s3 c2 96 s3 c3 score 88 Database System Principle 82
9. SC(S#, C#, SCORE) Ex 4: SELECT C#, AVG(score) FROM SC GROUP BY C# S# C# score s1 s1 c1 c2 84 90 82 s1 c3 96 s2 c1 80 92 s2 c2 90 s3 c2 96 92 s3 c3 88 Database System Principle 83
9. Having Group By Database System Principle 84
90 90 Select s#, count(s#) From SC Where score >=90 Group By s# Having count(s#) >= 2 Database System Principle 85
3.4.2 9. Ex 5: SELECT d#, MAX(sal), MIN(sal), AVG(sal) FROM Prof GROUP BY d# Database System Principle 86
3.4.2 10. IS [NOT] NULL = NULL - Ex 1: SELECT pname FROM Prof WHERE age IS NULL Database System Principle 87
is [not] null null null null false SQL- 92 unknown null count(*) null Database System Principle 88
3.4.2 SC S# s1 s1 C# c1 c2 score 80 90 10. s1 s2 c3 c1 95 85 s2 c2 s3 c2 SELECT SUM (score ) FROM SC 350 SC S# s1 C# c1 score 80 SELECT COUNT(*) FROM SC 6 s1 s1 c2 c3 90 95 SELECT COUNT(score) FROM SC 4 s2 s2 s3 c1 c2 c2 85 null null Database System Principle 89
3.4.3 1. 2. (IN) 3. ( ) 4. (EXISTS) Database System Principle 90
3.4.3 ( ) 1. Select-From-Where SQL Order By Order By Database System Principle 91
2. (IN) 3.4.3 ( ) [NOT] IN select * from S where SNAME in, Database System Principle 92
3.4.3 ( ) 001 select from where SNO SNAME S SNO in (select SNO from SC where CNO = 001 Database System Principle 93
3.4.3 ( ) 001 002 select SNO from SC where SC.CNO = 001 and SNO in select SNO from SC where CNO = 002 Database System Principle 94
3.4.3 ( ) Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) Database System Principle 95
3.4.3 ( ) Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) Sc(sno, cno, grade) SELECT Sdept FROM Student WHERE Sname= Sdept IS IS SELECT Sno Sname Sdept FROM Student WHERE Sdept='IS' Sno Sname 95001 95004 Database System Principle 96
Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) SELECT Sno Sname FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname ) Database System Principle 97
Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) : Course Cno SC Cno Cno Sno Student Sno Sno Sno Sname Database System Principle 98
Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) SQL SELECT Sno Sname FROM Student WHERE Sno IN (SELECT SnO FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname )) Database System Principle 99
Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) SELECT Student.Sno Sname FROM Student SC Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname = Database System Principle 100
3.4.3 ( ) 3. > < > <! <> Database System Principle 101
3.4.3 ( ) 3. Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) Ex 2: SELECT sname FROM Student WHERE Sdept = ( SELECT sdept FROM Student WHERE sname = ) Database System Principle 102
3.4.3 ( ) 3. ANY ALL θ ANY θ θ ALL θ Database System Principle 103
3.4.3 ( ) 3. Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) : IS SELECT Sname Sage FROM Student WHERE Sage<ANY (SELECT Sage FROM Student WHERE Sdept IS ) AND Sdept<> IS ORDER BY Sage DESC Database System Principle 104
3.4.3 ( ) 3. Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) : IS SELECT Sname Sage FROM Student WHERE Sage<ALL (SELECT Sage FROM Student WHERE Sdept='IS') AND Sdept<> IS ORDER BY Sage DESC Database System Principle 105
3.4.3 ( ) 3. Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) Ex 5 SELECT sno FROM SC GROUP BY sno HAVING AVG(grade) >= ALL (SELECT AVG(grade) FROM SC ) Database System Principle 106
3.4.3 ( ) 4. Exists Database System Principle 107
3.4.3 ( ) 4. (EXISTS) [NOT] EXISTS Database System Principle 108
3.4.3 ( ) Exists + Exists True/False Exists True Exists False Select * Database System Principle 109
3.4.3 ( ) 4. (EXISTS) Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) 1 1 Student SC Student Sno Student.Sno SC SC SC.Sno Student.Sno SC.Cno 1 Student.Sname Database System Principle 110
Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) SELECT Sname FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 ) Database System Principle 111
03 select from sname student where not exists (select * from sc where sno = student.sno and cno = 03 ) Database System Principle 112
3.4.3 ( ) 4. (EXISTS) IN EXISTS IN EXISTS Database System Principle 113
3.4.4 union intersect except all Database System Principle 114
3.4.4 ( ) 001 002 (select SNO from SC where CNO = 001) union (intersect) (select SNO from SC where CNO = 002) Database System Principle 115
001 002 003 (select SNO from SC where CNO = 001 or CNO = 002 ) except (select SNO from SC where CNO = 003) Database System Principle 116
SQL 3.1 SQL 3.2-3.3 3.4 3.5 3.6 Database System Principle 117
3.5 3.5.1 3.5.2 3.5.3 Database System Principle 118
3.5 ( ) 3.5.1 INSERT INTO [ [ ] ] VALUES ( [ ] ) INSERT INTO [ [ ] ] Database System Principle 119
3.5 ( ) 3.5.1 INSERT INTO Prof VALUES ( P0011,, 35, 08, 4980 ) INSERT INTO Prof (P#, PNAME, D#) VALUES ( P0012,, 03 ) Database System Principle 120
3.5 ( ) 3.5.1 Ex 10: Excellent Excellent(s#,avg_score) SC(s#, c#, score) INSERT INTO Excellent ( s#, avg_score) SELECT s#, AVG(score) FROM SC GROUP BY s# HAVING AVG(score) > 90 Database System Principle 121
3.5.2 3.5 ( ) DELETE FROM < > [WHERE < >] where Ex11: DELETE FROM SC Ex12: 95019 DELETE FROM Student WHERE Sno 95019 Database System Principle 122
3.5 ( ) 3.5.3 UPDATE SET = = [ ] [WHERE ] Ex 13: 5% UPDATE Prof SET sal = sal * 1.05 Database System Principle 123
2000 10% 5% update PROF set SAL = SAL * 0.9 where SAL > 2000 update PROF set SAL = SAL * 0.95 where SAL <= 2000 Database System Principle 124
3.5.3 3.5 ( ) Ex 14: DEPT(D#, DNAME, DEAN) PROF(P#, PNAME, AGE, D#, SAL) UPDATE Prof SET sal = (SELECT AVG(sal) FROM Prof WHERE d# = 01 ) WHERE pname = (SELECT dean FROM Dept WHERE d# = 01 ) Database System Principle 125
Ex15: C1 5% SC(S#, C#, score) update SC set score = score * 1.05 where C# = C1 and score < (select avg(score) from SC where C# = C1 ) Database System Principle 126
SQL 3.1 SQL 3.2-3.3 3.4 3.5 3.6 Database System Principle 127
3.6 3.6.1 3.6.2 3.6.3 3.6.4 3.6.5 Database System Principle 128
3.6.1 Database System Principle 129
3.6.1 Database System Principle 130
3.6.2 CREATE VIEW < > AS < > [WITH CHECK OPTION] - WITH CHECK OPTION insert update - ORDER BY DISTINCT Database System Principle 131
Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) Create View CS_Student As Select sno, sname, ssex From Student Where sdept = CS Select sno, sname From CS_Student Where ssex = M Database System Principle 132
3.6.2 [ ] CREATE VIEW CS_Student AS SELECT Sno Sname Sage FROM Student WHERE Sdept= CS' WITH CHECK OPTION Database System Principle 133
3.6.2 CS_Student Sdept= CS' Sdept= CS' Sdept CS' Sdept Sdept CS' Database System Principle 134
3.6.2 Student(sno, sname, ssex, sage, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade) create view S_detail as select sno, sname, cname, grade from student, sc, course where student.sno = sc.sno and course.cno = sc.cno Database System Principle 135
3.6.2 DROP VIEW < > CASCADE DROP VIEW Database System Principle 136
3.6.3 RDBMS View Resolution Database System Principle 137
3.6.3 [ ] 20 SELECT Sno Sage FROM CS_Student WHERE Sage<20 SELECT Sno Sage FROM Student WHERE Sdept= CS' AND Sage<20 Database System Principle 138
3.6.3 [ ] S_G 90 SELECT * FROM S_G WHERE Gavg>=90 S_G CREATE VIEW S_G (Sno Gavg) AS SELECT Sno AVG(Grade) FROM SC GROUP BY Sno Database System Principle 139
3.6.3 SELECT Sno AVG(Grade) FROM SC WHERE AVG(Grade)>=90 GROUP BY Sno SELECT Sno AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90 Database System Principle 140
3.6.4 INSERT, UPDATE, DELETE Database System Principle 141
3.6.4 INSERT, DELETE, UPDATE View Ex 1: CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept='IS WITH CHECK OPTION; Database System Principle 142
3.6.4 Ex 1: INSERT INTO IS_Student VALUES('95029', ' ', 20) INSERT INTO Student(sno,sname,sage,sdept) VALUES('95029', ' ', 20, 'IS') Database System Principle 143
3.6.4 Ex 2: DELETE FROM CS_Student WHERE sno='95029' DELETE FROM Student WHERE Sno='95029' AND Sdept= CS' Database System Principle 144
3.6.4 SELECT SELECT UNIQUE DISTINCT GROUP BY Database System Principle 145
CREATE VIEW AS SELECT FROM P_SAL pno, pname, sal Prof INSERT INTO P_SAL VALUES ( P0018,, 750 ) INSERT INTO PROF VALUES ( P0018,, null, null, 750 ) Database System Principle 146
PROF(P#, PNAME, AGE, D#, SAL) CREATE VIEW prof_1 AS SELECT pname, sal FROM prof insert into prof_1 P# values(' ',3400) Database System Principle 147
3.6.5 Database System Principle 148
Database System Principle 149