Back

SQL 知识复习( SQL tips review)

发布时间: 2012-08-28 06:52:00

SQL 是我最初接触编程时用到的技术。我仅仅在05年的两个项目中使用过。一个是毕业设计，另一个是帮同学写的毕业设计。o(∩∩)o...哈哈。 ( Raw SQL is the technology that I used in my early programming life. I have used them for 2 applications on 2005)

后来就使用了持久层的技术，( Hibernate, ActiveRecord, Mongoid ) ，所以我的raw SQL底子很差。 (After 2005, I knew the concept "Data Persistent" and used many of the Object-Relational Mapping tools such as: Hibernate(java), ActiveRecord(Ruby on Rails) and Mongoid, even there're some document based database which even directly support model operatons)

尽管在工作中使用了很多持久层的技术，以及文档数据库，但是，了解底层的SQL语句仍然是非常重要的。所以，在这里我把一些边际知识回顾一下(It's very important and very helpful to know the details of the raw SQL although we are working with those data-persistence tool or document based database.

1. Join

看到这个图片，我很惊喜，把各种join的用法都说清楚了. ( so , today when I was reviewing the Raw SQL knowledges, I saw this picture. It illustrated all the cases for "Joins" very clearly)

Sql Joins

2. group by

重点是：对多个列进行group by, (let's focus on the multiple columns' group by)

  column1    column2
  1              a
  1              a
  2              a
  2              c
 
  group by column 1 :      
  1          a
  2          a
  group by column 2 : 
  1          a
  2          c
  group by column1, column2   ( or group by column2, column1 )
  1          a
  2          a
  2          c

3. count()

1. 对于普通的count(*) 非常好理解，返回当前表中的记录行数

  column1    column2
  1              a
  1              a
  2              a
  2              c
  select count(*) from ...    
  4

当group by 分句存在时，返回分组的条数。

  column1    column2
  1              a
  1              a
  2              a
  2              c
  select count(*) from ... group by column1  
  2
  2

练习题 (Example)

见这里(see here)

Back