While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.
-- create database|schema for utility objects
create schema if not exists `util`;
-- drop any existing copies of numbers
drop table if exists `util`.`numbers`;
-- create numbers table structure (very basic)
create table `util`.`numbers` (
`n` int(11) not null,
primary key (`n`)
) engine=InnoDB default charset=latin1;
-- insert generated number sequence (e.g., 1-1,000,000)
insert into `util`.`numbers`(n)
select @rownum:=@rownum+1
from (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) a, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) b, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) c, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) d, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) e, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) f, (select @rownum:=0) r
;
Voilà !select n from util.numbers limit 10;
select dates.dt, sum(coalesce(bks.bk_amount, 0)) as total_sales
from (
-- generate table of dates
select date_add(str_to_date('2009-09-01','%Y-%m-%d'), interval n day) as dt
from util.numbers
where n between 0 and 60
) dates
left outer join bookings bks on bks.bk_date = dates.dt
group by dates.dt
;
Notice how easily we generated all the days from September 1st to October 31st. Now from there we simply use a left join to bring in matching sales data and aggregate to our heart’s content. mysql> SELECT
-> GROUP_CONCAT(
-> CONCAT(
-> IF(help_keyword_id=252,
-> REPLACE(RIGHT(`name`, 4), 'S', ' '),
-> `name`),
-> IF(help_keyword_id=43, ',', ''))
-> ORDER BY help_keyword_id
-> SEPARATOR ' ') AS `DBA Trash Talk`
-> FROM mysql.help_keyword
-> WHERE help_keyword_id IN (43, 86, 149, 252, 419, 449)
-> ;
+-----------------------------------------+
| DBA Trash Talk |
+-----------------------------------------+
| DATA, ROLLBACK FOR I AM DATABASE MASTER |
+-----------------------------------------+
SELECT help_topic_id AS n
FROM mysql.help_topic
WHERE help_topic_id BETWEEN 0 AND 9
;
SELECT @rownum:=COALESCE(@rownum+1, 0) AS n
FROM mysql.help_topic
JOIN (SELECT @rownum:=NULL) r
LIMIT 10
-- create database|schema for utility objects
create schema if not exists `util`;
-- drop any existing copies of numbers
drop table if exists `util`.`numbers`;
-- create numbers table structure (very basic)
create table `util`.`numbers` (
`n` int(11) not null,
primary key (`n`)
) engine=InnoDB default charset=latin1;
-- insert generated number sequence (e.g., 1-1,000,000)
insert into `util`.`numbers`(n)
select @rownum:=@rownum+1
from (select @rownum:=0) r
join (
select help_topic_id
from mysql.help_topic
limit 100
) a
join (
select help_topic_id
from mysql.help_topic
limit 100
) b
join (
select help_topic_id
from mysql.help_topic
limit 100
) c
;
Data, rollback for I am [your] database master!
Title | Views | Activity |
---|---|---|
Things To Know About Local Load Balancing | 3,000 | |
PHP Error Handling: Never Say die() Again | 2,748 | |
Thread_Statistics and High Memory Usage | 562 | |
Where Do I Put ProxySQL? | 625 |