Basics of SQL Data Operations (Elementary) 5

Create a new table using SQL Transaction Manager

You can create a new table using the methods described in the previous sections. However, it is easier to create new tables using a transaction manager. This section describes how to use this program to create new tables.

SQL from taskbar Select SQL EnterPRise in the Sever program group Manager, start the program and you will see the window shown in Figure 10.4. Browse the tree structure in the Service Manager window and select the folder named Database. After opening the folder Database, select the database you created in Chapter 3.

Note:

If you haven’t created your own database yet, go back to Chapter 3 to create it. You should never add data to master, tempdb or any other system database.

After selecting the database, you will see a folder named Group/users and a folder named objects. Open the folder objects and you will see many folders, one of which is named Tables. Right click on the folder Tables and select New table, the window shown in Figure 10.5 will appear.

You can use the Manager Tables window to create a new table. Manager The Tables window has 7 columns: Key, Column, Name, Datatype, Size, Nulls and Default. Manager Each row in the Tables window identifies information about a field in the table.

Figure 10.4

10.5

To create a new table, you must enter at least one row of information. In the column named Type mycolumn under the Name column. Next, select the Datatype column and select CHAR from the drop-down list. After you enter information in these two columns, the window will look like Figure 10.6.

Figure 10.6

You have created a simple table with only one field. Click the Save button to save this new table. When you are asked to enter a name for the new table, enter mytable and click OK. The table is now saved to your database.

If you open the Tables folder in the Service Manager window, you will see the new tables you created listed. You can double-click the table's chart to edit it, and the Manager The Tables window will reappear and you can add new fields and save again.

Any work that can be done with the SQL transaction manager can be done with SQL statements. However, the transaction manager makes the table creation process much simpler.

Add data to a table

The next chapter will discuss how to insert data into a table using SQL. However, if you need to add many records to a table, it is very inconvenient to use SQL statements to enter data. Fortunately, Microsoft SQL Server comes with a client application called Microsoft Query that makes it easy to add data to tables.

Start SQL located on the taskbar Microsoft Query program in the Sever program group. Select File|New Query from the menu at the top of the window. A Select Data will be displayed. Source dialog box (see Figure 10.7). Select your data source name and click Use.

Figure 10. 7

After entering your login account and password, the program asks you to select a table and a database. Select the table you created in the previous section (mytable ), click the button Add and then click the button Close to close the dialog box.

A dialog box will appear in the upper left corner of the window. In the box is a column of field names taken from the table mytable. You can double-click any field to add it to the main window. If you double-click the asterisk (*) character, all fields will be added to the main window.

If you have records in your table, they now appear under the field headings in the main window. However, because you just created the table, the table is still empty. To add a new record, select Records|Allow Editing, a new record will appear in the main window. Entering a row of data to complete the record adds a new record to the table.

Figure 10. 8

When you go to the next new record, the value you entered in the previous record will be automatically saved. If you need, you can use Microsoft Query Enter a few hundred records into the table.

Deleting and Modifying Tables

You should design tables carefully before creating them, because you will be severely limited in changing an existing table. For example, once you have created a table, you cannot delete fields in the table or change the data type of the fields. All you can do in this situation is drop the table and start over (see the "Creating Records and Tables Using SQL" section in Chapter 11, "Intermediate SQL").

To delete a table, you can use the SQL statement DROP TABLE. For example, to completely delete the table mytable from the database, you would use the following statement:

DROP TABLE mytable

WARNING:

USE DROP Be careful when using the TABLE command. Once a table is deleted, you cannot restore it.

When you build a site, you will most likely need to enter test data into the database. And when you're ready to make your site available to the world, you'll want to clear the table of this testing information. If you want to clear all data in a table without deleting the table, you can use TRUNCATE TABLE statement. For example, the following SQL statement deletes all data from the table mytable:

TRUNCATE TABLE mytable

Although you cannot delete or modify existing fields, you can add new fields. The easiest way to achieve this is to use the Manager in SQL Transaction Manager Tables window. You can also use the SQL statement ALTER TABLE. Here is an example of how to use this statement:

ALTER TABLE mytable ADD mynewcolumnINT NULL

This statement adds a new field mynewcolumn to the table mytable. When you add a new field, you must allow it to accept null values ​​because there may already be many records in the table.

Summary

This chapter introduced you to SQL. Using SQL, you can operate Microsoft SQL Server database. You have learned to use the SELECT statement to retrieve data from the database, and you have also learned how to use CREATE TABLE statement and SQL transaction manager to create new tables. Finally, you learned how to specify a series of important field properties.

The next chapter will introduce how to use indexes to enhance the operation of SQL queries. Your SQL knowledge will be further expanded through many other SQL statements and functions.

The above is the content of SQL Data Operation Basics (Elementary) 5. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!