Database operation, internal and external query, grouped query, nested query, cross query, multiple table query, statement summary. __ Database

In order to make it easier for everyone to understand the SQL statement I cited, this article assumes that a student performance management database has been established, and the full text is described as an example of student performance management.

1. Display column names in query results:

A. With AS keyword: select name as ' name ' from students

B. Direct representation: Select name ' Name ' from students

2. Accurate Search:

A. In limited scope: SELECT * from students where native in (' Hunan ', ' Sichuan ')

B.between...and:select * from students where age between 30

C. " = ": SELECT * from students where name = ' Li Shan '

D.like:select * from students where name like ' Li% ' (note that there is a "%" in the query condition, the description is a partial match, and there is a sequence of information in it, that is, to find a match that starts with "Li".) Therefore, if the query has "Lee" all the objects, should be ordered: '% Li '; if the second word for Lee, it should be ' _ Lee% ' or ' _ Lee ' or ' _ li _ '. )

E.[] Matching Inspector: SELECT * from courses where CNO like ' [ac]% ' (expression or relationship, with "in (...)" Similarly, and "[]" can represent a range, such as: SELECT * from courses where CNO like ' [a-c]% ']

3. For the processing of time type variables

A.smalldatetime: processing is handled directly by string, for example:
SELECT * from students where birth > = ' 1980-1-1 ' and birth <= ' 1980-12-31 '

4. Set function

A.count () sum, such as: SELECT COUNT (*) from students (total number of students)

B.avg (column) averaging, such as: Select AVG (Mark) from grades where cno= ' B2 '

C.max (column) and min (column), for maximum and minimum

5. Grouping Group

Often used in statistics, such as the total number of group search:
Select Gender,count (SNO)
From students
GROUP BY gender
(See how many male and female students are)

Note: From what angle is the column "group by" grouped from

For multiple groupings, you simply list the grouping rules. For example, to inquire about the number of male and female students in each professional, then the group rules are: the session (grade), Professional (MNO) and gender (gender), so there is "group by grade, MNO, gender"

Select grade, MNO, Gender, COUNT (*)
From students
Group by grade, MNO, gender

Usually group is also associated with having, for example 1 students who fail to pass the course, according to the school number (SNO) categories are:

Select Sno,count (*) from grades
where mark<60
GROUP BY Sno
Having Count (*) >1

6.UNION Union

Merge query results, such as:

SELECT * FROM students
WHERE name like ' Zhang% '
UNION [All]
SELECT * FROM students
WHERE name like ' Lee% '

7. Multi-Table Query

A. Intra-link

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 you can refer to aliases)
B. Outer joins
B1. Left connection
Select Courses.cno,max (Coursename), COUNT (Sno)
From courses left JOIN grades on COURSES.CNO=GRADES.CNO
GROUP BY Courses.cno

Left JOIN features: All items in the left table are displayed, even if the data in some of these items is not filled in completely.

The left OUTER join returns rows that are present in the left table but not in the right table, plus the rows in the inline connection.

B2. Right connection

Similar to the LEFT join connection

B3. Full connection

Select Sno,name,major
From students full JOIN majors on Students.mno=majors.mno

The contents of both tables are displayed

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 problems.

D. Cross-linking

Select Lastname+firstname from LastName CROSS JOIN Firstanme

The equivalent of a Cartesian product.

8. Nested queries

A. Use the keyword in, such as query Li Shan's fellow:

SELECT * FROM Students
where native in (select native from students where Name= ' Li Shan ')

B. Use 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. About sort order

A. For a sort order, there are two ways: ASC Ascending and DESC Descending

B. For a sort order, you can arrange it according to an item in the query criteria, and this is available as a numeric representation, such as:

Select Sno,count (*), AVG (Mark) from grades
GROUP BY Sno
Having AVG (Mark) >85
ORDER BY 3

10. Other

A. For distinguished names with spaces, you should enclose them with "[]".

B. A specific query with no data in a column can be judged by NULL, such as Select Sno,courseno from grades where Mark is null

C. Note the distinction between any and all used in nested queries, any equivalent to the logical operation "| |" And all is equivalent to the logical operation "&&"

D. Note that in doing negative queries the query is carefully entered into the trap:

For example, there are no students who are enrolled in the ' B2 ' course:

Select students.*
From students, grades
where Students.sno=grades.sno
and Grades.cno <> ' B2 '

The above query method is wrong, the correct way to see below:

SELECT * FROM Students
Where NOT EXISTS (SELECT * from grades
where Grades.sno=students.sno and cno= ' B2 ')

11. On the difficulty of multiple nested query solution idea:

For example, students who have enrolled in 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))

One of the most important: choose from the student table, excluding those who have no choice in class. Use not exist. Since the discussion object is a course, the second query is found from the course table, excluding those who chose the class.