Как научиться писать запросы sql

Как быстро писать сложные и надежные SQL-запросы на выборку коллекций

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

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

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

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

Итак, традиционный путь

Предположим, у нас есть интернет-магазин и нам необходимо выводить список товаров, используя набор критериев (фильтров). Мы пишем:

SELECT * FROM product WHERE price > :start_price AND price < :end_price;

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

Но что, если нужно выводить рубрики, в которые включены товары из выборки? Отлично, можно добавить соединение с рубриками, потом GROUP BY и агрегатную функцию.

SELECT p.*, GROUP_CONCAT(c.title SEPARATOR ‘, ‘) as categories FROM product p LEFT JOIN product_category pc ON pc.product_id=p.id LEFT JOIN category c ON c.id=pc.category_id WHERE p.price > :start_price AND p.price < :end_price GROUP BY p.id;

Ну.. это работает. А что если нам еще нужен список тегов? И обычно здесь программист добавляет еще JOIN`ов

SELECT p.*, GROUP_CONCAT(c.title SEPARATOR ‘, ‘) as categories, GROUP_CONCAT(t.tag SEPARATOR ‘, ‘) as tags FROM product p LEFT JOIN product_category pc ON pc.product_id=p.id LEFT JOIN category c ON c.id=pc.category_id LEFT JOIN product_tag pt ON pt.product_id=p.id LEFT JOIN tag t ON t.id=pc.tag_id WHERE p.price > :start_price AND p.price < :end_price GROUP BY p.id;

Это уже не работает. Категории и теги начинают задваиваться, ну ладно, мы же можем сделать GROUP_CONCAT(DISTINCT . )! А что если нужно подсчитывать количество отзывов по каждому товару? Еще JOIN? И мы оказываемся в ситуации, когда каждый следующий JOIN начинает мешать предыдущему.

Здесь нужно задуматься.

Альтернативный подход

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

Фильтрация данных (сужение выборки)
Представление данных (вывод данных и форматирование)

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

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

SELECT p.* FROM product p INNER JOIN manufacturer m ON m.id=p.manufacturer_id AND m.country_key=’RU’ WHERE p.price > :start_price AND p.price < :end_price;

Здесь INNER JOIN не добавляет новые колонки, но сужает выборку. Это явное поведение по типу фильтра.

Распределив части запроса на группы, нужно собрать из них финальный запрос в три простых шага:

Построить основной запрос на выборку без GROUP BY и по возможности используя покрывающие индексы
Добавить соединения, обеспечивающие фильтрацию (сужение выборки).
Добавить соединения, обеспечивающие представление, здесь можно использовать GROUP BY

Соединение с таблицами только по уникальному ключу

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

Идея проста — не допустить разрастания выборки по количеству строчек после соединения (и таким образом GROUP BY в основной части запроса не понадобится).

В примере выше с производителями — принцип обеспечен уникальностью ключа manufacturer.id, таким образом соединение с таблицей по этому ключу не приведет к повторению строчек из основной таблицы.

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

SELECT p.*, pc.categories, pt.tags FROM product LEFT JOIN ( SELECT pc.product_id, GROUP_CONCAT(c.title SEPARATOR ‘, ‘) as categories FROM product_category pc INNER JOIN category c ON c.id=pc.category_id GROUP BY pc.product_id; ) pc ON pc.product_id=p.id LEFT JOIN ( SELECT pt.product_id, GROUP_CONCAT(t.title SEPARATOR ‘, ‘) as tags FROM product_tag pt INNER JOIN tag t ON c.id=pt.tag_id GROUP BY pt.product_id; ) pt ON pt.product_id=p.id WHERE p.price > :start_price AND p.price < :end_price;

Основная проблема этого запроса в том, что при большом количестве товаров в подзапросах может быть большая выборка. Однако, с этим можно работать, привнося в подзапросы дополнительные фильтры из основного запроса, сужая выборку и здесь. Еще есть надежда, что оптимизатор БД, выполняя одни и те же подзапросы, догадается закешировать их результаты.

Давайте добавим фильтр по тем товарам, у которых пользовательская оценка равна 5 звездам:

SELECT p.*, pc.categories, pt.tags FROM product INNER JOIN ( SELECT DISTINCT r.product_id FROM review r WHERE r.starts=5 ) r ON r.product_id=p.id LEFT JOIN ( . ) pc ON pc.product_id=p.id LEFT JOIN ( . ) pt ON pt.product_id=p.id WHERE p.price > :start_price AND p.price < :end_price

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

Порядок соединений — от фильтров к представлениям

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

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

Отказ от GROUP BY в основной части запроса

Как только мы начинаем практиковать изложенные выше принципы, GROUP BY из основной части запроса обычно исчезает сам по себе за ненадобностью.

Но если все же он после всех манипуляций остался, то, скорее всего, это обусловлено объективной ситуацией. У вас, скорее всего — агрегатная выборка, а не выборка коллекции.

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

Запросы SQL: руководство для начинающих

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

Аббревиатура SQL расшифровывается как «Structured Query Language» — язык структурированных запросов. С помощью этого языка вы можете работать с записями в базах данных.

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

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

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

Структура базы данных

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

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

Возьмем для примера некую компанию, штат которой разбросан по всему миру. Допустим, у этой компании есть много баз данных. Чтобы увидеть их полный список, нужно набрать SHOW DATABASES;

Результат может выглядеть как-то так:

+--------------------+ | Databases | +--------------------+ | mysql | | information_schema | | employees | | test | | sys | +--------------------+

В каждой отдельной базе данных может быть много таблиц. Чтобы увидеть, какие таблицы есть в базе данных employees из нашего примера, нужно набрать SHOW TABLES in employees; . В таблицах могут содержаться данные по разным командам, что отражается в названиях: engineering, product, marketing, sales.

+----------------------+ | Tables_in_employees | +----------------------+ | engineering | | product | | marketing | | sales | +----------------------+

Все таблицы состоят из различных столбцов, описывающих данные.

Чтобы просмотреть столбцы таблицы Engineering, используйте Describe Engineering; . Каждый столбец этой таблицы может описывать какой-то один атрибут сотрудника, например: employee_id , first_name , last_name , email , country и salary .

+-----------+-------------------+--------------+ | Name | Null | Type | +-----------+-------------------+--------------+ |EMPLOYEE_ID| NOT NULL | INT(6) | |FIRST_NAME | NOT NULL |VARCHAR2(20) | |LAST_NAME | NOT NULL |VARCHAR2(25) | |EMAIL | NOT NULL |VARCHAR2(255) | |COUNTRY | NOT NULL |VARCHAR2(30) | |SALARY | NOT NULL |DECIMAL(10,2) | +-----------+-------------------+--------------+

Таблицы также состоят из строк — отдельных записей. В нашем примере в строках будут указаны id, имена, фамилии, email, зарплата и страны проживания сотрудников. Каждая строка будет касаться одного сотрудника, допустим, из команды Engineering.

Базовые запросы SQL

Все операции, которые можно осуществлять с данными, входят в понятие «CRUD».

CRUD расшифровывается как Create, Read, Update и Delete (создать, прочесть, обновить, удалить). Это четыре основных операции, которые мы осуществляем, делая запросы к базе данных.

Мы создаем информацию в базе ( CREATE ), мы читаем, получаем информацию из базы ( READ ), мы обновляем данные или осуществляем какие-то манипуляции с ними ( UPDATE ) и, при желании, можем удалять данные ( DELETE ).

Для осуществления различных операций с данными в SQL есть специальные ключевые слова (операторы). Ниже мы рассмотрим некоторые простые запросы SQL и их синтаксис.

Ключевые слова в SQL

CREATE DATABASE

Для создания базы данных с именем engineering мы используем следующий код:

CREATE DATABASE engineering;

CREATE TABLE

CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype );

Этот запрос создает новую таблицу в базе данных.

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

Что касается типов данных (datatype), они могут быть разными. Самые распространенные — INT, DECIMAL, DATETIME, VARCHAR, NVARCHAR, FLOAT и BIT.

В нашем примере запрос может быть таким:

CREATE TABLE engineering ( employee_id int(6) NOT NULL, first_name varchar(20) NOT NULL, last_name varchar(25) NOT NULL, email varchar(255) NOT NULL, country varchar(30), salary decimal(10,2) NOT NULL );

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

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	COUNTRY	SALARY

ALTER TABLE

После создания таблицы мы можем изменять ее путем добавления столбцов.

ALTER TABLE table_name ADD column_name datatype;

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

ALTER TABLE engineering ADD birthday date;

Теперь таблица выглядит немного иначе:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	COUNTRY	SALARY	BIRTHDAY

INSERT

Это ключевое слово служит для вставки данных в таблицы и создания новых строк. В аббревиатуре CRUD это соответствует букве C.

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

Этот запрос создает новую запись в таблице, т. е. новую строку.

В части INSERT INTO мы указываем столбцы, которые хотим заполнить информацией. В VALUES указана информация, которую нужно сохранить.

При вставке строковых значений их нужно брать в одинарные кавычки.

INSERT INTO table_name(employee_id,first_name,last_name,email,country,salary) VALUES (1,'Timmy','Jones','timmy@gmail.com','USA',2500.00); (2,'Kelly','Smith','ksmith@gmail.com','UK',1300.00);

Теперь таблица будет выглядеть так:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	COUNTRY	SALARY
1	Timmy	Jones	timmy@gmail.com	USA	2500.00
2	Kelly	Smith	ksmith@gmail.com	UK	1300.00

SELECT

Это ключевое слово служит для выборки данных из базы. В CRUD эта операция соответствует букве R.

SELECT column1,column2 FROM table_name;

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

SELECT first_name,last_name FROM engineering;

+-----------+----------+ |FirstName | LastName | +-----------+----------+ | Timmy | Jones | | Kelly | Smith | +-----------+----------+

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

В части FROM определяется сама таблица.

Вот еще один пример запроса SELECT :

SELECT * FROM table_name;

Астериск (звездочка) означает, что нам нужна вся информация из указанной таблицы (а не отдельный столбец).

WHERE

WHERE позволяет составлять более специфичные (конкретные) запросы.

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

SELECT employee_id,first_name,last_name,email,country FROM engineering WHERE salary > 1500

Таблица из предыдущего примера:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	COUNTRY	SALARY
1	Timmy	Jones	timmy@gmail.com	USA	2500.00
2	Kelly	Smith	ksmith@gmail.com	UK	1300.00

Теперь вывод будет такой:

+-----------+----------+----------+----------------+------------+ |employee_id|first_name|last_name |email |country | +-----------+----------+----------+----------------+------------+ | 1| Timmy |Jones |timmy@gmail.com | USA | +-----------+----------+----------+----------------+------------+

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

Операторы AND, OR, BETWEEN в SQL

Эти операторы позволяют еще больше уточнить запрос. С их помощью можно добавить больше критериев в блоке WHERE .

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

SELECT column_name FROM table_name WHERE column1 =value1 AND column2 = value2;

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

SELECT column_name FROM table_name WHERE column_name = value1 OR column_name = value2;

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

SELECT column1,column2 FROM table_name WHERE column_name BETWEEN value1 AND value2;

Все эти операторы можно комбинировать друг с другом.

Допустим, наша таблица выглядит так:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	COUNTRY	SALARY
1	Timmy	Jones	timmy@gmail.com	USA	2500.00
2	Kelly	Smith	ksmith@gmail.com	UK	1300.00
3	Jim	White	jwhite@gmail.com	UK	1200.76
4	José Luis	Martìnez	jmart@gmail.com	Mexico	1275.87
5	Emilia	Fischer	emfis@gmail.com	Germany	2365.90
6	Delphine	Lavigne	lavigned@gmail.com	France	2108.00
7	Louis	Meyer	lmey@gmail.com	Germany	2145.70

Если мы напишем такой запрос:

SELECT * FROM engineering WHERE employee_id BETWEEN 3 AND 7 AND country = 'Germany';

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

+------------+-----------+-----------+----------------+--------+--------+ |employee_id | first_name| last_name | email |country |salary | +------------+-----------+-----------+----------------+--------+--------+ |5 |Emilia |Fischer |emfis@gmail.com | Germany| 2365.90| |7 |Louis |Meyer |lmey@gmail.com | Germany| 2145.70| +------------+-----------+-----------+----------------+--------+--------+

Были выбраны все столбцы, где employee_id от 3 до 7, а страна проживания — Германия.

ORDER BY

Ключевое слово ORDER BY позволяет отсортировать выдачу по столбцам, указанным в SELECT .

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

По умолчанию сортировка идет по возрастанию. Но мы можем указать желаемый порядок явно — при помощи команды ORDER BY column_name DESC | ASC .

SELECT employee_id, first_name, last_name,salary FROM engineering ORDER BY salary DESC;

В этом примере мы отсортировали зарплату сотрудников в команде engineering и представили вывод в порядке убывания числовых значений (DESC — от англ. descending — «нисходящий»).

Читать:

Как сделать галерею в html и css

GROUP BY

Ключевое слово GROUP BY в SQL позволяет комбинировать строки с идентичными и похожими данными.

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

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

Здесь COUNT(*) подсчитывает все строки и возвращает число строк в указанной таблице, группируя строки-дубликаты.

От редакции Techrocks: о COUNT и других агрегатных функциях можно почитать в статье «Агрегатные функции в SQL: объяснение с примерами запросов».

LIMIT

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

Это бывает полезно при работе с большими наборами данных. Если данных много, запрос может обрабатываться слишком долго. Но когда будет достигнут лимит результатов, обработка прекратится.

SELECT column1,column2 FROM table_name LIMIT number;

UPDATE

Ключевое слово UPDATE позволяет обновлять записи в таблице. В CRUD этой операции соответствует буква U.

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

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

UPDATE engineering SET country = 'Spain' WHERE employee_id = 1

Прежде наша таблица выглядела так:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	COUNTRY	SALARY
1	Timmy	Jones	timmy@gmail.com	USA	2500.00
2	Kelly	Smith	ksmith@gmail.com	UK	1300.00
3	Jim	White	jwhite@gmail.com	UK	1200.76
4	José Luis	Martìnez	jmart@gmail.com	Mexico	1275.87
5	Emilia	Fischer	emfis@gmail.com	Germany	2365.90
6	Delphine	Lavigne	lavigned@gmail.com	France	2108.00
7	Louis	Meyer	lmey@gmail.com	Germany	2145.70

Теперь, после выполнения запроса, она выглядит так:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	COUNTRY	SALARY
1	Timmy	Jones	timmy@gmail.com	Spain	2500.00
2	Kelly	Smith	ksmith@gmail.com	UK	1300.00
3	Jim	White	jwhite@gmail.com	UK	1200.76
4	José Luis	Martìnez	jmart@gmail.com	Mexico	1275.87
5	Emilia	Fischer	emfis@gmail.com	Germany	2365.90
6	Delphine	Lavigne	lavigned@gmail.com	France	2108.00
7	Louis	Meyer	lmey@gmail.com	Germany	2145.70

Обновилась страна проживания сотрудника с id 1.

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

UPDATE table_name SET table_name1.column_name1 = table_name2.column_name1 table_name1.column_name2 = table_name2.column2 FROM table_name1 JOIN table_name2 ON table_name1.column_name = table_2.column_name;

DELETE

Ключевое слово DELETE служит для удаления записей из таблицы. В CRUD операция удаления представлена буквой D.

DELETE FROM table_name WHERE condition;

Пример с нашей таблицей:

DELETE FROM engineering WHERE employee_id = 2;

При выполнении запроса будет удалена запись о сотруднике с id 2 из команды engineering.

DROP COLUMN

Чтобы удалить из таблицы столбец, можно воспользоваться следующим кодом:

ALTER TABLE table_name DROP COLUMN column_name;

DROP TABLE

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

DROP TABLE table_name;

Итоги

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

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

От редакции Techrocks. Вам также могут быть интересны другие статьи по теме SQL:

Топ-30 вопросов по SQL на технических собеседованиях
ТОП-10 сайтов, на которых можно потренировать SQL-запросы
Порядок выполнения SQL-операций
Выражение CASE в SQL: объяснение на примерах

SQL для начинающих

�� Система управления базами данных (СУБД) — это отдельная программа, которая работает как сервер, независимо от PHP.

Создавать свои базы данных, таблицы и наполнять их данными можно прямо из этой же программы, но для выполнения этих операций прежде придётся познакомиться с ещё одним языком программирования — SQL.

SQL или Structured Query Language (язык структурированных запросов) — язык программирования, предназначенный для управления данными в СУБД. Все современные СУБД поддерживают SQL.

На языке SQL выражаются все действия, которые можно провести с данными: от записи и чтения данных, до администрирования самого сервера СУБД. Для повседневной работы совсем не обязательно знать весь этот язык; достаточно ознакомиться лишь с основными понятиями синтаксиса и ключевыми словами.

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

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

Например, чтобы вывести на экран все записи из таблицы города , составим такой запрос:

ПРОЧИТАТЬ всё ИЗ ТАБЛИЦЫ 'города'

Если перевести этот запрос на язык SQL, то корректным результатом будет:

SELECT * FROM 'cities'

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

ВСТАВЬ В ТАБЛИЦУ 'города' ЗНАЧЕНИЯ 'имя города' = 'Санкт-Петербург'

INSERT INTO 'cities' SET 'name' = 'Санкт-Петербург'

Эта команда создаст в таблице города новую запись, где полю имя города будет присвоено значение Санкт-Петербург .

С помощью SQL можно не только добавлять и читать данные, но и:

удалять и обновлять записи в таблицах;
создавать и редактировать сами таблицы;
производить операции над данными: считать сумму, получать самое большое или малое значение, и так далее;
настраивать работу сервера СУБД.

MySQL

Существует множество различных реляционных СУБД. Самая известная СУБД — это Microsoft Access, входящая в состав офисного пакета приложений Microsoft Office. Нет никаких препятствий для использования в качестве СУБД MS Access, но для задач веб-программирования гораздо лучше подходит альтернативная программа — MySQL.

В отличие от MS Access, MySQL абсолютно бесплатна, может работать на серверах с Linux, обладает гораздо большей производительностью и безопасностью, что делает её идеальным кандидатом на роль базы данных в веб-разработке.

Подавляющее большинство сайтов и приложений на PHP используют в качестве СУБД именно MySQL.

Установка

Последняя версия MySQL доступна для загрузки по ссылке: https://dev.mysql.com/downloads/mysql/. На этой странице следует выбрать MySQL Installer for Windows и нажать на кнопку Download для загрузки.

В процессе установки запомните директорию, куда вы устанавливаете MySQL (скрывается под ссылкой Advanced options). На шаге Accounts and Roles установщик попросит придумать пароль для доступа к БД (MySQL Root Password) — обязательно запомните или запишите этот пароль — он вам ещё понадобится.

Если для своей работы вы используете программную среду OpenServer, то этот раздел можно смело пропустить, так как в состав OpenServer уже входит свежая версия MySQL.

Выполнение запросов

По умолчанию, если вы не устанавливали дополнительные программы, у MySQL нет графического интерфейса пользователя. Это значит, что единственный способ работы с ней — это использование командной строки.

Откройте командную строку (Выполнить — cmd.exe ).
Перейдите в каталог с установленной MySQL: cd /d /bin .
Выполните: mysql -uroot -p .
Введите пароль, заданный при установке.

Если вы всё выполнили верно, то в командной строке запустится клиент для работы с MySQL (вы поймете это по строке приглашения «mysql>»). С этого момента можно вводить любые SQL запросы, но каждый запрос обязательно должен заканчиваться точкой с запятой ; .

Оператор SQL create database: создание новой базы данных

Приступим к практике — начнём создавать базу данных для ведения погодного дневника.

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

CREATE DATABASE

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

create table

Зачем нужен:создание таблиц

Создав новую БД, сообщим MySQL, что теперь мы собираемся работать именно с ней. Выбор активной БД выполняется командой:

Пришло время создать первые таблицы! Для ведения дневника по всем правилам, понадобится создать три таблицы: города ( cities ), пользователи ( users ) и записи о погоде weather_log . В подразделе «Запись» этой главы описано, как должна выглядеть структура таблицы weather_log . Переведём это описание на язык SQL:

CREATE TABLE weather_log ( id INT AUTO_INCREMENT PRIMARY KEY, city_id INT, day DATE, temperature INT, cloud TINYINT DEFAULT 0 );

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

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

CREATE TABLE cities ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(128) )

MySQL может показать созданную таблицу, если попросить об этом командой:

SHOW COLUMNS FROM weather_log

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

Что такое первичный ключ

В примере с созданием новой таблицы при перечислении необходимых полей первым полем идёт id INT AUTO_INCREMENT PRIMARY KEY . Это поле называется первичным ключом. Обязательно создавать первичный ключ в каждой таблице.

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

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

А если ещё и добавить аттрибут AUTO_INCREMENT , то MySQL при добавлении новых записей будет заполнять это поле сама. AUTO_INCREMENT будет играть роль счётчика — каждая новая запись в таблице получит значение на единицу больше максимального существующего значения.

insert into

Зачем нужен: добавление записи в таблицу

Начнём с добавления новых данных в таблицу. Для добавления записи используется следующий синтаксис:

insert into set = , = .

В начале добавим город в таблицу городов:

insert into cities set name = 'Санкт-Петербург'

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

Теперь создадим запись о погоде за сегодня.

При определении таблицы weather_log мы решили ссылаться на город, путём записи в поле city_id идентификатора города из таблицы cities. Так как мы только что добавили новый город, ничего не мешает использовать его идентификатор в записи о погоде.

Идентификатором города будет первичный ключ, который также был определён в качестве первого поля таблицы. Нумерация этого поля начинается с единицы, значит первая добавленная запись имеет идентификатор 1 . Зная это, запрос на добавление записи о погоде в Санкт-Петербурге за третье сентября 2017 года выглядит так:

INSERT INTO weather_log SET city_id = 1, day = '2017-09-03', temperature = 5, cloud = 1;

select. Чтение информации из БД

Для вывода информации из БД используются запросы типа SELECT .

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

SELECT FROM

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

SELECT id, name FROM cities

Все погодные записи:

SELECT id, day, city_id, temperature, cloud FROM weather_log

Вместо перечисления всех столбцов можно использовать знак звездочки — * .

Оператор update: обновление информации в БД

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

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

UPDATE SET = , = . WHERE =

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

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

Запрос на обновление:

UPDATE weather_log SET day = '2022-12-07' WHERE join: объединение записей из двух таблиц

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

Поменяем запрос на показ погодных записей, чтобы он объединял две таблицы, а в поле города показывалось его название, а не идентификатор:

SELECT day, cities.name, temperature, cloud FROM weather_log JOIN cities ON weather_log.city_id = cities.id

Важно усвоить три самых главных момента:

При чтении из объединённых таблиц, в перечислении полей после SELECT нужно явно указывать в поле имени также имя таблицы, с которой производится объединение.
Всегда есть основная таблица (тб1), из которой читается большинство полей, и присоединяемая (тб2), имя которой определяется после оператора JOIN.
Помимо указания имени второй таблицы, обязательно следует указать условие, по которому будет происходить объединение. В этом примере таким условием будет соответствие идентификатора города из тб1 ( weather_log.city_id ) первичному ключу города из тб2 ( cities.id ).

«Доктайп» — журнал о фронтенде. Читайте, слушайте и учитесь с нами.