Основы SQL MS ACCESS

1. Основы SQL MS ACCESS

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

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

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

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

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

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

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

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

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

11. 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));

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

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

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

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

16. Ввод данных в таблицы 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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

42. JOIN • SQL-89: • Select <список> FROM table1, table2 Where table1.column1= table2.column2 • SQL-92: • Select <список> FROM table1{INNER|LEFT[OUTER]|RIGHT[OUTER]} JOIN table2 • On table1.column1= table2.column2

43. Пример 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;

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

47. SELECT DISTINCTROW tblPtSubset.PtID, tblPtSubset.ECGCategory, tlkpECGCode.ECGDescription • FROM tblPtSubset INNER JOIN tlkpECGCode ON tblPtSubset.ECGCategory = tlkpECGCode.ECGCategory;

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

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