SQL Foundation, must be ———— casually tidy, disorganized

1, the installation of SQLSERVER2008R2

2, database and table of the establishment, addition, deletion, modification.

3, the concept of indexing, including the difference between aggregation and non-aggregation. The creation of full-text indexes and how to use full-text indexes.

4. Rebuild the index and reorganize the index.

5, establish maintenance plan, including backup, purge, shrink, reorganize and build index and so on.

6, subscription and replication of the database.

7, understand the concept of full backup, differential backup, log backup and implementation relationship.

8. Understand the establishment of transactions, triggers, stored procedures, and custom functions.

Statement:

Temporary table usage:

SELECT * Into #tmpe from book

SELECT * Into Famp from #tmpe

Select *from #tmpe

The SPID database ID for a database that is in use should be fixed.

Use master

SELECT * FROM sysprocesses where dbid=db_id (' database name ') Order by program_name

View the process of a logged-on user

exec sp_who ' sa '

exec sp_who ' 14 '

End Process:

Kill (SPID number)

Drop Datebase Database name

Example: Drop Database DDD

To create a full-text index, for example:

/* Build a test environment */

If object_id (' TB ') is not null

DROP table TB

Go

CREATE TABLE TB

(ID int identity (),

Title varchar (200),

Detail varchar (1000),

Constraint pk_id primary key (ID)

)

INSERT INTO TB

Select ' rockets are about to sign the rookie shooter ', ' it is learnt that the contract negotiations between Chase Budinger and the Rockets were completed yesterday, Chase Budinger will get the same contract as Taylor. Prior to the media exposure Taylor's contract for four years, the total value of million dollars, of which the first two years for the supportability contract. Chase Budinger is expected to formally announce the signing of the Rockets within the next few days. ‘

UNION ALL

Select ' Weaver has been exposed to the Greek giants ', ' according to the International Basket report, former Rockets player Van Weaver has signed a contract with the Greek giants Olympiacos. Weaver received a two-year contract with a total value of $ million. ‘

UNION ALL

Select ' Spurs to fight the Lakers ', ' Spurs in the summer off the season to add a few of the general, manager Greg-Popovich in the Yahoo! Sports interview revealed that the Spurs to pay luxury tax to build a luxury squad to fight the Lakers, to win the fifth championship. ‘

UNION ALL

Select ' Wallace had never thought of leaving the city of cars ', ' after Ben-Detroit had agreed to return to the Pistons and signed a $ million contract with veteran base salaries and pistons, according to the Daily Herald columnist Mick McGraw The defensive titans of the booming into NBA game never even considered leaving the Pistons. ‘

UNION ALL

Select ' Miller is a good cross-knife to pursue a wife ', ' for those who have not seen Reggie Miller in the Walker to create the "Miller Time", how to introduce the former NBA star? What about the banner that was pulled from the plane over Malibu beach the day before yesterday? I'm afraid no parent is willing to do so. ‘

UNION ALL

Select ' Yao: did not put Shanghai when the investment project happy business to do a good man when the ', ' "Yao Honey," said: "No lack of advertising effect Yao Ming buy the brink of the Shanghai Oriental Basketball Club, that he really want to do something for the parent team." ‘

UNION ALL

Select ' Rockets defeat Wizards of the end of the year record ', ' This service TMAC combination of bad, Yao Ming voted to score a rebound in the cap, McGrady voted to score a rebound in the assists, the two joined together to get a sub-rebounds to assist the sub-cap Jamison. ‘

SELECT * FROM TB

SELECT * FROM TB

where contains ((title,detail), ' Yao Ming ')

SELECT * from TB where contains (detail, ' Yao Ming near Shanghai ')

SELECT * from TB where contains ((title,detail), ' Yao Ming ')

SELECT * from TB where contains (detail, ' Yao Ming * ')

SELECT * from TB where contains (detail, ' Yao Ming ' or ' Shanghai ')

SELECT * from TB where contains (detail, ' Yao Ming ' and ' Shanghai ')

SELECT * from TB where contains (detail, ' Yao Ming ' and not ' Shanghai ')

SELECT * from TB where contains (detail, ' Yao Ming Shanghai ')

SELECT * from TB where contains ((title,detail), ' Yao Ming Shanghai ')

For example, you can test it, maybe it's wrong.

The graphical interface can also be operated on, with two main steps:

1, set up a full-text catalog: Expand a database = "Storage = =" To establish a full-text catalog.

2, expand the table to build the full-text index, right-click = "full-text index =" To define the full-text index, follow the prompts next.

CREATE TABLE, ID increment:

CREATE TABLE ABC (ID int identity (), test int)

Insert ABC Select 1

UNION ALL SELECT 2

UNION ALL Select 3

SELECT * FROM ABC

Transaction: Simply stated, it is a scope, all statements within this range must be completed, and if one is not completed, all statements are not completed.

For example, a bank account transfers money to Bank B's account, a decrease in the account money, the B account must increase the corresponding amount, if the B account is not increased, a account cannot be reduced. As an example:

There is a rollback operation in the transaction.

BEGIN Tran

CREATE TABLE Test2

(

ID int identity (+),

Name varchar (15),

Age int

)

INSERT INTO Test2

Select ' Wang Cheng ', union ALL

Select ' Lee ', UNION ALL

Select ' News ', 18

Save Tran Mytran

Delete from test2 where name = ' Wang Cheng '

Rollback Tran Mytran

Commit Tran

SELECT * FROM Test2

Cursors: This is nothing big. Temporarily think, like a pointer: for example:

Declare test_cursor cursor Scroll for

SELECT * FROM Test2

Open Test_cursor

DECLARE @id int

DECLARE @age int

declare @name nvarchar (10)

Fetch last from Test_cursor into @id, @name, @age

Print @id

Print @age

Print @name

Close Test_cursor

Deallocate test_cursor

Note that the definition and the end cursor, which remains to be studied, although it seems useless, but the existence of the following, it must have his usefulness.

--Simple stored procedures

create proc Mypro3 @bookname varchaR ()

As

Begin

if (select Price from book where book_name like @bookname) >= 29

Return 2

Else

Return 1

End

--Simple invocation

DECLARE @return_value int

exec @return_value = Mypro3 '%linux% '

Print @return_value

If @return_value =2

print ' This book is too expensive '

Else

print ' This book is also available '

Go

The correct results are as follows

2

This book is too expensive.

--Query the number of tables in a library

SELECT * from sysobjects where type = ' u '

--Query How many fields are in a table.

Select COUNT (*) from syscolumns where id =

(select id from sysobjects where name = ' testcopy ')

SQL Foundation, must be ———— casually tidy, disorganized