Ngôn ngữ SQL

Ngôn ngữ SQL 1 Chương 5 Nội dung  Giới thiệu chung  Nhóm lệnh DDL  Nhóm lệnh DML  Câu lệnh SELECT  Lệnh Insert  Lệnh Delete  Lệnh Update 2 Giới thiệu chung ngôn ngữ SQL  Là ngôn ngữ chuẩn cho các CSDL quan hệ  Đặc điểm của ngôn ngữ SQL:  Ngôn ngữ tựa tiếng Anh  Ngôn ngữ phi thủ tục 3 Giới thiệu chung ngôn ngữ SQL  Gồm 3 nhóm lệnh:  DDL (Data Definition Language)  DML (Data Manipulation Language)  DCL (Data Control Language) 4 DDL  Là ngôn ngữ định nghĩa dữ liệu gồm các lệnh  CREATE/ALTER/DROP DATABASE  CREATE/ALTER/DROP TABLE 5 DML  Là ngôn ngữ thao tác dữ liệu  Gồm các lệnh:  SELECT  INSERT  UPDATE  DELETE 6 DCL  Là ngôn ngữ điều khiển dữ liệu  Gồm các lệnh GRANT/REVOKE/DENY  COMMIT/ROLLBACK 7 Tạo CSDL CREATE DATABASE CREATE DATABASE database_name [ ON [ [ ,...n ] ] [ , [ ,...n ] ] ] [ LOG ON { [ ,...n ] } ] 8 CREATE DATABASE - Filespec/filegroup ::= ( NAME = logical_name, FILENAME = 'path\filename', SIZE = size_in_MB, MAXSIZE = size_in_MB | UNLIMITED, FILEGROWTH = %_or_MB ) ::= { FILEGROUP [ DEFAULT ] [ ,...n ] } 9 Ví dụ lệnh tạo CSDL CREATE DATABASE Sales ON PRIMARY ( NAME = SPri1_dat, FILENAME = 'c:\program files\data\SPri1dat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), ( NAME = SPri2_dat, FILENAME = 'c:\program files\data\SPri2dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), FILEGROUP SalesGroup1 ( NAME = SGrp1Fi1_dat, FILENAME = 'c:\program files\data\SG1Fi1dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) 10 continued Ví dụ lệnh tạo CSDL ( NAME = SGrp1Fi2_dat, FILENAME = 'c:\program files\data\SG1Fi2dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), LOG ON ( NAME = 'Sales_log', FILENAME = 'c:\program files\data\salelog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO 11 Thay đổi cấu trúc CSDL ALTER DATABASE  Thêm files hay filegroups vào CSDL  Thêm log files vào CSDL  Xoá files hay filegroups từ 1 CSDL  Cài đặt các tuỳ chọn của CSDL 12 ALTER DATABASE ALTER DATABASE database_name ADD FILE filespec [TO FILEGROUP filegroup_name] | ADD LOG FILE filespec | REMOVE FILE logical_filename | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE filespec | MODIFY FILEGROUP filegroup_name filegroup_property 13 Xoá CSDL Dropping a database  Khi 1 CSDL bị xóa thì tất cả các file vật lý của nó cũng sẽ bị xóa  Cú pháp: DROP DATABASE database_name 14 Tạo bảng - CREATE TABLE CREATE TABLE [ database_name.[ owner .]] table_name ({ |column_name AS computed_column_expression | } [ ,...n ] ) [ ON { filegroup | DEFAULT } ] 15 Định nghĩa cột - Column definition ::= { column_name data_type } [ [ DEFAULT constant_expression ] | [ IDENTITY [ ( seed , increment ) ] ] ] [ ] [ ...n ] 16 Ví dụ 1 CREATE TABLE cthoadon ( sohd int NOT NULL, MaHang char(4) NOT NULL, SoLuong int NOT NULL, DonGia money, ThanhTien AS SoLuong*DonGia ) 17 IDENTITY [ ( seed , increment )]  Tạo giá trị gia tăng duy nhất cho 1 cột, và cột này thường được dùng làm khoá chính cho bảng. Chỉ dùng các kiểu dữ liệu sau: tinyint, smallint, int, bigint, decimal(p,0).  Trong mỗi bảng chỉ cho phép 1 cột là identity mà thôi.  Seed: là giá trị đầu tiên được tạo.  Increment: là bước tăng để tạo ra giá trị kế tiếp.  Giá trị mặc định thường là (1,1). 18 Ví dụ 2 CREATE TABLE jobs ( job_id smallint IDENTITY(1,1) , job_desc varchar(50) NOT NULL ) 19 Cách khai ba ́o ràng buộc cột  ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } | [ FOREIGN KEY ] REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] | CHECK ( logical_expression ) } 20 Ví dụ 1 CREATE TABLE jobs ( job_id smallint PRIMARY KEY NOT NULL, job_desc varchar(50) NOT NULL DEFAULT 'New Position - title not formalized yet' ) 21 Ví dụ 2 CREATE TABLE Employee ( EmpID int primary key NOT NULL, LastName varchar(20) NOT NULL, FirstName varchar(10) NOT NULL, job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs(job_id) ) 22 Ví dụ các ràng buộc Check CHECK (cContractCode LIKE '[0-9][0-9][0-9][0-9]') CHECK (mTotalPaid BETWEEN 0 AND 50000) CHECK(cCity IN ('Berkeley', 'Boston', 'Chicago', ' Dallas‘)) 23 Các mức ràng buộc  Có hai mức ràng buộc :  Mức cột (Column level)  Mức bảng (Table level)  Ràng buộc mức bảng: tương tự như ràng buộc mức cột nhưng điều kiện ràng buộc có thể liên quan đến nhiều cột 24 Ví dụ về ràng buộc mức bảng CREATE TABLE cthoadon ( sohd int NOT NULL, MaHang char(4) NOT NULL, SoLuong int NOT NULL, DonGia money, CONSTRAINT pk_ctHoadon primary key(sohd, MaHang) ) 25 Lệnh ALTER TABLE ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name { ALTER COLUMN column_name { [ type_schema_name. ] type_name | [ WITH { CHECK | NOCHECK } ] | ADD { | | | } [ ,...n ] | DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name } [ ,...n ] | { CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ ,...n ] } | { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] } 26 Cú pháp lệnh SELECT SELECT [ ALL | DISTINCT ] [TOP ( expression ) [PERCENT] [ WITH TIES ] ] <select_list > [ FROM { } [ ,...n ] ] [ WHERE search_condition ] [ GROUP BY group_by_expression [ HAVING search_condition ] ] [ ORDER BY order_expression [ ASC | DESC ] ] 27 Mệnh đề SELECT  TOP n [PERCENT]: chỉ xuất n hàng đầu tiên hay n% của các hàng của bảng kết quả.  WITH TIES: cho phép 1 số hàng được thêm vào bảng kết quả gốc nếu các hàng này có cùng giá trị trong cột ORDER BY với hàng cuối cùng của n hàng đầu tiên được xác định. 28 Ví dụ  Liệt kê 3 hoá đơn có cước phí cao nhất SELECT top 3 with ties OrderID, Freight FROM Orders ORDER BY Freight DESC Nếu hàng thứ tư có cùng cước phí (freight) với hàng thứ ba trong bảng kết quả thì sao??? 29 Không WITH TIES Co ́ WITH TIES Tham số ::= { * | { table_name | view_name | table_alias }.* | { [ { table_name | view_name | table_alias }. ] { column_name} | expression [ [ AS ] column_alias ] } | column_alias = expression } [ ,...n ] 30 Ví dụ SELECT ShopperName = vFirstName +’ ‘+ vLastName, year(getdate()) – year(dBirthDate) AS Tuoi FROM Shoppers 31 Các hàm tập hợp (Aggregate function) Tên hàm Ý nghĩa SUM Tính tổng các số MIN Trả về giá trị nhỏ nhất MAX Trả về giá trị lớn nhất AVG Tính giá trị trung bình COUNT Đếm số phần tử 32 Ví dụ các hàm gộp trong lệnh select  SELECT sum(Freight) from dbo.Orders  Tính tổng cước phí chuyên chở của tất cả hoá đơn  SELECT count(OrderID) from dbo.Orders  Đếm số hoá đơn  SELECT SUM(UnitPrice*Quantity*(1-Discount)) FROM dbo.[Order Details]  Tính doanh số bán hàng 33 Mệnh đề FROM/JOIN [ FROM { } [ ,...n ] ] ::= { table_or_view_name [ [ AS ] table_alias ] | rowset_function [ [ AS ] table_alias ] | user_defined_function [ [ AS ] table_alias ] | } 34 Mệnh đề FROM/JOIN ::= { [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } JOIN ON }] 35 Ví dụ 1 SELECT DISTINCT o. cToyID, cToyName FROM orders o JOIN orderDetails d ON o.cOrderNo = d.cOrderNo JOIN Toys t ON d.cToyId = t.cToyId WHERE datediff(mm, dOrderDate,getdate()) 36 Ví dụ 2 SELECT OrderID,p.ProductID, ProductName, CategoryName , Quantity FROM [Order Details] o JOIN Products p ON o.ProductID = p.ProductID JOIN Categories c ON p.CategoryID = c.CategoryID 37 Ví dụ 3 SELECT p.pub_name, p.state, a.au_lname, a.au_fname, a.state FROM publishers p INNER JOIN authors a ON a.state > p.state WHERE p.pub_name = 'New Moon Books' ORDER BY au_lname ASC, au_fname ASC 38 Kết nối trái - LEFT OUTER JOIN SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a. au_fname ASC 39 Mệnh đề WHERE  Dùng để lọc (filter) đầu ra của mệnh đề FROM, hạn chế các hàng dữ liệu được trả về trong bảng kết quả.  Điều kiện trong mệnh đề WHERE có thể chứa:  Dữ liệu trong các bảng  Các toán tử so sánh, toán tử Boolean  Biểu thức  Các hàm có sẵn hay các hàm của người dùng. 40 Mệnh đề WHERE  Các toán tử hay dùng trong biểu thức điều kiện:  BETWEEN ..AND  IN  LIKE  EXISTS 41 Ví dụ  SELECT * FROM Toys WHERE siToyQOH BETWEEN 10 and 19  SELECT Contactname FROM dbo.CUSTOMERS WHERE CITY IN ('LONDON', 'BERLIN', 'MADRID') 42 Mệnh đề ORDER BY  Xác định thứ tự của bảng kết quả  Cú pháp [ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n] ] ASC (ascending) : xếp theo thứ tự tăng DESC (descending): xếp theo thứ tự giảm 43 Mệnh đề GROUP BY [ GROUP BY [ ALL ] group_by_expression [ ,...n ] Mệnh đề GROUP BY dùng để nhóm dữ liệu  ALL: bảng kết quả sẽ chứa tất cả các nhóm kể cả những nhóm không thỏa mãn điều kiện lọc trong trong mệnh đề WHERE, những nhóm không thoả điều kiện sẽ có giá trị null.  group_by_expression: biểu thức dùng để xác định cột được nhóm 44 Ví dụ 1 SELECT Type, Advance = SUM (Advance) FROM Titles WHERE Type IN ('business', 'mod_cook', 'trad_cook') GROUP BY Type 45 GROUP BY và HAVING  Có thể hạn chế các nhóm trong bảng kết quả bằng mệnh đề HAVING.  Chỉ sau khi dữ liệu đã được nhóm và tổng hợp , điều kiện trong mệnh đề HAVING mới được áp dụng.  Không thể dùng các cột mà nó không tham gia vào hàm gộp của mệnh đề SELECT hay của mệnh đề GROUP BY. SELECT pub_id, AVG(price) FROM titles GROUP BY pub_id HAVING (AVG(price) > 10) 46 Ví dụ SELECT titles.pub_id, AVG(titles.price) FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id WHERE publishers.state = 'CA' GROUP BY titles.pub_id HAVING AVG(price) > 10 47 Truy vấn con - Subqueries  Subquery là lệnh SELECT được đặt lồng vào bên trong các lệnh SELECT, INSERT, UPDATE, hay DELETE, hay bên trong truy vấn con khác.  Subquery có thể được dùng bất kỳ nơi nào mà biểu thức được phép dùng 48 Ví dụ Subqueries SELECT Ord.OrderID, Ord.OrderDate, (SELECT MAX(OrdDet.UnitPrice) FROM Northwind.dbo.[Order Details] AS OrdDet WHERE Ord.OrderID =OrdDet.OrderID) AS MaxUnitPrice FROM Northwind.dbo.Orders AS Ord 49 Subqueries  Subquery có thể được dùng theo 1 trong các dạng sau: WHERE expression [NOT] IN (subquery) WHERE expression comparison_operator [ANY | ALL] (subquery) WHERE [NOT] EXISTS (subquery) 50 Subquery với các toán tử so sánh 51 Nhâ ̣p dữ liệu 52 Lệnh INSERT/ Values  Lệnh dùng để thêm các hàng vào 1 bảng hay view. 53 Ví dụ 1 CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30), Manager nvarchar(50)); GO INSERT INTO dbo.Departments VALUES (1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Byham'), (3, 'Finance', 'Gill'), (4, 'Purchasing', 'Barber'), (5, 'Manufacturing', 'Brewer'); 54 Chèn dữ liệu  Giá trị có thể được phát tự động cho các cột sau:  Cột IDENTITY  Cột có kiểu dữ liệu timestamp  Cột có giá trị default  Cột có thể có giá trị null  Khi dùng giá trị DEFAULT :  Những cột có giá trị default sẽ được thêm vào với giá trị default của nó  Những cột IDENTITY sẽ nhận được giá trị identity kế tiếp  Những cột timestamp sẽ nhận các giá trị thích hợp kế tiếp  Tất cả cột khác sẽ nhận giá trị NULL 55 Ví dụ 2 CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 varchar(30) DEFAULT ('my column default'), column_3 timestamp, column_4 varchar(40) NULL ); GO INSERT INTO dbo.T1 (column_4) VALUES ('Explicit value'); INSERT INTO dbo.T1 (column_2, column_4) VALUES ('Explicit value', 'Explicit value'); INSERT INTO dbo.T1 (column_2) VALUES ('Explicit value'); INSERT INTO T1 DEFAULT VALUES; 56 Ví dụ 3 CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30)); GO INSERT T1 VALUES ('Row #1'); INSERT T1 (column_2) VALUES ('Row #2'); GO SET IDENTITY_INSERT T1 ON; GO INSERT INTO T1 (column_1,column_2) VALUES (-99, 'Explicit identity value'); GO 57 INSERT/SELECT  Truy vấn con SELECT trong lệnh INSERT được dùng để tạo 1 bộ kết quả (result set) từ 1 hay nhiều bảng chèn vào 1 bảng khác  Một hay nhiều hàng được thêm vào cùng 1 lúc. 58 INSERT/SELECT INSERT dbo.EmployeeSales SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD FROM Employee AS e INNER JOIN SalesPerson AS sp ON e.EmployeeID = sp.SalesPersonID INNER JOIN Contact AS c ON e.ContactID = c.ContactID WHERE e.EmployeeID LIKE '2%' ORDER BY e.EmployeeID, c.LastName; 59 Lệnh UPDATE  Lệnh update dùng để thay đổi giá trị hiện có trong 1 bảng. Cú pháp: 60 Ví dụ 1 lệnh UPDATE update dbo.Products set UnitPrice= UnitPrice*1.1 where ProductID= 14 Ý nghĩa lệnh?? Tăng sản phẩm có mã 14 lên 10% 61 Mệnh đề FROM  Mệnh đề FROM cho phép kết nối bảng đang được cập nhật với các nguồn dữ liệu khác. UPDATE dbo.Contact SET IsCustomer = 1 FROM dbo.Contact AS C JOIN dbo.[Order] AS O ON C.ContactID = O.ContactID Ý nghĩa?? Cách khác?? Cập nhật trường IsCustomer đánh dấu khách hàng nào đã thực sự mua hàng 62 UPDATE dbo.Contact SET IsCustomer = 1 WHERE ContactID IN (SELECT ContactID FROM dbo.[Order]) Lệnh DELETE  Lệnh DELETE dùng để xoá các hàng trong 1 bảng.  Cú pháp:  Caution: SQL Server has no inherent ‘‘undo’’ command. 63 Ví dụ 1- lệnh Delete USE OBXKites; DELETE FROM dbo.Product WHERE ProductID = ‘DB8D8D60-76F4-46C3-90E6’ Ý nghĩa lệnh? 64 Ví dụ 2 - lệnh DELETE DELETE dbo.Product FROM dbo.Product JOIN dbo.ProductCategory ON Product.ProductCategoryID = ProductCategory.ProductCategoryID WHERE ProductCategory.ProductCategoryName = ‘Video’;  Ý Nghĩa?? 65