MsSqlServer statement _ MySQL-mysql tutorial

-- If the score is 100-if the score is 90, select * fromTblScoreselect (casewhentEnglish90then Liang whentEnglish100then excellent end) and math (casewhentMat -- if the score is greater than 100)
-- If the score is greater than 90 good
Select * from TblScore
Select English score =
(Case when tEnglish> 90 then 'Liang' when tEnglish> 100 then 'out' end ), mathematical score = (case when tMath> 90 then 'Liang' when tMath> 100 then 'out' end) from TblScore
-- Second exercise 1 2 3
Select * from user5
Select level = (case when [level] = 1 then 'creance' when [level] = 2 then 'cainiao 'when [level] = 3 then 'great go' end) from user5
-- The third exercise
-- 6000 5500 4500
Select * from MyOrders
Select salesman, total sales amount = SUM (sales quantity * sales price), title = (
Case
When SUM (sales price * sales quantity)> 6000
Then 'gold Med'
When SUM (sales price * sales quantity)> 5500
Then 'sil'
When SUM (sales price * sales quantity)> 4500
Then 'bronz'
Else
'Card pass'
End
) From MyOrders
Group by salesman

-- Income and expenditure
Select * from test
Select number, revenue = (
Case
When amount> 0
Then amount
When amount <0
Then 0
End
), Expenditure = (case
When amount <0

Then ABS (amount)
When amount> 0
Then 0

End) from test

-- Query all English scores and English scores> 90 -- subquery
Select * from (select tEnglish from TblScore) as t where t. tEnglish> 90
-- Query whether the gender of a male is over 20 years old.
Select * from (select * from TblStudent where tSGender = 'male') as t where t. tSAge> 20

-- 1. query all the 24-year-olds in the class (subquery)
Select * from (select * from TblStudent where tSGender = 'male') as t where tSAge = 24
-- 2. query all the students in the first, third, and second classes (subqueries)
Select * from TblStudent where tSClassId in (
Select tClassId from TblClass where tClassName = 'high one class' or tClassName = 'high two class ')

-- 2. check all the students of Phase I and Phase II.
Use MyItcast
Select * from student
Select * from TblClass

Select * from student where TClassId in (select TClassId from TblClass where TClassName = 'Black horse Phase I 'or TClassName = 'Black horse Phase II ')

-- 3. total number of queried students, number of male students, and average mathematical score (subquery)
Total select COUNT (*) from student), number of male students = (select COUNT (*) from student where TSGender = 1 ), average math score = (select AVG (TblScore. TSMath) from TblScore)


-- 9 to 16 pieces of data

Select * from student
Select top 8 * from student where TSId not in (select top 8 TSId from student )--
-- 16 to 26
Select top 8 * from student where TSId not in (select top 15 TSId from student)
Select * from student

Use nononodeleteImportant

Select * from TblStudent

-- Check

Select * from (
Select *, number = ROW_NUMBER () over (order by tSid) from TblStudent) as newTbl where newTbl. number between (5-1) * 3 + 1 and 5*3

-- Query nine data entries on each page
Select * from (
Select number = ROW_NUMBER () over (order by tSId), * from TblStudent) as t where t. number between (13-1) * 9 + 1 and 13*9


Select tMath, ranking = ROW_NUMBER () over (order by tMath) from TblScore
Select tMath, ranking = RANK () over (order by tMath) from TblScore -- rank the same score
Select * from MyOrders
Select product name, row number = ROW_NUMBER () over (partition by product name order by id) from MyOrders -- partition by partition

-- Total sales amount of the salesperson
Select * from MyOrders
Select salesperson, total sales amount = SUM (sales quantity * sales price) from MyOrders
Group by salesman

-- 2. count the percentage of sales amount of each salesperson (order) to the total sales amount.
Select *, sales quantity * sales price,
Percentage = sales quantity * Sales price * 1.0/SUM (sales quantity * sales price) over (partition by salesman) * 100
From MyOrders


-- Link query

-- Can I display the name of the student's class when querying this student?
Select TblStudent. tSName, TblStudent. tSAge, TblStudent. tSGender, TblClass. tClassName from TblStudent
Inner join TblClass
On TblStudent. tSClassId = TblClass. tClassId

-- Query which class the student is in and his or her test scores
Select TblStudent. tSName, TblStudent. tSGender, TblClass. tClassName, TblScore. tEnglish, TblScore. tMath from TblStudent
Inner join TblClass
On TblStudent. tSClassId = TblClass. tClassId
Inner join TblScore
On TblStudent. tSId = TblScore. tSId
-- Create a view
Create view vw_Stu_Cla_Sco_newView
As
Select TblStudent. tSName, TblStudent. tSGender, TblClass. tClassName, TblScore. tEnglish, TblScore. tMath from TblStudent
Inner join TblClass
On TblStudent. tSClassId = TblClass. tClassId
Inner join TblScore
On TblStudent. tSId = TblScore. tSId
--
Select * from vw_Stu_Cla_Sco_newView -- query view
Drop view vw_Stu_Cla_Sco_newView -- delete a view


-- Query the names, ages, and classes of students over 20 years old.

Select TblStudent. tSName, TblStudent. tSAge, TblClass. tClassName from TblStudent
Inner join
TblClass
On
TblStudent. tSClassId = TblClass. tClassId
Inner join
TblScore
On
TblStudent. tSId = TblScore. tSId
Where TblStudent. tSAge> 20

--
-- Query all students (all students who have not taken the test) and their scores
Select * from TblStudent
Inner join TblScore
On TblStudent. tSClassId = TblScore. tSId-students taking the test

Select TblStudent. tSName, TblScore. tMath, TblScore. tEnglish from TblStudent
Left join TblScore
On TblStudent. tSClassId = TblScore. tSId -- students who take the test and those who do not take the test


Select TblStudent. tSName, TblScore. tMath, TblScore. tEnglish from TblStudent
Left join TblScore
On TblStudent. tSClassId = TblScore. tSId
Where TblScore. tSId is null -- students not taking the test

-- Query the names, ages, and scores of all students whose english scores are not null.

Select TblStudent. tSName, TblScore. tMath, TblScore. tEnglish from TblStudent
Inner join TblScore
On TblStudent. tSClassId = TblScore. tSId
Where TblScore. tEnglish is not null -- the English score of the student taking the test is not null.

-- Exercise 3: query the names, ages, and scores of all students (or students not taking the test). If the students do not take the test, the students fail the test.
Use nononodeleteImportant
Select TblStudent. tSName, TblStudent. tSAge, English score = (case
When tEnglish is null
Then 'missing test'
Else
CONVERT (nvarchar, tEnglish)
End), math score = (case
When tMath IS null
Then 'missing test'
Else
CONVERT (nvarchar, tMath)
End), pass = (case when tEnglish> 60 and tMath> 60 then 'pass'
Else 'failed'
End) from TblStudent left join
TblScore on TblStudent. tSId = TblScore. tSId


Select * from TblArea
Select t. AreaId, t. AreaName, t1.AreaName from TblArea as t inner join TblArea as t1 on t. AreaPId = t1.AreaId

-- Declare variables
Declare @ number int;
Set @ number = 30;
Print @ number
Select @ number
If (@ number = 30)
Begin
Print 'haoshuai'
End
Else
Begin
Select 'really disgusting'
End



Declare @ avg int = 0
Set @ avg = (select AVG (tMath) from TblScore)
If (@ avg> 60)
Begin
Select top 3 * from TblScore order by tMath desc
End
Else
Begin
Select top 3 * from TblScore order by tMath asc
End