Bài giảng Cơ sở dữ liệu - Bài 5: Ngôn ngữ CSDL - SQL - Vũ Văn Định

Bài 5. Ngôn ngữ CSDL – SQL  Để người sử dụng có thể giao tiếp được với CSDL phải có một hệ thống ngôn ngữ giao tiếp bao gồm: • Ngôn ngữ định nghĩa dữ liệu . • Ngôn ngữ thao tác dữ liệu • Ngôn ngữ truy vấn dữ liệu • Ngôn ngữ quản lý dữ liệu Mỗi hệ quản trị CSDL đều có cách cài đặt các ngôn ngữ khác nhau nhưng đều dựa trên chuẩn của ngôn ngữ truy vấn có cấu trúc SQL ( Structured- Query Language) TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí I. Ngôn ngữ định nghĩa dữ liệu SQL  Là ngôn ngữ mô tả dữ liệu, cho phép người dùng định nghĩa cấu trúc CSDL, sửa đổi cấu trúc, liên kết các thành phần trong CSDL  Ngoài ra , nó còn cung cấp các khả năng khác như : định nghĩa khung nhìn, kiểm soát tính toàn vẹn dữ liệu, cấp phát quyền truy cập, TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Quy ước : – Tên tên bảng, thuộc tính đối tượng không chứa dấu cách, không trùng tên với nhau. – Các phạm trù cú pháp bắt buộc phải điền được đặt trong cặp dấu – Các thành phần tuỳ chọn được viết trong cặp dấu [ ] – Mỗi câu lệnh SQL có thể được viết trên nhiều dòng và được kết thúc bằng dấu ; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Các kiểu dữ liệu đơn giản trong SQL  Kiểu dữ liệu số: integer , float, decimal(n,p)- số thực với độ dài tối đa là n chữ số với p chữ số thập phân , number(n)- kiểu số có độ dài tối đa là n  Kiểu dữ liệu chuỗi : -varchar (n) - chuỗi có độ dài biến đổi từ 0 đến n - Char (n): xâu kí tự có độ dài cố định n ( n<=255)  Kiểu dữ liệu ngày tháng: date, time  Kiểu luận lí : boolean TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí II. Các câu lệnh tạo cấu trúc bảng 1. Lệnh tạo bảng tổng quát CREATE TABLE ( < kích thước 1>, . < kích thước n>, ); TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ : Tạo bảng SINH_VIEN có các thuộc tính: - mã sinh viên ( kiểu số có độ dài <5) - họ tên ( kiểu xâu kí tự có độ dài <25) - ngày sinh (kiểu date) - giới tính ( kiểu xâu có độ dài <3) - địa chỉ ( kiểu xâu có độ dài < 30 ) - điểm TB ( kiểu số thực có độ dài <4, trong đó có 2 chữ số sau phần thập phân) TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí CREATE TABLE SINH_VIEN ( ma_sv NUMBER(5) , ho_ten VARCHAR(25), ng_sinh DATE, gioi_tinh VARCHAR (3), đia_chi VARCHAR (30), diem_tb DECIMAL (4,2), ); TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 2. Lệnh tạo bảng với một số điều kiện ràng buộc CREATE TABLE ( , . , [ CONSTRAINT ]| NULL| NOT NULL| Primary Key ( Khoá chính ) [ Unique ( khoá )] [Foreign Key (Khoá _ ngoài) References Tên_bảng] [ Check ] ); TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Trong đó :  NOT NULL : chỉ rằng cột không nhận giá trị rỗng. Ngầm định là Null. Thuộc tính khoá ngầm định là Not Null.  COSNTRAINT : khai báo các ràng buộc toàn vẹn của CSDL.  Primary Key ( Khoá chính): Khai báo khoá chính của mảng  Unique ( Khoá ) : Khai báo các khoá khác nếu có  Foreign Key Khoá_ ngoài References Tên_ bảng : Khai báo các khoá ngoài của bảng  Check Điều_kiện_ràng_buộc : Khai báo các ràng buộc dữ liệu . TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ : Tạo bảng SINH_VIEN có: - khoá chính là thuộc tính mã sinh viên ( kiểu số) - họ tên ( kiểu xâu kí tự có độ dài <=25) không được để trống, - ngày sinh (kiểu date) - giới tính ( kiểu xâu có độ dài <=3, mặc định là 'Nam') - mã lớp là một khoá ngoài, mã lớp thuộc bảng LOP. - điểm TB ( kiểu số thực có độ dài <=4) TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Câu lệnh : CREATE TABLE SINH_VIEN ( ma_sv NUMBER(5) PRIMARY KEY, ho_ten VARCHAR(25) NOT NULL, ng_sinh DATE, gioi_tinh VARCHAR (3) DEFAULT ' Nam', ma_lop CHAR (3) , diem_tb DECIMAL (4,2), FOREIGN KEY ma_lop REFERENCES LOP ); TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Hoặc : CREATE TABLE SINH_VIEN ( ma_sv NUMBER(5) , ho_ten VARCHAR(25) NOT NULL, ng_sinh DATE, gioi_tinh VARCHAR (3) DEFAULT ' Nam', ma_lop CHAR (3) , diem_tb DECIMAL (4,2), PRIMARY KEY ( ma_sv), FOREIGN KEY ma_lop REFERENCES LOP ); TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 3. Bổ sung -xoá một cột trong bảng a. Bổ sung một cột : ALTER TABLE ADD < kích thước>; VD : Thêm cột số điện thoại vào bảng SINH_VIEN ở trên. ALTER TABLE SINH_VIEN ADD dien_thoai NUMBER(10); TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí a.Xoá một cột : ALTER TABLE DROP <tên cột> ; VD : Xoá cột điểm TB trong bảng SINH_VIEN ở trên. ALTER TABLE SINH_VIEN DROP diem_tb; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 4. Xoá một bảng khỏi CSDL DROP TABLE ; VD : Xoá bảng SINH_VIEN trong CSDL DROP TABLE SINH_VIEN; Khi đó toàn bộ thông tin về bảng SINH_VIEN( gồm cả lược đồ và các bản ghi ) đều bị xoá, khác với lệnh DELETE FROM SINH_VIEN ; chỉ xoá các bản ghi trong bảng, vẫn giữ lại cấu trúc (lược đồ ) của bảng TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí III. Các câu lệnh truy vấn 1. Truy vấn theo câu hỏi đơn giản Cấu trúc cơ sở của một biểu thức hỏi ( còn gọi là biểu thức truy vấn) SQL gồm : SELECT [ DISTINCT | ALL] {* | AS [<tên mới>],..} FROM [ ],... [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ]; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Trong đó : - Từ khoá DISTINCT : để loại bỏ sự trùng lặp ( các bộ trùng lặp chỉ giữ lại một bộ) - : là tên của một cột hoặc của biểu thức - : là tên của một bảng trong CSDL hay một khung nhìn mà ta có thể truy cập vào - GROUP BY : dùng để gộp nhóm các bộ cùng giá trị tương ứng ở các cột xuất hiện trong ds tên cột. - HAVING: dùng để lọc các nhóm thoả điều kiện - ORDER BY : quy định thứ tự trong các cột trả ra gồm : ASC ( tăng dần) và DESC ( giảm dần). Mặc định là ASC TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ 1: Tìm tên các dự án và mã các phòng quản lý dự án tương ứng SELECT ten_da, ma_p FROM DU_AN; Kết quả tra ra : P1Giáo trình điện tử P3Mạng B P2Phần mềm A Ma_pTen_da TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Chú ý : a . Muốn hiển thị tất cả các cột trong bảng, ta có thể dùng dấu * để thay thế Ví dụ 2. Cho biết thông tin về nhân viên trong công ty . C1 : SELECT ma_nv, ho_ten, ng_sinh, gioi_tinh, ma_p, luong FROM NHAN_VIEN; C2: SELECT * FROM NHAN_VIEN ; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí b. Trong bảng kết quả, nếu muốn ta có thể đặt tên mới cho cột sau từ khoá As Ví dụ3 : Cho biết tên và lương mới của mỗi nhân viên biết họ được tăng 10% . Câu lệnh : C1: SELECT ho_ten, luong + luong*0.1 FROM NHAN_VIEN; C2: SELECT ho_ten, luong+luong*0.1 AS luong_moi FROM NHAN_VIEN; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí c. SQL dùng các phép so sánh : <= ( nhỏ hơn hoặc bằng) , >=( lớn hơn hoặc bằng) , ( khác) - Có thể kết hợp nhiều biểu thức logic bằng các phép AND, OR, NOT. TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ 4:Tìm các nhân viên thuộc phòng có mã phòng là P2 và có lương >=2tr. SELECT ma_nv, ho_ten, ma_p, luong FROM NHAN_VIEN WHERE ma_p= 'P2' AND luong>=2000000; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ 5: Lập danh sách ( gồm mã sinh viên,họ tên, ngày sinh, giới tính) của các sinh viên nữ có học lực từ khá trở lên và các sinh viên nam học lực giỏi. SELECT SV#, HT, NS, GT FROM SV WHERE (GT= 'Nữ' AND HL>=6.5) OR ( GT= 'Nam' AND HL >=8.0 ); TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí d. Tìm kiếm có xử lý xâu kí tự  SQL dùng toán tử LIKE để so sánh xâu.  SQL sử dụng kí tự ' %' để thay thế cho một xâu con, dấu phân cách '_' để thay thế cho một kí tự. VD: - A%B : xâu kí tự bất kì bắt dầu bằng chữ A và kết thúc bằng chữ B - A_B : xâu gồm 3 kí tự, có kí tự thứ hai bất kì TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ 6: Cho biết mã và tên các dự án mà địa điểm có chứa từ UBND: SELECT ma_da, ten_da FROM DU_AN WHERE dia_diem_da LIKE '%UBND%'; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí e. SQL cho phép sử dụng các giá trị NULL để chỉ sự thiếu vắng thông tin về giá trị của một bộ tại một thuộc tính. Với từ khoá IS NULL, SQL cho phép kiểm tra xem một giá trị có là NULL hay không ? Ví dụ 7: Cho biết tên và mã số dự án mà cột địa điểm có giá trị là NULL. SELECT ma_da, ten_da FROM DU_AN WHERE dia_diem_da IS NULL; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí f. Truy vấn có sắp xếp thứ tự Kết quả của câu truy vấn SQL có thể được sắp theo thứ tự tăng dần (ASC ) hoặc giảm dần (DESC) bởi khoá ORDER BY. Mặc định là tăng dần. Ví dụ 8 : Liệt kê các nhân viên nữ theo thứ tự tăng dần của lương: SELECT * FROM NHAN_VIEN WHERE GT = ' Nữ' ORDER BY luong; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí  Việc sắp thứ tự có thể được thực hiện trên nhiều thuộc tính Ví dụ 9: Hiển thị các thông tin chấm công của các dự án có mã thuộc tập D1, D2, D8 theo thứ tự tăng dần của số giờ và giảm dần của mã dự án: SELECT * FROM CHAM_CONG WHERE ma_da IN ('D1', 'D2', 'D8') ORDER BY so_gio ASC, ma_da DESC; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí g. Tìm kiếm nhờ sử dụng IN và BETWEEN Ví dụ 10: Tìm những nhân viên có lương từ 2000000 đến 3000000 , sắp xếp tên theo thứ tự tăng dần. SELECT * FROM NHAN_VIEN WHERE luong BETWEEN 2000000 AND 3000000 ORDER BY ho_ten ASC; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 2. Các hàm thư viện SQL có 5 hàm kết tập được cài sẵn : - COUNT :đếm số giá trị - SUM : tính tổng các giá trị trong một cột - MAX : tính giá trị lớn nhất trong một cột - MIN : tính giá trị nhỏ nhất trong một cột - AVG : tính giá trị trung bình của một cột Chú ý : Các hàm sum và avg chỉ áp dụng cho các cột có kiểu số TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ 1: Tìm lương cao nhất, lương thấp nhất, và trung bình cộng lương toàn cơ quan SELECT MAX (luong) AS max, MIN (luong) AS min, AVG (luong) AS tb FROM NHAN_VIEN; Kết quả câu truy vấn này trả ra như sau : 235000012000003000000 tbminmax TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ 2: Cho biết số nhân viên thực hiện dự án có mã là D8 SELECT COUNT (*) FROM CHAM_CONG WHERE ma_da = 'D8'; Chú ý : - Hàm Count khi có đối số (*) có nghĩa là đếm số bản ghi thoả mãn yêu cầu mà không cần quan tâm đến bất kì cột nào . TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí - Dùng từ khoá DISTINCT ngay trước tên cột được áp dụng hàm, khi cần loại bỏ các bộ trùng nhau. Ví dụ 3: Đếm số tỉnh có sinh viên theo học SELECT COUNT (DISTINCT Que) FROM SINHVIEN; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 3.Tìm kiếm nhờ mệnh đề GROUP BY Ví dụ 1: In ra danh sách sinh viên theo từng quê của sinh viên? SELECT SV#, HT, NS FROM SV GROUP BY Que; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 4. Tìm kiếm có sử dụng mệnh đề HAVING Mệnh đề HAVING thường sử dụng cùng mệnh đề GROUP BY. Sau HAVING là biểu thức điều kiện. Biểu thức này không tác động vào toàn bảng mà chỉ tác động vào từng nhóm các bản ghi đã chỉ ra tại mệnh đề GROUP BY VÍ DỤ : Tìm mã số những sinh viên thực hiện ít nhất 2 đề tài: SELECT SV# FROM SD GROUP BY SV# HAVING COUNT (DT# ) >=2; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 5. Tìm kiếm với câu hỏi phức tạp Tìm kiếm với nhiều bảng qua việc sử dụng ánh xạ lồng nhau hoặc qua phép kết nối.  Phép kết nối : ĐK: Các miền tham gia kết nối phải có miền trị là sánh được với nhau. Tên của các cột khác nhau có thể được viết tường minh qua tên bảng theo cú pháp : Tên_ bảng. Tên_ cột Ví dụ : Với mỗi đề tài có sinh viên tham gia thực tập. Hãy cho biết tên đề tài, tên chủ nhiệm đề tài và kinh phí thực hiện đề tài đó. TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Câu lệnh : SELECT TDT, CN, KP FROM DT, SD WHERE SD.DT# = DT.DT#; Chú ý : Trong câu truy vấn có hơn một bảng, nếu tên cột là không duy nhất thì bắt buộc phải viết tên cột dạng tường minh. TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí  Ánh xạ lồng Ví dụ 1: Tìm tên những sinh viên thực tập tại Hà Nội . SELECT HT FROM SV WHERE SV# IN (SELECT SV# FROM SD WHERE NTT= ‘Hà Nội ‘); TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ 2: Tìm những sinh viên thực hiện ít nhất một đề tài nào đó: SELECT * FROM SV WHERE EXISTS ( SELECT * FROM SD WHERE SV.SV# = SD.SV#); SQL còn có thể tìm kiếm trên nhiều bảng nhờ mênh đề EXISTS ( tồn tại ) TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí  Tìm kiếm có sử dụng lường từ ANY và ALL Ví dụ 3 : Tìm tên những đề tài mà sinh viên có mã số 5 đã thực hiện: SELECT TDT FROM DT WHERE DT# = ANY ( SELECT DT# FROM SD WHERE SV# = 5); TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ 4 : Tìm tên sinh viên có điểm học lực cao hơn tất cả các sinh viên SELECT HT FROM SV WHERE HL >= ALL ( SELECT HL FROM SV ); Mệnh đề trên tương đương với : SELECT HT FROM SV WHERE HL = ( SELECT MAX (HL) FROM SV ); TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí IV. CÁC MỆNH ĐỀ CẬP NHẬT DỮ LIỆU 1. Thêm một bộ : Dạng tổng quát : INSERT INTO Tên _ bảng ( ds_tên _cột) VALUES (các_ giá_trị ) [ câu hỏi con] Có thể bổ sung vào một tập các bản ghi là kết quả xử lý của một câu hỏi nào đó. VD: Chèn vào bảng SVG các sinh viên giỏi trong bảng SV. INSERT INTO SVG SELECT * FROM SV WHERE HL >= 8.0; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 2. Xoá bản ghi Có thể xoá một hay nhiều bản ghi thoả mãn một điều kiện nào đó. Dạng tổng quát: DELETE [tên_bảng] [FROM { Tên_bảng / Tên_ view}] [WHERE Biểu_ thức _điều _kiện] Ví dụ : Xoá những sinh viên có điểm HL kém (HL<=3.5): DELETE FROM SV WHERE HL<=3.5; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 3. Sửa đổi dữ liệu Sửa đổi các giá trị của các bản ghi theo một điều kiện nào đó: Dạng tổng quát : UPDATE [ tên_bảng] SET [ tên_cột = biểu_thức,...] FROM tên _ bảng WHERE Biểu _ thức _điều _kiện Ví dụ : Sửa điểm học lực của bạn Phan Ngọc Hà thành 8.5 UPDATE SV SET HL = 8.5 WHERE HT=‘ Phan Ngọc Hà’; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 4. Tạo chỉ mục. Việc tạo chỉ mục là tạo ra một bảng lưu trữ vị trí các bản ghi dựa trên giá trị tăng dần của một ( hay một số) cột nào đó. Việc này có tác dụng làm tăng tốc độ tìm kiếm thông tin trong CSDL. Dạng tổng quát : CREATE INDEX tên_bảng_chỉ_mục ON Tên_bảng ( tên _cột [ASC | DESC]); Bỏ chỉ mục thì sử dụng mệnh đề : DROP INDEX tên_chỉ_mục; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ví dụ : Tạo chỉ mục CMHT trên cột Ho_ten của bảng sinh viên: CREATE INDEX CMHT ON SV ( HT ASC); Ví dụ: Xoá chỉ mục CMHT ra khỏi bảng SV DROP INDEX CMHT; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 5. Tạo View của người sử dụng Tạo ra một khung nhìn của người sử dụng : Dạng tổng quát : CREATE VIEW Tên_View (Danh_ sách_ tên _cột) AS mệnh_đề_Select ; Ví dụ : Tạo View DSSVG gồm những sinh viên giỏi bao gồm thông tin về họ tên, ngày sinh, giới tính của các sinh viên từ bảng SV. CREATE VIEW DSSVG ( Hoten, NS, GT) AS SELECT HT , NS, GT FROM SV WHERE HL >= 8.0; TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí 6. SQL nhúng Có thể truy cập vào CSDL từ một ngôn ngữ lập trình bậc cao nếu có SQL "nhúng" trong ngôn ngữ này. Một ngôn ngữ trong đó các câu hỏi được SQL được nhúng vào gọi là ngôn ngữ chủ, còn các cấu trúc của SQL được phép trong ngôn ngữ này làm thành SQL nhúng - Sơ đồ xử lý các chương trình có nhúng câu lệnh SQL: TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí Ngôn ngữ chủ + SQL nhúng Tiền xử lý Ngôn ngữ chủ Các lời gọi hàm Biên dịch ngôn ngữ chủ Thư viện SQL Chương trình ngôn ngữ chủ + TopTaiLieu.Com | Chia Sẻ Tài Liệu Miễn Phí