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->16777245) |
MEDIUMBLOB |
String (0->16777245) |
LONGTEXT |
String (0->4294967295) |
LONGBLOB |
String (0->4294967295) |
TINYINT X |
Integer (-128->127) |
SMALLINT X |
Integer (-32768->32767) |
MEDIUMINT X |
Integer (-8388608->8388607) |
INT X |
Integer (-2147483648->2147483647) |
BIGINT X |
Integer (-9223372036854775808->9223372036854775807 |
FLOAT |
Decimal (precise 23 digits) |
DOUBLE |
Decimal (24->53 digits) |
DECIMAL |
"DOUBLE" stored as String |
DATE |
YYYY-MM-DD |
DATETIME |
YYYY-MM-DD HH:MM:SS |
TIMESTAMP |
YYYYMMDDHHMMSS |
TIME |
HH:MM:SS |
ENUM |
One of the preset options |
SET |
Selection of preset options |
Integers (marked with an X) that are "Unsigned" 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 (create_clause1, create_clause2,...) |
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 permanently |
ALTER TABLE table_name ADD (create_clause1, create_clause2,...) |
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] (column_name1, ...) |
adding an index to a table |
CREATE INDEX index_name ON table_name (column_name1, column_name2,...) |
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 database_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 database_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 PASSWORD=PASSWORD('new_password') |
Allows the user to set his/her own password |
Create_Clause Modifiers
AUTO_INCREMENT |
Each data record is assigned the next sequential number when it is given a NULL value |
PRIMARY_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 (column_name1, column_name2,...) VALUES (value1, value2,...) |
Insert values into certain columns |
INSERT INTO table_name SET column_name1=value1, column_name2=value2,... |
Insert data into the listed columns only. Alternate forms, with the SET form showing column assignment with explicitly |
INSERT INTO table_name (column_name1, column_name2,...) SELECT list_of_fields_from_another_table FROM other_table_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_name1=value1, column_name2=value2,... [WHERE where_clause] |
alter the data within a column based on the conditions in the where_columns |
|
Created By
Metadata
Favourited By
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets