Краткий справочник по управлению базой данных SQL

MariaDB, mySQL | Amber | Комментировать запись

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

Как пользоваться справочником

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

Обратите внимание: хотя SQL признан стандартом, большинство программ для обслуживания БД SQL имеют собственные расширения. В этом мануале в качестве примера системы управления реляционными базами данных (RDBMS) используется MySQL, но данные команды будут работать и с другими программами реляционных БД, включая PostgreSQL, MariaDB и SQLite. Если между СУБД есть существенные различия, мы предлагаем альтернативные команды.

Командная строка БД

Аутентификация по сокету

По умолчанию в Ubuntu 18.04 пользователь root MySQL может проходить аутентификацию без пароля, используя следующую команду:

sudo mysql

Чтобы открыть командную строку PostgreSQL, используйте следующую команду. В этом примере вы войдете в систему как пользователь postgres (это включенная роль суперпользователя), но вы можете заменить его на любую существующую роль:

sudo -u postgres psql

Парольная аутентификация

Если ваш root пользователь MySQL поддерживает парольную аутентификацию, вы можете пройти ее с помощью следующей команды:

mysql -u root -p

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

mysql -u user -p

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

mysql -u root -ppassword

Создание базы данных

Следующая команда создаст базу данных со стандартными параметрами:

CREATE DATABASE database_name;

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

CREATE DATABASE database_name CHARACTER SET character_set COLLATE collation;

Просмотр баз данных

Чтобы увидеть, какие базы данных существуют в вашей установке MySQL или MariaDB, выполните следующую команду:

SHOW DATABASES;

В PostgreSQL вы можете увидеть список текущих баз данных с помощью следующей команды:

\list

Удаление базы данных

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

DROP DATABASE IF EXISTS database;

Создание пользователя

Чтобы создать для вашей БД профиль пользователя без привилегий, запустите:

CREATE USER username IDENTIFIED BY 'password';

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

CREATE USER user WITH PASSWORD 'password';

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

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Обратите внимание на ключевое слово PRIVILEGES в этом операторе GRANT. В большинстве РСУБД это ключевое слово является необязательным, и этот оператор можно аналогично записать в таком виде:

GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Однако имейте в виду, что ключевое слово PRIVILEGES необходимо для предоставления таких привилегий, когда включен режим Strict SQL.

Удаление пользователя

С помощью этой команды можно удалить пользователя БД:

DROP USER IF EXISTS username;

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

Выбор базы данных

Прежде чем создать таблицу, вы должны сообщить СУБД базу данных, в которой вы хотите ее создать. В MySQL и MariaDB это делается так:

USE database;

В PostgreSQL это команда:

\connect database

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

Следующая команда создаст новую таблицу по имени table и включит в нее два столбца с разными типами данных.

CREATE TABLE table ( column_1 column_1_data_type, column_2 column_2_data_taype );

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

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

DROP TABLE IF EXISTS table

Добавление данных в таблицу

С помощью этого синтаксиса вы можете вставить в таблицу одну строку данных.

INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_A', 'data_B', 'data_C' );

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

INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_1A', 'data_1B', 'data_1C' ),  ( 'data_2A', 'data_2B', 'data_2C' ), ( 'data_3A', 'data_3B', 'data_3C' );

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

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

DELETE FROM table WHERE column='value';

Примечание: Если вы не включите WHERE в оператор DELETE, как в следующем примере, он удалит все данные, содержащиеся в таблице, но не столбцы или саму таблицу:

DELETE FROM table;

С помощью следующего синтаксиса можно обновить данные в указанной строке. Обратите внимание на оператор WHERE в конце команды – он сообщает SQL, какую строку нужно обновить.

UPDATE table SET column_1 = value_1, column_2 = value_2 WHERE column_A=value;

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

Добавление столбца

Следующая команда добавит новый столбец в таблицу:

ALTER TABLE table ADD COLUMN column data_type;

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

Чтобы удалить столбец из таблицы, введите:

ALTER TABLE table DROP COLUMN column;

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

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

SELECT column FROM table;

Чтобы запросить несколько столбцов из одной таблицы, разделите имена столбцов запятой:

SELECT column_1, column_2 FROM table;

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

SELECT * FROM table;

Оператор WHERE

Вы можете сузить результаты запроса, добавив оператор SELECT с оператором WHERE:

SELECT column FROM table WHERE conditions_that_apply;

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

SELECT * FROM table WHERE column = value;

Работа с операторами сравнения

Оператор сравнения в WHERE определяет способ сравнения указанного столбца со значением. Вот некоторые распространенные операторы сравнения SQL:

Работа с подстановочными знаками

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

Звездочка (*) – это подстановочный знак, который значит «все», он будет запрашивать каждый столбец в таблице:

SELECT * FROM table;

Знак процента (%) представляет ноль или более неизвестных символов.

SELECT * FROM table WHERE column LIKE val%;

Нижнее подчеркивание (_) указывает на один неизвестный символ.

SELECT * FROM table WHERE column LIKE v_lue;

Подсчет записей в столбце

Функция COUNT используется для определения количества записей в данном столбце. Следующий синтаксис вернет общее количество значений в столбце:

SELECT COUNT(column) FROM table;

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

SELECT COUNT(column) FROM table WHERE column=value;

Нахождение среднего значения в столбце

Функция AVG используется для определения среднего среди значений, хранящихся в определенном столбце. Обратите внимание, что функция AVG будет работать только со столбцами, содержащими числовые значения; при применении на столбцы, содержащие строковые значения, она вернет ошибку или 0.

SELECT AVG(column) FROM table;

Сумма значений в столбце

Функция SUM используется для нахождения суммы всех числовых значений в столбце:

SELECT SUM(column) FROM table;

Как и в случае с AVG, если вы запускаете функцию SUM для столбца, содержащего строковые значения, она вернет ошибку или 0, в зависимости от РСУБД.

Наибольшее значение в столбце

Чтобы найти наибольшее числовое значение в столбце или последнее значение в алфавитном порядке, используйте функцию MAX:

SELECT MAX(column) FROM table;

Наименьшее значение в столбце

Чтобы найти наименьшее числовое значение в столбце или первое значение в алфавитном порядке, используйте функцию MIN:

SELECT MIN(column) FROM table;

Сортировка результатов с помощью операторов ORDER BY

Оператор ORDER BY используется для сортировки результатов запроса. Следующий синтаксис возвращает значения из column_1 и column_2 и сортирует результаты по значениям, содержащимся в column_1, в возрастающем порядке (для строковых значений – в алфавитном порядке).

SELECT column_1, column_2 FROM table ORDER BY column_1;

Чтобы выполнить то же действие, но упорядочить результаты в алфавитном порядке по убыванию или в обратном порядке, добавьте DESC:

SELECT column_1, column_2 FROM table ORDER BY column_1 DESC;

Сортировка результатов с помощью операторов GROUP BY

Оператор GROUP BY похож на ORDER BY, но он используется для сортировки результатов запроса, который включает агрегатную функцию, такую как COUNT, MAX, MIN или SUM. Сами по себе агрегатные функции, описанные в предыдущем разделе, будут возвращать только одно значение. Однако вы можете просмотреть результаты статистической функции, выполненной для каждого соответствующего значения в столбце, включив оператор GROUP BY.

Следующий синтаксис подсчитывает количество совпадающих значений в column_2 и группирует их по возрастанию или в алфавитном порядке:

SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2;

Чтобы выполнить то же действие, но сгруппировать результаты в алфавитном порядке по убыванию или в обратном порядке, добавьте DESC:

SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2 DESC;

Запрос нескольких таблиц с помощью JOIN

Оператор JOIN используется для создания наборов результатов, которые объединяют строки из двух или более таблиц. Оператор JOIN работает, только если все запрашиваемые таблицы содержат столбец с одним и тем же именем и типом данных, как в этом примере:

SELECT table_1.column_1, table_2.column_2 FROM table_1 JOIN table_2 ON table_1.common_column=table_2.common_column;

Это пример оператора INNER JOIN. INNER JOIN вернет все записи, которые имеют совпадающие значения в обеих таблицах, но не покажет записи, которые совпали.

Можно вывести все записи одной из двух таблиц, включая значения, которые не имеют соответствия в другой таблице. Это делается с помощью внешнего оператора JOIN. Такие операторы записываются как LEFT JOIN или RIGHT JOIN.

Предложение LEFT JOIN возвращает все записи из «левой» таблицы и только совпадающие записи из «правой» таблицы. В контексте внешних операторов JOIN левая таблица – это таблица, на которую ссылается FROM, а правая таблица – любая другая таблица, на которую есть ссылка после оператора JOIN. Далее будут показаны все записи из table_1 и только совпадающие значения из table_2. Любые значения, которые не совпадают в table_2, будут отображаться в наборе результатов как NULL:

SELECT table_1.column_1, table_2.column_2 FROM table_1 LEFT JOIN table_2 ON table_1.common_column=table_2.common_column;

Оператор RIGHT JOIN действует так же, как LEFT JOIN, но выводит все результаты из правой таблицы и только совпадающие значения из левой.

SELECT table_1.column_1, table_2.column_2 FROM table_1 RIGHT JOIN table_2 ON table_1.common_column=table_2.common_column;

Объединение нескольких операторов SELECT с помощью UNION

Оператор UNION нужен для объединения результатов двух (или более) операторов SELECT в один набор результатов:

SELECT column_1 FROM table UNION SELECT column_2 FROM table;

Кроме того, оператор UNION может объединить два (или более) оператора SELECT, запрашивающих разные таблицы, в один набор результатов:

SELECT column FROM table_1 UNION SELECT column FROM table_2;

Заключение

В этом мануале рассматриваются некоторые из наиболее распространенных команд SQL, необходимых для управления базами данных, пользователями и таблицами, а также для запроса данных, содержащихся в этих таблицах. Однако существует множество комбинаций операторов, которые создают уникальные наборы результатов. Если вы ищете более подробное руководство по работе с SQL, мы рекомендуем вам обратиться к Database SQL Reference от Oracle.