- 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 могут быть сохранены только на одном или большем количестве разделов, таким образом исключая любые остающиеся разделы из поиска.