НОВЫЙ КУРС БД 2013

Оператор DROP

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

DROP TABLE table

Вторая разновидность используется для удаления индекса:

DROP INDEX index ON table

3.2.3 Операторы TSQL для манипуляции данными (SELECT, INSERT,

DELETE, UPDATE).

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

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

SELECT column-list FROM table-list [GROUP BY column-list] [WHERE where-clause]

[ORDER BY order-by-clause]

Операторы SELECT должны содержать слова SELECT и FROM; другие ключевые слова, такие как WHERE или ORDER BY, являются необязательными.

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

SELECT *

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

SELECT CompanyName

Пример выбора нескольких колонок имеет вид:

SELECT CompanyName, ContactName, ContactTitle

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

SELECT Customers.CompanyName, Shippers.CompanyName

Для указания имен таблиц, из которых выбираются записи, применяется ключевое слово FROM, например:

SELECT * FROM Customers

Этот запрос возвратит все поля из таблицы Customers.

Если в результирующем наборе данных нужны только поля CompanyName и ContactName, мы можем ввести следующее предложение SELECT:

SELECT CompanyName, ContactName FROM Customers

Если нужно осуществить выборку из нескольких таблиц следует производить соединения, о низ будет рассказано ниже.

Предложение WHERE и операторы AND, OR и NOT

Для фильтрации результатов, возвращаемых оператором SELECT, можно использовать предложение WHERE, синтаксис которого имеет вид:

WHERE expression1 [{AND | OR} expression2 […]]

Например, вместо получения полного списка продуктов можно ограничиться только теми из них, у которых значение поля CategoryID равно 4:

SELECT * FROM Products WHERE CategoryID = 4

31

В предложении WHERE можно использовать различные выражения, например:

SELECT * FROM Products

WHERE CategoryID = 2 AND SupplierID > 10

или:

SELECT ProductName, UnitPrice FROM Products WHERE CategoryID = 3 OR UnitPrice < 50

или:

SELECT ProductName, UnitPrice FROM Products WHERE Discontinued IS NOT NULL

Выражение ‘IS NOT NULL’ означает, что соответствующая колонка результирующего набора данных не может содержать пустых значений.

В предложении WHERE можно использовать один из шести операторов отношений, определенных в SQL. Эти операторы приведены в табл. 1.

Таблица 1

Оператор	Описание
<	Меньше
<=	Меньше или равно
<>	Не равно
=	Равно
>	Больше
>=	Больше или равно

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

Таблица 2

Оператор	Описание
ALL	Применяется совместно с операторами сравнения при сравнении со списком значений
ANY	Применяется совместно с операторами сравнения при сравнении со списком значений
BETWEEN	Применяется при проверке нахождения значения внутри заданного интервала
	(включая его границы)
IN	Применяется для проверки наличия значения в списке
LIKE	Применяется при проверке соответствия значения заданной маске

Приведем несколько примеров применения этих операторов. Для сопоставления данных с маской применяется ключевое слово LIKE:

SELECT CompanyName, ContactName

FROM Customers

WHERE CompanyName LIKE ‘M%’

В данной маске символ ‘%’ (процент) заменяет любую последовательность символов, а символ ‘_’ (подчеркивание) — один любой символ. Тот же самый результат может быть получен следующим способом:

SELECT CompanyName, ContactName

32

FROM Customers

WHERE CompanyName BETWEEN ‘M’ AND ‘N’

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

SELECT:

SELECT CompanyName, ContactName FROM Customers

WHERE CompanyName BETWEEN ‘A’ AND ‘D’

Используя, оператор LIKE, мы можем сузить диапазон поиска, применив более сложную маску для сравнения. Например, чтобы найти компании, содержащие в своем названии подстроку bl, можно применить следующий запрос:

SELECT CompanyName, ContactName FROM Customers

WHERE CompanyName LIKE ‘%bl%’

Маска ‘%bl%’ показывает, что до и после искомой подстроки может быть любое количество произвольных символов.

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

SELECT CompanyName, ContactName FROM Customers

WHERE CustomerID IN (‘ALFKI’, ‘BERGS’, ‘VINET’)

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

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

ORDER BY column1 [{ASC | DESC}][, column2 [{ASC | DESC}] [,…]

Например, для сортировки сотрудников по фамилии и затем по имени следует использовать следующий SQL-запрос:

SELECT LastName, FirstName, Title FROM Employees

ORDER BY LastName, FirstName

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

SELECT ProductName, UnitPrice FROM Products

ORDER BY UnitPrice DESC

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

Оператор UPDATE

Для изменения значений в одной или нескольких колонках таблицы применяется оператор UPDATE. Синтакcис этого оператора имеет вид:

UPDATE tableSET column1 = expression1 [, column2 = expression2] [,…] [WHERE criteria]

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

33

UPDATE Products

SET UnitPrice = UnitPrice * 1.1 WHERE UnitPrice < 10

Оператор DELETE

Для удаления строк из таблиц следует использовать оператор DELETE, синтаксис которого имеет вид: DELETE FROM table [WHERE criteria]

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

DELETE FROM Products

WHERE Discontinued = 1

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

SELECT ProductName FROM Products

WHERE Discontinued = 1

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

SELECT CompanyName FROM Customers

WHERE Customers.CustomerID NOT IN

(SELECT CustomerID FROM Orders WHERE OrderDate > 01.01.96)

а затем заменить оператор SELECT на оператор DELETE:

DELETE FROM Customers

WHERE Customers.CustomerID NOT IN

(SELECT CustomerID FROM Orders WHERE OrderDate > 01.01.96)

Замечание. При использовании в операторах SQL даты или времени, а также полей, содержащих такие данные, следует уточнить синтаксис таких предложений в документации из комплекта поставки используемой СУБД.

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

Оператор INSERT

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

INSERT [INTO] table( [column_list]{ VALUES ( { DEFAULT | NULL | expression } } [, …] )

Например, для добавления нового клиента в таблицу Customers запрос выглядит так:

INSERT INTO Customers (CustomerID, CompanyName) VALUES (‘XYZFO’, ‘XYZ Deli’)

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

INSERT INTO table SELECT …

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

INSERT INTO Employers

SELECT Id, Name, Surname, Patronymic, Salary*1,25 FROM TrialEmployers WHERE test = 1

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

34

3.2.4 Создание связей между таблицами

Теперь мы умеем создавать таблицы, писать запросы на вставку, удаление и обновление данных. Однако наши таблицы пока изолированы друг от друга. И связь между ними мы поддерживаем, так сказать «виртуально», задавая правильные данные. Конечно, SQL Server предоставляет возможность связать таблицы. Связь здесь задается на основе ключей. Давайте вспомним определения ключа – это столбец или набор столбцов однозначно определяющих каждую строку в таблице. При этом различают простые и составные ключи. Ключ, который однозначно определяет каждую строку в таблице, называется первичным (PRIMARY KEY). А для связи между таблицами создается копия первичного ключа в связываемой таблице с тем же типом данных и размером, но с другим именем, которая называется внешним ключом (FOREIGN KEY).

Давайте рассмотрим такой пример, пусть существуют таблицы «Students» и «Marks». В

таблице «Students» определены следующие поля: name, surname, patronymic, dirthdate. В ней пока не задан ключ. Давайте, исправим это и подумаем над тем, какой ключ здесь может быть? При выборе первичного ключа нужно думать, прежде всего, о его уникальности в рамках таблицы. На первый взгляд составной ключ из всех четырех полей name, surname, patronymic и birthdate является неплохим вариантом. И хотя вероятность появления двух человек с одинаковыми ФИО и рожденными в один день крайне мала, но она существует. И здесь вопрос о выборе ключа зависит уже от предметной области, например если мы описываем базу фирмы, где трудятся 10-150 человек, то ничего страшного нет, вероятность появления двух «одинаковых» людей настолько мала, что ей можно пренебречь. А если мы пишем, скажем, ту же базу учебного заведения, в которой обучаются от 10000 до 50000 студентов ежегодно, то согласитесь, вероятность появления двух одинаковых людей значительно увеличивается. Поэтому для определения уникальности мы введем идентифицирующий столбец, так называемый ID. Который будет выполнять роль счетчика и уникально определять каждую строку. Итак, давайте напишем код создания для этого столбца в нашей таблице:

CREATE TABLE Students

(

id int identity(1,1) not null, name varchar(30) not null, surname varchar(50) not null, patronymic varchar(40) not null, dirthdate datetime not null

)

Ключевое слово identity(1,1) просто определяет счетчик, а значения в скобках указывают начальное значение и приращение счетчика соответственно. Однако мы еще не создали сам ключ. Как уже было сказано в SQL Server связи устанавливаются на основе ключей. Ключи являются разновидностью ограничений, - специальных конструкций в SQL Server. Ограничениям посвящен отдельный пункт данной статьи. А пока только немного познакомимся с ними.

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

Всего существуют три основных вида ограничений:

Ограничения сущностей

Ограничения домена

Ограничения ссылочной целостности

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

35

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

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

1.При создании объекта (CREATE)

2.При изменении объекта (ALTER)

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

CREATE TABLE MyTable (id int not null PRIMARY KEY)

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

CREATE TABLE LinkTable

(

id int not null PRIMARY KEY,

m int not null FOREIGN KEY REFERENCES MyTable(id)

)

Мы только что создали связь между двумя этими таблицами на основе первичного и внешних ключей. А теперь посмотрим, как создавать ограничения на уже существующих таблицах. А посмотрим мы это на таблицах Students и Marks, озвученных вначале этого пункта. Мы уже создали таблицу Students ранее, давайте создадим таблицу Marks с такой структурой: идентификатор, студент, оценка (id, student, mark). При этом поле id будет первичным ключом, а поле student будет являться внешним ключом, связанным с полем id в таблице Students. Однако мы зададим эти ограничения позже через конструкцию ALTER. Итак создаем таблицу Marks

CREATE TABLE Marks

(

id int not null, student int not null, mark byte not null

)

Итак, мы создали две таблицы теперь можно устанавливать между ними связь. Но прежде чем это сделать запомним следующий момент, при создании внешних ключей все таблицы, на которые ссылаются эти ключи, должны быть созданы. Т.е. очень важна последовательность выполнения команд – сначала создаем таблицы, потом устанавливаем связи. Т.е. создать все таблицы без ограничений, а потом просто добавить ограничения к этим таблицам с помощью команды ALTER. Так мы и поступим с нашими таблицами, предположим, что мы еще не задавали ограничений. Давайте зададим первичные ключи для таблиц Students и Marks. Это делается так:

ALTER TABLE Students

ADD CONSTRAINT PK_Students_Id PRIMARY KEY (Id)

ALTER TABLE Marks

ADD CONSTRAINT PK_ Marks _Id PRIMARY KEY (Id)

Атеперь зададим внешний ключ для таблицы Marks. Ссылаясь на столбец id таблицы Students:

ALTER TABLE Marks

ADD CONSTRAINT FK_ Marks _Student FOREIGN KEY Student REFERENCES Students (id)

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

36

3.3 Создание таблиц и связей в среде SQL Server.

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

Но начну я немного издалека. А именно я хочу обратиться к вопросу, где писать все эти запросы и что из себя представляет среда Enterprise Manager. До этого момента мы писали запросы только на бумаге или в текстовых редакторах. К этому моменту необходимо, чтобы у читателя уже был установлен MS SQL Server, если он еще этого не сделал. О том, как его поставить, подробно написано в приложении 3. А сейчас я хотел бы обратиться к самой среде разработки. При установке SQL Server’a в меню пуск появляется вот такие программы (в зависимости от версии, количество их может быть различным):

Рисунок 1. MS SQL Server в меню « уск»

Тут есть и справка и различные утилиты, но я остановлюсь на 3-х программах:

1.Service Manager

2.Query Analyzer

3.Enterprise Manager

1.Программа Service Manager предоставляет возможность управлять запущенными экземплярами SQL Server’ов. Т.е. она включает и выключает службы. Главное окно программы выглядит примерно так (примерно, потому что в зависимости от версии оно может выглядеть поразному):

Рисунок 2. Окно программы Service Manager

Здесь пользователь может выбрать экземпляр сервера и управлять его состоянием. А именно существуют 3 возможности: можно останавливать сервер, приостановить и запустить.

37

Также можно отметить флажком «Auto-start service when OS starts» автоматический старт службы SQL Server при загрузке операционной системы.

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

Рисунок 3. Стартовое окно программы Query Analyzer.

Пользователю необходимо выбрать экземпляр сервера, к которому производится подключение и указать логин и пароль, если это необходимо. Далее мы увидим окно представленное на рисунке 4.

Рисунок 4. Основное окно программы Query Analyzer.

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

38

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

3. И, наконец, последняя, но самая важная и самая функциональная программа - Enterprise Manager. Главное окно программы изображено на рисунке 5.

Рисунок 5. Основное окно программы Enterprise Manager.

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

Databases

Data Transformation Services

Management

Replication

Security

Support Services

Meta Data Services

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

Рисунок 6. Список баз данных.

39

Можно раскрыть любую базу скажем test_1 и тогда мы увидим ее содержание в следующем

виде:

Рисунок 7. Список объектов базы данных test_1.

Как видим, объекты базы данных нам хорошо знакомы по предыдущим пунктам. Это диаграммы, таблицы, представления и так далее. Выбор любой группы объектов немедленно отобразит список объектов этой группы. Например, если мы кликнем на группе объектов Tables, т.е. таблицы, нам будут показаны все таблицы в этой базе данных.

Рисунок 8. Список таблиц базы данных test_1.

По умолчанию там уже содержится множество системных таблиц, трогать которые не рекомендуется, нашей же целью стоит создание своих таблиц и обеспечение связей между ними. Итак для того, чтобы создать таблицу необходимо щелкнуть правой кнопкой мыши на любой свободной области в правой части окна, там где указаны имена таблиц. В результате будет показано меню, представленное на рисунке 9, и в этом меню нужно выбрать New Table... (Новая таблица…). После этого на экране появится окно для создания новой таблицы, представленное на рисунке 10.

40