SQL "для чайников": что нужно знать начинающим? SQL: универсальный язык для работы с базами данных Язык sql основы

В предыдущих двух статьях данного цикла, опубликованных в № 6 и 7 нашего журнала, мы рассмотрели различные механизмы доступа к данным, включая ADO, BDE и их альтернативы. Теперь мы знаем, как выбрать технологию доступа к данным для той или иной пары «СУБД - средство разработки».

Располагая технологией доступа к данным, можно наконец подумать и о том, каким образом следует манипулировать самими данными и метаданными. Способы манипуляции могут быть специфичными для данной СУБД (например, использование объектов клиентской части этой СУБД для доступа к объектам баз данных) или для данного механизма доступа к данным. Тем не менее существует более или менее универсальный способ манипуляции данными, поддерживаемый почти всеми серверными реляционными СУБД и большинством универсальных механизмов доступа к данным (в том числе при использовании их совместно с настольными СУБД). Этот способ - применение языка SQL (Structured Query Language - язык структурированных запросов). Ниже мы рассмотрим назначение и особенности этого языка, а также изучим, как с его помощью извлекать и суммировать данные, добавлять, удалять и модифицировать записи, защищать данные от несанкционированного доступа, создавать базы данных. Для более подробного изучения SQL мы можем порекомендовать книги Мартина Грабера «Введение в SQL» (М., Лори, 1996) и «SQL. Справочное руководство» (М., Лори, 1997).

Введение

Structured Query Language представляет собой непроцедурный язык, используемый для управления данными реляционных СУБД. Термин «непроцедурный» означает, что на данном языке можно сформулировать, что нужно сделать с данными, но нельзя проинструктировать, как именно это следует сделать. Иными словами, в этом языке отсутствуют алгоритмические конструкции, такие как метки, операторы цикла, условные переходы и др.

Язык SQL был создан в начале 70-х годов в результате исследовательского проекта IBM, целью которого было создание языка манипуляции реляционными данными. Первоначально он назывался SEQUEL (Structured English Query Language), затем - SEQUEL/2, а затем - просто SQL. Официальный стандарт SQL был опубликован ANSI (American National Standards Institute - Национальный институт стандартизации, США) в 1986 году (это наиболее часто используемая ныне реализация SQL). Данный стандарт был расширен в 1989 и 1992 годах, поэтому последний стандарт SQL носит название SQL92. В настоящее время ведется работа над стандартом SQL3, содержащим некоторые объектно-ориентированные расширения.

Существует три уровня соответствия стандарту ANSI - начальный, промежуточный и полный. Многие производители серверных СУБД, такие как IBM, Informix, Microsoft, Oracle и Sybase, применяют собственные реализации SQL, основанные на стандарте ANSI (отвечающие как минимум начальному уровню соответствия стандарту) и содержащие некоторые расширения, специфические для данной СУБД.

Более подробную информацию о соответствии стандарту версии SQL, используемой в конкретной СУБД, можно найти в документации, поставляемой с этой СУБД.

Как работает SQL

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

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

Обратите внимание на то, что SQL сам по себе не является ни СУБД, ни отдельным продуктом. Это язык, применяемый для взаимодействия с СУБД и являющийся в определенном смысле ее неотъемлемой частью.

Data Definition Language (DDL)

Data Definition Language содержит операторы, позволяющие создавать, изменять и уничтожать базы данных и объекты внутри них (таблицы, представления и др.). Эти операторы перечислены в табл. 1.

Таблица 1

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

Data Manipulation Language (DML)

Data Manipulation Language содержит операторы, позволяющие выбирать, добавлять, удалять и модифицировать данные. Обратите внимание на то, что эти операторы не обязаны завершать транзакцию, внутри которой они вызваны. Операторы DML представлены в табл. 2.

Таблица 2

Иногда оператор SELECT относят к отдельной категории, называемой Data Query Language (DQL).

Cursor Control Language (CCL)

Операторы Cursor Control Language используются для определения курсора, подготовки SQL-предложений для выполнения, а также для некоторых других операторов. Операторы CCL представлены в табл. 5.

Таблица 5

Оператор	Описание
	Применяется для определения курсора для запроса
	Применяется для описания плана запроса. Этот оператор представляет собой расширение SQL для Microsoft SQL Server 7.0. Он не обязан выполняться в других СУБД. Например, в случае Oracle следует использовать оператор EXPLAIN PLAN
	Применяется для открытия курсора при получении результатов запроса
	Применяется для получения строки из результатов запроса
	Применяется для закрытия курсора
	Применяется для подготовки оператора SQL для выполнения
	Применяется для выполнения оператора SQL
	Применяется для описания подготовленного запроса

Все операторы SQL имеют вид, показанный на рис. 2 .

Каждый оператор SQL начинается с глагола, представляющего собой ключевое слово, определяющее, что именно делает этот оператор (SELECT, INSERT, DELETE...). В операторе содержатся также предложения, содержащие сведения о том, над какими данными производятся операции. Каждое предложение начинается с ключевого слова, такого как FROM, WHERE и др. Структура предложения зависит от его типа - ряд предложений содержит имена таблиц или полей, некоторые могут содержать дополнительные ключевые слова, константы или выражения.

С помощью чего можно выполнить SQL-операторы

Все современные серверные СУБД (а также многие популярные настольные СУБД) содержат в своем составе утилиты, позволяющие выполнить SQL-предложение и ознакомиться с его результатом. В частности, клиентская часть Oracle содержит в своем составе утилиту SQL Plus, а Microsoft SQL Server - утилиту SQL Query Analyzer. Именно этой утилитой мы воспользуемся для демонстрации возможностей SQL, а в качестве базы данных, над которой мы будем «экспериментировать», возьмем базу данных NorthWind, входящую в комплект поставки Microsoft SQL Server 7.0. В принципе, можно использовать другую базу данных и любую другую утилиту, способную выполнять в этой базе данных SQL-предложения и отображать результаты (или даже написать свою, используя какое-либо средство разработки - Visual Basic, Delphi, C++Builder и др.). Однако на всякий случай рекомендуется сделать резервную копию этой базы данных.

Язык программирования

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

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

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

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

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

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

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

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

Примеры:

Hello, World!:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Строка ‘Hello, World!’ выбирается из встроенной таблицы dual , используемой для запросов, не требующих обращения к настоящим таблицам.

select "Hello, World!" from dual ;

Факториал:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

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

• псевдостолбец level для создания псевдотаблиц t1 и t2 , содержащих числа от 1 до 16,
• агрегатную функцию sum , позволяющую суммировать элементы множества без явного использования цикла,
• и математические функции ln и exp , позволяющие заменить произведение (необходимое для вычисления факториала) на сумму (предоставляемую SQL).

Строка “0! = 1” не войдет в набор строк, полученный в результате, т.к. попытка вычислить ln(0) приводит к исключению.

Числа Фибоначчи:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

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

• формулу Бине и математические функции ROUND , POWER и SQRT для вычисления n-ого числа Фибоначчи;
• псевдостолбец level для создания псевдотаблицы t1, содержащей числа от 1 до 16;
• встроенную функцию SYS_CONNECT_BY_PATH для упорядоченной конкатенации полученных чисел.

SELECT REPLACE (MAX (SYS_CONNECT_BY_PATH (fib || ", " , "/" )), "/" , "" ) || "..." fiblist FROM ( SELECT n , fib , ROW_NUMBER () OVER (ORDER BY n ) r FROM (select n , round ((power ((1 + sqrt (5 )) * 0 . 5 , n ) - power ((1 - sqrt (5 )) * 0 . 5 , n )) / sqrt (5 )) fib from (select level n from dual connect by level <= 16 ) t1 ) t2 ) START WITH r = 1 CONNECT BY PRIOR r = r - 1 ;

Hello, World!:

Пример для версий Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012 , MySQL 5 , PostgreSQL 8.4 , PostgreSQL 9.1 , sqlite 3.7.3

select "Hello, World!" ;

Факториал:

Пример для версий Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012

Используется рекурсивное определение факториала, реализованное через рекурсивный запрос. Каждая строка запроса содержит два числовых поля — n и n!, и каждая следующая строка вычисляется с использованием данных из предыдущей.

Можно вычислить целочисленные факториалы только до 20!. При попытке вычислить 21! возникает ошибка “Arithmetic overflow error”, т.е. происходит переполнение разрядной сетки.

Для вещественных чисел вычисляется факториал 100! (Для этого в примере необходимо заменить bigint на float в 3-ей строке)

Числа Фибоначчи:

Пример для версий Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012

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

Факториал:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Этот пример демонстрирует использование оператора model , доступного начиная с версии Oracle 10g и позволяющего обработку строк запроса как элементов массива. Каждая строка содержит два поля — номер строки n и его факториал f.

select n || "! = " || f factorial from dual model return all rows dimension by ( 0 d ) measures ( 0 f , 1 n ) rules iterate (17 ) ( f [ iteration_number ] = decode (iteration_number , 0 , 1 , f [ iteration_number - 1 ] * iteration_number ), n [ iteration_number ] = iteration_number );

Числа Фибоначчи:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Этот пример демонстрирует использование оператора model , доступного начиная с версии Oracle 10g и позволяющего обработку строк запроса как элементов массива. Каждая строка содержит два поля — само число Фибоначчи и конкатенация всех чисел, меньше или равных ему. Итеративная конкатенация чисел в том же запросе, в котором они генерируются, выполняется проще и быстрее, чем агрегация как отдельное действие.

select max (s ) || ", ..." from (select s from dual model return all rows dimension by ( 0 d ) measures ( cast (" " as varchar2 (200 )) s , 0 f ) rules iterate (16 ) ( f [ iteration_number ] = decode (iteration_number , 0 , 1 , 1 , 1 , f [ iteration_number - 1 ] + f [ iteration_number - 2 ]), s [ iteration_number ] = decode (iteration_number , 0 , to_char (f [ iteration_number ]), s [ iteration_number - 1 ] || ", " || to_char (f [ iteration_number ])) ) );

Факториал:

Пример для версий MySQL 5

select concat (cast (t2 . n as char ), "! = " , cast (exp (sum (log (t1 . n ))) as char )) from ( select @ i : = @ i + 1 AS n from TABLE , (select @ i : = 0 ) as sel1 limit 16 ) t1 , ( select @ j : = @ j + 1 AS n from TABLE , (select @ j : = 0 ) as sel1 limit 16 ) t2 where t1 . n <= t2 . n group by t2 . n

Числа Фибоначчи:

Пример для версий MySQL 5

Замените TABLE на любую таблицу, к которой есть доступ, например, mysql.help_topic .

select concat (group_concat (f separator ", " ), ", ..." ) from (select @ f : = @ i + @ j as f , @ i : = @ j , @ j : = @ f from TABLE , (select @ i : = 1 , @ j : = 0 ) sel1 limit 16 ) t

Hello, World!:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

В этом примере используется анонимный блок PL/SQL, который выводит сообщение в стандартный поток вывода с помощью пакета dbms_output .

begin dbms_output . put_line ("Hello, World!" ); end ;

Факториал:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Этот пример демонстрирует итеративное вычисление факториала средствами PL/SQL.

declare n number : = 0 ; f number : = 1 ; begin while (n <= 16 ) loop dbms_output . put_line (n || "! = " || f ); n : = n + 1 ; f : = f * n ; end loop ; end ;

Числа Фибоначчи:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Этот пример использует итеративное определение чисел Фибоначчи. Уже вычисленные числа хранятся в структуре данных varray — аналоге массива.

declare type vector is varray (16 ) of number ; fib vector : = vector (); i number ; s varchar2 (100 ); begin fib . extend (16 ); fib (1 ) : = 1 ; fib (2 ) : = 1 ; s : = fib (1 ) || ", " || fib (2 ) || ", " ; for i in 3 .. 16 loop fib (i ) : = fib (i - 1 ) + fib (i - 2 ); s : = s || fib (i ) || ", " ; end loop ; dbms_output . put_line (s || "..." ); end ;

Квадратное уравнение:

Пример для версий Oracle 10g SQL , Oracle 11g SQL

Этот пример тестировался в SQL*Plus, TOAD и PL/SQL Developer.

Чистый SQL позволяет вводить переменные в процессе исполнения запроса в виде заменяемых переменных. Для определения такой переменной ее имя (в данном случае A, B и C) следует использовать с амперсандом & перед ним каждый раз, когда нужно сослаться на эту переменную. Когда запрос выполняется, пользователь получает запрос на ввод значений всех заменяемых переменных, использованных в запросе. После ввода значений каждая ссылка на такую переменную заменяется на ее значение, и полученный запрос выполняется.

Существует несколько способов ввести значения для заменяемых переменных. В данном примере первая ссылка на каждую переменную предваряется не одинарным, а двойным амперсандом && . Таким образом значение для каждой переменной вводится только один раз, а все последующие ссылки на нее будут заменены тем же самым значением (при использовании одиночного амперсанда в SQL*Plus значение для каждой ссылки на одну и ту же переменную приходится вводить отдельно). В PL/SQL Developer ссылки на все переменные должны предваряться одиночным знаком & , иначе будет возникать ошибка ORA-01008 “Not all variables bound”.

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

Сам запрос состоит из четырех разных запросов. Каждый запрос возвращает строку, содержащую результат вычислений, в одном из случаев (A=0, D=0, D>0 и D<0) и ничего — в трех остальных случаях. Результаты всех четырех запросов объединяются, чтобы получить окончательный результат.

alter session set NLS_NUMERIC_CHARACTERS = ". " ; select "Not a quadratic equation." ans from dual where && A = 0 union select "x = " || to_char (-&& B / 2 /& A ) from dual where & A != 0 and & B *& B - 4 *& A *&& C = 0 union select "x1 = " || to_char ((-& B + sqrt (& B *& B - 4 *& A *& C )) / 2 /& A ) || ", x2 = " || to_char (-& B - sqrt (& B *& B - 4 *& A *& C )) / 2 /& A from dual where & A != 0 and & B *& B - 4 *& A *& C > 0 union select "x1 = (" || to_char (-& B / 2 /& A ) || "," || to_char (sqrt (-& B *& B + 4 *& A *& C ) / 2 /& A ) || "), " || "x2 = (" || to_char (-& B / 2 /& A ) || "," || to_char (- sqrt (-& B *& B + 4 *& A *& C ) / 2 /& A ) || ")" from dual where & A != 0 and & B *& B - 4 *& A *& C < 0 ;

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

Приветствую вас на моем блоге сайт. Сегодня поговорим про sql запросы для начинающих. У некоторых вебмастеров может возникнуть вопрос. Зачем изучать sql? Разве нельзя обойтись ?

Оказывается, что для создания профессионального интернет-проекта этого будет недостаточно. Sql используется чтобы работать с БД и создания приложений для Вордпресс. Рассмотрим, как использовать запросы подробнее.

Что это такое

Sql - язык структурированных запросов. Создан для определения типа данных, предоставления доступа к ним и обработке информации за короткие промежутки времени. Он описывает компоненты или какие-то результаты, которые вы хотите видеть на интернет-проекте.

Если говорить по-простому, то этот язык программирования позволяет добавлять, изменять, искать и отображать информацию в БД. Популярность mysql связана с тем, что он используется для создания динамических интернет-проектов, основа которых составляет база данных. Поэтому для разработки функционального блога вам необходимо выучить этот язык.

Что может делать

Язык sql позволяет:

• создавать таблицы;
• изменять получать и хранить разные данные;
• объединять информацию в блоки;
• защитить данные;
• создавать запросы в access.

Важно! Разобравшись с sql вы сможете писать приложения для Вордпресс любой сложности.

Какая структура

БД состоит из таблиц, которые можно представить в виде Эксель файла.

У нее имеется имя, колонки и ряд с какой-то информацией. Создавать подобные таблицы можно при помощи sql запросов.

Что нужно знать

Основные моменты при изучении Sql

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

Create database ‘bazaname’

В кавычках пишем имя БД на латинице. Старайтесь придумать для нее понятное имя. Не создавайте базу типа «111», «www» и тому подобное.

После создания БД устанавливаем :

SET NAMES ‘utf-8’

Это нужно чтобы контент на сайте правильно отображаться.

Теперь создаем таблицу:

CREATE TABLE ‘bazaname’ . ‘table’ (

id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,

log VARCHAR(10),

pass VARCHAR(10),

date DATE

Во второй строке мы прописали три атрибута. Посмотрим, что они означают:

• Атрибут NOT NULL означает, что ячейка не будет пустой (поле обязательное для заполнения);
• Значение AUTO_INCREMENT — автозаполнение;
• PRIMARY KEY — первичный ключ.

Как добавить информацию

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

INSERT INTO ‘table’

(login , pass , date) VALUES

(‘Vasa’, ‘87654321’, ‘2017-06-21 18:38:44’);

В скобках указываем название столбцов, а в следующей - значения.

Важно! Соблюдайте последовательность названий и значений столбцов.

Как обновить информацию

Для этого используется команда UPDATE. Посмотрим, как изменить пароль для конкретного пользователя. Пишем такие строки кода:

UPDATE ‘table’ SET pass = ‘12345678’ WHERE id = ‘1’

Теперь поменяйте пароль ‘12345678’. Изменения происходят в строке с «id»=1. Если не писать команду WHERE - поменяются все строки, а не конкретная.

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

Как удалить запись

Если вы написали что-то не так, исправьте это при помощи команды DELETE. Работает так же, как и UPDATE. Пишем такой код:

DELETE FROM ‘table’ WHERE id = ‘1’

Выборка информации

Для извлечения значений из БД используется команда SELECT. Пишем такой код:

SELECT * FROM ‘table’ WHERE id = ‘1’

В данном примере в таблице выбираем все имеющиеся поля. Это происходит если прописать в команде звездочку «*». Если нужно выбрать какое-то выборочное значение пишем так:

SELECT log , pass FROM table WHERE id = ‘1’

Необходимо отметить, что умения работать с базами данных будет недостаточно. Для создания профессионального интернет-проекта придется научиться добавлять на страницы данные из БД. Для этого ознакомьтесь с языком веб-программирования php. В этом вам поможет классный курс Михаила Русакова .

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

Происходит при помощи запроса DROP. Для этого напишем такие строки:

DROP TABLE table;

Вывод записи из таблицы по определенному условию

Рассмотрим такой код:

SELECT id, countri, city FROM table WHERE people>150000000

Он отобразит записи стран где населения больше ста пятидесяти миллионов.

Объединение

Связать вместе несколько таблиц возможно используя Join. Как это работает посмотрите подробнее в этом видео:

PHP и MySQL

Еще раз хочу подчеркнуть, что запросы при создании интернет-проекта - это обычное дело. Чтобы их использовать в php-документах выполните такой алгоритм действий:

• Соединяемся с БД при помощи команды mysql_connect();
• Используя mysql_select_db() выбираем нужную БД;
• Обрабатываем запрос при помощи mysql_fetch_array();
• Закрываем соединение командой mysql_close().

Важно! Работать с БД не сложно. Главное - правильно написать запрос.

Начинающие вебмастера подумают. А что почитать по этой теме? Хотелось бы порекомендовать книгу Мартина Грабера «SQL для простых смертных ». Она написана так, что новичкам все будет понятно. Используйте ее в качестве настольной книги.

Но это теория. Как же обстоит дело на практике? В действительности интернет-проект нужно не только создать, но еще и вывести в ТОП Гугла и Яндекса. В этом вас поможет видеокурс «Создание и раскрутка сайта ».

Видео инструкция

Остались еще вопросы? Посмотрите подробнее онлайн видео.

Вывод

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