第 5 章 关系数据库标准语言 SQL
description
Transcript of 第 5 章 关系数据库标准语言 SQL
-
5 SQLSQLStructured Query LanguageSQLSQLSQLSQLSQL
-
5.1 SQL SQL2070IBMSEQUEL(Structured English Query Language)2080SQLANSISQL19984ISOSQL1990SQL
-
5.1.1 SQL(1)(2)(3)(4)
-
5.1.2 SQL SQL()5.1 SQL SQL SELECT CREATEDROPALTER INSERTUPDATEDELETE GRANTREVOKE
-
5.2 SQL
-
5.2.1 SQLSQLSQLSELECTSELECT-FROM-WHERE SELECT ALL|DISTINCT TOP Select AS , Select AS FORM ! ASLocal_Alias INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER JOIN ASLocal_AliasON INTO |TO FILE ADDITIVE | TO PRINTER PROMPT | TO SCREEN PREFERENCE PreferenceNameNOCONSOLEPLAINNOWAIT WHERE 1 AND 2AND | OR GROUP BY , HAVING UNION ALL SELECT ORDER BY ASC |DESC , ASC |DESCSELECT-SQLSELECTFROMWHEREORDERGROUPUNION
-
SELECT FROM WHERE GROUP BY HAVING ORDER BY
-
1.SELECTSELECTALLDISTINCTTOP()SelectASSelectSelect*
-
2.FROMJOININNER | LEFTOUTER | RIGHTOUTER | FULLOUTER45.2OUTEROUTER
-
3.WHERE FROM
-
4.GROUP BY
-
5. HAVING GROUP BYHAVINGGROUP BY HAVING WHERE
-
6. ORDER BY ASCDESE
-
7.
UNIONUNIONSELECT-SQLUNIONALLSELECTSELECT
-
5.2.2 5.1zgda SELECT * FROM zgda
-
5.2.2 5.2zggz2000 SELECT ,, FROM zggz WHERE >2000
-
5.2.2 5.3zgda SELECT DISTINCT FROM zgda5.4zgda SELECT ,, FROM zgda WHERE =""
- 5.2.2 5.5zggz10001800 SELECT ,, FROM zggz WHERE >1000 AND
-
5.2.3 SQLWHEREVFP(1)NOTIN(2)NOTBETWEENAND(3)NOTLIKE
-
5.2.3 (1)NOT(2)LIKE_0(3)ININ(12)
-
5.2.3 5.6zgda SELECT DISTINCT ,, FROM zgda WHERE LIKE "" NOT SELECT DISTINCT ,, FROM zgda WHERE NOT( LIKE "")
-
5.2.3 5.7zgda SELECT , FROM zgda WHERE LIKE "%"
-
5.2.3 5.8zgda SELECT , FROM zgda WHERE IN("","") VFP, SELECT , FROM zgda WHERE ="" OR =""
-
5.2.4 ,5.9zgdazggz2000 SELECT zgda.,zgda.,zggz. FROM zgda,zggz WHERE (>2000) AND (zgda.=zggz.) zgda.=zggz. &&
-
5.2.4 5.10zgdazggz, 1900SELECT zgda.,zgda.,zgda.,zggz. FROM zgda,zggzWHERE (="") AND (>1900) AND (zgda.=zggz.)
-
5.2.5 ,,4(4 ),,4 P.90
-
5.2.5 5.111250SELECT FROM WHERE IN (SELECT FROM WHERE =1250)SELECT-FROM-WHERE,, WH1WH2, IN: SELECT FROM WHERE IN("WH1", "WH2")
- 5.2.5 5.121210 SELECT * FROM WHERE NOT IN(SELECT FROM WHERE
- 5.2.5 SELECTFROMWHERE1210WH1;WH4SQL1210 SELECT * FROM WHERE NOT IN (SELECT FROM WHERE
-
5.2.5 5.13E4SELECT FROM WHERE = (SELECT FROM WHERE ="E4")
- 5.2.5 5.1412201240BETWEENAND SELECT * FROM WHERE BETWEEN 1220 AND 1240 BETWEENAND (>=1220) AND (
-
5.2.5 5.15,LIKE SELECT * FROM WHERE LIKE "%" LIKE%0-
-
5.2.5 5.16,SELECT * FROM WHERE !="" SQL!=NOTSELECT * FROM WHERE NOT(="") NOTNOT INNOT BETWEEN
-
SELECT SnoSnameSdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= )
-
SELECT Sdept FROM Student WHERE Sname= Sno Sname Sdept 95001 IS 95002 BA 95004 cs 95004 IS Sdept IS
-
IS SELECT SnoSnameSdept FROM Student WHERE Sdept= IS Sno Sname Sdept 95001 IS 95002 BA 95004 cs 95004 ISSno Sname Sdept
IS95001 IS95004 IS
-
5.2.6 SQLORDER BYORDER BY 1 ASC | DESC, 2 ASC | DESCASC()DESC
-
5.2.6 5.17, SELECT * FROM ORDER BY ORDER BYDESC SELECT * FROM ORDER BY DESC
-
5.2.6 5.18,, SELECT * FROM ORDER BY , ORDER BY
-
5.2.7 SQLSQLSQL(1)COUNT(2)SUM(3)AVG(4)MAX(5)MINSELECT
-
5.2.7 5.19, SELECT COUNT (DISTINCT ) FROM COUNTDISTINCT SELECT COUNT(*) FROM 6
-
5.2.7 5.20, SELECT SUM() FROM SELECT SUM(DISTINCT ) FROM DISTINCT1250
-
5.2.7 5.21, SELECT SUM() FROM WHERE IN (SELECT FROM WHERE ="" OR ="")
- 5.2.7 [5.22] ,1210 SELECT AVG() FROM WHERE NOT IN (SELECT FROM WHERE
-
5.2.7 5.23,WH2 SELECT MAX() FROM WHERE ="WH2"
MAXMIN() SELECT MIN() FROM WHERE ="WH2"
-
5.2.8 GROUP BYGROUP BY HAVINGHAVING
-
5.2.8 5.24, SELECT AVG() FROM GROUP BY GROUP BYWHEREWHEREFROMHAVING
-
5.2.8 5.25, SELECT COUNT(*),AVG() FROM GROUP BY HAVING COUNT(*)>=2HAVINGGROUP BY HAVINGWHERE WHEREHAVINGHAVINGWHEREWHEREHAVINGHAVING
-
5.2.9 SQLFROM4 SELECT FROM WHERE ="" AND ="" AND .=. AND .=. AND .=.
-
5.2.9 SELECT FROM S, P, E W WHERE =""AND ="" AND S.=P. AND P.=E. AND E.=W.FROM S P E WSPEWSQL
-
5.2.10 SQLFROM FROMINNER | LEFT OUTER| RIGHTOUTER| FULLOUTER JOIN !AS Local_AliasON OUTEROUTER(outer join)
-
5.2.10 1.INNER JOININNER JOINJOININNER JOIN
-
5.2.12 5.31 SELECT .,,,, FROM JOINON .=. SELECT .,,,, FROM INNER JOIN ON .=. SELECT .,,,, FROM WHERE .=.
-
5.2.10 2.LEFTOUTER JOINJOINJOIN
-
5.2.12 5.32 SELECT .,,,, FROM LEFT JOIN ON .=.ABNULL
-
5.2.10 3.RIGHT OUTER JOINJOINJOIN: "WH8""E8"1200
-
5.2.10 5.33 SELECT .,,,, FROM RIGHT JOIN ON .=.E8
-
5.2.10 4.FULL OUTER JOINJOIN
-
5.2.10 5.34 SELECT .,,,, FROM FULL JOIN ON .=.Visual FoxProSQL SELECT
-
5.2.10 4SELECT .FROM ,,,WHERE .=.AND .=.AND .=.Visual FoxPro
-
5.2.11 UNIONSELECTSelcct1 UNION ALL Selcct2 (1)UNIONALL() (2)UNION (3)SelcctORDER BY()
-
5.2.11 5.35, SELECT * FROM WHERE ="" UNION SELECT * FROM WHERE =""
-
5.2.12 ARRAY CURSOR TABLE | DBF TO FILEADDITIVETO PRINTERINPROMPTTO SCREENPLAINNOWAIT
-
5.2.12 1.: TOP PERCENT::(1)PERCENT132 767(2)PERCENT0.0199.99TOPORDERBY
-
5.2.12 5.36, SELECT * TOP 3 FROM ORDER BY DESC5.37, 30% SELECT * TOP 30 PERCENT FROM ORDER BY
-
5.2.12 2.: INTO ARRAY : :
-
5.2.14 5.38T1 SELECT * FROM INTO ARRAY T1 T1(1,1)T1(13)
-
5.2.12 3.: INTO CURSOR :: dbfdbf
-
5.2.12 5.39dbfT2 SELECT * FROM INTO CURSOR T2 INTO CURSOR
-
5.2.12 4.: INTO DBF|TABLE : (dbf)
-
5.2.12 5.40, ,Al SELECT * TOP 3 FROM INTO TABLE A1 ORDER BY DESC
-
5.2.12 5. TO FILE ADDITIVE (txt)ADDITIVE
-
5.2.12 5.41A2.txt SELECT * TOP 3 FROM TO FILF A2 ORDER BY DESCTOINTOTO
-
5.2.12 6. :TO SCREEN :
-
5.2.12 7. :TO PRINTER PROMPT :PROMPTFROM INTO TO INTO 3ARRAYCURSORTABLE | DBF TO3
-
SQL SELECTVisual FoxProSQL SELECTVisual FoxPro
-
5.3 SQL
-
5.3.1 SQL3 INSERT INTO (12) VALUES(1), 2)INSERT INTO FROM ARRAY INSERT INTO FROM MEMVAR3
-
5.3.1 (1)1VALUES(2)2(3)3 Visual FoxProSQLVisual FoxPro
-
5.3.1 5.42("E7","S4","OR01",092503)INSERT INTO (,,,,)VALUES("E7","S4","OR01",{^2003-09-25},1200)
{^2003-09-25}
-
5.3.1 INSERT INTO () VALUES("E7","OR01")
-
5.3.1 INSERT INTOFROM ARRAY USE &&SCATTER to A1 &&A1COPY STRUCTURE TO A2 &&A2INSERT INTO A2 FROM ARRAY A1 &&A1A2SELECT A2 &&A2BROWSE &&BROWSEUSE &&A2.dbfDELETE FILE A2.dbf &&A2.dbf
-
5.3.1 FoxPro(INSERTAPPEND)
-
5.3.2 UPDATE! SET1=1 ,2=2 WHERE1AND | OR 2
-
5.3.2 (1)! (2)SET=WHERE(3)WHERE;WHERE
-
5.3.2 5.43,WH110%5.49 UPDATE SET =*1.10 WHERE ="WH1 : 1UPDATE SET =+1
-
5.3.3 SQLDELETE FROM !WHERE1AND | OR2 (1)!(2)WHERE WHERE(3)PACKREACLL
-
5.3.3 5.44WH2DELETE FROM WHERE ="WH2"SQL DELETEPACK
-
5.4 SQLVisual FoxPro
-
5.4.1 3Visual FoxProSQLCREATE TABLESQLSQL
-
5.4.1 CREATE TABLE |DBF 1NAME FREE (1 (,) NULL | NOT NULL CHECK 1ERROR 1 DEFAULT 1 PRIMARY KEY | UNIQUE REFERENCES 2TAG1 NOCPTRANS,2 ,PRIMARY KEY2TAG2 |,UNIQUE3TAG3 ,FOREIGN KEY4TAG4NODUP REFERENCES3TAG 5 ,CHECK2ERROR 2 ) |FROM ARRAY
-
5.4.1 CREATE TABLE3(1)TABLEDBF(2)(3) NAME128(4)FREE(5) 1 (, ) (6)NULLNOT NULLNOT NULL(7)CHECK1(8)ERROR1CHECKVFP(9)DEFAULT(10)PRIMARY KEYUNIQUE(11)REFERENCES TAG (12)CHECK 2ERROR 2 (13)FROM ARRAY SQL
-
5.4.1 5.451CREATE DATABASE D:VFP11 &&1SQL CREATE1OPEN DATABASE 1 &&1CREATE TABLE D:VFP11 ( C (5) PRIMARY KEY, C (10), I CHECK (>0) ERROR "0!") &&1
-
5.4.1 :(1)PRIMARY KEY(2)CHECK(>0) 1
-
5.4.1 5.46SQL CREATE 1CREATE TABLE D:VFP11( C (5), C (5), C (5) PRIMARY KEY, D) SQL CREATE4
-
5.4.1 :(1)SQL CREATESET EXCLUSIVE(2)(FREE)NAMECHECKDEFAULTFOREIGN KEYPRIMARY KEYREFERENCES
-
5.4.2 DROP TABLE DROP TABLEDBFDBFDBC
-
5.4.3 SQLALTER TABLE
-
5.4.3 1.1ALTER TABLEALTER TABLE 1 ADD | ALTER COLUMN1() NULL | NOT NULLCHECK1 ERRORDEFAULT1 PRIMARY KEY | UNIQUEREFERENCES 2 TAG 1NOCPTRANS
-
5.4.3 (1)1(2)ADD COLUMNADDCHECKPRIMARY KEYUNIQUE(3)ALTER COLUMNALTERCHECKCHECKPRIMARY KEYUNIQUE
-
5.4.3 5.471 ALTER TABLE 1 ADD I CHECK >0 ERROR "0!"
-
5.4.3 5.48156ALTER TABLE 1 ALTER C(6);
-
5.4.3 2.2 DEFAULTCHECKALTER TABLEALTER COLUMN NULL | NOT NULL SET DEFAULT SET CHECK ERROR DROP DEFAULT DROP CHECK
-
5.4.3 (1)(2)ALTER COLUMN (3)NULL | NOT NULL(4)SET DEFAULT (5)SET CHECK ERROR (6)DROP DEFAULT(7)DROP CHECK
-
5.4.3 5.49ALTER TABLE 1 ALTER SET CHECK >0 ERROR "0"
-
5.4.3 5.50ALTER TABLE 1 ALTER DROP CHECK 3
-
5.4.3 3.3 ALTER TABLEDROP COLUMN1 SET CHECK1ERROR DROP CHECK ADD PRIMARY KEY1TAG1FOR2 DROP PRIMARY KEY ADD UNIQUE 2TAG 2FOR3 DROP UNIQUE TAG3 ADD FOREIGN KEY3TAG4FOR4 REFERENCES 2 TAG4 DROP FOREIGN KEY TAG5SAVE RENAME COLUMN2TO3 NOVALIDATE
-
5.4.3 (1)DROPCOLUMN (2)SET CHECK ERRORDROP CHECK (3)ADD PRIMARY KEY TAG DROP PRIMARY KEY(4)ADD UNIQUE TAG DROP UIQUE TAG(5)ADD FOREIGN KEY()(6)DROP FOREIGN KEY TAGSAVE(7)RENAME COLUMN 2TO323(8)NOVALIDATEDEFAULTFOREIGN KEYPRIMARY KEYREFERENCESSET
-
5.4.3 5.511ALTER TABLE 1 RENAME COLUMN TO
-
5.4.3 5.521ALTER TABLE 1 DROP COLUMN
-
5.4.3 5.531()e1ALTER TABLE 1 ADD UNIQUE + TAG e1
-
5.4.3 5.541e1 ALTER TABLE 1 DROP UNIQUE TAG e1
-
5.4.4 Visual FoxPro
-
5.4.4 CREATESQLVIEW(1 ,2 )AS(1)SELECT(2)1.
-
5.4.4 5.55CREATE VIEW W1 AS SELECT , FROM 1.
-
5.4.4 W1 SELECT * FROM W1 SELECT , FROM W1 SELECT , FROM
-
5.4.4 5.56,CREATE VIEW W2 AS;SELECT * FROM WHERE ="" W2
-
5.4.4 2.
-
5.4.4 5.57,, CREATE VIEW W3 AS SELECT out.,out.,out.,out.,out. FROM out WHERE = (SELECT MAX()FROM inner1 WHERE out.=inner1.) SELECT * FROM W3
-
5.4.4 5.58CREATE VIEW W4 AS SELECT ,, FROM , WHERE .=.
-
5.4.4 3. SELECT
-
5.4.4 5.593CREATE VIEW W5 AS SELECT , AS ,*12 AS FROM SELECTAS12;
-
5.4.4 4. DROP VIEWVFPDELETE VIEW
-
5.4.4 5.60 W4 DROP VIEW W4
-
5.4.4 5. Visual FoxProVisual FoxPro;
-
P.109