23.4.5 INSERT StatementInsert a row into a table
INSERT INTO tableName ( columnName1, … , columnNameN )
VALUES ( value1, … , valueN )
INSERT INTO authors ( firstName, lastName )
VALUES ( ‘Sue’, ‘Smith’ )
Lecture 9 Chapter 23: Java Database Connectivity with JDBC
Outline 23.1 Introduction 23.2 Relational-Database Model 23.3 Relational Database Overview: The books Database 23.4 SQL 23.4.1 Basic SELECT Query 23.4.2 WHERE Clause 23.4.3 ORDER BY Clause 23.4.4 Merging Data from Multiple Tables: INNER JOIN 23.4.5 INSERT Statement 23.4.6 UPDATE Statement 23.4.7 DELETE Statement 23.5 Creating Database books in Cloudscape 23.6 Manipulating Databases with JDBC 23.6.1 Connecting to and Querying a Database 23.6.2 Querying the books Database 23.7 Stored Procedures 23.8 Internet and World Wide Web Resources
23.1 Introduction
Database
Collection of data
DBMS
Database management system
Stores and organizes data
SQL
Relational database
Structured Query Language
Fig. 23.1 Employee table sample data. Number Name Department Salary Location 23603 Jones 413 1100 New Jersey 24568 Kerwin 413 2000 New Jersey 34589 Larson 642 1800 Los Angeles 35761 Myers 611 1400 Orlando 47132 Neumann 413 9000 New Jersey 78321 Stephens 611 8500 Orlando Row Column Primary key
23.2 Relational-Database Model (Cont.)
Fig. 23.2 Result of selecting distinct Department and Location data from the Employee table. Department Location 413 New Jersey 611 Orlando 642 Los Angeles
23.3 Relational Database Overview: The books Database
Sample books database
Four tables
authors, publishers, authorISBN and titles
Relationships among the tables
23.3 Relational Database Overview: The books Database (Cont.)
23.3 Relational Database Overview: The books Database (Cont.)
23.3 Relational Database Overview: The books Database (Cont.)
23.3 Relational Database Overview: The books Database (Cont.)
23.3 Relational Database Overview: The books Database (Cont.)
23.3 Relational Database Overview: The books Database (Cont.)
Fig. 23.11 Table relationships in books. authorISBN authorID isbn authors authorID firstName lastName publishers publisherID publisherName titles isbn title editionNumber copyright publisherID imageFile price 1 8 1 8 1 8
23.4 SQL
SQL overview
SQL keywords
23.4.1 Basic SELECT Query
Simplest form of a SELECT query
SELECT * FROM tableName
SELECT * FROM authors
Select specific fields from a table
SELECT authorID, lastName FROM authors
23.4.2 WHERE Clause
specify the selection criteria
SELECT columnName1, columnName2, … FROM tableName WHERE criteria
SELECT title, editionNumber, copyright
FROM titles
WHERE copyright > 2000
WHERE clause condition operators
<, >, <=, >=, =, <>
LIKE
wildcard characters % and _
23.4.2 WHERE Clause (Cont.)
23.4.2 WHERE Clause (Cont.)
SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE ‘D%’
23.4.2 WHERE Clause (Cont.)
SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE ‘_i%’
23.4.3 ORDER BY Clause
Optional ORDER BY clause
SELECT columnName1, columnName2, … FROM tableName ORDER BY column ASC
SELECT columnName1, columnName2, … FROM tableName ORDER BY column DESC
ORDER BY multiple fields
ORDER BY column1 sortingOrder, column2 sortingOrder, …
Combine the WHERE and ORDER BY clauses
Comments