Что не является базовой командой sql

Sysadminium

База знаний системного администратора

Базовые команды SQL

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

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

Чтобы создать базу данных используется команда CREATE DATABASE . Например:

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

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

Типы данных, которые можно применять, рассмотрю позже.

Наполнение таблицы строками

Строки вставляются в таблицу с помощью команды SQL INSERT INTO (перечисляем поля)
VALUES (перечисляем значения). При этом можно вставлять по одной строке или по несколько. Вставим три строки в нашу таблицу users:

Как видно из примера выше, мы вначале определяем порядок колонок (fio, company, phone, email), а затем в этом порядке вставляем значения.

При вставке строковых значений, строка всегда берётся в одинарные кавычки.

Просмотр таблицы

Можно вывести определённые колонки указав их вместо звездочки:

Можно вывести определённые строки с помощью WHERE :

В качестве условия можем указать, что значение в определённой колонке должно:

Можем комбинировать эти методы, например:

А ещё можем отсортировать таблицу по какой-нибудь строке:

Условий может быть несколько, например ФИО должно содержать Иван, а телефон должен заканчиваться на 94:

” – означает что значение должно содержать, а не равняться.

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

Комбинировать условия можно с помощью AND и OR.

Изменение строк

Если не указать условие, то обновятся все строки:

Удаление строк

Если не указать блок WHERE, то удалятся все строки! Но очищать таблицу лучше другой командой.

Очистка таблицы

Очистка таблицы осуществляется с помощью команды TRUNCATE . Например:

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

Для удаления используем команду DROP TABLE . Например:

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

А чтобы удалить базу данных выполняем DROP DATABASE . Но предварительно нужно отключиться от удаляемой базы:

Источник

От CREATE до JOIN: введение в SQL + шпаргалка

Львиная доля мировой информации хранится в реляционных базах данных. Чтобы работать с ней, нужно владеть языком SQL-запросов.

Для решения многих стандартных задач не требуется быть SQL-виртуозом, достаточно изучить азы работы с базами:

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

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

* В примерах используется SQL-синтаксис для MySQL 5.6. Запросы, предназначенные для разных СУБД, могут различаться.

Терминология

База данных состоит из таблиц, а таблица – из колонок и строк.

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

Каждая строка, или запись таблицы, – это описание конкретного объекта, например, студента или сотрудника.

Уровень: Новичок

Создание и редактирование таблиц

CREATE

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

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

ALTER

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

DROP и TRUNCATE

Оператор DROP удаляет таблицу из базы целиком:

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

Атрибуты и ограничения

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

Самые распространенные в SQL ограничения целостности (CONSTRAINTS):

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

Первичный ключ, автоматический инкремент, NOT NULL и значение по умолчанию мы уже использовали в примере с собаками.

Решим новую задачу – составление списка президентов:

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

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

Еще одно удобное ограничение в SQL – внешний ключ (FOREIGN KEY). Он позволяет связать поля двух разных таблиц.

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

Теперь в поле department таблицы employees нельзя будет указать произвольный отдел. Он обязательно должен содержаться в таблице departments.

Сохранение и обновление записей

INSERT

Добавить в таблицу новую запись (или даже сразу несколько) очень просто:

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

UPDATE

Оператор UPDATE используется для изменения существующих записей таблицы.

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

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

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

DELETE

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

Уровень: уверенный пользователь

Выборка и фильтрация данных

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

Вот небольшая демо-база, на которой вы можете попрактиковаться:

SELECT

Можно переименовывать поля для вывода:

Ограничение количества результатов:

Агрегатные функции и группировка

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

Агрегатные функции могут работать со всеми записями таблицы разом, а могут и с отдельными группами. Чтобы эти группы сформировать, используйте оператор GROUP BY:

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

Объединение таблиц

Очень часто нужная вам информация хранится в разных таблицах – это обусловлено законами нормализации. Поэтому важно уметь объединять их.

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

Соединение бывает внутреннее (INNER) и внешнее (OUTER).

Внутреннее соединение

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

SQL просмотрит каждую запись из таблицы employees и попытается поставить ей в соответствие каждую запись из таблицы departments. Если это удастся (id отделов совпадают), запись будет включена в результат, иначе – не будет.

Таким образом, вы не увидите Kenny Washington, у которого отдел не указан, а также все отделы, в которых нет сотрудников.

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

Внешнее соединение

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

Например, чтобы увидеть в результате Kenny Washington, потребуется левое внешнее соединение. Слово OUTER можно не указывать – соединение по умолчанию внешнее:

Теперь в результате есть все данные из левой таблицы (employees), даже если для них нет соответствия.

Правое соединение соответственно проигнорирует Кенни, но выведет все пустые отделы:

И наконец, полное внешнее соединение выведет и соответствия, и пустые отделы, и сотрудников без отдела.

Декартово произведение

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

Автосоединение

Кроме того, таблицу можно соединять с самой собой. Это пригодится, чтобы найти босса для каждого сотрудника. Сейчас в поле boss находится идентификатор другого сотрудника, необходимо вывести его имя:

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

Объединение выборок

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

UNION

Объединить штатных и внештатных сотрудников

INTERSECT

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

MINUS

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

Уровень: SQL-мастер

Представления

Views, или представления, в SQL – это SELECT-запрос, который вы можете сохранить для дальнейшего использования. Один раз написали, а потом можете пользоваться полученной таблицей, которая – внимание! – всегда остается актуальной в отличие от результата обычных запросов.

У представлений есть еще одна важная миссия: обеспечение безопасности. Под view вы легко можете скрыть бизнес-логику и архитектуру базы и защитить свое приложение от нежелательных вторжений.

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

Если представление изменяемое, можно использовать при его создании CHECK OPTION для проверки изменений на соответствие некоторому предикату:

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

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

Индексы

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

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

Триггеры

Триггеры в SQL – это процедуры, которые автоматически запускаются при выполнении определенной операции (INSERT/UPDATE/DELETE) – до (BEFORE) или после (AFTER) нее.

Удалить существующий триггер можно с помощью оператора DROP:

Источник

Что не является базовой командой sql

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

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

DDL – Data Definition Language

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

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

DML – Data Manipulation Language

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

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

DCL – Data Control Language

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

TCL – Transaction Control Language

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выведем имена членов семьи, чей статус равен «father» или «mother»:

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

Например, выведем всех людей с фамилией «Quincey»:

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

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

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

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

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

Выведем все полёты, которые были совершены на самолёте «Boeing», но, при этом, вылет был не из Лондона:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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-файла), у которых есть поля название, тип и т.д. (далее по тексту я её называю таблица импорта)

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

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

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

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

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

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

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

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

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

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

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

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

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

Необходимо во-первых, восстановить структуру БД из скрипта, во-вторых, импортировать исходные данные в Excel, в-третьих исправить данные (смотрите на структуру таблиц в БД, где-то надо явно указать тип данных, где-то вырезать лишние данные. ) и в-четвёртых загрузить исправленные данные в БД (сначала просто импорт во временные таблицы, потом разнести SQL-запросами по нужным таблицам)

Источник