MYSQL Cheat Sheet – My memo Wall

To dump all data in a database

mysqldump --host="hostname" --user="username" --password="password" databasename > backup_name.sql

To reset auto-increment field in a table
```
ALTER TABLE `table name` AUTO_INCREMENT = 1
```
Note that for InnoDB, you cannot set the auto_increment value lower or equal to the highest current index.

Load CSV into selected columns:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE t1 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
(@col1,@col2,@col3,@col4) set name=@col4,id=@col2 ;

MySQL allows multiple NULLs in a column with a unique constraint. This is not necessarily true for other SQL servers.

Show all indexes on a table:

SHOW INDEX FROM table_name FROM db_name;

One way to get rid of duplicate entries in a table:

CREATE TEMPORARY TABLE IF NOT EXISTS tmpsel AS (
SELECT max(id) as mxid, column_of_interest, count(*) 
FROM table group by column_of_interest having count(*)>1);

create temporary table if not exists tmpid as (
select a.id from table a, tmpsel b where a.column_of_interest=b.column_of_interest and a.id != b.mxid);

delete from table where id in (select id from tmpid);

Or combined into one statement:

DELETE a FROM tablename as a, tablename as b 
where a.id<b.id and a.columnOfInterest = b.columnOfIntest;

To execute a serious of SQL statements in a text file such as sample_query.sql:
- mysql –user username –password
- source sample_query.sql
- This will execute the query statement inside of the file
The Performance Impact of Adding MySQL Indexes

To set a column to null

UPDATE table_name
SET column_name = NULL
WHERE column_name is not null;

Count grouped by value

SELECT DISTINCT col_name, COUNT( col_name ) 
FROM `table_name`
GROUP BY col_name

Order count group by value

select * from (select col_name, count(col_name) as cnt 
FROM `table_name` group by col_name) as tmp_table order by cnt desc;

See Create Table Statement
```
show create table TableName
```

Change Column Default Value

ALTER TABLE TableName MODIFY COLUMN ColName type DEFAULT value'

Remove Column Default Value

ALTER TABLE TableName ALTER COLUMN ColName DROP DEFAULT

To find out which index may be used by MYSQL when executing a query:

explain select id from table where created_at between "2020-12-07" and "2020-12-08";

Force use index

select * from tableName use index (index1, index2) where created_at 
between "2020-12-7" and "2020-12-08" ;

To see constraint on a table

select * from information_schema.table_constraints where table_name="tableName";

Time range half open problem:
```
SELECT * 
FROM some_table_
WHERE invoice_received_ >= '2015-06-22 18:00:00Z'
AND invoice_received_ < '2015-06-22 19:00:00Z'
;
```
This query will catch any values such as “2015-06-22 18:59:59.654321”
Notice the Z on the end of string literal which means “UTC time zone” (“Z” for “Zulu”).