SQL Server Synthesis Summary

Abstract programming oriented

Interface-Abstract class-Parent class-Concrete class

1. Review T-SQL

Establishment of-> database, table establishment and constraint (DDL)

-> Build a library

if (db_id (database name)) is not NULL

drop database name;

Create database name;

-> Building Table

if (object_id (' Table name ', ' U ')) is null

CREATE TABLE Table name

(

field type [constraint],

field type [constraint],

field type [constraint]

);

-> Construction constraints

ALTER TABLE name ADD CONSTRAINT constraint name constraint

-> PRIMARY KEY constraint (Pk_ table name _ field name)

Primary KEY (field)

-> default constraint (df_ table name _ field name)

Default (value) for field

-> Check Constraint (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 Manipulation Language (DML)

-> Basic Language

Increase

Insert into Table name (field list) VALUES (list of values);--Table exists

The Select field list into the new table name from the old table; --Requires that the new table not exist

Insert into Table name (field list) select query;

Delete (when the primary foreign key is associated, the deletion can sometimes cause problems)

Delete from table name where condition;

TRUNCATE table name;--all data to zero

drop database databases;

The drop table table name;

Change

Update table name Set field = value, field = value, ... where condition;

Check (* * *)

-> FROM clause

-> WHERE clause

-> GROUP BY clause

-> HAVING clause

-> select top distinct and OP expression

-> ORDER BY clause

Core Remember Process

-> Advanced Section

Case syntax

-> if ELSE if structure

Case

When expression 1 then display 1

When expression 2 then display 2

...

Else Show n

End

-> switch-case Structure

Case Field

When value 1 then display 1

When value 2 then display 2

...

Else Show 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;

-> Inner Connection

ANSI-89 Syntax: SELECT * FROM table 1, table 2 on condition;

ANSI-92 Syntax: SELECT * FROM table 1 inner JOIN table 2 on condition;

-> Outer Joins

Syntax for ANSI-92:

SELECT * FROM table 1 left JOIN table 2 on condition;

SELECT * FROM table 1 Right join table 2 on condition;

->, multiple table connections

Table 1

INNER JOIN

Table 2

On condition

INNER JOIN

Table 3

On condition

Child query

Condition of one query as the result of another query

Distinguish between internal queries (subqueries) and external queries

-> independent standard Quantum query field = (subquery)

-> Independent multivalued subquery field in (subquery)

Table-expression

-> derived tables

To use the result set of a query as a data source--cannot be used by

SELECT * FROM

(

Select

Row_number () over (stuid) as NUM,

*

From

Student

) as TBL

where

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

2. Common table Expressions (CTE)

is to store the resulting set of a query in a single table and specify a variable name

Later query directly using variable name can

With Alias

As

(

Result set

)

Inquire

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

Use this object directly in the database each time you use it, this complex query specification is documented in this object

Query executed by database

Then there's the view (virtual table)

Create View Vw_ View name

As

Inquire

Note: The view does not have the ability to store data, but it is used like a table

4. (* introduction) inline table-valued function

An attempt with parameters

Create function Fn_ Name

(at the @ parameter type, ...) Returns table

As

return query

5. Variables and Process Control in SQL

-> SQL is a scripting language that allows BASIC programming statements to support

-> Define Variables

DECLARE @ variable name type name;

-> Assign Value

SET @ variable name = value;

-> Supplement

Using Select to assign values

SELECT @ variable = field from table name ... (Multiple value queries are supported)

Using subqueries to assign values

SET @ variable = (single value subquery); (only single value queries are supported)

>sql 2008 New Syntax

DECLARE @name varchar (5) = ' Great Xin ';

Select @name;

-> judgment

if (bool expression) begin end equals curly braces in the program ()

Begin

--statement

End

else if (expression)

Begin

--statement

End

Else

Begin

--statement

End

-> Cycle

while (bool expression)

Begin

--statement

End

Cycling Practice

--ask for 1 to 100 and

declare @jkSum int;

declare @i int;

Set @jkSum = 0;

Set @i = 1;

while (@i <= 100)

Begin

Set @jkSum = @jkSum + @i;

--set @i = @i + 1;

--sql inside the loop, you assign the @i+1 and then perform the new values provided by the next loop.

Set @i + + 1;

End

Select @jkSum;

Go

6, why have a business, what is the business

Transaction, which is an independent execution process in SQL

This result is the result of the data that will affect the database

This transaction has nothing to do with other execution procedures

The execution of a transaction is a whole, or it all succeeds, and all fails.

Atomicity, persistence, isolation, consistency of transactions (cannot violate constraints)

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

A transaction is a minimal execution unit.

Common global variable @ @version, @ @error (last error t_sql information)

@ @identity (the last to insert the table's own ID)

Query Chinese for error messages trying to

SELECT * from sys.messages where where language_id=2052;

Show Declaration transactions

BEGIN TRANSACTION--shorthand BEGIN TRAN

Transaction internal

--Committing a transaction or rolling back a transaction

Commit transaction--indicates that everything inside the transaction was executed successfully

Rollback transaction--said to undo everything inside the transaction

-> use Try-catch block to handle exception errors in transactions

Begin try

End Try

Begin Catch

End Catch

7. The stored procedure is to record the execution steps of some columns to the database, somewhat similar to the method

-> a stored procedure with no parameters and no return value

-> with parameters, no stored procedure with return value

-> stored procedures with default parameters

-> stored procedures with default parameters and parameter return values

Declares a default parameter @stunametemp nvarchar = ' all '

---> [] can be, can have no

---> Multiple statements must add a BEGIN end If a statement can omit the Begin end

CREATE proc[edure] Usp_ stored procedure name

@ Parameter 1 data type [= Default] [OUTPUT],

@ parameter n data type [= Default] [OUTPUT]

As

BEGIN

SQL statement

End

exec stored procedure parameters;

exec stored procedure parameter name = parameter;

exec stored procedure parameters, parameter n output;

8, using Ado.net to execute SQL method

-> stored procedures that do not return a value

-> stored procedures with parameter return values

-> Connection string

-> SQL statement (name of stored procedure)

-> Create parameters

If it is a stored procedure that performs a parameter return value

Define the parameters to return, do not assign values, set direction

String sql = "Usp_transmoney";

Sqlparameter[] PS =

{

New SqlParameter ("@from", from),

New SqlParameter ("@to", to),

New SqlParameter ("@money", money),

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

};

PS[3]. Direction = ParameterDirection.Output;

-> Connection Channel SqlConnection

-> Create Execution Object SqlCommand (set CommandType)

-> Add Parameters

-> Open Connection

-> Execution method

9. Triggers

-> a special stored procedure. Inserted table and deleted table 2 temporary tables

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

VALUES (' 0004 ', 10000);

For or after triggers are executed to trigger

Instead of substitution

--Add a trigger, delete all data, after inserting the data back

Create trigger Tr_deluseinfo on table name (bank)

For delete

As

INSERT INTO the bank select * from deleted;

Go