mysql-cheat-sheet.md

MySql Cheat Sheet

TOC

• General
• Users, privileges
• Table structure
• Relations
• Misc

---

General

Screen Clear

system clear

Create database

CREATE DATABASE `database_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Users, privileges

Add a user

CREATE USER 'user'@'localhost' IDENTIFIED BY 'pass';

Grant privileges

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

Revoke privileges

REVOKE INSERT, UPDATE, DELETE ON database_name.* FROM 'user'@'localhost';
FLUSH PRIVILEGES;

Privileges list

• data: SELECT, INSERT, UPDATE, DELETE, FILE
• structure: CREATE, ALTER, INDEX, DROP, CREATE TEMPORARY TABLES, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE VIEW, EVENT, TRIGGER
• administration: GRANT, SUPER, PROCESS, RELOAD, SHUTDOWN, SHOW DATABASES, LOCK TABLES, REFERENCES, REPLICATION CLIENT, REPLICATION SLAVE, CREATE USER

TOC

Table structure

Create table

CREATE TABLE [IF NOT EXISTS] `table_name` (
...
) ENGINE InnoDb CHARACTER SET utf8 COLLATE utf8_general_ci;

Rename table

RENAME TABLE `database_name`.`old_table_name` TO `database_name`.`new_table_name`;

Remove table

DROP TABLE [IF EXISTS] `table_name`;

TOC

Relations

Show relations

SHOW CREATE TABLE `table_name` \G

Add relation

ALTER TABLE `table_name` ADD CONSTRAINT `relation_name` FOREIGN KEY `index_name` (`column_name`) REFERENCES `related_table_name` (`related_column_name`) ON DELETE [RESTRICT | CASCADE | SET NULL | NO ACTION];

Remove relation

ALTER TABLE `table_name` DROP FOREIGN KEY `relation_name`;

TOC

Misc

Foreign key check

SET foreign_key_checks = 0;
SET foreign_key_checks = 1;

Reset auto increment

ALTER TABLE `table_name` AUTO_INCREMENT = 1;

Concatenate (non NULL) values from a group

SELECT `movie`, GROUP_CONCAT(`time` SEPARATOR ',') AS `running_times` FROM `schedule` WHERE `movie` = 'A Beautiful Mind' GROUP BY `movie` ORDER BY NULL

Search & Replace Data

UPDATE `table_name` SET `field_name` = REPLACE(`field_name`, 'string_to_find', 'string_to_replace');

Troubleshooting

SHOW ENGINE INNODB STATUS

Disable cache

SELECT SQL_NO_CACHE ...; (per query)

SET SESSION query_cache_type=0/1/2; (per session, use 0 to turn OFF, 1 to turn ON)

SHOW VARIABLES LIKE 'query_cache_size';
SET GLOBAL query_cache_size=0 (globally)
SET GLOBAL query_cache_size=<old_value>

TOC