17th Lesson-Database development and ADO aggregation function, fuzzy query like, wildcard. NULL handling. Order by sort. Group by-having. type conversion-cast,convert.union all; Select column into new table; string function; Date function

Section - class - database Development and ADO

Aggregation Functions , Fuzzy Query Like , wildcard characters . Null value processing . Order by Sort . Grouping group by-having. Type Conversions  -cast,convert.union All; Select column into new table ; String Functions ; Date Function

SQL aggregate functions

Max (max), min (min), AVG (average), SUM (and), Count (Qty: Number of records)

The aggregate function is not evaluated for NULL. If a row of data is Null,count (*) contains statistics for null rows and duplicate rows.

--Aggregation function Demo

SELECT * FROM Tblstudent

--cast Convert???

-Average age

Select AVG (tsage*1.0) as average from tblstudent

Select AVG (tsage*1.0) average from tblstudent

Select Average =avg (tsage*1.0) from tblstudent

--error. When using aggregate functions, be aware that in the Select query list, do not appear in addition to the use of the aggregate function columns,

--Unless the column also uses an aggregate function or the column is included in the GROUP BY clause.

Select Average =avg (tsage*1.0), tsname from Tblstudent

--Summation

Select SUM (tsage) from tblstudent

--Seek maximum value

Select MAX (tsage) from tblstudent

--Minimum value

Select min (tsage) from tblstudent

--Total number of bars

Select COUNT (*) from tblstudent

--The tsid=32 and tsid=30 tsage=null

Update tblstudent set tsage=null where tsid=32 or tsid=30

--

Select COUNT (tsage) as record number from tblstudent

Select

COUNT (Tsage),

SUM (Tsage),

MAX (Tsage),

MIN (Tsage),

AVG (Tsage)

From Tblstudent

SELECT * FROM tblstudent--primary key table

SELECT * FROM tblscore--foreign key table

--Check the number of students who have failed in English

Select Tsid from Tblscore where Tenglish <60

--check for male students aged between 30 years

SELECT * from Tblstudent where tsage<=30 and tsage>=20 and tsgender = ' Male '

--not and OR is a logical operator with priority Not→and→or

--between...and ... In between

--check for male students aged between 30 years

SELECT * from Tblstudent where tsage between and 30

--Query math score between 70 and all students

Select Tsid from Tblscore where Tmath between and 70

--Query the class ID for all students in 2,3

SELECT * from Tblstudent where Tsclassid in (all-in-a-

SELECT * from tblstudent where Tsclassid =1 or Tsclassid =2 or Tsclassid =3

-If the above can be optimized for the following wording, try to use the following notation.

SELECT * from Tblstudent where Tsclassid >=1 and Tsclassid <=3

Fuzzy queries (both for string manipulation)

--fuzzy queries, wildcard characters

SELECT * FROM Tblstudent

--The previous query used: =

SELECT * from tblstudent where Tsname =n ' Zhang Ben '

--The first wildcard% represents any number of arbitrary characters.

--When using wildcard characters, you must use the like

SELECT * from Tblstudent where tsname like N ' sheet% '

--by [] to include%, then% does not represent a wildcard, but instead represents a normal character

SELECT * from Tblstudent where tsname like N '%[%]% '

--Wildcard: _ Denotes (single) an arbitrary character.

SELECT * from Tblstudent where tsname like N ' Marten _ '

SELECT * from Tblstudent where tsname like N ' Marten% ' and LEN (tsname) =2

--wildcard character: []

SELECT * from Tblstudent where tsname like N ' Zhang _ Mei '

SELECT * from Tblstudent where tsname like N ' Zhang [0-9] sister '

SELECT * from Tblstudent where tsname like N ' Zhang [A-z] sister '

--like and not-like [^] wildcard usage Note distinguish

SELECT * from Tblstudent where tsname like N ' Zhang _ Mei '

SELECT * from Tblstudent where tsname don't like N ' Zhang _ Mei '

SELECT * from Tblstudent where tsname don't like N ' Zhang [^0-9] sister '

Null value processing

--Null value processing

--null

SELECT * FROM Tblstudent

Update tblstudent set Tsname =null where Tsid=1--represents a null value for the database

Update tblstudent Set tsname = ' where tsid=2--represents a zero-length string

--Please check out all student records that are tsage null (the following two are wrong.) ）

SELECT * from tblstudent where Tsage =null

SELECT * from tblstudent where Tsage <>null

--The null value in the database is more special, which represents a unknow value. A value that is not known.

--The null value cannot be judged by = or <> in the database, and the null value can only be judged by using a special operator is.

SELECT * from tblstudent where tsage is null

SELECT * from tblstudent where tsage are NOT null

--isnull (), which indicates that a function distinguishes above is NULL

--null and any result of the operation are null

17th Lesson-Database development and ADO aggregation function, fuzzy query like, wildcard. NULL handling. Order by sort. Group by-having. type conversion-cast,convert.union all; Select column into new table; string function; Date function