Optimizing the MySchool Database (iii)

Java Training,Android training,IOS Training,. Net Training , look forward to communicating with you!

Write business logic using T_sql:

How to define and use "variables":

----Let the computer remember a name for me (Wang II)

C#: String name; "Define a variable" Name = "Wang er"; "Variable Assignment" MessageBox.Show (name); Output

T_sql: (completely subvert the original syntax) DECLARE @name varchar (20) "Define a variable" Set @name = ' King two ' "Variable assignment" Print @name "Output"

Attention:

1, when defining multiple variables, you can use a declare, multiple variables using "," split;

2, when you need to assign a value to multiple variables at the same time, you can use "select" To replace "set";

3, start with @ is "custom variable", with @@ 开头 is "global variable" (System variable), the global variable is used to publicize the system inside the operation process of various operation information;

----users can not define and modify "global variables", is able to read!

Common Global Variables:

@ @ERROR------Records The result of each execution of the SQL statement, and if executed normally, the result is "0", otherwise the various "error numbers" are returned

@ @IDENTITY----Record the last time you insert data into a "labeled column" form, you get the "self-growth data"

Use of the Select sentence:

--1, for data form "query"

Select Loginpwd from [Admin]

--2, variable assignment "Assign value"

DECLARE @password varchar (50)

Select @password = ' 99999 '

--3, querying and assigning values (ensuring only one value is queried) "Query assignment"

DECLARE @pwd varchar (50)

Select @pwd = Loginpwd from [Admin] where LoginId = ' King II '

--4, output result set "stitching result set"

Select ' King II ' as Stuname, as Stuage, ' Male ' as Stusex Union

Select ' Lily ', 20, ' female ' union

Select ' Zhao June ', 23, ' Male '

Attention:

1, the result set, may not come from the form, but through the select direct splicing

2. Select * from, can be not just a table name, or a "result set" from the back

Variable assignments can be made using set and Select, but there are differences:

Set	Select
Set can only assign a value to one variable at a time	Select can assign values to multiple variables at once
When a set is assigned, more than one result is obtained, Error	When you assign a value to a SELECT, you get more than one result, Automatically keep last value
When the set assignment error occurs, the original data is lost!	When the select assignment fails, the original data is preserved!

How do I save a "result set"?

------table variables, temporary tables

1. Table variables

Defined

DECLARE @temptable Table (name varchar), age int, sex nchar (1))

Assign value

A:insert into @temptable values (' Wang June ', 20, ' men ')----individual assignment

B:insert into @temptable Select query statement----multiple assignments

b_ Example:

Insert INTO @temptable Select ' Wang June ', 20, ' Male ' union

Insert INTO @temptable Select ' Li June ', 20, ' Male '

Take value

is consistent with the way normal forms are accessed.

2. Temporary table

Defined

CREATE TABLE #表名 (... ）

Assign value

Consistent with normal form operation

Take value

Consistent with normal form operation

Table variables	Temp table
Grammar: DECLARE @ variable name Table (column definition)	Create Table #表名 (column definition)
Working principle: Data is stored in "in memory"; can only be defined and used immediately (in a go), using automatic release	Data is saved in the system database in tempdb (on disk); Temporary tables will be available until the user's data connection is "disconnected"
Usage scenarios: A small number of result sets need to be saved and subsequently used immediately	A larger amount of temporary data that needs to be saved

What is the impact of using "variables" on data usage?

---reduces the difficulty of writing SQL business statements! You can split the steps of the original SQL statement into multiple SQL statements to execute, and connect the data between them, using "variables" to save!

T_sql statements can also use if, while for logical branching, looping!

1, the original "{"-----> Begin

2, the original "}"-----> End

Case .... End sentence:

1, two halves;

Case <> End

When <> Then

else (out of order)

2, Case...end cannot appear alone, must be "embedded" query or assignment statement

3, when after, can only be true or false expression

4, then, data type unification

Tips:

The "+" in T_sql has a twofold meaning:

1, when the "+" on both sides are numbers, the "Add operation"

2, when "+" on both sides are characters, the "character connection"

3. Type conversion is required when the data types on both sides are inconsistent

CAST (variable as type)

Convert (type, variable)

-----Difference: Convert is able to make "output format" settings, often used for "time type" and "Type with decimal"

Optimizing the MySchool Database (iii)