Create and Drop Database, Create, Alter and Drop Tables, Select, Insert, Update, Delete Commands

此文仅做自我学习记录用！！！

Introduction (Descriptive)

Content:- Creating and Maintaining Tables, Objectives, The CREATE DATABASE Statement, CREATE DATABASE Options, Database Design, Creating a Data Dictionary, Creating Key Fields, The CREATE TABLE Statement, The Table Name, The Field Name, The Field's Data Type, The NULL Value, Table Storage and Sizing, Creating a Table from an Existing Table, The ALTER TABLE Statement, The DROP TABLE Statement, The DROP DATABASE Statement.

Details

It covers the CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, and DROP DATABASE statements, which are collectively known as data definition statements. (In contrast, the SELECT, UPDATE, INSERT, and DELETE statements are often described as data manipulation statements.) By the end, you will understand and be able to do the following:

• Create key fields
• Create a database with its associated tables
• Create, alter, and drop a table
• Add data to the database
• Modify the data in a database
• Drop databases

The syntax of the CREATE statements can range from the extremely simple to the complex, depending on the options your database management system (DBMS) supports and how detailed you want to be when building a database.

NOTE: The examples used today were generated using Personal Oracle7. Please see the documentation for your specific SQL implementation for any minor differences in syntax.

The CREATE DATABASE Statement

The first data management step in any database project is to create the database. This task can range from the elementary to the complicated, depending on your needs and the database management system you have chosen. Many modern systems (including Personal Oracle7) include graphical tools that enable you to completely build the database with the click of a mouse button. This time-saving feature is certainly helpful, but you should understand the SQL statements that execute in response to the mouse clicks.

Through personal experience, we have learned the importance of creating a good SQL install script. This script file contains the necessary SQL code to completely rebuild a database or databases; the script often includes database objects such as indexes, stored procedures, and triggers. You will see the value of this script during development as you continually make changes to the underlying database and on occasion want to completely rebuild the database with all the latest changes. Using the graphical tools each time you need to perform a rebuild can become extremely time-consuming. In addition, knowing the SQL syntax for this procedure enables you to apply your knowledge to other database systems.

The syntax for the typical CREATE DATABASE statement looks like this:

SYNTAX:

CREATE DATABASE database_name

Because the syntax varies so widely from system to system, we will not expand on the CREATE DATABASE statement's syntax. Many systems do not even support an SQL CREATE DATABASE command. However, all the popular, more powerful, relational database management systems (RDBMSs) do provide it. Instead of focusing on its syntax, we will spend some time discussing the options to consider when creating a database.

CREATE DATABASE Options

The syntax for the CREATE DATABASE statement can vary widely. Many SQL texts skip over the CREATE DATABASE statement and move directly on to the CREATE TABLE statement. Because you must create a database before you can build a table, this section focuses on some of the concepts a developer must consider when building a database. The first consideration is your level of permission. If you are using a relational database management system (RDBMS) that supports user permissions, you must make sure that either you have system administrator-level permission settings or the system administrator has granted you CREATE DATABASE permission. Refer to your RDBMS documentation for more information.

Most RDBMSs also allow you to specify a default database size, usually in terms of hard disk space (such as megabytes). You will need to understand how your database system stores and locates data on the disk to accurately estimate the size you need. The responsibility for managing this space falls primarily to system administrators, and possibly at your location a database administrator will build you a test database.

Don't let the CREATE DATABASE statement intimidate you. At its simplest, you can create a database named PAYMENTS with the following statement:

SYNTAX:

SQL> CREATE DATABASE PAYMENTS;

NOTE: Again, be sure to consult your database management system's documentation to learn the specifics of building a database, as the CREATE DATABASE statement can and does vary for the different implementations. Each implementation also has some unique options.

Database Design

Normalization is the process of breaking your data into separate components to reduce the repetition of data. Each level of normalization reduces the repetition of data. Normalizing your data can be an extremely complex process, and numerous database design tools enable you to plan this process in a logical fashion. Many factors can influence the design of your database, including the following:

Security

• Disk space available
• Speed of database searches and retrievals
• Speed of database updates
• Speed of multiple-table joins to retrieve data
• RDBMS support for temporary tables

Disk space is always an important factor. Although you may not think that disk space is a major concern in an age of multigigabyte storage, remember that the bigger your database is, the longer it takes to retrieve records. If you have done a poor job of designing your table structure, chances are that you have needlessly repeated much of your data.

Often the opposite problem can occur. You may have sought to completely normalize your tables' design with the database and in doing so created many tables. Although you may have approached database-design nirvana, any query operations done against this database may take a very long time to execute. Databases designed in this manner are sometimes difficult to maintain because the table structure might obscure the designer's intent. This problem underlines the importance of always documenting your code or design so that others can come in after you (or work with you) and have some idea of what you were thinking at the time you created your database structure. In database designer's terms, this documentation is known as a data dictionary.

Creating a Data Dictionary

A data dictionary is the database designer's most important form of documentation. It performs the following functions:

• Describes the purpose of the database and who will be using it.
• Documents the specifics behind the database itself: what device it was created on, the database's default size, or the size of the log file (used to store database operations information in some RDBMSs).
• Contains SQL source code for any database install or uninstall scripts, including documentation on the use of import/export tools.
• Provides a detailed description of each table within the database and explains its purpose in business process terminology.
• Documents the internal structure of each table, including all fields and their data types with comments, all indexes, and all views.
• Contains SQL source code for all stored procedures and triggers.
• Describes database constraints such as the use of unique values or NOT NULL values. The documentation should also mention whether these constraints are enforced at the RDBMS level or whether the database programmer is expected to check for these constraints within the source code.

Many computer-aided software engineering (CASE) tools aid the database designer in the creation of this data dictionary. For instance, Microsoft Access comes prepackaged with a database documenting tool that prints out a detailed description of every object in the database.

NOTE: Most of the major RDBMS packages come with either the data dictionary installed or scripts to install it.

Creating Key Fields

Along with documenting your database design, the most important design goal you should have is to create your table structure so that each table has a primary key and a foreign key. The primary key should meet the following goals:

• Each record is unique within a table (no other record within the table has all of its columns equal to any other).
• For a record to be unique, all the columns are necessary; that is, data in one column should not be repeated anywhere else in the table.

Regarding the second goal, the column that has completely unique data throughout the table is known as the primary key field. A foreign key field is a field that links one table to another table's primary or foreign key. The following example should clarify this situation.

Assume you have three tables: BILLS, BANK_ACCOUNTS, and COMPANY. Table 9.1 shows the format of these three tables.

Table 9.1. Table structure for the PAYMENTS database.

Take a moment to examine these tables. Which fields do you think are the primary keys? Which are the foreign keys?

The primary key in the BILLS table is the NAME field. This field should not be duplicated because you have only one bill with this amount. (In reality, you would probably have a check number or a date to make this record truly unique, but assume for now that the NAME field works.) The ACCOUNT_ID field in the BANK_ACCOUNTS table is the primary key for that table. The NAME field is the primary key for the COMPANY table.

The foreign keys in this example are probably easy to spot. The ACCOUNT_ID field in the BILLS table joins the BILLS table with the BANK_ACCOUNTS table. The NAME field in the BILLS table joins the BILLS table with the COMPANY table. If this were a full-fledged database design, you would have many more tables and data breakdowns. For instance, the BANK field in the BANK_ACCOUNTS table could point to a BANK table containing bank information such as addresses and phone numbers. The COMPANY table could be linked with another table (or database for that matter) containing information about the company and its products.

Exercise 9.1

Let's take a moment to examine an incorrect database design using the same information contained in the BILLS, BANK_ACCOUNTS, and COMPANY tables. A mistake many beginning users make is not breaking down their data into as many logical groups as possible. For instance, one poorly designed BILLS table might look like this:

The results may look correct, but take a moment to really look at the data here. If over several months you wrote several bills to the company in the NAME field, each time a new record was added for a bill, the company's ADDRESS, CITY, and STATE information would be duplicated. Now multiply that duplication over several hundred or thousand records and then multiply that figure by 10, 20, or 30 tables. You can begin to see the importance of a properly normalized database.

Before you actually fill these tables with data, you will need to know how to create a table.

The CREATE TABLE Statement

The process of creating a table is far more standardized than the CREATE DATABASE statement. Here's the basic syntax for the CREATE TABLE statement:

SYNTAX:

CREATE TABLE table_name

( field1 datatype [ NOT NULL ],

field2 datatype [ NOT NULL ],

field3 datatype [ NOT NULL ]...)

A simple example of a CREATE TABLE statement follows

INPUT/OUTPUT:

SQL> CREATE TABLE BILLS (

2 NAME CHAR(30),

3 AMOUNT NUMBER,

4 ACCOUNT_ID NUMBER);

Table created.

ANALYSIS:

This statement creates a table named BILLS. Within the BILLS table are three fields: NAME, AMOUNT, and ACCOUNT_ID. The NAME field has a data type of character and can store strings up to 30 characters long. The AMOUNT and ACCOUNT_ID fields can contain number values only.

The following section examines components of the CREATE TABLE command.

The Table Name

When creating a table using Personal Oracle7, several constraints apply when naming the table. First, the table name can be no more than 30 characters long. Because Oracle is case insensitive, you can use either uppercase or lowercase for the individual characters. However, the first character of the name must be a letter between A and Z. The remaining characters can be letters or the symbols _, #, $, and @. Of course, the table name must be unique within its schema. The name also cannot be one of the Oracle or SQL reserved words (such as SELECT).

NOTE: You can have duplicate table names as long as the owner or schema is different. Table names in the same schema must be unique.

The Field Name

The same constraints that apply to the table name also apply to the field name. However, a field name can be duplicated within the database. The restriction is that the field name must be unique within its table. For instance, assume that you have two tables in your database: TABLE1and TABLE2. Both of these tables could have fields called ID. You cannot, however, have two fields within TABLE1 called ID, even if they are of different data types.

The Field's Data Type

If you have ever programmed in any language, you are familiar with the concept of data types, or the type of data that is to be stored in a specific field. For instance, a character data type constitutes a field that stores only character string data. Table 9.2 shows the data types supported by Personal Oracle7.

Table 9.2. Data types supported by Personal Oracle7.

NOTE: The LONG data type is often called a MEMO data type in other database management systems. It is primarily used to store large amounts of text for retrieval at some later time.

The LONG RAW data type is often called a binary large object (BLOB) in other database management systems. It is typically used to store graphics, sound, or video data. Although relational database management systems were not originally designed to serve this type of data, many multimedia systems today store their data in LONG RAW, or BLOB, fields.

The ROWID field type is used to give each record within your table a unique, no duplicating value. Many other database systems support this concept with a COUNTER field (Microsoft Access) or an IDENTITY field (SQL Server).

NOTE: Check your implementation for supported data types as they may vary.

The NULL Value

SQL also enables you to identify what can be stored within a column. A NULL value is almost an oxymoron, because having a field with a value of NULL means that the field actually has no value stored in it.

When building a table, most database systems enable you to denote a column with the NOT NULL keywords. NOT NULL means the column cannot contain any NULL values for any records in the table. Conversely, NOT NULL means that every record must have an actual value in this column. The following example illustrates the use of the NOT NULL keywords.

INPUT:

SQL> CREATE TABLE BILLS (

2 NAME CHAR(30) NOT NULL,

3 AMOUNT NUMBER,

4 ACCOUNT_ID NOT NULL);

ANALYSIS:

In this table you want to save the name of the company you owe the money to, along with the bill's amount. If the NAME field and/or the ACCOUNT_ID were not stored, the record would be meaningless. You would end up with a record with a bill, but you would have no idea whom you should pay.

The first statement in the next example inserts a valid record containing data for a bill to be sent to Joe's Computer Service for $25.

INPUT/OUTPUT:

SQL> INSERT INTO BILLS VALUES("Joe's Computer Service", 25, 1);

1 row inserted.

INPUT/OUTPUT:

SQL> INSERT INTO BILLS VALUES("", 25000, 1);

1 row inserted.

ANALYSIS:

Notice that the second record in the preceding example does not contain a NAME value. (You might think that a missing payee is to your advantage because the bill amount is $25,000, but we won't consider that.) If the table had been created with a NOT NULL value for the NAME field, the second insert would have raised an error.

A good rule of thumb is that the primary key field and all foreign key fields should never contain NULL values.

Unique Fields

One of your design goals should be to have one unique column within each table. This column or field is a primary key field. Some database management systems allow you to set a field as unique. Other database management systems, such as Oracle and SQL Server, allow you to create a unique index on a field. This feature keeps you from inserting duplicate key field values into the database.

You should notice several things when choosing a key field. As we mentioned, Oracle provides a ROWID field that is incremented for each row that is added, which makes this field by default always a unique key. ROWID fields make excellent key fields for several reasons. First, it is much faster to join on an integer value than on an 80- character string. Such joins result in smaller database sizes over time if you store an integer value in every primary and foreign key as opposed to a long CHAR value. Another advantage is that you can use ROWID fields to see how a table is organized. Also, using CHAR values leaves you open to a number of data entry problems. For instance, what would happen if one person entered 111 First Street, another entered 111 1st Street, and yet another entered 111 First St.? With today's graphical user environments, the correct string could be entered into a list box. When a user makes a selection from the list box, the code would convert this string to a unique ID and save this ID to the database.

Now you can create the tables you used earlier today. You will use these tables for the rest of today, so you will want to fill them with some data. Use the INSERT command covered yesterday to load the tables with the data in Tables 9.3, 9.4, and 9.5.

INPUT/OUTPUT:

SQL> create database PAYMENTS;

Statement processed.

SQL> create table BILLS (

2 NAME CHAR(30) NOT NULL,

3 AMOUNT NUMBER,

4 ACCOUNT_ID NUMBER NOT NULL);

Table created.

SQL> create table BANK_ACCOUNTS (

2 ACCOUNT_ID NUMBER NOT NULL,

3 TYPE CHAR(30),

4 BALANCE NUMBER,

5 BANK CHAR(30));

Table created.

SQL> create table COMPANY (

2 NAME CHAR(30) NOT NULL,

3 ADDRESS CHAR(50),

4 CITY CHAR(30),

5 STATE CHAR(2));

Table created.

Table 9.3. Sample data for the BILLS table.

Table 9.4. Sample data for the BANK_ACCOUNTS table.

Table 9.5. Sample data for the COMPANY table.

Table Storage and Sizing

Most major RDBMSs have default settings for table sizes and table locations. If you do not specify table size and location, then the table will take the defaults. The defaults may be very undesirable, especially for large tables. The default sizes and locations will vary among the implementations. Here is an example of a CREATE TABLE statement with a storage clause (from Oracle).

INPUT:

SQL> CREATE TABLE TABLENAME

2 (COLUMN1 CHAR NOT NULL,

3 COLUMN2 NUMBER,

4 COLUMN3 DATE)

5 TABLESPACE TABLESPACE NAME

6 STORAGE

7 INITIAL SIZE,

8 NEXT SIZE,

9 MINEXTENTS value,

10 MAXEXTENTS value,

11 PCTINCREASE value);

OUTPUT:

Table created.

ANALYSIS:

In Oracle you can specify a tablespace in which you want the table to reside. A decision is usually made according to the space available, often by the database administrator (DBA). INITIAL SIZE is the size for the initial extent of the table (the initial allocated space). NEXT SIZE is the value for any additional extents the table may take through growth. MINEXTENTS and MAXEXTENTS identify the minimum and maximum extents allowed for the table, and PCTINCREASE identifies the percentage the next extent will be increased each time the table grows, or takes another extent.

Creating a Table from an Existing Table

The most common way to create a table is with the CREATE TABLE command. However, some database management systems provide an alternative method of creating tables, using the format and data of an existing table. This method is useful when you want to select the data out of a table for temporary modification. It can also be useful when you have to create a table similar to the existing table and fill it with similar data. (You won't have to reenter all this information.) The syntax for Oracle follows.

SYNTAX:

CREATE TABLE NEW_TABLE(FIELD1, FIELD2, FIELD3)

AS (SELECT FIELD1, FIELD2, FIELD3

FROM OLD_TABLE <WHERE...>

This syntax allows you to create a new table with the same data types as those of the fields that are selected from the old table. It also allows you to rename the fields in the new table by giving them new names.

INPUT/OUTPUT:

SQL> CREATE TABLE NEW_BILLS(NAME, AMOUNT, ACCOUNT_ID)

2 AS (SELECT * FROM BILLS WHERE AMOUNT < 50);

Table created.

ANALYSIS:

The preceding statement creates a new table (NEW_BILLS) with all the records from the BILLS table that have an AMOUNT less than 50.

Some database systems also allow you to use the following syntax:

SYNTAX:

INSERT NEW_TABLE

SELECT <field1, field2... | *> from OLD_TABLE

<WHERE...>

The preceding syntax would create a new table with the exact field structure and data found in the old table. Using SQL Server's Transact-SQL language in the following example illustrates this technique.

INPUT:

INSERT NEW_BILLS

1> select * from BILLS where AMOUNT < 50

2> go

(The GO statement in SQL Server processes the SQL statements in the command buffer. It is equivalent to the semicolon (;) used in Oracle7.)

The ALTER TABLE Statement

Many times your database design does not account for everything it should. Also, requirements for applications and databases are always subject to change. The ALTER TABLE statement enables the database administrator or designer to change the structure of a table after it has been created.

The ALTER TABLE command enables you to do two things:

• Add a column to an existing table
• Modify a column that already exists

The syntax for the ALTER TABLE statement is as follows:

SYNTAX:

ALTER TABLE table_name

<ADD column_name data_type; |

MODIFY column_name data_type;>

The following command changes the NAME field of the BILLS table to hold 40

characters:

INPUT/OUTPUT:

SQL> ALTER TABLE BILLS

2 MODIFY NAME CHAR(40);

Table altered.

NOTE: You can increase or decrease the length of columns; however, you can not decrease a column's length if the current size of one of its values is greater than the value you want to assign to the column length.

Here's a statement to add a new column to the NEW_BILLS table:

INPUT/OUTPUT:

SQL> ALTER TABLE NEW_BILLS

2 ADD COMMENTS CHAR(80);

Table altered.

ANALYSIS:

This statement would add a new column named COMMENTS capable of holding 80 characters. The field would be added to the right of all the existing fields.

Several restrictions apply to using the ALTER TABLE statement. You cannot use it to add or delete fields from a database. It can change a column from NOT NULL to NULL, but not necessarily the other way around. A column specification can be changed from NULL to NOT NULL only if the column does not contain any NULL values. To change a column from NOT NULL to NULL, use the following syntax:

SYNTAX:

ALTER TABLE table_name MODIFY (column_name data_type NULL);

To change a column from NULL to NOT NULL, you might have to take several steps:

1. Determine whether the column has any NULL values.

2. Deal with any NULL values that you find. (Delete those records; update the column's value, and so on.)

3. Issue the ALTER TABLE command.

NOTE: Some database management systems allow the use of the MODIFY Clause; others do not. Still others have added other clauses to the ALTER TABLE statement. In Oracle, you can even alter the table's storage parameters. Check the documentation of the system you are using to determine the implementation of the ALTER TABLE statement.

The DROP TABLE Statement

SQL provides a command to completely remove a table from a database. The DROP TABLE command deletes a table along with all its associated views and indexes. After this command has been issued, there is no turning back. The most common use of the DROP TABLE statement is when you have created a table for temporary use. When you have completed all operations on the table that you planned to do, issue the DROP TABLE statement with the following syntax:

SYNTAX:

DROP TABLE table_name;

Here's how to drop the NEW_BILLS table:

INPUT/OUTPUT:

SQL> DROP TABLE NEW_BILLS;

Table dropped.

ANALYSIS:

Notice the absence of system prompts. This command did not ask Are you sure? (Y/N). After the DROP TABLE command is issued, the table is permanently deleted.

WARNING: If you issue

SQL> DROP TABLE NEW_BILLS;

you could be dropping the incorrect table. When dropping tables, you should always use the owner or schema name. The recommended syntax is

SQL> DROP TABLE OWNER.NEW_BILLS;

We are stressing this syntax because we once had to repair a production database from which the wrong table had been dropped. The table was not properly identified with the schema name. Restoring the database was an eight-hour job, and we had to work until well past midnight.

The DROP DATABASE Statement

Some database management systems also provide the DROP DATABASE statement, which is identical in usage to the DROP TABLE statement. The syntax for this statement is as follows:

DROP DATABASE database_name

NOTE: The various relational database implementations require you to take different steps to drop a database. After the database is dropped, you will need to clean up the operating system files that compose the database.

Exercise 9.2

Create a database with one table in it. Issue the DROP TABLE command and the issue the DROP DATABASE command. Does your database system allow you to do this? Single-file based systems, such as Microsoft Access, do not support this command. The database is contained in a single file. To create a database, you must use the menu options provided in the product itself. To delete a database, simply delete the file from the hard drive.

Summary

It covers the major features of SQL's Data Manipulation Language (DML). In particular, you learned five new statements: CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, and DROP DATABASE. It also discusses the importance of creating a good database design.

A data dictionary is one of the most important pieces of documentation you can create when designing a database. This dictionary should include a complete description of all objects in the database: tables, fields, views, indexes, stored procedures, triggers, and so forth. A complete data dictionary also contains a brief comment explaining the purpose behind each item in the database. You should update the data dictionary whenever you make changes to the database.

Before using any of the data manipulation statements, it is also important to create a good database design. Break down the required information into logical groups and try to identify a primary key field that other groups (or tables) can use to reference this logical group. Use foreign key fields to point to the primary or foreign key fields in other tables.

You learned that the CREATE DATABASE statement is not a standard element within database systems. This variation is primarily due to the many different ways vendors store their databases on disk. Each implementation enables a different set of features and options, which results in a completely different CREATE DATABASE statement. Simply issuing CREATE DATABASE database_name creates a default database with a default size on most systems. The DROP DATABASE statement permanently removes that database.

The CREATE TABLE statement creates a new table. With this command, you can create the fields you need and identify their data types. Some database management systems also allow you to specify other attributes for the field, such as whether it can allow NULL values or whether that field should be unique throughout the table. The ALTER TABLE statement can alter the structure of an existing table. The DROP TABLE statement can delete a table from a database.

Q&A

Q Why does the CREATE DATABASE statement vary so much from one system to another?

A CREATE DATABASE varies because the actual process of creating a database varies from one database system to another. Small PC-based databases usually rely on files that are created within some type of application program. To distribute the database on a large server, related database files are simply distributed over several disk drives. When your code accesses these databases, there is no database process running on the computer, just your application accessing the files directly. More powerful database systems must take into account disk space management as well as support features such as security, transaction control, and stored procedures embedded within the database itself. When your application program accesses a database, a database server manages your requests (along with many others' requests) and returns data to you through a sometimes complex layer of middleware.

Q Can I create a table temporarily and then automatically drop it when I am done with it?

A Yes. Many database management systems support the concept of a temporary table. This type of table is created for temporary usage and is automatically deleted when your user's process ends or when you issue the DROP TABLE command.

Q Can I remove columns with the ALTER TABLE statement?

A No. The ALTER TABLE command can be used only to add or modify columns within a table. To remove columns, create a new table with the desired format and then select the records from the old table into the new table.