一、sql基础

查询city字段以A或L或N开头的数据

SELECT * FROM Persons WHERE City LIKE '[ALN]%'

查询city字段不以A或L或N开头的数据

SELECT * FROM Persons WHERE City LIKE '[!ALN]%'

连接符

select concat(id,’的学号’,cardno,’的卡号’);  #Mysql语法
select 'hello'||'oracle' test from dual;   #Oracle语法(oracle也可以用上边的concat)

字符串

select substr('hello',0,3) from dual    --下标1与0效果一样   结果：hel
select length('hello') from dual   --结果：5
select replace('hello','l','x') from dual   --结果：hexxo

数值

select round(45.926,2) from dual  --45.93  四舍五入
select trunc(45.926,2) from dual  --45.92  截断
select mod(1600,300) from dual    --100

日期

select  ename,round((sysdate-hiredate)/7) from emp –入职周数
select ename,round(months_between(sysdate,hiredate)) from emp  --入职月数
select add_months(sysdate,3) from dual  --3个月后的日期   2018/3/26 21:29:19

to_char函数

select ename,to_char(hiredate,'yyyy-mm-dd') from emp --1981--02—20
select ename,to_char(hiredate,'fmyyyy-mm-dd') from emp --1981-2-20
select to_char(sysdate,'yyyy-mm-dd hh :mi:ss') from dual—分钟是mi

to_number函数

select to_number('10')+to_number('12') from dual --将字符串转换为数字
--查询1980和1985年入职的员工
select * from emp where to_char(hiredate,'yyyy')=1980 or to_char(hiredate,'yyyy')=1985

to_date函数

select * from emp where hiredate between '1890-01-01' and '1982-12-31'  #mysql语法
#oracle不能像上边那样写，因为hiredate是日期类型，与字符串类型匹配不上
select * from emp where hiredate between to_date('1980-01-01','yyyy-mm-dd') and to_date('1980-12-31','yyyy-mm-dd')  #oracle语法

decode函数（只有oracle有该函数）

select job, decode(job, 
'CLERK', '业务员',
'SALESMAN','销售员',
'其他'
) from emp;
#以下语法（case/when）可以替换上边的表达
select job,
case job 
   when 'CLERK' then '业务员'
   when 'SALESMAN' then '销售员'
     else '其他'
       end
from emp;

约束

create table  person(

pid number(5),
pname varchar2(30) not null,
gender number(1),
tele varchar2(11) ,

constraint pk_pid primary key(pid),-- constraint后是起的名字
constraint check_gender check(gender in(0,1)),--性别只能是1或2
constraint unique_key unique(tele)
)

外键

create table   orders(   --订单
oid number(5) primary key,
totalprice number(8,2)
)

create table orderdetail(  --订单项   键盘，鼠标是两个订单项，属于同一订单
oidid number primary key,
price number(8,2),
name varchar2(30),
oid number(5),--外键
constraint fk_orderdetail_orders foreign key(oid) references orders(oid)
);

聚合函数不能写在where之后

SELECT city FROM weather WHERE temp_lo = max(temp_lo); #语法错误，修改为如下写法是正确的
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);  #语法正确

where与having的区别

WHERE在分组和聚合计算之前选取输入行(它控制哪些行进入聚合计算)；而HAVING在分组和聚合之后选取输出行。
因此，WHERE 子句不能包含聚合函数；因为试图用聚合函数判断哪些行将要输入给聚合运算是没有意义的。 相反，HAVING子句总是包含聚合函数。当然，你可以写不使用聚合的HAVING 子句，但这样做没什么好处，因为同样的条件用在WHERE阶段会更有效。

15.创建表空间

--创建表空间,收费目录系统所需
create tablespace EFMIS
datafile 'F:\oradata\orcl\EFMIS.dbf' size 1024M    
autoextend on next 256M maxsize unlimited logging  
extent management local autoallocate
segment space management auto;

create tablespace FS_BASE_TBS
datafile 'F:\oradata\orcl\FS_BASE_TBS.dbf' size 512M    
autoextend on next 256M maxsize unlimited logging  
extent management local autoallocate
segment space management auto;

16.创建用户

--创建用户(用户名密码你自己定义)
create user test identified by 12345;
--给用户赋权
grant  connect,exp_full_database,imp_full_database,resource    to test;

--授予DBA权限：
 grant connect,resource,dba to test;
--赋予不同的权限
grant dba to sgw;
grant select any table to sgw;
grant insert any table to sgw;
grant update any table to sgw;
grant delete any table to sgw;

--解锁用户
alter user sgw account unlock;

17、删除用户及用户下的所有数据

drop user user_name  cascade;

18 查询mysql哪些表死锁了

#查看死锁的表
show OPEN TABLES WHERE IN_USE > 0;
# 查看进程号
SHOW PROCESSLIST;
# 删除进程号
kill 3776;

19 查看一个用户有哪些权限

select * from user where user='zhangsan';

二、数据类型

浮点型

float(m,d);   #单精度浮点型    8位精度(4字节)     m总个数，d小数位
double(m,d);  #双精度浮点型    16位精度(8字节)    m总个数，d小数位

char与varchar

char：
固定长度；例如某字段定义为10个长度，该字段存了一个汉字”是”，这个汉字占两个字符，剩下八个字符会自动填满；最大长度255，长度没有默认长度，必须显式指定长度
varchar2：
长度不固定，同上，剩下八个用不到的字符空间会释放出去。Varchar2与mysql里的varchar一样；最大长度3999(即可以放2000个中文或3999个英文)，没有默认长度，必须指定长度；

number

number：默认长度是8
number(3):存的最大数是999
number（3，2）:存的最大数是9.99

date：

相当于mysql里的datetype，mysql里的date只有年月日，datetype还包括时分秒

timestamp:

也是日期类型，相对于date来说，精度高，最多可以保留到秒后9位小数

long

相当于mysql里的longtext，即大文本，可以支持两个G

clob：

可以放四个G内容

blob：

可以放四个G内容，存放电影，图片等；

定点数

#浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。 
decimal(m,d) 参数m<65，指的是总个数；d<30且 d<m，指的是小数位个数。

三、注意点

union与union all

UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

删除数据——delete与truncate

delete from person where name=’tom’；# delete from person删除全部数据；
truncate person;  # 删除全部数据
delete:可以回滚，也可以闪回；
delete删除会产生磁盘碎片，且不释放空间；truncate不会产生磁盘碎片；
truncate先摧毁表结构，再重构表结构；delete只删除数据而已；

savepoint事物保存点

insert into person(pid,pname,gender,tele) values('1','tom','1','13222229090');
savepoint a;
update  person set tele='1111111111' where   pid='1';
savepoint b;
select * from  person;
rollback to a;  #回滚到事物保存点a之前

视图 view
视图可以理解为一个虚表，封装了一条复杂的查询语句，视图可以隐藏敏感列；
语法：create view 视图名 as sql查询语句(最后是复杂的sql查询语句，可以用括号括起来)

先登录管理员用户，为当前用户(scott)提供创建视图的权限：grant create any view to scott;
create view person_view as (select pname from  person where  pid='1')；#创建视图
select * from   person_view;--查出来的是姓名
#优点：隐藏敏感列：比如隐藏工资与奖金列
create view view_emp as(select empno,ename,job,mgr,hiredate,deptno from emp)
select * from    view_emp；
修改(update)视图的话，会将视图里涉及到的表数据修改，所以一般创建视图时将视图设计成只读：
create or replace  view person_view  as (select pname from  person where  pid='1') with read only;   #此时该视图就不能update了

序列——sequence
auto increment语法只有mysql有，oracle没有；序列主要是做主键自增的，是独立于表之外的对象；
```sql
create sequence seq_person; --创建序列，没有与任何表产生关系，独立于表之外
select seq_person.nextval from dual --1 每执行一次，得到的结果依次加1
select seq_person.currval from dual --查看当前的值
insert into person(pid,pname) values (seq_person.nextval,'tom');
序列一般单表单用，有几个表就创建几个序列，如果多个表操作同一张表的序列，容易出现断层；
创建序列的复杂语法如下：

create sequence test
minvalue 3 --最小值，默认是1
increment by 2 --步增值，默认1
start with 5 --起始值，默认1
maxvalue 20 --最大值，默认值是18个9。不做循环的话，超过最大值会报错
cycle --循环默认是nocycle
cache 5 --缓存，默认缓存的数是20(缓存在内存里)，这里的值必须小于每次循环出来的数的个数
select test.nextval from dual；上边的复杂语法不能做主键自增，主键会冲突；即序列主要是做主键自增，也可以做其他事；

6. 索引——index
也是独立于表之外的对象；作用是提高查询效率(相当于书的目录)；
```sql
使用索引规则：
1、表里的数据经常被修改的话，不适合建索引；
2、数据量小时不用建立索引；
3、某些字段不会被当成条件做查询时，没有必要建立索引
4、外键字段需要加索引

语法：create index 索引名 on 表名(字段名);
eg: create index index_emp_ename on emp(ename);
select * from   emp where ename='SMITH';  --建索引前后查询所花的时间：0.031  0.016
数据少的话效果不明显，下边创建500万条数据
create table  t_test(
tid number,
tname varchar2(30)
)
begin
  for i in 1..5000000
    loop
      insert into t_test values(i,'测试数据'||i);
    end loop;
end;
create index test_index on t_test(tname);
select * from  t_test where tname='测试数据4565558';#这样数据量大的话效果会很明显;

复合索引：对表里多个字段做索引
   create index index_emp on emp(ename,job)；# 注意，此时只有将ename，job同时作为查询条件时才会用到索引，不然不会用到索引的，比如select * from   emp where job='' and hiredate=''是不会用到索引的；

查询表里的索引

--根据表名查询表里的索引（看不到列名）
select * from user_indexes where table_name=upper('fsc_item_addition'); 
--根据表名查询表里的索引（可以看到列名）
select * from  user_ind_columns where  table_name=upper('fsc_item_addition');

同义词

给用户分配创建同义词的权限：grant create synonym to scott;
同义词相当于别名；

使用场景：一个用户下想访问另一个用户下的某张表，可以这样写：
select * from   scott.emp；
此时为了访问方便，可以建立同义词：create public synonym emp for scott.emp  --public 可以不写
以后查询时：select * from emp；
删除同义词：drop public synonym emp --如果上边加了public，这里也要加上

数据导入导出
8.1、用于数据备份；

1、 exp system/root full=y; # 整库导出,基本不用;
2、按用户导入导出：把scott用户下的数据导入maltose01用户下：
注意：使用exp与imp这两个命令的话，电脑上必须安装oracle软件，所以下边的命令需要在虚拟机里执行
--先导出
exp scott/tiger@192.168.144.128:1521/orcl file='c:\scott.dmp'
--再将上边的数据导入到另一个用户
imp maltose01/maltose01@192.168.144.128:1521/orcl file='c:\scott.dmp' full=y
3、按表导入导出（plSql工具右键表就能导出该表）
--先导出
exp scott/tiger@192.168.144.128:1521/orcl file='c:\scott.dmp' tables=emp，student，teacher
--再导入到另一个用户
imp maltose01/maltose01@192.168.144.128:1521/orcl file='c:\scott.dmp' full=y  tables=emp，student，teacher

8.2、导入的几种情况

imp sfml/1@127.0.0.1:1521/orcl  file=e:\ACT_GE_BYTEARRAY.dmp  TABLES=(ACT_GE_BYTEARRAY) full=y ignore=y;
imp CCENSE/CCENSE@orcl file=D:\DBBackUp\oracleExp.dmp log=D:\DBBackUp\oracleImp20161103.log FULL=y;

--dmp文件存放目录：D:\worksoftware\oracleinstall\admin\orcl\dpdump
impdp SGW666/sgw123@orcl  dumpfile=22.DMP   remap_schema=tjfs:SGW666  transform=segment_attributes:n;

impdp SGW666/sgw123@192.168.1.101:1521/orcl  directory=DATA_PUMP_DIR dumpfile=SFML20190506-DP.DMP remap_schema= sfml:SGW666;

--指定表空间：EFMIS是表空间
impdp SGW666/sgw123@orcl remap_schema=sfml:SGW666  remap_tablespace=EFMIS:EFMIS,FS_BASE_TBS:sfml_log,SDT:EFMIS directory=DATA_PUMP_DIR dumpfile=22.DMP parallel=4

6.3、导入时报错日志：

D:\worksoftware\oracleinstall\admin\orcl\dpdump\import.log文件

9、PlSql基本语法

格式：
    declare  --声明：变量 常量 游标 异常
    begin
      --逻辑处理语句
        exception
          --异常处理语句 相当于 try catch
    end;
    # 其中，declare与exception可以不写

eg1：
    declare
      -- 下行的语法相当于java里的:private int age=1;
      age  number(3) := 1;       -- :=就是赋值，=相当于java里的==
    v_sex constant number(1):=1;    --声明常量，constant相当于java里的final
      name varchar2(30) := 'smith';
    begin
      age := 100; --对age重新赋值
      dbms_output.put_line(age);     --将变量age打印到控制台
      dbms_output.put_line(name);
    end;

eg2：
    declare
      name varchar2(30) := 'smith';
    begin
      select ename into name from  emp where empno='7788';--将表里查出来的值赋值给变量name
      dbms_output.put_line(name); --scott，而不是smith
    end;

eg3：
    declare
      name  varchar2(30) := 'smith';
      v_sal emp.sal%type := 100; --不确定数据类型时，使用引用型变量，即此处定义的v_sal数据类型与emp表的sal字段类型一致
    begin
      select ename,sal into name,v_sal from emp where empno = '7788'; --将表里查出来的多个值赋值给多个变量name
      dbms_output.put_line(name || v_sal); --scott，3000
    end;

eg:4：
    declare
      v_row emp%rowtype; --记录型变量，没法初始化值，接收某一条记录，例如接收某一员工的所有信息
    begin
      select * into v_row from emp where empno = '7788';
      dbms_output.put_line(v_row.ename || v_row.sal || v_row.job);
    end;

10、if分支

1、
    if 条件 then
      逻辑处理语句
    end if;
2、
       if 条件 then
          逻辑处理语句
    else
          逻辑处理语句
    end if;
3、
    If 条件 then
          逻辑处理语句
    elsif 条件 then
          逻辑处理语句
    ………
    else
       逻辑处理语句
    end if;

eg：
    declare
      v_age number(4):= &age; --&后随便写，这样写可以弹出一个框，可以输入年龄值
    begin
      if v_age<18 then
       dbms_output.put_line('未成年人');
      elsif  v_age>=18 and v_age<=60 then
         dbms_output.put_line('成年人');
      else
        dbms_output.put_line('老年人');
      end if;
end;

11、循环

1、无条件循环(必须有条件退出)
    loop

    end loop;
    eg:
    --打印1-100
    declare
      v_num number := 1;
    begin
      loop
         if v_num > 100 then
            exit;--退出，这里可以不用if：exit when v_num>100;
         end if;
         dbms_output.put_line(v_num);
         v_num := v_num + 1;
      end loop;
    end;

2、有条件循环
    declare
      v_num number := 1;
    begin
      while v_num <= 100
        loop
            dbms_output.put_line(v_num);
            v_num := v_num + 1;
        end loop;
    end;

3、for循环
    declare
      v_num number:=1;--此处不定义v_num,下边照样可以执行
    begin
      for v_num in 1..100
      loop
          dbms_output.put_line(v_num);--不需要加1，for循环自动加1
      end loop; 
    end;

12、游标cursor

用来接收多个对象；
定义语法：cursor 游标名  is sql查询语句;
使用语法：
    open 游标名
      loop
         fetch 游标名 into 记录型变量
           exit when 游标名%notfound;
      end loop;
    close;

eg:
--打印所有员工信息
    declare
      cursor c_emp is select * from emp;
      v_row emp%rowtype;
    begin
      open c_emp;
      loop
        fetch c_emp into v_row;
        exit when c_emp%notfound;
        dbms_output.put_line(v_row.ename ||'-----'|| v_row.job);
      end loop;
      close c_emp;
    end;

eg2:
    --打印指定部门的员工信息
    declare
      --number后不能写长度
      cursor c_emp(v_no number) is select * from emp where deptno=v_no;
      v_row emp%rowtype;
    begin
      open c_emp(10);  --打印10号部门员工
      loop
        fetch c_emp into v_row;
        exit when c_emp%notfound;
        dbms_output.put_line(v_row.ename ||'-----'|| v_row.job);
      end loop;
      close c_emp;
    end;

13、异常

1、预定义异常
    declare
      v_num number(6);
      v_name varchar2(1);
    begin
     -- v_num:=1/0;  --除数为0异常  ZERO_DIVIDE
      v_name:='smith'; --超出了长度异常 VALUE_ERROR
      exception
        when ZERO_DIVIDE then  --when后是异常名称（资料里有对应异常的名称）
             v_num:=0;
             dbms_output.put_line(v_num);
        when VALUE_ERROR then  --when后是异常名称（资料里有对应异常的名称）
             v_name:='s';
             dbms_output.put_line(v_name);
       when others then
          dbms_output.put_line('其他异常');
    end;

2、自定义异常
eg：年龄超过150时抛异常
    declare
      v_age number:=&age;
      exc_age exception;--异常的声明方式
    begin
      if v_age >150 then
        raise exc_age;  --抛异常
      end if;

 exception      --捕获异常
         when exc_age then
           --dbms_output.put_line('年龄太大，请检查输入');
           --弹出错误窗口,参数1是错误代码，-20001 与-29999之间的数随便用
           raise_application_error(-20001,'年龄太大，请检查输入');
    end;

14、存储过程

  存储过程是一段被命名话的plsql语句；已经预编译到数据库中的（普通sql是需要先编译的）；
语法：
  create or replace procedure 存储过程名 （参数1 in/out 数据类型…… ）
      as | is    --替代了declear,is与as作用一样，写一个就行
  begin

end;
注意：in可以省略(in表示输入参数，out表示输出参数)，数据类型后不可以加长度，括号里可以写多个参数；

eg：利用存储过程计算指定员工年薪
  create or replace procedure pro_yearsal(v_no in number)—in可以省略
is
  v_yearsal number(8,2);
begin 
  select sal*12+nvl(comm,0) into v_yearsal from   emp where empno=v_no;
  dbms_output.put_line('员工年薪：'||v_yearsal);
end;

--使用上边的存储过程有两种方式
1、
call pro_yearsal (7788);
2、
begin
   pro_yearsal (7788);
end;

存储过程参数是out：
eg：利用存储过程计算指定员工年薪，把计算出来的年薪放到out参数里
create or replace procedure pro_yearsal2(v_no in number,v_year_sal out number)
is  --is不能删

begin 
  select sal*12+nvl(comm,0) into v_year_sal from   emp where empno=v_no;
end;
--使用上边带out参数的存储过程的话，不能用call
declare
  v_yearsal number(8,2);
begin
   pro_yearsal2(7788,v_yearsal);
   dbms_output.put_line(v_yearsal);
end;

15、存储函数

语法格式：
    create or replace function 存储函数名(参数1 in/out 数据类型)
    return 数据类型
    is | as 
    begin

      return 具体值；
    end;

eg：利用存储函数计算指定员工年薪
    create or replace function  func_yearsal(v_no in number )
    return number 
    is
     v_sal number(8,2);
    begin
      select sal*12+nvl(comm,0) into v_sal from emp where empno=v_no;
      return v_sal;
    end;
使用上边的存储函数：
    declare
      v_sal number(8, 2);
    begin
      v_sal := func_yearsal(7788);
      dbms_output.put_line(v_sal);
    end;

存储过程与存储函数区别：

1、语法上不同,存储过程的关键自是procedure，函数关键字是function;
2、使用场景不同:
   存储函数多被存储过程调用，项目与项目之间的相互调用使用的是存储过程；
3、存储函数可以在sql中使用，存储过程不能在sql中使用
  select   ename,job,sal,comm,sal*12+nvl(comm,0),func_yearsal(empno) from emp;
4、函数必须有返回值，存储过程没有返回值，但是有传出参数；
5、函数注重的是结果，存储过程注重的是过程

16、jdbc调用存储过程

Connection conn=null; //使用的jar包：ojdbc14.jar;
CallableStatement stmt=null;//专门处理存储过程，存储函数
//参数一是in，参数二是out
stmt=conn.prepareCall(“{
  call pro_yearsal2(?,?)}”);
stmt.setLong(1,v_no);
stmt.registerOutParamentet(2,OracleTypes.NUMBER);//对out参数只能指定数据类型，不能赋值
stmt.execute();
int sal=stmt.getLong(2);

17、jdbc调用存储函数：

Connection conn=null;
CallableStatement stmt=null;//专门处理存储过程，存储函数
//参数一是in，参数二是out
stmt=conn.prepareCall(“{?=call pro_yearsal(?)}”);
stmt.setLong(2,v_no);
stmt.registerOutParamentet(1,OracleTypes.NUMBER);//对参数只能指定数据类型，不能赋值
stmt.execute();
int sal=stmt.getLong(1);

18、数据库输入错误密码多次的话会被锁定，登录system用户进行解锁

alter user maltose account unlock;

四、sql查询语句优化

在表中建立索引
尽量避免使用select *，返回无用的字段会降低查询效率
尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描；

优化方式：如果是连续数值，可以用between代替。如下：
SELECT * FROM t WHERE id BETWEEN 2 AND 3
如果是子查询，可以用exists代替。如下：
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE id = 1 OR id = 3
优化方式：可以用union代替or。如下：
SELECT * FROM t WHERE id = 1 UNION SELECT * FROM t WHERE id = 3
（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，即使union扫描的是索引，or扫描的是全表）

尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE username LIKE '%li%'
优化方式：将字符串反转之后，将%放在后边，尽量在字段后面使用模糊查询。如下：
SELECT * FROM t WHERE username LIKE 'li%'

尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE score IS NULL;
优化方式：可以给字段添加默认值0，对0值进行判断。如下：
SELECT * FROM t WHERE score = 0;

尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t2 WHERE score/10 = 9;
SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li';
优化方式：可以将表达式、函数操作移动到等号右侧。如下：
SELECT * FROM t2 WHERE score = 10*9
SELECT * FROM t2 WHERE username LIKE 'li%'

当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。

SELECT * FROM t WHERE 1=1;
优化方式：用代码拼装sql时进行判断，没where加where，有where加and。
建立索引：
在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX indexName ON table_name (column_name);
在表上创建一个唯一的索引，唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX indexName ON table_name (column_name)

排序时有空值时可以指定空值出现的位置，关键字：first/last
```
select * from emp order by sal desc nulls last ;
```
10、查询字段里有case when时，不要在case when里写sql，改为在外层关联其他表，在case when里只判断字段值就行

<!--优化之前，case when里写了查询sql-->
SELECT o.FID,
       o.FITEMCODE,
       o.FAGENIDCODE,
       o.FPIDCODE,
       o.FREGINID,
       o.FITEMNAME,
       o.FITEMTYPECODE,
       o.FFUNDMANAGEWAYCODE,
       FITEMTYPENAME,
       o.FISCLASSIFY,
       o.FISCONFIRM,
       o.FISFINAL ,
       (CASE
         WHEN (SELECT b.FSTDCONFIRMWAYCODE
                 FROM FSC_ITEM_ADDITION_YW b
                WHERE b.FITEMCODE = o.FITEMCODE
                  AND b.FAGENIDCODE = '5510d79c29584ec294c858c748c03bf9') IS not NULL THEN
          (SELECT b.FSTDCONFIRMWAYCODE
             FROM FSC_ITEM_ADDITION_YW b
            WHERE b.FITEMCODE = o.FITEMCODE
              AND b.FAGENIDCODE = '5510d79c29584ec294c858c748c03bf9')
         ELSE
          o.FSTDCONFIRMWAYCODE
       END) AS FSTDCONFIRMWAYCODE
  FROM FSC_ITEM_ADDITION o
 where o.FRGCODE = '555555'
   AND o.FAGENIDCODE = '5510d79c29584ec294c858c748c03bf9'
   AND o.FITEMCODE='000000023000011140525'

<!--优化之后，case when里只写判断的字段-->
 select a.FID,a.FITEMCODE,
                a.FAGENIDCODE,
                a.FPIDCODE,
                a.FREGINID,
                a.FITEMNAME,
                a.FITEMTYPECODE,
                a.FFUNDMANAGEWAYCODE,
                a.FITEMTYPENAME,
                a.FISCLASSIFY,
                a.FISCONFIRM,
                a.FISFINAL,
                (case
                  when y.FSTDCONFIRMWAYCODE is not null then
                   y.FSTDCONFIRMWAYCODE
                  else
                   a.FSTDCONFIRMWAYCODE
                end) as FSTDCONFIRMWAYCODE

          from (SELECT o.FID,
                         o.FITEMCODE,
                         o.FAGENIDCODE,
                         o.FPIDCODE,
                         o.FREGINID,
                         o.FITEMNAME,
                         o.FITEMTYPECODE,
                         o.FFUNDMANAGEWAYCODE,
                         o.FITEMTYPENAME,
                         o.FISCLASSIFY,
                         o.FISCONFIRM,
                         o.FISFINAL,
                         o.FSTDCONFIRMWAYCODE
                    FROM FSC_ITEM_ADDITION o
                   WHERE
                   o.FRGCODE = '555555'
                AND o.FAGENIDCODE = '5510d79c29584ec294c858c748c03bf9'
                  ) a
          left join FSC_ITEM_ADDITION_YW y
            on a.FITEMCODE = y.FITEMCODE

五、常用sql

1、查询数据库表里的个数

select count(*) from user_tables;

2、新建表空间

create tablespace tj_apartmentorasys logging datafile 
    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\RESOURCE\tj_apartmentorasys.dbf' 
    size 1024m autoextend on next 50m maxsize 
    unlimited extent management local segment space management auto;

3、新建用户

create user sgw
default tablespace users
identified by sgw666;

grant dba to sgw;
grant select any table to sgw;
grant insert any table to sgw;
grant update any table to sgw;
grant delete any table to sgw;
alter user sgw account unlock;

4、system解锁用户

ALTER USER 用户名  ACCOUNT UNLOCK;

5、MyBaties批量插入数据

dao：
void doInsert(@Param("list") List<ItemPolicyBasis> listCz);

oracle：
<insert id="doInsert" parameterType="com.bosssoft.nontax.basicinfo.standard.sfml.domain.ItemPolicyBasis">
        INSERT INTO FSC_ITEM_POLICY_YW( FID, FPOLICYID, FCREATETIME, FUPDATETIME, FALTERCODE, FAGENIDCODE, FITEMID)
        <foreach collection="list" item="item" index="index" separator="UNION ALL">
        <!--如果List是String类型的，则下边{}里直接写item即可-->
            (SELECT #{item.fid},#{item.fpolicyId}, #{item.fcreateTime},#{item.fupdateTime}, #{item.falterCode}, #{item.fagenIdCode}, #{item.fitemId}  FROM DUAL)
        </foreach>
</insert>

MySql：
<insert id="doInsertBantchYw" parameterType="com.bosssoft.nontax.basicinfo.standard.sfml.domain.ItemPolicyBasis">
        INSERT INTO FSC_ITEM_POLICY_YW( FID, FPOLICYID, FCREATETIME, FUPDATETIME, FALTERCODE, FAGENIDCODE, FITEMID)
        <foreach collection="list" item="item" index="index" separator="UNION ALL">
            (#{item.fid},#{item.fpolicyId}, #{item.fcreateTime},#{item.fupdateTime}, #{item.falterCode}, #{item.fagenIdCode}, #{item.fitemId} )
        </foreach>
</insert>

5.1、查询条件是list集合（oracle）

dao：
List<AgencyItem> queryAllItemByCodeList(@Param("list") List<String> list,@Param("fagenIdCode") String fagenIDCode);

Mapper.xml:
<sql id="AGENCY_ITEM_COLUMN">
        fid,fitemId,fagenIdCode,fupdateTime,fcreateTime,frgnId,frgnCode,fversion
</sql>
    <!--oracle语法-->
<select id="queryAllItemByCodeList" resultType="com.bosssoft.nontax.basicinfo.standard.sfml.domain.AgencyItem">
   SELECT  <include refid="AGENCY_ITEM_COLUMN"/>  FROM fsc_agency_item agenItem
   where  agenItem.fagenIdCode=#{fagenIdCode}
   and
   <foreach collection="list" item="item" open="agenItem.FITEMCODE in (" separator="," close=")">
        #{item}
   </foreach>
</select>

6、前端页面传入关键字查询，关键字里包含空格，将带有空格的关键字转换为list集合，即查询条件是list集合
6.1、结果包含空格前后之一的就显示出来

<select id="queryAllSpaceAgency"  resultType="com.bosssoft.nontax.basicinfo.standard.sfml.domain.Agency" parameterType="java.lang.String">
        SELECT distinct FID,FCODE,FNAME,FAGENIDCODE FROM FSC_AGENCY a WHERE (
        <foreach collection="list" item="item" index="index" separator="OR">
            a.fname LIKE CONCAT(CONCAT('%', #{item}), '%')
        </foreach>
        ) or(
        <foreach collection="list" item="item" index="index" separator="OR">
            a.FCODE LIKE CONCAT(CONCAT('%', #{item}), '%')
        </foreach>
        )
        order by FCODE
</select>

6.2、结果必须包含空格前后所有关键字才显示出来

<!--空格前后的关键字，全部包含才显示出来（区划为空），最多显示10条，fcode与fname拼接结果最短的优先显示， &lt;=是小于等于的意思-->
<select id="queryAllSpaceAgency"  resultType="com.bosssoft.nontax.basicinfo.standard.sfml.domain.Agency" parameterType="java.lang.String">
        SELECT * from ( SELECT distinct FID,FCODE,FNAME,FAGENIDCODE, (Concat(Concat(FCODE, ''), FNAME)) as CN FROM FSC_AGENCY a WHERE
        (
        <foreach collection="list" item="item" index="index" separator="AND">
            (Concat(Concat(FCODE, ''), FNAME)) LIKE CONCAT(CONCAT('%', #{item}), '%')
        </foreach>)
        order by nvl(length(trim(CN)), 0) asc
        ) where   rownum  &lt;= 10
    </select>
</select>

6.3、树类型的级联查询
从父节点查询下边的所有子节点

select *
  from  FSC_ITEM_ADDITION a where a.fisclassify='0'
 START WITH a.fitemcode = '1400000300000057'
connect by prior a.fitemcode = a.FPIDCODE

从子节点查询所有父节点

select *
  from  FSC_ITEM_ADDITION a
 START WITH a.fitemcode = '1400000500000012'
connect by prior a.FPIDCODE = a.fitemcode

7、查询表所占的内存大小

 select SEGMENT_NAME, TABLESPACE_NAME, sum(BYTES / 1024 / 1024) m
  from dba_extents
 where (SEGMENT_NAME like 'FSC%' OR SEGMENT_NAME like 'FSC%')
   AND SEGMENT_TYPE = 'TABLE'
 group by SEGMENT_NAME, TABLESPACE_NAME
 order by m desc

sql日常总结

一、sql基础

二、数据类型

三、注意点

序列一般单表单用，有几个表就创建几个序列，如果多个表操作同一张表的序列，容易出现断层；

四、sql查询语句优化

五、常用sql

热门文章

最新文章

相关课程

相关电子书

相关实验场景

sql日常总结

一、sql基础

二、数据类型

三 、注意点

序列一般单表单用，有几个表就创建几个序列，如果多个表操作同一张表的序列，容易出现断层；

四、sql查询语句优化

五、常用sql

热门文章

最新文章

相关课程

相关电子书

相关实验场景

三、注意点