SQL学习

1. distinct作用于所有列指的两列不完全相同跟主键一样

2.行限制用的rownum

··· where rownum<=5
3. order by
1) order by 应该放在检索的最后如果不是则会报错
2) order by 不仅可以用列名排序还可以按列位置排序
Eg:select * from life_dd_product_info d where d.series_no = ‘04’ ORDER BY 1,2(不推荐)
3如果需要降序排列需要在字段后面加 desc(多列都需要降序排列就必须在需要降序的列名后面增加desc)
Eg:select * from life_dd_product_info d where d.series_no = ‘04’ ORDER BY d.PRODUCT_ID DESC,d.PRODUCT_CODE
就是对product_id降序排列对product_code升序排列
Eg:select * from life_dd_product_info d where d.series_no = ‘04’ ORDER BY d.PRODUCT_ID DESC,d.PRODUCT_CODE DESC
就是对product_id与prodcut_code都进行降序排列
4. or
or的情况下当第一个条件满足时就不再考虑第二个条件
5. not还可以这么用eg:select * from life_dd_product_info d where NOT d.series_no in (‘01’,‘03’)
6. 通配符
1) %可以代表0个字符
2) 代表一个字符
3) 尽量不要把通配符放在开始的位置这样检测比较慢
7. 字符串拼接1) 使用|| 可以连接两个字符串eg:select d.PRODUCT_NAME || ‘(’ || d.PRODUCT_CODE || ‘)’ from life_dd_product_info d
2) 使用concat也可以连接两个字符串
Eg:select concat(product_name,’(’,product_code,’)’) from life_dd_product_info;
8. 常用文本处理函数
1) Length返回串的长度
Eg:select length(product_code) from life_dd_product_info;
2) Upper将串转换为大写
Eg:select upper(product_code) from life_dd_product_info;
3) Lower将串转换为小写
Eg:select lower(product_code) from life_dd_product_info;
4) Trim去除串两边的空格
5) LTrim去除串左边的空格
6) RTrim去除串右边的空格
7) SubString返回子串的字符
8) Soundex返回串的SOUNDEX值注意SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法
9) to_number() :提取数字
10) to_char() :提取日期
Eg:select * from life_dd_product_info where to_number(to_char(created_date,‘YYYY’)) = 2020;
11) To_date:转化日期
Eg:select * from life_dd_product_info where created_date between to_date(‘2020-01-01 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’) and to_date(‘2020-03-03 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
12) round():四舍五入
13) floor()、ceil()floor()取小于等于数值n的最大整数;ceil()取大于等于数值n的最小整数
14) abs()绝对值
15) greatest()求最大值
16) least()求最小值
17) nvl()如果为空时可以补上任意类型的值
18) mod()取余数
19) AVG(DISTINCT|ALL) ALL表示对所有的值求平均值,DISTINCT只对不同的值求平均值eg:select AVG(DISTINCT STD_PERM_SUM) as avg_perm from life_th_app_policy_info where product_code = ‘168’ and elis_policy_no is not null默认all,只能求单列忽略值为null的情况
20) COUNT(DISTINCT|ALL)
求记录、数据个数。 ALL对所有记录数组做统计 DISTINCT只对不同值统计(相同值只取一次)count() 统计行的数目无论是null还是非空count(字段名)只统计非空的情况eg:select count(DISTINCT STD_PERM_SUM) from life_th_app_policy_info where product_code = ‘168’ and elis_policy_no is not null
21) Max():求最大值忽略null,一般数值和日期可以求出来如果是文本的话返回该列排序以后的最后一行
22) Min():求最小值忽略null,一般数值和日期可以求出来如果是文本的话返回该列排序以后的第一行
23) STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
Eg:SELECT STDDEV(SAL) FROM SCOTT.EMP;
Eg:SELECT STDDEV(DISTINCT SAL) FROM SCOTT.EMP;
24) VARIANCE(DISTINCT|ALL)
求协方差 ALL表示对所有的值求协方差,DISTINCT表示只对不同的值求协方差
Eg:SELECT VARIANCE(SAL) FROM SCOTT.EMP;
Eg:SELECT VARIANCE(DISTINCT SAL) FROM SCOTT.EMP;
25) SUM(DISTINCT|ALL)忽略null
求和 ALL表示对所有值求和DISTINCT表示只对不同值求和(相同值只取一次)
Eg:SELECT SUM(SAL) FROM SCOTT.EMP;
Eg:SELECT SUM(DISTINCT SAL) FROM SCOTT.EMP;
26) COUNT(DISTINCT|ALL)
求记录、数据个数。 ALL对所有记录数组做统计 DISTINCT只对不同值统计(相同值只取一次)
Eg:SELECT COUNT(SAL) FROM SCOTT.EMP;
Eg:SELECT COUNT(DISTINCT SAL) FROM SCOTT.EMP;
27) MEDIAN():求中位数
Eg:SELECT MEDIAN(SAL) FROM SCOTT.EMP;
Eg:SELECT MEDIAN(DISTINCT SAL) FROM SCOTT.EMP; --错误DISTINCT 选项在此函数中禁用。
9. Group by
1) Group by 必须出现在where之后order by 之前
2) null会作为一个分组返回
3) Group by 子句中每一列都必须是检索列不能是聚合函数如果在select中使用表达式group by 必须使用相同的不能使用别名
4) Group by 子句中可以包含多列进行嵌套分组是指定所有的列都一起计算
Eg:select d.SERIES_NO,d.product_name,COUNT() AS prodcut_num from life_dd_product_info d group by d.SERIES_NO,d.product_name;
10. Having 对分组以后可以进行筛选
Eg:select d.SERIES_NO,COUNT() AS prodcut_num from life_dd_product_info d group by d.SERIES_NO having count() >=10
11. 子查询的返回只能是单个列1可以用在left joineg:
LEFT JOIN (SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PAIC_UM_NUM ORDER BY UPDATED_DATE DESC) RN,
PAIC_UM_NUM,
TFZX_NAME,
TDEP_NAME
FROM LIFE_TJS_NCHRMS_EMPLOYEE_ALL) EE2) 可以放在select后面的
select(select fpi.value from life_th_frey_params_info fpi where fpi.key=‘customerId’ and fpi.object_id = pi.th_id) 销售系统客户号
12. Union 联合查询规则1 每个查询所包含的列表达式或聚合函数必须相同顺序可以不同2 列数据的类型必须兼容3 会自动去除重复的行如果不想去除使用union all4 使用union只能使用一条order by 放在最后对整体的结果集进行排序
13. Insert select 联合使用1可以联合使用列名的数目要相同可以使用where过滤注意主键重复
14. 创建表的是利用default 指定默认值oracle中默认日期值为 sysdate
15. 创建视图语句democreate view 视图名 ASselect 结果
16. 事务回退可以回退哪些语句:insert,update,delete不能回退create或drop操作
17. Oracle事务使用操作为(1) set transaction
……
commit;(rollback)
(2) 可以设置和使用保留点保留单点越多回退越方便设置SAVEPOINT delete1;使用保留点ROLLBACK TRANSACTION delete1;
18. 数据库可以检查某个条件比如说某个字段不为null,性别必须为男女语法如下
check (条件)
19. 索引应该注意问题1.索引虽然改善查询的性能但是降低了插入修改和删除的性能。因为执行这些操作时数据库必须动态的更新索引并且占用大量的储存空间2 索引用于数据过滤和排序经常用来排序的列也适合做索引3 创建索引的语句CREATE INDEX 索引名一般index索引名必须唯一ON 表名字段名
20. 触发器1触发器具有insert,update,delete的权限
2用途 a.保持数据一致性eginsert或update时改变某一字段的大小写
b.基于某个表的变动eg:每当更新或删除一行时将记录写入某个记录表 c.计算计算列或者更新时间戳
3触发器创建的语句 create [or replace] trigger 触发器名 触发时间 触发事件
on 表名
after insert or update
[for each row]
begin
pl/sql语句
end
21. oracle中
=应该相当于 a = :b 表明b是个绑定变量需要执行时进行变量绑定
:= 相当于一般编程语言中的 赋值 a := 1 即将 数字1赋值给变量 a