Data Maintenance and S tructure

Read(241) Label: data maintenance, structure,

This chapter lists code examples about data maintenance and structure, including INSERT, INSERT FROM SELECT…, DELETE…WHERE…, UPDATE…WHERE…, CREATE/DROP TABLE, ALTER TABLE, KEYS, and CONNECT/DISCONNECT/COMMIT/ROLLBACK.

INSERT

 

A

 

1

=demo.query("select * from SCORES")

 

2

=A1.insert(0,"Class one",20,"PE",100)

Append a new record

3

=A1.insert(5,"Class one",21,"PE",100)

Insert a new record

 

INSERT

 

A

 

1

=demo.query("select * from SCORES")

 

2

=A1.insert(0,"Class one",20,"PE",100)

Append a new record

3

=A1.insert(5,"Class one",21,"PE",100)

Insert a new record

 

INSERT FROM SELECT …

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.derive()

Duplicate the table sequence

 

INSERT FROM SELECT …

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.derive()

Duplicate the table sequence

 

DELETE … WHERE …

 

A

 

1

=demo.query("select ORDERID,CLIENT,SELLERID, ORDERDATE,AMOUNT from SALES")

 

3

=A1.delete(A1.select(AMOUNT<10000))

Delete the matching records

 

DELETE … WHERE …

 

A

 

1

=demo.query("select ORDERID,CLIENT,SELLERID, ORDERDATE,AMOUNT from SALES")

 

3

=A1.delete(A1.select(AMOUNT<10000))

Delete the matching records

 

UPDATE … WHERE …

 

A

 

1

=demo.query("select ORDERID,CLIENT,SELLERID, ORDERDATE, AMOUNT from SALES")

 

2

=A1.select(CLIENT:"HL").run(AMOUNT= int(AMOUNT*1.1))

Update data on certain conditions

 

UPDATE … WHERE …

 

A

 

1

=demo.query("select ORDERID,CLIENT,SELLERID, ORDERDATE, AMOUNT from SALES")

 

2

=A1.select(CLIENT:"HL").run(AMOUNT= int(AMOUNT*1.1))

Update data on certain conditions

 

CREATE/DROP TABLE

 

A

 

1

=create(ProductNo,ProductName,UnitPrice,Quantity)

Create a table sequence

2

>A1=null

Delete the table sequence

 

CREATE/DROP TABLE

 

A

 

1

=create(ProductNo,ProductName,UnitPrice,Quantity)

Create a table sequence

2

>A1=null

Delete the table sequence

 

ALTER TABLE

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.rename(EID:ID)

Modify field names

 

ALTER TABLE

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.rename(EID:ID)

Modify field names

 

KEYS

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.keys(EID)

Set a primary key

 

KEYS

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.keys(EID)

Set a primary key

 

CONNECT/DISCONNECT/COMMIT/ROLLBACK

 

A

B

 

1

=connect@e("demo")

 

Establish a connection

2

>A1.execute@k(...)

 

 

3

=A1.error()

 

Error message arising from the execution of the previous database operation

4

if A3==0

>A1.commit()

Commit if no errors

5

else

>A1.rollback()

Rollback if any errors occur

6

>A1.close()

 

Close the connection

 

CONNECT/DISCONNECT/COMMIT/ROLLBACK

 

A

B

 

1

=connect@e("demo")

 

Establish a connection

2

>A1.execute@k(...)

 

 

3

=A1.error()

 

Error message arising from the execution of the previous database operation

4

if A3==0

>A1.commit()

Commit if no errors

5

else

>A1.rollback()

Rollback if any errors occur

6

>A1.close()

 

Close the connection