Chemistry Reference
In-Depth Information
the table will be updated! This is almost never intended, but can be useful
when necessary.
The following SQL command will delete one row from the struc-
ture table.
Delete from achemcompany.structure where id=1002;
Again, it is important to use a
Where
clause in the delete statement to specify
precisely which rows are to be deleted and to prevent accidental deletion.
There are many other SQL commands besides
Create
,
Insert
,
Update
,
Delete
, and
Select
. For example, the
Alter
command mod-
ifies tables, columns, schemas, and other aspects of the database. There
are many topics that describe the SQL language. Most are speciic to one
particular RDBMS, such as PostgreSQL or Oracle. Many examples in this
topic will work in PostgreSQL, Oracle, MySQL and any other RDBMS
that follows the SQL standard. However, some examples use data types
and functions that differ among the various RDBMS. In those examples,
PostgreSQL is used. In other words, all examples in this topic will oper-
ate correctly using PostgreSQL. Most examples will work with Oracle and
MySQL as well. Similarities and differences among various RDBMS are
discussed in Chapter 4.
3.7 SQL Functions
There are many SQL functions available to perform common operations
on data values. For example,
sqrt
,
sin
, and
abs
operate on numerical
data. Text data can be operated on using functions such as
substring
,
lower
, and
trim
. These functions are explained in any topic on SQL,
although many of them are self-explanatory. This section will show how
to create new functions for use in SQL. These functions may be written
using SQL itself, or using the various procedural languages such as plsql,
plpgsql, or plpython, depending on which RDBMS is being used.
There is another class of functions called aggregate functions. There
are several standard SQL aggregate functions, such as
sum
,
max
, and
avg
.
These are called aggregate functions because, rather than operating on a
single value such as
sqrt
, they operate on multiple values. The result is an
aggregate value such as a sum, maximum, or average of the multiple input
values. This section shows how to use the standard SQL aggregate functions
and explains how to create new aggregate functions for use with SQL.
3.7.1 Regular Functions
One type of SQL function is simply a collection of SQL statements. The
input data type must be defined along with the data type of the result of
Search WWH ::
Custom Search