Insert into Пример 1

INSERT INTO dept VALUES (50, » ПРОДУКЦИЯ «, » САН-ФРАНЦИСКО «);

INSERT INTO Customers (city, cname,cnum) VALUES (‘London’,’Hoffman’,2001);

INSERT INTO Пример 2 Нижеприведенная команда копирует данные сотрудников фирмы, комисионные которых превышают 25% от дохода в таблицу bonus:

INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal;

INSERT INTO Пример 3 Если нужно вставить NULL-значение, необходимо указать его как обычное значение следующим образом:

INSERT INTO Salespeople VALUES (1001,’Peel’,NULL,12);

INSERT INTO Пример 4 Команду INSERT можно применить для того, чтобы извлечь значения из одной таблицы и разместить их в другой, воспользовавшись для этого запросом. Для этого достаточно заменить предложение VALUES на соответствующий запрос:

INSERT INTO Londonstaff SELECT * FROM Salespeople WHERE city = ‘London’;

INSERT MySQL

Для вставки новых строк в базу данных MySQL используется команда INSERT, примеры команды INSERT приведены ниже: INSERT INTO Пример 1. Вставка новой строки в таблицу table_name.

INSERT INTO table_name VALUES (’1′,’165′,’0′,’name’);

INSERT INTO Пример 2. Вставка новой строки в таблицу table_name с указанием вставки данных в нужные нам колонки.

INSERT INTO table_name VALUES (’1′,’165′,’0′,’name’);

В базе данных MySQL имеется возможность вставлять множество новых строк, используя одну команду INSERT. INSERT INTO Пример 3. Вставка несколько строк в таблицу table_name.

INSERT INTO table_name (tbl_id, chislo, chislotwo, name) VALUES (’1′,’159′,’34′,’name1′), (’2′,’14′,’61′,’name2′), (’3′,’356′,’8′,’name3′);

Лекция 10

SQL DELETE

Команда DELETE

Команда DELETE удаляет строки из таблицы или представления основной таблицы базы данных, например, в MySQL, Oracle.

Команда DELETE. Синтаксис команды DELETE

Синтаксис команды DELETE

Команда DELETE. Основные ключевые слова и параметры команды DELETE

• schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя

• table view — имя таблицы, из которой удаляются строки; если определяется представление, сервер удаляет строки из основной таблицы представления

• subquery — подзапрос, с помощью которого выбираются строки для удаления; сервер выполняет подзапрос и использует строки его результата как таблицу фразы FROM

• WHERE — удаляет только строки, которые удовлетворяют условию; условие может ссылаться на таблицу и содержать подзапрос.

При выдаче утверждения DELETE включается любой DELETE-триггер, определенный на таблице. Команда DELETE Пример №1 Удаление всех строк из таблицы:

DELETE FROM temp_assign;

В данном примере команда DELETE удаляет все строки без исключения. Команда DELETE Пример №2. Удаляет из таблицы всех продавцов, у которых комиссионные меньше 100 у.е. в месяц:

DELETE FROM emp WHERE JOB = ‘SALESMAN’ AND COMM < 100;

В данном примере команда DELETE удаляет все строки, которые попадают под условие JOB = ‘SALESMAN’ AND COMM < 100; Команда DELETE Пример №3 Предыдущий пример можно записать по-другому:

DELETE FROM (select * from emp) WHERE job = ‘SALESMAN’ AND comm < 100;

Для удаления всех записей в MySQL можно использовать следующую команду DELETE:

DELETE * FROM table_nam;

SQL UPDATE

Команда UPDATE

Команда UPDATE — производит изменения в уже существующей записи или во множестве записей в таблице SQL. Изменяет существующие значения в таблице или в основной таблице представления.

Команда UPDATE Синтаксис команды

Синтаксис команды UPDATE

Команда UPDATE. Основные ключевые слова и параметры команды UPDATE

• schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя

• table view — имя таблицы SQL, в которой изменяются данные; если определяется представление, данные изменяются в  основной таблице SQL представления

• subquery_1 — подзапрос, который сервер обрабатывает тем же самым способом как представление

• сolumn — столбец таблицы SQL или представления SQL, значение которого изменяется; если столбец таблицы из предложения SET опускается, значение столбца остается неизменяемым

• expr — новое значение, назначаемое соответствующему столбцу; это выражение может содержать главные переменные и необязательные  индикаторные переменные

• subquery_2 — новое значение, назначаемое соответствующему столбцу

• subquery_3 — новое значение, назначаемое соответствующему столбцу

WHERE — определяет диапазон изменяемых строк теми, для которых определенное условие является TRUE; если опускается эта фраза, модифицируются все строки в таблице или представлении. При выдаче утверждения UPDATE включается любой UPDATE-триггер, определенный на таблице. Подзапросы. Если предложение SET содержит подзапрос, он возвращает точно одну строку для каждой модифицируемой строки. Каждое значение в результате подзапроса назначается соответствующим столбцам списка в круглых скобках. Если подзапрос не возвращает никакие строки, столбцу назначается NULL. Подзапросы могут выбирать данные из модифицируемой таблицы. Предложение SET может совмещать выражения и подзапросы.

Команда UPDATE Пример 1 Изменение для всех покупателей рейтинга на значение, равное 200:

UPDATE Customers SET rating = 200;

Команда UPDATE Пример 2 Замена значения столбца во всех строках таблицы, как правило, используется редко. Поэтому в команде UPDATE, как и в команде DELETE, можно использовать предикат. Для выполнения указанной замены значений столбца rating, для всех покупателей, которые обслуживаются продавцом Giovanni (snum = 1003), следует ввести:

UPDATE Customers SET rating = 200 WHERE snum = 1001;

Команда SQL UPDATE Пример 3 В предложении SET можно указать любое количество значений для столбцов, разделенных запятыми:

UPDATE emp SET job = ‘MANAGER’, sal = sal + 1000, deptno = 20 WHERE ename = ‘JONES’;

Команда UPDATE Пример 4 В предложении SET можно указать значение NULL без использования какого-либо специального синтаксиса (например, такого как IS NULL). Таким образом, если нужно установить все рейтинги покупателей из Лондона (city = ‘London’) равными NULL-значению, необходимо ввести:

UPDATE Customers SET rating = NULL WHERE city = ‘London’;

Команда UPDATE Пример 5 Поясняет использование следующих синтаксических конструкций команды UPDATE:

• Обе формы предложения SET вместе в одном утверждении.

• Подзапрос.

• Предложение WHERE, ограничивающее диапазон  модифицируемых строк.

UPDATE emp a SET deptno = (SELECT deptno FROM dept WHERE loc = ‘BOSTON’), (sal, comm) = (SELECT 1.1*AVG(sal), 1.5*AVG(comm) FROM emp b WHERE a.deptno = b.deptno) WHERE deptno IN (SELECT deptno FROM dept WHERE loc = ‘DALLAS’ OR loc = ‘DETROIT’);

Вышеупомянутое утверждение UPDATE выполняет следующие операции:

• Модифицирует только тех служащих, кто работают в Dallas или Detroit

• Устанавливает значение колонки deptno для служащих из Бостона

• Устанавливает жалованье каждого служащего в 1.1 раз больше среднего жалованья всего отдела

• Устанавливает комиссионные каждого служащего в 1.5 раза больше  средних комиссионных всего отдела

SQL SELECT

Команда SELECT

Команда SELECT производит выборку данных из таблиц по запросу.

Язык SQL допускает три типа синтаксических конструкций, начинающихся с ключевого слова SELECT:

1. оператор выборки (select statement)

2. спецификация курсора (cursor specification)

3. подзапрос (subquery).

Синтаксис команды SELECT в MySQL

Синтаксис команды SELECT в Oracle

Основные ключевые слова и параметры команды SELECT в MySQL

• DISTINCT — возвращает только одно значение  для каждого набора одинаковых выбранных значений столбца

• ALL — возвращает все выбранные строки, включая все повторяющиеся значения столбцов (принимается по умолчанию)

• * — выбирает все столбцы из всех таблиц или представлений, перечисленных после оператора  FROM

• schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя

• table.*  view.* — выбирает все столбцы из указанной таблицы, представления

• Expr — извлекает из таблицы (представления) некоторое определяемое выражение

• table view — имя таблицы(представления), из которой происходит выборка  данных.

• subquery — подзапрос, который сервер обрабатывает тем же самым способом как представление.

• WHERE — ограничивает множество строк выборкой тех записей, для которых условие является истинным; если это предложение опускается, сервер возвращает все строки из таблиц.

• GROUP BY — группирует выбранные строки по группам строк с одинаковым значением указанных полей и возвращает одиночную строку итоговой информации для каждой группы.

• HAVING — ограничивает выбираемые группы строк такими группами, для которых определяемое условие является истинным; если это предложение опускается, сервер возвращает строки всех групп.

• UNION UNION ALL INTERSECT MINUS — объединяет строки, возвращенные двумя утверждениями SELECT с использованием операции пересечения множеств; для ссылки на столбец вводится псевдоним для его обозначения; предложение FOR UPDATE не может использоваться с этими операторами

• ORDER BY — упорядочивает строки, возвращенные запросом.

• Expr— значение выражения определяет правило  упорядочивания строк.

• ASC DESC — определяет порядок вывода данных (по возрастанию или по убыванию); значением по умолчанию является ASC.

• FOR UPDATE — блокирует выбранные строки.

• OF — блокирует выбираемые строки для специфической таблицы в объединении.

• NOWAIT — возвращает  управление пользователю, если команда SELECT пытается блокировать строку, которая уже блокирована другим пользователем; если это предложение опускается, сервер ждет, пока строка не станет доступной и только тогда возвращает результаты команды SELECT.

Синтаксис команды SELECT в Oracle

Синтаксис команды SELECT в Oracle

Query table expression clause

Sample clause

With clause

Table collection expression

Where clause

Outer join

Hierarchical query clause

Group by clause

Order by clause

For update clause

Основные ключевые слова и параметры команды SELECT в Oracle

• DISTINCT — возвращает только одно значение  для каждого набора одинаковых выбранных значений столбца.

• ALL — возвращает все выбранные строки в Oracle, включая все повторяющиеся значения столбцов (принимается по умолчанию).

• * — выбирает все столбцы из всех таблиц или представлений, перечисленных после раздела  FROM.

• schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя.

• table.*  view.* — выбирает все столбцы из указанной таблицы Oracle, представления.

• Expr — извлекает из таблицы (представления) некоторое определяемое выражение.

• table view — имя таблицы(представления), из которой происходит выборка  данных.

• c_alias – алиасное имя (псевдоним) извлекаемого столбца, выражения.

• t_alias – алиасное имя (псевдоним) таблицы Oracle.

• subquery — подзапрос, который сервер обрабатывает тем же самым способом как представление.

• WHERE — ограничивает множество строк выборкой тех записей, для которых условие является истинным; если это предложение опускается, сервер возвращает все строки из таблиц Oracle.

• GROUP BY — группирует выбранные строки по группам строк с одинаковым значением указанных полей и возвращает одиночную строку итоговой информации для каждой группы.

• HAVING — ограничивает выбираемые группы строк такими группами, для которых определяемое условие является истинным; если это предложение опускается, сервер возвращает строки всех групп.

• UNION [ALL] INTERSECT MINUS — объединяет строки, возвращенные двумя утверждениями SELECT с использованием операции пересечения множеств; для ссылки на столбец вводится псевдоним для его обозначения. Предложение FOR UPDATE не может использоваться с этими операторами.

• ORDER BY — упорядочивает строки, возвращенные запросом: в Expr — указывается значение выражения, которое определяет правило  упорядочивания строк по возрастанию ASC или убыванию DESC. Значением по умолчанию является ASC.

• PARTITION —  в отличие от ORDER BY позволяет частично упорядочивать набор данных.

• FOR UPDATE — блокирует выбранные строки.

• NOWAIT — возвращает  управление пользователю, если команда SELECT пытается блокировать строку, которая уже блокирована другим пользователем; если это предложение опускается, сервер ждет, пока строка не станет доступной и только тогда возвращает результаты команды SELECT.

Описание команды SELECT

Основой всех синтаксических конструкций, начинающихся с ключевого слова SELECT, является синтаксическая конструкция “табличное выражение”.

Семантика табличного выражения состоит в том, что на основе последовательного применения разделов FROM, WHERE, GROUP BY и HAVING из заданных в разделе FROM таблиц строится некоторая новая результирующая таблица, порядок следования строк которой не определен и среди строк которой могут находиться дубликаты (т.е. в общем случае таблица-результат табличного выражения является мультимножеством строк).

Наиболее общей является конструкция “спецификация курсора”. Курсор — это понятие языка SQL, позволяющее с помощью набора специальных операторов получить построчный доступ к результату запроса к БД. К табличным выражениям, участвующим в спецификации курсора, не предъявляются какие- либо ограничения. При определении спецификации курсора используются три дополнительных конструкции: спецификация запроса, выражение запросов и раздел ORDER BY.

В спецификации запроса задается список выборки (список арифметических выражений над значениями столбцов результата табличного выражения и констант). В результате применения списка выборки к результату табличного выражения производится построение новой таблицы, содержащей то же число строк, но вообще говоря другое число столбцов, содержащих результаты вычисления соответствующих арифметических выражений из списка выборки.

Выражение запросов — это выражение, строящееся по указанным синтаксическим правилам на основе спецификаций запросов. Единственной операцией, которую разрешается использовать в выражениях запросов, является операция UNION (объединение таблиц) с возможной разновидностью UNION ALL.

Оператор выборки — это отдельный оператор языка SQL, позволяющий получить результат запроса в прикладной программе без привлечения курсора. Поэтому оператор выборки имеет синтаксис, отличающийся от синтаксиса спецификации курсора, и при его выполнении возникают ограничения на результат табличного выражения. Фактически, и то, и другое диктуется спецификой оператора выборки как одиночного оператора SQL: при его выполнении результат должен быть помещен в переменные прикладной программы. Поэтому в операторе появляется раздел INTO, содержащий список переменных прикладной программы, и возникает то ограничение, что результирующая таблица должна содержать не более одной строки.

В диалекте SQL СУБД Oracle поддерживается расширенный вариант оператора выборки, результатом которого не обязательно является таблица из одной строки. Такое расширение не поддерживается ни в SQL/89, ни в SQL/92.

Подзапрос — запрос, который может входить в предикат условия выборки оператора SQL.

.

SET ROLE

SET ROLE Oracle

Роль (ROLE) – это именованный элемент базы данных, который объединяет несколько привилегий. Если имеется несколько привилегий для одной роли, то между привилегиями и ролями создается отношение «многие – к-одному». Роль – это уникальный элемент базы данных, не принадлежащий пользователю. Ее можно создать с помощью команды CREATE ROLE, причем наименование роли не может совпадать с именем пользователя. При наличии привилегии CREATE ROLE пользователи также могут задавать привилегии. После создания роль становится частью заданного по умолчанию набора ролей.

Какая роль назначена тому или иному пользователю, СУБД может определить в процессе его опознания по паролю. Для присвоения пользователю роли используется команда SET ROLE.

SET ROLE Пример 1

Пользователь USER1 создает роль my_role без защиты паролей, передает ей набор привилегий. Затем использование роли данным пользователем разрешается (активизируется) или запрещается с помощью команды SET ROLE.

ROLE Oracle

• CREATE ROLE my_role NOT IDENTIFIED;

• GRANT SELECT, INSERT ON Student TO my_role;

• GRANT UPDATE (Fam,Ball) ON Student TO my_role;

• SET ROLE my_role IDENTIFIED BY st;

После этого пользователь USER1 может передать право на использование роли my_role другому пользователю с помощью команды GRANT.

SET TRANSACTION

В Oracle пользователю разрешается выполнять несколько операторов изменения данных как одну логическую единицу работы, заканчиваемую или сохранением результатов в базе данных, или их отменой. Транзакция начинается с первого исполняемого SQL-оператора пользователя и заканчивается явной фиксацией (завершением) или откатом со стороны пользователя.

Транзакции обрабатываются группой управляющих средств, с помощью которых пользователь может выдать команду INSERT, UPDATE или DELETE, указывающую начало серии команд изменения данных. После внесения изменений в базу данных пользователь может сохранить данные в базе, закончив транзакцию явным образом. Если же в любой момент транзакции возникает ошибка, пользователь может отменить изменения в базе данных, возвратив ее в состояние, в котором она находилась до начала транзакции.

Транзакции создаются посредством  двух различных элементов базы данных Oracle. Первый – это набор команд, определяющий начало, контрольные точки и окончание транзакции, а второй – специальный механизм блокирования, предотвращающий изменение информации строк базы данных несколькими пользователями одновременно.

SET TRANSACTION

Транзакции описываются следующими командами:

• SET TRANSACTION – начинает транзакцию и устанавливает ее базовые характеристики.

• COMMIT – заканчивает текущую транзакцию сохранением изменений в базе данных и начинает новую транзакцию

• ROLLBACK – заканчивает текущую транзакцию отменой изменений в базе данных и начинает новую транзакцию

• SAVEPOINT – устанавливает контрольные точки (точки прерывания) для транзакции, разрешая неполный откат.

Команда SET TRANSACTION является необязательной, так как транзакция начинается:

• после регистрации пользователя в Oracle с помощью SQL*Plus и исполнения им первой команды

• после выдачи команды ROLLBACK или COMMIT, заканчивающей транзакцию

• после выхода пользователя из системы

• в результате аварии системы

• после выдачи команды описания данными, например ALTER DATABASE

Команда COMMIT в обработке транзакций представляет тот момент времени, когда пользователь уже внес необходимые изменения как одну логическую группу, а поскольку ошибок не произошло, он готов к сохранению результатов своей работы. Выдачей команды COMMIT в базе данных еще и неявно закрывается текущая транзакция и начинается новая.

Откат транзакции выполняется не только в результате выдачи команды ROLLBACK, но и неявно, когда выполнение оператора по той или иной причине заканчивается неудачно или когда пользователь отменяет оператор командой CTRL-C.

Установка контрольных точек с помощью команды SAVEPOINT позволяет разбить весь объем работы, выполняемой транзакцией, на отдельные сегменты. Откат можно производить только до конкретной точки сохранения, оставляя предшествующие изменения в силе. Точки сохранения очень полезны в ситуациях, когда необходимо восстановить часть незавершенной транзакции. Следующий пример показывает, что в программном блоке сохраняются только те изменения, которые были внесены до описания точки сохранения:

UPDATE my_table.products SET price=50 WHERE product=10010;

SAVEPOINT точка_сохранения;

UPDATE my_table.products SET price=200;

ROLLBACK TO SAVEPOINT точка_сохранения;

COMMIT;

GRANT

С помощью команд управления данными можно управлять доступом пользователей к базе данных.

Команда GRANT используется для назначения привилегий пользователям.

Синтаксис команды GRANT:

Синтаксис команды GRANT

• system_priv — системная привилегия

• role — роль: набор соответствующих полномочий, которые администратор может коллективно предоставлять пользователям и другим ролям.

• user — пользователь

• PUBLIC —  привилегия передается всем пользователям

• WITH ADMIN OPTION — если предоставлены системные полномочия или роли, то параметр позволяет пользователю передать полномочие или роль другим пользователям или ролям

Пример 1

Предположим, пользователь Р1 является владельцем таблицы Student и нужно передать пользователю Р2 право на формулирование запросов к этой таблице:

GRANT SELECT ON Student TO P2;

Пример 2

Для передачи прав на другие привилегии синтаксис тот же самый. Пользователь Р1, являющийся владельцем таблицы Student, может разрешить пользователю Р2 вводить строки в нее:

GRANT INSERT ON Student TO P2;

Пример 3

Передача привилегий не ограничивается передачей единственной привилегии единственному пользователю с помощью одной команды GRANT. Допустимы списки привилегий и/или пользователей с элементами, разделенными запятыми:

GRANT SELECT, INSERT ON Student TO P2;

GRANT SELECT, INSERT ON Student TO P2, Р3;

Пример 4

Можно разрешить пользователю изменять значения любого или всех столбцов таблицы:

GRANT UPDATE ON Student TO P2;

GRANT UPDATE (Fam,Ball) ON Student TO P2;

Пример 5

Если необходимо предоставить кому-то все полномочия на конкретный объект, используется ключевое слово ALL:

GRANT ALL ON Student ТО Р2;

Пример 6

Когда передаются привилегии с атрибутом PUBLIC, который относится  к пользователям, а не к привилегиям, то все пользователи получают их автоматически. Чаще всего это применяется для привилегии SELECT для определенных таблиц или представлений, которые нужно предоставить каждому пользователю для рассмотрения. Разрешить каждому пользователю просматривать таблицу Student можно следующей командой:

GRANT SELECT ON Student TO PUBLIC;

Пример 7.

Иногда создатель таблицы хочет, чтобы другие пользователи имели право передавать привилегии на эту таблицу. Это можно сделать с помощью предложения WITH GRANT OPTION. Если пользователь Р1 желает, чтобы пользователь Р2 имел право передавать полномочия на работу с таблицей Student другим пользователям, то он должен передать пользователю Р2 привилегию на выполнение соответствующих команд:

GRANT SELECT, INSERT ON Student ТО Р2 WITH GRANT OPTION;

REVOKE

С помощью команды REVOKE осуществляется отмена привилегий, синтаксис команды REVOKE аналогичен синтаксису команды GRANT.

Пример 1. Отмена привилегии CREATE TABLE на создание таблиц в базе данных у пользователя user

REVOKE CREATE TABLE FROM user;

Пример 2. Отмена привилегии SELECT на выборку записей из таблицы

REVOKE SELECT ON table_name FROM user;

Пример 3. Отмена привилегий UPDATE и DELETE на изменение и удаления записей

REVOKE UPDATE, DELETE ON table_name FROM user;

Пример 4. Отмена общих привилегий

REVOKE ALL ON table_name FROM user;

Лекция 12

Команда DROP

Используя запрос DROP можно удалить таблицы (TABLE), индексы (INDEX) и базы данных (DATABASE).

DROP TABLE

DROP TABLE, применяемый в базе данных Oracle. Обычно с таблицей в базе данных связано несколько объектов, например индекс, создаваемый первичным ключом, или ограничение UNIQUE, налагаемое на столбцы таблицы. При удалении таблицы Oracle автоматически удаляет и любой связанный с ней индекс. Для удаления таблицы из БД необходимо выполнить команду DROP TABLE:

DROP TABLE Пример 1 Удаление таблицы:

DROP TABLE table;

Однако удалить таблицу не всегда столь просто. В любой момент мы можем создать таблицу с ограничениями целостности. Ограничение целостности (Integrityconstraint ) – это правило, устанавливаемое для таблицы и ограничивающее тип данных, которые можно вводить в эту таблицу. Если попытаться удалить таблицу с ограничениями целостности, возвращается сообщение об ошибке следующего вида: «Unique/primary keys in table referenced by foreign keys» (на уникальные/первичные ключи таблицы ссылаются внешние ключи).

Когда существуют ограничения для других таблиц, на которые ссылается удаляемая таблица, можно пользоваться каскадной конструкцией CASCADE CONSTRAINTS: DROP TABLE. Пример 2 Удаление таблицы с ограничениями целостности:

DROP TABLE table CASCADE CONSTRAINTS;

DROP TABLE, применяемый в mySQL DROP TABLE. Пример 3

Для удаления таблицы также используется запрос:

DROP TABLE table;

DROP TABLE. Пример 4

В случае, если необходимо установить проверку на существование таблицы при удалении (если существует удалить таблицу) запрос принимает следующий вид:

DROP TABLE IF EXISTS table;

Данный запрос будет выполнен в том случае, если удаляемая таблица существует в базе данных.

DROP INDEX Данный запрос DROP INDEX используется для удаления индексов в таблице.

DROP INDEX, применяемый в базе данных Oracle: Когда индекс в базе данных больше не нужен, разработчик может удалить его командой DROP INDEX. После удаления индекса эффективность поиска с использованием столбца или столбцов, ограниченных индексом, больше не повышается и упоминание об индексе исчезает из словаря данных. Индекс, применяемый для первичного ключа, удалить нельзя.

Синтаксис оператора DROP INDEX одинаков для удаления индекса любого типа (уникальности, битовой карты или В-дерева). Чтобы каким-то образом улучшить индекс, нужно сначала удалить его, а потом создать новый.

DROP INDEX. Пример 1

DROP INDEX my_index;

DROP INDEX, применяемый в mySQL:

DROP INDEX. Пример 2 Для удаления индексов (INDEX) используется запрос:

DROP INDEX my_index ON table;

Данный запрос удаляет индексы, указанные в my_index из таблицы table, но она не работает в версиях MySQL до 3.22. В версиях 3.22 и более поздних используется команда:

ALTER TABLE table_name DROP INDEX index_name;

DROP DATABASE

DROP DATABASE. Пример 1

DROP DATABASE database;

Запрос DROP DATABASE удаляет базу данных database.

TRUNCATE TABLE

Запрос TRUNCATE TABLE используется для того, чтобы удалить данные внутри таблицы, тем самым не затрагивая саму таблицу. В нужном случае TRUNCATE TABLE может быть очень полезен.

TRUNCATE TABLE, примеры использования TRUNCATE TABLE TRUNCATE TABLE. Пример 1

TRUNCATE TABLE table;

DROP SEQUENCE DROP SEQUENCE используется для удаления последовательности.

DROP SEQUENCE. Пример 1

DROP SEQUENCE sequence_name;

DROP SYNONYM DROP SYNONYM используется для удаления синонимов.

DROP SYNONYM. Пример 1

DROP SYNONYM synonym_name;

Для удаления общих синонимов необходимо воспользоваться командой DROP PUBLIC SYNONYM.

DROP SYNONYM. Пример 2

DROP PUBLIC SYNONYM synonym_name;

Команда SELECT Раздел FROM

Раздел FROM

Результатом выполнения раздела FROM является расширенное декартово произведение таблиц, заданных списком таблиц раздела FROM, которое в стандарте определяется следующим образом:

“Расширенное произведение R есть мультимножество всех строк r таких, что r является конкатенацией строк из всех идентифицированных таблиц в том порядке, в котором они идентифицированы. Мощность R есть произведение мощностей идентифицированных таблиц. Порядковый номер столбца в R есть n+s, где n — порядковый номер порождающего столбца в именованной таблице T, а s — сумма степеней всех таблиц, идентифицированных до T в разделе FROM”.

Примеры простой выборки SELECT

Пример 1. Выборка полей fname и sname из таблицы persons.

SELECT fname, sname FROM persons;

Пример 2. Выборка всех полей из таблицы persons без перечисления названий полей.

SELECT * FROM persons;

Результирующая таблица может включать не только поля исходных таблиц, но и результат вычисления над полями таблицы. В выражениях могут использоваться арифметические операторы и функции. Перечень допустимых функций определяется используемым диалектом языка SQL. Функции являются наиболее узким местом при переносе приложений из одного сервера данных на другой.

Конструкция SELECT может включать арифметические выражения, а также простые имена полей. Кроме того, можно добавить константы в результат выборки.

Пример 3. Выборка полей: id товара, текстовое поле «цена товара» и стоимость товара.

SELECT idgoods, «Цена», price FROM goods;

Команда SELECT Раздел WHERE

Раздел WHERE

Если в табличном выражении присутствует раздел WHERE, то следующим вычисляется он.

Условие, следующее за ключевым словом WHERE, может включать предикат условия поиска, булевские операторы AND (и), OR (или) и NOT(нет) и скобки, указывающие требуемый порядок вычислений.

Вычисление раздела WHERE производится по следующим правилам: Пусть R — результат вычисления раздела FROM. Тогда условие поиска применяется ко всем строкам R, и результатом раздела WHERE является таблица SQL, состоящая из тех строк R, для которого результатом вычисления условия поиска является true. Если условие выборки включает подзапросы, то каждый подзапрос вычисляется для каждого кортежа таблицы R (в стандарте используется термин “effectively” в том смысле, что результат должен быть таким, как если бы каждый подзапрос действительно вычислялся заново для каждого кортежа R).

Среди предикатов условия поиска в соответствии со стандартом могут находиться следующие предикаты: предикат сравнения, предикат between, предикат in, предикат like, предикат null, предикат с квантором и предикат exists.

При проверке условия выборки числа сравниваются алгебраически: отрицательные числа считаются меньше, чем положительные, независимо от их абсолютной величины. Строки сравниваются в соответствии с их представлением в коде ANSI. При сравнении двух строк, имеющих разные длины, предварительно более короткая строка дополняется справа пробелами для того, чтобы обе строки имели одинаковую длину.

Предикат сравнения с выражениями или результатами подзапроса. Условие определяется из двух выражений, разделенных одним из знаков операции отношения: =, <>(не равно), >, >=, < и <=.

Арифметические выражения левой и правой частей предиката сравнения строятся по общим правилам построения арифметических выражений и могут включать в общем случае имена столбцов таблиц из раздела FROM и константы. Типы данных арифметических выражений должны быть сравнимыми (например, если тип столбца a таблицы A является типом символьных строк, то предикат “a = 5” недопустим).

Если правый операнд операции сравнения задается подзапросом, то дополнительным ограничением является то, что мощность результата подзапроса должна быть не более единицы. Если хотя бы один из операндов операции сравнения имеет неопределенное значение, или если правый операнд является подзапросом с пустым результатом, то значение предиката сравнения равно unknown.

Для обеспечения переносимости прикладных программ нужно внимательно оценивать специфику работы с неопределенными значениями в конкретной СУБД.

Примеры выборки SELECT с разделом WHERE

Выборка кода и фамилии покупателей, проживающих в Москве.

SELECT CUSTOMERNO, FIRSTNAME, LASTNAME FROM CUSTOMER WHERE CITY = ‘Москва’;

Выборка из таблицы emp данных по служащим отдела с номером 40:

SELECT * FROM emp  WHERE deptno = 40;

Извлечение из таблицы записи с полями  имя, должность, размер оклада и номер отдела для всех служащих за исключением продавцов из отдела с номером 30:

SELECT ename, job, sal, deptno FROM emp WHERE NOT deptno = 30;

Команда SELECT Раздел ORDER BY

Раздел ORDER BY

Фраза ORDER BY используется для того, чтобы упорядочить строки, извлекаемые запросом.

В предложении ORDER BY SQL можно задавать несколько выражений. Сначала сортируются строки, основываясь на их значениях для первого выражения. Строки с одним и тем же значением для первого выражения затем сортируются по второму выражению и так далее. NULL-значения располагает после всех других при упорядочивании в порядке возрастания и перед всеми другими при сортировке в убывающем порядке.

Вместо имени столбца можно указать его позицию для сокращения записи длинного выражения.

Кроме того, при составлении сложных запросов, содержащих множественные операторы UNION, INTERSECT, MINUS, или UNION ALL, в предложении ORDER BY лучше использовать позиции, чем непосредственно сами выражения. Предложение ORDER BY может появляться только в последнем составляющем запросе и сортирует строки, полученные всем составным запросом в целом.

Предложение ORDER BY подчинено следующим ограничениям:

• Если в утверждении SELECT используются и оператор ORDER BY и оператор DISTINCT, то  предложение ORDER BY не может ссылаться на столбцы, не упоминаемые в списке выбора выбираемых столбцов.

• Предложение ORDER  BY не может появляться в подзапросах внутри других утверждений.

ORDER BY в обратном порядке

Выбрать из EMP записи по всем продавцам, и упорядочить результаты по размерам комиссионных в обратном порядке (убывающем порядке):

SELECT * FROM emp WHERE job = ‘SALESMAN’ ORDER BY comm DESC;

ORDER BY в возрастающем порядке

Выбрать из EMP записи по всем сотрудникам, и упорядочить результаты по размерам комиссионных в возрастающем порядке:

SELECT * FROM emp WHERE job = ‘SALESMAN’ ORDER BY comm ASC;

ORDER BY в возрастающем и убывающем порядке

Выбрать из EMP записи по служащим, упорядоченные сначала по возрастанию номера отдела а затем по убыванию размера оклада:

SELECT ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;

Команда SELECT Раздел GROUP BY

Раздел GROUP BY

Если в табличном выражении присутствует раздел GROUP BY SQL, то следующим выполняется GROUP BY.

Если обозначить через R таблицу, являющуюся результатом предыдущего раздела (FROM или WHERE), то результатом раздела GROUP BY является разбиение R на множество групп строк, состоящего из минимального числа групп таких, что для каждого столбца из списка столбцов раздела GROUP BY во всех строках каждой группы, включающей более одной строки, значения этого столбца равны. Для обозначения результата раздела GROUP BY в стандарте используется термин “сгруппированная таблица”.

Если утверждение SELECT содержит предложение GROUP BY(SELECT GROUP BY), список выбора может содержать только следующие типы выражений:

• Константы.

• Агрегатные функции.

• Функции USER, UID, и SYSDATE.

• Выражения, соответствующие перечисленным в предложении GROUP BY.

• Выражения, включающие вышеперечисленные выражения.

Пример 1. Вычислить общий объем покупок для каждого товара:

SELECT stock,  SUM(quant)   FROM ordsale GROUP BY stock;

Фраза GROUP BY не предполагает упорядочивания строк. Для упорядочивания результата этого примера по кодам товаров, следует поместить фразу  ORDER BY stock следом за фразой GROUP BY.

Пример 2. Можно использовать группировки данных GROUP BY совместно с условием. Например, выбрать для каждого покупаемого товара его код и общий объем покупок, за исключением покупок покупателя с кодом 23:

SELECT stock,  SUM(quant)   FROM ordsale WHERE customerno<>23 GROUP BY stock;

Строки, не удовлетворяющие условию WHERE, исключаются перед группированием данных.

Строки таблицы можно группировать по любой комбинации ее полей. Если поле, по значениям которого осуществляется группирование, содержит какие-либо неопределенные значения, то каждое из них порождает отдельную группу.

Допустим, есть задача на вычисление количества какого-либо продукта. Поставщик поставляет нам продукцию по определённой цене. Вычислим общее количество каждого из продуктов. В этом нам поможет фраза GROUP BY. Результатом задачи станет таблица, состоящая из нескольких колонок. Поставки будут группироваться по ПР. Компоновка происходит по группам, которую и инициирует Group By SQL. Необходимо отметить, что данная фраза предполагает применение фразы Select, она же в свою очередь определяет единственное значение для каждого выражения сформированной группы. Бывают три случая для конкретного выражения: оно принимает арифметическое значение, оно становится SQL-функцией, которая будет сводить все значения столбца к сумме или другому заданному значению, также выражение может стать константой. Строки таблицы не обязательно должны быть строго сгруппированы, они могут группироваться по любой комбинации столбцов таблицы. Необходимо учитывать, что упорядочивание запросы по ПР возможно в том случае, если будет сделан соответствующий запрос.

Команда SELECT Раздел HAVING

Раздел HAVING

Наконец, последним при вычислении табличного выражения используется раздел HAVING (если он присутствует).

Раздел HAVING может осмысленно появиться в табличном выражении только в том случае, когда в нем присутствует раздел GROUP BY. Условие поиска этого раздела задает условие на группу строк сгруппированной таблицы. Формально раздел HAVING может присутствовать и в табличном выражении, не содержащем GROUP BY. В этом случае полагается, что результат вычисления предыдущих разделов представляет собой сгруппированную таблицу, состоящую из одной группы без выделенных столбцов группирования.

Условие поиска раздела HAVING строится по тем же синтаксическим правилам, что и условие поиска раздела WHERE, и может включать те же самые предикаты. Однако имеются специальные синтаксические ограничения по части использования в условии поиска спецификаций столбцов таблиц из раздела FROM данного табличного выражения. Эти ограничения следуют из того, что условие поиска раздела HAVING задает условие на целую группу, а не на индивидуальные строки.

Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.

Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть TRUE. В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования.

HAVING COUNT

Выбрать коды товаров, покупаемых более чем одним покупателем:

SELECT stock FROM ordsale GROUP BY stock HAVING COUNT(*) > 1;

HAVING MIN

Получить значения минимального и максимального оклада для клерков каждого отдела, где  самое низкое жалованье составляет менее $1,000:

SELECT deptno, MIN(sal), MAX(sal) FROM emp WHERE job = ‘CLERK’ GROUP BY deptno HAVING MIN(sal) < 1000;

SQL Строки и выражения

Предположим, нам необходимо выполнить простые числовые операции с данными для представления их в более удобном виде. SQL позволяет вносить скалярные выражения и константы в выбранные поля. Эти выражения могут дополнять или заменять поля в предложениях SELECT и могут содержать множество выбранных полей. 1. Например, можно представить комиссионные продавцов в виде процентов, а не десятичных чисел:

SELECT snum, sname, city, comm * 100 FROM Salespeople;

Последний столбец в данном примере не имеет имени, так как является вычисляемым. Вычисляемые (выходные) столбцы – это столбцы, которые создаются с помощью запроса в тех случаях, когда в предложении SELECT используются агрегатные функции, константы или выражения, а не извлекаются непосредственно из таблицы. Поскольку имена столбцов являются атрибутами таблицы, столбцы, не переходящие из таблицы в выходные данные, не имеют имен. Почти во всех ситуациях выходные столбцы отличаются от столбцов, извлекаемых из таблицы тем, что они не поименованы. Константы, а также текст, можно включать в предложение запроса SELECT. Однако, буквенные константы, в отличие от числовых, нельзя использовать в выражениях. В SELECT-предложение можно включить 1+2, но не «А»+ «В», поскольку «А» и «В» здесь просто буквы, а не переменные или символы, используемые для обозначения чего-либо отличного от них самих. Тем не менее, возможность вставить текст в выходные данные запроса вполне реальна. 2. Например, можно пометить комиссионные продавцов, выраженные в процентах, символом «процент» (%), что позволяет представить их в выходных данных в виде символов и комментариев:

SELECT snum, sname, city, ‘%’, comm * 100 FROM Salespeople;

Результат выполнения запроса:

snum	sname	city
1001	Peel	London	%	12
1002	Monika	New York	%	15
1004	Rifkin	London	%	11

3. Можно пометить выходные данные, включив в них некоторый комментарий. Однако нужно помнить, что один и тот же комментарий будет печататься не один раз для всей таблицы, а в каждой строке выходных данных. Например, генерируются выходные данные для отчета, в котором фиксируется количество заказов на каждый день:

SELECT odate, ‘поступило’, COUNT (DISTINCT onum), ‘заказов’ FROM Orders GROUP BY odate;

Результат выполнения запроса:

оdate
10/03/05	поступило	5	заказов
11/03/05	поступило	1	заказов
12/03/05	поступило	12	заказов

Выходные данные запроса также можно изменить путем объединения столбцов. Метод, применяемый для слияния выходных данных двух столбцов в единое целое, называется конкатенацией и обозначается символами || :

SELECT odate || ‘поступило’ || COUNT (DISTINCT onum) || ‘заказов’ FROM Orders GROUP BY odate;

4. Любому столбцу при выдаче оператора SELECT можно присвоить любое, более информативное имя, не нарушая правил по длине, установленных в описании типа данных столбца. Такое имя называется псевдонимом. Псевдонимы указываются двумя способами: после описания столбца через пробел или при помощи ключевого слова AS, отмечающего псевдоним более четко:

SELECT snum, sname, city, comm * 100 AS commis FROM Salespeople;

SQL Соединение таблиц

Одна из наиболее важных черт запросов SQL состоит в их способности определять связи между множеством таблиц и отображать содержащуюся в них информацию в терминах этих связей в рамках единственной команды. Операция такого рода называется соединением (соединение таблиц). Фактически, наличие операции соединения является едва ли не самым главным, что отличает реляционные системы от систем других типов. При операции соединения таблицы перечисляются в предложении запроса FROM; имена таблиц разделяются запятыми. Предикат запроса может ссылаться на любой столбец любой из соединяемых таблиц и, следовательно, может использоваться для установления связей между ними. Обычно предикат сравнивает значения в столбцах различных таблиц для того, чтобы определить, удовлетворяется ли условие WHERE.

1. Предположим, нужно установить связь между продавцами (Salespeople) и покупателями (Customers) в соответствии с местом их проживания, чтобы получить все возможные комбинации продавцов и покупателей из одного города. Для этого необходимо взять продавца из таблицы Salespeople и выполнить по таблице Customers поиск всех покупателей, имеющих то же значение в столбце city:

SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers WHERE Salespeople.city = Customers.city;

Во избежание двусмысленности в выборке ссылки на поля во фразе WHERE должны уточняться именами содержащих их таблиц.

Операция соединения таблиц посредством ссылочной целостности применяется для использования связей, встроенных в базу данных. 2. Например, чтобы показать соответствие имен покупателей именам продавцов, обслуживающих этих покупателей, используется следующий запрос:

SELECT Customers.cname, Salespeople.sname FROM Customers, Salespeople WHERE Salespeople.snum = Customers.snum;

Соединение, использующее предикаты, основанные на равенствах, называется эквисоединением. Рассмотренные выше примеры относятся именно к этой категории, поскольку все условия в предложении WHERE базируются на математических выражениях, использующих символ равенства. Эквисоединение является наиболее распространенным типом соединения., но существуют и другие. Фактически в соединении можно использовать любой оператор сравнения.

3. Например, следующее соединение генерирует все комбинации имен продавцов и покупателей так, что первые предшествуют последним в алфавитном порядке, а последние имеют рейтинг меньше 200:

SELECT sname, cname FROM Salespeople, Customers WHERE sname < cname AND rating < 200;

4. Можно конструировать запросы путем соединения более чем двух таблиц. Например, нам нужно найти все заявки покупателей, не находящихся в том же городе, что и их продавец. Для потребуется связать все три рассматриваемые таблицы:

SELECT onum, cname, Orders.cnum, Orders.snum FROM Salespeople, Customers, Orders WHERE Customers.city <> Salespeople.city AND Orders.cnum = Customers.cnum AND Orders.snum = Salespeople.snum;

Хотя команда выглядит достаточно сложно, следуя ее логике, легко убедиться, что в выходных данных будут перечислены покупатели и продавцы, расположенные в разных городах (они сравниваются по полю snum), и что указанные заказы сделаны именно этими покупателями (подбор заказов устанавливается в соответствие с полями cnum и snum таблицы Orders).

В некоторых, довольно часто встречающихся на практике случаях, необходимо выбрать данные из таблицы, основываясь на результатах дополнительных выборок из этой же таблицы. Такие выборки называются коррелированными. Для их выполнения используются псевдонимы таблиц (алиасные имена), которые следуют непосредственно за именем таблицы в выборке. В приведенном ниже примере используются псевдонимы таблицы CUSTOMERS: first и second.

5. Выбрать все пары продавцов, имеющих одинаковый рейтинг, можно следующей командой:

SELECT first.cname, second.cname, first.rating FROM Customers first, Customers second WHERE first.rating = second.rating AND first.cname < second.cname;

В приведенном примере команда SQL ведет себя так, как будто в операции соединения участвуют две таблицы, называемые «first» и «second». Обе они в действительности являются таблицей Customers, но алиасы позволяют рассматривать ее как две независимые таблицы. Алиасы first и second были определены в предложении запроса FROM непосредственно за именем таблицы. Алиасы применяются также в предложении SELECT, несмотря на то, что они не определены вплоть до предложения FROM. Это совершенно оправдано. SQL сначала примет какой-либо из таких алиасов на веру, но затем отвергнет команду, если в предложении FROM запроса алиасы не определены. Время жизни алиаса зависит от времени выполнения команды. После выполнения запроса используемые в нем алиасы теряют свои значения. Получив две копии таблицы Customers для работы, SQL выполняет операцию JOIN, как для двух разных таблиц: выбирает очередную строку из одного алиаса и соединяет ее с каждой строкой другого алиаса. Для исключения повторений необходимо задать порядок для двух значений так, чтобы одно значение было меньше, чем другое, или предшествовало в алфавитном порядке.

Чтобы соединение возвращало данные одной таблицы даже при отсутствии соответствующей записи в другой таблице, можно создать внешнее соединение с помощью строки символов (+) в сравнении, формирующем соединение таблиц. 6. В данном примере выбираются строки, содержащие имена и должности сотрудников с указанием названий отделов, в которых они работают. При этом в результирующую выборку попадает также отдел OPERATIONS, в котором не работает ни одного служащего

SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno;

Результат выполнения данного запроса:

SQL Соединение таблиц

SQL Соединение таблиц.

SQL Подзапросы

Подзапрос — форма команды SELECT, которая появляется внутри другого утверждения SQL. Подзапрос иногда называется вложенным запросом. Утверждение, содержащее подзапрос называется родительским выражением. Строки, возвращенные подзапросом, используются родительским выражением.

Подзапросы SELECT

Подзапросы могут использоваться для следующих целей:

• Для определения множества строк, вставляемых в целевую таблицу выражениями INSERT или CREATE TABLE

• Для определения одного или более значений, назначаемых существующим строкам в утверждении UPDATE

• Для обеспечения необходимых условий в выражениях WHERE, HAVING утверждений SELECT, UPDATE, и DELETE

Чтобы определить таблицу, обрабатываемую запросом, подзапрос располагается после оператора FROM запроса вместо имени таблицы. Можно использовать подзапросы вместо таблиц таким же образом и в утверждениях INSERT, UDPATE и DELETE. Подзапросы, используемые таким образом, могут применять переменные корреляции, но только если эти переменные определены внутри самого запроса и не содержат внешних ссылок.

1. Например, чтобы определить, кто работает в отделе Тейлора, можно сначала использовать подзапрос, чтобы определить, в каком отделе этот Тейлор работает:

SELECT ename, deptno FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = ‘Taylor’);

Подзапрос выполняется один раз для всего родительского утверждения, в отличие от соотнесенного подзапроса, который выполняется для каждой строки, обрабатываемой родительским утверждением. Подзапрос может и сам содержать подзапрос, уровень вложенности не ограничивается.

2. Подзапрос можно использовать для создания копий таблиц. Например, создадим копию таблицы DEPT с именем NEWDEPT:

CREATE TABLE newdept (deptno, dname, loc) AS SELECT deptno, dname, loc FROM dept;

SQL UNION

Объединением двух множеств называется множество всех элементов, принадлежащих какому-либо одному или обоим исходным множествам. Результатом объединения будет множество, состоящее из всех строк, входящих в какое-либо одно или в оба первоначальных отношения. Однако, если этот результат сам по себе должен быть другим отношением, а не просто разнородной смесью строк, то два исходных отношения должны быть совместимыми по объединению, т. е. строки в обоих отношениях должны быть одной и той же формы. Что касается SQL, то две таблицы совместимы по объединению (и к ним может быть применен оператор объединения UNION) тогда и только тогда, когда:

• Они имеют одинаковое число полей (например, m);

• Для всех i (i = 1, 2, …, m) i-е поле первой таблицы и i-е поле второй таблицы имеют в точности одинаковый тип данных.

1. В качестве примера выберем коды товаров, которые имеют стоимость, превышающую 1000, либо покупаются покупателем с кодом 23 (либо то и другое):

SELECT stock FROM goods WHERE unitprice > 1000 UNION SELECT stock FROM ordsale WHERE customerno = 23;

Из результата выборки, использующей оператор UNION, всегда исключаются избыточные дубликаты. Поэтому, хотя в рассматриваемом примере товар выбирается обеими из двух составляющих предложений SELECT, в результирующей таблице каждый товар присутствует только один раз.

Оператором UNION можно соединить любое число конструкций SELECT. Оператор ORDER BY в запросе с использованием оператора UNION может входить только в последнее предложение SELECT. При указании критерия упорядочивания используйте номера полей результирующей таблицы. При использовании оператора UNION часто оказывается полезным включение константы в результирующую таблицу.

2. Например, текстовую константу можно использовать в качестве пояснения условия выборки товаров:

SELECT stock, ‘Стоимость товара> 1000′ FROM goods WHERE unitprice > 1000 UNION SELECT stock, ‘Товар куплен покупателем 23′ FROM ordsale WHERE customerno = 23 ORDER BY 2,1;

UNION ALL. Оператор UNION ALL позволяет, в отличие от UNION, разрешить выборку повторяющихся значений.

SELECT name FROM employees_Rus UNION ALL SELECT name FROM employees_Usa;

Лекция 13

Работа с NULL-значениями

NULL-значение

Достаточно часто встречаются такие случаи, когда в таблице имеются записи с не заданными значениями какого-либо из полей, потому что значение поля неизвестно или его просто нет. В таких случаях SQL позволяет указать в поле NULL-значение. Строго говоря, NULL-значение вовсе не представлено в поле. Когда значение поля есть NULL — это значит, что программа базы данных специальным образом помечает поле, как не содержащее какого-либо значения для данной строки (записи). Дело обстоит не так в случае простого приписывания полю значения «нуль» или «пробел», которые база данных трактует как любое другое значение. Поскольку NULL не является значением как таковым, он не имеет типа данных. NULL может размещаться в поле любого типа. Тем не менее, NULL, как NULL-значение, часто используется в SQL. Предположим, появился покупатель, которому еще не назначен продавец. Чтобы констатировать этот факт, нужно ввести значение NULL в поле snum, а реальное значение включить туда позже, когда данному покупателю будет назначен продавец.

IS NULL

Поскольку NULL фиксирует пропущенные значения, результат любого сравнения при наличии NULL-значений неизвестен. Когда NULL-значение сравнивается с любым значением, даже с NULL-значением, результат просто неизвестен. Булево значение «неизвестно» ведет себя также, как «ложь» — строка, на которой предикат принимает значение «неизвестно», не включается в результат запроса – при одном важном исключении: NOT от лжи есть истина (NOT (false)=true), тогда как NOT от неизвестного значения есть также неизвестное значение. Следовательно, такое выражение как «city = NULL» или «city IN (NULL)» является неизвестным независимо от значения city. Часто необходимо различать false и unknown – строки, содержащие значения столбца, не удовлетворяющие предикату, и строки, которые содержат NULL. Для этой цели SQL располагает специальным оператором IS, который используется с ключевым словом NULL для локализации NULL-значения.

SQL IS NULL. Пример. Вывести все поля из талицы Customers, значения поля city которых равны NULL:

SELECT * FROM Customers WHERE city IS NULL

В данном случае выходных данных не будет, поскольку в поле city нет NULL-значений.

IS NOT NULL

Условие IS NOT NULL используется в запросах для выборки записей со значениями не равных значению NULL SQL IS NOT NULL. Пример. Вывести все поля из талицы Customers, значения поля city которых НЕ равны NULL:

SELECT * FROM Customers WHERE city IS NOT NULL

CHECK

Условие CHECK

Можно устанавливать любое число ограничений для данных, вводимых в таблицы,  чтобы, например, ограничить диапазон вводимых данных. Опция CHECK обеспечивает ограничение,  которое позволяет  установить условие, которому должно удовлетворять значение, вводимое в таблицу,  прежде чем оно будет принято.  Ограничение CHECK состоит из ключевого слова CHECK, сопровождаемого  предложением предиката,  который использует указанное поле.  Любая попытка модифицировать или вставить значение поля, которое могло бы сделать этот предикат неверным будет отклонена. Например:

CREATE TABLE Student

( Kod_stud integer NOT NULL PRIMARY KEY,

Fam   char (30) NOT NULL UNIQUE,

Adres   char (50),

Ball  decimal CHECK ( Ball > 0));

Можно использовать ограничение CHECK чтобы защитить от ввода в поле определенных значений, и таким образом предотвратить ошибку. Например:

CREATE TABLE Student

( Kod_stud    integer NOT NULL PRIMARY KEY,

Fam   char (30) NOT NULL UNIQUE,

Adres   char (50),

Ball  decimal CHECK ( Ball > 0),

Form_ob char(10) CHECK (Form_ob IN (‘Дневная’, ‘Заочная’, ‘Вечерняя’));

Можно также  использовать CHECK в качестве табличного ограничения.  Это полезно в тех случаях когда необходимо включить более  одного поля строки в условие. Например:

CREATE TABLE Student

( Kod_stud    integer NOT NULL PRIMARY KEY,

Fam   char (30) NOT NULL UNIQUE,

Adres   char (50),

Ball  decimal,

Form_ob char(10),

CHECK (Ball > 50 OR Form_ob = ‘Дневная’));

Если строка вставляется в таблицу и не предоставляются значения для каждого поля, SQL должен иметь значения по умолчанию для заполнения ими значений полей, не заданных явно в команде; в противном случае команда вставки должна быть отвергнута. Наиболее распространенным значением по умолчанию является значение NULL. Это значение является значением по умолчанию для любого столбца, ели для него не указано ограничение NOT NULL, либо не указано значение, присвоенное по умолчанию. Для назначения иного значения по умолчанию используют ограничение DEFAULT.

CREATE TABLE Student

( Kod_stud    integer NOT NULL PRIMARY KEY,

Fam   char (30) NOT NULL UNIQUE,

Ball  decimal CHECK (Ball > 0),

Stip decimal DEFAULT =300);

UNIQUE

Ограничение UNIQUE

Ограничение UNIQUE в SQL позволяет идентифицировать каждую запись в таблице.

Если помещается ограничение столбца UNIQUE в поле при создании таблицы, база данных отклонит любую попытку ввода в это поле для одной из строк, значения, которое уже представлено в другой  строке. Это ограничение может применяться только к полям, которые были объявлены как непустые (NOT NULL), так как не имеет смысла позволить одной строке таблицы иметь значение NULL, а затем исключать другие строки с NULL значениями как дубликаты.

SQL Server / Oracle / Access

Пример создания таблицы SQL с ограничением UNIQUE:

CREATE TABLE Student

( Kod_stud   integer NOT NULL UNIQUE,

Fam   char (30) NOT NULL UNIQUE,

Adres    char (50),

Ball  decimal);

Когда обьявляется поле Fam уникальным, две Смирновых Марии могут быть введены различными способами — например,  Смирнова Мария и  Смирнова М. Столбцы (не  первичные  ключи), чьи значения требуют уникальности,  называются ключами-кандидатами или уникальными ключами. Можно определить группу полей как уникальную с помощью команды  ограничения таблицы — UNIQUE.  Объявление группы полей уникальной, отличается от объявления уникальными индивидуальных  полей,  так как это комбинация значений,  а не просто индивидуальное значение, которое обязано быть уникальным. Уникальность группы заключается в том, что пары строк со значениями столбцов «a», «b» и «b», «a» рассматривались отдельно одна от другой.

Если база данных определяет, что каждая специальность принадлежит одному и только одному факультету, то каждая комбинация кода факультета(Kod_f) и кода специальности(Kod_spec) в таблице Spec должна быть уникальной. Например:

CREATE TABLE Spec ( Kod_spec integer NOT NULL, Kod_f integer NOT NULL, Nazv_spec char (50) NOT NULL, UNIQUE (Kod_spec, Kod_f));

Оба поля в ограничении таблицы UNIQUE все еще используют ограничение столбца — NOT NULL.  Если бы использовалось ограничение столбца UNIQUE для поля Kod_spec, такое ограничение таблицы было бы необязательным.  Если значения поля Kod_spec различно для каждой строки, то не может быть двух строк с идентичной комбинацией значений полей Kod_spec и Kod_f.  Ограничение таблицы UNIQUE наиболее полезно, когда индивидуальные поля не обязательно должны быть уникальными.

MySQL UNIQUE

Пример создания таблицы Persons в MySQL с ограничением UNIQUE:

CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), UNIQUE (P_Id) );

Удалить ограничение UNIQUE

Если после создания ограничения UNIQUE и в том случае, когда ограничение UNIQUE не имеет смысла, UNIQUE можно удалить. Для этого используйте следующий SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE table_name DROP CONSTRAINT uc_PersonID;

MySQL:

ALTER TABLE table_name DROP INDEX uc_PersonID;

PRIMARY KEY

Первичный ключ PRIMARY KEY

PRIMARY KEY — первичный ключ, ограничение, позволяющее однозначно идентифицировать каждую запись в таблице SQL.

PRIMARY KEY Oracle Первичный Ключ (PRIMARY KEY) может ограничивать таблицы или их столбцы. Это ограничение работает так же как и ограничение UNIQUE. Но следует учитывать различие между первичными ключами и уникальностью столбцов в способе их использования с внешними ключами. Первичные ключи не могут позволять значений NULL. Это означает что, подобно полям в ограничении UNIQUE, любое поле, используемое в ограничении PRIMARY KEY, должно уже быть обьявлено NOT NULL.

PRIMARY KEY Oracle. Пример №1. Пример создания таблицы SQL с ограничением PRIMARY KEY:

CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal);

Лучше всего помещать ограничение PRIMARY KEY в поле (или в поля), которое будет образовывать уникальный идентификатор строки,  и сохранить ограничение UNIQUE для полей  которые  должны быть  уникальными логически (такие как номера телефона или поле sname), а не для идентификации строк. Ограничение PRIMARY KEY может также быть применено для  многочисленных  полей,  составляющих уникальную комбинацию значений:

PRIMARY KEY Oracle. Пример №2.

CREATE TABLE  Student ( Fam char (30) NOT NULL, Im char (30) NOT NULL Adres char (50), PRIMARY KEY (Fam, Im));

PRIMARY KEY MySQL

PRIMARY KEY SQL / MySQL. Пример №3.

CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id));

PRIMARY KEY SQL / MySQL. Пример №4.

CREATE TABLE `ad_packages` ( `id` int(111) NOT NULL auto_increment, `title` varchar(132) NOT NULL default », `price` float NOT NULL default ’0′, `type` varchar(22) NOT NULL default », `c_type` enum(‘cash’,'points’,'rur’) NOT NULL default ‘cash’, PRIMARY KEY (`id`) );

PRIMARY KEY SQL / MySQL. Пример №5.

CREATE TABLE `gamestat` ( `id` int(11) NOT NULL auto_increment, `game` varchar(10) NOT NULL default ‘tuz’, `stavok` int(11) NOT NULL default ’0′, `usd` float NOT NULL default ’0′, `rur` float NOT NULL default ’0′, `point` float NOT NULL default ’0′, `bank_usd` decimal(12,2) NOT NULL default ’0.00′, `bank_rur` decimal(12,2) NOT NULL default ’0.00′, `bank_point` decimal(12,2) NOT NULL default ’0.00′, PRIMARY KEY (`id`) );

FOREIGN KEY

Ограничения FOREIGN KEY

FOREIGN KEY используется для ограничения по ссылкам. Когда все  значения в одном поле таблицы представлены в поле другой таблицы, говорится, что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей.

Когда одно  пол в таблице ссылается на другое, оно называется внешним ключом;  а поле на которое оно ссылается,  называется родительским  ключом. Имена внешнего ключа и родительского ключа не обязательно должны быть одинаковыми. Внешний ключ может иметь любое число полей,  которые все обрабатываются как  единый  модуль.  Внешний ключ и родительский ключ, на который он ссылается,  должны иметь одинаковый номер и тип поля,  и находиться в одинаковом порядке. Когда поле является внешним ключом, оно определеным образом связано с таблицей, на которую он ссылается. Каждое значение, (каждая строка ) внешнего ключа должно недвусмысленно ссылаться к одному и только этому значению (строке) родительского ключа. Если это условие соблюдается, то база данных находится в состоянии ссылочной целостности.

SQL поддерживает  ссылочную целостность с ограничением FOREIGN KEY. Эта функция должна ограничивать значения, которые  можно  ввести в базу данных, чтобы заставить внешний ключ и родительский ключ соответствовать принципу  ссылочной целостности. Одно из действий ограничения FOREIGN KEY — это отбрасывание значений для полей, ограниченных как внешний ключ, который еще не представлен в родительском ключе. Это ограничение также воздействует на способность изменять или удалять значения родительского ключа

Ограничение FOREIGN KEY используется в команде CREATE TABLE (или ALTER TABLE (предназначена для модификации стуктуры таблицы), содержащей поле, которое объявлено внешним ключом. Родительскому ключу дается имя, на которое  имеется ссылка внутри ограничения FOREIGN KEY.

Подобно большинству ограничений, оно может быть ограничением таблицы или столбца, в форме таблицы позволяющей использовать многочисленные поля как один внешний ключ.

Синтаксис ограничения таблицы FOREIGN KEY:

FOREIGN KEY <column list> REFERENCES

<pktable> [ <column list> ]

Первый список столбцов — это список из одного или более столбцов таблицы,  которые отделены запятыми и будут созданы или изменены  этой командой.

Pktable — это таблица содержащая родительский ключ. Она может быть таблицей,  которая создается или изменяется текущей командой.

Второй  список столбцов — это список столбцов, которые будут составлять родительский ключ. Списки двух столбцов должны быть совместимы, т.е.:

• иметь одинаковое число столбцов

• в данной последовательности, первый, второй, третий, и т.д., столбцы списка столбцов внешнего ключа, должны иметь одинаковые типы данных и размеры, что и первый, второй, третий, и т.д., столбцы списка столбцов родительского ключа.

• столбцы в списках обоих столбцов не должны иметь одинаковых имен.

FOREIGN KEY Пример 1

CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Kod_spec integer NOT NULL, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal), FOREIGN KEY (Kod_spec) REFERENCES Spec (Kod_spec) );

При использовании ALTER TABLE вместо CREATE TABLE, для применения ограничения FOREIGN KEY, значения, указываемые во внешнем ключе и родительском ключе, должны быть в состоянии ссылочной целостности. Иначе команда будет отклонена.

Используя ограничение FOREIGN KEY таблицы или столбца, можно не указывать список столбцов родительского ключа, если родительский ключ имеет ограничение PRIMARY KEY. Естественно, в случае ключей со многими полями, порядок столбцов во внешних и первичных ключах должен совпадать, и, в любом случае, принцип совместимости между двумя ключами все еще применим.

FOREIGN KEY Пример 2

CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal), Kod_spec integer REFERENCES Spec );

Поддержание ссылочной целостности требует некоторых ограничений на значения, которые  могут быть представлены в полях,  объявленных как внешний ключ и родительский ключ. Родительский ключ должен быть структурен, чтобы гарантировать, что каждое значение внешнего ключа будет соответствовать одной указанной строке. Это означает, что он (ключ) должен быть уникальным и не содержать никаких пустых значений(NULL).

Этого не достаточно для родительского ключа в случае выполнения такого требования,  как при объявлении внешнего ключа. SQL должен быть уверен, что двойные значения или пустые значения (NULL) не были введены в  родительский ключ.  Следовательно необходимо убедиться, что все поля, которые используются как родительские ключи,  имеют или ограничение PRIMARY KEY или ограничение UNIQUE, наподобие ограничения NOT NULL.

Ссылка внешних  ключей только на первичные ключи — хорошая стратегия. Когда используются внешние  ключи,  они связываются не просто с родительскими ключами, на которые они ссылаются; они связываются с определенной строкой таблицы, где этот родительский ключ будет найден. Сам по себе родительский ключ не обеспечивает никакой информации, которая бы не была уже представлена во внешнем ключе.

Так как цель первичного ключа состоит в том,  чтобы идентифицировать уникальность строки, это более логичный и менее  неоднозначный  выбор для внешнего ключа.  Для любого внешнего ключа, который использует уникальный ключ как родительский ключ, необходимо создать внешний ключ, который бы использовал первичный ключ той же самой таблицы для того же самого действия. Внешний ключ, который не имеет никакой другой цели кроме связывания строк, напоминает первичный ключ, используемый исключительно для идентификации строк, и является хорошим средством сохранения структуры  базы данных ясной и простой. Внешний ключ может содержать только те значения, которые фактически представлены в родительском ключе или пустые (NULL). Попытка ввести другие значения в этот ключ будет отклонена.

FOREIGN KEY Пример 3

CREATE TABLE payment ( sh_payout integer, sh_eml integer, date_payout date, summ_payout real, FOREIGN KEY (sh_eml) REFERENCES k_sotr2 (eid) );

В данном примере FOREIGN KEY столбец sh_eml связывается со столбцом eid из таблицы k_sotr2.

SQL And & Or

AND & OR

Операторы AND & OR используются для составления условий в запросах.

Описание операторов AND & OR в SQL AND — оператор, который отображает только те записи, когда первое и второе условие является правдой / true.

OR — оператор, который отображает только те записи, когда хотя бы одно из двух условий является правдой / true.

Поскольку допускается наличие в базе данных неопределенных значений, то вычисление условия поиска производится не в булевой, а в трехзначной логике со значениями true, false и unknown (неизвестно). Для любого предиката известно, в каких ситуациях он может порождать значение unknown. Булевские операции AND, OR и NOT работают в трехзначной логике следующим образом:

Значение	Операция	Значение	Результат
true	AND	unknown	unknown
unknown	AND	true	unknown
unknown	AND	unknown	unknown
true	OR	unknown	true
unknown	OR	true	true
unknown	OR	unknown	unknown
	NOT	unknown	unknown

Пример оператора AND

Пример 1. Выборка (SELECT) записи, где поле fname равно ‘Ivan’ AND(и) поле lname равно ‘Abrakov’.

SELECT * FROM table_name WHERE fname=’Ivan’ AND lname=’Abrakov’;

Пример оператора OR

Пример 2. Выборка (SELECT) записи, где поле fname равно ‘Ivan’ OR(или) поле fname равно ‘Alex’.

SELECT * FROM table_name WHERE fname=’Ivan’ OR fname=’Alex’;

Пример комбинирования AND & OR

Пример 3. Выборка (SELECT) записи, которые соответствуют условию: поле lname должно быть равно ‘Abrakov’ AND(и) поле fname должно принимать значение ‘Ivan’ OR(или) значение ‘Alex’.

SELECT * FROM table_name WHERE lname=’Abrakov’ AND (fname=’Ivan’ OR fname=’Alex’);

SQL SELECT

Не многие владеют в совершенстве языками, а если говорит о языках программирования, то большая часть знает о них только то, что изучали в школе. Однако в полной мере об используемых языках, для управления компьютерными программами, можно почерпнуть только при глубоком изучении материала, в высших учебных заведениях и путем самостоятельного обучения.

Огромное количество программ позволяют выбирать данные для финансовых и других отчетов в считанные минуты. Одна из самых простых и к тому же удобных — SQL SELECT. При знакомстве с ней любой программист сможет без проблем рассказать о том, что SQL – это язык программирования, помогающий структурировать все необходимые запросы, а SELECT – это команда, используемая в комбинации с SQL. Безусловно, в сочетании SQL SELECT позволяют проводить все необходимые действия, изложенные на примерах данной страницы. В связи, с чем нижеизложенный материал более подробно и наглядно характеризует возможности SQL SELECT. Поэтому, установив программу, используемую SQL SELECT, вы сэкономите время своих сотрудников, а также деньги предприятия.

С помощью запроса SQL SELECT можно выполнять выборку данных из таблицы. Следующие примеры запросов SQL SELECT используются в таких SQL СУБД как MySQL, Oracle, Access и других.

Для выполнения следующих SQL запросов SELECT нам необходимо прежде всего изучить структуру таблиц.

Имя таблицы	Имя поля	Тип поля	Примечание
FAKULTET	KOD_F	Integer	PRIMARY KEY
	NAZV_F	Char, 30
SPEC	KOD_S	Integer	PRIMARY KEY
	KOD_F	Integer
	NAZV_S	Char, 50
STUDENT	KOD_STUD	Integer	PRIMARY KEY
	KOD_S	Integer
	FAM	Char, 30
	IM	Char, 15
	OT	Char, 15
	STIP	Decimal, 3
	BALL	Decimal, 3

SQL SELECT Пример №1 Выбрать студентов, получающих стипендию, равную 150.

SELECT fname FROM STUDENT WHERE STIP=150;

С помощью данного SQL запроса SELECT выбираются все значения из таблицы STUDENT, поле STIP которых строго равно 150.

SQL SELECT Пример №2 Выбрать студентов, имеющих балл от 82 до 90. Студенты должны быть отсортированы в порядке убывания балла.

SELECT FAM FROM STUDENT WHERE BALL BETWEEN 81 AND 91 ORDER BY BALL DESC;

Как видно из SQL примера, чтобы выбрать студентов, которые имеют балл от 82 до 90, мы используем условие BETWEEN. Чтобы отсортировать в убывающем порядке DESC.

SQL SELECT. Пример №3 Выбрать студентов, фамилии которых начинаются с буквы «А».

SELECT FAM FROM STUDENT WHERE FAM LIKE ‘А%’;

Для того, чтобы выбрать фамилии, начинающиеся с буквы «А», мы используем оператор SQL LIKE для поиска значений по образцу.

SQL SELECT Пример №4 Подсчитать средний балл на каждом факультете.

SELECT NAZV_F As Название, ROUND(AVG(BALL), 2) As СредБалл FROM FAKULTET, SPEC, STUDENT WHERE STUDENT.KOD_S=SPEC.KOD_S AND SPEC.KOD_F=FAKULTET.KOD_F GROUP BY NAZV_F;

Пример запроса SQL SELECT показывает нам использование функции SQL AVG для вычисления среднего значения, ROUND для округления значения, раздела GROUP BY для группировки столбцов.

SQL SELECT. Пример №5. Подсчитать количество студентов, обучающихся на каждом факультете. Вывести в запросе название факультета, комментарий – «обучается», количество студентов, комментарий «человек».

SELECT NAZV_F||’ обучается ‘||COUNT(STUDENT.BALL)||’ человек’ As CountStudOnFakultet FROM FAKULTET, SPEC, STUDENT WHERE STUDENT.KOD_S=SPEC.KOD_S AND SPEC.KOD_F=FAKULTET.KOD_F GROUP BY NAZV_F;

SQL SELECT. Пример №6. Упорядочить студентов по факультетам, специальностям, фамилиям.

SELECT NAZV_F, NAZV_S, FAM FROM FAKULTET, SPEC, STUDENT WHERE STUDENT.KOD_S=SPEC.KOD_S AND SPEC.KOD_F=FAKULTET.KOD_F ORDER BY NAZV_F, NAZV_S, FAM;

SQL SELECT. Пример №7. Определить, кто учится на специальности, к которой относится студент «Асанов».

SELECT FAM FROM STUDENT WHERE STUDENT.KOD_S=(SELECT KOD_S FROM STUDENT WHERE FAM=’Асанов’);

В данном SQL примере мы используем подзапрос SQL SELECT, который возвращает код специальности, на которой учится студент по фамилии Асанов.

SQL SELECT. Пример №8. Показать, какие специальности встречаются в таблице STUDENT. Дубликаты исключить. Вывести в запросе названия специальностей.

SELECT DISTINCT NAZV_S FROM SPEC, STUDENT WHERE STUDENT.KOD_S=SPEC.KOD_S;

Здесь мы с помощью SQL ограничения DISTINCT выводим только различные значения.

SQL SELECT. Пример №9. Извлечь из базы данных все данные по сотрудникам, принятым на работу после 01.01.1980 г. в формате “Сотрудник < фамилия сотрудника и его инициалы> принят на работу < дата принятия на работу>”.

SELECT CONCAT(CONCAT(CONCAT(‘Сотрудник ‘, sname), CONCAT(SUBSTR(fname, 0, 1), SUBSTR(otch, 0, 1))), CONCAT(‘принят на работу ‘, acceptdate)) FROM employees WHERE acceptdate > to_date(’01.01.80′,’dd.mm.yyyy’);

В данном SQL SELECT, используя SQL функцию CONCAT мы выводим все поля таблицы в одну строчку. SQL функция to_date возвращает привычное для СУБД значение даты.

SQL SELECT. Пример №10. Извлечь из базы данных перечень должностей, которые имеют сотрудники следующих отделов: ‘БИОТЕХНОЛОГИЙ’, ‘ИНЖЕНЕРНОЙ ЭКОЛОГИИ’. В запросе использовать названия отделов.

SELECT pname FROM posts, departments, employees WHERE posts.pid = employees.pid AND employees.did = departments.did AND (departments.dname = ‘БИОТЕХНОЛОГИЙ’ OR departments.dname = ‘ИНЖЕНЕРНОЙ ЭКОЛОГИИ’);

Пояснение: posts — таблица должностей, departments — таблица отделов, employees — таблица сотрудников, pname — название должности.

SQL SELECT. Пример №11. Извлечь из базы данных значение максимального личного шифра и фамилию сотрудника с этим номером в формате “Максимальный личный шифр < значение шифра> имеет сотрудник < фамилия сотрудника и его инициалы>”.

SELECT ‘Максимальный личный шифр ‘||eid||’ имеет сотрудник ‘||sname||’ ‘||SUBSTR(fname, 0, 1)||’. ‘||SUBSTR(otch, 0, 1)||’.’ As Максимальный_личный_шифр FROM employees WHERE eid = (SELECT MAX(eid) from employees);

Для вывода максимального личного шифра мы устанавливаем условие в WHERE так, чтобы шифр был равен полученному максимальному шифру из подзапроса SELECT, используя функцию MAX.

SQL SELECT. Пример №12. Получить из базы данных значение числа записей в таблице данных о сотрудниках в формате “Таблица данных о сотрудниках содержит < число записей> записей”.

SELECT ‘Таблица данных о сотрудниках содержит ‘||COUNT(*)||’ записей’ FROM employees;

Используя SQL функцию COUNT, выводим количество записей таблицы employees.

SQL SELECT. Пример №13. Получить единым запросом список отделов и должностей предприятия.

SELECT pname FROM posts UNION SELECT dname FROM departments;

С помощью UNION мы объединяем два запроса SQL SELECT и выводим их как один.

SQL SELECT. Пример №14. Вывести 30 комментариев начиная с 5 комментария из таблицы replies, кроме комментариев автора ‘Вася’. Данные сортируются по дате добавления комментария в убывающем порядке.

SELECT * FROM replies WHERE author!=’Вася’ ORDER BY date DESC LIMIT 5, 30;

SQL SELECT. Пример №15. Получить из SQL таблицы news одну новость с пометкой «Важные новости».

SELECT * FROM news WHERE status=’Важные новости’ LIMIT 1;

В данном SQL примере мы выбираем все столбцы поля, у которого столбец status равен ‘Важные новости’. SQL ограничение LIMIT 1 означает, что выбираем только одну запись.

SQL SELECT. Пример №16. Получить имя письма с идентификатором 1565.

SELECT name FROM mail_inbox WHERE id=’1565′;

SQL SELECT. Пример №17. Получить название рекламного пакета с идентификатором 24.

SELECT title FROM ad_packages WHERE id=’24′;

SQL SELECT. Пример №18. Вывести столбцы id, title, price, c_type с сортировкой по идентификатору в возрастающем порядке.

SELECT id, title, price, c_type FROM ad_packages ORDER BY id ASC;

SQL SELECT. Пример №19. Вывести все записи из SQL таблицы actions с идентификатором 1234567890.

SELECT * actions WHERE uid=’1234567890′;

SQL SELECT. Пример №20.

SELECT * FROM buypts ORDER BY c_type DESC, price DESC;

SQL SELECT. Пример №21.

SELECT uid FROM refs WHERE rid=’19′;

SQL SELECT. Пример №22.

SELECT * FROM sellpts ORDER BY price ASC;

SQL SELECT. Пример №23.

SELECT * FROM useronline WHERE uid=’1′;

SQL SELECT. Пример №24.

SELECT * FROM mail_inbox WHERE uid=’4590′ AND status=’unread’;

SQL SELECT. Пример №25.

SELECT * FROM buyref WHERE rid!=’5′ ORDER BY dateStamp DESC;

SQL SELECT. Пример №26.

SELECT id FROM replies WHERE nid=’5′;

SQL SELECT. Пример №27.

SELECT id, dateStamp, title, text FROM news WHERE dateStamp=’1232342412′;

SQL SELECT. Пример №28.

SELECT id, dateStamp, author, text, remote_addr FROM replies WHERE nid=’45′ ORDER BY dateStamp ASC;

sql select

Лекция 14

ОСНОВЫ PHP

Что такое PHP?

Если вы только начинаете знакомиться с PHP, то вам нужно знать определения. Итак, что же такое PHP?

PHP – это широко используемый язык сценариев общего назначения с открытым исходным кодом.

Говоря проще, PHP это язык программирования, специально разработанный для написания web-приложений (сценариев), исполняющихся на Web-сервере.

Аббревиатура PHP означает “Hypertext Preprocessor (Препроцессор Гипертекста)". Синтаксис языка берет начало из C, Java и Perl. PHP достаточно прост для изучения. Преимуществом PHP является предоставление web-разработчикам возможности быстрого создания динамически генерируемых web-страниц. Подробнее о преимуществах PHP можно узнать здесь.

Важным преимуществом языка PHP перед такими языками, как языков Perl и C заключается в возможности создания HTML документов с внедренными командами PHP. Подробнее об этой возможность смотрите здесь.

Значительным отличием PHP от какого-либо кода, выполняющегося на стороне клиента, например, JavaScript, является то, что PHP-скрипты выполняются на стороне сервера. Вы даже можете сконфигурировать свой сервер таким образом, чтобы HTML-файлы обрабатывались процессором PHP, так что клиенты даже не смогут узнать, получают ли они обычный HTML-файл или результат выполнения скрипта.

PHP позволяет создавать качественные Web-приложения за очень короткие сроки, получая продукты, легко модифицируемые и поддерживаемые в будущем.

PHP прост для освоения, и вместе с тем способен удовлетворить запросы профессиональных программистов.

Даже если Вы впервые услышали о PHP, изучить этот язык не составит для Вас большого труда. Мы не сомневаемся, что изучив основы PHP в течение нескольких часов, вы уже сможете создавать простые PHP-скрипты.

Язык PHP постоянно совершенствуется, и ему наверняка обеспечено долгое доминирование в области языков web -программирования, по крайней мере, в ближайшее время.

Возможности PHP

Возможности PHP очень большие. Главным образом, область применения PHP сфокусирована на написание скриптов, работающих на стороне сервера; таким образом, PHP способен выполнять всё то, что выполняет любая другая программа CGI. Например, обрабатывать данных форм, генерировать динамические страницы, отсылать и принимать cookies. Но PHP способен выполнять и множество других задач.

Существуют три основных области, где используется PHP.

• Создание скриптов для выполнения на стороне сервера. PHP наиболее широко используется именно таким образом. Все, что вам понадобится, это парсер PHP (в виде программы CGI или серверного модуля), вебсервер и браузер. Чтобы Вы могли просматривать результаты выполнения PHP-скриптов в браузере, вам нужен работающий вебсервер и установленный PHP. За более подробными сведениями обратитесь к подразделу установка PHP.

 

• Создание скриптов для выполнения в командной строке. Вы можете создать PHP-скрипт, способный запускаться вне зависимости от вебсервера и браузера. Все, что вам потребуется - парсер PHP. Такой способ использования PHP идеально подходит для скриптов, которые должны выполняться регулярно, например, с помощью cron (на платформах *nix или Linux) или с помощью планировщика задач (Task Scheduler) на платформах Windows. Эти скрипты также могут быть использованы в задачах простой обработки текстов. Дополнительная информация находится здесь.

 

• Создание приложений GUI, выполняющихся на стороне клиента. Возможно, PHP является не самым лучшим языком для создания подобных приложений, но, если вы очень хорошо знаете PHP и хотели бы использовать некоторые его возможности в своих клиент-приложениях, вы можете использовать PHP-GTK для создания таких приложений. Подобным образом Вы можете создавать и кросс-платформенные приложения. PHP-GTK является расширением PHP и не поставляется вместе с дистрибутивом PHP. Если вы заинтересованы, посетите сайт PHP-GTK.

PHP доступен для большинства операционных систем, включая Linux, многие модификации Unix (такие, как HP-UX, Solaris и OpenBSD), Microsoft Windows, Mac OS X, RISC OS, и многих других. (Существует даже версия PHP для OS/2. Неизвестно, правда, насколько соответствующая нынешним реалиям). Также в PHP включена поддержка большинства современных вебсерверов, таких, как Apache, Microsoft Internet Information Server, Personal Web Server, серверов Netscape и iPlanet, сервера Oreilly Website Pro, Caudium, Xitami, OmniHTTPd и многих других. Для большинства серверов PHP поставляется в качестве модуля, для других, поддерживающих стандарт CGI, PHP может функционировать в качестве процессора CGI.

Таким образом, выбирая PHP, вы получаете свободу выбора операционной системы и вебсервера. Кроме того, у вас появляется выбор между использованием процедурного или объектно-ориентированного программирования или же их сочетания.

PHP способен не только выдавать HTML. Возможности PHP включают формирование изображений, файлов PDF и даже роликов Flash (с использованием libswf и Ming), создаваемых "на лету". PHP также способен выдавать любые текстовые данные, такие, как XHTML и другие XML-файлы. PHP способен осуществлять автоматическую генерацию таких файлов и сохранять их в файловой системе вашего сервера, вместо того, чтобы отдавать клиенту, организуя, таким образом, кеш динамического содержания, расположенный на стороне сервера.

Одним из значительных преимуществ PHP является поддержка широкого круга баз данных. Создание скрипта, использующего базы данных, - очень просто. В настоящее время PHP поддерживает следующие базы данных:

Adabas D	Ingres	Oracle (OCI7 и OCI8)
dBase	InterBase	Ovrimos
Empress	FrontBase	PostgreSQL
FilePro (только чтение)	mSQL	Solid
Hyperwave	Direct MS-SQL	Sybase
IBM DB2	MySQL	Velocis
Informix	ODBC	Unix dbm

Также в PHP включена поддержка DBX для работы на абстрактном уровне, так что вы можете работать с любой базой данных, использующих DBX. Кроме того, PHP поддерживает ODBC (Open Database Connection standard), таким образом, вы можете работать с любой базой данных, поддерживающей этот всемирно признанный стандарт. Подробнее о работе PHP с СУБД здесь.

PHP также поддерживает "общение" с другими сервисами с использованием таких протоколов, как LDAP, IMAP, SNMP, NNTP, POP3, HTTP, COM (на платформах Windows) и многих других. Кроме того, вы получаете возможность работать с сетевыми сокетами "напрямую". PHP поддерживает стандарт обмена сложными структурами данных WDDX. Обращая внимание на взаимодействие между различными языками, следует упомянуть о поддержке объектов Java и возможности их использования в качестве объектов PHP. Для доступа к удаленным объектам вы можете использовать расширение CORBA.

PHP включает средства обработки текстовой информации, начиная с регулярных выражений Perl или POSIX Extended и заканчивая парсером документов XML. Для парсинга XML используются стандарты SAX и DOM. Для преобразования документов XML вы можете использовать расширение XSLT.

Последним по порядку, но не по значению, является поддержка многих других расширений, таких, как функции поисковой машины mnoGoSearch, функции IRC Gateway, функции для работы со сжатыми файлами (gzip, bz2), функции календарных вычислений, функции перевода и многое другое.