Mysql基础（十二)：sql语句执行步骤详解（一）准备工作

DISTINCT < select_list > FROM < left_table > < join_type > JOIN < right_table > ON < join_condition > WHERE < where_condition > GROUP BY < group_by_list > HAVING < having_condition > ORDER BY < order_by_condition > LIMIT < limit_number >

继续做以下的前期准备工作：

新建一个测试数据库TestDB；

 create   database  TestDB;

创建测试表table1和table2；

  CREATE   TABLE   table1
 (
     customer_id   VARCHAR ( 10 )  NOT   NULL  ,
     city   VARCHAR ( 10 )  NOT   NULL  ,
       PRIMARY   KEY  (customer_id)
 )ENGINE  = INNODB  DEFAULT  CHARSET =  UTF8;
 
   CREATE   TABLE   table2
 (
     order_id   INT   NOT   NULL   auto_increment,
     customer_id   VARCHAR ( 10  ),
       PRIMARY   KEY  (order_id)
 )ENGINE  = INNODB  DEFAULT  CHARSET = UTF8;

插入测试数据；

  INSERT   INTO  table1(customer_id,city)  VALUES ( ‘  163  ‘ , ‘  hangzhou  ‘  );
   INSERT   INTO  table1(customer_id,city)  VALUES ( ‘  9you  ‘ , ‘  shanghai  ‘  );
   INSERT   INTO  table1(customer_id,city)  VALUES ( ‘  tx  ‘ , ‘  hangzhou  ‘  );
   INSERT   INTO  table1(customer_id,city)  VALUES ( ‘  baidu  ‘ , ‘  hangzhou  ‘  );
 
   INSERT   INTO  table2(customer_id)  VALUES ( ‘  163  ‘  );
   INSERT   INTO  table2(customer_id)  VALUES ( ‘  163  ‘  );
   INSERT   INTO  table2(customer_id)  VALUES ( ‘  9you  ‘  );
   INSERT   INTO  table2(customer_id)  VALUES ( ‘  9you  ‘  );
   INSERT   INTO  table2(customer_id)  VALUES ( ‘  9you  ‘  );
   INSERT   INTO  table2(customer_id)  VALUES ( ‘  tx  ‘  );
   INSERT   INTO  table2(customer_id)  VALUES ( NULL );

准备工作做完以后，table1和table2看起来应该像下面这样：

     mysql >   select   *   from   table1;
       +  --  -----------+----------+ 
      |  customer_id  |  city      | 
      +  --  -----------+----------+ 
      |   163           |  hangzhou  | 
      |  9you         |  shanghai  | 
      |  baidu        |  hangzhou  | 
      |  tx           |  hangzhou  | 
      +  --  -----------+----------+ 
      4  rows  in   set  ( 0.00  sec)

 mysql >   select   *   from   table2;
   +  --  --------+-------------+ 
  |  order_id  |  customer_id  | 
  +  --  --------+-------------+ 
  |          1   |   163           | 
  |          2   |   163           | 
  |          3   |  9you         | 
  |          4   |  9you         | 
  |          5   |  9you         | 
  |          6   |  tx           | 
  |          7   |   NULL          | 
  +  --  --------+-------------+ 
  7  rows  in   set  ( 0.00  sec)

准备SQL逻辑查询测试语句

      SELECT  a.customer_id,  COUNT (b.order_id)  as   total_orders
       FROM  table1  AS   a
       LEFT   JOIN  table2  AS   b
       ON  a.customer_id  =   b.customer_id
       WHERE  a.city  =   ‘  hangzhou  ‘ 
      GROUP   BY   a.customer_id
       HAVING   count (b.order_id)  <   2 
      ORDER   BY  total_orders  DESC ;

使用上述SQL查询语句来获得来自杭州，并且订单数少于2的客户。

 

Mysql基础（十二)：sql语句执行步骤详解（一）准备工作

标签：详解   前期准备   sql查询语句   步骤   arch   cond   sql   base   ota   

查看更多关于Mysql基础（十二)：sql语句执行步骤详解（一）准备工作的详细内容...