MySQL

MySQL is an open-source, relational database used for storing and manipulating data. Tables are the data structures for MySQL. Tables have rows and columns. Each database contains a collection of tables. Data Manipulation Language (DML), which permits queries, and Data Definition Language (DDL), which permits creation and deletion of databases and tables, make up MySQL. There are fixed schemas in MySQL.

Data Definition Language (DDL)

CREATE - creates a database or a table
ALTER - modifies a table or a database
DROP - deletes a table or a database

Data Manipulation Language (DML)

SELECT - extracts data and returns a result-table
UPDATE - updates a data in a table
DELETE - deletes a row (data) into a table
INSERT INTO - inserts a row (data) into a table

MySQL Quick Reference

Data Manipulation Language (DML), creation, alteration, and deletion of databases and tables

CREATE DATABASE nameOfDatabase;
-This creates a database called nameOfDatabase. Replace "nameOfDatabase" with whatever name you would like to called your database.
USE nameOfDatabase;
-"USE" specifies the database you are going to use.

CREATE TABLE nameOfTable (nameOfColumn1 dataType1, nameOfColumn2 dataType2, nameOfColumn3 dataType3 ...);
-This creates a table and assigns data types to your columns. Replace nameOfTable with the name of your table. Replace nameOfColumn1, nameOfColumn2, and so on with the names of your columns. Replace datatype1, datatype2, and so on with the data types listed in figure 1.
DESCRIBE nameOfTable;
-Use this to see the data types of your columns from your table. Replace "nameOfTable" with the name of your table.

Figure 1: Some Common Data Types for columns
Data Type	Description
VARCHAR(sizeNumber)	Holds a variable length of characters (Stores a variable size String). Specify the numbers of characters in "sizeNumber". Maximum number for size is 255.
TEXT	Holds a string with maximum length of 65,535 characters
INT(maxDigits)	Holds integer values. Specify the maximum number of digits allowed in "maxDigits".
DOUBLE(maxDigits, numberOfDecimalDigits)	Holds floating decimal point. Specify the maximum number of digits allowed in "maxDigits". Specify the number of decimal digits allowed in "numberOfDecimalDigits".
DATE()	Holds a date in the format of YYYY-MM-DD

ALTER TABLE nameOfTable ADD nameOfColumn datatype;
-Add a column to an existing table. Specify the name of your table in "nameOfTable" and the name of column you would like to add in "nameOfColumn" along with its data type in "datatype".
ALTER TABLE nameOfTABLE DROP COLUMN nameOfColumn;
-Delete a column in a table.
ALTER TABLE nameOfTable CHANGE COLUMN nameOfColumn newNameColumn newDataType;
-Modify the data type and name of an existing column.
DROP TABLE nameOfTable;
-Deletes a table. Replace "nameOfTable" with the name of table you want to remove.
DROP DATABASE nameOfDatabase;
-Deletes a database. Replace "nameOfDatabase" with the name of database you want to remove.

Example (do not type anything after the semicolon; they are just helpful comments):
CREATE DATABASE practice; //create a database called practice
USE practice; //database practice is in use
CREATE TABLE items (id INT, itemName VARCHAR(50), quantity INT, price DOUBLE(10,2)); //Create table called "items"
DESCRIBE items; //indicates to you the types of your columns

Structure of Table Columns
id(INT)	itemName(VARCHAR(50))	quantity (INT)	price (DOUBLE(10,2))

ALTER TABLE items ADD tags VARCHAR(255); //add a column to existing table
DESCRIBE items; //check structure of your table

Using ALTER to Add a Column
id(INT)	itemName(VARCHAR(50))	quantity (INT)	price (DOUBLE(10,2))	tags (VARCHAR(255))

ALTER TABLE items CHANGE COLUMN tags hello TEXT; //change the data type of column "tags" to TEXT. And change the column name to "hello".
DESCRIBE items; //check the structure of table

Using ALTER to Change a Column Info
id(INT)	itemName(VARCHAR(50))	quantity (INT)	price (DOUBLE(10,2))	hello (TEXT)

ALTER TABLE items DROP COLUMN hello; //Drop column
Describe items; //check the structure of table

Using ALTER to Drop a Column
id(INT)	itemName(VARCHAR(50))	quantity (INT)	price (DOUBLE(10,2))

Data Manipulation Language (DML)

SELECT nameOfColumn1, nameOfColumn2, ... FROM nameOfTable;
-Extracts data from the specified columns and returns a result table with all the rows with the specified columns.
SELECT * FROM nameOfTable;
-Returns a result-table exactly like "nameOfTable" table. * is a shorthand for all columns. Also, this is used to check what your table looks like.
INSERT INTO nameOfTable(nameOfColumn1, nameOfColumn2, ... ) VALUES (value1, value2, ...);
-Inserts a row into table "nameOfTable". Data Type of columns must match up with the data type of values. -Also parameters must match up. For example nameOfColumn1 corresponds to value1, nameOfColumn2 corresponds to value2, and so on. -Unspecified columns will have null values. String values must be in quotes 'value1'.
INSERT INTO nameOfTable VALUES (value1, value2, ... );
-Another way to insert a row into a table. This assumes you are adding the proper data values for each columns. -For example value1 corresponds to column1, value2 corresponds to column2, and so on. And you are specifying every values for each columns.

Example: using SELECT and INSERT INTO. (Use the database in previous example)
If you have not created a database from the previous example, here are the commands:
CREATE DATABASE practice; //create a database called practice
USE practice; //specify database in current use
CREATE TABLE items(id INT, itemName VARCHAR(50), quantity INT, price DOUBLE(10,2)); //create a table in practice database

INSERT INTO items VALUES(1, 'apple', 4, 1.25); //add a row to table
SELECT * FROM items; //check what your table looks like

Results of SELECT *
id	itemName	quantity	price
1	apple	4	1.25

Let's insert some more rows.
INSERT INTO items (id, itemName, price) VALUES (5, 'orange', .75); //column quantity will be null for this particular row
INSERT INTO items VALUES (10, 'cookie',2, .5); //insert another row
INSERT INTO items VALUES (4, 'apple', 3, 1); //insert row
INSERT INTO items VALUES (2, 'candy', 1, 1.50); //insert row
SELECT * FROM items; //check table

Results of SELECT *
id	itemName	quantity	price
1	apple	4	1.25
5	orange	NULL	0.75
10	cookie	2	0.50
4	apple	3	1.00
2	candy	1	1.50

SELECT itemName, price FROM items; //return result table with columns itemName and price from each row.

Results of SELECT items, price
itemName	price
apple	1.25
orange	0.75
cookie	0.50
apple	1.00
candy	1.50

WHERE clause - specifies a criterion.
SELECT columnName(s) FROM nameOfTable WHERE nameOfColumn operator value;
-This extracts records that fulfill a specified criterion.
-You can also use the AND operator or the OR operator to specify more conditions.
SELECT columnName(s) FROM namoOfTable WHERE nameOfColumn operator value AND nameOfColumn operator value;
-AND operator displays records if both the first condition and the second condition are true.
SELECT columnName(s) FROM namoOfTable WHERE nameOfColumn operator value OR nameOfColumn operator value;
-OR operator displays records if either the first condition or the second condition is true.

Operators for WHERE Clause
operator	description
=	equal
<>	not equal
>	greater than
<	less than
>=	greater than or equal
<=	less than or equal
BETWEEN	between an inclusive range
LIKE	search for a pattern
IN	search for exact values specified in a set of values

Examples of WHERE Clause
SELECT * FROM items WHERE price > 1.20; //return rows that have price greater than 1.20

Table items
id	itemName	quantity	price
1	apple	4	1.25
5	orange	NULL	0.75
10	cookie	2	0.50
4	apple	3	1.00
2	candy	1	1.50

WHERE price > 1.20
id	itemName	quantity	price
1	apple	4	1.25
2	candy	1	1.50

SELECT id, itemName FROM items WHERE itemName = 'apple'; //Return rows with itemName set to "apple". NOTE: string value must be quoted. The result table will have only the id and itemName columns as specified in the SELECT statement.

Table items
id	itemName	quantity	price
1	apple	4	1.25
5	orange	NULL	0.75
10	cookie	2	0.50
4	apple	3	1.00
2	candy	1	1.50

WHERE itemName='apple'
id	itemName
1	apple
4	apple

SELECT id, itemName FROM items WHERE itemName = 'apple' OR itemName='candy'; //Return rows with itemName set to "apple" and itemName set "candy". NOTE: string value must be quoted. The result table will have only the id and itemName columns as specified in the SELECT statement.

Table items
id	itemName	quantity	price
1	apple	4	1.25
5	orange	NULL	0.75
10	cookie	2	0.50
4	apple	3	1.00
2	candy	1	1.50

WHERE itemName='apple' OR itemName='candy'
id	itemName
1	apple
4	apple
2	candy

SELECT * FROM items WHERE price <= 1.00 AND id > 4;

Table items
id	itemName	quantity	price
1	apple	4	1.25
5	orange	NULL	0.75
10	cookie	2	0.50
4	apple	3	1.00
2	candy	1	1.50

WHERE price <= 1.00 AND id > 4
id	itemName	quantity	price
5	orange	NULL	0.75
10	cookie	2	0.50

UPDATE nameOfTable SET column1=value1, column2=value2, ... WHERE some_column=some_value;
-Modify existing records in a table.

Example of UPDATE
-UPDATE items SET itemName='banana' , price=.40 WHERE id=1; //modify the row that has an id of 1

Table items
id	itemName	quantity	price
1	apple	4	1.25
5	orange	NULL	0.75
10	cookie	2	0.50
4	apple	3	1.00
2	candy	1	1.50

Updated Table items
id	itemName	quantity	price
1	banana	4	0.40
5	orange	NULL	0.75
10	cookie	2	0.50
4	apple	3	1.00
2	candy	1	1.50