SQL (Structured Query Language) (tiếp theo)

SQL (Structured Query Language)

(tiếp theo)

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

Xoá dữ liệu

mãn một điều kiện mong muốn

 Dùng câu lệnh “delete from…” để xoá các dữ liệu thoả

delete from tên-quan-hệ [where điều-kiện];

 Cú pháp:

 delete from Student where regdate > '2000-01-01';  delete from Book where

 Mệnh đề where tương tự trong câu lệnh select  VD:

 delete from quan-hệ;  truncate quan-hệ;

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

 Xoá toàn bộ dữ liệu của quan hệ:

Sửa đổi dữ liệu

thuộc tính trong một quan hệ bằng giá trị mới thoả mãn một điều kiện mong muốn

 Dùng câu lệnh “update …” để cập nhật giá trị của các

update tên-quan-hệ set thuộc-tính = giá-trị,... where điều-kiện;

 Cú pháp:

 update Student set class = 'C‘ where name = 'Bill Gates';  update Book set borrowed = 1, date = now() where id = 1234;

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

 VD:

Số học

 Các phép toán: +, -, *, /, %  Hàm: abs(), sqrt(), exp(), ln(), power(),

rand(),…

 Ví dụ:

 select sqrt(5) + power(40, 5);  update Product set price = price * (1 + ln(2)) where category = 'laptop';

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

Logic

…  Ví dụ:

 select * from Product where price >= 50 and count < 10;

 Các phép toán: and, or, not  So sánh: >, <, >=, <=, =, <>, !=, between … and

 select id from Student where phone is not null;

 So sánh với null: dùng “is null” và “is not null”

 select id from Student where class in ('A', 'B');

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

 Tập hợp: in(…)

Làm việc với ngày tháng

 date, time, datetime, timestamp

 Các kiểu dữ liệu:

 curdate(), curtime(), now()

 Thời gian hiện tại:

 date_add(), time_add()  date_sub(), time_sub()

 Cộng trừ thời gian:

 year(), month(), day(), week(), hour(), minute(),

second()

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

 Trích các tham số:

Làm việc với kiểu chuỗi

 So sánh: =, !=, >, <, >=, <=  Một số hàm: lower(s), upper(s), concat(s1, s2), locate(s1, s2), substring(s, p, n),…

 Toán tử like: so sánh chuỗi theo khuôn dạng sử

dụng  %: đại diện cho một chuỗi bất kỳ  _: đại diện cho một ký tự bất kỳ

 VD:

 select * from Student where name like 'Bill%';  select * from Word where title like '__%tion';

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

Gộp nhóm

 select avg(mark) from Mark where std = 10;  Câu hỏi: tính điểm trung bình của từng học sinh

 select std, avg(mark) from Mark group by std;

 Câu hỏi: tính điểm trung bình của học sinh có id = 10

 avg(): giá trị trung bình  min(): giá trị min  max(): giá trị max  sum(): tổng  count(): số lượng

 Các hàm gộp:

lượng  select title, count(*) from Book group by title;

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

 Ví dụ khác: danh sách sách trong thư viện kèm theo số

Gộp nhóm: điều kiện trên nhóm

 Trong câu lệnh có gộp nhóm, “where…” là điều kiện đối với từng phần tử, còn “having…” là điều kiện với nhóm

 VD:

 select s.id, s.name, avg(m.mark) from Mark m, Student s where m.std = s.id and s.class = 'B' group by s.id having max(m.mark) >= 8;  Danh sách các sinh viên lớp B có điểm trung bình >= 8

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

Gộp kết quả

select … union select …

 Phép hợp trong SQL được thực hiện bằng cú pháp:

 Kết quả các phép select phải tương thích với nhau  Có thể thực hiện hợp hai hoặc nhiều phép select với nhau  Có thể dùng ngoặc (…) để cho rõ ràng  Các kết quả trùng sẽ bị loại bỏ, nếu không muốn thì dùng “union

all”  Ví dụ:

 select name, dob from Student where class = 'B' union (

select name, dob from Teacher where city = 'Hanoi');

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

 Chú ý:

Giao và loại trừ kết quả

“except” thay cho “union”

 Tương tự như phép gộp, nhưng dùng “intersect” và

 select id, name from Employee where gender = 'male' intersect (

select id, name from Employee where dob < '1980-01-01' except (

select id, name from Employee where level = 5 ));

 Ví dụ:

 Trong MySQL không hỗ trợ “intersect” và “except”, mà phải

dùng các câu lệnh lồng nhau

 MSSQL dùng “minus” thay vì “except” 11

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

 Chú ý:

Câu lệnh lồng nhau (nested queries)

 SQL cho phép sử dụng các câu lệnh con

(subqueries) để tăng tính linh hoạt trong truy vấn  Ví dụ: liệt kê các bài hát (quan hệ Song) thuộc các thể loại được sáng tác bởi các tác giả (quan hệ Author) sinh trước năm 1950  select * from Song where author_id in (

select id from Author where dob < '1950-01-01');

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

Toán tử “in”

 Dùng để kiểm tra tính thuộc tập hợp  Phủ định dùng “not in…”  Có thể được dùng cho bộ giá trị

 select * from Song where (author, category) in

( (5, 10), (4, 15), (4, 19) );

 select * from Author where (first_name, last_name) in

(select firstn, lastn from Nominated)

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

Toán tử exists

hàng  select * from Customer where not exists (

select id from Invoice where customer = Customer.id);

 Dùng để kiểm tra một tập hợp có rỗng hay không  Phủ định dùng “not exists…”  Ví dụ: liệt kê các khách hàng chưa mua máy tính ở cửa

 select * from Customer

where count (

select id from Invoice where customer = Customer.id) > 0;

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

 Tương tự:

Liên kết giữa các thuộc tính với câu lệnh con

 Các thuộc tính của câu lệnh mẹ có thể được sử

dụng trong các câu lệnh con

 VD:

 select customer from Borrower

where exists (

select * from Depositor where Depositor.customer = Borrower.customer);

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

So sánh tập hợp

 Câu hỏi: liệt kê các sinh viên của lớp A có điểm lớn

hơn ít nhất một sinh viên của lớp B  select * from Student

where class = 'A' and mark > some (

select mark from Student where class = 'B');

 Các kiểu so sánh tập hợp:

 some / any: có ít nhất một phần tử thoả mãn  all: so với tất cả

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

Câu lệnh con trong mệnh đề from

tham chiếu

 Câu lệnh con có thể được sử dụng trong mệnh đề from  Kết quả của câu lệnh con có thể được đổi tên để dễ

 select branch-name, avg-balance

from (

select branch-name, avg(balance) from account group by branch-name ) as branch-avg(branch-name, avg-balance)

where avg-balance > 1200;

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

 Ví dụ:

Biến

 SQL cho phép tạo các biến để lưu tạm thời các giá trị.

Các biến được dùng theo cú pháp: @tên

 Định nghĩa / thay đổi giá trị của biến:

 set @VAR = VALUE;  select @VAR := VALUE;

 Ví dụ:

 set @std = 20;

select @stddob:=dob

from Student where id=@std;

select @stddob;

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

Bài tập

Cho các quan hệ SinhVien, GiaoVien, DangKyHoc, Day, Lop, MonHoc. Viết câu truy vấn cho các yêu cầu sau:

Liệt kê các sinh viên có đăng ký các môn X, Y

Liệt kê các giáo viên dạy từ 5 lớp trở lên

Liệt kê các giáo viên dạy ít nhất một lớp có 30 sinh viên trở lên

Liệt kê các môn học của giáo viên G mà sinh viên A đăng ký

Cho các quan hệ Sach, TacGia, NguoiDoc, Muon. Viết câu truy vấn cho các yêu cầu sau:

Liệt kê các sách đã được mượn nhiều hơn 10 lần trong năm 2011

Liệt kê những người đọc đã mượn ít nhất 10 đầu sách khác nhau

Liệt kê các sách đang được mượn bởi một trong các người đọc ở câu trên

Liệt kê các sách của Stephen King hiện tại không có ai đang mượn

EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội

SQL (Structured Query Language) (tiếp theo)

Chủ đề:

Trực quan hóa dữ liệu