aggregate function: sum,avg,max,min,count; fuzzy query; sort

----aggregate function--do the calculations to do statistics on NULL filtering: null is the value that you don't know, so you can't do the calculation
--sum (Parameters): statistics and
--avg (parameter): averaging
--max (parameter): Maximum value
--min (parameter): Minimum value
--count (parameter): Gets the number of records that meet the criteria
--1. Total number of participants
Select COUNT (Email) from Student
--2. Get the maximum age value the greater the age, the smaller the age, the older
Select MAX (borndate) from Student
Select min (borndate) from Student
--3. Consider the average score for account ID 1
Select AVG (studentresult) from Result
--Get the test scores for students with the number 2
Select SUM (studentresult) from Result where studentno=2

---Description: Requirements for parameters
count:*, field, constant value, will filter out null
Max.. Min: You can aggregate any type of data, and if it is a non-numeric value, it will be aggregated in the sort order of the number letters, etc.
Select MAX (email) from Student where Email was not null
Select min (studentname) from Student
--sum. AVG can aggregate only on numeric types, not on date values
Select AVG (examdate) from Result
Select AVG (studentname) from Student

--Conditional Query--where
--Check the student information of age between 1990---1998
SELECT * from Student where borndate>= ' 1990-12-31 ' and borndate<= ' 1997-1-1 '
--the numeric or date range can also be optimized using between and equivalent >= <=. Higher efficiency
SELECT * from Student where borndate between ' 1990-12-31 ' and ' 1997-1-1 '

--Query student information for class ID 1,2,3,4,5
SELECT * from Student where ClassId =1 or classid=2 or classid=3 or classid=4 or classid=5
--Specifies that a specific scope can use the numeric type contained in the in Inside to be consistent
SELECT * from Student where ClassId in (1,2,3,4, ' a ')
--Query the class ID is not 12345 of the student information
SELECT * from Student where ClassId <>1 and classid<>2 and Classid<>3 and Classid<>4 and CLASSID&L T;>5
SELECT * from Student where ClassId not in (1,2,3,4, ' 5 ')

--Fuzzy query
--Query "Zhang" surname learner = is a strict content matching
SELECT * from Student where Studentname= ' Zhang '
--Wildcard characters:
--%:.* *: Represents any arbitrary character
--_: Represents any one character
--[]: Can represent a specified range or value of a character [0-9a-za-z] [13579]
--[^]: Take a value outside the range
SELECT * from Student where studentname= ' Zhang% '
--wildcard characters must be in the fuzzy query is the special meaning of the wildcard must use the keyword: like: .... The same
SELECT * from Student where studentname like '% sheet% '
SELECT * from Student where studentname like ' Zhang __ '
--Query student information between 11~15 number
SELECT * from Student where Studentno like ' 1[1-5] '
SELECT * from Student where studentname don't like ' sheet% '
SELECT * from Student where studentname like '%% '

--null refers to what values are not known, so null cannot be evaluated by the time it is null to be NOT NULL
Use of the--isnull () function: Replace with a custom string for display if it is null
Select Studentname,isnull (email, ' not filled in email ') from Student where classid=2

--Data sorting: Just a rearrangement of the records of the query's result set
--select Field List/* from table list where condition order by sort field List default is ascending ASC descending: desc
SELECT * from Student ORDER by sex ASC, Studentno desc

--1. Inquiry for all students surnamed Chen in the six-period class-student
Select ClassId from Classes where Classname= ' seven class '
SELECT * from Student where studentname like ' Chen% ' and classid=5
--Advanced Query---sub-query
SELECT * from Student where studentname like ' Zhang% ' and classid= (select ClassId from Classes where classname= ' seven period ')
--2. Query for account information that contains C characters in all accounts subject
SELECT * from Subject where subjectname like '%c% '
--3. Query office last exam time result
Select MAX (examdate) from the Result where subjectid= (select Subjectid from Subject where subjectname= ' office ')
Select top 1 examdate from Result where subjectid= (select Subjectid from Subject where subjectname= ' office ') Order by Exam Date desc

aggregate function: sum,avg,max,min,count; fuzzy query; sort