|
  
7.
Inserting, Updating and Deleting Data
 7.1
Inserting Data
7.2
Updating Data
7.3
Deleting Data
7.
Inserting, Updating and Deleting Data
7.1
Inserting Data
There are
two ways to insert data into a table using SQL. The first method uses
the standard SQL syntax, and the other uses host variables. Host variables
allow you to set up an insert statement where the data to be inserted
is not known at the time the command executes. DBMaker will then enter
the Value state, which allows you to enter the values for multiple records.
The following
command uses the standard SQL insert command to insert data:
dmSQL> insert into Employees values (10000, `Gabrial', `Davis', 10000, `228-6932', `1/21/57', `4/24/95');
1 row inserted
where
Employees is the table name. This statement lets you insert the
values 10000, Gabrial, Davis, 10000, 228-6932, 1/21/57, and 4/24/95
into the columns Number, FirstName, LastName, Manager,
Phone, BirthDate and HireDate.
The next
command also uses the standard SQL insert command, but only inserts
data into the specified columns:
dmSQL> insert into Employees (FirstName,LastName,Manager) values (`Greg', `Carter', 10002);
1 row inserted
This statement
inserts the values Greg, Carter, and 10002 into the FirstName,
LastName and Manager columns respectively. The value of
all the unspecified columns is NULL.
dmSQL> insert into Employees values (NULL, `Dean', `Cogar');
1 row inserted
This statement
inserts the next SERIAL value in the serial number sequence, and the
values Dean and Cogar into the FirstName and LastName
columns.
Using
Host Variables
The syntax of INSERT
with the host variables is the same as the SQL standard. An INSERT statement
with host variables makes dmSQL enter into the VALUE state when the
screen prompt of dmSQL/Val>.
dmSQL> insert into Employees values (?,?,?,?,?,?,?);
dmSQL/Val> NULL, `Benson', `Armstrong',10002, `918-3517', `12/9/70', `3/2/93';
1 row inserted
dmSQL/Val> NULL, `Lyn', `Belger', 10000,`363-4511', `5/9/59', `12/6/91';
1 row inserted
dmSQL/Val> end;
dmSQL>
The preceding
statements are equivalent to the following statements.
dmSQL> insert into Employees values (NULL, `Benson', `Armstrong', 10002, `918-3517', `12/9/70', `3/2/93');
dmSQL> insert into Employees values (NULL, `Lyn', `Belger', 10000, `363-4511', `5/9/59', `12/6/91');
The following
example shows how you can enter a constant for some values and host
variables for others in an INSERT statement:
dmSQL> insert into Employees values (NULL, ?, ?, 10002, ?, ?, ?);
value> 'Murphy', `Flaherty', `575-8846', `10/17/77', `11/17/90';
1 row inserted
value> 'Taylor', `Galbreath', `648-6633', `2/9/75', `10/22/94';
1 row inserted
value> end;
dmSQL>
The preceding
example is equivalent to the following INSERT commands.
dmSQL> insert into Employees values (NULL, 'Murphy', `Flaherty', 10002, `575-8846', `10/17/77', `11/17/90');
dmSQL> insert into Employees values (NULL, 'Taylor', `Galbreath', 10002, `648-6633', `2/9/75', `10/22/94');
Inserting
Different Data Types
DBMaker requires that you input data types as
shown below:
123, -252783
FLOAT
and DOUBLE:
float: 30000.05, -234.56
double: 234.56e-257, 6.04E+23
CHAR
and VARCHAR:
'Hello', 'DBmaker is a powerful database !'
BINARY:
dmSQL
has two formats to identify the binary type. One type is the hex format,
the other one is the same as the CHAR type.
In CHAR
format, every character represents one byte. However, the value stored
in the database is the ASCII value of the character. For the char
type:
'abcedf', '!@#$%'
In hex
format, every two hex codes represent one byte. You can use hex codes
0-9, a-f (or A_F) to represent binary data. Binary data in hex format
must be enclosed by single quotes, and followed by a character x (or
X). For example, the binary data '61'x is the ASCII value of 'a'.
For the hex type:
'0001020304'x, '3f2eff5c'x
DATE and TIME:
'1994-12-20'd, '14:10:20't
DECIMAL:
12.34, _0.123
Inserting
Blob Data
DBMaker
supports BLOB data. These data types are LONG VARCHAR and LONG VARBINARY.
The difference between LONG VARCHAR and LONG VARBINARY is the same as
the difference between CHAR and BINARY. In dmSQL, there are several
ways to insert BLOB data.
- Insert
BLOB data with an SQL command.
dmSQL> create table blob_table (lcharcol long varchar,
2> lbincol long varbinary);
dmSQL> insert into blob_table values ('this is blob data', '2d2d2d2d'x);
1 row inserted
Insert
BLOB data with host variables.
dmSQL> insert into blob_table values (?,'5f5f5f5f5f'x);
You
have several ways to bind BLOB data to host variables. You can use
a string-like BLOB:
dmSQL/Val> 'blob using host variable';
1 row inserted
Or you
can insert BLOB data from an external file. To insert BLOB data from
an external file, type &file_name in INSERT mode.
dmSQL/Val> &comment.txt;
1 row inserted
comment.txt
is a file in the current directory.
7.2
Updating Data
DBMaker
provides three ways to update column data by using standard SQL, host
variables, or OIDs.
dmSQL> update Employees set Manager = 10000 where LastName = 'Carter';
1 row updated
where
Employees is the table name. You use this statement to change the manager
for Greg Carter.
Using
Host Variables
An update
statement with host variables makes dmSQL enter into the value state
with the dmSQL/Val> prompt. In the value state you can enter data
for the corresponding host variables. You use end; to exit the value
state and complete the update statement.
dmSQL> update Employees set Phone = ? where FirstName = ? and LastName = ?;
dmSQL/Val> `736-8376', `Gabrial', `Davis';
1 row updated
dmSQL/Val> `837-7847', `Lyn', `Belger';
1 row updated
dmSQL/Val> end;
dmSQL>
The preceding
statements are equivalent to the following statements if you do not
use host variables:
dmSQL> update Employees set Phone = `736-8376' where FirstName = `Gabrial' and LastName = `Davis';
dmSQL> update Employees set Phone = `837-7847' where FirstName = `Lyn' and LastName = `Belger';
Using
OIDs
The OID
(object id) is a special binary data type that contains the record ID
of an object. Each row of a table has a unique OID. You can select an
OID from a table, and then update its data using that OID. OIDs are
usually used in the internal programming interface, and not directly
in the interactive dmSQL environment.
dmSQL> select oid from Employees where FirstName = `Dean' and LastName = `Cogar';
oid
================
0200000002000200
1 rows selected
dmSQL> update Employees set BirthDate = `12/8/70' where oid='0200000002000200'x;
7.3
Deleting Data
To delete
rows from a table, dmSQL provides three methods: standard SQL, host
variables, or OIDs.
Using
Standard SQL
The syntax
of DELETE is the same as the SQL standard.
dmSQL> delete from Employees;
This command
deletes all rows from the Employees table.
dmSQL> delete from Employees where Number > 10030;
This command
delete rows which satisfy the condition Number > 10030 from
the Employees table.
Using
Host Variables
A delete
statement with host variables makes dmSQL enter into the value state
with the dmSQL/Val> prompt. In the value state you can enter
data for the corresponding host variables. You use end; to exit
the value state and complete the update statement.
dmSQL> delete from Employees where FirstName = ?;
dmSQL/Val> `Benson';
dmQL/Val> `Murphy';
dmSQL/Val> end;
dmSQL>
The preceding
statements are equivalent to the following statements.
dmSQL> delete from Employees where name = `Benson';
dmSQL> delete from Employees where name = `Murphy';
Using
OIDs
Although
it is possible to manipulate data using OIDs, the OID is a special binary
data type that is usually used internally by the database. It is unusual
to use OIDs directly in dmSQL.
dmSQL> select oid from Employees where FirstName = `Taylor';
oid
================
0200000002000700
1 rows selected
dmSQL> delete from Employees where oid='0200000002000700'x;
|
