Bài giảng Kỹ thuật phần mềm chương 4: SQL và MS. Access

KỸ THUẬT PHẦN MỀM Chương IV: SQL & MS. ACCESS TRƯỜNG ĐẠI HỌC BÁCH KHOA HÀ NỘI Khoa Điện tử Viễn Thông – Bộ môn Điện tử Tin học Chương IV. SQL & MS. Access 4.1 SQL 1. Giới thiệu 2. Phân loại SQL 3. Các lệnh SQL A) Lệnh tạo bảng B) Lệnh thêm dữ liệu C) Lệnh thay đổi dữ liệu 4.2 MS. Access 4.1 SQL 1. Giới thiệu SQL-Structure Query Language Ngôn ngữ truy xuất CSDL quan hệ Là phương tiện được sử dụng để trao đổi với DBMS Câu lệnh giống ngôn ngữ tiếng Anh (dễ đọc, dễ hiểu hơn tiếng Anh). 4.1 SQL Là một ngôn ngữ phi thủ tục =>NSD không cần phải quan tâm đến việc dữ liệu được lưu trữ thật sự như thế nào, ở đâu và có thể lấy ra bằng cách nào. Cho phép truy vấn và khai thác dữ liệu một cách linh hoạt 4.1 SQL 2 cách thực hiện câu lệnh SQL: Tương tác (Dòng lệnh) SQL nhúng. 4.1 SQL * Lịch sử SQL IBM’lab, San Jose, California – 1970: phát triển một hệ quản trị CSDL quan hệ thực nghiệm mà có thể dần dần tạo ra một sản phẩm thương mại. Một dự án được bắt đầu từ năm 1974 – Hệ thống R Một ngôn ngữ được gọi là Sequel (Structure English QUEry Language) được chọn là ngôn ngữ CSDL quan hệ cho hệ thống R. Sequel đã được viết gọn lại thành SQL. 4.1 SQL Hệ thống R: Giai đoạn 1: Nghiên cứu phát triển một phiên bản cơ bản của các hệ QTCSDL quan hệ. 1975, Hệ QTCSDL cho 1 người sử dụng dựa trên mô hình quan hệ. 1979: Kết thúc dự án 1981: Phiên bản chính thức đầu tiên của hệ quản trị CSDL quan hệ chính thức xuất hiện trên thị trường như là một sản phẩm thương mại: SQL/DS chạy trên môi trường hệ điều hành DOS/VSE. 1983: DB2 ra đời và phát triển nhanh chóng. 4.1 SQL Oracle ANSI 4.1 SQL 2. Phân loại SQL DDL – Data Definition Language Làm việc với cấu trúc CSDL DML – Data Manipulation Language Làm việc với dữ liệu thực sự được lưu trữ DCL – Data Control Language 4.1 SQL 3. Các lệnh SQL a. Lệnh “CREATE TABLE” – Tạo bảng Ví dụ CSDL gồm 3 bảng: Tạo bảng CARS Tên bảng: CARS Các cột: Tên + kiểu dữ liệu (kích thước) 4.1 SQL CREATE TABLE CARS ( MD_NUM INTEGER, MD_NAME CHAR(10), STYLE CHAR(6), YEAR INTEGER); 4.1 SQL Cú pháp lệnh CREATE TABLE CREATE TABLE ( Column1 DataType [(width [,dec.])] [NOT NULL | UNIQUE |INDEX], Column2 DataType [(width [,dec.])] [NOT NULL | UNIQUE |INDEX], …….. Columnn DataType [ (width [,dec.])] [NOT NULL | UNIQUE |INDEX] ); 4.1 SQL Lệnh “CREATE TABLE” Từ khóa: CREATE TABLE Tên bảng: Danh sách cột: , Bảng phải chứa ít nhất 1 cột Tên cột là duy nhất trong mỗi bảng Thứ tự cột sẽ là thứ tự mặc định khi truy vấn Bảng sau khi tạo là bảng rỗng 4.1 SQL Kiểu dữ liệu ANSI CHAR VCHAR NUMERIC DECIMAL SMALLINT INTEGER FLOAT DATE Kiểu riêng của DBMS Money Datetime Boolean 4.1 SQL Tính chất cột: UNIQUE Đảm bảo giá trị của cột là duy nhất trên tất cả các hàng dữ liệu của bảng INDEX Không thuộc tiêu chuẩn ANSI Có trong hầu hết các hệ QTCSDL thương mại Tăng tốc độ truy xuất dữ liệu NOT NULL Không cho phép giá trị của cột rỗng tại bất kỳ hàng nào Thường dùng với khóa chính 4.1 SQL PRIMARY KEY CREATE TABLE ( … PRIMARY KEY (columnname) ); FOREIGN KEY CREATE TABLE ( … FOREIGN KEY (columnname) REFERENCES ); DEFAULT CREATE TABLE ( … [DEFAULT ] ); 4.1 SQL Tương tự: SPECS, STOCK CREATE TABLE SPECS ( MD_NUM INTEGER , MPG INTERGER , RADIO CHAR(3) , ENGINE CHAR(7) ); CREATE TABLE STOCK ( MD_NUM INTEGER , QTY INTEGER , PRICE INTEGER ); 4.1 SQL Thay đổi cấu trúc bảng: ALTER TABLE Thêm cột: ALTER TABLE ADD (ColumnName Datatype(width,dec) [BEFORE ], ……. ) Bớt cột: ALTER TABLE DROP (ColumnName ,… ) 4.1 SQL Xóa bảng khỏi CSDL: “DROP TABLE” Cú pháp: DROP TABLE ; Ví dụ: DROP TABLE CARS; DROP TABLE SPECS; 4.1 SQL b. Lệnh “INSERT INTO”: Thêm dữ liệu 4.1 SQL Thêm DL vào bảng: CARS 4.1 SQL Cú pháp lệnh INSERT Tên bảng cần thêm dữ liệu Tên cột chứa dữ liệu Giá trị dữ liệu cần thêm Chú ý: kiểu dữ liệu của các cột phải đúng INSERT INTO TABLE (Field1, Field2, …., Fieldn) VALUES (Value1, Value2, …, Valuen) 4.1 SQL Cú pháp rút gọn Bỏ qua phần khai báo tên trường nếu thứ tự và số lượng trường giống cấu trúc bảng. INSERT INTO TABLE VALUES (Value1, Value2, …, Valuen) 4.1 SQL Thêm thông tin vào bảng SPECS 4.1 SQL Thêm thông tin vào bảng STOCK 4.1 SQL c. Lệnh “SELECT”: Lấy thông tin từ bảng Ví dụ: lấy thông tin từ bảng CARS Kết quả: 4.1 SQL Là câu lệnh phức tạp nhất trong các lệnh SQL Cú pháp lệnh SELECT SELECT [DISTINCT] Field1, Field2, …, Fieldn FROM ,[] [WHERE ] [GROUP BY gfield1, gfield2,.. [HAVING ] ] [ORDER BY oField1, oField2 [DESC]] 4.1 SQL Có thể lấy DL từ nhiều bảng bằng cách đặt danh sách bảng sau FROM Kết quả: tích đề các của giữa các bản ghi của các bảng. Xác định điều kiện WHERE 4.1 SQL Lấy ra tất cả thông tin (giá trị của mọi cột) của bảng: SELECT * FROM …. Thứ tự cột dữ liệu trả về giống thứ tự khi tạo bảng Danh sách cột sau từ khóa SELECT: Tên cột của bảng Biểu thức tính toán Hằng số (chuỗi, số,…) Có thể đặt tên “bí danh” (alias) cho cột. 4.1 SQL SELECT DISTINCT Loại bỏ các hàng dữ liệu giống nhau Ví dụ: SELECT DISTINCT ma_lop FROM SinhVien Được dùng để kiểm tra sự xuất hiện của các giá trị của cột trong bảng mà không quan tâm đến việc giá trị đó xuất hiện bao nhiêu lần 4.1 SQL Cột tính toán: SELECT mahs, ((D1T*2+D15+DM)/4*2+THI)/3 FROM Diem => Trả về danh sách mã học sinh và điểm trung bình học kỳ của từng học sinh 4.1 SQL Lọc kết quả trả về: sử dụng mệnh đề WHERE Sau WHERE phải là một biểu thức logic mà kết quả phải là một trong 2 giá trị: true/false. Kết hợp các điều kiện: AND, OR, () Ví dụ: SELECT * FROM SinhVien WHERE TenSV=‘Nguyễn Văn A’ => Lấy ra thông tin của tất cả SV có tên là ‘Nguyễn Văn A’ 4.1 SQL Các toán tử kiểm tra giá trị: =, >=, , >, =minvalue AND colname Trả ra danh sách môn học có giá trị cột subname là ‘_VXL%’ 4.1 SQL Xắp sếp kết quả trả về: ORDER BY SELECT * FROM SinhVien ORDER BY MaLop, TenSV ASC, NgaySinh DESC => Kết quả trả về là danh sách sinh viên được sắp xếp theo mã lớp tăng dần, tên sinh viên tăng dần và ngày sinh giảm dần. 4.1 SQL Các hàm tổng hợp (ANSI): COUNT() SUM() AVG() MIN() MAX() 4.1 SQL COUNT() Đếm số lần xuất hiện của giá trị hoặc số dòng dữ liệu của bảng. Ví dụ: SELECT COUNT(Dept_no) FROM Staffs Đếm số lượng nhân viên đã được gán giá trị Dept_no SELECT COUNT(DISTINCT Dept_no) FROM Staffs Đếm số lượng mã phòng sử dụng SELECT COUNT(*) FROM Staffs Đếm số nhân viên (số bản ghi hiện có trong bảng Staffs) 4.1 SQL SUM() Tính tổng giá trị của cột Kiểu dữ liệu của cột phải là kiểu số Đối số có thể là một biểu thức mà kết quả trả về là kiểu số. Ví dụ: SELECT SUM(pay) FROM Salary Tính tổng giá trị cột pay trong bảng Salary SELECT SUM(pay+1500) FROM Salary Tính tổng giá trị cột pay+1500 của bảng Salary 4.1 SQL AVG() Tính trung bình cộng giá trị của cột Kiểu dữ liệu của cột phải là kiểu số Ví dụ: SELECT AVG(pay) FROM Salary Tính trung bình cộng giá trị cột pay trong bảng Salary 4.1 SQL MIN() Lấy giá trị nhỏ nhất trong các giá trị của cột Đối số có thể là một biểu thức Ví dụ: SELECT MIN(mark) FROM Marks Tìm giá trị điểm nhỏ nhất trong bảng Marks 4.1 SQL MAX() Lấy giá trị lớn nhất trong các giá trị của cột Đối số có thể là một biểu thức Ví dụ: SELECT MAX(mark) FROM Marks Tìm giá trị điểm lớn nhất trong bảng Marks 4.1 SQL Nhóm kết quả: GROUP BY Các hàm tổng hợp ở trên tính toán dựa trên dữ liệu của các cột trong toàn bộ bảng (grant-totals) Kết hợp các hàm tổng hợp với mệnh đề GROUP BY để tính toán kết quả theo nhóm (sub-totals). Ví dụ: SELECT MaSP, SUM(ThanhTien) FROM HoaDon GROUP BY MaSP Trả ra danh sách mã sản phẩm cùng tổng số tiền bán được của mỗi sản phẩm từ bảng HoaDon SELECT MaSV, AVG(Diem) FROM DiemThi GROUP BY MaSV Trả ra danh sách mã sinh viên cùng với điểm trung bình cộng của các môn học mà SV đã thi. 4.1 SQL Lọc nhóm: HAVING Dùng với GROUP BY và các hàm tổng hợp Chỉ lấy ra nhóm thỏa mãn điều kiện nào đó của kết quả hàm tổng hợp Ví dụ: SELECT MaSV, AVG(Diem) FROM DiemThi GROUP BY MaSV HAVING AVG(Diem)>=50 => Lấy ra danh sách sinh viên và điểm trung bình của những sinh viên có điểm trung bình >=50 4.1 SQL Liên kết bảng: Lấy dữ liệu từ nhiều bảng Xác định liên kết bằng điều kiện WHERE Ví dụ: SELECT SV.TenSV, Lop.TenLop FROM SV, Lop Trả ra tích đề các của 2 bảng SELECT SV.TenSV, Lop.TenLop FROM SV, Lop WHERE SV.MaLop = Lop.MaLop Trả ra danh sách tên SV và tên lớp SV đang theo học 4.1 SQL Liên kết bảng: INNER JOIN SELECT SV.TenSV, Lop.TenLop FROM SV INNER JOIN Lop ON SV.MaLop = Lop.MaLop 4.1 SQL Truy vấn lồng nhau: nested query; sub query Ví dụ: SELECT * FROM SanPham WHERE MaSP = (SELECT MaSP FROM HoaDon Where MaNV=‘NV1’ AND NgayHD=’25-10-2006’) Lấy ra thông tin về sản phẩm do nhân viên có mã ‘NV1’ bán vào ngày ’25-10-2006’. (Giả sử trong ngày hôm đó nhân viên ‘NV1’ chỉ bán được 1 sản phẩm) SELECT * FROM SanPham WHERE MaSP IN (SELECT MaSP FROM HoaDon Where NgayHD=’25-10-2006’) Lấy ra danh sách và thông tin về sản phẩm được bán vào ngày ’25-10-2006’ bởi tất cả nhân viên. 4.1 SQL SELECT * FROM Depts WHERE DeptCode IN (SELECT DISTINCT DeptCode FROM Staffs) Lấy ra thông tin chi tiết của các phòng có nhân viên làm việc 4.1 SQL Nối các kết quả của các câu truy vấn: UNION Các câu truy vấn phải trả về kết quả có cùng cấu trúc. Kết quả cuối cùng là hợp của tất cả kết quả của các câu truy vấn Loại bỏ các dòng dữ liệu giống nhau Các câu truy vấn không được có ORDER BY Các câu truy vấn không được dùng các hàm tổng hợp 4.1 SQL Ví dụ sử dụng UNION 4.1 SQL d. Lệnh “UPDATE” và “DELETE”: Thay đổi giá trị dữ liệu. Chú ý: Where 4.1 SQL Cú pháp lệnh UPDATE Không có WHERE => All records affected. UPDATE SET Field1 = Value1, Field2 = Value2, …. Fieldn = Valuen WHERE 4.1 SQL Cú pháp lệnh DELETE Không có WHERE => All records affected. DELETE [FROM] WHERE 4.1 SQL e. Views (Virtual tables) Nội dung lấy từ các bảng cơ bản Chỉ lưu trữ cấu trúc trên đĩa (không thực sự lưu trữ dữ liệu nào cả). Khai thác DL từ View