SQL Reference
A database can be very simple or very complex. It can be as simple text file with one value per row, it can be like a spreadsheet with rows and columns of data or it can be a relational database with multiple tables of data. Here are the pieces of the database:
- A table contains a certain set of data. A database can contain multiple tables.
- Each table consists of rows. Each row is a single record of data.
- A field is the type of data held in each column such as text value like name or a number value such as the price of an item.
- Each table has a primary key column that identifies that row as unique. In the image below - the field MemberID is the primary key.
- If a table links to another table it will have a foreign key column that will match the other table's primary key. In the image below - in the PhoneNumbers table, ContactID is the primary key and MemberID is the foreign key.
- Don't forget about Normalization when designing a database either!
CHAR Data Type
- CHAR: allows you to store fix-length strings up to 255 characters.
- VARCHAR: allows you to store variable length strings up to 65,535 characters - it used to be limited to 255 as well.
Which one? The main difference between the two is how they store data - if you are storing values with a fixed length like State abbreviation or telephone number use CHAR. Otherwise use VARCHAR to save space.
Binary, TEXT and BLOB are also used to store text but you'll only need those if you are doing some weird stuff. Really big fields.
Numeric Data Types
These are used to store numbers from simple 1 byte long to integers to floats (decimals). Choose the smallest size one you can!
| Data Type | Bytes Used | Min Value(Signed) | Max Value(signed) | Min Value(Unsigned) | Max Value(Unsigned) |
|---|---|---|---|---|---|
| TINYINT | 1 | -128 | 127 | 0 | 255 |
| SMALLINT | 2 | -32768 | 32767 | 0 | 65535 |
| MEDIUMINT | 3 | -8388608 | 8388607 | 0 | 16777215 |
| INT | 4 | -2147483648 | 2147483647 | 0 | 4294967295 |
| BIGINT | 8 | -9223372036854775808 | 9223372036854775807 | 0 | 18446744073709551615 |
| FLOAT | 4 | -3.402823466E+38 | -1.175494351E-38 | 1.175494351E-38 | 3.402823466E+38 |
| DOUBLE | 8 | -1.7976931348623157E+ 308 | -2.2250738585072014E- 308 | 0 | 1.7976931348623157E+ 308 |
Which one?
- If you are in doubt, just use the generic INT.
- If you are creating a primary key or a field that will be automatically incremented and you think that a lot of people will be using this database use BIGINT.
- If you know how big the field will be - say you are creating a field to store a person's age - then you can use SMALLINT.
- You should always use the smallest that you can!
MySQL Boolean data type
MySQL does not have the built-in BOOLEAN or BOOL data type. To represent Boolean values, MySQL uses the smallest integer type which is TINYINT(1).
So, to make a booleanfield - make a field of type TINYINT with a length of 1
DATE and TIME types
These are used to store dates, times and all other fun timely things. Here
| Data Type | Description | Display Format |
|---|---|---|
| DATETIME | Use when you need values containing both date and time information. | YYYY-MM-DD HH:MM:SS |
| DATE | Use when you need only date information. | YYYY-MM-DD |
| TIMESTAMP | This is very useful as MySQL will set the value for you such as whenever a row is added or modified. Values are converted from the current time zone to UTC while storing and converted back from UTC to the current time zone when retrieved. | YYYY-MM-DD HH:MM:SS |
| TIME | Use when you just need the time | A time value in hh:mm:ss format |
| YEAR | Use when you just need the year. | A year value in CCYY or YY format |
There are two basic parts that are required to make this statement work: SELECT and FROM. They have to be in that order as well.
- SELECT - which fields or columns to grab
- FROM - which table the columns belong to
The Basic SELECT Statement
The basic syntax is as follows:
SELECT * FROM table_name;
Using Columns
Instead of returning all the fields in a database, which can be slow if your database is huge - you should usually specify which fields you want.
The basic syntax is as follows:
SELECT column_name1, column_name2, ... FROM table_name;
Let's say you want to see first name and email address. You can list out which columns you want returned separated by commas. So typing this:
SELECT first_name, email FROM member
View Full Topic
The WHERE clause is used to filter data so that we can retrieve only those records which meet a certain condition.
SELECT column1, column2,... FROM table_name WHERE condition;
Basic WHERE Clause
To find all the members who are taking Spanish.
SELECT * FROM member WHERE language ='Spanish';
Text Fields vs Numbers
Do you notice the quotes in the WHERE clause we just used? When you compare to a text value, you put single or double quotes around the text value or string. You do not need to do this when comparing a field value that stores a number.
To find all the members who are 16. We would use this:
SELECT * FROM member WHERE age = 16;
Want to be able to sort your data by a particular field or multiple fields? Then the ORDER BY keyword is your tool. It allows you to sort by one or more columns in ascending or descending order.
SELECT column1, column2,... FROM table_name ORDER BY condition;
Simple Sort Example
Let's say we want to sort the members table by first name. You would use this:
SELECT * FROM member ORDER BY first_name;
Reversing Your Sort
The default is to sort in ascending order - meaning A-Z, 0-10, or oldest date to newest date. But, if you want to sort in descending order - useful for dates to see the newest items first - you would add the DESC keyword on:
SELECT * FROM member ORDER BY first_name DESC;
To Sort By Multiple Rows
SELECT * FROM member ORDER BY language, first_name;
SELECT * FROM member ORDER BY language ASC, first_name DESC;
SELECT DISTINCT
This allows you to select only records from the table that have distinct or unique values.
The basic syntax is as follows:
SELECT DISTINCT column1, column2,... FROM table_name;
For example, if you want to find out which languages students are taking but you only want to see each language returned once, you would use this:
SELECT DISTINCT language FROM member;
Selecting Certain Number of Records
If you wanted to pick the top 2 records in our members table you would use this:
SELECT * FROM member LIMIT 2;
You can also add a WHERE clause - here we will pick the top 2 members who take Chinese:
SELECT * FROM member
WHERE language = "Chinese" LIMIT 2;
MINReturns the smallest value of the selected field.
SELECT MIN(field_name) FROM table_name;
Example
SELECT MIN(age) FROM member;
MAX: Returns the largest value of the selected field.
SELECT MAX(field_name) FROM table_name;
Example
SELECT MAX(age) FROM member;
COUNTReturns number of rows that match criteria specified by the WHERE statement.
SELECT COUNT(field_name) FROM table_name) WHERE condition;
Example
SELECT COUNT(*) FROM member; SELECT COUNT(*) FROM member WHERE language="Spanish";
AVG: Returns the average value of the numeric column.
SELECT AVG(field_name) FROM table_name WHERE condition;
Example
SELECT AVG(age) FROM member; SELECT AVG(age) FROM member WHERE language="Spanish";
SUM: Returns the sum of a numeric column.
SELECT SUM(field_name) FROM table_name WHERE condition;
Example
SELECT SUM(age) FROM member; SELECT SUM(age) FROM member WHERE language="Spanish";
LIKE is used in WHERE clause to search for a partial match of a string to a TEXT or VARCHAR field.
SELECT column1, column2,... FROM table_name WHERE columnx LIKE pattern;
By itself LIKE is moderately useful but when used with wildcards they are very useful! A wildcard character can be substituted in a WHERE clause to stand for any other character or multiple characters.
Two basic wildcard characters:
- % The percent sign represents 0, 1 or multiple characters
- _ The underscore to match a single character only.
Examples:
- Finds the member whose last name starts with "f":
SELECT * FROM members WHERE LastName LIKE 'f%';
- Finds the member whose last name ends with "n":
SELECT * FROM members WHERE LastName LIKE '%h';
- Finds the member whose last name contains "on":
SELECT * FROM members WHERE LastName LIKE '%on%';
- Finds the member where the second letter of the first name "a":
SELECT * FROM members WHERE FirstName LIKE '_a%';
- Find the member whose first name starts with 'e' but is at least four characters long:
SELECT * FROM members WHERE FirstName LIKE 'e____%';
IN or NOT IN
Match a field to multiple values - can search for records that meet more than one value without the OR clause.
IN Basic Syntax
SELECT column1, column2,... FROM table_name WHERE column_name IN (value1, value2,...);
NOT IN Basic Syntax
You can also search for any values that do not match multiple values.
SELECT column1, column2,... FROM table_name WHERE column_name NOT IN (value1, value2, ....);
This would return all the members who take either Chinese or Frenchs:
SELECT * FROM members WHERE Language IN ('Chinese', 'French');
This would return all the members who do not take Chinese or French
SELECT * FROM members WHERE Language NOT IN ('Chinese', 'French');
BETWEEN
This is similar to the IN operator but allows you to select records where the value of the given field is a set range - it can be numbers, texts or dates. It works like greater then or equal and not just greater then. Which means it includes the beginning and ending values.
BETWEEN Basic Syntax
SELECT column1, column2,... FROM table_name WHERE column_name BETWEEN value1 AND value2;
Return all members whose first names are alphabetically between Fred and Matilda.
SELECT FirstName FROM members WHERE FirstName BETWEEN 'Fred' AND 'Matilda';
Return all the members who have a member ID between and including 3 and 5.
SELECT MemberID, FirstName, LastName FROM members WHERE MemberID BETWEEN 3 and 5
NOT BETWEEN
You can also use the NOT operator with BETWEEN to return all the values that are not in the range. Return all members who do NOT have a member ID between and including 3 and 5.
SELECT MemberID, FirstName, LastName FROM members WHERE MemberID NOT BETWEEN 3 and 5
.
GROUP BY
GROUP BY is used in as part of the WHERE clause when you are using an aggregate function (COUNT, SUM, MAX, MIN, AVG) and with additional columns. It allows you to perform operations by groups of data.
The basic syntax is as follows:
SELECT column1, column2,... FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
Example:
SELECT AVG(age), language FROM member GROUP BY language;
Using HAVING
The HAVING clause was added so that you could apply a condition to your aggregate columns which you cannot do in the WHERE clause. Let's say you wanted to make sure each language class had at least two kids in it.
SELECT language, COUNT(*) FROM member GROUP BY language HAVING COUNT(*) > 1;
Table ALIAS
Here's the basic syntax for a table alias:
SELECT column_name1, column_name2,... FROM table_name AS alias_name;
Here is an example using the table alias:
SELECT first_name, last_name, age FROM member AS m;
Column ALIAS
And here is the basic syntax for a column alias:
SELECT column_name1 AS alias_name, column_name2,... FROM table_name;
And here is the basic syntax for a column alias:
SELECT first_name AS first, last_name AS last, age, email FROM member;
View Full Topic
A JOIN is a way to connect two or more database tables. Let's start of with the syntax of a basic JOIN.
SELECT table1.column1, table1.column2, table2.column1, table2.column2... FROM table_name1 JOIN table_name2 ON condition;
Let's take a look at what this does -
- Line 1: selects the columns to pull data from. However, you will notice a difference.
- Instead of just listing the column name, the table name is shown followed by a dot and then the column name.
- This is because you are pulling from more than one table and you need to designate which table the column is in.
- Line 2: specifies the first table you are pulling data from.
- Line 3: joins the first table to the second table and explains how that happens.
If we wanted to see a list of all members with their phone numbers, we would use this SQL:
SELECT member.first_name, member.last_name, contact.phone, contact.type FROM member JOIN contact ON member.member_id= contact.member_id;
The syntax is the same - just more tables are involved. Here's the basic syntax:
SELECT table1.column1, table1.column2, table2.column1, table3.column1... FROM table_name1 JOIN table_name2 ON (condition1) JOIN table_name3 ON (condition2);
And here's the SQL to get all students and their classes:
SELECT m.first_name, m.last_name, c.class_name FROM member m JOIN member_class_lookup l ON m.member_id= l.member_id JOIN class c ON s.class_id = c.class_id
Here's what our table looks like.
Inner Join
This returns data that has matching values in BOTH tables. This is the default.
SELECT member.first_name, member.last_name, contact.phone, contact.type FROM member INNER JOIN contact ON member.member_id= contact.member_id;
Left Join
This returns ALL the data from the Left table and only the data from the right table that matches the condition. 
So, if we wanted to get all the students - even if they do not have a phone number - we would use a LEFT JOIN.
SELECT m.first_name, m.last_name, c.phone, c.type FROM members m LEFT JOIN contact c ON m.member_id= c.member_id;
Right Join
This returns ALL the data from the Right table and only the data from the right table that matches the condition. 
So, if we wanted to get all the phone numbers - even if a student is not associated - we would use a RIGHT JOIN.
SELECT m.first_name, m.last_name, c.phone, c.type FROM members m RIGHT JOIN contact c ON m.member_id= c.member_id;
Adding All Fields
The first way assumes that you are adding values for all the fields in your table, so you do not need to list the field names. But, it is very important to make sure that the order of your values is the same as the field order in the table. The basic syntax is as follows:
INSERT INTO table_name VALUES (value1, value2, value3,....);
Example:
INSERT INTO member VALUES (NULL,'Margaret','Pen','13','margaret@pen.org','French','Left us..but came back')
Adding Some Fields
The second way allows you to assign values to specific fields - this is useful if you are adding a record with partial information. The basic syntax is as follows:
INSERT INTO table_name (column_name1, column_name2, column_name3 ...) VALUES (value1, value2, value3,....);
Example:
INSERT INTO member (first_name, last_name, description) VALUES ('John', 'Doe', 'Why is that his name?');
The basic syntax is as follows:
UPDATE table_name SET field1 = value1, field2 = value2 ... WHERE condition;
So to update a a member to say they are taking French.
UPDATE member SET age = 13, language = "French" WHERE last_name= "Doe"
DELETE statement.
The basic syntax is as follows:
DELETE FROM table_name WHERE condition;
Remember to be very careful when using this - use a WHERE clause and test it with a simple SELECT statement first! Enough? Let's delete some data. Though it makes me sad...let's get rid of John Doe. We were never really sure who he was anyway, right?
DELETE FROM member WHERE Last_name = "Doe"
Now if you go and browse the table you will see it he is gone. So sad.
A word about DROP
If the table is no longer needed you can delete the entire table, including all data and the structure, by clicking on the "Drop" tab in phpMyAdmin or running this line:
DROP TABLE members
But don't do that!!
General Rules
- Use lowercase: speed typing, avoid mistakes due to case sensitivity
- No space - use underscore instead
- No numbers in name
- Valid understandable names
- Names should self explanatory
- Names should not be more then 64 characters
- Avoid prefix
Database Name Conventions:
Follow all rules on general from above plus:
- Name can be both singular and plural but singular if possible
- Avoid prefix if possible
Naming Conventions for tables
- Lower case
- Table names should be singular - like user not users or car not cars.
- Avoid prefixes unless you need them if multiple projects use the same database.
Naming conventions for fields
- Since MySQL is case sensitive use lower case for all field names
- No spaces, no numbers and avoid prefixes.
- Primary key can be id or tablename_id.
- Avoid using column names that match the table name
- Choose short names no longer then two words when possible.
- Avoid abbreviations and acronyms when possible.