конспект лекций по базам данных для специальности 09.02. Конспект лекций основы проектирования баз данных

• Название: Конспект лекций основы проектирования баз данных
• Анкор: конспект лекций по базам данных для специальности 09.02.05
• Дата: 19.11.2022
• Размер: 0.61 Mb.
• Имя файла: konspekt_lektsiy_opbd_2015.doc
• Тип: Конспект лекций; #799045
• страница: 12 из 12

Лекция 14. Создание, модификация и удаление таблиц

План:

1. 
   Создание таблицы
   
2. 
   Модификация таблиц
   
3. 
   Удаление таблиц
   

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

CREATE TABLE <имя_таблицы>

(<имя_столбца> <тип_столбца>

[NOT NULL]

[UNIQUE | PRIMARY KEY]

[REFERENCES <имя_ таблицы> (<имя_столбца>)]

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

• 
  NOT NULL - в этом случае элементы столбца всегда должны иметь определенное значение (не NULL)
  
• 
  один из взаимоисключающих параметров UNIQUE - значение каждого элемента столбца должно быть уникальным или PRIMARY KEY - столбец является первичным ключом.
  
• 
  REFERNCES <имя_мастер_таблицы> [<имя_столбца>] - эта конструкция определяет, что данный столбец является внешним ключом и указывает на ключ какой мастер_таблицы он ссылается.
  

Контроль за выполнением указанных условий осуществляет СУБД
Модификация таблиц
Как бы тщательно не планировалась структура таблицы, иногда возникает необходимость внести в нее некоторые изменения. Предположим, что в уже сформированную таблицу необходимо добавить столбец. Эту операцию можно выполнять различными путями. Например, можно удалить таблицу со старой структурой и создать вместо нее новую таблицу с нужной структурой. Недостатком этого метода является то, что необходимо будет куда-то скопировать имеющиеся в таблице данные и переписать их в новую таблицу после ее создания.

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

• 
  добавить в таблицу определение нового столбца;
  
• 
  удалить столбец из таблицы;
  
• 
  изменить значение по умолчанию для какого-либо столбца;
  
• 
  добавить или удалить первичный ключ таблицы;
  
• 
  добавить или удалить внешний ключ таблицы;
  
• 
  добавить или удалить условие уникальности;
  
• 
  добавить или удалить условие на значение.
  
  
  

Команда ALTER TABLE берет на себя все действия по копированию данных во временную таблицу, удалению старой таблицы и созданию вместо нее новой таблицы с нужной структурой и последующим переписыванием в нее данных.

Назначение многих параметров и ключевых слов команды ALTER TABLE аналогично назначению соответствующих параметров и ключевых слов команды CREATE TABLE.

Рассмотрим основные режимы использования команды ALTER TABLE:

• 
  добавление столбца;
  
• 
  удаление столбца;
  
• 
  модификация столбца;
  

1. 
   Добавление столбца
   

ALTER TABLE <имя_таблицы> ADD     (<имя_столбца> <тип_столбца>     [NOT NULL]     [UNIQUE | PRIMARY KEY]    [REFERENCES <имя_мастер_таблицы> (<имя_столбца>)]    ,...)

2. 
   Модификация столбца
   

ALTER TABLE <имя_таблицы>   ALTER COLUMN(<имя_столбца> <тип_столбца>    [NOT NULL]       [UNIQUE | PRIMARY KEY]       [REFERENCES <имя_мастер_таблицы> (<имя_столбца>)]    ,...)

Изменение столбца невозможно, если:

• 
  столбец участвует в ограничениях PRIMARY KEY или FOREIGN KEY;
  
• 
  на столбец наложены ограничения целостности, например UNIQUE (исключение – столбцы, имеющие тип данных переменной длины;
  
• 
  со столбцом связано значение по умолчанию.
  

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

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

ALTER TABLE <имя_таблицы> DROP (<имя_столбца> ,...)

Нельзя удалять столбцы с ограничением целостности CHECK, FOREIGN KEY, UNIQUE или PRIMARY KEY, а также столбцы, для которых определены значения по умолчанию.


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

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

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

1. 
   Приведите общий синтаксис SQL-оператора для создания таблицы.
   
2. 
   Приведите общий синтаксис SQL-оператора для добавления столбца в таблицу.
   
3. 
   Приведите общий синтаксис SQL-оператора для модификации столбца.
   
4. 
   В каких случаях модификация столбца невозможна?
   
5. 
   Приведите общий синтаксис SQL-оператора для удаления столбца.
   
6. 
   В каких случаях удаление столбца невозможно?
   

Лекция 15. Операторы манипулирования данными

План:

1. 
   Добавление новой записи в таблицу
   
2. 
   Модификация записей
   
3. 
   Удаление записей
   

К этой группе относятся операторы добавления, изменения и удаления записей.

1. 
   Добавление новой записи в таблицу
   

INSERT INTO <имя_таблицы> [<имя_столбца>,<имя_столбца>,..)] VALUES (<значение>,<значение>,..);

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

2. 
   Модификация записей
   

UPDATE <имя_таблицы> SET <имя_столбца>=<значение>,... WHERE <условие>]

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

• 
  операции сравнения: > , < , >= , <= , = , <> , != . В SQL эти операции могут применяться не только к числовым значениям, но и к строкам ( "<" означает раньше, а ">" позже в алфавитном порядке) и датам ( "<" раньше и ">" позже в хронологическом порядке).
  
• 
  операции проверки поля на значение NULL: IS NULL, IS NOT NULL
  
• 
  операции проверки на вхождение в диапазон: BETWEEN и NOT BETWEEN.
  
• 
  операции проверки на вхождение в список: IN и NOT IN
  
• 
  операции проверки на вхождение подстроки: LIKE и NOT LIKE
  
• 
  отдельные операции соединяются связями AND, OR, NOT и группируются с помощью скобок.
  

3. 
   Удаление записей
   

DELETE FROM <имя_таблицы> [ WHERE <условие> ]

Удаляются все записи, удовлетворяющие указанному условию. Если ключевое слово WHERE и условие отсутствуют, из таблицы удаляются все записи.
Вопросы для самоконтроля:

1. 
   Приведите общий синтаксис SQL-оператора для добавления записи в таблицу.
   
2. 
   Почему список столбцов в данной команде не является обязательным параметром?
   
3. 
   Приведите общий синтаксис SQL-оператора для модификации записи.
   
4. 
   Какие операции допустимы в логических выражениях условия?
   
5. 
   Приведите общий синтаксис SQL-оператора для удаления записи.
   

Лекция 16-17. Организация запросов на выборку данных при помощи языка SQL

План:

1. 
   Синтаксис оператора SELECT
   
2. 
   Выборка из нескольких таблиц
   

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

SELECT [ALL | DISTINCT] <список_выбора>            FROM <имя_таблицы>            [ WHERE <условие> ]            [ GROUP BY <имя_столбца>,... ]            [ HAVING <условие> ]            [ORDER BY <имя_столбца> [ASC | DESC],... ]

Порядок предложений в операторе SELECT должен строго соблюдаться (например, GROUP BY должно всегда предшествовать ORDER BY), иначе это приведет к появлению ошибок.

Этот оператор всегда начинается с ключевого слова SELECT.

В конструкции  <список_выбора>  определяется столбец или столбцы, включаемые в результат. Он может состоять из имен одного или нескольких столбцов, или из одного символа * (звездочка), определяющего все столбцы. Элементы списка разделяются запятыми.

В том случае, когда нас интересуют не все записи, а только те, которые удовлетворяют некому условию, это условие можно указать после ключевого слова WHERE.

В заключение заметим, что при выполнении оператора SELECT результирующее отношение может иметь несколько записей с одинаковыми значениями всех полей. Чтобы исключить повторяющиеся записи из выборки используется ключевое слово DISTINCT. Ключевое слово ALL указывает, что в результат необходимо включать все строки. 
Выборка из нескольких таблиц
Очень часто возникает ситуация, когда выборку данных надо производить из отношения, которое является результатом слияния двух других отношений.

Для выполнения операции такого рода в операторе SELECT после ключевого слова FROM указывается список таблиц, по которым производится поиск данных. После ключевого слова WHERE указывается условие, по которому производится слияние.

Следует обратить внимание на то, что когда в разных таблицах присутствуют одноименные поля, то для устранения неоднозначности перед именем поля указывается имя таблицы и знак "." (точка). (Хорошее правило: имя таблицы указывать всегда!)
Замечание:

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

1. 
   Приведите общий синтаксис SQL-оператора SELECT.
   
2. 
   Для чего используются ключевые слова ALL и DISTINCT?
   
3. 
   Для чего используется ключевое слово FROM?
   
4. 
   Для чего используются ключевое слово WHERE?
   
5. 
   Как произвести выборку данных из нескольких связанных таблиц?
   

Лекция 18. Сортировка и группировка данных при помощи языка SQL

План:

1. 
   Группировка данных
   
2. 
   Сортировка данных
   

Группировка данных
Группировка данных в операторе SELECT осуществляется с помощью ключевого слова GROUP BY и ключевого слова HAVING, с помощью которого задаются условия разбиения записей на группы.

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

Kлючевое слово HAVING работает следующим образом: сначала GROUP BY разбивает строки на группы, затем на полученные наборы накладываются условия HAVING.
Сортировка данных
Для сортировки данных, получаемых при помощи оператора SELECT служит ключевое слово ORDER BY. С его помощью можно сортировать результаты по любому столбцу или выражению, указанному в <списке_выбора>. Данные могут быть упорядочены как по возрастанию, так и по убыванию.

Ключевое слово DESC задает обратный порядок сортировки, ключевое слов ASC (его можно опускать) - прямой порядок сортировки. 
Вопросы для самоконтроля:

1. 
   С помощью какого ключевого слова осуществляется группировка данных в операторе SELECT?
   
2. 
   Для чего используется ключевое слово HAVING?
   
3. 
   С помощью какого ключевого слова осуществляется сортировка данных в операторе SELECT?
   
4. 
   Как отсортировать данные по возрастанию (убыванию)?
   

Лекция 19. Функции в запросах SQL

План:

1. 
   Агрегатные функции
   
2. 
   Преобразование текста
   
3. 
   Работа со строками
   

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

В арифметических выражениях допускаются операции сложения (+), вычитания (-), деления (/), умножения (*), а также различные функции (COS, SIN, ABS - абсолютное значение и т.д.).
Агрегатные функции
В SQL также определены так называемые агрегатные функции, которые совершают действия над совокупностью одинаковых полей в группе записей. Среди них:

• 
  AVG(<имя поля>) - среднее по всем значениям данного поля
  
• 
  COUNT(<имя поля>) или COUNT (*) - число записей
  
• 
  MAX(<имя поля>) - максимальное из всех значений данного поля
  
• 
  MIN(<имя поля>) - минимальное из всех значений данного поля
  
• 
  SUM(<имя поля>) - сумма всех значений данного поля
  

Следует учитывать, что каждая агрегирующая функция возвращает единственноезначение.

Область действия данных функции можно ограничить с помощью логического условия.
Преобразование текста
Часто, текстовые значения заполняются пользователями программного обеспечения по-разному: кто пишет Ф.И.О. с заглавной буквы, кто нет; кто-то пишет все заглавными буквами. Многие отчетные формы требуют унифицированного подхода, да и не только отчетные формы. Для решения этой задачи в SQL есть две функции UCASE - преобразует символы строки в верхний регистр и LCASE - преобразует символы строки в нижний регистр.


Работа со строками
MID(, , )- возвращает строку символов из середины текстовой строки с учетом начальной позиции и длины, где text - текстовая строка, из которой нужно извлечь символы, или столбец, содержащий текст; start_num - положение первого символа, который необходимо извлечь (начинаются с 1); num_chars - число возвращаемых символов.

Иногда приходится в качестве аргументов функции MID использовать выражения с функцией LEN:
LEN(column_name)- возвращает длину значения в поле записи.

Функция LEN( ) исключает из подсчета конечные пробелы.
Вопросы для самоконтроля:

1. 
   Какие операции и функции можно выполнять над данными в SQL?
   
2. 
   Что такое агрегатные функции? Какие функции входят в эту группу?
   
3. 
   Какие функции для работы со строками в SQL вам известны?
   

Литература

Основные источники:

1. 
   Медведева, А.А. Конспект лекций «Основы проектирования баз данных» для специальностей среднего профессионального образования 09.02.04 «Информационные системы (по отраслям)», 09.02.05 «Прикладная информатика (по отраслям)» / А.А. Медведева. – Курган: КТК, 2015. - 64 с.
   
2. 
   Федорова, Г.Н. Основы проектирования баз данных: учебное пособие для студ. учреждений сред. проф. образования / Г.Н. Федорова. – М.: Издательский центр «Академия», 2014. – 224 с.
   

Дополнительные источники:

3. 
   Кузнецов, С.Д. Базы данных: учебник / С.Д. Кузнецов. - М.: Издательский центр «Академия», 2012. – 496 с. (в электронном формате)
   
4. 
   Фуфаев, Э.В. Базы данных: уч. пос. / Э.В. Фуфаев, Д.Э. Фуфаев. – М.: Издательский центр «Академия», 2013. – 320 с.
   

Интернет-ресурсы:

5. 
   Интернет Университета информационных технологий: http://www.intuit.ru
   
6. 
   Компьютерный информационный портал: http://www.oszone.net
   
7. 
   Сайт по СУБД: http://postgresql.ru.net
   
8. 
   Сервер Информационных Технологий: http://citforum.ru
   

ДЛЯ ЗАМЕТОК