Dynamic Content Managment

Understanding MySQL

MySQL is a relational database management system, or RDBMS. This type of system stores one or more databases, each of which can contain tables of data of various types. Compared to other systems that can do the same, MySQL is free and requires minimal setup.

MySQL, like most RDBMS systems, is a client-server system. In this type of system, a server manages the actual data storage. One or more clients can connect to the server, send it data or request data, and obtain their results from the server.

Client-server systems are powerful because the server can focus on data management without worrying about the user interface or other issues. Different types of clients or multiple clients can connect to the server simultaneously.

The MySQL client sends a request, or query, to the server. This may be a request to add data to the database, or any of several other types of requests. The server receives the query and sends a result back to the client.

How MySQL works

MySQL consists of clients components and server components. The basic components of MySQL are described in the section below.

Hardware

The MySQL server runs on many different types of hardware. PC systems running Linux or 32-bit Windows are the most common hardware used. The hardware required for the server depends on how busy the server will be with client requests.

Server Software

The MySQL server software is available from www.mysql.com/. The MySQL server is available free of charge for non-commercial use under the open source Gnu Public License (GPL). You can download the software and install it on a Windows, Linux, or Mac.

Client Software

A simple MySQL client, the MySql monitor, is included with the server software. You can also use any client that knows the correct protocols to communicate with the server. Two clients used commonly are MySQLGUI and PHPmyAdmin.

Programming Languages

In addition to the existing MySQL clients, you can use programming languages such as PHP and Perl to create applications that can communicate with a MySQL server. This allows you to create custom client software for virtually any purpose.

Other Database Systems

Even though this class focuses on MySQL, it is actually just one of many server DBMS that you may find useful. Many of these use similar languages and can be used interchangeably, and you can choose whichever server fits the needs of your application.

Flat File Database - Multiplatform/Not DBMS
PostgreSQL - Free/Open Source/High-end applications
Access - Desktop software/simple applications
Microsoft SQL server - only Windows Platform
Oracle - Supports corporate databases and more critical Web sites.
DB2 - Banks use it/Works with Java/XML
mSQL - Lightweight, run on machines with limited resources

Launch MySQL - First Query

Before we can actually attempt a query we need to start the mysql server. At school it's an easy step.

Go to Start->Programs->WampServer. Now select the Apache MySQL Start.cmd option.

Wait for all of the software to be initiated. If there's a beep, try it again. You will see one window which is the command prompt. It looks like a black screen and you will have a blinking cursor after "mysql>".

Now we are ready to begin!

A MySql query begins with an SQL command, such as CREATE, INSERT, or SELECT. The remainder of the query specifies the parameters of the command. You must end each MySQL query with the semicolon (;) character.

When you enter a query into the MySQL Monitor, it is executed immediately by the MySQL server. The monitor displays the result of your query.

While the MySQL monitor is a great way to test MySQL queries and perform simple tasks, it is not the most efficient interface for complex database management. If you need to use a large number of queries, you can do so using a database client such as phpMyAdmin.

In this example, you will enter a simple query at the MySQL Monitor prompt to create a database. The database you create here will be "testdb".

From the MySQL Monitor, type:

create database testdb;

To quit the MySQL Monitor, type:

\q

To restart the MySQL server, type:

mysql -uroot

Multi-line Query

Web-enabled databases provide the opportunity to fundamentally change the way web sites are produced, managed, and delivered. Currently, many people (especially small and medium-sized businesses) are trying to figure out how to effectively use the Web. In most cases, the prime focus is on using the Web to create, manage, find, and deliver information that is stored in a database. This has led to an explosion in the number of products for providing connections between the Web and existing database products, as well as completely new Web-oriented database tools.

The number of products for connecting databases and the web seems to be increasing faster than any other segment of the Internet software market, and with good reason -- knowledge is power.

Databases provide a direct way of tapping the knowledge that already exists in an organization and providing it to interested parties with the click of a mouse. Sites that harness that knowledge and deliver it over the web are a powerful presence compared to their less sophisticated brethren.

Trying Your First Multi-Line Query

While simple SQL queries fit on a single line, some query commands require several lines. You can enter a multiple-line query in MySQL monitor by pressing Enter after each section of a command.

Because you must use the semicolon character at the end of a query, the MySQL monitor does not act on the query until you end a line with a semicolon. You can enter any number of lines, in order, and use a semicolon at the end of the last line to indicate the end of the query.

After you have entered a line, you can press the up-arrow key to return to that line and edit it futher. Press Enter again to continue to the next line.

If you make a mistake, the MySQL monitor also supports the \c (clear) command. Any command you have entered so far will be removed and you can start a new command on the next line.

As an example of using a multi-line query you can try the CREATE TABLE command below.

Make sure that you have already created the testdabase and type:

USE testdb;

Type each line and press enter after each:

CREATE TABLE address (
		name CHAR(100) NOT NULL,
		address CHAR(120),
		city CHAR(50),
		state CHAR(2)
		);

To see if the table was added, type:

SHOW TABLES;

SOURCE Command

Unfortunately if you make a mistake with the coding, you are forced to start at the beginning and begin typing all over again. One smarter and faster way to increase your work flow is to write all of your code in a single text document and have the MySQL monitor read all of the commands at one time. This makes your code easily editable and extremely mobile.

First you must open a text editor like, notepad and change the three letter extension to .sql.
Now in the MySQL monitor type: SOURCE and a space, then actually drag and drop the file from the desktop into the MySQL monitor and Press Enter. Oddly, you do not want a semicolon at the end of these SOURCE queries. If quotes appear, use the keyboard arrow keys to get to and delete them.

Create and Drop Databases

You can create a new database with the CREATE DATABASE command in MySQL. To use this command, simply specify a name for the new database.

CREATE DATABASE newdb;

Database Internals

When you create a database, the MySQL server creates a directory (folder) on the server's file system. When you install MySQL, you can choose the location of these directories. The MySQL server creates files for each of the tables you create in the database.

existing Databases

If you attempt to create a database using CREATE DATABASE, but a database with the name you specify already exists on the server, an error message is returned. To avoid this error, you can use the IF NOT EXISTS keywords with the CREATE command. If this is specified and the database already exists, no error is returned, no new database is created, and the existing database is unchanged.

CREATE DATABASE IF NOT EXISTS newdb;

DROP DATABASE Command

The DROP DATABASE command in SQL allows you to delete an existing database. This command immediately deletes the database; you are not asked to confirm this action! All tables within the database and the data they contain are deleted.

Normally, using DROP DATABASE will return an error if the database does not exist. You can avoid this error by adding the phrase IF EXISTS to the DROP DATABASE query

DROP DATABASE IF EXISTS newdb;

Create and Delete a Table, Index and Primary Key

Create and Delete a Simple Table

You can use the CREATE TABLE command from the MySQL monitor or other client to create a new table. As with the CREATE DATABASE command, this command normally returns an error if the table already exists. If you use the optional keywords IF NOT EXISTS, this error is suppressed. To create a table, you specify the columns, or fields, the table will use and their types.

You can use any character in a table name that is allowed in a filename on your operating system. The period (.) and slash (/) characters are not allowed. You need to choose a unique name for each table within a database, but separate databases can have tables with the same name.

Specify Columns

The list of columns, or fields for the table is included in parentheses in the CREATE TABLE command. Column types such as CHAR and DECIMAL can have parameters in parentheses; include these within the column list. Commas separate the column names.

The following command creates a simple table with one column:

Syntax of CREATE TABLE

CREATE TABLE tableName ( columnName1 datatype );

Example: CREATE a TABLE with one column

CREATE TABLE test_table ( myfield1 char(10) );

Because you will usually be specifying more than one column for a table, a CREATE TABLE statement often takes more than one line. For example:

Example: CREATE a TABLE with several columns

CREATE TABLE prices_table (
         	itemNo INT,
         	price DECIMAL(9,2),
         	quantity TINYINT UNSIGNED
    	);

INT specifies that the itemNo column can store any number value (integer) up to about 2 billion. Because the INT type is used for this column, each row of the table will be the same length. The price column stores a decimal number that can have up to nine digits, including two digits after the decimal. The quantity column stores an integer. The TINYINT type can store numbers from 0 to 255. You learn more about these field types in the section "Numeric Column Types" and "Text and Date Column Types."

Specify Column Attributes

You can specify one or more optional attributes for a column after its column type. For example, the NULL or NOT NULL attribute indicates wheater the column can store NULL values. NULL is a special value that indicates that nothing has been stored in the column.

The DEFAULT attribute specifies a default value for a column. For columns that allow NULL values, NULL is the default; otherwise the default is zero for numeric columns and a blank value for text columns. If you specify a value for the DEFAULT attribute it overrides this default.

Create Indexes

When you index a table, the server stores a list of values and pointers into the database to make it easier to search for values in the indexed columns. You can create a simple index with the INDEX keyword. You can specify an optional name for the index and one or more columns to index in parentheses.

Example: CREATE a TABLE with INDEX

CREATE TABLE clients (
         	name VARCHAR(20),
         	city VARCHAR(30),
         	INDEX index1 (city)
    	);

Create a Unique Index or Primary Key

You can use the keyword UNIQUE to create a unique index, also known as a key. A unique index is similar to a standard index, but each row's value for the indexed column must be unique.

As with INDEX, you can specify an optional name for the index and one or more columns to be indexed. When you index more than one column, only the combination of values of the columns needs to be unique. A table can have any number of unique indexes.

A primary key is similar to a unique index, but each table can have only one primary key. Additionally, the primary key must have the NOT NULL attribute. The primary key is used to uniquely identify each row of the table.

You can assign the primary key using the PRIMARY KEY keywords, similar to INDEX.

Syntax of CREATE PRIMARY KEY clause

CREATE TABLE tableName ( columnName1 datatype,
         	[ columnName2 datatype, ]
         	PRIMARY KEY (existingColumnName)
    		);

Example of CREATE PRIMARY KEY clause

CREATE TABLE phonelist (
         	username VARCHAR(20) NOT NULL,
         	phone BIGINT,
         	UNIQUE phoneindex (phone),
         	PRIMARY KEY (username)
    	);

Alternately, you can specify the PRIMARY KEY attribute for one of the columns.

Example of CREATE PRIMARY KEY attribute

CREATE TABLE phonelist (
         	username VARCHAR(20) PRIMARY KEY,
         	phone BIGINT,
         	UNIQUE phoneindex (phone)
    	     );

Table Types

You actually have a choice of the way your database stores data behind the scenes. You can include the TYPE keyword within the CREATE TABLE command to specify one of MySQL's supported table types:

MyISAM: Default table type for MySQL. These tables are not transaction safe but good for all non transactions.
ISAMM
: standard table type that MyISAM tables are based on. Larger and less efficient.
Heap: Stored in RAM, fast, not reliable for permanent storage.
BDB: support transactions.
InnoDB: industrial-strength database system supporting transactions. We will use these for our tables.

We should really be using InnoDB tables, so make sure to add this type to each table when you create it.

Example of TABLE TYPE clause

CREATE TABLE test_table (
    		field1 INT
    		) TYPE = InnoDB;

Table Options

You can also specify one or more options when creating a table.

AUTO_INCREMENT: As you add rows to the table, the database will automatically add the next highest number value into the AUTO_INCREMENT field. Used in table creation to define an initial value other than 1.
AVG_ROW_LENGTH: An estimate of the row length when variable length columns are used.
COMMENT: optional description of the table, up to 60 characters
MAX_ROWS: maximum number of rows you will store in the table
MIN_ROWS: minimum number of rows you will store in the table

To specify an option, include it at the end of the column specifications for the table followed by an = sign and its value. Separate multiple options with spaces. Use quotation marks around text options such as COMMENT. Note that table types, such as ISAM should not be quoted.

CREATE TABLE students (
         	stname VARCHAR(100)
    		) COMMENT = 'student names';

Deleting a Table

You can use the DROP TABLE command in MySQL to delete an existing table. This immediately deletes the table, whether it currently contains any data or not. Because it deletes without confirmation, use this command with caution.

The DROP TABLE command will return an error if the table you attempt to delete does not exist. You can avoid this error by adding the IF EXISTS phrase to the DROP TABLE command.

Syntax of DROP TABLE

DROP TABLE tableName;

Example of DROP TABLE

DROP TABLE temptable;

Show Database and Table Information

SHOW DATABASE Command

Because the Drop Database command is drastic, use it carefully. One way to be sure you are deleting the correct database is to first use the SHOW DATABASE and SHOW TABLES commands.

Lists all databases:

Syntax of SHOW DATABASES

SHOW DATABASES;

You can also use the LIKE keyword to show only databases whose names match a pattern. The following shows a list of all databases that begin with the letter t:

SHOW DATABASES LIKE 't%';

The LIKE clause supports wildcard character. These are useful when you are unsure of the exact name of the database you are looking for, or when you need to list all of the databases that match a certain keyword. The first wildcard, underscore (_), matches any character. The following command would list the testdb database and any others that contain testd followed by one letter:

SHOW DATABASES LIKE 'testd_';

The second wildcard is the percent (%) character. This matches any string of characters, or no characters. The following command would list the testdb database along with any other with a name containing 'test':

SHOW DATABASES LIKE '%test%';

Because this command includes wildcards at the beginning and end of the database name, it looks for the characters test at any location. This example would match databases named testdata, datatest, or newtest23.

SELECTING A DATABASE

Before you can work with tables in a database, you must select the database. You can do this with the USE command. To use the command, type USE followed by the database name and a semicolon to end the statement. For example, the following command selects the testdb database:

Syntax of USE

USE databaseName;

Example of USE

USE testdb;

After you have selected a database with the USE command, it is used as the default database for any queries you make. If you refer to a table in a subsequent query, the MySQL server looks for that table in the database you previously selected.

As an alternative to the USE command, you can also specify a database name and table name when you perform a query that involves a table. You do this by separating the database name from the table name using a period. For example, testdb.address refers to the address table within the testdb database.

After you have selected a database with the USE command, you can use commands like CREATE TABLE or SELECT to work with the current database. This database remains as the default until you specify another database with USE or until you exit from the MYSQL monitor.

SHOW TABLE INFORMATION

You can use several SHOW commands to find out information about the tables in the current database.

Lists all tables in a specific database:

Syntax of SHOW TABLES

SHOW TABLES FROM databaseName;

Example of SHOW TABLES

SHOW TABLES FROM tempdb;

Note that if you have used a 'USE database' command recently, you do not need to mention the database in your show tables statement

Example of SHOW TABLES after a USE statement

SHOW TABLES;

To list all tables, including the number of rows stored in each table, use SHOW TABLE STATUS:

Syntax of SHOW TABLE STATUS

SHOW TABLE STATUS FROM databaseName;

Example of SHOW TABLE STATUS

SHOW TABLE STATUS FROM tempdb;

You can optionally use the LIKE keyword to show only tables matching a string. Like the SHOW DATABASES command, you can use the wildcards ( _ and %).

The SHOW COLUMNS command lists the columns for a table with detailed information about each. To use this command, you specify a table name with the FROM keyword:

Syntax of SHOW COLUMNS

SHOW COLUMNS FROM tableName;

Example of SHOW COLUMNS

SHOW COLUMNS FROM temptable;

You can also use the LIKE keyword with SHOW COLUMNS. In this case, LIKE allows you to display only the column names that match the string you specify. You can use wildcards to specify a partial column name.

SHOW COLUMNS FROM address LIKE 'n%';

A final command is SHOW INDEX. This command displays information about the indexes and keys used with the table you specify with the FROM keyword. Here is a simple example of the command:

Syntax of SHOW INDEX

SHOW INDEX FROM tableName;

Example of SHOW INDEX

SHOW INDEX FROM temptable;

Numeric Columns

Some of the most important data you can store in a database is in the form of numbers: monetary figures, quantities, sequence numbers, and so on. MySQL includes a variety of standard column types for the storage of numeric values. You can specify a display size in parentheses for each.

INTEGER or INT: a round number with no decimal portion. You can optionally specify the UNSIGNED attribute for integers, which disallows negative numbers. An INTEGER column can store values from -2,147,483,658 to 2,147,483,647. You can optionally specify a display length for an INTEGER column in parentheses. This length will be used to pad small values with spaces. If you specify the ZEROFILL attribute, zeroes will be used instead of spaces.

CREATE TABLE inventory(
    	item INTEGER,
    	quantity INT ZEROFILL );

TINYINT: column type is a smaller version of INTEGER. It is limited to values -128 to 127. (If you specify the UNSIGNED keyword, values can range from 0 to 255).
SMALLINT: column type is another version of INTEGER. This type stores each value in a range from -32,768 to 32,767. (If you specify the UNSIGNED keyword, values can range from 0 to 65,535.)
MEDIUMINT: column type is another version of INTEGER. This type stores each value in a range from -8,388,608 to 8,388,607. (If you specify the UNSIGNED keyword, values can range from 0 to 16,777,216.)
BIGINT: Larger than INTEGER, and stores values in 8 bytes (up to 4,000,000,000,000,000,000, roughly).
GOLDEN RULE: use whichever integer datatype is the maximum size (or higher) of numbers you need to store in the column. Using larger types than you need is an unnecessary waste of space, but you donœt want number values getting cut off because the datatype canœt accommodate them. For example, if the datatype is TINYINT and someone tries to enter the number 1000, the value would be reduced to 255.
FLOAT: This allows you to store floating-point numbers. The MySQL server normally can store numbers as large as 39 digits long in a FLOAT column. As with integer types, you can specify UNSIGNED to double the range and limit values to positive numbers. You can define two parameters in parentheses when defining a FLOAT column: the first is the number of bits used to store the number up to 53. The second parameter is the number of digits that should follow the decimal point. Both parameters are optional.
REAL or DOUBLE: You can use REAL columns to store a FLOAT of a bigger size. It creates an eight-byte floating-point.
DECIMAL or DEC: Decimal columns are used to store numbers with a fixed decimal position. When you create a DECIMAL column, you specify two values in parentheses: the total number of digits, and the number of digits after the decimal point. These values are actually stored as text. These values are not rounded, so you can expect them to be accurate.

Column Attributes

UNSIGNED: only positive values
ZEROFILL: pad with zeros instead of spaces
DEFAULT: give a default value.
AUTO_INCREMENT: means that when you add rows to the table, you do not need to tell the database what value to put in an AUTO_INCREMENT column; it just chooses the next highest number that's never been used in the column.
NULL: same as zero, but it means "no value"
NOT NULL: a value must be created.

Here is an example of some of the column attributes in use.

CREATE TABLE stock (
         	item INTEGER UNSIGNED,
         	price DECIMAL(5,2),
         	quantity INTEGER DEFAULT 1
    		);

Text and Date Columns

Text and Date Types

While data is often in the form of numbers, MySQL also includes a variety of column types for storing non-numeric data. This includes text columns that store a length of text, also known as a string, and several column types devoted to the storage of dates and times.

Text Column Types

CHAR: Basic text column type. A CHAR column can store up to 255 characters of text. You can specify the number of characters allowed in parentheses.
VARCHAR: Like CHAR, a VARCHAR column can store up to 255 characters of text. You must specify the number of characters allowed in parentheses. Unlike CHAR, VARCHAR columns have a variable length. Shorter values will use less space in the table than longer values. The disadvantage of variable-length columns is that they are harder for the MySQL server to work with, and consequently slower. You must use either VARCHARs or CHAR types in a table; you can't use both.
TEXT: allows you to store a larger amount of text. You do not need to specify a maximum length, and each item can range from zero to 65,535 characters in length. Because TEXT columns have a variable length, longer values will use more space.
TINYTEXT: Can store up to 255 bytes.
MEDIUMTEXT: Can store up to 16MB.
LONGTEXT: Can store up to 4GB of text.
BLOB: They are the same as TEXT, but can store binary data. You can use these columns to store images, data files, or anything that is not simple text.
TINYBLOB: can store up to 255 bytes
MEDIUMBLOB: can store up to 16MB.
LONGBLOB: can store up to 4GB.
DATETIME: This stores the year, month, day of month, hours, minutes, and seconds.
DATE: It stores only a year, month, and day of month.
TIME: Stores only the time of day in hours, minutes, and seconds.
YEAR: Simply stores the year.
TIMESTAMP: stores a date and time, but the values in this column will be updated automatically by the database. TIMESTAMP columns can store any date and time from the beginning of 1970 to the end of 2037, with a different unique value for each second. You can specify a display width up to 14 characters when you create the table. This does not affect the values the column can store.

Here is an example of some of the text datatypes in use.

CREATE TABLE historical_events (
         	event_name CHAR(75),
         	description TEXT,
         	date_and_time DATETIME
    	);

Sets and Enumerations

Sets and enumerations are different from normal text columns in that they are limited to a specific list of values. You can use these columns whenever you need to assign categories to the items in a table. ENUM allows a choice of one value from a list...

Syntax of ENUM datatype

CREATE TABLE tableName (
       		columnName ENUM(value1, value2 [, etc])
    	     );

For example, suppose you were using a database table to catalog music recordings. You could use an ENUM column to let user choose a category of music from a list of certain ones:

Example: ENUM datatype

CREATE TABLE recordings_table (
                category ENUM("rock", "pop", "blues", "country", "dance")
    	     );

The one potential disadvantage of an ENUM column is that each item can have only one value. Anything that fits more than one category could only be listed in one category. This is where SET columns are useful.

With a SET column, each item can be assigned any number of values from the list you specify when you create the table. You can look at a SET column as a list of flags, each of which can be assigned or not assigned to an item. In the music catalog example, a SET column would be ideal to store the media the music is available on:

CREATE TABLE recordings_table (
         	media SET("CD", "DVD", "LP", "Cassette")
    	     );

Although you could use separate columns to achieve the same effect, SET columns are convenient because they can be assigned values as a single unit. They are also stored on the server in a highly efficient way, using only one bit per item, and are thus especially useful when you are storing many rows of data in a table.

Because ENUM and SET columns can have a large number of possible values, you may need to split the column specification into two or more lines when you create the table.

The values for an ENUM column are actually stored as integers. A value of 1 represents the first possible value, 2 represents the second possible value, and so on. ENUM columns can have a maximum of 5,535 total values available. ENUM values use one byte of storage if there are less than 256 possible values and two bytes if there are 256 or more.

If you need to change the definition, doing so does not change the values stored for existing data. If you add a value at the beginning of the list, the numeric values for the entire list will change. You can safely add values at the end of the list, but the best strategy is to determine the possible values in advance and include them when you first create the table.

Example of SET AND ENUM

CREATE TABLE music (
         	title VARCHAR(100),
         	category ENUM ("rock", "pop", "blues", "country", "dance"),
         	media SET("CD", "DVD", "LP", "Cassette")
    	     );

Create Tables