MySQL Correlation query Tips

No more nonsense, go straight to the chase.

#数据准备

Class Table class:

CREATE TABLE' class ' (' Class_no ' )int(2) unsigned Zerofill not NULLAuto_increment COMMENT'class Number', ' class_name 'varchar( -)CHARACTER SETUtf8 not NULLCOMMENT'class name',  PRIMARY KEY(' class_no ')) ENGINE=MyISAMDEFAULTCHARSET=latin1;
Insert  intoClassValues(1,'Pei-Gifted class');Insert  intoClassValues(2,'Regular Class');Insert  intoClassValues(3,'Ascension Class');

Student Table Student:

CREATE TABLE' student ' (' Stu_no ' )int(2) unsigned Zerofill not NULLAuto_increment COMMENT'Student Number', ' Stu_name 'varchar( -)CHARACTER SETUtf8 not NULLCOMMENT'Student Name', ' Stu_sex 'varchar(3)CHARACTER SETUtf8 not NULLCOMMENT'Student Sex', ' Stu_age 'tinyint(2) unsigned ZerofillDEFAULT NULLCOMMENT'Student Age', ' Grade 'Double(5,2) unsigned ZerofillDEFAULT NULLCOMMENT'Achievements', ' class_no 'int(2) unsigned ZerofillDEFAULT NULLCOMMENT'Your class number',  PRIMARY KEY(' Stu_no '),KEY' class_no ' (' class_no ')) ENGINE=MyISAM auto_increment= One DEFAULTCHARSET=latin1;Insert  intoStudentValues( on,'Li Bai','male', -, -, on);Insert  intoStudentValues( Geneva,'du Fu','male', -, the, on);Insert  intoStudentValues(Geneva,'Zhang Fei','male', +, the, Geneva);Insert  intoStudentValues(Geneva,'Han Xin','male', -,98, Geneva);Insert  intoStudentValues( to,'the Dragon','male', -, About, Geneva);Insert  intoStudentValues( .,'Big Joe','female', -, the, on);Insert  intoStudentValues( -,'Little Joe .','female', -, the, on);Insert  intoStudentValues( ,,'Little Joe .','female', -, -, on);Insert  intoStudentValues( the,'Brother Guan','male', +, the, Geneva);Insert  intoStudentValues(Ten,'Liu Bei','male', $,98,NULL);

1:exists Sub-query

True if the subquery has a return result, or False if no return value

Select *  from where exists (Select*fromwhere=+)

For example NOT exists:

Select *  from where  not exists (Select*fromwhere=n);

The above results return NULL because NOT exists returns false

Select *  from where exists (Select*fromwhere= student.class_no);

The above query can see that we are missing a piece of data, the tenth clas_no is null ... So this data is flase .... and

Class.class_no = Student.class_no is true, it returns all

2: [Union] together query

Requirement: Get the top grade of 01 class and the lowest grade of 02 class

That's what we usually do.

Select Max  from where =  on ; Select min  from where = ;

To optimize this query we can do this:

(SelectConcat'The highest grade in class 1th:',Max(grade))'Achievements'  fromStudentwhereClass_no=  on)Union(SelectConcat'minimum grade of class 2nd:',min(grade))'Achievements'  fromStudentwhereClass_no=  Geneva);

Here again the difference between Union and UNION ALL:

Union

(Selectfromwhere=1) Union (Selectfromwhere=2);

UNION ALL:

(Selectfromwhere=1) Union  All (Selectfromwhere=2);

Through the above two queries, we can see:Union and query it will automatically repeat the record, if you do not want to remove duplicate records, you can use union all;

We add a sort:

(Selectfromwhere=1) Union  All (Selectfromwhere=2order by desc;

Classification of connection queries
1: Inner Connection
2: Outer connection
3: Natural Connection

1:inner Join (inner connection)
Requirements: Check out the student's school number, name, where the class name

Select  from student  joinwhere= ' class '. class_no;

The above SQL is equivalent to:

Select  from Join where = ' class '. class_no; Select  from where = class.class_no;

Inner characters within the connection can be used without writing

2:cross Join (cross join, Cartesian set) No conditions for inner joins
Example: SELECT * FROM student cross Join class;
Example: SELECT * FROM student inner JOIN class;
Example: SELECT * FROM student cross Join class where ' student '. Class_no = ' class '. class_no;
The Ps:cross join and the inner join are not distinguished in use, except that the cross join is defined as a crossover connection in MySQL

Just write this. Other connection methods are also simple, the data is also many ...

MySQL Correlation query Tips