Внутреннее (естественное) соединение таблиц
Таким способом можно соединять только таблицы, имеющие общие столбцы. При выполнении данной операции соединяются (склеиваются) только строки, имеющие общие значения в столбце связи. Как правило, таким способом соединяются таблицы, связанные отношением «один-ко-многим», а в качестве столбцов связи используются первичный ключ главной таблицы и внешний ключ подчиненной. Таким образом, те строки главной таблицы, для которых нет связанных строк в подчиненной таблице, при внутреннем соединении вообще не попадут в результат запроса.
Работа с внешним соединением таблиц (запрос OUTER JOIN)
а)выборка из декартового произведения
Начнем с примеров. Пусть требуется вывести фамилии всех студентов и их оценки. Текст запроса будет выглядеть так:
SELECT students.namest, marks.mark
FROM students, marks
WHERE students.cod_st=marks.cod_st
б)операция соединения [INNER] JOIN
Тот же самый запрос, соединяющий студентов с их оценками, будет записан несколько по-другому:
SELECT students .name st, marks.mark
FROM students JOIN marks ON students.cod_st=marks.cod_st
Внешнее соединение таблиц (операция OUTER JOIN)
При внешнем соединении, в отличие от внутреннего, в результат выборки попадают не только все связанные строки обеих таблиц, но и строки одной из таблиц (или обеих), для которых нет связанных в другой таблице. Недостающим значениям столбцов другой таблицы при этом присваивается значение NULL.
Три вида внешних соединений:
LEFT [OUTER] JOIN - левое внешнее соединение
RIGHT [OUTER] JOIN - правое внешнее соединение
FULL [OUTER] JOIN - полное внешнее соединение.
INNER JOIN : возвращает строки, когда есть совпадение в обеих таблицах. LEFT JOIN : возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений.
RIGHT JOIN : возвращает все строки из правой таблицы, даже если в левой таблице нет совпадений.
Полный JOIN : он объединяет результаты как левого, так и правого внешних соединений.
38. Язык SQL : использование подзапросов.
Подзапрос - очень мощное средство языка SQL. Он позволяет строить сложные иерархии запросов, многократно выполняемые в процессе построения результирующего набора или выполнения одного из операторов изменения данных ( DELETE , INSERT , UPDATE ).
Подзапрос это обычный aql запрос который мы можем использовать в другом нашем основном запросе и их еще часто называют вложенными запросами. С помощью sql можно вкладывать запросы друг в друга.
Условно подзапросы иногда подразделяют на три типа, каждый из которых является сужением предыдущего:
- табличный подзапрос, возвращающий набор строк и столбцов;
- подзапрос строки, возвращающий только одну строку, но, возможно, несколько столбцов (такие подзапросы часто используются во встроенном SQL);
- скалярный подзапрос, возвращающий значение одного столбца в одной строке.
Подзапрос позволяет решать следующие задачи:
- определять набор строк, добавляемый в таблицу на одно выполнение оператора INSERT ;
- определять данные, включаемые в представление, создаваемое оператором CREATE VIEW ;
- определять значения, модифицируемые оператором UPDATE ;
- указывать одно или несколько значений во фразах WHERE и HAVING оператора SELECT ;
- определять во фразе FROM таблицу как результат выполнения подзапроса ;
- применять коррелированные подзапросы. Подзапрос называется коррелированным, если запрос, - содержащийся в предикате, имеет ссылку на значение из таблицы (внешней к данному запросу), которая проверяется посредством данного предиката.
Простым примером использования подзапроса может служить следующий оператор:
SELECT * from tbl1
WHERE f2=(SELECT f2 FROM tbl2
WHERE f1=1);
В данном операторе подзапрос всегда должен возвращать единственное значение, которое будет проверяться в предикате. Если подзапрос вернет более одного значения, то СУБД выдаст сообщение об ошибке выполнения SQL-оператора.В случае если подзапрос не выберет ни одной строки, то предикат будет равен UNKNOWN, что большинством СУБД интерпретируется как FALSE.
Подзапрос может быть указан как в предикате, определяемом фразой WHERE, так и в предикате по группам, определяемом фразой HAVING.
Например:
SELECT avg_f1, COUNT (f2) from tbl1
GROUP BY avg_f1
HAVING avg_f1 >(SELECT f1 FROM tbl1
WHERE f3='a1');
39. Язык SQL : операторы EXISTS , ANY , ALL в командах с подзапросом.
.
В случае, если подзапрос возвращает множество результатов, для подобных запросов необходимо использовать соответствующие операторы сравнения:
· IN – эквивалетно значению любого элемента из подзапроса
· ANY – сравнивает значение со всеми значениями из подзапроса, возвращая true если сравнение верно для любого ОДНОГО значения из списка
· ALL – сравнивает значение со всеми значениями из подзапроса, возвращая true если сравнение верно для ВСЕХ значений из списка
Разберем свойства и методы применения каждого из операторов по отдельности.
SELECT *
FROM emp
WHERE job in (SELECT job
FROM emp
WHERE sal > 1000)
Оператор IN – опеспечивает сравнения столбца job со всеми значениями из подзапроса. В результате данного примера выйдет столько строк, сколько будет совпадений столбца со значениями подзапроса.
Оператор ANY, как уже отмечалось ANY сравнивает значение со всеми значениями из подзапроса. При этом зависимости от знака равенства смысл оператора изменяется. Таким образом, если использовать знак равенства вместе с ANY у нас получиться эквивалент оператору IN. При этом если будет применяться знак < и ANY то будет иметь смысл меньше минимального значения из списка. Если же > и ANY то будет иметь смысл больше максимального значения из списка.
Оператор ALL, как уже отмечалось ALL сравнивает значение со всеми значениями из подзапроса. При этом зависимости от знака равенства смысл оператора изменяется. При этом если будет применяться знак < и ALL то будет иметь смысл меньше минимального значения из списка. Если же > и ALL то будет иметь смысл больше максимального значения из списка.
EXISTS
Оператор EXISTS берет подзапрос, как аргумент, и оценивает его как верный, если подзапрос возвращает какие-либо записи и неверный, если тот не делает этого. То есть если наш подзапрос возвращает хотя бы одну строку то - это выражение принимается за истину.
Читателю может показаться что это функция редко используемая - но такое мнение может сложится только при небольшом опыте общения с коллегами из департамента маркетинга. Иногда маркетологи придумывают ужасные условия и хотят тут же получить результат. EXIST поможет вам справиться как и с ужасными условиями так и с быстротой получение результата. Так как использование оператора EXIST увеличивает производительность выполнения отчета.
40. Язык SQL : основные команды определения данных.
Язык определения данных используется для создания и изменения структуры базы данных и ее составных частей - таблиц, индексов, представлений (виртуальных таблиц), а также триггеров и сохраненных процедур. Основными его командами являются:
1. CREATE DATABASE (Эта команда создает новую базу данных)
CREATE DATABASE db_name;
2. USE (Для того чтобы начать работу с таблицами, необходимо сообщить MySQL с какой базой данных вы намерены работать. Это осуществляется при помощи команды USE)
USE db_name;
3. CREATE TABLE( создает новую таблицу в выбранной базе данных)
CREATE TABLE table_name [(create_definition, ...)]
4. DESCRIBE( показывает структуру созданных таблиц)
DESCRIBE tаble_name
5. ALTER TABLE( позволяет изменить структуру таблицы. Эта команда позволяет добавлять и удалять столбцы, создавать и уничтожать индексы, переименовывать столбцы и саму таблицу)
ALTER TABLE table_name alter_spec
6. DROP TABLE( для удаления одной или нескольких таблиц)
DROP TABLE table_name [ ,table_name,...]
7. DROP DATABASE ( удаляет базу данных со всеми таблицами входящими в её состав)
DROP DATABASE database_name
8. INSERT INTO … VALUES(вставляет новые записи в существующую таблицу)
INSERT INTO table_name VALUES (values,…)
9. DELETE (удаляет из таблицы table_name записи, удовлетворяющие заданным в definition условиям, и возвращает число удаленных записей.)
DELETE FROM table_name [WHERE definition]
10. SELECT( предназначена для извлечения строк данных из одной или нескольких таблиц)
SELECT column,...
[FROM table WHERE definition]
[ORDER BY col_name [ASC | DESC], ...]
[LIMIT [offset], rows
11. UPDATE (обновляет столбцы таблицы table в соответствии с их новыми значениями в строках существующей таблицы. В выражении SET указывается, какие именно столбцы следует модифицировать и какие величины должны быть в них установлены. В выражении WHERE, если оно присутствует, задается, какие строки подлежат обновлению. В остальных случаях обновляются все строки. Ключевое слово LIMIT позволяет ограничить число обновляемых строк.
UPDATE table
SET col_name1=expr1 [, col_name2=expr2 ...
[WHERE definition]
12. SHOW ( получения списка баз данных и таблиц в выбранной базе данных)
41. Язык SQL : создание и использование представлений (просмотров) и индексов.
Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления или Views представляют виртуальные таблицы. Но в отличии от обычных стандартных таблиц в базе данных представления содержат запросы,
Представления дают нам ряд преимуществ. Они упрощают комплексные SQL-операции. Они защищают данные, так как представления могут дать доступ к части таблицы, а не ко всей таблице. Представления также позволяют возвращать отформатированные значения из таблиц в нужной и удобной форме.
Преимущества использования представлений:
-Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление. Это очень удобно в случае если пользователю нужно дать права на отдельные строки таблицы или возможность получения не самих данных, а результата каких-то действий над ними.
-Позволяет разделить логику хранения данных и программного обеспечения. Можно менять структуру данных, не затрагивая программный код, нужно лишь создать представления, аналогичные таблицам, к которым раньше обращались приложения. Это очень удобно когда нет возможности изменить программный код или к одной базе данных обращаются несколько приложений с различными требованиями к структуре данных.
-Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.
Создание представлений
Для создания представления используется оператор CREATE VIEW, имеющий следующий синтаксис:
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]