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

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

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