как посмотреть скрипт таблицы в sql

Как посмотреть скрипт таблицы в sql

Сообщения: 4432
Благодарности: 993

——-
— Я не разрешаю тебе быть плохой! Потому что плохие люди совершают плохие поступки. А это нехорошо!
(Из наставлений 5 летней девочки своей младшей сестре)

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

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

USE [DBWorks]
GO
/****** Object: Table [dbo].[Table_1] ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DTM] [datetime] NOT NULL,
[NKA] [smallint] NULL,
[LKA] [smallint] NULL,
[UM] [real] NULL,
[AZ] [real] NULL,
[NMS_PT1] [smallint] NULL,
[NMS_VT1] [smallint] NULL,
[NMS_PT2] [smallint] NULL,
[NMS_VT2] [smallint] NULL,
[UP1] [real] NULL,
[UV1] [real] NULL,
[UP2] [real] NULL,
[UV2] [real] NULL,
[RP1] [float] NULL,
[RV1] [float] NULL,
[RP2] [float] NULL,
[RV2] [float] NULL,
[VP1] [float] NULL,
[VV1] [float] NULL,
[VP2] [float] NULL,
[VV2] [float] NULL,
[NBL] [smallint] NULL
) ON [PRIMARY]

Источник

Исследуем базы данных с помощью T-SQL

Как dba и консультант по оптимизации производительности SQL Server в Ambient Consulting, я часто сталкиваюсь с необходимостью анализа узких мест производительности на экземплярах SQL Server, которые вижу первый раз в жизни. Это может быть сложной задачей. Как правило, у большинства компаний нет документации по их базам данных. А если есть, то она устарела, или же её поиск занимает несколько дней.

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

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

Как и с любыми скриптами, сначала проверьте их в тестовом окружении, прежде чем запускать в продакшене. Я бы рекомендовал вам погонять их на тестовых базах MS, таких как AdventureWorks или pubs.

Ну, хватит слов, давайте я покажу скрипты!

Изучаем сервера

Начнём с запросов, предоставляющих информацию о ваших серверах.

Базовая информация

Во-первых, несколько простых @@Функций, которые предоставят нам базовую информацию.

Как долго ваш SQL Server работает после последнего перезапуска? Помните, что системная база данных tempdb пересоздаётся при каждом перезапуске SQL Server. Вот один из методов определения времени последнего перезапуска сервера.

Связанные сервера

Связанные сервера – это соединения, позволяющие SQL Server’у обращаться к другим серверам с данными. Распределённые запросы могут быть запущенны на разных связанных серверах. Полезно знать – является ли ваш сервер баз данных изолированным от других, или он связан с другими серверами.

Список всех баз данных

Во-первых, получим список всех баз данных на сервере. Помните, что на любом сервере есть четыре или пять системных баз данных (master, model, msdb, tempdb и distribution, если вы пользуетесь репликацией). Вы, вероятно, захотите исключить эти базы в следующих запросах. Очень просто увидеть список баз данных в SSMS, но, эти запросы будут нашими «строительными блоками» для более сложных запросов.

Есть несколько путей для получения списка всех БД на T-SQL и ниже вы увидите некоторые из них. Каждый метод возвращает похожий результат, но с некоторыми отличиями.

Последний бэкап?

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

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

Активные пользовательские соединения

Хорошо было бы понимать какие БД сейчас используются, особенно, если вы собираетесь разбираться с проблемами производительности.

Примечание переводчика: это будет работать только в SQL Server 2012 и выше, в предыдущих редакциях, в dmv sys.dm_exec_sessions отсутствовал столбец database_id. Чтобы узнать в каких БД в данный момент работают пользователи, можно воспользоваться sp_who.

Изучаем базы данных

Давайте заглянем поглубже и посмотрим, как мы можем собрать информацию об объектах во всех ваших БД, используя различные представления каталога и dmv. Большинство из запросов, представленных в этом разделе, смотрят «внутрь» только одной БД, поэтому не забывайте выбирать нужную БД в SSMS или с помощью команды use database. Также помните, что вы всегда можете посмотреть в контексте какой БД будет выполнен запрос, с помощью select db_name().

Системная таблица sys.objects одна из ключевых для сбора информации об объектах, составляющих вашу модель данных.

Ниже представлен список типов объектов, информацию о которых мы можем получить (смотрите документацию на sys.objects в MSDN)

Другие представления каталога, такие как sys.tables и sys.views, обращаются к sys.objects и предоставляют информацию о конкретном типе объектов. С этими представлениями, плюс функцией OBJECTPROPERTY, мы можем получить огромное количество информации по каждому из объектов, составляющих нашу схему БД.

Расположение файлов баз данных

Физическое расположение выбранной БД, включая основной файл данных (mdf), и файл журнала транзакций (ldf), могут быть получены с помощью этих запросов.

Таблицы

Конечно, Object Explorer в SSMS показывает полный список таблиц в выбранной БД, но часть информации с помощью GUI получить сложнее, чем с помощью скриптов. Стандарт ANSI предполагает обращение к представлениям INFORMATION_SCHEMA, но они не предоставят информацию об объектах, которые не являются частью стандарта (такие как триггеры, extended procedures и т.д.), поэтому лучше использовать представления каталога SQL Server.

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

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

В SSMS мы можем нажать правой кнопкой мыши на любую таблицу, открыть свойства на вкладке Storage и увидеть количество записей в таблице.

Довольно тяжело собрать вручную эту информацию обо всех таблицах. Опять же, если мы будем писать SELECT COUNT(*) FROM TABLENAME для каждой таблицы, нам придётся очень много печатать.

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

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

sp_msForEachTable

Sp_msforeachtable – это недокументированная функция, которая «проходит» по всем таблицам в БД и выполняет запрос, подставляя вместо ‘?’ имя текущей таблицы. Так же существует похожая функция sp_msforeachdb, работающая на уровне баз данных.

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

Самый быстрый способ получения количества записей – кластерный индекс

Все предыдущие метода использовали COUNT(*), который медленно отрабатывает, если в таблице больше чем 500K записей.

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

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

Работа с кучами – это как работа с плоским файлом, вместо базы данных. Если вы хотите гарантированно получать полное сканирование таблицы при выполнении любого запроса, используйте кучи. Обычно я рекомендую добавлять primary key ко всем таблицам-кучам.

Разбираемся с активностью в таблице

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

Помните, что эта информация из dmv, очищается при каждом перезапуске SQL Server. Чем дольше сервер работает, тем более надёжна статистика. Я чувствую себя намного более уверенно со статистикой, собранной за 30 дней, чем со статистикой, собранной за неделю.

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

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

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

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

В SQL Server, в некоторых случаях, мы можем обновлять данные с использованием представления. Чтобы получить представление «только для чтения», можно использовать SELECT DISTINCT при его создании. Данные «через» представление можно менять только в том случае, если каждой строке представления соответствует только одна строка в «базовой» таблице. Любое представление, не отвечающее этому критерию, т.е. построенное на нескольких таблицах, или с использованием группировок, агрегатных функций и вычислений, будет доступно только для чтения.

Синонимы

Несколько раз в моей карьере я сталкивался с ситуацией, когда не мог понять к какой же таблице обращается запрос. Представьте простой запрос SELECT * FROM Client. Я ищу таблицу под именем Client, но я не могу найти её. Хорошо, думаю я, должно быть это представление, ищу представление с именем Client и всё равно не могу найти. Может быть я ошибся базой данных? В итоге выясняется, что Client – это синоним для покупателей и таблица, на самом деле, называется Customer. Отдел маркетинга хотел обращаться к этой таблице как к Client и из-за этого был создан синоним. К счастью, использование синонимов – это редкость, но разбирательства могут вызвать определённые затруднения, если вы к ним не готовы.

Хранимые процедуры

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

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

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

Функции

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

Триггеры

Триггер – это что-то вроде хранимой процедуры, которая выполняется в ответ на определённые действия с той таблицей, которой этот триггер принадлежит. Например, мы можем создать INSERT, UPDATE и DELETE триггеры.

CHECK-ограничения

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

Углубляемся в модель данных

Ранее, мы использовали скрипты, которые дали нам представление о «верхнем уровне» объектов, составляющих нашу базу данных. Иногда нам нужно получить больше данных о таблице, включая столбцы, их типы данных, какие значения по умолчанию заданы, какие ключи и индексы существуют (или должны существовать) и т.д.

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

Столбцы

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

Значения по умолчанию

Значение по умолчанию – это значение, которое будет сохранено, если никакого значения для столбца не будет задано при вставке. Зачастую, для столбцов хранящих дату ставят get_date(). Также, значения по умолчанию используются для аудита – вставляется system_user для определения учётной записи пользователя, совершившего определённое действие.

Вычисляемые столбцы

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

Столбцы identity

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

Ключи и индексы

Как я писал ранее, наличие первичного ключа и соответствующего индекса у таблицы – это одна из best practice. Ещё одна best practice заключается в том, что внешние ключи так же должны иметь индекс, построенный по столбцам, входящим во внешний ключ. Индексы, построенные «по внешним ключам» отлично подходят для соединения таблиц. Эти индексы так же хорошо сказываются на производительности при удалении записей.

Какие индексы у нас есть?

Скрипт для поиска всех индексов во всех таблицах текущей БД.

Каких индексов не хватает?

На основании ранее исполнявшихся запросов, SQL Server предоставляет информацию об отсутствующих индексах в БД, создание которых может увеличить производительность.

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

Внешние ключи

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

Пропущенные индексы по внешним ключам

Как я уже говорил, желательно иметь индекс, построенный по столбцам, входящим во внешний ключ. Это значительно ускоряет соединения таблиц, которые, обычно, всё равно соединяются по внешнему ключу. Эти индексы так же значительно ускоряют операции удаления. Если такого индекса нет, SQL Server будет производить table scan связанной таблицы, при каждом удалении записи из «первой» таблицы.

Зависимости

Это зависит… Я уверен, вы слышали это выражение раньше. Я рассмотрю три разных метода для «реверс-инжиниринга» зависимостей в БД. Первый метода – использовать хранимую процедуру sp_msdependecies. Второй – системные таблицы, связанные со внешними ключами. Третий метод – использовать CTE.

sp_msdependencies

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

Если мы выведем все зависимости, используя sp_msdependencies, мы получим четыре столбца: Type, ObjName, Owner(Schema), Sequence.

Обратите внимание на номер последовательности (Sequence) – он начинается с 1 и последовательно увеличивается. Sequence – это «порядковый номер» зависимости.

Я несколько раз использовал этот метод, когда мне нужно было выполнить архивирование или удаление на очень большой БД. Если вы знаете зависимости таблицы, значит у вас есть «дорожная карта» — в каком порядке вам нужно архивировать или удалять данные. Начните с таблицы с самым большим значение в столбце Sequence и двигайтесь от него в обратном порядке – от большего к меньшему. Таблицы с одинаковым значением Sequence могут быть удалены одновременно. Этот метод не нарушает ни одного из ограничений внешних ключей и позволяет перенести/удалить записи без временного удаления и перестроения ограничений (constraints).

В SSMS, если вы нажмёте правой кнопкой мыши на имя таблицы, вы сможете выбрать «View Dependencies» и «Объекты, которые зависят от TABLENAME»:

Мы также можем получить эту информацию следующим способом:

Если в SSMS, в окне просмотра зависимостей, выбрать «Объекты которые зависят от TABLENAME», а затем раскрыть все уровни, мы увидим следующее:

Ту же самую информацию вернёт sp_msdependencies.

Так же, в SSMS, мы можем увидеть от каких объектов зависит выбранная таблица.

Следующий запрос, с использованием msdependencies, вернёт ту же самую информацию.

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

Запросы к системным представлениям каталога

Второй метод «реверс-инжиниринга» зависимостей в вашей БД – это запросы к системным представлениям каталога, связанным со внешними ключами.

Использование CTE

Третий метод, для получения иерархии зависимостей – использование рекурсивного CTE.

Заключение

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

Примечание переводчика: все запросы в тексте (за исключением одного, в тексте он отмечен) будут работать на SQL Server 2005 SP3 и в более поздних редакциях. Текст достаточно объёмный, я старался как мог его вычитать и найти свои ошибки (стилистические, синтаксические, смысловые и прочие), но, наверняка что-то не заметил, напишите мне в личку, пожалуйста, если что-то будет резать глаз.

Источник

Просмотр и редактирование таблиц SQL Server в графическом режиме

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

Для этого надо открыть SQL Server Management Studio, найти в разделе «Databases» нужную базу и раскрыть ее. Затем в разделе «Tables» выбрать таблицу и правой клавишей мыши вызвать контекстное меню. В этом меню есть два пункта — «Select Top 1000 Rows» и «Edit Top 200 Rows».

Select Top 1000 Rows, как следует из названия, выводит первые 1000 строк таблицы

а Edit Top 200 Rows открывает для редактирования первые 200 строк таблицы. Это очень удобно, так как таблицу можно быстро пролистать, найти требуемую информацию и изменить ее.

При необходимости дефолтные значения 200\1000 можно изменить. Для этого надо открыть меню «Tools», перейти к пункту «Options»

открыть вкладку «SQL Server Object Explorer» и в разделе «Table and View Options» установить необходимые значения. А если поставить 0, то будет выводиться все содержимое базы без ограничений.

Все вышеописаное применимо ко всем более-менее актуальным версиям, начиная с SQL Server 2008 и заканчивая SQL Server 2016.

Источник

Памятка/шпаргалка по SQL

Доброго времени суток, друзья!

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

Для более полного погружения в SQL рекомендую изучить эти руководства по MySQL и PostgreSQL от Метанита. Они хороши тем, что просты в изучении и позволяют быстро начать работу с названными СУБД.

При обнаружении ошибок, опечаток и неточностей, не стесняйтесь писать мне в личку.

Содержание

Что такое SQL?

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

Почему SQL?

Процесс SQL

При выполнении любой SQL-команды в любой RDBMS (Relational Database Management System — система управления РБД, СУБД, например, PostgreSQL, MySQL, MSSQL, SQLite и др.) система определяет наилучший способ выполнения запроса, а движок SQL определяет способ интерпретации задачи.

В данном процессе участвует несколького компонентов:

Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.

Команды SQL

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

Что такое таблица?

Данные в СУБД хранятся в объектах БД, называемых таблицами (tables). Таблица, как правило, представляет собой коллекцию связанных между собой данных и состоит из определенного количества колонок и строк.

Таблица — это самая распространенная и простая форма хранения данных в РБД. Вот пример таблицы с пользователями (users):

Что такое поле?

Каждая таблица состоит из небольших частей — полей (fields). Полями в таблице users являются userId, userName, age, city и status. Поле — это колонка таблицы, предназначенная для хранения определенной информации о каждой записи в таблице.

Что такое запись или строка?

Запись или строка (record/row) — это любое единичное вхождение (entry), существующее в таблице. В таблице users 5 записей. Проще говоря, запись — это горизонтальное вхождение в таблице.

Что такое колонка?

Что такое нулевое значение?

Ограничения

Ограничения (constraints) — это правила, применяемые к данным. Они используются для ограничения данных, которые могут быть записаны в таблицу. Это обеспечивает точность и достоверность данных в БД.

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

Среди наиболее распространенных ограничений можно назвать следующие:

Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.

Целостность данных

В каждой СУБД существуют следующие категории целостности данных:

Нормализация БД

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

Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.

Синтаксис SQL

Примеры синтаксиса

Типы данных

Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:

Точные числовые

Приблизительные числовые

Дата и время

Строковые символьные

Строковые символьные (юникод)

Бинарные

Смешанные

Операторы

Оператор (operators) — это ключевое слово или символ, которые, в основном, используются в инструкциях WHERE для выполнения каких-либо операций. Они используются как для определения условий, так и для объединения нескольких условий в инструкции.

Арифметические

Операторы сравнения

Логические операторы

Выражения

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

Базовый синтаксис выражения выглядит так:

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

Логические

Логические выражения извлекают данные на основе совпадения с единичным значением.

Предположим, что в таблице users имеются следующие записи:

Выполняем поиск активных пользователей:

Числовые

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

Простой пример использования числового выражения:

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

Выражения для работы с датами

Эти выражения, как правило, возвращают текущую дату и время.

Другие функции для получения текущей даты и времени:

Функции для разбора даты и времени:

Функции для манипулирования датами:

Создание БД

Условие IF NOT EXISTS позволяет избежать получения ошибки при попытке создания БД, которая уже существует.

Название БД должно быть уникальным в пределах СУБД.

Получаем список БД:

Удаление БД

Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.

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

Проверяем, что БД удалена:

Выбор БД

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

Проверяем, что таблица была создана:

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

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

Удаляем таблицу users :

Добавление колонок

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

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

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

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

Заполнение таблицы с помощью другой таблицы

Выборка полей

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

Предложение WHERE

Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки ( » ), а числа, напротив, указываются как есть.

Операторы AND и OR

Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.

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

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

Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:

Обновление полей

Обновим возраст пользователя с именем Igor :

Удаление записей

Удалим неактивных пользователей:

Предложения LIKE и REGEX

LIKE

Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:

% означает 0, 1 или более символов. _ означает точно 1 символ.

Сделаем выборку неактивных пользователей:

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

REGEX

Предложение REGEX позволяет определять регулярное выражение, которому должна соответствовать запись.

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

Сделаем выборку пользователей с именами Igor и Vika :

Предложение TOP / LIMIT / ROWNUM

Данные предложения позволяют извлекать указанное количество или процент записей с начала таблицы. Разные СУБД поддерживают разные предложения.

Сделаем выборку первых трех пользователей:

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

Предложения ORDER BY и GROUP BY

ORDER BY

Предложение ORDER BY используется для сортировки данных по возрастанию ( ASC ) или убыванию ( DESC ). Многие СУБД по умолчанию выполняют сортировку по возрастанию.

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

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

Теперь выполним сортировку по убыванию:

Определим собственный порядок сортировки по убыванию:

GROUP BY

Сгруппируем активных пользователей по городам:

Ключевое слово DISTINCT

Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).

Сделаем выборку городов проживания пользователей:

Соединения

Соединения (joins) используются для комбинации записей двух и более таблиц.

Существуют разные типы объединений:

Предложение UNION

Однако, они могут быть разной длины.

Объединим наши таблицы users и orders :

Предложение UNION ALL

Существует еще два предложения, похожих на UNION :

Синонимы

Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.

Синтаксис синонима таблицы:

Синтаксис синонима колонки:

Пример использования синонимов таблиц:

Пример использования синонимов колонок:

Индексы

Создание индексов

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

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

Синтаксис создания индекса:

Синтаксис создания индекса для одной колонки:

Синтакис создания уникальных индексов (такие индексы используются не только для повышения производительности, но и для обеспечения согласованности данных):

Синтаксис создания индексов для нескольких колонок (композиционный индекс):

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

Для ограничений PRIMARY KEY и UNIQUE автоматически создаются неявные индексы.

Удаление индексов

Для удаления индексов используется инструкция DROP INDEX :

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

К таким ситуациям относится следующее:

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

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

Добавляем в таблицу users новую колонку — пол пользователя:

Удаляем эту колонку:

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

Команда TRUNCATE TABLE используется для очистки таблицы. Ее отличие от DROP TABLE состоит в том, что сохраняется структура таблицы ( DROP TABLE полностью удаляет таблицу и все ее данные).

Очищаем таблицу users :

Проверяем, что users пустая:

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

Представление (view) — это не что иное, как инструкция, записанная в БД под определенным названием. Другими словами, представление — это композиция таблицы в форме предварительно определенного запроса.

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

Представления — это виртутальные таблицы, позволяющие делать следующее:

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

Создаем представление для имен и возраста пользователей:

Получаем данные с помощью представления:

WITH CHECK OPTION

Если условие не удовлетворяется, выбрасывается исключение.

Обновление представления

Представление может быть обновлено при соблюдении следующих условий:

Пример обновления возраста пользователя с именем Igor в представлении:

Обратите внимание: обновление строки в представлении приводит к ее обновлению в базовой таблице.

С помощью команды DELETE можно удалять строки из представления.

Удаляем из представления пользователя, возраст которого составляет 26 лет:

Обратите внимание: удаление строки в представлении приводит к ее удалению в базовой таблице.

Удаление представления

Для удаления представления используется инструкция DROP VIEW :

Удаляем представление usersView :

HAVING

Транзакции

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

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

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

Свойства транзакции

Транзакции имеют 4 стандартных свойства (ACID):

Управление транзакцией

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

Удаляем пользователя, возраст которого составляет 26 лет, и отправляем изменения в БД:

Удаляем пользователя с именем Oleg и отменяем эту операцию:

Контрольные точки создаются с помощью такого синтаксиса:

Возврат к контрольной точке выполняется так:

Делаем выборку пользователей:

Как видим, из таблицы был удален только пользователь с возрастом 26 лет.

Команда SET TRANSACTION используется для инициализации транзакции, т.е. начала ее выполнения. При этом, можно определять некоторые характеристики транзакции. Например, так можно определить уровень доступа транзакции (доступна только для чтения или для записи тоже):

Временные таблицы

Некоторые СУБД поддерживают так называемые временные таблицы (temporary tables). Такие таблицы позволяют хранить и обрабатывать промежуточные результаты с помощью таких же запросов, как и при работе с обычными таблицами.

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

Клонирование таблицы

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

Подзапросы

Подзапрос — это внутренний (вложенный) запрос другого запроса, встроенный (вставленный) с помощью WHERE или других инструкций.

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

Правила использования подзапросов:

Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.

Последовательности

Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.

Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:

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

Источник