Dark Horse Learning SQL Server Advanced

Case

In the database , Compare the data to 2 different ways

The first way, do not case for any column

Select Name,case when age>16 then ' adult '

When age<16 then ' underage ' end as ' adult '

From Student

If at the time of judging multiple worth , when ( condition ), who first satisfies to execute who

Equivalent to the if else inside C #

The second way , the direct case column

Select Case ID

When 1 Then ' first row '

When 2 Then ' second row '

When 3 Then ' third row '

End from Student

Equivalent to the switch case inside C #

1. Multiple then followed data types are consistent

Sub-query

In one query , There is another query result, which is called the subquery of the main query

SELECT * from Student where id= (select ID from class where name = ' class ')

The columns queried within the parentheses must have a column

A generic subquery is used after the Where condition

EXists

SELECT * from Student where exists (SELECT * from class where class.id=student.id)

SELECT * from Student where exists (select "from class where class.id=student.id)

EXISTS is a way to refer to the data of the main query as a subquery , in the subquery , only to do the conditional judgment, do not do any column output

Paging Query

SELECT * FROM ((select *,row_number () over (order by ID) as ROW from Student) as T

where T.row between 1 and 3

Use the Row_number () function to separate a column and then match between and on this column .

Table Join Join

Connect The data rows of 2 or more tables according to the specified rules

-- Check the student's class name age

Select Student.name,age from Student join class on Student.classid=class.id

Join connection to meet the specified conditions, if not met the data will not appear

Select Student.name,age,math,english,chinese from Student

Join class on Student.classid=class.id

Join score on Student.id=score.stuid

Connect 3 sheets

-- Check the students ' grades, regardless of their score

SELECT * from Student LEFT JOIN score on Student.id=score.stuid

The left join is based on the normal join , showing the table on the right, and if the condition is met, the data of the table after the connection is displayed , if the condition is not satisfied, The left table data is still displayed, and the right table value is NULL

Right join on the basis of the normal join , the first display of the table on the left , If the condition is satisfied, the data of the table after the connection is displayed , if the condition is not satisfied, The right table data is still displayed, and the left table value is NULL

- students inquiring for exams

SELECT * from Student right join score on Student.id=score.stuid

INNER Join is actually a join .

Cross join results in a 2-sheet product line

View

A view is a virtual table that represents part of a table's data or comprehensive data for multiple tables , and the results and data are based on queries on the table.

When we query a view, it is actually the result set of the query in the query view.

What columns are in this result set and only what columns are displayed

Insert statement will not affect multiple tables under the premise , can be used ( affect only one table )

The view is only used to make the query , once the change to do only for a table , Add in the data , can not violate the constraints of the original table

Delete will affect more than one table, so it is certainly not possible

DECLARE

DECLATR @ variable name data type

The default value of the variable is NULL

Assign value

SET @ variable name = value

SELECT @ variable name = value

Take value

PRINT @ Variable name

Global variables

@ @ERROR Record the last error number that occurred

@ @IDENTITI The last marked value inserted

@ @MAX_CONNECTIONS The number of connection that can be created at the same time per server is limited

@ @ROWCOUNT The number of rows affected by the last statement

IF ELSEif ((select COUNT (ID) from student) >2)

Begin

print ' A lot of people. '

End

Else

Begin

print ' very few people! '

End

Transaction

Begin TRANSACTION---- start a transaction

ROLLBACK TRANSACTION--- abort the transaction , abort the operation

Commit TRANSACTION------ COMMIT TRANSACTION

Stored Procedures

The stored procedure is like a method in C # , but the method in the database is actually

Create a stored procedure

Create proc Getstu

As

SELECT * FROM Student

Executing stored procedures

EXEC Getstu

ALTER proc GETSTU-- stored procedure with parameters

@id int=0

As

Begin

Select *from Student where [email protected]

End

EXEC getstu 2-- call a stored procedure with parameters

Trigger

New Modify delete query without trigger

Create trigger Tgclass on class

After insert

As

Begin

print ' adds a new line '

End

Create trigger Tgclasss on class

After update

As

Begin

print ' modified a line '

End

Create trigger Tgclasss on class

After delete

As

Begin

print ' deleted a line '

End

Dark Horse Learning SQL Server Advanced