Подмножество sql для манипулирования данными: предложения insert, delete, update. Правила написания запросов: предложение select.

Существуют четыре предложения подмножества SQL для манипулирования данными:

· SELECT – выборка данных из таблиц

· INSERT – вставка данных

· DELETE –удаление данных

· UPDATE –обновление данных

 

Для вставки новых строк в таблицы используется предложение INSERT. Пример использования предложения INSERT для вставки строки в таблицу:

INSERT INTO Tabl4 (A,C,D,F,G) VALUES (1,‘asdf’,’05/10/2003’,20,7)

При использовании предложения INSERT указывается имя таблицы, в которую вставляются новые данные, имена колонок таблицы и значения, соответствующие заданным колонкам таблицы. Для автоинкрементных полей значения задавать не следует. В списке вставляемых значений также могут быть пропущены значения для колонок, имеющих признак обязательности NULL. При использовании предложения INSERT имеется возможность вставлять сразу несколько строк, которые являются результатом выполнения предложения SELECT:

INSERT INTO Tabl1 SELECT A,C FROM Tabl4 WHERE A>5

 

Для удаления строк из таблицы в языке SQL используется предложение DELETE. Пример удаления всех строк из таблицы Tabl4: DELETE FROM Tabl4

В случае если требуется удалить часть строк таблицы, то необходимо задать условие удаления: DELETE FROM Tabl4 WHERE (A =5) or (A=10)

DELETE FROM Tabl4 WHERE C like ‘a%c’

В данном примере удаляются строки, в которых значение С с ‘а’ и заканчивается на ‘с’.

 

Для обновления данных в таблицах применяется предложение UPDATE. При использовании предложения UPDATE требуется указать:

· таблицу, данные которой подвергаются обновлению,

· колонки таблицы, для которых задаются новые значения, условие для строк, которые подвергаются обновлению.

пр: UPDATE tb1 SET prov = prov + 1 WHERE fld < 2;

 

Для выборки данных из таблицы используется предложение SELECT.

SELECT -- ALL ------- схема, столбец --

-- DISTINCT ----------------------- список столбцов возвращаемых запросом

FROM -- схема, таблица.. ---------------- обязательно, список таблиц для выполнения запроса

WHERE -- условие поиска ----------------- условие поиска (часть строк, подходящих условию)

GROUP BY -- схема, столбец ------------- итоговой запрос разбивается на группы

HAVING -- условие поиска -----------------условие возврата групп(только вместе с GROUP BY)

ORDER BY -- спецификатор сортировки -- определяет порядок сортировки результата

 

Рассмотрим работу SELECT на основе таблиц:

Branch (Bno, Street, Area, City, Tel_No, Fax_No)

Staff (Sno, FName, LName, Address, Tel_No, Position, DOB, Salary, Bno)

Property_For_Rent (Pno, Street, Area, City, Rooms, Type,Rent,Ono,Sno,Bno)

Renter (Rno, FName, LName, Address, Tel_No, Bno)

Owner (Ono, FName, LName, Address, Tel_No)

Viewing (Rno, Pno, Date, Comment)

Выборка всех строк

Составить список всех сведений о сотрудниках

SELECT * FROM Staff

SELECT Sno,FName,LName, Address, Tel_No, Position, DOB, Salary, Bno FROM Staff

 

Создание отчета о заработной плате всех работников с указанием только табельного номера работника, имени и фамилии.

SELECT Sno,Fname,Lname,Salary FROM Staff

 

Составить список номеров сдаваемых в аренду объектов, осмотренных клиентами, с удалением из отчета дублированных строк

SELECT distinct Pno FROM Viewing

 

Создать отчет о годовой зарплате персонала

SELECT Sno, Fname, Lname, Salary*12 AS Annul_Salary FROM Staff

 

Выборка части строк

Сравнение

Получить отчет о персонале с размером заработной платы больше 10000 рублей в месяц

SELECT Sno, Fname, Lname, Salary FROM Staff WHERE Salary>10000

Перечислить адреса всех отделений в Москве и Твери

SELECT Bno, Street, Area, City FROM Branch WHERE city = ‘Москва’ OR city =’Тверь’

Использование диапазонов

Перечислить персонал с зарплатой от 20000 до 30000 рублей

SELECT Sno,Fname,Lname,Salary FROM Staff WHERE Salary>20000 AND Salary<30000

SELECT Sno,Fname,Lname,Salary FROM Staff WHERE Salary BETWEEN 20000 AND 30000

Вхождение во множество

Составить список всех руководителей и их заместителей

SELECT Sno, FName, LName, Position FROM Staff WHERE position IN (‘Manager’, ‘Deputy’)

Использование шаблона

Найти всех работников, проживающих в Москве

SELECT Sno, FName, LName, Address, Salary WHERE address like ‘%Москва%’

% - любая последовательность из нуля или более символов, _ - любой одиночный символ

Использование NULL в условии поиска

Составить отчет обо всех помещениях объекта с номером PG4, по которым не было предоставлено комментариев.

SELECT rno, date FROM viewing WHERE pno = ‘PG4’ AND comment is null

Сортировка результатов выборки

Отчет о зарплате всех работников компании в порядке убывания суммы зарплаты

SELECT sno, fname, lname, salary FROM staff ORDER BY salary desc

Сортировка по нескольким столбцам

Подготовить сокращенный список сдаваемых в аренду объектов, упорядоченный по типу

SELECT pno, type, rooms, rent FROM property_for_rent ORDER BY type, rent desc

– сортировка по величине арендной платы в пределах одного типа

Использование агрегатных функций

Определить количество сдаваемых в аренду объектов недвижимости со ставкой арендной платы меньше 10000 рублей в месяц

SELECT count(*) AS Cnt FROM property_for_rent WHERE rent<10000

 

Определить, сколько различных сдаваемых в аренду объектов было осмотрено клиентами в мае 2000 года

SELECT count(distinct pno) AS Cnt FROM viewing

WHERE date BETWEEN ‘05/01/00’ AND ‘05/31/00’

 

Определить общее кол-во менеджеров компании и вычислить сумму их годовой зарплаты

SELECT count(sno) AS cnt, sum(salary)*12 AS sum FROM straff WHERE position = ‘manager’

 

Вычислить размер минимальной, максимальной и средней зарплаты

SELECT min(salary) AS min, max(salary) AS max, avg(salary) AS avg FROM staff

Группировка результатов

Определить, количество персонала в каждом из отделений, а также суммарную зарплату.

SELECT bno, count(sno) AS Cnt, sum(salary) AS sum FROM staff

GROUP BY bno ORDER BY bno

 

Для каждого отделения компании с численностью персонала более пяти человек определить количество работающих и сумму их заработной платы

SELECT bno, count(sno) AS Cnt, sum(salary) AS sum FROM staff

GROUP BY bno HAVING count(sno)>5 ORDER BY bno

Подзапросы

Составить список персонала, работающего в отделении компании, расположенном на улице ‘Киевская’ в доме 16

SELECT sno, fname, lname, position FROM staff

WHERE bno = (SELECT bno FROM branch WHERE street =’Киевкая 16’)

 

Составить список всех сотрудников, имеющих зарплату выше средней, указав то, насколько их зарплата превышает среднюю зарплату по предприятию

SELECT sno, fname, lname, position, salary – (SELECT avg(salary) FROM staff) AS diff

FROM staff WHERE salary > (SELECT avg(salary) FROM staff)

 

Составить перечень сдаваемых в аренду объектов, за которые отвечают работники отделения компании, расположенного на улице ‘Киевская’ в доме 16

SELECT pno, street, area, city, pcode, type, rooms, rent FROM property_for_rent

WHERE sno IN (SELECT sno FROM staff WHERE bno = (SELECT bno FROM branch

WHERE street =’Киевкая 16’))

Использование ключевых слов ANY (SOME) и ALL

Найти всех работников, чья зарплата превышает зарплату хотя бы одного работника отделения компании под номером ‘B3’

SELECT sno, fname, lname, position, salary FROM staff

WHERE salary >SOME (SELECT salary FROM staff WHERE bno=’B3’)

 

Найти всех работников, чья заработная плата больше платы любого работника отделения компании под номером ‘B3’

SELECT sno, fname, lname, position, salary FROM staff

WHERE salary > ALL (SELECT salary FROM staff WHERE bno=’B3’)

Многотабличные запросы

Составить список имен всех клиентов, которые уже осмотрели хотя бы один сдаваемый в аренду объект недвижимости и сообщили свое мнение о нем.

SELECT r.rno, fname, lname,comment FROM renter r, viewing v WHERE r.rno=v.rno

 

Для каждого отделения перечислите работников, отвечающих за какие-либо арендуемые объекты, с указанием города, в котором данное отделение, и номеров объектов

SELECT b.bno, b.city, s.sno, fname,lname,pno FROM branch b, staff s, property_for_rent p

WHERE b.bno=s.bno and s.sno=p.sno ORDER BY b.bno,s.sno,pno

 

Левое открытое соединение

Перечислить отделения компании и сдаваемые в аренду объекты, которые расположены в одном и том же городе, а также прочие отделения компании

SELECT b.*, p.* FROM branch b LEFT JOIN property_for_rent p ON b.city=p.city

 

Полное открытое соединение

Перечислить отделения компании и сдаваемые в аренду объекты, которые расположены в одном и том же городе, а также прочие отделения компании и объекты недвижимости

SELECT b.*, p.* FROM branch FULL JOIN property_for_rent p ON b.city=p.city

 

EXISTS

Перечислить всех сотрудников компании, которые работают в ее московском отделении

SELECT sno, fname, lname, position FROM staff s WHERE EXISTS (SELECT * FROM branch b WHERE s.bno=b.bno AND city = ‘Москва’)

 

Union

Создать список всех регионов, в которых либо находятся отделения компании, либо располагаются сдаваемые в аренду объекты

SELECT area FROM branch WHERE area is not null UNION

SELECT area FROM property_for_rent WHERE area is not null)

 

Intersect

Создать список всех городов, в которых располагаются и отделения, и арендуемые объекты

SELECT city FROM branch INTERSECT SELECT city FROM property_for_rent