MySQL cheat sheet

mysql-cheat-sheet.sql.md

Select

CSV file

SELECT * FROM `tbl`
INTO OUTFILE '/tmp/out.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Show

Indexes from a table

SHOW INDEX FROM `tbl`;

Constraints on tables

USE INFORMATION_SCHEMA;
SELECT 
  TABLE_NAME, 
  COLUMN_NAME, 
  CONSTRAINT_NAME, 
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME 
FROM KEY_COLUMN_USAGE;

Views

SHOW FULL TABLES IN `database` WHERE TABLE_TYPE LIKE 'VIEW';

Users

SELECT User FROM mysql.user;

Grants

SHOW GRANTS;

Grants for specific user

SHOW GRANTS FOR 'user-name'@'localhost';

Grant permission

GRANT SELECT ON `db`.`table` TO user@host IDENTIFIED BY 'password';

Add

Primary key

ALTER TABLE `tbl` ADD PRIMARY KEY(`col`);

Constraint / Foreign key

ALTER TABLE `tbl_1` ADD CONSTRAINT `fk` FOREIGN KEY(`col`) REFERENCES `tbl_2`(`col`) ON DELETE CASCADE;

Column

ALTER TABLE `tbl` ADD COLUMN `col_name` int not null DEFAULT 1 AFTER `col`;

Create

Index

CREATE INDEX `name` ON `tbl` (`col`) USING BTREE;

Unique index

CREATE UNIQUE INDEX `name` ON `tbl` (`col_1`, `col_2`);

User

CREATE USER `user-name` IDENTIFIED BY PASSWORD 'the-password';

Table

CREATE TABLE `tbl` (`col1` int(11) NOT NULL AUTO_INCREMENT, `col2` varchar(100), PRIMARY KEY (`col1`));

Rename

Table

RENAME TABLE `tbl` TO `new_tbl_name`;

Column

ALTER TABLE `tbl` CHANGE `col_name` `new_col_name` BIGINT(20) NOT NULL;

Drop

Key

ALTER TABLE `tbl` DROP KEY `key`;

Foreign key

ALTER TABLE `tbl` DROP FOREIGN KEY `fk`;

Index

ALTER TABLE `tbl` DROP INDEX `index`;

Column

ALTER TABLE `tbl` DROP COLUMN `col_name`;

INSERT

Insert, update on duplicate key

INSERT INTO `tbl` SET `col1`='foo', `col2`='bar' ON DUPLICATE KEY UPDATE `col2`=VALUES(`col2`)';