Язык SQL. DML. (Лекция 12)

Июль 29, 2022

Главная
Информатика
Язык SQL. DML. (Лекция 12)

Содержание

2. DML INSERT UPDATE DELETE 18.11.2017 Горбунов О.Е.
3. INSERT Однострочный оператор: INSERT INTO [( )] VALUES ( ); Пример: INSERT INTO Student (Number, LastName,
4. INSERT INSERT INTO employee(ID, FirstName, LastName, Email, HireDate, Job_ID, Salary, CommissionPct, Manager_ID, Department_ID) VALUES (113, ‘Louis’,
5. INSERT Многострочный оператор: INSERT INTO [( )] оператор SELECT Пример: INSERT INTO Sales_reps(ID, Name, Salary, Commission_pct)
6. UPDATE UPDATE SET = [, …] [WHERE ] Примеры: UPDATE Employee SET Department_id = 50 WHERE
7. UPDATE UPDATE Employee SET (Job_id, Salary) = SELECT (Job_id, Salary FROM Employee WHERE ID = 205)
8. DELETE DELETE FROM [WHERE ]; Пример: DELETE FROM Department WHERE ID = 123; DELETE FROM Copy_emp;
9. DELETE DELETE FROM Employee WHERE Department_ID IN (SELECT Department_ID FROM Deartment WHERE Name LIKE ‘%Public%’); 18.11.2017
11. Скачать презентацию

Слайд 2

DML
INSERT
UPDATE
DELETE
18.11.2017
Горбунов О.Е.

Слайд 3

INSERT
Однострочный оператор:
INSERT INTO <таблица> [(<список столбцов>)] VALUES (<список значений>);
Пример: INSERT INTO Student (Number,

LastName, FirstName, Group, BirthDate, Salary) VALUES (1003456, 'Иванов', 'Петр', NULL, '01.01.1990', 1300)

18.11.2017

Горбунов О.Е.

Слайд 4

INSERT
INSERT INTO employee(ID, FirstName,
LastName, Email, HireDate, Job_ID, Salary, CommissionPct, Manager_ID, Department_ID)
VALUES

(113, ‘Louis’, ‘Popp’, ‘lPopp@gmail.com’,
SYSDATETIME(), 15, 6900, NULL, 205, 110);

18.11.2017

Горбунов О.Е.

Слайд 5

INSERT
Многострочный оператор: INSERT INTO <таблица> [(<список столбцов>)] оператор SELECT
Пример:
INSERT INTO Sales_reps(ID, Name,

Salary, Commission_pct)
SELECT Employee_id, Last_name, Salary, Commission_pct
FROM employee
WHERE job_id LIKE ‘%REP%’;

18.11.2017

Горбунов О.Е.

Слайд 6

UPDATE
UPDATE <таблица> SET <столбец1> = <значение1> [, …] [WHERE <условие отбора>]
Примеры:
UPDATE Employee
SET

Department_id = 50
WHERE ID = 113;
UPDATE Copy_emp
SET Department_id = 110;

18.11.2017

Горбунов О.Е.

Слайд 7

UPDATE
UPDATE Employee
SET (Job_id, Salary) =
SELECT (Job_id, Salary
FROM Employee
WHERE ID =

205)
WHERE ID = 103;
UPDATE Employee
SET Job_id = SELECT Job_id FROM Employee WHERE ID = 205, Salary = SELECT Salary FROM Employee WHERE ID = 205
WHERE ID = 103;

18.11.2017

Горбунов О.Е.

Слайд 8

DELETE
DELETE FROM <таблица>
[WHERE <условие отбора>];
Пример:
DELETE FROM Department
WHERE ID = 123;
DELETE

FROM Copy_emp;

18.11.2017

Горбунов О.Е.

Слайд 9

DELETE
DELETE FROM Employee
WHERE Department_ID IN
(SELECT Department_ID
FROM Deartment
WHERE Name LIKE ‘%Public%’);
18.11.2017
Горбунов О.Е.