Transcript Инфраструктурата на WWW и HTTP

SQL
Димитър Кунчев
3rd Eye Solutions
Съдържание
1. Релационни бази данни и модели
2. Какво е SQL?
3. Създаване и редакция на модела на
базата от данни
3. SELECT заявка
WHERE
SORT BY
GROUP BY
JOIN
Вложен SELECT
Съдържание (2)
4. INSERT заявка
5. DELETE заявка
6. UPDATE заявка
Терминология
Таблица: EMPLOYEES
Ключ
Поле
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY DEPART
MENT_ID
100
Steven
King
24000
80
101
Neenah
Kochhar
17000
50
102
Lex
De Haan
(null)
90
103
Hunold
Alexander
9000
60
104
Ernst
Bruce
6000
90
Външен
ключ
Ред
(Запис)
Колона
Стойност
NULL
Релационни
бази данни
• Една релационна база данни:
– Може да бъде достигната и
модифицирана чрез SQL заявки
– Се състои от множество от таблици,
които съдържат и организират данните
• Има дефинирани връзки между таблиците
Комуникация
Въведена е SQL
заявка
SELECT
department_name
FROM departments
Заявката се изпараща
Към базата
База
данни
DEPARTMENT_NAME
Administration
Marketing
Shipping
Връща се резултат
(обикновенно таблица)
Какво е SQL?
Въведение
Какво e SQL
• Structured Query Language (SQL)
– Език за модифициране на данни в
релационна база данни
• Състои се от:
– Език за манипулиране на данните (Data
Manipulation Language - DML)
• SELECT, INSERT, UPDATE, DELETE
– Език за дефиниране на данните (Data
Definition Language DDL)
• CREATE, DROP, ALTER
• GRANT, REVOKE
Езикът SQL
Заявкa SELECT
Заявка SELECT
Избиране
Проекция
Избираме множество от
колони
Table 1
Избираме множество от
редове
Table 1
Свързване
Комбиниране
на данни от 2
таблици
Table 1
Table 2
Заявка SELECT
SELECT [DISTINCT]
*|{column|expression [AS column_name] ,...}
FROM table [WHERE expression]
– SELECT указва кои колони се избират
• Символът (*) указва да се изберат всички
колони
– FROM указва таблицата от която се
избира
– WHERЕ дефинира кои данни от
таблицата да се изведат
– AS задава име на колоната в резултата
SELECT – Пример (1)
• Избиране на всички данни от таблица
SELECT * FROM EMPLOYEES
EMPL_ID
FIRST_NAME
LAST_NAME
SALARY
10
Larry
King
900
20
John
Kochhar
800
30
Papa
De Haan
850
50
Mimi
Tochkova
1200
• Избиране на определени колони
SELECT
FIRST_NAME,
SALARY
FROM EMPLOYEES
FIRST_NAME
SALARY
Larry
900
John
800
Papa
850
Mimi
1200
SELECT – Пример (2)
• Избиране на всички служители със заплата
под 1000 лв
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEES WHERE SALARY < 1000
LAST_NAME
FIRST_NAME
SALARY
King
Larry
900
Kochhar
John
800
De Haan
Papa
850
Стойност null
• null е стойност, която е неизвестна,
недостъпна, неприложима или незададена
– Различна е от нула или празен стринг
• Всички операции с null дават стойност null
SELECT LAST_NAME, MANAGER_ID FROM EMPLOYEES
LAST_NAME MANAGER_ID
King
(null)
Kochhar
100
De Haan
100
Mimi
100
Показва се като
(null) или като
празно място
14
Символни низове
• Символни низове
– Представят се винаги между два
апострофа. Пример: ' foo '
– Може да се използва в SELECT заявка:
SELECT LAST_NAME, ' foo ‘ AS FOO FROM EMPLOYEES
LAST_NAME
FOO
King
foo
Kochhar
foo
De Haan
foo
Mimi
foo
Премахване на
Еднакви Редове
• Еднакви редове в резулата се премахват с
ключова дума DISTINCT в SELECT заявката
Обикновенна заявка: BOSS_ID DEPARTMENT_NAME
SELECT BOSS_ID,
DEPARTMENT_NAME
FROM DEPARTMENTS
15
SALES
19
DESIGN
15
SALES
19
DEVELOPMENT
Заявка с ключова дума DISTINCT:
SELECT DISTINCT
BOSS_ID,
DEPARTMENT_NAME
FROM EMPLOYEES
BOSS_ID
DEPARTMENT_NAME
15
SALES
19
DESIGN
19
DEVELOPMENT
Операции (1)
• Позволени са аритметични операции:
– +, -, *, /, (, )
• Пример:
SELECT LAST_NAME, SALARY, SALARY + 300,
2*(SALARY + 300) AS BIG_SALARY
FROM EMPLOYEES WHERE SALARY < 1000
LAST_NAME
SALARY
SALARY + 300
BIG_SALARY
King
900
1200
2400
Kochhar
800
1100
2200
De Haan
850
1150
2300
Операции (2)
• Конкатенация
– Обединява стойностите на две колони или
символни низове
– Извършва се чрез две вертикални черти (||)
– Връща символен низ
SELECT LAST_NAME || JOB_ID AS "Employees"
FROM EMPLOYEES
Employees
KingAD_PRES
KochharAD_VP
De HaanAD_VP
MimiAD_VP
Операции (3)
• Сравнение <, >, =, <=, >=, <>
– Използват се в WHERE клаузата на
SELECT заявка
– Предикати:
• BETWEEN value AND value – за указване на
интервал от стойности
• IN | NOT IN (value, …) – за указване на
множество от стойности
• LIKE string – за указване на съвпадение с
регулярен израз
• column IS {NOT} NULL – за указване на
стойноти различни или равни на null
– Сравнение с null винаги връща false
Операции (4)
• Логически операции
OR, AND, NOT
– Използват се в WHERE клауза на SELECT заявка
Пример:
SELECT BOSS_ID, DEPARTMENT_NAME
FROM DEPARTMENTS WHERE
(BOSS_ID < 20 AND NOT (DEPARTMENT_NAME = ‘SALES’)
BOSS_ID
DEPARTMENT_NAME
19
DESIGN
19
DEVELOPMENT
UNION и INTERSECT
• UNION обединява резултатите, а
INTERSECT извършва логическо
сечение на резултатите от SELECT
заявки
– Броя и типа на колоните във всяка
трябва да съвпадат
NAME
SELECT FIRST_NAME AS NAME
FROM EMPLOYEES
UNION
SELECT LAST_NAME AS NAME
FROM EMPLOYEES
Abel
Adam
Alana
Alberto
...
Сортиране
• Сортираме резултата с клаузата ORDER
BY {column [ASC | DESC],…}
– Списъкът на колоните указва реда на
сортиране
– ASC указва сортиране в нарастващ ред
– DESC указва сортиране в намаляващ ред
– По подразбиране се сортира в нарастващ ред
SELECT LAST_NAME,
HIRE_DATE FROM EMPLOYEES
ORDER BY HIRE_DATE
LAST_NAME HIRE_DATE
King
17-JUN-87
Whalen
17-SEP-87
Kochhar
21-SEP-89
Езикът SQL
Работа с повече от една таблица
Работа с повече от
една таблица
• Понякога се нуждаем от данни в
различни таблици:
LAST_NAME DEPART
MENT_ID
DEPART
MENT_ID
DEPARTMENT_NAME
King
90
90
Executive
Kochhar
90
20
Marketing
Fay
20
10
Administration
LAST_NAME
DEPARTMENT_NAME
King
Executive
Fay
Marketing
Kochhar
Executive
Картезианово
умножение
• Следната заявка ще върне картезианово
умножение на двете таблици:
SELECT LAST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
• И резултатът ще е:
LAST_NAME
DEPARTMENT_NAME
King
Executive
King
Marketing
King
Administration
Kochhar
Executive
Kochhar
Marketing
..
..
INNER JOIN и
клауза ON
• Използваме клаузата INNER JOIN с ON
за да укажем правила за обединение:
– INNER JOIN е аналогично на JOIN
SELECT E.EMPLOYEE_ID, E.LAST_NAME,
E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.LOCATION_ID
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON (E.DEPARTMENT_ID =
D.DEPARTMENT_ID)
EMPLOYEE_ID
LAST_NAME
DEPART
MENT_ID
DEPART
MENT_ID
LOCATION_ID
200
Whalen
10
10
1700
201
Hartstein
20
20
1800
202
Fay
20
20
1800
INNER и OUTER JOIN
• Обединение на две таблици, при което
се връщат само пасналите си редове се
нарича inner join
• Обединение на две таблици, при което
се връщат всички паснали си редове и
тези от лявата (дясната), които не са, се
нарича left (right) outer join
• Обединение на две таблици, при което
се връща резулата на inner join, както и
резултатите от left и right join се нарича
full outer join
INNER JOIN
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS
MANAGER_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.EMPLOYEE_ID=D.MANAGER_ID
MANAGER_NAME
DEPARTMENT_ID
DEPARTMENT_ NAME
Jennifer Whalen
10
Administration
Michael Hartstein
20
Marketing
Den Raphaely
30
Purchasing
Susan Mavris
40
Human Resources
Adam Fripp
50
Shipping
Alexander Hunold
60
IT
Hermann Baer
70
Public Relations
...
...
...
LEFT OUTER JOIN
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS
MANAGER_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
ON E.EMPLOYEE_ID=D.MANAGER_ID
MANAGER_NAME
DEPARTMENT_ID
DEPARTMENT_ NAME
Jennifer Whalen
10
Administration
Michael Hartstein
20
Marketing
Den Raphaely
30
Purchasing
Clara Vishney
(null)
(null)
Jason Mallin
(null)
(null)
Hazel Philtanker
(null)
(null)
Nanette Cambrault
(null)
(null)
...
...
...
RIGHT OUTER JOIN
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS
MANAGER_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D
ON E.EMPLOYEE_ID=D.MANAGER_ID
MANAGER_NAME
DEPARTMENT_ID
DEPARTMENT_ NAME
Jennifer Whalen
10
Administration
Michael Hartstein
20
Marketing
Den Raphaely
30
Purchasing
(null)
120
Treasury
(null)
130
Corporate Tax
(null)
140
Control And Credit
(null)
150
Shareholder Services
...
...
...
FULL OUTER JOIN
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS
MANAGER_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D
ON E.EMPLOYEE_ID=D.MANAGER_ID
MANAGER_NAME
DEPARTMENT_ID
DEPARTMENT_ NAME
Jennifer Whalen
10
Administration
Michael Hartstein
20
Marketing
...
...
...
Clara Vishney
(null)
(null)
Jason Mallin
(null)
(null)
...
...
...
(null)
150
Shareholder Services
...
...
...
Многократен JOIN
• Позволява се умножение на няколко
таблици:
SELECT E.EMPLOYEE_ID, CITY, DEPARTMENT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
EMPLOYEE_ID
CITY
DEPARTMENT_ NAME
103
Southlake
IT
104
Southlake
IT
124
San Francisco
Administration
...
...
...
Допълнителни
условия
• Можете да добавите условия в WHERE
клаузата:
SELECT E.EMPLOYEE_ID,
E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,
E.MANAGER_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D ON
(E.DEPARTMENT_ID = D.DEPARTMENT_ID)
WHERE E.MANAGER_ID = 149
EMPLO NAME
YEE_ID
MANAG
ER_ID
DEPARTM
ENT_ID
DEPARTMENT_NAME
174
Ellen Abel
149
80
Sales
175
Alyssa Hutton
149
80
Sales
...
...
...
...
...
Езикът SQL
Вложени SELECT заявки
Вложени заявки
• SELECT заявки могат да се извикват в
WHERE клаузата:
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY =
(SELECT MAX(SALARY) FROM EMPLOYEES)
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM DEPARTMENTS
WHERE DEPARTMENT_NAME='Accounting')
– Използването на JOIN и условия винаги са
по-бързи от вложени SELECT заявки
Оператор EXISTS
• Оператота се изпозва в WHERE клауза.
Връща true ако резултата от вложената
заявка не е празен
– Пример: Избиране на всички работници,
работили в отдел #110
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES E
WHERE EXISTS
(SELECT EMPLOYEE_ID FROM JOB_HISTORY JH
WHERE DEPARTMENT_ID = 110 AND
JH.EMPLOYEE_ID=E.EMPLOYEE_ID)
Езикът SQL
Агрегиране на данни
Агрегатни функции
• Агрегатните функции работят върху
многжество от редове и връщат по един
резултат на група от редове
EMPLOYEE_ID
SALARY
100
24000
101
17000
102
17000
103
9000
104
6000
...
...
MAX(SALARY)
24000
Някои агрегатни
функции в SQL
• COUNT(*) – брой на избраните редове
• SUM(колона) – Сума на стойностите в
колоната
• AVG(колона) – средно аритметично на
стойностите в колоната
• MAX(колона) – най-голямата стойност в
колоната
• MIN(колона) – най-малката стойност в
колоната
Примери (1)
• Избиране на средна, максимална,
минимална и обща заплата на всички
търговски представители
SELECT AVG(SALARY), MAX(SALARY),
MIN(SALARY), SUM(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP%'
AVG(SALARY)
MAX(SALARY)
MIN(SALARY)
SUM(SALARY)
8272.72
11500
6000
273000
Примери (2)
• Избиране на най-ранната и най-късната
дата на наемане на служител
SELECT MIN(HIRE_DATE), MAX(HIRE_DATE)
FROM EMPLOYEES
MIN(HIRE_DATE)
MAX(HIRE_DATE)
17-JUN-1987
29-JAN-00
• Избиране на първия и последния
служител по азбучен ред
SELECT MIN(LAST_NAME), MAX(LAST_NAME)
FROM EMPLOYEES
Примери (3)
• Избиране на броя служители в отдел
номер 50
SELECT COUNT(*) FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
COUNT(*)
5
• COUNT(*) брои само стойностите,
различни от null!
• Брой различни редове в таблица:
SELECT COUNT(DISTINCT *)
FROM SALES
COUNT(*)
5
Примери (3)
• Избиране на работниците с най-висока
заплата
SELECT
FIRST_NAME,
LAST_NAME,
SALARY
FROM EMPLOYEES
WHERE
SALLARY = (SELECT MAX(SALARY) FROM EMPLOYEES)
Агрегиране и
стойност null
• Агрегатните функции игнорират null
стойностите. Например:
EMPLOYEE_ID
COMMISION
100
20
101
10
102
(null)
SELECT AVG(COMMISION) FROM
EMPLOYEES
AVG(COMMISSION)
15
• Ако приемем всяка стойност null за 0, то
резултата щеше да е 10
Езикът SQL
Функции за групиране
Оператор GROUP BY
Групи от данни
EMPLOYEES
Пример за групиране
по отдел
DEPARTMENT_ID SALARY
50
3100
50
3000
50
2600
50
2600
DEPART SUM(SALARY)
MENT_ID
20
4400
50
11300
20
13000
20
23400
20
6000
40
16500
40
6500
110
20300
40
10000
...
...
110
12000
110
8300
...
...
11300
23400
16500
20300
Оператор GROUP BY
• Използва се за разделяне на данните от
таблица в групи по определени правила
• Синтаксис:
SELECT
FROM
[WHERE
[GROUP
[ORDER
<columns>, <group_function(column)>
<table>
<condition>]
BY <group_by_expression>]
BY <columns>
• Където <group_by_expression> е списък с
колони от таблицата
Оператор GROUP BY
• Пример:
SELECT DEPARTMENT_ID, SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
DEPARTMENT_ID SUM(SALARY)
100
51600
30
24900
(null)
7000
...
...
• Полетата, по които се групира няма
нужда да са в SELECT списъка
EMPLOYEES
Групиране по няколко
колони
DEPART JOB_ID
MENT_ID
SALARY
20
AD_ASST
4400
20
MK_MAN
13000
20
MK_MAN
12000
30
PU_CLERK 2500
30
PU_CLERK 2500
30
4400
EMPLOYEES
25000
DPT_ID JOB_ID
SUM(SA
LARY)
20
AD_ASST
4400
20
MK_MAN
25000
PU_CLERK 2500
30
PU_CLERK 7500
30
PU_MAN
11000
30
PU_MAN
43500
30
PU_MAN
11500
...
...
...
30
PU_MAN
10000
30
PU_MAN
11000
...
...
...
7500
43500
Пример
• Пример за групиране по повече от една
колона:
SELECT DEPARTMENT_ID, JOB_ID,
COUNT(EMPLOYEE_ID), SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY SUM(SALARY) DESC
DEPARTMENT_ID JOB_ID
COUNT(
EMPLOYEE_ID)
SUM(SALARY)
80
SA_REP
29
243500
50
SH_CLERK 20
64300
80
SA_MAN
5
61000
...
...
...
...
Невалидни заявки
• GROUP BY се използва задължитено,
когато комбинирате колони със и без
агрегатни функции
SELECT DEPARTMENT_ID, COUNT(LAST_NAME)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID /* това е задължително */
• Не може да се използва агрегатна
функция в WHERE клауза
SELECT DEPARTMENT_ID, AVG(SALARY)
FROM EMPLOYEES
WHERE AVG(SALARY) > 8000 /* грешно!!! */
GROUP BY DEPARTMENT_ID;
Оператор HAVING
• HAVING се изпозва както WHERE, но е
предназначен специално за условия по
агрегатни функции
SELECT DEPARTMENT_ID,
COUNT(EMPLOYEE_ID), AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(EMPLOYEE_ID) BETWEEN 3 AND 6
DEPARTMENT_ID
COUNT(EMPLOYEE_ID)
AVG(SALARY)
100
6
8600
30
6
4150
90
3
19333.33
60
5
5760
Използване на
GROUP BY и JOIN
• Можем да използваме функции за
групиране при умножаване на таблици
SELECT COUNT(*) AS EMPS, DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
WHERE
HIRE_DATE BETWEEN '1991-1-1' AND '1997-12-31'
GROUP BY DEPARTMENT_NAME
HAVING COUNT(*) > 5
ORDER BY EMPS DESC
EMPS DEPARTMENT_NAME
19
Shipping
15
Sales
Езикът SQL
Въвеждане на данни в
таблица
Оператор INSERT
• Оператор INSERT
– INSERT INTO <table> VALUES (<values>)
– INSERT INTO <table>(<columns>) VALUES
(<values>)
– INSERT INTO <table> SELECT <values>
INSERT INTO COUNTRY
VALUES ('1', 'Bulgaria', 'Sofia')
INSERT INTO COUNTRY(NAME, CAPITAL)
VALUES ('Bulgaria', 'Sofia')
INSERT INTO COUNTRY(COUNTRY_ID, NAME, CAPITAL)
SELECT NULL, COUNTRY, CAPITAL FROM CAPITALS
Езикът SQL
Промяна на данни в
таблица
Оператор UPDATE
• Оператор UPDATE
UPDATE <table>
SET <column=expression>
WHERE <condition>
UPDATE PERSONS
SET NAME = 'Updated Name'
WHERE PERSON_ID = 1
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.10
WHERE DEPARTMENT_ID = 3
Езикът SQL
Изтриване на данни от
таблица
Оператор DELETE
• Изтриване на редове от таблица
DELETE FROM <table>
WHERE <condition>
DELETE FROM PERSONS WHERE PERSON_ID = 1
DELETE FROM PERSONS WHERE NAME LIKE 'S%'
• Изтриване на всички данни от таблица
– TRUNCATE TABLE <table>
TRUNCATE TABLE PERSONS