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

第三章 PowerPoint PPT Presentation


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

第三章. 資料庫之資料選取查詢. 章節概要. 3.1 撰寫 SQL 指令 3.2 如何編輯及執行 SQL 指令 3.3 使用 SELECT 選取資料 3.4 使用 WHERE 設定條件選取資料 3.5 使用運算式及函數 3.6 使用聚合函數來統計資料  3.7 使用 GROUP BY 於資料群組化  3.8 使用 HAVING 子句篩選群組化資料  3.9 限定最大輸出筆數  3.10 關於 NULL. 3.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


3323980


3323980

  • 3.1 SQL

  • 3.2 SQL

  • 3.3 SELECT

  • 3.4 WHERE

  • 3.5

  • 3.6

  • 3.7 GROUP BY

  • 3.8 HAVING

  • 3.9

  • 3.10 NULL


3 1 sql

3.1SQL

  • SQL(Non-procedure Language)SQL(Keyword)


Sql mysql

SQL(MySQL)

  • alter

  • asc

  • between

  • cross

  • distinct

  • from

  • having

  • avg

  • case

  • else

  • for

  • and

  • char

  • constraint

  • desc

  • as

  • by

  • creat

  • describe

  • drop

  • exists

  • group

  • identified


Sql mysql1

SQL(MySQL)()

  • if

  • jey

  • lock

  • natural

  • null

  • select

  • in

  • insert

  • is

  • keys

  • left

  • on

  • or

  • set

  • use

  • order

  • update

  • like

  • not

  • outer

  • table

  • to

  • unique


3323980

SQL()

  • SQL(Statement)(;)

  • go run

  • SQL


3 2 sql

3.2 SQL

    • A. ODBC Viewer (ODBC)

    • B. MS Access ( QBE)

    • C. SQL Server

    • D. MySQL Query Browser

    • E. DB Visualizer

    • F. Oracle SQL Developer

  • (Departments)(DepartmentID)(Name)SQL(SQL)

    • SELECT DepartmentID, NameFROM Departments;


A odbc viewer

A. ODBC Viewer

  • ODBC Viewer (ODBC) software odbcv310.exe ODBC Viewer (http://www.slik.co.nz )

  • []/[]/[Slik software]ODBC Viewer( orders_access ODBCODBC)


3323980

[Data Source]ODBC


3323980

[]orders_accessorders_mssqlorders_mysql


3323980

SQL[Execute]( orders_access)


B ms access

B. MS Access

  • orders.mdb


3323980

[]


3323980

[]


3323980


3323980

Departments[]


3323980

[ ! ]


3323980


3323980

[][SQL]SQL

SQL

(Query By Example; QBE)


C sql server

C. SQL Server()

  • SQL Query Analyzer

  • Enterprise Manager

  • SQL Query Analyzer (SQL )


3323980

orders SQL [ ! ]


3323980

[]SQL


C sql server1

C. SQL Server()

  • Enterprise Manager SQL SQL Server Enterprise Manager


3323980

orders Departments


3323980

[ ! ]


3323980


3323980

SQL SQL


C sql server2

C. SQL Server()

  • SQL Server 2005/2008 SQL Server Management Studio Express SQL SQL Server Management Studio Express


3323980

orders(N)SQL(X)

QBE Ctrl+Shift+Q


D mysql query browser

D. MySQL Query Browser

  • MySQL Query Browser


3323980

[...](Username)(Password)(Hostname)(Port)(Schema; )orders


3323980

orders


3323980


3323980

SQL SQL()


E db visualizer

E. DB Visualizer

  • DB Visualizer

    • (JDBC)

    • SQLSQL


3323980

orders


3323980

(MySQL)


3323980


3323980

SQL CommanderSQL


F oracle sql developer

F. Oracle SQL Developer

  • Oracle SQL Developer


3323980

Oracle


3323980

SQLSQL


3 3 select

3.3 SELECT

  • SELECTSELECT(SELECT Clause)FROM(FROM Clause)

    • SELECT SELECT FROM


Select

SELECT()

  • SELECT/FROM

  • (Departments)(DepartmentID)(Name)SQL

    • SELECT DepartmentID, NameFROM Departments;


Select1

SELECT()

  • SELECT *

    • SELECT SELECT * FROM


Select2

SELECT()

  • (Departments)SQL

    • SELECT *FROM Departments;


Select3

SELECT()

  • SELECT (Alias) AS ( AS )SQL

    • SELECTSELECT AS , AS, ...FROM


Select4

SELECT()

  • DepartmentID Department

    • SELECT DepartmentID AS Department, NameFROM Departments;

    • SELECT DepartmentID AS , Name AS FROM Departments;

    • SELECT DepartmentID AS '', Name AS ''FROM Departments;


Select5

SELECT()

  • SQLSQL

    • SELECT LocationFROM Departments;


Select6

SELECT()

  • DISTINCTSQL

    • SELECTSELECT DISTINCT AS , AS ,

      FROM

  • SQL

    • SELECT DISTINCT LocationFROM Departments;


3 4 where

3.4 WHERE

    • SQLWHEREWHERE

    • WHERE()


3323980

3.3


3323980

  • SQL

    • SELECTSELECT AS , AS ,FROM WHERE

  • 10SQL

    • SELECT *FROM DepartmentsWHERE DepartmentID = 10;


3323980

()


3323980

()

  • 20()

    • SELECT *FROM EmployeesWHERE DepartmentID = 20;


Is null

(IS NULL)

  • 10021008

  • (NULL)

    • SELECT *FROM EmployeesWHERE Commission IS NULL;


3323980

()

  • ()

    • SELECT *FROM EmployeesWHERE Commission = NULL;

    • -- SELECT *FROM EmployeesWHERE Commission = '';


3323980

()

  • NULL (=>>=<<= ...)NULL(FALSE) WHERE Commission = NULL

  • NULL

    • SELECT*FROM EmployeesWHERE Commission IS NOT NULL;


3323980

  • WHERE(Logical Operator)

  • SQL3.4


3323980

3.4

(

)


3323980

()

  • AND() ()

  • OR () () () ()

  • NOT ()


3323980

()

    • SELECT EmployeeID, Name, Salary, TitleFROM EmployeesWHERE Title ='' AND TitleOfCourtesy = '';


3323980

()

  • NULL

    • SELECT EmployeeID, Name, Salary, Title, CommissionFROM EmployeesWHERE Title = '' OR Commission IS NOT NULL;


3323980

()

    • SELECT *FROM EmployeesWHERE NOT (Title = '');


3323980

()

  • NOT 0()

    • -- SQL SELECT *FROM EmployeesWHERE NOT Title = '';


3323980

()

    • SELECT *FROM EmployeesWHERE Title <> '';

  • (SQL)

    • SELECT *FROM EmployeesWHERE Title != '';


3323980

  • WHERE

    • LIKE

    • BETWEEN AND

    • IN


3323980

()

  • ()

    • SELECT *FROM EmployeesWHERE Title LIKE '%';


3323980

LIKE '%'


3323980

()

  • '' ()

    • SELECT *FROM EmployeesWHERE Name LIKE '%';


3323980

LIKE '%'


3323980

()

  • 'M___' ()M

    • SELECT *FROM EmployeesWHERE EngName LIKE 'M___';


3323980

LIKE 'M___'


3323980

()

  • 'ar'

    • SELECT *FROM EmployeesWHERE EngName LIKE '%ar%';


3323980

LIKE '%ar%'


3323980

()

  • 420000540000BEWTEEN...AND

    • SELECT *FROM EmployeesWHERE Salary BETWEEN 420000 AND 540000;


3323980

[42000, 54000]


3323980

()

    • SELECT *FROM EmployeesWHERE Salary >= 420000 AND Salary <= 540000;

  • BEWTEEN AND


3323980

()

  • 10,20,30 A, B, C IN

  • 10021005()

    • SELECT EmployeeID AS '', Name AS '', SupervisorID AS 'FROM EmployeesWHERE SupervisorID IN (1002, 1005);


3323980

()

  • 'Mary' 'Marua' 'Mike' 'Linda'

    • SELECT *FROM EmployeesWHERE EngName IN ('Mary', 'Marua', 'Mike', 'Linda');


3323980

  • ()


3323980

()

  • ORDER BYSQL

    • SELECTSELECT AS , AS , ...FROM WHERE ORDER BY [ASC|DESC], [ASC|DESC], ...


3323980

()

    • SELECT *FROM DepartmentsORDER BY DepartmentID DESC;


3323980

()

  • ()

    • SELECT DepartmentID, EmployeeID, Name, EngNameFROM EmployeesORDER BY DepartmentID, EmployeeID


3323980


3323980

()

  • ()()

    • SELECT DepartmentID, EmployeeID, Name, EngNameFROM EmployeesORDER BY DepartmentID DESC, EmployeeID ASC


3323980


3323980

()

  • (1)

  • SQL

    • SELECT DepartmentID, EmployeeID, Name, EngNameFROM EmployeesORDER BY 1 DESC, 2


3323980

3.5

  • SQL(Expression)(Function)(Operator)

  • (Numeric)()


3323980

()

  • 10251()(OrderDetails)

    • SELECT ProductID AS '', UnitPrice AS '', Quantity AS '',UnitPrice*Quantity AS '' FROM OrderDetailsWHERE OrderID = 10251

      (Oracle "")


3323980

()

  • (Bitwise)

  • DBMS

  • MySQLC/C++


3323980

MySQL


3323980

()

  • 1224FROM()SQL

    • SELECT 12 & 24, 12 | 24, 12 ^ 24

  • Oracle SELECTFROMDUALSELECT 1+2 FROM DUAL


3323980

()

  • RDBMS

    • (CHAR/VARCHAR)


3323980

()

    • SELECT ROUND(12.5,0), LEAST('A', 'B', 'C'), RAND(0), SIGN(-4)


3323980

='ABCDEF'

LEFT SUBSTR(str, 1, N) (OracleLEFT)


3323980

REVERSE(AB) = BA

SUBSTR

RIGHT SUBSTR(str, LENGTH(str)+1-N) (OracleRIGHT)


3323980

()

  • 10

    • SELECT Name, BirthDate, LEFT(BirthDate, 4), MID(BirthDate, 6, 2), RIGHT(BirthDate, 2)FROM EmployeesWHERE DepartmentID = 10


3323980

()

  • 10(MySQL/ORACLE)

    • SELECT EmployeeID, Name, EngName,CONCAT(Name,' - ',EngName) AS CombinedNameFROM EmployeesWHERE DepartmentID = 10


3323980

()

  • SQL Server/Access

    • SELECT EmployeeID, Name, EngName, Name + ' - ' + EngName AS CombinedNameFROM EmployeesWHERE DepartmentID = 10


3323980

Access NOW()


3323980

()

  • 10MySQL

    • SELECT Name, HireDate, DATE_ADD(HireDate, INTERVAL 3 YEAR)FROM EmployeesWHERE DepartmentID=10

  • SQL Server

    • SELECT Name, HireDate,DATEADD(year,3,HireDate)FROM EmployeesWHERE DepartmentID=10


3323980

3.6

  • SQL(Aggregate Functions)


3323980

DISTINCT


3323980

()

    • SELECT COUNT(*)FROM Employees;

  • ///

    • SELECT COUNT(*) AS '', MIN(Salary) AS '', MAX(Salary) AS '', SUM(Salary) AS '', AVG(Salary) AS '', STDDEV(Salary) AS ''FROM Employees;


3323980

()

  • 10///

    • SELECT COUNT(*) AS '', MIN(Salary) AS '', MAX(Salary) AS '', SUM(Salary) AS '', AVG(Salary) AS '', STDDEV(Salary) AS ''FROM EmployeesWHERE DepartmentID = 10;

SQL Server/Access STDEV( Salary )


3323980

()

  • NULL

    • SELECT COUNT(*), COUNT(Commission), COUNT(DISTINCT Commission)FROM Employees;


3 7 group by

3.7 GROUP BY

  • SQLGROUP BYSQL

    • SELECTSELECT AS , AS , ...FROM WHERE GROUP BY , , ...ORDER BY [ASC|DESC], [ASC|DESC], ...


Group by

GROUP BY()

  • GROUP BY


Group by1

GROUP BY()

  • ()///

    • SELECT DepartmentID, COUNT(*) AS '', MIN(Salary) AS '', MAX(Salary) AS '', SUM(Salary) AS '', AVG(Salary) AS '', STDDEV(Salary) AS ''FROM EmployeesGROUP BY DepartmentID;


Group by2

GROUP BY()

  • GROUP BYSELECTGROUP BY

  • SQL()

    • SELECT DepartmentID, Name, COUNT(*) AS '', MIN(Salary) AS '', MAX(Salary) AS '', SUM(Salary) AS '', AVG(Salary) AS ''FROM EmployeesGROUP BY DepartmentID;


3 8 having

3.8 HAVING

  • GROUP BY

    • SELECTSELECT AS , AS , ...FROM WHERE GROUP BY , , ...HAVINGORDER BY [ASC|DESC], [ASC|DESC], ...


Having

HAVING()

  • HAVINGWHEREHAVINGWHERE

  • SELECT 3.2


Having1

HAVING()

  • ()///5()

    SELECT DepartmentID AS '', COUNT(*) AS '', MIN(Salary) AS '', MAX(Salary) AS '', SUM(Salary) AS '', AVG(Salary) AS '', STDDEV(Salary) AS ''FROM EmployeesGROUP BY DepartmentIDHAVING COUNT(*) >= 5;


3323980

3.9

  • WEB()SQLRDBMS

  • SQL Server/AccessTOP nnTOP n PERCENTn

    • SELECT *FROM Employees;


3323980

()

  • 5(SELECTTOP 5)

    • SELECT TOP 5 *FROM Employees;

  • 5%(SELECTTOP 5 PERCENT)

    • SELECT TOP 5 PERCENT *FROM Employees;


3323980

()

  • SQLSQL

    • SELECT DISTINCT TOP 25 PERCENT EmployeeID, Name, SalaryFROM Employees;

    • DISTINCTTEXT/BLOB/IMAGE


3323980

()

  • MySQLSQL LIMIT n1, n2 n1 (0)n25

    • SELECT *FROM EmployeesLIMIT 0,5


3 10 null

3.10NULL

  • NULL


3323980

NULL()

  • NULLNULL

    • SELECT EmployeeID, Name, Salary, Commission, Salary+Commission AS TotalFROM Employees

  • (TotalNULL)


3323980

NULL()

  • NULL

  • SQL

    • SELECT DepartmentID, SUM(Salary), SUM(Commission), SUM(Salary+Commission) AS TotalFROM EmployeesGROUP BY DepartmentID


3323980

NULL()

  • NULLNULL


3323980

NULL()

  • SQL

    • SELECT EmployeeID, Name, Salary, Commission, Salary+Commission AS TotalFROM Employees

  • (NULL0)(MySQL)

    • SELECT EmployeeID, Name, Salary, Commission,Salary + IFNULL(Commission,0) AS TotalFROM Employees


3323980

NULL()

    • SELECT DepartmentID, SUM(Salary), SUM(Commission), SUM(Salary + IFNULL(Commission,0) ) AS TotalFROM EmployeesGROUP BY DepartmentID


  • Login