SQL常用基础语句

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
解释:外联结,联结时包含左侧表格的不关联行。
在这里插入图片描述

发布了2 篇原创文章 · 获赞 0 · 访问量 74
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览