MySQL DataTypes

MySQL has several data types, most notably:

Numeric Values
String Values
Date and Time Values

Numeric Values

For our purposes, we need only focus on the INTEGER type, the DOUBLE, and the DECIMAL

INTEGER should be used to store integral values - things such as numeric IDs, number of hits on a web page, number of times you cried while writing your Perl script from last week
DOUBLE should be used for anything that won’t be a whole number, such as the average rating a movie got (4.56 out of 5)
DECIMAL is good for storing monetary values (i.e. 3.99, 69.99). When creating a DECIMAL field, make sure you specify 9,2 in the “Length/Values” field, as this creates a number with 9 digits before the decimal and 2 after it. Thus, the max value is 999999999.99. If you need to go higher than one trillion, than specify 10,2 in the Length/Values field

String Values

For our purposes, we will focus on the VARCHAR type, the TEXT type, and the BLOB type

VARCHAR is great for storing short text values that are 255 characters or less. You also need to specify a max length. I usually use 255 for URLs and email addresses, and 128 for first and last names, city names, musical style names, etc.
TEXT is good for storing longer text entries, like blog or forum messages. It can store up to 65K worth of text
BLOB (BLOB stands for Binary Large OBject) is basically the same as TEXT but for binary data. It also can store up to 65K of data

Date and Time Values

In MySQL, there are several variables to deal with date and time.

DATE refers to the actual day of an event (i.e. 2002-04-01)
TIME refers to the time of day (23:59:59)
DATETIME lets you store both in one value, which is of format YYYY-MM-DD HH-MM-SS
TIMESTAMP is automatically set to the current system date and time whenever that row is inserted or updated. For full coverage, set the “Length/Values” field to 14 when you create a field of this type.
YEAR refers to a year value and can range from 1901 to 2155. Why? It’s only one byte long and thus its full range of values can only span 255 years.

Remember that for some data types, like DECIMAL, VARCHAR, and TIMESTAMP, you’ll need to also specify related information in the “Length/Values” field in PhpMyAdmin when you create these fields.

Basic SQL Commands

SQL stands for Structured Query Language and was developed by IBM. All relational databases understand statements made using SQL. You can do a lot with a little in SQL, and the four main commands we will focus on is INSERT, UPDATE, SELECT, and DELETE.

INSERT allows you to enter new records into your table
UPDATE allows you to modify any existing records in your table
SELECT allows you to retrieve a specific subset of existing records from your table
DELETE allows you to remove a specific subset of existing records from your table

The INSERT Statement

The insert statement is used to put new records into the table.


INSERT INTO table1(column1,..)
VALUES (expression,...)

so for example; in our recording table:


INSERT INTO recording (id, title)
VALUES (1, 'Hello, Nasty')

Note that numeric values do not need quotes to delineate them, but string values do. Also, if your string has a single quote ’ in it, you must escape it by adding another single quote:


INSERT INTO recording (title)
VALUES ('Don''t Fear The Reaper')

The UPDATE statement

The UPDATE statement is used to modify any existing records in your table. Here is the format of the UPDATE statement:


UPDATE table
SET column1=expression1, column2=expression2, ...
[WHERE "conditions"]

So, for example, in our recording table:


UPDATE recording
SET title='The White Album'
WHERE title='White Album'


UPDATE recording
SET title='OK, Computer'
WHERE id=3

The SELECT statement

The SELECT statement is used to query the database and retrieve selected data that match whatever criteria you specify. It has five main clauses, and FROM is the only required clause.

Here is the format of the SELECT statement:


SELECT [DISTINCT] column1[,column2]
FROM table1[,table2]
[WHERE "conditions"]
[GROUP BY "column-list" [ASC | DESC] ]
[HAVING "conditions]
[ORDER BY "column-list" [ASC | DESC] ]
[LIMIT [offset,] number-rows]

Often, you only need a simpler version of this:


SELECT column1
FROM table1
WHERE "conditions"

Example:


SELECT id
FROM recording
WHERE title = "Hello, Nasty"

The above statement will select all of the values in the id column from the recording table for recording whose title is “Hello, Nasty”. If you use an * for which columns you want, it acts as wildcard and gets all of the columns:


SELECT *
FROM recording
WHERE title = "Hello, Nasty"

Comparison Operators

|= |Equal| |> |Greater than| |< |Less than| |>= |Greater than or equal to| |<= |Less than or equal to| |<> or != |Not equal to| |LIKE |String comparison test|

Example:

SELECT id, title FROM recording WHERE title LIKE ‘Hello%’;

The above statement selects the ID and title of every recording that beings with ‘Hello’. In our case, it will return “Hello, Nasty” and its ID.

DISTINCT is a keywords used to obtain only the “distinct” or unique records in your query results. DISTINCT will discard the duplicate records for the columns you specified after the “SELECT” statement: For example, suppose one of our fields in recording was “release_year”, which is the year of the recording’s release date. We could say:


SELECT DISTINCT release_year 
FROM recording

which would return all of the unique release years in the recording table. Thus, if two recordings were released in the same year, that year would only show up once in our results.

The DELETE statement The DELETE statement is remove specific records from your table. Here is the format of the INSERT statement:


DELETE FROM table1
[WHERE "conditions"]

So, for example, to delete just the “Hello, Nasty” entry in our recording table, we would say:


DELETE FROM recording
WHERE title='Hello,Nasty'

If you wanted to delete everything from the recording table:


DELETE FROM recording