MySQL & Statements in SQL

There are many misconceptions when it comes to databases:

  • Some people think they are hard to develop (Not True)
  • Many people think they are expensive (There are Free Versions Available)
  • Most people don’t know how to start using them (You’re in the right place)

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:

  • Do you have trouble managing your data, because it is unorganized
  • Do you have massive amounts of data that is too complicated to store in a spreadsheet
  • Would you like to provide access to your data on the internet
  • Would you like to be able to sort through and analyze your data


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
(
customer_name CHAR(15),
customer_address CHAR(30),
customer_phone INT
);

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.

USE acme_database;

You type in USE followed by the name of the database you want to edit, to start editing.

SELECT DATABASE();

Well tell you which database is currently being used. The terminal reply would look like this:

ACME Database SQL Statement Example

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,
last_meeting TIMESTAMP,
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:

  • The CREATE TABLE sql statements tell the MySQL server that you want to create a new table called customers and you want to define storage spaces, that will be contained between to brackets ( & )
  • first_name VARCHAR(15) NOT NULL : Create a cell named first_name, it is going to contain characters that will (more than likely) be no longer than 15 in length, and it must contain a value (NOT NULL).
  • NOT NULL: Is an optional attribute, that states when a new row of data is entered this value must be defined and can’t be left blank.
  • state CHAR(2) NOT NULL DEFAULT “PA”: Create a cell named state, that will definitely be 2 characters in length and has a DEFAULT value of PA
  • birth_date DATE NOT NULL: Create a cell named birth_date, that will contain data of the format (YYYY-MM-DD).
  • sex ENUM(‘M’,’F’) NOT NULL: Create a cell named sex, that will only except the value M, or F.

cust_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY:

  • INT: Defines the value entered will be a number. I’ll describe this in detail soon.
  • UNSIGNED: Defines that the number can’t have a negative value
  • AUTO_INCREMENT: States that you want this number generated automatically and that it should be one greater than the value of the row of data that proceeds it. More on this soon!
  • PRIMARY KEY: States that this value must be unique and that you plan on using it to speed up the process of searching through the database. Much more on this soon.
  • last_meeting TIMESTAMP: Defines a cell that will contain date and time data in the following format (YYYY-MM-DD-HH-MM-SS)
  • money_owed FLOAT NULL: States that the data contained in this cell will be a number with decimal places. By defining NULL, in this statement, we are stating that this value could be empty.

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.

Numeric Types

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

String Types

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:

Customer Table SQL Statement Example

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:

  • A value must be entered for each cell in the row and the values must be inserted in order
  • You place quotes around strings and dates. You can use single or double quotes
  • It is legal to enter NULL as a value because cust_id auto-increments itself, meaning it automatically generates it’s own value when a new row is created

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:

Arithmetic Operators

+ : Addition
– : Subtraction
* : Multiplication
/ : Division
% : Modulo – Returns the remainder of a division. Ex: 5 % 2 = 1

Logical Operators

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.

Comparison Operators

= : 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!

MySQL SQL Table Example

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…
Think Tank

9 Responses to “MySQL & Statements in SQL”

  1. cna training says:

    Wow this is a great resource.. I’m enjoying it.. good article

  2. Aamir says:

    hi derek, when i write money_owned FLOAT null ); it show error “check MYsql version for the right syntax near this line”

  3. Rasme says:

    Hi which editor should I write the those code….I’m sorry but I know nothing bout mysql/sql

  4. Rasme says:

    Thanks

  5. Martin says:

    Hi, first of all thanks again for everything you do.

    I just want to tell you that there’s a typo in line 4
    “Most people don’t know how to start using them (Your in the right place)”

    It’s you’re 😛

Leave a Reply

Your email address will not be published.

Google+