Chương 3: SQL - Cô Nguyễn Thị Thu Hiếu

Môn học Cơ sở dữ liệu GV: Nguyễn Thị Thu Hiếu LOGO Chương 3: Ngôn ngữ SQL 1. Khái quát về ngôn ngữ truy vấn có cấu trúc SQL 2. Ngôn ngữ định nghĩa dữ liệu 3. Ngôn ngữ thao tác dữ liệu 4. Bài tập áp dụng Bài toán Trường CĐN Cơ điện Hà Nội cần thiết kế CSDL để quản lý sinh viên, giáo viên; theo dõi việc học và dạy ở trường.  Mỗi SV có một họ tên, ngày sinh, giới tính và duy nhất một mã SV.  Có rất nhiều môn học. Mỗi môn học có tên môn, số đơn vị học trình và có duy nhất một mã môn. Tên của các môn học không được phép trùng nhau.  Một SV có thể đăng ký nhiều môn học và một môn học có thể được đăng ký bởi nhiều SV.  Học xong một môn SV sẽ có điểm (hệ số 10) của môn đó.  Mỗi môn học chỉ được dạy bởi một giáo viên.  Mỗi giáo viên có thể dạy nhiều môn học.  Mỗi giáo viên được quản lý bởi mã duy nhất, họ tên, giới tính. Các giáo viên có các sở thích, số điện thoại khác nhau. Mô hình ER Lược đồ CSDL  SinhVien(#MaSV,TenSV,GioiTinh,NgaySinh)  GiaoVien(#MaGV,TenGV,GioiTinh, DienThoai,SoThich)  MonHoc(#MaMH,TenMH,DVHT,@MaGV)  KetQua(#@MaSV,#@MaMH,Diem) Mô hình dữ liệu Chương 3: Ngôn ngữ SQL 1. Khái quát về ngôn ngữ truy vấn có cấu trúc SQL 2. Ngôn ngữ định nghĩa dữ liệu 3. Ngôn ngữ thao tác dữ liệu 4. Bài tập áp dụng 1. Khái quát về ngôn ngữ SQL  SQL – Structured Query Language  Được phát triển từ ngôn ngữ SEQUEL-2, thử nghiệm và cài đặt tại trung tâm nghiên cứu của hãng IBM cho hệ thống quản trị CSDL lớn điển hình là System-R.  Vừa đóng vai trò là ngôn ngữ thao tác dữ liệu (Data Manipulate Language – DML), vừa là ngôn ngữ định nghĩa dữ liệu (Data Definition Language – DDL)  Hiện nay rất nhiều sản phẩm phần mềm thương mại đều được cài đặt SQL như MS Access, MS SQL Server, Oracle,…  SQL dựa trên phép toán tập hợp và quan hệ  Kết quả của một câu lệnh SQL là một quan hệ Lệnh tạo, xóa CSDL  Tạo Cơ sở dữ liệu  Cú pháp: • CREATE DATABASE  Ví dụ: • CREATE DATABASE QuanLyDiem  Sử dụng Cơ sở dữ liệu  Cú pháp: • USE  Ví dụ: • USE QuanLyDiem  Xóa Cơ sở dữ liệu  Cú pháp: • DROP DATABASE  Ví dụ: • DROP DATABASE QuanLyDiem Chương 3: Ngôn ngữ SQL 1. Khái quát về ngôn ngữ truy vấn có cấu trúc SQL 2. Ngôn ngữ định nghĩa dữ liệu 3. Ngôn ngữ thao tác dữ liệu 4. Bài tập áp dụng 2. Ngôn ngữ định nghĩa dữ liệu 2.1. Tạo bảng dữ liệu 2.2. Sửa đổi định nghĩa bảng 2.3. Xóa bảng 2. Ngôn ngữ định nghĩa dữ liệu 2.1. Tạo bảng dữ liệu 2.2. Sửa đổi định nghĩa bảng 2.3. Xóa bảng 2.1. Tạo bảng dữ liệu Trong đó:  tên_bảng: Tên của bảng cần tạo. Quy tắc định danh: là xâu ký tự bất kỳ, không chứa ký tự đặc biệt, không chứa ký tự trống.  tên_cột: Tên cột cần định nghĩa. Tuân theo quy tắc định danh. Trong một bảng tên cột là duy nhất. Thứ tự các cột không quan trọng 2.1. Tạo bảng dữ liệu (tiếp)  thuộc_tính_cột: Bao gồm:  Kiểu dữ liệu của cột. Đây là thuộc tính bắt buộc phải có đối với mỗi cột. Kiểu dữ liệu có thể dùng một số loại dữ liệu như sau: 2.1. Tạo bảng dữ liệu (tiếp) 2.1. Tạo bảng dữ liệu (tiếp)  Giá trị mặc định của cột: Ràng buộc DEFAULT.  Cột có tính chất IDENTITY hay không? Sử dụng với những cột kiểu số. Giá trị của cột được tự động tăng mỗi khi có bản ghi mới được bổ sung.  Cột có chấp nhận giá trị NULL hay không?  các_ràng_buộc:  Ràng buộc PRIMARY KEY: Khai báo khoá chính của bảng  Ràng buộc UNIQUE: Khai báo các khoá khác nếu có  Ràng buộc FOREIGN KEY: Khai báo các khoá ngoại của bảng  Ràng buộc CHECK: Khai báo các ràng buộc dữ liệu. 2.1. Tạo bảng dữ liệu (tiếp) Ràng buộc PRIMARY KEY:  Cú pháp:  Ví dụ 2.1: Tạo bảng Sinh Viên: Create table SinhVien( MaSV nvarchar(10) NOT NULL, TenSV nvarchar(50) NOT NULL, GioiTinh Bit NOT NULL, NgaySinh datetime NULL, Primary Key (MaSV) ) 2.1. Tạo bảng dữ liệu (tiếp) Ràng buộc DEFAULT:  Cú pháp:  Ví dụ 2.2: a. Tạo bảng Giáo Viên với giá trị mặc định cho cột Giới tính là 1: Create table GiaoVien( MaGV nvarchar(10) NOT NULL, TenGV nvarchar(50) NOT NULL, GioiTinh Bit Default(1), DienThoai int NULL, SoThich nvarchar(100) NULL, Primary Key (MaGV) ) b. Thêm giá trị mặc định cho cột Giới Tính trong bảng Sinh Viên là 1: Alter table SinhVien Add Constraint DF_SV_GioiTinh Default(1) for GioiTinh 2.1. Tạo bảng dữ liệu (tiếp) Ràng buộc FOREIGN KEY:  Cú pháp:  Ví dụ 2.3: Tạo bảng Môn Học: Create table MonHoc( MaMH nvarchar(10) NOT NULL, TenMH nvarchar(50) NOT NULL, DVHT int NOT NULL, MaGV nvarchar(10) NOT NULL, Primary Key (MaMH), Foreign Key (MaGV) References GiaoVien(MaGV) ) 2.1. Tạo bảng dữ liệu (tiếp) Ràng buộc UNIQUE:  Cú pháp:  Ví dụ 2.4: Thêm khóa dự tuyển là cột Tên MH trong bảng Môn Học: Alter table MonHoc Add constraint Unique_MH_TenMH Unique (TenMH) 2.1. Tạo bảng dữ liệu (tiếp) Ràng buộc CHECK:  Cú pháp:  Ví dụ 2.5: Tạo bảng SV_MH với ràng buộc check cho cột Điểm: 0 <= Điểm <= 10 Create table KetQua( MaSV nvarchar(10) NOT NULL, MaMH nvarchar(10) NOT NULL, Diem int NOT NULL, Primary Key (MaSV,MaMH), Check (0<=Diem And Diem<=10), Foreign Key (MaSV) References SinhVien (MaSV), Foreign Key (MaMH) References MonHoc (MaMH)) 2. Ngôn ngữ định nghĩa dữ liệu 2.1. Tạo bảng dữ liệu 2.2. Sửa đổi định nghĩa bảng 2.3. Xóa bảng 2.2. Sửa đổi định nghĩa bảng  Một bảng sau khi đã được định nghĩa bằng câu lệnh CREATE TABLE có thể được sửa đổi thông qua câu lệnh ALTER TABLE.  Bổ sung một cột vào bảng.  Xoá một cột khỏi bảng.  Thay đổi định nghĩa của một cột trong bảng.  Xoá bỏ hoặc bổ sung các ràng buộc cho bảng  Cú pháp: 2.2. Sửa đổi định nghĩa bảng (tiếp) VD 2.6:  Thêm cột Quê quán với kiểu dữ liệu nvarchar(50) vào bảng Sinh Viên Alter Table SinhVien Add QueQuan nvarchar(50)  Xóa cột Sở thích từ bảng Giáo viên Alter Table GiaoVien Drop Column SoThich  Thay đổi kiểu dữ liệu của cột Điện thoại trong bảng Giáo viên thành nvarchar(11) Alter Table GiaoVien Alter Column DienThoai nvarchar(11) NULL 2. Ngôn ngữ định nghĩa dữ liệu 2.1. Tạo bảng dữ liệu 2.2. Sửa đổi định nghĩa bảng 2.3. Xóa bảng 2.3. Xóa bảng  Cú pháp: DROP TABLE Tên_bảng Bảng có tên được chỉ ra trong mệnh đề được xoá khỏi CSDL  Chú ý:  Câu lệnh này sẽ xóa các ràng buộc, chỉ mục, trigger liên quan đến bảng cần xóa  Khi xóa bằng lệnh DROP không thể khôi phục lại được  Không thể thực hiện được nếu vẫn còn ràng buộc về khóa ngoại  VD 2.7: Xóa bảng GiaoVien Drop Table GiaoVien  có thực thi được hay không? Chương 3: Ngôn ngữ SQL 1. Khái quát về ngôn ngữ truy vấn có cấu trúc SQL 2. Ngôn ngữ định nghĩa dữ liệu 3. Ngôn ngữ thao tác dữ liệu 4. Bài tập áp dụng 3. Ngôn ngữ thao tác dữ liệu 3.1. Truy xuất dữ liệu - Lệnh SELECT 3.2. Thêm mới một dòng dữ liệu – Lệnh INSERT 3.3. Cập nhật dữ liệu – Lệnh UPDATE 4. Xóa các dòng dữ liệu- Lệnh DELETE 3. Ngôn ngữ thao tác dữ liệu 3.1. Truy xuất dữ liệu - Lệnh SELECT 3.2. Thêm mới một dòng dữ liệu – Lệnh INSERT 3.3. Cập nhật dữ liệu – Lệnh UPDATE 3.4. Xóa các dòng dữ liệu- Lệnh DELETE 3.1. Lệnh Select 3.1.1. Tìm thông tin từ các cột của bảng (Phép chiếu) 3.1.2. Chọn các bộ của bảng – Mệnh đề WHERE (Phép chọn) 3.1.3. Thứ tự hiển thị các bản ghi – Mệnh đề ORDER BY 3.1.4. Phân nhóm dữ liệu – Mệnh đề GROUP BY 3.1.5. Điều kiện hiển thị các bản ghi – Mệnh đề HAVING 3.1.6. Truy vấn thông tin từ nhiều bảng (Phép kết nối) 3.1.7. Truy vấn lồng nhau 3.1.8. Các hàm tính toán trên nhóm các bản ghi 3.1.9. Các hàm tính toán trên bản ghi 3.1. Lệnh Select Cú pháp tổng quát Chức năng  Truy xuất dữ liệu từ các dòng và các cột của một hoặc nhiều bảng  Phép chiếu  Phép nối 3.1. Lệnh Select  Một số toán tử, phép toán, hàm sử dụng trong lệnh Select  Toán tử so sánh: o =,>,<,>=,<=,

o BETWEEN o IS NULL, IS NOT NULL o LIKE (%,_) o IN, NOT IN o EXISTS, NOT EXISTS o SOME, ALL, ANY  Toán tử logic: AND, OR, NOT  Các phép toán: +, - ,* , /  Các hàm xử lý: DAY( ), MONTH( ), YEAR( ) …  Các hàm: COUNT( ), SUM( ), MAX( ), MIN( ), AVG( )… 3.1.1. Tìm thông tin từ các cột của bảng (Phép chiếu) Chọn tất cả các trường: *  VD: Select * From SinhVien Chọn một số cột  Thứ tự của các cột trong kết quả truy vấn tuân theo thứ tự của các trường trong danh sách chọn  Khai báo tường minh: TenBang.TenTruong  VD: Cho biết mã GV, tên GV và tên môn học do GV đó dạy Select MaGV, GiaoVien.TenGV,MonHoc.TenMH From GiaoVien, MonHoc Where GiaoVien.MaGV = MonHoc.MaGV 3.1.1. Tìm thông tin từ các cột của bảng (Phép chiếu) Thay đổi tiêu đề các cột     tiêu_đề_cột = tên_trường hoặc tên_trường AS tiêu_đề_cột hoặc tên_trường tiêu_đề_cột VD: Hiển thị Mã SV, Tên SV, Quê quán Select „Mã SV‟ = MaSV, TenSV AS „Tên SV‟, QueQuan „Quê quán‟ From SinhVien Kết quả của câu lệnh: Mã SV Tên SV Quê quán Tin4A01 Nguyễn Thị Ánh Hà Nội Tin4A02 Trịnh Đức Hưng Hà Nội Tin4A03 Phan Thị Thảo Bắc Ninh 3.1.1. Tìm thông tin từ các cột của bảng (Phép chiếu) Cấu trúc CASE trong danh sách chọn  VD: Hiển thị mã, họ tên và giới tính (nam hoặc nữ) của các sinh viên Select MaSV, TenSV, Case GioiTinh When 1 Then „Nam‟ Else „Nữ‟ End AS GioiTinh From SinhVien Select MaSV, TenSV, Case When GioiTinh=1 Then „Nam‟ Else „Nữ‟ End AS GioiTinh From SinhVien 3.1.1. Tìm thông tin từ các cột của bảng (Phép chiếu) Loại bỏ các dòng dữ liệu trùng nhau trong kết quả truy vấn  Thêm từ khóa DISTINCT ngay sau từ khoá SELECT.  VD: Hiển thị quê quán của các SV Select Distinct QueQuan From SinhVien Kết quả của câu lệnh: QueQuan Bắc Ninh Hà Nội Tuyên Quang 3.1.1. Tìm thông tin từ các cột của bảng (Phép chiếu) Giới hạn số lượng dòng trong kết quả truy vấn  Thêm mệnh đề TOP ngay trước danh sách chọn.  VD:  Câu lệnh dưới đây hiển thị họ tên và ngày sinh của 3 sinh viên đầu tiên trong danh sách Select Top 3 TenSV, NgaySinh From SinhVien  Câu lệnh dưới đây hiển thị họ tên và ngày sinh của 20% số lượng sinh viên hiện có trong bảng SinhVien Select Top 20 Percent TenSV, NgaySinh From SinhVien 3.1.2. Chọn các bộ của bảng – Mệnh đề WHERE (Phép chọn) Mệnh đề WHERE trong câu lệnh SELECT được sử dụng nhằm xác định các điều kiện đối với việc truy xuất dữ liệu. Sau mệnh đề WHERE là một biểu thức logic và chỉ những dòng dữ liệu nào thoả mãn điều kiện được chỉ định mới được hiển thị trong kết quả truy vấn. 3.1.2. Chọn các bộ của bảng – Mệnh đề WHERE (Phép chọn) Các toán tử so sánh  VD: Hiển thị thông tin về những điểm < 5 Select * From KetQua Where Diem < 5 Các toán tử logic:  And, Or, Not  VD: Hiển thị thông tin về những điểm từ 5 đến 10 Select * From KetQua Where Diem >= 5 And Diem <= 10 3.1.2. Chọn các bộ của bảng – Mệnh đề WHERE (Phép chọn) Kiểm tra dữ liệu trong phạm vi nào đó  VD: Select * From KetQua Where Diem Between 5 And 10 3.1.2. Chọn các bộ của bảng – Mệnh đề WHERE (Phép chọn) Danh sách (IN, NOT IN)  Sau IN (hoặc NOT IN) có thể là một danh sách các giá trị hoặc là một câu lệnh SELECT khác.  VD: Select * From KetQua Where Diem IN(5,6,7,8,9,10) 3.1.2. Chọn các bộ của bảng – Mệnh đề WHERE (Phép chọn) Toán tử Like và ký tự đại diện  Từ khoá LIKE (NOT LIKE) sử dụng trong câu lệnh SELECT nhằm mô tả khuôn dạng của dữ liệu cần tìm kiếm. Chúng thường được kết hợp với các ký tự đại diện sau: 3.1.2. Chọn các bộ của bảng – Mệnh đề WHERE (Phép chọn)  VD: Hiển thị các sinh viên có quê quán bắt đầu bằng chữ Hà Select MaSV, TenSV, QueQuan From SinhVien Where QueQuan Like „Hà%‟ MaSV TenSV QueQuan Tin4A01 Nguyễn Thị Ánh Hà Nội Tin4A02 Trịnh Đức Hưng Hà Nội Tin4A15 Nguyễn Thu Hường Tin4A34 Trần Văn Quý Hà Nam Hà Giang 3.1.2. Chọn các bộ của bảng – Mệnh đề WHERE (Phép chọn)  Giá trị NULL: Dữ liệu trong một cột cho phép NULL sẽ nhận giá trị NULL trong các trường hợp:  Nếu không có dữ liệu được nhập cho cột và không có mặc định cho cột hay kiểu dữ liệu trên cột đó.  Người sử dụng trực tiếp đưa giá trị NULL vào cho cột đó.  Một cột có kiểu dữ liệu là kiểu số sẽ chứa giá trị NULL nếu giá trị được chỉ định gây tràn số. Cú pháp:  WHERE tên_cột IS NULL  Hoặc WHERE tên_cột IS NOT NULL  VD: Hiển thị danh sách Sinh viên chưa có quê quán Select * From SinhVien Where QueQuan IS NULL 3.1.3. Thứ tự hiển thị các bản ghi – Mệnh đề ORDER BY  Mặc định, các dòng dữ liệu trong kết quả của câu truy vấn tuân theo thứ tự của chúng trong bảng dữ liệu hoặc được sắp xếp theo chỉ mục (nếu trên bảng có chỉ mục).  Trong trường hợp muốn dữ liệu được sắp xếp theo chiều tăng hoặc giảm của giá trị của một hoặc nhiều trường, ta sử dụng thêm mệnh đề ORDER BY trong câu lệnh SELECT  Sau ORDER BY là danh sách các cột cần sắp xếp (tối đa là 16 cột).  Dữ liệu được sắp xếp có thể theo chiều tăng (ASC) hoặc giảm (DESC), mặc định là sắp xếp theo chiều tăng.  VD: Select * From MonHoc Order By DVHT DESC 3.1.4. Phân nhóm dữ liệu – Mệnh đề GROUP BY Mệnh đề GROUP BY cho phép phân hoạch các dòng dữ liệu thành các nhóm dữ liệu và thực hiện các phép toán trên các nhóm dữ liệu đó. Các hàm gộp được sử dụng để tính toán trên toàn bảng, hoặc trên mỗi nhóm dữ liệu Các hàm gộp được sử dụng như là các cột trong danh sách chọn của câu lệnh SELECT hoặc xuất hiện trong mệnh đề HAVING, không được xuất hiện sau WHERE 3.1.4. Phân nhóm dữ liệu – Mệnh đề GROUP BY Các hàm gộp nhóm Trong đó:  Hàm SUM và AVG chỉ làm việc với các biểu thức số.  Hàm SUM, AVG, COUNT, MIN và MAX bỏ qua các giá trị NULL khi tính toán.  Hàm COUNT(*) không bỏ qua các giá trị NULL. 3.1.4. Phân nhóm dữ liệu – Mệnh đề GROUP BY  VD:  Tính điểm trung bình của sinh viên toàn trường Select AVG(Diem) AS DiemTB From KetQua  Tính điểm trung bình các môn học của từng sinh viên Select MaSV, AVG(Diem) AS DiemTB From KetQua Group By MaSV  Chú ý:  Các trường trong danh sách chọn của câu lệnh Select phải có mặt trong mệnh đề Group By