Основы SQL MS ACCESS

History of SQL Модель Кодда (1970 год) теоретически описывает проектирование и использование реляционных баз данных SQL-язык для создания таблиц/полей и манипулирования данными Началось со Structured English Query (SEQUEL), который был спроектирован для использования в SystemR. Вторая версия была названа Sequel/2, который был в последствии переименован в SQL (Structured Query Language).

History of SQL Существует 3 стандарта SQL-86 (наиболее распространенный в настоящее время) SQL-89 (с незначительными изменениями) SQL-92 (с существенными изменениями) MS Access поддерживает наиболее распространенный стандарт, но не полностью и с некоторыми отличиями. Так он поддерживает подмножество SQL-89 и некоторые элементы SQL-92.

КТО такой SQL? SQL - Structured Query Language Высокоуровневый декларативный язык, используемый для создания и обработки данных реляционных баз данных Декларативный оределяет ЧТО, а не КАК Включается как подмножество во многие языки 4-го поколения

Цели стандарта of SQL 1. Определяет синтаксис и семантику языков создания таблиц (DDL) и обработки данных(DML) 2. Определяет структуры данных и основные операции для поддержки, управления и защиты баз данных SQL 3. Обеспечивает средство переносимости баз данных и приложений между СУБД

Цели стандарта of SQL 4. Определяет минимальный (level 1) и полный (level 2) стандарты для разрешения различных степеней адаптации продуктов 5. Обеспечивает начальный стандарт, который все время улучшается

Преимущества стандартизованного языка Сокращается стоимость обучения - Организация может ограничиться одним языком Производительность - Увеличивается уровень мастерства и таким образом производительность через постоянное использование Мобильность приложений - Если каждая машина использует SQL, то перемещение приложений выполняется легче

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

Data Definition Language (DDL) Компонент DDL языка SQL позволяет создавать, изменять и удалять таблицы и индексы реализовывать ограничения целостности данных и доменов Доступно восемь команд DDL Create Table Drop Table Alter Table Create Index Drop Index Create View Drop View Create Schema

Data Manipulation Language (DML) Компонент DML языка SQL позволяет пользователям и приложениям запрашивать, изменять, удалять существующие записи в таблице и вставлять новые записи Select, Update, Insert, Delete

Create Table CREATE TABLE EMPLOYEE_T (EMP_ID VARCHAR(4) NOT NULL, NAME VARCHAR(40) NOT NULL, DATE_OF_BIRTH DATE, DEPARTMENT VARCHAR(25), CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMP_ID));

Alter Table Позволяет сделать изменения в существующей таблице - Добавлять и удалять столбцы Изменять имена столбцов, тип данных, ограничения и т.п. ALTER TABLE EMPLOYEE_T ADD (COMMENCE_DATE DATE);

Drop Table Если таблица удаляется, то все индексы, views, права и пр., определенное для таблицы также удаляется - Использовать осторожно – возврата нет DROP TABLE EMPLOYEE_T

Create Index Индексы создаются для улучшения производительности запроса Например, CREATE INDEX NAME_IDX ON EMPLOYEE_T (NAME); Команда удаления индекса: DROP INDEX NAME_IDX

Create Index Следует осторожно рассматривать создание индексов для таблиц - Каждый индекс требует дополнительного пространства внешней памяти Применяемый индекс изменяется, когда значения индексного поля изменяется Производительность может реально сокращена

Ввод данных в таблицы INSERT INTO EMPLOYEE_T VALUES (D325, Alison Hart, 19/04/2000, Sales) INSERT INTO EMPLOYEE_T (EMP_ID, NAME) VALUES (F123,Henry Chang); INSERT INTO YEAR2NET_T SELECT ID, Name, Class FROM ENROLLED WHERE YEAR_LEVEL = 2;

Удаление данных из таблицы DELETE FROM EMPLOYEE_T; DELETE FROM EMPLOYEE_T WHERE EMP_ID = C434;

Изменение данных в таблице UPDATE SALARY_T SET SALARY_AMOUNT = SALARY_AMOUNT * 1.05;

Утверждение Select SELECT наиболее используемое - Позволяет извлекать информацию из 1 или более таблиц 3 наиболее общих оператора: SELECT - поля, и т.д. для показа FROM - определяет таблицы/представления WHERE - условия

Select SELECT и FROM требуются всегда WHERE требуется, когда необходимы условия

Примеры Select SELECT * FROM EMPLOYEE_T Показывается все данные из таблицы employee - Порядок полей такой же как в таблице

Примеры Select SELECT NAME, DEPARTMENT FROM EMPLOYEE_T; Будут показаны только значения name и department из таблицы employee table will be displayed

Примеры Select SELECT EMP_ID, NAME, DEPARTMENT FROM EMPLOYEE_T WHERE COMMENCE_DATE < #01/01/85# Показывает служащих, которые приступили к работе до 1 января 1985

Select с выражениями SELECT ITEM_NO, DESCRIPTION, ON_HAND, COST * 1.25 AS SELL PRICE FROM INVENTORY Показывает описание, количество на руках и продажную цену для всех предметов на складе

Select с Functions SELECT COUNT (*) FROM EMPLOYEE_T; Сколько всего служащих?

Select с Functions SELECT COUNT (ITEM_NO) FROM INVENTORY; Сколько различных предметов в настоящее время в запасе?

Select с Functions SELECT ITEM_NO, MIN(ON_HAND) FROM INVENTORY; Какой предмет с наименьшим количеством запаса?

Select с Functions SELECT ITEM_NO, MAX(ON_HAND) FROM INVENTORY; Какой предмет с максимальным количеством на руках?

Групповые символы Групповые символы используются, когда невозможно использовать точное совпадение. Например, можно знать первый симвло C имени и не помните остальных. Описатель LIKE часто используется с групповыми символами (кроме «*») (*) совпадает со всем

Групповые символы % - используется для любого номера символа; -> LIKE C% _ - «_» используется для точно одного символа; -> LIKE SMITH_; MS Access использует * и _ как символы заполнитли

Операторы сравнения Операторы сравнения аналогичны математическим Пример: SELECT ITEM_NO, NAME FROM INVENTORY WHERE ON_HAND > 30;

Логические операторы AND – объединяет два или более условий и возвращает результат, если все условия истинны OR - объединяет два или более условий и возвращает результат, если некоторое из условий истинны NOT – отрицает любое условие Приоритет: NOT, AND, OR

Пример SELECT ITEM_NO, ON_HAND FROM INVENTORY WHERE ON_HAND > 10 AND DESCRIPTION LIKE %bolts OR COST < 1.00;

Описатель Distinct Используется для устранения дублирующих значений полей в результате Пример: SELECT DISTINCT ITEM_NO FROM ITEM_SALES_T; Замечание: Не доступен в дизайнере MS Access

IN и NOT IN IN и NOT IN используются для совпадения (или несовпадения) из списка значений Список значений может быть получен с использованием утверждения SELECT Пример: SELECT S_ID, SUPPLIER_NAME, PHONE FROM SUPPLIER_T WHERE CITY IN (Tomsk);

Order By ORDER BY используется для сортировки по значению одного или нескольких столбцов в результирующем наборе по увеличению (ASC) или уменьшению (DESC); Пример: SELECT FIRST_NAME, LAST_NAME, CUST_ID, ADDRESS FROM CUSTOMER_T ORDER BY LAST_NAME ASC;

Group By и Having GROUP BY – Группирует строки в таблице промежуточного результата. Аналогично SORT в дизайнере. HAVING – используется только с GROUP BY и как вторичное утверждение WHERE для задания дополнительных условий

Пример GROUP BY SELECT STATE, COUNT (STATE) FROM SUPPLIER GROUP BY STATE;

Пример GROUP BY с HAVING SELECT STATE, COUNT (STATE) FROM CUSTOMER GROUP BY STATE HAVING COUNT (STATE) < 100; Определяет штаты, где меньше чем 100 клиентов

Соединение (Join) Соединение – реляционная операция, которая объединяет 2 таблицы на основе общего домена в одну таблицу или представление Общее правило - Для каждой их 2 таблиц, которые объединяются, должно быть по крайней мере 1 условие WHERE, которое содержит условие на общем домене. В противном будет образовано декартово произведение таблиц.

Естественное соединение Естественное соединение исключает дублирующие столбцы. Наиболее общее использование соединения SELECT ITEM.ITEM_NO, DESCRIPTION FROM ITEM, ORDER_ITEM WHERE ITEM.ITEM_NO = ORDER.ITEM_NO

JOIN SQL-89: Select FROM table1, table2 Where table1.column1= table2.column2 SQL-92: Select FROM table1{INNER|LEFT[OUTER]|RIGHT[O UTER]} JOIN table2 On table1.column1= table2.column2

Пример INNER JOIN SELECT DISTINCTROW tblPatient.SiteNo, tblFollowup.PtID, tblPatient.RandomizationDate, tblFollowup.Visit, tblFollowup.Compliance, tblFollowup.Status FROM tblPatient INNER JOIN tblFollowup ON tblPatient.PtID = tblFollowup.PtID;

Inner JOIN->Left OUTER JOIN-> RIGHT OUTER JOIN. Объединение только тех записей, в которых связанные поля обеих таблиц совпадают. Объединение всех из первой таблицы и только тех записей у которых совпадают связанные поля из второй. Объединение только тех записей у которых совпадают связанные поля из первой

SELECT DISTINCTROW tblPtSubset.PtID, tblPtSubset.ECGCategory, tlkpECGCode.ECGDescriptio n FROM tblPtSubset INNER JOIN tlkpECGCode ON tblPtSubset.ECGCategory = tlkpECGCode.ECGCategory;

SELECT DISTINCTROW tblPtSubset.PtID, tblPtSubset.ECGCategory, tlkpECGCode.ECGDescription FROM tblPtSubset LEFT JOIN tlkpECGCode ON tblPtSubset.ECGCategory = tlkpECGCode.ECGCategory;

SELECT DISTINCTROW tblPtSubset.PtID, tlkpECGCode.ECGCategory, tlkpECGCode.ECGDescription FROM tblPtSubset RIGHT JOIN tlkpECGCode ON tblPtSubset.ECGCategory = tlkpECGCode.ECGCategory;

Соединение нескольких таблиц Синтаксис объединения множества таблиц следующий: FROM (…table1 Join table2 on условиеА) Join table3 on условиеВ) Join…)

SELECT DISTINCTROW tblPatient.PtID, [MDFirstname] & " " & [MDLastName] AS MD, tblPatient.Age, tblPatient.Sex, tblPatient.DischargeDate FROM tblMD INNER JOIN (tblPatient INNER JOIN tblPtDoctors ON tblPatient.PtID = tblPtDoctors.PtID) ON tblMD.MDNo = tblPtDoctors.MDNo;

Self Joins

Sub FinlandCustomers () ' Эта процедура изменяет источник записей для формы Customers ' для включения заказчиков из Finland. ' Объявляются переменные database, form и query. Dim dbs As Database, frm As Form, qdf As QueryDef ' Назначается текущую база данных переменной dbs. Set dbs = CurrentDB() ' Создается новый запрос с использованием метода CreateQueryDef ' и назначается переменной qdf, ' Устанавливаются свойства Name и SQL для запроса, ' затем добавляется запрос к семейству сохраненных запросов. Set qdf = dbs.CreateQueryDef() qdf.Name = "Finland Query" qdf.SQL = "SELECT * FROM Customers WHERE Country='Finland';" dbs.QueryDefs.Append qdf dbs.QueryDefs.Refresh ' Форма Customers назначается переменной frm, затем изменяется ' источник записей формы на новый запрос. Set frm = Forms!Customers frm.RecordSource = qdf.Name End Sub