[mysql] sql code snippets #sql, #mysql, #snippet

sql.md

Koza Queries

-- check error ratio of payments
SELECT
	SUM(IF(success = 1, 1, 0)) AS success,
	SUM(IF(success = 0, 1, 0)) AS error
FROM payment_history
WHERE created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
AND fk_payment_data is null

import data dump to database

zcat /path/to/file.sql.gz | mysql -u 'root' -p your_database

allow remote access on mysql server

comment this line in my.conf to allow any access from any IPs

bind-address = 127.0.0.1

# change to
#bind-address = 127.0.0.1

set super user privilege to root

login on mysql server via cli mysql -u root -p

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
mysql> revoke all privileges, grant option from 'root'@'%';

this will set the super user permissions to user root on all databases

dump specific table data into compressed zip

#!/bin/bash

mysqldump -h localhost -uroot -proot --extended-insert --quick --no-create-info ubup voucher_template_validator | gzip > voucher_template_validator.sql.gz

import compressed sql dump into specific table

gzip -dc < voucher_template_validator.sql.gz | mysql -h 127.0.0.1 -uroot -proot ubup

group concate the selected Ids to a string

select group_concat(id) as order_list
from `order`
where tracking_id is null and customer_id = 54960 and order_status = 'PAID';

allow remote access for root on all databases

-- remove all existing user and grant all privileges
-- allow remote access for root on all databases
DELETE FROM mysql.user ;
CREATE USER 'root'@'%' IDENTIFIED BY '${MYSQL_ROOT_PASSWORD}' ;
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION ;
DROP DATABASE IF EXISTS test ;
FLUSH PRIVILEGES ;