Основы T-SQL. Объявление текущей базы данных. Пример создания локальной базы данных Microsoft SQL Server в MS Visual Studio

Определите место для папки Databases. После выполнения соединения с сервером (локальное или удаленное), откроется окно обозревателя объектов Object Explorer в левой стороне экрана. В верхней части дерева обозревателя объектов будет сервер, к которому вы подключены. Если дерево не расширено, нажмите на значок "+" рядом с ним. Определите место папки базы данных Databases.

Создайте новую базу данных. Щелкните правой кнопкой мыши по папке Databases и выберите пункт "New Database...". Появится окно, которое позволяет настроить базу данных перед ее созданием. Дайте имя базе данных, которое поможет вам идентифицировать ее. Большинство пользователей могут оставить значения остальных настроек по умолчанию.

• Вы заметите, что при вводе имени базы данных два дополнительных файла будут созданы автоматически: Data и Log. Файл данных (Data) вмещает все данные в вашей базе данных, в то время как файл журнала (Log) отслеживает изменения в базе данных.
• Нажмите кнопку OK, чтобы создать базу данных. Вы увидите вашу новую базу данных, которая появится в развернутой папке Databases. Она будет иметь значок цилиндра.

Создайте таблицу. База данных может только хранить данные, если вы создаете структуру для этих данных. Таблица содержит информацию, которую вы вводите в вашу базу данных, и вам нужно будет создать ее, прежде чем можете продолжить. Разверните новую базу данных в папке Databases, и щелкните правой кнопкой мыши на папке Tables и выберите пункт "New Table...".

• Windows откроется в остальной части экрана, позволяя вам управлять вашей новой таблицей.

Создайте Primary Key (первичный ключ). Настоятельно рекомендуется, чтобы вы создавали первичный ключ в качестве первого столбца в вашей таблице. Он действует как идентификационный номер, или номер записи, что позволит вам легко выводить эти записи позже. Для его создания введите "ID" в столбце Name field, тип int в поле Data Type и снимите флажок "Allow Nulls". Нажмите на значок Key iна панели инструментов, чтобы установить этот столбец в качестве Primary Key (первичного ключа).

• Вы же не хотите допустить нулевые значения, так как всегда хотите иметь запись по крайней мере "1". Если вы разрешите 0, ваша первая запись будет "0".
• В окне Column Properties прокрутите вниз, пока не найдете опцию Identity Specification. Разверните ее и установите "(ls Identity)" на "Yes". Эта опция автоматически увеличит значение столбца ID для каждой записи, автоматически нумеруя каждую новую запись.

Разберитесь, как устроены таблицы. Таблицы состоят из полей или столбцов. Каждый столбец представляет один из аспектов записи базы данных. Например, если вы создаете базу данных сотрудников, вы можете иметь столбец "FirstName", столбец "LastName", столбец "Address" и столбец "PhoneNumber".

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

• nchar(#) - это тип данных следует использовать для текста, как имена, адреса и т.д. Число в скобках – это максимальное количество символов, разрешенное для это го поля. Установление лимита гарантирует, что ваш размер базы данных остается управляемым. Номера телефонов должны быть сохранены в этом формате, так как вы не выполняете математические функции с ними.
• int - это целые числа, и обычно используются в поле идентификатора.
• decimal(x,y) - будут хранить числа в десятичной форме, а числа в скобках обозначают соответственно общее количество цифр и количество цифр после десятичной. Например, decimal(6,2) будет сохранять числа как 0000.00.

Сохраните вашу таблицу. Когда вы закончите создавать свои столбцы, то вам нужно сохранить таблицу перед вводом информации. Щелкните на значке Save на панели инструментов, а затем введите название таблицы. Рекомендуется присваивать имя таблице таким образом, чтобы оно помогло вам распознать содержимое, особенно для больших баз данных с несколькими таблицами.

Добавьте данные в вашу таблицу. После того, как вы сохранили таблицу, можете начать добавлять в нее данные. Откройте папку Tables в окне обозревателя объектов Object Explorer. Если вашей новой таблицы нет в списке, щелкните правой кнопкой мыши на папке Tables и выберите Refresh. Щелкните правой кнопкой мыши по таблице и выберите "Edit Top 200 Rows".

В организации базы данных задействуется большое число различных объектов. Все объекты базы данных являются либо физическими, либо логическими. Физические объекты связаны с организацией данных на физических устройствах (дисках). Физическими объектами компонента Database Engine являются файлы и файловые группы. Логические объекты являются пользовательскими представлениями базы данных. В качестве примера логических объектов можно назвать таблицы, столбцы и представления (виртуальные таблицы).

Объектом базы данных, который требуется создать в первую очередь, является сама база данных. Компонент Database Engine управляет как системными, так и пользовательскими базами данных. Пользовательские базы данных могут создаваться авторизованными пользователями, тогда как системные базы данных создаются при установке СУБД.

Для создания базы данных используется два основных метода. В первом методе задействуется обозреватель объектов среды SQL Server Management Studio, как было показано ранее, а во втором применяется инструкция языка Transact-SQL CREATE DATABASE . Далее приводится общая форма этой инструкции, а затем подробно рассматриваются ее составляющие:

CREATE DATABASE db_name { file_spec1} ,...] Соглашения по синтаксису

Параметр db_name - это имя базы данных. Имя базы данных может содержать максимум 128 символов. Одна система может управлять до 32 767 базами данных. Все базы данных хранятся в файлах, которые могут быть указаны явно администратором или предоставлены неявно системой. Если инструкция CREATE DATABASE содержит параметр ON , все файлы базы данных указываются явно.

Компонент Database Engine хранит файлы данных на диске. Каждый файл содержит данные одной базы данных. Эти файлы можно организовать в файловые группы. Файловые группы предоставляют возможность распределять данные по разным приводам дисков и выполнять резервное копирование и восстановление частей базы данных. Это полезная функциональность для очень больших баз данных.

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

Учетная запись компонента Database Engine, применяемая для создания базы данных, называется владельцем базы данных . База данных может иметь только одного владельца, который всегда соответствует учетной записи. Учетная запись, принадлежащая владельцу базы данных, имеет специальное имя dbo . Это имя всегда используется в отношении базы данных, которой владеет пользователь.

Опция LOG ON параметра dbo определяет один или более файлов в качестве физического хранилища журнала транзакций базы данных. Если опция LOG ON отсутствует, то журнал транзакций базы данных все равно будет создан, поскольку каждая база данных должна иметь, по крайней мере, один журнал транзакций. (Компонент Database Engine ведет учет всем изменениям, которые он выполняет с базой данных. Система сохраняет все эти записи, в особенности значения до и после транзакции, в одном или более файлов, которые называются журналами транзакций. Для каждой базы данных системы ведется ее собственный журнал транзакций.)

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

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

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

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

USE master; CREATE DATABASE SampleDb;

Код, приведенный в примере, создает базу данных, которая называется SampleDb. Такая сокращенная форма инструкции CREATE DATABASE возможна благодаря тому, что почти все ее параметры имеют значения по умолчанию. По умолчанию система создает два файла. Файл данных имеет логическое имя SampleDb и исходный размер 2 Мбайта. А файл журнала транзакций имеет логическое имя SampleDb_log и исходный размер 1 Мбайт. (Значения размеров обоих файлов, а также другие свойства новой базы данных зависят от соответствующих спецификаций базы данных model.)

В примере ниже показано создание базы данных с явным указанием файлов базы данных и журнала транзакций:

USE master; CREATE DATABASE Projects ON (NAME=projects_dat, FILENAME = "D:\projects.mdf", SIZE = 10, MAXSIZE = 100, FILEGROWTH = 5) LOG ON (NAME=projects_log, FILENAME = "D:\projects.ldf", SIZE = 40, MAXSIZE = 100, FILEGROWTH = 10);

Созданная в примере база данных называется Projects. Поскольку опция PRIMARY не указана, то первичным файлом предполагается первый файл. Этот файл имеет логическое имя projects_dat и он сохраняется в дисковом файле projects.mdf. Исходный размер этого файла 10 Мбайт. При необходимости, система выделяет этому файлу дополнительное дисковое пространство в приращениях по 5 Мбайт. Если не указать опцию MAXSIZE или если этой опции присвоено значение UNLIMITED, то максимальный размер файла может увеличиваться и будет ограничиваться только размером всего дискового пространства. (Единицу размера файла можно указывать с помощью суффиксов KB, TB и MB, означающих килобайты, терабайты и мегабайты соответственно. По умолчанию используется единица размера MB, т.е. мегабайты.)

Кроме файла данных создается файл журнала транзакций, который имеет логическое имя projects_log и физическое имя projects.ldf. Все опции спецификации файла журнала транзакций имеют такие же имена и значения, как и соответствующие опции для спецификации файла данных.

В языке Transact-SQL можно указать конкретный контекст базы данных (т.е. какую базу данных использовать в качестве текущей) с помощью инструкции USE . (Альтернативный способ - выбрать имя требуемой базы данных в раскрывающемся списке Database (Базы данных) в панели инструментов среды SQL Server Management Studio.)

Системный администратор может назначить пользователю текущую базу данных по умолчанию с помощью инструкции CREATE LOGIN или инструкции ALTER LOGIN. В таком случае пользователям не нужно выполнять инструкцию USE, если только они не хотят использовать другую базу данных.

Создание моментального снимка базы данных

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

Таким образом, чтобы создать моментальный снимок базы данных, в инструкцию CREATE DATABASE нужно вставить предложение AS SNAPSHOT OF . В примере ниже иллюстрируется создание моментального снимка базы данных SampleDb и сохранения его в папке D:\temp. (Прежде чем выполнять этот пример, нужно создать данный каталог.)

USE master; CREATE DATABASE SampleDb ON (NAME = "SampleDb_Data" , FILENAME = "D:\temp\snapshot_DB.mdf") AS SNAPSHOT OF SampleDb;

Моментальный снимок существующей базы данных - это доступная только для чтения копия базы данных-источника, которая отражает состояние этой базы данных на момент копирования. (Таким образом, можно создавать множественные моментальные снимки существующей базы данных.) Файл моментального снимка (в примере выше это файл D:\temp\snapshot_DB.mdf) содержит только измененные данные базы данных-источника. Поэтому в коде для создания моментального снимка необходимо указывать логическое имя каждого файла данных базы данных-источника, а также соответствующие физические имена.

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

Моментальные снимки баз данных можно создавать только на дисках с файловой системой NTFS (New Technology File System - файловая система новой технологии), т.к. только эта файловая система поддерживает технологию разреженных файлов, применяемую для хранения моментальных снимков.

Моментальные снимки баз данных обычно применяются в качестве механизма предохранения данных от искажения.

Присоединение и отсоединение баз данных

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

Для отсоединения базы данных от сервера баз используется системная процедура sp_detach_db . (Отсоединяемая база данных должна находиться в однопользовательском режиме.)

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

Итак, вы установили MySQL, и мы начинаем осваивать язык SQL. В уроке 3 по основам баз данных , мы создали концептуальную модель маленькой БД для форума. Пришло время реализовать ее в СУБД MySQL.

Для этого прежде всего надо запустить сервер MySQL. Идем в системное меню Пуск - Программы - MySQL - MySQL Server 5.1 - MySQL Command Line Client. Откроется окно, предлагающее ввести пароль.

Нажимаем Enter на клавиатуре, если вы не указывали пароль при настройке сервера или указываем пароль, если вы его задавали. Ждем приглашения mysql>.

Нам надо создать базу данных, которую мы назовем forum. Для этого в SQL существует оператор create database

Create database имя_базы_данных;

Итак, создадим БД forum:

Нажимаем Enter и видим ответ "Query OK ...", означающий, что БД была создана:

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

Use имя_базы_данных;

Нажимаем Enter и видим ответ "Database changed" - база данных выбрана.

Выбирать БД необходимо в каждом сеансе работы с MySQL.

Для создания таблиц в SQL существует оператор create table . Создание базы данных имеет следующий синтаксис:

Create table имя_таблицы (имя_первого_столбца тип, имя_второго_столбца тип, ..., имя_последнего_столбца тип);

Нажимаем Enter - таблица создана:

Итак, мы создали таблицу topics (темы) с тремя столбцами:
id_topic int - id темы (целочисленное значение),
topic_name text - имя темы (строка),
id_author int - id автора (целочисленное значение).

Аналогичным образом создадим оставшиеся две таблицы - users (пользователи) и posts (сообщения):

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

show databases - показать все имеющиеся БД,

show tables - показать список таблиц текущей БД (предварительно ее надо выбрать с помощью оператора use ),

describe имя_таблицы - показать описание столбцов указанной таблицы.

Давайте попробуем. Смотрим все имеющиеся базы данных (у вас она пока одна - forum, у меня 30, и все они перечислены в столбик):

Теперь посмотрим список таблиц БД forum (для этого ее предварительно надо выбрать), не забываем после каждого запроса нажимать Enter:

В ответе видим названия наших трех таблиц. Теперь посмотрим описание столбцов, например, таблицы topics:

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

А сегодня мы рассмотрим последний оператор - drop , он позволяет удалять таблицы и БД. Например, давайте удалим таблицу topics. Так как мы два шага назад выбирали БД forum для работы, то сейчас ее выбирать не надо, можно просто написать:

Drop table имя_таблицы;

Теперь снова посмотрим список таблиц нашей БД:

Наша таблица действительно удалена. Теперь давайте удалим и саму БД forum (удаляйте, не жалейте, ее все равно придется переделывать). Для этого напишем:

Drop database имя_базы данных;

И убедитесь в этом, сделав запрос на все имеющиеся БД:

У вас, наверно, нет ни одной БД, у меня их стало 29 вместо 30.

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

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

Именно поэтому мы подготовили краткий экскурс по основам SQL. Начнем же!

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

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

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

• id – Номер месяца в календарном году (целое число).
• name – Название месяца (строка, максимум 10 символов).
• days – Количество дней в этом месяце (целое число).

Вот как будет выглядеть соответствующий SQL запрос:

CREATE TABLE months (id int, name varchar(10), days int);

Также при создании таблиц целесообразно добавить первичный ключ для одной из колонок. Это позволит держать записи уникальными и ускорит запросы на выборку. Пусть в нашем случае уникальным будет название месяца (столбец name )

CREATE TABLE months (id int, name varchar(10), days int, PRIMARY KEY (name));

2. Вставка строк

Теперь давайте заполнять нашу таблицу months полезной информацией. Добавление записей в таблицу производится через инструкцию INSERT . Есть два способа записи этой инструкции.

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

Этот способ записи прост, но небезопасен, поскольку нет гарантии, что по мере расширения проекта и редактировании таблицы, столбцы будут располагаться в том же порядке, что и ранее. Безопасный (и в тоже время более громоздкий) способ записи инструкции INSERT требует указания как значений, так и порядка следования столбцов:

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

3. Извлечение данных из таблиц

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

Самый простое использование инструкции SELECT - запрос, который возвращает все столбцы и строки из таблицы (например, таблицы по имени characters ):

SELECT * FROM "characters"

Символ звездочка (*) означает, что мы хотим получить данные из всех столбцов. Так базы данных SQL обычно состоят из более чем одной таблицы, то требуется обязательно указывать ключевое слово FROM , следом за которым через пробел должно следовать название таблицы.

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

SELECT id, name FROM month

Кроме того, во многих случаях мы хотим, чтобы полученные результаты были отсортированы в определенном порядке. В SQL мы делаем это с помощью ORDER BY . Он может принимать опциональный модификатор – ASC (по-умолчанию) сортирующий по возрастанию или DESC , сортирующий по убыванию:

SELECT id, name FROM month ORDER BY name DESC

При использовании ORDER BY убедитесь, что оно будет последним в инструкции SELECT . В противном случае будет выдано сообщение об ошибке.

4. Фильтрация данных

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

В этом запросе мы выбираем только те месяцы из таблицы month , в которых больше 30 дней с помощью оператора больше (>).

SELECT id, name FROM month WHERE days > 30

5. Расширенная фильтрация данных. Операторы AND и OR

Ранее мы использовали фильтрацию данных с использованием одного критерия. Для более сложной фильтрации данных можно использовать операторы AND и OR и операторов сравнения (=,<,>,<=,>=,<>).

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

6. In/Between/Like

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

• IN – служит для указания диапазона условий, любое из которых может быть выполнено
• BETWEEN – проверяет, находится ли значение в указанном диапазоне
• LIKE – ищет по определенным паттернам

Например, если мы хотим выбрать альбомы с поп и соул музыкой, мы можем использовать IN("value1","value2") .

SELECT * FROM albums WHERE genre IN ("pop","soul");

Если мы хотим получить все альбомы, изданные между 1975 и 1985годами, мы должны записать:

SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;

7. Функции

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

• COUNT() – возвращает количество строк
• SUM() – возвращает общую сумму числового столбца
• AVG() – возвращает среднее значение из множества значений
• MIN() / MAX() – получает минимальное / максимальное значение из столбца

Чтобы получить самый последний год в нашей таблице мы должны записать такой SQL запрос:

SELECT MAX(released) FROM albums;

8. Подзапросы

В предыдущем пункте мы научились делать простые расчеты с данными. Если мы хотим использовать результат от этих расчетов, нам не обойтись без вложенных запросов. Допустим, мы хотим вывести artist , album и release year для старейшего альбома в таблице.

Мы знаем, как получить эти конкретные столбцы:

SELECT artist, album, released FROM albums;

Мы также знаем, как получить самый ранний год:

SELECT MIN(released) FROM album;

Все, что нужно сейчас, - это объединить два запроса с помощью WHERE:

SELECT artist,album,released FROM albums WHERE released = (SELECT MIN(released) FROM albums);

9. Объединение таблиц

В более сложных базах данных существует несколько таблиц, связанных друг с другом. Например, ниже представлены две таблицы о видеоиграх (video_games ) и разработчиков видеоигр (game_developers ).

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

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

SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country FROM video_games INNER JOIN game_developers ON video_games.developer_id = game_developers.id;

Это самый простой и наиболее распространенный тип JOIN . Есть несколько других вариантов, но они применимы к менее частым случаям.

10. Алиасы

Если вы посмотрите на предыдущий пример, то вы заметите, что существуют две колонки называемые name . Это сбивает с толку, так что давайте установим псевдоним одного из повторяющихся столбцов, например, name из таблицы game_developers будет называться developer .

Мы также можем сократить запрос задав псевдонимы имен таблиц: video_games назовем games , game_developers - devs :

SELECT games.name, games.genre, devs.name AS developer, devs.country FROM video_games AS games INNER JOIN game_developers AS devs ON games.developer_id = devs.id;

11. Обновление данных

Часто мы должны изменить данные в некоторых строках. В SQL это делается с помощью инструкции UPDATE . Инструкция UPDATE состоит из:

• Таблицы, в которой находится значение для замены;
• Имен столбцов и их новых значений;
• Выбранные с помощью WHERE строки, которые мы хотим обновить. Если этого не сделать, то изменятся все строки в таблице.

Ниже приведена таблица tv_series с сериалами с их рейтингом. Однако, в таблицу закралась маленькая ошибка: хотя сериал Игра престолов и описывается как комедия, он на самом деле ей не является. Давайте исправим это!

12. Удаление данных

Удаление строки таблицы с помощью SQL - это очень простой процесс. Все, что вам нужно, - это выбрать таблицу и строку, которую нужно удалить. Давайте удалим из предыдущего примера последнюю строку в таблице tv_series . Делается это с помощью инструкции >DELETE

DELETE FROM tv_series WHERE id = 4

Будьте осторожными при написании инструкции DELETE и убедитесь, что условие WHERE присутствует, иначе все строки таблицы будут удалены!

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

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

TRUNCATE TABLE table_name;

В случае, когда мы на самом деле хотим, чтобы удалить и данные, и саму таблицу, то нам пригодится команда DROP:

DROP TABLE table_name;

Будьте очень осторожны с этими командами. Их нельзя отменить!/p>

На этом мы завершаем наш учебник по SQL! Мы многое о чем не рассказали, но то, что вы уже знаете, должно быть достаточно, чтобы дать вам несколько практических навыков в вашей веб-карьере.

Разновидность языка, применяемая в конкретной СУБД, называется диалектом SQL . Например, диалект СУБДOracleназываетсяPL / SQL ; вMSSQLServerиDB2 применяется диалектTransact - SQL ; вInterbaseиFirebird–isql . Каждый диалектSQLсовместим до определенной степени со стандартомSQL, но может иметь отличия и специфические расширения языка, поэтому для выяснения синтаксиса того или иногоSQL-оператора следует в первую очередь смотретьHelp конкретной СУБД.

Для операций над базами данных и таблицами в стандарте sql предусмотрены операторы:

Ниже приводится синтаксис этих операторов по стандарту SQL92. Поскольку их синтаксис в СУБД может отличаться от стандарта, при выполнении лабораторной работы рекомендуется обращаться к справочной системе СУБД.

Имена объектов базы данных (таблиц, столбцов и др.) могут состоять из буквенно-цифровых символов и символа подчеркивания. Специальные символы (@$# и т.п.) обычно указывают на особый тип таблицы (системная, временная и др.). Не рекомендуется использовать в именах национальные (русские) символы, пробелы и зарезервированные слова, но если они всё же используются, то такие имена следует писать в кавычках ".." или в квадратных скобках [..].

Далее при описании конструкций операторов SQLбудут использоваться следующие обозначения: в квадратных скобках записываются необязательные части конструкции; альтернативные конструкции разделяются вертикальной чертой | ; фигурные скобки {} выделяют логические блоки конструкции; многоточие… указывает на то, что предшествующая часть конструкции может многократно повторяться. «Раскрываемые» конструкции записываются в угловых скобках < >.

Создание базы данных

CREATE DATABASE Имя_базы_данных

Удаление одной и более баз данных

DROP DATABASE Имя_базы_данных [,Имя_базы_данных …]

Объявление текущей базы данных

USE Имя_базы_данных –- в SQL Server и MySQL

SET DATABASE Имя _ базы _ данных – в Firebird

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

CREATE TABLE Имя_таблицы (

<описание_столбца> [, <описание_столбца> |

<ограничение_целостности_таблицы> …]

< описание_столбца > 

Имя_столбца ТИП

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

ТИП столбца может быть либо стандартным типом данных (см. таблицу 1), либо именем домена (см. п.6.2).

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

Описание вычислимого столбца в SQL Server имеет вид:

<описание_столбца>  Имя_столбца AS выражение

Описание вычислимого столбца в Firebird имеет вид:

<описание_столбца>  Имя_столбца COMPUTED BY <выражение>

СУБД MySQL 3.23 вычислимые столбцы не поддерживает.

< > 

CONSTRAINT Имя_ограничения_целостности

{UNIQUE|PRIMARY KEY}(список_столбцов_образующих_ключ )

|FOREIGN KEY (список _ столбцов _FK )

REFERENCES Имя_таблицы (список_столбцов_ PK )

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

|CHECK (условие_проверки )

Некоторые СУБД допускают объявление врéменных таблиц (существующих только во время сеанса). В SQL Server имена временных таблиц должны начинаться с символа # (локальные временные таблицы, видимые только создавшему их пользователю) или ## (глобальные таблицы, видимые всем пользователям); в MySQL для создания временных таблиц используется ключевое слово TEMPORARY, например:

CREATE TEMPORARY TABLE … (далее синтаксис см. CREATE TABLE).

Изменение структуры таблицы

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

ALTER TABLE Имя_таблицы

Изменение типа столбца (в SQLServerиFirebird)

ALTER COLUMN Имя_столбца новый_ТИП

Изменение типа, имени и ограничений столбца (в MySQL)

CHANGE COLUMN Имя_столбца <описание_столбца>

Добавление обычного или вычислимого столбца

|ADD <описание_столбца >

Добавление ограничения целостности

| ADD

<ограничение_целостности_таблицы >

Удаление столбца

|DROP COLUMN Имя_столбца

Удаление ограничения целостности

|DROP CONSTRAINT Имя_ограничения_целостности

Включение или отключение проверки ограничений целостности

ВMSSQLServer

|{CHECK|NO CHECK} CONSTRAINT

{Список_имен_ограничений_целостности |ALL}

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

DROP TABLE Имя_таблицы



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

1. Обязательное наличие данных (NULL–значения)

Объявляется словом NULL(столбец может иметь пустые ячейки) илиNOT NULL(столбец обязательный). По умолчанию принимаетсяNULL.

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

CREATE TABLE Clients(

ClientName NVARCHAR (60) NOT NULL ,

DateOfBirth DATE NULL ,

Phone CHAR (12)); -- по умолчанию тоже NULL

2. Значение по умолчанию (DEFAULT)

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

Рассмотрим пример создания таблицы Orders (Заказы). Столбец OrderDate принимает по умолчанию значение текущей даты, а столбец Quantity (количество) по умолчанию равен 0.

CREATE TABLE Orders(

OrderNum INT NOT NULL , -- номер заказа

OrderDate DATETIME NOT NULL -- дата заказа

DEFAULT GetDate(),

Функция GetDate() возвращает текущую дату 8

Quantity SMALLINT NOT NULL -- кол-во товара, DEFAULT 0);

3. Объявление первичных ключей (PRIMARYKEY)

Простой первичный ключ объявляется словами PRIMARYKEYпри создании таблицы. Например,

CREATE TABLE Staff(-- таблица "Работники"

TabNum INT PRIMARY KEY , -- первичный ключ

WName NVARCHAR (40) NOT NULL , -- ФИО

... -- описание прочих столбцов );

Составной первичный ключ объявляется иначе:

-- способ 1 (объявление PK при создании таблицы)

CREATE TABLE Clients(

PasSeria NUMERIC (4,0)NOT NULL ,-- серия паспорта

PasNumber NUMERIC (6,0)NOT NULL ,-- номер паспорта

Name NVARCHAR (40)NOT NULL ,

Phone CHAR (12),

-- объявление составного первичного ключа

CONSTRAINT Clients_PK

PRIMARY KEY (PasSeria,PasNumber));

-- способ 2(PK объявляется после создания таблицы)

-- сначала создаем таблицу без PK

CREATE TABLE Clients(

PasSeria NUMERIC (4,0)NOT NULL ,--серия паспорта

PasNumber NUMERIC (6,0)NOT NULL ,--номер паспорта

ClientName NVARCHAR (40)NOT NULL ,

Phone CHAR (12));

-- модификация таблицы – добавляем РК

ALTER TABLE Clients

ADD CONSTRAINT Clients_PK

PRIMARY KEY (PasSeria,PasNumber);

4. Уникальность столбцов (UNIQUE)

Подобно Primary Key указывает, что столбец или группа столбцов не могут содержать повторяющихся значений, но не являютсяPK . Все столбцы, объявленныеUNIQUE, должны бытьNOTNULL. Пример объявления простого уникального столбца:

CREATE TABLE Students(

SCode INT PRIMARY KEY , -- суррогатный РК

FIO NVARCHAR (40) NOT NULL , -- ФИО

RecordBook CHAR (6) NOT NULL UNIQUE ); -- № зачетки

Пример объявления составного уникального поля:

CREATE TABLE Staff(-- таблица " Работники "

TabNum INT PRIMARY KEY , -- табельный номер

WName NVARCHAR (40) NOT NULL , -- ФИО

PasSeria NUMERIC (4,0) NOT NULL , -- серия паспорта

PasNumber NUMERIC (6,0) NOT NULL , -- номер паспорта

-- объявление составного уникального поля

CONSTRAINT Staff_UNQ UNIQUE (PasSeria,PasNumber));

5. Ограничения на значения столбца (CHECK)

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

Пример создания таблицы Workers (Работники) :

CREATE TABLE Workers(

-- табельные номера 4-значные

TabNum INT PRIMARY KEY

CHECK (TabNum BETWEEN 1000 AND 9999),

Name VARCHAR (60) NOT NULL , -- ФИО сотрудника

-- пол – буква " м " или " ж "

Gentry CHAR (1) NOT NULL

CHECK (Gentry IN ("м","ж")),

Возраст не менее 14 лет

Age SMALLINT NOT NULL CHECK (Age>=14),

--№ свидет-ва пенсионного страхования (по маске)

PensionCert CHAR (14)

CHECK (PensionSert LIKE ""));

В этом примере показаны разные типы проверок. Диапазон допустимых значений указывается конструкцией BETWEEN…AND; обычные условия (как для столбцаAge ) используют знаки сравнений =, <>, >, >=, <, <=, связанные при необходимости логическими операциямиAND,OR,NOT(например,Age >=14ANDAge <=70); для указания списка допустимых значений используется предикатINи его отрицаниеNOTIN; конструкция

LIKEмаска_допустимых_значений EXCEPTсписок_исключений

используется для задания маски допустимых значений строковых столбцов. В маске применяются два спецсимвола: «%» – произвольная подстрока, и ­«_» – любой единичный символ. Конструкция EXCEPTявляется необязательной.

В условии отбора CHECKмогут сравниваться значения двух столбцов одной таблицы и столбцы разных таблиц.