For everyone, it is more likely to understand the SQL statement I will, this article assumes a student performance management.
Database, full text is described as an example of management of student results.
1. Display the column name in the query results:
a. Use the AS key: SELECT NAME AS 'Name' from students Order by agge
b. Directly, SELECT NAME 'Name' from students Order by agge
2. Exactly Find:
a. Use IN limited scope: Select * from Students Where Native in ('Hunan', 'Sichuan')
B.between ... and: select * from students where agnween 20 and 30
c. "=": select * from students where name = 'Lee Mountain'
D.Like :Select * from students where name like 'Lee%' (Note "%" in query conditions, the description is partial match, and there is also a successful information, that is, look for match items starting with "Lee". So if you find all the objects of "Li", you should command: '% Li%'; if the second word is Li, it should be '_ Lee%' or '_ Lee' or '_ Lee _'.)
e. [] Matching Accurat: Select * from coursees where cn like '[ac]%' (representative or relationship, "in (...)" is similar, and "[]" can represent the range, such as: SELECT * From coursees where cn like '[ac]%')
3. Processing for time type variables
A.SMallDateTime: Directly in accordance with the string processing, for example: Select * from students where birth> = '1980-1-1' and birth <= '1980-12-31'
4. Contest
A.count () begging, such as: select count (*) from students (seeking the total number of students)
B. AVG (column) Ask average, such as: SELECT AVG (Mark) from grades where cno = 'b2'
C.max (column) and min (column), seeking maximum and minimum
5. Group group
Commonly used in statistics, such as the total number of groups: SELECT GENDER, Count (SNO) from studentsgroup by GenDER (see how many men and women students)
Note: Which angle group is from "Group By"
For multiple grouped, you only need to list the group rules. For example, check the number of male and female students in each major, then group rules: GRADE, professional (MNO) and gender, so there is "group by grade, mno, gender"
Select Grade, Mno, Gnder, Count (*) from studentsgroup by grade, mno, gender
Usually Group also uses Having, such as students who are not as good as in 1 less class, and classified according to the student (SNO):
Select Sno, Count (*) from grades where mark <60group by snohaving count (*)> 16.Union United
Merge query results, such as:
Select * from studentswhere name name Like 'Zhang% Union [all] select * from studentswhere name Like' Lee% '
7. Multi-table query
a. internal connection
Select G. Sno, S.Name, C.Coursename from grades g Join Students S on g.sno = s.snojoin coursees c on g.cno = c.cno (Note You can reference alias) b. Outer connection B1. Left connection Select Courses.cno, Max (CourseName), Count (SNO) from coursees left join grades on coursees.cno = grades.cno group by coursees.cno
Left connection Features: Displays all items in all left tables, even in some of them, the data 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
Similar to the left connection
B3. full connection
Select Sno, Name, Major from students full join majors on students.mno = majors.mno
The content in both sides is displayed
c. Self connection
Select c1.cno, c1.courseename, c1.pno, c2.courseename from coursees C1, Courses C2 where c1.pno = c2.cno
Use an alias to solve the problem.
d. Cross connection
SELECT Lastname Firstname from Lastname Cross Join Firstname
Equivalent to Dai Cartesi
8. Nested inquiry
a. Use the keyword in, such as Querying Li Mountain:
Select * from studentswhere native in (select native from students where name = 'Li Mountain))
b. Use the keyword exist, for example, the following two sentences are equivalent:
Select * from stay1re 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 Sort Order
a. For the order of order, there are two methods: ASC ascending sequence and DESC descending order
b. For Sort Order, you can arrange it in a query condition, and this available numbers are indicated, 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 identification, "[]" 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 Null
c. Note that the ANY and ALL used in nested queries are distinguished, and ANY is equivalent to logical operation "||" and all is equivalent to logical operation "&&"
d. Note that the inquiry in the negative meaning is careful to enter the trap:
For example, there is no student in the 'B2' course:
Select students. * from students, gradeswhere students.sno = grades.snoand grades.cno <> The query method above the above query is wrong, the right way is seen below:
Select * from studentswhere NOT EXISTS (Select * from grades where grades.sno = students.sno and cno = 'b2')
11. Solve ideas for multiple nesting queries:
For example, students who have elected all the courses:
Select * from studentswhere NOT EXISTS (SELECT * from co gradeswhere not exists (select * from gradeeswhere sno = students.snoand cn = coursees.cno))
The outermost weight: Selected from the student watch, eliminating 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.
