SQL Sage Road Note--SQL study notes

1. Review
-"self-connected"
Self-connecting tables for what structure
Off series reference the primary key column information for this table
This kind of table is not a single meaning
-"Outside Connection"
Left Join
Right Join
Full Join
-"Case clause"
Must appear in the SELECT statement
Complete the selection function
-"Common global variables: @ @identity, @ @error
-"OVER clause: Combined numbering, aggregation use
SUM (Ocount) over ()
Open Window function: to delimit multi-row data
Over (partition by column name ORDER by)
Rank ()
Row_number ()
-"View
Encapsulation of a SELECT statement
-"Business
What issues are used to solve?
-"Stored Procedures
Encapsulate a section of SQL code
-"Index
Optimize query speed
Note: Not as much as possible.

2. T-SQL
-"Trigger:
When the associated operation occurs
For example: When placing an order, the number of items in the creation needs to be reduced
For example: When the refund, the total number of votes to increase
Belongs to the DDL
Keyword Trigger
Key: Determine on which table the trigger is to be built on
Execution time: After: Executes the code in the trigger after the trigger source execution completes
Instead of: Executing code in the trigger before triggering the source execution, while the operation that triggered the source is discarded
Trigger Source: Insert Update Delete
Temp table: Inserted deleted
-"Cursors
Line-by-row access to data

3. Basic types of ADO
-What are two parts of ADO? Data set, data provider Provider
-What are the five major categories? Connection,command,datareader,dataadapter,dataset
-"Connect
Connection string: Server name Data Source, database name initial Catalog, login user ID, password password
-"Major classes on Datasets"
DataSet in-memory database
DataTable in-memory data table
DataColumn Column Information
DataRow line information
-"class that operates SQL Server
-"Connection SqlConnection
Note: This is an unmanaged resource that needs to be completed with close (), Dispose ()
Need to use exception handling
Operation procedure: Construct connection string, create object, open connection ... Close the connection and release the resource
-"Execute Command SqlCommand
ExecuteNonQuery ()
ExecuteScalar ()
ExecuteReader ()
-"Read SqlDataReader
Read ()
get*** (int index)
-"Operating Procedure
= "First: remember the steps; second: Remember the classes and members used in each step

SQL Sage Road Note--SQL study notes