БД_Основы SQL1


Чтобы посмотреть презентацию с картинками, оформлением и слайдами, скачайте ее файл и откройте в PowerPoint на своем компьютере.
Текстовое содержимое слайдов презентации:

Основы SQL Тригуб Н.А. Справка iBase.ruСтандартные логин и пароль для IB: SYSDBA masterkeysql-school.infohttps://downloads.embarcadero.com/free/interbaseБен Форта. Освой самостоятельно язык запросов SQL / Пер. с англ. — 3-е изд. — 288 с. Пол Уилтон, Джон Колби. Язык запросов SQL для начинающих / Пер. с англ. — 496 с. К. Дж. Дейт. Введение в системы баз данных / Пер. с англ. — 1328 с. Кевин Клайн. SQL. Справочник. — 832 с. Справка Сайты СУБДIBM DB2 - (http://ibm.com/db2)Microsoft Access - (http://office.microsoft.com/ru-ru/access/FX100487571049.aspx)Microsoft SQL Server - (http://www.microsoft.com/Rus/sql/)MySQL - (http://mysql.com/)Oracle - (http://www.oracle.com/global/ru/index.html)PostgreSQL  - (http://www.postgresql.org/)Sybase Adaptive Server - (http://www.sybase.com/) Основные определения Structured Query Language — «язык структурированных запросов SQL является- информационно-логический язык, предназначенный для описания хранимых данных, для извлечения хранимых данных и для модификации данных. Первый официальный стандарт языка SQL был принят ANSI(Американский Национальный Институт Стандартов) в 1986 году и ISO (Международной организацией по стандартизации) в 1987 году (так называемый SQL-86) и несколько уточнён в 1989 году. Дальнейшее развитие языка поставщиками СУБД потребовало принятия в 1992 году нового расширенного стандарта (ANSI SQL-92 или просто SQL2). Следующим стандартом стал SQL:1999 (SQL3). В настоящее время действует стандарт, принятый в 2003 году (SQL:2003) с небольшими модификациями, внесёнными позже. История версий стандарта SQL 1986. SQL-86,SQL-87. Первый вариант стандарта, принятый институтом ANSI и одобренный ISO в 1987 году.1989. SQL-89,FIPS 127-1. Немного доработанный вариант предыдущего стандарта.1992. SQL-92. SQL2, FIPS 127-2. Значительные изменения (ISO 9075); уровень Entry Level стандарта SQL-92 был принят как стандарт FIPS 127-2.1999. SQL:1999. SQL3. Добавлена поддержка регулярных выражений, рекурсивных запросов, поддержка триггеров, базовые процедурные расширения, нескалярные типы данных и некоторые объектно-ориентированные возможности.2003. SQL:2003. Введены расширения для работы с XML-данными, оконные функции (применяемые для работы с OLAP-базами данных), генераторы последовательностей и основанные на них типы данных.2006. SQL:2006. Функциональность работы с XML-данными значительно расширена. Появилась возможность совместно использовать в запросах SQL и XQuery.2008. SQL:2008. Улучшены возможности оконных функций, устранены некоторые неоднозначности стандарта SQL:2003 Соответствие стандарту Впервые понятие «уровня соответствия» было предложено в стандарте SQL-92. ANSI определяли четыре уровня соответствия реализации этому стандарту, где каждый последующий уровень соответствия заведомо подразумевал соответствие предыдущему уровню :Entry (базовый) Transitional (переходный) — проверку на соответствие этому уровню проводил только NIST Intermediate (промежуточный) Full (полный)Положение изменилось с введением стандарта SQL:1999. в котором стандарт приобрёл модульную структуру — основная часть стандарта была вынесена в раздел «SQL/Foundation», все остальные были выведены в отдельные модули. Соответственно, остался только один уровень совместимости — что означает поддержку этой основной части. Поддержка остальных возможностей оставлена на усмотрение производителей СУБД. Аналогичное положение имело место и с последующими версиями стандарта. Достоинства SQL Независимость от конкретной СУБДНаличие стандартовДекларативность Недостатки SQL Несоответствие реляционной модели данныхНеопределённые значения (nulls) Явное указание порядка колонок слева направо Колонки без имени и дублирующиеся имена колонок Отсутствие поддержки свойства «=» Использование указателей Высокая избыточностьСложностьОтступления от стандартовСложность работы с иерархическими структурами Расширения SQL InterBase/Firebird – краткое название языка –PSQL. Procedural SQLIBM DB2 – краткое название языка – SQL PL. SQL Procedural Language (расширяет SQL/PSM); также в DB2 хранимые процедуры могут писаться на обычных языках программирования: Си, Java и т. д.MS SQL Server/Sybase ASE – краткое название языка – Transact-SQL. Transact-SQLMySQL – краткое название языка – SQL/PSM. SQL/Persistent Stored ModuleOracle – краткое название языка – PL/SQL. Procedural Language/SQL (основан на языке Ada)PostgreSQL – краткое название языка – PL/pgSQL. Procedural Language/PostgreSQL Structured Query Language (очень похож на Oracle PL/SQL) Операторы SQL Операторы SQL делятся на:операторы определения данных (Data Definition Language, DDL) операторы манипуляции данными (Data Manipulation Language, DML) операторы определения доступа к данным (Data Control Language, DCL) операторы управления транзакциями (Transaction Control Language, TCL) Операторы DDL Create - СОЗДАТЬAlter - ИЗМЕНИТЬDrop - УДАЛИТЬ Объекты БД DataBase – база данных БДTable - таблицаTrigger - триггерProcedure - процедураGenerator - генераторException – исключениеVIEW - просмотры(вьюшки) Создание таблиц CREATE TABLE имя_таблицы ( имя_столбца тип_данных [NULL | NOT NULL] [CONSTRAINTS], имя_столбца тип_данных[NULL|NOT NULL]  [CONSTRAINTS] , ..... );ОграниченияНа пустое значениеНа значение по умолчаниюНа определенные ранее параметры Изменение таблиц ALTER TABLE имя таблицыADD | DROP имя_столбца тип_данных [NULL | NOT >NULL] [CONSTRAINTS] , ADD I DROP имя_столбца тип_данных [NULL|NOT >NULL] [CONSTRAINTS] ,Изменения не только структуры но и целостности Удаление Объектов DROP ТИП_ОБЪЕКТА имя_объекта;ТИП_ОБЪЕКТА:Table - таблицаTrigger - триггерProcedure - процедураGenerator - генераторException – исключениеVIEW – вьюшкаINDEX – индексForeign key – внешний ключPrimary key – первичный ключ Создание Объектов типа procedure, trigger CREATE ТИП_ОБЪЕКТА имя_объекта [параметры) [опции] AS SQL statement;Для ProcedureВходные параметрыВыходные параметрыПринцип организационной структыр возвращаемых значений Создание Триггеров CREATE TRIGGER name FOR table [ACTIVE | INACTIVE] {BEFORE | AFTER} {DELETE | INSERT | UPDATE} [POSITION number] AS terminator = [] = DECLARE VARIABLE variable ; [DECLARE VARIABLE variable ; ...] = BEGIN [ ...] END Создание Генераторов CREATE GENERATOR имя_генератора; CREATE TRIGGER имя_триггера FOR имя_таблицы BEFORE INSERT POSITION 0 AS BEGIN NEW. имя_поля = GEN_ID(имя_генератора, 1); END; Ссылочная целостность CREATE TABLE ORDERS (Order_ID integer, Order_Date date, Customer_SID integer, Amount double, Primary Key (Order_ID), Foreign Key (Customer_SID) references CUSTOMER(SID)); Пример Внешний ключ ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID); Операторы DML SELECT INSERT UPDATE DELETE SELECT SELECT[ALL|DISTINCT]<Список полей>|*FROM <Список таблиц>[WHERE <Предикат-условие выборки или соединения JOIN>][GROUP BY <Список полей результата>][HAVING <Предикат-условие для группы>][ORDER BY <Список полей, по которым упорядочить вывод> [ASC|DESC]] В выражении условий раздела WHERE могут быть использованы следующие предикаты:Предикаты сравнения { =, <>, >,<, >=,<= }, которые имеют традиционный смысл. Предикат Between A and B —принимает значения между A и B. Предикат истинен, когда сравниваемое значение попадает в заданный диапазон, включая границы диапазона. Одновременно в стандарте задан и противоположный предикат Not Between A and B, который истинен тогда, когда сравниваемое значение не попадает в заданный интервал, включая его границы. Предикат вхождения в множество IN (множество) истинен тогда, когда сравниваемое значение входит в множество заданных значений. При этом множество значений может быть задано простым перечислением или встроенным подзапросом. Одновременно существует противоположный предикат NOT IN (множество), который истинен тогда, когда сравниваемое значение не входит в заданное множество. Предикаты сравнения с образцом LIKE и NOT LIKE. Предикат LIKE требует задания шаблона, с которым сравнивается заданное значение, предикат истинен, если сравниваемое значение соответствует шаблону, и ложен в противном случае. Предикат NOT LIKE имеет противоположный смысл. В выражении условий раздела WHERE могут быть использованы следующие предикаты:По стандарту в шаблон могут быть включены специальные символы:символ подчеркивания (_) — для обозначения любого одиночного символа; символ процента (%) — для обозначения любой произвольной последовательности символов; остальные символы, заданные в шаблоне, обозначают самих себя. Предикат сравнения с неопределенным значением IS NULL. Понятие неопределенного значения было внесено в концепции баз данных позднее. Неопределенное значение интерпретируется в реляционной модели как значение, неизвестное на данный момент времени. Это значение при появлении дополнительной информации в любой момент времени может быть заменено на некоторое конкретное значение. При сравнении неопределенных значений не действуют стандартные правила сравнения: одно неопределенное значение никогда не считается равным другому неопределенному значению. Для выявления равенства значения некоторого атрибута неопределенному применяют специальные стандартные предикаты: <имя атрибута> IS NULL и <имя атрибута> IS NOT NULL. A B Not A A & B A | B TRUE TRUE FALSE TRUE TRUE TRUE FALSE FALSE FALSE TRUE TRUE Null FALSE Null TRUE FALSE TRUE TRUE FALSE TRUE FALSE FALSE TRUE FALSE FALSE FALSE Null TRUE FALSE Null Null TRUE Null Null TRUE Null FALSE Null FALSE Null Null Null Null Null Null Введение Null-значений вызвало необходимость модификации двузначной логики и превращения ее в трехзначную: < Логическое выражение > IS {TRUE | FALSE | UNKNOWN} R1 = (ФИО, Дисциплина, Оценка); R2 = (ФИО, Группа); R3 = (Группы, Дисциплина) R1 ФИО Дисциплина Оценка Петров Ф. И. Базы данных 5 Сидоров К. А. Базы данных 4 Миронов А. В. Базы данных 2 Степанова К. Е. Базы данных 2 Крылова Т. С. Базы данных 5 Сидоров К. А. Теория информации 4 Степанова К. Е. Теория информации 2 Крылова Т. С. Теория информации 5 Миронов А. В. Теория информации Null Владимиров В. А. Сети и телекоммуникации 5 Трофимов П. А. Сети и телекоммуникации 4 Иванова Е. А. Сети и телекоммуникации 5 Уткина Н. В. Сети и телекоммуникации 5 Владимиров В. А. Английский язык 4 Трофимов П. А. Английский язык 5 Иванова Е. А. Английский язык 3 Уткина Н. В. Английский язык 5 R2 ФИО Группа Петров Ф. И. 4906 Сидоров К. А. 4906 Миронов А. В. 4906 Крылова Т. С. 4906 Владимиров В. А. 4807 Трофимов П. А. 4807 Иванова Е. А. 4807 Уткина Н. В. 4807 R3 Группа Дисциплина 4906 Базы данных 4906 Теория информации 4906 Английский язык 4807 Английский язык 4807 Сети и телекоммуникации Примеры использования оператора SELECT 1. Вывести список всех групп (без повторений), где должны пройти экзамены: SELECT DISTINCT Группа FROM R3 Группа 4906 4807 2. Вывести список студентов, которые сдали экзамен по дисциплине «Базы данных» на «отлично»:SELECT ФИО FROM R1WHERE Дисциплина = ‘Базы данных’ AND Оценка = 5 ФИО Петров Ф. И. Крылова Т. С. 3. Вывести список лентяев, имеющих несколько двоек. SELECT DISTINCT a.ФИОFROM R1 a, R1 bWHERE a.ФИО = b.ФИО ANDa.Дисциплина <> b.Дисциплина ANDa.Оценка <= 2 AND b.Оценка <= 2 ФИО Степанова К. Е. Примеры использования оператора SELECT 4. Найти студентов, пришедших на экзамен, но не сдававших его с указанием названия дисциплины:SELECT ФИО, Дисциплина FROM R1WHERE Оценка IS NULL ФИО Дисциплина Миронов А. В. Теория информации 5. Вычислить количество студентов, сдававших экзамены по каждой дисциплине (применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе):SELECT R1.Дисциплина, COUNT(*) FROM R1GROUP BY R1.Дисциплина Дисциплина COUNT(*) Базы данных 5 Теория информации 4 Сети и телекоммуникации 4 Английский язык 4 Примеры использования оператора SELECT 6. Если нужно сосчитать количество сдавших экзамен по какой-либо дисциплине, то нам необходимо исключить неопределенные значения из исходного отношения перед группировкой:SELECT R1.Дисциплина, COUNT(*) FROM R1WHERE R1.Оценка IS NOT NULL GROUP BY R1.Дисциплина Дисциплина COUNT(*) Базы данных 5 Теория информации 3 Сети и телекоммуникации 4 Английский язык 4 7. Определить для каждой группы и каждой дисциплины количество успешно сдавших экзамен и средний балл по дисциплинеSELECT R1.Дисциплина, COUNT(*), AVR(Оценка)FROM R1, R2 WHERE R1.ФИО = R2.ФИО AND R1.Оценка IS NOT NULL ANDR1.Оценка > 2 GROUP BY R1.Оценка, R1.Дисциплина Дисциплина COUNT(*) AVR(Oцeнкa) Базы данных 3 4.67 Теория информации 2 3.67 Сети и телекоммуникации 4 4.75 Английский язык 4 4.25 8. Список тех, кто сдал все положенные экзамены:SELECT ФИО FROM R1 as aWHERE Оценка > 2GROUP BY ФИОHAVING COUNT(*) = (SELECT COUNT(*)FROM R2, R3 WHERE R2.Группа=R3.Группа AND R2.ФИО=a.ФИО) Примеры использования оператора SELECT ФИО Владимиров В. А. Трофимов П. А. Иванова Е. А. 9. Список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал: SELECT ФИОFROM R2 a, R3WHERE а.Группа=R3.Группа AND R3.Дисциплина = ‘Английский язык’ AND NOT EXISTS (SELECT ФИО FROM R1 WHERE R1.ФИО=a.ФИО AND R1.Дисциплина = ‘Английский язык’) ФИО Петров Ф. И. Сидоров К. А. Миронов А. В. Крылова Т. С. Пример выбора «первых» 10 значений из таблицы Firebird SELECT FIRST 10 * FROM [T] Interbase SELECT * FROM [T] ROWS 10 Microsoft SELECT TOP 10 [PERCENT] * FROM T ORDER BY col MySQL SELECT * FROM T LIMIT 10 PostgreSQL SELECT * FROM T LIMIT 10 Oracle SELECT * from T WHERE ROWNUM <= 10 INSERT INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES (expression,...),(...),... INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name=expression, col_name=expression, ... Расшифровка LOW_PRIORITY – низкий приоритет –будет выполнено когда нет клиентовDELAYED - позволяет пользователю продолжать работу сразу же не дожидаясь, пока инструкция вставки будет завершена IGNORE - строки значений, которых дублируют существующий ключ PRIMARY или UNIQUE в таблице, игнорируются и не будут вставлены, если не определяете IGNORE, вставка будет прервана, если имеется любая строка, которая дублирует существующее значение ключа. INSERT INTO R1 ([ФИО], [ДИСЦИПЛИНА], [ОЦЕНКА]) VALUES (‘Степанов А.П.’, ‘Базы данных’, 4) INSERT INTO R1 VALUES (‘Петровский П.В.’, ‘Базы данных’, Null)INSERT INTO R1 (([ФИО]) SELECT ФИО FROM R2 Пример INSERT DELETE DELETE FROM имя_таблицы [WHERE условия_отбора]Примеры:DELETE FROM R1DELETE FROM R2WHERE ФИО = 'Миронов А.В.' DELETE Примеры (продолжение):Исключить неуспевающих студентов (по закону о высшем образовании неуспевающим считается студент, имеющий две и более задолженности по последней сессии):DELETE FROM R2 WHERE R2.ФИО IN (SELECT R1.ФИО FROM R1WHERE Оценка = 2 OR Оценка IS NULL GROUP BY R1.ФИО HAVING COUNT(*) >= 2) UPDATE UPDATE имя_таблицыSET имя_столбца = новое_значение [WHERE условие_отбора]Примеры:Пересдача экзамена с 2 на 4UPDATE R1 SET R1.Оценка = 4WHERE R1.ФИО = ‘Степанова К.Е.’ AND R1.Дисциплина = ‘Базы данных’ UPDATE Примеры: =>UPDATE R4SET R4.Kypc = R4.Kypc + 1 R4 Группа Курс 4906 3 4807 4 R4 Группа Курс 4906 4 4807 5 Операторы определения доступа к данным (Data Control Language, DCL) Оператор предоставления привилегий GRANTОператор отмены привилегий REVOKEОператоры создания пользователей (ролей) CREATE USER (CREATE ROLE)Оператор удаления пользователей (ролей) DROP USER (DROP ROLE) Оператор GRANT GRANT {<список действий> | ALL PRIVILEGES } ON <имя_объекта> TO {<имя_пользователя> | PUBLIC } [WITH GRANT OPTION]/* объекты: таблицы, представления, хранимой процедуры, триггера */ Примеры:1. GRANT INSERT ON Tab1 TO user2 2. GRANT SELECT ON Tab1 TO user33. GRANT SELECT, UPDATE (COST) ON Tab1 TO user34. GRANT ALL PRIVILEGES ON Tab1 TO user4 WITH GRANT OPTION Оператор GRANT Примеры:5. GRANT EXECUTE ON COUNT_EX TO user4 6. GRANT CREATE TABLE, ALTER TABLE, DROP TABLE ON DB_LIB TO user1 7. GRANT CREATE DATABASE ON SERVER_0 TO main_user Оператор GRANT Оператор REVOKE REVOKE {<список операций> | ALL PRIVILEGES}ON <имя_объекта>FROM {<список пользователей>|PUBLIC}{CASCADE | RESTRICT} Примеры:REVOKE ALL PRIVILEGESON Tab1 FROM user4 CASCADE2. REVOKE EXECUTE ON COUNT_EX FROM PUBLIC CASCADE 3. REVOKE ALL PRIVILEGES ON Tab1 FROM user4 RESTRICT Оператор REVOKE

Приложенные файлы

  • ppt 886990
    Размер файла: 712 kB Загрузок: 1

Добавить комментарий