Show Menu
Cheatography

mysql

Data Types

CHAR
String (0->255)
VARCHAR
String (0->255)
TINYTEXT
String (0->255)
TEXT
String (0->65535)
BLOB
String (0->65535)
MEDIUMTEXT
String (0->16­777245)
MEDIUMBLOB
String (0->16­777245)
LONGTEXT
String (0->42­949­67295)
LONGBLOB
String (0->42­949­67295)
TINYINT X
Integer (-128-­>127)
SMALLINT X
Integer (-3276­8->­32767)
MEDIUMINT X
Integer (-8388­608­->8­388607)
INT X
Integer (-2147­483­648­->2­147­483647)
BIGINT X
Integer (-9223­372­036­854­775­808­->9­223­372­036­854­775807
FLOAT
Decimal (precise 23 digits)
DOUBLE
Decimal (24->53 digits)
DECIMAL
"­DOU­BLE­" stored as String
DATE
YYYY-MM-DD
DATETIME
YYYY-MM-DD HH:MM:SS
TIMESTAMP
YYYYMM­DDH­HMMSS
TIME
HH:MM:SS
ENUM
One of the preset options
SET
Selection of preset options
Integers (marked with an X) that are "­Uns­ign­ed" have the same range of values but start from 0. i.e. Unsigned TINYINT can have any value from 0->255.

Table Commands

CREATE TABLE table_name (creat­e_c­lause1, create­_cl­aus­e2,...)
Creates a table with columns as indicated in the create clause
create­_clause
Column name followed by column type, followed by modifiers.
DROP TABLE table_name
Removes table from the database perman­ently
ALTER TABLE table_name ADD (creat­e_c­lause1, create­_cl­aus­e2,...)
Add the listed columns to the table
ALTER TABLE table_name DROP column­_name
drop the listed column from the table
ALTER TABLE table_name MODIFY create­_clause
Changes the type or modifies to a column. Using MODIFY means that the column keeps the same name even though its type is altered.
ALTER TABLE table_name CHANGE column­_name create­_clause
Changes the name and type or modifiers of a column. Using change (instead of modify) implies that the column is getting a new name.
**ALTER TABLE table_name ADD INDEX [index­_name] (colum­n_n­ame1, ...)
adding an index to a table
CREATE INDEX index_name ON table_name (colum­n_n­ame1, column­_na­me2­,...)
Adds an index to this table, based on the listed columns. Nate that the order of the columns is important, because additional indexes are created from all subsets of the listed columns reading from left to right.
 

General Commands

USE databa­se_name
Change to this database. You need to change to some database when you first connect to MySQL
SHOW DATABASES
Lists all MySQL databases on the system
SHOW TABLES [FROM databa­se_­name]
Lists all tables from the current database or from the database given in the command
Describe table_name
 
SHOW FIELDS FROM table_name
 
SHOW COLUMNS FROM table_name
These commands all give a list of all columns (fields) from the given table, along with column type and other info.
SHOW INDEX FROM table_name
Lists all indexes from this table
SET PASSWO­RD=­PAS­SWO­RD(­'ne­w_p­ass­word')
Allows the user to set his/her own password

Create­_Clause Modifiers

AUTO_I­NCR­EMENT
Each data record is assigned the next sequential number when it is given a NULL value
PRIMAR­Y_KEY
This must be unique, one column must be primary key
NOT NULL
No NULL values are allowed in this column
DEFAULT value
If a NULL value is used in the data for this column, the default value will be entered

Data Commands

INSERT INTO table_name VALUES (value1, value2­,...)
Insert a complete row of data, giving a value (or NULL) for every column in the proper order.
INSERT INTO table_name (colum­n_n­ame1, column­_na­me2­,...) VALUES (value1, value2­,...)
Insert values into certain columns
INSERT INTO table_name SET column­_na­me1­=va­lue1, column­_na­me2­=va­lue­2,...
Insert data into the listed columns only. Alternate forms, with the SET form showing column assignment with explicitly
INSERT INTO table_name (colum­n_n­ame1, column­_na­me2­,...) SELECT list_o­f_f­iel­ds_­fro­m_a­not­her­_table FROM other_­tab­le_name WHERE where_­clause
Insets the data resulting from a SELECT statement into the listed columns. Be sure the number of items taken from the old table match the number of columns they are put into.
DELETE FROM table_name WHERE where_­clause
Deletes rows that meet the conditions of the where_­clause. If the WHERE statement is omitted, the table is emptied, although its structure remains intact.
UPDATE table_name SET column­_na­me1­=va­lue1, column­_na­me2­=va­lue­2,... [WHERE where_­clause]
alter the data within a column based on the conditions in the where_­columns
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          MySQL Cheat Sheet
          Essential MySQL Cheat Sheet
          PHP Syntax for beginners Cheat Sheet