Chapter 6. SQL According to MySQL and mSQL
Contents:
SQL Basics
Creating and Dropping Tables
SQL Datatypes
Indices
Sequences and Auto-Incrementing
Managing Data
Queries
Extended Functionality
The Structured Query Language (SQL) is the language used to read and write to MySQL and mSQL databases. Using SQL, you can search for data, enter new data, modify data, or delete data. SQL is simply the most fundamental tool you will need for your interactions with MySQL and mSQL. Even if you are using some application or graphical user interface to access the database, somewhere under the covers that application is generating SQL.
SQL is a sort of "natural" language. In other words, an SQL statement should read -- at least on the surface -- like a sentence of English text. This approach has both benefits and drawbacks, but the end result is a language very unlike traditional programming languages such as C, Java, or Perl.
In this chapter, we take a look at the SQL language as supported in MySQL and mSQL. For the most part, MySQL's dialect is a superset of mSQL's. We will carefully note the instances where the two dialects diverge. For the most part, however, this chapter applies to both database engines.
6.1. SQL Basics
SQL[7] is "structured" in the sense that it follows a very specific set of rules. A computer program can easily parse a formulated SQL query. In fact, the O'Reilly book lex & yacc by John Levine, Tony Mason, and Doug Brown implements a SQL grammar to demonstrate the process of writing a program to interpret language! A query is a fully-specified command sent to the database server, which then performs the requested action. Below is an example of an SQL query:
[7]Pronounced either "sequel" or "ess-que-ell." Certain people get very religious about the pronunciation of SQL. Ignore them. It is important to note, however, that the "SQL" in mSQL and MySQL is properly pronounced "ess-que-ell."
SELECT name FROM people WHERE name LIKE `Stac%'
As you can see, this statement reads almost like a form of broken English: "Select names from a list of people where the names are like Stac." SQL uses very few of the formatting and special characters that are typically associated with computer languages. Consider, for example, "$++;($*++/$|);$&$^,,;$!" in Perl versus "SELECT value FROM table" in SQL.
6.1.1. The SQL Story
IBM invented SQL in the 1970s shortly after Dr. E. F. Codd first invented the concept of a relational database. From the beginning, SQL was an easy to learn, yet powerful language. It resembles a natural language such as English, so that it might be less daunting to a nontechnical person. In the 1970s, even more than today, this advantage was an important one.
There were no casual hackers in the early 1970s. No one grew up learning BASIC or building web pages in HTML. The people programming computers were people who knew everything about how a computer worked. SQL was aimed at the army of nontechnical accountants and business and administrative staff that would benefit from being able to access the power of a relational database.
SQL was so popular with its target audience, in fact, that in the 1980s the Oracle corporation launched the world's first publicly available commercial SQL system. Oracle SQL was a huge hit and spawned an entire industry built around SQL. Sybase, Informix, Microsoft, and several other companies have since come forward with their implementations of a SQL-based Relational Database Management System (RDBMS).
At the time Oracle and its first competitors hit the scene, SQL was still brand new and there was no standard. It was not until 1989 that the ANSI standards body issued the first public SQL standard. These days it is referred to as SQL89. This new standard, unfortunately, did not go far enough into defining the technical structure of the language. Thus, even though the various commercial SQL languages were drawing closer together, differences in syntax still made it nontrivial to switch among implementations. It was not until 1992 that the ANSI SQL standard came into its own.
The 1992 standard is called both SQL92 and SQL2. The SQL2 standard expanded the language to accommodate as many of the proprietary extensions added by the commercial implementations as was possible. Most cross-DBMS tools have standardized on SQL2 as the way in which they talk to relational databases. Due to the extensive nature of the SQL2 standard, however, relational databases that implement the full standard are very complex and very resource intensive.
NOTE
SQL2 is not the last word on the SQL standard. With the growing popularity of object-oriented database management systems (OODBMS) and object-relational database management systems (ORDBMS), there has been increasing pressure to capture support for object-oriented database access in the SQL standard. SQL3 is the answer to this problem. It is not yet official, but it is currently very well defined and looks to become official sometime in 1999.
When MySQL and mSQL came along, they took a new approach to the business of database server development. Instead of manufacturing another giant RDBMS and risk having nothing more to offer than the big guys, they created small, fast implementations of the most commonly used SQL functionality.
6.1.2. The Design of SQL
As we mentioned earlier, SQL resembles a human language more than a computer language. SQL accomplishes this resemblance by having a simple, defined imperative structure. Much like an English sentence, individual SQL commands, called "queries," can be broken down into language parts. Consider the following examples:
CREATE TABLE people (name CHAR(10))
verb object adjective phrase
INSERT INTO people VALUES ('me')
verb indirect object direct object
SELECT name FROM people WHERE name LIKE '%e'
verb direct object indirect object adj. phraseMost implementations of SQL, including MySQL and mSQL, are case-insensitive. Specifically, it does not matter how you type SQL keywords as long as the spelling is correct. The CREATE example from above could just as well appeared:
cREatE TAblE people (name cHaR(10))
The case-insensitivity only extends to SQL keywords.[8] In MySQL and mSQL, names of databases, tables, and columns are case-sensitive. This case-sensitivity is not necessarily true for all database engines. Thus, if you are writing an application that should work against all databases, you should act as if names are case-sensitive.
[8]For the sake of readability, we capitalize all SQL keywords in this book. We recommend this convention as a good "best practice" technique.
This first element of an SQL query is always a verb. The verb expresses the action you wish the database engine to take. While the rest of the statement varies from verb to verb, they all follow the same general format: you name the object upon which you are acting and then describe the data you are using for the action. For example, the query CREATE TABLE people (CHAR(10)) uses the verb CREATE, followed by the object TABLE. The rest of the query describes the table to be created.
An SQL query originates with a client -- the application that provides the façade through which a user interacts with the database. The client constructs a query based on user actions and sends the query to the SQL server. The server then must process the query and perform whatever action was specified. Once the server has done its job, it returns some value or set of values to the client.
Because the primary focus of SQL is to communicate actions to the database server, it does not have the flexibility of a general-purpose language. Most of the functionality of SQL concerns input to and output from the database: adding, changing, deleting, and reading data. SQL provides other functionality, but always with an eye towards how it can be used to manipulate the data within the database.
 |  |  |
| 5.6. mSQL Utilities |  | 6.2. Creating and Dropping Tables |
Copyright © 2001 O'Reilly & Associates. All rights reserved.