SQL you must know-query aggregation grouping sorting

Use Myschooltwo
--Simple query
SELECT * FROM Student
This query is more efficient than *.
Select SId, SName, SAge, SNo, Sbirthday, Sclassid, SSex from Student
Select SName from Student
--Renaming a column three different ways
Select SName as ' name ', SAge as ' age ', sNo as ' learn ' from student
Select SName ' name ', SAge ' age ', sNo ' study number ' from student
Select ' Name ' = sName, ' age ' = sAge, ' study number ' = SNo from student
--Add where query condition
Select SName as ' name ', SAge as ' age ', sNo as ' learn ' from student
where SSex = "Woman"
Select SName as ' name ', SAge as ' age ', sNo as ' learn ' from student
where SAge >= 20
--You can also direct select to call SQL Server built-in functions into newid () getdate ()
Select 3
Select GETDATE ()
--top distinict ORDER BY
--top get the first few data
Select Top 2 * from Student where SAge < 20
ORDER BY SAge
--distinct de-duplication Distinct data is repeated for the entire result set, not for a column
Select distinct SName from Student
--exclusion of the same is the relationship between sName and sage that must satisfy all field conditions after distinct
Select distinct SName, SAge from Student
--The first few data are truncated even if they are counted
Select top Percent SName, SAge, SNo
From Student

--Aggregate function
--max min avg sum count
Select MAX (中文版) from score
Select min (中文版) from score
--Be aware that AVG is not calculating null values if it is necessary to calculate the average score of the missing persons, use Sum/count
Select AVG (中文版) from score
Select SUM (中文版)/COUNT (*) from score
Select Count (*) from Student where SSex = ' female '

--With conditional query
Select StudentID from score where english>= 60
Select SName from Student where sage>= and SAge <=30 and SSex = "Male"
--between...and ....
Select SName from Student where sAge between and SSex = "Male"
SELECT * from Student where sclassid = 1 or Sclassid = 4
--in ....
SELECT * from Student where Sclassid in (1, 4)

--Fuzzy query
--left a bit from left to right
Select Left (' 2334 ', 2)
--Retrieve the surname Zhang
SELECT * from Student where left (sName, 1) = ' Zhang '
The--like percent symbol represents one or more characters-the slide bar represents only one character [] representing one of the characters that satisfies it
-Same search for surname Zhang
SELECT * from Student where sName like ' Zhang% '
--The name of the search appears bright
SELECT * from Student where sName like '% bright% '
---Retrieve only the name two words of a name named Zhang
SELECT * from Student where sName like ' Zhang __ '
--Retrieving a certain or Zhang Liang of Zhang Fei
SELECT * from Student where sName like ' [Fly bright]% '
--Test data
Insert into Student (Sclassid, SName, SAge, SSex, SNo, Sbirthday, Sphone) VALUES (4, ' Zhuge Liang ', 20, ' Male ', 2231567880 11, ' 1989-8-8 ', ' 123456 ')
Insert into Student (Sclassid, SName, SAge, SSex, SNo, Sbirthday, Sphone) VALUES (4, ' Zhangliangying ', 20, ' female ', 2231567883 4, ' 1989-8-8 ', ' 123456 ')
--use a like wildcard to limit sphone to six digits
ALTER TABLE student
Add constraint ck_student_sphone check (Sphone like "[0-9][0-9][0-9][0-9][0-9][0-9]")

--NULL to process nulls
--In the database, if a column does not have a value specified, the value is NULL, this null and NULL in C #, NULL in the database means "not known",
--not to say no. Therefore, the Select null+1 result is null because the result of "don't Know" is still "not known".
Select null+ 123--return NULL
--select * FROM score where 中文版! = null; None of the results are returned because the database is also "not known."
Null value in--sql using is NULL, is not NULL
SELECT * FROM student where sphone is null
SELECT * FROM student where sphone are NOT null

The--order by clause is at the end of the SELECT statement, which allows you to specify the sort by one column or columns.
-You can also specify whether the sort order is ascending (from small to large, ASC) or descending (from large to small, DESC).
--Start with the same English ascending English and then press the math ascending order
SELECT * FROM Score
Order BY 中文版, math
--Specify the collation before and after
--According to the English scores from the big to the small sort, if the English result is same, according to the mathematics result from small to big order
SELECT * FROM Score
ORDER BY 中文版 DESC, math asc
--order by must appear after the where
SELECT * FROM Student
where SSex = "male"
ORDER BY SAge

--GROUP BY
--Grouping is the merging of those rows of the same value into one row
--When the query condition contains ' Each ', it is used to group
-How many students are there in each class
--first question the column after select must appear except for the GROUP BY clause aggregate function
Select COUNT (*), sclassid from Student
GROUP BY Sclassid
--The number of male students in each class
Select COUNT (*) as ' number of boys ', sclassid as ' class ' from Student
where SSex = "male"
GROUP BY Sclassid
-average age of each class
Select COUNT (*) as ' number ', sclassid as ' class ', AVG (sAge) as ' average age ' from Student
GROUP BY Sclassid
--The Error aggregation function cannot appear in the WHERE clause
-Must use having and having to be in Group by
--the average age of each class is greater than the total number
Select COUNT (*), Sclassid, AVG (sAge) from Student
where AVG (sAge) >= 20
GROUP BY Sclassid
--correct use of the having to filter the data after group by grouping
--having is a group by condition to filter the data after grouping
--the average age of each class is greater than the total number
Select COUNT (*) as ' total number ', sclassid as ' class ', AVG (sAge) as ' average age ' from Student
GROUP BY Sclassid
Having AVG (sAge) >= 20
Note that you cannot use a column that does not participate in a group, and that having cannot replace where. The effect is different, having is filtering the group.
--To find the total number of people of each class older than the average age
Select COUNT (*), sclassid from Student
where SAge >= (select AVG (SAge) from Student)
GROUP BY Sclassid
SELECT * FROM Student

--Group Exercises
--How many men and women are there to ask for
Select COUNT (*), SSex from Student
GROUP BY SSex
--How many boys are there in each class?
Select COUNT (sId), Sclassid from Student
where SSex = "male"
GROUP BY Sclassid
-average age of male students in each class
Select COUNT (sId), Sclassid, AVG (sAge) as ' average age ' from Student
where SSex = "male"
GROUP BY Sclassid
--For those classes with an average age of less than
Select Sclassid, AVG (sAge) from Student
GROUP BY Sclassid
Having AVG (SAge) < 22

--union Federated Result set
--Basic principle: Each result set must have the same number of columns, and the columns of each result set must be type-compatible
--Sorting to remove duplicate data
Select Tsex, tname from teacher union
Select SSex, sName from Student
The difference between--union all and union is that the union removes duplicate rows and will regroup the sort
--union merges two query result sets and merges fully duplicated rows of data into one
--union is inefficient because it is being scanned for duplicate values, so if you are not sure that you want to merge duplicate rows, use the Union all
Select Tsex, tname from teacher union ALL
Select SSex, sName from Student
Another usage of--union inserting multiple data at a time
Insert INTO teacher (Tname, Tsex, Tage, Tsalary)
Select ' Chicken sister ', ' female ', 2400 union
Select ' Month ', ' female ', 19, 3000

--Request a table to find out the student's highest English, lowest score, average score
Select ' Highest score ', Max from score Union
Select ' Lowest score ', Min (中文版) from score Union
Select ' Average score ', AVG (中文版) from score
--Query Each teacher's information, including name, salary, and at the end of the average salary and maximum wage
Select Tname, tsalary from teacher union ALL
Select ' Average salary ', AVG (tsalary) from teacher UNION ALL
Select ' Maximum wage ', max (tsalary) from teacher
--inserting data from an existing table into a new table (the Newstudent table cannot exist)
SELECT * Into Newstudent from Student
--based on the above logic, newstudent can be implemented as a zero table to remove duplicate data from the table.
Select distinct SAge into newstudent from student

--Copy the data from an existing table to a table that already exists
TRUNCATE TABLE Newstudent
--Two questions to copy data the first Newstudent no identity column does not have a primary key
INSERT INTO Newstudent select * from Student
Insert INTO Newstudent Select SName, SAge, SNo, Sbirthday, Sclassid, SSex, Sphone from Student

SQL you must know-query aggregation grouping sorting