Transcript ppsx

SQL Server 2005 ‫آموزش‬
‫مروری بر دستورات پرس و جوهای انتخاب‬
‫‪TSQL‬‬
TSQL ‫چندین نمونه از دستورات‬
Use Northwind
GO
SELECT FirstName, LastName FROM dbo.Employees;
SELECT * FROM dbo.Employees;
--Concatenating
SELECT LastName + ', ' + FirstName FROM
dbo.Employees;
TSQL ‫چندین نمونه از دستورات‬
-- Aliasing column names
SELECT LastName + ‘ , ‘ + FirstName AS [Full Name]
FROM dbo.Employees;
TSQL ‫چندین نمونه از دستورات‬
-- AS is optional
SELECT LastName + ', ' + FirstName FullName
FROM dbo.Employees;
-- Another aliasing option
SELECT FullName = LastName + ', ' + FirstName
FROM dbo.Employees;
TSQL ‫چندین نمونه از دستورات‬
-- This is deprecated:
SELECT 'FullName' = LastName + ', ' +
FirstName
FROM dbo.Employees;
--SELECT and SELECT DISTINCT
SELECT Title FROM dbo.Employees;
SELECT DISTINCT Title FROM dbo.Employees;
TSQL ‫چندین نمونه از دستورات‬
--WHERE
SELECT CompanyName, City
FROM dbo.Customers
WHERE City = 'Paris';
TSQL ‫چندین نمونه از دستورات‬
--LIKE and wildcard characters
SELECT CompanyName
FROM dbo.Customers
WHERE CompanyName LIKE 'S%';
SELECT CompanyName
FROM dbo.Customers
WHERE CompanyName LIKE '%S';
SELECT CompanyName
FROM dbo.Customers
WHERE CompanyName LIKE '%S%';
TSQL ‫چندین نمونه از دستورات‬
--Matching single characters
SELECT CustomerID
FROM dbo.Customers
WHERE CustomerID LIKE 'B___P';
--Matching from a list
SELECT CustomerID
FROM dbo.Customers
WHERE CustomerID LIKE 'FRAN[RK]';
TSQL ‫چندین نمونه از دستورات‬
SELECT CustomerID
FROM dbo.Customers
WHERE CustomerID LIKE 'FRAN[A-S]';
SELECT CustomerID
FROM dbo.Customers
WHERE CustomerID LIKE 'FRAN[^R]';
TSQL ‫چندین نمونه از دستورات‬
--BETWEEN
SELECT LastName, FirstName, PostalCode
FROM dbo.Employees
WHERE PostalCode BETWEEN '98103' AND '98999';
--Testing for Null
SELECT LastName, FirstName, Region
FROM dbo.Employees
WHERE Region IS NULL;
TSQL ‫چندین نمونه از دستورات‬
--AND requires both conditions to be true
SELECT LastName, City, PostalCode
FROM dbo.Employees
WHERE City = 'Seattle' AND PostalCode LIKE '9%';
--OR only requires one condition to be true
SELECT LastName, City, PostalCode
FROM dbo.Employees
WHERE City = 'Seattle' OR PostalCode LIKE '9%';
TSQL ‫چندین نمونه از دستورات‬
--NOT negates the expression
SELECT LastName, City, PostalCode
FROM dbo.Employees
WHERE City NOT LIKE 'Seattle';
--Operator Precedence: NOT, AND, OR
SELECT LastName, FirstName, City
FROM dbo.Employees
WHERE LastName LIKE '%S%'
AND City NOT LIKE 'Seattle';
TSQL ‫چندین نمونه از دستورات‬
--IN
SELECT CustomerID, Country
FROM dbo.Customers
WHERE Country IN ('France', 'Spain');
--IN with a subquery
SELECT CustomerID
FROM dbo.Customers
WHERE CustomerID NOT IN(SELECT CustomerID
FROM dbo.Orders);
TSQL ‫چندین نمونه از دستورات‬
--ORDER BY
SELECT LastName, City
FROM dbo.Employees
ORDER BY City;
TSQL ‫چندین نمونه از دستورات‬
--Sorting in descending order
SELECT LastName, City
FROM dbo.Employees
ORDER BY City DESC;
--Sorting on multiple columns
SELECT LastName, City
FROM dbo.Employees
ORDER BY City DESC, LastName ASC;
TSQL ‫چندین نمونه از دستورات‬
--Sorting on an expression
SELECT LastName
FROM dbo.Employees
ORDER BY LEN(LastName);
TSQL ‫چندین نمونه از دستورات‬
--Counting rows
SELECT COUNT(*) AS ‫تعداد‬
FROM dbo.Employees;
TSQL ‫چندین نمونه از دستورات‬
--Counting values in a column
SELECT COUNT(*) AS NumEmployees,
COUNT(Region) AS NumRegion
FROM dbo.Employees;
TSQL ‫چندین نمونه از دستورات‬
--Counting with WHERE
SELECT COUNT(*) AS NumEmployeeSeattle
FROM dbo.Employees
WHERE City = 'Seattle';
TSQL ‫چندین نمونه از دستورات‬
--Gives an error (no grouping)
SELECT City, COUNT(*) AS NumEmployees
FROM dbo.Employees;
TSQL ‫چندین نمونه از دستورات‬
--Using Grouping
SELECT City, COUNT(*) AS NumEmployees
FROM dbo.Employees
GROUP BY City;
TSQL ‫چندین نمونه از دستورات‬
--ORDER BY with GROUP BY
SELECT City, COUNT(*) AS NumEmployees
FROM dbo.Employees
GROUP BY City
ORDER BY COUNT(*) DESC, City;
TSQL ‫چندین نمونه از دستورات‬
--HAVING
SELECT City, COUNT(*) AS NumEmployees
FROM dbo.Employees
GROUP BY City
HAVING COUNT(*) > 1
ORDER BY NumEmployees DESC, City;
--TOP
TSQL ‫چندین نمونه از دستورات‬
SELECT TOP 3
City, COUNT(*) AS NumEmployees
FROM dbo.Employees
GROUP BY City
ORDER BY COUNT(*) DESC;
TSQL ‫چندین نمونه از دستورات‬
--TOP WITH TIES
SELECT TOP 3 WITH TIES
City, COUNT(*) AS NumEmployees
FROM dbo.Employees
GROUP BY City
ORDER BY COUNT(*) DESC;
TSQL ‫چندین نمونه از دستورات‬
--TOP WITH PERCENT
SELECT TOP 25 PERCENT WITH TIES
City, COUNT(*) AS NumEmployees
FROM dbo.Employees
GROUP BY City
ORDER BY COUNT(*) DESC;
TSQL ‫چندین نمونه از دستورات‬
--A Cartesian Product
SELECT ProductName, CategoryName
FROM dbo.Products, dbo.Categories;
TSQL ‫چندین نمونه از دستورات‬
SELECT ProductName, CategoryName
FROM dbo.Products CROSS JOIN dbo.Categories;
TSQL ‫چندین نمونه از دستورات‬
--Joining in the WHERE clause
SELECT ProductName, CategoryName
FROM dbo.Products, dbo.Categories
WHERE dbo.Products.CategoryID =
dbo.Categories.CategoryID;
TSQL ‫چندین نمونه از دستورات‬
--Using JOIN
SELECT dbo.Products.ProductName,
dbo.Categories.CategoryName
FROM dbo.Products JOIN dbo.Categories
ON dbo.Products.CategoryID = dbo.Categories.CategoryID;
TSQL ‫چندین نمونه از دستورات‬
-- save typing with table aliases
SELECT P.ProductName, C.CategoryName
FROM dbo.Products AS P JOIN dbo.Categories AS C
ON P.CategoryID = C.CategoryID;
TSQL ‫چندین نمونه از دستورات‬
--JOIN with WHERE and ORDER BY
SELECT dbo.Products.ProductName,
dbo.Categories.CategoryName,
dbo.Products.UnitPrice
FROM dbo.Products INNER JOIN dbo.Categories
ON dbo.Products.CategoryID = dbo.Categories.CategoryID
WHERE UnitPrice > 50
ORDER BY ProductName;
TSQL ‫چندین نمونه از دستورات‬
--Multiple table join
SELECT OrderID, convert(varchar(10), OrderDate,101) AS
Date,CompanyName, LastName
FROM dbo.Orders
INNER JOIN dbo.Customers
ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
WHERE OrderDate BETWEEN '9/1/1996' AND '9/10/1996'
ORDER BY OrderDate;
‫خروجی دستور‬
TSQL ‫چندین نمونه از دستورات‬
SELECT CompanyName,
SUM([Order Details].UnitPrice * [Order Details].Quantity)
AS TotalSold
FROM dbo.Customers INNER JOIN dbo.Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE Orders.OrderDate BETWEEN '9/1/1996' AND '9/10/1996'
GROUP BY CompanyName
ORDER BY TotalSold DESC;
‫خروجی دستور‬
TSQL ‫چندین نمونه از دستورات‬
--Left outer join to find unmatched values
SELECT CompanyName as [No Orders]
FROM dbo.Customers LEFT JOIN dbo.Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderID IS NULL
ORDER BY CompanyName;
TSQL ‫چندین نمونه از دستورات‬
--Right outer join to find unmatched values
SELECT CompanyName as [No Orders]
FROM dbo.Orders RIGHT JOIN dbo.Customers
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderID IS NULL
ORDER BY CompanyName;
‫اتصال اطالعات ستونها به یکدیگر ‪Concatenation‬‬
‫ترکیب ستون های نام و نام خانوادگی از جدول ‪:Contact‬‬
‫‪ ‬الحاق ستون های ‪ FirstName‬و ‪ LastName‬با عالمت ‪+‬‬
‫‪ ‬اختصاص دادن یک ‪ Alias Name‬با استفاده از کلمه کلیدی ‪AS‬‬
‫‪ ‬مرتب سازی رکوردهای به صورت الفبایی بر اساس نام با استفاده از دستور ‪:ORDER BY‬‬
‫** با الحاق کردن ستون ها هیچ تغییری در ساختار بانک اطالعاتی اتفاق نمی افتد‪.‬‬
‫ایجاد و مدیریت روالهای ذخیره شده‬
‫‪Stored Procedures‬‬
‫آشنایی با پروسیجرهای ذخیره شده‬
‫هر پروسیجر ذخیره شده ‪ ،‬مجموعه ای از دستورات است که به جای اجرا در کامپیوتر سرویس گیرنده در سروری که بانک‬
‫اطالعاتی روی آن قرار دارد اجرا میشود‪.‬‬
‫مزایای استفاده از پروسیجرهای ذخیره شده‪:‬‬
‫‪ ‬برنامه نویس ی ماژوالر‬
‫‪ ‬دسترس ی محدود و تابعی به جداول‬
‫‪ ‬کاهش ترافیک شبکه‬
‫‪ ‬اجرای سریع‬
‫‪ ‬کاهش اشتباهات برنامه نویس ی‬
‫آشنایی با ایجاد یک پروسیجر ذخیره شده ساده‬
CREATE PROCEDURE [dbo].[GetAllConcat]
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM
[AdventureWorks].[Person].[Contact];
END
GO
‫آشنایی با اجرای یک پروسیجر ذخیره شده ساده‬
Stored Procedures ‫ایجاد و مدیریت‬
CREATE PROCEDURE dbo.ListEmployees
AS
Begin
SET NOCOUNT ON
SELECT EmployeeID, LastName, FirstName
FROM dbo.Employees;
End
Stored Procedures ‫ایجاد و مدیریت‬
EXECUTE dbo.ListEmployees;
EXEC dbo.ListEmployees;
Stored Procedures ‫ایجاد و مدیریت‬
CREATE PROC dbo.ListEmployeesByCity
@City varchar(25)
AS
SET NOCOUNT ON
SELECT LastName, FirstName, City
FROM dbo.Employees
WHERE City = @City;
Stored Procedures ‫ایجاد و مدیریت‬
EXEC dbo.ListEmployeesByCity @City = 'London';
EXEC dbo.ListEmployeesByCity 'London';
Stored Procedures ‫ایجاد و مدیریت‬
CREATE PROC dbo.ListEmployeesOptionalCity
@City varchar(25) = NULL
AS
SET NOCOUNT ON
IF @City IS NULL
SELECT LastName, FirstName, City
FROM dbo.Employees;
ELSE
SELECT LastName, FirstName, City
FROM dbo.Employees
WHERE City = @City;
Stored Procedures ‫ایجاد و مدیریت‬
-- Lists employees in London
EXEC dbo.ListEmployeesOptionalCity @City = 'London';
-- Lists all employees
EXEC dbo.ListEmployeesOptionalCity;
CREATE PROC dbo.InsertShipper
@CompanyName nvarchar(40) = NULL,
@Phone nvarchar(24) = NULL,
@ShipperID int = NULL OUTPUT
AS
SET NOCOUNT ON
IF @CompanyName IS NULL
SET @ShipperID = 0;
ELSE
BEGIN
INSERT INTO dbo.Shippers(CompanyName, Phone)
VALUES (@CompanyName, @Phone);
SET @ShipperID = SCOPE_IDENTITY();
END
Stored Procedures ‫ایجاد و مدیریت‬
DECLARE @NewShipperID int
EXEC dbo.InsertShipper
@CompanyName = ‘Fantazio',
@Phone = NULL,
@ShipperID = @NewShipperID OUTPUT;
SELECT @NewShipperID AS ShipperID;
Stored Procedures ‫ایجاد و مدیریت‬
CREATE PROC dbo.InsertShipperReturn
@CompanyName nvarchar(40) = NULL,
@Phone nvarchar(24) = NULL
AS
SET NOCOUNT ON
IF @CompanyName IS NULL
RETURN 0;
ELSE
BEGIN
DECLARE @NewID int;
INSERT INTO dbo.Shippers (CompanyName, Phone)
VALUES (@CompanyName , @Phone);
SET @NewID = SCOPE_IDENTITY();
RETURN @NewID
END
Stored Procedures ‫ایجاد و مدیریت‬
DECLARE @NewShipperID int;
EXEC @NewShipperID = dbo.InsertShipperReturn
@CompanyName = ‘Fantazio',
@Phone = NULL;
SELECT @NewShipperID AS ShipperID;
Stored Procedures ‫ویرایش و حذف‬
-- ALTER ‫ویرایش روالهای ذخیره شده با کلمه کلیدی‬
ALTER PROCEDURE dbo.ListEmployees
AS
SET NOCOUNT ON
SELECT EmployeeID, LastName, FirstName
FROM dbo.Employees;
-- DROP ‫حذف روالهای ذخیره شده با کلمه کلیدی‬
DROP PROC dbo.ListEmployees
‫نماها‬
Views
CREATE VIEW dbo.vwEmployeeAddressLabels
AS
SELECT FirstName + SPACE(1) + LastName +
Char(13) + Char(10) +
Address + Char(13) + Char(10) +
City +
CASE
WHEN Region IS NULL THEN ' ' + PostalCode
ELSE ', ' + Region +
' ' + PostalCode
END
+
Char(13) + Char(10) + Country
AS Address
FROM dbo.Employees;
Views ‫ایجاد و مدیریت‬
SELECT * FROM dbo.vwEmployeeAddressLabels;
Views ‫ویرایش و حذف‬
-- ALTER ‫ویرایش نماهابا کلمه کلیدی‬
ALTER VIEW dbo.ListEmployees
SELECT EmployeeID, LastName, FirstName
FROM dbo.Employees;
-- DROP ‫حذف نماها با کلمه کلیدی‬
DROP VIEW dbo.ListEmployees
‫موفق باشید‬