中国人民大学信息学院计算机系
-
Upload
kahlilia-kurush -
Category
Documents
-
view
35 -
download
4
description
Transcript of 中国人民大学信息学院计算机系
-
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
- [23] 20 SELECT Sname FROM Student WHERE Sdept= 'CS' AND Sage
-
[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
- [10][10] 20~232023 SELECT SnameSdeptSageFROM StudentWHERE Sage BETWEEN 20 AND 23 SELECT SnameSdeptSageFROM Student WHERE Sage>=20 AND Sage
-
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