There are many misconceptions when it comes to databases:
In the next series of articles I will show you how to use mySQL. A completely free database server that many consider to also be the best. Heck, NASA uses MySQL.
So do you need one?
If you answer any of these questions affirmative, the answer is yes:
What is MySQL?
A database is a highly structured place you use to store information of any type. You organize your information in a series of tables with rows and columns, like a spreadsheet.
If you read about databases you no doubt will hear the term “Relational Database Management System.” This is what that means:
The Relational word refers to the fact that the database organizes data, based off of how it relates to the other data. If you structure the database in the right way, it can access millions of bits of data in seconds.
The Management System provides you with the ability to access, add to and delete the data within.
MySQL is just a RDMS that is freely available for your use. To use it though you will need to learn how to talk to it in it’s native tongue. Don’t worry, it’s not that complicated.
What is SQL?
You use Structured Query Language to issue commands to MySQL. Remember you create tables of data in a database. Here is an example of how you could create a table using SQL:
CREATE TABLE customer
Here we are creating a table. We are placing in that table storage areas for the customers name, address and phone number. With the code, CHAR(15), we are notifying the database that it should expect the data to be entered to be in the form of a character and be no longer than 15 characters long. With INT, we are telling the database that it should expect a number to be entered and that number will not have decimal places.
What do you Need to Create a Database?
You will probably host your database on someone else’s computer and the access it through the internet. I write about internet based technologies, so that is how I’ll approach this subject. ISP’s provide you with a database pre-installed. You can then access them with a terminal or with the ISP’s proprietary software. Putty is a commonly used terminal, but you’ll probably just use the ISP’s personal system to access your database.
If you use Go Daddy as your ISP, chances are you are using a program called PHPmyAdmin. This is the most common way ISP’s provide access to databases. For now though I’m going to teach you SQL, outside of the terminal environment to keep everything simple.
A Simple Example Using SQL
After you log in to your account with your userid and password, you’ll be able to start creating databases and tables with SQL. You login to MySQL by typing the following statement: mysql -u derek -p
Note: this is done when you are accessing MySQL through a terminal and won’t be required if your using an ISP’s system. This command tells the computer to start up mysql and with the -u, you are providing your userid. With -p, you are telling mysql to ask for your password, once it has loaded. Then you type in your password and you are ready to go.
To create a database just type the following sql statement: CREATE DATABASE acme_database;
This creates the storage area for my companies customers. You can name the database what ever you’d like.
You type in USE followed by the name of the database you want to edit, to start editing.
Well tell you which database is currently being used. The terminal reply would look like this:
To create our customer table you would do the following:
CREATE TABLE customers
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(15) NOT NULL,
state CHAR(2) NOT NULL DEFAULT “PA”,
birth_date DATE NOT NULL,
sex ENUM(‘M’,’F’) NOT NULL,
cust_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
money_owed FLOAT NULL
Ok, I made this complicated so I could talk about many things. This is what’s being done with this statement:
cust_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY:
Data Types Available with SQL
So, that is how you define a complicated table with MySQL. I’ll now describe all of the different types of variables you can use.
TINYINT: A number with a value no bigger than 127 or smaller than -128
SMALLINT: A number with a value no bigger than 32,768 or smaller than -32,767
MEDIUM INT: A number with a value no bigger than 8,388,608 or smaller than -8,388,608
INT: A number with a value no bigger than 2^31 or smaller than 2^31 – 1
BIGINT: A number with a value no bigger than 2^63 or smaller than 2^63 – 1
FLOAT: A number with decimal spaces, with a value no bigger than 1.1E38 or smaller than -1.1E38
DOUBLE: A number with decimal spaces, with a value no bigger than 1.7E308 or smaller than -1.7E308
CHAR: A character string with a fixed length
VARCHAR: A character string with a length that’s variable
BLOB: Can contain 2^16 bytes of data
ENUM: A character string that has a limited number of total values, which you must define.
SET: A list of legal possible character strings. Unlike ENUM, a SET can contain multiple values in comparison to the one legal value with ENUM.
Date & Time Types
DATE: A date value with the format of (YYYY-MM-DD)
TIME: A time value with the format of (HH:MM:SS)
DATETIME: A time value with the format of (YYYY-MM-DD HH:MM:SS)
TIMESTAMP: A time value with the format of (YYYYMMDDHHMMSS)
YEAR: A year value with the format of (YYYY)
Checking the Tables
Now you know how to create tables and what all the different data types are. Let’s check our work with the DESCRIBE function. If you type DESCRIBE customers; you’ll see this:
As I’m sure you guessed that the DESCRIBE sql statement prints out all the information pertaining to your tables attributes. If Null doesn’t have a value, that means that a value must be set by default. If NULL was set the word Yes would appear in the column. If the cell is a key, the column would tell you what type of key it is. I’ll explain keys in a bit. Default contains any default values that are set and Extra contains any additional information that is available about a given column.
You can display all of the tables contained in a database by typing SHOW TABLES; and you can see all of the databases on a server by typing SHOW DATABASES;
Inserting Data into the Database
Now you have a table, how do you enter information into it? With the INSERT command:
INSERT INTO customers VALUES ( ‘Paul’, ‘Jones’, ‘PA’, “1972-10-2”, ‘M’, NULL, “2010109121054”, 54.96);
This is all pretty self explanatory. Just remember the following:
You could also insert data into a table with the following sql statements:
INSERT INTO customers VALUES(last_name,first_name) VALUES(‘Paul’,‘Jones’);
The final way you could load values into a table is with the LOAD DATA function. You can submit a tab delimited list of values as long as they are in order. Each line will have the values separated by tabs and nothing else. No INSERT, or anything. After you have that prepared just type the following to input all of the new data:
LOAD DATA LOCAL INFILE “customers.txt” INTO TABLE customers;
Getting at Your Data
The SELECT function provides you with the ability to access and sort your data in a multitude of ways. The general format of a SELECT function looks like this:
SELECT what you want to select
FROM which table or tables
WHERE conditions you want the data to satisfy
Here are some examples:
SELECT * FROM customers; # This returns all of the data in the table customers
Note: You can put a comment in SQL with the # symbol, or surround a comment that goes over multiple lines with the opening characters /* and the closing characters */
SELECT first_name FROM customers; # Outputs all of the values stored in the cell first_name
SELECT last_name, birth_date FROM customers; /* Outputs last_name and birth_date for all the rows in the table customers */
Getting Exactly What you Want with WHERE
You use the WHERE attribute to specify that the data retrieved fulfills your specific conditions. Here are examples:
SELECT * FROM customers WHERE money_owed > 10.00; /* Returns all of the information on anyone that owes us more than $10 */
SELECT first_name, last_name, birth_date FROM customers WHERE birth_date > “1990-1-1”; /* Returns specified values for everyone born before 1990 */
SELECT first_name FROM customers WHERE (money_owed > 10.00) AND (state =”PA”);
/* Returns first_name for any customer that owes us over $10 and lives in PA */
Like I described in my other programming tutorials their are many operators available for comparing data. Here they are:
+ : Addition
– : Subtraction
* : Multiplication
/ : Division
% : Modulo – Returns the remainder of a division. Ex: 5 % 2 = 1
AND, && : Returns a true value if both conditions are true
OR, || : Returns a true value if either condition is true
NOT, ! : Returns a true value if the operand is false
note: You can use either the && or the AND logical operator. They along with the other Logical Operators, both perform the same action.
= : Equal to
< : Less than
> : Greater than
<= : Less than or equal to
>= : Greater than or equal to
!=, <> : Not equal to
IN : Ex. x IN (y1, y2…) Will return true if x is one of the values listed
BETWEEN : Ex. x BETWEEN y AND z Will return true is the value of x lies between y and z
LIKE : Ex. x LIKE y, returns the value true if the value of x matches the value in y, based off of pattern matching.
NOT LIKE : The opposite of LIKE
REGEXP : Allows you to use regular expression tests on data. See the regular expression tutorial I’ve written for more information.
NOT REGEXP : The opposite of REGEXP
IS NULL : Returns true if a value equals null
IS NOT NULL: Opposite of IS NULL
The COUNT & GROUP BY SQL Statements
Let us say you’d like to count the number of customers that owe you more than $10. Here is an example of how you could do that.
SELECT COUNT(*) FROM customers WHERE money_owed>10.00;
You could also see how many male versus female customers you have, with this statement:
SELECT sex, COUNT(*) FROM customers;
How about a statement that places the states in alphabetical order and the number of customers from each state:
SELECT state, COUNT(*) FROM customers GROUP BY state;
If you wanted to sort in reverse alphabetical order, you would type:
SELECT state, COUNT(*) FROM customers GROUP BY state ORDER BY count DESC;
The AS Statement
Would you like to change the names of your columns to something nicer? Well then you’ll love the AS statement. The following statement will change your titles:
SELECT first_name as First Name, last_name as Last Name FROM customers;
Now you have a stylish table!
That’s All Folk’s
That’s all I’m covering in part 1 of my SQL & MySQL tutorial. If you have any questions leave them below. In the next article I’ll teach you about keys, working with multiple tables and give you more functions you can use.
Till next time…