- Introduction
- What Is the ZODB?
- Relational Database Management Systems (RDBMS)
- Why Are Database Adapters Necessary?
- Which Database Adapter Do I Need?
- Connecting MySQL and Zope
- Gadfly—Zope's Integrated Demo Relational Database
- Introduction to SQL
- Using Z SQL Methods to Submit SQL Statements
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Introduction to SQL
The following pages will give you an overview of some of the most important SQL statements that will get you started with SQL. For the given examples, we use the Z Gadfly Database. However, the Z Gadfly Database only supports some of the actual SQL statements. For more detailed information on SQL, please refer to an SQL book. There are also some good tutorials on the Internet at
http://www.w3scripts.com/sql/
or
http://w3.one.net/~jhoffman/sqltut.htmor
http://sqlcourse.com/
After introducing the statements, you will learn how to use Z SQL methods to interact with your database via a Web page.
Example: Creating an Addressbook Database
For this example, we want to create a database using the Z Gadfly Database that is to function as an address book. Consequently, we need certain data to be stored in this database:
Last Name
First Name
Address
City
ZIP Code
Phone Number
First, we need to create a new folder on the file system in the /var/gadfly/ directory so that we can store the addresses in a separate database. We will call this folder MyAddressbook. The /var/gadfly/ directory now contains two foldersβdemo and MyAddressbook. For this example, we will use the database connection that we created earlier in the chapter.
Go to the Properties View of the MyGadflyDatabaseConnection and change the data source from demo to MyAddressbook. The connection is now established and we can start creating tables in the database and fill those tables with data.
The CREATE TABLE Statement
Next, we create a table via the Test View. Enter the following SQL statement into the text area in the view:
1: CREATE TABLE Addressbook ( 2: Name varchar, 3: FirstName varchar, 4: Address varchar, 5: City varchar, 6: ZIP integer, 7: Phone varchar)
After you have clicked the Submit Query button, you can see the new table in the Browse View. You need to click the plus sign next to ADDRESSBOOK to see all names of the table columns, as shown in Figure 3.13.
){kind=link}
The INSERT INTO Statement
The first step in creating an address book is done. Now we need to fill the table with data. This is done with the INSERT INTO statement. The statement's syntax is as follows:
1: INSERT INTO table_name (first_row_name,...,last_row_name) 2: VALUES (first_value, second_value, ... , last_value)
In this example, if we want to enter the address
John Smith
249 9th Ave
New York, NY 10001
(212)987-1234
into the Addressbook table, we need to send the following SQL statement to the MyAddressbook database:
1: INSERT INTO Addressbook
2: (Name, FirstName, Address, City, ZIP, Phone)
3: VALUES
4: ('Smith', 'John', '249 9th Ave', 'New York, NY', 10001, '(212)987-1234')
This SQL statement will return the result shown in Figure 3.14.
){kind=link}
Figure 3.13 Browse View of the created table.
Figure 3.14 A data record was successfully inserted.
Although this page looks like an error, it is actually the standard result page if an SQL statement does not return data.
The SELECT...FROM... Statement
To see the new data entered in the table, we need the SELECT...FROM... statement. This statement can be used to get a certain kind of data from a table or the entire table. To see the entire table, you use a wildcard *, as shown in the following:
SELECT * FROM Addressbook
The result of this statement is
|
ADDRESS |
NAME |
FIRST NAME |
PHONE |
CITY |
ZIP |
|
249 9th Ave |
Smith |
John |
(212)987-1234 |
New York, NY |
10001 |
To select certain rows from the table, you use the names of the table rows instead of the wildcard, as shown in the following:
SELECT Name, FirstName FROM Addressbook
This statement will give you the following result:
|
NAME |
FIRSTNAME |
|
Smith |
John |
If you want to get certain records from a table, perhaps the name of everyone who lives in New York, NY or everyone whose last name is Smith, you need to specify exactly what you want. This is done by adding a WHERE statement to the SELECT...FROM... statement:
SELECT * FROM Addressbook WHERE (Name='Smith')
If you have a list of entries in your Addressbook table and combine the row and column specifications, as shown in the following:
SELECT Name, FirstName FROM Addressbook WHERE (Name='Smith')
you might get something like
|
NAME |
FIRSTNAME |
|
Smith |
Jane |
|
Smith |
John |
|
Smith |
Ellen |
The UPDATE Statement
It is often necessary to change records in an address book because some of the information has changed, perhaps the phone number and/or the address of a person. This is done with the UPDATE statement. The UPDATE statement has the following form:
UPDATE table_name SET col_name = new_value
Using this statement on a table would change the values of the entire col_name column. Therefore, you need to specify which row you want to change. Again, this is done with the WHERE specification:
UPDATE table_name SET col_name = new_value WHERE col_name = certain_value
The following is an example of how to change the last name of Jane Smith to Williams:
1: UPDATE Addressbook SET Name='Williams' 2: WHERE Name='Smith' AND FirstName='John'
The DELETE FROM Statement
To delete records from a table, you use the DELETE FROM statement. Again you need the WHERE statement to specify what you want to delete. For example, if you want to delete the John Smith record from the Addressbook table, the necessary command will be
DELETE FROM Addressbook WHERE Name='Smith' AND FirstName='John'