数据库相关
数据库增删改
数据库增加语句
create TABLE_NAME as
数据库删除语句
delate from TABLE_NAME where …
数据库修改语句
数据库查询
sql数据库基本查询语句
查询完整语句,必填<>,选填[],或|
select [all|distinct]<目标列表达式>[,<目标列表达式>]…
from<表名或视图名>[,<表名或视图名>]…
[where <条件表达式>]
[group by<列名>[having <条件表达式>]]
[order by <列名>[asc|desc]];
- 第一行中,select语句必须存在,并且在select语句后必须存在至少一个查询后保留的列名,列名可以由*代替,或者表达式代替。可选参数all或distinct,默认为all。当参数为all时保留所有重复元组,当参数为distinct时去掉所有重复元组。
- 第二行中,from语句必须存在,并且在from语句后必须存在至少一个查询的表名或视图名。
- 第三行中,where语句可选。其中条件表达式可以由多个and或or连接的表达式组成。
- *第四行中,group by语句按列分组,值相同的为一组,配合集函数使用,使得集函数的作用域到表中的每个组内。
- *第五行中,order by语句按列排序。可选参数asc或desc,分别表示升序和降序,默认为升序排列。
外连接为from <列名>
join <列名> on (连接条件) [join <列名> on (连接条件)]
sql数据库快捷查询
在PL/SQL中,存在快捷操作方式:
-
输入sf按下空格可以得到select * from
-
输入w按下空格可以得到where
--浏览全体
select * from ...
--是否相等(冗余)
select * from ... where ... != ...
--是否为空
select * from ... where ... is not null;
--是否单值
select distinct ... from ...
--模糊查询
select * from ... where ... like'BU%'
--是否是子集
select * from ... where ... not in (select ... from ...)
--是否是两个表中的子集
select * from ... where ... not in
(SELECT ... FROM ...
UNION
SELECT ... FROM ...)
字符串连接工具
||
concat
if条件语句
decode(e.segment8, ‘0’, ‘NO’, ‘YES’)
NVL默认
group by
sql数据库查询优化
索引
关于索引失效的问题
关联查询与嵌套查询
关于关联查询与嵌套查询速度问题
参考资料Mysql嵌套查询与连接查询的效率问题与
子查询是实现关联式计算的一种实例,连接实现了关联式代数。关于关联式计算(relational calculus)和关联式代数(relational algebra),貌似比较数学或者理论性的东西理论,我也还没深入弄明白。
很多人也说到,这个性能的比较,不能一概而论的,需要具体的每个Case具体分析。
也有人说(并有在某种DBMS上用一个例子证明),子查询可以也可能被优化为与join一样的执行计划,性能可以一样的。
where语句
今天优化了一段项目中的代码。代码的作用就是在用户表和用户账户表中查出所有有账户的用户,当然查出来的用户应该是不重复的(distinct一下),这2个表中的记录大约都为2万条左右,数据量还是很大的,代码通过hibernate的转换出来是这样的:
select *
from (select rownumber() over() as rownumber_, row_.*
from (select distinct userpo1_.ID as col_0_0_
from RUN08110.T_ACCUSR accountuse0_
inner join RUN08110.T_USRINFO userpo1_
on accountuse0_.USER_ID = userpo1_.ID
where 1 = 1
and accountuse0_.CHK_FLAG <> 0
and (userpo1_.IDENTITY in (4, 5, 6, 7))) as row_) as temp_
where rownumber_ <= ?
很简单的一条语句,没有必要详细的解释。看起来任何问题都没有,运行时肯定不会报错,但查询速度非常非常慢,从后台取出数据到前台分页显示,如果计算机性能不太好,可能需要1分半钟的时间,问题困扰了我很长时间,我尝试着更换表之间的连接方式,但是效果很不明显,真是百思不得其解。后来和同事一起分析了一下,我把’<>‘换成了’>’,把 ‘in (4 , 5 ,6 , 7)‘换成了’>3’(和以前的条件是等价的),重启服务器,再执行一遍查询,简直太不可思议了,查询速度非常非常快,优化成功!看来用sql查询时’<>'以及’in’不是随便用的,使用得不当可能会严重影响查询速度,要慎用!
left join 语句
LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行,即使在右表 (Orders) 中没有匹配的行。
若现在我们拥有人员表“PERSON”以及人员订单表“ORDER”
现在,我们希望列出所有的人,以及他们的定购 - 如果有的话。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
left join 的执行原理
mysql采用嵌套循环的方式处理left join。
SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)其中
P1
是on
过滤条件,缺失则认为是TRUE
,P2
是where
过滤条件,缺失也认为是TRUE
,该语句的执行逻辑可以描述为:
FOR each row lt in LT {// 遍历左表的每一行
BOOL b = FALSE;
FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行,找到满足join条件的行
IF P2(lt, rt) {//满足 where 过滤条件
t:=lt||rt;//合并行,输出该行
}
b=TRUE;// lt在RT中有对应的行
}
IF (!b) { // 遍历完RT,发现lt在RT中没有有对应的行,则尝试用null补一行
IF P2(lt,NULL) {// 补上null后满足 where 过滤条件
t:=lt||NULL; // 输出lt和null补上的行
}
}
}
根据上面的推导过程,结论是利用 left join on 条件限制,如果右表不满足 on 条件则会补空值;而 where 条件限制是在遍历右表的过程中,先进行 on 条件判断再进行 where 条件判断,若都满足则输出连接后的行,如果仅没有满足on条件,则在最后补空,补空后若满足where判断,则输出空行。这个过程可以看做是在 left join 结束后,将不满足 where 条件的直接删除。
where与left join on中
对于限制左表,where意味着规定而join意味着关联,规定会吧左表中的值减少,而关联不会改变左表中的值。
对于限制右表,where会删除不符合条件行,而on会将不符合条件的行保留并补空值。
以下示例可以方便理解
1.只有1个on条件
这里可以直接看第一部分中的例子。最终会输出以左表为准,右表匹配不上补null的结果,但可能会有多对多的情况。
2.有2个on条件
上图是在关联条件中增加了b.age=24之后的输出结果。由于对b表进行了限制,满足条件的只有一个,但是由于没有where条件,因此依然要以左表为准,又因为是一对一,所以输出还是左表的记录数。更极端的,我们可以“清空”b表。
以上两种情况,在b表中都没有符合条件的结果,因此在以左表为准的基础上,右边的所有字段都为空。
3.有where的情况
将b.age=24写到where里,发现结果中只有这一行,打破了“left join”以左表为主的限制。同样再来看下后两种情况写到where里会发生什么:
没错,结果全部是为空的。因为where 在 on 后面执行,而on生成的结果里没有满足条件的记录!
group by语句
sql操作小知识
前端转义字符
在 html 中 <> 中包含内容往往有含义,比如
表示换行,如果需要打印
到屏幕上,则需要使用转义字符 < br/ >
<代表<
字符串连接工具
||
concat
if条件语句
decode(e.segment8, ‘0’, ‘NO’, ‘YES’)
NVL默认
pl/sql查询
存储过程
触发器
数据库高级查询
Java查询
数据库结构
数据库规范化
数据库关系模式五元组表达
R( U , D , DOM , F )
R 关系名
U 组成该关系的属性名集合
D 属性组U中属性所来自的域
DOM 属性向域的映像集合
F 属性间数据的依赖关系集合
数据库关系模式可简化为三元组表达
R( U , F )