I am by no means an expert SQL programmer. Having said that, I’ve been using MySQL for a very long time. This is a collection of simple but useful SQL queries and MySQL configuration options. Also, see my Useful Bash One-Liners and the Regex Collection.

[accordion] [spoiler title=”Configuration Options” style=”fancy”]

MySQL query cache

[/spoiler] [spoiler title=”Run-time Options” style=”fancy”]

Non-locking queries

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SELECT * FROM some_table; COMMIT;

[/spoiler] [spoiler title=”Import and Export” style=”fancy”]

Dump the data to STDOUT

mysqldump --default-character-set=utf8 --max_allowed_packet=32M --opt -hHOST -uUSER -pPASSWORD DATABASE > database.sql

Import the Data from file

mysql --default-character-set=utf8 --max_allowed_packet=32M -hHOST -uUSER -pPASSWORD DATABASE < database.sql

MySQL table to CSV

mysql -udbuser -pdbpasswd << EOF
SELECT * INTO OUTFILE '/tmp/database1_table1.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM database1.table1;
EOF

Load local data file into table

mysql --local-infile=1 -u -p
LOAD DATA LOCAL INFILE '/wordlists/incoming/list2' INTO TABLE biglist 
FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' 
(word1, word2);

Exporting spreadsheet data to MySQL

[/spoiler] [spoiler title=”Database Maintenance” style=”fancy”]

Evade “myisamchk: error: myisam_sort_buffer_size is too small”

myisamchk --sort_buffer_size=2G -r -f table_name.MYI

Correct MySQL GRANT syntax

mysql -uroot -ppassword
CREATE DATABASE my_drug_deals ;
GRANT ALL PRIVILEGES ON my_drug_deals.* to root@'%' IDENTIFIED BY 'password' WITH GRANT OPTION ;
quit

A Better Mysqlcheck Script

MySQL mysqlhotcopy Script

MySQL mysqlhotcopy Script

Creating table indexes in MySQL

[/spoiler] [spoiler title=”Database Copy and Backup” style=”fancy”]

Duplicate a table

This will copy structure, content, indexes, and triggers from tablename to tablename_YYYYMMDD_HHMMSS

SET @ts=DATE_FORMAT(NOW(),'%Y%m%d_%H%i%s');
SET @c = CONCAT('CREATE TABLE `tablename_',@ts, '` LIKE `tablename');
PREPARE stmt from @c;EXECUTE stmt;DEALLOCATE PREPARE stmt;
SET @c = CONCAT('INSERT `tablename_',@ts, '` SELECT * FROM `tablename');
PREPARE stmt from @c;EXECUTE stmt;DEALLOCATE PREPARE stmt;

Same as the previous example, but executed from shell:

db_user=mysql; db_pass=yourpass; db_name=dbname; db_host=dbhost; tbl_name=oldtable 
MYSQL="/usr/bin/mysql --batch --skip-column-names --max_allowed_packet=100M -u${db_user} -p${db_pass} ${db_name} -h${db_host} -e"
ts=$(date +'%Y%m%d_%H%M%S')
tbl_name_new=${tbl_name}_${ts}
${MYSQL} "CREATE TABLE ${tbl_name_new} LIKE ${tbl_name};"
${MYSQL} ${tbl_name_new} SELECT * FROM ${tbl_name};"

[/spoiler] [spoiler title=”SELECT” style=”fancy”]

Select distinct with count

SELECT column1, COUNT(*) AS column1_count FROM some_table GROUP BY column1 ORDER BY column1_count DESC

Select via shell script when column names have spaces

SELECT `Column One`,`Column Two`,`Column Three`

Select and replace spaces in values with underscores

SELECT REPLACE(column1, ' ', '_'),REPLACE(column2, ' ', '_')

Find and Replace in MySQL

update TABLE set COLUMN = replace(COLUMN, 'OLD_VALUE', 'NEW_VALUE');

Using REGEX

SELECT `Col 1`,`Col 2`
FROM database1.table1 
WHERE `Col 3` = 'Something'
AND `Col 4` REGEXP '[pu]l.*'
ORDER BY `Col 1`;

Select records from 30 days ago or older

SELECT * FROM d1.t1 WHERE col1 >= DATE_SUB(NOW(), INTERVAL 30 DAY);

Select records from the past day

select * from d1.t1
WHERE username LIKE 'thisguy' 
AND logintime > DATE_SUB(NOW(), INTERVAL 1 DAY) 
AND logintime < DATE_SUB(NOW(), INTERVAL 0 DAY) 
ORDER BY username,logintime DESC;

Select records between two timestamps

SELECT col1,col2,col3,col4 FROM d1.t1
WHERE TimeStamp BETWEEN '2016-07-01 14:30:00' AND '2016-07-12 16:30:00'
AND col1 LIKE '%something.com%'
ORDER BY col1 DESC
LIMIT 1000;

Select records from the past 10 minutes

...WHERE time_db BETWEEN NOW() - INTERVAL 10 MINUTE AND NOW()

Select records from the past year

...WHERE time_db BETWEEN NOW() - INTERVAL 365 DAY AND NOW()

Select records after specific date

...WHERE time_db >= '2016-04-26'

Select with comma-separated output

SELECT CONCAT_WS(',',col1,MAX(col2)) FROM d1.t1 GROUP BY col1 ORDER BY col2 DESC;

Sort by IP

...ORDER BY host_name, INET_ATON(remote_ip), remote_port ;

Select with lookup table (INNER JOIN)

SELECT DISTINCT col1 AS hostname, col2 AS ipaddr, col3, col4
FROM t1
INNER JOIN t2 ON col3 = t2.col5
WHERE col1  LIKE 'this%'
AND col2 LIKE 'that%'
AND col3 < 9999
AND ( col4 NOT LIKE '0.0.0.0' AND col4 NOT LIKE '127.0.0.1' AND col4 NOT LIKE col2)
ORDER BY hostname, INET_ATON(ipaddr);

MySQL Global Search and Replace Script

Parallel MySQL Queries

Passing MySQL Commands from Shell Script

[/spoiler] [spoiler title=”DELETE” style=”fancy”]

Delete rows with NULL values

DELETE FROM d1.t1 WHERE col1 IS NULL;

[/spoiler] [spoiler title=”GROUP BY” style=”fancy”]

Group results into 10-minute intervals

SELECT DISTINCT MIN(date_db) AS first_event,MAX(date_db) AS last_event,
TIMEDIFF(MAX(date_db),MIN(date_db)) AS event_duration,
COUNT(message_compact) as message_compact_count,facility,message_clean
FROM sysinfo.logdig
WHERE hostid = '600a85c5'
AND facility NOT REGEXP '(auditd|in\.rexecd|xinetd|rlogind|nscd|rhsmd|syslog|puppet\-agent|ntpd)'
GROUP BY message_compact_md5
HAVING ROUND(TIME_TO_SEC(TIMEDIFF(MAX(date_db),MIN(date_db)))/60) > 10
ORDER BY event_duration DESC;

[/spoiler] [spoiler title=”Statistical Functions” style=”fancy”]

Arithmetic mean

SELECT SUM( x ) / COUNT( x ) FROM t1

Weighted average

SELECT SUM( x * w ) / SUM( w ) FROM t1

Harmonic average

SELECT COUNT( x ) / SUM( 1 / x ) FROM t1

Geometric mean

SELECT EXP( SUM( LOG( x ) ) / COUNT( x ) ) FROM t1

Midrange

SELECT( MAX( x ) + MIN( x ) ) / 2 FROM t1

Median

SELECT median( x ) FROM t1

Most popular value – Mode

SELECT x, COUNT( * )
FROM t1
GROUP BY x
ORDER BY COUNT( * ) DESC
LIMIT 1;

Calculating deviations with MySQL

STDDEV_POP( x ) = STD( x ) = STDDEV( x )

VAR_POP( x ) = VARIANCE( x )

VAR_POP( x ) = STDDEV_POP( x ) * STDDEV_POP( x )

VAR_POP( x ) = VAR_SAMP( x ) *( COUNT( x ) - 1 ) / COUNT( x )

VAR_POP( x ) = SUM( x * x ) / COUNT( x ) - AVG( x ) * AVG( x )

VAR_SAMP( x ) = STDDEV_SAMP( x ) * STDDEV_SAMP( x )

VAR_SAMP( x ) = VAR_POP( x ) /( COUNT( x ) - 1 ) * COUNT( x )

Covariance

COVAR_POP(x, y):
SELECT( SUM( x * y ) - SUM( x ) * SUM( y ) / COUNT( x ) ) / COUNT( x ) FROM t1

COVAR_SAMP(x, y):
SELECT( SUM( x * y ) - SUM( x ) * SUM( y ) / COUNT( x ) ) /( COUNT( x ) - 1 ) FROM t1

Pearson Correlation Coefficient

SELECT COVARIANCE( x, y ) / ( STDDEV( x ) * STDDEV( y ) ) FROM t1

Higher statistical moments

SELECT SKEWNESS( x ) FROM t1
SELECT KURTOSIS( x ) FROM t1

Row Ranking

SELECT @x:= @x + 1 AS rank, title
FROM t1
JOIN (
   SELECT @x:= 0
)X
ORDER BY weight;

SELECT row_number() AS rank, title FROM t1 ORDER BY weight;

Longtail Analysis

SELECT MAX( x ) Max_X,
   COUNT( x = 1 OR NULL ) Num_1,
   COUNT( x > 1 OR NULL ) Num_X,
   LESSPARTPCT( x, 0.5 ) Border
FROM (
   SELECT COUNT( * ) x
   FROM phrase
   GROUP BY P_ID
) tmp;

SELECT COUNT( c ) AS LESSPARTPCT
FROM(
   SELECT x, @x:= @x + x, IF(@x < @sum * 0.5, 1, NULL) AS c
   FROM t1
   JOIN(
      SELECT @x:= 0, @count:= 0, @sum:= SUM(rnum)
      FROM t1
   )x
ORDER BY x
)x

A simple ranking system

SELECT count( * ) count, lessavg( x ) less FROM t1;

if (less / count > 0.5) {

    print less / count * 100, "% are worse than average"

} else {

    print (count - less) / count * 100, "% are better than average"
}

[/spoiler] [/accordion]