paulocheque · February 27, 2016 16:52 · Dec 2, 2014 · Dec 1, 2014 · Dec 1, 2014 · Dec 1, 2014
diff --git a/mysql.md b/mysql.md
@@ -27,6 +27,16 @@ 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
 ======================

diff --git a/postgres.md b/postgres.md
@@ -16,6 +16,13 @@ Select a random row
     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
 ======================
 

diff --git a/sql.md b/sql.md
@@ -29,6 +29,8 @@ 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
 ======

diff --git a/postgres.md b/postgres.md
@@ -41,8 +41,19 @@ Dump/Restore
 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;
@@ -52,13 +63,16 @@ Monitoring
     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
@@ -67,4 +81,5 @@ http://bucardo.org/wiki/Check_postgres
     --action=backends
     --action=dbstats
 
-
+
+
diff --git a/postgres.md b/postgres.md
@@ -54,9 +54,17 @@ Monitoring
     SELECT * FROM pg_stat_user_tables;
 
 https://github.com/pganalyze/pganalyze-collector
+
 https://github.com/zalando/pg_view
-https://github.com/zalando/PGObserver
 
+https://github.com/zalando/PGObserver
 
+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
+
 
diff --git a/postgres.md b/postgres.md
@@ -36,3 +36,27 @@ Dump/Restore
 
     pg_dump -v -h [hostname] -p 5432 -U [user] -Fc [database] > [flename]
     pg_restore -d [database] [filename]
+
+
+Monitoring
+===============
+
+    SELECT * FROM pg_stat_activity order by query_start;
+    SELECT count(*) as cnt, usename FROM pg_stat_activity GROUP BY usename ORDER BY cnt DESC limit 5;
+    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;
+
+https://github.com/pganalyze/pganalyze-collector
+https://github.com/zalando/pg_view
+https://github.com/zalando/PGObserver
+
+
+
+
diff --git a/mysql.md b/mysql.md
@@ -34,6 +34,7 @@ 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%";
 

diff --git a/mysql.md b/mysql.md
@@ -31,9 +31,10 @@ Insert or Update.
 Monitoring
 ======================
 
-show global status
-show global status like '%conn%';
-show global variables like 'max_connections';
-show global status like 'max%';
-show variables like "%timeout%";
+    show global status
+    show global status like '%conn%';
+    show global variables like 'max_connections';
+    show global status like 'max%';
+    show variables like "%timeout%";
+
 http://mysqlblog.fivefarmers.com/2013/08/01/practical-p_s-how-idle-are-your-connections/
diff --git a/mysql.md b/mysql.md
@@ -36,3 +36,4 @@ show global status like '%conn%';
 show global variables like 'max_connections';
 show global status like 'max%';
 show variables like "%timeout%";
+http://mysqlblog.fivefarmers.com/2013/08/01/practical-p_s-how-idle-are-your-connections/
diff --git a/mysql.md b/mysql.md
@@ -35,3 +35,4 @@ show global status
 show global status like '%conn%';
 show global variables like 'max_connections';
 show global status like 'max%';
+show variables like "%timeout%";
diff --git a/mysql.md b/mysql.md
@@ -31,5 +31,7 @@ Insert or Update.
 Monitoring
 ======================
 
+show global status
+show global status like '%conn%';
 show global variables like 'max_connections';
 show global status like 'max%';
diff --git a/mysql.md b/mysql.md
@@ -26,3 +26,10 @@ Insert or Update.
 ======================
 
     INSERT INTO TABLE (col1) values (val1) ON DUPLICATE KEY UPDATE col1 = val1
+
+
+Monitoring
+======================
+
+show global variables like 'max_connections';
+show global status like 'max%';
diff --git a/mysql.md b/mysql.md
@@ -15,6 +15,9 @@ 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....
 

diff --git a/mysql.md b/mysql.md
@@ -8,3 +8,18 @@ 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);
+
+    -- To ignore unique contraint errors..
+    INSERT IGNORE INTO TABLE....
+
+
+Insert or Update.
+======================
+
+    INSERT INTO TABLE (col1) values (val1) ON DUPLICATE KEY UPDATE col1 = val1
diff --git a/postgres.md b/postgres.md
@@ -2,9 +2,12 @@ Commands
 ======================
 
 postgres -D /usr/local/var/postgres
+
 sudo -u paulocheque createuser dev
+
 sudo -u postgres createuser dev
 
+createdb DATABASENAME
 
 Select a random row
 ======================

diff --git a/sql.md b/sql.md
@@ -33,6 +33,8 @@ UPDATE
 Join
 ======
 
+http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server
+
     WHERE Aluno.numAluno = Desempenho.numAluno;
 
 

diff --git a/postgres.md b/postgres.md
@@ -30,3 +30,6 @@ Dump/Restore
     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]
diff --git a/postgres.md b/postgres.md
@@ -2,7 +2,8 @@ Commands
 ======================
 
 postgres -D /usr/local/var/postgres
-psql -u postgres createuser -P application
+sudo -u paulocheque createuser dev
+sudo -u postgres createuser dev
 
 
 Select a random row

diff --git a/postgres.md b/postgres.md
@@ -2,6 +2,8 @@ Commands
 ======================
 
 postgres -D /usr/local/var/postgres
+psql -u postgres createuser -P application
+
 
 Select a random row
 ======================

diff --git a/mysql.md b/mysql.md
@@ -1,3 +1,7 @@
+Commands
+======================
+sudo mysqld_safe
+
 Select a random row
 ======================
 

diff --git a/postgres.md b/postgres.md
@@ -1,3 +1,8 @@
+Commands
+======================
+
+postgres -D /usr/local/var/postgres
+
 Select a random row
 ======================
 

diff --git a/general.md → sql.md b/general.md → sql.md
diff --git a/general.md b/general.md
@@ -21,6 +21,8 @@ WHERE
     WHERE nota BETWEEN 6.0 AND 10.0;
 
     ORDER BY curso DESC, nomeAluno DESC;
+
+    LIMIT 100 OFFSET 10
 
 UPDATE
 ===========

diff --git a/postgres.md b/postgres.md
@@ -15,6 +15,7 @@ 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

diff --git a/general.md b/general.md
@@ -31,8 +31,7 @@ UPDATE
 Join
 ======
 
-    WHERE Aluno.numAluno =
-    Desempenho.numAluno;
+    WHERE Aluno.numAluno = Desempenho.numAluno;
 
 
 

diff --git a/mysql.md b/mysql.md
@@ -1,10 +1,6 @@
-Select a random row with MySQL:
+Select a random row
+======================
 
-SELECT column FROM table
-ORDER BY RAND()
-LIMIT 1
-Select a random row with PostgreSQL:
-
-SELECT column FROM table
-ORDER BY RANDOM()
-LIMIT 1
+    SELECT column FROM table
+    ORDER BY RAND()
+    LIMIT 1
diff --git a/oracle.md b/oracle.md
@@ -1,6 +1,7 @@
-Select a random record with Oracle:
+Select a random record
+======================
 
-SELECT column FROM
-( SELECT column FROM table
-ORDER BY dbms_random.value )
-WHERE rownum = 1
+    SELECT column FROM
+    ( SELECT column FROM table
+    ORDER BY dbms_random.value )
+    WHERE rownum = 1
diff --git a/postgres.md b/postgres.md
@@ -1,10 +1,23 @@
-select nextval('table')
-setval('table', value)
+Select a random row
+======================
 
-pg_dump -h localhost -p 5432 DBNAME > db.sql
-psql -h localhost DBNAME < db.sql
+    SELECT column FROM table
+    ORDER BY RANDOM()
+    LIMIT 1
+
+Counter
+======================
 
-pg_dump -Fc -h localhost -p 5432 DBNAME > db.bin.dump
-pg_restore -h localhost db.bin.dump
+    select nextval('table')
+    setval('table', value)
 
-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
+Dump/Restore
+======================
+
+    pg_dump -h localhost -p 5432 DBNAME > 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
diff --git a/sqlserver.md b/sqlserver.md
@@ -1,4 +1,5 @@
-Select a random row with Microsoft SQL Server:
+Select a random row
+======================
 
-SELECT TOP 1 column FROM table
-ORDER BY NEWID()
+    SELECT TOP 1 column FROM table
+    ORDER BY NEWID()
diff --git a/general.md b/general.md
@@ -1,46 +1,56 @@
-Basic
+SELECT
 ==============
 
-SELECT DISTINCT curso FROM aluno;
+    SELECT DISTINCT curso FROM aluno;
 
-SELECT curso, count(*)
+    SELECT curso, count(*)
         FROM aluno
         GROUP BY curso
         HAVING count(*) > 1;
 
-UPDATE table SET col1=value1, col2=value2 WHERE col3=value3
 
 WHERE
 ======
-WHERE nomeAluno LIKE ?Jos?%?;
 
-WHERE numAluno IN ('22233', '77777777');
+    WHERE nomeAluno LIKE ?Jos?%?;
 
-WHERE numAluno IN (SELECT numAluno FROM desempenho);
+    WHERE numAluno IN ('22233', '77777777');
 
-WHERE nota BETWEEN 6.0 AND 10.0;
+    WHERE numAluno IN (SELECT numAluno FROM desempenho);
+
+    WHERE nota BETWEEN 6.0 AND 10.0;
+
+    ORDER BY curso DESC, nomeAluno DESC;
+
+UPDATE
+===========
+
+    UPDATE table set column = column + 2
 
-ORDER BY curso DESC, nomeAluno DESC;
 
 Join
 ======
 
-WHERE Aluno.numAluno =
-Desempenho.numAluno;
+    WHERE Aluno.numAluno =
+    Desempenho.numAluno;
 
 
 
-more
+TRUNCATE
 ======
-TRUNCATE table
-TRUNCATE table CASCADE
 
+    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 
-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';
+    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';
diff --git a/general.md b/general.md
@@ -33,4 +33,14 @@ Desempenho.numAluno;
 more
 ======
 TRUNCATE table
-TRUNCATE table CASCADE
+TRUNCATE table CASCADE
+
+
+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';
diff --git a/general.md b/general.md
@@ -1,10 +1,15 @@
+Basic
+==============
+
 SELECT DISTINCT curso FROM aluno;
 
 SELECT curso, count(*)
         FROM aluno
         GROUP BY curso
         HAVING count(*) > 1;
 
+UPDATE table SET col1=value1, col2=value2 WHERE col3=value3
+
 WHERE
 ======
 WHERE nomeAluno LIKE ?Jos?%?;
@@ -23,6 +28,8 @@ Join
 WHERE Aluno.numAluno =
 Desempenho.numAluno;
 
+
+
 more
 ======
 TRUNCATE table

diff --git a/general.md b/general.md
@@ -23,4 +23,7 @@ Join
 WHERE Aluno.numAluno =
 Desempenho.numAluno;
 
-TRUNCATE table
+more
+======
+TRUNCATE table
+TRUNCATE table CASCADE
diff --git a/general.md b/general.md
@@ -21,4 +21,6 @@ Join
 ======
 
 WHERE Aluno.numAluno =
-Desempenho.numAluno;
+Desempenho.numAluno;
+
+TRUNCATE table
diff --git a/general.md b/general.md
@@ -8,9 +8,13 @@ SELECT curso, count(*)
 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;
 
 Join

diff --git a/postgres.md b/postgres.md
@@ -1,2 +1,10 @@
 select nextval('table')
-setval('table', value)
+setval('table', value)
+
+pg_dump -h localhost -p 5432 DBNAME > 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
diff --git a/general.md b/general.md
@@ -0,0 +1,20 @@
+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;
+
+Join
+======
+
+WHERE Aluno.numAluno =
+Desempenho.numAluno;
diff --git a/mysql.md b/mysql.md
@@ -0,0 +1,10 @@
+Select a random row with MySQL:
+
+SELECT column FROM table
+ORDER BY RAND()
+LIMIT 1
+Select a random row with PostgreSQL:
+
+SELECT column FROM table
+ORDER BY RANDOM()
+LIMIT 1
diff --git a/oracle.md b/oracle.md
@@ -0,0 +1,6 @@
+Select a random record with Oracle:
+
+SELECT column FROM
+( SELECT column FROM table
+ORDER BY dbms_random.value )
+WHERE rownum = 1
diff --git a/postgres.md b/postgres.md
@@ -0,0 +1,2 @@
+select nextval('table')
+setval('table', value)
diff --git a/sqlserver.md b/sqlserver.md
@@ -0,0 +1,4 @@
+Select a random row with Microsoft SQL Server:
+
+SELECT TOP 1 column FROM table
+ORDER BY NEWID()
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		select nextval('table')
		setval('table', value)
No results found