Databases Reference
In-Depth Information
environment for running SQL commands. When you use GUI utilities, in some
environments they work by building SQL command strings and then executing
them in the background where you don't see them used. Depending on whose
definition you prefer, you will see SQL commands divided into either two or
three types of commands. You will learn more about these types of commands
in Chapter 6. The most common categories you'll see are data manipulation
language (DML) and data definition language (DDL) statements. DML state-
ments are, like the name implies, statements used to modify data, including
statements used to insert new data or delete data rows. DDL statements are used
to define server and database objects, like logins, users, tables, views, indexes,
and so forth.
You will sometimes see a third type of statement, data query language (DQL)
statements. A DQL statement refers specifically to SELECT command queries that
are used to retrieve data. Because the term
query
can be used to refer to state-
ments used to retrieve or modify data, the SELECT statement is often rolled in
as part of the DML command set.
Using SQL Commands
The standard SQL command set includes the
CREATE TABLE command,
which
can be used to create a table, specify columns and data tables, and set table prop-
erties and constraints, such as identifying the primary key.
Constraints,
in basic
terms, are limits or controls placed on tables and table columns. For example,
a table's primary key and foreign keys (if any) are technically referred to as
its primary key constraint and foreign key constraints. Another command,
ALTER TABLE, lets you modify existing tables. Here's an example CREATE
TABLE command:
CREATE TABLE dbo.Car(
SerialNumber char(30) NOT NULL,
Model varchar(50) NOT NULL,
Year char(4) NOT NULL,
Class nchar(2) NULL,
CONSTRAINT PK_Car PRIMARY KEY(SerialNumber) CLUSTERED )
ON PRIMARY
The full command syntax is beyond the scope of this chapter, but if you
take a close look at this command example, it's relatively easy to see what the
command is doing. The command name tells you that you are creating a table.
The table name in this example is Car. The dbo is the relational schema and is
part of the
fully qualified object name,
which is the complete object name that
defines the object as globally unique. The period (.) is used as a delimiter to
identify the different parts of the fully qualified name. The object name syntax
is a SQL concept, but its specific implementation can vary somewhat by DBMS.
The column names, along with basic column parameters, are enclosed in
