7602

Запити. Створення запиту

Лекция

Информатика, кибернетика и программирование

Запити Створення запиту Запит являє собою команду, написану мовою SQL, яка вибирає дані з таблиць. Якщо запит виготовляється візуальним способом, то інтерпретатор складає його скрипт у вигляді тексту...

Украинкский

2013-01-26

173.5 KB

3 чел.

Запити

Створення запиту

        Запит являє собою команду, написану мовою SQL, яка вибирає дані з таблиць. Якщо запит виготовляється візуальним способом, то інтерпретатор складає його скрипт у вигляді тексту, написаний теж мовою SQL. Скрипт вигідний тим, що його можна редагувати, він також застосовується на стадії вивчення мови SQL. На відміну від інших мов програмування, запит вказує серверу, які дані потрібно знайти, але не повідомляє, як це зробити. Слід особливо наголосити на тому, що запит – команда, але не вибірка даних.

        У підручниках з SQL терміни “команда” і “запит” часто вважаються еквівалентними. Тут термін “запит” стосується тільки команд, які повертають дані, але не до загальних команд SQL, які створюють або модифікують дані.

        Дані, повернені запитом, називаються підсумковим набором. Як і таблиця, він складається із записів та полів, тобто виглядає так, як таблиця. Вибірка не дублює таблицю, але містить її задану частину або всю інформацію, вона формується заново при кожному відкритті (запуску) запиту. Підсумковий набір не зберігається на диску, це тимчасові дані. Він може містити поля як із однієї, так з декількох таблиць.Для одержання вибірки служить команда SELECT, яка має такий вигляд (у скороченому варіанті):

SELECT список_імен_полів FROM ім’я_джерела

WHERE умова_відбору_записів

GROUP BY критерій групування

        Тут службові слова SELECT і FROM – обов’язкові.

        WHERE дозволяє обмежити набір записів. Якщо використовуються всі поля джерела, то замість їх переліку у списку імен полів запиту вживається символ зірочка. 

        Секція GROUP BY дозволяє групувати записи за заданим критерієм (наприклад, підсумувати кількість транспортованого газу для кожного газопроводу).

        Приклад простого запиту: видати всі поля всіх записів таблиці gazpr, де діаметр однієї нитки (поле diam) менший за 1800:

SELECT * FROM gazpr WHERE diam < 1800;

        Як бачимо, запит може містити вирази. До складу виразу можуть входити:

  •  константи;
  •  оператори;
  •  імена полів і таблиць;
  •  функції;
  •  фігурні, квадратні і круглі дужки.

        Нижче поданий перелік та опис цих складників (нагадаємо, що константи та імена полів і таблиць вже розглядалися в попередніх розділах).

        Оператори зазвичайно містять від одного до чотирьох символів. Слід зауважити, що деякі з них існують тільки у версії SQL для PostgreSQL і можуть не бути в інших СУБД.

        Об’єднання двох рядкових констант у один виконується оператором конкатенації (||), наприклад, так:

SELECT 'Ра' || 'кета' AS example;

example

------------

 Ракета

         Крім, власне демонстрації злиття двох рядків, у цьому прикладі показано застосування альтернативного імені example, яке тут дозволяє підвищити наглядність результату.

        Строкові оператори служать для порівняння двох рядків (або чисел), вони такі:

      

Оператор

Опис

=

Повертає True, якщо перший рядок точно збігається з другим

! =

Повертає True, якщо перший рядок не збігається з другим

<>

Ідентичний оператору ! =

<

Повертає True,  якщо перший рядок передує другому

<=

Повертає True, якщо  перший рядок передує другому або їх значення співпадають

>

Повертає True, якщо  другий рядок передує першому

>=

Повертає True, якщо  другий рядок передує першому або їх значення співпадають

 

         Оператори регулярних виразів розширюють можливості строкових операторів, вони такі:

~     перевіряє (видає true, якщо так) чи в рядку існує збіг для регулярного  виразу;

!~   перевіряє чи в рядку немає збігу для регулярного виразу;

~*   – перевіряє чи в рядку існує збіг без урахування регістру символів;

!~* перевіряє чи в рядку немає збігу без урахування регістру символів.  

         Регулярний вираз може містити такі метасимволи (спеціальні символи):

^    – з початку рядка;

$     – наприкінці рядка;

.     один символ;

[ ]    будь-який із символів, перелічених у квадратних дужках;

[^]   будь-який символ, крім перелічених у квадратних дужках;

[-]   будь-який символ з інтервалу, заданого в квадратних дужках;

[^-] будь-який символ, крім символів з інтервалу, заданого в квадратних дужках;

?     – один примірник попереднього символа або підвиразу (якщо, звичайно, він є) ;

*     – довільна кількість примірників попереднього символа або підвиразу;

+     один і більше примірників попереднього символа або підвиразу;

|      – лівий або правий підвираз;

( )     групування підвиразів з явним визначенням пріоритету операцій;

         Нижче наведено декілька прикладів застосування регулярних виразів для видачі:

  •  всіх значень поля name таблиці authors, які починаються з літер А або Т:  

SELECT name FROM authors WHERE name ~ '^A|^T';

  •  всіх значень поля title тих записів таблиці books, де зустрічається слово “Кобзар”:

SELECT title FROM books  WHERE title ~*'Кобзар';

 

  •  всіх значень, які починаються (^) на “Кобзар” без врахування регістра (*), а далі через довільне число символів (.) містять слово “малий” або (|) “повний”, написане лише малими буквами (перед ними немає зірочки):

SELECT title FROM books  WHERE title ~* '^ Кобзар.( малий | повний)';

  •  всіх значень, які починаються (^) на “К” без врахування регістра (*), а далі через довільне число символів (.) зустрічається якась буква з переліку ([]) “о” та “м”, наступним символом після яких зразу ж стоїть буква “к”, або (|) видати всі ті значення, які закінчуються ($) на “ий” або (|) на “не”:

SELECT title FROM books  WHERE title ~* '(^К.*[о,м]к)|(ий$|не$)';

        Математичні оператори використовуються в числових виразах, вони такі:

а + b

Підсумовування чисел а і b

а - b

Віднімання числа b від а

а * b

Множення числа а на b

а / b

Ділення числа а на b

а % b

Залишок від ділення а на b

а ^ b

Піднесення а до степеня b

|/ а

Квадратний корінь з а

||/ а

Кубічний корінь з a

а!

Факторіал а

!!а

Факторіал а (відрізняється від постфіксного оператора тільки розташуванням аргумента а)

Модуль (абсолютне значення) а

        Приклад застосування операції множення: видати коди газопроводів та кількість транспортованого газу за весь період обліку, збільшену в 1.6 разів (так начислюється премія персоналу, який обслуговує газопровід) і названу premia, взяті з таблиці oblik:

SELECT kod_g, (1.6 * kilk)::numeric(8, 2) AS premia FROM oblik; 

        PostgreSQL має такі варіанти зміни типів даних:

тип 'символьна константа'

'символьна константа':: тип  

числова константа:: тип

ідентифікатор:: тип

CAST (символьна константа' AS тип)

CAST (числова константа AS тип)

CAST (ідентифікатор AS тип)

        Математичні оператори порівняння такі ж, як вищерозглянені строкові.

        Ключове слово BETWEEN (також іноді називається оператором) дозволяє перевірити, чи входить значення в деякий інтервал. Наприклад, нищенаведена команда SELECT видає книги, ціна яких знаходиться в інтервалі між 10 та 17:

SELECT naz_kniga FROM  kniga  WHERE cina BETWEEN 10 AND 17;

        Двійкові оператори виконують порозрядні операції з бітовими послідовностями або цілими числами, вони аналогічні до мови С. Приклад зсуву числа 8 (двійкове 1000) на два розряди вправо та перетворення одержаної бітової послідовності в цілочисловий тип функцією bittoint4():

SELECT bittoint4(b'1000' >> 2);

        Пріоритет операторів SQL (у порядку спадання):

Оператор

Опис

::

Явне перетворення типу

[ ]

Індексація елемента масиву

.

Крапка , роздільник імен таблиці і поля

–

Унарний мінус

^

Піднесення до степеня

* / %

Множення, ділення і залишок

+

Додавання і віднімання

IS значення

Перевірка наявності заданого значення

IS NULL

Перевірка наявності значення NULL

IS NOT NULL

Перевірка розбіжності з NULL

IN перелік

Задання набору значень

BETWEEN

Перевірка приналежності до інтервалу

LIKE ILIKE

Вибір за зразком

<> <= >= =

Оператори порівняння

AND ОR NOT

Логічні оператори

        Функції

        Аргумент функцій записується в круглих дужках. Вони не обов’язкові тільки для функцій SQL92 current_date, current_time і current_timestamp. У PostgreSQL ці функції також викликаються без круглих дужок, щоб забезпечити сумісність зі специфікацією SQL92.

        Приклад видачі версії PostgreSQL:

SELECT version(); -- PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.

     real (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit

        Математичні функції: 

 

Функція

Опис

abs(x)

Модуль (абсолютне значення) х

acos(x)

Арккосинус х

asin(x)

Арксинус х

atan(x)

Арктангенс х

cbrt(x)

Кубічний корінь х

ceil(x)

Заокруглення х до цілого у верхню сторону

cos(x)

Косинус х

cot(x)

Котангенс х

degrees(r)

Кількість градусів у r радіан

exp(x)

Експонента числа x

floor(x)

Заокруглення х до цілого в нижню сторону)

ln(x)

Натуральний логарифм х (функція, зворотна ехр (х))

log(b.x)

Логарифм х з основою b

log(x)

Десятковий логарифм х

mod(x, y)

Залишок від ділення х на у

pi()

Константа π=3,1415926...

pow(x, y)

Піднесення х до степеня у

radians(d)

Кількість радіан в d градусів

random()

Видача псевдовипадкового числа в інтервалі від 0,0 до 1,0

round(x)

Зокруглення числа х до найближчого цілого

sin(x)

Синус х

sqrt(x)

Квадратний корінь з х

tan(x)

Тангенс

trunc(x)

Ціла частина числа х

trunc(x, s)

Заокруглення числа x до s цифр у дробовій частині

        Приклад видачі cos(π/3):

SELECT cos(pi()/3) AS kosinus;

        Рядкові функції видають такі значення:

 

ascii(s)ASCII-код символа строкової змінної s;

btrim(s[, t]) – видалення на початку і наприкінці рядка s всіх символів, які входять у рядок t.

        Якщо аргумент t не заданий, то усікаються пробіли, символи табуляції і т. д.);

char_length(s) – довжина рядка s у символах (стандарт SQL92);

chr(n) – символ з ASCII-кодом n (зворотня функція до ascii(s));

silike(f) – перевіряє чи вираз f збігається (без урахування регістра символів) з s;

initcap(s)рядок s, в якій перша буква кожного слова перетвориться до верхнього регістру;

length(s)довжина рядка s у символах;

slike(f) – перевіряє чи вираз f збігається з s;

lower(s)рядок s, перетворений до нижнього регістру (стандарт SQL92);

lpad(s , n  [, c])рядок s, доповнений зліва вмістом рядка с (або пробілами, якщо аргумент с

        не заданий) до довжини n (або усічену зправа до n символів);

ltrim(s  [, f]) рядок s, на початку якого видалені всі символи, що входять в рядок f (якщо

        аргумент f не заданий, то усікаються початкові пробіли, символи табуляції і т. д.);

octet_length(s) – довжина рядка символів s у байтах (стандарт SQL92);

position(b IN s) – позиція підрядка b в рядку s (відлік починається з 1 (стандарт SQL92));

repeat(s , n) – повторення рядка s n разів;

rpad(s , n [, c]) – рядок s, доповнений зправа вмістом рядка c (або пробілами, якщо аргумент c 

        не заданий) до довжини n (або усічений зліва до n символів);

rtrim(s  [, f ]) – рядок s, наприкінці якого видалені всі символи, що входять в рядок f (якщо

        аргумент f не заданий, то усікаються кінцеві пробіли, символи табуляції і т. д.);

strpos(s , b) – позиція підрядка b в рядку s (відлік починається з 1). Належить до функцій

        PostgreSQL і дублює функцію SQL position(), але з передачею аргументів у стилі C;

substr(s , n[, l]) – виділення підрядка з рядка s, починаючи з позиції n. Необов’язковий

        аргумент І визначає максимальну довжину підрядка в символах. Належить до функцій

        PostgreSQL і дублює функцію SQL substring(), але з передачею аргументів у стилі C;

substring(s FROM n FOR l) – виділення підрядка з рядка s, починаючи з позиції n.

        Необов’язковий аргумент І визначає максимальну довжину підрядка в символах

        (стандарт SQL92);

to_ascii(s, f) – рядок s, перетворений з розширеним кодуванням f в ASCII;

translate(s, f, r) рядок s, у якому всі символи, які входять у рядок f, замінюються символами

        рядка r;

trim(напрямок f FROM s) – рядок s, на початку і/або наприкінці якого видалені всі символи,

        що входять в рядок f. Напрямок задається словами SQL: LEADING, TRAILING, BOTH.

        (стандарт SQL92, здатна замінити функції PostgreSQL btrim(), ltrim() і rtrim());

upper(s) рядок s, перетворений до верхнього регістра.

        Приклад видачі ASCII-коду букви “Т”:

 

SELECT ascii('T');

           Функції для роботи з датою і часом:

 

current_date – поточна дата типу date (стандарт SQL92, функція не має дужок);

current_time – поточний час типу time (стандарт SQL92, не має дужок);

current_timestamp – поточна дата і час типу timestamp (стандарт SQL92, не має дужок);

date_part(s, t) – компонента дати або часу, визначена рядком s, із значення типу timestamp;

date_part(s, i) – компонента дати або часу, визначена рядком s, із значення типу interval;

date_trunc(s, t) – значення типу timestamp, усічене до точності s;

extract(k FROM t) – компоненту дати або часу, визначену k, із значення t типу timestamp;

extract(k FROM i) – компоненту дати або часу, визначену k, із значення t типу interval;

isfinite(t) – перевірка чи значення типу timestamp invalid або infinity;

isfinite(i) – перевірка чи значення типу interval не infinity;

now() – поточна дата і час у вигляді значення типу timestamp. Еквівалент константи now;

timeofday() – поточна дата і час у вигляді значення типу text.

        Приклади окремих форматів дати:

ISO      2001-06-25

SQL          06/25/2001  

PostgreSQL     Моn 25 Jun

German     25.06.2001

        Приклад видачі поточної дати і часу типу timestamp, поточної дати типу date та порядковий номер поточного місяця:

SELECT now();

SELECT current_date;

SELECT extract(month FROM current_date); 

        Функції перетворення типів.

        Як було зазначено вище, PostgreSQL має операції для перетворення типів, однак, деякі перетворення зручніше виконувати за допомогою функцій. Вони такі:

bitfromint4(n) число n на бітову послідовність;

bittoint4(b) – бітову послідовність на десяткове число;

to_char(n, f) число в рядок формату f; 

to_char(t, f) – значення типу timestamp в рядок формату f;

to_date(s, f) – рядок f формату дати в значення типу date;

to_number(s, f) – рядок f формату дати в значення типу numeric;

to_timestamp(s, f) – рядок f формату дати в значення типу timestamp;

timestamp(d) – значення типу date до типу timestamp;

timestamp(d. t) – два значення типів date і time до типу timestamp.

        Приклад перетворення бітової послідовності 101010 на десяткове число:

SELECT bittoint4(B'101010');

       Деякі інші функції, наприклад text(), теж дозволяють змінити тип, наприклад, перетворимо число 1000 на текст:

 

SELECT text (1000) AS explicit_text;

explicit_text

----------------------

1000

         При форматуванні перетворених даних можуть застосовуватися такі метасимволи:

9 – цифра;

.  – крапка, відокремлює цілу частину числа від дробової;

,   кома, використовується для розділення груп розрядів (тисячі, мільйони і т. д.);

D – десятковий роздільник (наприклад, крапка);

G – роздільник груп розрядів (наприклад, кома);

SG – знак плюс (+) або мінус (-) залежно від значення;

Ml – знак мінус (-), якщо число від’ємне;

PL – знак плюс (+), якщо число додатнє;

S – знак плюс (+) або мінус (-), визначений в локальному контексті;

L – грошовий знак, визначений в локальному контексті;

RN – римські цифри для значень в інтервалі від 1 до 3999;

FM – видалити всі початкові і прикінцеві нулі, створені символами 9.

        Приклад перетворення числа 123456789 на рядок, у якому тисячі розділити комами і представлення його як дійсне з двома знаками (тут – нулями) після крапки:

SELECT to_char(123456789, 'PL999G999G999D99'); -- + 123 456 789,00

        Агрегатні функції застосовуються в запитах для групування даних, вони такі:

 

avg(вираз)середнє арифметичне значення;

count(вираз) – кількість записів зі значенням відмінним від NULL;

mах(вираз)максимальне значення;

min(вираз)мінімальне значення;

stddev(вираз) – середньоквадратичне відхилення;

sum(вираз) – сума;

variаnce(вираз) – дисперсія.

        Наведемо ще декілька прикладів запиту:

SELECT b'10000' >> 2; - - = 4

SELECT * FROM gazpr WHERE naz_g ~ 'есн';

SELECT * FROM gazpr WHERE naz_g ~ '[г, ч]';

  - - почин (^) на “Є” без врах рег (*), а далі через довільне число символів (.)

  - - зустріч буква з переліку ([р, К]) , наступною за якою стоїть буква “е” :

SELECT * FROM gazpr  WHERE naz_g ~* '(^Є.*[р, К]е)'; - - Єлецьк-Орел                 

SELECT * FROM gazpr WHERE naz_g ='Україна';

SELECT * FROM gazpr WHERE  substring(naz_g FROM 2 FOR 1) = 'л';

SELECT * FROM gazpr WHERE  substring(naz_g FROM 3 FOR 2) = 'ец';

SELECT ||/ 27; - - 3

SELECT to_char(123456789, 'PL999G999G999D99');

SELECT to_char(-23456789, '999G999G999D99'); - - дає мінус

SELECT to_char(3123, 'rn'); - - mmmcxxiii

SELECT length(to_char(128,'999'));  - - 4

SELECT length(to_char(128,'99999')); - - 6


 

А также другие работы, которые могут Вас заинтересовать

21042. Виртуальные машины 207.5 KB
  Опять же необходимо указать имя и место сохранения создаваемого диска. Поэтому сразу выберите место для сохранения с учетом достаточности свободного пространства на реальном разделе жесткого диска. Отмечу некоторые особенности: к системе можно подключить три жестких диска. Напоследок рассмотрим процедуру создания нового виртуального жесткого диска.
21043. УСТАНОВКА ВИРТУАЛЬНОЙ МАШИНЫ и ОС WINDOWS XP 763 KB
  Представьте как это удобно если вы хотите разрешить виртуальной машине полный доступ к вашим файлам на реальной машине. При выборе этого пункта создается лишь файл виртуальной машины без дефолтного железа после чего открывается окно настройки и на образ навешиваются и настраиваются дополнительные виртуальные комплектующие сетевые адаптеры образа дисков и прочее. Нажмите кнопку Next На третьем экране нам предлагают ввести имя нашей виртуальной машины и место где мы ее разместим на физическом диске компьютера.
21044. Работа с файлами и дисками в ОС Windows XP 161.46 KB
  В 32битной ОС Windows XP в виде командной оболочки методом эмуляции реализован режим MSDOS позволяющий выполнять все указанные выше действия по работе с файлами и дисками. Подготовка к выполнению лабораторной работы К числу основных команд и служебных утилит используемых при работе с файлами дисками и томами в ОС Windows XP посредством командной оболочки относятся: Assoc Attrib Cacls Cd Chdir Chkdsk Chkntfs Comp Compact Convert Copy Date Del Dir Diskcomp Diskcopy Erase Fc Find Findstr Format Label Md Mkdir Move...
21045. Патофизиология гемостаза 37.5 KB
  Этиология патогенез и патогенетическая терапия наследственной и приобретенной патологии сосудистотромбоцитарного гемостаза. Этиология патогенез и патогенетическая терапия вторичных нарушений коагуляционного гемостаза. Функции системы гемостаза: В норме поддержание жидкого состояния крови При патологии повышение свертываемости крови Патология гемостаза делится на 2 группы: гиперкоагуляция гипокоагуляция МЕХАНИЗМЫ ГЕМОСТАЗА а тромбоцитарнососудистый б коагуляционный Оба механизма включаются одновременно.
21046. Патофизиология почек и кислотно-щелочного равновесия 19 KB
  ПЛАН ЛЕКЦИИ : Определение и классификация почечной недостаточности. Этиология патогенез принципы диагностики и патогенетической терапии острой почечной недостаточности. Этиология патогенез принципы диагностики и патогенетической терапии хронической почечной недостаточности. Классификация почечной недостаточности по этиопатогенезу: Преренальная характеризуется нарушением притока крови по a.
21047. Патофизиология язвенной болезни, голодание 54.5 KB
  Цель лекции: Изучить этиологию патогенез и принципы терапии язвенной болезни желудка и двенадцатиперстной кишки. Язвенная болезнь – это заболевание с наследственной предрасположенностью с полигенным типом наследования основным морфологическим субстратом которой является формирование одиночного либо множественных язвенных дефектов на слизистой желудка либо ДПК. Наиболее часто язвенные дефекты формируются в антральном отделе желудка и в луковице ДПК. Язвы тела и дна желудка наблюдаются редко и рассматриваются как предраковые изменения.
21048. Патофизиология экстремальных состояний 23 KB
  Рассмотреть вопросы этиологии патогенеза и патогенетической терапии шока коллапса комы и обморока. Определение виды патогенез принципы патогенетической терапии шока. Характеристика шока. Классификация шока.
21049. Общий адаптационный синдром (стресс) 17 KB
  Дать патофизиологическую оценку стадиям стресса. Определение стресса виды стресса. Патофизиологическая характеристика Дистресса. Впервые описал оси стресса и дал их патофизиологическую оценку.
21050. Предмет, методы и задачи патологической физиологии. История патологической физиологии. Общая нозология 31 KB
  Характеристика этиологии патогенеза цепи патогенеза определение понятий здоровье и болезнь. В его основе лежит цепь патогенеза. Цепь патогенеза: стержневой механизм формирования болезни. Следующие друг за другом важнейшие факторы патогенеза связанные между собой причинноследственными взаимоотношениями.