SQL server Summary

Abstract Programming

Interface-abstract class-parent class-specific class

1. Review T-SQL

-> Database creation, table creation, and constraints (DDL)

-> Database creation

If (db_id (Database Name) is not null

Drop database name;

Create database name;

-> Create a table

If (object_id ('table name', 'U') is null

Create table Name

(

Field Type [constraint],

Field Type [constraint],

Field Type [constraint]

);

-> Constraint Creation

Alter table name add constraint name Constraints

-> Primary key constraint (pk_table name_field name)

Primary key (field)

-> Default constraint (DF _ TABLE name_field name)

Default (value) for Field

-> Check constraints (CK _ TABLE name_field name)

Check (expression)

-> Unique constraint (UQ _ TABLE name_field name)

Unique (field)

-> Foreign key constraint (FK _ foreign key table _ primary key table _ field name)

*

Alter table foreign key table add

Constraint FK _ foreign key table _ primary key table _ field name

Foreign key (field name) references primary key table (field name );

-> Database operation language (DML)

-> BASIC Language

Add

Insert into Table Name (Field List) values (Value List); -- the table exists

Select field list into new table name from old table; -- requires that the new table does not exist

Insert into Table Name (Field List) select query;

Delete (when the primary and Foreign keys are associated, deletion may cause problems)

Delete from table name where condition;

Truncate table name; -- returns all data to zero.

Drop database;

Drop table name;

Change

Update table name set field = value, field = value ,... Where condition;

Query (***)

-> From clause

-> Where clause

-> Group by clause

-> Having clause

-> Select top distinct and operation expression

-> Order by clause

Core remember Process

-> Advanced

Case syntax

-> If else if Structure

Case

When expression 1 then display 1

When expression 2 then display 2

...

Else display n

End

-> Switch-case structure

Case Field

When value 1 then display 1

When value 2 then display 2

...

Else display n

End

Connection

-> Cross join

ANSI-89 Syntax: select * from table 1, table 2;

ANSI-92 Syntax: select * from table 1 cross join table 2;

-> Internal connection

Syntax for the ANSI-89: select * from table 1, Table 2 on condition;

ANSI-92 Syntax: select * from table 1 inner join table 2 on condition;

-> External connection

ANSI-92 syntax:

Select * from table 1 left join table 2 on condition;

Select * from table 1 right join table 2 on condition;

-> Self-join and multi-table join

Table 1

Inner join

Table 2

On condition

Inner join

Table 3

On condition

Subquery

Use the result of one query as the condition of another query

Clear internal queries (subqueries) and external queries

-> Independent scalar quantum query field = (subquery)

-> Independent multi-value subquery FIELD in (subquery)

Table expression

-> Derived table

Use the queried "result set" as the data source -- order by cannot be used

Select * from

(

Select

Row_number () over (order by stuId) as num,

*

From

Student

) As tbl

Where

Tbl. num between @ count * (@ page-1) + 1 and @ count * @ page;

2. Common table expressions (CTE)

A query result set is saved as a table and specified with a variable name.

You can directly use the variable name for subsequent queries.

With alias

As

(

Result set

)

Query

3. Considering that the derived table and CTE are both temporary and bloated, we hope to define the common query structure in the database.

This object in the database is directly used for each usage. This OBJECT records this complex query specification.

Query by database

Then there is a view (virtual table)

Create view vw_view name

As

Query

Note: A view does not have the ability to store data, but it is used as a table.

4. (* Introduction) inline Table value functions

Attempt with Parameters

Create function fn _ name

(@ Parameter type ,...) Returns table

As

Return Query

5. SQL variables and Process Control

-> SQL is a scripting language and can be considered as supported by all basic programming statements.

-> Define variables

Declare @ variable name type name;

-> Value assignment

Set @ variable name = value;

-> Supplement

Assign values using select

Select @ variable = field from table name... (Multi-value query supported)

Assign values using subqueries

Set @ variable = (single-value subquery); (only single-value query is supported)

> SQL 2008 new syntax

Declare @ name varchar (5) = 'daxin ';

Select @ name;

-> Judgment

If (bool expression) begin end is equal to the braces () in the program ()

Begin

-- Statement

End

Else if (expression)

Begin

-- Statement

End

Else

Begin

-- Statement

End

-> Loop

While (bool expression)

Begin

-- Statement

End

Loop exercise

-- Calculate the sum of 1 to 100

Declare @ jkSum int;

Declare @ I int;

Set @ jkSum = 0;

Set @ I = 1;

While (@ I <= 100)

Begin

Set @ jkSum = @ jkSum + @ I;

-- Set @ I = @ I + 1;

* *** -- Assign a value to @ I + 1 after the SQL statements are executed cyclically, and then execute the new value provided by the next loop.

Set @ I + = 1;

End

Select @ jkSum;

Go

6. Why are there transactions and what are transactions?

A transaction is an independent execution process in SQL.

This result will affect the data results of the database.

This transaction (Execution Process) has nothing to do with other execution processes

The transaction is executed as a whole, either all succeeded or all failed.

Atomicity, persistence, isolation, and consistency of transactions (constraints cannot be violated)

Every SQL statement in SQL is actually a transaction (implicit transaction)

A transaction is the smallest execution unit.

Common global variables @ version, @ error (information of the last incorrect T_ SQL Statement)

@ Identity (the last auto-increment id of the inserted Table)

// Try to query Chinese error messages

Select * from sys. messages where region age_id = 2052;

Show declarative transactions

Begin transaction -- in tran

Internal transaction

-- Submit a transaction or roll back a transaction

Commit transaction -- indicates that all tasks in the transaction are successfully executed.

Rollback transaction -- indicates to cancel all tasks in the transaction.

-> Use the try-catch Block to handle exceptions and errors in the transaction

Begin try

End try

Begin catch

End catch

7. The stored procedure records the execution steps of some columns in the database, which is similar to the Method

-> Stored procedures with no parameters or returned values

-> Stored procedures with parameters and no return values

-> Stored procedures with default parameters

-> Stored procedures with default parameters and return values

Declare a parameter with the default parameter @ stuNameTemp nvarchar (20) = 'all'

---> [] Yes, no

---> Begin end must be added to multiple statements. If a statement is used, begin end can be omitted.

Create proc [EDURE] USP _ stored procedure name

@ Parameter 1 Data Type [= default value] [OUTPUT],

@ Parameter n data type [= default value] [OUTPUT]

AS

BEGIN

SQL statement

END

Exec stored procedure parameters;

Exec Stored Procedure Parameter = parameter;

Exec stored procedure parameter, parameter n output;

8. Use ADO.net to execute SQL statements

-> Stored procedure with no return value

-> Stored procedures with parameter return values

-> Connection string

-> SQL statement (stored procedure name)

-> Create Parameters

If it is a stored procedure that executes the parameter Return Value

Define the parameters to be returned. Do not assign values. set Direction.

String SQL = "usp_TransMoney ";

SqlParameter [] ps =

{

New SqlParameter ("@ from", from ),

New SqlParameter ("@ to", ),

New SqlParameter ("@ money", money ),

New SqlParameter ("@ isAccess", SqlDbType. Int)

};

Ps [3]. Direction = ParameterDirection. Output;

-> Connection channel SqlConnection

-> Create the execution object SqlCommand (set CommandType)

-> Add Parameters

-> Open the connection.

-> Execution Method

9. triggers

-> Special stored procedures. Inserted Table and deleted table

Insert into back (cid, balance) output inserted .*

Values ('20140901', 0004 );

The for or after trigger can be triggered only after execution.

Replace instead

-- Add a trigger. After deleting all data, insert the data back.

Create trigger tr_DelUseInfo on table name (bank)

For delete

As

Insert into bank select * from deleted;

Go