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