Languages
Pages
Legal
An Introduction to Database System
SQL
An Introduction to Database System
SQL3.1 SQL3.2 3.3 3.4 3.5 3.6 3.7 SQL3.8
An Introduction to Database System
3.1 SQLSQL1. 2. 3. 4. 5.
An Introduction to Database System
5.
An Introduction to Database System
3.1 SQL
SQL
CREATEDROPALTER
SELECT
INSERTUPDATE
DELETE
GRANTREVOKE
SQL3.1 SQL3.2 3.3 3.4 3.5 3.6 3.7 SQL3.8
An Introduction to Database System
3.2
An Introduction to Database System
3.2 SQL
CREATE TABLE
DROP TABLE
ALTER TABLE
CREATE VIEW
DROP VIEW
CREATE INDEX
DROP INDEX
3.2.1 CREATE TABLE [ ] [ [ ] ] [ ]
An Introduction to Database System
[1] StudentSnoSnameSsexSageSdept CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE Sname CHAR(20) UNIQUE Ssex CHAR(1) Sage INT Sdept CHAR(15))
An Introduction to Database System
PRIMARY KEYUNIQUENOT NULL
PRIMARY KEY UNIQUE
An Introduction to Database System
[2] SCSnoCnoGrade(Sno, Cno)CREATE TABLE SC( Sno CHAR(5) , Cno CHAR(3) , Grade int, Primary key (Sno, Cno));
An Introduction to Database System
An Introduction to Database System
DROP TABLE ;
An Introduction to Database System
[5] Student DROP TABLEStudent ;
An Introduction to Database System
ALTER TABLE [ ADD [ ] ][ DROP ][ MODIFY ]
ADDDROPMODIFY
An Introduction to Database System
[2] Student
ALTER TABLE Student ADD Scome DATE
An Introduction to Database System
/:()ALTER TABLE Student Drop Scome
An Introduction to Database System
[3]
ALTER TABLE Student MODIFY Sage SMALLINT
An Introduction to Database System
[4] ALTER TABLE Student DROP UNIQUE(Sname)
An Introduction to Database System
3.2.2 DBADBMS PRIMARY KEY UNIQUE DBMS DBMS
An Introduction to Database System
CREATE [UNIQUE] [CLUSTER] INDEX ON ([][,[] ])ASCDESCASCUNIQUECLUSTER
An Introduction to Database System
[6] -StudentCourseSCStudentCourseSC
CREATE UNIQUE INDEX Stusno ON Student(Sno)CREATE UNIQUE INDEX Coucno ON Course(Cno)CREATE UNIQUE INDEX SCno ON SC(Sno ASCCno DESC)
An Introduction to Database System
UNIQUEUNIQUEDBMSUNIQUE
An Introduction to Database System
CREATE CLUSTER INDEX Stusname ON Student(Sname)StudentSnameStudentSname
An Introduction to Database System
An Introduction to Database System
DROP INDEX [7] StudentStusnameDROP INDEX Stusname
An Introduction to Database System
3.3 3.3.1 3.3.2 3.3.3 3.3.4 3.3.5 3.3.6
An Introduction to Database System
3.3.1 SELECT [ALL|DISTINCT] [] FROM [ ] [ WHERE ][ GROUP BY [ HAVING ] ][ ORDER BY [ ASC|DESC ] ]
An Introduction to Database System
SELECTFROM()WHERE GROUP BYHAVINGORDER BY
An Introduction to Database System
-Student(SnoSnameSsexSageSdept)Course(CnoCnameCpnoCcredit) SC(SnoCnoGrade)
An Introduction to Database System
3.3 3.3.1 3.3.2 3.3.3 3.3.4 3.3.5 3.3.6
An Introduction to Database System
3.3.2
An Introduction to Database System
[1] SELECT SnoSnameFROM Student[2] SELECT SnameSnoSdeptFROM Student
An Introduction to Database System
[3] SELECT SnoSnameSsexSageSdept FROM Student SELECT *FROM Student
An Introduction to Database System
3. SELECT
An Introduction to Database System
3. [4] SELECT Sname2000-SageFROM Student Sname 2000-Sage --------- ------------- 1976 1977 1978 1978
An Introduction to Database System
3. [5]
SELECT Sname'Year of Birth: '2000-Sage ISLOWER(Sdept)FROM Student
An Introduction to Database System
Sname 'Year of Birth:' 2000-Sage ISLOWER(Sdept) ------- ---------------- --------- -------------- Year of Birth: 1976 cs Year of Birth: 1977 is Year of Birth: 1978 ma Year of Birth: 1977 is
An Introduction to Database System
[5.1] SELECT Sname NAME'Year of Birth: BIRTH 2000-Sage BIRTHDAYISLOWER(Sdept) DEPARTMENTFROM Student NAME BIRTH BIRTHDAY DEPARTMENT ------- ---------------- ------------- ------------------ Year of Birth: 1976 cs Year of Birth: 1977 is Year of Birth: 1978 ma Year of Birth: 1977 is
An Introduction to Database System
An Introduction to Database System
1. SELECTDISTINCTSC Sno Cno Grade ------- ------- ------- 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80
An Introduction to Database System
ALL DISTINCT [6] (1) SELECT Sno FROM SC;( ALL) SELECT ALL Sno FROM SC; Sno ------- 95001 95001 95001 95002 95002
An Introduction to Database System
(2) SELECT DISTINCT Sno FROM SC; Sno ------- 95001 95002
An Introduction to Database System
DISTINCTSELECT DISTINCT CnoDISTINCT GradeFROM SC; SELECT DISTINCT CnoGrade FROM SC;
An Introduction to Database System
2.WHERE
An Introduction to Database System
(1) WHERE=>=!= 20
An Introduction to Database System
(2) BETWEEN AND NOT BETWEEN AND [10] 20~232023 SELECT SnameSdeptSageFROM StudentWHERE Sage BETWEEN 20 AND 23
An Introduction to Database System
[11] 20~23
SELECT SnameSdeptSageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23
An Introduction to Database System
(3) IN , NOT IN [12]ISMA CSSELECT SnameSsexFROM StudentWHERE Sdept IN ( 'IS''MA''CS' );
An Introduction to Database System
(3) [13] SELECT SnameSsexFROM Student WHERE Sdept NOT IN ( 'IS''MA''CS' );
An Introduction to Database System
(4) [NOT] LIKE [ESCAPE ] = LIKE != < > NOT LIKE
An Introduction to Database System
% () 0a%babacbaddgbab _ () a_bab3acbafb
An Introduction to Database System
ESCAPE
% _ ESCAPE ''
An Introduction to Database System
1) [14] 95001 SELECT * FROM Student WHERE Sno LIKE '95001' SELECT * FROM Student WHERE Sno = '95001'
An Introduction to Database System
2)
[15] SELECT SnameSnoSsex FROM Student WHERE Sname LIKE %
An Introduction to Database System
[16] "" SELECT Sname FROM Student WHERE Sname LIKE '__'
An Introduction to Database System
[17] 2"" SELECT SnameSno FROM Student WHERE Sname LIKE '__%'
An Introduction to Database System
[18] SELECT SnameSnoSsex FROM Student WHERE Sname NOT LIKE '%'
An Introduction to Database System
3) [19] DB_Design SELECT CnoCcredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\'
An Introduction to Database System
()
[20] "DB_"3 i SELECT * FROM Course WHERE Cname LIKE 'DB\_%i_ _' ESCAPE ' \ '
An Introduction to Database System
(5) IS NULL IS NOT NULL IS NULL = NULL [21] SELECT SnoCno FROM SC WHERE Grade IS NULL
An Introduction to Database System
()
[22]
SELECT SnoCno FROM SC WHERE Grade IS NOT NULL
An Introduction to Database System
(6) AND OR ANDOR [NOT] IN [NOT] BETWEEN AND
An Introduction to Database System
[12][12] ISMACSSELECT SnameSsexFROM StudentWHERE Sdept IN ( 'IS''MA''CS' )SELECT SnameSsexFROM StudentWHERE Sdept= ' IS ' OR Sdept= ' MA' OR Sdept= ' CS '
An Introduction to Database System
ORDER BY ASCDESCASCDESC
An Introduction to Database System
[24] 3
SELECT SnoGrade FROM SC WHERE Cno= ' 3 ' ORDER BY Grade DESC
An Introduction to Database System
Sno Grade ------- ------- 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55
An Introduction to Database System
[25]
SELECT * FROM Student ORDER BY SdeptSage DESC
An Introduction to Database System
5COUNT[DISTINCT|ALL] *COUNT[DISTINCT|ALL] SUM[DISTINCT|ALL] AVG[DISTINCT|ALL]
An Introduction to Database System
MAX[DISTINCT|ALL] MIN[DISTINCT|ALL] DISTINCTALLALL
An Introduction to Database System
[26] SELECT COUNT(*) FROM Student[27] SELECT COUNT(DISTINCT Sno) FROM SCDISTINCT
An Introduction to Database System
[28] 1 SELECT AVG(Grade) FROM SC WHERE Cno= ' 1 '[29] 1 SELECT MAX(Grade) FROM SC WHER Cno= ' 1 '
An Introduction to Database System
GROUP BY
An Introduction to Database System
GROUP BY[30] SELECT CnoCOUNT(Sno) FROM SC GROUP BY Cno Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48
An Introduction to Database System
GROUP BYGROUP BYSELECT
An Introduction to Database System
HAVING[31] 3 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >3
An Introduction to Database System
[32] 390 90 SELECT Sno, COUNT(*) FROM SC WHERE Grade>=90 GROUP BY Sno HAVING COUNT(*)>=3;
An Introduction to Database System
HAVINGHAVINGHAVINGWHEREWHEREHAVING
An Introduction to Database System
An Introduction to Database System
Top Related