常用SQL

1. SQl语句

1.0 Select

子句执行顺序： SELECT--> FROM -->WHERE -->GROUP BY --> HAVING --> ORGER BY --> LIMIT

1.4 Between And

	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

1.5 IS [NOT] NULL null值判断

SELECT distinct t.ACCT_ID, t.BRAND from bescust.INF_SUBSCRIBER t  where t.ACCT_ID is not null;

1.6 UPDATE 更新记录

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' ;

1.7 ORDER BY 排序(DESC/ASC)

//以逆字母顺序显示公司名称，并以数字顺序显示顺序号
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

1.8 Distinct 去重

SELECT distinct t.ACCT_ID, t.BRAND from bescust.INF_SUBSCRIBER t ;

1.9 IN/Not IN 多表查询

SELECT * FROM table1 WHERE name [NOT] IN(SELECT name FROM table2)

1.10 Union[All] 结果并集

select_statement union [all] select_statement
	a). 有all时不对结果去重
	b). 子结果集要具有相同的结构。
	c). 字结果集的列数必须相同。
	d). 子结果集对应的数据类型必须可以兼容。

1.11 Except(Oracle中minus) 结果差集

//返回左边结果集合中已有，而右边没有的记录	
select_statement except select_statement

1.12 Intersect 结果交集

//左右结果集中都有的记录
select_statement intersect select_statement

1.13 Join on 合并展示不同表中的不同字段

	//只展示满足 ON 条件的条数
	SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons JOIN Orders ON Persons.Id_P = Orders.Id_P;
		LEFT JOIN: 即使右表中没有匹配(相应字段值置空)，也从左表返回所有的行,
		RIGHT JOIN: 即使左表中没有匹配(相应字段值置空)，也从右表返回所有的行
		FULL JOIN: 只要其中一个表中存在匹配，就返回行
        示例： SELECT a.*,b.INPUTCONTENTID channelId FROM btv_template_task a  left join  btv_tvod_archive  b on a.CONTENTID=b.CONTENT_ID where a.POPORDEV = '1' 

1.14 Like 通配符

Not Like
%	与零个或多个字符匹配
_	与任何单个字符匹配
\	转义字符
SELECT * FROM Persons WHERE City LIKE 'N%'

1.20 Create Index 创建索引

CREATE INDEX t_1 ON table_name(column_name)

2. SQL函数

2.1 Count 统计记录条数

SELECT COUNT(*) FROM table_name

2.2 Sum 总和

SELECT SUM(column_name) FROM table_name;

2.3 HAVING 条件子句

//查找订单总金额少于 2000 的客户
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000;

2.4 Group By 结果分组(结合sum/count)

//查找每个客户的总金额
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer;
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY Customer,OrderDate

2.5 Max/Min 最大/小值

SELECT MAX(column_name) FROM table_name;

10. 查看数据库信息

10.1 oracle

select userenv('language') from dual; //查看数据库语言
select * from v$version; //查看数据库版本