Home arrow MySQL arrow Page 2 - Taking a Look at MySQL 4.1

MySQL 4.1 - MySQL

Many of you are still working with earlier versions of the MySQL database. This article takes a look at MySQL 4.1. It is the first of several parts that examine more recent versions of the software. It is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324).

TABLE OF CONTENTS:
  1. Taking a Look at MySQL 4.1
  2. MySQL 4.1
  3. Subqueries As Scalar Values
  4. Benefits of Subqueries
  5. Other New Features in MySQL 4.1
By: Apress Publishing
Rating: starstarstarstarstar / 11
April 20, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Weve already talked about some of the features that are new in MySQL 4.1, such as prepared statements and multiple statements and the SHOW WARNINGS command, in previous chapters. In this section well discuss what are probably the two most important new additions in MySQL 4.1 because of the increased speed, flexibility, and power they lend to SELECTqueries. These are subqueries (also known as nested queries or subselects) and derived tables. In addition, as well see later in this chapter, they mark an important stage in MySQLs evolution, as they pave the way for enterprise-level features such as stored procedures, triggers, and views.

Subqueries and Derived Tables

Try as we might to combine queries using the various types of joins, SQL functions, and operators that weve looked at previously in this book, sometimes its necessary to use more than one query to derive the exact data that we require. Even in cases where we can use a single query to obtain a desired result, we find that what seems conceptually very simple often requires very complex joins or unions. In MySQL 4.1, these problems can often be overcome through the use of subqueries, that is, queries within other queries. Being able to use subqueries can ease matters greatly, and they can allow us to combine queries or simplify them considerably. They also provide for structured queries, in which the different parts of a query can be considered apart from the others. This tends to make them much more easily read and understood than the more complex statements that they can be used to replace.

Subqueries can be used in several places within queries and in several ways. In the next few examples, well use two tables, representing products and categories of products. (You may recognize some of the products table data from the QA Testing example in Chapter 2.) These tables are structured as shown in the following twoCREATE TABLEstatements:

CREATE TABLE products (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  category_id INT(11) NOT NULL,
  name VARCHAR(30) NOT NULL,
  price DECIMAL(6,2) NOT NULL
);
CREATE TABLE categories (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL
);

Each product has a name and a price. Each product belongs to a category (and only one category); this relationship is indicated by the category_id column in the products table, which serves as a foreign key linking to the categories table.

The table creation statements and some statements to insert sample data are included in the ch8 directory of the code download for this book (available from the Downloads section ofhttp://www.apress.com).

Subqueries can be used in any of theSELECT,INSERT,UPDATE,DELETE, orSETstatements. (They can also be used with theDOstatement, which well see when we discuss stored procedures later in this chapter.) Subqueries can include any of the constructs found in any otherSELECTquery, includingFROMandWHEREclauses, joins,LIMITclauses, unions, function calls, and so forth.



 
 
>>> More MySQL Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: