SQL: основы (индексы, ключи, нормализация), примеры SQL запросов, работа с таблицами CREATE, DROP, EDIT; использование SELECT (join, distinct, enlosed, like, sum, etc)

  • http://sqlfiddle.com/ – Очень удобный сайт по экспериментам с SQL (запросы, создание страниц и проч.).
Отдельные статьи
SQL Clients
  • Toad – enterprise продукт, работает с популярными в крупном enterprise как SQL, так и noSQL базами
  • DBeaver
    • Community версия – работает с большим количеством relational databases (по сути всеми известными популярными), используя JDBC
    • Enterprise – поддерживает работу с noSQL базами типа MongoDB, Cassandra, Redis
  • phpmyadmin, MySQL workbench – для работы только с MySQL, второй более продвинутый
  • консольные для каждой базы
    • mysql
    • sqlite
    • psql
BASIC SQL

DDL – Data Definition Language

CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT

DML – Data Manipulation Language

SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

DCL – Data Control Message

GRANT
REVOKE

TCL – Transaction Control Language

COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION

 

 

Create INDEX (индексы)

Ускорение select может быть достигнуто за счет использования индексов. Нужно учитывать, что index’ы могут негативно влиять на write, т.е. если в таблицу ожидается большое количество write при меньшем количестве read – в среднем лучше индексы не использовать и наоборот. Так же по сути создается вспомогательная таблица – это доп. память.

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

Сами индексы работают быстрее за счет оптимизации структуры хранения данных – в виде отсортированных/сбалансированного дерева (проиндексирорванных) данных по которым проще осуществлять поиск.

Жизненный пример – поиск в базе 4 млн записей:

    • часы до индексирования
    • после индексирования 3 секунды

Можно проиндексировать таблицу по полю.

CREATE INDEX <table>_index ON <table>(<field>)

 

DROP

В современных базах (MS SQL, MySQL, да даже sqlite3 python) поддерживается полезная функция DROP, но только в случае exist объекта (базы).

DROP TABLE if exists <table>

 

SELECT

Сопоставление таблиц (join, multiple where, вложенные запросы)

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

Данные в Oracle из нескольких таблиц можно выбрать и сопоставить не только с помощью стандартного join (ANSI синтаксис), но и с помощью указания нескольких таблиц в поле from (в базе, сделанных как вложенный запрос или временных таблиц). Это традицонный синтаксис Oracle.

Данные в таком случае могут выбираться в таблицы по разному, но сопоставление в любом случае идет через WHERE. Если в условиях WHERE указать (+) – это позволяет добавить данные, в случае если подпадение нулевое – т.е. отсутствие данных в одной из таблиц считается подпадением.

#1 JOIN
SELECT w.n,w.employee,w.emp_phone,wbox.equip_type,wbox.entrance,wbox.place
FROM walli_theft w 
LEFT JOIN walli_theft_box wbox on w.n=wbox.n 
WHERE w.h_id='119783'
ORDER BY theft_date

#2 MULTIPLE FROM
SELECT w.n,w.employee,w.emp_phone,wbox.equip_type,wbox.entrance,wbox.place
FROM walli_theft w, walli_theft_box wbox 
WHERE 1=1
AND w.h_id='119783'
AND w.n=wbox.n
ORDER BY theft_date

#3 TEMP TABLE
with
wbox as (
     select * from walli_theft_box
)
SELECT w.n,w.employee,w.emp_phone,wbox.equip_type,wbox.entrance,wbox.place
FROM walli_theft w, wbox
WHERE 1=1
AND w.h_id='119783'
AND w.n=wbox.n
ORDER BY theft_date

#4 ENCLOSED
SELECT w.n,w.employee,w.emp_phone,wbox.equip_type,wbox.entrance,wbox.place
FROM walli_theft w, (select * from walli_theft_box) wbox
WHERE 1=1
AND w.h_id='119783'
AND w.n=wbox.n
ORDER BY theft_date

временная таблица, формируемая на время запроса

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

with
 reg as (
  select distinct l.region, d.name
  from dict_one l, dict_two d
  where 1=1
  and d.id=1 and d.code=l.region
 ),
 bra as (
  select distinct l.branch, d.name
  from dict_one l, dict_two d
  where 1=1
  and d.id=2 and d.code=l.branch
 ),
 distr as (
  select distinct l.id, d.name
  from dict_one l, dict_two d
  where 1=1
  and d.id=3 and d.code=l.district
 )
SELECT tb.data,reg.name reg,bra.name bra,distr.name distr
FROM tb t, reg, bra, distr
WHERE 1=1
AND t.region=reg.region
AND t.branch=bra.branch
AND t.area=distr.id

distinct, group by, join

Простой GROUP BY где мы имеем некоторый ID и счетчик повторений этого ID с использованием COUNT (или сумму значений для этого ID с использованием SUM) для одной таблицы.

SELECT <field>, COUNT(<field>) 
FROM <table> 
GROUP BY <field>
EXMPL: SELECT Customer, SUM(OrderPrice) FROM orders GROUP BY Customer
Так же можно присвоить имя полю со счетчиком Count/Sum.
SELECT <field>, COUNT(<field>) AS CountOf
FROM <table> 
GROUP BY <field>
EXMPL: SELECT Customer, SUM(OrderPrice) AS sums FROM orders GROUP BY Customer

На выборку GROUP BY получившую имя с использованием AS можно применить фильтр с использованием HAVING, но не WHERE. Альтернативой HAVING может быть использование вложенных запросов, но читаемость такого кода хуже.

SELECT Customer, SUM(OrderPrice) AS sums FROM orders GROUP BY Customer HAVING sums < 2000

Если все что нужно это исключение дублей (уникальные записи) – нужно использовать DISTINCT, он работает быстрее. Но если нужно исключение дублей (уникальные записи) в рамках нескольких полей – зачастую проще использовать GROUP BY. К примеру, в запросе ниже мы объединяем таблицу пользователей и покупок с использованием LEFT JOIN и отображаем только Имена и Фамилии пользователей, которые имеют заказы с использованием WHERE и GROUP BY.

SELECT p.FirstName, p.LastName FROM persons p LEFT JOIN orders o on p.P_id=o.P_id WHERE o.OrderNo IS NOT NULL GROUP BY p.FirstName, p.LastName

 

стилистика

операции нужно писать с большой буквы, а переменные с малой. В таком случае sublime сможет подсветить синтаксис (должен быть большими SELECT в начале запроса)

date

mysql за три (n) последних месяца (с текущего дня)

select * from table where date >= now()-interval 3 month

mysql за последние тридцать (n) дней

SELECT * FROM table WHERE exec_datetime BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();

like

В Oracle можно спокойно указывать в качестве объекта LIKE переменную, конкатенцию переменной и спец-символа % делаем через ||.

AND gg.gp_text LIKE '%' || na.net_address || '%'

сумма значений выборки (столбца)

SELECT sum(ticket_counts) FROM ticket_films WHERE film='GoT'

вложенные запросы

Пример вложенного SQL – на базе результатов делаем последующие запросы.

-- FIND SWITCHES BY SY_ID overall
select device_id, device_name, serial_number from devices@orange dv where dv.group_id in (
       select device_group_id from os_eqm.device_groups@orange where group_type_id='605' and par_group_id in (
                   select device_group_id from os_eqm.device_groups@orange where group_type_id='1190' and par_group_id=(select header_id from groups_604@orange g4 where g4.adres='554') -- TKD by SY_ID
	     ) -- YASHIK by TKD; 
); -- DEVICES_ID + NAME + SERIAL by yashik

 

Партиционирование таблиц

Отсюда и отсюда

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

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

Leave a Reply