In this tutorial, we will take a look at basics SQL commands needed to manipulate your database tables, this is the first part of a three part tutorial, as I would not be able to cover as much as I want in just one part. For the examples here I assume you are able to issue commands through the MySQL monitor on Windows or Linux, in my case I use MySQL Query Analyzer in Windows.
In this first part will cover:
- The basic MySQL data types
- How to use the CREATE TABLE command to create a table
- How to use the INSERT command to enter records
- How to use the SELECT command to retrieve records
MySQL Data Types:
Properly defining a field in a table is really important for the overall optimization of a database. you should use only the types and size of field you really need to use. These types of fields (or columns) are also referred to as data types because it’s the type of data you will be storing on those fields. MySQL uses many different data types, which are broken into three categories: numeric, date and time, and string types. Creating the correct data type is more important than any other part of the table creation process.
Numeric Data Types.
MySQL uses all the standard ANSI SQL numeric data types, so if you’re coming to MySQL from a different database system, these definitions will look familiar to you. The following list shows the most common numeric data types and their descriptions.
- INT — A normal sized integer that can be signed or unsigned. If signed the allowable range is from -2147483648 to 2147483647. If unsigned the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.
- TINYINT — A very small integer that can be signed or unsigned. If signed the allowable range is form -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.
- SMALLINT — A small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5 digits.
Of all the MySQL data types you will be using these the most, specially the INT data type. You can run into problems if you define your fields to be smaller than you actually need; for example if you define an “id” field as unsigned TINYINT, you won’t be able to successfully insert that 256th record if ID is a primary key.
Date and Time Types.
MySQL has several data types available for storing dates and times, and these data types are flexible in their input. In other words, you can enter dates that are not really days such as February 30– February has only 28 or 29 days, never 30. Also you can store dates with missing information. If you know that someone was born sometime in November of 1980, you can use 1980-11-00, where 00 would have been for the day, if you knew it. The flexibility of MySQL’s date and time types also means that the responsibility for date checking falls on the application developer. MySQL checks only two elements for validity: that the month is between 0 and 12 and the day is between0 and 31. MySQL doesn’t automatically verify that the 30th day of the second month (February 30th) is a valid date.
- DATE — A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31 For example, December 30th, 1973 would be stored as 1973-12-30.
- DATETIME — A date in time combination in YYYY-MM-DD HH:MM:SS format between 1000-01-01 00:00:00 and 9999-12-31 23:59:59, For example, 3:30 in the afternoon on December 30th, 1973 would be stored as 1973-12-30 15:30:00.
- TIME — Stores the time in HH:MM:SS format
There are many more Date and Time data types but out of all those you will be using these the most.
Although numeric and data types are fun, most data you’ll store will be in string format… This describes the most basics or used string formats.
- CHAR(M) — A fixed length string between 1 and 255 characters in length (for example, CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1.
- VARCHAR(M) — A variable-length string between 1 and 255 characters in length, for example VARCHAR(25). You must define a length when creating a VARCHAR field.
- BLOB or TEXT — A field with a maximum length of 65535 characters. BLOB’s are Binary Large Objects and are used to store large amounts of binary data, such as images or other types of fields. Fields defined as TEXT also hold large amounts of data; the difference between the two is that sorts and comparisons on stored data are case sensitive on BLOB’s and are not case sensitive in TEXT fields. you do not specify a length with BLOB or TEXT.
If you want a complete more in depth reference about data types, refer to the MySQL reference manual:
The Table Creation Syntax:
The table creation command requires.
Name of the table
Name of the fields
Definitions for each field
The generic table creation syntax is
CREATE TABLE table_name (column_name column_type);
The table name is up to you of course, but should be a name that reflects the use of the table. For example, if you have a table that holds the inventory of a grocery store, you wouldn’t name the table s. You would probably name it something like grocery_inventory. Similarly, the field names you select should be as concise as possible and relevant to the function they serve and data they hold. For example you might call a field holding the name of an item item_name, not n. This example creates a generic grocery_inventory table with fields for ID, name, description, price, and quantity:
CREATE TABLE grocery_inventory (
id INT not null primary key auto_increment,
item_name VARCHAR(50) not null,
item_price FLOAT not null,
curr_qty INT not null
The id field is defined as primary key and auto_increment which will add the next available number to the id field.
The INSERT Command:
After you have created your tables, you’ll use the SQL command INSERT for adding new records to these tables. The basic syntax of INSERT is:
INSERT INTO table_name (column list) VALUES (column values);
Within the parenthesis goes the list of values, you must enclose strings within quotation marks, The SQL standard is single quotes, but MySQL allows the usage of either single or double quotes. Remember to escape the type of quotation mark used, if it’s within the string itself. Here is an example of a string where escaping is necessary:
O’Connor said “Boo”
If you enclose your string in double quotes, the INSERT statement would look like this:
INSERT INTO table_name (column_name) VALUES (“O’Connor said \”Boo\””);
If you enclose your string in single quotes instead, the INSERT statement wold look like this:
INSERT INTO table_name (column_name) VALUES (‘O\’Connor said “Boo”‘);
Besides the table name, there are two main parts to the INSERT statement, the column list and the value list. Only the value list is required, but if you omit the column list, you must specifically name each column in your value list in order. Using the table we created previously, we can use the either of this INSERT statements:
1 – A statement with all columns named:
INSERT INTO grocery_inventory (id, item_name, item_desc, item_price, curr_qty) VALUES (1, “apples”, “beautiful apples”, “0.25”, 1000);
2- A statement that uses all columns but does not name them:
INSERT INTO grocery_inventory VALUES (1, “apples”, “beautiful apples”, “0.25”, 1000);
Because id is an auto-incrementing integer, you don’t have to put it in your value list. However, if there’s a value you specifically don’t want to list (such as id) you then must list the remaining column in use. For example, the following statement does not list the columns and also does not give a value for id.
1- A statement with all columns named except id:
INSERT INTO grocery_inventory (item_name, item_desc, item_price, curr_qty) VALUES (“bottled water”, “400ml spring water”, “2.20”, 250);
2- A statement that uses all columns, but does not explicitly name them and indicates a NULL entry for id (so one is filled in for you).
INSERT INTO grocery_inventory VALUES (“NULL”, “bottled water”, “400ml spring water”, “2.20”, 250);
I suggest that you be consistent and stick to one method throughout your program, for sake of debugging proposes.
The SELECT Command:
SELECT is the SQL command use to retrieve records. This command syntax can be totally simplistic or very complicated. As you become more comfortable with database programming you will learn to enhance your SELECT statements, ultimately making your database do as much work as possible and not overworking your programming language
The most basic SELECT syntax looks like this:
SELECT expressions_and_columns FROM table_name
[ORDER BY some_column ASC | DESC ]
[LIMIT offset, rows]
Let’s start with the first line:
SELECT expressions_and_columns FROM table_name
One handy expression is the * symbol, which stands for “everything” so, to select everything (all rows, all columns) from grocery_inventory table, your SQL statement would look like this:
SELECT * FROM grocery_inventory;
as you can see the results that MySQL outputs, if you want specific columns just replace the * with the name of the columns, separated by commas. The following statement select the id, item_name, and curr_qty fields from the grocery_inventory table.
SELECT id, item_name, curr_qty FROM grocery_inventory;
Ordering SELECT Results:
By default, results of SELECT queries are ordered as they were inserted into the table, and shouldn’t be relied upon as a meaningful ordering system. If you want to order results a specific way such as by date, id, name, and so on, specify your requirements using the ORDER BY clause. In the following statement results are ordered by item_name
SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY item_name;
SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY item_name DESC;
You’re not limited to sorting by just one field, you can specify as many fields as you want separated by comma, The sorting priority is the order in which you list the fields.
Limiting Your Results:
You can use the LIMIT clause to return only a certain number of records in your SELECT query result. There are two requirements when using the LIMIT clause: offset and number of rows. The offset is the starting position, and the number of rows should be self explanatory.
Suppose you had more than 3 records in the grocery_inventory table, and you wanted to select the id, name, and quantity of the first 3 ordered by curr_qty. In other words, you want to select the 3 items with the least inventory. The following single parameter limit will start at the 0 position and go to the third record:
SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY curr_qty LIMIT 3;
1- SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 0, 3;
2- SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 3, 3;
3- SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 6, 3;
In web applications, when list of data are displayed with links such as “previous 10” and “next 10”, it’s a safe bet that a LIMIT clause is at work.