Oracle数据库常用语句使用样例及说明
表级别操作
--创建表有三列，其中userid不能为空
create table test12301(
userid number(3) not null,
kemu varchar(10),
score number(3,1));
--向表test12301增加一列数据
alter table test12301 add beizhu varchar(20);
--向表test12301删除一列数据
alter table test12301 drop column beizhu;
--设置表test12301中score列不能为空（modify对列进行重新设置）
alter table test12301 modify score not null;
--删除表test12301
drop table test12301;
--1拿没有实际业务意义的字段做主键（如随机生成的id）
--2拿有实际业务意义的字段做主键（如身份证号）
--设置表test12301中id为主键
alter table test124 add constraint prim_id primary key (id);
--设置表test123中userid,username为主键(双主键)
alter table test123 add constraint pk_test123 primary key(userid,username);
--设置表test124的userid为test123中userid的外键（一对多中外键建在多的那个）
alter table test123 add constraint for_test123_userid foreign key (userid) references test124 (userid);
--一对多例子
create table t_class(
c_id number(3) primary key,
c_name varchar2(10) not null
);
--t_student表外键c_id关联表t_class的主键c_id
create table t_student(
s_id number(3) primary key,
s_name varchar2(20) not null,
s_sex char(2) default '男',
s_birthday date,
c_id number(3),
foreign key(c_id) references t_class(c_id)
);
--设置表test123中usertel为唯一的
alter table test123 add constraint uni_tel unique(usertel);
--设置表test123中userage的大小范围
Alter table test123 add constraint che_age check(userage);
增删改操作
--在test123表新增一条数据设置所有字段值
insert into test123 (userid, userage, username, userdate) values (1, 18, '韩梅梅', '1992-06-24 10:30:56');
insert into test123 values (1, 18, '韩梅梅', '1992-06-24 10:30:56');
--在test123表新增一条数据设置部分字段值
insert into test123 (userid, username) values (1, '韩梅梅');
--删除test123表中userid=1的数据（不跟where条件会删除所有数据，小心操作）
delete from test123 where userid=1;
--修改test123表中username='韩梅梅'行中userage的数据
update test123 set userage=20 where username='韩梅梅';
--修改test123表中userid=3行中userage的数据
update test123 set userage=18 where userid=3;
update test123 set username=1,uesrage=12 where userid=2;
--修改test123表中userid=2行中userdate的数据(注意时间的格式)
update test123 set userdate=to_date('1992-06-24 10:30:56','yyyy-mm-dd hh24:mi:ss') where
userid=2;
--提交(增删改完成后，不要忘记提交，否则不生效)
commit;
--回滚(增删改错了，且未提交的时候，可以执行回滚，数据就不会有变动了)
rollback;
查询操作
test123表
| 列名 | 注释 |
|---|---|
| userid | 主键，自增长 |
| useranme | 姓名 |
| userage | 年龄 |
| usersex | 性别 |
| userdate | 出生日期 |
| c_id | 关联test124表主键 |
test124表
| 列名 | 注释 |
|---|---|
| c_id | 主键，自增长 |
| c_name | 班级名 |
test125表
| 列名 | 注释 |
|---|---|
| k_id | 主键，自增长 |
| k_name | 科目名 |
| k_score | 科目得分 |
| userid | 关联test123表主键 |
--查询test123表所有内容
select * from test123;
--查询test123表有多少条数据
select count(*) from test123;
--查询test123表中所有username
select username from test123;
--查询test123表中所有不重复的username
select distinct username from test123;
--查询表test123中userid=2的这行内容
select * from test123 where userid=2;
--查询表test123中的所有数据并先按username排序有一样的按userage字段倒序排序（desc倒序,nulls first 空值先排）
select * from test123 order by username,userage desc;
--查询表test123中username,usersex并把username,usersex相同的合并到一组中
select username,usersex from test123 group by username,usersex;
--查询表test123中相同username的userage的个数
select username,count(userage) from test123 group by username;
--||和concat可以用作拼接
select userid||','||username from test123;
select concat(userid,username) from test123;
--查询c_id为1或2并且usersex为男的信息in 与exists有同样功能，in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in
select * from test123 where c_id in (1,2) and usersex='男';
select * from test123 where (c_id=1 or c_id=2) and usersex='男';
--查询表test123中相同username的userage的个数大于1的username（having后可以跟聚组函数）
select username,count(userage) from test123 group by username having count(userage)>1;
--查询表test123中userid=3的username，usersex，userage内容
select username,usersex,userage from test123 where userid=3;
--查询表test123中userage大于等于20小于等于30的username, usersex,userage内容
select username, usersex,userage from test123 where userage between 20 and 30;
select username, usersex,userage from test123 where userage>=20 and userage<=30;
--查询表test123和test125中科目是java的username,userage,k_name,k_score
select username,k_name,k_score from test123,test125 where test123.userid=test125.userid and c_name='java';
--查询表中s_name跟_ha%类型一样的（_代表一个字符，%代表多个字符）模糊查询
select * from test123 where username like '_ha%';
--字段值转换处理
select username,case when c_id=1 then 'java1' else 'java2' end c_name from test123;
--min最小值，max最大值，avg平均值，sum总和
select min(userage),max(userage),avg(userage),sum(userage) from test123;
--rownum是从1开始递增的，查询前5条数据，数据库分页
select t.*, rownum from test123 t where rownum<5;
--先按usersex分类，在按userid逆序排列，rk记录各类中的排序
select rank()over(partition by usersex order by userid desc) rk, test123.* from test123
dense_rank();
--round返回某个数值按指定位数取整后的数值
select userid, round(avg(k_score),2) from test125 group by userid;
--左连接，左边所有数据显示，右边匹配数据显示（或left outer join）
select tc.*,ts.* from test124 tc left join test123 ts on tc.c_id=ts.c_id;
select tc.*,ts.* from test124 tc,test123 ts where tc.c_id=ts.c_id(+);
--右连接，右边所有数据显示，左边匹配数据显示
select tc.*,ts.* from test124 tc right join test123 ts on tc.c_id=ts.c_id;
select tc.*,ts.* from test124 tc,test123 ts where tc.c_id(+)=ts.c_id;
--列转行用decode，decode（value，if,then,if1,then1….else）,sum 求和，如果有相同的k_score相加
select
userid,
sum(decode(k_name, '数学', k_score)) 数学,
sum(decode(k_name, '物理', k_score)) 物理,
sum(decode(k_name, '英语', k_score)) 英语,
sum(decode(k_name, '语文', k_score)) 语文
from
test125
group by userid;
--replace(1,2,3)字符替换，1：字符串2：需要替换的字符3：想要的字符。wmsys.wm_concat()字符串拼接：可以把某列合并（如：score1,score2,score3）
select
userid,
replace (wmsys.wm_concat (decode(k_name, '数学', k_score)),',','-') 数学,
replace (wmsys.wm_concat (decode(k_name, '物理', k_score)) ,’,’,’-’) 物理,
replace (wmsys.wm_concat (decode(k_name, '英语', k_score)) ,’,’,’-’) 英语,
replace (wmsys.wm_concat (decode(k_name, '语文', k_score)) ,’,’,’-’) 语文
from
test125
group by userid;
利用dual系统虚拟表做运算操作
--可以做+，-，*，/运算(dual系统虚拟表)
select zxf+1,zxf-2,zxf*3,zxf/4 from dual;
--sysdate调用数据库服务器时间及时间的天运算
select sysdate,sysdate+1,sysdate-1 from dual;
--月份加2
select add_months(sysdate,2) from dual;
--last_day计算日期那个月的最后一天
select last_day(to_date('2013-02-05','yyyy-mm-dd')) from dual;
--greatest返回两个日期较大的那个，least返回较小的
select greatest('2012-02-05','2014-06-01'),least('2012-02-03','2011-02-03')from dual;
--返回两个日期相差的月数
select months_between(sysdate,to_date('2013-06-01','yyyy-mm-dd')) from dual;
--trunc截取日期的年月日
select sysdate,trunc(sysdate) from dual;
-- to_char把时间按照指定格式转换成字符串，as起别名（直接名后空格别名也可以）
select to_char(sysdate,’yyyy-mm-dd’) as cudate from dual;
select to_char(sysdate,’hh24’) as cudate from dual;
--to_number把字符串转换成数字类型，第二个参数表示指定按照指定格式显示（小数点前面的0不够显示错误，0多余则不够的补0:；小数点后面的表示小数点位数，多的去掉，少的补0）
select to_number(‘125.62548’) from dual;
select to_number(‘125.62548’,’000.00’) from dual;
--ltrim去左边空格，rtrim去右边空格，trim去两边空格
select ' a aa ',ltrim(' a aa '),rtrim(' a aa '),trim(' a aa ') from dual;
--upper把字符串转换成大写，lower转换成小写
select upper('abCDeFg'),lower('aaaBBB') from dual;
--initcap把首字母转成大写，其他为小写
select initcap('aaBBccDD') from dual;
--lpad在100的左边加0凑够6位，rpad在右边加
select lpad('100',6,'0'),rpad('100',6,'0') from dual;
--计算字符串的长度
select length('abcdef') from dual;
--计算c在字符串里的下标（oracle中下标从1开始）
select instr('abcdef','c') from dual;
--截取下标从4后的所有字符
select substr('abcdef',4) from dual;
--截取下标从4后的2个字符
select substr('abcdef',4,2) from dual;
--把76 转成 L
select chr(76) from dual;
--把a转化成97
select ascii('a') from dual;
--取绝对值
select abs(-25) from dual;
--返回大于或等于当前数字的最小整数
select ceil(25.4777) from dual;
--返回小于或等于当前数字的最小整数
select floor(5.9) from dual;
--四舍五入,第二个参数表示小数位
select round(33.5651,2) from dual;
--相当于7%3取余
select mod(7,3) from dual;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
