Output, usage of variables, subqueries, logical statements, loops, case... When... then... end multi-branch statements, exists (judgement)

-------------- Output ----------------
Print 'Hello world' -- output in text format
Select 'Hello world' -- output in Grid Format or in text format
Print 'abc' + 'cde'
Print 3 + 5
Print 'AB' + 5 -- error, 'AB' cannot be converted to int
Print 'AB' + convert (varchar, 5) -- output AB5
Print '2' + 5 -- output 7, because '2' can be automatically converted to integer data 2

---------------- Local variable -------------------
-- Declare local variables
Declare @ age int
--- Set value assignment (set can only assign values to one local variable at a time)
Set @ age = 22
Set @ [email protected] + 5
Print @ age

-- Select value (select can assign values to multiple local variables at a time)
Declare @ stuage int
Declare @ stuname nvarchar (20)
Select @ stuname = stuname, @ stuage = stuage from stuinfo where stuno = 's25302'
Print 'name: '[email protected] +' age: '+ convert (varchar, @ stuage)
-- Note: When the select value is used, it is better to find a row of data rows. If multiple rows are found, values of the last row are assigned.


---------------- Global variable ------------------
Print @ version -- version information
Print @ servername -- Name of the local server
Insert into stuinfo values ('zhang san', 's25305 ', 'male', 23, 'shantou ')
Print @ error -- the error number of the last t-SQL statement. (If the last t-SQL statement fails to be executed, the value of @ error is greater than 0. The execution is successful, @ error is equal to 0)
Print @ identity -- get the value of the ID column of the last inserted row
Update stuinfo set stuage = 32 where stuage = 22
Print @ rowcount -- number of rows affected by the previous SQL statement

--------------- If-else branch structure ---------------------
Use netbardb
Declare @ PCID int -- computer number
Set @ PCID = 3
Declare @ pcuse int -- computer status
Select @ pcuse = pcuse from pcinfo where [email protected]
If (@ pcuse> 0)
Begin
Print convert (varchar, @ PCID) + 'is in use! '
End
Else
Begin
Print convert (varchar, @ PCID) + 'is idle! '
End


------------ While loop statement --------------
-- Complete: Internet cafe feedback Service
Use netbardb
Declare @ count int -- number of users whose storage balance is less than 20
Update cardinfo set cardbalance = cardbalance + 50 where datediff (day, transacttime, getdate ()> = 365
Update cardinfo set cardbalance = cardbalance + 10 where datediff (day, transacttime, getdate () <365
While (1 = 1)
Begin
Select @ COUNT = count (*) from cardinfo where cardbalance <20 -- find the number of rows with less than 20 yuan in balance
If (@ count> 0)
Begin
Update cardinfo set cardbalance = cardbalance + 1
End
Else
Begin
Break
End
End
Go


--------- Case... When... then... end multi-branch statement -----------
-- Finished: Computer status problems
-- Method 1: Union
Select *, 'id' as status from pcinfo where pcuse = 0
Union
Select *, 'use' from pcinfo where pcuse = 1
-- Method 2: Case... When... then... end
Select *,
Status = case
When pcuse = 0 then 'id'
When pcuse = 1 then 'use'
Else 'error status'
End
From pcinfo


-------------- Subquery ------------------
-- Completion: Student Information older than 'lisiven'
-- Method 1: ordinary T-SQL
Use studb
Declare @ age int
Select @ age = stuage from stuinfo where stuname = 'liyun' -- get the age of 'liyun' first
Select * From stuinfo where stuage> @ age -- then use the age of 'liven' as the filter condition.
-- Method 2: subquery
Select * From stuinfo where stuage>
(Select stuage from stuinfo where stuname = 'lisboa ')
-- Note: When Using subqueries together with Relational operators such as <,>, <=,> =..., make sure that the subquery returns no more than one value. Otherwise, an error is returned.

-- Completed: the student information with the test score of 60
-- Method 1: table join
Select stuname from stuinfo join stumarks
On stuinfo. stuno = stumarks. stuno
Where stumarks. writtenexam = 60
-- Method 2: subquery
Select stuname from stuinfo where stuno in
(Select stuno from stumarks where writtenexam = 60)

-- Complete: query the list of trainees who have taken the test.
Select stuname from stuinfo where stuno in
(Select stuno from stumarks)
-- The preceding SQL statement is equivalent to: Select stuname from stuinfo where stuno in ('s25303', 's25302', 's25301 ')

-- Complete: query the list of students not taking the test.
Select stuname from stuinfo where stuno not in
(Select stuno from stumarks)


-------------- Use of exists -----------------------
-- Syntax: exists (subquery)
-- Return Value: If the subquery can find data, true is returned. If the subquery cannot find data, false is returned.
Use studb
If exists (select * From stumarks where writtenexam> 80) -- determines whether there is a written test with over 80 points
Begin
Update stumarks set writtenexam = writtenexam + 2
End
Else
Begin
Update stumarks set writtenexam = writtenexam + 5
End

-------------- Use of not exists -----------------------
-- Syntax: Not exists (subquery)
-- Return Value: If the subquery can find data, false is returned. If the subquery cannot find data, true is returned.
Use studb
If not exists (select * From stumarks where writtenexam> 60 and labexam> 60)
Begin
Update stumarks set writtenexam = writtenexam + 3, labexam = labexam + 3
End
Else
Begin
Update stumarks set writtenexam = writtenexam + 1, labexam = labexam + 1
End
Go

Output, usage of variables, subqueries, logical statements, loops, case... When... then... end multi-branch statements, exists (judgement)