Summary

MySQL provides a number of internal functions that allow you, the database administrator or application
programmer, to accomplish complex tasks within the database. Today, you learned about the advantages
and disadvantages of including logic on the database server. You also learned some guidelines to determine
where the best place is to put your application logic. You also learned about the various intrinsic functions
MySQL has to offer.

Q&A

Q: MySQL has a lot of neat functions. What if I need one that isn't there?

A:

MySQL is so flexible that it allows you to create your own functions. These

user-defined functions act the same way that MySQL's own intrinsic

functions operate. It is also possible to recompile your functions into the

application so that you will always have them, no matter how many times

you install. An overview of user-defined functions, as well as an example of

how to do one, is covered on Day 18.

Q: Why are there so many functions?

A:

The creators of MySQL included as many functions as they did for several

reasons. The main reason is that MySQL should be able to provide a

reporting mechanism for the data it contains. As you know, reporting can

become quite complicated. For that reason, many functions are provided.

Another reason is for compatibility. If you are changing database platforms,

you may have some queries in your old database that contain some intrinsic

functions. MySQL has tried to include many of the most common of these so

that transferring your old database to a better one is easy.

Exercises

1. Use some of the functions you learned today in queries using the Meet_A_Geek
   database.


2. Create an SQL statement that returns the number of people from the state of Maine
   in the Customers table of the Meet_A_Geek database.

Day 11: MySQL Table Locks and Assorted Keys

Overview

This chapter introduces you to two key concepts in the design of a database. Locks are important for
protecting data integrity in a multithreaded environment. Keys are important both for designing the
architecture of a database and for improving performance.

Today, you will learn

ƒ What a lock is, and what kinds of locks are supported by MySQL

ƒ How and when to use locks

ƒ What keys are

ƒ The concepts of primary, unique, and non-unique keys

ƒ The importance of primary keys to database structure

ƒ The performance advantages and disadvantages of using keys

What Is a lock?

Imagine the situation when your database gets rather busy, so busy that accesses are being made almost
simultaneously. While some threads are trying to read data, others need to read, perform a calculation, and
then write data back.
Note What is a thread? Imagine a thread as a sub-process that goes on within the
MySQL daemon. Every time a query is made to MySQL, it initiates a thread.
When finished, the thread dies. This way, several things can be happening
concurrently, even on the same table in a database, and yet each one is safely
kept from interfering with another.