💻 Примеры

💻 Примеры

Исходная таблица Employees

CREATE TABLE Employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Department VARCHAR(50),
    City VARCHAR(50),
    Salary DECIMAL(10, 2),
    HireDate DATE
);

INSERT INTO Employees (FirstName, LastName, Department, City, Salary, HireDate)
VALUES ('Alice', 'Green', 'Sales', 'Berlin', 55000.00, '2022-03-15'),
       ('Bob', 'Smith', 'Engineering', 'Hamburg', 60000.00, '2023-07-01'),
       ('Charlie', 'Johnson', 'Sales', 'Berlin', 52000.00, '2021-11-20'),
       ('Diana', 'Williams', 'HR', 'Munich', 58000.00, '2020-05-10'),
       ('Edward', 'Brown', 'Engineering', 'Berlin', 61000.00, '2024-01-15');

Представление на все строки

CREATE VIEW All_Employees_View AS
SELECT * FROM Employees;

Представление с ограниченным набором столбцов

CREATE VIEW Employee_Names_View AS
SELECT EmployeeID, FirstName, LastName
FROM Employees;

Представление с фильтром

CREATE VIEW Berlin_Employees_View AS
SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE City = 'Berlin';

Представление с вычисляемыми столбцами

CREATE VIEW Employee_Summary_View AS
SELECT
    EmployeeID,
    FirstName,
    LastName,
    Department,
    Salary,
    Salary * 12 AS Annual_Salary,
    CASE
        WHEN Salary >= 60000 THEN 'High'
        WHEN Salary >= 55000 THEN 'Medium'
        ELSE 'Low'
    END AS Salary_Level
FROM Employees;

Запрос к представлению

SELECT FirstName, LastName, Annual_Salary
FROM Employee_Summary_View
WHERE Salary_Level = 'High';

Представление с защитой от изменений

CREATE VIEW Berlin_Employees_Secure_View AS
SELECT * FROM Employees
WHERE City = 'Berlin'
WITH CHECK OPTION;

-- Ошибка: строка не попадает под условие WHERE
INSERT INTO Berlin_Employees_Secure_View (FirstName, LastName, City)
VALUES ('Frank', 'White', 'Hamburg');