Basics of SQL Data Operations (Intermediate) 7

Indexing with SQL

To index a table, start taskbar SQL The ISQL/w program in the Sever program group. After entering the query window, enter the following statement:

CREATE INDEX mycolumn_index ON mytable (myclumn)

This statement creates an index named mycolumn_index. You can give an index any name, but you should include the name of the field being indexed in the index name. This will help you figure out the purpose of creating the index in the future.

Note:

When you execute any SQL statement in this book, you will receive the following message:

This command did not return data,and it did not return any rows

This shows that the statement was executed successfully.

The index mycolumn_index is performed on the mycolumn field of the table mytable. This is a non-clustered index and a non-unique index. (This is the default property of an index)

If you need to change the type of an index, you must delete the original index and rebuild it one. After creating an index, you can delete it using the following SQL statement:

DROP INDEX mytable.mycolumn_index

Note on DROP INDEX You need to include the name of the table in the statement. In this example, the index you deleted is mycolumn_index, which is the index of the table mytable.

To create a clustered index, you can use the keyword CLUSTERED. ) Remember that a table can only have one clustered index. (Here is an example of how to create a clustered index on a table:

CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn)

If there are duplicate records in the table, an error will occur when you try to create an index using this statement. But tables with duplicate records can also be indexed; you just tell SQL this using the keyword ALLOW_DUP_ROW Just Sever:

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

WITH ALLOW_DUP_ROW

This statement creates a clustered index that allows duplicate records. You should try to avoid duplicate records in a table, but if they do occur, you can use this method.

To create a unique index on a table, you can use the keyword UNIQUE. This keyword can be used for both clustered indexes and non-clustered indexes. Here's an example:

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

This is the indexing statement you will use often. Whenever possible, you should try to build unique clustered indexes on one table to enhance query operations.

Finally, you need to create an index on multiple fields - a composite index - and include multiple field names in the index creation statement. The following example creates an index on the firstname and lastname fields:

CREATE INDEX name_index ON username(firstname,lastname)

This example creates a single index on two fields. In a composite index, you can index up to 16 fields.

Create indexes using transaction managers

Creating indexes using transaction managers is much easier than using SQL statements. Using the transaction manager, you can see a list of indexes that have been created and select indexing options through a graphical interface.

Using the transaction manager you can create indexes in two ways: Using Manage Tables window or use the Manage Indexes window.

To create a new index using the Manage Tables window, click the button Advanced Options (it looks like a table with a plus sign in front of it). This opens the Advanced Options dialog box. There is a section of this dialog box named PRimary Key (see Figure 11.1).

Figure 11. 1

To create a new index, select the field name you want to index from the drop-down list. If you want to create an index on multiple fields, you can select multiple field names. You can also choose whether the index is clustered or non-clustered. After saving the table information, the index will be automatically created. InManage A key will appear next to the field name in the Tables window.

You have created a "master index" for your table. The primary index must be established on fields that do not contain null values. Additionally, the primary index forces a field to be a unique value field.

To build an index without these restrictions, you need to use Manage Indexes window. Select Manage|Indexes from the menu to open the Manage Indexes window. Manage Indexes In the window, you can select the table and specific index through the drop-down box. (See Figure 11.2). To create a new index, select New from the Index drop-down box Index. and you can select the fields to index. Click the Add button to add the field to the index.

Figure 11.2

There are many different options you can choose for your index. For example, you can choose whether the index is clustered or non-clustered. You can also specify that the index is a unique index. After designing the index, click the Build button to create the index.

Note:

Unique index means that the field cannot have duplicate values, not that only one index can be created.

SQL core statements

In Chapter 10, you learned how to use SQL SELECT Statement to retrieve data from a table. However, until now, there has been no discussion on how to add, modify or delete data in a table. In this section, you'll learn about these.

Insert data

To add a new record to the table, you use SQL INSERT statement. Here is an example of how to use such a statement:

INSERT mytable (mycolumn) VALUES (‘some data’)

This statement converts the string ‘some data’ is inserted into the mycolumn field of table mytable. The name of the field into which data will be inserted is specified in the first bracket, and the actual data is given in the second bracket.

INSERT The complete syntax of the statement is as follows:

INSERT [INTO] {table_name|view_name} [(column_list)] {DEFAULT VALUES |

Values_list | select_statement}

If a table has multiple fields, you can insert data into all fields by separating the field name and field value with commas. Suppose the table mytable has three fields first_column, second_column, and third_column. The following INSERT statement adds a complete record with values ​​for all three fields:

INSERT mytable (first_column,second_column,third_column)

VALUES (‘some data’,’some more data’,’yet more data’)

Note:

You can use the INSERT statement to insert data into text fields. However, if you need to enter a very long string, you should use the WRITETEXT statement. This material is too advanced for this book and will not be discussed. For more information, please refer to Microsoft Documentation for SQL Sever.

If you are INSERT What will happen if only two fields and data are specified in the statement? In other words, you insert a new record into a table, but one of the fields does not provide data. In this case, there are four possibilities:

If the field has a default value, that value will be used. For example, suppose you insert a new record without providing data to the third_column field, and this field has a default value of 'some value’. In this case, the value 'some value' is inserted when a new record is created.
If the field can accept null values ​​and there is no default value, null values ​​will be inserted.
If the field cannot accept null values ​​and there is no default value, an error will occur. You will receive the error message:
The column in table mytable may not be null.

Finally, if the field is an identity field, then it will automatically generate a new value. When you insert a new record into a table that has an identification field, just ignore the field and the identification field will assign itself a new value.
Note:

After inserting a new record into a table with an identity field, you can use the SQL variable @@identity to access the value of the identity field of the new record

. Consider the following SQL statement:

INSERT mytable (first_column) VALUES(‘some value’)

INSERT anothertable(another_first,another_second)

VALUES(@@identity,’some value’)

If the table mytable has an identification field, the value of this field will be inserted into the another_first field of the table anothertable. This is because the variable @@identity always holds the last value inserted into the identity field.

Field another_first should have the same data type as field first_column. However, the field another_first cannot be a field that is supposed to identify. Another_first field is used to save the value of field first_column.

Delete Records

To delete one or more records from a table, you need to use SQL DELETE statement. You can provide WHERE to the DELETE statement clause. The WHERE clause is used to select records to be deleted. For example, the following DELETE statement only deletes the value of the field first_column equal to 'Delete Me’ record:

DELETE mytable WHERE first_column=’Deltet Me’

DELETE The complete syntax of the statement is as follows:

DELETE [FROM] {table_name|view_name} [WHERE clause]

Any condition that can be used in the SQL SELECT statement can be used in the WHERE clause of the DELECT statement used in. For example, the following DELETE statement only deletes those whose first_column field value is 'goodbye' or second_column field value is 'so long’ record:

DELETE mytable WHERE first_column=’goodby’ OR second_column=’so long’

If you don’t provide WHERE to the DELETE statement clause, all records in the table will be deleted. You shouldn't think this way. If you want to delete all records in the table, you should use TRUNCATE as discussed in Chapter 10 TABLE statement.

Note:

Why should we use TRUNCATE TABLE statement instead of DELETE statement? When you use TRUNCATE TABLE statement, record deletion is not recorded. In other words, this means that TRUNCATE TABLE is much faster than DELETE

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