07-01-2013, 04:49 PM
SQL Basics
SQL Basics.pdf (Size: 111.68 KB / Downloads: 62)
A Brief History of SQL
Before we get into the nitty-gritty of SQL command syntax, let’s spend a few moments
understanding how SQL came into existence.
SQL began life as SEQUEL11, the Structured English Query Language, a component
of an IBM research project called System/R. System/R was a prototype of the first
relational database system; it was created at IBM’s San Jose laboratories in 1974, and
SEQUEL was the first query language to support multiple tables and multiple users.
In the late 1970s, SQL made its first appearance in a commercial role as the query
language used by the Oracle RDBMS. This was quickly followed by the Ingres RDBMS,
which also used SQL, and by the 1980s, SQL had become the de facto standard for the
rapidly growing RDBMS industry. In 1989, SQL became an ANSI standard commonly
referred to as SQL89; this was later updated in 1992 to become SQL92 or SQL2, the
standard in use on most of today’s commercial RDBMSs (including MySQL).
55
1 The name was later changed to SQL for legal reasons.
56 P a r t I I : U s a g e
An Overview of SQL
As a language, SQL was designed to be “human-friendly”; most of its commands
resemble spoken English, making it easy to read, understand, and learn. Commands
are formulated as statements, and every statement begins with an “action word.” The
following examples demonstrate this:
CREATE DATABASE toys;
USE toys;
SELECT id FROM toys WHERE targetAge > 3;
DELETE FROM catalog WHERE productionStatus = "Revoked";
As you can see, it’s pretty easy to understand what each statement does. This
simplicity is one of the reasons SQL is so popular, and also so easy to learn.
SQL statements can be divided into three broad categories, each concerned with
a different aspect of database management:
• Statements used to define the structure of a database These statements define
the relationships among different pieces of data, definitions for database, table
and column types, and database indices. In the SQL specification, this component
is referred to as Data Definition Language (DDL), and it is discussed in detail in
Chapter 8 of this book.
• Statements used to manipulate data These statements control adding and
removing records, querying and joining tables, and verifying data integrity.
In the SQL specification, this component is referred to as Data Manipulation
Language (DML), and it is discussed in detail in Chapter 9 of this book.
• Statements used to control the permissions and access level to different pieces
of data These statements define the access levels and security privileges for
databases, tables and fields, which may be specified on a per-user and/or
per-host basis. In the SQL specification, this component is referred to as Data
Control Language (DCL), and it is discussed in detail in Chapter 14.
Typically, every SQL statement ends in a semicolon, and white space, tabs, and
carriage returns are ignored by the SQL processor. The following two statements
Breaking the Rules
Although most of today’s commercial RDBMSs do support the SQL92 standard,
many of them also take liberties with the specification, extending SQL with
proprietary extensions and enhancements. (MySQL is an example of such.) Most
often, these enhancements are designed to improve performance or add extra
functionality to the system; however, they can cause substantial difficulties when
migrating from one DBMS to another.
A complete list of MySQL’s deviations from the SQL specification is available
at http://www.mysqldoc/en/Compatibility.html.
are equivalent, even though the first is on a single line and the second is split over
multiple lines.
DELETE FROM catalog WHERE productionStatus = "Revoked";
DELETE FROM
catalog
WHERE productionStatus =
"Revoked";
A (My)SQL Tutorial
With the language basics out of the way, let’s run through a quick tutorial to get you
up to speed on a few more SQL basics. In the following section, we’ll design a set of
relational tables, create a database to store them, re-create the table design in MySQL,
insert records into the database, and query the system to obtain answers to several
burning questions.
At this point, we’ll encourage you to try out the examples that follow as you’re
reading along. This process will not only give you some insight into how MySQL
works, but it will also teach you the fundamentals of SQL in preparation for the
chapters ahead.