13. T-SQL and SQL Query Analyzer
What is SQL?
What is T-SQL?
New features for T-SQL
How to use T-SQL
Summary of this chapter
In this chapter, we will introduce Structured Query Language
What is SQL? SQL is a database query and programming language for accessing materials and queries, update, and managing associated library systems. The US State Standards (ANSI) and the International Standardization Organization (ISO) have developed SQL standards. ANSI is an American industry and business group organization to develop the US business and communication standards. ANSI is also one of the members of ISO and International Electrotechnical Commission (IEC). ANSI releases the US standards corresponding to the International Standards Organization. In 1992, ISO and IEC released SQL international standards called SQL-92. The corresponding standard released by ANSI is ANSI SQL-92. ANSI SQL-92 is sometimes referred to as ANSI SQL. Although there are some differences in SQL versions used by different associated database, most of them follow the ANSI SQL standard. SQL Server uses ANSI SQL-92 extension, called T-SQL, which follows ANSI to formulate SQL-92 standards. The SQL language includes two statements of two major programming language categories: Data Definition Language (DDL) and Data Operation Language (DML). Below we will introduce these two languages. DDL
DDL is used to define and manage objects, such as databases, data sheets, and view tables (Chapter 18, will explain what to view the table). DDL statements typically include Create, Alter, and DROP commands for each object. For example, Create Table, ALTER TABLE, and DROP TABLE can be used to establish new information tables, modify their properties (such as new or delete data line), delete data tables, etc. We will introduce one by one.
CREATE TABLE statement
Use DDL to establish an example-of-Customer_Data, which is called Customer_Data, which will be used in this chapter. We will use this information sheet. As mentioned earlier, the CREATE TABLE statement can be used to establish a data sheet. This example of the sample is defined as four data lines as follows:
Use mydb
Create Table Customer_Data
(Customer_ID Smallint,
First_name char (20),
Last_name char (20),
Phone char (10))
Go
This statement can produce a Customer_Data data table, which has always been empty until the data is filled in the data sheet. See Chapter 10 for details on establishing a database data sheet.
ALTER TABLE statement
The ALTER TABLE statements are used to change the definition and properties of the data sheet. In the following example, we use Alter Table to add a Middle_Initial data line in the existing Customer_Data table. ALTER TABLE CUSTOMER_DATA
Add middle_initial char (1)
Go
The definition of the data sheet includes five data lines, not the previous four data lines. For more details on using Alter Table, see Chapter 15.
Drop Table Statement
Drop Table Statement is used to delete data sheet definitions and all data, indexes, triggers, conditional constraints, and permissions for data sheets. To delete our Customer_Data data table, you can use the following command:
Drop Table Customer_Data
Go
See Chapter 15 for details on Drop Table Statement.
DML
DML uses insert, select, update, and delete, etc. to operate the information contained in the database object.
INSERT statement
Insert statement is used to insert a list of data in a data sheet or view table. For example, if you want to add a customer in the Customer_Data table, you can use the following INSERT statement:
INSERT INTO CUSTOMER_DATA
(Customer_ID, First_Name, Last_name, Phone)
VALUES (777, "Frankie", "Stein", "4895873900")
Note that the second line of the second line of SQL statements is inventory, and the order of the data line name on the list determines which information of the data value will be placed. For example, the first data value will be placed in the list of the first data listed in the list, and the second data value is placed in the second data line, and so on. Since we are in establishing a data sheet, the order of defining the data line fill in the value is the same, so we don't have to specify the column name. We can use the following INSERT statement instead of:
INSERT INTO CUSTOMER_DATA
VALUES (777, "Frankie", "Stein", "4895873900")
note
If this form of INSERT statement is used, but the inserted numerical order is different from the establishment data table, the value will be placed in the wrong data line. If the type of data does not match the type of definition, an error message will be received.
SELECT statement
SELECT statement is used to retrieve information in the table, and which information is retrieved by the list of information lines and the state of the WHERE clauses in the statement. For example, to retrieve the information of Customer_ID and the first_name data line from the previous Customer_Data data table, and only want to take out the information of the first_name data value of each column is frankie, then the following SELECT statement can be used:
SELECT Customer_ID, First_Name from Customer_Data
WHERE first_name = "frankie"
If a column is compliant with the standard in the SELECT statement, the result will be shown as follows:
Customer_id first_name
--------------------------
777 Frankie
Update statement
Update statements are used to update or change the values in one or more columns. For example, a customer named Frankie Stein wants to change his last name to Franklin in the record, you can use the following Update statement: Update Customer_Data
Set first_name = "franklin"
WHERE LAST_NAME = "stein" and custom000_id = 777
We join Customer_ID project in the WHERE clause to determine that customers who have other names Stein will not be affected - only Customer_ID is 777 customers, the last name will change.
Description
When you use Update statements, you have to determine the filter criteria for the WHERE clause, so that some information that should not be changed inadvertently.
DELETE statement
DELETE statements are used to delete a list of or more columns in the data sheet, and you can also delete all the information columns in the data sheet. To remove all columns from a Customer_Data table, you can take advantage of the following statements:
Delete from Customer_Data
or
Delete Customer_Data
The FROM keyword before the data sheet is selective in the delete statement. In addition, these two statements are identical.
To remove the value of the Customer_ID data line from the Customer_Data table, you can use the following statements:
Delete from Customer_Data
WHERE CUSTOMER_ID <100
Now we have quickly viewed the DDL and DML statement provided by SQL, then, T-SQL will be described below.
What is T-SQL? T-SQL is a primary language for standard SQL programming languages, which is used to communicate with SQL Server communication. T-SQL provides standard SQL DDL and DML functions, plus extension functions, system pre-depicting programs, and programming structures (eg, if and while) are more flexible. The function of T-SQL continues to grow with the new version of SQL Server, let's take a look at the latest features of T-SQL.
Introduction T-SQL's new features T-SQL in Microsoft SQL Server 7.0, there are many new features, including new pre-deposit programs, system data sheets, functions, data type, statements, and existing statements. . These are kept in SQL Server 2000, so we must review here (especially when you are not familiar with the function of T-SQL in SQL Server 7.0, this warm application is important). Because there are too many new features that need to be discussed, we will introduce for each category for each category.
Related information To find a complete list of these features, you can refer to the topic of "Transact-SQL new and strengthened function" in SQL Server "online book".
System Prepaid Program System Preparation is SQL Server Provision for executing management and other work, including system data sheet updates, and retrieving information from the system data sheet. The system preparation is installed with SQL Server, with the name of SP_ (System Prepaid Procedure) or XP_ (extended pre-store). These pre-deposited programs are stored in the Master database, and the permissions belong to the system administrator. However, many programs can be executed from the user-reserved database to retrieve information from the system data sheet in a particular database. When you perform a system pre-deployment, it is a job in the current database system data sheet.
Relevant information about this type of program, please refer to "Extended Pre-Deprecation Procedure" topic in Linear Book.
In SQL Server 7, many system preparations have been added, and now they can also be utilized in SQL Server 2000. Table 13-1 lists some system pre-depreciation procedures that may be helpful. Table 13-1 Procedures Introducesd ....
System Prepaid Procedure Description Sp_cycle_errorlog Close current error log file, rename ERRORLOG.1 (and, if necessary, rename the old ErrorLog.1 as ErrorLog.2, so on), and start a new error log file. SP_HELPFILE Remove the name and properties of the file associated with the current database. Sp_helpfilegroup Recycled the name and properties of the archive group associated with the current database. SP_HELPROLE is back to the role in the current database. SP_HELP_ALERT Reports a warning information defined by the server. sp_start_job indicates that the SQL Server agent starts to perform a job.
Not only these pre-depreciation procedures provide immediate information, but they are quite useful when using your user database. For example, when performing a T-SQL instruction code and stores the output to a file, the program that provides user information will be quite help. You can store sp_helpfile, SP_HELPFILEGROUP, and sp_helpdb's execution results in a specific database. After all, it is necessary to understand files, archive groups, and database options when you need to rebuild the database. In SQL Server 7.0, the system pre-depicting list in SQL Server 2000 can be reserved, and you can find it in the topic of "TRANSACT-SQL and Enhanced Features". The system data sheet system data sheet is used to store setting information of all databases in SQL Server, and the definition of objects, users, and user access rights. Each user database has its own system data sheet, where the information is saved. Only the server-level setting information is held in the system data sheet in the Master database. You should use the system pre-depreciation program to access system data sheets instead of direct access to system data sheets. About the new SQL Server 7.0 new system data sheet list can be found in the topic of "Transact-SQL new and strengthened features" in the "online series". List some interesting new system data sheets: Backupfile This data sheet is stored in the MSDB database. It records information about all record file backups or database files. These information include archive identification codes, file-owned archives groups, and names of entity disks or data existing in the file. RESTOREHISTORY This data sheet is stored in the MSDB database. It contains information columns corresponding to each restore job related information, whether it is the restore of the file or the restore of the database. These information include the date and time of the restore, the point of interest, and the time point of the data reply, and the type of restore. Sysfiles This information list is a virtual data sheet, which is not directly updated or modified. It contains information about each database file, such as entity devices and logical file names, file size and maximum file size, and the size of the database (if the database grows).
Note Please use the system pre-depreciation program to access system data sheets, and system pre-depreciation provides a layer of isolation to ensure that you don't fade with unremnible materials. If you have manually access system data sheets, there may be risks for changing important system information.
Function SQL Server built-in functions provide a quick, simple way to complete certain work. SQL Server 7.0 has added several useful functions and is also included in SQL Server 2000. Learn which functions are available, the design work of the SQL Server application can become slightly simpler. The topics of "Transact-SQL New and Reinforced Features" in "Online Book" can find a complete list of new functions. This will only introduce some functions that you may have a very helpful function: NewID builds a UniqueIdentifier data type, which is the unique identification item (GUID). You need to use this function to assign values to this data-type data line, how to use newID (). (This function does not need quota) Year transferred complete integrity of the designated date part. The way is Year (date). For example: The following statements Select Year ('07 / 11/01 ') will pass the value of 2001. MONTH passed the integer of the specified date month. Use mode for Month (date). For example: The following statement Select Month will pass the value of 7. DAY is transmitted back to the integer of the specified date date. Use how the use is day (date). For example: The following statement select day ('07 / 11/01 ') will pass the value 11.
File_name Removes the logical file name of the file identification code (ID). Use the way to file_name (file_id_number). For example: statement select file_name (4) file logical name of 4 file identification code is 4. If you can't find a file with this identification code in the database, you will pass back NULL. Data types have added several new data patterns in SQL Server 7, and some of some of the data sizes are also extended. In addition, SQL Server 2000 has added three types of data, and most of these materials have been discussed in Chapter 10. Only SQL Server 7 has changed here, and also included in SQL Server 2000: The data indicator variable adds a CURSOR data type. For information on indicators, please refer to "Cursors" in "Cursors" in the "Cursors". Newly added three new Unicode data types - Nchar, NVARCHAR, and NTEXT. Each Unicode character uses two bit yuan groups and supports all international characters. A UNIQUEIDENTIFIER data is added to store unique unique identification code (GUID). The maximum length of the character data and the binary string extends to 8,000 bit groups. This length is suitable for CHAR, VARCHAR, BINARY, and VARBINARY. SQL Server 2000 adds the following data type: Bigint stores an integer of 8 bits tuple lengths. SQL_VARIANT allows different data types to be stored in the same information. This type of data is stored in the value of the data itself and described - its basic type, decimal number, accuracy, maximum size, and collation. Table mode of operation is similar to the temporary information form; its announcement contains the information line list and data type. This data type can be used to define a zone variable or a transmission value of a user's custom function. The statement of SQL Server 7.0 includes many new T-SQL statements and new options that already present. Similarly, these are also kept in S QL 2000. These statements correspond to some new features of SQL Server 7.0. For example, ALTER DATABASE statements include the following new options for archives and archives: Modify File, Add FileGroup, Modify FileGroup, Remove File, and Remove FileGroup. In particular, the archive group, the new DBCC CheckfileGroup statement checks the integrity of all the information sheets in the archive group. SQL Server 7.0 and SQL Server 2000 include two additional DBCC statements, DBCC ShrinkFile and DBCC ShrinkDatabase. The former can reduce the size of the data file, the latter can reduce the data files of the database and release unused disk space. SQL Server 7 supports an improved backup and reducing structure with SQL Server 2000. The new Backup statement allows all or part of the library backup and record file backup. The new RESTORE statement allows you to restore all or part of the database backup and record file backup. These replace Dump and LOAD statements in SQL Server earlier. For a complete list of SQL Server 7.0 and SQL Server 2000, please see the topic of "Transact-SQL New and Strengthening Functions" in "Online Book".
How to use T-SQL in addition to using T-SQL to design your application (this topic has exceeded the discussion scope of this book), you can use the following three SQL Server tools -ISQL, OSQL or Query Analyzer one of them. T-SQL statement, or establish a T-SQL instruction code. ISQL public program ISQL public program and SQL Server are communicated between DB-Library, which allows you to perform T-SQL statements, pre-deposit programs and instruction codes. Since DB-Library retains the function of SQL Server 6.5, the ISQL application may not support some new features of SQL Server 2000. For example, ISQL cannot retrieve the Unicode NText data type. OSQL utility OSQL utilities is new and reserved to SQL Server 2000 in SQL Server 7.0, mainly to replace ISQL. These two public programs are basically not very different, but OSQL is communicated with SQL Server, not DB-Library, and OSQL support SQL Server 2000. . OSQL is similar to other features of ISQL, and SQL Server 2000 supports both, but you should use OSQL to replace ISQL to avoid those issues mentioned before. To perform OSQL from a command prompt character window (MS-DOS prompt window), you can use the following appropriate parameters to perform OSQL.exe program: OSQL -U UserName -P Password -s ServerName
When the OSQL is connected to the SQL Server, the following digital prompt character appears:
1>
Type T-SQL statement under this prompt element,
1> sp_helpdb master
2> Go
This statement will list information about the Master database. The keyword Go is not a T-SQL statement, which is an ISQL, OSQL, and Query Analyzer that can identify commands, mainly used as a T-SQL statement end tag (considered as a batch operation). The results like this interactive query will be displayed in the command prompt pen. If you enter an error in OSQL, you can use the RESET this osql command to return to 1> prompt character,
1> sp_helpbd
2> RESET
1> sp_helpdb
2> Go
To stop OSQL, enter Quit or Exit. You can also press Ctrl C at the same time at the same time, and end a command or interrupt query action without exiting the OSQL utility. OSQL utilities can identify non-A, -P and -S. For the parameters of the OSQL utilities, the information available to OSQL, please refer to the OSQL Utility topic on the online guide. Query Analyzer Query Analyzer allows you to perform T-SQL state or instruction codes in the GUI interface and output the result to the window. You can also use this tool to perform some indexes and query analysis. Some people prefer to use Query Analyzer to perform statements rather than MS-DOS prompt character window. To perform Query Analyzer, you can follow these steps:
Start Query Analyzer with one of the following three methods:
Enter "IsqLW" after the command prompt the character. Enable Enterprise Manager, select SQL Query Analyzer in the Tool Function. Wire from the start / program / Microsoft SQL Server / Query Analyzer (unless you have connected to the server), as shown in Figure 13-1. Figure 13-1 Wire to SQL Server dialogue block
Select the server name you want to connect from the SQL Server drop-down menu. Enter your login information, if SQL Server is currently not started, you want it to start the service, check the check box under the option. Press to determine. SQL Query Analyzer will appear, as shown in Figure 13-2.
Figure 13-2 SQL Query Analyzer
Enter any T-SQL statement or call pre-save program in the query window, as shown in Figure 13-3. Note that the query window has been put on, accounting for the entire SQL Query Analyzer window.
Figure 13-3 Calling a pre-store in query window in Query Analyzer
To perform this statement, press the execution query button on the tool column (green point to the right triangle arrow) or press Ctrl E at the same time. The results will be displayed in the result pane, as shown in Figure 13-4.
Figure 13-4 Query results displayed in Query Analyzer
To load and perform the T-SQL instruction code you pre-established in Query Analyzer, press the tool column to load the SQL command code button (yellow data clip symbol), or browse from the file / open old files and find out Your file. The instruction code will be displayed on the pane above. Press to execute the query button to execute the instruction code.
Note SQL Query Analyzer has many other options, including some of the new features of SQL Server 2000, see Chapter 35 for more detailed instructions.
T-SQL Instruction Code When you need to execute some T-SQL statements or pre-depreciation programs multiple times, build your own instruction code is a quite good way. The instruction code (Script) is a simple file containing T-SQL to execute, which can be a statement or a series of continuous statements. It is recommended that when you establish a T-SQL instruction code, the file sub-file name is set to .sql, so you can identify them quickly. The following program code is a simple instruction code example. This instruction code calls several system preparations that can output all kinds of information about the MYDB database, file group, and data table (Customer_Data and Product_INFO). We assume that this instruction code has been pre-stored in the file name mY_db.sql.
Use mydb
Go
sp_helpdb mydb
Go
SP_HELPFILEGROUP
Go
sp_spaceused custom000_data
Go
sp_spaceused product_info
Go
To perform this command code through the command, you can use the OSQL -i option and the -o option. When the -i option fills the command code file name you want to enter, fill in the execution result after which the execution result is intended to output to which file name - this example, the file name is MYDB_INFO.OUT. We can also use the -e option to make the original T-SQL state display in the output file, making the results clearer. For example, the above-described instruction code is performed as a system administrator, and then enter the following code after the command prompt character:
Osql -usa -p -i mydb_info.sql -o mydb_info.out -e
Check the output file to determine if the instruction code is as appropriate as you expect. In this way, the T-SQL execution file can be stored in the output file to facilitate later viewing. Especially when you want to compare the output results executed by the instruction code before you change the output of the instruction code, this method can meet your expectations. In addition, the instruction code is also very helpful when you need some statement. Another way to perform the instruction code without having to enter a bunch of OSQL commands each time, using the osql command to create one. CMD file. In this case, we can name a MyDB_Info.cmd file, which contains the OSQL command we just typed. If you enter the file with the output and the .cmd profile is not in the same directory, you have to make sure that they have pointed out their path. Now you can execute myDB_info.cmd directly under the command prompt or press two MYDB_INFO.CMD file names in Microsoft Windows Explorer. You can also execute the T-SQL instruction code in Query Analyzer. To perform our instruction size MY_DBINFO.SQL, select the file from the file function to open the old file, browse and select intend to execute. Press the program code of the instruction code will appear above the pane. Press to execute the query button or press Ctrl E to perform these statements. Each statement will appear in order, as shown in Figure 13-5. Note the two two result data tables in the uppermost end, respectively from the first two sp_helpdb pre-deposit programs in the instruction code. Figure 13-5 Execution Results in Query Analyzer in Query Analyzer
This chapter concludes that in this chapter, you have learned the basic concepts of SQL and T-SQL, and some simple examples of DDL and DML statements have also been seen. We also introduce different methods of implementing T-SQL statements - including ISQL, OSQL, SQL Query Analyzer, and T-SQL instruction codes. In the next chapter and in Chapter 25, there will be more related messages using T-SQL.
