[Pg/My] SQL сниппеты

Категория: / DEV Блог / PHP (LAMP)

Postgresql



Замена в тексте

Update authors set city = replace(city, 'Salt', 'Olympic');
update documents set body = replace(body, '542-1177', '(495) 542-4977') where title like 'example%';


Случайное число

rand() as random
order by random


Работа с датами

select date_part('epoch',now()) as unixtime;  
select extract('epoch' from now()) as unixtime;
SELECT '2001-01-01'::timestamp + '1 year'::interval;
--where date is unixtime
WHERE date >= date_part('epoch', (to_timestamp('" . date('d.m.Y') . " 19:00', 'DD.MM.YYYY HH:MI') - '1 day'::interval))
select to_timestamp('2002.11.03 04:16:00', 'YYYY.MM.DD HH24:MI:SS');
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); --Result: 16
SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); -- Result: 4


Limit

LIMIT $limit OFFSET $offset

На закуску

Имеется база данных:

CREATE TABLE authors
(
id serial NOT NULL,
name varchar(64) NOT NULL,
CONSTRAINT "pk authors" PRIMARY KEY (id)
);
 
CREATE TABLE books
(
id serial NOT NULL,
author_id int NOT NULL,
name varchar(255) NOT NULL,
published timestamp without time zone NULL, - если NULL, то книга еще не издана.
CONSTRAINT "pk books" PRIMARY KEY (id),
CONSTRAINT "fk books/authors"
FOREIGN KEY (author_id)
REFERENCES authors (id) ON UPDATE CASCADE ON DELETE CASCADE
);
 
CREATE UNIQUE INDEX "ix books" ON books (author_id, published);


+--------------------------------------------------------------------- ----------+
Надо вывести: | имя автора | название последней изданной книги | дата издания последней книги |
+--------------------------------------------------------------------- ----------+


То есть, написать запрос, который выводит всех авторов, у которых ИЗДАНО более двух книг и показать
последнюю изданную книгу каждого автора и дату ее издания.
Предполагается, что таблицы огромные, поэтому запрос ДОЛЖЕН быть максимально экономичным.

SELECT a.name, b.name, b.published
FROM books b
INNER JOIN
(
SELECT author_id, MAX(published) as published
FROM books
WHERE published IS NOT NULL
GROUP BY author_id
HAVING COUNT(id) > 2
) as x
ON x.author_id = b.author_id
AND x.published = b.published
INNER JOIN
authors a
ON a.id = x.author_id;



Mysql



Запрос тестирования производительности системы. Может использоваться для доса при недостаточной фильтрации входящих данных скриптом.

SELECT BENCHMARK(500000, MD5(rand()))