OPEN SQL:插入、删除、修改语法
1. UPDATE 用于实现对数据据的更新操作，语法如下：
UPDATE <dbtab> set f1...fn (where <condition>).
UPDATE <dbtab> FROM TABLE <itab> (where <condition>).
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值。
例如1：更新某一行中的一个列
| LastName | FirstName | Address | City |
|---|---|---|---|
| Gates | Bill | Xuanwumen 10 | Beijing |
| Wilson | Champs-Elysees |
我们为lastname是Wilson的人添加firstname:
UPDATE PERSON SET FIRSTNAME = 'Fred'
WHERE LASTNAME = 'Wilson'
结果：
| LastName | FirstName | Address | City |
|---|---|---|---|
| Gates | Bill | Xuanwumen 10 | Beijing |
| Wilson | Fred | Champs-Elysees |
例如2：更新某一行中的若干列
如上，我们要修改地址（address）,并添加城市名称（city）:
UPDATE PERSON SET ADDRESS = 'Zhongshan 23'
CITY = 'Nanjing'
WHERE LASTNAME = 'Wilson'.
结果：
| LastName | FirstName | Address | City |
|---|---|---|---|
| Gates | Bill | Xuanwumen 10 | Beijing |
| Wilson | Fred | Zhongshan 23 | Nanjing |
例如3：
UPDATE SCUSTOM SET:DISCOUNT = '003',
TELEPHONE = '0592-123456'
WHERE ID = '456789'.
2.INSERT 用于实现对数据的新增，其语法结构如下：
INSERT INTO <dbtab> VALUES <condition>.
INSERT <dbtab> FROM TABLE <itab>.
INSERT INTO 表名称 VALUES （值1,值2,...） "指插入新的行
INSERT INTO table_name(列1,例2,...) VALUES (值1,值2,...) "指在指定的列中插入数据
例如1：在这一行下插入新的行lastname = gates的值
| LastName | FirstName | Address | City |
|---|---|---|---|
| Carter | Thomas | Changan Street | Beijing |
INSERT INTO PERSONS VALUES ( 'Gates', 'Bill', 'Xuanwumen 10', 'Beijing' )
结果：
| LastName | FirstName | Address | City |
|---|---|---|---|
| Carter | Thomas | Changan Street | Beijing |
| Gates | Bill | Xuanwumen 10 | Beijing |
例如2.在指定列中插入数据
| LastName | FirstName | Address | City |
|---|---|---|---|
| Carter | Thomas | Changan Street | Beijing |
| Gates | Bill | Xuanwumen 10 | Beijing |
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
结果：
| LastName | FirstName | Address | City |
|---|---|---|---|
| Carter | Thomas | Changan Street | Beijing |
| Gates | Bill | Xuanwumen 10 | Beijing |
| Wilson | Champs-Elysees |
例3.
TABLES SCUSTOM.
SCUSTOM-ID = '11279'.
SCUSTOM-NAME = 'JACK BLOGS'.
SCUSTOM-POSTCODE = '658896'.
SCUSTOM-CITY = 'SIGAPO'.
INSERT INTO SCUSTOM VALUES SCUSTOM. "从某一结构体获取新增数据
TABLES SAIRPORT.
SAIRPORT-ID = 'KIT'.
SAIRPORT-NAME = 'NEW ZLAND'.
INSERT SAIRPORT. "直接对表HEADER LINE赋值新增
3. DELETE 用于删除表中的数据，其语法结构如下：
DELETE FROM dbtab WHERE cond.
DELETE dbtab.
DELETE dbtab FROM TABLE itab.
TABLES SBOOK.
DELETE FROM SBOOK WHERE CARRID = 'LH'
AND CONNID = '0400'
AND FLDATE = '19950228'."按赋值条件删除透明表数据
TABLES SBOOK.
SBIIK-CARRID = 'LH'.
SBOOK-CONNID = '0400'.
SBOOK-FLDATE = '19950228'.
SBOOK-BOOKID = '00000003'.
DELETE SBOOK."按结构体条件删除表数据，按内表数据进行删除基本类似
4.MODIFY 用于修改表中的数据
当修改条件符合时，直接按条件更新表数据，若改数据不存在，则在数据表中新增一条，通过MODIFY修改或新增数据的执行效率，此使用UPDATE和INSERT效率你，其语法如下：
MODIFY dbtab.
MODIFY dbtab FROM TABLE itab.
TABLES SCUSTOM.
SCUSTOM-ID = '186999868'.
SCUSTOM-NAME = 'DAYRON ROBLES'.
SCUSTOM-POSTCODE = '39866'.
SCUSTOM-CITY = 'CUBA'.
MODIFY SCUSTOM."该表以ID为主键，此时数据库中没有符合ID='186999868'的数据，故先插入一条.
TABLES SCUSTOM.
SCUSTOM-ID = '186999868'.
SCUSTOM-NAME = 'DAYRON ROBLES'.
SCUSTOM-POSTCODE = '39866'.
SCUSTOM-CITY = 'CUBA'.
MODIFY SCUSTOM. "此时数据库中存在ID='186999868'的数据，故将其更新。
