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;
发表人:梦想的边缘