+- انجمن وب سایت مشاوره در زمینه پروژه های برنامه نویسی و طراحی وب سایتهای تجاری (http://forum.a00b.com)
+-- انجمن: سوالها و مقاله های آموزشی (/forumdisplay.php?fid=1)
+--- انجمن: مقاله های آموزشی (/forumdisplay.php?fid=3)
+--- موضوع: آموزش برخی از دستورات SQL Server به همراه مثال ساده (/showthread.php?tid=364)
آموزش برخی از دستورات SQL Server به همراه مثال ساده - ali - 11-10-2023 09:01 AM
لیست برخی از دستورات در پایگاه داده sql server به همراه نحوه استفاده از آنها:
کد:
1. CREATE DATABASE:
CREATE DATABASE MyDatabase;
2. CREATE TABLE:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DepartmentID INT
);
3. ALTER TABLE:
ALTER TABLE Employees
ADD Salary DECIMAL(10, 2);
4. DROP TABLE:
DROP TABLE Employees;
5. INSERT INTO:
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID)
VALUES (1, 'John', 'Doe', 101);
6. UPDATE:
UPDATE Employees
SET Salary = 50000
WHERE EmployeeID = 1;
7. DELETE FROM:
DELETE FROM Employees
WHERE EmployeeID = 1;
8. SELECT:
SELECT * FROM Employees;
9. DISTINCT:
SELECT DISTINCT DepartmentID
FROM Employees;
10. WHERE:
SELECT * FROM Employees
WHERE DepartmentID = 101;
11. ORDER BY:
SELECT * FROM Employees
ORDER BY LastName;
12. GROUP BY:
SELECT DepartmentID, COUNT(*)
FROM Employees
GROUP BY DepartmentID;
13. HAVING:
SELECT DepartmentID, COUNT(*)
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;
14. INNER JOIN:
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
15. LEFT JOIN:
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
16. RIGHT JOIN:
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
17. FULL JOIN:
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
FULL JOIN Departments d ON e.DepartmentID = d.DepartmentID;
18. UNION:
SELECT FirstName, LastName
FROM Employees
UNION
SELECT ManagerFirstName, ManagerLastName
FROM Managers;
19. UNION ALL:
SELECT FirstName, LastName
FROM Employees
UNION ALL
SELECT ManagerFirstName, ManagerLastName
FROM Managers;
20. EXISTS:
SELECT *
FROM Employees e
WHERE EXISTS (SELECT * FROM Managers m WHERE e.EmployeeID = m.EmployeeID);
21. NOT EXISTS:
SELECT *
FROM Employees e
WHERE NOT EXISTS (SELECT * FROM Managers m WHERE e.EmployeeID = m.EmployeeID);
22. IN:
SELECT *
FROM Employees
WHERE DepartmentID IN (101, 102);
23. NOT IN:
SELECT *
FROM Employees
WHERE DepartmentID NOT IN (101, 102);
24. BETWEEN:
SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2022-01-01' AND '2022-01-31';
25. LIKE:
SELECT *
FROM Products
WHERE ProductName LIKE 'App%';
26. IS NULL:
SELECT *
FROM Customers
WHERE Email IS NULL;
27. IS NOT NULL:
SELECT *
FROM Customers
WHERE Email IS NOT NULL;
28. AVG():
SELECT AVG(Salary)
FROM Employees;
29. SUM():
SELECT SUM(Salary)
FROM Employees;
30. COUNT():
SELECT COUNT(*)
FROM Employees;
31. MAX():
SELECT MAX(Salary)
FROM Employees;
32. MIN():
SELECT MIN(Salary)
FROM Employees;
33. GROUP_CONCAT():
SELECT DepartmentID, GROUP_CONCAT(LastName)
FROM Employees
GROUP BY DepartmentID;
34. CASE WHEN:
SELECT FirstName,
CASE WHEN Salary > 50000 THEN 'High'
WHEN Salary > 30000 THEN 'Medium'
ELSE 'Low'
END AS SalaryLevel
FROM Employees;
35. DATEPART():
SELECT DATEPART(YEAR, OrderDate) AS OrderYear,
DATEPART(MONTH, OrderDate) AS OrderMonth,
COUNT(*) AS TotalOrders
FROM Orders
GROUP BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate);
36. DATEDIFF():
SELECT DATEDIFF(DAY, OrderDate, ShippedDate) AS DaysToShip
FROM Orders;
37. DATEADD():
SELECT DATEADD(MONTH, 3, HireDate) AS ProbationEndDate
FROM Employees;
38. UPPER():
SELECT UPPER(LastName)
FROM Employees;
39. LOWER():
SELECT LOWER(LastName)
FROM Employees;
40. LEFT():
SELECT LEFT(FirstName, 3)
FROM Employees;
41. RIGHT():
SELECT RIGHT(LastName, 3)
FROM Employees;
42. LEN():
SELECT LEN(FirstName) AS FirstNameLength
FROM Employees;
43. ROUND():
SELECT ROUND(Salary, 2) AS RoundedSalary
FROM Employees;
44. CAST():
SELECT CAST(Salary AS INT) AS RoundedSalary
FROM Employees;
45. CONVERT():
SELECT CONVERT(VARCHAR(10), HireDate, 101) AS FormattedHireDate
FROM Employees;
46. COALESCE():
SELECT COALESCE(ManagerFirstName, 'No Manager') AS ManagerName
FROM Employees;
47. NULLIF():
SELECT NULLIF(ManagerFirstName, '') AS ManagerName
FROM Employees;
48. TOP():
SELECT TOP 10 *
FROM Products;
49. ROW_NUMBER():
SELECT ROW_NUMBER() OVER (ORDER BY LastName) AS RowNumber,
FirstName,
LastName
FROM Employees;
50. RANK():
SELECT RANK() OVER (ORDER BY Salary DESC) AS Rank,
FirstName,
LastName,
Salary
FROM Employees;
51. DENSE_RANK():
SELECT DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank,
FirstName,
LastName,
Salary
FROM Employees;
52. NTILE():
SELECT NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile,
FirstName,
LastName,
Salary
FROM Employees;
53. CTE (Common Table Expression):
WITH EmployeeCTE AS (
SELECT EmployeeID, FirstName, LastName, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber
FROM Employees
)
SELECT EmployeeID, FirstName, LastName, Salary
FROM EmployeeCTE
WHERE RowNumber <= 10;
54. INDEXES:
CREATE INDEX IX_DepartmentID ON Employees(DepartmentID);
55. TRIGGERS:
CREATE TRIGGER trgAfterInsertEmployee
ON Employees
AFTER INSERT
AS
BEGIN
-- Trigger logic here
END;
56. STORED PROCEDURES:
CREATE PROCEDURE spGetEmployeeByID @EmployeeID INT
AS
BEGIN
-- Procedure logic here
END;
57. VIEWS:
CREATE VIEW vwHighSalaryEmployees AS
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;
58. TRANSACTIONS:
BEGIN TRANSACTION;
-- SQL statements here
59. JOINS with USING clause:
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
JOIN Departments d USING (DepartmentID);
60. GRANT PERMISSIONS:
GRANT SELECT ON Employees TO user1;