MySQL multi-table query case

1.order by sort

Mysql> SELECT * from Xin the order by ID;

+------+----------+

| ID | name |

+------+----------+

| 1 | Lilie |

| 2 | Tianjin |

| 3 | Shanghai |

+------+----------+

2. In ascending order

Mysql> SELECT * from Xin ORDER by ID ASC;

+------+----------+

| ID | name |

+------+----------+

| 1 | Lilie |

| 2 | Tianjin |

| 3 | Shanghai |

+------+----------+

3. Sort in descending order

Mysql> SELECT * from Xin ORDER by id desc;

+------+----------+

| ID | name |

+------+----------+

| 3 | Shanghai |

| 2 | Tianjin |

| 1 | Lilie |

+------+----------+

---------------------------------------------

CREATE TABLE Student (

Son Int (Ten) not NULL COMMENT ' School Number ',

Sname varchar (+) not NULL COMMENT ' name ',

Ssex char (2) not NULL COMMENT ' sex ',

Sage tinyint (2) Not NULL default ' 0 ' COMMENT ' student age ',

Sdept varchar (+) default NULL COMMENT ' student Department ',

PRIMARY KEY (Son),

Key Index_sname (Sname)

) Engine=innodb auto_increment=1 DEFAULT charset=latin1;

CREATE TABLE Course (

Cno Int (Ten) not NULL COMMENT ' course number ',

Cname varchar (+) not NULL COMMENT ' course name ',

Ccredit tinyint (Ten) not NULL COMMENT ' credits ',

PRIMARY KEY (Cno)

) Engine=innodb auto_increment=1 DEFAULT charset=latin1;

CREATE TABLE SC (

SCid Int (a) not NULL auto_increment COMMENT ' primary key ',

Cno Int (Ten) not NULL COMMENT ' course number ',

Sno Int (Ten) not NULL COMMENT ' study number ',

Grade tinyint (2) not NULL COMMENT ' student score ',

PRIMARY KEY (SCid)

) Engine=innodb DEFAULT charset=latin1;

INSERT into student values (0001, ' macro log ', ' Male ', 30, ' computer network ');

INSERT into student values (0002, ' Wang Shuo ', ' Male ', ' n ', ' Computer Application ');

INSERT into student values (0003, ' oldboy ', ' Male ', 28, ' logistics Management ');

INSERT into student values (0004, ' pulsation ', ' Male ', computer, ' application ');

INSERT into student values (0005, ' oldgirl ', ' female ', 26, ' Computer Science and Technology ');

INSERT into student values (0006, ' yingying ', ' female ', 22, ' Nurse ');

Insert into course values (1001, ' Linux Advanced Ops ', 3);

Insert into course values (1002, ' Linux Senior Architect ', 5);

Insert into course values (1003, ' MySQL Advanced dba ', 4);

Insert into course values (1004, ' Python OPS Development ', 4);

Insert into course values (1005, ' Java Web Development ', 3);

Insert INTO SC (Sno,cno,grade) values (0001,1001,4);

Insert INTO SC (Sno,cno,grade) values (0001,1002,3);

Insert INTO SC (Sno,cno,grade) values (0001,1003,1);

Insert INTO SC (Sno,cno,grade) values (0001,1004,6);

Insert INTO SC (Sno,cno,grade) values (0002,1001,3);

Insert INTO SC (Sno,cno,grade) values (0002,1002,2);

Insert INTO SC (Sno,cno,grade) values (0002,1003,2);

Insert INTO SC (Sno,cno,grade) values (0002,1004,8);

Insert INTO SC (Sno,cno,grade) values (0003,1001,4);

Insert INTO SC (Sno,cno,grade) values (0003,1002,4);

Insert INTO SC (Sno,cno,grade) values (0003,1003,2);

Insert INTO SC (Sno,cno,grade) values (0003,1001,8);

Insert INTO SC (Sno,cno,grade) values (0004,1001,1);

Insert INTO SC (Sno,cno,grade) values (0004,1002,1);

Insert INTO SC (Sno,cno,grade) values (0004,1003,2);

Insert INTO SC (Sno,cno,grade) values (0004,1004,3);

Insert INTO SC (Sno,cno,grade) values (0005,1001,5);

Insert INTO SC (Sno,cno,grade) values (0005,1002,3);

Insert INTO SC (Sno,cno,grade) values (0005,1003,2);

Insert INTO SC (Sno,cno,grade) values (0005,1004,9);

--------------------------------------------

4. Multi-Table Query

Mysql> Select Student. Sno,student. Sname,course. Cname,sc. Grade from STUDENT,COURSE,SC where student. Sno=sc. Sno and course. Cno=sc. Cno;

+-----+---------+----------------------+-------+

| Sno | Sname | Cname | Grade |

+-----+---------+----------------------+-------+

| 1 | Macro-Log |     Linux Advanced Operations | 4 |

| 2 | Wang Shuo |     Linux Advanced Operations | 3 |

| 3 | Oldboy |     Linux Advanced Operations | 4 |

| 3 | Oldboy |     Linux Advanced Operations | 8 |

| 4 | pulsation |     Linux Advanced Operations | 1 |

| 5 | Oldgirl |     Linux Advanced Operations | 5 |

| 1 | Macro-Log |     Senior Linux Architects | 3 |

| 2 | Wang Shuo |     Senior Linux Architects | 2 |

| 3 | Oldboy |     Senior Linux Architects | 4 |

| 4 | pulsation |     Senior Linux Architects | 1 |

| 5 | Oldgirl |     Senior Linux Architects | 3 |

| 1 | Macro-Log |     MySQL Advanced DBA | 1 |

| 2 | Wang Shuo |     MySQL Advanced DBA | 2 |

| 3 | Oldboy |     MySQL Advanced DBA | 2 |

| 4 | pulsation |     MySQL Advanced DBA | 2 |

| 5 | Oldgirl |     MySQL Advanced DBA | 2 |

| 1 | Macro-Log |     Python DevOps Development | 6 |

| 2 | Wang Shuo |     Python DevOps Development | 8 |

| 4 | pulsation |     Python DevOps Development | 3 |

| 5 | Oldgirl |     Python DevOps Development | 9 |

+-----+---------+----------------------+-------+

Rows in Set (0.06 sec)

5. Multi-table query sorted by Sno

Mysql> Select Student. Sno,student. Sname,course. Cname,sc. Grade from STUDENT,COURSE,SC where student. Sno=sc. Sno and course. Cno=sc. Cno ORDER by Sno;

+-----+---------+----------------------+-------+

| Sno | Sname | Cname | Grade |

+-----+---------+----------------------+-------+

| 1 | Macro-Log |     Linux Advanced Operations | 4 |

| 1 | Macro-Log |     Python DevOps Development | 6 |

| 1 | Macro-Log |     MySQL Advanced DBA | 1 |

| 1 | Macro-Log |     Senior Linux Architects | 3 |

| 2 | Wang Shuo |     Linux Advanced Operations | 3 |

| 2 | Wang Shuo |     Python DevOps Development | 8 |

| 2 | Wang Shuo |     MySQL Advanced DBA | 2 |

| 2 | Wang Shuo |     Senior Linux Architects | 2 |

| 3 | Oldboy |     Senior Linux Architects | 4 |

| 3 | Oldboy |     Linux Advanced Operations | 8 |

| 3 | Oldboy |     Linux Advanced Operations | 4 |

| 3 | Oldboy |     MySQL Advanced DBA | 2 |

| 4 | pulsation |     MySQL Advanced DBA | 2 |

| 4 | pulsation |     Senior Linux Architects | 1 |

| 4 | pulsation |     Linux Advanced Operations | 1 |

| 4 | pulsation |     Python DevOps Development | 3 |

| 5 | Oldgirl |     Python DevOps Development | 9 |

| 5 | Oldgirl |     MySQL Advanced DBA | 2 |

| 5 | Oldgirl |     Senior Linux Architects | 3 |

| 5 | Oldgirl |     Linux Advanced Operations | 5 |

+-----+---------+----------------------+-------+

Rows in Set (0.01 sec)

6. Multi-table query in flashback order

Mysql> Select Student. Sno,student. Sname,course. Cname,sc. Grade from STUDENT,COURSE,SC where student. Sno=sc. Sno and course. Cno=sc. Cno ORDER by student. Sno DESC;

+-----+---------+----------------------+-------+

| Sno | Sname | Cname | Grade |

+-----+---------+----------------------+-------+

| 5 | Oldgirl |     Python DevOps Development | 9 |

| 5 | Oldgirl |     MySQL Advanced DBA | 2 |

| 5 | Oldgirl |     Senior Linux Architects | 3 |

| 5 | Oldgirl |     Linux Advanced Operations | 5 |

| 4 | pulsation |     MySQL Advanced DBA | 2 |

| 4 | pulsation |     Senior Linux Architects | 1 |

| 4 | pulsation |     Linux Advanced Operations | 1 |

| 4 | pulsation |     Python DevOps Development | 3 |

| 3 | Oldboy |     Senior Linux Architects | 4 |

| 3 | Oldboy |     Linux Advanced Operations | 8 |

| 3 | Oldboy |     Linux Advanced Operations | 4 |

| 3 | Oldboy |     MySQL Advanced DBA | 2 |

| 2 | Wang Shuo |     Linux Advanced Operations | 3 |

| 2 | Wang Shuo |     Python DevOps Development | 8 |

| 2 | Wang Shuo |     MySQL Advanced DBA | 2 |

| 2 | Wang Shuo |     Senior Linux Architects | 2 |

| 1 | Macro-Log |     Linux Advanced Operations | 4 |

| 1 | Macro-Log |     Python DevOps Development | 6 |

| 1 | Macro-Log |     MySQL Advanced DBA | 1 |

| 1 | Macro-Log |     Senior Linux Architects | 3 |

+-----+---------+----------------------+-------+

Rows in Set (0.00 sec)

7. Multiple-table queries are arranged in a positive order

Mysql> Select Student. Sno,student. Sname,course. Cname,sc. Grade from STUDENT,COURSE,SC where student. Sno=sc. Sno and course. Cno=sc. Cno ORDER by student. Sno ASC;

+-----+---------+----------------------+-------+

| Sno | Sname | Cname | Grade |

+-----+---------+----------------------+-------+

| 1 | Macro-Log |     Linux Advanced Operations | 4 |

| 1 | Macro-Log |     Python DevOps Development | 6 |

| 1 | Macro-Log |     MySQL Advanced DBA | 1 |

| 1 | Macro-Log |     Senior Linux Architects | 3 |

| 2 | Wang Shuo |     Linux Advanced Operations | 3 |

| 2 | Wang Shuo |     Python DevOps Development | 8 |

| 2 | Wang Shuo |     MySQL Advanced DBA | 2 |

| 2 | Wang Shuo |     Senior Linux Architects | 2 |

| 3 | Oldboy |     Senior Linux Architects | 4 |

| 3 | Oldboy |     Linux Advanced Operations | 8 |

| 3 | Oldboy |     Linux Advanced Operations | 4 |

| 3 | Oldboy |     MySQL Advanced DBA | 2 |

| 4 | pulsation |     MySQL Advanced DBA | 2 |

| 4 | pulsation |     Senior Linux Architects | 1 |

| 4 | pulsation |     Linux Advanced Operations | 1 |

| 4 | pulsation |     Python DevOps Development | 3 |

| 5 | Oldgirl |     Python DevOps Development | 9 |

| 5 | Oldgirl |     MySQL Advanced DBA | 2 |

| 5 | Oldgirl |     Senior Linux Architects | 3 |

| 5 | Oldgirl |     Linux Advanced Operations | 5 |

+-----+---------+----------------------+-------+

Rows in Set (0.00 sec)

This article from the "Technology in hand, the world I have" blog, please be sure to keep this source http://xin521long.blog.51cto.com/11884590/1882487

MySQL multi-table query case