Как сделать ключевое поле sql

Добавил пользователь Skiper
Обновлено: 05.10.2024

Open with Desktop
View raw
Copy raw contents Copy raw contents

Copy raw contents

Structured Query Language (SQL) — язык структурированных запросов, с помощью него пишутся специальные запросы (SQL инструкции) к базе данных с целью получения этих данных из базы и для манипулирования этими данными.

С точки зрения реализации язык SQL представляет собой набор операторов, которые делятся на определенные группы и у каждой группы есть свое назначение. В сокращенном виде эти группы называются DDL, DML, DCL и TCL.

DDL – Data Definition Language

Data Definition Language (DDL) – это группа операторов определения данных. Другими словами, с помощью операторов, входящих в эту группы, мы определяем структуру базы данных и работаем с объектами этой базы, т.е. создаем, изменяем и удаляем их.

В эту группу входят следующие операторы:

CREATE – используется для создания объектов базы данных;
ALTER – используется для изменения объектов базы данных;
DROP – используется для удаления объектов базы данных.

DML – Data Manipulation Language

Data Manipulation Language (DML) – это группа операторов для манипуляции данными. С помощью этих операторов мы можем добавлять, изменять, удалять и выгружать данные из базы, т.е. манипулировать ими.

В эту группу входят самые распространённые операторы языка SQL:

SELECT – осуществляет выборку данных;
INSERT – добавляет новые данные;
UPDATE – изменяет существующие данные;
DELETE – удаляет данные.

DCL – Data Control Language

Data Control Language (DCL) – группа операторов определения доступа к данным. Иными словами, это операторы для управления разрешениями, с помощью них мы можем разрешать или запрещать выполнение определенных операций над объектами базы данных.

TCL – Transaction Control Language

Transaction Control Language (TCL) – группа операторов для управления транзакциями. Транзакция – это команда или блок команд (инструкций), которые успешно завершаются как единое целое, при этом в базе данных все внесенные изменения фиксируются на постоянной основе или отменяются, т.е. все изменения, внесенные любой командой, входящей в транзакцию, будут отменены.

Базовый синтаксис SQL команды SELECT

Одна из основных функций SQL — получение данных из СУБД. Для построения всевозможных запросов к базе данных используется оператор SELECT. Он позволяет выполнять сложные проверки и обработку данных.

Общая структура запроса

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

DISTINCT используется для исключения повторяющихся строк из результата
ALL (по умолчанию) используется для получения всех данных, в том числе и повторений
FROM перечисляет используемые в запросе таблицы из базы данных
WHERE — это условный оператор, который используется для ограничения строк по какому-либо условию
GROUP BY используется для группировки строк
HAVING применяется после группировки строк для фильтрации по значениям агрегатных функций
ORDER BY используется для сортировки. У него есть два параметра:
ASC (по умолчанию) используется для сортировки по возрастанию
DESC — по убыванию
LIMIT используется для ограничения количества строк для вывода

Псевдонимы используются для представления столбцов или таблиц с именем отличным от оригинального. Это может быть полезно для улучшения читабельности имён и создания более короткого наименования столбца или таблицы.

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

Для создания псевдонимов используется оператор AS:

Вы можете выводить любые строки и числа вместо столбцов:

Вы можете вывести любой столбец, определённый в таблице, например, town_to из таблицы Trip:

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

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

Эта конструкция используется для формирования словарей, примеры рассмотрим в главе про команду INSERT

Условный оператор WHERE

Ситуация, когда требуется сделать выборку по определенному условию, встречается очень часто. Для этого в операторе SELECT существует параметр WHERE, после которого следует условие для ограничения строк. Если запись удовлетворяет этому условию, то попадает в результат, иначе отбрасывается.

Общая структура запроса с оператором WHERE

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

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

Операторы сравнения служат для сравнения 2 выражений, их результатом может являться ИСТИНА (1), ЛОЖЬ (0) и NULL.

Результат сравнения с NULL является NULL. Исключением является оператор эквивалентности.

Оператор	Описание
=	Оператор равенство
Оператор эквивалентность Аналогичный оператору равенства, с одним лишь исключением: в отличие от него, оператор эквивалентности вернет ИСТИНУ при сравнении NULL NULL
<> или !=	Оператор неравенство
Оператор больше
>=	Оператор больше или равно

IS [NOT] NULL — позволяет узнать равно ли проверяемое значение NULL.

Для примера выведем всех членов семьи, у которых статус в семье не равен NULL:

[NOT] BETWEEN min AND max — позволяет узнать расположено ли проверяемое значение столбца в интервале между min и max.

Выведем все данные о покупках с ценой от 100 до 500 рублей из таблицы Payments:

[NOT] IN — позволяет узнать входит ли проверяемое значение столбца в список определённых значений.

[NOT] LIKE шаблон [ESCAPE символ] — позволяет узнать соответствует ли строка определённому шаблону.

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

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

ESCAPE-символ используется для экранирования трафаретных символов. В случае если вам нужно найти строки, содержащие проценты (а процент — это зарезервированный символ), вы можете использовать ESCAPE-символ.

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

Если бы мы не экранировали трафаретный символ, то в выборку попало бы всё, что начинается на 3.

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

Оператор NOT — меняет значение специального оператора на противоположный
Оператор OR — общее значение выражения истинно, если хотя бы одно из них истинно
Оператор AND — общее значение выражения истинно, если они оба истинны
Оператор XOR — общее значение выражения истинно, если один и только один аргумент является истинным

Выборка сводных данных (из двух и более таблиц)

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

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

id	Name
1	Иванов
2	Петров

id	Name	Phone
1	Иванов	322223
2	Петров	111111

То при простом запросе без условий

Получим примерно следующее:

id	Name	id2	Name2	Phone
1	Иванов	1	Иванов	322223
1	Иванов	2	Петров	111111
2	Петров	1	Иванов	322223
2	Петров	2	Петров	111111

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

Сводные выборки нужны при импорте данных в базу. Сначала вы выделяете из таблиц импорта словари. А потом из таблиц импорта и словарей формируете запрос INSERT . SELECT для записи данных в основную таблицу.

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

Вложенный запрос — это запрос на выборку, который используется внутри инструкции SELECT, INSERT, UPDATE или DELETE или внутри другого вложенного запроса. Подзапрос может быть использован везде, где разрешены выражения.

Пример структуры вложенного запроса

Здесь, SELECT поля_таблиц FROM список_таблиц WHERE конкретное_поле IN (. ) — внешний запрос, а SELECT поле_таблицы FROM таблица — вложенный (внутренний) запрос.

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

Подзапрос может содержать все стандартные инструкции, разрешённые для использования в обычном SQL-запросе: DISTINCT, GROUP BY, LIMIT, ORDER BY, объединения таблиц, запросов и т.д.

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

Подзапрос как скалярный операнд

Скалярный подзапрос — запрос, возвращающий единственное скалярное значение (строку, число и т.д.).

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

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

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

Подзапросы с ANY, IN, ALL

ANY — ключевое слово, которое должно следовать за операцией сравнения (>, , = и т.д.), возвращающее TRUE, если хотя бы одно из значений столбца подзапроса удовлетворяет обозначенному условию.

ALL — ключевое слово, которое должно следовать за операцией сравнения, возвращающее TRUE, если все значения столбца подзапроса удовлетворяет обозначенному условию.

IN — ключевое слово, являющееся псевдонимом ключевому слову ANY с оператором сравнения = (эквивалентность), либо <> ALL для NOT IN. Например, следующие запросы равнозначны:

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

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

Связанным подзапросом является подзапрос, который содержит ссылку на таблицу, которая была объявлена во внешнем запросе. Здесь вложенный запрос ссылается на внешюю таблицу "таблица_1":

Подзапросы как производные таблицы

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

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

Обработка вложенных запросов

Добавление данных, оператор INSERT

Для добавления новых записей в таблицу предназначен оператор INSERT.

Общая структура запроса с оператором INSERT

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

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

Первичный ключ при добавлении новой записи

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

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

В SQL введен механизм его автоматической генерации. Для этого достаточно снабдить первичный ключ good_id атрибутом AUTO_INCREMENT. Тогда при создании новой записи в качестве значения good_id достаточно передать NULL или 0 — поле автоматически получит значение, равное максимальному значению столбца good_id, плюс единица.

Теперь, зная синткасис команд INSERT и SELECT, можем разобраться как создать из исходного набора данных словари и загрузить данные в БД с учетом внешних ключей

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

В структуре БД поле "тип агента" создано как внешний ключ на таблицу типов

Для добавления "типов агентов" в таблицу AgentType мы будем использовать альтернативный синтаксис INSERT . SELECT

Пишем инструкцию SELECT, которая выбирает уникальные записи из таблицы импорта:

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

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

После отладки запроса SELECT перед ним допишем запрос INSERT:

Поле ID можно пропустить, оно автоинкрементное и создастся само (по крайней мере в MsSQL)
Количество вставляемых полей (Title) должно быть равным количеству выбираемых полей (Тип_агента)

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

Заполнение основной таблицы

Тоже сначала пишем SELECT запрос, чтобы проверить те ли данные получаются

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

в поле AgentTypeID мы должны вставить ID соответсвующей записи из таблицы AgentType, поэтому выборка у нас из двух таблиц и чтобы не писать перед каждым полем полные названия таблиц мы присваиваем им алиасы

Т.е. мы выбираем перечисленные поля из таблицы agents_import и добавляем к ним ID агента у которого совпадает название.

При выборке из нескольких таблиц исходные данные перемножаются. Т.е. если мы не заполним перед этой выборкой словарь, то 100 * 0 = пустая выборка .

Если же мы не укажем условие WHERE, то выберутся, к примеру, 100 * 10 = 1000 записей (каждый агент будет в каждой категории). Поэтому важно, чтобы условие WHERE выбирало уникальные значения.

Естественно, количество внешних ключей в таблице может быть больше одного, в таком случае в секции FROM перечисляем все используемые словари и в секции WHERE перечисляем условия для всех таблиц объединив их логическим выражением AND

где алиасы b, c, d - словарные таблицы, а алиас "а" - таблица импорта

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

В каталоге data этого репозитория находится структура БД ( ms.sql ) и файлы для импорта: products_k_import.csv , materials_short_k_import.txt , productmaterial_k_import.xlsx .

После создания пустых таблиц следующим логическим шагом является заполнение их данными и обновление этих данных. Для этого в Transact-SQL предназначена пара инструкций INSERT – UPDATE.

Однако, часто на позднем этапе проектирования возникает необходимость изменить саму схему таблиц. Например, если изменился первичный ключ или тип данных столбца. Чтобы не удалять старые таблицы и не создавать их заново с помочью CREATE TABLE c правильными параметрами, применяется инструкция ALTER TABLE. Применение этих трех конструкций рассматривается ниже.

Заполнение таблиц

Заполнить таблицу данными можно через конструкцию CREATE TABLE, однако более эффективным подходом является разделять создание таблицы и ее заполнение, особенно новичкам в SQL, потому что:

визуально понятнее;
удобнее, если наполнение таблиц поэтапное.

Чтобы получить следующий вид таблицы:

Потребуется создать ее с помощью CREATE TABLE и заполнить, применив инструкцию INSERT. Следующая инструкция добавляет одну строку в уже созданную нами таблицу housemates:

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

INTO – указывающий на таблицу в которую добавляются данные

VALUES – инициализирующий построчный ввод.

В параметрах (в скобках) блока INTO перечисляются столбцы в порядке их следования. Допустима запись без параметров, если порядок следования столбцов при создании таблицы и заполнении ее совпадают. Следующий код идентичен предыдушему:

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

В блоке VALUES производится построчная инициализация в порядке следования столбцов блока INTO. Заполнение строки – это перечисление значений ячеек в скобках. Значения перечисляются через запятую, строки между собой тоже.

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

Обновление таблицы

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

Следующий код присвоит новый почтовый ящик жителю дома с идентификационным номером 103.

Блок SET – это блок изменений. Если нужно обновить значение нескольких ячеек, то они перечисляются через запятую.

Изменение таблицы

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

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

Для всех этих операций предназначена инструкция переопределения ATER TABLE.

Чтобы добавить столбец инструкция ALTER TABLE применяется с предложением ADD. Добавим новый столбец к таблице housemates из прошлого раздела:

Нужно применить к нему предложение ALTER COLUMN внутри ALTER TABLE:

Удаляется столбец применением DROP COLUMN внутри ALTER TABLE:

Первичный или внешний ключ удаляется и добавляется конструкциями ALTER TABLE ADD CONSTRAINT/DROP CONSTRAINT, соответственно:

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

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

Создание новой базы данных MySQL

Новая база данных создается с помощью оператора SQL CREATE DATABASE, за которым следует имя создаваемой базы данных. Для этой цели также используется оператор CREATE SCHEMA. Например, для создания новой базы данных под названием MySampleDB в командной строке mysql нужно ввести следующий запрос:

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

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

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

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

CREATE TABLE имя_таблицы ( определение имени_столбца, определение имени_таблицы …, PRIMARY KEY = (имя_столбца) ) ENGINE = тип_движка;

В определении столбца задается тип данных, может ли столбец быть NULL, AUTO_INCREMENT. Оператор CREATE TABLE также позволяет указать столбец (или группу столбцов) в качестве первичного ключа.
Прежде чем будет создавать таблицу, нужно выбрать базу данных. Это делается с помощью оператора SQL USE:

Создадим таблицу, состоящую из трех столбцов: customer_id , customer_name и customer_address . Столбцы customer_id и customer_name не должны быть пустыми (то есть NOT NULL). customer_id содержит целочисленное значение, которое будет автоматически увеличиваться при добавлении новых строк. Остальные столбцы будут содержать строки длиной до 20 символов. Первичный ключ определяется как customer_id.

Значения NULL и NOT NULL

Если для столбца указано значение NULL, тогда пустые строки будут добавляться в таблицу. И наоборот, если столбец определяется как NOT NULL, тогда пустые строки не будут добавлены.

Первичные ключи

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

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

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

AUTO_INCREMENT

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

AUTO_INCREMENT может быть присвоен только одному столбцу в таблице. И он должен быть проиндексирован (например, объявлен в качестве первичного ключа).

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

Можно запросить у MySQL самое последнее значение AUTO_INCREMENT, используя функцию last_insert_id() следующим образом:

Определение значений по умолчанию при создании таблицы

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

Типы движков баз данных MySQL

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

InnoDB — был представлен вMySQL версии 4.0 и классифицирован как безопасная среда для транзакций.Ее механизм гарантирует, что все транзакции будут завершены на 100%. При этом частично завершенные транзакции (например, в результате отказа сервера или сбоя питания) не будут записаны. Недостатком InnoDB является отсутствие поддержки полнотекстового поиска.
MyISAM — высокопроизводительный движок с поддержкой полнотекстового поиска. Эта производительность и функциональность обеспечивается за счет отсутствия безопасности транзакций.
MEMORY — с точки зрения функционала эквивалентен MyISAM, за исключением того, что все данные хранятся в оперативной памяти, а не на жестком диске. Это обеспечивает высокую скорость обработки. Временный характер данных, сохраняемых в оперативной памяти, делает движок MEMORY более подходящим для временного хранения таблиц.

Движки различных типов могут сочетаться в одной базе данных. Например, некоторые таблицы могут использовать движок InnoDB, а другие — MyISAM. Если во время создания таблицы движок не указывается, то по умолчанию MySQL будет использовать MyISAM.

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

Пожалуйста, опубликуйте ваши комментарии по текущей теме статьи. За комментарии, отклики, лайки, дизлайки, подписки низкий вам поклон!

Пожалуйста, опубликуйте ваши мнения по текущей теме материала. За комментарии, отклики, подписки, дизлайки, лайки низкий вам поклон!

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

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

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

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

SQL — это стандартизированный язык. Тем не менее разработчики вольны соблюдать или не соблюдать стандарты до определенной степени. Это зависит от конкретной реализации. Одной из таких реализаций и является SQLite. В отличие от MySQL или PostgreSQL, это более простая библиотека, созданная для использования локально и без необходимости внешних серверов.

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

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

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

Первой командой меняем каталог в файл, в котором будет база данных, а второй создаем базу данных, активировав библиотеку sqlite3 и выбрав для нее название.

SQLite — гибкая библиотека с расширениями, которые задействуются для файла базы данных. Такие расширения, как .db , .sqlite или .sqlite3 , используются без проблем. Теперь создадим первую таблицу.

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

Начинаем с ввода команды CREATE TABLE , за которой следует название таблицы в table_name . В квадратные скобки заключен необязательный код. Команда IF NOT EXISTS используется для того, чтобы SQLite проверила отсутствие этого названия среди уже имеющихся таблиц, прежде чем создавать новую.

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

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

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

Атрибут size — это то, насколько велики в среднем по размеру представители той или иной расы, как правило они либо маленькие, либо средние. Speed — это максимальное расстояние, которое представители расы способны пробежать за один раунд сражения. Languages — это языки, на которых говорит раса, а age — это средняя продолжительность жизни представителей расы.

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

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

А теперь заполним таблицу. Для этого напишем оператор INSERT с тремя записями:

Посмотрите: мы передаем название таблицы, а также ее атрибуты, куда вставляем значения, за которыми идут фактические, желаемые нами значения. Вот данные, сохраняющиеся в races:

Тут все просто: пишем код для создания таблицы, вставляем значения в текстовый файл и проверяем, нет ли опечаток. Если все правильно, сохраняем файл под любым именем и вводим его в командной строке вот так: .read имя файла . Представьте, что операторы CREATE TABLE и INSERT у нас выше разделены пустой строкой в текстовом файле races.txt :

sqlite>.read races.txt автоматически создаст таблицу и заполнит ее.

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

SQLite поддерживает пять основных типов данных, которые определяются в момент создания новой таблицы: TEXT , INTEGER , REAL , GLOB и NULL . Мы видели выше, что в созданной таблице races содержатся TEXT (текстовые) и INTEGER (целочисленные) типы данных.

Вообще говоря, тип данных TEXT представляет собой текстовую информацию, заключенную в кавычки или отделенную строками. В таблице races атрибут race_name установлен для хранения данных TEXT текстового типа, а строки Aasimar, Kalashtar и Genasi заполняют этот атрибут.

Тип данных INTEGER представляет собой целые числа (положительные или отрицательные) размером от 1 до 8 байтов. Speed — хороший пример того, где использовать целые числа, потому что этот атрибут обозначает количество футов, которые персонаж способен пробежать в раунде сражения.

REAL — это тип данных, содержащий числа длиной более 8 байтов или десятичные и экспоненциальные числа, например 1,5 или 2⁴.

NULL используется для отсутствующей или неизвестной информации. Допустим, нам не удалось найти, на каких языках говорят представители расы Genasi. В этом случае на какое-то время будем сохранять это значение как NULL.

Тип данных Datetime не поддерживается SQLite, но такая информация все равно сохраняется как тип данных TEXT , REAL или INTEGER при условии корректных преобразований. Например, данные timestamp сохраняются как INTEGER .

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

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

Ограничения помогают устанавливать границы в атрибутах, значениях по умолчанию или связях между таблицами. Начнем с последнего. Есть два ограничения, которые играют важную роль в том, что называется нормализацией. Это PRIMARY KEY (первичный ключ) и FOREIGN KEY (внешний ключ).

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

В таблице subraces содержится атрибут race_id, который принимает значения INTEGER (целочисленного типа данных) и ссылается на столбец race_id в таблице races с помощью строки FOREIGN KEY (race_id) REFERENCES races (race_id) .

Благодаря такой ссылке мы сохраняем информацию о подгруппах, сводя к минимуму повторы этой информации в базе данных. Посмотрите на значения в таблице subraces:

Без нормализации нам пришлось бы создавать таблицу типа races_subraces со следующим кодом:

Основные команды SQL не ограничиваются стандартными CREATE , UPDATE и DELETE . Данная статья будет полезна тем, кто хочет освежить свои знания по SQL перед собеседованием на работу.

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

Примечание Вы читаете улучшенную версию некогда выпущенной нами статьи.

Настройка базы данных

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

Читайте также: