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

第三章 PowerPoint PPT Presentation


  • 84 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



  • 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.1SQL

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


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(MySQL)()

  • if

  • jey

  • lock

  • natural

  • null

  • select

  • in

  • insert

  • is

  • keys

  • left

  • on

  • or

  • set

  • use

  • order

  • update

  • like

  • not

  • outer

  • table

  • to

  • unique


SQL()

  • SQL(Statement)(;)

  • go run

  • 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

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

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


[Data Source]ODBC


[]orders_accessorders_mssqlorders_mysql


SQL[Execute]( orders_access)


B. MS Access

  • orders.mdb


[]


[]



Departments[]


[ ! ]



[][SQL]SQL

SQL

(Query By Example; QBE)


C. SQL Server()

  • SQL Query Analyzer

  • Enterprise Manager

  • SQL Query Analyzer (SQL )


orders SQL [ ! ]


[]SQL


C. SQL Server()

  • Enterprise Manager SQL SQL Server Enterprise Manager


orders Departments


[ ! ]



SQL SQL


C. SQL Server()

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


orders(N)SQL(X)

QBE Ctrl+Shift+Q


D. MySQL Query Browser

  • MySQL Query Browser


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


orders



SQL SQL()


E. DB Visualizer

  • DB Visualizer

    • (JDBC)

    • SQLSQL


orders


(MySQL)



SQL CommanderSQL


F. Oracle SQL Developer

  • Oracle SQL Developer


Oracle


SQLSQL


3.3 SELECT

  • SELECTSELECT(SELECT Clause)FROM(FROM Clause)

    • SELECT SELECT FROM


SELECT()

  • SELECT/FROM

  • (Departments)(DepartmentID)(Name)SQL

    • SELECT DepartmentID, NameFROM Departments;


SELECT()

  • SELECT *

    • SELECT SELECT * FROM


SELECT()

  • (Departments)SQL

    • SELECT *FROM Departments;


SELECT()

  • SELECT (Alias) AS ( AS )SQL

    • SELECTSELECT AS , AS, ...FROM


SELECT()

  • DepartmentID Department

    • SELECT DepartmentID AS Department, NameFROM Departments;

    • SELECT DepartmentID AS , Name AS FROM Departments;

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


SELECT()

  • SQLSQL

    • SELECT LocationFROM Departments;


SELECT()

  • DISTINCTSQL

    • SELECTSELECT DISTINCT AS , AS ,

      FROM

  • SQL

    • SELECT DISTINCT LocationFROM Departments;


3.4 WHERE

    • SQLWHEREWHERE

    • WHERE()


3.3


  • SQL

    • SELECTSELECT AS , AS ,FROM WHERE

  • 10SQL

    • SELECT *FROM DepartmentsWHERE DepartmentID = 10;


()


()

  • 20()

    • SELECT *FROM EmployeesWHERE DepartmentID = 20;


(IS NULL)

  • 10021008

  • (NULL)

    • SELECT *FROM EmployeesWHERE Commission IS NULL;


()

  • ()

    • SELECT *FROM EmployeesWHERE Commission = NULL;

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


()

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

  • NULL

    • SELECT*FROM EmployeesWHERE Commission IS NOT NULL;


  • WHERE(Logical Operator)

  • SQL3.4


3.4

(

)


()

  • AND() ()

  • OR () () () ()

  • NOT ()


()

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


()

  • NULL

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


()

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


()

  • NOT 0()

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


()

    • SELECT *FROM EmployeesWHERE Title <> '';

  • (SQL)

    • SELECT *FROM EmployeesWHERE Title != '';


  • WHERE

    • LIKE

    • BETWEEN AND

    • IN


()

  • ()

    • SELECT *FROM EmployeesWHERE Title LIKE '%';


LIKE '%'


()

  • '' ()

    • SELECT *FROM EmployeesWHERE Name LIKE '%';


LIKE '%'


()

  • 'M___' ()M

    • SELECT *FROM EmployeesWHERE EngName LIKE 'M___';


LIKE 'M___'


()

  • 'ar'

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


LIKE '%ar%'


()

  • 420000540000BEWTEEN...AND

    • SELECT *FROM EmployeesWHERE Salary BETWEEN 420000 AND 540000;


[42000, 54000]


()

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

  • BEWTEEN AND


()

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

  • 10021005()

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


()

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

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


  • ()


()

  • ORDER BYSQL

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


()

    • SELECT *FROM DepartmentsORDER BY DepartmentID DESC;


()

  • ()

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



()

  • ()()

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



()

  • (1)

  • SQL

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


3.5

  • SQL(Expression)(Function)(Operator)

  • (Numeric)()


()

  • 10251()(OrderDetails)

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

      (Oracle "")


()

  • (Bitwise)

  • DBMS

  • MySQLC/C++


MySQL


()

  • 1224FROM()SQL

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

  • Oracle SELECTFROMDUALSELECT 1+2 FROM DUAL


()

  • RDBMS

    • (CHAR/VARCHAR)


()

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


='ABCDEF'

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


REVERSE(AB) = BA

SUBSTR

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


()

  • 10

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


()

  • 10(MySQL/ORACLE)

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


()

  • SQL Server/Access

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


Access NOW()


()

  • 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


3.6

  • SQL(Aggregate Functions)


DISTINCT


()

    • SELECT COUNT(*)FROM Employees;

  • ///

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


()

  • 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 )


()

  • NULL

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


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()

  • ()///

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


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

  • GROUP BY

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


HAVING()

  • HAVINGWHEREHAVINGWHERE

  • SELECT 3.2


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;


3.9

  • WEB()SQLRDBMS

  • SQL Server/AccessTOP nnTOP n PERCENTn

    • SELECT *FROM Employees;


()

  • 5(SELECTTOP 5)

    • SELECT TOP 5 *FROM Employees;

  • 5%(SELECTTOP 5 PERCENT)

    • SELECT TOP 5 PERCENT *FROM Employees;


()

  • SQLSQL

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

    • DISTINCTTEXT/BLOB/IMAGE


()

  • MySQLSQL LIMIT n1, n2 n1 (0)n25

    • SELECT *FROM EmployeesLIMIT 0,5


3.10NULL

  • NULL


NULL()

  • NULLNULL

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

  • (TotalNULL)


NULL()

  • NULL

  • SQL

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


NULL()

  • NULLNULL


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


NULL()

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


  • Login