18.1 Creating databases and tables with SQL

18.1 Creating databases and tables with SQL

18.1.1 Designing databases with tables

In this section, we will show you how to use MySQL databases to design tables on the Web. Some information regarding the data type differences among Microsoft Access, Oracle, and MySQL is also provided. In general the MySQL package is assumed to be installed on a remote machine called www.pwt-ex.com. Inside the MySQL server, we also assume a database called personnel and a table called people in early sections of this chapter.

You can refer to section 17.2 of Chapter 17 to see how to install MySQL, connect to the MySQL server, and create databases. Once you have registered a database such as personnel on your local machine, you can control it using the Web pages developed in Chapter 17. In particular, we use ex17-12.htm and ex17-12.pl to demonstrate and evaluate SQL statements. As a starting point, this example is referred to as ex18-01.htm and ex18-01.pl here.

The page ex18-01.htm is a basic Web page using Perl to establish communications between you and the MySQL databases installed on www.pwt-ex.com. Although the implementation is simple, it is a database application on the Web using ODBC. For example, you can enter the

SHOW DATABASES;

statement to see all databases on www.pwt-ex.com available to you. Some screen shots to see this in action are shown in Figs 18.1 and 18.2.

In general, we will use the examples ex18-01.htm and ex18-01.pl as our SQL test programs. In fact, you can use any SQL test program including the MySQL client mysql> to test and evaluate the examples. If your SQL test program can display something similar to Fig. 18.2, you should have no problems in evaluating all the examples in the first three sections of this chapter.

Although MySQL commands are not case sensitive, the user-defined names are. To avoid confusion and improve readability, capital letters are used for all system keywords and lower case for defined names.

Since you can have a number of databases in MySQL, if you are using the MySQL client mysql>, you must select your active database before starting your query or operation. To select a database, the keyword USE is used. For example, the following USE command selects personnel as the current database:

USE personnel;

Since ODBC and DBI already have registered the default database, the statement above is not necessary.

The real contents of a database are tables. Before any data can get into a database, you will need to know how to create a table to store them. One simple way to create a table is to use the CREATE TABLE statement. Before a more formal syntax or definition of this statement is given, consider a simple example as follows:

Example: ex18-01.sql - Creating A Table Called expense

 1: CREATE TABLE expense (
 2:   name CHAR(30),
 3:   amount INTEGER,
 4:   account INTEGER
 5: );

This is a multiline example. You can spread your SQL command or statement over multiple lines and type them into the Web page. Once a semi-colon is encountered, the system will execute the entire SQL statement as a single command.

This example creates a table called expense with three fields, i.e., name, amount, and account. The name is a string of 30 characters. The amount and account are of integer type.

The SHOW TABLES; statement can be used to display all tables in the database personnel. If you enter this command in the SQL test program, you will see that the table expense exists as shown in Fig. 18.3. You can use the DESCRIBE expense; statement to see the internal structure of the table (Fig. 18.4).

Figure 18.4 shows the structure or data types of the table expense. In many cases, this command is an effective way to examine the table that you created or a table that you don't know much about. To remove the table from the database, the DROP TABLE command can be used. For example, the following command removes the table people from the database:

DROP TABLE people;

After removing this table, let's see how to re-create it. In general, the SQL statement to create a table is

CREATE TABLE table_name
(
  field1 datatype,
  field2 datatype,
  ...  ...
  fieldn datatype
);

The fields represent the names for each column of the table. Unfortunately, definitions for data types may be different among database vendors. SQL itself has no direct specifications or implementation on data types. Some of the frequently used data types on Access, Oracle, and MySQL are listed in Table 18.1.

Table 18.1. Data types used in Access, Oracle, and MySQL

Access	Oracle	MySQL	Description
TEXT	CHAR(N)	CHAR(N) VARCHAR(N)	CHAR(N) is a fixed length string with N characters VARCHAR(N) is a string with variable length (max. N characters)
INTEGER	NUMBER	SMALLINT INT(N)	The range of SMALLINT is 32768 to 32767. The range of INT(N) is from 2147483648 to 2147483647
SINGLE	NUMBER	SINGLE	Single-precision floating point number. Allowable values are 3.402823466E+38 to 1.175494351E-38
DOUBLE	NUMBER	DOUBLE	Double-precision floating point number. Allowable values are -1.7976931348623157E+308 to 1.7976931348623157E+308
DATE/TIME	DATE	DATE DATETIME	The DATE format is '00000000'. The DATETIME format is '00000000 00:00:00'
MEMO	LONG	LONGTEXT	A BLOCK or TEXT column with a maximum length of 4294967295 (232 1) characters

From this table, we can see that the data types in MySQL are more disciplined and that other systems are more flexible. For example, the data type NUMBER in Oracle systems can represent numeric, positive, or negative fixed or floating point data.

To design a table suitable for your needs is extremely important to the success of your application. It is recommended that you should always start from the name and proper data type for your fields. Also keep a record of your design with comments.

For example, to describe the staff of a company, you might start with a table called people. The first field of this table is usually a positive small integer called id to uniquely identify each employee of the company. This id cannot be empty and should be automatically incremented by 1. You may also want to have name, birth, sex, location, and salary fields inside the table. Table 18.2 is a simple design.

Table 18.2. Data type of the table people

Field name	Structure (data type)	Comments
id	SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT	Automatic increment by 1 and cannot be NULL This field is the primary key
name	VARCHAR(30)	Name of the person
birth	DATE	Birthday of the person
sex	CHAR(1)	Female (F) or male (M)
location	VARCHAR(30)	Traveling location
salary	INT(6)	Annual salary

Put all your data requirements into data types and describe them by SQL keywords. Since data types are not the same among database vendors, you may need to consult your database manual for further information. Based on this information, you can define the table people with the SQL statements below:

Example: ex18-02.sql - Creating A Database Table Called people

 1:  CREATE TABLE people (
 2:    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 3:    name VARCHAR(30),
 4:    birth DATE,
 5:    sex CHAR(1),
 6:    location VARCHAR(30),
 7:    salary INT(6),
 8:    PRIMARY KEY (id)
 9:  );

This is a long SQL statement split into multiple line input. The result and data types of this table are shown in the screen shots in Figs 18.5 and 18.6.

Now you have a database table, the next question is: "How can we load data into the table?"

18.1.2 Input/output data from database tables

Different database products may have different ways to load data into a table. For example, Access has a number of visualization tools for data loading. Whether your tables were created with Microsoft Word or Excel, Access supports all of them. Apart from proprietary utilities to convert databases and tables from other vendors, most database products support text files for import and export. That means you can load or dump text files from and to tables. We will introduce a text file method to load data into a MySQL table in this section.

By using the MySQL function LOAD DATA, a text file can be read into a table provided:

• Each line of the file represents a record of the table.

• Field data are in the right order as described in the table and are separated by a tab.

Suppose you have created a text file called people.dat with your favorite editor in the exact format as described above. A section of the text file is

Listing: 18-01.txt - Text Data File (people.dat)

    NULL          Michael      19501218  M    London 30000
    NULL          Mary         19800622  F    Paris  23000
    ...           ...          ...              ...    ...          ...
    NULL          Anne         19761102  F    London 28000

You can use the statement

LOAD DATA INFILE "people.dat" INTO TABLE people;

to load the file into the table. If the fields of the data file were delimited by commas, you can use the following statement to read it in:

Example: ex18-03.sql - Load Data Into A File

1: LOAD DATA INFILE 'people.dat'
2: INTO TABLE people
3: FIELDS TERMINATED BY ',';

The LOAD DATA INFILE is a useful feature in MySQL. It can be used to read files obtained from external sources. For example, a database file in dBASE format will have fields separated by commas and enclosed in double quotes. To read such a file, you can issue the command

Example: ex18-04.sql - Load Data Into A File With Delimiter

1: LOAD DATA INFILE 'people.dat'
2: INTO TABLE people
3: FIELDS TERMINATED BY ','
4: ENCLOSED BY '"'
5: LINES TERMINATED BY '\n';

For Microsoft systems, every line of a text file contains a carriage return and a line feed to represent the end of a line. That is, if you create the data file people.dat on a Windows platform, you should use the following statement to read the file in:

Example: ex18-05.sql - Loading A File With DOS/Windows Format

 1:  LOAD DATA INFILE 'people.dat'
 2:  INTO TABLE people
 3:  LINES TERMINATED BY '\r\n';

Type this statement into the page as in Fig. 18.7. The result is shown in Fig. 18.8.

If the execution of the query is successful, a confirmation message is displayed at the top of the returned page. Since this query has no returned data, the total entries field is zero.

The complement of LOAD DATA is to output data into a text file. Outputting data into a text file is not just a good way to communicate with other databases; the data can be used as a backup or for further processing too. To output data from a table into a text file, the statement SELECT*INTO OUTFILE is used. For example,

Example: ex18-06.sql - Output A Text File

1:  SELECT * INTO OUTFILE 'data.txt'
2:  FROM people;

This statement will output all data from the table people into a text file called data.txt. If you want fields to be separated by a comma or a line terminated by a new line, you can use the following example:

Example: ex18-07.sql - Output A Text File With Format

 1:  SELECT * INTO OUTFILE 'data.txt'
 2:  FIELDS TERMINATED BY ','
 3:  LINES TERMINATED BY '\n'
 4:  FROM people;

If you are using a Windows system, you may need to add the carriage return together with the new line symbol (i.e., "\r\n").

Example: ex18-08.sql - Output A Text File In DOS/Windows Format

 1: SELECT * INTO OUTFILE 'data.txt'
 2: FIELDS TERMINATED BY ','
 3: LINES TERMINATED BY '\r\n'
 4: FROM people;

MySQL also has a script mode or batch mode to simplify statement typing. For example, suppose you have created the database personnel with MySQL and have no table in it. You can type the following commands into a file called personnel.txt:

Example: ex18-09.sql - Batch Mode File For MySQL

 1:  USE personnel;
 2:  CREATE TABLE people (
 3:    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 4:    name VARCHAR(30),
 5:    birth DATE,
 6:    sex CHAR(1),
 7:    location VARCHAR(30),
 8:    salary int(6),
 9:    PRIMARY KEY (id)
10:  );
11:  LOAD DATA INFILE "people.dat" INTO TABLE people
12:  LINES TERMINATED BY '\r\n';

To execute this file, you type the following command from a shell or DOS window:

shell> mysql < personnel.txt

If you need to supply a user name and password for connection to a server, you can use the command

shell> mysql h www.pwt-ex.com -u johnsmith p < personnel.txt
Enter Password: ****

The client program mysql in this case will log in to the remote server with user name and password. If everything is all right, it will execute the statements inside the script file.

Getting input/output text files from tables is a popular technique to establish a dialog among different databases. The text files, in many cases, are convenient for further data processing.

18.1.3 Manipulating table data with SQL

Once the data are in the table, you can use the following SQL statements to manipulate them:

• The INSERT statement To insert a record to the table.

• The UPDATE statement To change a record.

• The DELETE statement To delete a record from the table.

There are two versions of the INSERT statement.

• INSERT INTO table_name (field1, field2, ..., fieldN)

  
  

  VALUES (value1, value2, ..., valueN)
  

  
  

• INSERT INTO table_name (field1, field2, ..., fieldN)

  
  

  SELECT (field1, field2, ..., fieldN)
  FROM table_name2
  WHERE search_condition
  

  
  

The INSERT...VALUE statement enters one record at a time into a table. It is designed for small operations to manipulate a table. For example, if you want to add another person called "Jack" into the people table, you can enter

Example: ex18-10.sql - Insert A Record

 1:  INSERT INTO people
 2:  VALUES
 3:  (NULL,'Jack','19680708','M','Tokyo',32000);

The insertion result is shown in Figs 18.9 and 18.10.

When using the SELECT...VALUE statement, the data type, size, and location of the inserted fields must match the corresponding fields declared in the table. Unless you use programming techniques to generate this SQL statement, the limitation is that you can only add one record at a time. Also, this statement cannot be used to build a table from other tables.

To copy information from one table into another or to build a new table from information in an existing one is common practice in SQL. For this purpose the INSERT…SELECT statement can be used. Suppose you want to create a table to project a salary increase of 7% for all your staff next year. You may use the following SQL statements:

Example: ex18-11.sql - Populate A Table From An Existing Table

 1:  CREATE TABLE salary (
 2:    name VARCHAR(30),
 3:    sex CHAR(1),
 4:    salary INT(6),
 5:    new_salary INT(6)
 6:  );
 7:
 8:  INSERT INTO salary
 9:  SELECT name, sex, salary, (salary * 1.07) as new_salary
10:  FROM people;
11:

This example contains two SQL statements. You need to enter the statements one by one into the Web page. The first SQL statement defined in lines 16 is to create a new table called salary. Once this table is created, you can call the second statement to fill it. The first three fields, name, sex, and salary are selected from the existing table people. The SQL clause

(salary * 1.07) as new_salary

generates the fourth field called new_salary and is a 7% rise on the previous field salary. A screen shot is shown in Fig. 18.11.

To change the values of existing records, the SQL statement UPDATE is used. The general format or syntax is

UPDATE table_name
SET field1 = value1, field2=value2, ... , fieldN=valueN
WHERE search_condition

A simple application for UPDATE is to change the data of a table. For example, the following statements change the names and salary of Jack (Fig. 18.12):

Example: ex18-12.sql - Update The Contents Of A Table

 1:  UPDATE people
 2:  SET name='Jo', salary='33000'
 3:  WHERE name='Jack';

As another example, let's use UPDATE to construct a new salary table. The salary table can also be generated by the following three steps:

Step 1. Create a new table called salary from the fields name, sex, and salary of the table people.

Step 2. Alter the table by adding a new field called new_salary.

Step 3. Update the new_salary fields by (salary*1.07).

In terms of SQL, these three steps can be accomplished by the following three SQL statements:

Example: ex18-13.sql - Using ALTER TABLE And UPDATE

 1:  CREATE TABLE salary
 2:  SELECT name, sex, salary
 3:  FROM people;
 4:
 5:  ALTER TABLE salary ADD new_salary INT(6);
 6:
 7:  UPDATE salary
 8:  set new_salary = salary * 1.07;

The first statement (lines 13) creates a table salary. The three fields name, sex, and salary are copied to the new table from an existing table people and carry the same data type. Creating a table this way provides a handy method to populate the data fields or to back up a table.

To add a column or field to the new table, the second statement (line 5) is used. The command

ALTER TABLE salary ADD new_salary INT(6);

will alter the table structure by adding a new field called new_salary with data type INT(6). In addition to the ADD keyword, you can also use the MODIFY keyword to modify the data type of an existing field.

For the last step, you can use the UPDATE statement to populate the new_salary field. For example, you can use:

UPDATE salary
set new_salary = salary * 1.07;

to generate the data for new_salary as the salary field increased by 7%.

As your experience with SQL grows, you will find these statements particularly useful for preparing and backing up your tables for database applications.

Finally, to remove a record from a table, you can use the keyword DELETE. For example,

Example: ex18-14.sql - Delete Table Element

 1:  DELETE FROM salary
 2:  WHERE new_salary < 25000;

This example will delete all the entries (if any) from the table salary where the salary is less than 25,000 (Fig. 18.13).

Now you have some experience on SQL tables with data, it's time to consider some SQL queries. Most of the SQL queries introduced in section 18.2 are core queries to the user. Behind the XHTML interface or scripting, they are the driving force for many database applications on the Web.