Bài giảng SQL (Structured Query Language)

SQL (Structured Query Language) 1 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội CSDL quan hệ (relational database)  Cách biểu diễn CSDL bằng ngôn ngữ hình thức cho thấy cơ sở lý thuyết của các ngôn ngữ truy vấn  Các sản phẩm trong thực tế cần tới những ngôn ngữ thân thiện hơn  SQL là ngôn ngữ phổ biến nhất và có ảnh hưởng lớn nhất cho tới nay  Ngoài ra còn có nhiều ngôn ngữ khác 2 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Giới thiệu  Lịch sử:  SQL được IBM phát triển từ ngôn ngữ Sequel từ những năm 1970  Năm 1986, ANSI và ISO thông qua chuẩn SQL-86  Mới nhất: SQL:2008  Mặc dù viết tắt từ “structured query language”, nhưng SQL bao gồm cả ngôn ngữ mô tả (DDL) và thao tác (DML) 3 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Sơ lược  Thành phần cơ bản của một CSDL quan hệ là các quan hệ  SQL cho phép định nghĩa các quan hệ trong CSDL, và các thuộc tính của các quan hệ  Mỗi thuộc tính có tên và kiểu  Có thể xác định mỗi thuộc tính có thể có giá trị null hay không  Kết nối:  Để bắt đầu làm việc, người dùng cần thiết lập kết nối với DBMS  Thường phải đăng nhập bằng một tên người dùng/mật khẩu  Câu lệnh:  Người dùng thao tác với DBMS bằng các câu lệnh SQL  Mỗi câu lệnh có một nhiệm vụ xác định, thường phân tách nhau bằng dấu “;”  Đặc điểm:  SQL không phân biệt chữ hoa/thường với các từ khoá và các tên  Các tên trong SQL chỉ bao gồm các ký tự Latin, chữ số và _, @, #, $  Nếu tên trùng với các từ khoá, dùng ký hiệu `…` 4 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Ví dụ với MySQL 5 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Các kiểu giá trị  SQL có các kiểu dữ liệu cơ bản sau: 6 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Tên Kiểu char(n), character(n) chuỗi ký tự có độ dài cố định n (giá trị chuỗi trong dấu '...') varchar(n), character varying(n) chuỗi ký tự có độ dài thay đổi với độ dài max là n (giá trị chuỗi trong dấu '...') int, integer số nguyên (khoảng giá trị phụ thuộc máy) smallint số nguyên nhỏ (khoảng giá trị phụ thuộc máy) numeric(p, d) số dấu phảy tĩnh với p chữ số (tính cả dấu) và d chữ số thập phân real, double precision số dấu phảy động (khoảng giá trị phụ thuộc máy) float(n) số dấu phảy động với độ chính xác thấp nhất n chữ số date ngày tháng năm. VD: '2012-05-13' time thời gian (giờ, phút, giây). VD: '19:04:23' timestamp tổng hợp của date và time. VD: '2012-05-13 19:04:23' Tạo quan hệ: create table  Dùng câu lệnh “create table…” để định nghĩa một quan hệ mới  Cú pháp cơ bản: create table tên-quan-hệ ( thuộc-tính-1 kiểu, thuộc-tính-2 kiểu, ... các-ràng-buộc);  VD: create table Member ( id integer, name varchar(50), password varchar(50), regdate date); 7 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Ràng buộc khoá chính  Định nghĩa khoá chính cho quan hệ bằng ràng buộc “primary key(khoá)” create table Member ( id integer, name varchar(50), password varchar(50), regdate date, primary key (id));  Khoá chính với nhiều thuộc tính: create table Address ( road varchar(50), number int, primary key (road, number));  Nếu khoá chính chỉ có một thuộc tính, có thể dùng: create table Member ( id integer primary key, name varchar(50), password varchar(50), regdate date); 8 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Ràng buộc khoá thường  Tương tự như khoá chính, nhưng dùng ràng buộc “unique (khoá)” create table Address ( id integer not null, road varchar(50), number int, primary key (id), unique (road, number)); 9 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Giá trị null  Một thuộc tính mặc định sẽ có thể có giá trị null  Nếu muốn thuộc tính đó không thể lấy giá trị null, thêm ràng buộc “not null” vào sau khai báo của thuộc tính  VD: create table Member ( id integer not null primary key, name varchar(50) not null, password varchar(50) not null, regdate date); 10 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Ràng buộc tổng quát  Dùng ràng buộc “check (điều-kiện)” nếu muốn các bộ giá trị trong quan hệ phải luôn thoả mãn các điều kiện mong muốn  VD: create table Student ( id integer not null, name varchar(50), class varchar(10), birthday date, check (name is not null), check (class in ('A','B','C')), check (birthday is not null and birthday < '2000-01-01')); 11 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Xoá quan hệ  Để xoá một quan hệ, dùng câu lệnh: drop table tên-quan-hệ  VD:  drop table Member;  drop table Student;  Nếu chỉ muốn xoá các giá trị, mà vẫn giữ lại định nghĩa, thì dùng câu lệnh: delete from tên-quan-hệ  VD:  delete from Member;  delete from Student; 12 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Thêm dữ liệu vào quan hệ  Dùng câu lệnh “insert into …” để thêm dữ liệu  Cú pháp: insert into tên-quan-hệ values(các-giá-trị);  Các giá trị liệt kê theo đúng trình tự khi khai báo quan hệ  VD: insert into Member values(10, 'bill', '1234', '2005-03-22'); insert into Student values(5, 'Tran Phi Nghia', 'A', '1993-05-12');  Nếu không muốn phải liệt kê các giá trị theo thứ tự, dùng cú pháp: insert into tên-quan-hệ(các-thuộc-tính) values(các-giá-trị);  VD: insert into Member(regdate, name, password, id) values('2005-03-22', 'bill', '1234', 10); 13 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Thêm nhiều dữ liệu cùng một lúc  Một câu lệnh insert có thể được dùng để thêm nhiều dữ liệu một lần  VD: insert into Member values (10, 'bill', '1234', '2005-03-22'), (11, 'lonely', 'smile', '2006-02-11'), (12, 'brokenheart', 'abcd', '2010-01-02'); tương đương với: insert into Member values (10, 'bill', '1234', '2005-03-22'), insert into Member values (11, 'lonely', 'smile', '2006-02-11'), insert into Member values (12, 'brokenheart', 'abcd', '2010-01-02'); 14 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Giá trị mặc định  Có thể khai báo giá trị mặc định cho mỗi thuộc tính  Giá trị mặc định là giá trị của một trường khi không xác định giá trị của nó trong câu lệnh insert  VD: create table Student ( id integer not null primary key, name varchar(50), class varchar(10) default 'A', birthday date); insert into Student(id, name, birthday) values (5, 'Tran Phi Nghia', '1993-05-12'); 15 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Tìm dữ liệu  Sử dụng câu lệnh: select các-thuộc-tính from các-quan-hệ [where điều-kiện];  Gồm 3 mệnh đề: select, from, where  Kết quả là giá trị các thuộc tính trong các quan hệ thoả mãn điều kiện yêu cầu  Mệnh đề where có thể lược bớt nếu muốn lấy toàn bộ kết quả (không có điều kiện lựa chọn)  Câu lệnh trên tương đương với biểu thức: Πcác-thuộc-tính(σđiều-kiện(r1 × r2 × … × rn))  VD: select stdnum, Student.name, Teacher.name from Student, Teacher where Student.class = Teacher.class; 16 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Mệnh đề select và from  select: liệt kê các thuộc tính cần lấy  Dùng “*” nếu muốn lấy tất cả  Dùng cú pháp “tên-quan-hệ.tên-thuộc-tính” nếu nhiều quan hệ có thuộc tính cùng tên  Có thể dùng các phép toán trước khi trả về kết quả  from: liệt kê các quan hệ cần dùng  VD:  select name from Student;  select * from Student;  select Student.name, Teacher.name from Student, Teacher;  select name, 800*level from Employee;  select length(first_name)+length(last_name) from Staff; 17 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Mệnh đề where…  Điều kiện của phép chọn  Là một biểu thức logic, nếu kết quả là đúng thì bộ giá trị được chọn, sai thì không được chọn  Có thể sử dụng các phép toán, hàm,… và các phép liên hệ and, or, not,…  VD:  select name from Student where regdate > '2011-01-01';  select * from Student, Teacher where Student.class = Teacher.class;  select * from Plot where area<10 and city in ('Hanoi', 'Haiphong'); 18 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Đổi tên  Các quan hệ và thuộc tính trong câu lệnh có thể được đổi tên cho tiện sử dụng bằng toán tử “as”  VD:  select e.name from Employee as e;  select level*800 as salary from Employee;  select s.name as sname, t.name as tname from Student as s, Teacher as t;  Toán tử “as” có thể được lược bỏ và ngầm hiểu:  select s.name sname, t.name tname from Student s, Teacher t; 19 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Lọc dữ liệu trùng lặp  Kết quả của câu lệnh select có thể có nhiều kết quả trùng nhau  Thêm từ khoá “distinct” nếu muốn loại bỏ các kết quả trùng lặp  VD:  select distinct s1.name from Student s1, Student s2 where s1.note > s2.note; 20 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Sắp xếp kết quả  Theo mặc định, dữ liệu ra của câu lệnh select sẽ có thứ tự bất kỳ  Thêm mệnh đề “order by…” để sắp xếp lại theo thứ tự mong muốn: order by các-biểu-thức [asc/desc]  Kết quả sẽ được sắp xếp theo thứ tự tăng dần của kết quả biểu thức theo mặc định hoặc dùng “asc”, nếu muốn sắp xếp theo thứ tự giảm dần, dùng “desc”  Có thể dùng nhiều biểu thức, biểu thức đứng trước sẽ được ưu tiên hơn  VD:  select * from Student order by name;  select * from Student order by name asc;  select * from Student order by name desc;  select * from Student order by name, student_number, note;  select * from Student order by name asc, note desc; 21 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội Bài tập 1. Tạo các quan hệ cho CSDL công ty, nhân viên, chi nhánh, dự án,… 2. Thêm dữ liệu vào các quan hệ 3. Tìm tên các nhân viên của chi nhánh Hà Nội làm việc cho dự án A, xếp theo tên nhân viên 4. Tìm các dự án mà chi nhánh Hà Nội có tham gia, xếp theo thời gian mới nhất trước 5. Tạo các quan hệ cho CSDL nhạc sĩ, ca sĩ, bài hát,… 6. Thêm dữ liệu vào các quan hệ 7. Tìm các bài hát của Trịnh Công Sơn được Mỹ Linh hát 8. Tìm các ca sĩ đã hát các bài hát của Trịnh Công Sơn trẻ hơn 30 tuổi, sắp xếp theo tên ca sĩ 22 EE4509, EE6133 – HK2 2011/2012 TS. Đào Trung Kiên – ĐH Bách khoa Hà Nội