SQL

mysql.md

Commands

sudo mysqld_safe

Select a random row

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Conditional Insert

INSERT INTO TABLE(col1, col2) SELECT val1, val2 FROM dual where not exists (select * from TABLE where col1 = val1 and col2 = val2);


INSERT INTO TABLE(col1, col2) SELECT val1 as col1, val2 as col2 FROM TABLE where col1 = val1 and col2 = val2 having count(*) = 0;

-- To ignore unique contraint errors..
INSERT IGNORE INTO TABLE....

Insert or Update.

INSERT INTO TABLE (col1) values (val1) ON DUPLICATE KEY UPDATE col1 = val1

DELETE

DELETE  gc.*
FROM    guide_category AS gc 
LEFT JOIN
        guide AS g 
ON      g.id_guide = gc.id_guide
WHERE   g.title IS NULL

Monitoring

show global status
show global status like '%conn%';
show global variables like 'max_connections';
show global variables like '%conn%';
show global status like 'max%';
show variables like "%timeout%";

http://mysqlblog.fivefarmers.com/2013/08/01/practical-p_s-how-idle-are-your-connections/

oracle.md

Select a random record

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

postgres.md

Commands

postgres -D /usr/local/var/postgres

sudo -u paulocheque createuser dev

sudo -u postgres createuser dev

createdb DATABASENAME

Select a random row

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

DELETE with join

DELETE FROM table as t using anothertable as x where t.id = x.refid and t.etc = etc;

Counter

select nextval('table')
setval('table', value)

Dump/Restore

pg_dump -h localhost -p 5432 DBNAME > db.sql
pg_dump -h localhost -p 5432 DBNAME -U username -W password > db.sql
psql -h localhost DBNAME < db.sql

pg_dump -Fc -h localhost -p 5432 DBNAME > db.bin.dump
pg_restore -h localhost db.bin.dump

pg_restore --no-owner --clean --exit-on-error --disable-triggers --data-only --host localhost --port 5432 --username             "paulocheque" --dbname "DBNAME" --no-password  --verbose db.bin.dump

pg_dump -v -h [hostname] -p 5432 -U [user] -Fc [database] > [flename]
pg_restore -d [database] [filename]

Monitoring

http://www.postgresql.org/docs/9.3/static/monitoring-stats.html

-- show active queries
SELECT * FROM pg_stat_activity where state = 'active' order by query_start;
SELECT * FROM pg_stat_activity order by query_start;

-- show indexes information
SELECT * FROM pg_stat_all_indexes;
SELECT * FROM pg_stat_user_indexes;

SELECT count(*) as cnt, usename FROM pg_stat_activity GROUP BY usename ORDER BY cnt DESC limit 5;

-- DB
SELECT version();
SHOW server_version;
SHOW server_version_num;
SHOW all;
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
select * from FROM pg_database;
show ssl;
SELECT * FROM pg_statio_user_tables;
SELECT * FROM pg_stat_user_tables;
SHOW rds.extensions;

https://github.com/pganalyze/pganalyze-collector

https://github.com/zalando/pg_view

https://github.com/zalando/PGObserver

https://pganalyze.com/docs/install/01_enabling_pg_stat_statements

http://bucardo.org/wiki/Check_postgres

brew install check_postgres
check_postgres --host=host --port 5432 --dbname db --dbuser user --dbpass pass --action=connection
--action=connection
--action=backends
--action=dbstats

sql.md

SELECT

SELECT DISTINCT curso FROM aluno;

SELECT curso, count(*)
    FROM aluno
    GROUP BY curso
    HAVING count(*) > 1;

WHERE

WHERE nomeAluno LIKE ?Jos?%?;

WHERE numAluno IN ('22233', '77777777');

WHERE numAluno IN (SELECT numAluno FROM desempenho);

WHERE nota BETWEEN 6.0 AND 10.0;

ORDER BY curso DESC, nomeAluno DESC;

LIMIT 100 OFFSET 10

UPDATE

UPDATE table set column = column + 2

UPDATE TABLE as main SET column=value
FROM OTHERTABLE as x WHERE main.columnid = x.id and etc;

Join

http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server

WHERE Aluno.numAluno = Desempenho.numAluno;

TRUNCATE

TRUNCATE table
TRUNCATE table CASCADE

DELETE

DELETE FROM table1 
where table2Id IN (
select id from table2 
where date >= '2014-08-01 00:00:00' and date <= '2014-09-15 23:59:59'
);


delete from table1 a using table2 b
where a.table2Id = b.id and b.date >= '2014-09-01 00:00:00' and b.date <= '2014-09-01 23:59:59';

sqlserver.md

Select a random row

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Heroku Postgres:

https://devcenter.heroku.com/articles/heroku-postgres-import-export
heroku pg:backups capture --app APP
curl -o latest.dump heroku pg:backups public-url --app APP
createdb DB
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U paulocheque -d DB latest.dump