Oracle

创建序列

Create sequence ACCOUNTFREEZETHAW_SEQ Increment by 1 Start with 1 Maxvalue 999999 Minvalue 1

查询序列

SELECT ACCOUNTFREEZETHAW_SEQ.NEXTVAL AS id from dual

联合查询：
并集（去重复）：
select * from t_user1
union
select * from t_user2;

并集：(不去重复)
select * from t_user1
union all
select * from t_user2;
交集：(相同的数据)
select * from t_user1
intersect
select * from t_user2;
差集：(相差的数据)
select * from t_user1
minus
select * from t_user2;
内连接：
select * from emp t,dept d where t.deptno=d.deptno;
类似：select * from emp e inner join dept d on e.deptno=d.deptno; inner可以省略；
外连接：
左外连接：select * from emp e left join dept d on e.deptno=d.deptno;
右外连接：select * from emp e right join dept d on e.deptno=d.deptno;

假如数据库字段stuno 字段值为110

userName 值为jAva1234

lpad() 向左补全字符串：select lpad(stuno,6,'0') from t_user3; 结果为000110
rpad() 向右补全字符串：select rpad(stuno,6,'0') from t_user3; 结果为110000
lower() 返回字符串小写：select lower(userName) from t_user3; 结果为 java1324
upper() 返回字符串大写：select upper(userName) from t_user3;结果为 JAVA1324
initcap() 单词首字符大写：select initcap(userName) from t_user3;结果为 Java1324
length() 返回字符串长度：select length(password) from t_user3;
substr() 截取字符串：select substr(userName,1,2) from t_user3;结果为 ja 索引是从1开始的substr(字段名,起始位置,长度)
instr() 获取字符串出现的位置：select instr(password,'23',2,2) from t_user3;

instr(password,'要查找的字符串',起始位置,第几次出现)
ltrim() 删除左侧空格:select ltrim(userName) from t_user3;
rtrim() 删除右侧空格:select rtrim(userName) from t_user3;
trim() 删除两侧空格:select trim(userName) from t_user3;
concat() 串联字符串：select concat(userName,password) from t_user3;
reverse() 反转字符串：select reverse(userName) from t_user3;