TQNYC Tutorials > MySQL

MySQL is a type of database system which is freely available for almost all computer platforms. A database system includes a server which hosts the databases or database instances (structure and information), and a client that can connect and talk or send commands to the server. It is not important that you understand this in great detail but you need to understand in order to send commands to or "query" a database you need to use a client. A client can be accessed from the command line of the TQNYC server or through a scripting language like Perl or PHP (see TQNYC PHP Tutorial) which is available on the TQNYC server. PHPMyAdmin is a browser based MySQL client that is available on the TQNYC server in the Dev Tools of your Team Account. This tutorial is written for the command line client which should make programming a PHP enabled web page to use MYSQL much easier. PHPMyAdmin screenshots are included for those unwilling to go the command line route. Learning SQL and how MySQL works this way will make using PHPMyAdmin possible.

What is a "database instance"?

We will use the term "database" to refer to a "database instance" or "schema". Just as there can be multiple users on a server at the same time there can be multiple database instances on a MySQL database server. This is good to know but will not come into use for our purposes. We will refer to a "database" when refering to your database instance. Each team can have a database, or database instance, all running on the TQNYC MySQL sever. Again, not important but may answer some questions down the road.

MySQL Client-Server-Instance Relationship

What is a Database?

A database is a system for organizing information or data, storing it and retrieving it. Good database design will define the structure of your database (where you will put your data) in the most logical way possible.

Databases are used as stand alone applications, within other software as well as with web sites. Since this is TQNYC this tutorial will refer to using the database as if it were part of a web site. The differences between these different ways of using a database refer to how the database is accessed. For a web site you might use PHP or another scripting language to process data entered into web forms and insert it into the database or select data from the database and generate HTML pages. If the database is used as a standalone database you might use the command line client or PHPMyAdmin to access the information in the database. If used as part of a piece of software you would probably use whatever language the software is written in.

The first step before integrating a database is to learn how databases work and are created.

A database is made up of "tables" defined by you, the databae designer. Each table, like a spreadsheet, is divided into "columns". Tables should be created for a specific set of information with a unique table name that describes the data in that table. Information will be inserted into tables as "rows. Each row is an individual record. A database is usually made up of many tables. Each table should have a column that functions as a "unique identifier" or primary key. This should be a numeric ID. The unique identifier allows us to locate and reference the exact row of information we want.

Here is a visual example of a table named "students" with four columns. Two rows of data has been put into this table.

Tables are related to each other by "keys" or shared pieces of information. These shared keys crreate "relationships" between tables. These relationships allow data to be related without having to be repeated. One major goal of a database is that a single piece of data is only entered into the database at one place. This will allow us to update data without having to update the data in several places. Our example to follow will hopefully make this clearer.

Once we define the fields (columns) of information we want to store in our table, the next step is to determine what kind or format of information is going into each one of those fields. Databases require that we define the type of information we are putting into the columns when we create the columns so it can know what to expect and sort it out as efficiently as possible. The database column types are mainly about the format or characters you will put into a column. This means we have to know the type of data we are putting into the columns so we can define the column types properly.

Column Types

The "type" of data (range of characters) we are going to put into each column and the length of that data (the number of characters) will determine what type of column we create them as. There are many types of columns supported by MySQL and you can read more about them here but for our purposes I will limit our tutorial to four types.

If we are putting general text with numbers, letters and punctuation marks into a column we have 2 general types, "varchar" or "text". The difference between the two is that varchar columns can have a maximum of 255 characters and a text column has a limit of 65,535 characters. You may be wondering why not just use text columns? Well the answer is efficiency. The less empty space in a table the fast the database server can sort and search through it. So for a last_name you may want a varchar(50) while for the email column you may need a little more room and use a varchar(150).

The two other types of columns you will need to understand are integer (int) and datetime columns. Int columns are columns with only numbers in them. You do not have to define the size but you can. Unique identifier columns should be interger columns.

Datetime column type is a standard "YYYY-MM-DD HH:MM:SS" timestamp format. Why use a datetime instead of a varchar column? Well because the database can do math and figure things our about the date for you like if it is a Monday or a Tuesday, what the month name is. It can also perform math functions do you can figure out things like age from a birthdate. We will get to these but for the moment just understand dates should go in a datetime column.

Column Attributes

Column attributes are further descriptions of the column types to help the database and the database desingner to layout the tables and columns to prevent bad data getting in or worthless records being created.

NULL or NOT NULL

One more important concept is the NULL value and wether a column will allow them. First a NULL value is nothing. This can be confusing because although a zero (0) maybe considered nothing the database would see it as a character, the same with a white space, (" "). So when we talk about a NULL value we are talking about if a column will accept a row without a value in the field for that column. Columns that do not are known as "NOT NULL" columns. NULL or NOT NULL is defined when the column is created. All unique identifier columns must be NOT NULL. If NULL's were allowed in the "user_id" column then those rows would not have an ID or identifier. In our users table we may only require that a "first_name" and of course "user_id" be NOT NULL or in other words require values be entered to have a valid row. If you tried to insert a row with NULL for all columns but "user_id" and the "first_name" column was created as NOT NULL the database would not accept that row.

Auto Increment

"auto_increment" is an attribute which tells the database to fill this column with unique numeric identifers. This is an attribute that makes _id columns very easy to create and manage. The database will start with 1 and then for each row inserted will increment the counter so the next row is 2 and next 3 on and on. Tables can only have one auto_increment field.

Primary Keys

Primary key is the designation of the column which the database will look to to sort the table by. It is usually the unique identifier. This designation allows the database to keep an index of the table and speed up searches and sorting of the rows.

The SQL Language

Once you are connected to the database you will use the "SQL" language to select, insert, update or delete information in the da tabase. SQL also has commands for creating, altering and dropping tables and columns within those tables. The name of the language is pronounced "See Qwell" or "ess-cue-el", either way is fine. This language is simple yet powerful. We will cover basic SQL comman ds throughout the tutorial.

SQL commands are not case sensitive. The following commands and command syntax is how to communicate with the database.

Basic SQL Statement

The syntax below is the SQL keywords are all capitals, the table and column names are in curly brackets ({}). When writing SQL you replace {table name} with the name of the table, no curly brackets. Any data you are inserting or selecting by you should put it in single quotes ('').

CREATE TABLE {table name} ( {column name1} {column type}, {column name2} {column type} );

This statement creates a table with the name given with columns named and defined.

ALTER TABLE {table name} ADD {new column name} {column type};

This statement adds a column to an existing table.

SELECT {column_a}, {column_b} FROM {table};

This statement tells the database to select the data in the column(s) listed from the table listed.

INSERT INTO {table} ({column}, {column2}) VALUES ('{data}','{data2}');

This statement tells the database to insert data and data2 into column and column2 in the table.

UPDATE {table} SET {column} = 'data';

The update statement sets the column equal to the data for all records that exist in the table.

SHOW TABLES;

This command will list all the tables in your database.

DESC {table};

The Describe statement will list all the columns and their type and attributes.

DROP TABLE {table name};

This statement will delete the table and all data it contains.

DELETE FROM {table};

This command deletes all data from the table.

Select, update and delete commands can be limited by the "WHERE" clause. A where clause basically allows you to define which row s you want to select, update or delete.

Where Clause

SELECT {column(s)} FROM {table} WHERE {column} = '{data}';

The where clause will cause the select statement to return those rows that have column equal to the data.

Joins

A Join is a technique in which you select data from two or more tables through the use of common columns.

SELECT {table1}.{column1}, {table2}.{column3} FROM {table1}, {table2} WHERE {table1}.{column2} = {table2}.{column2};

This select statement will return column1 from table 1 and column3 from table2 where column2 in table1 equals column2 in table2. This is a complicated and confusing but powerful statement.

SQL does allow us to use "alias" or short names for the tables. One goal of all good programmers is to use as few key strokes as possible. So this join statement could be written like this.

SELECT a.{column1}, a.{column3} FROM {table1} a, {table2} b WHERE a.{column2} = b.{column2};

As you can see the letters "a" and "b" are used instead of the full table names.

Although this is a lot of information and you may be scratching your head wondering what all of this means, don't worry. Once you see the example database and how we are going to build that it should all become crystal clear.

Continue to page 2 | 3 | 4 | 5 | 6 | 7 .