[Leng Feng recommendation]: database operations, internal and external queries, group queries, nested queries, cross queries, multi-table queries, and statement summary.

To make it easier for everyone to understand the SQL statements I have mentioned, this article assumes that a student achievement management database has been established. The full text is described in the case of student achievement management.

1. display the column name in the query results:

A. Use the as Keyword: Select name as 'name' from students order by age

B. Direct representation: Select name 'name' from students order by age

2. Exact search:

A. Use in to specify the range: Select * from students where native in ('hunan ', 'sichuan ')

B. Between... And: Select * from students where age between 20 and 30

C. "=": Select * from students where name = 'hill'

D. like: Select * from students where name like 'Li % '(Note that there is "%" in the query condition, it indicates that it is partially matched and there is information in it successively, that is, search for matching items starting with "Li. Therefore, to query all objects with "Li", run the following command: '% Li %'. If the second word is Li, it should be '_ Li %', '_ li', or '_ Li _'.)

E. [] match check character: Select * from courses where CNO like '[AC] %' (The Relationship Between or and "in (...) "similar, and" [] "can represent a range, such as: Select * from courses where CNO like '[A-C] % ')

3. Processing Time type variables

A. smalldatetime: Process strings directly, for example:
Select * from students where birth> = '2017-1-1 1' and birth <= '2017-12-31'

4. Set Functions

A. Count () summation, for example: Select count (*) from students (total number of students)

B. AVG (column) calculates the mean, for example, select AVG (Mark) from grades where CNO = 'b2'

C. Max (column) and min (column), Max and min

5. Group

Used for statistics, such as the total number of group queries:
Select gender, count (SNO)
From students
Group by gender
(Check the number of male and female students)

Note: from which point of view the group is, from which column "group"

For multiple groups, you only need to list the grouping rules. For example, to query the number of male and female students in each major, the grouping rules are: grade, MnO, and Gender. Therefore, "group by grade, MnO, gender"

Select grade, MnO, gender, count (*)
From students
Group by grade, MnO, Gender

Normally, group is also used with having. For example, if a student who fails to take more than one course is queried, the student IDs are classified by student ID (SNO) as follows:

Select SnO, count (*) from grades
Where Mark <60
Group by SnO
Having count (*)> 1

6. Union

Merge query results, such:

Select * from students
Where name like 'sheet %'
Union [all]
Select * from students
Where name like 'Li %'

7. Multi-Table query

A. Internal Connection

Select G. Sno, S. Name, C. coursename
From grades g join students s on G. Sno = S. SnO
Join Courses c on G. CNO = C. CNO
(Note that aliases can be referenced)
B. External Connection
B1. left join
Select courses. CNO, max (coursename), count (SNO)
From courses left join grades on courses. CNO = grades. CNO
Group by courses. CNO

Left join feature: displays all items in all left tables, even if some of the items are not completely filled in.

The left Outer Join returns the rows that exist in the left table but do not exist in the right table, plus the rows of the inner join.

B2. right join

Similar to left join

B3. full connection

Select SnO, name, Major
From students full join majors on students. MnO = majors. MnO

Show all content in both tables

C. Self-connection

Select c1.cno, c1.coursename, c1.pno, c2.coursename
From courses C1, courses C2 where c1.pno = c2.cno

Use aliases to solve the problem.

D. Cross join

Select lastname + firstname from lastname cross join firstanme

Equivalent to playing the flute Product

8. nested Query

A. Use the keyword in, for example, to query the hometown of Li Shan:

Select * from students
Where native in (Select native from students where name = 'hill ')

B. Use the keyword exist. For example, the following two sentences are equivalent:

Select * from students
Where SnO IN (select SnO from grades where CNO = 'b2 ')

Select * from students where exists
(Select * from grades where
Grades. Sno = students. SnO and CNO = 'b2 ')

9. sorting order

A. There are two methods for sorting order: ASC Ascending Order and desc descending order.

B. sorting order can be arranged according to a certain item in the query condition, and this can be represented by numbers, such:

Select SnO, count (*), AVG (Mark) from grades
Group by SnO
Having AVG (Mark)> 85
Order by 3

10. Others

A. names with spaces should be enclosed.

B. You can use null to determine specific queries without data in a column, such as select SnO and courseno from grades where Mark is null.

C. Differentiate the differences between any and all used in nested queries. Any is equivalent to the logical operation "|", while all is equivalent to the logical operation "&".

D. Be careful when querying negative meanings:

For example, students who have not taken the 'b2' course:

Select students .*
From students, grades
Where students. Sno = grades. SnO
And grades. CNO <> 'b2'

The preceding query method is incorrect. For the correct method, see the following:

Select * from students
Where not exists (select * from grades
Where grades. Sno = students. SnO and CNO = 'b2 ')

11. Solution to multiple difficult nested queries:

For example, students who take all courses:

Select *
From students
Where not exists (select *
From courses
Where not exists
(Select *
From grades
Where SnO = students. SnO
And CNO = courses. CNO ))

Exclusive: select from the student table to exclude those with no courses. Use not exist. Because the subject is a course, you can find the second query in the Course table and exclude those that have selected the course.