quarta-feira, 31 de julho de 2013

Truques SQL

Inserindo a partir de uma consulta SQL

Supondo que temos a seguinte estrutura de tabelas:
CREATE TABLE users(
id int not null,
name text not null,
email text
);

CREATE TABLE user_searches_results(
id int not null,
id_user int not null,
search_date date
);

Agora como nós podemos guardar todos os id's da tabela users na tabela user_searches_results retornados na seguinte consulta: SELECT id FROM users WHERE name LIKE 'Maria%' ?
Este seguinte comando sql é capaz de fazer a tarefa de forma muito eficiente:
INSERT INTO user_searches_results(id, id_user, search_date)
SELECT 1, id, now() FROM users WHERE name LIKE 'Maria%';
Simples não?

Retornando um registro aleatório a partir de uma consulta

MySQL

SELECT column1, column2, ... FROM table ORDER BY rand() LIMIT 1

PostgreSQL

SELECT column1, column2, ... FROM table ORDER BY random() LIMIT 1

SQL Server

SELECT TOP 1 column1, column2, ... FROM table
ORDER BY NEWID()

DB2

SELECT column1, column2, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Oracle

SELECT column1 FROM
( SELECT column1 FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Selecionar o segundo maior(ou menor valor) de uma coluna

SELECT MAX(column)
  FROM table
 WHERE column < (SELECT MAX(column) FROM table )

Use MIN se você quiser o segundo menor valor e não se esqueça do operador >.

Copiar a estrutura de uma tabela sem copiar os dados(MySQL, PostgresSQL)

CREATE TABLE users_2 AS (SELECT * FROM users WHERE 1=0);
Observe que índices e constraints não são duplicados.

Gerar SQL para atualizar os dados de uma tabela baseado no conteúdo de outra tabela(PostgresSQL)

Considerem a seguinte estrutura e dados:
CREATE TABLE departments(
id int not null,
name text not null,
employee_count int
);

INSERT INTO departments values(1, 'Development', 0);
INSERT INTO departments values(2, 'Sales', 0);
INSERT INTO departments values(3, 'Marketing', 0);

CREATE TABLE employees(
id int not null,
id_department int not null,
name text not null
);

INSERT INTO employees VALUES(1, 2, 'Ryu');
INSERT INTO employees VALUES(2, 2, 'Ken');
INSERT INTO employees VALUES(3, 3, 'Chun-Li');
INSERT INTO employees VALUES(4, 1, 'Guile');

Certo. Agora precisamos atualizar a tabela departments com o número de empregados que cada uma contém. Com o seguinte sql os comandos de update serão gerados automaticamente:
SELECT 'UPDATE departments SET employee_count = ' || COUNT(e.id) 
  || ' WHERE id = ' || e.id_department || ';'
FROM employees e
GROUP BY e.id_department;

A linguagem SQL pode ser mais poderosa e útil do você imagina.