The CREATE statement is used to create a new table, and the INSERT statement is used to add a new record to a table.
The CREATE statement has this general format:
|
Format of SQL CREATE statement |
Additional information |
|---|---|
|
|
Here is an example of the use of the CREATE statement:
|
Example of SQL CREATE statement |
Additional information |
|---|---|
|
|
This statement will create the Person table that we saw earlier in the chapter. It will have three columns called “name,” “age,” and “lives in.” The “optionalConstraint” shown in the first example means that you can add or omit a constraint to a column, giving more information about what kind of values are legal there. We have added a constraint to the “name” column, saying that this is the primary key of the table. That has the effect of making sure that records always have a non-null unique value there when the records are inserted into the table. “Not null” and “unique” are also constraints that can be applied individually.
Try typing the SQL statement into the visual query tool and confirm you get these results. There is already a table called Person in this database, so you will get an error message to that effect. Enter the query again, changing the table name to Person2. You need to completely overwrite the old query with the new one, when using the Mckoi GUI tool.
Some datatypes that SQL understands and the corresponding Java types are shown in Table 23-6. The SQL keywords and datatypes can use any letter case. The convention is to write them all in uppercase.
Table 23-6. Some SQL datatypes
|
SQL datatype |
Corresponding Java type |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The BLOB datatype means “Binary Large Object.” There are also CLOBs, “Character Large Objects.” SQL arrays are sequences of data. Think back to earlier in the chapter where we said that first normal form means that there are no lists of items in an attribute. There's a question of where you draw the line though. If you are recording student marks over 12 weeks of homework assignments, it's overkill to store each mark in a new record. Instead, we'll have a student/course marks record, and the course marks will be held as a fixed length array that gets updated with a new mark each week. BLOBs, CLOBs, and arrays are represented by classes in the java.sql package, and they have methods to get their values.
The CREATE statement just creates an empty table. As yet it has no records in it. We will put records in using the INSERT statement, which has this general appearance:
|
Format of SQL INSERT statement |
Additional information |
|---|---|
|
|
Here is an example of the use of the INSERT statement:
|
Examples of SQL INSERT statement |
Additional information |
|---|---|
|
|
This statement will start to populate (fill in with data) the Person table that we saw earlier in the chapter. The values are inserted into the record in the order in which the attributes are named. The number of values given in each of the value lists should match the number of attributes in the list before the “values” keyword. SQL is very picky about the requirement that character strings be enclosed in single quotes.
Note that some database vendors have not implemented support for inserting multiple rows with one statement. So to retain maximum portability you would want to restrict yourself to adding one record per insert statement.
Try typing the INSERT statement into the visual query tool and confirm you get these results. Put the data into the table named Person2. You should try each subsequent SQL statement in the visual tool from here on.