SQL基础 利用SELECT检索数据
SELECT
[DISTINCT|ALL]
SELECT_LIST
FROM TABLE_LIST
[WHERE+CLAUSE]
[GROUP_BY_CLAUSE]
[HAVING CONDITION]
[ORDER_BY_CLAUSE]
使用别名代替数据库中的字段名(AS可去除用空格，但不利于阅读)
SELECT COLIMN_NAME1 AS 别名1,…COLUMN_NAME2 || ’*’
|| 1.23 || ’=’ || COLUMN_NAME2 * 1.23 AS 别名2
FROM TABLE_NAME;
使用函数操作查询的字段
SELECT COLIMN_NAME1 AS 别名1,
subStr(COLIMN_NAME1,1,6) AS 别名2,…COLUMN_NAME2 || ’*’ || 1.23 || ’=’ || COLUMN_NAME2 * 1.23 AS 别名2
FROM TABLE_NAME;
去除检索数据中的重复记录
SELECT DISTINCT(COLUMN_NAME) FROM TABLE_NAME;
检索出来的数据排序
ORDER BY
{EXPR | POSITION | C_ALIAS}
[ASC | DESC]
[NULLS FIRST | NULLS LAST]
[,{EXPR | POSITION | C_ALIAS}
[ASC | DESC]
[NULLS FIRST | NULLS LAST]
]…
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME ORDER BY COLUMN_NAME3;
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME ORDER BY COLUMN_NAME3 DESC;(默认是升序ASC，降序必须声明DESC)
排序时对NULL 值的处理(默认时升序时NULL值排在末位，降序时排在首位)
升序时将NULL值放于排在首位
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME ORDER BY COLUMN_NAME3 NULLS FIRST;
降序时将NULL值放于排在末位
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME ORDER BY COLUMN_NAME3 NULLS
LAST;
可使用别名
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME ORDER BY 别名 NULLS LAST;
使用表达式作为排序字段
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME ORDER BY EXPR NULLS LAST;
使用字段的位置作为排序字段（方便，且防止使用UNION时出错）
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME ORDER BY 3 ASC;
使用多个字段排序
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME ORDER BY COLUMN_NAME2 ASC，3 DESC;
使用WHEHE子句设置检索条件
关系操作符包括：<、 <=、 >、 >=、 =、 !=、 <>
比较操作符包括：
IS NULL 如果操作数为NULL返回TRUE
LIKE 模糊比较字符串值
BETWEEN…AND… 验证值是否在范围之内
IN 验证操作数在设定的一系列值中
逻辑比较符包括：
AND 两个条件都必须得到满足
OR 只要满足两个条件中其中的一个
NOT 与某个逻辑值取反
查询中使用单一条件限制
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME WHERE CONDITION ORDER BY COLUMN_NAME;
查询条件中使用函数
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME WHERE SUBSTR(CONDITION,1,2)=’123456’;
查询中使用多个条件限制
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME WHERE CONDITION1 AND CONDITION2;
查询条件使用BETWEEN…AND…
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME WHERE COLUMN_NAME1 BETWEEN ‘12344’ AND ‘12334’;
查询条件使用OR
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME WHERE CONDITION1 OR CONDITION2;
模糊查询数据
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME WHERE COLUMN_NAME1 LIKE ‘%CONDITION%’;
查询条件限制在某个列表范围之内
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME WHERE COLUMN_NAME1 IN(‘CONDITION1’, ‘CONDITION2’);
专门针对空值的查询
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME WHERE COLUMN_NAME1 IS NULL;
专门争夺非空值的擦洗
SELECT COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME WHERE COLUMN_NAME1 IS NOT NULL;
GROUP BY 子句语法及使用
GROUP BY
{EXPR
|{ ROLLOP | CUBE } ({EXPR [,EXPR]…})
}
EXPR通常指数据库列名
ROLLOP | CUBE GROUP BY子句扩展 可返回小计和总计记录
和分组函数一起使用，可以根据某一列进行分组，也可以根据某几列进行分组
SELECT COLUMN_NAME1…,COLUMN_NAME2 FROM TABLE_NAME [WHERE CONDITION] GROUP BY COLUMN_NAME4…,COLUMN_NAME5;
HAVING子句的使用
HAVING子句通常和GROUP BY子句一起使用，限定搜索条件，他和WHERE子句不一样HAVING子句与组有关，而不与单个的组有关，在GROUP BY子句中，他会作用于GROUPBY创建的组
SELECT COLUMN_NAME1 FROM TABLE_NAMEGROUP BY COLUMN_NAME2 HAVING CONDITION ;
使用子查询
单一条件子查询
SELECT COLUMN_NAME1…,COLUMN_NAME2 FROM TABLE_NAME WHERE COLUMN_NAME CODITION (SELECT COLUMN_NAME FROM TABLE_NAME WHERE CONDITION);
多条件子查询
SELECT COLUMN_NAME1…,COLUMN_NAME2 FROM TABLE_NAME WHERE COLUMN_NAME CODITION (SELECT COLUMN_NAME FROM TABLE_NAME WHERE CONDITION) AND COLUMN_NAME CODITION (SELECT COLUMN_NAME FROM TABLE_NAME WHERE CONDITION);
子查询返回多行
IN(子查询语句);与上面的相同
ANY(子查询语句);表示满足子查询结果的任何一个，和<，<=配合，表示小于等于列表中的最大值，而和>，>=配合表示大于等于列表的最大值
SOME(子查询语句);可以认为和ANY相同
ALL(子查询语句); 表示满足子查询结果的所有结果，和<，<=配合，表示小于等于列表中的最大值，而和>，>=配合表示大于等于列表的最大值l
计数函数
SELECT COUNT(*) FROM TABLE_NAME [WHERE CONDITION];
连接查询
内连接查询(多张表连接在一起查询条数我n*m…)
SELECR COLUMN_NAME1,…COLUMN_NAME2 FROM TABLE_NAME1,…TABLE_NAME2 [WHERE CONDITION];
等值连接
SELECT A.COLUMN_NAME1,…A.COLUMN_NAME2,B.COLUMN_NAME1,…B.COLUMN_NAME2 FROM TABLE_NAME1 A,…TABLE2_NAME B WHERE A.COLUMN_NAME3=B.COLUMN_NAME3 AND A.COLUMN_NAME4=B.COLUMN_NAME4;
SELECT A.COLUMN_NAME1,…A.COLUMN_NAME2,B.COLUMN_NAME1,…B.COLUMN_NAME2 FROM TABLE_NAME1 A INNER JOIN TABLE_NAME2 B ON A.COLUMN_NAME3=B.COLUMN_NAME3 AND A.COLUMN_NAME4=B.COLUMN_NAME4;
INNER JOIN … 与上面的等效
不等值连接
不等值连接就是指连接条件中使用‘>’‘>=’‘<’‘<=’‘!=’‘<>’‘BETWEEN…AND…’‘IN’等连接两个条件列表，但这种方式通常需要和其他的等值计算一起使用，否则检索出的数据可能没有实际意义
SELECT A.COLUMN_NAME1,…A.COLUMN_NAME2,B.COLUMN_NAME1,…B.COLUMN_NAME2 FROM TABLE_NAME1 A INNER JOIN TABLE_NAME2 B ON A.COLUMN_NAME3 CONDITION B.COLUMN_NAME3 AND A.COLUMN_NAME4 CONDITION B.COLUMN_NAME4;
INNER JOIN 可以直接写成JOIN 关键字ON 不能省去
自连接
SELECT A.COLUMN_NAME1,…A.COLUMN_NAME2,B.COLUMN_NAME1,…B.COLUMN_NAME2 FROM TABLE_NAME A,…TABLE_NAME B WHERE A.COLUMN_NAME3=B.COLUMN_NAME3 AND A.COLUMN_NAME4=B.COLUMN_NAME4;
外连接
左外连接
SELECT A.COLUMN_NAME1,…A.COLUMN_NAME2,B.COLUMN_NAME1,…B.COLUMN_NAME2 FROM TABLE_NAME1 A LEFT JOIN TABLE_NAME2 B ON A.COLUMN_NAME3 CONDITION B.COLUMN_NAME3 AND A.COLUMN_NAME4 CONDITION B.COLUMN_NAME4;
右外连接
SELECT A.COLUMN_NAME1,…A.COLUMN_NAME2,B.COLUMN_NAME1,…B.COLUMN_NAME2 FROM TABLE_NAME1 A RIGHT JOIN TABLE_NAME2 B ON A.COLUMN_NAME3 CONDITION B.COLUMN_NAME3 AND A.COLUMN_NAME4 CONDITION B.COLUMN_NAME4;
全外连接
SELECT A.COLUMN_NAME1,…A.COLUMN_NAME2,B.COLUMN_NAME1,…B.COLUMN_NAME2 FROM TABLE_NAME1 A FULL JOIN TABLE_NAME2 B ON A.COLUMN_NAME3 CONDITION B.COLUMN_NAME3 AND A.COLUMN_NAME4 CONDITION B.COLUMN_NAME4;
外连接(+)的使用
左连接
SELECT A.COLUMN_NAME1,…A.COLUMN_NAME2,B.COLUMN_NAME1,…B.COLUMN_NAME2 FROM TABLE_NAME1 A,…TABLE_NAME2 B WHERE A.COLUMN_NAME3=B.COLUMN_NAME3(+);
右连接
SELECT A.COLUMN_NAME1,…A.COLUMN_NAME2,B.COLUMN_NAME1,…B.COLUMN_NAME2 FROM TABLE_NAME1 A,…TABLE_NAME2 B WHERE A.COLUMN_NAME3(+)=B.COLUMN_NAME3;
