MySQL advanced query

Advanced Query Basic Usage

One, exists and notexists subquery

1, exists sub-query

Select .... From table name where exists (sub-query);

2, not exsits sub-query

Select .... From table name where NOT EXISTS (subquery);

Second, sub-query considerations

1) A subquery statement can be nested where any expression in the SQL statement appears.

In a SELECT statement, a subquery can be nested in the columns, tables, and query conditions of a SELECT statement, that is, a SELECT clause, a FROM clause, a WHERE clause, a GROUP BY clause, and a HAVING clause.

The ① subquery uses syntax in the SELECT clause and FROM clause.

Select (sub-query) from table name;

The subquery result is a single row, but you do not have to specify a column alias.

② the use syntax of a subquery in the FROM clause.

SELECT * FROM (sub-query) as table alias;

Note: You must specify an alias for the table, which typically returns multiple rows of multicolumn data records, which can be used as a temporary table.

Third, group query

1. Use GROUP by for group queries

① the average score for each course student:

Select Subjectno, avg (Subjectresult)

From result

Group BY Subjectno;

② are grouped by gender:

Select COUNT (*) as number of people, sex

From student

Group BY sex;

③ total number of students per grade

Select COUNT (*) as grade number, Gradeid

From student

Group BY Gradeid;

2, multi-column group query

Count the number of male and female students in each grade:

Select grade as grade, COUNT (*) as number, sex as gender

From student

GROUP BY Grade,sex

Order by grade;

3. Group filter using HAVING clause

① for grades with a total of more than 2 students

Select COUNT (*) as number of grade as Grade

From student

Group BY grade

Having Count (*) >2;

② the average score to reach the passing course information

Select Subjectno as course number, AVG (Studentresult) as average

From result

GROUP BY subject

Having avg (Student_result) >=60;

③ Check the total number of passes per course and the average score of passing students

Select COUNT (*) as total number of AVG (Student_result) as average, subjectno as courses

From result

Where student_result> ' 60 ';

Group BY Subjectno;

④ the number of passes per course and the average pass score of 80 points.

Select COUNT (*) as number of AVG (Student_result) as flat average, subjectno as course

From result

Where student_result> ' 60 '

GROUP BY Subjectno

Having avg (Student_result) >80;

Four, multi-table connection query

1, Multi-table connection classification

① Internal Connection Query

Connect two tables using a inner JOIN or a WHERE clause

(1) specifying the join condition in the WHERE clause

Such as: Query student name and results

Select Student.name,result.student.result

From Student,result

where Student.studentno=result.studentno;

(2) Use inner join in the FROM clause ... On

Such as: Query student name and results

Select S.name,r.student_redult,r.studentno

From student as S

Inner JOIN result as R on (S.STUDENTNO=R.STUDENTNO);

2, External connection query

① LEFT outer link query

Left join ... On or left outer join ... On

Such as: The student table is the main table, the score table is from the table

Select S.name,r.student_redult,r.studentno

From student as S

Left OUTER join result as R

On (S.STUDENTNO=R.STUDENTNO);

2. Query by external connection

Right join: On or right outer join: On

Such as: The student table is the main table, the score table is from the table

Select S.name,r.student_redult,r.studentno

From student as S

Right outer join result as R

On (S.STUDENTNO=R.STUDENTNO);

V. The difference between outer and inner connections

Http://www.cnblogs.com/Ewin/archive/2009/10/05/1578322.html

MySQL advanced query