SQL Server. Работа с SELECT. Операции удаления, вставки и обновления данных

Выборка данных через SELECT

Этот оператор используется для извлечения записей из одной или нескольких таблиц в базе данных SQL Server.

Простейшая форма оператора SELECT:

SELECT {выражение}
FROM {список таблиц}
WHERE {условия};

Полная форма оператора:

SELECT [ ALL | DISTINCT ] [ TOP (top_value) [ PERCENT ] [ WITH TIES ] ] {выражение}
FROM {таблица}
WHERE conditions
[ GROUP BY expressions ]
[ HAVING condition ]
[ ORDER BY expression [ ASC | DESC ];

Используемые параметры и аргументы:

ALL Необязательный аргумент. Возвращать все подходящие записи.
DISTINCT Необязательный аргумент. Удалять дубликаты из результирующего набора.
TOP (top_value) Необязательный аргумент. Если аргумент указан, запрос вернет верхнее количество строк результирующего набора на основе top_value. TOP(10) вернет первые верхние 10 строк из результирующего набора.
PERCENT Необязательный аргумент. Если указан, запрос вернет верхнее количество строк на основе значения переданного процента от общего числа строк, равного 100%.
WITH TIES Необязательный аргумент. Если указан, будут возвращены дополнительные строки из результирующего набора, которые соответствуют последнему значению из TOP на основе ORDER BY. Аргумент может быть указан только в SELECT выражениях и только, если указано значение сортировки ORDER BY.

Например, пусть результирующим набором без учета TOP у нас является: 1, 1, 1, 1, 2, 2, 2, 3.

Запрос имеет такую форму:

SELECT TOP(5) WITH TIES column1
from {table}
ORDER BY column1

Итоговый результат будет дополнен всеми значениями “2”: 1, 1, 1, 1, 2, 2, 2.

SELECT {выражение} Колонки или вычисления, которые вы хотите вернуть в результате.
FROM {таблицы} Таблицы, из которых вы хотите вернуть записи. Как минимум одна таблица должна быть указана в пункте FROM.
WHERE {условия} Список условий, которым должны удовлетворять выбранные в результате записи.
GROUP BY Необязательный аргумент. Собирает результаты в группы по одной или нескольким колонкам.
HAVING Необязательный аргумент. Используется в комбинации с GROUP BY, чтобы ограничить группы возвращаемых строк только теми, для которых условие Истина.
ORDER BY Необязательный аргумент. Используется для сортировки записей в результирующем наборе.

Создадим таблицу, для этого используем вот такой код:

CREATE TABLE Students
(
    Id INT PRIMARY KEY IDENTITY,
    email NVARCHAR(256) UNIQUE,
    firstName NVARCHAR(256) NOT NULL,
    lastName NVARCHAR(256) NOT NULL,
    address NVARCHAR(256) NOT NULL,
    award money
)

Откроем management studio и выберем нужную базу данных (или создадим новую), затем наживаем кнопку "создать запрос" и вставляем код.

Несколько слов по коду: столбец id устанавливаем атрибут PRIMARY KEY (первичный ключ), атрибут IDENTITY инкрементирует на единицу значение этого столбца у последней записи (т.е. значение увеличивается на 1).

Атрибут UNIQUE контролирует уникальность email адреса в столбце email (не позволит добавить несколько одинаковых адресов в таблицу).

Атрибут NOT NULL не допускает значение NULL в столбце.

INT, NVARCHAR, money - типы данных.

После этого нажимаем "выполнить", внизу видим сообщение "Выполнение команд успешно завершено." После этого слева в списке таблиц должна появиться нужная нам таблица, если этого не произошло, нажимаем кнопку "обновить" или F5.

Т.к. в таблице пока нет данных можно выполнить вот такой запрос (наполним таблицу некоторыми данными):

INSERT INTO Students VALUES('str@gmail.com', 'Иван', 'Иванов', 'г. Рязань, ул. Ленина 54/2',50000),
('str@yandex.ru', 'Петр', 'Петров', 'г. Рязань, ул. Ленина 54/3',50000),
('ilya@gmail.com', 'Илья', 'Ильин', 'г. Рязань, ул. Ленина 54/4',40000),
('vp@gmail.com', 'Иван', 'Прохоров', 'г. Рязань, ул. Ленина 57/8',40000),
('bak@gmail.com', 'Борис', 'Акунин', 'г. Москва, ул. Лебедева 23/21',60000),
('el@gmail.com', 'Екатерина', 'Ларина', 'г. Шахты, ул. Пражская 4/9',60000),
('eb@gmail.com', 'Елизавета', 'Бродская', 'г. Рязань, ул. Ленина 54/2', 90000)

1. Пример запроса выбирающего все данные из таблицы:

select * from  Students

Результатом данного запроса является выборка всех данных из таблицы Students.

1	str@gmail.com	Иван	Иванов	г. Рязань, ул. Ленина 54/2	50000,00
2	str@yandex.ru	Петр	Петров	г. Рязань, ул. Ленина 54/3	50000,00
3	ilya@gmail.com	Илья	Ильин	г. Рязань, ул. Ленина 54/4	40000,00
4	vp@gmail.com	Иван	Прохоров	г. Рязань, ул. Ленина 57/8	40000,00
5	bak@gmail.com	Борис	Акунин	г. Москва, ул. Лебедева 23/21	60000,00
6	el@gmail.com	Екатерина	Ларина	г. Шахты, ул. Пражская 4/9	60000,00
7	eb@gmail.com	Елизавета	Бродская	г. Рязань, ул. Ленина 54/2	90000,00

2. Пример запроса с условием:

select * 
from  Students 
where email = 'str@gmail.com'

Результатом данного запроса является выборка из таблицы записей (записи) с email адресом str@gmail.com.

1	str@gmail.com	Иван	Иванов	г. Рязань, ул. Ленина 54/2	50000,00

3. Пример запроса на выборку первых нескольких значений:

SELECT distinct TOP(3) award  
FROM Students 
order by award desc

Результатом запроса является выборка первых трех разных (distinct) записей из таблицы упорядоченных по убыванию.

90000,00
60000,00
50000,00

4. Пример запроса выбора случайной записи из таблицы:

SELECT TOP 1 email FROM Students
ORDER BY NEWID()

Результатом запроса является выборка случайного email из таблицы Students.

5. Пример запроса с сортировкой данных:

select email, lastName 
from  Students 
where id >4 
order by email

Результатом данного является выборка столбцов email, lastName отсортированных по email.

bak@gmail.com	Акунин
eb@gmail.com	Бродская
el@gmail.com	Ларина

6. Пример запроса с группировкой данных:

select award, count (*) as 'Кол-во студентов' 
from students
group by award

Результатом данного запроса будет выборка всех премий, а также сколько одинаковых премий у студентов.

7. Пример запроса с группировкой данных и фильтрацией групп (HAVING):

select award, count (*) as 'Кол-во студентов' 
from students 
group by award 
HAVING count(*)>1

Результатом запроса является выборка премий, которые встречаются больше одного раза.

40000,00	2
50000,00	2
60000,00	2

8. Пример запроса с подзапросом:

SELECT *
FROM Students
WHERE award = (SELECT MIN(award) FROM Students)

Результатом запроса является выборка студентов из таблицы с самой минимальной премией.

3	ilya@gmail.com	Илья	Ильин	г. Рязань, ул. Ленина 54/4	40000,00
4	vp@gmail.com	Иван	Прохоров	г. Рязань, ул. Ленина 57/8	40000,00

9. Пример запроса выбора среднего значения:

SELECT * 
FROM Students
WHERE award > (SELECT avg(award) FROM Students)

Результатом запроса является выборка всех студентов у которых премия выше средней.

5	bak@gmail.com	Борис	Акунин	г. Москва, ул. Лебедева 23/21	60000,00
6	el@gmail.com	Екатерина	Ларина	г. Шахты, ул. Пражская 4/9	60000,00
7	eb@gmail.com	Елизавета	Бродская	г. Рязань, ул. Ленина 54/2	90000,00

10. Пример запроса с проверкой некоторого значения, в зависимости от проверки возвращается выбранный результат:

SELECT award, email,
    CASE award
        WHEN 50000 THEN 'премия маленькая'
        WHEN 60000 THEN 'премия средняя'
        WHEN 90000 THEN 'премия высокая'
        ELSE 'так себе'
    END AS EvaluateCount
FROM Students

Результатом запроса является последовательное сравнивание премий со значениями после операторов WHEN. В зависимости от значения столбца award функция CASE будет возвращать одну из строк, которая идет после соответствующего оператора THEN.

50000,00	str@gmail.com	премия маленькая
50000,00	str@yandex.ru	премия маленькая
40000,00	ilya@gmail.com	так себе
40000,00	vp@gmail.com	так себе
60000,00	bak@gmail.com	премия средняя
60000,00	el@gmail.com	премия средняя
90000,00	eb@gmail.com	премия высокая

Изменение данных в БД через insert, update, delete

Язык обработки данных DML - инструкции предназначены для добавления данных, изменения данных, запроса данных и удаления данных из базы данных SQL Server.

Оператор UPDATE - обновление строк таблицы

UPDATE изменяет существующие данные в таблице. Общий синтаксис:

UPDATE table_name
SET column1=value1, column2=value2, ...
WHERE some_column = some_value;

Пример простого запроса update:

Оператор DELETE - удаление строк таблицы

DELETE удаляет одну или несколько строк из таблицы или представления. Общий синтаксис:

DELETE FROM table_name
WHERE some_column = some_value;

Пример простого запроса delete:

Оператор INSERT - вставка данных

INSERT добавляет одну или несколько строк в таблицу или представление. Существует две формы использования оператора INSERT.

Первая не содержит определение имен колонок, куда будут вставлены данные, а только их значения, подразумевается, что порядок передачи данных будет соответствовать определению колонок в БД, иначе возникнет ошибка

INSERT INTO table_name 
VALUES (value1,value2,value3,...)

Вторая форма определяет название колонок, куда будут вставлены данные

INSERT INTO table_name (column1,column2,column3,...) 
VALUES (value1,value2,value3,...);

Пример простого запроса insert:

В этом запросе добавляем новую строку в таблицу rp_clients. Т.к. id у нас с автоинкрементом (добавляется автоматически), поэтому в значениях (values) указываем только name, phone, adress.

Альтернативный вариант добавления в таблицу - через запрос SELECT:

insert into table (a1, a2,a3)
select x1, x2, x3 from table1

ВАЖНО. Всегда указывайте в insert столбцы таблицы. Синтаксис позволяет их не указывать, но лучше указывать. Дело в том, что при изменении структуры таблицы (добавили колонки), запрос начнет выполняться с ошибками из за несоответствия столбцов в insert into и select. Поэтому в явном виде прописывайте все необходимые столбы создаваемой строки.

TRUNCATE TABLE удаляет все строки в таблице, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций. Синтаксис: