DML и DDL.Чем же отличаются эти две группы операторов? В то время, как операторы DML достаточно однотипны для различных реализаций SQL (что дает возможность каждому поставщику программной продукции вводить свои расширения), DDL имеет существенные различия для разных продуктов. Каждый поставщик системы управления базой данных на физическом уровне различным образом реализует реляционную модель и каждый поставщик DDL неизбежно отражает эти различия. Большинство поставщиков предоставляют графические инструменты для определения данных и многие, включая и Microsoft, не ограничивают вас использованием только SQL DDL. Например, Microsoft предоставляет поддержку двух стандартов определения данных: ADO и DAO. Мы уже успели рассмотреть основные операторы DML: SELECT, INSERT, UPDATE и DELETE. Базовыми же операторами SQL DDL являются CREATE, ALTER и DROP, каждый из которых имеет несколько вариаций для создания объектов различных типов. Язык определения данных (Data Definition Language).
Не многие программисты создают базу данных программным путём, большинство из нас для этого используют некую визуальную среду наподобие MS Access для построения файла MDB. Но иногда нам всё таки приходится создавать и удалять базу данных, а так же объекты базы данных программным путём. Для этого используется наиболее распространённая на сегодняшний день технология Structured Query Language Data Definition Language (SQL DDL). Выраджения языка определения данных (DDL) - это SQL выражения, которые поддерживают определения или объявления объектов базы данных (например, CREATE TABLE, DROP TABLE, CREATE INDEX либо подобные им). Давайте взглянем на простейший пример выражения CREATE TABLE: CREATE TABLE PhoneBook (Name TEXT(50) Tel TEXT(50)); Данное DDL выражение (для MS Access) в время выполнения создаст новую таблицу с названием PhoneBook. Таблица PhoneBook будет иметь два поля: Name и Tel. Оба поля имеют строковый тип (TEXT) и размер поля 50 символов. Язык манипулирования данными Язык манипулирования данными - командный язык, обеспечивающий выполнение основных операций по работе с данными: ввод, модификацию и выборку данных по запросам. К базовым средствам манипулирования данными языка SQL относятся "поисковые" варианты операторов UPDATE и DELETE. Эти варианты называются поисковыми, потому что при задании соответствующей операции задается логическое условие, налагаемое на строки адресуемой оператором таблицы, которые должны быть подвергнуты модификации или удалению. Кроме того, в такую категорию языковых средств входит оператор INSERT, позволяющий добавлять строки в существующие таблицы.
Команды языка SQL
Основным инструментом выборки данных в языке SQL является команда SELECT. С помощью этой команды можно получить доступ к данным, представленным как вокупность таблиц практически любой сложности. Чаще всего используется упрощенный вариант команды SELECT, имеющий следующий синтаксис: SELECT <Список_выбора> [INTO <Новая_таблица>] FROM <Исходная_таблица> [WHERE <Условие_отбора>] [GROUP BY <Ключи_группировки>] [HAVING <Условие_отбора>] [ORDER BY <Ключи_сортировки> [ASC | DESC] ] Инструкция SELECT разбивается на отдельные разделы, каждый из которых имеет свое назначение. Обязательными являются только разделы SELECT и FROM, а остальные разделы могут быть опущены. Полный список разделов следующий: SELECT UNION INTO ORDER BY FROM COMPUTE WHERE FOR GROUP BY OPTION HAVING Основное назначение радела SELECT – задание набора столбцов, возвращаемых после выполнения запроса, т.е. внешнего вида результата. В простейшем случае возвращается столбец одной из таблиц, участвующих в запросе. В более сложных ситуациях набор значений в столбце формируется как результат вычисления выражения. Такие столбцы называются вычисляемыми, и по умолчанию им не присваивается никакого имени. При необходимости пользователь может указать для столбца, возвращаемого после выполнения запроса, произвольное имя. Такое имя называется псевдоним (alias). В обычной ситуации назначение псевдонима необязательно, но в некоторых ситуациях требуется явное его указание. Наиболее часто это требуется при работе с разделом INTO, в котором каждый из возвращаемых столбцов должен иметь имя, и это имя должно быть уникально. SELECT ALL Семестр, Отчетность FROM Учебный_план
SELECT TOP 5 * FROM Студенты – первые 5 строк
SELECT TOP 10 PERCENT * FROM Студенты – первые 10% от таблицы
SELECT Дисциплина.ID_Дисциплина as Дисц-на, Наименование, Семестр FROM Дисциплина, Учебный_план
SELECT Наименование, Семестр, Количество_часов FROM Учебный_план INNER JOIN Дисциплины ON Учебный_план.ID_Дисциплина=Дисциплины. ID_Дисциплина WHERE Количество_часов>60
SELECT Наименование, Семестр, Количество_часов FROM Учебный_план LEFT OUTER JOIN Дисциплины ON Учебный_план.ID_Дисциплина=Дисциплины.ID_Дисциплина
WHERE (Наименование LIKE ‘%информатик%)
Группировка данных: GROUP BY – выполняет группировку строк таблицы по определенным критериям. GROUP BY [ALL] <условие группировки> [,…,n]
ORDER BY – предназначен для упорядочения набора данных, возвращаемого после выполнения запроса.
SELECT TOP 10 * FROM Дисциплины ORDER BY Наименование
Добавление данных – команда INSERT Команда может быть использована использованы для вставки как одной, так и множества строк.
Имя таблицы – идентифицирует имя таблицы, в которую необходимо вставить строку данных. Список столбцов – задает имена столбцов, в которые будет производиться добавление данных. Пример: таблица «Сводная_ведомость». Каждая строка таблицы содержит результат сдачи экзамена (зачета) по отдельной дисциплине отдельным студентом. Если студент, ID_Студент которого равен 10, сдал экзамен со значением 3 в столбце ID_Дисциплина на оценку «пять», то команда добавления этих данных в таблицу «Сводная_ведомость» выглядит следующим образом:
INSERT Сводная_ведомость VALUES (10,3,5)
Изменение данных – команда UPDATE Для внесения изменений в данные таблиц служит команда UPDATE, позволяющая выполнить как простое обновление данных в столбце, так и сложные операции модификации данных во множестве строк таблица.
Имя таблицы – имя таблицы, в которой необходимо произвести изменение данных. SET – с этого ключевого слова начинается блок, в котором определяется список изменяемых столбцов. <имя столбца> = {<выражение> | DEFAULT | NULL } - для каждого изменяемого столбца нужно задать значение, которое он примет после выполнения изменения
UPDATE Учебный_план SET Количечство_часов = Количество часов + 2 WHERE (Отчетность = ’э’) AND (Семестр = 1) SELECT * FROM Учебный_план WHERE (Отчетность = ‘э’) AND (Семестр = 1)
Удаление данных – команда DELETE Удаление данных выполняется построчно. За одну операцию можно выполнить удаление как одной строки, так и нескольких тысяч строк.
DELETE <Имя_таблицы> [WHERE <Условие_отбора>]
SELECT Учебный_план WHERE (Отчетность = ‘э’) AND (Семестр = 1)
Создание таблицы – CREATE TABLE Создание таблицы выполняется при помощи команды CREATE TABLE.
CREATE TABLE Студенты (ID_Студент INTEGER NOT NULL, Фамилия CHAR(30) NOT NULL, Имя CHAR(15) NOT NULL, Отчество CHAR(20) NOT NULL, Номер_группы INTEGER NOT NULL, Адрес CHAR(30), Телефон CHAR(8), PRIMARY KEY (ID_Студент) );
Параметры: CREATE TABLE | DBF TableName1 Задает имя создаваемой таблицы. Опции TABLE и DBF идентичны. NAME LongTableName Задает длинное имя таблицы. Длинное имя таблицы можно задать только в том случае, когда имеется открытая база данных, поскольку длинные имена таблиц хранятся в базах данных. Длинное имя может содержать до 128 символов и применяться вместо коротких имен файлов в базах данных. FREE Указывает, что данная таблица не будет включена в открытую базу данных. Если открытых баз данных нет, параметр FREE можно не задавать. (FieldName1 FieldType [(nFieldWidth [, nPrecision])] Задает соответственно имя поля, его тип, ширину и точность (число десятичных знаков). FieldType это буква, обозначающая тип данных поля. Для некоторых типов полей необходимо задавать параметры nFieldWidth и/или nPrecision. В следующей таблице перечислены значения, используемые в качестве FieldType, и указывается, необходимо ли при этом задавать nFieldWidth и nPrecision. FieldType nFieldWidth nPrecision Описание типа данных C n - Символьное поле ширины n D - - Дата T - - Тип DateTime N n d Числовое поле ширины n, содержащее d десятичных знаков F n d Плавающее числовое поле ширины n, содержащее d десятичных знаков I - - Целочисленное B - d Двойной Y - - Тип Currency L - - Логический M - - Memo G - - General
Для типов D, T, I, Y, L, M, G и P аргументы nFieldWidth и nPrecision игнорируются. Если для типа N, F или B параметр nPrecision не задан, его значение по умолчанию принимается равным нулю (нет десятичных знаков). NULL Разрешает иметь в поле значения NULL. NOT NULL Запрещает значения NULL в поле. Если опущены параметры NULL и NOT NULL, то допустимость значений NULL в поле определяется текущей установкой SET NULL. Однако если вы, опустив NULL и NOT NULL, включили предложение PRIMARY KEY или UNIQUE, текущая установка SET NULL игнорируется и для данного поля по умолчанию принимается опция NOT NULL. CHECK lExpression1 Задает правило для поля. В качестве lExpression1 можно указать пользовательскую функцию. Учтите, что правило проверяется и при присоединении пустой записи. Если данное правило не допускает пустые значения полей в присоединяемой записи, генерируется ошибка. ERROR cMessageText1 Задает текст сообщения об ошибке, которое Visual FoxPro выдает при нарушении правила поля. Это сообщение выдается только в том случае, когда происходит изменение данных в окне просмотра или в окне редактирования. DEFAULT eExpression1 Задает значение, принимаемое в поле по умолчанию. Тип данных выражения eExpression1 должен совпадать с типом данных поля. PRIMARY KEY Создает для поля первичный индекс. Тег первичного индекса имеет то же имя, что и поле. UNIQUE Создает индекс-кандидат для поля. Имя тега индекса-кандидата совпадает с именем поля. Подробнее о индексах-кандидатах см. тему Использование индексов и главу 7 "Работа с таблицами" Руководства разработчика. Замечание. Индексы-кандидаты (создаваемые с помощью опции UNIQUE команды CREATE TABLE или ALTER TABLE - SQL) отличаются от индексов, создаваемых с помощью опции UNIQUE команды INDEX. В индексе, созданном по команде INDEX с опцией UNIQUE, разрешены повторяющиеся ключи индекса; в индексах-кандидатах повторение ключей индекса запрещено. Подробнее об опции UNIQUE см. тему INDEX. В поле, используемом для первичного индекса или для индекса-кандидата, не допускаются значения NULL и повторяющиеся записи. Однако если вы создали первичный индекс или индекс-кандидат для поля, поддерживающего значения NULL, Visual FoxPro не будет генерировать ошибку. Ошибка будет сгенерирована в том случае, когда вы попытаетесь ввести в поле, используемое для первичного индекса или индекса-кандидата, значение NULL или повторяющееся значение. REFERENCES TableName2 [TAG TagName1] Задает родительскую таблицу, с которой устанавливается постоянное отношение. Если опустить аргумент TAG TagName1, отношение устанавливается на основе ключа первичного индекса родительской таблицы. Если родительская таблица не имеет первичного индекса, Visual FoxPro генерирует ошибку. Задав TAG TagName1, вы установите отношение на базе существующего тега индекса родительской таблицы. Имя тега индекса может включать до 10 символов. В качестве родительской таблицы нельзя использовать свободную таблицу. NOCPTRANS Запрещает трансляцию содержимого символьных и memo-полей в другую кодовую страницу. Если таблица преобразуется в другую кодовую страницу, то поля, для которых задана опция NOCPTRANS, транслироваться не будут. NOCPTRANS можно указывать только для символьных и memo-полей. В следующем примере создается таблица MYTABLE, содержащая два символьных поля и два memo-поля. Для второго символьного поля CHAR2 и второго memo-поля MEMO2 задана опция NOCPTRANS, запрещающая их трансляцию. CREATE TABLE mytable (char1 C(10), char2 C(10) NOCPTRANS,; memo1 M, memo2 M NOCPTRANS)
PRIMARY KEY eExpression2 TAG TagName2 Задает первичный индекс, который предстоит создать. Выражение eExpression2 задает произвольное поле или комбинацию полей таблицы. TAG TagName2 задает имя создаваемого тега первичного индекса. Имя тега индекса может включать до 10 символов. Поскольку таблица может иметь только один первичный индекс, вы не можете задавать это предложение, если уже создали для данного поля первичный индекс. Visual FoxPro сгенерирует ошибку, если в команду CREATE TABLE включено более одного предложения PRIMARY KEY. UNIQUE eExpression3 TAG TagName3 Создает индекс-кандидат. Выражение eExpression3 задает произвольное поле или комбинацию полей таблицы. Однако если вы создали первичный индекс с помощью одной из опций PRIMARY KEY, вы не сможете использовать поле, заданное для первичного индекса. TAG TagName3 определяет имя создаваемого тега индекса-кандидата. Имя тега индекса может включать до 10 символов. У таблицы может быть несколько индексов-кандидатов. FOREIGN KEY eExpression4 TAG TagName4 [NODUP] Создает внешний (отличный от первичного) индекс и устанавливает отношение с родительской таблицей. eExpression4 задает выражение ключа внешнего индекса, а TagName4 имя создаваемого тега ключа внешнего индекса. Имя тега индекса может включать до 10 символов. Чтобы создать внешний индекс-кандидат, укажите ключевое слово NODUP. Вы можете создать для таблицы несколько внешних индексов, но при этом все выражения внешних индексов должны задавать разные поля в таблице. REFERENCES TableName3 [TAG TagName5] Задает родительскую таблицу, с которой устанавливается постоянное отношение. Задав TAG TagName5, вы установите отношение на базе тега индекса родительской таблицы. Имя тега индекса может включать до 10 символов. Если опустить TAG TagName5, отношение по умолчанию устанавливается с использованием ключа первичного индекса родительской таблицы. CHECK eExpression2 [ERROR cMessageText2] Задает правило таблицы. ERROR cMessageText2 задает сообщение об ошибке, которое Visual FoxPro выдает при выполнении правила таблицы. Это сообщение выдается только в том случае, когда происходит изменение данных в окне просмотра или в окне редактирования. FROM ARRAY ArrayName Задает имя существующего массива, содержимое которого представляет собой значение имени, типа, ширины и масштаба для каждого поля таблицы. Подобный массив можно определить с помощью функции AFIELDS( ).
Комментарии: Новая таблица открывается в рабочей области с наименьшим из имеющихся номеров; к этой таблице можно обращаться по псевдониму. Новая таблица открывается для монопольного пользования, независимо от текущей установки SET EXCLUSIVE.
Если имеется открытая база данных, а предложение FREE не задано, новая таблица включается в базу данных. Нельзя создать новую таблицу с именем, которое уже принадлежит некоторой таблице базы данных.
Если база данных открыта, то для команды CREATE TABLE - SQL требуется монопольное пользование этой базой данных. Чтобы открыть базу данных в монопольном режиме, включите предложение EXCLUSIVE в команду OPEN DATABASE.
Если в момент создания новой таблицы нет открытой базы данных, то включение предложения NAME, CHECK, DEFAULT, FOREIGN KEY, PRIMARY KEY или REFERENCES вызовет ошибку.
SQL. Изменение структуры таблицы.
Как бы тщательно не планировалась структура таблицы, иногда возникает необходимость внести в нее некоторые изменения. Предположим, что в уже сформированную таблицу «Преподаватели» необходимо добавить номер домашнего телефона и домашний адрес. Эту операцию можно выполнять различными путями. Например, можно удалит таблицу со старой структурой и создать вместо нее новую таблицу с нужной структурой. Недостатком этого метода является то, что необходимо будет куда-то скопировать имеющиеся в таблице данные и переписать их в новую таблицу после ее создания. Специальная команда ALTER TABLE предназначена для модификации структуры таблицы. С ее помощью модно изменять свойства существующих столбцов, удалять или добавлять в таблицу столбцы, а также управлять ограничением целостности как на уровне столбца, так и на уровне таблицы, т.е. выполнять следующие функции: • добавить в таблицу определение нового столбца;
• удалить столбец из таблицы;
• изменить значение по умолчанию для какого-либо столбца;
Команда ALTER TABLE берет на себя все действия по копированию данных во временную таблицу, удалению старой таблицы и созданию вместо нее новой таблицы с нужной структурой и последующим переписыванием в нее данных. Назначение многих параметров и ключевых слов команды ALTER TABLE аналогично назначению соответствующих параметров и ключевых лов команды CREATE TABLE (например, синтаксис конструкции <определение_столбца> совпадает с синтаксисом аналогичной конструкции CREATE TABLE).
Основные режимы использования команды ALTER TABLE следующие: • добавление столбца;
• удаление столбца;
• модификация столбца;
• изменение, добавление и удаление ограничений (первичных и внешних ключей, значений по умолчанию).
ALTER TABLE Студенты ADD Год_поступления INTEGER NOT NUL DEFAULT YEAR(GETDATE())
В структуру таблицы «Студент» будет добавлен еще один столбец со значением по умолчанию, равным текущему году.
