SQL语句总结2之基本查询

SELECT语句的完整语法如下,其中只有SELECT 和 FROM是必需的:

SELECT [DISTINCT | ALL]  field_list
FROM      table_name_list                     
[WHERE    search_conditions] 
[GROUP BY expression]
[HAVING   search_condition]
[ORDER BY order_expression [ASC | DESC]]

执行步骤:

1.执行FROM语句,如果有多个表,则讲多表进行交叉链接
2.然后是GROUP BY, HAVING, ORDER BY.

WHERE语句中的运算符

1.单值比较运算符<,<=,!=等等
2.BETWEEN: BETWEEN 30 AND 50
3.IS [NOT] NULL
4.AND: condition1 AND condition2
5.OR
6.IN: IN ('zhang san','li si','wang wu')
7.NOT: WHERE NOT NAME='zhang san'
8.LIKE以及通配符:LIKE '李%'表示所有李开头的字符串

聚合函数

1.SUM():求和
2.MAX():最大数
3.MIN():最小数
4.AVG():平均值
5.COUNT():查询student表中所有的记录数

SELECT COUNT(*) AS total_number
FROM student

GROUP BY分组

依据列里的数据进行分组

HAVING

HAVING也定义了搜索条件,对分组就行过滤