جلسه 8پیادهسازي عملیات روي رابطه ها استاندارد و شرح دستورات تخصصي تعریف داده، دستکاري داده و مدیریت داده، ایجاد SQL زبان پرسوجوهاي نمونه اي روي پایگاه داده

تعریف داده (Data Definition Language - DDL)

این بخش به شما می‌آموزد که چگونه ساختار پایگاه داده خود را ایجاد و مدیریت کنید.

• تیتر: ایجاد و مدیریت جداول


• شرح دستورات:


• CREATE TABLE: برای ایجاد جداول جدید.


• مثال: CREATE TABLE Students (StudentID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), BirthDate DATE);


• ALTER TABLE: برای تغییر ساختار جداول موجود (افزودن، حذف یا تغییر ستون‌ها).


• مثال: ALTER TABLE Students ADD Email VARCHAR(100);


• DROP TABLE: برای حذف جداول.


• مثال: DROP TABLE Students;


• CREATE INDEX: برای ایجاد نمایه‌ها جهت افزایش سرعت جستجو.


• مثال: CREATE INDEX idx_student_lastname ON Students (LastName);


• DROP INDEX: برای حذف نمایه‌ها.


• مثال: DROP INDEX idx_student_lastname ON Students;


• نکات کلیدی:


• تعریف کلید اصلی (PRIMARY KEY) برای تضمین یکتایی رکوردها.


• استفاده از انواع داده مناسب (مانند INT, VARCHAR, DATE, BOOLEAN).


• تعریف محدودیت‌ها (CONSTRAINTS) مانند NOT NULL, UNIQUE, FOREIGN KEY.

بخش ۲: دستکاری داده (Data Manipulation Language - DML)

در این بخش، نحوه درج، به‌روزرسانی، حذف و بازیابی داده‌ها از جداول را فرا خواهید گرفت.

• 
  

  تیتر: عملیات پایه روی داده‌ها

  
  


• 
  

  شرح دستورات:

  
  


• 
  

  INSERT INTO: برای افزودن رکوردهای جدید به جدول.

  
  


• 
  

  مثال: INSERT INTO Students (StudentID, FirstName, LastName) VALUES (101, 'Ali', 'Rezaei');

  
  


• 
  

  UPDATE: برای به‌روزرسانی رکوردهای موجود.

  
  


• 
  

  مثال: UPDATE Students SET Email = 'ali.rezaei@example.com' WHERE StudentID = 101;

  
  


• 
  

  DELETE FROM: برای حذف رکوردها از جدول.

  
  


• 
  

  مثال: DELETE FROM Students WHERE StudentID = 101;

  
  


• 
  

  SELECT: برای بازیابی داده‌ها از یک یا چند جدول. این دستور پایه و اساس پرس‌وجوهاست.

  
  


• 
  

  مثال پایه: SELECT FirstName, LastName FROM Students;

  
  


• 
  

  نکات کلیدی:

  
  


• 
  

  اهمیت استفاده از شرط WHERE در دستورات UPDATE و DELETE برای جلوگیری از تغییر یا حذف ناخواسته داده‌ها.

  
  


• 
  

  آشنایی با عملگرهای مقایسه‌ای (=, >, <, >=, <=, !=) و منطقی (AND, OR, NOT).

  
  


• 
  

  تیتر: پرس‌وجوهای پیشرفته با SELECT

  
  


• 
  

  شرح دستورات:

  
  


• 
  

  ORDER BY: برای مرتب‌سازی نتایج بر اساس یک یا چند ستون (صعودی ASC یا نزولی DESC).

  
  


• 
  

  مثال: SELECT * FROM Students ORDER BY LastName ASC, FirstName ASC;

  
  


• 
  

  GROUP BY: برای گروه‌بندی ردیف‌ها با مقادیر مشابه در یک یا چند ستون، اغلب همراه با توابع تجمعی.

  
  


• 
  

  مثال: SELECT COUNT(StudentID), LastName FROM Students GROUP BY LastName;

  
  


• 
  

  توابع تجمعی (Aggregate Functions): مانند COUNT, SUM, AVG, MIN, MAX.

  
  


• 
  

  مثال: SELECT AVG(StudentID) FROM Students;

  
  


• 
  

  HAVING: برای فیلتر کردن گروه‌ها پس از اعمال GROUP BY.

  
  


• 
  

  مثال: SELECT LastName, COUNT(StudentID) FROM Students GROUP BY LastName HAVING COUNT(StudentID) > 5;

  
  


• 
  

  JOIN: برای ترکیب ردیف‌ها از دو یا چند جدول بر اساس ستون مرتبط. انواع رایج: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.

  
  


• 
  

  مثال INNER JOIN: SELECT Students.FirstName, Courses.CourseName FROM Students INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

  
  


• 
  

  نکات کلیدی:

  
  


• 
  

  درک تفاوت بین WHERE (فیلتر ردیف‌ها قبل از گروه‌بندی) و HAVING (فیلتر گروه‌ها بعد از گروه‌بندی).

  
  


• 
  

  اهمیت انتخاب نوع JOIN مناسب بسته به نیاز بازیابی داده‌ها.

  
  

بخش ۳: مدیریت داده (Data Control Language - DCL) و تراکنش‌ها (Transaction Control Language - TCL)

این بخش به مفاهیم امنیتی و مدیریت یکپارچگی داده‌ها در طول عملیات می‌پردازد.

• تیتر: کنترل دسترسی و مدیریت تراکنش‌ها


• شرح دستورات:


• GRANT: برای اعطای مجوزها به کاربران.


• مثال: GRANT SELECT ON Students TO 'public_user';


• REVOKE: برای پس گرفتن مجوزها.


• مثال: REVOKE DELETE ON Students FROM 'admin_user';


• COMMIT: برای ذخیره دائمی تغییرات تراکنش.


• ROLLBACK: برای بازگرداندن تغییرات تراکنش به حالت قبل.


• SAVEPOINT: برای ایجاد نقطه بازگشت در طول یک تراکنش طولانی.


• نکات کلیدی:


• اصول ACID (Atomicity, Consistency, Isolation, Durability) در تراکنش‌ها و اهمیت آن‌ها برای یکپارچگی داده‌ها.


• مدیریت امنیتی داده‌ها از طریق اعطای حداقل مجوزهای لازم به کاربران.

پرس‌وجوهای نمونه روی پایگاه داده (مثال فرضی)

فرض کنید پایگاه داده‌ای با جداول زیر داریم:

• Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary)


• Departments (DepartmentID, DepartmentName)

1. یافتن نام و نام خانوادگی تمام کارمندان به همراه نام دپارتمان آن‌ها:

                                            content_copy                        sql
    SELECT E.FirstName, E.LastName, D.DepartmentName

    FROM Employees E

    INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID;

2. یافتن کارمندانی که حقوقشان بیشتر از میانگین حقوق کل شرکت است:

                                            content_copy                        sql
    SELECT FirstName, LastName, Salary

    FROM Employees

    WHERE Salary > (SELECT AVG(Salary) FROM Employees);

3. شمارش تعداد کارمندان در هر دپارتمان و نمایش نام دپارتمان:

                                            content_copy                        sql
    SELECT D.DepartmentName, COUNT(E.EmployeeID) AS NumberOfEmployees

    FROM Employees E

    JOIN Departments D ON E.DepartmentID = D.DepartmentID

    GROUP BY D.DepartmentName

    ORDER BY NumberOfEmployees DESC;

4. یافتن کارمندانی که در دپارتمان ‘IT’ کار می‌کنند و حقوقشان بالاتر از 50,000,000 تومان است:

                                            content_copy                        sql
    SELECT E.FirstName, E.LastName

    FROM Employees E

    JOIN Departments D ON E.DepartmentID = D.DepartmentID

    WHERE D.DepartmentName = 'IT' AND E.Salary > 50000000;

خلاصه نکات کلیدی برای تدریس

• اهمیت SQL: زبان استاندارد و جهانی برای کار با پایگاه‌های داده رابطه‌ای.


• DDL vs DML vs DCL/TCL: تفکیک واضح بین تعریف ساختار، دستکاری داده‌ها، و کنترل دسترسی/تراکنش‌ها.


• کلید اصلی و خارجی: ستون فقرات روابط بین جداول و تضمین یکپارچگی داده‌ها.


• SELECT: قدرتمندترین دستور؛ تسلط بر فیلتر کردن (WHERE), مرتب‌سازی (ORDER BY), گروه‌بندی (GROUP BY, HAVING), و ترکیب جداول (JOIN) ضروری است.


• توابع تجمعی: ابزاری کلیدی برای خلاصه‌سازی و تحلیل داده‌ها.


• تراکنش‌ها: درک ACID برای اطمینان از صحت و قابلیت اطمینان عملیات پایگاه داده.


• کاربردی بودن: تاکید بر اینکه این مفاهیم پایه و اساس بسیاری از برنامه‌های کاربردی، تحلیل داده‌ها و مهندسی نرم‌افزار هستند.

تمرین پایان فصل (برای دانشجویان):

فرض کنید دو جدول Products (ProductID, ProductName, CategoryID, Price) و Categories (CategoryID, CategoryName) دارید.

1. یک جدول Products با فیلدهای مشخص شده ایجاد کنید.


2. چندین رکورد در جداول Products و Categories درج کنید.


3. لیست تمام محصولات به همراه نام دسته‌بندی آن‌ها را بازیابی کنید.


4. محصولاتی که قیمت آن‌ها بین 100,000 تا 500,000 تومان است را بیابید.


5. میانگین قیمت محصولات در هر دسته‌بندی را محاسبه کرده و نمایش دهید.


6. نام دسته‌بندی‌هایی که بیش از 3 محصول دارند را بیابید.


7. یک ستون StockQuantity به جدول Products اضافه کنید و سپس آن را برای یک محصول خاص به‌روزرسانی کنید.


8. محصولی که گران‌ترین قیمت را دارد، پیدا کنید.