Database

A database is a computerized record keeping system. More completely, it is a system involving data, the hardware that physically stores that data, the software that utilizes the hardware's file system in order to 1) store the data and 2) provide a standardized method for retrieving or changing the data, and finally, the users who turn the data into information.

The Relational Database Model

A database can be understood as a collection of related files. How those files are related depends on the model used. Early models included the hierarchical model (where files are related in a parent/child manner, with each child file having at most one parent file), and the network model (where files are related as owners and members, similar to the network model except that each member file can have more than one owner).

The relational database model was a huge step forward, as it allowed files to be related by means of a common field. In order to relate any two files, they simply need to have a common field, which makes the model extremely flexible.
Poet

Code	First Name	Surname	Age
1	Mongane	Afrika	62
2	Stephen	Serote	58
3	Tatumkhulu	Watson	29

Poem

Title	Poet
Wakening Night	1
Thrones of Darkness	2
Once	3

These two tables relate through the code field in the poet table, and the poet field in the poem table. We can see who wrote the poem 'Once' by following the relationship, and see that it was poet 3, or Tatumkhulu Watson.

In 1970, when E.F. Codd developed the model, it was thought to be hopelessly impractical, as the machines of the time could not cope with the overhead necessary to maintain the model. Of course, hardware since then has come on in huge strides, so that today even the most basic of PC's can run sophisticated relational database management systems. Together with this went the development of SQL. SQL is relatively easy to learn, and allows people to quickly learn how to perform queries on a relational database. This simplicity is part of the reason that relational databases now form the majority of databases to be found.

Basic Terms

An understanding of relational databases requires an understanding of some of the basic terms.

• Data are the values stored in the database. On its own, data means very little. "43156" is an example.
• Information is data that is processed to have a meaning. For example, "43156" is the population of the town of Littlewood.
• A database is a collection of tables.
• Each table contains records, which are the horizontal rows in the table. These are also called tuples.
• Each record contains fields, which are the vertical columns of the table. These are also called attributes. An example would be a product record.
• Fields can be of many different types. There are many standard types, and each DBMS (database management system, such as Oracle or MySQL) can also have their own specific types, but generally they fall into at least three kinds - character, numeric and date. For example, a product description would be a character field, a product release date would be a date field, and a product quantity in stock would be a numeric field.
• The domain refers to the possible values each field can contain (it's sometimes called a field specification). For example, a field entitled "marital_status" may be limited to the values "Married" and "Unmarried".
• A field is said to contain a null value when it contains nothing at all. Fields can create complexities in calculations and have consequences for data accuracy. For this reason, many fields are specifically set not to contain NULL values.
• A key is a logical way to access a record in a table. For example, in the product table, the product_id field could allow us to uniquely identify a record. A key that uniquely identifies a record is called a primary key.
• An index is a physical mechanism that improves the performance of a database. Indexes are often confused with keys. However, strictly speaking they are part of the physical structure, while keys are part of the logical structure.
• A view is a virtual table made up of a subset of the actual tables.
• A one-to-one (1:1) relationship occurs where, for each instance of table A, only one instance of table B exists, and vice-versa. For example, each vehicle registration is associated with only one engine number, and vice-versa
• A one-to-many (1:m) relationship is where, for each instance of table A, many instances of the table B exist, but for each instance of table B, only once instance of table A exists. For example, for each artist, there are many paintings. Since it is a one-to-many relationship, and not many-to-many, in this case each painting can only have been painted by one artist.
• A many to many (m:n) relationship occurs where, for each instance of table A, there are many instances of table B, and for each instance of table B, there are many instances of the table A. For example, a poetry anthology can have many authors, and each author can appear in many poetry anthologies.
• A mandatory relationship exists where, for each instance of table A, one or more instances of table B must exist. For example, for a poetry anthology to exist, there must exist at least one poem in the anthology. The reverse is not necessarily true though, as for a poem to exist, there is no need for it to appear in a poetry anthology.
• An optional relationship is where, for each instance of table A, there may exist instances of table B. For example, a poet does not necessarily have to appear in a poetry anthology. The reverse isn't necessarily true though, for example for the anthology to be listed, it must have some poets.
• Data integrity describes the accuracy, validity and consistency of data. An example of poor integrity would be where a poet's name is stored differently in two different places.
• Database normalization is a technique that helps us to reduce the occurrence of data anomalies and poor data integrity.

Some of the features of MySQL Include: Handles large databases, in the area of 50,000,000+ records. No memory leaks. Tested with a commercial memory leakage detector (purify). A privilege and password system which is very flexible and secure, and which allows host-based verification. Passwords are secure since all password traffic when connecting to a server is encrypted.

For more information about MySQL, check out the web site at http://www.mysql.com/

This multi-part tutorial will be on the various basic commands and functions available within MySQL.

Unless you are sitting in front of your server (which you likely aren't), you are going to have to telnet into the server. Once you are in, proceed to the directory where you can find the MySQL files...

At the prompt type in something along the lines of the following syntax to login

mysql [-h host_name] [-u user_name] [-p your_pass]

Usually you have to ask your web host for a MySQL user name and password, as this is the kind of service you would have to inquire about in the first place.

Personally, when I log in to mysql, the first thing is see is:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is XXXX to server version: 3.22.22

Type 'help' for help.

Once you type help, you will see:

MySQL commands:
help (\h) Display this text
? (\h) Synonym for `help'
clear (\c) Clear command
connect (\r) Reconnect to the server. Optional arguments are db and host
edit (\e) Edit command with $EDITOR
exit (\q) Exit mysql. Same as quit
go (\g) Send command to mysql server
ego (\G) Send command to mysql server; Display result vertically
print (\p) Print current command
quit (\q) Quit mysql
rehash (\#) Rebuild completion hash
status (\s) Get status information from the server
use (\u) Use another database. Takes database name as argument

These usually help you out when you get lost during your MySQL Adventures...
All commands will be issued from the mysql> prompt from now on.

MySQL >> Datatypes
MySQL >> Creating Your Database
MySQL >> Logical Operators
MySQL >> Manipulating your Tables

No

INT[(M)]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

Example Use: Age INT;

This would be a valid integer if it is within the -2147483648 to 2147483647 values if it is unsigned, which it would be of course as no one is younger or older then those numbers... obviously.

FLOAT[(M,D)]
A small (single-precision) floating-point number. Cannot be unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0 and 1.175494351E-38 to 3.402823466E+38.

Floating point numbers are made to be very precise. If I specified Price FLOAT(6,2) then 6 numbers would be allowed to the left of the decimal point, and 2 to the right. Very nice indeed.

Date and Time

DATE A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers.

Ex. Today DATE;

TIME

A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but allows you to assign values to TIME columns using either strings or numbers.

Ex. Now TIME;

String Types

CHAR(M)
CHAR's are fixed length strings that are right-padded with space to whatever you specify the length as in M. If you specify a data type as Name CHAR(60), and the entry is only 20 characters in length, then the entry would be padded with 40 characters worth of spaces. These spaces will be removed when the value is retrieved though. These entries will be sorted and compared by MySQL in case-insensitive fashions unless you give the BINARY keyword.... The maximum size is 255 characters.

Ex. Name CHAR(60);

VARCHAR(M)
These strings are made of variable lengths (as the name kind of suggests). All of the trailing spaces will be removed when the value is stored in the database. This is great for when you have limited hard drive space, but it can be a problem performance-wise. You can lose up to 50% of speed because of this. The explanation is that it actually loads the maximum length in anyway, and stores everything into memory. This causes the lag in performance. The maximum size is 255 characters.

Ex. Name VARCHAR(60);

BLOB or TEXT
BLOB or TEXT columns have a maximum length of 65535 (2^16 - 1) (BIG). They can hold text, obviously of course, and are good at keeping large records, such as articles. The difference is that BLOB searches are case sensitive, while TEXT searches are case in-sensitive. These are used when your record size is between 255 and 65535 characters.

Creating a Database

Right now I am assuming you have already logged into MySQL, so you should be looking at something like the following on your screen right now:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10663 to server version: 3.22.22

Type 'help' for help.

mysql>

First note before I continue, you must remember that ALL MySQL commands end with a semicolon ";".
Anyway, lets make a database called info. To make it, you would type

mysql> create database info;

If you did this successfully, you will get something like this result;

Query OK, 1 row affected (0.03 sec)

To start using this database, type in

mysql> use info;

You will get the result;

Database changed.

Great, you now have the info database working, and are now using it.

Creating Your Tables

This is where the previously mentioned MySQL data types come in handy. When you create a table, you specify all of the columns and their data types. It is possible to add a column, or manipulate current columns after they are made, which I will show you later on in the tutorial.

Ok, this example Table will be called users, and it will have 3 total columns. 1, the ID Number, 2, the Person's Name, and 3, the Persons E-Mail address.

mysql> CREATE TABLE users (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR (50),
-> email VARCHAR (50),
-> PRIMARY KEY (id));

Asking what the !@#$ does that mean? Well, I got some explaining to do.

The first column is probably the most cryptic one. Basically, it tells MySQL that this column will be called id, it is an integer, its value cannot be null, and it automatically increments the number from the prior entry. You will see how it is used in the next section, adding data.

The name column is a Variable Length String, which I explained last week. Its length is a maximum of 50 characters. The email column is pretty much the same thing as well.

What's this about the Primary Key part though?

The Primary Key is a type of index MySQL uses. This index can do such things as;

1. Quickly find the rows that match a WHERE clause.
2. Retrieve rows from other tables when performing joins. 3. Sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key

+----+---------------+---------------------------------+
| id | name          | email                           |
+----+---------------+---------------------------------+
| 1  | Mike Smart    | mikesmart@scriptcode.com        |
| 2  | Moon Wings    | moonwings@scriptcode.com        |
+----+---------------+---------------------------------+
2 rows in set (0.00 sec)

Say you want to select a particular row in this database though, you would use this sort of command;

mysql> SELECT * FROM users WHERE (name="Mike Smart");

This would give you

+----+---------------+---------------------------------+
| id | name          | email                           |
+----+---------------+---------------------------------+
| 1  | Mike Smart    | mikesmart@criptcode.com         |
+----+---------------+---------------------------------+

You can also select specific columns, like this;

mysql> select name from users;

+----------------+
| name           |
+----------------+
| Mike Smart     |
| Moon Wings     |
+----------------+
2 rows in set (0.06 sec)

Modifying Database Data

If you have data already in the database that needs some modifying, you would change it by utilizing the UPDATE command in mysql.

Its use is something like this;

mysql> UPDATE users SET email = 'webmaster@scriptcode.com'
-> WHERE email = "mikesmart@scriptcode.com";

This would just change all rows with email set to mikesmart@scriptcode.com and change them to webmaster@scriptcode.com. In this case though, only one entry has mikesmart@scriptcode.com as its email, so only one entry would be changed.

Deleting Database Data

If you want to remove data in the database, you would use MySQL's DELETE command. Its use would be as follows

mysql> DELETE FROM users WHERE (name="Moon Wings");

This would delete Moon Wings entry in the database, leaving only Mike Smart entry in it. So far, a very small portion of MySQL's commands have been covered. There are quite a few more advanced commands you can integrate within your SQL queries.

First off, I will use the users table that has been used throughout these tutorials.

mysql> CREATE TABLE users (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR (50),
-> email VARCHAR (50),
-> PRIMARY KEY (id));

Search Functions

As you have seen in previous examples, MySQL most definitely has the ability to find specific search data. I have never covered general searches though, so here you go; The % character in MySQL is the wildcard character. That is, it can represent anything, literally.

To do a general search, you would use the following syntax;

mysql> SELECT * FROM test WHERE
-> (name LIKE "%B%");

This will result in finding anything with the capital letter B in the column name. Notice the two %'s used. This checks for anything before or after that letter. You can use just one if you like though.

You can place that % sign anywhere within the query though, as the search is based upon the placement of this character.

Another wildcard character is the _ character. It will match exactly one character. To use a literal wildcard character in your searches, you Order By

mysql> SELECT * FROM users WHERE
-> (name = "Joe%") ORDER BY id DESC;

This will return all the records containing someone with the first name of Joe, and will output it from the greatest ID Number, descend until the lowest ID number is reached.

The default for ORDER BY is ascending, so if you want it to go by the lowest ID number first, you would just type in ORDER BY id, or you could plug in the ASC keyword where DESC is currently. Both would give you the same result.

Logical Operators

One of the great features within MySQL is its full support for logical operations. I will name off, and show examples of them below

mysql> CREATE TABLE users (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR (50),
-> email VARCHAR (50),
-> PRIMARY KEY (id));

NOT (or) !

mysql> SELECT * FROM users WHERE
-> (name != "Blair Ireland");

or

mysql> SELECT * FROM users WHERE
-> (name NOT = "Blair Ireland");

This query would return all records without Blair Ireland present as the name.

AND (or) &&

mysql> SELECT * FROM users WHERE
mysql> (name = "Mike Smart") AND mysql> (email = "mikesmart@scriptcode.com");

or

mysql> SELECT * FROM users WHERE
-> (name = "Mike Smart") &&
-> (email = "Mike Smart@scriptcode.com");

This query would return all records with Mike Smart present as the name, and mikesmart@scriptcode.com as the email.

OR ( or ) ||

mysql> SELECT * FROM test WHERE
-> (name = "Mike Smart") OR
-> (email = "mikesmart@scriptcode.com");

or

mysql> SELECT * FROM test WHERE
-> (name = "Mike Smart") ||
-> (email = "mikesmart@scriptcode.com");

This query would return all records with Mike Smart present as the name, or records with mikesmart@scriptcode.com as the email.

Table Manipulation

Lets say you made your table, and all the data has been added to it. Now you have come across a problem... your limit for characters in that particular column is too small for what you now need. You don't want to have to delete all of this data, yet, you have to change your table some how.

Fret no more everyone, you can manipulate your tables that have already been created.

The command for this task is known as ALTER TABLE. Just a note, it is possible to mix and match these commands, usually just separate them with a comma (,), or just place them all in the same line. Play around with them to get a feel for what I am talking about.

Renaming a Table

mysql> ALTER TABLE users RENAME public;

Changing a columns datatype

mysql> ALTER TABLE public MODIFY name CHAR(150);

Renaming a Table and Changing its datatype at once

mysql> ALTER table users CHANGE
-> email emailaddy CHAR (100);

Adding a Column

mysql> ALTER TABLE public ADD time TIMESTAMP;

Remove a Column

mysql> ALTER TABLE public DROP COLUMN time;

After you make these changes to the table, you may want to optimize the table afterwards (especially if you are using VARCHAR's, TEXT's or BLOB's, as this will optimize its memory allocation. You will also want to do it if you have deleted a large part of a table.

During a table optimization, the original table is available to clients, however, modifying and adding to the table is stalled until optimization is complete.

The syntax is: OPTIMIZE TABLE table_name_goes_here

Deleting an entire table

To delete (or drop) an entire table, you would use the following syntax;

mysql> DROP TABLE public;

If you would like to drop more tables at once though, you would do this;

mysql> DROP TABLE public, tests;

Though this does not even remotely cover all of the available features found within MySQL, it does directory mysql> basic the surface structure command.