MySQL²éѯÓï¾ä¡ª¡ª×Ó²éѯ£¨1£©# ×Ó²éѯ¼òÊö

Ò»：×Ó²éѯµÄÌصã：

1. ×Ó²éѯ¿É±»Ç¶Ì×ÔÚ select，insert，update，delete µÈÓï¾äÖÐ
2. ´ó¶àÊýÇé¿öÏÂ×Ó²éѯ³äµ±Öмä½á¹û¼¯½ÇÉ«
3. ×Ó²éѯ¿É½øÐÐǶÌ×，ÇÒ¸ù¾ÝÄÚ´æ¼°±í´ïʽ¸´Ôӳ̶Ȳ»Í¬，ǶÌ×ÏÞÖÆÒ²²»Í¬
4. ÈκοÉʹÓñí´ïʽµÄµØ·½，¶¼¿ÉÒÔʹÓÃ×Ó²éѯ，Ö»ÒªËû·µ»ØµÄÊǵ¥¸öÖµ

¶þ：×Ó²éѯµÄ·ÖÀà：

1. °´·µ»ØÖµµÄÊýÁ¿¿É·ÖΪ：±êÁ¿×Ó²éѯ，¶àÖµ×Ó²éѯ
2. °´×Ó²éѯ¶ÔÍⲿÒÀÀµÐÔ：¶ÀÁ¢×Ó²éѯ，Ïà¹Ø×Ó²éѯ
3. °´±È½ÏÔËËã·ûµÄ²»Í¬ÐÔ：IN，EXISTS，ANY，SOME，ALLµÈ¶àÖÖÐÎʽ

Èý：×Ó²éѯµÄʹÓÃ：

1. Ê×ÏÈ´´½¨Á½¸ö±í（ѧÉú±íºÍ½Ìʦ±í）

   # ´´½¨Ñ§Éú±í
   mysql> create table tb_student(
       -> stu_ID long,
       -> class varchar(5),
       -> score int
       -> );
   Query OK, 0 rows affected (0.23 sec)
   
   # ´´½¨½Ìʦ±í
   mysql> create table tb_teacher(
       -> tea_ID long,
       -> class varchar(5),
       -> age int
       -> );
   Query OK, 0 rows affected (0.49 sec)

2. ½«Ò»Ð©Öµ²åÈëµ½±íÖÐ

   insert into tb_student values(1, "A", 20);
   
   insert into tb_student values(2, "A", 30);
   
   insert into tb_student values(3, "A", 70);
   
   insert into tb_student values(4, "B", 60);
   
   insert into tb_student values(5, "B", 70);
   
   insert into tb_student values(6, "B", 80);
   
   insert into tb_teacher values(1, "A", 25);
   
   insert into tb_teacher values(2, "B", 40);

3. ×¼±¸¹¤×÷Íê±Ï，½ÓÏÂÀ´½øÐÐ×Ó²éѯÁ·Ï°

• ÀýÒ»：¸÷°à½ÌʦID¼°Æä°à¼¶Æ½¾ù·ÖÊý

  mysql> select tea_ID,
      -> (select avg(score) from tb_student as s where s.class = t.class group by class)
      -> as Avg from tb_teacher as t;
  +--------+---------+
  | tea_ID | Avg     |
  +--------+---------+
  | 1      | 40.0000 |
  | 2      | 70.0000 |
  +--------+---------+
  2 rows in set (0.00 sec)

• Àý¶þ：¸÷°à¼¶½ÌʦÄêÁä¼°Æä°à¼¶¼°¸ñÈËÊý（60Ϊ¼°¸ñÏß）

  mysql> select age,
      -> (select count(*) from tb_student as s where s.class = t.class && s.score >= 60 group by class)
      -> as Count from tb_teacher as t order by Count desc;
  +------+-------+
  | age  | Count |
  +------+-------+
  |   40 |     3 |
  |   25 |     1 |
  +------+-------+
  2 rows in set (0.00 sec)