Transcript Action Queries

SQL ACTION QUERIES AND
TRANSACTION CONTROL
CS 260
Database Systems
Overview




Inserting data
Updating/deleting data
Surrogate keys
Transaction control
Inserting Data

SQL INSERT statements
 Used
to insert new data into a database table
 Used when migrating existing data from another table
 Typically, users will use applications with an interface
that allows data to be easily added/migrated, but
programmers often need to write the INSERT statements
ultimately executed by those applications
 Two approaches
 Insert
a value for every field
 Insert values for specific fields
Inserting Data

Inserting a value for every field
 Syntax
INSERT INTO <table_name>
VALUES (<value1>, <value2>, …);
 Example
INSERT INTO candy_product
VALUES (1, ‘Celestial Cashew Crunch’, 7.45, 10);
Inserting Data

Inserting a value for every field
 Rules
 You
must include a value for every field
 You must list the values in the correct order

You can use DESCRIBE to find the correct order
DESCRIBE <table_name>;
 With
this syntax, the DBMS is expecting data for every
field in a specific order
 The data types must be consistent with the specified order

Otherwise an error occurs
Inserting Data

Inserting a value for specific fields
 Syntax
INSERT INTO <table_name> (<field1>, <field2>,…)
VALUES (<value1>, <value2>, …);
 Example
INSERT INTO candy_product (prod_desc, prod_id)
VALUES (‘Celestial Cashew Crunch’, 1);
Inserting Data

Inserting a value for specific fields
 Rules
 The
field names can be specified in any order
 The corresponding values must be specified in the same
order as the specified field names

If you are unsure of the values for all fields, use this
syntax
 Allows
default column values to be used (if specified for
the corresponding field in the table)
Inserting Data

Specifying values
 Characters
 Enclose
values in single quotes (just like in SELECT query
search conditions)
 If a single quote should be present in the value, use two
single quotes
 Numbers
 Just
specify the value (just like in SELECT query search
conditions)
Inserting Data

Specifying foreign key values
 You
must insert the parent record first
 Otherwise, insert them just like other value
Child
record
Primary key
CUST_ID
Foreign key
PURCH_ID
PROD_ID
CUST_ID
CUST_TYPE_ID
1 Jones, Joe
P
1234 Main St.
91212 434-1231
2 Armstrong,Inc.
R
231 Globe Blvd.
4
3 Sw edish Burgers
R
5
1889 20th N.E.
R
CUST_NAME
5 28-Oct-04
CUST_PHONE
28-Oct-04
3.5
2
6
28-Oct-04
30-Oct-04
15
1
9
28-Oct-04
28-Oct-04
2
91212
434-76649
3
28-Oct-04
28-Oct-04
3.7
3
91213
434-90902
28-Oct-04
2-Nov-04
3.7
194 CityView6
1
91289
324-89097
29-Oct-04
29-Oct-04
3.7
2121 Main St.8
91212
563-45457
3
29-Oct-04
29-Oct-04
1.2
29-Oct-04
29-Oct-04
4.4
P
23 Yankee Blvd.
9
91234
2
6
29-Oct-04
30-Oct-04
3
7 Bobby Bon Bons
R
10
12 Nichi Cres.
2
91212
434-904510
29-Oct-04
31-Oct-04
14
8 Crow sh, Elias
P
7 77th Ave.
91211 434-0007
4.8
9 Montag, Susie
P
981 Montview
13
10 Columberg Sw eets W
14
239 East Falls
5
91209
874-90923
4 Pickled Pickles
5 The Candy Kid
6 Waterman, Al
Parent
W
record
2
1
CUST_ZIP
PURCH_DATEDELIVERY_DATE
POUNDS
CUST_ADDR1
3
7
11
12
2
7
5
10
29-Oct-04
2-Nov-04
1
4
29-Oct-04
29-Oct-04
1
91213
456-20914
5
29-Oct-04
30-Oct-04
7.6
29-Oct-04
29-Oct-04
3.5
Inserting Data

How many parent records must be inserted and/or
present in other tables before a record can be
inserted into the candy_purchase table?
a.
b.
c.
d.
e.
0
1
2
3
None of the above
Inserting Data

Inserting dates
 Oracle
and MySQL
 Specify
dates as strings in the expected format
INSERT INTO <table_name> (<date_field>)
VALUES (‘25-DEC-2014’);
Oracle
INSERT INTO <table_name> (<date_field>)
VALUES (‘2014-12-25’);
MySQL
 Oracle
 Use
the TO_DATE function
INSERT INTO <table_name> (<date_field>)
VALUES (TO_DATE(‘2014-12-25 19:00:00’, ‘YYYY-MM-DD HH24:MI:SS’));
Format specifies the format of the date in the first argument
Inserting Data

Inserting dates
 MySQL
 Remember
that MySQL DATE types lack a time component
 Use the DATETIME type for dates with a time component

Again, specify the date as a string in the expected format
INSERT INTO <table_name> (<date_field>)
VALUES (‘2014-12-25 19:00:00’);
Overview




Inserting data
Updating/deleting data
Surrogate keys
Transaction control
Updating Data

Syntax
UPDATE <table_name>
SET <field1> = <new_value>,
<field2> = <new_value>,
…
<fieldN> = <new_value>
WHERE <search_condition>;

Example
UPDATE candy_product
SET prod_price = 10.5,
prod_cost = 7.50
WHERE prod_id = 1;
Updating Data



Records can only be updated in one table at a time
Multiple records in the same table will be updated
simultaneously if they match the search condition
When using Oracle SQL Developer or MySQL
Workbench, if any part of the update fails, the
entire update fails
 Not
necessarily the case in other tools (such as JDBC in
Java applications)
Updating Data

Syntax
DELETE FROM <table_name>
WHERE <search_condition>;

Example
DELETE FROM candy_purchase
WHERE purch_id = 9;
Deleting Data


Records can only be deleted in one table at a time
Multiple records in the same table will be deleted
simultaneously if they match the search condition
 If
the search condition is omitted, all records in the
table will be deleted


A record cannot be deleted if one of its fields is
referenced as a foreign key in another table
For tables with many records, it is quicker to drop
the table and recreate it than it is to delete all of its
records
Deleting Data

How many total records must be deleted in order to
delete the “Nuts Not Nachos” record from the
candy_product table?
a.
b.
c.
d.
1
2
3
4
Deleting Data

CASCADE DELETE
Causes all child records to be automatically deleted when a
record in a parent table is deleted
 Option available in both Oracle and MySQL
 Applied to the foreign key constraint


Don’t use unless you’re absolutely sure that this behavior is desired
CREATE TABLE <table_name> (
<fk_field> <data_type>,
CONSTRAINT <constraint_name> FOREIGN KEY (fk_field) REFERENCES
<parent_table> (<parent_table_pk_field>) ON DELETE CASCADE
);

Example is Oracle syntax, MySQL syntax is similar, but without
“CONSTRAINT <constraint_name>”
Overview




Inserting data
Updating/deleting data
Surrogate keys
Transaction control
Surrogate Keys

A surrogate key is a field created solely for the
purpose of being a unique identifier in a database
CUST_ID
CUST_NAME
CUST_TYPE_ID
CUST_ADDR
CUST_ZIP
1 Jones, Joe
P
1234 Main St.
91212
2 Armstrong,Inc.
R
231 Globe Blvd.
91212
3 Sw edish Burgers
R
1889 20th N.E.
91213
4 Pickled Pickles
R
194 CityView
91289
5 The Candy Kid
W
2121 Main St.
91212
6 Waterman, Al
P
23 Yankee Blvd.
91234
7 Bobby Bon Bons
R
12 Nichi Cres.
91212
8 Crow sh, Elias
P
7 77th Ave.
91211
9 Montag, Susie
P
981 Montview
91213
10 Columberg Sw eets W
239 East Falls
91209
Surrogate Keys

Their creation depends on the DBMS
 Oracle
 Declare
a NUMBER field as a primary key
 Create a “sequence” to automatically generate sequential
numbers


 Use
This sequence is independent of the table
Default sequence minimum value is 1
the sequence when inserting new records
 MySQL
 Declare
an integer primary key field with the
AUTO_INCREMENT modifier
Surrogate Keys

Oracle Example
CREATE TABLE candy_product(
prod_id NUMBER(6) CONSTRAINT candy_product_id_pk PRIMARY KEY,
prod_desc VARCHAR2(30),
prod_cost NUMBER(4,2),
prod_price NUMBER(4,2)
);
CREATE SEQUENCE candy_product_seq MINVALUE 0;
 Other
modifiable properties
 INCREMENT
BY: allows sequence values to increase by more
than 1
 MAXVALUE: specify a maximum sequence value
 CACHE: determine how many sequence values are allocated
when the sequence is used (allows for faster access)
Surrogate Keys

MySQL Example
CREATE TABLE candy_product(
prod_id INT(6) PRIMARY KEY AUTO_INCREMENT,
prod_desc VARCHAR(30),
prod_cost DECIMAL(4,2),
prod_price DECIMAL(4,2)
);
Surrogate Keys

Using surrogate keys in Oracle
 Use
the sequence’s NEXTVAL to obtain the next value
INSERT INTO candy_customer (cust_id)
VALUES (candy_customer_seq.NEXTVAL);
INSERT INTO candy_product (prod_id)
VALUES (candy_product_seq.NEXTVAL);
 Use
the sequence’s CURRVAL to obtain the current value
INSERT INTO candy_purchase (purch_id, prod_id, cust_id)
VALUES(purch_id_sequence.NEXTVAL,
candy_customer_seq.CURRVAL, candy_product_seq.CURRVAL);
 CURRVAL
is useful when inserting values for foreign keys that
reference primary keys inserted immediately prior

It will access the most recently generated surrogate key by that
sequence in the current session
Surrogate Keys

Using surrogate keys in MySQL

Using the “inserting values for specific fields” approach
INSERT INTO candy_customer(cust_name, cust_type, cust_addr)
VALUES (‘Bobby Bon Bons’, ‘R’, ‘12 NichiCres.’);

Using the “inserting values for all fields” approach
INSERT INTO candy_product
VALUES (null, ‘Celestial Cashew Crunch’, 7.45, 10);

The AUTO_INCREMENT modifier will automatically generate
and apply the surrogate key value for the corresponding
record

Use null or 0 if specifying a value for the AUTO_INCREMENT key
Surrogate Keys

Using surrogate keys in MySQL

Use the LAST_INSERT_ID() function to retrieve the most recently
AUTO_INCREMENT generated surrogate key value for any table
for the current session
INSERT INTO candy_purchase (prod_id, cust_id)
VALUES(LAST_INSERT_ID(), (SELECT MAX(cust_id)
FROM candy_customer));
 Assumptions



purch_id is an AUTO_INCREMENT field and we want to
recognize a new purchase
A new customer and product were just inserted (previous slide) in
that order using AUTO_INCREMENT (with ascending increments)
We are confident that no customers have been inserted since
Bobby Bon Bons (risky)
 Better to programmatically store the most recently added
cust_id
Surrogate Keys

Oracle sequence concerns
 Sequences
are not explicitly connected to a table
 Sequences
 If
can be referenced by multiple tables
a sequence’s NEXTVAL is called and the value
obtained is not inserted, then that value is lost
 If multiple users access a sequence that has a CACHE
value (must be at least 2), then those sequence values
are reserved and may be lost if not used
 If a surrogate key value is hardcoded in an insert
statement instead of using the sequence, the sequence
may later produce the same value
Surrogate Keys

MySQL AUTO_INCREMENT concerns
 Limited
to one column per table
 Assigned to a specific table (cannot be used across
tables as in Oracle)
 If a surrogate key value is hardcoded in an insert
statement instead of using the AUTO_INCREMENT,
AUTO_INCREMENT will recognize it
 If transactions are rolled back, gaps in
AUTO_INCREMENT values may be present
Overview




Inserting data
Updating/deleting data
Surrogate keys
Transaction control
Transaction Control

A transaction is a logical unit of work that might
involve multiple action queries
 Examples
 Withdrawing
money from one bank account and depositing
it into a different account
 Booking a seat on an airplane
 All
of the action queries in a transaction must succeed
or none of them should succeed
 Applies
to INSERT, UPDATE, and DELETE statements, but not
to SELECT statements (since they don’t modify the database)
Transaction Control

A transaction can be explicitly started using the START
TRANSACTION command
START TRANSACTION;


All subsequent action queries will belong to the same
transaction until the transaction is committed
A transaction can be explicitly committed using the
COMMIT command
COMMIT;

Use the ROLLBACK command to “undo” all action
queries in the current transaction (since the last commit)
ROLLBACK;
Transaction Control

Oracle SQL Developer
 By
default, implicit transaction control is used
 The
transaction starts when you connect to the database,
and commits when you tell it to (either via the commit button
or the COMMIT command)
 Database changes wouldn’t be visible to other users until
they’re committed
 After a commit, the next command automatically starts a
new transaction
 You should be prompted to commit any uncommitted
transactions when quitting the application
Transaction Control

MySQL Workbench
 No
implicit transaction control
 Example with auto commit off
Connection 1
-- 1 -INSERT INTO candy_product
VALUES(null, 'test1', 1, 2);
-- 2 -SELECT * FROM candy_product;
Connection 2
-- 3 –SELECT * FROM candy_product;
-- prod_id 7 not shown
-- not committed by Connection 1
Transaction Control

MySQL Workbench with auto commit off
Connection 1
-- 1 -INSERT INTO candy_product
VALUES(null, 'test1', 1, 2);
-- 2 –SELECT * FROM candy_product;
-- prod_id 7 is shown
-- 4 -COMMIT;
This SELECT operation started a
transaction and later SELECTs won't
show changes until COMMIT from
connection 1 AND connection2
Connection 2
-- 3 -SELECT * FROM candy_product;
-- prod_id 7 not shown
-- 5 –SELECT * FROM candy_product;
-- prod_id 7 still not shown
-- 6 -COMMIT;
-- 7 -SELECT * FROM candy_product;
-- prod_id 7 now shown
Transaction Control

Oracle SQL Developer in same scenario
Connection 1
-- 1 -INSERT INTO candy_product
VALUES(null, 'test1', 1, 2);
-- 2 –SELECT * FROM candy_product;
-- prod_id 7 is shown
-- 4 -COMMIT;
Not necessary to commit from
connection 2 to see committed
changes from connection 1
Connection 2
-- 3 -SELECT * FROM candy_product;
-- prod_id 7 not shown
-- 5 –SELECT * FROM candy_product;
-- prod_id 7 is shown
Transaction Control

Implications due to transactions
 Before
a transaction is committed, its actions are visible
on your connection but not to others
 When a transaction is committed, it cannot be rolled
back, and its changes may be visible to other users
 By default, most applications that interact with a
database will auto commit transactions