Ведение в язык sql
Большинство выпускаемых в настоящие время СУБД позволяют использовать для управления данными структурированный язык запросов SQL (Structured Query Language)
Язык SQL – первый и пока единственный стандартный язык для работы с БД, который получил достаточное распространение.
Язык SQL был разработан в середине 1970-х годов в компании IBM и предназначен для создания удобных и понятных пользователями запросов к реляционным базам данных.
Вскоре SQL и его аналоги были реализованы во многих реляционных СУБД.
Начиная с 1980-х годов ведётся деятельность по стандартизации языка SQL.
К настоящему времени разработано и утверждено несколько стандартов языка: SQL/89 SQL/92 SQL/99 SQL/2003
Все имеющиеся сейчас реализации языка SQL соответствуют международным стандартам, по отличающимся друг от друга различными дополнительными усовершенствованиями.
Особенности языка SQL.
SQL – декларативный язык. С помощью языка SQL программист описывает только то, какие данные нужно извлечь из базы данных или как их модифицировать. То, каким образом сделать, решает непосредственно СУБД при обработке SQL-запроса.
Переносимость. Благодаря наличию международного стандарта, тексты SQL – запросов могут быть достаточно легко перенесены из одной СУБД в другую. (Единственно, что при применении некоторых специфичных для реализации возможностей такой переносимости добиться очень трудно).
Интерактивность. SQL обеспечивает пользователям немедленный доступ к данным, при этом в интерактивном режиме можно за очень короткое время получить результат запроса без написания сложных программ.
Возможность программного доступа к БД. Язык SQL легко использовать в приложениях, которым необходимо обращаться к БД. Одним и те же операторы SQL употребляются как для интерактивного, так и для программного доступа, поэтому части программ, содержащие обращения к БД, можно вначале проверить в интерактивном режиме, а затем вставить в программу.
Классы инструкций языка sql(по sql/92)
Язык определения данных (DDL).
содержит команды, которые обеспечивают создание объектов базы данных и манипулирование ими.
CREATE… - создать
ALTER… - изменить
DROP… - удалить
Язык манипулирования данными (DML)
содержит специальные команды для манипулирования данными.
INSERT… - добавить данные
UPDATE… - изменить данные
DELETE… - удалить данные
Язык управления данными (DCL)
содержит команды для разрешения/ограничения доступа к данным.
GRANT… - добавить разрешение
REVOKE… – отменить разрешение
Создание и удаление базы данных
CREATE DATABASE Имя БД
создание новой базы данных
ALTER DATABASE Имя БД
изменить общие характеристики БД (кодировочную таблицу и т.д.)
DKOP DATABASE Имя БД
удалить базу данных и все её таблицы
Создание таблицы
CREATE TABLE Имя Таблицы(
описания поля[параметры поля],
описание поля[параметры поля],
…
[Общие параметры]
);
Описание поля
ИмяПоля ТипПоля[(размер)]
[PRIMARY KEY[AUTOINCREMENT] I UNIQNE ]
[NOTNULL | NULL]
[CHECK(выражение)]
[DEFAULT ЗначениеПоУмолчанию]
Часто используемые типы полей(по ANSI SQL)
|
Логический |
BOOLEAN |
|
Символьные |
CHARACTER(N) VARCHAR(N) |
|
Целочисленные |
SMALLINT INTEGER BIGINT |
|
Дробные |
FLOAT DECIMAL(M,D) |
|
Дата и время |
DATE TIME TIMESTAMP |
Большинство существующий СУБД по-разному осуществляют поддержку типов данных:
обычно отсутствует полная реализация всех стандартных типов данных;
реализация отдельных типов данных может отличаться от описания в стандарте;
типы данных могут иметь другие имена;
имеется поддержка дополнительных типов данных, не упомянутых в стандарте.
Общие параметры таблицы
Составной первичный ключ
PRIMARY KEY (список полей)
Внешний ключ
FOREIGN KEY (список полей)
REFERENCES таблица (список полей)
[ON DELETE {CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {CASCADE | SET NULL | NQACTION}]
Пример. База данных “Предприятие”
brigade id_brigade
INT(10) NOT NULL(PK) name
CHAR(30) NOT NULL leader
INT(10) NULL(FK) employee icl_employee
INT(10) NOT NULL (pk) name
CNAR (30) NOT NULL profession-icl
INT(10) NULL(FK) brigade
INT(10) NULL(FK)
employeeinfo id_employee_info
INT(10) NOT NULL (PK)(FK) address
CNAR(100) NULL
Пример. Создание таблицы «Профессии»
CREATE TABLE Profession(
Id_profession INTEGER PRIMARI KEY AUTOINCREMENT,
jjbtitle CHAR(40) NOT NULL
);
или
CREATE TABLE Profession(
id_profession INTEGER PRIMARY KEY AUTOINCREMENT,
jobtitle CHAR(40) NOT NULL,
PRIMARY KEY (id_profession)
);
Пример. Создания таблицы «Бригада»
CREATE TABLE Brigade(
id_brigade INTEGER PRIMARY KEY AUTO_INCREMENT,
name CHAR(30) NOT NULL
);
Пример. Создания таблицы «Сотрудники»
CREATE TABLE Employee(
id_employee INTEGER PRIMARY KEY AUTO_INCREMENT,
name CHAR(30) NOT NULL,
profession_id INTEGER,
brigade_id INTEGER,
FOREIGN KEY (profession_id) REFERENCES Profession (id_profession),
FOREIGN KEY (brigade_id) REFERENCES Brigade (id_brigade)
);
Пример: Создания таблицы «Информация о сотруднике»
CREATE TABLE EmployeeInfo(
id_employee_info INTEGER PRIMARY KEY NOT NULL,
address CHAR(100),
FOREIGN KEY (id_employee_info) REFERENCES Employee (id_employee)
);
ДОБАВЛЕНИЕ ЗАПИСИ В ТАБЛИЦУ
INSERT INTO ИмяТаблицы (Список полей) VALUES (Список значений);
При добавлении записи можно не указывать значения для полей, имеющих параметры AUTOINCREMENT, DEFAULT или NULL.
Для полей, связанных с помощью FOREIGN KEY с другими таблицами, обязательно нужно указывать существующее значение.
Пример.
INSERT INTO Profession (id_profession, jobtitle) VALUES (1001, “слесарь”);
INSERT INTO Brigade (id_brigade, name) VALUES (2001, “слесарная”);
INSERT INTO Employee (id_employee, name, profession_id, brigade_id) VALUES (1, «Иванов И.И.», 1001, 2001);
INSERT INTO EmployeeInfo (id_employee_info, address) VALUES (1, «ул.Ленина, д.1 кв.1»);
Изменение существующей записи
UPDATE ИмяТаблицы SET ИмяПоля=Выражение[, ...] WHERE Условие;
При изменении записи с помощью условия требуется указывать записи, к которым нужно применить команду.
В противном случае будут изменены все записи таблицы.
Пример. Назначения сотрудника с id=1 ,бригадиром бригады с id=2001
UPDATE Brigade SET leader=1 WHERE id_brigade=2001;
Удаление строк из таблицы
DELETE FROM ИмяТаблицы WHERE Условие;
При удалении записи с помощью условия требуется указать записи, к которым нужно применит команду.
В противном случае будут удалены все записи таблицы.
Пример. Удаление сотрудника с id=17
DELETE FROM Employee WHERE id_employee=17;
Выборка данных
SELECT что
FROM откуда
WHERE Условие для записи
GROUP BY разбиение на группы
HAVING условие для групп
ORDER BY порядок сортировки
Формат предложения SELECT:
SELECT [DISTINCT | ALL] список выражений | *
Предложение SELECT определяет состав результирующего набора данных. Каждому выражению соответствует один столбец. Выражение * соответствует полному перечню полей таблицы.
С точки зрения реляционной алгебры предложение SELECT можно рассматривать как описание операции проекции отношений.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Пример 1. Получить содержимое всех полей таблицы Employee
SELECT * FROM Employee;
|
Id_employee |
name |
Profession_id |
Brigade_id |
|
1 |
Иванов И.И. |
1003 |
2001 |
|
2 |
Петров П.П. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Пример2. Получить содержимое поля name таблицы Employee
SELECT name FROM Employee;
Ключевое слово DITINCT ПОЗВОЛЯЕТ ИСКЛЮЧИТЬ из результирующего набора повторяющиеся записи:
SELECT DISTINCT profession_id FROM Employee;
Каждая СУБД позволяет записывать выражения с использованием широкого набора операторов и функций. В результирующий набор помещается значение выражения, вычисленное для каждой записи таблицы. Для вновь созданного столбца требуется задать имя с помощью ключевого слова AS:
выражение AS имя
Пример. Рассчитать премию сотрудников в размере 20% от оклада
SELECT id_employee, name, salary, salary*0.2 AS bonus FROM Employee
Формат предложения FROM:
FROM таблица [JOIN таблица2 ON условие ...]
Предложение FROM определяет источник данных для запроса. Данные могут извлекаться как из существующей таблицы, так и из виртуальной таблицы, полученной в результате выполнения реляционных операций.
С точки зрения реляционной алгебры предложение FROM можно рассматривать как ограничение декартового произведения отношений.
|
|
|
|
|
|
|
|
|
|
|
|
При использовании нескольких таблиц в запросах следует указывать полное имя в виде:
Таблица. Поле.
Пример1. Получить домашние адреса сотрудников
SELECT Employee.name, EmployeeInfo.address
FROM Employee JOIN EmployeeInfo ON Employee.id_employee=EmployeeInfo.id_employee;
Если при использовании кратких имён полей не возникает неоднозначности, то имена таблиц можно не указывать.
Пример 2. Получить профессии сотрудников
SELECT name, jobtitle
FROM Employee JOIN Profession ON profession_id=id_profession;
Формат предложения WHERE:
WHERE Поле ОПЕРАТОР Выражения …
Предложение WHERE позволяет поместить в результирующий набор только те записи исходной таблицы, которые будут удовлетворять требуемому условию.
Сточки зрения реляционной алгебры предложения WHERE можно рассматривать как ограничение отношения.
В предложении WHERE допускается использовать следующие параметры:
Отношения: <, >, =, ≤, ≥, <>
Логические: AND, OR, NOT
Специальные: IN, BETWEEN, IS, LIKE, EXISTS
Оператор IN используется для сравнения поля с несколькими значениями
jobtitle IN («слесарь», «столяр, «штукатур»)
<=> jobtitle = “слесарь” OR jobtitle = “столяр” OR jobtitle = “штукатур”
Оператор BETWEEN используется для проверки, попадает ли значение поля в заданный диапазон
salary BETWEEN 700 AND 900
<=> salary ≥ 700 AND salary ≤900
Оператор IS используется для сравнения со значением NULL (т.е. проверяет, имеет ли поле значение)
brigade_id IS NULL
Оператор LIKE используется для сравнения с шаблоном строки
В шаблоне могут использоваться символы:
_ - соответствует ровно одному любому символу
% - соответствует любому количеству любых символов
|
Пример условия |
Результат поиска |
|
Word LIKE ‘За_ор’ |
Забор Затор |
|
Word LIKE ‘Д%нь’ |
День, Длинная тень |
|
Word LIKE ‘%й’ |
138-й, Последний |
Пример 1. Получить список сотрудников имеющих профессию «столяр»
SELECT name
FROM Employee
WHERE profession_id = 1001;
или
SELECT name
FROM Employee JOIN Profession ON profession_id = id_profession
WHERE jobtitle = ‘столяр’;
Пример 2. Получить список сотрудников , проживающих на улице Чкалова.
SELECT name
FROM Employee JOIN EmployeeInfo
ON Employee.id_employee = EmployeeInfo.id_employee
WHERE address LIKE ‘%Чкалова%’;
Формат предложение GROUP BY
GROUP BY список полей
Предложение GROUP BY позволяет разбить исходную таблицу на группу записей с одинаковыми значениями указанных полей. В результирующем наборе каждая группа представляется одной записью.
С точки зрения реляционной алгебры предложение GROUP BY можно рассматривать как деление отношений.
При использовании предложения GROUP BY, предложение SELECT может содержать выражения, содержащие только
Пример. Получить список сотрудников, имеющихся в каждой бригаде.
SELECT Brigade.name, Profession.jobtitle
FROM Employee
JOIN Brigade
ON Employee.brigade_id = Brigade.id_brigade
JOIN Profession
ON Employee.profession_id = Profession.id_profession
GROUP BY Brigade.name, Profession.jobtitle;
Итог:
name jobtitle
столярная столяр
слесарная слесарь
Агрегатные (агрегировать – объединять) функции позволяют получить значение, характеризующее группу записей:
MAX (поле|выражение)
находит максимальное значение поля (выражения)
MIN (поле|выражение)
находит минимальное значение поля (выражения)
AVG (поле|выражение)
находит среднее значение поля (выражения)
SUM (поле|выражение)
находит сумму значения поля (выражения)
COUNT (поле|*)
находит количество непустых полей (общее количество записей)
Пример. Получить количество сотрудников в каждой бригаде.
SELECT Brigade.name, count (*) AS count
FROM Employee
JOIN Brigade ON Employee.brigade_id = Brigade.id_brigade
GROUP BY Brigade.name;
Итог:
name count
столярная 2
слесарная 2
Агрегатные функции допустимо применять без использования предложения GROUP BY. В этом случае из действие распространяется на всю таблицу.
Пример. Найти общее количество сотрудников
SELECT count (*) AS count
FROM Employee;
Итог:
count
5
Формат предложения HAVING:
HAVING условие …
Предложение HAVING позволяет включить в результирующую таблицу только те группы записей, которые удовлетворяют указанному условию.
В условии можно использовать только те поля, которые были перечислены только в GROUP BY, а также агрегатные функции.
Пример. Получить список бригад с заданным количеством сотрудников
SELECT Brigade.name, count (*) AS count
FROM Employee
JOIN Brigade ON Employee.brigade_id = Brigade.id_brigade
GROUP BY Brigade.name
HAVING count (*) = 2;
Итог:
name count
столярная 2
слесарная 2
Если в одном запросе встречаются и предложения WHERE, и предложение HAVING, то отбор осуществляется по следующей схеме:
Из исходной таблицы выбираются записи, удовлетворяющие условию WHERE.
Полученный набор записей разбивается на группы в соответствии с GROUP BY.
Из групп записей выбираются те, которые удовлетворяют условию HAVING.
Формат предложения ORDER BY;
ORDER BY поле [ ASC | DESK] [ …]
Предложение ORDER BY позволяет отсортировать строки результирующего набора по значениям одного или нескольких столбцов (или выражений)
ASC – сортировка по возрастанию (по умолчанию)
DESC – сортировка по убыванию
Пример. Вывод фамилий сотрудников в алфавитном порядке
SELECT Brigade.name AS BrigadeName, Employee.name AS EmployeeName
FROM Employee JOIN Brigade ON Employee.brigade_id = Brigade.id_brigade
ORDER BY BrigadeName ASC, EmployeeName ASC.
Итог:
BrigadeName EmployeeName
слесарная Андреев А.А.
слесарная Петров П.П.
столярная Иванов И.И.
столярная Сидоров С.С.
Технология доступа к данным
Различные СУБД по-разному организуют и хранят базы данных:
- Для каждой таблицы отдельные файлы (Paradox и dBase). БД – каталог, в котором находятся файлы таблиц;
- Несколько таблиц в одном файле (MS Access и InterBase). БД – файл с путём доступа к нему.
Технологии доступа к данным решают проблему унификации доступа к различным базам данных – предоставление универсального способа доступа к разным форматам данных единым образом.
С++ Builder предлагает следующие механизмы доступа к данным:
BDE (IDAPI);
ADO; // будем рассматривать
dbExpress;
IntelBase Express.
Средства C++ Builder для работы с базами данных можно разделить на 2 вида:
Инструменты – специальные программы и пакеты, обеспечивающие обслуживание БД вне разрабатываемых приложений.
Компоненты – предназначены для создания приложений, осуществляющих операции с базами данных.
Инструменты С++ Builder для операции с БД:
BDE Administrator – утилита для настройки различных параметров BDE, настройки драйверов баз данных, создания и удаления драйверов ODBC, создания и обслуживания псевдонимов.
Database Desktop – программа предназначенная для создания и редактирования структуры и записей таблиц, а также создания, редактирования и выполнения SQL-запросов.
SQL Explorer – проводник БД, позволяющий просматривать и редактировать БД и словари данных.
SQL Monitor – программа отслеживания порядка выполнения SQL-запросов к удалённым БД.
Data Pump – программа для переноса данных (схемы базы данных и содержимого) между БД.
Компоненты для создания приложений БД в C++ Builder:
Не визуальные. Предназначены для организации доступа к данным, содержащимися в таблицах, обеспечивают связь между данными таблиц БД и визуальными компонентами.
Визуальные. Используются для построения интерфейса приложения БД.
Компоненты для создания приложений БД в С++ Builder сгруппированы на следующих страницах:
Data Access – не визуальные компоненты, предназначенные для организации доступа к данным.
DataSource (источник данных)
ClientDataSet (клиентский набор данных)
DataSetProvier (провайдер набора данных)
Компоненты преобразования документа XML
Data Controls – визуальные компоненты, предназначенные для управления данными.
DBGrid (сетка или таблица)
DBNavigator (навигационный интерфейс)
DBText, DBEdit, DBMemo, DBImage, DBListBox, DBComboBox, DBCheakBox, DBRadioGroup
DBLookupListBox, DBLookupComboBox
DBRichEdit, DBCtrlGrid, DBChart
dbExpress – компоненты, предназначенные для соединения приложений для работы с БД с помощью dbExpress
SQLConnection dbExpress (соединение с сервером БД
SQLDataSet (набор данных)
SQLQuery (набор данных запроса)
SQLStoredProc (вызов хранимой процедуры сервера)
SQLTable (набор данных таблицы)
SQLMonitor (монитор выполнения SQL-запросов)
SQLClientDataSet (клиентский набор данных)
DataSnap – содержит компоненты, предназначенные для создания трёхуровневых (многоуровневых) приложений.
BDE – содержит компоненты, предназначенные для управления данными с использованием BDE.
Table
Query
StoredProc
DataBase
Session, Batch Move, UpdateSQL, NestedTable, BDEClientDataSet
ADO – компоненты, предназначенные для управления данными с использованием технологии ADO (Active Data Objects)
ADOConnection
ADOCommand
ADODataSet
ADOTable
ADOQuery
ADOStoredProc, RDSConnection
InterBase – компоненты, предназначенные для работы с сервером InterBase
IBTable, IBQuery, IBStoredProc, IBDatabase, IBTransaction, IBUpdateSQL, IBDataSet, IBSQL, IBDatabaseInfo, ...
Decision Cube – компоненты, предназначенные для построения систем принятия решений.
DecisionCube, DecisionQuery, DecisionSource, SecisionPivor, DecisionGrid, DecisionGraph
QReport – компоненты, предназначенные для построения отчётов.
В большинстве случаев назначение компонента можно определить по префиксу:
DB – визуальный компонент, связан с данными и используется для построения интерфейса приложения БД
QR – компонент используется для построения отчётов QuickReport
IB – компонент предназначен для работы с сервером InterBase
При работе с БД C++ Builder дополнительно предоставляет классы исключений:
EDatabaseError – ошибка БД
EDBEEngineError – ошибка BDE
EDBClient – ошибка в приложении клиента
EUpdateError – ошибка при обновлении записей
EDBEditError – введённое в поле значение не соответствует типу поля