This presentation is the property of its rightful owner.
Sponsored Links
1 / 39

本 章 内 容 PowerPoint PPT Presentation


  • 96 Views
  • Uploaded on
  • Presentation posted in: General

第五章 关系数据库 SQL 语言. 本 章 内 容. §5.1 SQL 概述 §5.2 查询语句 SELECT-SQL §5.2.1 单表查询 §5.2.2 多表查询 §5.2.3 嵌套查询. §5.2.4 自联查询 §5.2.5 集合的并运算 §5.3 其他 SQL 命令 §5.3.1 定义功能的 SQL 语句 §5.3.2 操作功能的 SQL 语句. §5.1 SQL 概述. 1 .结构化查询语言 SQL 简介

Download Presentation

本 章 内 容

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


5754074

SQL

5.1 SQL

5.2 SELECT-SQL

5.2.1

5.2.2

5.2.3

5.2.4

5.2.5

5.3 SQL

5.3.1 SQL

5.3.2 SQL


5 1 sql

5.1SQL

1SQL

SQLStructured Query LanguageVFPSQL

2SQL

1SQL

2SQLSQL


5754074

3SQL

1SELECT-SQLSQL

2CREATE TABLE-SQL

3ALTER TABLE -SQL

4INSERT-SQL

5DELETE-SQL

6UPDATE-SQL

7CREATE VIEW-SQL

4SQL


5 2 select sql

5.2SELECT-SQL

1SELECT-SQL

SELECT [ALL|DISTINCT][TOP nExpr [PERCENT]]

[Alias.]Select_Item [AS Column_name][,[Alias.]Select_Item [AS Column_name]]

FROM [DataName!]TableName [[AS] Local_Alias]

[,[DataName!]TableName [[AS] Local_Alias]]

[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER] JOIN

[DataName!]TableName [[AS] Local_Alias]

[ON JoinCondition]]

[WHERE JoinCondition [AND JoinCondition]

[AND|OR FilterCondition [AND|OR FilterCondition]]]

[ORDER BY Order_Item [ASC|DESC][,Order_Item [ASC|DESC]]]

[GROUP BY GroupColumn [,GroupColumn]

[HAVING FilterCondition]]

[TO SCREEN|FILE FileName [ADDITIVE]|PRINTER [PROMPT]]

[INTO TABLE TableName|CURSOR CursorName|ARRAY ArrayName]


5754074

5.2.1

SELECT-SQL

SELECT <>FROM <>WHERE <>

SELECT-SQLFROM

1

SELECT <> FROM <>

<> *


5754074

1

51Zgjk.dbf

SELECT ,, FROM Zgjk&&

2

52Zgjk.dbfVFP

SELECT * FROM Zgjk TO SCREEN


5754074

3

SQL

SELECT


5754074

53Zgjk.dbf

SELECT MAX() AS , ;

MIN() AS , ;

AVG() AS ;

FROM Zgjk

SQLASAS

54Zgjk.dbf

SELECT ,,YEAR(DATE())-YEAR() AS FROM Zgjk


5754074

2

SELECT <> FROM <> WHERE <>

WHERE

SQL

BETWEEN AND LIKE

BETWEEN AND

WHERE BETWEEN 200 AND 300

WHERE >=200 AND <=300

LIKE

WHERE LIKE "%

WHERE LEFT(,2)=""


5754074

1

55Zgjk.dbf10001000

SELECT DISTINCT ,, FROM Zgjk WHERE >=1000

2

56Zgjk.dbf

SELECT * FROM Zgjk WHERE ="" AND


5754074

3

WHERELIKELIKEWHERE

WHERE LIKE "<>"

_%

57Zgjk.dbf

SELECT * FROM Zgjk WHERE LIKE "%

SELECT * FROM Zgjk WHERE =""

SELECT * FROM Zgjk WHERE LEFT(,2)=""

SELECT * FROM Zgjk WHERE AT("",)=1


5754074

58Zgjk.dbf

SELECT * FROM Zgjk WHERE LIKE "_%"

59Zgjk.dbf10001200

SELECT * FROM Zgjk WHERE BETWEEN;

1000 AND 1200

SELECT * FROM Zgjk WHERE >=1000 AND ;

<=1200


5754074

3

SELECT-SQLORDER BY

SELECT <> FROM <> [WHERE <>];

ORDER BY <> [ASC|DESC]

ASORDER BY123

ASCDESCASC

a < A < b < B


5754074

1

510Zgjk.dbf

SELECT ,,YEAR(DATE())-YEAR() AS ;

FROM Zgjk ORDER BY

2

511Zgjk.dbf

SELECT * FROM Zgjk ORDER BY , DESC


5754074

4

SELECT-SQLGROUP BY<>

SELECT <> FROM <> [WHERE <>];

GROUP BY <> [HAVING <>]

GROUP BY

GROUPHAVINGWHEREHAVINGWHEREHAVING


5754074

512Zgjk.dbf

SELECT ,COUNT(*) AS FROM Zgjk ;

GROUP BY

513Zgjk.dbf2500

SELECT ,SUM() AS FROM Zgjk;

GROUP BY HAVING >=2500

514Zgjk.dbf

SELECT ,SUM() AS FROM;

Zgjk WHERE GROUP BY


5754074

5

SELECT-SQLTOASCIIVFPTO

1

515Zgjk.dbf2TOP2GZ.txt

SELECT TOP 2 * FROM Zgjk TO FILE TOP2GZ ;

ORDER BY DESC

TOP2GZ.txt,

Type TOP2GZ.txt&&TYPE


5754074

6

SELECT-SQLINTO

1

516Zgjk.dbfMEN.dbf

SELECT * FROM Zgjk WHERE ="" INTO TABLE MEN

USE MEN&&

BROWSE&&


5754074

2

517Zgjk.dbfMENtmp

SELECT * FROM Zgjk WHERE ="" ;

INTO CURSOR MENtmp

BROWSE&&


5754074

3

518Zgjk.dbfARRtmp

SELECT * FROM Zgjk INTO ARRAY ARRtmp

1ARRtmp(1,1)~ARRtmp(1,8)9ARRtmp(9,1)~ ARRtmp(9,8)

DISPLAY MEMORY LIKE ARRtmp


5754074

5.2.2

VFP4

VFP4ZgjkXsZgjk2Xs2519

519Zgjk2Xs2


5754074

1INNER JOIN

1INNER JOINON

SELECT <> FROM <1>;

INNER JOIN <2> ON <1>.<>=<2>.<>

2WHERE

SELECT <> FROM <1>,<2>;

WHERE <1>.<>=<2>.<>


5754074

519Zgjk2Xs2

SELECT Zgjk2.,,,;

FROM Zgjk2 INNER JOIN Xs2 ON Zgjk2.=Xs2.

SELECT Zgjk2.,,,;

FROM Zgjk2, Xs2 WHERE Zgjk2.=Xs2.


5754074

Zgjk2.=Xs2.520

520


5754074

2LEFT JOIN

122.NULL.

SELECT <> FROM <1>;

LEFT JOIN <2> ON <1>.<>=<2>.<>

520Zgjk2Xs2Zgjk2

SELECT Zgjk2.,,,;

FROM Zgjk2 LEFT JOIN Xs2 ON Zgjk2.=Xs2.


5754074

521

521


5754074

3RIGHT JOIN

211.NULL.

SELECT <> FROM <1> RIGHT JOIN <2> ;

ON <1>.<>=<2>.<>

521Zgjk2Xs2Xs2

SELECT Zgjk2.,,,;

FROM Zgjk2 RIGHT JOIN Xs2 ON Zgjk2.=Xs2.


5754074

522

522


5754074

4FULL JOIN

12.NULL.

SELECT <> FROM <1> FULL JOIN <2>;

ON <1>.<>=<2>.<>

522Zgjk2Xs2

SELECT Zgjk2.,,,;

FROM Zgjk2 FULL JOIN Xs2 ON Zgjk2.=Xs2.


5754074

523

523


5754074

5

Sp2524

523Zgjk2Xs2Sp2

524Sp2


5754074

SELECT Zgjk2.,,Xs2.,,,,;

FROM Zgjk2 INNER JOIN Xs2;

INNER JOIN Sp2 ;

ON Xs2. = Sp2. ;

ON Zgjk2. = Xs2. ;

ORDER BY Zgjk2.

SELECT Zgjk2.,,Xs2.,,,,FROM Zgjk2, Xs2, Sp2;

WHERE Zgjk2.=Xs2. AND Xs2.=Sp2.;

ORDER BY Zgjk2.


5754074

5.2.3

SELECT-SQLSELECT-SQLSELECT-SQL

SELECT <1> FROM <1>;

WHERE <1> IN;

(SELECT <2> FROM <2> WHERE <2>)

IN12


5754074

1

SELECT , FROM Zgjk WHERE IN ;

( SELECT FROM Xs WHERE >=5 )

526

524ZgjkXs55

526524


5754074

2

SELECT DISTINCT Zgjk2., FROM Zgjk;

INNER JOIN Xs ON Zgjk.=Xs.;

WHERE >=5

SELECT DISTINCT Zgjk2., FROM Zgjk,Xs;

WHERE Zgjk.=Xs. AND >=5


5754074

5.2.4

SELECT-SQLFROM

SELECT <1.> FROM <> <1> WHERE <>=;

(SELECT <> FROM <> <2>;

WHERE <1.=2.>)

525

SELECT out.,out.,out.;

FROM Xs out WHERE =;

(SELECT MAX() FROM Xs inner1;

WHERE out.=inner1.)


5754074

527

527525


5754074

5.2.5

SQLUNIONSELECT-SQL

SELECT <1> FROM <1> WHERE <1>;

UNION;

SELECT <2> FROM <2> WHERE <2>

SELECT-SQL

SELECT-SQL<>


5754074

526

SELECT * FROM Zgjk WHERE ="";

UNION;

SELECT * FROM Zgjk WHERE =""

528

528526


  • Login