snippets.txt

::General
Select *::SELECT * FROM table_name;
Select::SELECT column1, column2, columnN\nFROM table_name\nWHERE condition;
Update::UPDATE table_name\nSET column1=value, column2=value, columnN=value\nWHERE condition;
Insert into::INSERT INTO table_name (column1,column2,columnN)\nVALUES (value1,value2,valueN);
Delete::DELETE FROM table_name\nWHERE 0; -- Change this
::Database
Show databases::SHOW DATABASES;
Create database::CREATE DATABASE database_name;
Drop database::DROP DATABASE database_name;
---
Show tables::SHOW TABLES;
Show full tables::SHOW FULL TABLES;
Show tables only::SHOW FULL TABLES WHERE table_type<>'view';
::Table
Create table::CREATE TABLE table_name (\n\tid int NOT NULL AUTO_INCREMENT,\n\tcolumn2 datatype,\n\tcolumn3 datatype,\n\tcolumnN datatype,\n\n\tPRIMARY KEY (id)\n);
Drop table::DROP TABLE table_name;
Truncate table::TRUNCATE TABLE table_name;
---
Show columns::SELECT ORDINAL_POSITION AS "N", COLUMN_NAME AS "Column Name", COLUMN_TYPE AS "Datatype", COLUMN_KEY AS "Key", EXTRA\nFROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'table_name';
Add column::ALTER TABLE table_name\nADD column_name datatype;
Drop column::ALTER TABLE table_name\nDROP COLUMN column_name;
Modify column::ALTER TABLE table_name\nMODIFY COLUMN column_name datatype;
::Users
Show users::SELECT User, Host, CASE\n  WHEN Password="" THEN "NO" ELSE "YES"\nEND AS "Using password" FROM mysql.user;
Create user::CREATE USER 'username'@'localhost' IDENTIFIED BY 'user_password'; -- This user will not have any privileges. Use GRANT to assign them.
Grant privileges::GRANT INSERT, UPDATE, DELETE, ... /* For all privileges use: ALL PRIVILEGES */\nON table_name\nTO username@localhost;
Drop user::DROP USER 'username'@'localhost';
::Stored Procedures
Show procedures::SHOW PROCEDURE STATUS;
Execute procedure::CALL proc_name;
---
Create procedure::CREATE PROCEDURE proc_name()\nBEGIN\n	SELECT "world" AS "hello";\nEND;
Create procedure (params)::CREATE PROCEDURE proc_name(\n	IN param1 varchar(10),\n	IN param2 int\n)\nBEGIN\n	SELECT param1,param2;\nEND;
Drop procedure::DROP PROCEDURE proc_name;
::Views
Show views::SHOW FULL TABLES WHERE table_type='view';
---
Create view::CREATE VIEW view_name AS\nSELECT "world" AS "hello";
Drop view::DROP VIEW view_name;
::Misc
Set autoincrement::ALTER TABLE table_name AUTO_INCREMENT=100;
CASE Statement::CASE\n\tWHEN condition1 THEN result1\n\tWHEN condition2 THEN result2\n\tWHEN conditionN THEN resultN\n\tELSE result\nEND