Любая таблица имеет своё логическое начало и свой логический конец. Так же любая таблица состоит из данных, которые за частую, а в случае с базами данных в большей степени, расположены в разброс. Как отмечалось в первой записи касательно SQL и баз данных, данные могут вноситься регулярно, на протяжении всего жизненного цикла баз данных. За это время может быть внесён не один десяток данных. В SQL, как в прочем и во всех компьютерных программах, можно упорядочивать данные. Делается это по средствам команды ORDER BY . Для данной команды существуют два аргумента

• ASC задаёт порядок по возрастанию (от меньшего к большему)
• DESC - по убыванию (от большего к меньшему)

Вообще в SQL по умолчанию установлено упорядочивание по возрастанию (от меньшего к большему).
Попробуем упорядочить таблицу должников по фамилии - столбец Sname - по убыванию

Debtors

Num	Month	Year	Sname	City	Address	Penalty	Debt
0001	Июль	2012	Иванов	Ставрополь	Ставропольская, 1	4891.00	50000
0002	Декабрь	2019	Кононов	Татарка	Загородная, 254	19565.00	684068
0003	Май	2013	Ямшин	Михайловск	Сельская, 48	9868.00	165840
0004	Август	2012	Прени	Ставрополь	Центральная, 16	4659.00	46580
...	...	...	...	...	...	...	...
9564	Март	2015	Улиева	Дёмино	Международная, 156	6846.00	435089
9565	Октябрь	2012	Павлова	Ставрополь	Вокзальная, 37	685.00	68059
9566	Январь	2012	Урюпа	Михайловск	Фонтанная, 19	1235.00	51238
9567	Ноябрь	2017	Вальетов	Татарка	Выездная, 65	13698.00	789654

Выполняем запрос
SELECT *
FROM Debtors
ORDER BY Sname DESC ;

Debtors

Num	Month	Year	Sname	City	Address	Penalty	Debt
0003	Май	2013	Ямшин	Михайловск	Сельская, 48	9868.00	165840
9566	Январь	2012	Урюпа	Михайловск	Фонтанная, 19	1235.00	51238
9564	Март	2015	Улиева	Дёмино	Международная, 156	6846.00	435089
0004	Август	2012	Прени	Ставрополь	Центральная, 16	4659.00	46580
9565	Октябрь	2012	Павлова	Ставрополь	Вокзальная, 37	685.00	68059
0002	Декабрь	2019	Кононов	Татарка	Загородная, 254	19565.00	684068
0001	Июль	2012	Иванов	Ставрополь	Ставропольская, 1	4891.00	50000
9567	Ноябрь	2017	Вальетов	Татарка	Выездная, 65	13698.00	789654

Так же упорядочивание может выполняться и в группируемых данных, по средством команды . Но в таком случае, команда ORDER BY в запросе должна стоять последней, то есть
SELECT *
FROM Debtors
GROUP BY Sname
ORDER BY Sname DESC ;

Если же в упорядочиваемых полях имеются ячейки с пустыми значениями (NULL), то, в зависимости от программ, работающих с БД, такие ячейки могут располагаться либо в конце списка, либо в начале.
Существует ещё одна хитрость по выполнению упорядочивания данных. Рассмотрим запрос
SELECT Num, Year, Sname
FROM Debtors
ORDER BY 2 DESC ;
Здесь под двойкой подразумевается порядковый номер столбца из строки "SELECT Num, Year, Sname" и именно по этому столбцу будет выполняться ранжирование по убыванию в итоге

Debtors

Как видно, значения упорядочились по столбцу Year, где в начале списка более поздняя дата.
Итак, упорядочивание в SQL имеет те же принципы ранжирования, что и в любых компьютерных программах. Всё зависит от администратора БД и его потребностей в той или иной информации, которую он хочет получить.

SQL - Оператор SELECT

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

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

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

SELECT column_list FROM table_name

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

Результат:

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

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

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

SELECT snum , sname , city , comm FROM Salespeople

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

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

snum	sname	city	comm
1001	Peel	London	0,12
1002	Serres	San Jose	0,13
1003	Axelrod	New York	0,1
1004	Motika	London	0,11
1007	Rifkin	Barcelona	0,15

Также хочется дать небольшой совет. Для удобства отладки запросов, некоторые люди пишут перечень столбцов, которые необходимо вывести, в отдельной строке. Это облегчает комментирование кода. Для комментирования кода в SQL используется такой синтаксис - /* закомментированный код */ .Пример: SELECT snum ,sname , city , comm 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

Результат:

snum	sname	city	comm
1001	Peel	London	12
1002	Serres	San Jose	13
1003	Axelrod	New York	10
1004	Motika	London	11
1007	Rifkin	Barcelona	15

В последнем столбце все полученные данные умножаются на 100, и выводятся в виде процентов.

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

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

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

snum	sname	city	percent
1001	Peel	London	12
1002	Serres	San Jose	13
1003	Axelrod	New York	10
1004	Motika	London	11
1007	Rifkin	Barcelona	15

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

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

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

snum	sname	city	percent	%
1001	Peel	London	12	%
1002	Serres	San Jose	13	%
1003	Axelrod	New York	10	%
1004	Motika	London	11	%
1007	Rifkin	Barcelona	15	%

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

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

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

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

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

snum	sname	city	persent
1001	Peel	London	12.000%
1002	Serres	San Jose	13.000%
1003	Axelrod	New York	10.000%
1004	Motika	London	11.000%
1007	Rifkin	Barcelona	15.000%

В данном запросе, функция 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 Результат:

snum	sname	city
1002	Serres	San Jose
1007	Rifkin	Barcelona
1001	Peel	London
1004	Motika	London
1003	Axelrod	New York

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

Приветствую вас на моем блоге сайт. Сегодня поговорим про sql запросы для начинающих. У некоторых вебмастеров может возникнуть вопрос. Зачем изучать sql? Разве нельзя обойтись ?

Оказывается, что для создания профессионального интернет-проекта этого будет недостаточно. Sql используется чтобы работать с БД и создания приложений для Вордпресс. Рассмотрим, как использовать запросы подробнее.

Что это такое

Sql - язык структурированных запросов. Создан для определения типа данных, предоставления доступа к ним и обработке информации за короткие промежутки времени. Он описывает компоненты или какие-то результаты, которые вы хотите видеть на интернет-проекте.

Если говорить по-простому, то этот язык программирования позволяет добавлять, изменять, искать и отображать информацию в БД. Популярность mysql связана с тем, что он используется для создания динамических интернет-проектов, основа которых составляет база данных. Поэтому для разработки функционального блога вам необходимо выучить этот язык.

Что может делать

Язык sql позволяет:

• создавать таблицы;
• изменять получать и хранить разные данные;
• объединять информацию в блоки;
• защитить данные;
• создавать запросы в access.

Важно! Разобравшись с sql вы сможете писать приложения для Вордпресс любой сложности.

Какая структура

БД состоит из таблиц, которые можно представить в виде Эксель файла.

У нее имеется имя, колонки и ряд с какой-то информацией. Создавать подобные таблицы можно при помощи sql запросов.

Что нужно знать

Основные моменты при изучении Sql

Как уже отмечалось выше, запросы применяются для обработки и ввода новой информации в БД, состоящую из таблиц. Каждая ее строка - это отдельная запись. Итак, создадим БД. Для этого напишите команду:

Create database ‘bazaname’

В кавычках пишем имя БД на латинице. Старайтесь придумать для нее понятное имя. Не создавайте базу типа «111», «www» и тому подобное.

После создания БД устанавливаем :

SET NAMES ‘utf-8’

Это нужно чтобы контент на сайте правильно отображаться.

Теперь создаем таблицу:

CREATE TABLE ‘bazaname’ . ‘table’ (

id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,

log VARCHAR(10),

pass VARCHAR(10),

date DATE

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

• Атрибут NOT NULL означает, что ячейка не будет пустой (поле обязательное для заполнения);
• Значение AUTO_INCREMENT — автозаполнение;
• PRIMARY KEY — первичный ключ.

Как добавить информацию

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

INSERT INTO ‘table’

(login , pass , date) VALUES

(‘Vasa’, ‘87654321’, ‘2017-06-21 18:38:44’);

В скобках указываем название столбцов, а в следующей - значения.

Важно! Соблюдайте последовательность названий и значений столбцов.

Как обновить информацию

Для этого используется команда UPDATE. Посмотрим, как изменить пароль для конкретного пользователя. Пишем такие строки кода:

UPDATE ‘table’ SET pass = ‘12345678’ WHERE id = ‘1’

Теперь поменяйте пароль ‘12345678’. Изменения происходят в строке с «id»=1. Если не писать команду WHERE - поменяются все строки, а не конкретная.

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

Как удалить запись

Если вы написали что-то не так, исправьте это при помощи команды DELETE. Работает так же, как и UPDATE. Пишем такой код:

DELETE FROM ‘table’ WHERE id = ‘1’

Выборка информации

Для извлечения значений из БД используется команда SELECT. Пишем такой код:

SELECT * FROM ‘table’ WHERE id = ‘1’

В данном примере в таблице выбираем все имеющиеся поля. Это происходит если прописать в команде звездочку «*». Если нужно выбрать какое-то выборочное значение пишем так:

SELECT log , pass FROM table WHERE id = ‘1’

Необходимо отметить, что умения работать с базами данных будет недостаточно. Для создания профессионального интернет-проекта придется научиться добавлять на страницы данные из БД. Для этого ознакомьтесь с языком веб-программирования php. В этом вам поможет классный курс Михаила Русакова .

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

Происходит при помощи запроса DROP. Для этого напишем такие строки:

DROP TABLE table;

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

Рассмотрим такой код:

SELECT id, countri, city FROM table WHERE people>150000000

Он отобразит записи стран где населения больше ста пятидесяти миллионов.

Объединение

Связать вместе несколько таблиц возможно используя Join. Как это работает посмотрите подробнее в этом видео:

PHP и MySQL

Еще раз хочу подчеркнуть, что запросы при создании интернет-проекта - это обычное дело. Чтобы их использовать в php-документах выполните такой алгоритм действий:

• Соединяемся с БД при помощи команды mysql_connect();
• Используя mysql_select_db() выбираем нужную БД;
• Обрабатываем запрос при помощи mysql_fetch_array();
• Закрываем соединение командой mysql_close().

Важно! Работать с БД не сложно. Главное - правильно написать запрос.

Начинающие вебмастера подумают. А что почитать по этой теме? Хотелось бы порекомендовать книгу Мартина Грабера «SQL для простых смертных ». Она написана так, что новичкам все будет понятно. Используйте ее в качестве настольной книги.

Но это теория. Как же обстоит дело на практике? В действительности интернет-проект нужно не только создать, но еще и вывести в ТОП Гугла и Яндекса. В этом вас поможет видеокурс «Создание и раскрутка сайта ».

Видео инструкция

Остались еще вопросы? Посмотрите подробнее онлайн видео.

Вывод

Итак, разобраться с написанием sql запросов не так трудно, как кажется, но сделать это нужно любому вебмастеру. В этом помогут видеокурсы, описанные выше. Подпишитесь на мою группу ВКонтакте чтобы первыми узнавать о появлении новой интересной информации.

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

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

SELECT id, country_title, count_people FROM table_name

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

SELECT * FROM table_name

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

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

SELECT DISTINCT country_title FROM table_name

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

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

SELECT id, country_title, city_title FROM table_name WHERE count_people>100000000

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

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

SELECT id, city_title FROM table_name ORDER BY city_title

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

SELECT id, city_title FROM table_name ORDER BY city_title DESC

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

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

SELECT COUNT(*) FROM table_name

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

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

SELECT * FROM table_name LIMIT 2, 3

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

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

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

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

SELECT id, city_title FROM table_name WHERE country="Россия" AND oil=1

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

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

SELECT id, city_title FROM table_name WHERE country="Россия" OR country="США"

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

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

SELECT id, user_login FROM table_name WHERE country="Россия" AND NOT count_comments<7

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

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

SELECT id, user_login FROM table_name WHERE country IN ("Россия", "Болгария", "Китай")

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

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

SELECT id, user_login FROM table_name WHERE country NOT IN ("Россия","Китай")

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

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

SELECT id, user_login FROM table_name WHERE status IS NULL

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

SELECT id, user_login FROM table_name WHERE state IS NOT NULL

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

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

SELECT id, user_login FROM table_name WHERE surname LIKE "Иван%"

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

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

SELECT id, user_login FROM table_name WHERE salary BETWEEN 25000 AND 50000

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

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

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

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

(SELECT id, user_login FROM table_name1) UNION (SELECT id, user_login FROM table_name2)

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

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

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

SELECT MAX(counter) FROM table_name

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

SELECT MIN(counter) FROM table_name

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

SELECT SUM(counter) FROM table_name

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

SELECT AVG(counter) FROM table_name

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

SELECT COUNT(counter) FROM table_name

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

SELECT COUNT(counter) FROM table_name WHERE office="Цех №1"

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

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

SELECT continent, SUM(country_area) FROM country GROUP BY continent

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

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

SELECT o.order_no, o.amount_paid, c.company FROM orders AS o, customer AS с WHERE o.custno=c.custno AND c.city="Тюмень"

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

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

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

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

SELECT * FROM table_name WHERE salary=(SELECT MAX(salary) FROM employee)

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

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

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

SQL запрос: INSERT

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

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

INSERT INTO table_name (id, user_login) VALUES (1, "ivanov"), (2, "petrov")

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

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

INSERT table_name SET id=1, user_login="ivanov"; INSERT table_name SET id=2, user_login="petrov";

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

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

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

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

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

SQL запрос: UPDATE

UPDATE table_name SET user_login="ivanov", user_surname="Иванов" WHERE id=1

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

SQL запрос: DELETE

DELETE FROM table_name WHERE id=3

В таблице 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 запросов той или иной базы данных.

• Перевод
• Tutorial

Надо “ SELECT * WHERE a=b FROM c ” или “ SELECT WHERE a=b FROM c ON * ” ?

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

А потом начинаются джойны, агрегирование, подзапросы, и получается совсем белиберда. Вроде такой:

SELECT members.firstname || " " || members.lastname AS "Full Name" FROM borrowings INNER JOIN members ON members.memberid=borrowings.memberid INNER JOIN books ON books.bookid=borrowings.bookid WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock>(SELECT avg(stock) FROM books)) GROUP BY members.firstname, members.lastname;

Буэ! Такое спугнет любого новичка, или даже разработчика среднего уровня, если он видит SQL впервые. Но не все так плохо.

Легко запомнить то, что интуитивно понятно, и с помощью этого руководства я надеюсь снизить порог входа в SQL для новичков, а уже опытным предложить по-новому взглянуть на SQL.

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

1. Три волшебных слова

В SQL много ключевых слов, но SELECT , FROM и WHERE присутствуют практически в каждом запросе. Чуть позже вы поймете, что эти три слова представляют собой самые фундаментальные аспекты построения запросов к базе, а другие, более сложные запросы, являются всего лишь надстройками над ними.

2. Наша база

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

У нас есть книжная библиотека и люди. Также есть специальная таблица для учета выданных книг.

• В таблице "books" хранится информация о заголовке, авторе, дате публикации и наличии книги. Все просто.
• В таблице “members” - имена и фамилии всех записавшихся в библиотеку людей.
• В таблице “borrowings” хранится информация о взятых из библиотеки книгах. Колонка bookid относится к идентификатору взятой книги в таблице “books”, а колонка memberid относится к соответствующему человеку из таблицы “members”. У нас также есть дата выдачи и дата, когда книгу нужно вернуть.

3. Простой запрос

Давайте начнем с простого запроса: нам нужны имена и идентификаторы (id) всех книг, написанных автором “Dan Brown”

Запрос будет таким:

SELECT bookid AS "id", title FROM books WHERE author="Dan Brown";

А результат таким:

id	title
2	The Lost Symbol
4	Inferno

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

3.1 FROM - откуда берем данные

Сейчас это может показаться очевидным, но FROM будет очень важен позже, когда мы перейдем к соединениям и подзапросам.

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

3.2 WHERE - какие данные показываем

WHERE просто-напросто ведет себя как фильтр строк , которые мы хотим вывести. В нашем случае мы хотим видеть только те строки, где значение в колонке author - это “Dan Brown”.

3.3 SELECT - как показываем данные

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

Весь запрос можно визуализировать с помощью простой диаграммы:

4. Соединения (джойны)

Теперь мы хотим увидеть названия (не обязательно уникальные) всех книг Дэна Брауна, которые были взяты из библиотеки, и когда эти книги нужно вернуть:

SELECT books.title AS "Title", borrowings.returndate AS "Return Date" FROM borrowings JOIN books ON borrowings.bookid=books.bookid WHERE books.author="Dan Brown";

Результат:

Title	Return Date
The Lost Symbol	2016-03-23 00:00:00
Inferno	2016-04-13 00:00:00
The Lost Symbol	2016-04-19 00:00:00

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

borrowings JOIN books ON borrowings.bookid=books.bookid - это, считай, новая таблица, которая была сформирована комбинированием всех записей из таблиц "books" и "borrowings", в которых значения bookid совпадают. Результатом такого слияния будет:

А потом мы делаем запрос к этой таблице так же, как в примере выше. Это значит, что при соединении таблиц нужно заботиться только о том, как провести это соединение. А потом запрос становится таким же понятным, как в случае с «простым запросом» из пункта 3.

Давайте попробуем чуть более сложное соединение с двумя таблицами.

Теперь мы хотим получить имена и фамилии людей, которые взяли из библиотеки книги автора “Dan Brown”.

На этот раз давайте пойдем снизу вверх:

Шаг Step 1 - откуда берем данные? Чтобы получить нужный нам результат, нужно соединить таблицы “member” и “books” с таблицей “borrowings”. Секция JOIN будет выглядеть так:

borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid

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

Шаг 2 - какие данные показываем? Нас интересуют только те данные, где автор книги - “Dan Brown”

WHERE books.author="Dan Brown"

Шаг 3 - как показываем данные? Теперь, когда данные получены, нужно просто вывести имя и фамилию тех, кто взял книги:

SELECT members.firstname AS "First Name", members.lastname AS "Last Name"

Супер! Осталось лишь объединить три составные части и сделать нужный нам запрос:

SELECT members.firstname AS "First Name", members.lastname AS "Last Name" FROM borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid WHERE books.author="Dan Brown";

Что даст нам:

First Name	Last Name
Mike	Willis
Ellen	Horton
Ellen	Horton

Отлично! Но имена повторяются (они не уникальны). Мы скоро это исправим.

5. Агрегирование

Грубо говоря, агрегирования нужны для конвертации нескольких строк в одну . При этом, во время агрегирования для разных колонок используется разная логика.

Давайте продолжим наш пример, в котором появляются повторяющиеся имена. Видно, что Ellen Horton взяла больше одной книги, но это не самый лучший способ показать эту информацию. Можно сделать другой запрос:

SELECT members.firstname AS "First Name", members.lastname AS "Last Name", count(*) AS "Number of books borrowed" FROM borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid WHERE books.author="Dan Brown" GROUP BY members.firstname, members.lastname;

Что даст нам нужный результат:

First Name	Last Name	Number of books borrowed
Mike	Willis	1
Ellen	Horton	2

Почти все агрегации идут вместе с выражением GROUP BY . Эта штука превращает таблицу, которую можно было бы получить запросом, в группы таблиц. Каждая группа соответствует уникальному значению (или группе значений) колонки, которую мы указали в GROUP BY . В нашем примере мы конвертируем результат из прошлого упражнения в группу строк. Мы также проводим агрегирование с count , которая конвертирует несколько строк в целое значение (в нашем случае это количество строк). Потом это значение приписывается каждой группе.

Каждая строка в результате представляет собой результат агрегирования каждой группы.

Можно прийти к логическому выводу, что все поля в результате должны быть или указаны в GROUP BY , или по ним должно производиться агрегирование. Потому что все другие поля могут отличаться друг от друга в разных строках, и если выбирать их SELECT "ом, то непонятно, какие из возможных значений нужно брать.

В примере выше функция count обрабатывала все строки (так как мы считали количество строк). Другие функции вроде sum или max обрабатывают только указанные строки. Например, если мы хотим узнать количество книг, написанных каждым автором, то нужен такой запрос:

SELECT author, sum(stock) FROM books GROUP BY author;

Результат:

author	sum
Robin Sharma	4
Dan Brown	6
John Green	3
Amish Tripathi	2

Здесь функция sum обрабатывает только колонку stock и считает сумму всех значений в каждой группе.

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

Подзапросы это обычные SQL-запросы, встроенные в более крупные запросы. Они делятся на три вида по типу возвращаемого результата.

6.1 Двумерная таблица

Есть запросы, которые возвращают несколько колонок. Хороший пример это запрос из прошлого упражнения по агрегированию. Будучи подзапросом, он просто вернет еще одну таблицу, по которой можно делать новые запросы. Продолжая предыдущее упражнение, если мы хотим узнать количество книг, написанных автором “Robin Sharma”, то один из возможных способов - использовать подзапросы:

SELECT * FROM (SELECT author, sum(stock) FROM books GROUP BY author) AS results WHERE author="Robin Sharma";

Результат:

Можно записать как: ["Robin Sharma", "Dan Brown"]

2. Теперь используем этот результат в новом запросе:

SELECT title, bookid FROM books WHERE author IN (SELECT author FROM (SELECT author, sum(stock) FROM books GROUP BY author) AS results WHERE sum > 3);

Результат:

title	bookid
The Lost Symbol	2
Who Will Cry When You Die?	3
Inferno	4

Это то же самое, что:

SELECT title, bookid FROM books WHERE author IN ("Robin Sharma", "Dan Brown");

6.3 Отдельные значения

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

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

Среднее количество можно получить таким образом:

select avg(stock) from books;

Что дает нам:

7. Операции записи

Большинство операций записи в базе данных довольно просты, если сравнивать с более сложными операциями чтения.

7.1 Update

Синтаксис запроса UPDATE семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок SELECT "ом, мы задаем знаения SET "ом.

Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким:

UPDATE books SET stock=0 WHERE author="Dan Brown";

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

7.2 Delete

Запрос DELETE это просто запрос SELECT или UPDATE без названий колонок. Серьезно. Как и в случае с SELECT и UPDATE , блок WHERE остается таким же: он выбирает строки, которые нужно удалить. Операция удаления уничтожает всю строку, так что не имеет смысла указывать отдельные колонки. Так что, если мы решим не обнулять количество книг Дэна Брауна, а вообще удалить все записи, то можно сделать такой запрос:

DELETE FROM books WHERE author="Dan Brown";

7.3 Insert

Пожалуй, единственное, что отличается от других типов запросов, это INSERT . Формат такой:

INSERT INTO x (a,b,c) VALUES (x, y, z);

Где a , b , c это названия колонок, а x , y и z это значения, которые нужно вставить в эти колонки, в том же порядке. Вот, в принципе, и все.

Взглянем на конкретный пример. Вот запрос с INSERT , который заполняет всю таблицу "books":

INSERT INTO books (bookid,title,author,published,stock) VALUES (1,"Scion of Ikshvaku","Amish Tripathi","06-22-2015",2), (2,"The Lost Symbol","Dan Brown","07-22-2010",3), (3,"Who Will Cry When You Die?","Robin Sharma","06-15-2006",4), (4,"Inferno","Dan Brown","05-05-2014",3), (5,"The Fault in our Stars","John Green","01-03-2015",3);

8. Проверка

Мы подошли к концу, предлагаю небольшой тест. Посмотрите на тот запрос в самом начале статьи. Можете разобраться в нем? Попробуйте разбить его на секции SELECT , FROM , WHERE , GROUP BY , и рассмотреть отдельные компоненты подзапросов.

Вот он в более удобном для чтения виде:

SELECT members.firstname || " " || members.lastname AS "Full Name" FROM borrowings INNER JOIN members ON members.memberid=borrowings.memberid INNER JOIN books ON books.bookid=borrowings.bookid WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock> (SELECT avg(stock) FROM books)) GROUP BY members.firstname, members.lastname;

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

Результат:

Full Name
Lida Tyler

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

Теги: Добавить метки