Edu notes: SQL basics (based on PostgreSQL)

Простейшие

SELECT 'на экране отображается число', 5;
-- Так выглядит однострочный комментарий
многострочный комментарий ниже

/*В век высоких технологий

Без БД не обойтись,

SQL — язык запросов

Легче делает нам жизнь!*/

SELECT * FROM genre;
SELECT * FROM media_type;
SELECT * FROM playlist;

Как ограничивать выборку

SELECT поле_1,
       поле_2,
       поле_3
       ...
FROM таблица
LIMIT 10 OFFSET 5;

SELECT email,
       first_name,
       last_name
FROM client;

SELECT invoice_id,
       customer_id,
       invoice_date,
       billing_address
FROM invoice
LIMIT 5
OFFSET 0;

SELECT name,
       unit_price
FROM track
LIMIT 20
OFFSET 0;

SELECT *
FROM track
LIMIT 3
OFFSET 7; -- выводим строки с 8 по 10 включительно

Типы данных

https://www.postgresql.org/docs/current/datatype.html

integer от -2147483648 до +2147483647
real
character обозначают псевдонимом char(n), если не указывать n, то по умолчанию он 1. n- количество символов в хранимой строке (меньше можно, больше нельзя)
character varying, псевдоним varchar(n) - тексты нефиксированной длины. Значение n управляет лимитом строки, но его можно не указывать.
text - содержит строку любой длины. Такого типа в стандарте SQL нет, но в некоторых СУБД, помимо PostgreSQL, его тоже используют.
timestamp - содержит дату и время. Чаще всего дату и время указывают в формате ISO: 'YYYY-MM-DD'. Например, 30 июня 2010 года записывают так: '2010-06-30' (timestamp with time zone или timestamptz, timestamp without time zone в Postgres по умолчанию)
В тип date входит только дата. Дату можно задавать в любом формате, например, October 19, 2004, 2004-Oct-19 или даже 20041019.
Тип time хранит только время и принимает значения от 00:00:00 до 24:00:00.
Тип interval используют для обозначения интервала между датами. Задать нужный интервал можно с помощью условных обозначений или в свободной форме. Например, запись '1 12:59:10' обозначает 1 день 12 часов 59 минут 10 секунд.
boolean - Для TRUE список аналогий такой: 'true', 't', 'yes', 'y', 'on', '1'. Вместо FALSE можно написать: 'false', 'f', 'no', 'n', 'off', '0'.

Поменять тип данныйх можно сразу при выгрузке данных, при этом в самой таблице тип не изменится

SELECT CAST(track_id AS varchar),
       CAST(album_id AS varchar)
FROM track;

SELECT CAST(milliseconds AS varchar),
       CAST(bytes AS varchar)
FROM track;

SELECT CAST(total as int)
FROM invoice;

SELECT CAST(birth_date AS date)
FROM staff;

Оператор WHERE. Операторы сравнения

SELECT track_id, -- поля для выгрузки
       album_id 
       ...
FROM track -- таблица, из которой выгружают данные
WHERE track_id = 5; -- условие для среза данных >, <, >=, <=

SELECT *
/* Нужно выгрузить всю информацию о заказах,
поэтому поля можно не указывать*/
FROM invoice
WHERE total >= 1.2;

SELECT *
FROM invoice_line
WHERE unit_price>0.99;

SELECT first_name,
       last_name,
       city
FROM client
WHERE country='Brazil';

SELECT billing_address,
       CAST(invoice_date as date)
FROM invoice
WHERE total>=8;

SELECT invoice_id,
       billing_country,
       CAST(invoice_date as date)
FROM invoice
WHERE CAST(invoice_date as date)>='2009-01-06';

Логические операторы

WHERE city = 'Тула'
  AND age > 43;

WHERE city = 'Тула'
  OR city = 'Пермь';

WHERE NOT city = 'Тула'
  AND NOT city = 'Пермь';

Условия с оператором AND обладают большим приоритетом, чем условия с OR.

WHERE age > 43
  AND city = 'Тула'
  OR city = 'Пермь'
  AND age < 43;

WHERE age > 43
  AND (city = 'Тула'
  OR city = 'Пермь');

SELECT total,
       customer_id
FROM invoice
WHERE billing_city = 'Dublin'
      OR billing_city = 'London'
      OR billing_city = 'Paris';

SELECT total,
       customer_id
FROM invoice
WHERE total >= 5
      AND (customer_id = 40
           OR customer_id = 46);

SELECT total,
       customer_id
FROM invoice
WHERE (billing_city = 'Dublin'
      OR billing_city = 'London'
      OR billing_city = 'Paris')
      AND total >= 5
      AND (customer_id = 40
          OR customer_id = 46);

SELECT last_name,
       phone
FROM client
WHERE country IN ('USA', 'France')
      AND support_rep_id = 3;

SELECT title
FROM movie
WHERE rental_rate<=2
      AND rental_duration>6
      AND rating NOT IN ('PG', 'PG-13');

SELECT billing_address,
       billing_city
FROM invoice
WHERE
      CAST(invoice_date as date) BETWEEN '2009-09-01' AND '2009-09-30'
      AND NOT billing_country IN ('USA', 'Brazil')
      AND total>=2;

SELECT *
FROM invoice
WHERE customer_id BETWEEN 23 AND 48;

Оператор LIKE

Оператор регистрозависимый

SELECT *
FROM playlist_track
WHERE CAST(track_id AS varchar) LIKE '%43%';

SELECT name
FROM playlist
WHERE name LIKE '%Classic%';

SELECT billing_address,
       billing_country
FROM invoice
WHERE billing_country IN ('USA', 'India', 'Canada', 'Argentina', 'France');

SELECT billing_address,
       billing_country
FROM invoice
WHERE billing_country IN ('USA',
                          'India',
                          'Canada',
                          'Argentina',
                          'France')
		  AND billing_city NOT IN ('Redmond',
                                   'Lyon',
                                   'Delhi');

SELECT *
FROM invoice
WHERE CAST(invoice_date as date) BETWEEN '2009-03-04' AND '2012-02-09'
      AND total<=5
      AND billing_country NOT IN ('Canada', 'Brazil', 'Finland');

SELECT title
FROM movie
WHERE description LIKE '%Mexico'
      AND (rental_rate<2 code="" or="" rating="">

SELECT name
FROM track
WHERE (milliseconds>300000
       AND composer LIKE '%Bono%'
       AND genre_id IN (7, 8, 9, 10))
      OR
      (bytes>1000000000);

Операторы и функции для работы с датой и временем

CURRENT_DATE вернёт текущую дату,
CURRENT_TIME выведет текущее время,
CURRENT_TIMESTAMP вернёт текущие дату и время.

SELECT CURRENT_DATE;


DATE_TRUNC('отрезок времени', поле)

'microseconds' — микросекунды;
'milliseconds' — миллисекунды;
'second' — секунда;
'minute' — минута;
'hour' — час;
'day' — день;
'week' — неделя;
'month' — месяц;
'quarter' — квартал;
'year' — год;
'decade' — десятилетие;
'century' — век.

SELECT DATE_TRUNC('year', birth_date)
FROM staff
LIMIT 5;

Данный запрос вернет дату округленную до первого числа каждого года.

EXTRACT(отрезок времени FROM поле)

CENTURY — век;
DAY — день;
DOY (от англ. day of the year) — день года, выраженный числом от 1 до 365 или 366, если год високосный;
DOW (от англ. day of the week) — день недели, выраженный числом от 0 до 6, где понедельник — 1, воскресенье — 0.
ISODOW (от англ. day of the week и ISO 8601) — день недели, выраженный числом от 1 до 7, где понедельник — 1, воскресенье — 7.
HOUR — час;
MILLISECOND — миллисекунда;
MINUTE — минута;
MONTH — месяц;
SECOND — секунда;
QUARTER — квартал;
WEEK — неделя в году;
YEAR — год.

SELECT EXTRACT(YEAR FROM birth_date)
FROM staff
LIMIT 5;

Функции DATE_TRUNC и EXTRACT принимают на вход данные тех типов, которые используют для работы с датой и временем. Но есть особенности. Типы date и time функции автоматически переведут в timestamp with time zone. У этой проблемы есть решение — явно изменить тип данных на timestamp. Напомним, что в SQL так по умолчанию обозначается тип timestamp without time zone. С типом данных без часового пояса время не сместится.

SELECT customer_id,
       invoice_date,
       total,
       DATE_TRUNC('month', CAST(invoice_date AS timestamp)),
       EXTRACT(WEEK from CAST(invoice_date AS timestamp))
FROM invoice
WHERE customer_id BETWEEN 20 AND 50;

SELECT customer_id,
       invoice_date,
       total,
       DATE_TRUNC('month', CAST(invoice_date AS timestamp)),
       EXTRACT(WEEK FROM CAST(invoice_date AS timestamp))
FROM invoice
WHERE customer_id BETWEEN 20 AND 50 
      AND EXTRACT(WEEK FROM CAST(invoice_date AS timestamp)) IN (5, 7, 10, 33, 48);

SELECT *
FROM invoice
WHERE EXTRACT(DAY FROM CAST(invoice_date AS timestamp))=1;

SELECT email
FROM staff
WHERE city='Calgary'
      AND EXTRACT(YEAR FROM hire_date)=2002;

SELECT DATE_TRUNC('week', CAST(invoice_date AS timestamp)),
       DATE_TRUNC('week', CAST(invoice_date AS timestamp))+'1 00:00:00',
       EXTRACT(DOW FROM CAST(invoice_date AS timestamp)),
       invoice_date
FROM invoice;

NULL это пропуски данных. С NULL нельзя сравнить какое-либо значение с помощью любых операторов: =, <, >, LIKE. Для работы со специальными значениями используют отдельные операторы — IS NULL и IS NOT NULL.

SELECT email,
         fax
FROM client
WHERE fax IS NULL
LIMIT 5;

SELECT billing_city
FROM invoice
WHERE billing_postal_code IS NULL;

SELECT billing_city
FROM invoice
WHERE billing_postal_code IS NULL
      AND billing_state IS NOT NULL
      AND total >= 15;

SELECT album_id
FROM track
WHERE milliseconds>250000
      AND name LIKE '%Moon%'
      AND composer IS NULL;

SELECT first_name,
       last_name,
       country
FROM client
WHERE company IS NULL
      AND state IS NULL
      AND phone IS NULL
      AND fax IS NULL

Конструкция CASE Конструкция с оператором CASE выводит новое поле с результатом проверки условий. Это поле появляется только в выдаче, а оригинальная таблица остаётся без изменений.

SELECT total,
       CASE
           WHEN total < 5 THEN 'маленький'
           WHEN total >= 5 AND total < 10 THEN 'средний'
           WHEN total >= 10 THEN 'крупный'
       END
FROM invoice
LIMIT 10;

Если значение в проверяемом поле не удовлетворяет ни одному условию, результатом будет NULL. Условия в запросе проверяются по порядку. Если условие выполнено, сразу выводится соответствующий результат, а остальные условия не проверяются.

SELECT total,
       CASE
           WHEN total >= 5 AND total < 10 THEN 'средний'
           WHEN total >= 10 THEN 'крупный'
           ELSE 'маленький'
       END
FROM invoice
LIMIT 10;

SELECT last_name,
       first_name,
       title,
      CASE 
          WHEN title LIKE '%Manager%' AND title NOT LIKE '%IT%' THEN 'отдел продаж'
          WHEN title LIKE '%IT%' THEN 'разработка'
          WHEN title LIKE '%Support%' THEN 'поддержка'
      END
FROM staff;

SELECT title,
       rental_rate,
    CASE
        WHEN rental_rate<1 THEN 'категория 1'
        WHEN rental_rate>=1 AND rental_rate<3 THEN 'категория 2'
        WHEN rental_rate>=3 THEN 'категория 3'
    END
FROM movie;

Edu notes

пятница, 15 марта 2024 г.

SQL basics (based on PostgreSQL)

Простейшие

Как ограничивать выборку

Типы данных

Оператор WHERE. Операторы сравнения

Логические операторы

Оператор LIKE

Операторы и функции для работы с датой и временем

Комментариев нет:

Отправить комментарий

пятница, 15 марта 2024 г.

SQL basics (based on PostgreSQL)

Простейшие

Как ограничивать выборку

Типы данных

Оператор WHERE. Операторы сравнения

Логические операторы

Оператор LIKE

Операторы и функции для работы с датой и временем

Комментариев нет:

Отправить комментарий

пятница, 15 марта 2024 г.