SQL常用基础语句
1、select
语法：SELECT column1, column2, columnN FROM table_name
例句： SELECT * FROM COMPANY
解释：select为查询语句，从表格中选择字段（包括计算字段），返回结果表。
2、where
语法 ：SELECT column1, column2, columnN FROM table_name WHERE [condition]
例句：SELECT * FROM COMPANY WHERE AGE IS NOT NULL
解释：筛选子句，返回结果时，考虑字段筛选条件（需配合操作符），针对每一行，需留意与Having区别
3、like
语法：SELECT column_list FROM table_name WHERE column LIKE ‘XXXX%’
例句：SELECT * FROM COMPANY WHERE ADDRESS LIKE ‘%-%’;
解释：匹配通配符指定模式的文本值，与where搭配使用，与通配符搭配使用
4、limit
语法：SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]
例句：SELECT * FROM COMPANY LIMIT 6（offset rowid）
解释：返回结果限制行数量，可配合offset指定从哪开始检索。
5、distinct
语法：SELECT DISTINCT column1 FROM table_name
例句：SELECT DISTINCT Company FROM Orders
解释：作用于所有指定列，返回不同的值，具有唯一性。
6、order by
语法：SELECT column1, column2, columnN FROM table_name order by column_name（DESC）
例句：SELECT Company, OrderNumber FROM Orders ORDER BY Company
解释：排序检索数据，可指定多个列进行排序，默认升序排列，可用DESC指定排序方向为降序
7、group by
语法：SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
例句：SELECT site_id, SUM(access_log.count) AS nums FROM access_log GROUP BY site_id
解释：根据指定列的值进行分组，可多次分组。通常配合聚集函数使用，返回所需的统计结果。
8、HAVING
语法：SELECT column_name, aggregate_function(column_name) FROM table_name WHERE [condition] GROUP BY column_name HAVING [condition]
例句：SELECT site_id, SUM(access_log.count) AS nums FROM access_log GROUP BY site_id HAVING site_id>10
解释：根据分组后的结果进行筛选，需留意与where的差别，两个子句可同时使用，但含义不同。where针对数据分组前，having针对数据分组后
9、inner join
语法：SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
例句：SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName
解释：内联结（等值联结），根据on子句的检索条件，联结两个表格对应的行
10、left outer join
语法：SELECT column_name(s) FROM table_name1 LEFT OUTER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
例句：select class,name from log1 left outer join name on log1.id=name.id
解释：外联结，联结时包含左侧表格的不关联行。