SQL Commands.

mysql> select * in turba_objects;

mysql> select * from turba_objects where owner_id='luke@zina.org';

mysql> delete from turba_objects where owner_id='luke@zina.org';

mysql> CREATE DATABASE wp_gutstring;
Query OK; 1 row affected (0.04 sec)

mysql> GRANT ALL PRIVILEGES ON wp_gutstring.* TO 'thenownow'@'localhost';
Query OK; 0 rows affected (0.06 sec)

mysql> FLUSH PRIVILEGES; 
Query OK; 0 rows affected (0.07 sec)

mysql> GRANT ALL PRIVILEGES ON [dbname].* to [dbuser]@'' identified by '[dbpassword]';

mysql> SET PASSWORD FOR 'mrsnow'@'localhost' = OLD_PASSWORD('newpassword'); 
Query OK; 0 rows affected (0.03 sec) 

SHOW DATABASES;

USE mysql;
SHOW TABLES; 
SELECT user FROM `user`;

# import from gz
gunzip < ~/Documents/jobs/laudanum/Backups/dump-20090917.sql.gz | sudo mysql5 -p wp_cms

# import to remote server
mysql5 -u [username] -p -h [hostname.com] dev < dump.sql

mysql> UPDATE rc_posts SET guid = REPLACE(guid, 'dev2.houseoflaudanum.com' 'www.ruthcullen.com');
Query OK, 116 rows affected (0.03 sec)
Rows matched: 116  Changed: 116  Warnings: 0

mysql> select from_unixtime(timestamp, '%h:00 %d/%m/%Y') as date from log group by date;

# people who didn't refuse or receive the video . ( not OK, not REFUSED )
mysql > select part_id, action from log where part_id like '1.1' and action not in ('OK','REFUSED');

mysql> select from_unixtime(timestamp, '%Y/%m/%d %h:00') as date, COUNT(*) from log where action='OK' group by date;

select from_unixtime(timestamp, '%Y/%m/%d %h:00') as date, COUNT(*) from log where action='OK' group by date union select from_unixtime(timestamp, '%Y/%m/%d %h:00') as date, COUNT(*) from log where action='REFUSED' group by date

select from_unixtime(timestamp, '%Y/%m/%d %h:00') as date, COUNT(action) from (select action from log where action = 'OK' union all select action from log where action = 'refused') group by date;

mysql> SELECT FROM_UNIXTIME(timestamp, '%Y/%m/%d %H:00') AS date, SUM(CASE WHEN action='REFUSED' THEN 1 ELSE 0 END) AS refusedcount, SUM(CASE WHEN action='OK' THEN 1 ELSE 0 END) AS okcount FROM log GROUP BY date INTO OUTFILE '/tmp/result.txt';

mysql> SELECT FROM_UNIXTIME(timestamp, '%Y/%m/%d %H:00') AS date, SUM(CASE WHEN action='REFUSED' THEN 1 ELSE 0 END) AS refusedcount, SUM(CASE WHEN action='OK' THEN 1 ELSE 0 END) AS okcount, SUM(CASE WHEN action!='OK' AND action!='REFUSED' AND device_id!='EVENT' THEN 1 ELSE 0 END) AS okcount FROM log GROUP BY date INTO OUTFILE '/tmp/result.txt';

mysql> select part_id, count(*) from log where part_id like '1%' group by part_id;
+---------+----------+
| part_id | count(*) |
+---------+----------+
|       1 |      814 |
|     1.1 |       84 |
|     1.2 |       22 |
|     1.3 |        2 |
+---------+----------+

mysql> select part_id, count(*) from log where part_id like '1%' and action = 'OK' group by part_id;
+---------+----------+
| part_id | count(*) |
+---------+----------+
|       1 |      174 |
|     1.1 |       33 |
|     1.2 |       16 |
|     1.3 |        2 |
+---------+----------+

mysql> select part_id, count(*) from log where part_id like '1%' and action = 'REFUSED' group by part_id;
+---------+----------+
| part_id | count(*) |
+---------+----------+
|       1 |      595 |
|     1.1 |       41 |
|     1.2 |        6 |
+---------+----------+

mysql> select * from log where exists ( select 1 from log as earlierrefusal where earlierrefusal.device_id = log.device_id and earlierrefusal.timestamp < log.timestamp and earlierrefusal.action = 'REFUSED' );

mysql> select device_id, part_id, count(*) as messagecount from log group by device_id, part_id having count(*) > 1;

Categories: Snippets

Tags: SQL