Матвей Земсков

Заметки веб-мастера

Четверг, 11 февраля 2016 13:32

Знакомство с языком SQL за 20 минут

Оцените материал
(15 голосов)

Каждый веб-разработчик должен знать SQL. Несмотря на то, что SQL появился в 70-х годах прошлого века, он до сих пор широко используется и создать без него какое-нибудь серьезное приложение не представляется возможным. В большинстве современных фреймворков имеются инструменты для работы с SQL запросами. Например, ActiveRecord, Doctrine, Hibernate и другие. Но иногда все-таки приходится «запачкать руки» и написать запрос без применения этих инструментов.

Именно поэтому мы приготовили для вас краткое и четкое введение в основы языка SQL. В этой статье приведены примеры SQL запросов и результат их выполнения.

Итак, давайте приступим к знакомству.

Создание таблицы (CREATE TABLE)

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

Давайте создадим простую таблицу под названием months. Она состоит из 3 полей:

  • id – номер месяца в году (тип — INTEGER);
  • name – название месяца (тип — STRING, максимум 10 символов);
  • days – Количество дней в месяце (тип — INTEGER);

Запрос будет выглядеть следующим образом:

SQL

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

Также при создании таблицы желательно чтобы одно из полей содержало первичный ключ (primary key). Это обеспечит уникальность данных и ускорит выполнение запросов на выборку. Что такое первичный ключ, вы можете прочитать здесь.

Добавление данных (INSERT)

Сейчас давайте добавим в таблицу months несколько строк с данными. Добавление данных в таблицу производится с помощью оператора INSERT. Существует 2 варианта использования этого оператора.

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

SQL

INSERT INTO months VALUES (1,'January',31); 

Это короткий вариант SQL запроса. Но в нем есть один минус: если вы решите добавить в таблицу дополнительное поле, запрос работать больше не будет.

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

SQL

INSERT INTO months (id,name,days) VALUES (2,'February',29); 

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

months

id name days
1 January 31
2 February 29

SQL

		  INSERT INTO months (id,name,days) 
		  VALUES (3, "March", 31);
		  
×

Результат запроса

id name days
1 January 31
2 February 29
3 March 31

Выборка данных (SELECT)

Запросы на выборку данных — это наши «лучшие друзья» в случае, когда мы хотим получить данные из базы данных. Эти SQL запросы используются чаще всего, поэтому им будет уделено особое внимание в этой статье.

Самым простым вариантом запроса SELECT является тот, который возвращает данные из всех полей и строк таблицы (например, characters).

SQL

SELECT * FROM "characters";

Символ “*” (звёздочка) в запросе означает, что мы хотим получить из таблицы все данные без исключения. Как правило базы данных состоят более чем из одной таблицы, поэтому в запросе используется ключевое слово FROM, в котором указывается имя таблицы, где будет осуществляться поиск данных.

Порой нам бывают не нужны все данные из таблицы. В этом случае SQL позволяет нам указать только те поля, данные из которых нам необходимы. Для этого заменим символ “*” названием требуемых полей.

SQL

SELECT name, weapon FROM "characters";

В большинстве случаев, нам требуется, чтобы данные, полученные в результате запроса были отсортированы в определенном порядке. Это можно сделать с помощью ORDER BY. Можно указать один из двух вариантов сортировки: ASC (используется по-умолчанию) или DESC.

SQL

SELECT name, weapon FROM "characters" ORDER BY name DESC;

characters

name race gender weapon hobby
Han Solo Human male pistol shooting first
Chewbacca Wookiee male crossbow painting

SQL

		  SELECT name, weapon 
		  FROM "characters";
		  
×

Результат запроса

name weapon
Han Solo pistol
Chewbacca crossbow

Оператор WHERE

Мы узнали как выбрать данные из определенных полей (столбцов таблицы), но что, если нам нужно сделать чтобы выбрать из таблицы определенные записи (строки)? Нам на помощь приходит предложение WHERE, которое позволяет отфильтровать данные в зависимости от указанных в ней условий.

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

characters

name race gender weapon hobby
Han Solo Human male pistol shooting first
Chewbacca Wookiee male crossbow painting

SQL

		  SELECT * FROM "characters" 
		  WHERE weapon = "pistol";
		  
×

Результат запроса

name race gender weapon hobby
Han Solo Human male pistol shooting first

Логические операторы AND и OR

Условия выборки данных в WHERE могут быть конкретизированы с помощью логических операторов (AND, OR) и операторов сравнения (=,<,>,<=,>=,<>).

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

albums

artist album released genre sales_in_millions
Michael Jackson Thriller 1982 pop 70
AC/DC Back in Black 1980 rock 50
Pink Floyd The Dark Side of the Moon 1973 rock 45
Whitney Houston The Bodyguard 1992 soul 44

SQL

			SELECT * FROM albums 
			WHERE genre = 'rock' 
			AND sales_in_millions <= 50 ORDER BY released
		  
×

Результат запроса

artist album released genre sales_in_millions
AC/DC Back in Black 1980 rock 50
Pink Floyd The Dark Side of the Moon 1973 rock 45

Операторы In, Between, Like

Кроме логических операторов и операторов сравнения существуют особые операторы, которые можно применять в WHERE.

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

Например, если мы хотим выбрать из нашей таблицы альбомы, жанром которых является “pop” и “soul”, то мы можем использовать в этом случае команду IN("value1","value2").

SQL

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

Если нам необходимо выбрать из таблицы альбомы, вышедшие между 1975 и 1985 годами, нужно выполнить следующий SQL запрос:

SQL

SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;

albums

artist album released genre sales_in_millions
Michael Jackson Thriller 1982 pop 70
AC/DC Back in Black 1980 rock 50
Pink Floyd The Dark Side of the Moon 1973 rock 45
Whitney Houston The Bodyguard 1992 soul 44

SQL

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

Результат запроса

artist album released genre sales_in_millions
Michael Jackson Thriller 1982 pop 70
Whitney Houston The Bodyguard 1992 soul 44

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

Функции SQL

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

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

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

albums

artist album released genre sales_in_millions
Michael Jackson Thriller 1982 pop 70
AC/DC Back in Black 1980 rock 50
Pink Floyd The Dark Side of the Moon 1973 rock 45
Whitney Houston The Bodyguard 1992 soul 44

SQL

			SELECT MAX(released) FROM albums;
		  
×

Результат запроса

MAX(released)
1992

Вложенные запросы (SELECT)

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

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

Мы знаем как получить данные из интересующих нас полей:

SQL

SELECT artist, album, released FROM albums; 

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

SQL

SELECT MIN(released) FROM album; 

Теперь все, что нам нужно сделать — это скомбинировать эти 2 SQL запроса с помощью предложения WHERE.

albums

artist album released genre sales_in_millions
Michael Jackson Thriller 1982 pop 70
AC/DC Back in Black 1980 rock 50
Pink Floyd The Dark Side of the Moon 1973 rock 45
Whitney Houston The Bodyguard 1992 soul 44

SQL

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

Результат запроса

artist album released
Pink Floyd The Dark Side of the Moon 1973

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

Объединение таблиц (Join)

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

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

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

video_games

id name developer_id genre
1 Super Mario Bros. 2 platformer
2 World of Warcraft 1 MMORPG
3 The Legend of Zelda 2 adventure

game_developers

id name country
1 Blizzard USA
2 Nintendo Japan

SQL

			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;
		  
×

Результат запроса

name genre name country
Super Mario Bros. platformer Nintendo Japan
World of Warcraft MMORPG Blizzard USA
The Legend of Zelda adventure Nintendo Japan

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

Псевдонимы (Aliases)

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

Также мы можем значительно укоротить SQL запрос, присвоив псевдонимы именам таблиц: video_games переименуем в games, а game_developers станет devs.

video_games

id name developer_id genre
1 Super Mario Bros. 2 platformer
2 World of Warcraft 1 MMORPG
3 The Legend of Zelda 2 adventure

game_developers

id name country
1 Blizzard USA
2 Nintendo Japan

SQL

			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;
		  
×

Результат запроса

name genre developer country
Super Mario Bros. platformer Nintendo Japan
World of Warcraft MMORPG Blizzard USA
The Legend of Zelda adventure Nintendo Japan

Обновление данных (UPDATE)

Очень распространенной задачей при работе с базами данных является изменение записей в таблицах. Для этого служит оператор UPDATE.

Чтобы составить запрос на обновление данных, нужно сделать следующее:

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

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

tv_series

id name genre still_running imdb_rating
1 Breaking Bad drama no 9.5
2 Game of Thrones comedy yes 9.5
3 Rick and Morty comedy yes 9.4
4 Sherlock mystery yes 9.3

SQL

			UPDATE tv_series 
			SET genre = 'drama' 
			WHERE id = 2;
		  
×

Результат запроса

id name genre still_running imdb_rating
2 Game of Thrones drama yes 9.5

Удаление записей

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

tv_series

id name genre still_running imdb_rating
1 Breaking Bad drama no 9.5
2 Game of Thrones comedy yes 9.5
3 Rick and Morty comedy yes 9.4
4 Sherlock mystery yes 9.3

SQL

			DELETE FROM tv_series 
			WHERE id = 4
		  
×

Результат запроса

id name genre still_running imdb_rating
1 Breaking Bad drama no 9.5
2 Game of Thrones comedy yes 9.5
3 Rick and Morty comedy yes 9.4

Будьте очень внимательными когда собираетесь удалить что-то. Если не указать в запросе значение оператора WHERE, то можно удалить всю таблицу целиком.

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

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

SQL

TRUNCATE TABLE table_name;

Если же вы хотите удалить таблицу полностью, чтобы от нее не осталось и следа, выполните запрос DROP TABLE.

SQL

DROP TABLE table_name;

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

Заключение

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

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

Оригинал статьи - http://tutorialzine.com/2016/01/learn-sql-in-20-minutes/

Прочитано 23286 раз
Другие материалы в этой категории: « Знакомимся с нормализацией баз данных
Мои услуги

Предлагаю следующие услуги:

  • Верстка шаблона сайта из дизайн-макета для CMS «1С-Битрикс Управление сайтом» и CMS “Joomla”
  • Создание форм различной сложности (обратная связь, анкеты и тп) для указанных CMS
  • Настройка и кастомизация компонентов и модулей для указанных CMS
  • Доработка модулей и компонентов для указанных CMS, добавление нестандартного функционала
  • Разработка лендингов (landing-pages)

По все вопросам обращайтесь через форму обратной связи

Скачать

Предлагаю вашему вниманию:

Наверх