Databases Reference
In-Depth Information
SELF-CHECK
•
List and describe available operator categories.
•
Explain the significance of operator precedence.
•
List and describe function categories.
6.4 Understanding DML Commands
Many DML commands vary in how they are implemented by various DBMSs.
Rather than trying to introduce you to all of the statements you might use or
focusing on the statement syntax for a particular DBMS, this section's goal is to
give you a general introduction to the statements. The section includes a few
ANSI standard command syntax examples as representative examples. Statement
examples are based on Microsoft SQL Server.
Popular DBMS products have full product documentation readily available. For
example, you can install complete product documentation, including command syn-
tax and command use examples, when you install SQL Server. Because of the vari-
ations in how statements are implemented, you should refer to your product-specific
documentation for information about statement syntax and specific functionality.
The ANSI SQL standard defines three basic DML statements: INSERT, UPDATE,
and DELETE. You use the INSERT statement to add rows to a table. UPDATE lets
you modify values in specified columns and rows (existing records). Run DELETE
to remove rows from tables. Keep in mind with any of these statements that you
might be prevented from taking actions due to constraints or other limits placed
on a table. For example, you might not be allowed to delete a row that is refer-
enced by another row in a different table through a foreign key constraint.
Our discussion is limited primarily to the ANSI SQL standard syntax of each
of these DML statements and their basic use. We use the statement syntax as
defined in the ANSI SQL-99 standard, the standard on which most DBMSs
implementations of the statements are based. Details of how to use advanced
functionality, such as using queries to pass retrieved values for use in DML state-
ments, is beyond the scope of this chapter.
6.4.1 Using INSERT
The standard syntax for the INSERT statement is:
INSERT INTO
table_or_view_name
[(
column_list
)] { {VALUES (
literal
|
expression
|
NULL,...) |
select_statement
| DEFAULT VALUES
