Unit 13 Process Control and Functions

Unit 13 Process Control and Functions

1. Select structure

If(...)
 Begin
 ​
 statement block
 ​
 End
 ​
 else if  span>(...)
 Begin
 ​
 statement block
 ​
 End
 ​
 Else
 ​
 Begin
 ​
 statement block
 ​
 End;
  

Notes

The syntax of begin..end is equivalent to {} in C#

When there is only one execution statement, begin..end can be omitted

() can be omitted, but if the condition contains a select statement, () cannot be omitted

Use = in the database to indicate equality

2. Case structure

-- This syntax can only be used when the condition is to judge equality.
 case field
     when condition 1 then result
     when condition 2 then result
     else results
 end

 or

 case
     when condition 1 then result
     when condition 2 then result
     else results
 end
 example:

 selectId,NickName  ,Mobile Age,
 case Sex
 when 0 then '  Male'Male  span>
 when 1 then '  Female'  span>
 when 2 then '  Confidential'  span>
 when 3 then '  Unknown'  span>
 end gender
  from UserInfo

select *,

  case

  when Age<=10 then 'Childhood  '

  when Age between 11 and 25 then '  Teenager'

  when Age between 26 and 40 then '  Youth'

  when Age between 41 and 59 then '  Middle-aged'

  else 'Number
BigInt
√
 
 
 
 


NickName
Name
varchar
 
√
 
 
Length 2-10


Mobile
Mobile phone number
Varchar
 
√
√
 
Length 11


Sex
Gender (0: male, 1: female, 2: confidential, 3: unknown)
tinyint
 
√
 
0
0-3


Age
Age
tinyint
 
√
 
10
 


CreateTime
Add time
datetime
 
√
 
Getdate()
 



Order Form (OrderInfo)



Field name
Description
Type
not empty  
Default value
Check
Foreign key




Id
Number
bigint
 
 
 
 


UserId
Order user number
bigint
 
 
 
User table (Id), cascade update, delete


TotalPrice
Total order price
Decimal(10,2)
√
0
>=1
 


TotalCount
Total quantity of items
Int
√
1
>=1
 


CreateTime
Order time
datetime
√
Getdate()
 
 


OrderState
Status (0: Not paid, 1: Not shipped, 2: Awaiting receipt, 3: Awaiting evaluation, 4: Already  Completed)
tinyint
 
0
0-4
 






Create a database named: Unit 15 Exercise Database


Establish the table structure correctly and pay attention to table constraints


Insert 10 test data into each table


Query the student table and display gender as male, female, unknown, confidential


Query the order table and display the order status as unpaid, undelivered, pending receipt, pending evaluation, completed  


Display the name of the user table (remove spaces), age, gender, group category (age: 0-10 children, 10  -20 teenagers, 20-35 young people, 35-60 middle-aged people, 60 and above old people)


To query user information, use * in the middle 4 digits of the mobile phone number, such as 1384843


View order data for the last 3 months


View the latest order data after 2017 and count the number of orders for each user


Query the information of users with the same mobile phone number


Query user data in the past year, and replace the surname with the compound surname "Linghu" with "Ling"


Customize a function, pass in the values ​​​​of the three sides of the triangle, and output the shape of the triangle (ordinary triangle, right triangle,  Isosceles right triangle, equilateral triangle, isosceles triangle), for example, pass in: 3,4,5 to output a right triangle (just output text)


Create a function to output the sum of even numbers from 1 to 100


Output the number of daffodils within 1000.


Video supporting link: SQL Server Advanced Programming - NetEase Cloud Classroom (163.com)

          The sea is broad and the fish are leaping, the sky is high and I can do whatever I want. Give me a piece of blue sky and let me fly freely.
>=1
 


CreateTime
Order time
datetime
√
Getdate()
 
 


OrderState
Status (0: Not paid, 1: Not shipped, 2: Awaiting receipt, 3: Awaiting evaluation, 4: Already  Completed)
tinyint
 
0
0-4
 






Create a database named: Unit 15 Exercise Database


Establish the table structure correctly and pay attention to table constraints


Insert 10 test data into each table


Query the student table and display gender as male, female, unknown, confidential


Query the order table and display the order status as unpaid, undelivered, pending receipt, pending evaluation, completed  


Display the name of the user table (remove spaces), age, gender, group category (age: 0-10 children, 10  -20 teenagers, 20-35 young people, 35-60 middle-aged people, 60 and above old people)


To query user information, use * in the middle 4 digits of the mobile phone number, such as 1384843


View order data for the last 3 months


View the latest order data after 2017 and count the number of orders for each user


Query the information of users with the same mobile phone number


Query user data in the past year, and replace the surname with the compound surname "Linghu" with "Ling"


Customize a function, pass in the values ​​​​of the three sides of the triangle, and output the shape of the triangle (ordinary triangle, right triangle,  Isosceles right triangle, equilateral triangle, isosceles triangle), for example, pass in: 3,4,5 to output a right triangle (just output text)


Create a function to output the sum of even numbers from 1 to 100


Output the number of daffodils within 1000.


Video supporting link: SQL Server Advanced Programming - NetEase Cloud Classroom (163.com)

          The sea is broad and the fish are leaping, the sky is high and I can do whatever I want. Give me a piece of blue sky and let me fly freely.