SQL According to MySQL (Managing and Using MySQL, 2nd Edition)

The Structured Query Language (SQL) is used to read and write to MySQL databases. Using SQL, you can search for, enter, modify, or delete data. SQL is the most fundamental tool you will need for your interactions with MySQL. Even if you are using some application or graphical user interface to access the database, somewhere under the hood 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 unlike traditional programming languages such as C, Java, or Perl.

3.1. SQL Basics

SQL is "structured" in the sense that it follows a very specific set of rules. A computer program can parse a formulated SQL query easily. In fact, the O'Reilly book lex & yacc by John Levine, Tony Mason, and Doug Brown implements an 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. Here's an example of an SQL query:

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 few of the formatting and special characters that are typically associated with computer languages.

3.1.1. The SQL Story

IBM invented SQL in the 1970s shortly after Dr. E. F. Codd 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 it is less daunting to a nontechnical person. In the 1970s, even more than today, this advantage was important.

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 who would benefit from being able to access the power of a relational database.

When Oracle and its first competitors hit the scene, SQL was still relatively new and there was no standard. It was not until 1989 that the ANSI standards body issued the first public SQL standard. These days, the standard is referred to as SQL89. That 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 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 resource intensive.

TIP: 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. The recent SQL3 standard is the answer to this problem.

When MySQL came along, it 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, Monty created a small, fast implementation of the most commonly used SQL functionality. Over the years, that basic functionality has grown to support just about anything you might want to do with most database applications.

3.1.2. The Design of SQL

As we mentioned earlier, SQL resembles a human language more than a computer language because it has 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         adjective phrase

Most implementations of SQL, including MySQL, are case insensitive. Specifically, it does not matter how you type SQL keywords as long as the spelling is correct. The previous CREATE example could just as well be:

cREatE TAblE people (name cHaR(10))

The case insensitivity extends only to SQL keywords.[5] In MySQL, 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 assume that names are case sensitive.

[5]For the sake of readability, we capitalize all SQL keywords in this book. We recommend this convention as a solid "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 (name 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 must then process the query and perform the specified action. 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.

3.1.3. Sending SQL to MySQL

You can send SQL to MySQL using a variety of mechanisms. The most common way is through one of the programming APIs described in Part III. For the purposes of this chapter, however, we recommend you use the interactive command-line tool, mysql. When you run this program at the command line, it prompts you for SQL:

[09:04pm] carthage$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.22.29

Type 'help' for help.

mysql>

The previous mysql command says to connect to the MySQL server on the local machine as the user root (the -u option) with the client prompting you for a password (the -p option). Another option, -h, enables you to connect to MySQL servers on remote machines:

[09:04pm] carthage$ mysql -u root -h db.imaginary.com -p

There is absolutely no relationship between operating-system usernames and MySQL usernames. In other words, MySQL keeps its own list of users, and a MySQL administrator needs to add new users to MySQL independently of the host on which they reside. No one, therefore, has an account on a clean MySQL installation except root. This root is not the same root as your Unix root account. As a general rule, you should never connect to MySQL as root except when performing database administration tasks. If you have a clean installation of MySQL that you can afford to throw away, it is useful to connect as root for the purposes of this chapter so you can create and drop databases. Otherwise, you will have to connect to MySQL as whatever username has been assigned to you.

Once mysql is running, you can enter your SQL commands all on a single line or split them across multiple lines. MySQL waits for a semicolon before executing the SQL:[6]

[6]MySQL also accepts \g at the end of an SQL statement to indicate that the SQL should be executed.

mysql> SELECT book_number
    -> FROM book
    -> ;
+-------------+
| book_number |
+-------------+
|           1 |
|           2 |
|           3 |
+-------------+
3 rows in set (0.00 sec)

With the mysql command line, you generally get a command history depending on how your client tools were compiled. If a command history is compiled into your mysql client, you can use the up and down arrows on your keyboard to navigate through previously executed SQL commands.

Chapter 3. SQL According to MySQL

Contents:

3.1. SQL Basics

3.1.1. The SQL Story

3.1.2. The Design of SQL

3.1.3. Sending SQL to MySQL