MySQL DataTypes   [top]
MySQL has several data types, most notably:

• Numeric Values
• String Values
• Date and Time Values

• 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 PHP 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

• 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 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
• 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.

Basic SQL Commands   [top]
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

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

So, for example, in our recording table:

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

Actually, when you have a table that has an ID as a primary key, you don't have to specify it when you enter a new entry. It will automatically enter the proper value for you, so:

INSERT INTO recording (title)
VALUES ('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')

MySQL also gives you the ability to enter multiple records at the same time. The syntax for this is:

INSERT INTO recording (artist, title)
VALUES ('AC/DC','Powerage'), ('AC/DC','High Voltage'), ('AC/DC','Highway to Hell');

INSERT INTO recording (artist, title) VALUES ('AC/DC','Powerage');
INSERT INTO recording (artist, title) VALUES ('AC/DC','High Voltage');
INSERT INTO recording (artist, title) VALUES ('AC/DC','Highway to Hell');

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'

or

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"

If this is our last incarnation of the "recording" table above, this would return ID and title. You can also retrieve built-in MySQL functions like NOW() which returns the current system datetime:

SELECT NOW()

Here is a list of comparison operators that can be used in the WHERE clause.

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

Related Resources   [top]