Home arrow MySQL arrow Page 5 - Views and More in MySQL 5.0

Other Improvements in MySQL 5.0 - MySQL

MySQL 5.0 supports a range of features that earlier versions of MySQL do not support. This article, the fourth in a series, explains views and other new features. 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. Views and More in MySQL 5.0
  2. Syntax--Creating, Altering, and Dropping Views
  3. Examples
  4. Benefits
  5. Other Improvements in MySQL 5.0
  6. Change in User Variable Behavior
By: Apress Publishing
Rating: starstarstarstarstar / 25
May 11, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
 

There are some additional new features in MySQL 5.0 that you may find useful. These are detailed in the next few subsections.

New SELECT INTO Syntax

We’ve already seen how this can be used with cursors in a stored procedure. You can also use it to assign values to user variables in a regular SELECT query, e.g., SELECT name INTO @prodname FROM products WHERE prodid = 327; . Note that the SELECT must return a single row for this to work. We show an example here:

New Timestamp Functions

The TIMESTAMPADD() and TIMESTAMPDIFF() functions are introduced in MySQL 5.0.0. Here are the definitions for each:

TIMESTAMPADD(interval, expr, datetime) TIMESTAMPDIFF(interval, datetime1, datetime2)

TIMESTAMPADD() adds expr interval intervals to datetime and returns the result, where expr is an integer value. TIMESTAMPDIFF() returns the integer difference between datetime1 and datetime2 in terms of the unit supplied as interval .

In both cases, the interval argument may be any one of the values FRAC_SECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR , and any of these may optionally be prefixed with SQL_TSI_ . The datetime expressions may be any valid DATE or DATETIME values.

A few examples, as shown here, may help to make clear how these functions are used:

Notice that negative values are possible both for the value of expr input to the TIMESTAMPADD() function as well as for the result returned by TIMESTAMPDIFF() .



 
 
>>> 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: