1 / 50

SQL 結構化查詢語言

SQL 結構化查詢語言. SQL 語言種類. 資料處理型語言( DML Data Manipulation Language) SELECT,INSERT,UPDATE,DELETE 交易控制型語言( Transaction Control) COMMIT,ROLLBACK,SAVEPOINT. SQL 語言種類（續）. 資料定義型語言( DDL Data Definition Language) CREATE,ALTER,DROP 資料控制型語言( DCL Data Control Language) GRANT,REVOKE,SET ROLE.

aldan
Download Presentation

SQL 結構化查詢語言

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL結構化查詢語言

  2. SQL語言種類 • 資料處理型語言(DML Data Manipulation Language) • SELECT,INSERT,UPDATE,DELETE • 交易控制型語言(Transaction Control) • COMMIT,ROLLBACK,SAVEPOINT

  3. SQL語言種類（續） • 資料定義型語言(DDL Data Definition Language) • CREATE,ALTER,DROP • 資料控制型語言(DCL Data Control Language) • GRANT,REVOKE,SET ROLE

  4. 一、資料處理型語言(DML) • SELECT Syntax(語法)： SELECT [DISTINCT] {* , column [alias]..} FROM table ; * : selects all column column: select the name of column alias : give a select

  5. Example : 1. SELECT * FROM emp ; 2. SELECT emp_no , emp_name FROM emp ;

  6. Arithmetic Expressions 先乘除後加減，可以用括號改變次序

  7. Example : SELECT emp_no , emp_name , emp_salary * 5 FROM emp ;

  8. Column Aliases Example : SELECT emp_no AS 序號 , emp_name “姓名” FROM emp ;

  9. Concatenation Operator Example : SELECT emp_no || emp_name AS “序號 姓名” FROM emp ;

  10. NVL Function

  11. Example : SELECT emp_no,emp_name,emp_dept FROM emp ; Example : SELECT emp_no,emp_name,NVL(emp_dept,’NO_WRITE) FROM emp ;

  12. Duplicate Rows Example : SELECT emp_no,emp_name FROM emp ; Example : SELECT DISTINCT emp_no,emp_name FROM emp ;

  13. Limit Condition and Order Syntax : SELECT expr FROM table WHERE condition ORDER BY expr ;

  14. Comparison and Logical Operators • Logical comparison operators = > >= < <= • SQL comparison operators • BETWEEN ... AND... • IN(list) • LIKE • IS NULL • Logical operators • AND • OR • NOT

  15. Negating Expressions • Logical Operators != <> ^= • SQL Operators • NOT BETWEEN • NOT IN • NOT LIKE • IS NOT NULL

  16. BETWEEN and IN SQL Operators SQL> SELECT * 2 FROM emp 3 WHERE emp_no BETWEEN ‘1001' 4 AND ‘1025'; SQL> SELECT * 2 FROM emp 3 WHERE emp_dept IN (‘D01’,’D02’);

  17. LIKE SQL Operator • You can use the LIKE operator to perform wildcard searches of valid search string values. • Search conditions can contain either literal characters or numbers. • "%" denotes none or many characters. • "_" denotes one character. SQL> SELECT * 2 FROM emp 3 WHERE emp_name LIKE ‘USER%5';

  18. LIKE SQL Operator SQL> SELECT emp_name, emp_dept 2 FROM emp 3 WHERE emp_salary LIKE '%9%'; SQL> SELECT emp_name, emp_dept 2 FROM emp 3 WHERE emp_salary LIKE '%9_';

  19. IS NULL SQL Operator • Test for null values with the = NULL operator. • Use the = operator. SQL> SELECT emp_name, emp_dept 2 FROM emp 3 WHERE emp_salary = NULL;

  20. Multiple Conditions • Use complex criteria. • Combine conditions with AND or OR operators. • AND requires both conditions to be TRUE. • OR requires either condition to be TRUE.

  21. Multiple Conditions SQL> SELECT * 2 FROM emp 3 WHERE emp_dept = ‘D01’ 4 AND emp_salary between 3000 AND 5000; SQL> SELECT * 2 FROM emp 3 WHERE emp_dept = ‘D01’ 4 OR emp_salary between 3000 AND 5000;

  22. Displaying Data from Multiple Tables

  23. What Is a Join? • A join is used to query data from more than one table. • Rows are joined using common values, typically primary and foreign key values. • Join methods • Equijoin • Non-equijoin • Outer join • Self join • Set operators

  24. Relations Between Tables S_EMP Table ID LAST_NAME DEPT_ID -- --------------- ------- 1 Velasquez 50 2 Ngao 41 3 Nagayama 31 4 Quick-To-See 10 5 Ropeburn 50 6 Urguhart 41 7 Menchu 42 8 Biri 43 9 Catchpole 44 10 Havel 45 11 Magee 31 12 Giljum 32 13 Sedeghi 33 14 Nguyen 34 15 Dumas 35 16 Maduro 41 S_DEPT Table ID NAME REGION_ID -- --------------- --------- 30 Finance 1 31 Sales 1 32 Sales 2 43 Operations 3 50 Administration 1 S_REGION Table ID NAME -- --------------------- 1 North America 2 South America 3 Africa / Middle East 4 Asia 5 Europe

  25. Simple Join Query: Syntax • Write the join condition in the WHERE clause. • Precede each column name with the table name for clarity. • Column names must be prefixed with the table name when the same column name appears in more than one table. SELECT table.column, table.column FROM table1, table2 WHERE table1.column1 = table2.column2;

  26. Server Equijoin: Example EMP DEPT LAST_NAME DEPT_ID ID NAME--------- ------- -- ---------------Velasquez 50 50 AdministrationNgao 41 41 OperationsNagayama 31 31 SalesRopeburn 50 50 AdministrationUrguhart 41 41 OperationsMenchu 42 42 OperationsBiri 43 43 OperationsHavel 45 45 Operations... ...

  27. Server Additional Search Conditions Using the AND Operator: Example EMP DEPT LAST_NAME DEPT_ID ID NAME--------- ------- -- ---------------Velasquez 50 50 AdministrationNgao 41 41 OperationsNagayama 31 31 SalesRopeburn 50 50 AdministrationUrguhart 41 41 OperationsMenchu 42 42 OperationsBiri 43 43 OperationsHavel 45 45 Operations... ...

  28. Non-Equijoins: Example • Non-equijoins result when no column in one table corresponds directly to a column in the second table. • The join condition contains an operator other than equal (=). SQL> SELECT e.ename, e.job, e.sal, s.grade 2 FROM emp e, salgrade s 3 WHERE e.sal BETWEEN s.losal AND s.hisal;

  29. Outer Joins: Syntax • Use an outer join to see rows that do not normally meet the join condition. • Outer join operator is the plus sign (+). • Place the operator on the side of the join where there is no value to join to. SELECT table.column, table.column FROM table1, table2 WHERE table1.column(+) = table2.column;

  30. Server Outer Joins CUSTOMER EMP SALES_LAST_NAME ID REP_ID NAME--------- -- ------ --------------Magee 11 11 WomansportMagee 11 11 Beisbol Si!Magee 11 11 Ojibway RetailGiljum 12 12 UnisportsGiljum 12 12 Futbol SonoraSedeghi 13 13 Hamada SportDumas 15 15 Sportique Sweet Rock Sports No Sales RepAssigned toSweet RockSports

  31. Outer Joins: Example • Display the sales representative name and the customer name for all customers even if the customer has no sales representative. SQL> SELECT e.last_name, e.id, c.name 2 FROM s_emp e, s_customer c 3 WHERE e.id (+) = c.sales_rep_id 4 ORDER BY e.id;

  32. Server Self Joins S_EMP (MANAGER) S_EMP (WORKER) LAST_NAME MANAGER_ID ID LAST_NAME--------- ---------- -- ----------Ngao 1 1 VelasquezNagayama 1 1 Velasquez Ropeburn 1 1 Velasquez Urguhart 2 2 NgaoMenchu 2 2 Ngao Biri 2 2 Ngao Magee 3 3 NagaymaGiljum 3 3 Nagayma ... ...

  33. Self Joins: Example • Join rows in a table to rows in the same table by using a self join. • Simulate two tables in the FROM clause by creating two aliases for the table. SQL> SELECT worker.last_name||' works for '|| 2 manager.last_name 3 FROM s_emp worker, s_emp manager 4 WHERE worker.manager_id = manager.id;

  34. What Is a Subquery? A subsequery is a SELECT statement embedded in a clause of another SQL statement. SELECT Syntax Main Query SELECT... FROM... WHERE... Subquery SELECT Syntax (SELECT... FROM... WHERE...);

  35. Server Subqueries: Example S_EMP S_EMP LAST_NAME TITLE TITLE LAST_NAME--------- ----------- ----------- ---------Maduro Stock Clerk Stock Clerk SmithSmith Stock Clerk Nozaki Stock Clerk Patel Stock Clerk Newman Stock Clerk Chang Stock Clerk Patel Stock Clerk Dancs Stock Clerk Schwartz Stock Clerk

  36. Subqueries: Syntax • The subquery executes once before the main query. • The result of the subquery is used by the main outer query. SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

  37. How Are Nested Subqueries Processed? 1. Nested SELECT statement is executed first. 2. Result is passed into condition of main query. Nested Query Main Query SELECT dept_id FROM s_emp WHERE last_name='Biri'; SELECT last_name, title FROM s_emp WHERE dept_id = 43

  38. Single Row Subqueries Write the SQLstatement todisplay thelast name and title of an employee. SELECT last_name, titleFROM s_empWHERE title = SELECT titleFROM s_empWHERE last_name = ’Smith’ Write the SQLstatement tofind out Smith’s title. SQL> SELECT last_name, title 2 FROM s_emp 3 WHERE title = 4 (SELECT title 5 FROM s_emp 6 WHERE last_name = ’Smith’); Put bothstatements together and let SQL determinethe title for Smith.

  39. Multiple Row Subqueries: Example • A multiple row subquery returns many rows. • You must use a multiple row operator in the WHERE clause, for example the IN operator. SQL> SELECT last_name, first_name, title 2 FROM s_emp 3 WHERE dept_in IN 4 (SELECT ID 5 FROM s_dept 6 WHERE name = 'Finance' 7 OR region_id = 2);

  40. Creating Tables: Syntax CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint]); You must have specific privileges: • CREATE TABLE • A storage area

  41. Datatypes Datatype VARCHAR(size) CHAR(size) int float DATE DATETIME Decimal(M,D) Description Variable length character values Fixed length character values Integer 單精浮點數 Date Date and time values M為全部數字位數(包括小數位)D為小數位數

  42. Create Table: Example SQL> CREATE TABLE s_dept -> (id NUMBER(7), -> name VARCHAR (25), -> region_id NUMBER(7));

  43. Data Manipulation and Transaction Control Commands Command INSERT UPDATE DELETE COMMIT Description Adds a new row to the table. Modifies existing rows in the table. Removes existing rows from the table. Makes all pending changes permanent.

  44. Inserting New Rows into a Table: Syntax • Add new rows to a table by using the INSERT command. • Only one row is inserted at a time with this syntax. INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

  45. Inserting New Rows: Example • Insert a new row containing values for each column. • Optionally list the columns in the INSERT clause. • List values in the default order of the columns in the table. • Enclose character and date values within single quotation marks. SQL> INSERT INTO s_dept 2 VALUES (11, 'Finance', 2); 1 row created.

  46. Updating Rows in a Table: Syntax • Modify existing rows with the UPDATE command. UPDATE table SET column = value [, column = value] [WHERE condition];

  47. Updating Rows: Examples • Transfer employee number 2 to department 10. SQL> UPDATE s_emp 2 SET dept_id = 10 3 WHERE id = 2; 1 row updated. • Transfer employee number 1 to department 32 and change the salary to 2550. SQL> UPDATE s_emp 2 SET dept_id = 32, salary = 2550 3 WHERE id = 1; 1 row updated.

  48. Updating All Rows in the Table • All rows in the table will be updated if you do not add the WHERE clause. SQL> UPDATE s_emp 2 SET commission_pct = 10; 25 rows updated.

  49. Deleting Rows from a Table: Syntax • Remove existing rows by using the DELETE command. DELETE [FROM] table [WHERE condition]; • Remove all information about employees who started after January 1, 1996. SQL> DELETE FROM s_emp 2 WHERE start_date > 3 TO_DATE('01.01.1996', 'DD.MM.YYYY'); 1 row deleted.

  50. Deleting Rows: Example • Delete all the rows in the table by excluding the WHERE clause. SQL> DELETE FROM test; 25,000 rows deleted. • Confirm the deletions. SQL> SELECT * 2 FROM test; no rows selected

More Related