VISUAL QUICKSTART GUIDE SQL Second Edition [Electronic resources] نسخه متنی

About SQL

SQL is:

• A programming language

• Easy to learn

• Nonprocedural

• Interactive or embedded

• Standardized

• Used to change data and database objects

• Pronounced

  es-kyoo-el

• Not an acronym

A programming language .
SQL is a formal language in which you write programs to create, modify, and query databases. Your database system executes your SQL program, performs the tasks you''ve specified, and displays the results (or an error message). Programming languages differ from natural (spoken) languages in that programming languages are designed for a specific purpose, have a small vocabulary, and are inflexible and utterly unambiguous. Consequently, if you don''t get the results you expect, it''s because your program contains an erroror bugand not because the computer misinterpreted your instructions. (Debugging one''s programs is a cardinal programming task.)

SQL, like any formal language, is defined by rules of

syntax, which determine the words and symbols you can use and how they can be combined, and

semantics, which determine the actual meaning of a syntactically correct statement. Note that you can write a legal SQL statement that expresses the wrong meaning (good syntax, bad semantics). Chapter 3 introduces SQL syntax and semantics.

Easy to learn .
Easy compared with other programming languages, that is. If you''ve never written a program before, you''ll find the transition from natural to formal language frustrating. Still, SQL''s statements read like sentences to make things easy on humans. A novice programmer probably would understand the SQL statement SELECT au_fname, au_lname FROM authors ORDER BY au_lname; to mean "List the authors'' first and last names, sorted by last name," whereas the same person would find the equivalent C or Perl program impenetrable.

Nonprocedural .
If you''ve never programmed, you can skip this point without loss of continuity. If you''ve programmed in a language such as C or Perl, you''ve used a

procedural language, in which you specify the explicit steps to follow to produce a result. SQL is a

declarative language, in which you describe

what you want and not

how to do it; your database system''s optimizer will determine the "how." As such, standard SQL lacks traditional control-flow constructs such as if-then-else, while, for, and goto statements.

To demonstrate this difference, I''ve written programs that perform an equivalent task in Microsoft Access Visual Basic (VB; a procedural language) and SQL.

Listing i.1 shows a VB program that extracts author names from a table that contains author information. You needn''t understand the entire program, but note that it uses a Do Until loop to define explicitly how to extract data.

Listing i.2 shows how to do the same task with a single SQL statement (as opposed to about 20 lines of VB code). With SQL, you specify only what needs to be accomplished; the DBMS determines and performs internally the actual step-by-step operations needed to get the result.

Moreover, Listing i.2 is a trivial SQL query. After you add common operations such as sorts, filters, and joins, you might need more than 100 lines of procedural code to accomplish what a single SQL SELECT statement can do.

Interactive or embedded .
In interactive SQL, you issue SQL commands directly to your DBMS, which displays the results as soon as they''re produced. DBMS servers come with both graphical and command-line tools that accept typed SQL statements or text files that contain SQL programs (scripts).

If you''re developing database applications, you can "embed" SQL statements in programs written in a

host language, which commonly is a general-purpose language (C++, Java, or COBOL, for example) or a scripting language (Perl, PHP, or Python). A PHP CGI script can use an SQL statement to query a MySQL database, for example; MySQL will pass the query result back to a PHP variable for further analysis or web-page display. Drawing from the preceding examples, I''ve included an SQL statement in an Access Visual Basic program in

Listing i.3 .

This book covers only interactive SQL. In general, any SQL statement that can be used interactively also can be used in a host language, though perhaps with slight syntactic differences, depending on your DBMS, host language, and operating environment.

Standardized .
SQL isn''t "owned" by any particular firm. It''s an open standard defined by an international standards working group, under the joint leadership of the International Organization for Standardization (ISO) and the International Engineering Consortium (IEC). The American National Standards Institute (ANSI) participates in the working groups and has ratified the standard. "ISO/IEC SQL" isn''t a commonly used term, so I''ll stick to the better-known "ANSI SQL" name throughout this book. This book is based on the 2003 SQL standard, so you should consider

ANSI

SQL, SQL:2003, and

SQL to be synonymous unless I note otherwise. For more information, see "SQL Standards and Conformance" in Chapter 3.

All DBMS vendors add proprietary features to standard SQL to enhance the language. These extensions usually are additional commands, keywords, functions, operators, data types, and control-flow constructs such as if, while, and goto statements. Microsoft, Oracle, and IBM have added so many features to standard SQL that the resulting languagesTransact-SQL, PL/SQL, and SQL PL, respectivelycan be considered to be separate languages in their own right, rather than just supersets of SQL. One vendor''s extensions generally are incompatible with other vendors'' products. I don''t cover proprietary SQL extensions, but I do point out when a vendor''s SQL dialect doesn''t comply with the standard SQL examples in this book; see "Using SQL with a specific DBMS" later in this chapter.

Used to change data and database objects. SQL statements are divided into three categories:

• Data manipulation language (
  DML ) statements retrieve, reckon, insert, edit, and delete data stored in a database. Chapters 4 through 10 cover the DML statements SELECT, INSERT, UPDATE, and DELETE. Chapter 14 covers START (or BEGIN), COMMIT, and ROLLBACK.

• Data definition language (
  DDL ) statements create, modify, and destroy database objects such as tables, indexes, and views. Chapters 11 through 13 cover the DDL statements CREATE, ALTER, and DROP.

• Data control language (
  DCL ) statements authorize certain users to view, change, or delete data and database objects. The GRANT statement assigns privileges to users and roles (a
  role is a named set of privileges). The REVOKE statement removes privileges. GRANT and REVOKE aren''t covered in this book because they''re the responsibility of database administrators.

  All the DBMSes (except Access) covered in this book support GRANT and REVOKE, with variations on the SQL standard.

Pronounced

es-kyoo-el .
SQL isn''t pronounced

sequel; that pronunciation is a historical artifact. Avoid the error and articulate each letter:

S -

Q -

L. I disagree with people who claim that saying

sequel is so common that nothing''s wrong with it. You shouldn''t say

sequel for the same reason that you shouldn''t split infinitives in your novel; it generally may be accepted, but it will rasp on the ears of knowledgeable people. Also, pronounce MySQL as

my-es-kyoo-el and PostgreSQL as

post-gres-kyoo-el (particularly if you''re on a job interview).

Not an acronym .
It''s a common misconception that

SQL stands for

structured query language; it stands for SQL and nothing else. Why? Because ANSI says so. The official name is Database Language SQL (see
Figure i.1 ). Furthermore, referring to it as a structured query language is a disservice to new SQL programmers. It amuses insiders to point out that "structured query language" is the worst possible description, because SQL:

• Isn''t structured (because it can''t be broken down into blocks or procedures)

• Isn''t for only queries (because it has more than just the SELECT statement)

• Isn''t a language (because it''s not Turing-complete, which you''ll study should you take Theory of Computation)