Sign in to follow this  
Followers 0
JetX

Learn Database & SQL Query

9 posts in this topic

Internet Research 'bout Database: hxxp://www.[nofileshares].com/file/79975205/1fb893b4/DB_Internet_Research.html

Presentation 1 to 4: hxxp://www.[nofileshares].com/file/79972578/b6d057f2/Presentation1to4.html

Section 1 to 4: hxxp://www.[nofileshares].com/file/79972804/f8ff1e4d/Section1to4.html

section 1: Creating Database, Tables and Indexes

Section 2: Inserting, Deleting and Updating Data

Section 3: Querying MySQL

Section 4: Interfacing MySQL w/ PHP

PHP code: hxxp://www.[nofileshares].com/file/79972873/29da1d29/MyPHP.html

Download xampp here: hxxp://www.apachefriends.org/en/xampp.html

 

I don't have any plan to start this topic but as I was planning to reply on this post http://www.sadikhov.com/forum/index.php?showtopic=151044&st=0, then I realized that my reply was too long, so decided to delete my reply and start new topic.

Edited by JetX
0

Share this post


Link to post
Share on other sites

CREATING DATABASE, TABLES AND INDEXES

 

Case Sensitivity and Identifiers

• Database names have the same case sensitivity as directories in your operating system. Table names follow the same rules as filenames. Everything else is case insensitive.

• All identifiers except aliases can be up to 64 characters long. Aliases can be up to 255 characters long.

• Identifiers can contain most characters, but database names may not contain /, \, or . and table names cannot contain . or /.

• You can use reserved words for identifiers as long as you put them in quotes.

 

Creating a Database

• create database dbname; creates a database.

• use database dbname; selects a database for use.

 

Creating Tables

To create the tables in the employee database, we use the CREATE TABLE SQL statement. The usual form of this statement is

create table tablename ( table definition ) [type=table_type];

That is, we begin with the words create table, followed by the name we would like the table to have, followed by a set of column definitions. At the end of the statement, we can optionally specify the storage engine type we would like to use.

 

Table Creation Example

We will look at an example of table creation to illustrate this point. Listing 1 shows a set of SQL statements that can be used to create the employee database.

 

Listing 1 SQL to Create the Employee Database

 

drop database if exists employee;

create database employee;

 

use employee;

 

create table department

(

departmentID int not null auto_increment primary key,

name varchar(30)

) type=InnoDB;

 

create table employee

(

employeeID int not null auto_increment primary key,

name varchar(80),

job varchar(30),

departmentID int not null references department(departmentID)

) type=InnoDB;

 

create table employeeSkills

(

employeeID int not null references employee(employeeID),

skill varchar(15) not null,

primary key (employeeID, skill)

) type=InnoDB;

 

create table client

(

clientID int not null auto_increment primary key,

name varchar(40),

address varchar(100),

contactPerson varchar(80),

contactNumber char(12)

) type=InnoDB;

 

create table assignment

(

clientID int not null references client(clientID),

employeeID int not null references employee(employeeID),

workdate date not null,

hours float,

primary key (clientID, employeeID, workdate)

) type=InnoDB;

Let's go though the SQL statements in this file one by one.

We begin with

 

drop database if exists employee;

This statement checks whether an employee database already exists and deletes it if it does, cleaning the slate if you like. This is not strictly necessary and could even be dangerous, but we do it here to make sure that this database creation script should work, even if you have already been experimenting with an employee database.

Note that if you use MySQL in a hosted environment, your hosting company can disable the drop database command. In this case, simply eliminate that line of the script (but make sure that there isn't a database called employee).

We then create the database and select it for use, as we have seen already:

 

create database employee;

use employee;

Now, we begin creating tables inside this database. We begin by creating the department table, as follows:

 

create table department

(

departmentID int not null auto_increment primary key,

name varchar(20)

) type=InnoDB;

This table has two columns, departmentID, which is the primary key, and the department name. To declare the columns in the table, we give a comma-separated list of the column declarations enclosed in parentheses. Note that attributes of a column do not need to be comma separated—only the columns themselves do.

This is our first multiline SQL statement. Whitespace is not important in SQL, so we can lay out our queries in any way we like. Typically, with CREATE statements, you tend to put one item on each line to increase readability. The SQL interpreter will not try to interpret your statement until you have typed the final semicolon (;) and pressed Enter. (You can also choose to end your statements with \g, but the semicolon is far more commonly used.)

In this table, we are declaring two columns. Each column declaration begins with the name of the column, which is followed by information about the type of that column. Look at the second column first in this example because it's a little easier to understand. The declaration

 

name varchar(20)

tells us that the column is called name and that its type is varchar(20). The varchar type is a variable-length string, in this case up to 20 characters. We could also have used char, which is a fixed-length string. Choosing varchar or char does not make a difference in terms of using the data, just in how the data is stored in memory. A varchar(20) takes up only as much room as the number of characters stored in it, whereas a char(20) is always 20 characters wide, regardless of what is stored in it.

Now, look back at the first column definition. It looks like this:

 

departmentID int not null auto_increment primary key,

The name of this column is departmentID, and it is of type int (integer). This is a unique number that we will use to identify each department in the company.

After the type, there is some further information about the column.

First, we have specified that this column is not null—in other words, for every row in this table, this column must have a value in it.

Secondly, we have specified that this column is an auto_increment column. This is a nice feature in MySQL. When we insert data into this table, if we do not specify a department number, MySQL will allocate a unique number that will be the next number in the auto_increment sequence. This makes life easy for us.

Finally, we have specified that this column is to be the primary key for this table. If the primary key consists of a single column, we can specify it like this. For multicolumn primary keys, we must use a different approach, which we will look at in a moment.

That's the table definition. Now, look at the very end of the SQL statement. After the end parenthesis, you will see the following line:

 

type=InnoDB

This specifies that this table should use the InnoDB storage engine. If you look through the table definitions, you will see that in this case, we have declared all the tables as InnoDB tables.

What does this mean? MySQL supports various storage engines, let me know if I need to discuss all of them." The default type is MyISAM. If we want to use MyISAM tables, we don't need to add the type clause at the end of the create database statement.

In this case, we are using InnoDB because we are going to work through some examples using foreign keys. The InnoDB storage engine supports foreign keys and transactions, whereas the MyISAM table type does not. The MyISAM table type is often faster than the InnoDB table type. We need to decide what type is best for each table.

We could make the tables of different types having, for example, some InnoDB tables and some MyISAM tables (and perhaps some of the other types, if needed), but we are keeping it simple in this example and using InnoDB for all our tables.

Look now at the second create table statement:

 

create table employee

(

employeeID int not null auto_increment primary key,

name varchar(80),

job varchar(15),

departmentID int not null references department(departmentID)

) type=InnoDB;

There is only one new piece of syntax in this statement. The last column in the employee table is the id of the department for which the employees work. This is a foreign key. We declare this in the table definition by adding the references clause as follows:

 

departmentID int not null references department(departmentID)

This tells us that the departmentID in the employee table should be referenced back to the departmentID column in the department table.

Note that we can use this foreign key syntax because the employee table is an InnoDB table. When we use MyISAM tables, we cannot use foreign keys. Foreign keys in MyISAM tables are planned for a future version of MySQL, version 5.1 according to the development schedule. (I don’t have time to check what version of MySQL they used here at STI).

Now, look at the third create table statement:

 

create table employeeSkills

(

employeeID int not null references employee(employeeID),

skill varchar(15) not null,

primary key (employeeID, skill)

) type=InnoDB;

Again, in this table, we have a foreign key, in this case the employeeID. The interesting thing about this table definition is that this table has a two-column primary key. You can see that we declare the two columns in the table, employeeID and skill, and then declare the primary key separately with the following line:

 

primary key (employeeID, skill)

The other table definitions don't contain any new syntax, so we won't go through them in detail. You will note that we have used a couple of other data types: in the assignment table, the number of hours is a float, or floating-point number, and the workdate is of type date.

You can check whether the tables in your database have been set up correctly using the command

 

show tables;

You should get the following output:

 

+--------------------+

| Tables_in_employee |

+--------------------+

| assignment |

| client |

| department |

| employee |

| employeeSkills |

+--------------------+

You can get more information about the structure of each table by using the describe command, for example,

 

describe department;

This should give you something like the following output:

 

+---------------+-------------+-------------------+------+-----+---------+----------------+

| Field | Type | Collation | Null | Key | Default | Extra |

+---------------+-------------+-------------------+------+-----+---------+----------------+

| departmentID | int(11) | binary | | PRI | NULL | auto_increment |

| name | varchar(20) | latin1_swedish_ci | YES | | NULL | ______ |

+---------------+-------------+-------------------+------+-----+---------+----------------+

You might want to check the other tables at this point.

 

 

CREATE TABLE Statement

Now that we've looked at an example, let's go over the complete syntax for the CREATE TABLE statement. The MySQL manual tells us that the general form of this statement is as follows:

 

CREATE [TEMPORARY] TABLE [iF NOT EXISTS] tbl_name [(create_definition,...)]

[table_options] [select_statement]

 

or

 

CREATE [TEMPORARY] TABLE [iF NOT EXISTS] tbl_name LIKE old_table_name;

 

create_definition:

col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]

[PRIMARY KEY] [reference_definition]

or PRIMARY KEY (index_col_name,...)

or KEY [index_name] (index_col_name,...)

or INDEX [index_name] (index_col_name,...)

or UNIQUE [iNDEX] [index_name] (index_col_name,...)

or FULLTEXT [iNDEX] [index_name] (index_col_name,...)

or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)

[reference_definition]

or CHECK (expr)

Let's go through the various options we can see in this general form.

The TEMPORARY keyword is used to create a table that will be visible only in your current database session, and that will be automatically deleted when your connection is closed.

We can use the IF NOT EXISTS clause, logically enough, to create a table only if there is not already a table with this table name.

We can use the LIKE old_table_name clause to create a new table with the same schema as old_table_name.

Inside the parentheses of the CREATE TABLE statement, we declare the columns that we want, their types, and any other information about the structure of the table. The simplest column definition is a column name followed by a column type. In the next section of this chapter, we will look at valid column types.

Other options we can add to each column declaration include the following:

• We can declare each column as NOT NULL or NULL, meaning either that the column cannot contain a NULL (NOT NULL) or that it may contain NULLs (NULL). The default is that columns may contain NULLs.

• We can declare a default value for a column using the DEFAULT keyword followed by the default value we want.

• We can use the AUTO_INCREMENT keyword, as we did in the previous example, to generate a sequence number. The value automatically generated will be one greater than the current largest value in the table. The first row inserted will have the sequence number 1. You can have only one AUTO_INCREMENT column per table, and it must be indexed. You will note that in the previous examples, we did not manually create any indexes; however, some were automatically created for us. Indexes are automatically created for columns that are declared as PRIMARY KEY, which all our AUTO_INCREMENT columns were in this example.

• We can declare that this particular column is the PRIMARY KEY for the table.

• We can specify that this particular column is a foreign key using the REFERENCES clause, as we did in the example.

As well as declaring column names and types, we can declare some other column information in this part of the CREATE TABLE statement:

• We can specify a multicolumn PRIMARY KEY, as we did in the example, by specifying PRIMARY KEY followed by the names of the columns that make up the key. We can actually also declare a single column primary key this way. A PRIMARY KEY column is a unique, indexed column that cannot contain nulls.

• INDEX and KEY are synonyms which mean that the specified column(s) will be indexed. Note that these columns do not have to contain unique values in MySQL.

• UNIQUE can be used to specify that a particular column must contain unique values. UNIQUE columns will also be indexed.

• FULLTEXT is used to create full-text indexes on a TEXT, CHAR, or VARCHAR column type. You can use full-text indexes only with MyISAM tables.

• The FOREIGN KEY clause allows us to declare foreign keys in the same two ways that we can declare primary keys.

After the closing parenthesis, we can specify some table options for this table. The one we have looked at so far is the table type. If you do not specify a type, the tables will default to being MyISAM tables. Just briefly, these are the possible values for the table type:

• MyISAM, the default, is very fast and supports full-text indexing. It is a replacement for the previous standard ISAM type.

• ISAM is an older table type. It is similar to MyISAM but with fewer features, so you should always use MyISAM instead.

• InnoDB is the ACID-compliant storage engine that supports transactions, foreign keys, and row-level locking.

• BDB (Berkeley DB) is a storage engine that supports transactions and page-level locking.

• HEAP tables are stored completely in memory and are never written to disk, so they are very fast, but limited in size and are unrecoverable in the event of failure.

• MERGE tables allow you to combine a set of MyISAM tables with the same structure so that they can be queried as if they were one table. This can be used to get around operating-system restrictions on the maximum file—and therefore table—size.

We can also specify some other options for the table. These are not required and are mostly for optimization. The options are as listed here:

AUTO_INCREMENT = # This option allows you to set the initial auto_increment value to something other than 1.

AVG_ROW_LENGTH = # This option allows you to estimate what you think will be the average row length in order to aid the storage engine.

CHECKSUM = 1 This option allows you to turn on checksum calculation for the rows in the table that may help you find the problem if the table becomes corrupt. Set it to 1 to turn it on. Off is the default, and this option works only with MyISAM tables.

COMMENT = "string" This option stores a comment about this table.

MAX_ROWS = # This option sets the maximum number of rows that will be stored in this table.

MIN_ROWS = # This option sets the minimum number of rows that will be stored in this table.

PACK_KEYS = {0 | 1 | DEFAULT} By default, MySQL packs (compresses) strings in keys—that is CHARs, VARCHARs, and TEXT. If you set this value to 1, all keys will be packed; if you set this value to 0, then none will be packed.

PASSWORD = "string" This option does nothing in the standard version of MySQL.

DELAY_KEY_WRITE = {0 | 1} This option allows you to delay key updates until after the table is closed. This option works only on MyISAM tables.

ROW_FORMAT= {default | dynamic | fixed | compressed } This option allows you to specify the storage format for rows. This option works only with MyISAM tables.

RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=# This option lets you specify your RAID configuration for optimization purposes.

UNION = (table_name,[table_name...]) This option is only for MERGE tables, and it allows you to specify which tables should be part of the MERGE.

INSERT_METHOD= {NO | FIRST | LAST } This option is only for MERGE tables and is used to specify which table to insert data into.

DATA DIRECTORY="absolute path to directory" You can use this option to specify where you would like the data in this table to be stored.

INDEX DIRECTORY="absolute path to directory" You can use this option to specify where you would like the indexes for this table to be stored.

Finally, if you look back at the CREATE TABLE general form, you can see that you can end a CREATE TABLE with a SELECT statement. SELECT is the SQL statement we use to retrieve rows from one or more tables. We can use this clause to fill the new table with the data that is returned by the SELECT statement.

Column Types

• Exact numeric types are TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT, NUMERIC, and DECIMAL.

• Approximate numeric types are FLOAT and DOUBLE.

• String types are CHAR, VARCHAR, TEXT, and BLOB.

• Date and time types are DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

• There are also various aliases to these type names.

Dropping Databases, Tables, and Indexes

• Drop a database with

drop database dbname;

• Drop a table with

drop table tablename;

• Drop an index with

drop index indexname on tablename;

Altering Existing Table Structures

• Change table structure with ALTER TABLE. This is the general structure of the ALTER TABLE command:

ALTER [iGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

 

alter_spec:

ADD [COLUMN] create_definition [FIRST | AFTER col_name ]

or ADD [COLUMN] (create_definition, create_definition,...)

or ADD INDEX [index_name] (index_col_name,...)

or ADD PRIMARY KEY (index_col_name,...)

or ADD UNIQUE [index_name] (index_col_name,...)

or ADD FULLTEXT [index_name] (index_col_name,...)

or ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)

[referenc_e_definition]

or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

or CHANGE [COLUMN] old_col_name create_definition

[FIRST | AFTER col_name]

or MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]

or DROP [COLUMN] col_name

or DROP PRIMARY KEY

or DROP INDEX index_name

or DISABLE KEYS

or ENABLE KEYS

or RENAME [TO] new_tbl_name

or ORDER BY col_name

or table_options

 

 

Exercise 1:

1: Write SQL statements to create a database(ordersYourLastName) with the following schema:

 

customer(customerID, customerName, customerAddress)

 

orders(orderID, orderDate, customerID )

 

orderItem(orderID, itemID, itemQuantity)

 

item(itemID, itemName)

 

You may make any assumptions you like about data types.

 

Test your statements in MySQL and view the resulting tables using SHOW and DESCRIBE.

 

2: We would now like to add a notes field, which you may assume is of type TEXT, to each order in the orders table. Use an ALTER TABLE statement to achieve this, and check your result with a DESCRIBE statement.

 

3: Drop the database.

Edited by JetX
0

Share this post


Link to post
Share on other sites

Inserting, Deleting, and Updating Data

In this section, we will look at how to insert and change data in your MySQL database with the INSERT, DELETE, and UPDATE statements.

We will cover the following:

• Using INSERT

• Using DELETE

• Using UPDATE

• Uploading data with LOAD DATA INFILE

• Extensions: REPLACE and TRUNCATE

We have now moved into the DML (Data Manipulation Language) aspects of SQL.

Using INSERT

The INSERT SQL statement is used to insert rows into a table. We'll begin by looking at an example. Again, you can type these statements directly into the MySQL monitor or into a file.

Some sample insert statements are shown in Listing 2

Listing 2 employee_data.sql

use employee;

 

delete from department;

insert into department values

(42, 'Finance'),

(128, 'Research and Development'),

(NULL, 'Human Resources'),

(NULL, 'Marketing');

 

delete from employee;

insert into employee values

(7513,'Joel Lapitan','Programmer',128),

(9842, 'Jonathan Gucor', 'DBA', 42),

(6651, 'Dahlia de Mesa', 'Programmer', 128),

(9006, 'Marlon Ayuban', 'Systems Administrator', 128);

 

delete from employeeSkills;

insert into employeeSkills values

(7513, 'C'),

(7513, 'Perl'),

(7513, 'Java'),

(9842, 'DB2'),

(6651, 'VB'),

(6651, 'Java'),

(9006, 'NT'),

(9006, 'Linux');

 

delete from client;

insert into client values

(NULL, 'MyTel Inc', '24 New York St Cubao', 'Jet Era', '95551234'),

(NULL, 'MyBank Inc', 'Mexico Pampanga', 'Dennis Martillano', '95559876');

 

delete from assignment;

insert into assignment values

(1, 7513, '2008-08-09', 8.5);

You'll see that before we insert any data into each table, we are running a DELETE statement—again, this isn't necessary, but it will clean out any test data that you may have inserted so far. We'll come back to the DELETE statement in the next section.

All of these INSERT statements are pretty similar. Let's look at the first one to see how it works:

 

insert into department values

(42, 'Finance'),

(128, 'Research and Development'),

(NULL, 'Human Resources'),

(NULL, 'Marketing');

We specify the table that we want to insert data into on the first line—in this case, department. Here, we are inserting four rows into the table. You may recall that the department table has two columns, departmentID and name. (You can check this for yourself by running a describe department command.)

In the first two rows, we have specified the departmentID that we want to use. Let's look back at the definition of departmentID. You may recall that in the first handouts&exercise1, we declared it as

 

departmentID int not null auto_increment primary key

Because this is an auto_increment column, we can specify the value or let MySQL calculate it for us. (Usually, in this case, we would let MySQL allocate a number, but there may be cases like this one in which we have an existing number we want to use.)

In the rows for Human Resources and Marketing, you will see that we have left the departmentID as NULL. This will allow the auto_increment to do its magic and allocate a value. Let's see what we get from this INSERT statement.

If you look through the various INSERT statements, you will see that when we insert data into a string or date type, we enclose it in single quotes, for example, 'Research and Development'. When it is a numerical type, you should not use quotes.

If we are enclosing data in quotes, what do we do when the data contains quotes? The answer is that we need to escape the quotes. In simple terms, we need to put a backslash (\) in front of the single quote, for example, 'O\'Leary'.

Obviously, this brings up the question, "What do we do if we want a backslash to be just a backslash, without any special meaning?" In this case, we need to escape the backslash in the same way—replace the backslash with two backslashes (\\).

We retrieve data from the database using the SELECT statement. We will cover SELECT fairly exhaustively in the next few chapters. For the moment, we only need to know that typing

 

select * from tablename;

will return all the data currently stored in a table.

If you type

 

select * from department;

you should get output similar to the following:

 

+---------------+----------------------+

| departmentID | name |

+---------------+----------------------+

| 42 | Finance |

| 128 | Research and Develop |

| 129 | Human Resources |

| 130 | Marketing |

+---------------+----------------------+

4 rows in set (0.01 sec)

You should be able to see that the effect of auto_increment is a value which is one greater than the highest current value in the column.

 

 

 

The general form of the INSERT statement from the MySQL manual is as follows:

 

INSERT [LOW_PRIORITY | DELAYED] [iGNORE]

[iNTO] tbl_name [(col_name,...)]

VALUES ((expression | DEFAULT),...),(...),...

[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

 

or INSERT [LOW_PRIORITY | DELAYED] [iGNORE]

[iNTO] tbl_name [(col_name,...)]

SELECT ...

 

or INSERT [LOW_PRIORITY | DELAYED] [iGNORE]

[iNTO] tbl_name

SET col_name=(expression | DEFAULT), ...

[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

The examples we have looked at all follow the first form of the expression. You will note that the keyword INTO is optional. We could leave it out and begin our query with insert employee values, but we find it a little harder to read.

With this first form, we need to list values for each column value in each row in the same order as the columns are in the table. For example, we had to specify the departmentID first, followed by the name, because that's the way the department table is structured. As demonstrated, this form allows us to insert multiple rows in a table with a single INSERT statement.

The second form ends in a SELECT statement. Rather than inserting values manually, this allows us to retrieve data from another table or tables in the database and store it in this table.

The third form allows us to specify which columns data should be inserted into. An example of using INSERT in this way is

 

insert into department

set name='Asset Management';

This form only allows you to insert a single row at a time, but you don't need to specify values for all the columns. In this case, we are setting a value for only the name. All the unspecified values will either take their default value, if one is specified, or be NULL. In this case, departmentID will be set to NULL, which causes the auto_increment to work its magic and generate a new departmentID for us. (You can check this by typing select * from department again.)

There are a couple of optional clauses in the INSERT statement. Let's briefly go through what these do:

• We can specify that an INSERT should be LOW PRIORITY or DELAYED. Both of these clauses will cause the insertion to be delayed until no client is trying to read from the table. The difference between them is that LOW PRIORITY will block the inserting client and DELAYED will not. What this means is that if you run a LOW PRIORITY insert, you may wait for some time before you can continue running queries in your client. With DELAYED, you will be told OK and can continue running queries, but you need to remember that the insert will not be performed until the table is not in use.

• Specifying IGNORE is chiefly useful when you are inserting multiple rows. Normally, if one of the rows you are trying to insert clashes with an existing row's PRIMARY KEY or UNIQUE value, an error will occur and the insert will be aborted. If you specify IGNORE, the error will be ignored and the insert will continue and will attempt to insert the next row.

• We can specify that a column should contain its default value by specifying DEFAULT as the value for that column.

• The ON DUPLICATE KEY UPDATE clause allows us to deal elegantly with clashing primary key or unique values. We follow this clause with an UPDATE statement that we can use to change the primary key or unique value in the row already in the table so that it no longer clashes with the new row.

The following short example demonstrates a common style of use for the ON DUPLICATE KEY UPDATE clause:

 

create table warning

(

employeeID int primary key not null references employee(employeeID),

count int default 1

) type =InnoDB;

 

insert into warning (employeeID)

values (6651)

on duplicate key update count=count+1;

This clause is very useful for situations in which you want to not only record unique events, but also take some action, such as incrementing a counter when non-unique events occur. Any sort of logging would be a good example, but in keeping with the employee database we have been using, we will record employees who have been given a warning in the table warning.

To record somebody's warning, we run this INSERT statement. Because count has a default value of 1 and we are not specifying a value in the INSERT, it will be 1 the first time the insert is done for each employeeID. Subsequent inserts with the same employeeID will trigger the ON DUPLICATE KEY UPDATE clause and will increment the counter.

 

 

Using REPLACE

The REPLACE statement is exactly like the INSERT statement except that if a key clash occurs, the new row you are inserting will replace the old row.

This is the general form of REPLACE from the MySQL manual:

 

REPLACE [LOW_PRIORITY | DELAYED]

[iNTO] tbl_name [(col_name,...)]

VALUES (expression,...),(...),...

 

or REPLACE [LOW_PRIORITY | DELAYED]

[iNTO] tbl_name [(col_name,...)]

SELECT ...

 

or REPLACE [LOW_PRIORITY | DELAYED]

[iNTO] tbl_name

SET col_name=expression, col_name=expression,...

The similarity to INSERT should be obvious.

 

Using DELETE

The DELETE SQL statement allows us to delete rows from a table. There are some delete statements in Listing 2, for example,

 

delete from department;

In this form, the delete statement will delete all the rows from the department table.

We can also limit which rows are deleted using a WHERE clause, for example,

 

delete from department where name='Asset Management';

This will only delete the rows matching the criteria in the where clause. In this case, only rows in which the department name is 'Asset Management' will be deleted.

It is unusual to want to delete all the rows from a table. However, because this is the shortest form of the delete statement, you may sometimes type it by accident without a WHERE clause. You can save yourself this anguish by switching on the -–safe-updates or –-i-am-a-dummy command-line options of the mysql client. These options prevent you from deleting (or updating) rows without specifying a key constraint in the WHERE clause. That is, you need to specify that you want to delete only rows containing certain key values.

This is the general form of the DELETE statement from the MySQL manual:

 

DELETE [LOW_PRIORITY] [QUICK] FROM table_name

[WHERE where_definition]

[ORDER BY ...]

[LIMIT rows]

 

or

 

DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...]

FROM table-references

[WHERE where_definition]

 

or

 

DELETE [LOW_PRIORITY] [QUICK]

FROM table_name[.*] [, table_name[.*] ...]

USING table-references

[WHERE where_definition]

The first form is the one we have seen examples of so far.

The other two forms are designed to allow us to delete rows from one or more tables with references to other tables. For example:

 

delete employee, employeeSkills

from employee, employeeSkills, department

where employee.employeeID = employeeSkills.employeeID

and employee.departmentID = department.departmentID

and department.name='Finance';

This example deletes all the employees who work for the Finance department and erases all records of their skills. Note that rows are deleted from employee and employeeSkills (the tables listed in the initial where clause), but not department (because it is listed only in the from clause).

The tables in the initial delete clause will have rows deleted from them, whereas the tables listed in the from clause are used for searching for data and will not have rows deleted unless they are also listed in the delete clause.

We have used a couple of new things in this where clause: the AND operator and the table.column notation. We have used AND to join our conditions together. This is a simple Boolean AND. We have also used the notation employee.employeeID. This notation means "the employeeID column of the employee table." We will revisit both of these things in more detail in the next two chapters.

The third form of DELETE is similar to the second form, except that, in this case, we delete only from the tables listed in the FROM clause while referring to the tables in the USING clause. For example:

 

delete from employee, employeeSkills

using employee, employeeSkills, department

where employee.employeeID = employeeSkills.employeeID

and employee.departmentID = department.departmentID

and department.name='Finance';

This is equivalent to the preceding example, except that it uses an alternative syntax.

There are a couple of other optional clauses in the general form of the DELETE statement:

• The LOW_PRIORITY clause works in the same way as it does in the INSERT statement.

• Specifying QUICK may speed up the DELETE statement by telling MySQL not to do some of its housekeeping on indexes while deleting from the table.

• The ORDER BY clause specifies the order in which to delete rows. This is most useful in conjunction with the LIMIT clause—we may want to delete the oldest n rows from a table, for example.

• The LIMIT clause allows us to set a maximum number of rows that can be deleted by the DELETE statement. This is useful either in conjunction with the ORDER BY clause or to save us from accidentally deleting too many rows.

Using TRUNCATE

The TRUNCATE statement allows us to delete all the rows from a table. For example:

 

TRUNCATE TABLE employee;

This query would delete all the employees from the employee table. This is faster than a DELETE statement because it works by dropping the table and re-creating it empty. One thing to bear in mind is that TRUNCATE is not transaction safe.

Using UPDATE

We can use the UPDATE SQL statement to change rows already stored in the database. For example, imagine that one of our employees changes jobs:

 

update employee

set job='DBA'

where employeeID='6651';

This statement changes the value of the job column for employee number 6651.

The general form of the UPDATE statement from the MySQL manual is as follows:

 

UPDATE [LOW_PRIORITY] [iGNORE] tbl_name

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_definition]

[ORDER BY ...]

[LIMIT rows]

 

or

 

UPDATE [LOW_PRIORITY] [iGNORE] tbl_name [, tbl_name ...]

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_definition]

The UPDATE statement is similar in many respects to the DELETE statement.

We can use an optional WHERE clause to update particular rows or leave it off to update all rows. Again, you can fall into the trap of forgetting to specify a WHERE clause—I remember one project when a foolish colleague typed something along these lines:

 

update user

set password='test';

This again highlights the usefulness of the --i-am-a-dummy mysql option, particularly if you are forced to work with dummies.

The second version of the UPDATE statement listed previously is a multi-table update. This works similarly to the multi-table deletes we looked at before. Note that only the columns you specifically list in the SET clause will be updated.

We have seen all the other clauses of the UPDATE statement before. The LOW_PRIORITY and IGNORE clauses work the same way as they do in INSERT. The ORDER BY and LIMIT clauses work the same way they do in DELETE.

 

Uploading Data with LOAD DATA INFILE

The LOAD DATA INFILE command allows you to bulk insert data from a text file into a single table without having to write INSERT statements. For example, we could have loaded the data in the department table using this technique as follows. Listing 3 shows the contents of a data file containing department information.

 

 

Listing 3 department_infile.txt

42 Finance

128 Research and Development

NULL Human Resources

NULL Marketing

This file is in the default LOAD DATA INFILE format, with each row listed on a separate line with tabs between column values. (This is configurable and we will see how in a minute.)

We can load this information into the department table with the following LOAD DATA INFILE statement:

 

load data local infile 'department_infile.txt'

into table department;

This facility is particularly useful for converting data from another database format, spreadsheet, or CSV (comma-separated values) file.

The LOAD DATA INFILE statement requires the FILE privilege. The privilege to perform this is often restricted for good security reasons—to stop people from loading in /etc/passwd, for example.

The general form of the LOAD DATA INFILE statement is as follows:

 

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'fileName.txt'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY '\t']

[[OPTIONALLY] ENCLOSED BY '']

[ESCAPED BY '\\' ]

]

[LINES TERMINATED BY '\n']

[iGNORE number LINES]

[(col_name,...)]

The optional clauses are as listed here:

• The LOW PRIORITY clause works the same way it does in the INSERT statement by waiting for other clients to stop reading from the table. CONCURRENT, on the other hand, allows other clients to read from the table while the bulk insert is going on.

• In our example, we specified the optional keyword LOCAL, meaning that the data file is on the client machine. If this is not specified, MySQL will look for the infile on the server.

• If you have key clashes while inserting data, REPLACE and IGNORE provide two methods for dealing with this. Specifying REPLACE tells MySQL to replace the old row with the new row, while IGNORE tells MySQL to keep the old row.

• The FIELDS and LINES clauses specify how the data in the infile is laid out. The values in the general form are the defaults—each row on a new line, column values separated by tabs. We can also enclose column values in quotes and use the backslash character to escape any special characters (like single quotes) that might confuse MySQL.

• The IGNORE number LINES clause tells MySQL to ignore the first number lines in the infile.

• The final clause allows you to specify that you only want to read data into some of the table's columns.

A common format to receive data in is CSV or comma-separated values. Many programs can read and write files of this type, but one notable example is Microsoft Excel. Listing 4 shows a small CSV file saved from Excel.

Listing 4 new_programmers.csv

Name,Job,DepartmentID

 

Julia Lapitan,Programmer,128

Douglas Gucor,Programmer,128

Tim de Mesa,Programmer,128

We can load this data into the employee table with the following query:

 

load data infile 'e:\\new_programmers.csv'

into table employee

fields terminated by ','

lines terminated by '\n'

ignore 2 lines

(name, job, departmentID);

You can see that we have used more options to load this data than we did when the data was in the default format. A few points are worth noting:

• Because we have used a Windows/DOS-style path that includes a backslash, we need to escape the backslash. Our path therefore became 'e:\\new_programmers.csv'.

• It possibly goes without saying that the fields in a CSV file are terminated by commas, but we need to specify it.

• We do not need to specify that lines are terminated by a newline character, but we have chosen to.

• This file has a header, so the first two lines do not contain data and should be ignored.

• The data in this file does not contain employeeIDs, so to allocate the three columns of data into the four columns in the database, we need to specify what columns (in order) the data will be mapped to. In this case, we have specified (name, job, departmentID).

Summary

In this section, we looked at ways to insert, delete, and update data from the tables in our database.

Inserting Data

• String values should be in quotes. Single quotes or backslashes within a string need to be escaped with a backslash.

• Add data to tables with the INSERT statement:

• INSERT [LOW_PRIORITY | DELAYED] [iGNORE]

• [iNTO] tbl_name [(col_name,...)]

• VALUES ((expression | DEFAULT),...),(...),...

• [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

• or INSERT [LOW_PRIORITY | DELAYED] [iGNORE]

• [iNTO] tbl_name [(col_name,...)]

• SELECT ...

• or INSERT [LOW_PRIORITY | DELAYED] [iGNORE]

• [iNTO] tbl_name

• SET col_name=(expression | DEFAULT), ...

• [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

• The REPLACE statement is just like INSERT, but it overwrites rows where a key clash occurs. INSERT fails or triggers the ON DUPLICATE KEY UPDATE clause when a key clash occurs.

Deleting Data

• Avoid disasters with --i-am-a-dummy.

• Delete data from tables with the DELETE statement:

 

DELETE [LOW_PRIORITY] [QUICK] FROM table_name

[WHERE where_definition]

[ORDER BY ...]

[LIMIT rows]

or DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...]

FROM table-references

[WHERE where_definition]

or DELETE [LOW_PRIORITY] [QUICK]

FROM table_name[.*] [, table_name[.*] ...]

USING table-references

[WHERE where_definition]

The TRUNCATE TABLE statement deletes all rows from a table.

Updating Data

• Update data in tables with the UPDATE TABLE statement:

 

UPDATE [LOW_PRIORITY] [iGNORE] tbl_name

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_definition]

[ORDER BY ...]

[LIMIT rows]

or UPDATE [LOW_PRIORITY] [iGNORE] tbl_name [, tbl_name ...]

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_definition]

LOAD DATA INFILE

• Use LOAD DATA INFILE to load the contents of a text file into a table:

 

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY '\t']

[[OPTIONALLY] ENCLOSED BY '']

[ESCAPED BY '\\' ]

]

[LINES TERMINATED BY '\n']

[iGNORE number LINES]

[(col_name,...)]

 

 

 

 

Exercise 2:

 

 

1: Create a set of INSERT statements to place data in each table of your orders table.

 

2: Delete the data from your tables.

 

3: Write a data file containing the same data that you inserted in part 1 and load it into your orders database using LOAD DATA INFILE.

0

Share this post


Link to post
Share on other sites

Querying MySQL

So far, we have covered designing, creating, and populating a MySQL database. In this section, we will look at the other end of this process: retrieving data from your database.

In this section, we will cover the SQL SELECT statement in some detail. This is probably the most important statement in SQL. It is the statement we use to select rows from one or more database table(s).

In this section, we will discuss how to select rows from a single database table. We'll cover the following:

• Overview of SELECT

• Simple queries

• Selecting particular columns

• Column aliases

• Using the WHERE clause to select particular rows

• Using the GROUP BY clause

• Selecting particular groups with HAVING

• Sorting search results with ORDER BY

• Limiting search results with LIMIT

We'll begin by looking at the general form of the SELECT statement.

Overview of SELECT

The SELECT statement has the following general form:

 

SELECT columns

FROM tables

[WHERE conditions]

[GROUP BY group

[HAVING group_conditions]]

[ORDER BY sort_columns]

[LIMIT limits];

This is not a full set of syntax—we'll cover that later—but it gives you an idea of the general form of the statement. We will cover all the previously listed clauses in this section.

The SELECT statement has many optional clauses. You can use these or not as you choose, but they must appear in the order shown.

Simple Queries

An example of the simplest form of the SELECT statement is as follows:

 

select * from department;

If you run this query on the data we have in the employee database, you should get a result something like this:

 

+---------------+--------------------------+

| departmentID | name |

+---------------+--------------------------+

| 42 | Finance |

| 128 | Research and Development |

| 129 | Human Resources |

| 130 | Marketing |

+---------------+--------------------------+

4 rows in set (0.00 sec)

This query has selected all the data in the chosen table—that is, all the rows and all the columns from the department table.

You can test this out on another table—try selecting all the rows and columns from the employeeSkills table, for example.

Of course, the power of a relational database is not in its capability to give you back all the data you put in, but instead in its capability to allow you to search the data for particular pieces of information.

Selecting Particular Columns

The next step we can take is to limit the columns that are returned. The * in the previous query (select * from department) means "all the columns in the table." Instead of specifying *, we can list a set of columns we would like returned. This can be a single column, a subset of table columns, or even the complete set of columns in any order that suits us. You should specify the column names as a list of comma-separated values.

For example, the following query selects only the values in the employeeID and name fields of the employee table:

 

select name, employeeID from employee;

You can see that only the two columns we specified have been returned. Note that the columns are displayed in the order in which we requested the columns in the query, rather than the order in which they appear in the database schema.

Specifying Absolute Databases and Tables

An additional piece of notation you should be aware of at this point allows you to absolutely specify which database and table we are talking about. For example, we can refer to the name column in the employee table as employee.name. For example:

 

select employee.name

from employee;

Similarly, we can absolutely specify which table in which database we are talking about, for example:

 

select name

from employee.employee;

Here, we are making explicit reference to the employee table within the employee database. The notation here is database.table.

If desired, we can specify which database and table a column belongs to. The same example could be written using database.table.column syntax like this:

 

select employee.employee.name

from employee;

For these simple queries, this syntax is not very useful, but as we move on to more complex queries, this allows us to be unambiguous about what information we are looking for.

Aliases

At this point, we should discuss the concept of column and table aliasing.

We have the ability to rename columns or expressions in a SELECT statement, and the new name will be what is shown in the output. For example, we can use the following query:

 

select name as employeeName

from employee;

You can see that in the results, the contents of the name column are now listed under the heading employeeName.

The identifier employeeName is known as an alias. There are some rules about what we can and cannot do with aliases, and we will cover these as we come to them.

This specific example of an alias is not particularly useful. As we begin to write more complex queries and queries that involve calculation, you should see its power.

We can also use aliases for tables, like this:

 

select e.name

from employee as e;

This will give the same result as if we had written the query not using aliases. This notation will become useful when we begin running queries over multiple tables in the next chapter.

In the last two examples, the keyword AS is optional. We could simply have written

 

select name employeeName

from employee;

and

 

select e.name

from employee e;

You may choose to write the queries either way. It is simply a matter of style.

Using the WHERE Clause to Select Particular Rows

So far, we have looked at selecting all data from a table and selecting particular columns. Next, we will consider how to select particular rows. This is useful because we frequently want to select records from a table or tables that match particular search criteria. This becomes more important when we need to retrieve a few useful rows from a much larger table.

We can accomplish this using the WHERE clause of the SELECT statement. A simple example follows:

 

select employeeID, name

from employee

where job='Programmer';

(Remember, by the way, that we can set queries out on multiple lines. Each query is terminated by a semicolon. We lay out the SELECT statement like this to make it easier to read.)

We used a condition in the WHERE clause to match only the rows in the table that met the specified criteria—in this case, they had to be employed as programmers.

Notice that we have combined this with a specific list of desired columns (employeeID and name) to pull out only the information we are interested in.

In this case, we have used a test of equality in the WHERE clause. Note that SQL uses = for testing equality. This is different from various other languages that use == or eq.

A huge variety of functions are available for use in the WHERE clause, and we will address these in detail later. For the time being, we will mention only the most commonly used operators:

• Equality, or =, which we have seen used previously.

• Inequality, expressed as != or <>.

• All the permutations of > (greater than), < (less than), >= (greater than or equal to), and <= (less than or equal to).

• IS NULL and IS NOT NULL, which are used to test whether a value is or is not NULL. You cannot do this by testing whether somevalue=NULL. (We will discuss why in later)

• The arithmetic operators you would expect, typically used in conjunction with comparison operators. For example, we might like to test whether somevalue > someothervalue*10.

• The standard Boolean operators AND, OR, and NOT, which we can use to group tests together. These are lower in precedence than the comparison operators, so, for example, salary > 30000 AND salary < 50000 works as you would expect.

In addition to the operators, we will use one function in some examples. The count() function allows us to count the number of rows returned by a query. For example:

 

select count(*) from employee;

This query will tell us how many rows there are in the employee table.

Finally, we can control precedence by grouping expressions with parentheses.

An example of a slightly more complex query using WHERE is as follows:

 

select * from assignment

where employeeID=6651 and hours > 8;

This query selects all the work assignments performed by employeeID 6651 in which he performed more than 8 hours of work.

One important point to note is that we are not allowed to use any column aliases in the WHERE clause. We must use the original column name. This is an ANSI SQL limitation. The reason for it is that the value of the aliased column may be unknown at the time the WHERE condition is examined.

Removing Duplicates with DISTINCT

You can use the keyword DISTINCT in your queries to specify that you do not want to see duplicate results. For example, consider the following query:

 

select job

from employee;

This will return the following data:

 

+-----------------------+

| job |

+-----------------------+

| Programmer |

| Programmer |

| Systems Administrator |

| DBA |

+-----------------------+

4 rows in set (0.01 sec)

Note that the data Programmer appears twice. This is because this value occurs in two rows. This query has simply returned the complete list of values in the job column of this table.

Now, consider this query:

 

select distinct job

from employee;

This will return the following rows:

 

+-----------------------+

| job |

+-----------------------+

| Programmer |

| Systems Administrator |

| DBA |

+-----------------------+

3 rows in set (0.04 sec)

Here, the duplicates have been removed.

In this case, the difference doesn't seem like that big of a deal—sure, the second set of results is a little neater, but it doesn't really improve things much. It would be a little more important for a big table with a lot of repetition, but it would still be presenting accurate information.

On the other hand, consider this:

 

mysql> select count(job) from employee;

+------------+

| count(job) |

+------------+

| 4 |

+------------+

1 row in set (0.01 sec)

This query tells us there are four values in the job column. This is kind of misleading. It certainly doesn't tell us that there are four different values in the job column because we can see by inspection of the data that there are only three.

It is relatively easy to type the previous query by mistake when what you actually meant was this:

 

select count(distinct job) from employee;

This will give you the following result:

 

+---------------------+

| count(distinct job) |

+---------------------+

| 3 |

+---------------------+

1 row in set (0.05 sec)

This tells us how many different values are in the job column, a more useful piece of information.

Using the GROUP BY Clause

The next clause we will look at is the GROUP BY clause. This allows us to consider retrieved rows in groups. This is really useful only when we use it in combination with functions that operate over a group of rows. The only one of these we have mentioned so far is count(), but we will look at many more later.

Consider the following query:

 

select count(*), job

from employee

group by job;

This query will count the number of employees in each job grouping—that is, the number of employees who hold each job. If you run this query on the employee database, you should see a result similar to the following:

 

+----------+-----------------------+

| count(*) | job |

+----------+-----------------------+

| 1 | DBA |

| 2 | Programmer |

| 1 | Systems Administrator |

+----------+-----------------------+

3 rows in set (0.04 sec)

There are two differences between the way GROUP BY works in MySQL and ANSI SQL.

In ANSI SQL, you must group by all the columns you have listed in the initial SELECT clause. MySQL allows you to have additional fields in the SELECT clause that are not in the GROUP BY clause.

MySQL also allows you to sort the group order in which the results are presented. The default order is ascending. If we want to repeat the last query but see the results in descending order, we can use the following query:

 

select count(*), job

from employee

group by job desc;

This will produce results similar to the following:

 

+----------+-----------------------+

| count(*) | job |

+----------+-----------------------+

| 1 | Systems Administrator |

| 2 | Programmer |

| 1 | DBA |

+----------+-----------------------+

3 rows in set (0.04 sec)

As you can see, the names of the jobs are now in reverse alphabetical order. You can also specify ASC (for ascending), but this is the default, so it's redundant to do so.

Selecting Particular Groups with HAVING

The next clause in the SELECT statement is HAVING. A GROUP BY with a HAVING clause is like a SELECT with a WHERE clause. For example:

 

select count(*), job

from employee

group by job

having count(*)=1;

This query will select the jobs in the company for which we have only one employee in the role. It should produce results similar to the following:

 

+----------+-----------------------+

| count(*) | job |

+----------+-----------------------+

| 1 | DBA |

| 1 | Systems Administrator |

+----------+-----------------------+

2 rows in set (0.05 sec)

It's been our experience that people who are just beginning to use SQL often get WHERE and HAVING confused. You will use WHERE in just about every query you write to test conditions that relate to individual rows. You will use HAVING when you want to apply a conditional to whole groups.

Sorting Search Results with ORDER BY

The next clause in the SELECT statement is ORDER BY. This clause allows us to sort the result rows on one or more columns. The sort can be either ascending, denoted ASC, or descending, denoted DESC. For example:

 

select *

from employee

order by job asc, name desc;

This will select all the rows and columns from the employee table. They will be sorted according to job in alphabetical order, and if two or more people have the same job, they will be sorted in reverse alphabetical order by name.

If you just specify ORDER BY column with no ASC or DESC, the default is ASC. Note that if ORDER BY is not specified, you can't assume anything about the order in which rows will be returned.

Limiting Search Results with LIMIT

The final clause of the SELECT statement we will look at in this section is LIMIT.

The LIMIT clause is used to limit the number and range of rows that are returned from a query. For example, consider the following query:

 

select *

from employeeSkills

limit 5;

This query will return only the first five rows that match the selection criteria. In this particular case, we will simply get the first five rows found in the table, as shown here:

 

+-------------+-------+

| employeeID | skill |

+-------------+-------+

| 6651 | Java |

| 6651 | VB |

| 7513 | C |

| 7513 | Java |

| 7513 | Perl |

+-------------+-------+

5 rows in set (0.44 sec)

We can also specify that we want a subset of rows other than the first n. If we, for example, wanted to retrieve rows 6 through 8 from the preceding query, we would do so like this:

 

select *

from employeeSkills

limit 5, 3;

When we pass two parameters to limit, the first parameter is the offset (start point) and the second parameter is the maximum number of rows we would like returned. Contrast this with the previous case: When we pass only a single parameter, it represents the maximum number of rows we would like returned.

Row numbering starts from zero when specifying offsets (as you can see in the preceding example—for the sixth row, we specify offset 5). Our first LIMIT example selected rows 0 to 4, and our second selected rows 5 to 7.

If you specify the second parameter as -1, the query will return the rows from the offset to the end of the table.

The LIMIT clause is normally used with ORDER BY so that the order in which rows are returned makes some sense. Remember that without an ORDER BY clause, the records are not retrieved in any logical order.

This clause is especially useful when building Web or GUI applications using MySQL because it provides an easy mechanism for paging results.

Summary

• The SELECT statement has the following general form:

 

SELECT columns

FROM tables

[WHERE conditions]

[GROUP BY group

[HAVING group_conditions]]

[ORDER BY sort_columns]

[LIMIT limits];

• The clause select * retrieves all columns; select columnname retrieves a particular column.

• We can specify tables as database.table and columns as table.column or database.table.column to avoid ambiguity.

• Aliases are alternative names for tables and columns. Specify them this way:

select column as column_alias

from table as table_alias;

• The WHERE clause is used to select rows matching search criteria.

• The keyword DISTINCT removes duplicates from the result set.

• The GROUP BY clause treats the rows retrieved group by group. Its chief use is in conjunction with group functions like count().

• The HAVING clause is like a WHERE clause for groups.

• The ORDER BY clause sorts result rows according to the columns you specify.

• The LIMIT clause is used to control which rows are returned from the total possible result set. You can specify the maximum rows returned and an offset from which to start.

Using Joins to Run Queries over Multiple Tables

All the queries discussed above retrieved data from only a single table. Given that we've focused on a normalized database design in which information is stored in multiple tables, selecting from a single table is, well, limited. What make well-designed, relational databases interesting are the relationships—that is, the links between the tables. When information is selected from multiple tables, these links are called joins. Let's begin by looking at queries that link two tables.

Joining Two Tables

Consider the following query:

 

select employee.name, department.name

from employee, department

where employee.departmentID = department.departmentID;

You will see that we have specified two tables in the FROM clause instead of one. In this case, we want to retrieve employees' names and the names of the departments they work for.

 

How did we get these results? First of all, we selected columns that appeared in two tables. (You will notice that we used the dot notation to differentiate between the employee name and the department name) To do this, we needed to include both of these tables in the FROM clause.

The most interesting thing about this query is the WHERE clause. If we run this query without the WHERE clause, as

 

select employee.name, department.name

from employee, department;

The first query, with the WHERE clause, shows employees listed with the correct department, whereas the second query shows all possible combinations of employees and departments, with no way of knowing which rows are correct and which are spurious! This result set, containing all possible rows from combining the two tables, is called the Cartesian product of the two tables.

The WHERE clause is clearly important in finding the result rows we want. When performing a join, we refer to the condition or set of conditions used to join tables together as the join condition. In this case, the condition we used was employee.departmentID = department.departmentID, which is the link between the tables based on the foreign keys in our original schema.

When you need to find information that spans more than one table, you need to use these links between tables to find the information you are seeking. Sometimes this means looking for a path from the information you have to the information you want. We'll come back to this idea in the next section.

One other point to note is that if you look at the previous result sets, both of the columns are headed "name" because this is what each column is called in the context of its own table. We could improve the readability of the results by using aliases, as shown here:

 

select employee.name as employeeName, department.name as departmentName

from employee, department

where employee.departmentID = department.departmentID;

The presentation of this result set is easier to understand than the previous ones.

Joining Multiple Tables

The principle behind joining more than two tables is the same.

Consider the situation of wanting to find out which department's employees have been assigned to work for the client. How can we find this information?

We know the client name, and looking this up in the client table gives us the clientID. We can use this to find matching assignments in the assignment table and to see which employees have worked for the client. We get their employeeIDs from the assignment table and can then look these up in the employee table to find out the ids of the departments they work for. From this information, we can then finally go to the department table and look up the department name!

Having worked out this path across four tables, we need to write a query that reflects our logic. This is as follows:

 

select department.name

from client, assignment, employee, department

where client.name='MyTel Inc'

and client.clientID = assignment.clientID

and assignment.employeeID = employee.employeeID

and employee.departmentID = department.departmentID;

These are the results of running this query:

 

+--------------------------+

| name |

+--------------------------+

| Research and Development |

+--------------------------+

1 row in set (0.00 sec)

Looking at the query we wrote, you can see that we needed to list all the tables in the path that we followed and then join conditions to make each link from table to table. We have a regular condition—client.name = 'MyTel Inc'—and a series of join conditions. Notice that we had three join conditions to join four tables.

You can use this as a guideline to check whether you have all the join conditions you need. If you are joining n tables, in most cases, you will have a link between each pair of tables, and therefore have n-1 join conditions.

Joining a Table to Itself—Self Joins

As well as joining tables to other tables, we can join a table to itself. Why would we want to do this? Sometimes we are looking for relationships between rows in a table. Imagine that we want to know the names of employees who work in the same department as Joel Lapitan. To do this, we need to find the departmentID Joel works for from the employee table and then look in the employee table for employees who work for that department.

We can do this as shown here:

 

select e2.name

from employee e1, employee e2

where e1.name = 'Joel Lapitan'

and e1.departmentID = e2.departmentID;

You can see that, in this query, we have actually declared two different aliases for the employee table. Effectively, we are telling MySQL that we want to pretend we have two separate tables, e1 and e2, which just happen to contain the same data. Then, we can simply join them as we would any two other tables. We begin by finding Nora's row in e1 (where e1.name='Nora Edwards'). We can then look for rows in e2 that have the same departmentID as Nora (e1.departmentID = e2.departmentID).

This can take a little getting used to, but as long as you can pretend you are dealing with two different tables, you shouldn't have too much trouble.

You can see from the result that Joel himself is included in the list. We can easily add a further condition to exclude him from the result set:

 

select e2.name

from employee e1, employee e2

where e1.name = 'Joel Lapitan'

and e1.departmentID = e2.departmentID

and e2.name != 'Joel Lapitan';

 

Understanding the Different Join Types

There are various types of joins and various kinds we can use in MySQL.

Understanding the Basic Join

In the preceding section, we mentioned the concept of a Cartesian product. This is sometimes called a full join or cross join, but regardless of nomenclature, it returns a complete set of combinations. When we add a conditional statement to the join (like employee.departmentID = department.departmentID), we make it into something called an equijoin, which limits the number of rows in the result set.

So far, we have used a set of tables listed in the FROM clause separated by the comma operator. This gives us a cross join, as described previously, converted to an equijoin with the WHERE clause. MySQL has various forms of syntax we can use for this type of join.

Consider our original query:

 

select employee.name, department.name

from employee, department

where employee.departmentID = department.departmentID;

Optionally, we could have used the keyword JOIN, instead of a comma:

 

select employee.name, department.name

from employee join department

where employee.departmentID = department.departmentID;

Instead of JOIN, we could just as well have written CROSS JOIN or INNER JOIN.

When we perform this type of join, MySQL looks at the tables we are joining and tries to work out the most efficient way to join them together, rather than necessarily joining the tables in the order we have listed. Sometimes the query optimization can go slightly wrong. If you want to override the optimizer and force MySQL to join tables in the order in which you list them, replace the word JOIN with STRAIGHT JOIN.

Understanding LEFT and RIGHT Joins

When we used equijoins in the preceding section, we were using a JOIN, CROSS JOIN, INNER JOIN, or perhaps STRAIGHT JOIN and were looking for rows that matched in two or more tables. What about cases in which we want to find rows in one table that don't have a corresponding row in the other table?

Consider, for example, a situation in which we want to find employees who have not yet worked on any outside assignments—that is, employees whose employeeIDs are not listed in the assignment table. We can do this using LEFT JOIN, as follows:

 

select employee.name

from employee left join assignment

on employee.employeeID = assignment.employeeID

where clientID is null;

You can easily confirm by visual inspection of the tables that this is the right answer, but why and how does it work?

The left join works by taking the left-hand table in the join (in this case, employee) and trying to match it to rows in the right-hand table. These matching rows are placed alongside the left table. For each row in the left table that has no matching rows in the right table, the LEFT JOIN substitutes a row of NULL values. We can find rows from the left table that have no matching row in the right table by searching for a NULL key value.

Look back at the example. In this join, for each employee who has worked on an assignment, we will get a row showing the employee and the assignment details. When an employee has no matching row in the assignment table, the left join will make up a "dummy row" consisting of all NULLs. We can find these dummy rows by looking for employees who have worked on an assignment for which the clientID is NULL. (The clientID is a key field, so this should never occur in the assignment table.)

In versions of MySQL prior to 4.1, this technique was often used to work around the absence of subqueries.

In this example, we have used LEFT JOIN, but we could as easily have used RIGHT JOIN, which does the same thing but uses the right table as a base and fills any missing rows from the left table with NULLs.

 

Writing Subqueries

A subquery is a query within a query—that is, a query in which we reuse the result in another query. They are sometimes called nested queries or subselects. They are a feature that users have been requesting for a long time. Subqueries do not add new functionality, but queries are often more readable using subqueries, rather than a complex set of joins.

We have already looked at one kind of subquery without realizing it. The multi-table deletes and updates that we looked at in "Inserting, Deleting, and Updating Data," are a type of specialized subquery.

In this chapter, we will look at subqueries in SELECT statements.

Two basic kinds of subqueries have been added to MySQL:

• Derived table subqueries

• Expression subqueries

Expression subqueries appear in the WHERE clause of a SELECT statement. These come in two further types:

• Subqueries that return a single value or row

• Subqueries that are used to test a Boolean expression

We'll look at an example of each of these in turn.

Using Derived Table Subqueries

Derived table subqueries allow us to list a query in the FROM clause of another query. This effectively allows us to create a temporary table and add it to the query. For example, consider the following simple query:

 

select employeeID, name from employee where job='Programmer';

It should be obvious that this will retrieve the names and ids of all the programmers. We can use this query within another for another useful result:

 

select programmer.name

from (select employeeID, name from employee where job='Programmer')

as programmer,

assignment

where programmer.employeeID = assignment.employeeID;

In this case, we have used the subquery (select employeeID, name from employee where job='Programmer') to make a derived table that contains only the rows employeeID and name, and we have aliased this table to call it 'programmer'. We can then query it as we would any other table. In this case, we use it to find out which programmers have worked on outside assignments to garner the following results:

 

+--------------+

| name |

+--------------+

| Joel Lapitan |

+--------------+

1 row in set (0.01 sec)

Using Single-Value Subqueries

As in the preceding section, we will start with a simple query:

 

select max(hours) from assignment;

This will retrieve a single value, representing the maximum number of hours an employee has worked on an assignment. We are using a MySQL function we have not yet mentioned: max(), which finds the greatest value in a particular column. Using the result returned by this type of function is a common application of single-value subqueries.

As before, we can go on and use this query within another query.

Single-value subqueries return a single column value and are then typically used for comparison. For example, consider the following query:

 

select e.employeeID, e.name

from employee e, assignment a

where e.employeeID = a.employeeID

and a.hours = (select max(hours) from assignment);

Here, we are looking for what might be termed the company's hardest working employee: Who is the employee who has put in the greatest number of hours on a particular day on an assignment?

Here are the results of running this query:

 

+-------------+--------------+

| employeeID | name |

+-------------+--------------+

| 7513 | Joel Lapitan |

+-------------+--------------+

1 row in set (0.42 sec)

We can also write subqueries that return a row, rather than a single value, although this is often of limited usefulness. We will not look at an example of this here.

Using Boolean Expression Subqueries

Boolean expression subqueries are used to check our query against some special functions that return a Boolean expression. These special functions are IN, EXISTS, and (grouped together) ALL, ANY, and SOME.

We can use the keyword IN to check against a set of values. Consider the following query:

 

select name

from employee

where employeeID not in

(select employeeID

from assignment);

This query has the same effect as the one we looked at using LEFT JOIN. It allows us to look for employees who are not in the set of employees who have worked on an outside assignment. The keyword IN lets us look for values in a set of values. We get the same result here as we did from our LEFT JOIN query:

 

Interestingly enough, another use of IN is to just test against a listed a set of values, as shown here:

 

select name

from employee

where employeeID not in (6651, 1234);

The EXISTS keyword works in a slightly different fashion than the IN keyword. In queries using EXISTS, we actually use data from the outer query in the subquery. This is sometimes called a correlated subquery.

For example, consider the following query:

 

select e.name, e.employeeID

from employee e

where not exists

(select *

from assignment

where employeeID = e.employeeID);

Here, we are looking for employees who have never worked on an outside assignment.

In the subquery, we are looking at rows in the assignment table and we are checking for rows where the assignment employeeID is the same as the employee.employeeID. The e.employeeID comes from the outer query. Here's what MySQL is actually doing: For each row in the employee table, we check the results of the subquery, and if there is no matching row (WHERE NOT EXISTS), we add the employee's details to the result set.

Although some users find this an easier syntax to understand, we can get the same result using a LEFT JOIN as we did before. It will also be more efficient and, therefore, faster to execute if written as a left join. This query yields exactly the same results:

The ALL, ANY, and SOME keywords are used to compare against a set of values returned from a subquery.

Suppose that Joel Lapitan, who you may remember is our hardest working programmer, wants to establish that nobody works longer hours than the programmers. She comes up with the following query to establish this fact:

 

select e.name

from employee e, assignment a

where e.employeeID = a.employeeID

and a.hours > all

(select a.hours

from assignment a, employee e

where e.employeeID = a.employeeID

and e.job='Programmer');

The subquery finds the list of hours worked on assignments by programmers in the company. It then looks for any other employees who have worked on an assignment for longer than these programmers, using the check a.hours > ALL (the programmers' hours).

You will not be surprised to know that this query returns no rows, establishing that, in fact, nobody in this company works harder than the programmers.

 

Using SELECT Statement Options

When we first looked at the SELECT statement, we looked at an abbreviated form of the general syntax for the statement. Let's come back and look at the complete syntax and see what we don't know.

According to the MySQL manual, this is the form of a SELECT statement:

 

SELECT [sTRAIGHT_JOIN]

[sql_SMALL_RESULT] [sql_BIG_RESULT] [sql_BUFFER_RESULT]

[sql_CACHE | SQL_NO_CACHE] [sql_CALC_FOUND_ROWS] [HIGH_PRIORITY]

[DISTINCT | DISTINCTROW | ALL]

select_expression,...

[iNTO {OUTFILE | DUMPFILE} 'file_name' export_options]

[FROM table_references

[WHERE where_definition]

[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

[HAVING where_definition]

[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]

[LIMIT [offset,] rows | rows OFFSET offset]

[PROCEDURE procedure_name(argument_list)]

[FOR UPDATE | LOCK IN SHARE MODE]]

Most of these clauses are now familiar to us. Let's briefly go through the ones we have not yet covered:

• The STRAIGHT JOIN clause at the beginning can be used to force the query optimizer to join the tables in the order you specify. This has the same effect as specifying STRAIGHT JOIN in the WHERE clause, as discussed earlier in this chapter. This is an extension to ANSI SQL.

• The SQL_SMALL_RESULT, SQL_BIG_RESULT, and SQL_BUFFER_RESULT options are designed to help with optimization. You can use SQL_SMALL_RESULT and SQL_BIG_RESULT to tell MySQL that you expect the result set to have either few rows or a large number of them. SQL_BUFFER_RESULT tells MySQL that it must put the result set into a temporary table. You can use this when it takes significant time to send the results to the client to avoid having the queried tables locked for that time. These options are MySQL extensions to ANSI SQL.

• SQL_CACHE and SQL_NOCACHE tell MySQL whether to cache the results. (Another extension to ANSI SQL.)

• SQL_CALC_FOUND_ROWS is for use with the LIMIT clause; it tells MySQL to work out how many rows would have been returned if there had been no LIMIT clause. We can then retrieve this number with select found_rows(); (another extension to ANSI SQL). This is intended to reduce duplicated effort. In versions without it, a common task is to run a COUNT(*) query and then a SELECT with a LIMIT.

• HIGH PRIORITY tells MySQL that this query should be given priority over any UPDATE statements that are waiting to use the involved tables.

• We have already talked about DISTINCT, but DISTINCTROW is a synonym for it. ALL is the opposite (return all duplicates) and is the default option.

• The SELECT INTO OUTFILE is the opposite of the LOAD DATA INFILE command we looked at in "Inserting, Deleting, and Updating Data." This puts the result of the SELECT statement into the specified file. The export_options clause is the same as the options in LOAD DATA INFILE.

• The PROCEDURE clause allows you to specify a procedure that can be applied to the result set before it is sent to the client. This procedure must be written in C++ and, as such, is beyond the scope of this book, but see the MySQL manual if you need more information.

• The FOR UPDATE and LOCK IN SHARE MODE clauses affect you only if your storage engine uses page- or row-level locking—in practice, this is InnoDB and BDB. If you specify FOR UPDATE, you will set an exclusive lock, and if you use LOCK IN SHARE MODE, you will set a shared lock.

Summary

Joins

• A join is the process of linking two tables together. We specify the tables to join in the FROM clause, along with the type of join. We should also specify a join condition to describe how the tables should be joined together.

• The comma operator, JOIN, INNER JOIN, and CROSS JOIN all work the same way: They combine tables so that we can search for data across them.

• LEFT and RIGHT joins allow us to look for rows in one table that have no corresponding row in another table.

Subqueries

• A subquery is a query nested inside another query.

• Subqueries can return a single value, a row, or a set of values. They can also be used to evaluate the Boolean conditions SOME, ALL, or ANY.

• Using EXISTS, we can check whether a row exists in the subquery that correlates to a row in the outer query.

SELECT Statement Options

• The SELECT statement allows us to specify various optimization options.

• We can dump data to an external file with SELECT INTO OUTFILE in the same way that we performed LOAD DATA INFILE.

• We can add procedures (written in C++) for result processing to MySQL.

 

Using MySQL Built-In Functions with SELECT

• MySQL has a full set of arithmetic, comparison, and logical operators. You need to be careful when using operators with NULL because this does not always produce the expected results.

• MySQL provides a set of functions that can be used to perform string, numeric, date, casting, and miscellaneous functions.

• The grouping functions are performed over a set of column values. These sets are groups if a GROUP BY clause is specified, or they provide the complete set of returned values in a column if no GROUP BY clause is specified.

MySQL has a wide variety of built-in operators and functions that can be useful for writing queries. Most of these are for use in the SELECT and WHERE clauses. There are also some special grouping functions for use in the GROUP BY clause. We have already used the basic comparison operators and the count() and max() functions. A vast number of functions are available.

Since majority of you won’t even bother to read the entire contents of this Section, I won’t discuss them further since you could look it up @ google.

--------------------

MySQL Operators

Control Flow Functions

String Functions

Numeric Functions

Date and Time Functions

Cast Functions

Other Functions

Functions for Use with GROUP BY Clauses

--------------------

0

Share this post


Link to post
Share on other sites

Interfacing MySQL with Programs (PHP)

 

MIT41 is about “advance database systems design” so I’m not planning to teach you guys how to write PHP scripts, all I want to do is demonstrate how to interface MySQL with PHP and provide some e-books about MySQL and PHP.

 

Next meeting: Advance Database System Design

 

 

User Registration

User authentication is often at the very heart of many community - oriented and e - commerce web sites.

Because this functionality is so useful, I’ll be using this application to demonstrate how to interface MySQL with Programs (PHP).

 

Functional Perspective

• Allow users to create accounts

• Members must provide e-mail address to validate registration

• Allow user to update password, e-mail and reset forgotten passwords

 

Planning the Database

The information you choose to collect from your users will depend on what type of service your site offers. At the very least a unique user ID, username, password hash, and e-mail address should be stored. You will also need a mechanism to track which accounts have been verified or are pending verification.

 

CREATE DATABASE JET_SECURITY;

 

USE JET_SECURITY;

 

CREATE TABLE JET_USER (

userID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

userName VARCHAR(20) NOT NULL,

password CHAR(40) NOT NULL,

emailAddr VARCHAR(100) NOT NULL,

isActive TINYINT(1) DEFAULT 0,

PRIMARY KEY (userID)

) TYPE=InnoDB

Note: You should never store the original password in the database — a good security precaution.

 

CREATE TABLE JET_PENDINGUSER (

userID INTEGER UNSIGNED NOT NULL,

token CHAR(10) NOT NULL,

createDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

FOREIGN KEY (userID)

REFERENCES JET_USER(userID)

) TYPE = InnoDB

Note: The columns could be merged with “user” table , but I chose to separate them since the pending token is only used once. “pendingUser” lets you go back to the database and delete pending accounts that haven’ t been activated after a certain amount of time.

 

 

Plan the Directory Layout

• @xampp\htdocs: create “MyPHP” folder

• Create 3 folders

o public_files  to store all publicly accessible files

o lib  to store include files

o templates  to store presentation files

Note: PHP will be able to reference files from anywhere in your setup, the web server should only serve files from the public_files folder. Keeping support files outside of the publicly accessible directory increase security.

 

 

 

 

Todo’s:

1. Download MyPHP.zip from http://groups.yahoo.com/group/xxxx [i'll upload this to [nofileshares] or perhaps someone could PM me then I'll send the file for you and post it on rapid share]

2. Extract UserRegistration.zip @ C:\xampp\htdocs

3. @ your browser address bar, type: http://localhost/myphp/public_files/register.php

4. if error occurred;

a. make sure your php and mysql are running. Check xampp control panel.

b. Sometimes, you won’t be able to start MySQL and Apache because of IIS interference so you better turn it off or uninstall if not needed.

i. Click Start, Run

ii. Type: services.msc

iii. Locate IIS, then click “STOP”

0

Share this post


Link to post
Share on other sites

sir,thans 4 d help, d link u provided to me, is for??? please explain... i vud b grateful 2u...

and also all wtever u wrote needs knowledge of dbms???

0

Share this post


Link to post
Share on other sites

Update: Section 1 to 4, Presentation 1 to 4, PHP code, DB internet research are now downloadable.

Note: The presentation was intended for Trainer Led presentation, try to search google if it gets complicated.

Edited by JetX
0

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0