О языке SQL на примере SQLite, MySQL и PostgreSQL

4
18495
Добавить в избранное

Говоря о БД, нельзя не вспомнить о языке SQL, СУБД и запросах, непонятных на первый взгляд. После нашей статьи вы освоите основы SQL.

В нашей работе мы будем использовать:

  1. СУБД (SQLite, MySQL и PostgreSQL)
  2. Терминал (может быть любой: cmd, conemu, git-bash)
  3. Notepad++
  4. Любая IDE

Notepad++ нам понадобится только для удобного ввода и хранения необходимых данных, а IDE – для понимания работы с SQL через другие языки программирования (в примерах используется Java). Основная часть работы будет выполняться через терминал.

Пара слов о языке SQL

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

Вот пример работы с БД в Java:

Запросы, которые «обрамлены» двойными кавычками после .prepareStatement, – это и есть SQL. А вот как аналогичные запросы будут выглядеть в терминале:

Все о языке SQL на примере SQLite, MySQL и PostgreSQL

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

Синтаксис SQL

Запросы на SQL – это простая линейная последовательность операторов. В запросах используются:

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

Любой оператор начинается с ключевого слова-действия вроде SELECT, CREATE, UPDATE и т. д. В конце обязательно ставится точка с запятой. Оператор может свободно занимать как одну, так и несколько строк. Разделителями логических единиц выступают:

  • 1 или несколько пробелов;
  • 1 или несколько символов новой строки;
  • 1 или несколько символов табуляции.

Комментарии могут помечаться такими способами:

  • начиная двойным минусом;
  • начиная #;
  • между /* и */ (комментарии языка Си).

Подключение к базе данных

В SQLite нет таких понятий, как пользователь или пароль. База данных представлена в виде файла, и если у вас есть доступ к файлу – есть доступ и к базе. Для создания БД и подключения к ней нужно выполнить следующее:

SQLite

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

К MySQL подключиться также несложно, но здесь придется убедиться, что сервер запущен. Для этого перейдите в «Службы» и проверьте состояние:

MySQL

Если сервер запущен, введите mysql -u имя_пользователя -p, где имя_пользователя – это логин, под которым вы зарегистрировались. Пароль можно также написать следом за -p, но  после все равно придется вводить его еще раз, а терминал предупредит о том, что пароль нельзя «светить». Зачастую -u и -p – это root и root, но если вы сменили на что-то более сложное, постарайтесь не забыть, так как при работе с MySQL авторизовываться придется часто.

MySQL

Для вызова списка доступных команд используется help или \h.

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

Workbench

В PostgreSQL все аналогично, только вводится psql -U postgres. Также PostgreSQL можно настроить для быстрой авторизации без пароля:

PostgreSQL

Список доступных команд выводится по help. В списке содержатся команды программы и SQL-команды (а в языке SQL их немало).

Так подключение к БД выглядит в Java:

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

К самым распространенным типам данных относятся: INTEGER (он же INT), BIGINT, FLOAT, DOUBLE, BOOLEAN, VARCHAR (до 255 символов), TEXT, LONGTEXT, DATE, DATETIME, TIME, TIMESTAMP. С ними придется столкнуться при создании и редактировании таблиц, так как у каждого столбца будет свой тип данных.

Запросы в SQL очень удобны: это просто английские слова, которые отображают желаемое действие.

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

MySQL

В SQLite просмотреть таблицы можно с помощью команды .tables:

SQLite

В MySQL это делается при помощи show tables;:

MySQL

В PostgreSQL – через \d:

PostgreSQL

Не поленитесь воспользоваться сперва Notepad++, так как при возникновении ошибки (банальная опечатка) вы сможете своевременно отредактировать запрос и вставить его правильный вариант в терминал.

Вот наши команды в формате кода:

Так создание таблицы выглядит в Java:

Регистр букв не имеет значения, но если вы работаете с Notepad++ или IDE, для команд лучше использовать прописные буквы: текст будет визуально отделен от остального кода, и вы сможете четко прослеживать начало и конец запросов на языке SQL.

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

В этом случае используются такие команды:

Если вы запускаете какой-то файл, чтобы не было сбоев, просто напишите проверку на существование таблицы и/или базы данных:

Если вы воспользуетесь командами show databases или show tables, то увидите, что удаление прошло успешно.

Заполнение, редактирование и вывод таблицы

Запросы на языке SQL будут одинаковыми для всех СУБД, поэтому рассмотрим заполнение, редактирование и вывод таблицы на примере MySQL.

Чтобы заполнить таблицу значениями, необходимо помнить типы данных в столбцах, и в соответствии с этими типами заполнять. Допустим, у нас есть таблица test с группами данных id INT PRIMARY KEY (первичный ключ) и name VARCHAR (255) NOT NULL (не нулевое значение: обязательно заполняется). Тогда заполняться эти поля должны следующим образом:

В Java добавление в таблицу информации выглядит так:

Если мы установим для id констрейн AUTO_INCREMENT, это поле будет заполняться автоматически, начиная с единицы и далее. В таком случае нам не придется прописывать id: мы просто будем заполнять name.

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

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

В данном случае мы поменяем только второе имя.

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

Выводить данные можно все или какие-то конкретные. В приведенном ниже примере мы выполняем следующие действия:

  1. Выводим все данные таблицы.
  2. Выводим только 1 столбец.
  3. Выводим 1 строку.
  4. Выводим все строки, идущие после первой.
  5. Выводим данные по id в возрастающем порядке.
  6. Выводим данные по id в обратном порядке.

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

Вывод данных в Java выглядит так:

Мы также можем переименовать поле и сменить его тип:

Или просто сменить тип, оставив прежнее имя:

Импорт и экспорт файлов

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

Для SQLite:

Для MySQL:

Для PostgreSQL:

PRIMARY KEY и FOREIGN KEY

FOREIGN KEY (внешний ключ) необходим для ограничения по ссылкам. Создается прямая связь между значениями двух полей. Поле, которое ссылается на другое, называется внешним ключом, а поле, на которое ссылаются – родительским ключом. Их имена могут быть разными, но тип поля должен соответствовать. Внешний ключ связан с таблицей, на которую он ссылается. Каждое значение внешнего ключа должно ссылаться на одно и то же значение родительского ключа. Если это условие верно, БД пребывает в состоянии ссылочной целостности.

Давайте рассмотрим на примере. Допустим, у нас есть 2 таблицы: регионы и города.

Примечание: для SQLite вместо AUTO_INCREMENT используется AUTOINCREMENT, а в PostgreSQL – SERIAL.

В regions_id хранится идентификатор региона, и мы делаем его внешним ключом на поле id таблицы regions.

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

Однако запрос успешно выполнится. Это связано с тем, что зачастую в разных СУБД используются специальные команды для включения механизма внешних ключей. Как его включить?

Для SQLite:

Для MySQL:

В PostgreSQL данный механизм включен по умолчанию.

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

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

Вывод нескольких таблиц

На языке SQL можно вывести сразу несколько таблиц. Создадим в базе данных test две таблицы: test1 и test2. Заполним их значениями, при этом id будут одинаковыми и идти по порядку (можно реализовать с помощью констрейна AUTO_INCREMENT). Чтобы вывести обе таблицы рядом, выполним следующую команду:

Видим:

Все о языке SQL на примере SQLite, MySQL и PostgreSQL

Итоги

Да, мы затронули лишь базис, но даже с этим базисом вы можете смело вписывать в резюме, что понимаете и умеете работать с БД на языке SQL. Вот только чем более сложные операции необходимо реализовать, тем большим будет различие в реализации для разных СУБД. Так что устраиваясь каким-нибудь Salesforce-разработчиком, просто подтяните знания по каждой из этих систем. Удачи!

Вас также могут заинтересовать другие статьи по теме:

Инетесуетесь базами данных?

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

И не беспокойтесь, мы тоже не любим спам. Отписаться можно в любое время.




Комментариев: 4

  1. Юрий Царьков

    1. Странное ограничение varchar до 255 символов- согласно документации любой из рассматриваемых СУБД, размер для данного типа может быть много больше 255.
    2. >Их имена могут быть разными, но тип поля, номер и порядок должны быть одинаковыми.
    Не понятно, что такое номер и порядок? Ни в одной РСУБД нет такого ограничения на внешний ключ, да и быть не может.
    3. Некоторые примеры на java не безопасны для инъекций! Это вообще не допустимо для учебного материала ни при каких условиях.

    1. 1. При условии max длины в 255 символов СУБД может выбрать 1 байт, чтобы указать длину данных в поле. В случае, если предел >= 256 символов, потребуется 2 байта.
      2. Исправлено.
      3. Тем не менее, все примеры выдернуты из симбиоза нескольких учебных материалов.

      Спасибо за внимательность 🙂

Добавить комментарий