Oracle常用sql语句

Oracle常用的sql

---

基本用法

列表内容

• 新建表：
  create table tablename(
  id varchar(300) primary key,
  name varchar(200) not null
  );

• 插入数据
  insert into tablename(id,name) values (‘aa’,’bb’);

• 更新数据
  update tablename set id = ‘bb’ where id=’cc’;

• 删除数据
  delete from tablename where id =’cc’;

• 删除表
  drop table tablename;

• 修改表名：
  alter table tablename1 rename to tablename2;

• 表数据复制：
  insert into tablename1 (select * from tablename2);

• 复制表结构：
  create table tableName2 as select * from tableName1 where 1<>1

• 复制表结构和数据：
  create table tableName2 as select * from tableName1

• 复制指定字段：
  create table tablename1 as select id, name from tablename2 where 1<>1;

• 条件查询：
  select (case gender when 0 then ‘男’ when 1 then ‘女’ end ) gender from tablename

• 新增字段：
  语法：alter table tablename add (column datatype [default value][null/not null],….);
  说明：alter table 表名 add (字段名 字段类型 默认值 是否为空);
  例：alter tablename add (columnname varchar2(30) default ‘无’ not null);

• 修改字段：
  语法：alter table tablename modify (column datatype [default value][null/not null],….);
  说明：alter table 表名 modify (字段名 字段类型 默认值 是否为空);
  例：alter table tablename modify (BILLCODE number(4));

• 删除字段：
  语法：alter table tablename drop (column);
  说明：alter table 表名 drop column 字段名;
  例：alter table tablename drop column columnname;

• 重命名字段：
  说明：alter table tablename rename column ordname to newname
  例：alter table tablename rename column PIC to NEWPIC;
  备注：有别于表重命名（说明：alter table 表名 rename to 新表名）

数学函数

• 绝对值：abs()
  select abs(-2) value from dual; –(2)

• 取整函数（大）：ceil（）
  select ceil(-2.001) value from dual; –(-2)

• 取整函数（小）：floor（）
  select floor(-2.001) value from dual; –(-3)

• 取整函数（截取）：trunc（）
  select trunc(-2.001) value from dual; – (-2)

• 四舍五入：round（）
  select round(1.234564,4) value from dual; –(1.2346)

• 取平方：Power（m,n）
  select power(4,2) value from dual; –(16)

• 取平方根:SQRT()
  select sqrt(16) value from dual; –(4)

• 取随机数:dbms_random(minvalue,maxvalue)
  select dbms_random.value() from dual; (默认是0到1之间)
  select dbms_random.value(2,4) value from dual; （2-4之间随机数）

• 取符号：Sign()
  select sign(-3) value from dual; –(-1)
  select sign(3) value from dual; –(1)

• 取集合的最大值:greatest(value)
  select greatest(-1,3,5,7,9) value from dual; –(9)

• 取集合的最小值:least(value)
  select least(-1,3,5,7,9) value from dual; –(-1)

• 处理Null值：nvl(空值，代替值)
  select nvl(null,10) value from dual; –(10)
  　　
  select nvl(score,10) score from student;

字符函数

select substr('abcdefg',1,5)substr,                     --字符串截取
       instr('abcdefg','bc') instr,                     --查找子串

       'Hello'||'World' concat,                         --连接

       trim('  wish  ') trim,                           --去前后空格
       rtrim('wish  ') rtrim,                           --去后面空格
       ltrim('  wish') ltrim,                           --去前面空格

       trim(leading 'w' from 'wish') deleteprefix,      --去前缀
       trim(trailing 'h' from 'wish') deletetrailing,   --去后缀
       trim('w' from 'wish') trim1,

       ascii('A') A1, 
       ascii('a') A2,                                   --ascii(转换为对应的十进制数)
       chr(65) C1, 
       chr(97) C2,                                      --chr(十进制转对应字符)

       length('abcdefg') len,                           --length 

       lower('WISH')lower, 
       upper('wish')upper, 
       initcap('wish')initcap,                            --大小写变换

       replace('wish1','1','youhappy') replace,           --替换

       translate('wish1','1','y')translate,               --转换，对应一位(前面的位数大于等于后面的位数)
       translate('wish1','sh1','hy')translate1,

       concat('11','22') concat          　　　　　　　　   --连接


from dual;

时间操作

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')currenttime, 
       to_char(sysdate,'yyyy') year,--结果：2018
       to_char(sysdate,'mm') month,--结果：01
       to_char(sysdate,'dd') day,--结果：17
       to_char(sysdate,'day') week,--结果：星期五
       to_char(sysdate,'hh24')hour,--结果：11
       to_char(sysdate,'mi') minute,--结果：23
       to_char(sysdate,'ss') second--结果：21
from dual;

聚合函数

• count：

select count(1) as count from student;--效率最高
select count(*) as count from student;    
select count(distinct score) from student;--排除相同值    

• avg：

select avg(score) score from student;
select avg(distinct score) from student;
select classno,avg(score) score from student group by classno;

• max or min：

select max(score) from student;
select classno, max(score) score from student group by classno;
select min(score) from student;
select classno, min(score) score from student group by classno;

• sum：

select sum(score) from student;
select classno, sum(score) score from student group by classno;

• madian：中位数

select median(score) from student;
select classno, median(score) score from student group by classno;

• stddev：

select stddev(score) from student;
select classno, stddev(score) score from student group by classno;

• 零散方法：

--to_bumber函数
select to_number('0123')number1,            --结果：123
       trunc(to_number('0123.123'),2) number2,--结果：123.12
       to_number('120.11','999.99') number3,--结果：120.11
 　　　 to_number('0a','xx') number4,        --结果：10（十六进制）
from dual;

---

---

发表人：梦想的边缘