2015-10-20 SQL Second Lesson (constraint, date, isnull, case, exists, Cast\convert, index, view, stored procedure, trigger, backup and restore)

1 , Primary Key Constraints

There are five types of constraints in SQL Server, Primary key constraints, Foreign key constraints, Unique constraints, Default constraints, and Check constraints.

There is often a combination of one or more columns in a table whose values uniquely identify each row in the table.

Such a column or columns becomes the primary key (PrimaryKey) of the table. A table can have only one primary key, and a column in a PRIMARY KEY constraint cannot be a null value. Only the primary key column can be created as a foreign key for other tables.

To create a primary key constraint, you can right-click the table and select design.

Select the column where you want to create the primary key, and then click the Small key above.

You can also right-click the column where you want to create the primary key, and then press the small key.

2 , Foreign Key Constraints

FOREIGN KEY constraints are used to strengthen the connection between two tables (the primary table and the table) between one column or more columns of data. The order in which foreign key constraints are created is to first define the primary key of the primary table and then define the foreign key from the table. In other words, only the primary key of the main table can be used as a foreign key from the table, and the constrained column from the table may not be the primary key, and the primary table restricts the actions that are updated and inserted from the table.

Right-click the column where you want to set the foreign key (at which point the table appears as an external key from the table), and select the relationship.

Next click Add--table and column specifications.

In the primary key table, select the primary key column for the primary table and the primary table.

Save it when you're finished setting it up.

3 , Unique Constraints

A unique constraint ensures that a column of data in a table does not have the same value. Like a primary KEY constraint, a unique constraint also enforces uniqueness, but a unique constraint is used for a combination of one or more columns of a non-primary key, and a table can define multiple unique constraints.

Right-click the column you want to set to select the Index/key.

And then click the Add button.

Select the columns you want to set, either a column or a combination of multiple columns.

Close and save the settings.

4 , Default Constraints

If a default value constraint is defined in a table, when a user inserts a new data row, if the row does not have the specified data, the system assigns the default value to the column, and the system defaults to NULL if we do not set the default value.

For the Student information table, for example, in Table Designer, fill in the default value for the Sex sex column male.

5 , Check Constraints

A Check constraint uses a logical expression to determine the validity of the data and to limit the range of values that are entered into one or more columns. When you update data in a column, the content that you want to enter must meet the criteria for the Check constraint, or it will not be entered correctly.

In the case of sex in the Student information table, we want to limit the value of the sex column to only male or female.

Close and save the design.

6. Select the date of the month

Select year (GETDATE ())//Get current years

Select Month (getdate ())//Get the current month

Select Day (getdate ())//Get current days

SELECT * from employ where month (birthday) =8//Print out all information about employees who have birthdays in August

SELECT * from employ where year (GETDATE ())-year (birthday) >25//years (GETDATE ()) for current years, print out all information for employees older than 25

SELECT * from employ where year (birthday) =2008 and month (birthday) =8 and day (birthday) =12//Print out all employee information for birthdays 2008-8-12

Select DATEADD (Yy,100,getdate ())//the day plus 100 years time, GETDATE () can also be replaced by a specific day, such as written: ' 2108/12/31 '

Select DATEADD (Mm,1,getdate ())//the day plus 1 month time

Select DATEADD (Dd,100,getdate ())//day plus 100 days

Select DateDiff (Yy,getdate (), ' 2108/12/31 ')//How many teenagers are there from 2108/12/31

Select DateDiff (Mm,getdate (), ' 2108/12/31 ')

Select DateDiff (Dd,getdate (), ' 2108/12/31 ')

usage of 7.isnull

Select Title,content,isnull (categoryid,0) from news//null CategoryID shown with 0

8.case Usage

Find the state of the categoryid=13 and judge the state

Select State,case

When (state=1) then ' awaiting trial '

When (state=2) then ' has been examined '

End as Pro_state

From category where categoryid=13

Find out how many low-level, intermediate how many, advanced how many

Select COUNT (*) as [count] from category

GROUP BY

Case

When categoryid<15 and then ' low '

When CategoryID between, and then ' intermediate '

Else ' advanced '

End

Identify the Categoryid,name in category and determine the size of each CategoryID

Select Categoryid,name,

Case

When categoryid<15 and then ' low '

When CategoryID between, and then ' intermediate '

Else ' advanced '

End as Categoryrange from category

9.Category and new table CR (select result set as New table), left JOIN application

Select c.*, IsNull (CR. count,0) as [Count]

From Category C

Left Join

(

Select CR. Categoryid,count (*) as [Count] from categoryrelative CR

Right joins Entry E on CR. childID = e.id and e.state <> 2 and e.isactive = 1

where Cr.blogid = 8785

Group by Cr. CategoryID

) cr on C.categoryid = cr. CategoryID

Where BlogID = 8785 and CategoryType = 1

usage of 10.exists  

Find out that the CategoryID in the category table are not equal to N.categoryid C.categoryid,c.[name]

First query statement: Select C.categoryid,c.[name] from Category C left join News N in C.categoryid=n.categoryid where N.categoryid is Null

Second query statement: Select Categoryid,[name] from category where NOT exists

(

SELECT * FROM News where Category.categoryid=news.categoryid

)

* The first performance is undoubtedly the best when the CategoryID is not repeated in the news table, whereas the second performance is good.

11.SQL Statement conversion Format function cast, convert

Both cast and convert are often used. Specially extracted as an article, convenient to find.

Both cast and convert can perform data type conversions. In most cases, the two perform the same function, but the convert also provides some special date format conversions, and cast does not have this function.

Since convert includes all the functions of cast, and convert has a date conversion, why do I need to use cast? In fact, this is for ansi/iso compatibility. Cast is ANSI-compliant, and convert is not.

Grammar:

CAST (expression as data_type [(length)])

CONVERT (data_type [(length)], expression [, style])

Example:

SELECT ' AB ' + 1 --This statement is an error and fails when the varchar value ' AB ' is converted to a data type int.

SELECT ' AB ' + CAST (1 as varchar)--Output AB1

SELECT ' AB ' + CONVERT (varchar,1)--Output AB1

Both cast and convert can convert time:

SELECT CONVERT (DateTime, ' 2011-07-11 ')--output 2011-07-11 00:00:00.000

SELECT CAST (' 2011-07-11 ' as DateTime)-Output 2011-07-11 00:00:00.000

But time-to-string, cast does not convert so many tricks:

SELECT CONVERT (Varchar,getdate (),5)--Output 01-07-13

SELECT CONVERT (Varchar,getdate (),111)-Output 2013/07/01

SELECT CONVERT (Varchar,getdate (),1)--Output 07/01/13

SELECT CAST (GETDATE () as varchar)-Output 1 9:56pm

12. Index:

Http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html

Detailed information can be obtained through the above pages.

The storage of a table consists of two parts, one for storing the data page and the other for the index page.

Indexing is similar to a directory in an encyclopedia, and using an index is a way to quickly find the actual information in a database.

The index is divided into:

Clustered index--------Each table can have only one clustered index, and its corresponding table is physically sorted by its index, and for encyclopedias, the clustered index is similar to the page number of the book, saving the encyclopedia's information in the order of page numbers.

Nonclustered indexes-------Each table can have multiple nonclustered indexes, and for encyclopedia books, the nonclustered index refers to the keyword directory behind the encyclopedia.

13. View

A view is a virtual table whose use is similar to the use of tables, primarily to control the data that the user wants to display.

Create a view:

Eg:

CREATE VIEW CURRENTEMPLOYEES_VW

As

Select Categoryid,[name],age from Category

where state=1

Operation Method:

1, right-click to add a new view;

2, select the form to query;

3, select the corresponding related fields;

4. Save to generate the view, which is equivalent to the full join SQL statement.

14. Stored Procedures

1) Advantages: Compile at the time of creation, each subsequent execution of the stored procedure does not need to be compiled, and the general SQL statements are compiled once per execution (so some of the complex logic of SQL recommendations written in the stored procedures); Stored procedures are reused, Reduces the workload of the database Developer (so some SQL recommendations that are often invoked are written in the stored procedure); Security is high, and you can set only certain users to have the right to use the specified stored procedure.

1) Basic syntax

Eg:

Create proc Proccategoryselect

(

@name varchar (200),

@age int

)

As

SELECT * from Category where [name][email protected] and [email protected]

1) View results exec proccategoryselect ' xudads ', 11

2) Change/delete stored procedure

ALTER PROC//modify stored Procedure

drop proc//delete stored procedure

4) Flow control statement

Eg:

Create proc Procnewsinsert

(

@creatTime datetime//Set a Crattime variable to add an argument when executing

)

As

If DateDiff (DD, @creatTime, GETDATE ()) >7/*creattime is greater than the day */

Begin

SELECT * FROM News

End

Else

Begin

Select Top * * FROM News

End

Executive: Exec procnewsinsert ' 2009-05-10 11:36:43.810 '

Results:

15. Triggers

A trigger is a special kind of stored procedure, which is the same as an event when the data is added, deleted, and changed at the same time.

Create a new trigger:

Script to write:

EG1:

Create Trigger Trigcategorydelete

On Category

After delete/* There are three kinds of insert,delete,update*/

As

Begin

SELECT * FROM deleted/* Delete the contents after deletion * *

End

GO

Perform:

EG2:

Create Trigger Trigcategorydelete

On Category

Instead of delete/* instead of, which means that the DELETE statement is replaced by the statement below the begin when the delete is performed */

As

Begin

DECLARE @id int

Select @id =id from deleted

Delete news where [email protected]

Delete category where [email protected]

End

GO

Perform:

Delete from category where id=5/* This allows you to delete the id=5 in the category and delete the id=5 in the news * *

16. Database Backup and Restore

First, SQL Backup of the database :

1. Open Start menu → program →microsoft SQL Server 2008→SQL server Management studio→ database: dsideal_school_db is the database of students we need to back up

Figure (1)

2, select the database to be backed up "dsideal_school_db", right click → tasks → backup, (2):

Figure (2)

3. In the "Backup Database-dsideal_school_db" dialog box that opens, click Delete, then click "Add", (3):

Figure (3)

4. In the "Select Backup Destination" dialog box that pops up, (4)

Figure (4)

5, choose the path of the backup (such as the "D" Jiangxi-student-database backup folder), file type select "All Files", "file name" that location fill in the name of the database you want to back up (preferably after the name of the database you backed up with a date to facilitate later search), followed by a continuous click "OK" button to complete the backup operation of the database, (5):

Figure (5

Second, SQL Restoring the database :

1. Select the database you want to restore "dsideal_school_db", right click → tasks → restore → database, (6):

Figure (6)

2. In the "Restore Database-school_web_db" dialog box that appears, select "Source Device", and then click on the "..." button later, (7):

Figure (7)

3. In the "Specify Backup" dialog box that appears, click the "Add" button, (8):

Figure (8)

4, find the path of the database backup, select the database you want to restore "dsideal_school_db" (Note: File type Select all files), and then " two times " click the "OK" button, (9):

Figure (9)

5. In the "Restore Database-dsideal_school_db" dialog box that appears, tick the check box before "Select the database under user restored backup set" (10)

Figure (10)

6. Then select "Option", tick "Overwrite existing database", (11):

Figure (11)

Third, restore the database problem solution

When restoring the database "dsideal_school_db", sometimes you will encounter the problem "because the database is in use, so you cannot gain exclusive access to the database", we can follow the following steps to resolve this problem

1. Right-click the database "dsideal_school_db" and select "Properties", as

2. In the Database Properties-dsideal_school_db dialog box that appears, select options, and in status under Other options, locate restrict access. Select "Single_user" and click "OK" button, such as:

3, follow the normal restore database steps, restore the database.

2015-10-20 SQL Second Lesson (constraint, date, isnull, case, exists, Cast\convert, index, view, stored procedure, trigger, backup and restore)