Lập trình SQL nâng cao

Chia sẻ: Hồ đông Nhựt | Ngày: | Loại File: DOC | Số trang:31

Thêm vào BST

Báo xấu

497
lượt xem 160
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Ngôn ngữ truy vấn có cấu trúc (SQL) là ngôn ngữ chuẩn hóa để định nghĩa và xử lý dữ liệu trong một cơ sở dữ liệu quan hệ. Tất cả các hệ quản trị cơ sở dữ liệu quan hệ (RDBMS) đều hiểu được SQL. SQL được đặc biệt tạo ra như một phần của lý thuyết quan hệ. Bạn cần một ngôn ngữ để trao đổi với cơ sở dữ liệu, để lấy ra dữ liệu, hoặc để sửa đổi dữ liệu. SQL là một ngôn ngữ không dẫn đường. Nói cách khác, với SQL, bạn không phải...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Lập trình SQL nâng cao

Lập trình SQL [Nâng cao] SQL ORDER BY Từ khóa ORDER BY dùng để sắp xếp lại những kết quả trả về theo kiểu alphabetical hay một giá trị nào đó . Bây giờ ta sẽ hiển thị ra 2 cột Company và OrderNumber được sắp xếp theo vần alphabetical : Mã: SELECT Company, OrderNumber FROM Orders ORDER BY Company Còn đây là ví dụ hiển thị sắp xếp trong alphabetical Order và Ordernumbers trong kiểu numerical. Mã: SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber Bạn muốn sắp xếp đảo ngược lại alphabetical thì ta sẽ thêm từ khóa DESC sau giá trị của ORDER BY : Mã: SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC Và tương tự như vậy khi ta thêm sự sắp xếp kiểu numerical order , thêm từ khóa ASC : Mã: SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
SQL ALTER TABLE Biểu thức ALTER TABLE dùng để thay đổi các giá trị trong tables. Mã: ALTER TABLE table_name ADD column_name datatype ALTER TABLE table_name DROP COLUMN column_name Lưu ý : Một vài hệ thống database ko cho phép drop trực tiếp các cột trong table. (DROP COLUMN column_name) Thêm vào 1 cột trong table Persons : Mã: ALTER TABLE Person ADD City varchar(30) Và drop cột address : Mã: ALTER TABLE Person DROP COLUMN Address SQL Functions SQL có một số lượng lớn các hàm gắn liền với sự tính toán .
Cú pháp của Function : Mã: SELECT function(column) FROM table Ở đây chúng ta chỉ vài kiểu cơ bản trong các loại hàm của SQL. Đây là 2 dạng cơ bản : * (Aggregate functions) Tập hợp Functions * (Scalar functions) Định hướng functions Aggregate functions : Aggregate functions có tác dụng với nhiều giá trị nhưng chỉ trả về 1 giá trị . Lưu ý : Nếu sử dụng nhiều biểu thức trong một câu lệnh select thì câu lệnh SELECT đấy cần phải có mệnh đề GROUP BY Tập hợp Function trong MS Accesss : Function Description Mã: AVG(column) Trả về giá trị trung bình trong một cột COUNT(column) Trả về số lượng hàng (không có giá trị NULL) của một cột COUNT(*) Trả về số lượng các hàng được lựa chọn FIRST(column) Trả về giá trị của bản ghi đầu tiên trong một trường LAST(column) Trả về giá trị của bản ghi cuối trong một trường được chỉ định
MAX(column) Trả về giá trị lớn nhất của một cột MIN(column) Trả về giá trị nhỏ nhất của một cột STDEV(column) STDEVP(column) SUM(column) Trả về tất cả giá trị trong một cột VAR(column) VARP(column) Tập hợp Functions trong SQL Server (tương tự như của Access) Function Description Mã: AVG(column) Trả về giá trị trung bình trong một cột BINARY_CHECKSUM CHECKSUM CHECKSUM_AGG COUNT(column) Trả về số lượng hàng (không có giá trị NULL) của một cột COUNT(*) Trả về số lượng hàng được chọn COUNT(DISTINCT column) Trả về số lượng các kết quả riêng biệt FIRST(column) Trả về giá trị của bản ghi đầu tiên trong một trường được chỉ định (không hỗ trợ trong SQL Server 2000) LAST(column) Trả về giá trị của bản ghi cuối trong một trường được chỉ định (không hỗ trợ trong SQL Server 2000) MAX(column) Trả về giá trị lớn nhất của một cột MIN(column) Trả về giá trị nhỏ nhất của một cột STDEV(column) STDEVP(column) SUM(column) Trả về tất cả giá trị trong một cột VAR(column) VARP(column)
Scalar functions Scalar functions có tác dụng ngược lại đối với một số giá trị nhất định, và trả về thành phần một số giá trị trên những giá trị được đưa vào. Scalar Functions được dùng trong MS Access Function Description Mã: UCASE(c) Chuyển một trường sang chữ HOA LCASE(c) Chuyển một trường sang chữ thường MID(c,start[,end]) Lấy ra những kí tự trong trường text LEN(c) Trả về độ dài của một trường text INSTR(c,char) Trả về số vị trí của tên kí tự trong một trường text LEFT(c,number_of_char) Trả về phần bên trái của một trường text được yêu cầu RIGHT(c,number_of_char) Trả về phần bên phải của một trường text được yêu cầu ROUND(c,decimals) Làm tròn số trường thập phân MOD(x,y) Trả về phần còn lại của phép chia NOW() Trả về ngày tháng của hệ thống hiện thời FORMAT(c,format) Thay đổi hướng của trường hiển thị DATEDIFF(d,date1,date2) Sử dụng để thực thi việc tính toán ngày tháng Thất bại thì Làm lại Bộ sưu tập E-books tiếng Anh và tiếng Việt cho ITer (119 cuốn): http://www.mediafire.com/?zao9royjp2pc7 08-12-2008, 09:21 PM
Số bài viết: #2 Bài viết: 2,590 Gia nhập: Dec 2007 Shin Điểm tín nhiệm: 0 Ultimate Weapon Số lần cám ơn: 20 Được cám ơn 84 lần/51 bài viết Lập trình SQL [Nâng cao] SQL GROUP BY and HAVING Tập hợp functions (giống như lệnh SUM) thường cần thiết thêm vào hàm GROUP BY . GROUP BY... được xem thêm vào SQL bởi vì tập hợp các hàm trả về tập hợp của tất cả giá trị của cột theo mọi thời điểm mà họ gọi ra , và ko dùng hàm GROUP BY thì xem như ko thể tìm được số tổng số của những giá trị nhóm cột riêng lẻ . Mã: SELECT column,SUM(column) FROM table GROUP BY column Ví dụ về GROUP BY : Sử dụng câu lệnh SQL để xem những trường có trong table này : Mã: SELECT Company, SUM(Amount) FROM Sales Đoạn code trên sẽ ko có hiệu lực bởi vị cột SUM(Amount) sẽ trả về tổng của tất cả các giá trị trong đó nên hiển thị sai .Mệnh đề GROUP BY sẽ giải quyết được vấn đề này : Mã:
SELECT Company,SUM(Amount) FROM Sales GROUP BY Company Câu lệnh trên sẽ nhóm những giá trị theo cột Company như vậy các giá trị Amount sẽ phải hiển thị đúng theo sự sắp xếp bên đó. Kết quả W3Schools có giá trị là 12600 vì có 2 trường cộng lại , IBM thì giữ nguyên đúng giá trị của nó. HAVING... Với câu lệnh này có lẽ bạn đã hiểu ý nghĩa của nó vì SQL rất gần với ngôn ngữ bình thường. Mã: SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value Ta thực hiện lệnh như sau : Mã: SELECT Company,SUM(Amount) FROM Sales GROUP BY Company HAVING SUM(Amount)>10000 Kết quả sẽ hiển thị ra số nào ứng với cột Company có số lượng >10000 (having), vậy chỉ có W3Schools với tổng số là 12600. Thất bại thì Làm lại Bộ sưu tập E-books tiếng Anh và tiếng Việt cho ITer (119 cuốn): http://www.mediafire.com/?zao9royjp2pc7
08-12-2008, 09:24 PM Số bài viết: #3 Bài viết: 2,590 Gia nhập: Dec 2007 Shin Điểm tín nhiệm: 0 Ultimate Weapon Số lần cám ơn: 20 Được cám ơn 84 lần/51 bài viết Lập trình SQL [Nâng cao] SQL SELECT INTO Statement : Câu lệnh SELECT INTO thường được dùng để tạo những back up hoặc của tables hoặc những bản ghi . Mã: SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source Sử dụng để backup : Những ví dụ sau đây được dùng để backup table Persons : Mã: SELECT * INTO Persons_backup FROM Persons Mệnh đề IN có thể được sử dụng để copy thêm tables vào trong database : Mã: SELECT Persons.* INTO Persons IN 'Backup.mdb'
FROM Persons Nếu bạn chỉ muốn copy một vài trường thì có thể select trực tiếp đến những trường đó : Mã: SELECT LastName,FirstName INTO Persons_backup FROM Persons Bạn cũng có thể sử dụng mệnh đề WHERE. Những ví dụ sau đây sẽ tạo một table "Persons_backup" với 2 cột (FirstName và LastName) được trích trong table Persons với cột City='Sandnes' (có nghĩa là những FirstName và LastName có City = 'Sandnes' Mã: SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Sandnes' Cũng có thể select dữ liệu trong nhiều tables khác. Ví dụ này tạo một table mới có tên "Empl_Ord_backup" và chứa nội dung của 2 table là Employees và Orders: Mã: SELECT Employees.Name,Orders.Product INTO Empl_Ord_backup FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID Như vậy là table "Empl_Ord_backup" sẽ chứa tất cả các giá trị trong cột Employee_ID của 2 table Employees và Orders .
SQL CREATE VIEW Statement Một view có thể coi như là một "bảng ảo" có nội dụng được xác định từ một truy vẫn. Một truy vấn (query) chỉ là một tập hợp các chỉ dẫn (intruction) nhằm truy vẫn và hiển thị dữ liệu từ các bảng CSDL . Các truy vấn được thực hiện bằng các sử dụng câu lệnh SELECT. Using VIEW : Một VIEW (khung nhìn) trông giống như một bảng với một tập hợp các tên cột và các dòng dữ liệu.Tuy nhiên ,khung nhìn ko tồn tại như là một cấu trúc lưu trữ dữ liệu trong CSDL. Dữ liệu bên trong khung nhìn thực chất là dữ liệu được xác định từ một hay nhiều bảng cơ sở và do đó phụ thuộc vào các bảng cơ sở. Các VIEW được sử dụng trong CSDL nhằm các mục đích sau : - Sử dụng khung nhìn để tập trung trên dữ liệu xác định - Sử dụng khung nhìn để đơn giản hoá thao tác dữ liệu - Sử dụng khung nhìn để tuỳ biến dữ liệu - Sử dụng khung nhìn để xuất dữ liệu - Sử dụng khung nhìn để bảo mật dữ liệu Mã: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition Ví dụ sau về cách tạo 1 VIEW tên [Current Product List] với dữ liệu trong 2 cột ProductID,ProductName của table Products .
Mã: CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No Chúng ta có thể truy vấn VIEW bằng câu lệnh sau : Mã: SELECT * FROM [Current Product List] Một số ví dụ khác về VIEW : Tạo một VIEW với tên [Products Above Average Price] có chứa data của 2 cột ProductName,UnitPrice trong table Products mà giá trị UnitPrice lớn hơn số lượng trung bình trong Products . Mã: CREATE VIEW [Products Above Average Price] AS SELECT ProductName,UnitPrice FROM Products WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products) Query view trên : Mã: SELECT * FROM [Products Above Average Price]
Ví dụ sau view trong database tên Northwind và tính tổng số sản phẩm được bán năm 1997 : Mã: CREATE VIEW [Category Sales For 1997] AS SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales FROM [Product Sales for 1997] GROUP BY CategoryName Query VIEW : Mã: SELECT * FROM [Category Sales For 1997] Bạn cũng có thể thêm điều kiện và câu truy vấn .Ví dụ như ở đây là view trong CategoryName='Beverages' : Mã: SELECT * FROM [Category Sales For 1997] WHERE CategoryName='Beverages' Link của phần index giúp bạn tổng hợp những kiến thức cơ bản : Mã: http://www.w3schools.com/sql/sql_quickref.asp Thất bại thì Làm lại Bộ sưu tập E-books tiếng Anh và tiếng Việt cho ITer (119 cuốn): http://www.mediafire.com/?zao9royjp2pc7 08-12-2008, 09:26 PM Số bài viết: #4
Bài viết: 2,590 Gia nhập: Dec 2007 Shin Điểm tín nhiệm: 0 Ultimate Weapon Số lần cám ơn: 20 Được cám ơn 84 lần/51 bài viết Lập trình SQL [Nâng cao] Với một số kiến thức cơ bản về SQL ở trên bạn có thể làm gì nào ? Bây giờ ta sẽ thử xét với phương diện của người lập trình web : Tạo 1 database tên hacker với các trường như sau : Mã: create database hacker use hacker Create table admin( [id] numeric not null, username nvarchar(10) not null, [password] nvarchar(10) not null, address nvarchar(30), phone numeric(10) ) select * from admin Insert vào một số data : Mã: insert into admin ([id],username,[password],address,phone) values (1,'admin',12345,'Ha Noi',048324245)
go insert into admin ([id],username,[password],address,phone) values (2,'Neo',12345,'Ha Noi',048324245) Rồi okie. Giờ muốn select những username trong table admin mà có address tại Hà Nội thì câu query sẽ như sau : (tất cả những giá trị là character thì phải được bao trong dấu quote ' Mã: select username from admin where address='Ha Noi' Vậy nếu ta viết thế này thì sẽ có chuyện gì xảy ra nhỉ Mã: select username from admin where address=''or 1=1--' Kết quả cũng show ra như trên. Như trên ta thấy đáng lẽ ra address mang giá trị là Ha Noi nhưng nếu ta thay giá trị đó bằng 'or 1=1-- thì dấu quote đằng trước sẽ là 2 dấu '' có nghĩa giá trị trong đó rỗng . Và sau nó là or 1=1 , như ta đã biết or là câu lệnh điều kiện và ở đây có nghĩa là hoặc. Mà giá trị đằng trước là rỗng nên SQL sẽ chạy tiếp theo gặp điều kiện or thì tất nhiên sẽ thực thi lệnh này mà or 1=1-- , 1=1 thì luôn đúng , còn dấu -- sẽ biến tất cả những thứ đằng sau thành chú thích (trong SQL quy định sau dấu -- là câu chú thích). Như vậy câu truy vấn hoàn toàn hợp lệ và ko có lỗi gì cả và nó sẽ show cho ta toàn bộ username của admin , đến đây chắc bạn đã hiểu rồi chứ Còn về câu lệnh UNION :
Như đã biết câu lệnh UNION dùng để gộp chuỗi lại với nhau và các giá trị trong 2 chuỗi đó phải là tương ứng . Ví dụ : Mã: select username from admin UNION select [password] from admin Câu lệnh trên sẽ show cho ta thấy các username và password trong table admin. Khi tạo table bạn để ý thấy username ta gán thuộc tính là nvarchar , vậy nếu khi truy vấn bằng UNION , dòng thứ 2 bạn thay [password] thành số bất kì (ví dụ 1) thì sẽ báo lỗi sau : Câu truy vấn : Mã: select username from admin UNION select 1 from admin Error : Ghi chú:Syntax error converting the nvarchar value 'admin' to a column of data type int. Hô hô error báo sai cú pháp ,ko thể chuyển từ kiểu dữ liệu nvarchar (chuỗi) sang int (số) được vì dòng thứ 2 ta thay thế giá trị là kiểu int --> ko cùng loại dữ liệu --->error và show ra cái table admin . Còn khi áp dụng trên web thì đây là dấu hiệu cho thấy table admin là có tồn tại . Đây chỉ là một số ví dụ để các bạn có thể hiểu về SQL injection ,mong giúp được gì đó cho các bạn !
Mà còn cách fix thì sao nhỉ , trong SQL có một thứ gọi là trigger Hàm này có tính năng tương tự như hàm bắt sự kiện trong JavaScript , ta sẽ viết 1 hàm để bắt những lỗi trên hoặc có thể viết = chính ngôn ngữ lập trình web. Thất bại thì Làm lại Bộ sưu tập E-books tiếng Anh và tiếng Việt cho ITer (119 cuốn): http://www.mediafire.com/?zao9royjp2pc7 14-12-2008, 02:01 PM Số bài viết: #5 Bài viết: 2,590 Gia nhập: Dec 2007 Shin Điểm tín nhiệm: 0 Ultimate Weapon Số lần cám ơn: 20 Được cám ơn 84 lần/51 bài viết Lập trình SQL [Nâng cao] Câu lệnh điều kiện AND & OR AND và OR dùng nối 2 hoặc nhiều điều kiện trong một mệnh đề WHERE. Toán tử AND hiển thị số hàng nếu tất cả điều kiện trong danh sách đều đúng (true). Toán tử OR hiển thị số hàng nếu một vài điều kiện trong danh sách đúng (true). Sử dụng AND để hiển thị những giá trị trong table person với FirstName ="Tove" ,và LastName ="Svendson" Mã: SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'
Tương tự với toán tử OR ta sẽ dùng hiển thị FirstName và LastName như trên, nhưng đây là câu điều kiện hoặc lên sẽ show ra FirstName="Tove" hoặc LastName ="Svendson" mà trong table Persons có 2 người FirstName là 'Svendson' nên ta sẽ nhận được 2 kết quả. Hoặc ta có thể kết hợp cả 2 toán tử AND và OR để ra được kết quả trên (sử dụng biểu thức sẽ phức tạp hơn) Mã: SELECT * FROM Persons WHERE (FirstName='Tove' OR FirstName='Stephen') AND LastName='Svendson' Toán tử IN Toán tử IN có thể được sử dụng nếu bạn biết chính xác giá trị mà bạn muốn trả về trong ít nhất một cột. Soạn Persons table (hoặc cái gì đó tương tự) như sau : Ví dụ: Câu lệnh sau Select và hiển thị table Persons với LastName = "Hansen" hoặc "Pettersen" : Mã: SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen')
Toán tử BETWEEN : Toán tử BETWEEN ... AND dùng để lọc ra một khoảng trong dữ liệu giữa 2 giá trị. Giá trị đó có thể là số (numbers), chữ (text) hoặc ngày tháng (dates) Mã: SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 Ví dụ 1: Ta muốn lấy được ra tất cả các giá trị trong LastName trong khoảng từ "Hansen" đến "Pettersen" thì câu lệnh sẽ như sau : Mã: SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen' Ví dụ 2 : Trong ví dụ này ta sẽ hiện thị ra kết quả không phải trong khoảng 'Hansen' và 'Pettersen' Chỉ cần thêm NOT vào trước BETWEEN : Mã: SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Hansen' AND 'Pettersen'
Kết quả sẽ hiển thị những giá trị từ Pettersen trở xuống. Thất bại thì Làm lại Bộ sưu tập E-books tiếng Anh và tiếng Việt cho ITer (119 cuốn): http://www.mediafire.com/?zao9royjp2pc7 14-12-2008, 02:08 PM Số bài viết: #6 Bài viết: 2,590 Gia nhập: Dec 2007 Shin Điểm tín nhiệm: 0 Ultimate Weapon Số lần cám ơn: 20 Được cám ơn 84 lần/51 bài viết Lập trình SQL [Nâng cao] SQL Alias : Với SQL , Aliases có thể sử dụng để gán cho một biệt hiệu cho tên cột và tên table. Column Name Alias : Mã: SELECT column AS column_alias FROM table Table Name Alias : Mã: SELECT column FROM table AS table_alias
Ví dụ : Vẫn table Persons : Ta sẽ gán biệt hiệu là Family cho LastName và Name cho FirstName : Mã: SELECT LastName AS Family, FirstName AS Name FROM Persons Sử dụng Alias đối với table : Mã: SELECT LastName, FirstName FROM Persons AS Employees SQL UNION and UNION ALL Câu lệnh UNION được sử dụng để select (lựa chọn) những thông tin liên quan đến nhau của hai tables, số lượng những câu lệnh JOIN. Tuy nhiên nếu sử dụng câu lện UNION thì tất cả những sự lựa chọn tại các cột cần phải giống nhau về mặt dữ liệu. Chú ý : Trong UNION những giá trị giống nhau sẽ là duy nhất (Coi như có câu lệnh DISTINCT ) . Mã: SQL Statement 1 UNION SQL Statement 2