避免使用 where 1=1
使用 where 1=1 后索引将失效其他查询优化策略也将失效。
1.oracle聚合函数
sum 求和
count 统计(count会忽略null)
max 最大
min 最小
avg 平均值
其他函数
1.substr(列明,起始位置,结束位置) 返回截取后的字符串
2.length(列明) 返回数据长度
3.union 查询结果集去除重复字段 加all的话 union all的话允许重复
类型转换函数:
to_char
to_date
to_number
oracle自己的函数
last_day()
2.索引
好处:使用索引可以加快查询速度
坏处:使用索引消耗磁盘空间,在执行索引改动时营销效率(因为会更新索引)
创建普通索引
索引分类：
1.Unique 唯一
2. primary key 主键
3.index普通
4.check 检查索引
create index 索引名 on 表名(字段1,.....)
删除普通索引
drop index 索引名
表连接：
1.内联 inner join
示例：select * from paymainsub p inner join prpnewpayment c on p.orderid = c.orderid;
2.不等值连接
示例:select * from paymainsub p inner join prpnewpayment c on p.orderid != c.orderid;
3.交叉连接
示例:select * from paymainsub p inner join prpnewpayment c on p.orderid != c.orderid;
4.自连接 自己连自己没什么用
示例:select * from paymainsub p inner join paymainsub c on p.orderid = c.orderid
5.左外连接:
示例select * from paymainsub s left outer join prpnewpayment p on s.orderid = p.orderid
6.右外连接:
示例:select * from paymainsub s right outer join prpnewpayment p on s.orderid = p.orderid
7.全外连接:
示例:select * from paymainsub s full outer join prpnewpayment p on s.orderid = p.orderid
子查询:
select * from paymainsub where orderid in (select orderid from paymainsub where source = '0')
select (select min(source) from paymainsub where source = '0') from paymainsub
select * from paymainsub p where exists (select * from paymainsub where tradeno='2015031221667141');---当 exists 后的条件判断为true时才会返回所有数据
批量增加数据
insert into paymainsub (orderid,source) select orderid,souce from paymainsub
3.复合主键:
两列或两列以上组成的唯一标识称为唯一索引
好处:保证数据的唯一性
坏处:效率低,在执行CRUD时维护表关系复杂
4.sql语句:
SQL默认值
create table tablename(name varchar(20) default '内容')
SQL创建主键
create table tablename(name Int not null primary key)
SQL创建联合主键
create table txtable(tid int not null ,tname varchar(20),tcarNo int not null,primary key(tid,tcarNo));
SQL表增加列
语法:alter table 表名 add 列明 数据类型
示例:alter table tble_name add coloum_name varchar(20);
SQL表删除列
语法:alter table 表名 drop列名
示例:alter table tble_name drop coloum_name
删除表 删除之前注意主外键关系
语法:drop table 表名
示例:drop table paymainsub
插入语句
语法:insert into 表名 (列明。。。。。) values (‘值’)(建议起别名这样出现问题的可能教小，例如只插入一个表中的两个字段)
示例:insert into tables t (t.cloumName....), values ('testName',.....)
更新语句(一定要加where除非特殊情况)
语法:update 表名 set 列名='值' where id = '1'
示例:update paymainsub set source = '1' where orderid='1';
update paymainsub set source ='1' 更新所有数据
删除语句(一定要加where除特殊情况)
语法:delete from tablename where 删除条件
示例:delete from paymainsub where orderid = '1'
查询语句(最后将*换成你需要的列 减少CPU消耗和内存消耗和网络传输)
语法:select * from 表名
示例:select * from paymainsub
排序语句:
语法:select * from 表名 order by 列明 asc/desc (asc正序/desc倒叙 默认asc)
示例:select * from paymainsub order by souce desc,money desc；--根据渠道和支付金额进行排序
分组语句 (必须放到语句的末端)
语法:select * from 表名 group by 列明
示例:select * from paymainsub where group by paytype;
having语句(只能在order by 或者 group by 后面执行)
语法:select * from 表名 group by 列明 having 列明 = 11
示例:select * from paymainsub where group by paytype having where orderid='1';
distinct去重
语法:select distinct(列明) from 表名
示例:select distinct(orderid) from paymainsub
模糊查询
语法:select * from 表名 where name like '%值%' -- '_'代表一个通配符,‘%’代表不限制数
示例:select * from 表名 where name like '_1_';--查询 类似于 a1a的数据
select * from 表名 where name like '%c%';--查询只要带有c的数据
select * from 表名 where name like '%[a,b]%';--查询带有 a或者b的数据
select * from 表名 where name like '$[^ab]$';--模糊查询不带有ab的数据
null值查询
语法：select * from 表名 where name is null;
示例: select * from paymainsb where name is null;-- 查询出name是null 或者是 的数据
select * from paymainsb where name is not null;-- 查询出name不是null 或者是 的数据
or 关键字:
语法:select * from 表名 where 列明 = '1' or 列明 = '2'
示例:select * from paymainsb where cname ='1' or ccount = '2';
in 多值关键字:
语法 select * from 表名 where 列明 in(值1，值1，值1，值1)
示例:select * from paymainsb where source in (1,2,3,4);
between and 范围关键字:
语法：select * from 表名 where 列明 between 最小值 and 最大值
示例: select * from pay where amont between 1 and 30;--查询 1 -- 30之间的信息
dual随机数
示例：SELECT dbms_random.value FROM DUAL
转换小写lower
示例:select lower(tradebank) from paymainsub
转换大写upper
示例:select upper(tradebank) from paymainsub
清除空格trim
示例:select trim(tradebank) from paymainsub
截取 substr
示例:select substr(p.tradeno,0,3) from paymainsub p
判断是否存在 instr
示例:select instr(p.tradeno,'a') from paymainsub p
字符串替换 replace
示例:select replace(tradeno,'原字符','替换字符') from paymainsub
获取当前系统时间
示例:select SYSTIMESTAMP from dual
转换时间格式
yyyy-MM-dd HH24:mi:ss yyyy-MM-dd HH24:mi:ss
示例:select * from paymainsub p where p.tradetime between to_date('2015/3/12 21:58:40','yyyy/MM/dd HH24:mi:ss') and to_date('2020/3/12 21:58:40','yyyy/MM/dd HH24:mi:ss');
SQL高级
SQL注入
1' = '1' 实现SQL注入
如何防止sql注入
1.过滤敏感字符如:or and select delete
2.使用参数话操作如jdbc的.setparamter
优化SQL的基本原则
1.尽量根据索引查询数据 创建索引
2.使用预编译语句
3.调整where后的判断条件 将子查询进量在前面进行
4.select 语句中避免使用 * 需要什么查什么
5.使用where替换having 因having是在查询出所有记录后才会执行操作
6.使用表别名 减少解析时间和列名重复报错等。
7.使用exists代替 in
8.避免在索引上使用 类似于 sum count 等函数这样会使索引失去意义导致查询表所有内容
9.尽量使用union all 代替 union 因为使用union时会尝试合并两个表的信息
10.is not null 判断不等于 会造成全表检索
11使用‘a%’会使用索引 使用 '%a' 或 'a%c'会造成全表检索
数据库事务四大特性 ACID
1.原子性 Atomicity
一个事务要么全成功 要么全失败
2.一致性 Consistency
是指事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
3.隔离性 Isolation
多个事务并发执行的话，结果应该与多个事务串行执行效果是一样的,不影响结果
4.持久性 Durability
操作完的数据永久的存储在数据库。
oracle创建自增字段
1. 创建序列CREATE SEQUENCE emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE
序列名.nextval 获取序列值
删除序列 drop sequence 序列名
