SQL常用进阶操作

数据过滤与分组聚合

• DISTINCT:
  SELECT DISTINCT column1,… FROM table

• LIKE(%,_)
  WHERE column LIKE pattern

• BETWEEN IN
  SELECT column FROM table
  WHERE column BETWEEN 4 AND 5

• MAX,MIN,COUNT,SUM,AVG
  SELECT MAX(column) FROM table

• GROUP BY
  SELECT column FROM table
  (WHERE condition)
  GROUP BY column

JOIN ON(表连接)

• SELECT column_i
  FROM table1 INNER JOIN table2
  ON table1.column_j = table2.column_j;

• JOIN的语句包括INNER, LEFT,RIGHT,FULL

• 可通过AS来设置表别名
  SELECT column1,column2 FROM table1 as t1
  WHERE ti.column3>0

Primary Key(主键)

用于唯一确定表中的一条记录

Index(索引)

可通过WHERE实现二分索引，设为索引的字段搜索较快