Основы языка SQL

 

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

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

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

 

                               Состав языка SQL

Язык SQLпредназначен для манипулированиями данными в реляционных БД. Определение структуры БД и для управлениями правами доступа к данным в многопользовательской среде. По этому в язык SQL в качестве составных частей входят: язык маним\пулированиями данными (Data Manipulation Language, DML) и язык определения данных (Data Definition Language, DDL). Следует отметить, что это не отдельные языки, а различные команды одного языка. Такое деление проведено только лишь с точки зрения различного функционального назначения этих команд. Язык манипулирования данными используется для управления данными в таблицах БД. Он состоит из 4-х основных команд:

SELECT                 (выбрать)

INSERT                 (вставить)

UPDATE               (обновить)

DELETE                (удалить)

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

Основными его командами являются:

CREATE DATABASE создает БД 

CREATE TABLE                 создает Таблицу

CREATE INDEX создает индекс

ALTER DATABASE модифицировать

ALTER TABLE   

ALTER INDEX   

DROP DATABASE            удалить

DROP TABLE     

DROP INDEX      

рассмотрим команды языка манипулирования  данными.

 

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

Операция проекции позволяет выделить подмножество столбцов таблицы SELECT StudNo FROM student (список всех номеров зачеток).

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

SELECT Studno FROM student

WHERE toGroup = 1 (вывести список зачеток у выбранной группы)

Предложеные примеры илюстрируют общую форму команды select в языке SQL для одной таблицы

 

SELECT (выбрать поля)

FROM    (из таблицы)

WHERE (где)

 

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

SELECT Student.Studno

FROM Student, headgroup

WHERE headgroup.ToStudent=Student.id

 

SELECT *

FROM student

UNION объединение

SELECT *

FROM student_old

 

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

SELECT [DISTINCT] список полей

FROM    из таблицы (таблиц)

[WHERE               предикат]

[GROUP BY поле [HAVING предикат]]

[UNION другое выражение_Select]

[ORDER BY имя_поля_или_номер];

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

Команда select позволяет:

- назначать поля которые должны быть выбраны

-назначать к выборке все поля(*)

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

-подставлять собственные заголовки полей в результирующей таблице

-производить вычесление в списке выбираемых элементов

-использовать литералы в списке выбирамых элементов

-ограничивать число возвращаемых строк

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

-устранять одинаковые строки из результата

 

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

SELECT salary, salary * 1.15

FROM salary

получить список зарплаты служащих, в том числе увеличиную на 15 %.

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

                Литералы

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

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

SELECT "Зарплата ", salary

FROM salary

 

                               Конкатенация

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

SELECT Surname+' '+FirstName+' '+PatName

FROM people

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

SELECT count(*) AS RecCount

FROM Student (считает количество студентов)

 

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

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

SELECT min(StudNo) AS minid                      

FROM Student

 

SELECT avg(Salary) AS avgsalary

FROM Salary

 

                предложение from команды select

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

                ограничение на число выводимых строк

Число возвращаемых в результате запроса строк может быть ограничено путем использования предложения where, содержащего условие отбора. Условие отбора для отдельных строк может принимать значение true, false и unknow. При этом запрос возвращает в качестве результата только те строки, для которых предикат имеет значение true.

Типы предикатов используемы в предложении where:

=

<> 

!=

> 

< 

>=

<=

BETWEEN

IN

LIKE

IS NULL

EXIST

ANY

ALL

 

                Опреации сравнения

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

                предикат between

задает диапозон значений для которого выражение принимает значение true. Разрешено также испольщование конструкции not between

SELECT salary

FROM salary

WHERE salary BETWEEN 2000 AND 6000

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

SELECT salary

FROM salary

WHERE salary>2000 and  salary<6000

запрос с предикатом between может иметь следующий вид

SELECT SurName, Firstname, PatName

FROM People

WHERE SurName BETWEEN "а" AND "п"

Все люди входящие в диапозон в нижней части которого начало фамилии совпадает с а т.е. выполняется условие >= а в верхней части фамелия не более п (т.е. <=) попадут в выбор.

Следует отметить что при выборке с использованием преликата between поле на которое накладывается диапозон считается упорядоченым по возрастанию.

Not between позволяет получать выборку записей указаные поля которых имеют значения < нижней границы и > Больше верхней

 

предикат IN проверяет входит ли заданое значение предшествующее ключевому слову IN в указаный в скобках список. Если заданое проверяеме значение равно какому либо элемнту в списке, то предикат принимает значение true. Разрешено также использовать конструкцию not in

SELECT Name

FROM "Group"

WHERE Name IN ("181", "182")

 

                LIKE

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

%-заменяет любое количество символов (и ноль)

_-заменяет один символ.

разрешено использовать not LIKE

 

SELECT *

FROM people

WHERE SurName LIKE "A%"

 

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

 

SELECT firstname, Surname

FROM people

WHERE Surname LIKE "%@_%" ESCAPE "@"

 

escape символ не должен быть \ и должен представлять собой символ никогда не появляющийся в упоминаемом столбце, как информационный символ. Часто для этих целей используется символ @ и ~

               

                IS NULL

В запросах NULL означает что значение столбца неизвестно, поисковые условия в которых значение столбца сравнивается с NULL всегда принимают значения unknown ( и соответственно приводят к ошибке) в противоположность к true и false. предикат IS NULL принимает значение true только тогда когда  выражение слева от ключевых слов имеет значение null. Разрешено использовать конструкцию IS NOT NULL.

SELECT *

FROM People

WHERE Passport IS NULL

 

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

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

В одном предикате логические операторы выполняются в следующем порядке:not,and,or.

 

                ORDER BY

порядок выводимых строк может быть изменен с помощью дополнительного (опцилонального) предложения ORDER BY в конце SQL-запроса. Это предложение имеет вид ORDER BY <порядок строк> [ASC | DESC].

Способ упорядочивания определяется дополнительными зарезервированными словами [ASC | DESC]. По умолчанию по возрастанию (ASC).

SELECT *

FROM student

ORDER BY ToGroup

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

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

SELECT *

FROM student

ORDER BY 3

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

 

                Устранения дублирования (модификатор DISTINCT)

Дублироваными являются такие строки в результируюш\щей таблицы которых идентичен каждый столбец. Иногда (в зависимости от задачи) бывает необходимо устранить все повторы строк из результирующего набора. Этой цели служит модификатор DISTINCT. Данный модификатор может быть указан только один раз в списке выбираемых элементов и действует на весь список.

 

                соединение JOIN

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

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

2.Задавать имена столбцов различными способами.

3.Определять внешнее соединение.

4.определять соединение таблицы с собой.

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

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

               

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

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

SELECT *

FROM student, "group"

WHERE "group".id = 1

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

SELECT *

FROM student, "group"

WHERE "group".id = 1

AND ToGroup = "group".id

 

 

select *

from people,student,"group"

where "group".Name ="181"

and ToGroup = "group".id

and People.id=Student.ToPeople

Рассмотрим варианты работы с использованием зарезервированого слова join.

Условие соединения записываются в предложение from, в котором слева и справа от join указываются соединяемые таблицы. условие поиска основаные на правой таблице помещаются в предложение ON. Условие поиска основаные на левой таблице помещаются в предложении where

select People.Surname,People.FirstName,PatName

from people JOIN student

ON People.id=Student.toPeople

 

                самосоединение

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

SELECT *

FROM people p1,people p2

WHERE P1.FirstName = P2.FirstName

and P1.ID<>p2.id                                                //выбор отдноименцев.

 

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

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

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

left join - запрос возвращает все строки из левой таблицы (т.е. таблицы стоящие слева от слова left join)  и только те из правой таблицы которые удовлетворяют условию соединения. Если же в правой таблице не найдется строк удовлетворяющих заданому условию, то в результате они замещаются значениями null

right join - все наоборот

inner join -

 

Full Outer Join-соединяет таблицу включает в результирующий в набор данных все записи (в том числе и не связаные) из всез таблиц перечисленых после from. В не связаных записях недостающие значения полей заменяются на null

 

select * from people

full outer Join student on (people.id=student.topeople)

 

                Group by

можно сгрупировать по значению поля.

select togroup, count (togroup)

from student

group by togroup                               /показывает список груп и число студентов в них

 

 

HAVING-наложение ограничения на группировку записей.

если в результате нужно выдавать агрегацию не по всем группам а только по тем из них, которые отвечают некоторым условиям после предложения group by указывают having <условие поиска>, где <условие поиска> укаказывается по темже правилам, что и условия поиска where. За важным исключением:в условии поиска предложения having можно указывать агрегатные функции.

 

select togroup, count (togroup) as colstud

from student

group by togroup

having (count (togroup)>=2)                           //в каких группах более чем два студента

 

                использование функции UPPER

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

Эта функция может фигурировать как списке столбцов результирующего набора (после select) так и в условии поиска в предложении where.

Lower() -конвернтирует символы в маленькие буквы.

TRIM([LEADING|TRAILING|BOTH] [trimmed_char] FROM column_reference)-удаляет левые, правые, по умолчанию пробелы.

CAST (<значение> AS <тип данных>)-иногда необходимо трактовать значение одного типа как значение другого типа. Например использовать числовое значение как символьную строку или наоборот. Фунукция CAST делает копию значения, преобразуя его к указоному типу данных.При этом не следует забывать о множестве типов данных.

 

select cast(name as INTEGER)

from "group"

select cast(Date_Open as character(10)) as datestr

from ":DBdemos:clients"

 

EXTRACT(extract_field FROM column_reference) возвращает поле из даты (типа date или timestate)

 extract_field-может быть годом днем месяцем часом минутой или секундой

 

select Extract( YEAR from Date_Open ) as YY

from ":DBdemos:clients"                   //выбираем из даты год.

 

                Использование подзапросов.

Часто невозможно рештить поставленую задачу путем использования одного запроса. Это особенно актуально в тех случаях, когда при использовании условия поиска в предложении where с параметрема <Сравнимаемое значение><оператор><значение, с которым надо сравнить> Значение с которым надо сравнить заранее неопределено и должно быть выполнено в момент выполнения опреатора selsect