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
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:
- bai_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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- VÍ DỤ 2-9 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- EXISTS 2-29 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- > 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
- > 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 2-122 Khoa Hệ thống thông tin quản lý – Học viện Ngân Hàng