Базы данных Тема 2. Основы SQL. Создание базы данных
Язык SQL Историческое введение n n 1974 г. , Д. Чемберлен, IBM – SEQUEL 1976 г. , SEQUEL/2 -> SQL, IBM, СУБД System R 1982 г. , IBM, СУБД DB 2 1987 г. , 1989 г. , стандарт SQL
Язык SQL n n n В 1974 году Д. Чемберлен из исследовательской лаборатории IBM в Сан-Хосе (из той, где работал и Е. Ф. Кодд) опубликовал определение языка, который получил название "Structured English Query Language" (SEQUEL). В 1976 году была выпущена переработанная версия этого языка — SEQUEL/2. Впоследствии язык был назван SQL, т. к. аббревиатура SEQUEL была уже использована. В 1976 году IBM создала одну из первых СУБД System R. Специально для этой СУБД был разработан язык SQL, в основу которого были положены языки SEQUEL/2 и SQUARE (Specifying Queries as Rational Expressions). Язык SQUARE был разработан как исследовательский инструмент для реализации реляционной алгебры посредством фраз, составленных на английском языке. В 1982 году Национальный институт стандартизации США (ANSI) начал работу над языком RDL (Relation Database Language), руководствуясь документами, полученными от корпорации IBM. В 1983 году к этой работе подключилась Международная организация по стандартизации (ISO). От названия RDL впоследствии (1984) отказались и вернулись к названию SQL. Вариант стандарта языка SQL был выпущен в 1987 году. В адрес этого варианта было высказано довольно много замечаний. В частности, указывалось на отсутствие в языке поддержки ссылочной целостности и чрезмерную избыточность языка. С учетом замечаний разработчики выпустили первый стандарт языка SQL. В 1989 году ISO опубликовала дополнения к стандарту, в которых, в частности, определялась поддержка функций целостности. Именно 1989 год считается годом рождения первого стандарта SQL. С появлением первого стандарта языка SQL закончился первый период языка и начался второй.
Язык SQL Стандарты SQL n n n Стандарт 1989 года Стандарт 1992 года (SQL 92 или SQL 2) Стандарт 1999 года (SQL 99 или SQL 3)
Язык SQL n К основным стандартам языка SQL относятся следующие. Ø Стандарт 1989 года. Обеспечивал минимальную функциональность, имевшуюся в то время у большинства реляционных СУБД. Содержал поддержку функций целостности реляционных баз данных. Ø Стандарт 1992 года, иногда называемый SQL 92 (или SQL 2). Данный стандарт не только обобщал и закреплял уже существующие возможности языка, но содержал большое количество возможностей, обеспечивающих дальнейшее развитие языка и еще нигде не реализованных. Поскольку соответствие стандарту — это всего лишь пожелание, стандартизующие организации определили три уровня соответствия: o начальный (Entry), имеет минимальные отличия от стандарта 1989 года; o промежуточный (Intermediate), значительно отличается от стандарта 1989 года; o полный (Full), требует от разработчиков всех возможностей стандарта SQL 2. Ø Ø Некоторые возможности, заложенные в этом стандарте (уровень Full), до сих пор не реализованы многими известными разработчиками СУБД. В результате, когда говорят о соответствии стандарту SQL 92, всегда должно быть уточнение — уровень стандарта. Стандарт 1999 года (SQL 99 или SQL 3). Стандарт дополняет стандарт 1992 года. Определяет интеграцию с объектно-ориентированным подходом, программные расширения (события, триггеры и т. п. ) и другие возможности. 2003 год. В стандарт был введен ряд дополнений. В частности: тип данных XML, функции окна, специальные типы столбцов и т. д. 2006 год. Был введен еще ряд существенных дополнений, связанных с типом данных XML: хранение XML-объектов и их импорт, использования языка XQuery и т. д.
Язык SQL Схема выполнения команды SQL n n Синтаксический разбор SQL-инструкции. Проверка на предмет того, существуют ли указанные в команде имена таблиц, столбцов и других объектов базы данных. Генерация и оптимизация плана выполнения команды. В результате генерации будет получена последовательность двоично-кодированных элементарных команд над данными. Выполнение SQL-команды согласно плану выполнения.
Язык SQL n n n Команда SQL выражает собой запись результата, который должен быть получен после выполнения команды. В ней отсутствует алгоритм того, как и в какой последовательности следует выполнять операции реляционной алгебры, чтобы получить такой результат. Следует также учесть и то, что и сами операции реляционной алгебры не элементарны и для их выполнения требуется выполнить некоторое количество элементарных операций над данными. Таким образом, команда SQL должна быть преобразована к некоторой последовательности элементарных операций над данными. Одна и та же инструкция SQL может быть преобразована к разным последовательностям элементарных команд. Основным критерием того, какая последовательность будет использована, является время выполнения команды. На слайде представлены этапы выполнения SQL-инструкции. Обратите внимание на очень важное понятие— "план выполнения". Если сохранить план в базе данных, то при повторном выполнении команды он может быть использован, и, тем самым, ускорено выполнение. Выбором наиболее оптимального плана выполнения занимается часть СУБД, называемая оптимизатором запросов. Существуют два подхода к оптимизации: эвристический и стоимостный. Эвристический подход к оптимизации заключается в том, что заранее известно, как выполняются те или иные элементарные операции над данными. Следовательно, можно сделать некоторую оценку того, как быстро будет выполняться та или иная последовательность элементарных операций, и выбрать наиболее оптимальный вариант. В действительности эвристический подход не столь эффективен, как это может показаться. Так как многое зависит от того, как устроена обрабатываемая база данных: какие индексы созданы, количества уровней в индексах, количества записей в таблицах и как они расположены в таблице и другие параметры. Стоимостный подход предполагает сравнение планов выполнения команды по некоторому числовому параметру, называемому стоимостью команды. Расчет же стоимости для каждого из планов осуществляется на основе некоторого набора статистических параметров, относящихся к таблицам и индексам. Кроме того, в стоимость должны входить такие параметры, как свободная память, количество процессоров и их быстродействие и другие аппаратные характеристики. Реальные СУБД при выборе плана выполнения опираются и на эвристический, и на стоимостный подходы.
Язык SQL Формы языка SQL n n n Интерактивный SQL; Статический SQL: Ø Встроенный SQL; Ø Модульный; Динамический SQL.
Язык SQL n n n Интерактивный SQL позволяет конечному пользователю в интерактивном режиме выполнять SQL-запросы и просматривать результаты их выполнения. Все СУБД предоставляют инструментальные средства для работы с БД в интерактивном режиме. Для СУБД My. SQL «родным» (входящим в комплект установки) инструментом интерактивного SQL является утилита командной строки, которую можно вызвать, выбрав пункт меню My. SQL Command Line Client в группе программ My. SQL. Также можно использовать инструменты с графическим интерфейсом (например, EMS SQL Manager for My. SQL). Графические инструменты предоставляют возможность как непосредственно вводить текст SQL-запроса в редакторе, так и визуально составлять запросы с помощью построителя (конструктора). Применение языка SQL в прикладных программах на практике реализовано двумя различными способами. 1. Внедренные SQL-запросы. Отдельные SQL-запросы внедряются прямо в исходный текст программы и смешиваются с операторами базового языка. Это подход позволяет создавать программы, обращающиеся непосредственно к БД. Специальные программы-предкомпиляторы преобразуют исходный текст с целью замены SQL-запросов соответствующими вызовами подпрограмм СУБД, затем он компилируется и собирается обычным способом. 2. Использование прикладного интерфейса программирования (API – Application Programming Interface), позволяющего реализовывать работу с базой данных через предоставляемый набор функций. API может быть целевым, предоставленным производителем коммерческой СУБД для работы именно с этой СУБД, или межплатформенным, реализующим унифицированные средства доступа к СУБД различных производителей. Конкретный вариант API может предоставлять тот же набор функциональных возможностей, который существует при использовании встроенный операторов, однако при этом устраняется необходимость предкомпиляции исходного текста.
Язык SQL n n n Прикладной API включает набор библиотечных функций, предоставляющих программисту разнообразные типы доступа к БД, а именно: подключение, выполнение различных SQL-запросов и т. д. Внедрение SQL-запросов в текст программы предполагает использование операторов как статического, так и динамического SQL. Статический SQL может реализовываться как встроенный SQL или модульный SQL. Во встроенном SQL можно использовать переменные основного языка программирования. Запросы статического SQL определены уже в момент компиляции программы, какого-либо изменения после их однократного написания не предполагается. Они могут храниться как в файлах, предназначенных для дальнейшего использования, так и в виде хранимых процедур БД. Динамический SQL позволяет формировать SQL-запросы во время выполнения программы. Запросы динамического SQL формируются как строковые переменные (например, Ab: =‘SELECT * FROM Abonent’; ). Для динамического формирования оператора можно выполнять последовательное объединение строк. Динамический SQL дает возможность программисту или конечному пользователю создавать операторы во время выполнения программы и передавать их базе данных, которая после выполнения этих операторов помещает выходные данные в переменные программы.
Язык SQL Расширения SQL. Диалекты • PL/SQL (Procedural Language/SQL) — СУБД Oracle • PLpg/SQL (Procedural Language/postgre. SQL) — СУБД Postgre. SQL • SQLPL (SQL Procedural Language) — СУБД DB 2 ( IBM) • Transact-SQL (или T-SQL) — MS SQL Server и Sybase Adaptive Server
Язык SQL n n Расширения языка SQL, которые разрабатываются практически всеми производителями СУБД, призваны дополнить язык управления реляционными базами данных возможностями, присущими алгоритмическим языкам программирования — в первую очередь возможностями управлять ходом выполнения программы (операторы цикла, условные конструкции и др. ). Расширения языка SQL позволяет создавать на стороне базы данных (на стороне сервера баз данных) полноценные программные модули. Перенос части программного обеспечения на сторону СУБД позволяет оптимизировать работу информационной системы в сети. Стандарты языка SQL не поспевают за потребностями рынка СУБД, так что производители СУБД создавали соответственно свои версии расширений SQL. В свою очередь и сама интерактивная часть SQL имеет свои особенности в разных СУБД (диалекты SQL). Наиболее известные диалекты SQL представлены на слайде. Впервые язык PL/SQL появился в шестой версии СУБД Oracle в 1988 году. PLpg/SQL (Postgre. SQL) имеет много схожего с диалектом PL/SQL (Oracle), хотя обладает несколько меньшими возможностями. Диалект SQLPL (DB 2) реализован после появления стандарта и поэтому ближе остальных подходит к стандарту. СУБД MS SQL Server и Sybase Adaptive Server все дальше отходят друг от друга, отличаются друг от друга и реализации диалекта T-SQL (т. е. фактически мы имеем уже два диалекта T-SQL). Расширения языка SQL на основе отличающихся друг от друга диалектов SQL делает их малопригодными для переноса серверного программного обеспечения с одной платформы на другую. Стандарт SQL 1999 года предлагает возможные пути расширения SQL до уровня алгоритмического языка. Но здесь опять возникает известная проблема— стандарт появился, когда расширения уже существовали. Как разработчики СУБД будут выполнять требования стандарта — зависит от предыстории их продуктов и их коммерческих планов.
Язык SQL Описание языка SQL Типы данных
Язык SQL Описание языка SQL Типы данных Приведение типов CAST ({выражение для вычисления значения | NULL} AS {тип данных / домен} Пример SELECT CAST(number as INT) FROM tablel
Язык SQL n n n n Будем рассматривать язык SQL, опираясь на его стандарты (в основном на стандарт 1992 года) и приводя примеры из реализаций этого языка в конкретных СУБД. В таблице представлены типы данных, которые должны поддерживаться SQL по стандарту 1999 года. Типы данных, представленные в таблице, являются всего лишь типами, рекомендуемыми разработчикам СУБД. На практике разработчики реализовали далеко не все представленные типы. Кроме того, разработчики внедряют свои типы данных, иногда являющиеся аналогом стандартных типов, а иногда и вовсе на них не похожие. При работе с типами данных как в языках программирования важнейшим вопросом является преобразование или приведение типов. Можно выделить три уровня приведения типов данных. Безусловное приведение. В этом случае приведение возможно всегда без ограничения. Примером такого приведения служит преобразование числового типа с заданной точностью к числовому типу, имеющему болшую точность. Допустим, мы имеем тип данных INT (32 бита) и преобразуем его к типу данных BIGINT (64 бита). Приведение с ограничением. Предполагается, что приведение возможно при выполнении некоторых условий. Например, преобразование данного имеющего тип BIGINT, К типу данных INT возможно только при условии, что количество значимых разрядов в данном не превосходит 32. Невозможность приведения. Речь о несовместимых типах. Приме может служить попытка преобразовать числовой тип в тип DATE. В стандарте предусмотрена возможность явного преобразования типов при помощи оператора CAST. ЭТОТ оператор следует использовать вместо пpeобразуемого выражения. Сравнение строк осуществляется посимвольно в соответствии с их кодами. Если длины строк различны, то более короткая строка дополняется справа пробелами до длины более длинной строки. Результат сравнения двух строк может зависеть от их кодировки.
Язык SQL Описание языка SQL Элементы языка SQL • Числовые константы Ø Целые и десятичные константы: 21, -34 667, 2000. 45, +444. 22 Ø Константы с плавающей запятой: 1. 6 Е 4, -3. 345 Е-1, 0. 5678 Е 2 Ø Денежные константы : $0. 34, $1000 • Строковые константы 'Фамилия', 'Номерпартии', "Фамилия пациента" • Константы даты и времени Ø форматы констант для типа данных date: 'mm/dd/yyyy' (американский формат), ' yyyy-mm-dd' (формат ISO) и т. д. Øформаты констант для типа данных tамe: 'hh mm ss' 'hh: mm: ss' и т. д. • Системные или именованные константы USER
Язык SQL n n n n В некоторых СУБД вводятся дополнительные типы (и числовые константы) для хранения денежных величин. В соответствии со стандартом строковые константы должны заключаться в одинарные кавычки: 'Фамилия', 'номерпартии'. Двойные кавычки предусмотрены для написания имен, в которых имеются пробелы. Например "Фамилия пациента" — имя столбца таблицы. Двойные кавычки позволяют использовать такое имя в выражениях. Константы для хранения даты, времени и интервала представляются в виде строковых констант. Как правило, СУБД поддерживают несколько форматов такого представления. В стандарте SQL определена только одна именованная константа USER, однако в конкретных СУБД их может быть гораздо больше. Прежде чем использовать константу для конкретной СУБД, следует внимательно ознакомиться с документацией. Согласно стандарту в выражениях можно использовать четыре арифметических действия: +, - , *, /. Предполагается, что в выражении могут присутствовать числа различного типа и точности. При этом приведение типов осуществляется автоматически (целые числа в десятичные, десятичные в числа с плавающей точкой). Встроенные функции имеются во всех реляционных СУБД. Они необходимы для выполнения математических вычислений (SIN(X), ЕХР 2(Х) И т. п. ), получения информации о компонентах даты и времени (MONTH (d), YEAR(d)), получении системной информации, выполнения строковых операций и многого другого. Начиная с 1992 года, в стандарт языка SQL включаются и встроенные функции. К сожалению, разработчики не всегда придерживаются списка стандартных функций и добавляют в диалект множество своих. Прикладной программист всегда может получить описание встроенных функций в документации по конкретной СУБД
Язык SQL Описание языка SQL Типы данных My. SQL Строковые типы Тип столбца Необходимый объем памяти CHAR(M) М байтов, 0≤М≤ 255 VARCHAR(M) L+1 байтов, где L≤M и 0≤М≤ 65 535 BINARY (М) М байтов, 0≤М≤ 255 VARBINARY(W) L+1 байтов, где L≤М и 0≤М≤ 255 TINYBLOB, L+1 байтов, где L<28 TINYTEXT BLOB, TEXT L+2 байтов, где L < 216 MEDIUMBLOB, L +3 байтов, где L < 224 MEDIUMTEXT LONGBLOB, L +4 байтов, где L < 232 LONGTEXT ENUM( 'value 1 ' , 1 или 2 байта в зависимости от числа значений (65 535 'value 2 ', . . . ) значений максимум) SET( 'value 1 ' , 'value 2 1 , 2, 3, 4 или 8 байтов в зависимости от числа значений (64 ', . . . ) значения максимум)
Язык SQL n n n Типы CHAR (строки фиксированной длины) и VARCHAR (строки произвольной длины) – это типы, предназначенные для записи коротких фрагментов текста. Длину каждого из них можно регулировать числом M. Записи в столбцы типа CHAR будут дополняться пробелами до максимальной длины. При этом длина строки не зависит от размеров данных, в то время как в столбцах с типом VARCHAR размер поля зависит от размеров данных. My. SQL удаляет пробелы в конце текстовых строк у char во время извлечения и у VARCHAR во время сохранения. Тип VARCHAR позволяет экономить память, но при хранении данных приходится указывать, сколько памяти занимает введенное значение. Это значение сервер использует для того, чтобы определить, с какой позиции начнется запись следующего значения. При записи данных типа CHAR серверу не приходится указывать в каждом поле реальную длину строки, а при извлечении данных считывать это значение для того, чтобы определить, где начнется следующее значение поля, поэтому сервер работает с такими строками быстрее, зато используется несколько больший объем памяти. Если вы при определении таблицы зададите тип данных в поле фамилии как CHAR (10), а потом попробуете ввести более длинную фамилию, то вводимый текст будет усечен до указанной в определении таблицы длины. Тип BLOB (Binary Large Object, большой двоичный объект) используется для хранения двоичных данных. Данные типа ENUM могут принимать одно из нескольких заранее заданных значений: ENUM (value 1, value 2, . . . ). Например, предусмотрено, что в некотором столбце должен храниться ответ на вопрос, а допустимыми значениями ответа являются "да" и "нет". Тогда value 1 будет "да", a value 2 – "нет". Тип SET предусматривает, что в поле одновременно может содержаться несколько из заранее заданного списка значений.
Язык SQL Описание языка SQL Типы данных My. SQL Типы значений даты и времени Тип столбца Описание Диапазон значений DATETIME YYYY-MM-DDHH : MM : SS '1000 -01 -01 00: 00' — '9999 -12 -31 23: 59' DATE YYYY-MM-DD '1000 -01 -01' — '9999 -12 -31' TIMESTAMP YYYY-MM-DD HH: MM: SS '1970 -01 -01 00: 00' — '2037 -12 -31 23: 59' TIME HH: MM: SS '-838: 59' — '838: 59' YEAR YY ИЛИ YYYY 1970 -2069 или 1901 -2155 Отсчет времени в My. SQL осуществляется с начала эпохи UNIX – 1 января 1970 г.
Язык SQL Описание языка SQL Типы данных My. SQL Типы числовых значений Тип TINYINT SMALLINT MEDIUMINT INT BIGINT Память в байтах 1 2 3 4 8 Минимальное значение Signed -128 -32 768 -8 388 608 -2147483648 -263 Unsigned 0 0 0 Память в Минимальное значение байтах Signed Unsigned 1 -128 0 2 -32 768 0 3 -8 388 608 0 4 -2147483648 0 8 -263 0 Максимальное значение Signed Unsigned 127 255 32767 65535 8 388 607 16777215 2147483647 4294967295 263 -1 264 -1
Язык SQL n n Данные типа FLOAT или DOUBLE округляются при записи до указанной в определении столбца точности. l. Тип DECIMAL предназначен для хранения данных, которые не могут быть округлены, например, денежных величин. В My. SQL версии 5. 0. 2 и более ранних данные этого типа хранятся в виде текста. Начиная с версии 5. 0. 3, данные типа DECIMAL хранятся в двоичном виде, в котором десять двоичных цифр записываются в 4 -х байтах.
Язык SQL Подмножество DDL (SQL) Создание базы данных • На уровне СУБД под базой данных понимают некоторый объектконтейнер, содержащий: Ø Таблицы ; Ø Домены; Ø Пользовательские типы данных; Ø Утверждения (ASSERTION); Ø Наборы символов (CHARACTER SET); Ø Сравнения (COLLATION, определяют последовательность сортировки); Ø Схемы, трансляции (определяют трансляцию текста одного набора символов в другой); Ø Представления (VIEWS); Ø Хранимые процедуры и пользовательские функции; Ø Пользователи, группы и роли.
Язык SQL n n n n Команды DDL могут сильно отличаться друг от друга в различных реализациях языка SQL. Не все объекты баз данных, о которых говорит стандарт, реализованы в конкретных СУБД. Так объект SCHEMA, О котором говорится в стандарте, появился в MS SQL Server только в реализации 2005 года. Во многих СУБД отсутствует объект DOMAIN, который является одним из основополагающих понятий реляционной модели. Но так или иначе основными командами подмножества DDL являются команды: CREATE — создать объект, ALTER — изменить струткуру объекта, DROP — удалить объект. Ранее мы дали определение понятию "база данных", исходя уровня прикладного программирования. На уровне СУБД под базой данных понимают некоторый объект-контейнер, основным содержимым которого являются таблицы. Кроме того, в базе данных по стандарту существуют (могут существовать) такие объекты, как домены, пользовательские типы данных, утверждения (ASSERTION, При помощи ASSERTION можно, например, сформулировать ссылочные ограничения целостности или существенно расширить их), наборы символов (CHARACTER SET), сравнения (COLLATION, определяют последовательность сортировки), схемы трансляции (определяют трансляцию текста одного набора символов в другой), представления (VIEWS), хранимые процедуры и пользовательские функции, пользователи, группы и роли. Такие объекты, как триггеры, ограничения, индексы и др. , хоть и являются объектами базы данных, но привязываются к конкретным таблицам. Все перечисленные выше объекты могут создаваться с помощью команды CREATE В конкретной базе данных. Информация об этих объектах хранится в специальных системных таблицах, доступ к которым можно получить при помощи обычной команды выборки. Содержимое этих таблиц, в сущности, и составляет системный каталог. В различных СУБД по-разному решается проблема создания базы данных. В большинстве случаев в них реализованы команды: CREATE DATABASE — для создания базы данных, ALTER DATABASE — для изменения настроек базы данных, DROP DATABASE — для удаления базы данных. Параметры указанных команд могут сильно различаться в разных СУБД, так что мы на них останавливаться не будем. Кроме этого, в менеджерах, предоставляющих визуальные средства управления данными, имеется интерактивный способ создания и настройки параметров базы данных.
![Язык SQL Подмножество DDL (SQL) Создание таблиц CREATE [{GLOBAL | LOCAL} TEMPORARY] TABLE имя_таблицы](https://present5.com/presentation/-42593636_287293714/image-25.jpg "Язык SQL Подмножество DDL (SQL) Создание таблиц CREATE [{GLOBAL | LOCAL} TEMPORARY] TABLE имя_таблицы")
Язык SQL Подмножество DDL (SQL) Создание таблиц CREATE [{GLOBAL | LOCAL} TEMPORARY] TABLE имя_таблицы ( {определение_столбца | [ограничение_таблицы] }. . . [ON COMMIT {DELETE | PRESERVE } ROWS ] ) где: определение столбца : : = имя столбца {имя домена | тип данных [размер]} [ограничение столбца. . . ] [DEFAULT значение по умолчанию] [COLLATE имя сравнения]
Язык SQL n n n • Командам создания и модификации структуры таблицы мы уделим особое внимание, поскольку таблица является основным объектом базы данных. Команда CREATE TABLE позволяет создать таблицу в текущей или указанной базе данных. На слайде представлен синтаксис этой команды. Рассмотрим параметры команды, определяющие характер таблицы. Ø TEMPORARY — будет создана временная таблица. По завершению соединения такая таблица будет автоматически уничтожена. Временные таблицы удобны для хранения промежуточных результатов. Программисту не надо заботиться об уничтожении таких таблиц. Ø LOCAL — параметр используется для временных таблиц. Означает, что таблица локальна, т. е. доступна только для данного соединения. Ø GLOBAL — параметр используется для временных таблиц. Означает, что таблица глобальна, т. е. доступна из других соединений (для других пользователей). Ø ON COMMIT DELETE ROWS — параметр относится к временным таблицам. Означает, что по окончании текущей транзакции из таблицы удаляются строки. Ø ON COMMIT PRESERVE ROWS — параметр относится к временным таблицам. Означает, что по окончании текущей транзакции все строки таблицы сохраняются. Данная опция действует по умолчанию. Как обычно, конкретные СУБД вносят свои поправки в стандартные команды.
Язык SQL Подмножество DDL (SQL) Создание таблиц • Пример 1. CREATE TABLE table 2 ( id INT NOT NULL DEFAULT 0 PRIMARY KEY, "name" NCHAR(20) NOT NULL DEFAULT ' ' ) • Пример 2. Создание таблицы с составным первичным ключом (ограничение таблицы) CREATE TABLE t 1 ( id INT NOT NULL DEFAULT 0, NUMBER INT NOT NULL DEFAULT 0, SUMM FLOAT DEFAULT 0. 00 PRIMARY KEY (id, NUMBER) )
Язык SQL n n n Обратимся к конкретному примеру использования оператора CREATE TABLE. НА СЛАЙДЕ (ПРИМЕР 1) представлена команда создания простейшей таблицы. Параметры, определяющие характеристики столбца, располагаются сразу за именем столбца через пробел. Сразу после имени указывается тип столбца. Так, NOT NULL означает, что значение, которое будет храниться в столбце, не может принимать NULL. Ключевое слово DEFAULT используется для определения значения по умолчанию. Например, для столбца id значением по умолчанию является 0. Тип данных, хранящихся в столбце, определяется набором типов, которые поддерживает конкретная СУБД. В тех СУБД, в которых поддерживаются домены, вместо типа данных можно использовать имя домена. Также для столбца целого типа можно указать свойство, позволяющее автоматическую генерацию значения. К сожалению, в различных СУБД это свойство может обозначаться по-разному: в MS Access такое поле имеет тип Счетчик, в Postgre. SQL — SERIAL, в MS SQL Server — IDENTITY И Т. д. Кроме того, для генерации более сложных уникальных ключей в качестве значения по умолчанию можно использовать встроенные функции СУБД (например, NEWID () в MS SQL Server). Обратите внимание, что имя столбца name взято в кавычки. Мы предполагаем, что name может быть ключевым словом для данной СУБД, и пользуемся стандартным приемом — заключение имени в двойные кавычки. Для столбца id мы указано PRIMARY KEY, Т. е. данный столбец определен как первичный ключ. Если используется полное имя в формате имя_базы_данных. имя_схемы. имя_таблицы, то таблица будет создана именно в указанной базе данных и указанной схеме, иначе (как, в примере) будет использоваться текущая база данных и текущая схема (схема, владельцем которой является данный пользователь).
Язык SQL Подмножество DDL (SQL) Создание таблиц • Пример 3. Ограничение на значение столбца CREATE TABLE names ( id INT PRIMARY KEY , N_MONTH INT CONSTRAINT CHECK 1 CHECK (N_MONTH>0 AND N_MONTH<13) ) • Пример 4. Определение внешнего ключа (один столбец) CREATE TABLE names ( id INT PRIMARY KEY, id 1 INT REFERENCES dbo. Студенты(id) ON DELETE NO ACTION ON UPDATE NO ACTION, "name" NCHAR(20) )
Язык SQL Подмножество DDL (SQL) Создание таблиц • Пример 5. Определение составного внешнего ключа CREATE TABLE t 2 ( id INT NOT NULL DEFAULT 0, N INT NOT NULL DEFAULT 0, SUMM FLOAT DEFAULT 0. 00 FOREIGN KEY (id, N) REFERENCES t 1 ON DELETE CASCADE ON UPDATE CASCADE )
Язык SQL n n Пример 3 демонстрирует, как для столбца может быть указано ограничение. В нашем примере используется именованное ограничение (CONSTRAINT CHECK 1). В скобках указывается предикат, который и определяет ограничение, накладываемое на величины, хранящиеся в столбце. При желании можно использовать и другие предикаты. Например, в нашем случае можно было бы записать и так: CONSTRAINT CHECKI 1 CHECK (N_MONTH IN (1, -2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)). Важнейшим ограничением является требование ссылочной целостности. Если внешний ключ состоит всего из одного столбца (что обычно и бывает), то определить его можно вместе с другими характеристиками столбца (Пример 4). Обратите внимание, что при определении внешнего ключа определили также и сценарий для операций DELETE и UPDATE (ON DELETE NO ACTION ON UPDATE NO ACTION). В Примере 5 представлена команда создания таблицы с составным внешним ключом (по отношению к таблице t 1, см. Пример 2 ). Ограничения (CHECK), значение по умолчанию, первичный и внешние ключи рассматриваются СУБД как объекты, привязанные к конкретной таблице. Всем может быть присвоено имя с помощью ключевого слова CONSTRAINT. В противном случае СУБД сама присваивает некоторое уникальное имя.
Язык SQL Подмножество DDL (SQL) Создание БД и таблиц в My. SQL
Язык SQL n n n n Рассмотрим работу фирмы, владеющей несколькими такси и нанимающей водителей для работы на них. Руководство фирмы решило хранить информацию об автомобилях и водителях, работающих в компании в базе данных. Первая таблица cars содержит сведения об автомобилях: модель; год выпуска; государственный регистрационный номер; цвет; учетный номер автомобиля в автопарке. Вторая таблица drivers предназначена для информации о водителях: имя; отчество; фамилия; дата рождения; домашний адрес; дата приема на работу; учетный номер водителя. Таблица timetable содержит данные о расписании использования того или иного автомобиля: дата; учетный номер автомобиля в автопарке; учетный номер водителя. Таблица cars связана с таблицей timetable. Тип связи "один-ко-многим" (1: N). Одна и та же машина, описанная в таблице cars, может несколько раз упоминаться в таблице timetable. Аналогично каждый водитель из таблицы drivers может несколько раз упоминаться в таблице timetable. Учетный номер автомобиля в автопарке и учетный номер водителя являются первичными ключами в таблицах cars и drivers и внешними ключами в таблице timetable. Сервер проверяет соответствие значений в столбцах, по которым происходит связь. Вы не сможете ввести такое значение в поле внешнего ключа, которое отсутствует в поле первичного ключа. То есть надо сначала принять водителя на работу и присвоить ему номер, а уж потом использовать этот номер в расписании автопарка.
Язык SQL Подмножество DDL (SQL) Создание БД и таблиц в My. SQL •
Язык SQL n n n n Создадим базу данных описанной выше фирмы с использованием клиентской программы mysql. Создадим базу данных taxi таксопарка командой CREATE DATABASE. Команда должна завершаться точкой с запятой (этот символ называется разделителем – delimiter), получив команду, сервер выполняет ее и выдает сообщение (при успехе – Query OK, 1 row affected). При создании базы данных она автоматически не выбирается, выбирать ее нужно отдельно. Для этого надо дать команду использовать только что созданную базу данных: mysql>USE taxi; Создавать базу нужно только однажды, но выбирать ее приходится в каждом сеансе работы с mysql. Создадим таблицы базы данных taxi. Атрибут NOT NULL означает, что все строки таблицы должны иметь значение в этом столбце. Если NOT NULL не указано, поле может быть пустым (NULL). PRIMARY KEY после имени столбца определяет, что этот столбец является первичным ключом для таблицы. Данные в этом столбце должны быть уникальными. AUTO_INCREMENT – атрибут, используемый для создания столбца с уникальными значениями. Если при вставке строк в таблицу оставлять такое поле пустым, My. SQL автоматически генерирует уникальное значение идентификатора. Это значение будет на единицу больше максимального значения, уже существующего в столбце. В каждой таблице может быть не больше одного такого поля. My. SQL использует следующий алгоритм для инициализации счетчика для столбца id, имеющего атрибут AUTO_INCREMENT: после запуска сервера My. SQL при первом запросе на добавление данных в таблицу cars сервер высчитывает максимальное значение в столбце id. Полученное значение увеличивается на единицу, заносится в новую запись и в счетчик. Если таблица была пуста, то счетчик устанавливается в единицу.
Язык SQL Подмножество DDL (SQL) Создание БД и таблиц в My. SQL •
Язык SQL n n n n При создании таблицы для некоторых полей могут применяться дополнительные ключевые слова, уточняющие диапазон возможных значений. Ключевое слово UNSIGNED может применяться после указания целочисленного типа и означает, что его значение может быть либо положительным, либо нулевым. DEFAULT – после этого ключевого слова указывается значение по умолчанию для данного столбца. Например: DEFAULT 'no description‘ My. SQL поддерживает ссылочную целостность базы с помощью ограничения внешнего ключа foreign key. Назначение этого ключа — проверять соответствие значений в столбцах родительского и внешнего ключей. Эти столбцы должны иметь одинаковый тип данных, и только те значения, которые встречаются в родительском ключе, могут использоваться во внешнем ключе. Родительский ключ должен содержать неповторяющиеся значения и не содержать значений NULL. Последние два столбца таблицы timetable являются внешними ключами, и My. SQL 5 требует, чтобы перед созданием внешних ключей на этих столбцах были созданы индексы. Индекс формируется из значений одного или нескольких столбцов таблицы и позволяет находить нужную строку по заданному значению. Для ускорения запросов индексы обычно создаются на тех столбцах таблицы, которые часто используются в запросах. После создания таблиц можно просмотреть их список и структуру. Посмотреть список таблиц в базе можно командой: mysql> SHOW TABLES; а структуру таблицы – mysql> DESCRIBE table_name; Здесь tabie_name – имя таблицы. Эта команда позволит посмотреть типы данных столбцов и дополнительные атрибуты, указанные при создании таблицы.
Язык SQL Подмножество DDL (SQL) Создание БД и таблиц в My. SQL •
Язык SQL Подмножество DDL (SQL) Изменение структуры таблиц. Удаление таблиц ALTER TABLE имя_таблицы { ADD [COLUMN] определение_столбца } | { ALTER [COLUMN] имя_столбца изменяющее_действие} | { DROP [COLUMN] имя_столбца RESTRICT | CASCADE } | { ADD определение_ограничения_для_таблицы } | { DROP CONSTRAINT имя_ограничения RESTRICT | CASCADE }; изменяющее_действие : : = { SET DEFAULT значение_по_умолчанию } | {DROP DEFAULT} • Пример 1. ALTER TABLE t 2 DROP CONSTRAINT C 4 • Пример 2. ALTER TABLE tt ADD summa FLOAT NOT NULL DEFAULT 0. 00 DROP имя_таблицы
Язык SQL Подмножество DML (SQL) Вставка строк INSERT INTO table_name (f 1, f 2, . . . , fn) VALUES (v 1, v 2, . . . , vn) • Пример: СТУДЕНТЫ {фамилия, имя, отчество, ном_группы, ном_зач_книж, факультет} • INSERT INTO Студенты (фамилия, имя, отчество, ном_группы, ном_зач_книж, факультет) VALUES ('Васин', 'Петр', 'Васильевич', 206, '3235432', 'Физический') • INSERT INTO Студенты (фамилия, имя, отчество, ном_группы, ном_зач_книж, факультет) SELECT фамилия, имя, отчество, ном_группы, ном_зач_книж, факультет FROM Студенты_1
Язык SQL n n Вставка строки в таблицу осуществляется SQL-командой INSERT. Команда имеет две формы: однострочную и многострочную. Остановимся вначале на первой форме. Здесь tabie_name — имя таблицы, куда осуществляется вставка, fi— название столбца таблицы, Vi — некоторая константа. Предполагается, что при добавлении строки ее полям присваиваются значения из списка v. В результате выполнения такой команды к таблице будет добавлена строка, а в соответствующих столбцах будут стоять указанные в команде значения. Если после ключевого слова указываются все значения для столбцов, TО команда INSERT может быть использована в укороченном виде. Важно, чтобы значения столбцов перечислялись согласно их порядку в таблице. Если количество значений столбцов меньше количества столбцов в таблице, то оставшимся столбцам присваивается значение NULL либо значение по умолчанию. Если для данных столбцов не разрешается использовать значение NULL, и не указано значение по умолчанию, то СУБД должна сгенерировать ошибку. Обратимся теперь к многострочной форме команды INSERT. В этом варианте использования команды строки берутся из набора, полученного с помощью команды выборки SELECT.
Язык SQL Подмножество DML (SQL) Обновление строк UPDATE table_name SET f 1=v 1, f 2=v 2, . . . , fn=vn WHERE condition • Пример: UPDATE table 1 SET suma=sum 1+suma 2, type=1 WHERE type=0 AND suma<1000;
Язык SQL n n n Для обновления содержимого строк таблицы в языке SQL предусмотрена специальная команда UPDATE, ХОТЯ обновление можно рассматривать как последовательность операций удаления и вставки. Здесь tabie_name — имя таблицы, где производится обновление, fi— имя столбца, Vi— значение, присваиваемое столбцу, condition — условие отбора строк, где будет производиться обновление. При выполнении данной команды у группы строк, которые определяются условием condition, будут изменены значения полей fi. Разумеется, меняться будут только те поля, которые указаны в команде. Ключевое слово WHERE может отсутствовать в команде, и тогда команда осуществляет изменения во всех строках таблицы. Условие, которое указывается после ключевого слова WHERE, может состоять из нескольких простых условий (типа х>0), связанных друг с другом логическими операциями AND или OR. Кроме этого, условие может содержать запросы. Мы подробнее остановимся на содержимом раздела WHERE, когда будем рассматривать команду SELECT. Выражение suma=sumai+suma 2: здесь и слева, и справа от знака равенства стоят имена столбцов. Это значит, что значения столбцов справа от знака равенства берутся из одной и той же строки и присваиваются полю с именем suma опять той же строки. Эта возможность позволяет выполнять различные действия над столбцами строки в команде UPDATE и сохранять результат в той же строке. В СУБД My. SQL реализована возможность работы одновременно с несколькими таблицами в команде UPDATE. С помощью одной команды UPDATE.
Язык SQL Подмножество DML (SQL) Удаление строк DELETE FROM table_name WHERE condition • Пример: DELETE FROM zakaz WHERE date 1 >= 4. 10. 2008
Язык SQL n n n Удаление строк осуществляется командой DELETE. Формат команды представлен на слайде. Из таблицы с именем <tabie_name> удаляются строки, содержимое которых удовлетворяет условию <condition>. При отсутствии в команде ключевого слова WHERE из таблицы удаляются все строки. В примере из таблицы zakaz удаляется информация о заказах, которые были приняты после 1 октября 2008 г. Раздел WHERE, как и в случае с командой UPDATE, может содержать запросы. Остановимся на этом, когда будем разбирать команду SELECT.
Язык SQL Подмножество DDL (SQL) My. SQL. Добавление данных в таблицы •
Контрольные вопросы 1. SQL: назначение языка, характеристика стандартов и диалектов языка. 2. SQL: характеристика форм языка и состава языка. 3. Назовите и дайте характеристику основных команд языка DDL SQL. Приведите примеры описания таблиц. 4. Как средствами DDL SQL создаются связи между таблицами? Приведите пример. 5. DML SQL: команды delete, insert, update: назначение, синтаксис и примеры.
Скачать презентацию Базы данных Тема 2 Основы SQL Создание базы
Тема 2 Основы SQL. Создание базы данных.ppt