1.2 SQL Server 2000 data table managementA table is a database object that contains all the data in the database. After designing the database, you can create a table that will store the data in the database. Any user with the corresponding permissions can perform operations on it unless it has been deleted. Tables are stored in database files. Up to 102 columns can be defined for each table. The names of tables and columns must comply with the identifiers. They must be unique in a specific table, but different tables in the same database can use the same column name. The data type must be specified for each column. This section uses the student database as an instance to manage its data tables.
1.2.1 create a data tableSQL Server 2000 provides three methods to create a database table: one is to use the table designer to create a table, and the other is to use a database relationship diagram to create a table; the last one is to use the CREATE command in the transact-SQL statement to create a table. (1) use the table designer to create a table in the SQL Server Enterprise Manager. Expand the specified server and database, open the student database, and right-click the table object, select the "Create Table" command from the shortcut menu to display the "Table Designer" dialog box, as shown in Figure 4-7. Figure 4-7 The Table Designer defines the following attributes of a column in the dialog box: column name, data type, length, precision, decimal places, whether to be empty, default value, ID column, initial value of the ID column, increment value of the ID column, and whether a row is identified. Then, configure the settings as prompted. After entering the information, click "save". The "Select name" dialog box is displayed, as shown in Figure 4-8. Enter studentsinfo and click OK. Figure 4-8 enter the table name (2) create a table using the database relationship diagram in the SQL Server Enterprise Manager. Expand the specified server and database, open the student database, and right-click the graph object, click the "Create Database relationship diagram" command in the shortcut menu to display the "Create Database relationship diagram wizard" dialog box, as shown in Figure 4-9. In this dialog box, click "Next" to open the "select table to be added" dialog box. In the "select table to be added" dialog box, all tables in the student database are listed in the List on the left, including the tables created by the system. Select the table studentsinfo and click "add" to add it to the list on the right, as shown in Figure 4-10. Figure 4-9 create database relationship diagram wizard Figure 4-10 select the table to be added and click "Next" to open the "complete database relationship diagram wizard" dialog box. The table studentsinfo appears in the list, click "finish" to open the "New Graph" dialog box. We can see that the studentsinfo table appears in the graph. Next, we will create the table department in the graph. Right-click the blank area of the database relationship diagram and select the "Create Table" command. In the displayed "input table name" dialog box, enter the table name department to be created, and click "OK. Then, in the displayed table editing dialog box, enter the column information, as shown in Figure 4-11. Figure 4-11 after creating a table department in the database graph, click the "save" button in the dialog box and enter the name of the graph stu_di#in the "Save as" dialog box, as shown in Figure 4-12. Click "OK". The "save" dialog box is displayed, as shown in 4-13. The system prompts you whether to save the table department to the student database. Click "yes" to save the settings. Figure 4-12 Save the new relational graph stu_di1_figure 4-13 ask to save it here. The table department is successfully saved to the database, and the relational graph stu_di1_will also appear in the database relational graph list. Later, you can open the graph stu_di.pdf and edit the table. (3) Use the create table command in the transact-SQL statement to create a table. The syntax format is as follows: Create Table [
Database_name.[
Owner]. |
Owner.]
Table_name({<
Column_definition> |
Column_nameAs
Computed_column_expression |<Table_constraint> }[,... N]) [on {
Filegroup| Default}] [textimage_on {
Filegroup| Default}] the parameters are described as follows. ●
Database_name: The name of the database in which the table is to be created. ●
Owner: The user ID of the new table owner,
OwnerRequired
Database_nameExisting User ID in the specified database,
OwnerThe default value is
Database_nameID of the user associated with the current connection in the specified database. ●
Table_name: The name of the new table. The table name must comply with the identifier rules. In the database
Owne r. table_nameThe combination must be unique.
Table_nameThe name can contain a maximum of 128 characters, but the name of the local temporary table (with a # character before the name) can contain a maximum of 116 characters. ●
Column_name: Column name in the table. The column name must comply with the identifier rules and be unique in the table. ●
Computed_column_expression: Is an expression that defines the calculated column value. The expression can be a non-computed column name, constant, function, or variable, or any combination of the above elements connected using one or more operators. The expression cannot be a subquery. ● On {
Filegroup| Default}: Specifies the name of the file group used to store the table. ● Textimage_on: Specifies the file group for storing text, ntext, and image columns. ●
Data_type: Specifies the Data Type of a column. ● Default: used to specify the default value of a column. For example, to create a student table studentsinfo in an existing database student, the syntax is as follows: Create Table studentsinfo (stu_id int not null, sname varchar (20) not null, sgender char (2), sage int, sdept varchar (20) not null)
1.2.2 insert and modify table dataAfter creating a table structure, the most important task is to insert data into the table, which is also the most basic operation. Suppose you want to insert a student record to the studentsinfo table, you can right-click the table studentsinfo in the Enterprise Manager, point to "Open Table", and then select the "return all rows" command, open the "query table content" dialog box. You can add and modify table data in the dialog box. Each time a new row is inserted, a blank row is added below the table to add new content. If the format or length of the input data does not meet the requirements, an error message is displayed. Figure 4-14 shows the dialog box after data is inserted. Figure 4-14 insert a student record to the studentsinfo table. You can also use the INSERT command in the transact-SQL statement to insert a record to the table. The syntax is as follows: insert [into] {
Table_nameWith (<table_hint_limited> [... N]) |
View_name|
Rowset_function_limited}{[(
Column_list)] {Values ({default | null |
Expression}[,... N]) |
Derived_table|
Execute_statement} The parameters are described as follows. ● [Into]: an optional keyword that can be used between insert and the target table. ●
Table_name: Name of the table or table variable to receive data. ● With (<table_hint_limited> [...
N]): Specifies one or more table prompts allowed by the target table. The With keyword and parentheses are required. ●
View_name: View name and optional alias. Pass
View_nameThe referenced view must be updatable. Modifications made by the insert statement cannot affect multiple base tables referenced in the from clause of the view. ●
Rowset_function_limited: It is an openquery or OpenRowSet function. ● (
Column_list): The list of one or more columns in which you want to insert data. Must be enclosed in parentheses
Column_listAnd separated by commas. ● Values: introduces the list of data values to be inserted. For
Column_list(If specified) or each column in the table must have a data value. The value list must be enclosed in parentheses. You must use
Column_listSpecify columns that store each input value. ● Default: Forces SQL Server to load the default value defined by the column. ●
ExpressionA constant, variable, or expression. The expression cannot contain select or execute statements. ●
Derived_table: Retrieve row subqueries from the database,
Derived_tableUsed as the input for external queries. ●
Execute_statement: Any valid execute statement that uses select or readtext statements to return data. For example, the syntax for inserting data in the student table is as follows: insert into student (stu_id, sname, sgender, sage, sdpt) values (994101, 'zhang tao', 'mal', 21, "Computer System") if you need to modify data, you can find the target row in the query table content dialog box (4-14), delete the data to be modified, and enter new data. This method is suitable for tables with fewer records and can easily find the rows to be modified. However, if the table contains a large number of records, it will take a long time. You can use the update command in the transact-SQL statement to modify the data in the table. The syntax is as follows: Update {
Table_nameWith (<table_hint_limited> [... N])} set {
Column_name= {
Expression| Default | null} Where <
Search_condition> The parameters are described as follows. ●
Table_name: Name of the table to be updated. If the table is not on the current server or database or is not owned by the current user, the name can be specified by the linked server, database, and owner name. ●
(<Table_hint_limited> [... n]
): Specify one or more table prompts allowed by the target table. The With keyword and parentheses are required. ● Set: Specifies the list of columns or variable names to be updated. ●
Column_name: Name of the column containing the data to be changed.
Column_nameMust reside in the table or view specified in the update clause. The ID column cannot be updated. ●
Expression: Variable, literal value, expression, or Subselect statement that returns a single value by adding arc.
ExpressionThe returned value is replaced.
Column_name. ● Default: use the default value defined for the column to replace the existing values in the column. If this column does not have a default value and is defined to allow null values, this can also be used to change the column to null. For example, in the student table, change the student ID stu_id to 994103 years old to 20 years old. The syntax is as follows: Update student set sage = 20 where stu_id = 994103
1.2.3 query table dataIn the practical application of the SQL Server database, it is critical to accurately query valid information from a large amount of data in the table. This section describes how to query data. In the SQL Server Enterprise Manager, expand the specified server and database, open the student database, right-click the table object, point to "Open Table", and select the "query" command, open the query designer dialog box, as shown in Figure 4-15.
Figure 4-15 the query designer has four tabs: Diagram pane, grid pane, SQL pane, and result pane. The Diagram pane displays the tables and other table structured objects being queried. Each rectangle represents a table or table structured object, and displays available data columns and icons that indicate how each column is used for query. A line between rectangles indicates a join. The grid pane contains a grid similar to a workbook in which you can specify options, such as the data columns to be displayed, the rows to be selected, and how to group rows. The SQL pane displays the SQL statements used for queries or views. You can edit the SQL statements created by the designer or enter your own SQL statements. For SQL statements that cannot be created using the Diagram pane and grid pane (such as joint queries), it is particularly helpful to enter SQL statements. The result pane displays the results of the most recently executed selection query. You can modify the database by editing the values in the grid unit and add or delete rows. You can operate on any pane to create a query or view: You can select a column in the Diagram pane and enter the column in the grid pane, you can also use the SQL statement in the SQ pane to specify the columns to be displayed. The Diagram pane, grid pane, and SQL pane are both synchronized-when you make changes in a pane, the other pane automatically reflects the changes. You can use the query designer to query tables in the following five processes. (1) Add a table right-click the background in the Diagram pane, and select the "Add Table" command from the shortcut menu. In the "Add Table" dialog box, select the tab of the object type to be added to the query. In the project list, double-click each item you want to add. After adding a project, click Close. The query designer updates the Diagram pane, grid pane, and SQL pane accordingly. (2) to add a column to a query, you must add it to the query. The reason for adding a column may be that the column is displayed in the query output, sorted by the column, searched for the column content, or summarized. You can add columns as follows: ● Add individual columns in the Diagram pane and select the check box next to the data column to be added. Alternatively, move to the first blank grid row of the column to be added in the grid pane, click the field in the column, and select the column name from the list. Note that to add data rows to a specific position in the grid pane, select the grid row for which you want to add new columns and press the INS key to add a new column to the row. ● Add all columns in a table or table structured object in the Relationship Diagram pane and select the check box next to "(all columns. You can also specify the object name * in the SQL statement in the SQL pane, and replace the object name with the name of the table or table structured object. ● Add all columns in all tables and table structured objects to ensure that no wiring is selected in the Diagram pane. In the query dialog box, right-click and select the "properties" command from the shortcut menu. In the displayed dialog box, select the "query" tab and select "output all columns ". Alternatively, specify * in the SQL statement output list in the SQL pane *. For example, to display the name, gender, and age of students in studentsinfo in the structure set, you can select the check boxes next to the sname, sgender, and sage columns in the Diagram pane, the query designer updates the content in the Diagram pane, grid pane, and SQL pane accordingly. (3) It is easier to set query conditions. In the grid pane, locate the row to be set as the query condition, and enter the query condition in the "criterion" column corresponding to the row. For example, to display the name, gender, and age of a student in the Mathematics Department in the result set, first add the corresponding column and find the row sdpt in the grid pane, enter "= Mathematics Department" in the corresponding "criterion" column ". Note: In the end, remove the output mark in the row where the sdpt is located, because no additional information is required in the result set. (4) rearrange the output columns in the grid pane. Click the row selector button on the left of the row to select the row containing the column and drag the row to the new position. Alternatively, you can directly edit the sequence of column names in the SQL pane. For example, to display the student's age information before the Gender, click the row selector of the sgender row and drag it below the sage. If you want to display data in ascending or descending order of the specified column. You can use the following settings. ● In the Relationship Diagram pane, set: select the data column to be sorted (for example, sort by stu_id), right-click, in the pop-up shortcut menu, select "sort in ascending order" or "sort in descending order. ● Set in the grid Pane: if the data column to be sorted is not in the grid column, add this column to the blank row, select the "ASCENDING" or "descending" command in the corresponding "sort type" column, and then remove the output mark of the row. ● Set in the SQL Pane: add the order by clause to the SQL statement. For example, to sort stu_id in ascending order, add order by stu_id ASC here, because order by is sorted in ascending order by default, ASC does not need to be filled in. If you want to sort data in descending order, you only need to enter DESC. (5) run the query. After the query design is completed, you can run the query. Right-click any position in the query dialog box, select the "run" command from the shortcut menu, or click the "run" icon in the toolbar. The query result is displayed in the result pane, as shown in Figure 4-16. Figure 4-16 query the results of student information in the query designer to query data rows or columns in the database table. You can also use the SELECT command in the transact-SQL statement, its basic syntax format is as follows. Select select_list [into new_table] From table_source [where search_condition] [group by group_by_expression] [having search_condition] [order by order_expression [ASC | DESC] the parameters are described as follows. ● Select_list: select a list that specifies the number of columns and attributes in the query result set. ● Into: construct a new table using the query result set. ● New_table: name of the new table constructed by the into statement. ● From: indicates the name of the queried table and the logical relationship between the tables. ● Table_source: Specifies the table, view, derived table, and join table used for the SELECT statement. ● Where: indicates the query condition, which indicates which data rows in the table are returned to the result set. ● Search_condition: Use predicates to limit the rows returned in the result set. ● Group by: indicates the order of rows in the query result set. ● Group_by_expression: indicates the expression used for grouping. ● Having: usually used with group by to indicate the search criteria for a group or set. ● Search_condition: Specify the search conditions that should be met by a group or aggregation. ● Order by: Specify the order of rows in the query condition set. For example, in the studentsinfo table above, we want to query the names, ages, and gender of mathematics students and sort the students in ascending order by student numbers. The syntax is: Select sname, sage, sgenderfrom studentsinfowhere (sdpt = n' Department of mathematics ') order by stu_id1.2.4 delete a data tableSometimes you need to delete a table (for example, when you want to implement a new design or release the database space ). When a table is deleted, Its Schema definitions, data, full-text indexes, constraints, and indexes are permanently deleted from the database. The storage space that originally stores the table and its indexes can be used to store other tables. (1) Use the Enterprise Manager to delete a table. In the SQL Server Enterprise Manager, expand the specified server and database, open the database student, and right-click the table object, click Delete from the shortcut menu to display the delete object dialog box. Click "delete all" to delete the table. (2) Using the drop TABLE statement to delete a table drop TABLE statement can delete data in a table and all indexes, triggers, constraints, and license objects related to the table. The syntax of the drop TABLE statement is as follows: Drop table {Table_name}[,... N] where,Table_nameSpecifies the name of the data table to be deleted. For example, the syntax for deleting a studentsinfo table is as follows: Drop table studentsinfo can also be used to delete multiple tables at the same time. You only need to separate the names of the tables to be deleted with commas.
