Язык SQL. Лекция Язык SQL. В настоящей лекции мы не определяем подробно основные объекты реляционной
 Скачать 82.36 Kb.
|
|
Введение В настоящей лекции мы не определяем подробно основные объекты реляционной базы данных - базы данных, таблицы, индексы, представления, хранимые команды/процедуры, триггеры, события таймера. Это будет сделано в следующих лекциях. Данная лекция концентрирует ваше внимание на тех возможностях, которые предоставляет SQL по обработке данных. Для иллюстрации применения команд SQL будет использована простая база данных о сотрудниках организации, ее структуре и работах, которые выполняют сотрудники, предметная область и структура которой описана в приложении 1 к лекции. SQL - язык манипулирования данными в реляционной базе данных SQL и его история Единственным средством общения и администраторов баз данных, и проектировщиков, и разработчиков, и пользователей с реляционной базой данных является структурированный язык запрос SQL (Structured Query Language). SQL есть полнофункциональный язык манипулирования данными в реляционных базах данных. В настоящее время он является общепризнанным, стандартным интерфейсом для реляционных баз данных, таких как Oracle, Informix, Sybase, DB/2, MS SQL Server и ряда других (стандарты ANSI и ISO). SQL - непроцедурный язык, который предназначен для обработки множеств, состоящих из строк и колонок таблиц реляционной базы данных. Хотя существуют его расширения, допускающие процедурную обработку. Проектировщики баз данных используют SQL для создания всех физических объектов реляционной базы данных. Теоретические основы SQL были заложены в известной статье [Кодд], положившей начало развитию теории реляционных БД. Первая практическая реализации была выполнена в исследовательских лабораториях фирмы IBM Chamberlin D.D. и Royce R.F. Промышленное применение SQL было впервые реализовано в СУБД Ingres. Одной из первых промышленных реляционных СУБД является Oracle. По сути дела, реляционная СУБД - это программное обеспечение, которое управляет работой реляционной базы данных. Первый международный стандарт языка SQL был принят в 1989 г. (SQL-89). В конце 1992 г. был принят новый международный стандарт SQL-92. В настоящее время большинство производителей реляционных СУБД используют его в качестве базового. Однако работы по стандартизации языка SQL далеки от завершения и уже разработан проект стандарта SQL-99, который вводит в обиход языка понятие объекта и разрешает на него ссылаться в операторах SQL. В исходном варианте SQL не было команд управления потоком данных, они появились в недавно принятом стандарте ISO/IEC 9075-5: 1996 дополнительной части SQL. Каждой конкретной СУБД соответствует своя собственная реализация SQL, в целом поддерживающая определенный стандарт, но имеющая свои особенности. Эти реализации называются диалектами. Так, стандарт ISO/IEC 9075-5 предусматривает объекты, называемые постоянно хранимыми модулями или PSM-модулями (Persistent Stored Modules). В СУБД Oracle расширение PL/SQL является аналогом указанного выше расширения стандарта 1. Описание основных операторов SQL SQL состоит из набора команд манипулирования данными в реляционной базе данных, которые позволяют создавать объекты реляционной базы данных, модифицировать данные в таблицах (вставлять, удалять, исправлять), изменять схемы отношений базы данных, выполнять вычисления над данными, делать выборки из базы данных, поддерживать безопасность и целостность данных. Весь набор команд SQL можно разбить на следующие группы: команды определения данных ( DDL - Data Defininion Language ); команды манипулирования данными ( DML - Data Manipulation Language ); команды выборки данных ( DQL - Data Query Language ); команды управления транзакциями; команды управления данными. При выполнении каждая команда SQL проходит четыре фазы обработки: фаза синтаксического разбора, которая включает проверку синтаксиса команды, проверку имен таблиц и колонок в базе данных, а также подготовку исходных данных для оптимизатора; фаза оптимизации, которая включает подстановку действительных имен таблиц и колонок базы данных в представление, идентификацию возможных вариантов выполнения команды, определение стоимости выполнения каждого варианта, выбор наилучшего варианта на основе внутренней статистики; фаза генерации исполняемого кода, которая включает построение выполняемого кода команды; фаза выполнения команды, которая включает выполнение кода команды. В настоящее время оптимизатор является составной частью любой промышленной реализации SQL. Работа оптимизатора основана на сборе статистики о выполняемых командах и выполнении эквивалентных алгебраических преобразований с отношениями базы данных. Такая статистика сохраняется в системном каталоге базы данных. Системный каталог является словарем данных для каждой базы данных и содержит информацию о таблицах, представлениях, индексах, колонках, пользователях и их привилегиях доступа. Каждая база данных имеет свой системный каталог, который представляет совокупность предопределенных таблиц базы данных. Таблица 8.1 содержит список команд SQL в соответствии с принятым стандартом, за исключением некоторых практически не используемых в диалектах команд.
Набор команд SQL, перечисленный в таблице, не является полным. Этот список приведен, чтобы вы составили впечатление о возможностях SQL в целом. Для получения полного списка команд следует обратиться к соответствующему руководству для конкретной СУБД. Следует помнить, что SQL является единственным средством общения всех категорий пользователей с реляционными базами данных. Встроенные функции SQL и их использование в запросахАрифметические функцииSQL поддерживает полный набор арифметических операций и математических функций для построения арифметических выражений над колонками базы данных ( +, -, *, /, ABS, LN, SQRT и т.д.). Список основных встроенных математических функций дан ниже в таблице 8.2.
Набор встроенных функций может изменяться в зависимости от версии СУБД одного производителя и также в СУБД различных производителей. Так, например, в СУБД SQLBase, Centure Inc. есть функция @ATAN2(X,Y), которая возвращает арктангенс Y/X, но отсутствует функция SIGN(X). Арифметические выражения необходимы для получения данных, которые непосредственно не сохраняются в колонках таблиц базы данных, но значения которых необходимы пользователю. Допустим, что вам необходим список служащих, показывающий выплату, которую получил каждый служащий с учетом премий и штрафов. SELECT ENAME, SAL, COMM, FINE, SAL + COMM - FINE FROM EMPLOYEE ORDER BY DEPNO; Арифметическое выражение SAL + COMM - FINE выводится как новая колонка в результирующей таблице, которая вычисляется в результате выполнения запроса. Такие колонки называют еще производными (вычисляемыми) атрибутами или полями. Функции обработки строкSQL предоставляет вам широкий набор функций для манипулирования со строковыми данными (конкатенация строк, CHR, LENGTH, INSTR и другие). Список основных функций для обработки строковых данных приведен в таблице 8.3.
Названия одних и тех же функций могут отличаться в различных СУБД. Так, например, функция СУБД Oracle SUBSTR(S, pos, [, len]) в СУБД SQLBase называется @SUBSTRING(S, pos, len). В СУБД SQLBase имеются функции, которых нет в СУБД Oracle (см. таблицу ниже, где приведен список таких функций).
Можно использовать функцию INITCAP, чтобы при получении списка имен служащих фамилии всегда начинались с заглавной буквы, а все остальные были прописными. SELECT INITCAP(ENAME) FROM EMPLOYEE ORDER BY DEPNO; Специальные функцииSQL обеспечивает набор специальных функций для преобразований значений колонок. Список таких функций приведен в таблице 8.5.
В таблице EMPLOYEE для каждого служащего можно ввести признак пола - добавить колонку SEX типа CHAR(1) (0 - мужской, 1 - женский). Допустим, что вам нужен список служащих, в котором требуется разделение их по признаку пола с указанием его в числовом формате; тогда можно задать такую команду: SELECT ENAME, LNAME, AGE, 'Пол:', TO_NUMBER(SEX) FROM EMPLOYEE ORDER BY 5; В качестве примера использования функции DECODE приведем запрос, вычисляющий список служащих с указанием их руководителя. Если руководитель неизвестен, то выводится по умолчанию "не имеет". SELECT ENAME, DECODE(DEPNO, 10, 'Дрягин', 20,'Жиляева', 30,' Коротков', 'не имеет') FROM EMPLOYEE ORDER BY ENAME; Предположим, что руководитель организации имеет неопределенное значение колонки DEPNO и, следовательно, для него будет работать умолчание, предусмотренное в DECODE. Функции для обработки датыВ диалекте SQL СУБД Oracle имеется небольшой набор функций для манипулирования колонками с типом date. Список основных функций обработки даты и времени приведен в таблице 8.6.
Если вам потребовался список новых служащих, поступивших за последний квартал в организацию, то вы можете написать запрос в следующем виде: SELECT ENAME, HIREDATE, HIREDATE + 92 DAYS FROM EMPLOYEE WHERE HIREDATE + 92 DAYS > SYSDATE AND DEPNO=30; Ключевое слово SYSDATE всегда возвращает текущую дату. В этом примере также показано, как используется арифметический оператор сложения с переменными типа "дата". К переменной типа "дата" можно прибавлять и вычитать из него целое число дней, месяцев, лет, часов, минут, секунд, микросекунд. Для этого используются соответствующие ключевые слова ( DAY, MONTH и т.д.), следующие за целой константой (дробная часть игнорируется, если вы указываете число с десятичной точкой). Имеется ограничение на использование скобок в таких выражениях (так, заключение в скобки выражения 1 DAYS + 1 YEARS приведет к ошибке). Использование агрегатных функций в запросахАгрегатные функции в SQL позволяют выбирать обобщающую информацию из группы строк и проводить систематизацию данных. Список агрегатных функций приведен в таблице 8.7. Агрегатные функции почти во всех реализациях SQL носят одинаковые имена. Различие в наименование для Oracle дано через косую черту.
Использование функций агрегирования позволяет вам находить суммарные значения колонок и разброс данных в колонке. Так, после выполнения запроса SELECT SUM(SAL) FROM EMPLOYEE; вы узнаете итоговую сумму зарплаты по организации, а из запроса SELECT AVG(SAL), STDDEV(SAL) FROM EMPLOYEE; - среднюю зарплату по организации и ее разброс (дисперсию). Однако наиболее часто требуется подобная итоговая информация не для таблицы в целом, а для определенных наборов (групп) строк таблицы. Для того чтобы группировать строки таблицы по какому-либо признаку, в команде SELECT существует специальное предложение GROUP BY, которое задает колонку (или колонки) для проведения группировки. Это предложение группирует строки таблицы по значениям колонок группировки с последующим подавлением дублирующих значений в колонках группировки, т.е. позволяет определять подмножество значений некоторой колонки в терминах другой колонки и применять к полученным подмножествам функции агрегирования. Предположим, что вы хотите найти минимальные и максимальные оклады служащих в подразделениях, тогда вы можете написать SELECT DEPNO, MIN(SAL), MAX(SAL) FROM EMPLOYEE GROUP BY DEPNO; Предложение GROUP BY должно следовать после предложения WHERE, если последнее присутствует в команде SELECT. Каждая строка результирующей таблицы относится к одной группе строк. Число групп определяется числом различных значений в колонке группировки (в данном случае DEPNO ). Агрегатные функции применяются к каждой группе как к отдельному множеству. Агрегатные функции можно использовать при соединении таблиц. Допустим, что вам нужно знать, сколько служащих работает на каждой должности в каждом подразделении, какова сумма зарплаты на подразделение и средняя зарплата. Тогда вам нужен запрос SELECT DNAME, JOB, SUM(SAL), COUNT(*), AVG(SAL) FROM EMPLOYEE, DEPARTAMENT WHERE EMPLOYEE.DEPNO=DEPARTAMENT.DEPNO GROUP BY DNAME, JOB; Функции SUM( ), COUNT( ), AVG( ) вычисляют суммы, число строк в группе и среднее значение в группе строк. В SQL можно задавать условия поиска для группы строк. Для этого в команде SELECT существует предложение HAVING, которое должно следовать за предложением GROUP BY. HAVING задает условие поиска для группы строк. Допустим, что вам необходимо получить ответ на тот же вопрос, что и в предыдущем примере, но при этом каждая группа должна состоять не менее чем из двух сотрудников. SELECT DNAME, JOB, SUM(SAL), AVG(SAL) FROM EMPLOYEE, DEPARTAMENT WHERE EMPLOYEE.DEPNO=DEPARTAMENT.DEPNO GROUP BY DNAME, JOB HAVING COUNT(*)>=2; Условие поиска в предложении HAVING исключает из результирующей таблицы группы, содержащие менее двух работников. Таким образом, вы познакомились с различными вариантами использования команды SQL SELECT. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||