Transcript Database Access: SQL Queries SQL INSERT statement SQL UPDATE statement

ITE 370: Database Access: SQL Queries
Database Access: SQL Queries
• SQL INSERT statement
– Adds one or more rows to a table
• SQL UPDATE statement
– Modifies one or more columns of one or more rows of a
table
• SQL DELETE statement
– Removes one or more rows from a table
University of South Alabama School of CIS
Last Modified: 5/26/2016
1
ITE 370: Database Access: SQL Queries
SQL INSERT Statement
• Syntax:
INSERT INTO <table name>
[(<column name>[{,<column name>}…])]
VALUES (<value>[{,<value>}…])
– Second line is optional
• If you omit, then values list must be complete and in
order of field creation
• If you include column list, values list must match
column list in number and order (but not in field
creation order
University of South Alabama School of CIS
Last Modified: 5/26/2016
2
ITE 370: Database Access: SQL Queries
INSERT Example
INSERT INTO Team (League, Location,
Nickname, Stadium)
VALUES (‘AL’, ‘Mobile’, ‘Bay Bears’,
‘Hank Aaron Stadium’)
University of South Alabama School of CIS
Last Modified: 5/26/2016
3
ITE 370: Database Access: SQL Queries
SQL UPDATE statement
• Syntax:
UPDATE <table name>
SET <set clause expression> [{, <set clause expression>}…]
[WHERE <search condition>]
– <set clause expression> syntax:
<column name> = <value expression>
– Omitting WHERE updates all rows
University of South Alabama School of CIS
Last Modified: 5/26/2016
4
ITE 370: Database Access: SQL Queries
UPDATE Examples
UPDATE Team
SET Stadium=‘Citizens Bank Park’
WHERE Stadium=‘Veterans Stadium’
UPDATE Player
SET HR=HR + 1
WHERE PlayerId=22
University of South Alabama School of CIS
Last Modified: 5/26/2016
5
ITE 370: Database Access: SQL Queries
SQL Delete Statement
• Syntax:
DELETE FROM <table name>
[WHERE <search condition>]
– Omission of WHERE removes all rows
University of South Alabama School of CIS
Last Modified: 5/26/2016
6
ITE 370: Database Access: SQL Queries
DELETE Examples
DELETE FROM Team
WHERE Nickname=‘Bay Bears’
DELETE FROM Team
WHERE League NOT IN ('AL', 'NL')
University of South Alabama School of CIS
Last Modified: 5/26/2016
7