sql语句查询大全

2019独角兽企业重金招聘Python工程师标准>>>

left join( 左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join( 右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join( 等值连接) 只返回两个表中联结字段相等的行

举例如下：
--------------------------------------------
表A 记录如下：
aID 　　　　　aNum
1 　　　　　a20050111
2 　　　　　a20050112
3 　　　　　a20050113
4 　　　　　a20050114
5 　　　　　a20050115

表B 记录如下:
bID 　　　　　bName
1 　　　　　2006032401
2 　　　　　2006032402
3 　　　　　2006032403
4 　　　　　2006032404
8 　　　　　2006032408

--------------------------------------------
1.left join
sql 语句如下:
select * from A
left join B
on A.aID = B.bID

结果如下:
aID 　　　　　aNum 　　　　　bID 　　　　　bName
1 　　　　　a20050111 　　　　1 　　　　　2006032401
2 　　　　　a20050112 　　　　2 　　　　　2006032402
3 　　　　　a20050113 　　　　3 　　　　　2006032403
4 　　　　　a20050114 　　　　4 　　　　　2006032404
5 　　　　　a20050115 　　　　NULL 　　　　　NULL

（所影响的行数为 5 行）
结果说明:
left join 是以A 表的记录为基础的,A 可以看成左表,B 可以看成右表,left join 是以左表为准的.
换句话说, 左表(A) 的记录将会全部表示出来, 而右表(B) 只会显示符合搜索条件的记录( 例子中为: A.aID = B.bID).
B 表记录不足的地方均为NULL.
--------------------------------------------
2.right join
sql 语句如下:
select * from A
right join B
on A.aID = B.bID

结果如下:
aID 　　　　　aNum 　　　　　bID 　　　　　bName
1 　　　　　a20050111 　　　　1 　　　　　2006032401
2 　　　　　a20050112 　　　　2 　　　　　2006032402
3 　　　　　a20050113 　　　　3 　　　　　2006032403
4 　　　　　a20050114 　　　　4 　　　　　2006032404
NULL 　　　　　NULL 　　　　　8 　　　　　2006032408

（所影响的行数为 5 行）
结果说明:
仔细观察一下, 就会发现, 和left join 的结果刚好相反, 这次是以右表(B) 为基础的,A 表不足的地方用NULL 填充.
--------------------------------------------
3.inner join
sql 语句如下:
select * from A
innerjoin B
on A.aID = B.bID

结果如下:
aID 　　　　　aNum 　　　　　bID 　　　　　bName
1 　　　　　a20050111 　　　　1 　　　　　2006032401
2 　　　　　a20050112 　　　　2 　　　　　2006032402
3 　　　　　a20050113 　　　　3 　　　　　2006032403
4 　　　　　a20050114 　　　　4 　　　　　2006032404

结果说明:
很明显, 这里只显示出了 A.aID = B.bID 的记录. 这说明inner join 并不以谁为基础, 它只显示符合条件的记录.
--------------------------------------------
注:
LEFT JOIN 操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。

语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2

说明：table1, table2 参数用于指定要将记录组合的表的名称。
field1, field2 参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。
compopr 参数指定关系比较运算符："=" ， "<" ， ">" ， "<=" ， ">=" 或 "<>" 。
如果在INNER JOIN 操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误.

 

一、有 = <> > < >= <= 的子查询
格式：
SELECT col1 , col2 , col3
FROM tb
WHERE col = [ANY | SOME | ALL](
SELECT coln FROM tb WHERE ...
)

举例：
SELECT `ID`, `classname`
FROM `ydfzx_software_class`
WHERE `fatherid` = ANY(
SELECT `id`
FROM `ydfzx_software_class`
WHERE `fatherid`=13
)

在这个查询中，子查询返回一个离散值( 一列一行) ，如果有ANY 或ALL 的修饰，可以返回一个离散值列表（一列多行） , 其实 col = ANY 就相当于col IN


二、IN 子查询
格式：
SELECT col1 , col2 , col3
FROM tb
WHERE col [NOT] IN(
SELECT coln FROM tb WHERE ...
)

在有IN 谓词的这个子查询中，返回一个离散值列表（一列多行）



三、SELECT ROW 字查询，注意返回值是0 或1
格式：
SELECT ROW(value1,value2,value3 ...) = [ANY | SOME] SELECT col1,col2,col3

如果第2 条查询必须返回一组离散值（多列单行），如果有ANY 和SOME 谓词的修饰，返回一组离散值列表
举例：
SELECT ROW(1,' 操作系统' ，1) = ANY (SELECT `ID`,`caption`,`status` FROM `ydfzx_software_class`)
在`ydfzx_software_class` 里查找有没有`ID`=1 ，`caption`=' 操作系统',`status`=1 的记录，有就返回1 ，没有就返回NULL 或 0


四、EXISTS 子查询
格式:
SELECT col1,col2 FROM a WHERE EXISTS(SELECT ...)
解释:
主查询先查询出数据, 再逐条通过EXISTS 子查询验证, 只有子查询返回的记录数不为0 时, 主查询中的记录有效. 这个查询开销比较大

users 表
┏━━━┯━━━┯━━━━┓
┃ uid │ name │address ┃
┃ 1 │ 张荣 │ 中光 ┃
┃ 2 │ 晨曦 │fuyang ┃
┃ 3 │ 川湘 │ 中光 ┃
┃ 4 │ 张荣 │ 浙江 ┃
┗━━━┷━━━┷━━━━┛
xl 表
┏━━━┯━━━┯━━━━┓
┃ uid │ xl │year ┃
┃ 1 │ 大专 │2007 ┃
┃ 1 │ 大本 │2008 ┃
┃ 1 │ 硕士 │2009 ┃
┃ 2 │ 小学 │2012 ┃
┃ 2 │ 初中 │2018 ┃
┃ 2 │ 高中 │2021 ┃
┃ 2 │ 大本 │2025 ┃
┃ 4 │ 大本 │2025 ┃
┃ 5 │ 大本 │2025 ┃
┃ 3 │ 大专 │1995 ┃
┃ 3 │ 大本 │2001 ┃
┗━━━┷━━━┷━━━━┛


举例：找出users 表中那些在xl 表中有xl= 小学的记录，他们的共同键为id
SELECT *
FROM `users`
WHERE EXISTS (
SELECT *
FROM `xl`
WHERE `xl`.`uid` = `users`.`uid` AND xl = ' 小学'
)
等效于:
SELECT `users`. *
FROM `users` , `xl`
WHERE `xl`.`xl` = ' 小学' AND `users`.`uid` = `xl`.`uid`

得到的结果为:

┏━━━┯━━━┯━━━━┓
┃ uid │name │address ┃
┃ 2 │ 晨曦 │fuyang ┃
┗━━━┷━━━┷━━━━┛



五、衍生数据表子查询
格式： SELECT ... FROM (SELECT ...) AS name WHERE 。
首先执行子查询生成临时表，外层查询再到此临时表中获取数据。
举例：
SELECT `users`.`uid` , `users`.`name` , count( `xl`.`uid` ) AS lcount
FROM `users` , `xl`
WHERE `users`.`uid` = `xl`.`uid`
GROUP BY `users`.`uid`
ORDER BY `lcount` DESC

等效于：
SELECT *
FROM(
SELECT `users`.`uid` , `users`.`name` , count( `xl`.`uid` ) AS lcount
FROM `users` , `xl`
WHERE `users`.`uid` = `xl`.`uid`
GROUP BY `users`.`uid`
) AS `temp`
ORDER BY `lcount` DESC

有一存储树形目录的表tb, 共有3 个字段，ID ，classname,fatherid, 每个节点一条记录.ID 字段为非重复字段，classname 为此节点的名称, 每个子节点的fatherid 等于其父节点的ID ，如果fatherid 为0 ，表示它为顶层节点，如何能一次就查询

出各顶层节点有没有子节点呢，经过调试，以下语句执行成功。

SELECT `ID` , `classname` , `fatherid` ,

IF(
(SELECT COUNT(`ID` )
FROM `tb`
WHERE `fatherid` = `main`.`ID`
) >0, 1, 0) AS `haveson`

FROM `tb` AS `main`
WHERE `fatherid` =0

2 、一次性查询出某节点的兄弟节点
SELECT *
FROM `tb`
WHERE `fatherid`=(SELECT `fatherid` FROM `tb` WHERE `ID`=6)

3 、一次性查询出某节点的父辈节点
SELECT *
FROM `tb`
WHERE `fatherid`=(
SELECT `fatherid`
FROM `tb`
WHERE `ID`=(SELECT `fatherid` FROM `tb` WHERE `ID`=13)
)

exists 和 in

in 适合内外表都很大的情况，exists 适合外表结果集很小的情况。

http://blog.itpub.net/category/385/14461

exists 和 in 使用一例
===========================================================
今天市场报告有个sql 及慢，运行需要20 多分钟，如下：
update p_container_decl cd
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
where exists(
select 1
from (
select tc.decl_no,tc.goods_no
from p_transfer_cont tc,P_AFFIRM_DO ad
where tc.GOODS_DECL_NO = ad.DECL_NO
and ad.DECL_NO = 'sssssssssssssssss'
) a
where a.decl_no = cd.decl_no
and a.goods_no = cd.goods_no
)
上面涉及的3 个表的记录数都不小，均在百万左右。根据这种情况，我想到了前不久看的tom 的一篇文章，说的是exists 和in 的区别，
in 是把外表和那表作hash join ，而exists 是对外表作loop ，每次loop 再对那表进行查询。
这样的话，in 适合内外表都很大的情况，exists 适合外表结果集很小的情况。

而我目前的情况适合用in 来作查询，于是我改写了sql ，如下：
update p_container_decl cd
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
where (decl_no,goods_no) in
(
select tc.decl_no,tc.goods_no
from p_transfer_cont tc,P_AFFIRM_DO ad
where tc.GOODS_DECL_NO = ad.DECL_NO
and ad.DECL_NO = ‘ssssssssssss’
)

让市场人员测试，结果运行时间在1 分钟内。问题解决了，看来exists 和in 确实是要根据表的数据量来决定使用。

not in <>not exists

请注意not in 逻辑上不完全等同于not exists ，如果你误用了not in ，小心你的程序存在致命的BUG ：

 

请看下面的例子：
create table t1 (c1 number,c2 number);
create table t2 (c1 number,c2 number);

insert into t1 values (1,2);
insert into t1 values (1,3);
insert into t2 values (1,2);
insert into t2 values (1,null);

select * from t1 where c2 not in (select c2 from t2);
no rows found
select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
c1 c2
1 3

正如所看到的，not in 出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select 语句的执行计划，也会不同。后者使用了hash_aj 。
因此，请尽量不要使用not in( 它会调用子查询) ，而尽量使用not exists( 它会调用关联子查询) 。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，正如上面例子所示。
除非子查询字段有非空限制，这时可以使用not in , 并且也可以通过提示让它使用hasg_aj 或merge_aj 连接。

六、索引index【类似目录的作用，加快检索速度】

1.数据库术语
索引，使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，例如 employee 表的姓名（name）列。如果要按姓查找特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。
在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。
索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。
在数据库关系图中，可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。

2.注意
并非所有的数据库都以相同的方式使用索引。作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。在多数情况下，索引用于数据检索的速度优势大大超过它的不足之处。但是，如果应用程序非常频繁地更新数据或磁盘空间有限，则可能需要限制索引的数量。
可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。
如果经常同时搜索两列或多列或按两列或多列排序时，索引也很有帮助。例如，如果经常在同一查询中为姓和名两列设置判据，那么在这两列上创建多列索引将很有意义。
确定索引的有效性：
检查查询的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以选择的对象。
对新索引进行试验以检查它对运行查询性能的影响。
考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。
检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。
检查某列中唯一数据值的数量，并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性，这有助于确定该列是否适合建立索引，如果适合，确定索引的类型。

3.建立索引的优点
1).大大加快数据的检索速度;
2).创建唯一性索引，保证数据库表中每一行数据的唯一性;
3).加速表和表之间的连接;
4).在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

4索引的缺点
1).索引需要占物理空间。
2).当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
在创建索引之前，您必须确定要使用哪些列以及要创建的索引类型。

转载于:https://my.oschina.net/xsh1208/blog/174657