The whole article is described as an example of management of student results.
1. Display the column name in the query results: a. Use the AS keyword: select name as 'name' from students order by Ageb. Direct Representation: SELECT NAME 'Name' from students Order by agge
2. Exactly Find: a. Separation: Select * from Students Where Native in ('Hunan', 'Sichuan') B.between ... and: select * from students where agnween 20 and 30c. "=" : Select * from students where name = 'Lee's' D.Like :select * from students where name like 'Lee%' (note that "%" in query conditions, the description is partial match, and there is still information That is, look for match items starting with "Li". So if you query all objects with "Li", you should command: '% Li%'; if the second word is Li, it should be '_ Lee%' or ' _ 李 'or' _ 李 _ '.) E. [] Matching Accurat: Select * from coursees where cn like' [ac]% '(representative or relationship, "in (...)", and "[]" Can represent the range, such as: SELECT * from co coursees where cn like '[ac]%')
3. Processing for time type variables A.SMALLDATETIME: Directly followed by string processing, for example: Select * from students where birth> = '1980-1-1' and birth <= '1980-12-31'
4. Set function a.count () summary, such as: select count (*) from students (the total number of students) B.avg (column) Seeking average, such as: SELECT AVG (Mark) from grades where cno = 'b2 'c.max (column) and min (column), seeking maximum and minimum
5. Group Group is often used in statistics, such as the total number of groups: SELECT GENDER, COUNT (SNO) from Students Group by Gnder (see how many men and women students) Note: From which angle group is from "Group by" Multiple grouping, just put the group rules. For example, inquiring the number of male and female students in each major, the group rules are: GRADE, professional (MNO) and gender, so there is "GROUP BY GRADE, MNO, GENDER" Select Grade, Mno, Gender, Count (*) from StudentsGroup by Grade, Mno, Gnder usually group is also available, such as students who are not as good as in 1 class, and classified by learning (SNO): SELECT SNO, Count (*) from grades WHERE MARK <60Group by Snohaving Count (*)> 1
6. UNION combined with consolidation results, such as: select * from studentswhere name like 'Zhang% Union [all] Select * from studentswhere name Like' Lee% 'Note Union and UNION ALL:
The former should make a repetition to filter, only a repetition information is displayed in the results; and the latter regardless of. So you can see that the algorithms used by UNION ALL are different, and the latter is simple. From the perspective of tuning, recommend Union ALL so that query efficiency will increase much.
7. Multi-table query a. Inside connection Select G. Sno, S.Name, C.Coursename from grades g Join Students S on g.sno = s.sno join coursez c on g.cno = c.cno (Note can be referenced) Alias) b. Outer connection B1. Left connection Select Coursetes.cno, Max (CourseName), Count (SNO) from coursees left join grades on coursees.cno = grades.cno group by coursees.cno Left connection Features: Show all left table All items in it, even if the data in some of them is not completed.
The left outer connection returns the rows that exist in the left table, plus the rows connected in the inner connection. B2. Right connection and left connection Similar to B3. Full connection Select Sno, Name, Major from studys full join majors on students.mno = majors.mno two sides table content all display C. Self connection Select C1.cno, c1.courseename , c1.pno, c2.courseename from coursees C1.PNO = C2.cno uses an alias to solve the problem. d. Cross-connected Select Lastname Firstname from Lastname Cross Join FirstName is equivalent to Diki card
8. Nested query a. With the keyword IN, if Queans' homewhered: select * from studentswhere native in (select native from students where name = 'Li Mountain') b. Use Keywords EXIST, such as two sentences Is equivalent: SELECT * from homeswhere Sno in (Select Sno from grades where cno = 'b2')
Select * from students where exissrs (select * from grades where grades.sno = students.sno and cno = 'b2')
9. About ordering Ordera. For the ordering order, there are two methods: ASC ascending and DESC descending order b. For sorting Order, you can arrange it in a query condition, and this available digital representation, such as: SELECT SNO, Count *), AVG (Mark) from grades group by Snohaving Avg (Mark)> 85Order by 3
10. Others a. For the name of the space, "[]" should be horn. b. For specific queries that do not have data in a column can be judged with NULL, such as Select Sno, CourseNo from grades where mark is nullc. Note that the ANY and ALL used in nested queries, Any is equivalent to logical operations | "And all is equivalent to logical operation" && "d. Note that the inquiry query is careful to enter the trap: . Sno and grades.cno <> The above query method is wrong, correctly below: Select * from studentswhere not exists (select * from grades where grades.sno = students.sno and cno = 'b2') 11. Solve ideas for difficulty multiple nested queries: For example, students who have been repaired in all courses: SELECT * from stays where not exists (Select * from co grades where sno = students.sno and cno = Coursetes.cno) The outermost weight: from the student watch, exclude those lessons. Use not exist. Since the discussion object is a course, the second weight is looking for from the Course table to exclude those who choose the class.
