SQL SELECT и запросы на выборку данных. Удаление записей из базы данных SQL Сложные запросы к базе данных MySQL

Содержание статьи
1. Самые простые MySQL запросы
2. Простые SELECT (выбрать) запросы
3. Простые INSERT (новая запись) запросы
4. Простые UPDATE (перезаписать, дописать) запросы
5. Простые DELETE (удалить запись) запросы
6. Простые DROP (удалить таблицу) запросы
7. Сложные MySQL запросы
8. MySQL запросы и переменные PHP

1. Самые простые SQL запросы

SHOW databases;
2. Выведет список ВСЕХ таблиц в Базе Данных base_name.

SHOW tables in base_name;

2. Простые SELECT (выбрать) запросы к базе данных MySQL

1. Выбирает ВСЕ данные в таблице tbl_name.

SELECT * FROM tbl_name;
2. Выведет количество записей в таблице tbl_name.

SELECT count(*) FROM tbl_name;
3. Выбирает (SELECT) из(FROM) таблицы tbl_name лимит (LIMIT) 3 записи, начиная с 2.

SELECT * FROM tbl_name LIMIT 2,3;
4. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id по порядку.

SELECT * FROM tbl_name ORDER BY id;
5. Выбирает (SELECT) ВСЕ записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id в ОБРАТНОМ порядке.

SELECT * FROM tbl_name ORDER BY id DESC;
6. Выбирает (SELECT ) ВСЕ (*) записи из (FROM ) таблицы users и сортирует их (ORDER BY ) по полю id в порядке возрастания, лимит (LIMIT ) первые 5 записей.

SELECT * FROM users ORDER BY id LIMIT 5;
7. Выбирает все записи из таблицы users , где поле fname соответствует значению Gena .

SELECT * FROM users WHERE fname="Gena";
8. Выбирает все записи из таблицы users , где значение поля fname начинается с Ge .

SELECT * FROM users WHERE fname LIKE "Ge%";
9. Выбирает все записи из таблицы users , где fname заканчивается на na , и упорядочивает записи в порядке возрастания значения id .

SELECT * FROM users WHERE fname LIKE "%na" ORDER BY id;
10. Выбирает все данные из колонок fname , lname из таблице users .

SELECT fname, lname FROM users;

11. Допустим у Вас в таблице пользовательских данных есть страна. Так вот если Вы хотите вывести ТОЛЬКО список встречающихся значений (чтобы, например, Россия не выводилось 20 раз, а только один), то используем DISTINCT. Выведет, из массы повторяющихся значений Россия, Украина, Беларусь. Таким образом, из таблицы users колонки country будут выведены ВСЕ УНИКАЛЬНЫЕ значения

SELECT DISTINCT country FROM users;
12. Выбирает ВСЕ данные строк из таблицы users где age имеет значения 18,19 и 21.

SELECT * FROM users WHERE age IN (18,19,21);
13. Выбирает МАКСИМАЛЬНОЕ значение age в таблице users . То есть если у Вас в таблице самое большее значение age (с англ. возраст) равно 55, то результатом запроса будет 55.

SELECT max(age) FROM users;
14. Выберет данные из таблицы users по полям name и age ГДЕ age принимает самое маленькое значение.

SELECT name, min(age) FROM users;
15. Выберет данные из таблицы users по полю name ГДЕ id НЕ РАВЕН 2.

SELECT name FROM users WHERE id!="2";

3. Простые INSERT (новая запись) запросы

1. Делает новую запись в таблице users , в поле name вставляет Сергей, а в поле age вставляет 25. Таким образом, в таблицу дописывается новая строки с данными значениями. Если колонок больше, то они оставшиеся останутся либо пустыми, либо с установленными по умолчанию значениями.

INSERT INTO users (name, age) VALUES ("Сергей", "25");

4. Простые UPDATE запросы к базе данных MySQL

1. В таблице users age становится 18.

UPDATE users SET age = "18" WHERE id = "3";
2. Всё то же самое, что и в первом запросе, просто показан синтаксис запроса, где перезаписываются два поля и более.
В таблице users ГДЕ id равно 3 значение поля age становится 18, а country Россия.

UPDATE users SET age = "18", country = "Россия" WHERE id = "3";

5. Простые DELETE (удалить запись) запросы к базе данных MySQL

1. Удаляет строку из таблицы users ГДЕ id равен 10.

DELETE FROM users WHERE id = "10";

6. Простые DROP (удалить таблицу) запросы к базе данных MySQL

1. Удаляет целиком таблицу tbl_name .

DROP TABLE tbl_name;

7. Сложные запросы к базе данных MySQL

SELECT id,name,country FROM users,admins WHERE TO_DAYS(NOW()) - TO_DAYS(registration_date) <= 14 AND activation != "0" ORDER BY registration_date DESC;
Данный сложный запрос ВЫБИРАЕТ колонки id,name,country В ТАБЛИЦАХ users,admins ГДЕ registration_date (дата) не старше 14 дней И activation НЕ РАВНО 0 , СОРТИРОВАТЬ по registration_date в обратном порядке (новое в начале).

UPDATE users SET age = "18+" WHERE age = (SELECT age FROM users WHERE male = "man");
Выше указан пример так называемого запроса в запросе в SQL. Обновить возраст среди пользователей на 18+, где пол - мужской. Подобные варианты запроса не рекомендую. По личному опыту скажу, лучше создать несколько отдельных - они будут прорабатываться быстрее.

8. Запросы к базе данных MySQL и PHP

1. Выбирает все записи из таблицы users , где поле fname соответствует значению переменной $name .

SELECT * FROM users WHERE fname="$name";
2. В таблице users ГДЕ id равно 3 значение поля age изменяется на значение переменной $age.

UPDATE users SET age = "$age" WHERE id = "3";

Внимание! Если Вам интересен какой-либо ещё пример, то пишите вопрос в комментарии!

SQL - Оператор SELECT

Название SQL (Structured Query Language – язык структурированных запросов) отражает тот факт, что запросы являются наиболее часто используемым элементом SQL. Запрос – это оператор, который посылает команду Системе Управления Базой Данных (СУБД) произвести манипуляцию или отобразить определенную информацию. Все запросы по выборке данных в SQL конструируются с помощью оператора SELECT. Он позволяет выполнять довольно сложные проверки и обработку данных.

Запрос может выводить данные из определенного столбца или изо всех столбцов таблицы. Чтобы создать простейших SELECT запрос, необходимо указать имя столбца и название таблицы.

Синтаксис оператора SELECT

SELECT Ключевое слово, которое сообщает базе данных о том, что оператор является запросом. Все запросы начинаются с этого слова, за ним следует пробел.

Column_list Список столбцов таблицы, которые выбираются запросом. Столбцы, не указанные в операторе, не будут включены в результат. Если необходимо вывести данные всех столбцов, можно использовать сокращенную запись. Звездочка (*) означает полный список столбцов.

FROM table_name Ключевое слово, которое должно присутствовать в каждом запросе. После него через пробел указывается имя таблицы, являющейся источником данных.

Код в скобках является не обязательным в операторе SELECT. Он необходим для более точного определения запроса.

Также необходимо сказать, что SQL код является регистронезависимым. Это означает, что запись SELECT можно написать как select. СУБД не отличит эти две записи, однако советуют все операторы SQL писать прописными буквами, чтобы его легко можно было отличить от другого кода.

Примеры рассмотрим на таблице Salespeople (продавцы) из классического учебника по SQL Мартина Грабера.
Вот MySQL код для создания тестовой таблицы:

CREATE TABLE `salespeople` (`snum` INTEGER(11) NOT NULL AUTO_INCREMENT, `sname` VARCHAR(20) COLLATE cp1251_general_ci DEFAULT NULL, `city` VARCHAR(20) COLLATE cp1251_general_ci DEFAULT NULL, `comm` DOUBLE(15,3) DEFAULT NULL, PRIMARY KEY (`snum`))ENGINE=InnoDB CHARACTER SET "cp1251" COLLATE "cp1251_general_ci" COMMENT="InnoDB free: 5120 kB"; INSERT INTO `salespeople` (`snum`, `sname`, `city`, `comm`) VALUES (1001,"Peel","London",0.120), (1002,"Serres","San Jose",0.130), (1003,"Axelrod","New York",0.100), (1004,"Motika","London",0.110), (1007,"Rifkin","Barcelona",0.150); COMMIT;

Таблица выглядит так:

Пример использования оператора SELECT

1. Необходимо вывести список продавцов, и отобразить их имена (sname)

SELECT sname FROM Salespeople

Результат:

3. Необходимо вывести всю таблицу

Для этого можно использовать разный синтаксис написания запросов. Перечисляем каждый столбец после оператора SELECT:

SELECT snum , sname , city , comm FROM Salespeople

Или можно добиться того же результата, используя сокращенную запись:

SELECT * FROM Salespeople Результат:

Так будут выведены 4 столбца, однако при такой записи можно легко закомментировать ненужный столбец. Например так:

SELECT /* snum , */ sname , city , comm FROM Salespeople

Теперь столбец snum выводиться не будет, потому что он закомментирован. Очень быстро и удобно. Как писать SQL код, решать конечно Вам, но знать такие вещи иногда бывает полезно.

Использование выражений (expressions) в операторе SELECT

Многие СУБД предоставляют специальные возможности по обработке результатов запроса. Набор таких средств в разных СУБД различен, однако существуют некоторые стандартные возможности, такие как выражения. Например может потребоваться выполнить простые математические операции над данными, чтобы представить их в более удобном виде, или вставить дополнительный текст в результат запроса. SQL позволяет размещать среди выбранных столбцов скалярные выражения и константы, которые могут дополнять или замещать столбцы в предложениях SELECT. Рассмотрим пример.

4. Необходимо вывести комиссионные (comm) продавцов в виде процентов, а не десятичных чисел

SELECT snum, sname, city, comm * 100 FROM Salespeople

Результат:

Этот столбец не имеет названия, потому что не содержит измененные данные и поэтому именуется по усмотрению СУБД (например MySQL именует столбец comm * 100, в примерах М.Граббера столбец имеет имя 4, т.е его номер).

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

SELECT snum, sname, city, comm * 100 AS "percent" FROM Salespeople Результат:

В результате запроса последний столбец именуется строкой "percent", что облегчает понимание.

Так как выводится число в виде процентов, то неплохо бы обозначить это в результате. На помощь приходит возможность SQL добавлять текст в результат . Выглядит это так:

SELECT snum, sname, city, comm * 100 AS percent , "%" FROM Salespeople Результат:

Видно, что после вывода строки из БД, появился новый столбец, заполненный знаком процента (%).

Если Вас не устраивает вывод данных и дополняющего текста в разных столбцах, то можно использовать специальные функции Вашей СУБД, для объединения в один столбец.

В MySQL для этого используется функция CONCAT . Вот ее определение из справочника:

CONCAT(str1,str2,...)
Возвращает строку, являющуюся результатом конкатенации аргументов. Если хотя бы один из аргументов равен NULL, возвращается NULL. Может принимать более 2 аргументов. Числовой аргумент преобразуется в эквивалентную строковую форму.

Пример: SELECT snum, sname, city , CONCAT(comm * 100, "%") AS "persent" FROM salespeople Результат:

В данном запросе, функция CONCAT принимает 2 аргумента, это comm * 100 и знак процента ("%"). После этого с помощью AS мы именуем столбец.

Важно знать, что использование функций ухудшает быстродействие. Это не единственный минус, но очень важный. Поэтому если можно обойтись стандартным кодом SQL, лучше не использовать функции. О них стоит вспоминать только в крайних случаях.

Исключение избыточных данных

Часто бывает необходимым исключить повторяющиеся значения из результатов запроса. Для этого используется ключевое слово DISTINCT. Противоположный эффект дает применение слова ALL, которое используется по умолчанию (т.е. его не нужно указывать).

5. Необходимо вывести города (city), где есть продавцы

Запрос без исключений:

SELECT city FROM salespeople Результат:

Повторяющиеся значение London исключены из результата, что и требовалось.

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

Оператор SELECT выводит данные в произвольной последовательности. Для сортировки результата по определенному столбцу, в SQL используется оператор ORDER BY (т.е. упорядочить по….). Этот оператор позволяет изменить порядок вывода данных. ORDER BY упорядочивает результат запроса в соответствии со значениями одного или нескольких столбцов, выбранных в предложении SELECT. При этом для каждого столбца можно задать сортировку по возрастанию – ascending (ASC) (этот параметр используется по умолчанию) или по убыванию – descending (DESC).

Отсортируем результат по столбцу sname. После оператора ORDER BY указываем по какому столбцу сортировать, затем необходимо указать способ сортировки

Пример – сортировка по возрастанию:

SELECT * FROM Salespeople ORDER BY sname ASC

Результат:

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

SELECT snum, sname, city FROM Salespeople ORDER BY sname DESC, city DESC Результат:

Несколько важных замечаний:
- столбец, по которому происходит сортировка, обязательно должен быть указан в SELECT (можно использовать *)
- оператор ORDER BY всегда пишется в конце запроса

Каждый веб-разработчик должен знать SQL, чтобы писать запросы к базам данных. И, хотя, phpMyAdmin никто не отменял, зачастую необходимо испачкать руки, чтобы написать низкоуровневый SQL.

Именно поэтому мы подготовили краткий экскурс по основам SQL. Начнем же!

1. Создание таблицы

Для создания таблиц предназначена инструкция CREATE TABLE . В качестве аргументов должно быть задано название столбцов, а также их типы данных.

Создадим простую таблицу по имени month . Она состоит из 3 колонок:

• id – Номер месяца в календарном году (целое число).
• name – Название месяца (строка, максимум 10 символов).
• days – Количество дней в этом месяце (целое число).

Вот как будет выглядеть соответствующий SQL запрос:

CREATE TABLE months (id int, name varchar(10), days int);

Также при создании таблиц целесообразно добавить первичный ключ для одной из колонок. Это позволит держать записи уникальными и ускорит запросы на выборку. Пусть в нашем случае уникальным будет название месяца (столбец name )

CREATE TABLE months (id int, name varchar(10), days int, PRIMARY KEY (name));

2. Вставка строк

Теперь давайте заполнять нашу таблицу months полезной информацией. Добавление записей в таблицу производится через инструкцию INSERT . Есть два способа записи этой инструкции.

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

Этот способ записи прост, но небезопасен, поскольку нет гарантии, что по мере расширения проекта и редактировании таблицы, столбцы будут располагаться в том же порядке, что и ранее. Безопасный (и в тоже время более громоздкий) способ записи инструкции INSERT требует указания как значений, так и порядка следования столбцов:

Здесь первое значение в списке VALUES соответствует первому указанному имени столбца и т.д.

3. Извлечение данных из таблиц

Инструкция SELECT - наш лучший друг, когда мы хотим получить данные из базы данных. Она используется очень часто, так что отнеситесь к этому разделу очень внимательно.

Самый простое использование инструкции SELECT - запрос, который возвращает все столбцы и строки из таблицы (например, таблицы по имени characters ):

SELECT * FROM "characters"

Символ звездочка (*) означает, что мы хотим получить данные из всех столбцов. Так базы данных SQL обычно состоят из более чем одной таблицы, то требуется обязательно указывать ключевое слово FROM , следом за которым через пробел должно следовать название таблицы.

Иногда мы не хотим получить данные не из всех столбцов в таблице. Для этого, вместо звездочки (*) мы должны через запятую записать имена желаемых столбцов.

SELECT id, name FROM month

Кроме того, во многих случаях мы хотим, чтобы полученные результаты были отсортированы в определенном порядке. В SQL мы делаем это с помощью ORDER BY . Он может принимать опциональный модификатор – ASC (по-умолчанию) сортирующий по возрастанию или DESC , сортирующий по убыванию:

SELECT id, name FROM month ORDER BY name DESC

При использовании ORDER BY убедитесь, что оно будет последним в инструкции SELECT . В противном случае будет выдано сообщение об ошибке.

4. Фильтрация данных

Вы узнали, как выбрать из базы данных с помощью SQL запроса строго определенные столбцы, но что если нам нужно получить еще и определенные строки? На помощь здесь приходит условие WHERE , позволяющее нам фильтровать данные в зависимости от условия.

В этом запросе мы выбираем только те месяцы из таблицы month , в которых больше 30 дней с помощью оператора больше (>).

SELECT id, name FROM month WHERE days > 30

5. Расширенная фильтрация данных. Операторы AND и OR

Ранее мы использовали фильтрацию данных с использованием одного критерия. Для более сложной фильтрации данных можно использовать операторы AND и OR и операторов сравнения (=,<,>,<=,>=,<>).

Здесь мы имеем таблицу, содержащую четыре самых продаваемых альбомов всех времен. Давайте выберем те из них, которые классифицируются как рок и у которых менее 50 миллионов проданных копий. Это можно легко сделать путем размещения оператора AND между этими двумя условиями.

6. In/Between/Like

WHERE также поддерживает несколько специальных команд, позволяя быстро проверять наиболее часто используемые запросы. Вот они:

• IN – служит для указания диапазона условий, любое из которых может быть выполнено
• BETWEEN – проверяет, находится ли значение в указанном диапазоне
• LIKE – ищет по определенным паттернам

Например, если мы хотим выбрать альбомы с поп и соул музыкой, мы можем использовать IN("value1","value2") .

SELECT * FROM albums WHERE genre IN ("pop","soul");

Если мы хотим получить все альбомы, изданные между 1975 и 1985годами, мы должны записать:

SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;

7. Функции

SQL напичкан с функциями, которые делают разные полезные вещи. Вот некоторые из наиболее часто используемых:

• COUNT() – возвращает количество строк
• SUM() – возвращает общую сумму числового столбца
• AVG() – возвращает среднее значение из множества значений
• MIN() / MAX() – получает минимальное / максимальное значение из столбца

Чтобы получить самый последний год в нашей таблице мы должны записать такой SQL запрос:

SELECT MAX(released) FROM albums;

8. Подзапросы

В предыдущем пункте мы научились делать простые расчеты с данными. Если мы хотим использовать результат от этих расчетов, нам не обойтись без вложенных запросов. Допустим, мы хотим вывести artist , album и release year для старейшего альбома в таблице.

Мы знаем, как получить эти конкретные столбцы:

SELECT artist, album, released FROM albums;

Мы также знаем, как получить самый ранний год:

SELECT MIN(released) FROM album;

Все, что нужно сейчас, - это объединить два запроса с помощью WHERE:

SELECT artist,album,released FROM albums WHERE released = (SELECT MIN(released) FROM albums);

9. Объединение таблиц

В более сложных базах данных существует несколько таблиц, связанных друг с другом. Например, ниже представлены две таблицы о видеоиграх (video_games ) и разработчиков видеоигр (game_developers ).

В таблице video_games есть колонка разработчик (developer_id ), но в ней содержится целое число, а не имя разработчика. Это число представляет собой идентификатор (id ) соответствующего разработчика из таблицы разработчиков игр (game_developers ), связывая логически два списка, что позволяет нам использовать информацию, хранящуюся в них обоих одновременно.

Если мы хотим создать запрос, который возвращает все, что нужно знать об играх, мы можем использовать INNER JOIN для связи колонок из обеих таблиц.

SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country FROM video_games INNER JOIN game_developers ON video_games.developer_id = game_developers.id;

Это самый простой и наиболее распространенный тип JOIN . Есть несколько других вариантов, но они применимы к менее частым случаям.

10. Алиасы

Если вы посмотрите на предыдущий пример, то вы заметите, что существуют две колонки называемые name . Это сбивает с толку, так что давайте установим псевдоним одного из повторяющихся столбцов, например, name из таблицы game_developers будет называться developer .

Мы также можем сократить запрос задав псевдонимы имен таблиц: video_games назовем games , game_developers - devs :

SELECT games.name, games.genre, devs.name AS developer, devs.country FROM video_games AS games INNER JOIN game_developers AS devs ON games.developer_id = devs.id;

11. Обновление данных

Часто мы должны изменить данные в некоторых строках. В SQL это делается с помощью инструкции UPDATE . Инструкция UPDATE состоит из:

• Таблицы, в которой находится значение для замены;
• Имен столбцов и их новых значений;
• Выбранные с помощью WHERE строки, которые мы хотим обновить. Если этого не сделать, то изменятся все строки в таблице.

Ниже приведена таблица tv_series с сериалами с их рейтингом. Однако, в таблицу закралась маленькая ошибка: хотя сериал Игра престолов и описывается как комедия, он на самом деле ей не является. Давайте исправим это!

12. Удаление данных

Удаление строки таблицы с помощью SQL - это очень простой процесс. Все, что вам нужно, - это выбрать таблицу и строку, которую нужно удалить. Давайте удалим из предыдущего примера последнюю строку в таблице tv_series . Делается это с помощью инструкции >DELETE

DELETE FROM tv_series WHERE id = 4

Будьте осторожными при написании инструкции DELETE и убедитесь, что условие WHERE присутствует, иначе все строки таблицы будут удалены!

13. Удаление таблицы

Если мы хотим, чтобы удалить все строки, но оставить саму таблицу, то воспользуйтесь командой TRUNCATE:

TRUNCATE TABLE table_name;

В случае, когда мы на самом деле хотим, чтобы удалить и данные, и саму таблицу, то нам пригодится команда DROP:

DROP TABLE table_name;

Будьте очень осторожны с этими командами. Их нельзя отменить!/p>

На этом мы завершаем наш учебник по SQL! Мы многое о чем не рассказали, но то, что вы уже знаете, должно быть достаточно, чтобы дать вам несколько практических навыков в вашей веб-карьере.

Запросы написаны без экранирующих кавычек, так как у MySQL , MS SQL и PostGree они разные.

SQL запрос: получение указанных (нужных) полей из таблицы

Получаем список записей: ВСЕ страны и их население. Название нужных полей указываются через запятую.

SELECT * FROM table_name

* обозначает все поля. То есть, будут показы АБСОЛЮТНО ВСЕ поля данных.

SQL запрос: вывод записей из таблицы исключая дубликаты

Получаем список записей: страны, где находятся наши пользователи. Пользователей может быть много из одной страны. В этом случае это ваш запрос.

SQL запрос: вывод записей из таблицы по заданному условию

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

SQL запрос: вывод записей из таблицы с упорядочиванием

Получаем список записей: города в алфавитном порядке. В начале А, в конце Я.

SELECT id, city_title FROM table_name ORDER BY city_title DESC

Получаем список записей: города в обратном (DESC ) порядке. В начале Я, в конце А.

SQL запрос: подсчет количества записей

Получаем число (количество) записей в таблице. В данном случае НЕТ списка записей.

SQL запрос: вывод нужного диапазона записей

Получаем 2 (вторую) и 3 (третью) запись из таблицы. Запрос полезен при создании навигации на WEB страницах.

SQL запросы с условиями

Вывод записей из таблицы по заданному условию с использованием логических операторов.

SQL запрос: конструкция AND (И)

Получаем список записей: города из России И имеют доступ к нефти. Когда используется оператор AND , то должны совпадать оба условия.

SQL запрос: конструкция OR (ИЛИ)

Получаем список записей: все города из России ИЛИ США. Когда используется оператор OR , то должно совпадать ХОТЯ БЫ одно условие.

SQL запрос: конструкция AND NOT (И НЕ)

Получаем список записей: все пользователи из России И сделавших НЕ МЕНЬШЕ 7 комментариев.

SQL запрос: конструкция IN (В)

Получаем список записей: все пользователи, которые проживают в (IN ) (России, или Болгарии, или Китая)

SQL запрос: конструкция NOT IN (НЕ В)

Получаем список записей: все пользователи, которые проживают не в (NOT IN ) (России или Китае).

SQL запрос: конструкция IS NULL (пустые или НЕ пустые значения)

Получаем список записей: все пользователи, где status не определен. NULL это отдельная тема и поэтому она проверяется отдельно.

SELECT id, user_login FROM table_name WHERE state IS NOT NULL

Получаем список записей: все пользователи, где status определен (НЕ НОЛЬ).

SQL запрос: конструкция LIKE

Получаем список записей: пользователи, у которых фамилия начинается с комбинации «Иван». Знак % означает ЛЮБОЕ количество ЛЮБЫХ символов. Чтобы найти знак % требуется использовать экранирование «Иван\%».

SQL запрос: конструкция BETWEEN

Получаем список записей: пользователи, которые получает зарплату от 25000 до 50000 включительно.

Логических операторов ОЧЕНЬ много, поэтому детально изучите документацию по SQL серверу.

Сложные SQL запросы

SQL запрос: объединение нескольких запросов

Получаем список записей: пользователи, которые зарегистрированы в системе, а также те пользователи, которые зарегистрированы на форуме отдельно. Оператором UNION можно объединить несколько запросов. UNION действует как SELECT DISTINCT, то есть отбрасывает повторяющиеся значения. Чтобы получить абсолютно все записи, нужно использовать оператор UNION ALL.

SQL запрос: подсчеты значений поля MAX, MIN, SUM, AVG, COUNT

Вывод одного, максимального значения счетчика в таблице:

Вывод одного, минимальный значения счетчика в таблице:

Вывод суммы всех значений счетчиков в таблице:

Вывод среднего значения счетчика в таблице:

Вывод количества счетчиков в таблице:

Вывод количества счетчиков в цехе №1, в таблице:

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

SQL запрос: группировка записей

Получаем список записей: с названием континента и с суммой площадей всех их стран. То есть, если есть справочник стран, где у каждой страны записана ее площадь, то с помощью конструкции GROUP BY можно узнать размер каждого континента (на основе группировки по континентам).

SQL запрос: использование нескольких таблиц через алиас (alias)

Получаем список записей: заказы от покупателей, которые проживают только в Тюмени.

На самом деле, при правильном запроектированной базе данных данного вида запрос является самым частым, поэтому в MySQL был введен специальный оператор, который работает в разы быстрее, чем выше написанный код.

SELECT o.order_no, o.amount_paid, z.company FROM orders AS o LEFT JOIN customer AS z ON (z.custno=o.custno)

Вложенные подзапросы

Получаем одну запись: информацию о пользователе с максимальным окладом.

Внимание! Вложенные подзапросы являются одним из самых узких мест в SQL серверах. Совместно со своей гибкостью и мощностью, они также существенно увеличивают нагрузку на сервер. Что приводит к катастрофическому замедлению работы других пользователей. Очень часты случаи рекурсивных вызовов при вложенных запросах. Поэтому настоятельно рекомендую НЕ использовать вложенные запросы, а разбивать их на более мелкие. Либо использовать вышеописанную комбинацию LEFT JOIN. Помимо этого данного вида запросы являются повышенным очагом нарушения безопасности. Если решили использовать вложенные подзапросы, то проектировать их нужно очень внимательно и первоначальные запуски сделать на копиях баз (тестовые базы).

SQL запросы изменяющие данные

SQL запрос: INSERT

Инструкция INSERT позволяют вставлять записи в таблицу. Простыми словами, создать строчку с данными в таблице.

Вариант №1. Часто используется инструкция:

В таблицу «table_name » будет вставлено 2 (два) пользователя сразу.

Вариант №2. Удобнее использовать стиль:

В этом есть свои преимущества и недостатки.

Основные недостатки:

• Множество мелких SQL запросов выполняются чуть медленнее, чем один большой SQL запрос, но при этом другие запросы будут стоять в очереди на обслуживание. То есть, если большой SQL запрос будет выполняться 30 минут, то в все это время остальные запросы будут курить бамбук и ждать своей очереди.
• Запрос получается массивнее, чем предыдущий вариант.

Основные преимущества:

• Во время мелких SQL запросов, другие SQL запросы не блокируются.
• Удобство в чтении.
• Гибкость. В этом варианте, можно не соблюдать структуру, а добавлять только необходимые данные.
• При формировании подобным образом архивов, можно легко скопировать одну строчку и запустить ее через командную строку (консоль), тем самым не восстанавливая АРХИВ целиком.
• Стиль записи схож с инструкцией UPDATE, что легче запоминается.

SQL запрос: UPDATE

В таблице «table_name » в записи с номером id=1, будет изменены значения полей user_login и user_surname на указанные значения.

SQL запрос: DELETE

В таблице table_name будет удалена запись с id номером 3.

1. Все названия полей рекомендуются писать маленькими буквами и если надо, разделять их через принудительный пробел «_» для совместимости с разными языками программирования, таких как Delphi, Perl, Python и Ruby.
2. SQL команды писать БОЛЬШИМИ буквами для удобочитаемости. Помните всегда, что после вас могут читать код и другие люди, а скорее всего вы сами через N количество времени.
3. Называть поля с начала существительное, а потом действие. Например: city_status, user_login, user_name.
4. Стараться избегать слов резервных в разных языках которые могут вызывать проблемы в языках SQL, PHP или Perl, типа (name, count, link). Например: link можно использовать в MS SQL, но в MySQL зарезервировано.

Данный материал является короткой справкой для повседневной работы и не претендует на супер мега авторитетный источник, коим является первоисточник SQL запросов той или иной базы данных.

Инструкция UPDATE используется для модифицирования строк таблицы. Эта инструкция имеет следующую общую форму:

Строки таблицы tab_name выбираются для изменения в соответствии с условием в предложении WHERE. Значения столбцов каждой модифицируемой строки изменяются с помощью предложения SET инструкции UPDATE, которое соответствующему столбцу присваивает выражение (обычно) или константу. Если предложение WHERE отсутствует, то инструкция UPDATE модифицирует все строки таблицы. С помощью инструкции UPDATE данные можно модифицировать только в одной таблице.

В примере ниже инструкция UPDATE изменяет всего лишь одну строку таблицы Works_on, поскольку комбинация столбцов EmpId и ProjectNumber является первичным ключом этой таблицы и, следственно, она однозначна. В данном примере изменяется должность сотрудника, значение которого было ранее неизвестно или имело значение NULL:

В примере ниже значения строкам таблицы присваиваются посредством выражения. Запрос пересчитывает бюджеты всех проектов с долларов на евро:

USE SampleDb; UPDATE Project SET Budget = Budget * 0.9;

В данном примере изменяются все строки таблицы Project, поскольку в запросе отсутствует предложение WHERE.

В примере ниже в предложении WHERE инструкции UPDATE используется вложенный запрос. Поскольку применяется оператор IN, то этот запрос может возвратить более одной строки:

Согласно этому запросу, для сотрудницы Вершининой Натальи во всех ее проектах в столбце ее должности присваивается значение NULL. Запрос в этом примере можно также выполнить посредством предложения FROM инструкции UPDATE. В предложении FROM указываются имена таблиц, которые обрабатываются инструкцией UPDATE. Все эти таблицы должны быть в дальнейшем соединены. Применение предложения FROM показано в примере ниже. Логически, этот пример идентичен предыдущему:

В примере ниже показано использование выражения CASE в инструкции UPDATE. (Подробное рассмотрение этого выражения описывалось ранее.) В данном примере нужно увеличить бюджет всех проектов на определенное число процентов (20, 10 или 5), в зависимости от исходной суммы бюджета: чем меньше бюджет, тем больше должно быть его процентное увеличение:

USE SampleDb; UPDATE Project SET Budget = CASE WHEN Budget > 0 AND Budget 100000 AND Budget

Инструкция DELETE

Инструкция DELETE удаляет строки из таблицы. Подобно инструкции INSERT, эта инструкция также имеет две различные формы:

Удаляются все строки, которые удовлетворяют условие в предложении WHERE. Явно перечислять столбцы в инструкции DELETE не то чтобы нет необходимости, а даже не разрешается, поскольку эта инструкция оперирует строками, а не столбцами. Использование первой формы инструкции DELETE показано в примере ниже, в котором происходит удаление из таблицы Works_on всех сотрудников с должностью "Менеджер":

Предложение WHERE инструкции DELETE может содержать вложенный запрос, как это показано в примере ниже:

Поскольку сотрудница Вершинина уволилась, из базы данных удаляются все записи, связанные с ней. Запрос из этого примера можно также выполнить с помощью предложения FROM, как это показано ниже. В данном случае семантика этого предложения такая же, как и предложения FROM в инструкции UPDATE.

Использование предложения WHERE в инструкции DELETE не является обязательным. Если это предложение отсутствует, то из таблицы удаляются все строки:

USE SampleDb; -- Удаление всех строк таблицы DELETE FROM Works_on;

Инструкции DELETE и DROP TABLE существенно отличаются друг от друга. Инструкция DELETE удаляет (частично или полностью) содержимое таблицы, тогда как инструкция DROP TABLE удаляет как содержимое, так и схему таблицы. Таким образом, после удаления всех строк посредством инструкции DELETE таблица продолжает существовать в базе данных, а после выполнения инструкции DROP TABLE таблица больше не существует.

Другие инструкции и предложения Transact-SQL для модификации таблиц

Сервер SQL Server поддерживает следующие дополнительные инструкции и предложения для модификации таблиц:

инструкцию TRUNCATE TABLE;

инструкцию MERGE;

предложение OUTPUT.

Эти инструкции и предложение рассматриваются в последующих подразделах.

Инструкция TRUNCATE TABLE

Инструкция TRUNCATE TABLE является более быстрой версией инструкции DELETE без предложения WHERE. Эта инструкция удаляет все строки таблицы более быстро, чем инструкция DELETE, поскольку она удаляет содержимое постранично, тогда как инструкция DELETE делает это построчно. Инструкция TRUNCATE TABLE является расширением Transact-SQL стандарта SQL. Еще одним важным отличием этой инструкции является то, что она сбрасывает индекс столбца, для которого указано свойство автоинкремента IDENTITY.

Инструкция TRUNCATE TABLE имеет следующий синтаксис:

TRUNCATE TABLE table_name

Инструкция MERGE

Инструкция MERGE объединяет последовательность инструкций INSERT, UPDATE и DELETE в одну элементарную инструкцию, в зависимости от существования записи (строки). Иными словами, можно синхронизировать две разные таблицы, чтобы модифицировать содержимое таблицы назначения в зависимости от различий, обнаруженных в таблице-источнике.

Основной областью применения для инструкции MERGE является среда хранилищ данных, где таблицы необходимо периодически обновлять, чтобы отражать новые данные, прибывающие с систем оперативной обработки транзакций OLTP (On-Line Transaction Processing) . Эти данные могут содержать изменения существующих строк таблиц и/или новый строки, которые нужно вставить в таблицы. Если строка в новых данных соответствует записи, которая уже имеется в таблице, выполняется инструкция UPDATE или DELETE. В противном случае выполняется инструкция INSERT.

Альтернативно, вместо инструкции MERGE можно использовать последовательность инструкций INSERT, UPDATE и DELETE, в которых для каждой строки решается, какую операцию выполнять: вставку, удаление или обновление. Но этот подход имеет значительный недостаток, связанный с производительностью: в нем требуется выполнять несколько проходов по данным, а данные обрабатываются по принципу "запись за записью".

Предложение OUTPUT

По умолчанию единым видимым результатом выполнения инструкции INSERT, UPDATE или DELETE является только сообщение о количестве модифицированных строк, например "3 rows DELETED" (удалены 3 строки) и система не сохраняет информацию о модифицированных данных. Если такой видимый результат не удовлетворяет вашим требованиям, то можно использовать предложение OUTPUT , которое выводит модифицированные, вставленные или удаленные строки.

Предложение OUTPUT также применимо с инструкцией MERGE, для которой оно выводит все модифицированные строки в виде таблицы.

Результаты выполненных операций соответствующих инструкций предложение OUTPUT выводит в таблицах inserted и deleted. Кроме этого, чтобы заполнить таблицы, в предложении OUTPUT требуется использовать выражение INTO. Поэтому для сохранения результата используется табличная переменная.