Tài liệu SQL Server hướng dẫn chi tiết sử dụng các Hàm

Hệ quản trị cơ sở dữ liệu SQL Server Lê Thành Chung Lời nói đầu Microsoft SQL Servẻ là một hệ quản trị cơ sở dữ liệu quan hệ (Relational database managemet system – RDBMS), nó cung cấp các dịch vụ quản lý và lưu trữ dữ liệu người dùng thông quan internet. Nó cũng hố trợ khả năng truy xuất một cách dễ dàng đối với các tổ chức nhỏ hơn và các người dùng riêng biệt. SQL Server chấp nhận và thực thi các yêu cầu của các khách hàng (Client) đối với việc hiệu chỉnh và xóa dữ liệu, cũng như các lệnh tạo các đối tượng như là các cơ sở dữ liệu (database) và các bảng (Table). SQL Server cho phép người dùng truy xuất và sắp xếp dữ liệu theo các quan hệ, việc lưu trữ dữ liệu một cách hiệu quả theo dạng dòng và cột. Các lệnh của người dùng một ngôn ngữ dùng bởi SQL Server để truy vấn một cơ sở dữ liệu hoặc hiệu chỉnh nội dung của nó. Tài liệu ngày viết để hướng dẫn những câu lệnh trong việc sử dụng SQL Server Các câu lệnh đơn giản Câu lệnh SELECT Câu lệnh SELECT dùng để chọn dữ liệu trong Database, kết quả được lưu trữ trong 1 bảng kết quả gọi là kết quả đề ra Cú pháp của câu lệnh SELECT: SELECT [Column_Name] FROM [Table_Name] Và SELECT *FROM [Table_Name] Ví dụ: có bảng dữ liệu có tên là “Persons” Giờ chúng ta lấy dữ liệu hai cột LastName, FirstName từ bảng trên bằng câu lệnh sau: SELECT LastName,FirstName FROM Persons Kết quả chúng ta nhìn thấy nó sẽ như thế này: Ví dụ về: SELECT * FROM Bây giờ chúng ta lựa chọn tất cả dữ liệu trong bảng “Persons”, Chúng ta viết câu lệnh: SELECT *FROM Persons Kết quả chúng ta nhìn thấy nó sẽ như thế này Câu lệnh SELECT DISTINCT Trong bảng dữ liệu, thường có những cột mà trong đó dữ liệu trùng nhau. Đây không phải là vấn đề, nhưng đôi khi bạn chỉ muốn liệt kê ra những dữ liệu riêng biệt không trùng nhau Cú pháp: SELECT DISTINCT [Column_Name] FROM [Table] VÍ dụ: Trong bảng dữ liệu “Persons” trong cột City có sự trùng lặp “Sandnes”, giờ ta chỉ muốn lấy dữ liệu không trùng lặp ở cột City SELECT DISTINCT [City] FROM [Persons] Kết quả trả ra chúng ta nhìn thấy được là: Mệnh đề WHERE (điều kiện) Mệnh đề WHERE được dùng để lấy dữ liệu theo một điều kiện nào đấy Cú pháp: SELECT [Column_Name] FROM [Table_Name] WHERE [Column_Name] [Operator] (toán tử) Value (giá trị) Ví dụ: Trong bảng “Persons” Bây giờ chúng ta chỉ lấy dữ liệu của người đang sống ở thành phố (City) là “Sandnes” từ bảng dữ liệu trên. Câu lệnh: SELECT * FROM Persons WHERE City = ‘Sandnes’ Kết quả trả về chúng ta nhìn thấy được là: Chú ý: Xung quang đoạn văn bản phải có dấu nháy đơn ‘’ Với đoạn Text Viết đúng: SELECT * FROM Persons WHERE FirstName='Tove' Viết sai: SELECT * FROM Persons WHERE FirstName=Tove Với dữ liệu là số Viết đúng: SELECT * FROM Persons WHERE Year=1965 Viết sai: SELECT * FROM Persons WHERE Year='1965' Các toán tử sử dụng trong mệnh đề WHERE Operator Description = Ngang bằng (!=) Không ngang bằng (khác) > Lớn hơn < Bé hơn >= Lớn hơn hoặc bằng <= Bé hơn hoặc bằng BETWEEN Ở giữa (BETWEEN [value1] AND [value2]) LIKE Tìm kiếm theo từ khóa IN Nếu muốn lấy chính xác 1 dữ liệu Toán tử AND và OR Toán tử AND lấy dữ liệu khi cả hai điều kiện đều có giá trị đúng Toán tử OR lấy dữ liệu khi 1 trong 2 điều kiện trả về giá trị đúng Ví dụ: Với bảng dữ liệu “Persons” Bây giờ chúng ta muốn lấy người tên là “Tove” và chính xác sống ở Thành phố (City) “Sandnes” chúng ta sẽ dùng câu lệnh AND Câu lệnh: SELECT *FROM Persons WHERE FirstName = ‘Tove’ AND City = ‘Sandnes’ Kết quả hiển thị như sau: Khi chúng ta muốn lấy dữ liệu người chỉ có tên là “Tove” hoặc ‘Ola’ thì ta dùng toán tử OR Câu lệnh: SELECT *FROM Persons WHERE FirstName = ‘Tove’ OR FirstName = ‘Ola’ Kết quả hiển thị: Kết hợp giữa toán tử AND và OR Chúng ta lấy chỉ lấy người có họ là “Svendson” và tên là “Ola” hoặc “Tove” Câu lệnh: SELECT *FROM Persons WHERE LastName = ‘Svendson’ AND (FirstName = ‘Ola’ OR FirstName = ‘Tove’) Kết quả trả về: Từ khóa ORDER BY Từ khóa ORDER BY được sử dụng để sắp xếp các kết quả do từ 1 cột được chỉ định. Mặc định ORDER BY sắp xếp theo thứ tự tăng dần theo mặc định. Nếu muốn sắp xếp theo thứ tự giảm dần có thể sử dụng từ khóa DESC Cú pháp: SELECT [Column_Name] FROM [Table_Name] ORDER BY [Column_Name] ASC|DESC Ví dụ bảng dữ liệu Persons: Lấy dữ liệu lên và được sắp xếp mặc định theo trường tên LastName () Câu lệnh: SELECT *FROM Presons ORDER BY LastName Hoặc SELECT *FROM Presons ORDER BY LastName ASC Kết quả hiển thị: Lấy dữ liệu lên và sắp xếp giảm dần Câu lệnh: SELECT *FROM Presons ORDER BY LastName DESC Kết quả hiển thị: Câu lệnh INSERT INTO Câu lệnh INSERT INTO chèn thêm 1 dòng dữ liệu vào bảng Cấu trúc: INSERT INTO [Table_Name]([Column_Name1], [Column_Name2], [Column_Name3],…) VALUES ([Value1], [Value2], [Value3],…) Ví dụ:Ta vẫn sử dụng bảng ‘Persons’ Sau khi thực hiện hàm INSERT INTO ta được bảng giữ liệu như sau: Câu lệnh: INSERT INTO Persons (P_Id, LastName, FirstName)VALUES (5, 'Tjessem', 'Jakob') Câu lệnh UPDATE Câu lệnh UPDATE được sử dụng để cập nhật hồ sơ hiện có trong bảng dữ liệu Cấu trúc: UPDATE [Table_Name] SET [Column_name1] = [value1], [Column_name2] = [value2], [Column_name3] = [value3],… WHERE [Column_name] = [Value] Note: Cần sử dụng mệnh đề WHERE trong câu lệnh UPDATE nếu không tất cả dòng trong bảng dữ liệu sẽ được cập nhật lại Ví dụ: ta sử sử dụng bảng dữ liệu “Persons” Giờ tay sửa lại dòng thứ 5 Câu lệnh: UPDATE Persons SET Address='Nissestien 67', City='Sandnes' WHERE LastName='Tjessem' AND FirstName='Jakob' Kết quả hiển thị như sau: UPDATE dữ liệu lỗi Cẩn thận khi UPDATE dữ liệu bản ghi. Nếu không có mệnh đề WHERE như mệnh đề sau thì dữ liệu sẽ như thế này. Câu lệnh: UPDATE Persons SET Address='Nissestien 67', City='Sandnes' Kết quả hiển thị như sau: Câu lệnh DELETE Câu lệnh DELETE dùng để xóa các dòng trong bảng dữ liệu Cấu trúc: DELETE FROM [Table_Name] WHERE [Column_name] = [Values] Note: Cần sử dụng mệnh đề WHERE câu lệnh DELETE, nếu không sử dụng mệnh đề WHERE thì cả dữ liệu trong bảng sẽ bị xóa Ví dụ: ta sử dụng bảng dữ liệu “Persons” Bây giờ chúng ta sẽ xóa người có họ tên là “Tjessem,Jakob” trong bảng Persons trên. Câu lệnh: DELETE FROM Persons WHERE LastName = ‘Tjessem’ AND FirstName = ‘Jakob’ Kết quả hiển thị như sau: Xóa tất cả dữ liệu trong bảng Câu lệnh: DELETE FROM [table_name] Hoặc DELETE * FROM [table_name] Các câu lệnh nâng cao Câu lệnh CREATE DATABASE Dùng để tạo mới 1 Database Cú pháp: CREATE DATABASE my_db Câu lệnh CREATE TABLE Dùng để tạo bảng dữ liệu trong Database Cú pháp: CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name3 data_type,....) Ví dụ: tạo 1 bảng dữ liệu có cấu trúc Câu lệnh thực hiện: CREATE TABLE Persons(P_Id int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255)) Câu lệnh ALTER TABLE Câu lệnh ALTER TABLE dùng để thêm, xóa, sửa 1 cột trong 1 bảng đã có. Cấu trúc: Dùng để thêm 1 cột vào bảng dữ liệu ALTER TABLE [Table_name] ADD [Column_name] DATATYPE Dùng để xóa 1 cột trong bảng dữ liệu ALTER TABLE [Table_name] DROP COLUMN [Column_name] Dùng để sửa tên 1 cột trong bảng dữ liệu ALTER TABLE [Table_name] ALTER COLUMN [Column_name] DATATYPE Ví dụ: Với bảng “Person” Bây giờ ta thêm một cột “DateOfBirth” vào bảng dữ liệu trên Câu lệnh: ALTER TABLE Person ADD [DateOfBirth] DATETIME Kết quả hiển thị như sau: Bây giờ ta muốn chỉnh sửa trường dữ liệu “DateOfBirth” từ kiểu dữ liệu DATETIME sang kiểu dữ liệu YEAR Câu lệnh: TABLE Person ALTER COLUMN [DateOfBirth] YEAR Bây giờ ta muốn xóa đi cột dữ liệu “DateOfBirth” Câu lệnh: ALTER TABLE Person DROP COLUMN [DateOfBirth] Câu lệnh DROP Dùng để xóa 1 bảng dữ liệu trong cơ sở dữ liệu Cú pháp: DROP TABLE [Table_name] Dùng để xóa 1 cơ sở dữ liệu Cú pháp: DROP DATABASE [DB_name] Mệnh đề TOP Mệnh đề TOP xác định số lượng dòng dữ liệu lấy lên Cú pháp: SELECT TOP [Number]|[Percent] [Column_name] From [Table_name] Ví dụ: với bảng dữ liệu “Persons” Bây giờ ta muốn lấy 50% số lượng bản ghi tìm được từ bảng Persons trên Câu lệnh: SELECT TOP 50 PERCENT FROM Persons Kết quả thu được là: Bây giờ ta muốn lấy một số lượng bản ghi biết trước, kết quả sẽ lấy những bản ghi đầu tiên Câu lệnh: SELECT TOP 2 FROM Persons Toán tử LIKE Toán tử LIKE được sử dụng trong mệnh đề WHERE để tìm kiếm 1 mô hình quy định tại 1 cột nào đấy Cú pháp: SELECT [Column_name] FROM [Table_name] WHERE [Column_name] LIKE [Pattern](mẫu) Ví dụ: ta sử dụng bảng Persons Bây giờ ta muốn lựa chọn những người sống ở thành phố (City) bắt đầu bằng chữ ‘s’ Câu lệnh: SELECT *FROM Persons WHERE City LIKE ‘s%’ Kết quả hiển thị: Tiếp theo, ta lựa chọn những người sống ở thành phố có kết thúc bằng chữ ‘s’ Câu lệnh: SELECT * FROM Persons WHERE City LIKE ‘%s’ Kết quả hiển thị: Tiếp theo ta lựa chọn những người sống trong thành phố mà không chứa chữ ‘tav’ Câu lệnh: SELECT *FROM Persons WHERE City NOT LIKE ‘%tav%’ Kết quả hiển thị: Để hiểu hơn câu lệnh trên, giờ ta đưa ra hai bảng để so sánh Trước khi thực hiện câu lệnh bảng Person: Sau khi thực hiện câu lệnh: : SELECT *FROM Persons WHERE City NOT LIKE ‘%tav%’ Toán tử IN Toán tử IN cho phép bạn chỉ định được nhiều giá trị trong mệnh đề WHERE thay vì toán tử ‘=’ chỉ ra được 1 giá trị Cú pháp: SELECT [Column_name] From [Table_name] WHERE [Colum_name] IN (value1,value2,…) Ví dụ: với bảng Persons trên Bây giờ ta muốn lấy danh sách người có tên họ là ‘Hansen’ và ‘Pettersen’ có trong bảng dữ liệu trên Câu lệnh: SELECT *FROM Persons WHERE LastName IN (‘Hansen’, ‘Pettersen’) Kết quả hiển thị: Toán tử BETWEEN Toán tử BETWEEN lựa chọn những dữ liệu nằm giữa hai giá trị. Giá trị đó có thể là text, số, ngày tháng Cú pháp: SELECT [Colum_name] FROM [Table_name] WHERE [Colum_name] BETWEEN [value1] AND [value2] Từ khóa kết nối JOINS Từ khóa kết nối INNER JOIN Dùng để kết nối hai bảng dữ liệu và có ít nhất 1 khóa liên kết giữa hai bảng. Cú pháp: SELECT [Colum_name] FROM [Table_name1] a INNER JOIN [Table_name2] b ON a.colum_name = b.colum_name Ví dụ: giữa hai bảng dữ liệu Person Và bảng dữ liệu Order: Bây giờ ta muốn đưa ra danh sách người đã đặt hàng Câu lệnh: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName Kết quả hiển thị như sau: Từ khóa kết nối LEFT JOIN Từ khóa LEFT JOIN trả về kết quả theo bảng bên trái, kể cả những kết quả không kết nối sang bảng bên phải Cú pháp: SELECT [Column_name(s)] FROM [Table_name1] LEFT JOIN [Table_name2] ON Table_name1.column_name= Table_name2.column_name Ví dụ: vẫn sử dụng hai bảng dữ liệu trên. Bây giờ ta muốn đưa ra danh sách đơn đặt hàng của họ nếu có. Câu lệnh: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName Kết quả hiển thị: Từ khóa kết nối RIGHT JOIN Từ khóa RIGHT JOIN trả về kết quả theo bảng bên phải, kể cả những kết quả không kết nối sang bảng bên trái Cú pháp: SELECT [Column_name(s)] FROM [Table_name1] RIGHT JOIN [Table_name2] ON Table_name1.column_name= Table_name2.column_name Ví dụ: vẫn sử dụng hai bảng dữ liệu trên. Bây giờ ta muốn đưa ra danh sách đơn đặt hàng của họ nếu có. Câu lệnh: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName Kết quả hiển thị: Từ khóa kết nối FULL JOIN Đưa ra dữ liệu cả của hai bảng Cú pháp: SELECT [Column_name(s)] FROM [Table_name1] FULL JOIN [Table_name2] ON Table_name1.column_name= Table_name2.column_name Mệnh đề SELECT INTO Mệnh đề SELECT INTO đưa dữ liệu vào 1 bảng dữ liệu khác, hoặc backup dữ liệu(khó nói rõ) Ví dụ: Muốn đưa dữ liệu của bảng Persons sang 1 bảng dữ liệu khác SELECT * INTO Persons_backup FROM Persons sao chép vào bảng cơ sở dữ liệu khác SELECT * INTO Persons_backup IN ‘Backup.mdf’ FROM Persons Có thể sao chép 1 vài trường vào bảng dữ liệu mới SELECT LastName,FirstName INTO Persons_backup FROM Persons Có thể kết hợp với mệnh đề WHERE SELECT LastName,Firstname INTO Persons_Backup FROM Persons WHERE City='Sandnes' Có thể kết hợp kết nối bảng dữ liệu SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id Một số hàm trong sql Hàm AVG() Hàm AVG() trả về 1 giá trị trung bình của 1 cột dữ liệu số Cú pháp: SELECT AVG([column_name]) FROM [table_name] Ví dụ: Với bảng dữ liệu ‘Orders’ bên dưới. Bây giờ ta muốn lấy giá trị trung bình của cột ‘OrderPrice’ Câu lệnh: SELECT AVG(OrderPrice) AS OrderAverage FROM Orders Kết quả hiển thị: Bây giờ ta muốn tìm khách hàng có giá trị OrderPrice cao hơn giá trị trung bình Câu lệnh: SELECT Customer FROM Orders WHERE OrderPrice > (SELECT AVG(OrderPrice) FROM Orders) Kết quả hiển thị: Hàm COUNT() Trả về số dòng phù hợp với quy định Cú pháp: Cú pháp COUNT(Column_name) Trả về số dòng của cột chỉ định (giá trị null không được tính) SELECT COUNT(column_name) FROM [table_name] Cú pháp COUNT(*) Trả về số bản ghi trong bản dữ liệu SELECT COUNT(*) FROM [table_name] Cú pháp COUNT(DISTINCT [Column_name]) Hàm trả về số lượng bản ghi khác biệt (không trùng) của cột chỉ định SELECT COUNT(DISTINCT [Column_name]) FROM [table_name] Hàm FIRST() Đưa ra giá trị đầu tiên của bảng với cột chỉ định Cú pháp: SELECT FIRST(column_name) FROM [Table_name] Hàm LAST() Đưa ra giá trị cuối cùng của cột được chỉ định của bảng dữ liệu Cú pháp: SELECT LAST(column_name) FROM [Table_name] Hàm MAX() Đưa ra giá trị lớn nhất của cột được chỉ định của bảng dữ liệu Cú pháp: SELECT MAX(column_name) FROM [Table_name] Hàm MIN() Đưa ra giá trị bé nhất của cột được chỉ định của bảng dữ liệu Cú pháp: SELECT MIN(column_name) FROM [Table_name] Hàm SUM() Đưa ra giá trị tổng của cột được chỉ định của bảng dữ liệu đối với cột là số Cú pháp: SELECT SUM(column_name) FROM [Table_name] Hàm UCASE() Chuyển đổi sang chữ hoa Cú pháp: SELECT UCASE(column_name) FROM [Table_name] Hoặc SELECT UPPER(column_name) FROM [Table_name] Hàm LCASE() Chuyển đổi sang chữ thường Cú pháp: SELECT LCASE(column_name) FROM [Table_name] Hoặc SELECT LOWER(column_name) FROM [Table_name] Hàm MID() Dùng để kiết xuất xâu từ 1 trường văn bản Cú pháp: SELECT LCASE(column_name,[start],[length]) FROM [Table_name] Ví dụ: bảng dữ liệu Persons Bây giờ ta xuất ra 4 ký tự đầu tiên của cột City từ bảng dữ liệu trên Câu lệnh: SELECT MID(City,1,4) as SmallCity FROM Persons Kết quả trả về: Hàm LEN() Trả về giá trị độ dài của văn bản của cột được chỉ định trong bảng dữ liệu Cú pháp: SELECT LEN(column_name) FROM table_name Ví dụ: cũng với bảng dữ liệu Persons trên Bây giờ ta lấy độ dài của dữ liệu nằm trong cột Address Cú pháp: SELECT LEN(Address) FROM Persons Kết quả hiển thị: Hàm ROUND() Dùng để làm tròn số có thập phân Cú pháp: SELECT ROUND(column_name, decimals) FROM table_name Ví dụ: Với bảng dữ liệu có tên là Producst Bây giờ chúng ta muốn lấy dữ liệu làm tròn ở cột UnitPrice Câu lệnh: SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Producst Kếtquả hiển thị: Hàm FORMAT() Dùng để định dạng kiểu dữ liệu Cú pháp: SELECT FORMAT(column_name,format) FROM table_name Hàm REPLACE() Dùng để chuyển đổi 1 khóa nào đó sang khóa khác Cú pháp: REPLACE(String1, String2, String3) String1: là xâu gốc String2: là xâu cần biến đổi String3: là xâu biến đổi Nếu sử dụng với chữ Unicode thì cấu trúc cú pháp là Cú pháp: CAST(REPLACE(CAST ([string1] as nvarchar(max)) ,[string2], [string3]) Hàm CONVERT() Dùng để chuyển dữ liệu từ kiểu này sang kiểu dữ liệu khác Cú pháp: CONVERT([data_type1], [Data]) Trong đó: data_type1: kiểu dữ liệu chuyển đổi Data: dữ liệu cần chuyển đổi Hàm SUBSTRING() Dùng để cắt xâu Cú pháp: SUBSTRING([Xâu cần cắt],[Bắt đầu],[Độ dài cần cắt])