10-30c# Database Fundamentals-(Backup and restore, detach and attach databases), statement query operations

First, the database is a large storage of data warehouse, in order to ensure data integrity, to prevent some accidental loss of data, such as the need for data backup and restore.

Backing up data does not affect the normal operation of the database.

1. Back up and restore the database

Start by backing up the database with the following steps:

1) put the mouse in the select the database you want to back up, right-click on the "task"-"Backup";

2) Click OK, pop up the Backup Database window, under Backup type, select Copy backup only, select Backup expiration, add target backup to location, and click OK.

3) Popup dialog: Prompt the backup has been executed successfully.

Restores:

After the backup is complete, delete the data and restore the original database.

1) The first method: Put the mouse in a database you want to restore right-click, select "task"-"restore - database";

The second method: (under the premise that the database is all deleted), put the mouse in the "database" right click ---"restore Database";

2) The "Restore Database" window appears, the target database is the name of the database you want to restore, specify the location of the source device

The location of the specified backup appears, and click OK.

3) A restored backup set appears, select Copy only the backed up database,

4) Popup dialog: Prompt restore completed successfully.

If the following dialog box appears, the restore did not complete successfully.

2, attach, separate is the database

Separation:

1) Right-click on "task"-"Detach" in a database that you want to detach

2) Eject the Detach Database window and click OK.

3) Move the detached data elsewhere (as long as it is not in the original disk database folder)

Additional:

1) Back to the database management platform, right-click on "Database"--"Attach"

2) pop up the "Attach Database" window, select the database you want to attach, and click OK.

3) After attaching, under Object Explorer, locate the appropriate database.

Below the query, delete the database by code:

Drop database + Name of database--Delete DB statement

Second, the query statement (emphasis)

1, modify the table, add columns first, notice the column name and the built-in Word data type conflict, column name plus [] enclosed.

Alter table Xinxi Add[int]varchar (10)

Alter table Xinxi Add nianling int

Alter table cannot be set to not be empty,

2. Modify the table to delete a column:

Alter table xinxi Drop column [int]

3, change the name, change the student to Xuesheng

Modify the name of the database: Sp_renamedb student (previous database name), Xuesheng (the name of the database to be changed)

Modify the name of the table: sp_rename xinxi,tongji--Modify the name of the table

4, where. (conditions). . Between...and. (range). Between the two (greater than or equal to, less than or equal to)

Select *from Xinxi where Fenshu between and 100

5. Update....set...where ...

Example: Update Xinxi set nianling=26 where Fenshu between and 100

6. Automatic de-duplication distinct for one column, multiple columns cannot be displayed

Select distinct ...

Example: SELECT DISTINCT name from Xinxi

7, all generally default, do not write

8. Update Xinxi set nianling=26 where code=6

9. and (Separate). Is "and"

Check the two John Doe with the age of 26:

Select * from Xinxi where name= ' John Doe ' and nianling=26

10, or ... "or"

Select * from Xinxi where name= ' John Doe ' or nianling=26

11, IN (...) In... Range of

Column names are queried for which range to query.

Select * from Xinxi where nianling in (21,22,23,24)

Select * from Xinxi where name in (' John Doe ', ' Zhao Liu ')

12, not...in (...) Not... Range of

Select * from Xinxi where name is not in (' John Doe ', ' Zhao Liu ')

13, wildcard% means any number of any character; Like, fuzzy query, when only know the approximate time

Select * from Xinxi where name like '% four '

14. The underscore _ denotes any one character

Select * from Xinxi where name like ' Li _ '

15, _[,] underline and brackets equal to in, indicating that any one of the values within the brackets can be queried.

Select * from Xinxi where name like ' _[John Doe, Zhao Liu, Tianqi] '

16, order by a column name sort, ASC ascending, desc Descending, default is not write ascending.

Select * from Xinxi ORDER by nianling ASC

Select * from Xinxi ORDER BY nianling Desc

17, in descending order to take the top three

Select top Xinxi ORDER BY Fenshu Desc

18, according to the conditions of the query after the order (check the name of the person who John Doe the highest score)

Select top 1*from Xinxi where name= ' John Doe ' ORDER by Fenshu Desc

10-30c# Database Fundamentals-(Backup and restore, detach and attach databases), statement query operations