Язык sql основы. SQL программирование

Добро пожаловать в область разработки баз данных, выполняемой с помощью стандартного языка запросов SQL. В системах управления базами данных (СУБД) имеется много инструментов, работающих на самых разных аппаратных платформах.

Основы реляционных баз данных

Основы SQL

Компоненты SQL

В этой главе… | Создание баз данных | Обработка данных | Защита баз данных | SQL - это язык, специально разработанный, чтобы создавать и поддерживать данные в реляционных базах. И хотя компании, поставляющие системы для управления такими базами, предлагают свои реализации SQL, развитие самого языка определяется и контролируется стандартом ISO/ANSI.

Создание и поддержка простой базы данных

В этой главе… | Создание, изменение и удаление таблицы из базы данных с помощью инструмента RAD. | Создание, изменение и удаление таблицы из базы данных с помощью SQL. | Перенос базы данных в другую СУБД.

Создание многотабличной реляционной базы данных

Манипуляции данными из базы

Определение значений

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

Сложные выражения со значением

В этой главе… | Использование условных выражений case | Преобразование элемента данных из одного типа данных в другой | Экономия времени ввода данных с помощью выражений со значением типа запись | В главе 2 SQL был назван подъязыком данных.

"Пристрелка" к нужным данным

Реляционные операторы

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

Использование вложенных запросов

В этой главе… | Извлечение данных из множества таблиц с помощью одного оператора SQL | Поиск элементов данных путем сравнения значения из одной таблицы с набором значений из другой | Поиск элементов данных путем сравнения значения из одной таблицы с выбранным с помощью оператора select единственным значением из другой

Рекурсивные запросы

Обеспечение безопасности базы данных

Защита данных

В этой главе… | Как избежать повреждения базы данных | Проблемы, вызванные одновременными операциями | Решение этих проблем с помощью механизмов SQL | Задание требуемого уровня защиты с помощью команды set transaction

Использование SQL в приложениях

В этой главе… | SQL в приложении | Совместное использование SQL с процедурными языками | Как избежать несовместимости | Код SQL, встроенный в процедурный код | Вызов модулей SQL из процедурного кода | Вызов SQL из RAD-инструмента | В предыдущих главах мы в основном рассматривали SQL-команды в отдельности, т.е. формулировалась задача обработки данных, и под нее создавался SQL-запрос.

ODBC и JDBC

В этой главе… | Определение ODBC | Описание частей ODBC | Использование ODBC в среде клиент/сервер | Использование ODBC в Internet | Использование ODBC в локальных сетях | Использование JDBC | С каждым годом компьютеры одной организации или нескольких различных организаций все чаще соединяются друг с другом. Поэтому возникает необходимость в налаживании совместного доступа к базам данных по сети.

SQL:2003 и XML

В этой главе… | Использование SQL с XML | XML, базы данных и Internet | Одной из самых существенных новых функциональных возможностей языка SQL:2003 является поддержка файлов XML (extensible Markup Language - расширяемый язык разметки), которые все больше становятся универсальным стандартом обмена данными между разнородными платформами.

Курсоры

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

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

Язык был сотворен в 1970х годах под заглавием “SEQUEL” для системы управления базами данных (СУБД) System R. Позже он был переименован в “SQL” во избежание инцендента торговых марок. В 1979 году SQL был в первый раз размещен в облике платного продукта Oracle V2.

1-ый официальный стереотип языка был принят ANSI в 1986 году и ISO - в 1987. С тех времен были сделаны ещё некоторое количество версий эталона, кое-какие из их повторяли прошлые с малозначительными вариантами, иные воспринимали свежие немаловажные черты.

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

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

SQL произведено из четырех отдельных частей:

язык определения данных (DDL) применяется для определения структур данных, хранящихся в основе данных. Операторы DDL дают возможность делать, менять и удалять отдельные объекты в БД. Допускаемые типы объектов находятся в зависимости от применяемой СУБД и как правило включают базы данных, юзеров, таблицы и ряд больше маленьких запасных объектов, к примеру, роли и индексы.
язык манипуляции данными (DML) применяется для извлечения и конфигурации данных в БД. Операторы DML дают возможность извлекать, вставлять, менять и удалять данные в таблицах. Временами операторы select извлечения данных не рассматриваются как часть DML, потому что они не изменяют положение данных. Все операторы DML одевают декларативный нрав.
язык определения доступа к сведениям (DCL) применяется для контроля доступа к сведениям в БД. Операторы DCL используются к привилегиям и дают возможность выдавать и отнимать права на использование конкретных операторов DDL и DML к конкретным объектам БД.
язык управления транзакциями (TCL) применяется для контроля обработки транзакций в БД. Как правило операторы TCL включают commit для доказательства перемен, изготовленных в ходе транзакции, rollback для их отмены и savepoint для разбиения транзакции на некоторое количество наименьших частей.

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

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

Что Вам потребуется?

SQL (Structured Query Language) язык специально разработанный для взаимодействия с системами управления баз данных, таких как MySQL, Oracle, Sqlite и прочие... Для выполнения SQL запросов в этой статье я советую Вам установить MySQL на локальный компьютер. Также я рекомендую использовать phpMyAdmin в качестве визуального интерфейса.

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

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

CREATE DATABASE: создание базы данных

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

Для начала, откройте MySQL консоль и залогиньтесь. Для WAMP пароль по умолчанию пустой. То есть ничего:). Для MAMP - "root". Для Денвера необходимо уточнить.

После логина введите следующую строку и нажмите Enter:

CREATE DATABASE my_first_db;

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

Также команды в SQL чувствительны к регистру. Пишем их большими буквами.

Опци онально: Character Set и Collation

Если Вы хотите установить character set (набор символов ) и collation (сравнение ) можно написать следующую команду:

CREATE DATABASE my_first_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Находится список наборов символов, которые поддерживаются в MySQL.

SHOW DATABASES: выводит список всех БД

Эта команда используется для вывода всех имеющихся БД.

DROP DATABASE: удаление БД

Вы можете удалить существующую БД с помощью этого запроса.

Будьте осторожны с этой командой, так как она выполняется без предупреждения. Если в Вашей БД есть данные, они будут все удалены.

USE: Выбор БД

Технически это не запрос, а оператор и он не требует точки с запятой в конце.

Он сообщает MySQL выбрать БД для работы по умолчанию для текущей сессии. Теперь мы готовы создавать таблицы и делать прочие вещи с БД.

Что же такое таблица в БД?

Вы можете представить таблицу в БД в виде Excel файла.

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

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

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

Следующий запрос создаст таблицу с 2-мя колонками.

CREATE TABLE users (username VARCHAR(20), create_date DATE);

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

Первая строка простая. Мы просто создаем таблицу с названием "users ". Далее в скобках, через запятую, идет список всех колонок. После каждого названия колонки у нас идут типы информации, такие как VARCHAR или DATE.

VARCHAR(20) означает, что колонка имеет тип строки и может быть максимум 20 символов в длину. DATE также тип информации, который используется для хранения дат в таком формате: "ГГГГ - ММ-ДД ".

PRIMARY KEY ( первичный клю ч)

Перед тем как мы выполним следующий запрос, мы также должны включить колонку для "user_id ", которая будет нашим первичным ключом. Вы можете воспринимать PRIMARY KEY как информацию, которая используется для идентифицирования каждого ряда таблицы.

CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20), create_date DATE);

INT делает 32 битный целый тип (например, числа). AUTO_INCREMENT автоматически генерирует новое значение ID каждый раз, когда мы добавляем новые ряды информации. Это не обязательно, но делает весь процесс проще.

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

Давайте выполним запрос:

SHOW TABLES: показать все таблицы

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

EXPLAIN: Показать структуру таблиц

Для показа структуры существующей таблицы Вы можете пользоваться этим запросом.

Колонки отображаются со всеми свойствами.

DROP TABLE: удалить таблицу

Также как и DROP DATABASES, этот запрос удаляет таблицу и ее содержание без предупреждения.

ALTER TABLE: изменить таблицу

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

(если Вы удалили таблицу в прошлом шаге, создайте ее снова для тестов)

ДОБАВЛЕНИЕ КОЛОНКИ

ALTER TABLE users ADD email VARCHAR(100) AFTER username;

Благодаря хорошей читабельности SQL, я думаю, что нет смысла ее подробно объяснять. Мы добавляем новую колонку "email " после "username ".

УДАЛЕНИЕ КОЛОНКИ

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

Восстановите только что удаленную колонку для дальнейших экспериментов.

ВНЕСЕНИЕ ИЗМЕНЕНИЯ В КОЛОНКУ

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

Этот запрос переименовал колонку пользователь в "user_name " и изменил ее тип с VARCHAR(20) на VARCHAR(30). Такое изменение не должны изменить данные в таблице.

INSERT: Добавление информации в таблицу

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

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

Заметьте, что первое значение NULL для поля PRIMARY KEY под названием "user_id ". Мы делаем это для того, чтобы ID было сгенерировано автоматически, так как колонка имеет свойство AUTO_INCREMENT. Когда информация добавляется первый раз ID будет 1. Следующий ряд - 2, и так далее...

АЛЬТЕРНАТИВНЫЙ ВАРИАНТ

Есть еще один вариант запроса для добавления рядов.

В этот раз мы используем ключевое слово SET вместо VALUES, и у него нет скобок. Есть несколько нюансов:

Колонку можно пропустить. К примеру, мы не присвоили значение для "user_id ", которое по умолчанию получит свое AUTO_INCREMENT значение. Если Вы пропустите колонку с типом VARCHAR, тогда будет добавлено пустая строка.

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

АЛЬТЕРНАТИВНЫЙ ВАРИАНТ 2

Вот еще вариант.

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

LAST_INSERT_ID()

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

NOW()

Теперь настало время показать, как Вы можете использовать функцию MySQL в запросах.

Функция NOW() выводит текущую дату. Так что Вы можете использовать ее для автоматического установления даты колонки на текущую при вставке нового ряда.

Заметьте, что мы получили 1 предупреждение, но не обращайте на него внимания. Причина этому то, что NOW() также служит для вывода временной информации.

SELECT: Чтение данных из таблицы

Если мы добавляем информацию в таблицу значит логично было бы научиться ее оттуда считывать. Именно в этом нам и поможет запрос SELECT.

Ниже представлен самый простой возможный запрос SELECT для чтения таблицы.

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

Условие WHERE

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

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

Заметьте, что для равенства использоваться один знак равно (=), а не два, как в программировании.

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

AND или OR могут быть использованы для объединения условий:

Заметьте, что числовые значения не должны находиться в кавычках.

IN()

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

LIKE

Позволяет делать "wildcard" запросы

Значок % используется в качестве "wildcard". То есть на его месте может быть что-угодно.

Условие ORDER BY

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

Порядок по умолчанию ASC (от меньшегок большему). Для обратного используется DESC.

LIMIT ... OFFSET ...

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

LIMIT 2 берет только 2 первых результата. LIMIT 1 OFFSET 2 получает 1 результат после первых 2-х. LIMIT 2, 1 означает тоже самое (только обратите внимание сначала идет offset а потом limit ).

UPDATE: Внести изменения в информацию в таблице

Этот запрос используется для изменения информации в таблице.

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

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

DELETE: Удаление информации из таблицы

Также как и UPDATE, этот запрос используется с WHERE:

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

DELETE FROM users;

Но лучше использовать TRUNCATE

Кроме удаления этот запрос также сбрасывает значения AUTO_INCREMENT и при добавлении рядов снова, отсчет начнется с нуля. DELETE такого не делает и отсчет продолжается.

Отключение Строчных Значений и Специальных Слов

Строчные значения

Некоторые символы необходимо отключать ( escape), или же могут быть проблемы.

Для этого используется задний слеш (\).

Специальные слова

Поскольку в MySQL есть много специальных слов ( SELECT или UPDATE), чтобы избежать ошибок при их использовании необходимо использовать кавычки. Но не обычные кавычки, а вот такие (`).

То есть Вам необходимо будет добавить колонку с именем " delete", это необходимо сделать так:

Заключение

Спасибо, что дочитали до конца. Надеюсь, Вам эта статья была полезна. Это еще не конец! Будет продолжение:).

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

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

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

Читаемость и другие аспекты

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

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

use adventureworks2014 go select emp.businessentityid empid,psn.firstname,psn.lastname,emp.sickleavehours sickleave,emp.nationalidnumber natid,emp.JobTitle from humanresources.employee emp inner join person.person psn on emp.businessentityid=psn.businessentityid where emp.jobtitle="production technician - wc60" or emp.jobtitle="production technician - wc50" order by emp.JobTitle desc,EmpID asc

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

USE ADVENTUREWORKS2014 GO SELECT EMP.BUSINESSENTITYID EMPID,PSN.FIRSTNAME,PSN.LASTNAME,EMP.SICKLEAVEHOURS SICKLEAVE,EMP.NATIONALIDNUMBER NATID,EMP.JOBTITLE FROM HUMANRESOURCES.EMPLOYEE EMP INNER JOIN PERSON.PERSON PSN ON EMP.BUSINESSENTITYID=PSN.BUSINESSENTITYID WHERE EMP.JOBTITLE="PRODUCTION TECHNICIAN - WC60" OR EMP.JOBTITLE="PRODUCTION TECHNICIAN - WC50" ORDER BY EMP.JOBTITLE DESC,EMPID ASC

Это вполне допустимо. Но невозможно прочесть. Представьте, что вам нужно просмотреть скрипт, содержащий сотни строк такого кода.

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

/* Извлекаем данные технических сотрудников WC60 и WC50. */ USE AdventureWorks2014; GO SELECT emp.BusinessEntityID AS EmpID, psn.FirstName, psn.LastName, emp.SickLeaveHours AS SickLeave, emp.NationalIDNumber AS NatID, emp.JobTitle FROM HumanResources.Employee AS emp INNER JOIN Person.Person AS psn ON emp.BusinessEntityID = psn.BusinessEntityID WHERE (emp.JobTitle = "Production Technician - WC60") OR (emp.JobTitle = "Production Technician - WC50") ORDER BY emp.JobTitle DESC, EmpID ASC;

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

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

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

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

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

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

Лучшей практикой считается добавление точки с запятой в конце операторов. Хотя в большинстве случаев для SQL Server она не обязательна, в Microsoft предупреждают, что точка с запятой нужна. Это уже стало частью стандартов ANSI .

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

Наша задача состоит в том, чтобы разработать способ последовательного форматирования и представления кода T-SQL , сделать его читаемым, понятным и поддерживаемым.

Написание правильного кода

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

Часто встречающийся пример устаревшего кода — это объединение, основанное на стандарте SQL-92 , в котором условие объединения определено в выражении WHERE , как показано в следующем примере:

SELECT emp.BusinessEntityID AS EmpID, psn.FirstName, psn.LastName, emp.NationalIDNumber AS NatID FROM HumanResources.Employee emp, Person.Person psn WHERE emp.BusinessEntityID = psn.BusinessEntityID AND emp.JobTitle = "Production Technician - WC60";

Хотя SQL Server по-прежнему поддерживает такой подход, но все равно необходимо придерживаться новой модели, которая должна включать условие объединения в выражении FROM :

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

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

Другой пример устаревшего кода, когда оператор T-SQL включает выражение TOP . В прошлом мы указывали числовое выражение без круглых скобок, как в этом примере:

SELECT TOP 10 Title, FirstName, LastName FROM Person.Person; Хотя это все еще работает в SQL Server, правильный синтаксис теперь включает в себя круглые скобки: SELECT TOP(10) Title, FirstName, LastName FROM Person.Person;

Команда, занимающаяся базой данных, должна активно работать над заменой устаревшего кода. Многие T-SQL и ANSI SQL-элементы уже устарели или могут устареть в будущем. С учетом того, как быстро меняются стандарты, скорее всего, у вас есть устаревшие элементы в коде. Например, в SQL Server 2016 параметр SET ROWCOUNT устарел для операторов TSQL INSERT , UPDATE и DELETE . Так же есть типы данных text , ntext и image . Даже такие операторы, как CREATE DEFAULT и DROP DEFAULT когда-нибудь устареют.

Когда вы определяете стандарты кодирования, не забудьте указать, как поступать с устаревшими элементами. Легко сказать, что разработчики должны их избегать, но этого тяжело добиться. Поэтому нужно объяснить, когда и как их следует удалять. К счастью, Microsoft предоставляет сведения о том, что устаревает с выходом каждой новой версии SQL Server . Вы найдете список этих элементов в разделе Deprecated Database Engine Features in SQL Server 2016 .

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

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

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

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

SELECT EmpID = emp.BusinessEntityID, psn.FirstName, psn.LastName, NatID = emp.NationalIDNumber FROM HumanResources.Employee emp INNER JOIN Person.Person psn ON emp.BusinessEntityID = psn.BusinessEntityID WHERE emp.JobTitle = "Production Technician - WC60";

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

SELECT emp.BusinessEntityID AS EmpID, psn.FirstName, psn.LastName, emp.NationalIDNumber AS NatID FROM HumanResources.Employee emp INNER JOIN Person.Person psn ON emp.BusinessEntityID = psn.BusinessEntityID WHERE emp.JobTitle = "Production Technician - WC60";

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

Еще один вопрос, который стоит рассмотреть, использование недокументированных хранимых процедур. Например, следующий оператор SELECT используется хранимой процедурой sp_mstablespace для возврата количества строк и объема дискового пространства, используемого таблицей Person . Пример TSQL exec :

EXECUTE sp_mstablespace "person.person";

Хранимые процедуры прекрасно работают и могут быть очень удобны. Но нет никакого способа узнать, когда Microsoft изменит или выведет их полностью. Созданный вами код может быть сломан, а вы даже не узнаете об этом.

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

Несоответствие функций

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

Например, функция ISNUMERIC печально известна тем, что возвращает непредсказуемые результаты, что продемонстрировано в приведенном ниже примере:

DECLARE @a TABLE(ColA VARCHAR(10)); INSERT INTO @a VALUES ("abc"), ("123"), ("$456"), ("22:35:27"); SELECT ColA, CASE WHEN ISNUMERIC(ColA) = 1 THEN CAST(ColA AS INT) END AS TestResults FROM @a;

Мы создаем переменную таблицы TSQL и заполняем ее разными типами значений, которые передаются в виде строк. Затем используем функцию ISNUMERIC для проверки, является ли значение числовым. Если это так (функция возвращает 1 ), пытаемся преобразовать значение в тип данных INT . Но в данном случае, когда ядро базы данных достигает значения $456 , оно сбрасывается и возвращается сообщение об ошибке:

Conversion failed when converting the varchar value "$456" to data type int.

Проблема заключается в том, что функция ISNUMERIC иногда вызывает числовое значение, которое не может быть преобразовано в числовой тип данных, как для $456 . Она даже интерпретирует такие значения, как 7e9 и $. , как числовые. Лучшим решением данной проблемы является использование функции TRY_CONVERT :

DECLARE @a TABLE(ColA VARCHAR(10)); INSERT INTO @a VALUES ("abc"), ("123"), ("$456"), ("22:35:27"); SELECT ColA, CASE WHEN TRY_CONVERT(int, ColA) IS NOT NULL THEN CAST(colA AS INT) END AS TestResults FROM @a;

Преобразование данных является довольно сложным разделом в SQL Server , поэтому вы должны быть внимательны.

При работе с системными функциями вам придется иметь дело с различными функциями, которые могут использоваться для выполнения аналогичных операций или могут возвращать похожие типы информации. Например, можно использовать функцию CAST или CONVERT для преобразования типа данных значения. Функция CAST является частью стандартов ANSI , но имеет ограниченные возможности.

Функция TSQL CONVERT не предусмотрена стандартами, но она предоставляет ряд параметров для форматирования значений даты и времени. Хотя во многих случаях лучше оставить форматирование на уровне приложений. Независимо от этого нужно будет принять решение, какие из функций использовать, и насколько важно для вас соответствие кода стандартам ANSI .

Другим примером взаимозаменяемых функций являются COUNT и EXISTS , когда они используются для подтверждения существования определенных данных. Например, следующий оператор IF проверяет, содержит ли таблица Person строки, имеющие значение EM в столбце PersonType :

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

Неправильное использование функции — это не всегда проблема функции. Например, в зависимости от ситуации использование функции SCOPE_IDENTITY() выдает более точную информацию, чем системная переменная @@IDENTITY . В обоих случаях возвращается последнее значение идентификатора, сгенерированное для таблицы в текущей сессии. Но функция SCOPE_IDENTITY() применяется только к определенной области, а переменная @@ IDENTITY этого не делает, что может влиять на правильность возвращаемого значения. Дополнительные сведения об этой проблеме вы найдете в разделе документации SQL Server SCOPE_IDENTITY (Transact-SQL) .

Использование системных функций TSQL не должно быть произвольным. Необходимо тщательно взвесить, какие из них задействовать.

Переменные и параметры

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

DECLARE @a INT, @b VARCHAR(25), @c VARCHAR(2), @d VARCHAR, @e MONEY; SET @a = 25; SET @b = "twenty-five"; SELECT @c = "EM", @e = 25; SELECT @a AS "@a", @c AS "@c", @d AS "@d", @e AS "@e", @f AS "@f";

В этом коротком наборе операторов T-SQL нам удалось зафиксировать ряд ошибок:

Мы объявляем переменную @b и присваиваем ей значение, но никогда не используем ее в операторе SELECT ;
Мы объявляем @c с типом данных VARCHAR(2) , а не с типом CHAR(2) ;
Мы объявляем @d как VARCHAR , без указания длины, и не присваиваем переменной значение. Затем мы используем переменную в операторе SELECT ;
Мы используем @f в выражении SELECT , хотя не объявили ее и не присвоили ей значение.

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

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

1. Что такое База Данных

База данных (БД) - упорядоченный набор логически взаимосвязанных данных, используемых совместно, и которые хранятся в одном месте. Если коротко, то простейшая БД это обычная таблица со строками и столбцами в которой хранится разного рода информация (примером может служить таблица в Excel ). Так, часто, с БД нераздельно связывают Системы управления базами данных (СУБД) , которые предоставляют функционал для работы с БД . Язык SQL как раз и является частью СУБД , которая осуществляет управление информацией в БД . Мы будем считать БД набором обычных таблиц, которые хранятся в отдельных файлах.

2. Что такое SQL

Итак, переходим к SQL .

SQL - простой язык программирования, который имеет немного команд и которой может научиться любой желающий. Расшифровывается как Structured Query Language - язык структурированных запросов, который был разработан для работы с БД , а именно, чтобы получать /добавлять /изменять данные, иметь возможность обрабатывать большие массивы информации и быстро получать структурированную и сгруппированную информацию. Есть много вариантов языка SQL , но у них всех основные команды почти одинаковы. Также существует и много СУБД , но основными из них являются: Microsoft Access, Microsoft SQL Server, MySQL, Oracle SQL, IBM DB2 SQL, PostgreSQL та Sybase Adaptive Server SQL . Чтобы работать с SQL кодом, нам понадобится одна из вышеперечисленных СУБД . Для обучения мы будем использовать СУБД Microsoft Access .

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