round()
select round(123.45,1) from dual;--123.5
select round(123.45,-2) from dual;--100
trunc()
select trunc(123.56) from dual;--123
select trunc(123.45,1) from dual;--123.4
select trunc(123.45,-1) from dual;--120
extract()
select extract(year from date '2018-05-06') as day from dual; --2018
select extract(month from date '2018-05-06') as day from dual; --5
select extract(day from date '2018-05-06') as day from dual; --6
--select extract(year from GXRQ) as day from table --2018
nvl(string1, replace_with)
如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL
distinct 或者group by 按多个条件去重
select distinct name, id from table
select * from table group by name,id
多表左联
select * from
表1 a left join 表2 b on a.字段1=b.字段2
left join 表3 c on b.字段2=c.字段3
left join 表4 d on c.字段3=d.字段4
创建表
create table 表名(
id number primary key,
name varchar(20) not null,
age number
)
创建表的时候可能用到select关键字
复制旧表部分字段
create table 新表名 as select 字段名,字段名 from 旧表名;
复制旧表
create table 新表名 as select * from 旧表名;
复制旧表字段
create table 新表名 as select * from 旧表名;
delete from 新表名
或者 create table 新表名 as select * from 旧表名 where 1=2;
查看表结构
desc +表名
删除表
drop table 表名
插入数据
insert into 表名(字段们) VALUES(对应字段类型值)
插入大量数据
select * from 表名 for update 然后把数据复制到表中
删除数据
delete from 表名 where 某字段=某字段值
修改数据
update 表名 set 字段=新字段值 where 字段=原字段值
修改表结构,删除列,增加列
alter table 表名 drop column 字段
alter table 表名 add 字段 字段类型
删除主键、设置主键
alter table 表名 drop primary key
alter table 表名 add primary key(字段)
修改了表结构,内部刷新只能刷新行,关闭表再打开才能看见表结构的变化,sqldeveloper
查找某个字段最大值、最小值、平均值、和
select max(AGE) AS MAXVALUE FROM SHANGXIA
select min(AGE) AS MINVALUE FROM SHANGXIA
select avg(AGE) AS AVGVALUE FROM SHANGXIA
select sum(AGE) AS SUMVALUE FROM SHANGXIA
where or 条件查询
select * from emp where ename = 'KING' or ename ='SCOTT';
like 模糊查询
select * from emp where ename like '_L%';
order by 排序 ASC:升序, 缺省,DESC: 降序
select 字段们 from 表名 order by 字段, 字段 desc/esc;
Group by 分组
Select 字段1,sum(字段2) from 表名 group by 字段1;
Having
select 字段1,sum(字段2) from 表名 group by 字段1 HAVING sum(字段2)<某值
distinct 消除冗余
select distinct 字段 from 表名
IS NULL/is not null
select 字段们 from 表名 where 字段 IS NULL; //用 IS NULL操作符来检查有无空值
ROWNUM 分页查询 控制查询出来的数据个数
SELECT * FROM (SELECT 别名.*, ROWNUM RN FROM 表名 别名) WHERE RN > 0 AND RN <= 2//查前两行数据
SELECT * FROM (SELECT 别名.*, ROWNUM RN FROM (SELECT * FROM 表名 ORDER BY 字段 desc) 别名) WHERE RN > 0 AND RN <= 2 //先按字段排序 之后查询行数据
字符串截取函数substr()
substr(expr1,expr2,expr3),expr1指要截取的字符串,expr2指截取字符串开始的位置,expr3指截取字符串的长度。
select substr('HelloBoy',0,3) value from dual; //结果 Hel
select substr('HelloBoy',1,3) value from dual; //结果 Hel 0和1的结果是一样的
case when then end
SELECT 字段, COUNT (CASE WHEN sex= 1 THEN 1 /*sex 1为男生,2位女生 */
ELSE NULL
END) 男生数,
COUNT (CASE WHEN sex = 2 THEN 1
ELSE NULL
END) 女生数
FROM 表名 GROUP BY 字段; //按部门分组 看某部门男生多少 女生多少
decode
decode(条件,值1,翻译值1,值2,翻译值2,..值n,翻译值n,缺省值)
decode(字段,比较1,值1,比较2,值2,..比较n,值n,缺省值)
select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary*1.15) from employee
工资在8000元以下的加20%;工资在8000元或以上的加15%
PL/SQL(过程化SQL语言)
可以编写具有很多高级功能的程序
⒈、能够使一组SQL语句的功能更具模块化程序特点;
⒉、采用了过程性语言控制程序的结构;
⒊、可以对程序中的错误进行自动处理,使程序能够在遇到错误的时候不会被中断;
⒋、具有较好的可移植性,可以移植到另一个Oracle数据库中;
⒌、集成在数据库中,调用更快;
⒍、减少了网络的交互,有助于提高程序性能。