Basic SQL statements: DDL and DML
SQL statements are divided into two major categories:
data definition language (DDL) and data manipulation
language (DML). Both of these categories contain far more statements
than we can present here, and each of the statements is far more complex
than we show in this introduction.
Data definition language
DDL statements are used to build and modify the structure of your
tables and other objects in the database. When you execute a DDL statement, it
takes effect immediately.
• The create table statement does exactly that:
CREATE TABLE <table name> (
<attribute name 1> <data type 1>,
...
<attribute name n> <data type n>);
• The alter table statement may be used as you have seen to specify
primary and foreign key constraints, as well as to make other modifications to the table structure.
Key constraints may also be specified in the CREATE TABLE statement.
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> PRIMARY KEY (<attribute list>);
• The foreign key constraint is a bit more complicated, since
we have to specify both the FK attributes in this (child) table, and the PK attributes
that they link to in the parent table.
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> FOREIGN KEY (<attribute list>)
REFERENCES <parent table name> (<attribute list>);
• If you totally mess things up and want to start over, you
can always get rid of any object you’ve created with a drop
statement. The syntax is different for tables and constraints.
DROP TABLE <table name>;
ALTER TABLE <table name>
DROP CONSTRAINT <constraint name>;
• All of the information about objects in your schema is
contained, not surprisingly, in a set of tables that is called the data
dictionary. There are hundreds of these tables most database systems,
but all of them will allow you to see information about your own tables,
in many cases with a graphical interface. How you do this is entirely
system-dependent.
Data manipulation language
DML statements are used to work with the data in tables. When you are
connected to most multi-user databases (whether in a client program or by a connection
from a Web page script), you are in effect working with a private copy of your
tables that can’t be seen by anyone else until you are finished (or tell the
system that you are finished). You have already seen the SELECT statement; it is
considered to be part of DML even though it just retreives data rather than
modifying it.
• The insert statement is used, obviously, to add
new rows to a table.
INSERT INTO <table name>
VALUES (<value 1>, ... <value n>);
• The update statement is used to change values
that are already in a table.
UPDATE <table name>
SET <attribute> = <expression>
WHERE <condition>;
• The delete statement does just that, for rows
in a table.
DELETE FROM <table name>
WHERE <condition>;
• If you are using a large multi-user system, you
may need to make your DML changes visible to the rest of the
users of the
database. Although this might be done automatically when you log
out, you could also just type:
COMMIT;
• If you’ve messed up your changes in this type of system, and want
to restore your
private copy of the database to the way it was before you started (this only works if
you haven’t already typed COMMIT), just type:
ROLLBACK;