5.2 Transact-SQL foundation
5.2.2 Data Inquiry
1. Query all columns in the table
For example, to query all books in the table book, enter the following command in the SQL query analyzer:
SELECT * AWOK
2. The column specified in the query table
For example, to query all books and prices, you can enter the following SQL statement:
Select Book_name, Price from BOOK
The order of rearrangement of columns can be rearranged, and the order in the column name after SELECT determines the order in the display result. If you want to put the price in front, the above SQL statement should be written:
Select Price, Book_name from Book
3. Add a string using single quotes
For example, to query all the names and prices of all books, and display the string "Price:" in the price, you can enter the following SQL statement:
SELECT book_name, 'Price:', Price from Book
4. Alias
For example, query all the names and prices of all books, and display "book name" and "price" words in the title column, rather than display Book_name and Price. You can enter the following SQL statement:
SELECT book_name as book name, Price as price from book
Or: SELECT book_name Title, Price from Price Price from Book
Or: select 'book name' = BOOK_NAME, 'Price' = Price from BOOK
5. Query specific records
For example, you want to query the information of the "Windows 2000 Network Management" book, you can enter the following SQL statement:
Select * from book where book_name = 'windows 2000 network management'
6. Sort query results
For example, in accordance with the price level, display all books, enter the following SQL statement:
Select * from book ORDER by Price DESC
7. Multi-table query
Enter the following SQL statement:
SELECT book.book_name, authors.author_name
From book, authors
Where book.author_id = authors.author_id
8. Eliminate repetitive rows
For example, query publishers belonging to all books. Enter the SQL statement as follows:
Select Distinct Publisher from Book
5.2.3 Data Insert and Delete
Insert a record in the table authors, add an author. Enter the SQL statement as follows:
INSERT Authors (Author_ID, Author_Name) Values (3, 'Zhang Yingku))
A record is added. Use the SELECT statement to query the Authors table, you can see new records. Enter the following SQL statement:
Select * from authors
For example, delete the record of "Windows 2000 Professional Views" books in the Book table, you can enter the following SQL statement:
Delete book
Where book_name = 'windows 2000 professional view speed
The following example is to delete all the data in the Authors table:
Truncate Table Authors
5.2.4 Data Modification For example, the author of the Authors table is "Wang Xiaoming" to "Wang Xiaoming", the SQL statement is as follows:
Update authors set author_name = 'Wang Xiaoming'
WHERE author_name = 'Wang Xiaoming'
5.2.5 Use functions
For example, in the ORDERFORM table, submit a order, you can use the getDate () function when inserting data, you can use the getDate () function to get the current date. In order to maintain data integrity, first insert a customer in the Clients table, then submit a order in the OrderForm table. Enter the following SQL statement:
Insert Clients Values (1, 'Liu Ming Yao ",' Beijing Haidian District ')
Insert Orderform Values (1,2,50, getdate (), 1)
Press the F5 key and use the SELECT statement to view the execution result:
Select * from Orderform
5.2.6 Using the formula
For example, to query the title, quantity, and total amount in all orders. At this time, the formula can be used to calculate the total amount. The input SQL statement is as follows:
Select book.book_name, Orderform.Book_Number,
'Total: (Book.Price * Orderform.book_number)
From ORDERFORM, BOOK
Where orderform.book_id = book.book_id
5.2.7 Operating statement of the database
5.2.7.1 Creating a database
For example, establish a database called Test, you can enter the following SQL statement:
Create Database Test
For example, to create a sales database and set the data file to D: / sales. LDF, size is 5MB, up to 20MB, each time grows 5MB. The transaction log file is D: / sale. LDF, size is 5MB, up to 10MB, each time grows to 1MB. Then the created SQL statement is:
Create Database Sales Database
ON (
Name = Sales data,
Filename = 'D: / Program Files / Microsoft SQL Server / MSSQL / DATA / Sales Data. MDF',
Size = 10MB,
MaxSize = 50MB,
FileGrowth = 10MB
)
LOG ON
Name = Sales data log,
FileName = 'D: / Program Files / Microsoft SQL Server / MSSQL / DATA / Sales Data Log. LDF',
Size = 10MB,
MaxSize = 20MB,
Filegrowth = 5MB
)
5.2.7.2 Modify the database
For example, a data file for a logical name "Sales Data 2" is added to the sales database, and its maximum is 10MB and 50MB, respectively. The input SQL statement is:
Alter Database Sales Database
Add file
Name = Sales Data 2,
FileName = 'D: / Program Files / Microsoft SQL Server / MSSQL / DATA / Sales Data 2.mdf',
Size = 10MB,
MaxSize = 50MB,
Filegrowth = 5MB
)
5.2.7.3 Using and deleting a database
For example, you can use the following SQL statement to delete a sales database: DROP Database sales database
5.2.8 Operating statement
5.2.8.1 Creation of the table
1. Basic usage
Create a clients table in the Test database, the SQL statement is as follows:
Use test
CREATE TABLE Clients
Client_id Int,
Client_name char (8),
Address Char (50)
)
2. Segment property parameters
The following SQL statement is to create a BOOK table in the Test database and specify the book_id as the primary key, and Book_name is non-empty:
CREATE TABLE BOOK
Book_id Int Not Null Primary Key,
Book_name char (8) Not null,
Author_id char (50)
)
3. Establish association with other tables
For example, you can associate the author_id field in the Book table to the Author_ID field of the Authors table. Remove the Book table created above in the Enterprise Manager, then execute the following statement:
Create Table Authors
Author_id Int Not Null Primary Key,
Author_name char (8) Not null,
Address Char (50) NULL
)
CREATE TABLE BOOK
Book_id Int Not Null Primary Key,
Book_name char (8) Not null,
Author_id int forward key reasoning authors (author_id)
)
5.2.8.2 Modify Table
For example, add a "introduction" field to the Book table in the TEST database:
ALTER TABLE BOOK ADD Profile Text
5.2.8.3 Delete associations and tables
For example, to delete a table book, you can perform the following SQL statement:
Drop Table Book
5.3 Transact-SQL programming foundation
5.3.1 identifier
5.3.1.1 General Identifier
E.g:
Select * from TableX Where keycol = 124
5.3.1.2 Separation identifier
For example, the SET statement that opens and closes this option is as follows:
Set quoted_identifier on
Set quoted_identifier off
5.3.1.3 Using the identifier
For example, a user named bookadm logs in to the MyServer server and uses the book database. Create a MyTable table using the following statement:
Create Table MyTable
(
Column1 Int,
Column2 char (20)
)
5.3.2 Data Type
5.3.2.1 System Data Type
1. Integer type
For example, the following statement creates a table INT_TABLE, where the four types of integers are used separately:
Use test
CREATE TABLE INT_TABLE
(
C1 Tinyint,
C2 Smallint,
C3 Int,
C4 Bigint,
)
INSERT INT_TABLE VALUES (50, 5000, 50000, 500000)
Select * from int_table
2. Data type
For example, in the table decimal_table below, the field C1 is a Decimal data type:
CREATE TABLE DECIMAL_TABLE
(
C1 Decimal (3, 2)
)
INSERT DECIMAL_TABLE VALUES (4.5678)
Select * from decimal_table
When assigning a value for decimal value, it should be ensured that the data integer portion is less than or equal to the defined length, otherwise an overflow error will occur. For example, insert a record into a record, perform the following statement in the SQL query analyzer:
INSERT DECIMAL_TABLE VALUES (45.678)
3. Approximate numerical type
4. Character data type
For example, the following SQL statement declares local variable MyCharVar as a CHAR type, length is 25:
Declare @mycharvar char (25)
Set @mycharvar = 'this is a string'
The following is to use two single quotes to represent embedded single quotes:
Set @MYCHARVAR = 'this is a' 'string' ''
The char function can convert an integer to an ASCII character. This is useful when determining control characters (such as carriage return or wrap). A new line is generated with char (13) and char (10) in the string and generates a new line. E.g:
Print 'First Line.' Char (13) CHAR (10) 'Second Line.'
5. Logical data type
For example, the following is an example of using a Bit data type:
Create Table Bit_Table
(
C1 bit,
C2 bit,
C3 bit
)
INSERT BIT_TABLE VALUES (12, 1, 0)
SELECT * from Bit_Table
6. Monetary type
Here is an example of using currency data types:
CREATE TABLE MONEY_TABLE
(
C1 Money,
C2 SmallMoney
)
INSERT MONEY_TABLE VALUES ($ 12345678, $ 1234)
SELECT * from Money_Table
7. Binary data type
Below is an example of using binary data types:
CREATE TABLE BINARY_TABLE
(
C1 binary (10),
C2 Varbinary (20),
C3 image
)
INSERT BINARY_TABLE VALUES (0x123, 0xFfff, 0x14fffff)
SELECT * from binary_table
8. Date Time Type
Below is an example of using a SmallDateTime data type:
SELECT CAST ('2000-05-08 12: 35: 29.998' as smalldatetime)
The following is an example of using a datetime data type:
Create Table DateTime_Table
(
C1 datetime,
C2 SmallDateTime,
)
INSERT dateTime_Table Values ('2001-05-15 00: 04: 39.257', '04/15/1996 14:30:20 PM')
SELECT * from datetime_table
9. Unicode characters
When using the Unicode characters, you should add an identifier n in front, but the identifier is not stored when it is stored. E.g:
Declare @myunicodevar nchar (25)
Set @myunicodevar = n'this is a unicode string.'print @myunicodevar
10. Other data types
The following example assigns a variable declared as a UniqueIdentifier data type using a NewID and print it out:
Declare @myid uniqueIndifier
Set @myid = newid ()
Print 'Value of @myid IS:' Convert (varchar (255), @myid)
5.3.2.2 User Defines Data Types
2. Use stored procedures
The following example is a domestic and international telephone and fax number to create two user-defined data types Televhone and Fax:
EXEC SP_ADDTYPE TELEPHONE, 'VARCHAR (24)', 'NOT NULL'
EXEC SP_ADDTYPE FAX, 'VARCHAR (24)', 'NULL'
5.3.3 Operator
2. Assignment operator
For example, the following SQL statement declares a variable and then assigns the result of a sample operation to the variable, and finally the value of the variable is printed:
Declare @mycounter Int
Set @mycounter = 17% 3
Print Convert (VARCHAR (255), @mycounter)
You can also use the assignment operator to establish a relationship between column headers and expressions for column definition values. For example, the following SQL statement is displayed by "book" book_id in the BookDB database:
Use bookdb
SELECT book_id = 'book', Book_Name, Price from BOOK
3. Bit operator
For example, the SQL statement below performs two variables:
Declare @a int, @ b int
SET @a = 5
Set @B = 10
Select @ a & @ b, @ a | @ b, @ a ^ @ b
4. Compare operator
The following SQL statement query book information greater than 35.0 in the Book table:
Use bookdb
Select * from Book Where Price> 35.0
5. Logical Operators
For example, the following SQL statement queries the book name in the Book table contains "network management", and the price of books between 20 and 50:
SELECT * AWOK
WHERE (Book_name Like '% Network Management%') and (Price Between 20 and 50)
6. String connection operator
For example, the following SQL statement connects two strings together:
SELECT ('ABC' 'DEF')
7. One yuan operator
For example, the following SQL statement first declares a variable and assigns a variable, then handles the variable:
DECLARE @ Num1 INT
Set @ Num1 = 5
SELECT - @num1
8. Operator priority
For example, in the example below, in the expression used in the SET statement, the minuscar operator is obtained before the plus operator:
Declare @MYNUMBER INTSET @MYNumber = 6 - 5 7
SELECT @MYNUMBER
E.g:
Declare @MYNumber Int
Set @MYNumber = 3 * (5 (7 - 3)))
SELECT @MYNUMBER
5.1.4 variable
1. Local variable
In SQL Server, multiple variables can be defined at a time. E.g:
Declare @maxprice float, @ pub char (12)
For example, the two variables are first defined below, and the SET and SELECT are used to assign values, and then the two variable query prices are less than 50, and the publishing agency is "Ming Yao Studio" book information:
Declare @maxprice float, @ pub char (12)
Set @Maxprice = 50
SELECT @ Pub = 'Ming Yao Studio'
Select * from book where price <@maxprice and public @ @maxprice and publisher = @pub
5.3.5 batches
The following SQL statement creates a view. Because the Create View must be a unique statement in the batch, you need a Go command to isolate the CREATE VIEW statement with the USE and SELECT statements around them:
Use bookdb
Go
Create View Auth_titles As SELECT * AUTHORS GO
Select * from auth_titles Go 5.3.6 Note The following is an example of using a comment: Use bookdb Go - This is a double-linked character note Select * from book - Query book information in the table book, Go / * This is a front slash - asterisk Note * / select * from authors / * Query Author information * / GO 5.3.7 Control Story 5.3.7.1 Begin ... End Statement Begin ... End statement can be used nested. For example: begin declare @myvar float set @myvar = 456.256 begin print 'variable @MYVAR value:' print cast (@myvar as varchar (12)) end end 5.3.7.2 if ... ELSE statement if ... Else The statement is performed by: If the value of the Boolean expression is true, the statement block behind the IF is executed; otherwise the statement block behind the ELSE is performed.
For example: Use bookdbiff (select price from book where book_name limited "> 50 begin print" This book is too expensive! 'Print' I can't afford! 'End else begin print' This book is OK! 'Print' I want to buy a copy! 'End 5.3.7.3 CASE statement 1. Simple case format, for example: Use Pubs Go SELECT AU_FNAME, AU_LNAME, CASE STATE WHEN 'CA' THEN 'CALIFORNIA' WHEN 'KS' TEN 'KANSAS' WHEN 'TN' TEN 'TENNESSEE' WHEN 'or' TEN 'OREGON' WHEN 'MI 'TEN' MICHIGAN 'WHEN' IN 'THEN' INDIANA 'WHEN' MD 'TEN' MARYLAND 'WHEN' TEN 'TEN' UTAH 'End As Statename from Pubs.dbo.authors Where Au_FName Like' M% '2. Search Case format, for example: use bookdb go select book_name, Case when price> = 50 TEN 'is too expensive! 'Hen price> = 40 TEN 'is OK, consider! 'Else' is very cheap, buy a 'end as price from book to go 5.3.7.4 while statement, for example, the following SQL statement is calculated from 1 to 100: Declare @MyResult int, @ myvar int set @myvar = 0 set @myresult = 0 while @MYVAR <= 100 begin set @myresult = @ MyResult @ myvar set @ myvar = @ Myvar 1 End print Cast (@MyResult As char (25)) 5.3.7.5 goto statement, for example, using the following The statement recalculates from 1 to 100 value: declare @MyResult int, @ myvar int set @myvar = 0 set @myresult = 0 my_loop: - Defined Number Set @myResult = @ MyResult @ myvar set @ myvar = @ myvar 1 if @MYVAR <= 100 goto my_loop - If you are less than 100, jump to the MY_LOOP label That PRINT CAST (@MyResult As Char (25)) 5.3.7.6 Return statement, for example, first perform the following SQL statement to create a stored procedure : Use bookdb go create proc mypro @
bookname char (50) - Create a stored procedure MyPro AS IF (SELECT price FROM book WHERE book_name LIKE @bookname)> = 50 RETURN 1 ELSE RETURN 2 and execute the following SQL statement: DECLARE @Return_value int EXEC @ Return_value = MyPro '% Network management% 'if @ Return_Value = 1 Print' This book is too expensive! 'Else Print' This book is OK, it is worth considering purchase! 'Go 5.3.7.7 WaitFor statement, for example, the following SQL statement specifies a statement at 1:58: Begin Waitfor Time' 1:58:00 'Print' is now a 1:58:00 'end 5.3.8 function 5.3. 8.1 Built-in functions For example, the following is an example of using a built-in function: Print 'The current date and time is:' cast (getDate () AS Char (50)) 5.3.8.2 User-defined functions, for example: The following SQL statement is in the Test Database A CubicVolume user-defined function is defined, and then uses this function to calculate the volume of a rectangular square: Use test go create function cubicvolume - input parameters (@cubength decimal (4, 1), @cubewidth decimal (4, 1), @cubeheight Decimal (4, 1)) Returns Decimal (12, 3) - Returns the volume of cube as begin Return (@cubength * @cubewidth * @cubeHeight) End Go Print Cast (DBO.Cubicvolume (20, 10) as char (255)) GO example: USE bookdb GO CREATE FUNCTION SearchBook (@BookPrice float) RETURNS @BookInfo TABLE (book_name char (50), author_name char (8), book_price float, publisher char (50)) AS BEGIN INSERT @BookInfo SELECT book.book_name, authors.author_name, book.price, book.publisher FROM book, authors WHERE book.author_id = authors.author_id AND book.price> @BookPrice RETURN END GO following SQL query statement even if the function of price is greater than 35 Book information: SELECT * from Searchbook (35) 5.4 Split Reference Answer: 2. This is a conditional multi-table query. You can use the following statement: Use bookdb go select book_name as book name, Author_name AS author, Price as price, Publisher AS Press, from book, Authors where Price> 35 3. You can use the SQL statement below: Use bookdb go select book_name as book name, (Price * 0.9) AS discount price from book
Chapter 7 SQL Advanced Use 7.1 SELECT Advanced Query 7.1.1 Data Summary 7.1.1 Aggregate Functions For example, the following SQL statement is used to query the address of several authors in the Authors table: Use bookdb Go Select Count (address) from authors GO Execution: 2 The following SQL statement is the maximum price of the book in the book in the Book table: Use BookDb Go Select Max (Price) from book Go SELECT MAX (Price) from book Go SELO execution result is: 45.0 7.1.1.2 Group By clause, for example, below The SQL statement is first inserted into a few records in Table Clients, Table Book, Table ORDERFORM, and then use the Group By clause to summarize the total number of books ordered by each customer: Use bookdb go - Insert two customers in the Clients table Insert Clients Values 2, 'Science and Technology Bookstore', 'Beijing Chaoyang District') Insert Clients Values (3, 'Mingtian Bookstore', 'Beijing Xicheng District') --go - Insert 4 books in the Book Table Insert Book Values (5, 'AutoCAD 2000 Chinese User Guide', 2,25.0, '21 Century Press') Insert Book Values (6, 'Office 2000 Chinese User Guide', 2, 28.0, 'Tomorrow Publishing House') INSERT BOOK VALUES (7, 'Windows 2000 Professional Chinese User Guide ", 1,30.0,' Dongdong Publishing House ') Insert Book Values (8,' Linux Usage Guide ', 3, 32.0,' Tang Tang Publishing House ') Go - - Insert 6 records in the OrderForm table record INSERT ORDERFORM VALUES (2, 6, 10, getdate (), 2) Insert Orderform Values (3, 5, 10, getdate (), 3) Insert Orderform Values (4, 3, 25 , GetDate (), 1) Insert Orderform Values (5,8,15, getdate (), 1) Insert Orderform Values (6, 4, 30, getdate (), 3) Insert Orderform Values (7, 7, 40, getdate (),2) GO SELECT clients.client_name, SUM (orderform.book_number) AS total number of books FROM clients, field orderform WHERE clients.client_id = orderform.client_id GROUP BY clients.client_name GO CUBE parameters have retrieved all types of data do rollup operators. Examples For example, the following SQL statement is to use the CUBE on aggregate: SELECT clients.client_name, book.book_name, SUM (orderform.book_number) AS total number of books FROM clients, orderform, book WHERE clients.client_id = orderform.client_id AND book.book_id = Orderform.book_id group by clients.client_name, book.book_name with cube rollup parameters make aggregate operations based on Group By.
If you want to retrieve the total amount of various books or all books to be ordered, you can perform the following SQL statement: from clients, orderform, book where client.client_id = Orderform.Client_ID and book.book_id = Orderform.Book_ID Group BY clients.client_name, book.book_name with rollup 7.1.1.3 Having clause, for example, the following SQL statement is used to query the number of customer names and book titles for the order of 40 this book: select clients.client_name, book.book_name, SUM orderform.book_number) AS total number of books FROM clients, orderform, book WHERE clients.client_id = orderform.client_id AND book.book_id = orderform.book_id GROUP BY clients.client_name, book.book_name HAVING SUM (orderform.book_number)> = 40 7.1. examples 1.4 COMPUTE and COMPUTE BY clauses example, the following is used COMPUTE clause: SELECT clients.client_name, book.book_name, orderform.book_number FROM clients, orderform, book WHERE clients.client_id = orderform.client_id aND book.book_id = orderform .book_id compute sum (ORDERFORM.BOOK_NUMBER) If you need the number of purchases of each customer, instead of the total order quantity, you can perform the following SQL statement: from clients, orderform, book where client.client_id = Orderform.Client_ID and book.book_id = Orderform.book_id Order by Clients.client_name Compute Sum (ORDERFORM.BOOK_NUMBER) by Clients. client_name 7.1.2 join query for example, use the following join query the name of the book and its author: USE bookdb GO SELECT book_name, author_name FROM book JOIN authors ON (book.author_id = authors.author_id) GO can not be directly on ntext, text or image columns Joint table. However, you can use SubString to connect to the NTEXT, TEXT or Image column. For example: Select * from T1 Join T2 On Substring (T1.TextColumn, 1, 20) = Substring (T2.TextColumn, 1, 20) Two tables in the top 20 characters in each text in T1 and T2 Join. In addition, another method of comparing NText or Text columns in two tables is to compare the length of the column with the WHERE clause.
For example: WHERE DATALENGTH (P1.PR_INFO) = DATALENGTH (P2.PR_INFO) 7.1.2.1 Inner Jous Internal Connection Using Inner Join Keywords, the above query name and its author's example is an example of an internal connection, or it can be followed Query: Use bookdb go select book_name, author_name from book inner join authors on (book.author_id = authors.author_id) GO 7.1.2.2 Outline 1. Left outward jointly, for example, the following SQL statement first deletes orders (order for the "Windows 2000 Professional Chinese User Guide), then query the book is not ordered: use bookdb go delete Orderform where order_id = 7 Go Select ORDERFORM.Order_date, book.book_name, Orderform.book_number from book ing .book .book_id = orderform.book_id) Go 2. Right subjunction is actually the same, the right outward joint and left-outwardly coupled functions are the same, for example, in the example above, switches the Book table and the OrderForm table in the exemplary, then use Right Outer Join : uSE bookdb GO SELECT orderform.order_date, book.book_name, orderform.book_number FROM orderform RIGHT OUTER jOIN book ON (book.book_id = orderform.book_id) GO 7.1.2.3 e.g. cross-link, the following SQL statement is generated using cross-coupled clients and On the possible combinations: uSE bookdb GO SELECT clients.client_name, authors.author_name FROM clients CROSS JOIN authors GO 7.1.3 subquery example, the following SQL statement using subqueries to query author "Windows 2000 network management," a book: uSE bookdb GO SELECT author_name FROM authors WHERE author_id = (SELECT author_id FROM book WHERE book_name = 'Windows 2000 network management') GO using the following connection mode can perform this function: uSE bookdb GO SELECT author_name FROM authors jOIN book ON (book.author_id = authors.author_id) Where book_name = 'Windows 2000 Network Management' GO 7.1.3.2 Sub Query Type 1. Using IN or NOT IN, for example, the following SQL statement query the name and publishing agency that has been ordered: Use bookdb go select book_name, Publisher from book where book_id in (select book_id from orderform) GO If you want to query the book that is not ordered You can use NOT IN:
Use bookdb go select book_name, Publisher from book where book_id not in (select book_id from orderform) Go 2. The subqueries in the Update, DELETE, and INSERT statements can nested in Update, DELETE, and INSERT statements and SELECT statements.
For example, remove book information that is not ordered: Use bookdb Go Delete Book Where book_id not in (select book_id from orderform) Go Select * from Book Go 3. The sub-query of the comparison operator, for example, find a book that is greater than the average price: Use book_name from book ing,, for example, the following return price one book Title: Use bookdb go select book.book_name from book where price> = all (Select Price from Book) Go 4. Existence inspection, for example, to query all ordered book names and their corresponding publishers, you can use the following SQL statement: use bookdb go select book_name, Publisher from book where exists (Select * from Orderform) GO 7.1.3.3 nested for example, the following use of multiple levels of nested sub-query for customer books the name of "science and technology bookstore" as ordered: uSE bookdb GO SELECT book_name FROM book WHERE book_id iN (SELECT book_id FROM orderform WHERE client_id = (SELECT client_id FROM clients WHERE client_name = 'Technology Bookstore')) GO 7.1.3.4 Related Son Query For example, the following uses the related sub-query query named "Liu Yaoru" Author's Bibliography: Use BookDb Go Select Book_name from Book Where 'Liu Yao Ruo' in (Select Author_name from Authors where authors.author_id = book.author_id) GO 7.1.4 Using the Union operator combination multiple results For example, you want to query all authors and customers, you can use the following SQL statement: use bookdb go select author_id, author_name from Outhor_ID, Author_name from Authors Union Select Client_ID, Client_name From Clients Go 7.1.5 Create a new table based on the query, for example, the following SQL statement plus the book name and the author get the query into the new table Author_book: Use bookdb go select book.book_name, Authors.author_name INTO Author_book from authors, book where authors.author_id = book.author_id GO execution result can be viewed by the following SELECT statement: SELECT * AUTHOR_BOOK 7.2 Error handling 7.2.1 Using @@ Error global variable processing error, for example, The statement is an example of using @@ Error: US E bookdb go select * from book go if @@ error = 0 print 'Execution! 'GO 7.2.2 uses raiserror, for example,
The following example replaces the value of the DB_ID and DB_NAME functions in the message returned to the application: declare @dbid int set @dbid = db_id () declare @dbname nvarchar (128) set @dbname = db_name ()
RAISERROR ('The Current Database ID IS:% D, The Database Name IS:% s.', 16, 1, @dbid, @dbname) and the following example uses the same processing by user-defined messages: EXEC SP_ADDMESSAGE 50005 , 16, 'The Current Database ID IS:% D, The Database Name IS:% s.', 'US_ENGLISH' Go Declare @dbid Int set @dbid = db_id ()
Declare @dbname nvarchar (128) set @dbname = db_name ()
Raiserror (50005, 16, 1, @dbid, @dbname) GO 7.3 Manage NTEXT, TEXT, and Image Data For example, the following SQL statement creates a text1 table in the TEST database, where the data type of the C2 field is text, and inserted into one Pen Record: Use test go create Table Text1 (C1 INT, C2 TEXT) EXEC SP_TABLEOPTION 'TEXT1', 'TEXT IN ROW', 'On' INSERT TEXT1 VALUES ('1', 'this Is A Text.') Go is then executed The following SQL statement: select * from text1 7.3.1 Retrieve NTEXT, TEXT, or IMAGE value 1. Quote this column in the SELECT statement, for example, the following statement can be used to change TextSIZE to 64512: SET TEXTSIZE 64512 If you want to change to the default, you can use the following SQL statement: Set Textsize 0 2. Use the TextPtr function to get the text pointer that passes to the ReadText statement, for example, the first to 7th character of the C2 field of the Text1 table: - TEXT IN ROW option should be used before using a pointer to the TEXT data type. Close Exec sp_tableoption 'Text1', 'Text in Row', 'Off' Declare @ptrval Varbinary (16) SELECT @Ptrval = Textptr (C2) from text1 readtext text1.c2 @ptrval 0 7 3. Using the SubString function can retrieve the data block starting from the beginning of a specific offset location, for example, you can use the following SQL statement to retrieve the first to 7th characters of the C2 field of the TEXT1 table: SELECT SUBSTRING (C2, 1, 7) AS C2 from Text1 3. Using the PATINDEX function can retrieve some of the offset of some specific byte combinations, for example, the following SQL statement retrieves character a in the first group of C2 fields: Use test go select Patindex ('% a% a%', c2) AS start position from FROM TEXT1 GO 7.3.2 Modify NTEXT, TEXT or Image Value 2. Using the WRITETEXT statement to override the entire data value of the column, for example, the SQL statement below modifies the string value of the TEXT1 table in the TEST database: use test go declare @ptrval varbinary (16) Select @ptrval = textptr (C2) from text1 WriteText TEXT1.C2 @ptrval 'this is a modified text.' Go SELECT *WM text1 Go 3. Update NTEXT, TEXT, or Image Column, for example, the following SQL statement adds the following SQL statement at the end of the C2 field of the Text1 table. THIS an INSERTED TEXT .: Use test go declare @ptrval varbinary (16) SELECT @ Ptrval = Textptr (C2) from text1 Updatetext text1.c2 @ptrval null 0 'this is an inserted text.'
Go Select * from TEXT1 GO 7.4 Transaction 7.3.2 Explicit Transaction 4. Set saving points in the transaction, for example: use bookdb go begin Tran MyTran - Starting Transaction Insert Into Book Values (9, 'Windows 2000 Professional View Figure Speed ", 1, 35,' 21st Century Press') - Insert a Pen Record Save TRAN MYSAVE - Save Points
DELETE BOOK Where Book_ID = 9 - Delete Record
Rollback Tran Mysave - Rolling Transaction Commit Tran Go Select * from book - Query the Book table record GO 7.3.3 Automated Submit Transactions In the example below, due to compilation errors, any Insert statement in the third batch No execution (not returned). But it seems that the first two INSERT statements did not perform rollback: Use test Go Create Table Testbatch (Cola Int Primary Key, Colb Char (3)) Go Insert Into Testbatch Values (1, 'AAA') Insert Into Testbatch Values (2, 'BBB') Insert Into Testbatch Valuse (3, 'CCC') / * Symbol Error * / Go Select * from testbatch / * Will not return any result * / Go 7.3.4 Uver transaction SQL The statement demonstrates explicitly or implicitly launched the transaction when setting the implicit_transactions set to ON. It uses @@ TRANCOUNT functions to demonstrate open transactions and close transactions: use test go
SET NOCOUNT ON - Does not display the affected row Create Table T1 (a int) Go Insert Into T1 Values (1) Go
Print 'uses the number of transactions in the transaction in transactions in transactions:' Cast (@@ TRANCOUNT AS CHAR (5)) COMMIT TRAN PRINT 'Outside the number of transactions:' Cast @@Trancount As Char (5)) Go
Print 'Sets Implicit_Transactions for ON' Go Set Implicit_Transactions on Go
Print 'Use implicit transaction' go - here does not need a Begin TRAN statement to define transactions Insert Into T1 Values (4) Print 'transaction number:' cast (@@ TRANCOUNT AS Char (5)) CommIT The number of transactions outside the TRAN PRINT ':' Cast (@@ TRANCOUNT AS CHAR (5)) Lock 7.5.4 Custom Lock 2. Custom Lock Time To view the value of the current Lock_Timeout, you can use the @@ Lock_timeout global variable.
For example, the following SQL statement sets the value of Lock_timeout to 1800 milliseconds and use @@ lock_timeout to display this value: set lock_timeout 1800 go declare @timeout int search @timeout = @@ Lock_timeout print @timeout Go 3. Custom transaction isolation level For example, to set the transaction isolation level to make a serial reading to ensure that the concurrent transaction cannot insert an illusion line in the Book table, you can perform the following SQL statement: Use bookdb Go set Transaction isolation level Serializable Go Begin Transaction Select * from Book Go To determine the currently set transaction isolation level, you can use the DBCC UserOptions statement, for example: Use bookdb go set transaction isolation level.. Lock prompts, for example, if the transaction isolation level is set to Serializable, and in the SELECT statement uses a table-level lock prompt NOLOCK, you can perform the following SQL statement: Use bookdb go set transaction isolation Select Book_name from Book with ( NOLOCK) Go can use the sp_lock store to view the lock: EXEC SP_LOCK GO can use the object_name function to return this locking database object (Table): SELECT Object_name (1029578706) GO 7.6 Using a Cursor 7.6.1 Cursor concept 7.6.2 Using a cursor 3. Extraction from the open cursor, for example: use bookdb go set nocount on update book set book_name = 'Linux tutorial' where book_id = 20 if @@ rowcount = 0 print 'No line is updated! 'Go 6. The cursor example below is an example of a simple cursor: / * Declaring cursor * / declare book_cursor cursor for select * from book / * Open Cursor * / open book_cursor / * Extract first row data * / fetch next from book_cursor / * Close And release the cursor * / close book_cursor deallocate book_cursor Next Using a cursor print a simple book information: Use bookdb go set nonet ON / * Print table title * / print '******** Book information table ** ********* 'Print' 'Print' ----------------------------------- ------------------------------ 'Print' | book name | Author | Price | 'Print' ------ -------------------------------------------------- --------- '/ * Declared Variables * / Declare @bookname varchar (100), @
authorname varchar (20), @bookprice varchar (40), @ pubname varchar (80) / * Declare the cursor * / DECLARE book_cursor CURSOR FOR SELECT book_name, author_name, price FROM book, authors WHERE book.author_id = authors.author_id / * Open Cursor * / Open book_cursor / * extract the first line of data * / fetch next from book_cursor @bookname, @Authorname, @bookprice
While @@ fetch_status = 0 Begin / * Print Data * / Print '|' @ BookName '|' @ Authorname '|' Cast (@bookprice as char (5)) '|' print '---- -------------------------------------------------- ----------- '/ * Extract the next line of data * / fetch next from book_cursor @bookname, @Authorname, @bookprice end
/ * Close and release the cursor * / close book_cursor deallocate book_cursor Go 7.7 Small reference Answer: 1. You can use the following SQL statements: USE bookdb GO SELECT clients.client_name, SUM (orderform.book_number * book.price) AS total amount FROM clients, orderform, book WHERE clients.client_id = orderform.client_id AND orderform.book_id = book.book_id Group by clients.client_name 2. You can use the following SQL statement: Use bookdb Go Select * from Book Where Book_id Not in (SELECT BOOK_ID from Orderform) 3. Use the Union combination two results.
Chapter 8 Regaration 8.2 Creating View 8.2.3 Creating a View Using SQL Statements For example, the following SQL statement creates a book_total view, including the number and total number of books, prices, or order: Use BookDB Go Create View DBo.book_total as SELECT DBO .book.book_name as the title, dbo.book.Price as price, dbo.orderform.book_number as quantity, dbo.book.Price * dbo.orderform.book_number as total from dbo.orderform inner join dbo.book on dbo.orderform .book_id = dbo.book.book_id GO 8.3 Using view 8.3.1 Using the view for data retrieval using the view to retrieve data, you can operate the view like a table.
For example, use the book_total view created above to query all book names of all order books and its total amount: SELECT book name, total from book_total 8.3.2 Modify data by view, for example, the following SQL statement creates a table and Based on the TEST database The view of the table, and uses the view into a record: Use test go / * If the table table1 exists, delete * / if exists (SELECT TABLES where Table_Name = 'table1') Drop Table Table1 GO / * If the view View1 exists, delete * / IF eXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'View1') DROP vIEW View1 GO / * Create table Table1 * / CREATE tABLE Table1 (column_1 int, column_2 varchar (30)) GO / * Create a view view1 * / create view view1 asselect column_2, column_1 from table1 go / * Insert a record via view * / Insert Into View1 Values ('Row 1', 1) / * View Insert Record * / SELECT * From Table1 Go 8.4 Modification 8.4.2 Renaming view, for example, the following SQL statement renames the view book_info "book information": Use bookdb go exec sp_rename 'book_info', 'book information' Go Execute the following statement You can complete the same function: Use bookdb go exec sp_rename 'book_info', 'book information', 'Object' Go 8.5 View information Query 8.5.2 Using the sp_helptext stored procedure For example, you can use the following SQL statement to view the view "book information "Definition: EXEC SP_HELPText 'Book Information' 8.6 View Delete 8.6.2 Using Transact-SQL For example, the following statement checks if there is a View1 view in the Test database, if there is, remove: use test Go / * If the view view1 exists, delete * / if exists (select Table_name from information_schema.views where table_name = 'view1') Drop View View1 GO 8.7 Skull Answer: 3. The SQL statement is as follows: CREATE VIEW DBO. Order As SELECT DBO.OrDerform.Order_date AS Order Date, DBo.book.book_name AS Title, DBo.book.Price As unit price, dbo.orderform.book_number as quantity, dbo.clients. Client_name AS Customer Name, dbo.orderform.book_number * dbo.book.Price AS Total from dbo.book inner join dbo.orderform on dbo.book_id =
DBO.Orderform.book_id inner join dbo.clients on dbo.orderform.client_id = dbo.clients.client_id
Chapter 9 Database Integrity 9.1 Constraints 9.1.1 Primary Key Constrained, for example, the SQL statement below creates a table called Student, which specifies student_number as the primary key: Use test go create table student (SUTDENT_NUMBER INT Primary Key, Student_name Char (30 )) GO 9.1.2 FOREIGN KEY constraint e.g., the following is an example of using FOREIGN KEY constraint: CREATE TABLE product (product_number int, student_number int FOREIGN KEY REFERENCES student (student_number) ON DELETE NO ACTION) GO 9.1.3 UNIQUE constraint e.g., The following SQL statement creates a TEST2 table, which specifies that the C1 field cannot contain a repetition: use test go create table test2 (c1 int unique, c2 int) Go Insert test2 values (1,100) Go If inserted, INSERT TEST2 VALUES (1,200) 9.1.4 CHECK constraints, for example, the following SQL statement creates a score table, where the check constraint is limited to 0 to 100 points: Create Table Score (SUTDENT_NUMBER INT, SCORE INT NOT NULL CHECK (Score> = 0 and score <= 100) 9.2 Default 9.2.1 Specify the default value when you create a table, now create a DateTest table in the Test database, where C2 specifies the default value of the current date: USE TEST Go Create Table DateTest (C1 INT, C2 DateTime Default) then inserts a row of data: Insert DateTest (C1) VALUES (1) Select * from DateTest, you can add the order_date field in the ORDERFORM table in the BookDB database plus Unsimiff value: Use bookdb Go alter Table ORDERFORM AD D constraint dated default getdate () for order_date 9.2.2 Creating the default object 2 using the default object 9.2.2.1. Using the CREATE DEFAULT statement, for example, use the SQL statement below, you can also create address_default default objects: Use BookDB Go Create Default Address_Default As 'No' Go 9.2.2 Binding Default Object 2. Using the sp_bindefault store, for example, the ADDRESS_DEFAULT default object is bound to the ADDRESS table's Address column to complete: use bookdb exec sp_bindefault 'address_default', 'Authors.address' 9.2.2.4 Release Default Object binding, for example, the following SQL statement releases the default value binding on the AUTHORS table Address column: Use bookdb exec sp_unbindefault 'authors.address' 9.2.2.5 Delete the default object, for example,
The following SQL statement to determine whether there is address_default default object, if the default object is deleted: USE bookdb IF EXISTS (SELECT name FROM sysobjects WHERE name = 'address_default' AND type = 'D') DROP DEFAULT address_default GO 9.3 Rule 9.3.1 Creating rules For example, the following SQL statement creates a rule called score_rule, the value of the input must be between 0 and 100: Use test go create rule score_rule as @score betWeen 0 and 100 and the rules created below will be entered The actual value limits the actual value in the columns bound to the rule can only be the value listed in this rule: Use test go create rule list_rule as @List in ('1997', '1997', '1996') can also use LIKE To create a pattern rule, follow the rules of some format.
For example, to make this rule to specify the back of any two characters and any multiple characters (or no characters), and at the end of the integer between 1 and 6, the following SQL statement can be used: Use test GO CREATE RULE PATTERN_RULE As @Value Like '__-% [1-6]' 9.3.2 Binding rules For example, the following SQL statement can bind the score_rule rule on the score list: Use test exec sp_bindrule 'score_rule' , 'score.score' 9.3.3 Delete rules For example, to release the rules on the score column bound to the Score table, you can use the following SQL statement: Use test exec sp_unbindrule 'score.score', for example, to delete rule score_rule, Use the following SQL statement: DROP Rule Score_Rule 9.4 Store Procedure 9.4.1 Creating a Stored Procedure 9.4.1.1 Creating, using the CREATE Procedure statement, for example, create a simple stored procedure bookinfopro for retrieving books, Price and Press: USE bookdb - bookinfopro determined whether there is a stored procedure, if present, is deleted iF eXISTS (SELECT name FROM sysobjects WHERE name = 'bookinfopro' AND type = 'P') DROP pROCEDURE bookinfopro GO USE bookdb GO - Create a stored procedure bookinfopro CREATE pROCEDURE BookInfopro As SELECT BOOK_NAME, PRICE, PUBLISHER FROM BOOK Go Performs this stored procedure by the SQL statement: Execute BookInfopro 9.4.2 Executes a stored procedure, for example, the following is an example of performing a simple stored procedure: Execute BookInfopro 9.4.3 Parameter 9.4 .3.1 Using parameters For example, the SQL statement below creates a stored procedure add_order add_order: use bookdb go create proc add_order (@order_id int, @book_id int, @book_ @book_ @book_book_iD @book_book_id int, @book_id int, @book_ number int, @order_date datetime, @client_id int) AS INSERT INTO orderform VALUES (@ order_id, @ book_id, @ book_number, @ order_date, @ client_id) you can use the following SQL statement calls the stored procedure: EXEC Add_Order 7,2, 10, '99 -05-06 ', 2 Another method of transferring parameters is to use the "@ ORDER_ID = 7" form, at this time, the order of each parameter can be arbitrarily arranged.
For example, the above example can be performed like this: exec address @ order @ Order_ID = 7, @ Book_ID = 2, @ Book_Number = 10, @Order_date = '99-05-06 ', @client_id = 2 9.4.3.2 Using the default parameters, for example, below The stored procedure specifies the default value: use bookdb go create proc add_author (@Author_id int, @Author_name char (8), @address char (50) = 'no', - default value is 'no' @Telphone char ( 15) = 'No' - Default is 'No') AS INSERT INTO Authors Values (@Author_ID, @ Author_Name, @ address, @ Telphone) In the stored procedure created above, include 4 parameters, @address and @TelPhone has the default value "None".
If the stored procedure is called: Exec Add_author 4, 'Zhang San' Go Select * from Authors Go 9.4.3.3 Using Return Parameters, for example, the following stored procedure query_book returns two parameters @book_name and @price, representing the title and price, respectively. : CREATE PROC Query_book (@book_id int, @book_name char (50) OUTPUT, @price float OUTPUT) aS SELECT @ price = price, @ book_name = book_name FROM book WHERE book_id = @ book_id execute the stored procedure to query book_id 2 Name and price of books: declare @Price float declare @book_name char (50) exec query_book 2, @ Book_name output, @ price output select 'book name' = @ Book_name, 'price' = @ price GO 9.4.3.4 stored procedure Return value, for example, the following stored procedure determines the return value according to the input parameters: use test go create proc Test_ret (@Input_int int = 0) as if @ INPUT_INT = 0 Return 0 - Returns if the input parameter is equal to 0, then return 0 if @INPUT_INT> 0 RETURN 1000 - If the input parameter is greater than 0, return 1000 if @Input_int <0 return -1000 - If the input parameter is equal to 0, return -1000 to execute the stored procedure: declare @ret_int int INT EXEC @ RET_INT = TEST_RET-50 SELECT 'Return Value' = @ RET_INT 9.4.4 The stored procedure View, modify, and delete can use the sp_helptext stored procedure to view the definition information for the stored procedure, for example, to see the definition information for the Test_ret stored procedure, You can perform the following SQL statement: exec SP_HELPTEXT TEST_RET, for example, to delete the Test_ret stored procedure, execute the following SQL statement: Drop Procedure Test_ret 9.5 Trigger 9.5.1 Creating a trigger 9.5.1.1 Using Transact -SQL statement creates, for example, the following is to create a trigger, automatically display the content in the table when inserting records: Use test go / * If the table T1 exists, delete * / if exists (SELECT NAME from sysobjects where name = ' T1 ') DROP TABLE T1 Go / * Create Table T1 * / CREATE TABLE T1 (Student_Number Int, Student_name Char (30)) GO / * If the trigger query_t1 exists, delete * / if exists (SELECT NAME from sysoads where name = 'Query_t1' and type = 'tr'
Drop Trigger Query_T1 Go / * Create trigger query_t1 * / create trigger query_t1 on t1 for insert, Update, delete as select * from T1 GO When the following statement is performed: INSERT T1 VALUES (985240, 'Llyyrr') 9.5. 2 INSERTED table and deleted table, for example, the following example illustrates the role of the Inserted table and the deleted table: / * If the trigger query_t1 exists, delete * / if exists (SELECT NAME from sysoads where name = 'query_t1' and type = ' TR ') DROP tRIGGER Query_T1 GO / * create a trigger Query_T1 * / CREATE tRIGGER Query_T1 ON T1 FOR INSERT, UPDATE, DELETE AS SELECT * FROM inserted SELECT * FROM deleted GO performed at this time if the following UPDATE statement: UPDATE T1 SET student_name = 'lyr' where student_number = 985240 9.5.3 Using triggers 9.5.3.1 Insert and Update triggers For example, the check_score trigger created below can be used to check if the inserted score is between 0 and 100: Use test go / * Check if There is a score table. If there is, delete * / if exists (select name from sysoads where name = 'score') DROP TABLE SCIE GO / * Create Score Table * / CREATE TABLE SCORE (Student_no Int, Score Int) / * Check if There is a check_score trigger. If there is, delete * / if exists (select name from sysoads where name = 'check_score' and type = 'tr') Drop trigger check_score go / * Create a check_score trigger on the score table * / Create trigger check_score on score for insert, update as declare @score int search @ score = score from inflection if @Score <0 or @score> 100 Begin Rollback raiserror (') between 0 and 100! ', 16,1) End Go If you insert a record at this time: Insert Score Values (985240, 150) 9.5.3.2 Delete trigger, for example, the score table contains students' student number and grades, if there is still a T1 table (T1 table created in 9.5.1.1), which contains students' partial numbers and names, and they are associated with a student number.
If you want to delete records in the T1 table, students corresponding to the student number of the record should also be deleted: / * Insert a record in the table score * / Insert Score Values (985240, 85) GO / * Create trigger delete_trigger * / CREATE TRIGGER delete_trigger ON T1 fOR dELETE AS dELETE score WHERE score.student_no = deleted.student_number GO At this time, to delete records in the table T1: dELETE T1 WHERE student_number = 985240 then the score table corresponding to the record is also deleted. If you use the SELECT statement to query the score table, you will see that the record has been deleted. 9.5.5 Deleting a trigger, for example, to delete the Query_T1 trigger, then the following SQL statement can be performed: DROP TRIGGER QUERY_T1 Chapter 10 Backup Recovery and Transmission 10.1 Backup Data 10.1.2 Backup Device 10.1.2.2 Use the sp_addumpDevice storage process, for example , Click on a backup device for Test_Backup: Use test exec sp_addumpDevice 'disk', 'test_backup', 'd: /test_backup.bak' 10.1.3 Backup Database 10.1.3.2 Using the Backup statement, for example, using the following SQL statement You can complete the same function above: Backup Database BookDB to Book_backup 10.2 Data Recovery 10.2.2 Restore User Database 10.2.2.2 Using the Restore Statement, for example, the following SQL statement is used to restore BookDB Database: Restore Database BookDB from Book_Backup
