Операторы языка SQL используются во всех инструментальных средствах разработки INFORMIX (INFORMIX-SQL, INFORMIX-4GL, INFORMIX-ESQL/C и др.). Для каждого инструмента характерны определенные особенности использования SQL, которые описаны в соответствующей документации. В этом разделе даются сведения об применении операторов SQL в среде разработки INFORMIX-4GL.
Программа, написанная на 4GL использует предписания языка SQL (Структурного Языка Запросов) для манипуляций с базой данных.
Формат записи операторов SQL свободный. Можно писать все подряд на одной строке, один оператор на нескольких строках, слова операторов можно разделять произвольным количеством пробелов и комментариев. Никакими значками (типа ;) операторы разделять не
нужно. Окончание операторов определяется по контексту.
Примечание: если вы записываете предписания SQL не в программе 4GL а в среде INFORMIX-SQL, то разделять операторы точкой с запятой (;) необходимо.
Весь набор ключевых слов языка SQL зарезервирован, их нельзя занимать для других целей (на имена объектов и переменных SQL и 4GL).
Компилятору языка безразлично, большими или маленькими буквами
пишутся операторы. Он их не различает.
Комментарии обозначаются знаками { комментарий },
или знаком -- (два знака минус) до конца строки.
Идентификатор (имя объекта) - это слово, состоящее из букв, цифр, и знаков подчеркивания (_), начинающееся с буквы или знака (_). В INFORMIX-4GL не различаются маленькие и большие буквы. Поэтому i_Un1023Tt и I_UN1023TT - одно и тоже имя.
Имя базы данных не длиннее 10.
Имена прочих объектов SQL - таблиц, столбцов, view (псевдотаблиц) , синонимов - не длиннее 18.
SQL содержит 4 группы операторов:
Операторы описания данных предназначены для описания (создания), изменения описания и уничтожения объектов базы данных.
В SQL различаются следующие виды объектов:
Создание базы данных.
CREATE DATABASE zawod
DATABASE zawod . . . # текущей является база zawod DATABASE stanciq . . . # текущей является база stanciq CLOSE DATABASE # текущей базы нет
CREATE TABLE kadry ( nomerceh INT, tabnom SERIAL , fio CHAR(20) UNIQUE, zarplata MONEY(16,2), datarovd DATE, pribytie DATETIME year TO minute ) CREATE TABLE ceh ( nomerceh int, nameceh char(20) )
ALTER TABLE kadry ADD (dolvnostx CHAR(20) BEFORE zarplata), DROP(pribytie), ADD CONSTRAINT UNIQUE(tabnom, fio) CONSTRAINT tabnomfio ALTER TABLE items MODIFY (manu_code char(4))
CREATE VIEW poor AS SELECT tabnom, fio, datarovd FROM kadry WHERE zarplata < 120 # создано view - "псевдотаблица" из трех столбцов содержащая # строки из таблицы kadry, в которых zarplata меньше 120 рублей.
CREATE UNIQUE INDEX indkdtb ON kadry (tabnom) # создан индекс для столбца tabnom из таблицы kadry. Индекс # уникальный, значит в столбце не могут появиться одинаковые # значения.
ALTER INDEX indkdtb TO CLUSTER
kadry.nomerceh # столбец nomerceh из таблицы kadry ceh.nomerceh # столбец nomerceh из таблицы ceh iwanow.table1.c1 # столбец c1 из таблицы table1, владельцем которой является iwanow moshkow.table1.c1 # столбец c1 из другой (!) таблицы table1, владельцем которой является moshkow
CREATE SYNONYM t1 FOR petrow.sostoqnie_postow
START DATABASE zawod WITH LOG IN "/udd/moshkow/logfile/zawod"
CREATE AUDIT FOR kadry IN "/udd/moshkow/kadry.audit"
DROP VIEW poor # Уничтожается только view. С данными в таблицах, на которых оно базировалось ничего не происходит. DROP TABLE kadry # уничтожает таблицу вместе с данными. DROP INDEX indkdtb DROP SYNONYM t1 DROP DATABASE zawod # уничтожает базу вместе со всеми данными и системным журналом
Выдавать и забирать права доступа к таблице может владелец таблицы, Администратор Базы Данных (имеющий DBA права), а так же пользователь, которому было выдано право выдавать права (Оператором GRANT WITH GRANT OPTIONS)
REVOKE ALL ON customer FROM PUBLIC GRANT ALL ON customer TO iwanow, petrow WITH GRANT OPTION GRANT UPDATE(fname,lname,company, sity),SELECT ON customer TO PUBLIC REVOKE CONNECT FROM sidorowa, root REVOKE DBA FROM ivanov
BEGIN WORK LOCK TABLE kadry . . . UNLOCK TABLE kadry . . . LOCK TABLE kadry EXCLUSIVE
SET LOCK MODE TO WAIT
В базе данных, не имеющей системного журнала невозможно выполнение транзакций и восстановления до текущей контрольной точки.
Поскольку за все хорошее приходится платить, наличие системного журнала у базы данных вызывает заметный рост накладных расходов и замедление работы запросов. К тому же при активной работе с базой системный журнал быстро "распухает". За ним нужно следить и периодически чистить.
Указать базе новый системный журнал.
START DATABASE zawod WITH LOG IN "/ARM/log/zawod"
DATABASE kadry EXCLUSIVE # чтобы никто не лез ROLLFORWARD DATABASE kadry # прогнать базу вперед # по системному журналу CLOSE DATABASE # теперь всем можно работать
BEGIN WORK # начать транзакцию . . . # операторы IF все нормально THEN COMMIT WORK ELSE ROLLBACK WORK END IF
Следующая группа операторов предназначена для манипулирования данными в таблицах. В нее входят операторы выбора (SELECT) строк из таблицы (или таблиц), уничтожения (DELETE) строк в таблице, вставки (INSERT) строк, и изменения (UPDATE) значений в существующих в таблице строках.
Уничтожить в таблице kadry все строки, в которых номер цеха равен 4, а фамилия кончается на буквы "ов"
DELETE FROM kadry WHERE ceh=4 AND fio MATCHES "*ов"
DELETE FROM moshkow.kadry
Простейшая форма оператора SELECT.
Первый пример находит в таблице kadry строку, в которой столбец tabnum=345 . Из этой строки берутся только три указаных столбца. Второй пример выбирает ВСЕ строки из таблицы ceh, и все столбцы.
SELECT fio, dolvn, zarplata FROM kadry WHERE tabnom=345 SELECT * FROM ceh SELECT kadry.fio, ceh.nameceh WHERE kadry.nomerceh=ceh.nomerceh
Может вставить в таблицу одну строку, если используется в форме INSERT INTO ... VALUES, а может вставить в таблицу целый набор строк, выбранных подзапросом SELECT из другой таблицы.
INSERT INTO kadry VALUES (4,0,"Грицько",num,"10/25/1939",NULL) INSERT INTO customer VALUES (ps_customer.*) # ps_customer - переменная типа RECORD - аналог структуры в # языке Си. Этот оператор вставляет значения элементов записи # ps_customer в соответствующие поля таблицы customer INSERT INTO kadry (tabnom, fio, nomerceh, dolvnostx) SELECT 0 , fio, 4, dolvnostx FROM kadryold WHERE nomerceh=3 AND fio IS NOT NULL # последний оператор вставляет сразу несколько строк
В операторах DELETE, UPDATE, SELECT может присутствовать WHERE предложение, в котором можно задать условия на строки, которые требуется обработать (соответственно уничтожить, изменить или выбрать). Рассмотрим примеры использования WHERE предложения.
Меняет значения столбцов, в строках, удовлетворяющим WHERE условию.
UPDATE kadry SET fio="Зыкова" WHERE fio="Гирусова" UPDATE ceh SET kod_ceha[1,4]=nameceh[5,8] WHERE nomerceh BETWEEN 3 AND 5 OR nameceh IN ("токарный","литейный")
Предложение WHERE может присутствовать в любом из операторов
DELETE, UPDATE, SELECT, когда нужно задать условия на строки, которые требуется обработать (соответственно, уничтожить, изменить или выбрать). Рассмотрим структуру и примеры использования предложений WHERE.
В предложении WHERE пишется логическое условие, которое получается соединением с помощью логических операторов AND, OR и NOT элементарных сравнений типа:
выражение1 < выражение2, выражение1 >= выражение2, и т.п.,
column-name IS [NOT] NULL выраж [NOT] BETWEEN выраж1 AND выраж2 выраж [NOT] IN (выраж1 , ... [, ...] )
симв-выражение MATCHES "шаблон" симв-выражение LIKE "шаблон"
SELECT * FROM tab8 WHERE string1 LIKE "%+%Ы_"
* | заменяет любое количество символов |
? | заменяет один любой символ |
[...] | заменяет один символ из перечисленных в скобках |
возможно указание от и до (-), и не (^) | |
[abH] | любой из символов a, b, H |
[^d-z] | любой символ, исключая d,e,f,g, ... ,y,z |
\ | отменяет спецсмысл спецсимволов *,?,[,] |
SELECT * FROM customer WHERE company MATCES"?[^G-L]c*"
SELECT * FROM customer WHERE company MATCHES "*Я?*" ESCAPE"Я"
Если вы хотите:
выраж сравн {ALL | [ANY | SOME]} (SELECT-statement)
выраж [NOT] IN (SELECT-statement)
[NOT] EXISTS (SELECT-statement)
SELECT fio FROM kadry WHERE zarplata= (SELECT MAX(zarplata) FROM kadry )
SELECT fio, shifr, organizaciq FROM zaqwki WHERE denxgi_rek is not NULL and gorod in (SELECT gorod FROM regiony WHERE region="Урал")
SELECT order_num,stock_num,manu_code, total_price FROM items x WHERE total_price > (SELECT 2*MIN(total_price) FROM items WHERE order_num=x.order_num)
Оператор UNLOAD сбрасывает данные из таблицы в файл в печатном
представлении. Каждая строка преобразуется в отдельную запись, значения из столбцов разделяются символом "|".
После выполнения оператора
UNLOAD TO "kadry19.unl" SELECT * FROM kadry
5|5|туев |завхоз |100.0|31.12.1946| 4|6|петунин|кладовщик|80.0 | | . . .
Оператор LOAD выполняет обратную операцию - считывает строки из файла и вставляет их в таблицу. Естественно, что типы и количество значений в строках файла должны соответствовать столбцам таблицы.
LOAD FROM "kadry20.unl" INSERT INTO kadry
Предложения INTO, INTO TEMP, FROM.
Выбрать все строки (нет предложения WHERE) из таблицы kadry, взять в них все столбцы (вместо перечисления столбцов стоит *), оставить только различные строки (ключевое слово UNIQUE) и поместить результат во временную таблицу (INTO TEMP) x, которая будет при этом создана с такими же столбцами, что и у kadry.
SELECT UNIQUE * FROM kadry INTO TEMP x
SELECT tab1.a-tab2.b, tab1.a, tab2.b FROM tab1, tab2
SELECT a,b,c,d+e FROM tabl ORDER BY b,c SELECT a,b,c,d+e FROM tabl ORDER BY 2,3
SELECT customer_num, @lname,city INTO cnum,lname,town FROM customer
К выбранным строкам можно применять агрегатные функции COUNT(*)
- количество, MAX(column) и MIN(column) - максимальное и минимальное значение в столбце, SUM(column) - сумма всех значений в столбце, AVG(column) - среднее значение в столбце.
Поместить в переменную num количество строк в таблице orders, в которых столбец customer_num равен 101:
SELECT COUNT(*) INTO num FROM orders WHERE customer_num=101
SELECT AVG (zarplata) FROM table1,table2 WHERE table1.dolvnost=table2.dolvnost and zarplata>300
Группировка используется для для "сплющивания" группы (строк) в одну. Результат запроса содержит одну строку для каждого множества строк, удовлетворяющих WHERE предложению и содержащих одно и то же значение в указанном столбце.
SELECT dolvnostx, COUNT(*), AVG(zarplata) FROM kadry GROUP BY dolvnostx
SELECT dolvnostx, COUNT(*), AVG(zarplata) FROM kadry GROUP BY 1
SELECT order_num, AVG(total_priece) FROM items GROUP BY order_num HAVING COUNT(*) > 2
Строки из таблицы, присоединенной внешним образом (на внешнее соединение указывает ключевое слово OUTER) будут выбираться не смотря на то, удовлетворяют они условиям WHERE предложения или нет. В некоторых случаях это полезно, когда у вас есть главная таблица и есть вспомогательная, и данные из главной таблицы вам нужно получить в любом случае. Пример внешнего соединения:
SELECT company, order_num FROM customer c, OUTER orders o WHERE c.customer_num=o.customer_num
Операторы манипуляции данными - самая мощная составляющая SQL.
Следующий пример ликвидирует одинаковые строки в таблице kadry.
select unique * from kadry into temp kd delete from kadry where 1=1 insert into kadry select * from kd drop table kd
SELECT b.kl,b.pole, nomerceh,dolvnostx,zarplata,datarovd FROM kadry, b WHERE kadry.tabnom=b.kl into temp kd DELETE FROM kadry WHERE tabnom in (SELECT kl FROM b) INSERT INTO kadry SELECT * FROM kd DROP TABLE kd
UPDATE kadry SET dolvnostx=(select pole from b where kadry.tabnom=b.kl) WHERE tabnom IN (select kl from b)
## | ||||||||
Таблица agent | Таблица cia | |||||||
---|---|---|---|---|---|---|---|---|
fio John Piter Bob | har лентяй агент КГБ хороший | cen $300 $25 | fio John ... Piter ... Bob Ronny ... | ... | har трудяга агент CIA плохой плохой | ... | cen $600 $45 $15 | |
|
UPDATE cia SET (har,cen)=( (SELECT har,cen FROM agent WHERE cia.fio=agent.fio) ) WHERE fio IN (SELECT fio FROM agent) AND cen < (SELECT cen FROM agent WHERE cia.fio=agent.fio);