Database operators and Process Control if,while,break,continue

Use new

Go

--Define variable, @ start

DECLARE @hello as varchar (20)

--Assigned value

Set @hello = ' Sales Department '

--You can use variables as parameter values for the selection criteria

--select *from bumen where name [email protected]

Select does not perform a query function when placed in the middle of select and from, as an assignment statement

--select @hello =name from Bumen where code=1

Print @hello – Prints out ' sales Department '

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

--Global variables, system variables

--Returns the number of connections to SQL Server since it was last started, including successes and failures.

Print @ @connections

--Returns an error when executing the previous SQL statement, returning 0 is the right

-select from Bumen (example)

Print @ @error

--Returns the language currently used by the system

Print @ @language

--The return value indicates the number of rows affected by the previous statement in the list

Print @ @rowcount

--Returns the version of the installed SQL

Print @ @version

Update bumen Set phone = ' 22342432 '

Select ' This statement affects ' +cast (@ @ROWCOUNT as varchar (20)) + ' line '

--The transformation data type is string type.

DECLARE @text varchar (20)

Set @text = ' 123 ' 123 '

Print @text--display: 123 ' 123

---operator

---+-*/%, declare, set, print all to execute, just can!

DECLARE @jia INT-Creates an int variable @jia

Set @jia =1+1-Assignment

Set @jia =10%3

Print @jia-printing, Output!

---comparison operator >,<,<=,>=,!=,!>,!<,<> (indicates not equal)

Select *from bumen where code <>3

--logical operators And,or,all,any, between, in, like, Not,some, exists

--Indicates that all column element data is satisfied with the subquery, with the comparison operator

Select *from Xuesheng where name =any (select name from Xuesheng where code<=3)

--any means that any condition can be met, some and any are the same!

Select *from Xuesheng where name >any (select name from Xuesheng where code<=3)

--exists indicates that a condition exists.

--The following statement indicates that there is a score in the score table for the person in the student table information. Not exists represents information that does not exist

INSERT into Xuesheng values (, ' SD ', ' SD ', ' e ', 2,3,2)

Select *from Xuesheng where NOT EXISTS (select *from Fenshu where Xuesheng. Code =fenshu. Code)

--not can be combined with in, like,exists use,

---Query the information of the second class mathematics to be larger than all the classes of credits

Select *from Fenshu where Shufen> all (

Select Shufen from Fenshu where code in

(select code from Xuesheng where banji= ' 1 classes ')

)--the first method of

Select *from Fenshu where Shufen > (

Select MAX (Shufen) from Fenshu, Xuesheng

where Fenshu. Code =xuesheng. Code and banji= ' 1 class '

GROUP BY Banji)----The second method of

Select Banji, MAX (Shufen) from Fenshu

Join Xuesheng on Xuesheng.code=fenshu.code

GROUP BY Banji-----shows the highest score per class, grouped by class

Select Xuesheng.code, Banji, Fenshu.shufen from Xuesheng

Join Fenshu on Xuesheng.code =fenshu.code

ORDER BY Banji--student number, class, math score sorted by class

------------unary operators

Plus +, minus-(minus sign use, general parentheses)

--------Priority

1.*%/

2. Plus and minus sign

3.>,<,>=,<>,!=,!<,!>

4.not

5.and or between

6.all Some in like exists

7. =

Begin-Start (start to end is represented by a whole, convenient (distinction), is the concept of curly braces)

Select *from Xuesheng

End--Ending

DECLARE @bianliang INT--Defines an int type variable

Set @bianliang = 7--assignment variable to use Set

If @bianliang >5

Begin

print ' Hello '

print ' Thank you '

End--begin......end appears to be in C #, after the IF {}

Else

print ' Good you '

--Information of the students with the highest score in mathematics

-First

Select *from Xuesheng where code= (select top 1 code from Fenshu ORDER BY Shufen Desc)

-second, temporary storage through temporary variables, positive thinking to solve the problem

DECLARE @math decimal (18,2)

Select @math =max (shufen) from Fenshu

DECLARE @sd int

Select @sd =code from Fenshu where Shufen [email protected]

Select *from Xuesheng where [email protected]-to execute the following statement, first note this sentence

DECLARE @sex varchar (20)

Select @sex =sex from Xuesheng where [email protected]

If @sex = ' Male '

print ' Handsome '

Else

print ' Beauty '

----While loop

DECLARE @math int

Set @math =80

While @math <95

Begin

print ' Very good ' +cast (@math as varchar (10))--Convert int to String type

Set @[email protected]+1

--Break--while concluding sentence!

If @math =93

Break

If @math >=85 and @math after the <=90--IF statement if it is not begin...end. Default execution of a sentence!

--print ' Congratulations on Fortune '

Begin

print ' Many happy returns long water '

continue--this time continue the function of execution is to return while judging condition!

--If you do not comment ' Congratulations on getting rich ', the continue in Begin......end is to jump out if for while loop, not to continue printing ' HHH '

End

print ' HHH '

End

Database operators and Process Control if,while,break,continue