›
›

Bài giảng Hệ quản trị cơ sơ dữ liệu Oracle - Chương 2: Cơ bản về SQL - Ngô Thùy Linh

pdf 122 trang ngocly 3810
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Hệ quản trị cơ sơ dữ liệu Oracle - Chương 2: Cơ bản về SQL - Ngô Thùy Linh", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfbai_giang_he_quan_tri_co_so_du_lieu_oracle_chuong_2_co_ban_v.pdf

Nội dung text: Bài giảng Hệ quản trị cơ sơ dữ liệu Oracle - Chương 2: Cơ bản về SQL - Ngô Thùy Linh

  1. CƠ BẢN VỀ SQL  Giảng viên: Ngô Thùy Linh Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  2. Nội dung chính • Giới thiệu về SQL • Truy vấn dữ liệu: Select • Định nghĩa dữ liệu: DDL (Create, Alter, Drop) • Thao tác dữ liệu: DML (Insert, Update, Delete) • Tối ưu hóa câu lệnh SQL 2-2 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  3. SQL  SQL: Structured Query Language  Các loại: • DML (Data Manipulation Language) • DDL (Data Definition Language) • Transaction Control • Session Control • System Control 2-3 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  4. Kiểu dữ liệu Character Numeric Datetime LOB ROWID Binary CHAR (n) NUMBER(m,n) DATE CLOB ROWID RAW(size) NCHAR(n) FLOAT TIMESTAMP WITH NCLOB UROWID LONG TIMEZONE RAW VARCHAR2(n) BINARY_FLOAT TIMESTAMP BLOB WITH LOCAL TIMEZONE NVARCHAR2(n) BINARY_DOUBLE INTERVAL BFILE YEAR[(n)] TO MONTH INTERVAL DAY[(m)] TO SECOND[(n)] 2-4 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  5. Kiểu dữ liệu  Kiểu ký tự: • Char( ): 1byte 2000 byte • Varchar2( ): 4000 byte • Nvarchar2( ): 4000 byte  Kiểu số: • Number(p,s) • p is the precision (1 38) and s (-84 127) is the scale  Kiểu ngày tháng • Date: Jan 1, 4712 BC Dec 31, 9999 AD 2-5 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  6. Kiểu dữ liệu (tt)  Kiểu số: Number(p,s) • Number: có thể lưu trữ bất cứ giá trị nào • Number(6): lưu trữ tối đa số có 6 chữ số • Number(5, 2): -999.99 999.99 • Number(5, -2): số nguyên tối đa có (5-(-2)) chữ số và có 2 chữ số cuối là 0 • Number(3, 8): -0.00000999 0.00000999 8-3 3 2-6 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  7. Lấy dữ liệu SELECT [DISTINCT] danh_sách_cột FROM {table_name | view_name} [WHERE điều_kiện] [GROUP BY danh_sách_cột_1] [HAVING điều_kiện_lọc] [ORDER BY danh_sách_cột_2 [ASC | DESC]] 2-7 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  8. Phép toán Loại phép toán Phép toán Toán học +, -, *, / So sánh =, {!=, , = SOME/ANY, ALL Logic NOT, AND, OR Các phép toán ||, LIKE, NOT LIKE chuỗi Các phép toán IN, NOT IN, BETWEEN, EXISTS, IS NULL, IS khác NOT NULL 2-8 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  9. VÍ DỤ 2-9 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  10. Câu lệnh SELECT đơn giản  SELECT [DISTINCT] {*, column_list [alias], }  FROM table-name  Chọn tất cả các cột?  Một cột cụ thể?  Thay đổi tên hiển thị 2-10 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  11. Câu lệnh SELECT đơn giản  select tencb  from canbo;  select *  from ngoaingu; 2-11 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  12. Select (Limiting Results)  Hiển thị N bản ghi đầu tiên của bảng 2-12 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  13. SQL Alias (Bí danh cột)  SELECT column_name AS alias_name FROM table_name;  Ví dụ  select macb as MaCanBo, tengoaingu as "Ngoai Ngu"  from ngoaingu; 2-13 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  14. Toán tử nối  Nối các cột hoặc chuỗi ký tự vào các cột  Tượng trưng bởi 2 vạch đứng ||  Tạo ra các cột kết quả là một biểu thức ký tự  Ví dụ  select macb || tengoaingu as "CanBo NgoaiNgu"  from ngoaingu; 2-14 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  15. SQL SELECT DISTINCT  SELECT DISTINCT column_name,column_name FROM table_name;  Ví dụ 1: select quequan from canbo;  Ví dụ 2:  select distinct quequan from canbo; 2-15 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  16. Biểu thức số học  Phép toán số học: * / + -  Ví dụ: Thưởng cho mỗi cán bộ 1 tháng lương cộng thêm 500000đ  select tencb as "Ho ten", luong as Luong,  luong + 500000 as Thuong  from canbo; 2-16 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  17. SQL WHERE Syntax  SELECT column_name,column_name FROM table_name WHERE column_name comparison-operator value; 2-17 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  18. SQL WHERE Syntax  Ví dụ: Hiển thị tên và lương của các cán bộ có lương hơn 3 triệu:  select tencb as "Ho Ten", luong as "Luong thang"  from canbo  where luong > 3000000; 2-18 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  19. ALL, ANY/SOME  The ALL comparison condition is used to compare a value to a list or subquery.  It must be preceded by =, !=, >, = and followed by a list or subquery.  select tencb, luong  from canbo  where luong <= ALL (3000000, 4000000, 8000000); 2-19 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  20. ANY/ SOME  The ANY comparison condition is used to compare a value to a list or subquery.  It must be preceded by =, !=, >, = and followed by a list or subquery.  The SOME and ANY comparison conditions do exactly the same thing and are completely interchangeable.  select tencb as "Ho Ten", luong as "Luong thang"  from canbo  where luong > ANY (4000000, 7000000); 2-20 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  21. Một số phép toán khác  Các phép toán so sánh khác 2-21 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  22. Một số phép toán khác  IN (NOT IN) • Dùng để kiểm tra các giá trị thuộc (không thuộc) một danh sách cho trước  SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2, ); 2-22 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  23. Một số phép toán khác  Ví dụ: hiển thị lương các cán bộ thỏa mãn 1 trong các giá trị sau  select tencb, luong  from canbo  where luong IN (2000000,3000000, 4000000, 5000000, 6000000); 2-23 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  24. Một số phép toán khác  Select tencb, luong  from canbo  where luong NOT IN (2000000, 3000000, 4000000); 2-24 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  25. Một số phép toán khác  SQL LIKE Syntax • The LIKE operator is used to search for a specified pattern in a column.  SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; 2-25 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  26. Một số phép toán khác  Ví dụ: hiển thị ra các cán bộ có quê quán bắt đầu bởi chữ H  Select tencb,quequan  from canbo  where quequan LIKE 'H%'; 2-26 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  27. Một số phép toán khác  BETWEEN AND • Dùng để lựa chọn giá trị của trường trong một khoảng giá trị (numbers, text, dates) cho trước.  SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;  Ví dụ 1: Ví dụ 2: 2-27 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  28. Một số phép toán khác  IS NULL/ IS NOT NULL  Giá trị NULL • Nếu một bản ghi thiếu giá trị tại một trường nào đó thì giá trị đó là NULL.  Ví dụ: hiển thị các cán bộ không biết ngoại ngữ 2-28 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  29.  EXISTS 2-29 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  30. Phép toán logic  AND, OR, NOT 2-30 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  31. Phép toán logic  AND • Ví dụ 1: hiển thị các cán bộ quê không ở Hà Nội và lương trên 4 triệu  OR • Ví dụ 2: hiển thị mã cán bộ của các cán bộ hoặc biết tiếng Pháp hoặc có trình độ B 2-31 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  32. Phép toán logic  NOT • Ví dụ: hiển thị mã cán bộ không biết tiếng Trung Quoc 2-32 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  33. Mệnh đề ORDER BY  Sắp xếp thứ tự hàng với mệnh đề ORDER BY  SELECT column_name1, column_name2, FROM table_name ORDER BY column_name1,column_name2, ASC | DESC ;  Với ASC: thứ tự tăng (mặc định) • DESC: thứ tự giảm  Hiển thị thông tin của bảng cán bộ với cột lương giảm dần 2-33 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  34. Hàm đơn Single – Row Functions  Mô tả nhiều loại hàm khác nhau có sẵn trong SQL  Các hàm này chỉ thao tác trên từng hàng và trả về kết quả theo từng hàng.  Nhận nhiều đối số và trả về đơn trị  Có thể hiệu chỉnh kiểu dữ liệu  Có thể lồng nhau 2-34 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  35. Hàm đơn  Các loại hàm đơn: • Character • Number • Date • Conversion 2-35 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  36. Một hàm phục vụ cho truy vấn Chuỗi Số Thời gian Chuyển đổi Rẽ Gộp nhánh LENGTH FLOOR, ADD_MONTHS CAST CASE MIN, CEIL, MAX ROUND LOWER, MOD SYSDATE TO_CHAR DECODE COUNT UPPER LPAD, SQRT EXTRACT TO_DATE AVG RPAD LTRIM, MONTHS_BETWEEN TO_NUMBER SUM RTRIM, TRIM SUBSTR SIGN 2-36 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  37. Hàm Character  Nhận dữ liệu character là input và có thể trả về giá trị character hoặc number Tên hàm Ý nghĩa LOWER(string_value) Chuyển chuỗi ký tự string_value sang chữ thường UPPER (string_value) Chuyển chuỗi ký tự string_value sang chữ hoa INITCAP (string_value) Chuyển ký tự đầu tiên sang chữ hoa CONCAT(string1,string2) Nối string1 với string2  Ví dụ 2-37 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  38. Hàm Character Tên hàm Ý nghĩa SUBSTR (string_value, m, n) Returns 'n' number of characters from 'string_value' starting from the 'm' position. LENGTH (string_value) / Number of characters/bytes in 'string_value' in LENGTHB(string_value) returned. TRIM (trim_text FROM All occurrences of 'trim_text' from the left and string_value) right of 'string_value' , 'trim_text' can also be only one character long . LTRIM(string_value, All occurrences of 'trim_text' is removed from trim_text) the left of 'string_value'. RTRIM (string_value, All occurrences of 'trim_text' is removed from trim_text) the right of 'string_value' LPAD (string_value, n, Returns 'string_value' left-padded with pad_value) 'pad_value' . The length of the whole string will be of 'n' characters. RPAD (string_value, n, Returns 'string_value' right-padded with pad_value) 'pad_value' . The length of the whole string will be of 'n' characters. 2-38 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  39. Tên hàm Ví dụ Giá trị trả về LOWER(string_value) LOWER('Good Morning') good morning UPPER(string_value) UPPER('Good Morning') GOOD MORNING INITCAP(string_value) INITCAP('GOOD MORNING') Good Morning LTRIM(string_value, LTRIM ('Good Morning', 'Good‟) Morning trim_text) RTRIM (string_value, RTRIM ('Good Morning', ' Good trim_text) Morning') TRIM (trim_text FROM TRIM ('o' FROM 'Good Morning') Gd Mrning string_value) SUBSTR SUBSTR ('Good Morning', 6, 7) Morning (string_value, m, n) LENGTH LENGTH ('Good Morning') 12 (string_value) LPAD (string_value, n, LPAD ('Good', 6, '*') Good pad_value) RPAD (string_value, n, Good RPAD ('Good', 6, '*') pad_value) 2-39 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  40. Ví dụ các hàm ký tự  Hàm TRIM 2-40 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  41. Ví dụ các hàm ký tự  Hàm LTRIM 2-41 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  42. Ví dụ các hàm ký tự  Hàm RTRIM 2-42 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  43. Hàm Number Tên hàm Mô tả ABS (x) Absolute value of the number 'x' CEIL (x) Integer value that is Greater than or equal to the number 'x' FLOOR (x) Integer value that is Less than or equal to the number 'x' TRUNC (x, y) Truncates value of number 'x' up to 'y' decimal places ROUND (x, y) Rounded off value of the number 'x' up to the number 'y' decimal places 2-43 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  44. Hàm Number  Ví dụ 2-44 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  45. Hàm Number  Hàm SIGN 2-45 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  46. Hàm DATE  Tất cả hàm Date trả về giá trị kiểu Date ngoại trừ hàm MONTHS_BETWEEN (trả về numeric) Tên hàm Mô tả MONTHS_BETWEEN (x1, x2) Returns the number of months between dates x1 and x2. ADD_MONTHS (date, n) Returns the number of months between dates x1 and x2. ROUND (x, date_format) Returns the date 'x' rounded off to the nearest century, year, month, date, hour, minute, or second as specified by the 'date_format'. TRUNC (x, date_format) Returns the date 'x' lesser than or equal to the nearest century, year, month, date, hour, minute, or second as specified by the 'date_format'. 2-46 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  47. Hàm DATE Tên hàm Mô tả NEXT_DAY (x, week_day) Returns the next date of the 'week_day' on or after the date 'x' occurs. LAST_DAY (x) It is used to determine the number of days remaining in a month from the date 'x' specified. SYSDATE Returns the systems current date (host) CURRENT_DATE Returns the current date (client) EXTRACT( c FROM date) Returns and extracts a value c from a date or interval value. 2-47 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  48. Hàm DATE  Ví dụ 2-48 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  49. Hàm DATE  Ví dụ 2-49 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  50. Hàm DATE  Ví dụ 2013 9 2 2013 9 2-50 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  51. Hàm Conversion  SQL cung cấp hàm chuyển giá trị từ kiểu dữ liệu này sang kiểu dữ liệu khác Tên hàm Mô tả TO_CHAR (x [,y]) Converts Numeric and Date values to a character string value. TO_DATE (x [, date_format]) Converts a valid Numeric and Character values to a Date value. Date is formatted to the format specified by 'date_format'. TO_NUMBER(char) Converts a string to a number NVL(expr1, expr2) Converts a NULL to a vaild CAST(x AS type) Converts x to a compatible database type specified in type 2-51 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  52. Hàm Conversion  Ví dụ 1: hiển thị thông tin về đề tài có ngày hết hạn là 20/11/2013  Ví dụ 2: hiển thị thông tin về đề tài với ngày nhận đề tài (16/1/2012) có dạng Sixteen of January 2012 2-52 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  53. Hàm Conversion  Hàm CAST 2-53 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  54. SQL GROUP Functions  Hàm Group • Cho phép thao tác trên một nhóm các bản ghi và cho kết quả ứng với từng nhóm đó • MAX, MIN, AVG, SUM, COUNT, DISTINCT 2-54 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  55. SQL GROUP Functions  Một số chú ý • Các hàm Group bỏ qua giá trị NULL của cột • Hàm COUNT(*) đếm cả giá trị NULL, nếu dùng COUNT(expr) đếm giá trị khác NULL • Hàm MIN, MAX dùng với mọi kiểu dữ liệu • Dùng hàm NVL giúp hàm group tính giá trị NULL 2-55 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  56. SQL GROUP Functions  Ví dụ minh họa • Ví dụ 1: hiển thị lương cao nhất của các cán bộ • Ví dụ 2: đếm số cán bộ biết tiếng anh • Ví dụ 3: có bao nhiêu loại tiếng nước ngoài? 2-56 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  57. SQL GROUP Functions  Ví dụ 4: Ngày nhận đề tài sớm nhất? 2-57 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  58. SQL GROUP Functions  Ví dụ 5: Tính lương trung bình của các cán bộ • Trường hợp 1: trung bình lương các cán bộ nhận được lương • Trường hợp 2: trung bình lương các tất cả các cán bộ 2-58 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  59. SQL GROUP BY Clause  Mệnh đề GROUP BY • Dùng để chia các bản ghi thành từng nhóm. Sau đó dùng hàm Group để tính thông tin tổng hợp của từng nhóm • Không được dùng bí danh cột trong mệnh đề Group by • Cột GROUP BY không nhất thiết phải có trong SELECT • Tất cả các trường trong SELECT mà không có trong HÀM GROUP thì PHẢI có trong GROUP BY • Mệnh đề WHERE có thể loại bỏ trước các bản ghi trước khi chia chúng thành nhóm 2-59 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  60. SQL GROUP BY Clause  Ví dụ: hiển thị tên ngoại ngữ và số người biết tương ứng? 2-60 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  61. SQL GROUP BY Clause  GROUP BY trên nhiều cột : GROUP BY c1, c2 • Đầu tiên các bản ghi được nhóm theo c1 • Trong từng nhóm theo c1, được phân nhóm theo c2  Ví dụ: Hiển thị thông tin trên bảng canbo: phân nhóm theo mã đề tài, sau đó phân nhóm tiếp theo quê quán 2-61 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  62. SQL GROUP BY Clause  Ví dụ: trường tencb không có trong GROUP BY 2-62 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  63. SQL HAVING Clause  SELECT column_name, group_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING group_function(column_name) operator value;  Dùng mệnh đề HAVING để giới hạn các nhóm  Ví dụ: hiển thị lương trung bình lớn hơn 3 triệu theo vùng miền 2-63 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  64. Hàm rẽ nhánh  Hàm DECODE • expression is the value to compare. • search is the value that is compared against expression. • result is the value returned, if expression is equal to search. • default is optional. If no matches are found, the DECODE function will return default. If default is omitted, then the DECODE function will return null (if no matches are found). 2-64 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  65. Hàm rẽ nhánh  Hàm DECODE • Ví dụ 20 9 20 null 50 2-65 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  66. Hàm rẽ nhánh  Hàm CASE  Chú ý: Nếu không có ELSE thì hàm CASE sẽ trả về giá trị NULL 2-66 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  67. Hàm rẽ nhánh  Ví dụ 2-67 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  68. Hàm rẽ nhánh  Ví dụ: so sánh giá trị 20 và 10 2-68 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  69. Lấy dữ liệu từ nhiều bảng  Sử dụng phép nối • INNER JOIN • NATURAL JOIN • OUTER JOIN – LEFT OUTER JOIN – RIGHT OUTER JOIN – FULL OUTER JOIN 2-69 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  70. Lấy dữ liệu từ nhiều bảng  Cho csdl gồm 2 bảng • Tinh(matinh, tentinh) • Sinhvien(masv, tensv, matinh) 2-70 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  71. Lấy dữ liệu từ nhiều bảng  INNER JOIN  Ví dụ: 2-71 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  72. Lấy dữ liệu từ nhiều bảng  NATURAL JOIN 2-72 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  73. Lấy dữ liệu từ nhiều bảng  NATURAL JOIN 2-73 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  74. Lấy dữ liệu từ nhiều bảng  OUTER JOIN: LEFT JOIN  Ví dụ 2-74 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  75. Lấy dữ liệu từ nhiều bảng  OUTER JOIN: RIGHT JOIN  Ví dụ 2-75 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  76. Lấy dữ liệu từ nhiều bảng  OUTER JOIN: FULL JOIN  Ví dụ 2-76 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  77. Lấy dữ liệu từ nhiều bảng  JOIN USING . (INNER JOIN) 2-77 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  78. Lấy dữ liệu từ nhiều bảng  JOIN USING . (INNER JOIN) • Chú ý: 2-78 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  79. Truy vấn phụ - Subqueries  Khái niệm  Ví dụ  Phân loại 2-79 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  80. Truy vấn phụ - Subqueries  Câu hỏi: Ai có lương cao hơn „Dinh Thuy Hien‟ 2-80 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  81. Truy vấn phụ - Subqueries  A subquery is a query within a query  Syntax  These subqueries can reside in Clause:  the WHERE clause  the FROM clause  the SELECT clause 2-81 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  82. Truy vấn phụ - Subqueries  The subquery can be nested inside Statement: • A SELECT • A INSERT • A UPDATE • A DELETE • A another subquery 2-82 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  83. Truy vấn phụ - Subqueries  Ví dụ 1 2-83 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  84. Truy vấn phụ - Subqueries  Chú ý khi dùng truy vấn phụ: • Câu truy vấn phụ đặt trong ngoặc đơn • Không bổ sung mệnh đề ORDER BY cho câu truy vấn phụ • Dùng toán tử đơn hàng với câu truy vấn đơn hàng – Toán tử: =, >, >=, • Dùng toán tử đa hàng với câu truy vấn đa hàng – Toán tử: IN, ALL, ANY/SOME 2-84 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  85. Truy vấn phụ - Subqueries  Chú ý 2-85 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  86. Truy vấn phụ - Subqueries  Ví dụ 2: Hiển thị thông tin của bảng canbo, với điều kiện quê không ở Hà nội và lương nhỏ hơn bất kỳ lương của cán bộ nào quê ở Hà nội? 2-86 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  87. Truy vấn phụ - Subqueries  Ví dụ hiển thị mã đề tài, tên đề tài, ngày phải nộp đề tài của bảng đề tài, với các đề tài đã được phân công cho cán bộ 2-87 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  88. Truy vấn phụ - Subqueries  EXISTS/ NOT EXISTS  Ví dụ 2-88 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  89. Truy vấn phụ - Subqueries  EXISTS/ NOT EXISTS  Ví dụ được viết như sau:? Kết quả? 2-89 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  90. Truy vấn phụ - Subqueries  Hiển thị thông tin 3 cán bộ có lương cao nhất (thấp nhất) • B1: sắp xếp bảng canbo có lương giảm dần • B2: Lấy ra 3 bản ghi đầu tiên 2-90 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  91. Null Values in a Subquery  Nếu có giá trị NULL trong tập trả về của Subquery • Cho bảng nhanvien(manv, tennv, luong, ghichu)  Dữ liệu của bảng nhanvien 2-91 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  92. Null Values in a Subquery  Câu lệnh sau trả về bao nhiêu bản ghi 2-92 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  93. SQL TUNING  Tối ưu hóa truy vấn • Chỉ SELECT những cột và những bản ghi cần thiết • Sử dụng JOIN thay vì subquery • Tránh truy vấn trên view • Gọi tên cột tường mình • Dùng CASE thay vì sử dụng nhiều truy vấn • Dùng INDEX • Dùng WHERE tốt hơn HAVING • Dùng EXISTS/NOT EXISTS tốt hơn IN/NOT IN • Hạn chế sử dụng các phép tính toán trong mệnh đề WHERE 2-93 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  94. DML  Một câu lệnh DML được thực hiện khi: • Thêm một hàng vào bảng • Thay đổi một hàng đã có trong bảng • Xóa hàng đang tồn tại trong bảng 2-94 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  95. DML – Sửa dữ liệu  Sửa dữ liệu: thay đổi hàng đã tồn tại trong bảng UPDATE Tên_bảng SET cột1 = giá_trị1, , cộtn = giá_trị_n [WHERE điều_kiện];  Tăng lương của mỗi cán bộ thêm 500000 đồng 2-95 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  96. DML – Xóa dữ liệu  Xóa dữ liệu • Xóa từng bản ghi DELETE Tên_bảng WHERE [điều_kiện]; • Xóa toàn bộ dữ liệu trong bảng TRUNCATE TABLE Tên_bảng; 2-96 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  97. Thêm mới dữ liệu  Nhập giá trị cho mọi cột trong bảng INSERT INTO Tên_bảng VALUES(gt1, gt2, )  Nhập giá trị cho một số cột trong bảng INSERT INTO Tên_bảng (cột1, cột2, ) VALUES (gt1, gt2, )  Lấy giá trị từ bảng khác INSERT INTO Tên_bảng (cột1, cột2, ) SELECT gt1, gt2, FROM WHERE 2-97 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  98. DDL  Tạo bảng  Hiệu chỉnh cấu trúc bảng  Xóa bảng  Đổi tên bảng 2-98 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  99. Bảng  Tên bảng & tên cột tuân theo quy tắc: • 1-30 kí tự • Bắt đầu bằng chữ cái • Bao gồm chữ cái, số, _, #, $ (hạn chế dùng #, $) • Không dùng những từ có sẵn trong Oracle (NUMBER, INDEX ) • Tên cột phải duy nhất trong bảng • Tên bảng phải duy nhất trong namespace 2-99 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  100. > Tạo bảng Các thao tác liên quan tới bảng Sửa bảng Xóa bảng  Ví dụ: 2-100 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  101. > Tạo bảng Các thao tác liên quan tới cấu trúc bảng Sửa bảng Xóa bảng  Bảng tạm: • Lưu trữ dữ liệu dùng riêng cho 1 session • Dữ liệu bị xóa khi kết thúc session hoặc transaction • Có thể thao tác dữ liệu, nối với với các bảng khác như bình thường  Tạo bảng tạm 2-101 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  102. Tạo bảng Các thao tác liên quan tới bảng > Sửa bảng Xóa bảng  Sửa giá trị mặc định của cột  Đổi tên bảng  Xóa cột 2-102 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  103. Tạo bảng Các thao tác liên quan tới bảng > Sửa bảng Xóa bảng  Sửa cột  Thêm cột 2-103 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  104. Tạo bảng Các thao tác liên quan tới bảng Sửa bảng > Xóa bảng  Khi xóa bảng, Oracle tiến hành: • Xóa dữ liệu • Xóa cấu trúc dữ liệu lưu trữ bảng • Xóa các trigger liên quan tới bảng • Xóa các quyền liên quan tới bảng DROP TABLE hr.employees PURGE;  Một số tùy chọn cho câu lệnh xóa bảng • PURGE: không cho phép flashback • CASCADE CONSTRAINTS: xóa mọi ràng buộc dữ liệu có liên quan 2-104 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  105. Constraint  Đảm bảo tính toàn vẹn của dữ liệu  Có thể tạo constraint lúc tạo bảng hoặc sau khi tạo bảng 2-105 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  106. Các loại constraint  NOT NULL  UNIQUE • Không cho phép nhập giá trị giống nhau • Oracle tự động tạo unique index cho cột có ràng buộc UNIQUE  PRIMARY KEY • Có thể tạo khóa chính cho 1 hoặc nhiều cột • Oracle tự động tạo unique index cho cột làm khóa chính  FOREIGN KEY • Thiết lập mối quan hệ của 1 bảng với bảng khác  CHECK • Kiểm tra giá trị của một cột thỏa mãn điều kiện cho trước 2-106 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  107. Khai báo constraint - 1  NOT NULL contraint  UNIQUE contraint 2-107 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  108. Khai báo constraint - 2  PRIMARY KEY constraint 2-108 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  109. Khai báo constraint - 3  FOREIGN KEY constraint • Chú ý: Không được phép tạo khóa ngoại cho những cột có kiểu dữ liệu: CLOB, NCLOB, BLOB, LONG, LONG RAW, TIMESTAMP WITH TIMEZONE  Ví dụ: 2-109 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  110. Khai báo constraint - 4  CHECK constraint • Kiểm tra giá trị của một cột có thỏa mãn điều kiện cho trước hay không  Ví dụ: 2-110 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  111. Sửa constraint  Xóa  Đổi tên  Vô hiệu hóa (disable) 2-111 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  112. Sequence  Là một đối tượng trong schema dùng để tự động sinh ra các số nguyên theo thứ tự nào đó(thường dùng cho khóa chính)  Đặc điểm • Mỗi sequence có 1 tên xác định • Không gắn với 1 cột hay 1 bảng nào • Có thể tạo ra số nguyên theo thứ tự tăng hoặc giảm dần đều • Khoảng cách giữa 2 số nguyên do người dùng tùy đặt • Có thể quay vòng nếu giá trị sinh ra đã đạt ngưỡng 2-112 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  113. Sequence  Các thuộc tính: • START WITH • INCREMENT BY • MAXVALUE/NOMAXVALUE • MINVALUE/NOMINVALUE • CACHE/NOCACHE • CYCLE/NOCYCLE 1 3 5 7 9 11 13 15 2-113 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  114. Quản lý sequence  Sửa sequence • Có thể sửa các thuộc tính: INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, CACHE • Không thể sửa: START WITH • Ví dụ:  Xóa sequence  Sử dụng sequence 2-114 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  115. VIEW  Biểu diễn dữ liệu từ một hoặc nhiều bảng bảng ảo  Câu lệnh truy vấn lưu ở Data Dictionary, dữ liệu vẫn để ở bảng gốc  Truy vấn dữ liệu trên view như trên các bảng thông thường  Cú pháp: CREATE VIEW tên_view AS câu_truy_vấn; 2-115 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  116. VIEW  Có thể thực hiện các thao tác DML, nếu view: • View không chứa các mệnh đề: DISTINCT, GROUP BY, START WITH, CONNECT BY, ROWNUM • View không chứa các phép toán tập quan hệ đại số: UNION, UNION ALL, INTERSECT, MINUS • View không chứa subquery • Câu lệnh DML tác động lên 1 bảng gốc 2-116 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  117. INDEX  Là cấu trúc dữ liệu tùy chọn, xây dựng cho một bảng xác định Tăng tốc độ truy vấn dữ liệu  Có thể tạo index cho nhiều cột hoặc một cột có thể tạo nhiều index  Có 2 loại index: • B-tree index (mặc định) – Sắp xếp giá trị khóa & ROWID dưới dạng B-tree – Phù hợp với những cột có ít giá trị giống nhau – Hỗ trợ “row locking” – Có 2 loại: unique và non-unique • Bitmap – Khóa và ROWID lưu dưới dạng bitmap – Phù hợp với những cột có nhiều giá trị giống nhau – Không hỗ trợ “row-locking” 2-117 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  118. B-Tree Index Index entry Root Branch Index entry header Leaf Key column length Key column value ROWID 2-118 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  119. Bitmap Index Table File 3 Block 10 Block 11 Index Block 12 Start End Key ROWID ROWID Bitmap 2-119 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  120. INDEX  Tạo index  Xóa index  Quản lý index 2-120 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  121. Synonym  Bí danh cho mọi đối tượng trong Oracle  Ưu điểm: • Đơn giản cho câu lệnh SQL • Không tốn không gian lưu trữ • Tăng tính bảo mật  Ví dụ: 2-121 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
  122. 2-122 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng