| [ Team LiB ] |
|
10.1 BackgroundSQL 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 a programming language! A query is a fully specified command sent to the database server. The database server then performs the requested action. Here's an example of an SQL query: SELECT name FROM Person WHERE name LIKE 'Stac%'; This statement reads almost like a form of broken English: "Select names from a list of persons where the names are like `Stac'." SQL uses few of the formatting and special characters generally associated with computer languages. 10.1.1 The SQL StoryIBM 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, 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; you were a hardcore programmer or did not program at all. The people who programmed computers 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 accessing 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 it spawned an entire industry built around SQL. Sybase, Informix, Microsoft, and several other companies have since come forward with their implementations of SQL-based relational database management systems (RDBMSs). When Oracle and its competitors first hit the scene, SQL was still relatively new and no standard existed. It was only in 1989 that the ANSI standards body issued the first public SQL standard. These days, that standard is often referred to as SQL89. Unfortunately, the standard 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. People refer to the 1992 standard as both SQL92 and SQL2. The SQL2 standard expanded the language to accommodate as many of the proprietary extensions added by the commercial vendors as possible. Many cross-DBMS tools—including JDBC—have standardized on SQL2 as their mode of communication with relational databases. Due to the extensive nature of the SQL2 standard, however, relational databases that implement the full standard are very complex beasts.
SQL2 defines several levels of compliance to address its complexity. The most important level—the one required by JDBC—is SQL92, entry level. Entry level defines the core SQL syntax. If your goal is to write portable applications, you should go no further in the SQL specification than entry level. 10.1.2 Database InteractionAny number of methods exists for sending SQL to a database engine and retrieving the results of your command. Throughout most of this book, you are using Java's JDBC API to handle that interaction. For the purposes of this chapter, however, you will need to use a tool that interactively sends SQL to your database. Each database engine comes with at least one such tool. In general, there are both command-line and GUI-interactive SQL tools. MySQL, for example, provides the mysql commands line utility. PostgreSQL similarly provides a similar tool called psql. Before going any further in this chapter, I recommend you find out the tool that comes with your database so you can try the examples that come later. When you run a command-line program like mysql, it prompts you for SQL: [09:04pm] carthage$ mysql -u root -p jtest 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 database jtest 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 jtest 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 or the \g sequence before executing the SQL: mysql> SELECT book_number
-> FROM book
-> ;
+-------------+
| book_number |
+-------------+
| 1 |
| 2 |
| 3 |
+-------------+
3 rows in set (0.00 sec)
GUI utilities generally provide you with a text box into which you can enter SQL. Pressing Enter or clicking a button to send the SQL to the database will execute the SQL for you. The tool then displays the results in a graphical table. In some cases, you can even manipulate the table and the tool will create and send to the database SQL that updates the underlying database. 10.1.3 Basic SyntaxAs I 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 Person ( name CHAR(10) ); verb direct object adjective phrase INSERT INTO Person ( name ) VALUES ( 'me' ); verb indirect object adjective phrase direct object SELECT name FROM people WHERE name like '%e'; verb direct object indirect object adjective phrase Most SQL implementations are case-insensitive. In other words, it does not matter how you type SQL keywords as long as the spelling is correct. The previous CREATE example is just as valid when written like this: cREatE TAblE Person ( name ChAr(10) ); This case-insensitivity extends only to SQL keywords.[2] In some database engines, identifiers are also case-insensitive. For others, they are case-sensitive. Still, the case-sensitivity of other database engines like MySQL depends on the underlying operating system. Most MySQL identifiers are case-sensitive; however, table names and database names are case-sensitive only on operating systems whose filesystems are case-sensitive. It is therefore good practice to assume that your identifiers are case-sensitive in order to guarantee portability of your SQL across all database engines.
The first element of an SQL query is always a verb. The verb expresses the action you wish the database engine to take. The most commonly used verbs are:
Although what follows the verb varies depending on the verb used, 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 application. The client constructs a query based on user actions and sends the query to the database engine. The database engine 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 toward how it can be used to manipulate data within the database.
 |
| [ Team LiB ] |
|
