oracle最常用sql语句.docx
- 文档编号:30266992
- 上传时间:2023-08-13
- 格式:DOCX
- 页数:22
- 大小:31.22KB
oracle最常用sql语句.docx
《oracle最常用sql语句.docx》由会员分享,可在线阅读,更多相关《oracle最常用sql语句.docx(22页珍藏版)》请在冰豆网上搜索。
oracle最常用sql语句
在我的windows7上面安装上了oracle 11gR2,可谓痛苦,开了oracle的服务电脑变得很慢,安装的时候也麻烦多多
为了做写笔记,这里我总结了一些现在我经常用到的sql语句,高手看了不要见怪,我是初学者,写这个笔记主要是在网上搜索的时候大多都是一样的,最关键是老是不行
我是用myeclipse连接到oracle数据库
以下是总结:
(1)查看oracle的版本:
selectversion fromProduct_component_versionwhereSUBSTR(PRODUCT,1,6)='Oracle';
(23)创建数据表:
createtablesaiTest(useridnumbernotnullprimarykey,uNamevarchar2(50)notnull,uPasswordvarchar2(50)notnull);
(3)查看表字段:
selectcolumn_name,data_type,data_length,nullablefromuser_tab_columnswheretable_name=upper('saiTest');
网上很多都没有upper,所以之前总是查不到,在PLSQL和myeclipse都不行
当然也可以全部查出来,根据需要筛选要看的字段
select*fromuser_tab_columnswheretable_name=upper('saiTest');
(4)插入数据
insertintosaiTestvalues('1','king','123456');
(5)修改数据
updatesaiTest setuName='queen'whereuserId=1;
(6)增加列
altertablesaiTestadduImgvarchar2(50);
如果增加列的时候数据表已经有内容了,新增的列不能设置为非空,不然会报错
(7)修改列
为了测试,先增加一列
altertablesaiTestadduMessage number;
修改
altertablesaiTestmodifyuMessagevarchar2(50);
(8)删除列
altertablesaiTestdropcolumnuImg;
(9)添加/修改表的注释
commentontablesaiTestis'存放雇员信息';
查看表的注释:
select * from user_tab_commentswheretable_name=upper('saiTest');
(10)为数据表的列添加注释:
commentoncolumnsaiTest.userIdis'用户id';
commentoncolumnsaiTest.uNameis'用户名';
commentoncolumnsaiTest.uPasswordis'用户密码';
commentoncolumnsaiTest.uImgis'用户头像';
select * from user_col_commentswheretable_name=upper('saiTest');
(11)查看有哪那些表
selecttable_namefromuser_tables;
(12)分页语句
先在表中插入多几条数据
SELECT*FROM(SELECTsaiTest.*,ROWNUMRNFROM(SELECT*FROMsaiTest)saiTestWHEREROWNUM<=3)WHERE RN>=1
把3和1换成自己需要的数字
(13)查出倒叙的第一条
select*from(select*fromsaiTestorderbyuserIddesc)whererownum=1;
(14)查出数据库的系统时间
selectsysdatefromSaitestwhererownum=1;
(15)自动增长
在mysql、sqlserver中可以设置列的值自动增长,然后将这个字段作为主键,但是oracle没有这个自动增长的功能,因此要用到sequence
创建sequence:
createsequenceseq_test
minvalue1
maxvalue99999999999999999
startwith1
incrementby1
cache20
nocycle
order;
createsequenceSEQ_ID
minvalue1
maxvalue99999999
startwith1
incrementby1
nocache
order;
建解发器代码为:
createorreplacetriggertri_test_id
beforeinsertonS_Depart--S_Depart是表名
foreachrow
declare
nextidnumber;
begin
IF:
new.DepartIdISNULLor:
new.DepartId=0THEN--DepartId是列名
selectSEQ_ID.nextval--SEQ_ID正是刚才创建的
intonextid
fromsys.dual;
:
new.DepartId:
=nextid;
endif;
endtri_test_id;
minvalue1:
最小值为1
maxvalue99999999999999999:
最大值为99999999999999999
startwith1:
从1开始
incrementby1:
每次加1
cache:
ORACLE就可以预先在内存里面放置一些sequence,这样存取的快些
nocycle:
一直加下去
先把saiTest得内容全部删除
deletefromsaiTest;
insertintosaiTestvalues(seq_test.nextval,'auto','123456');
insertintosaiTestvalues(seq_test.nextval,'hehe','123456');
seq_test:
sequence的名字
(16)查看用户的sequence
select*fromuser_sequenceswheresequence_name=upper('seq_test');
(17)查看当前用户拥有的权限
select*fromuser_role_privs;
(18)查看当前用户当前会话的权限
select*fromsession_privs;
(19)修改表名
renamesaiTesttomyTest;
最基本查询
select*fromtable1wherename=’Lincoln’;
selectcol1,col2fromtable1;
无重复查询
select distinct amountfromchecks;
(distinct在一个语句中只能用一次,放在所有字段之前)
MySQL的一些常用命令
showdatabases; 显示所有数据库
showtables; 显示当前数据库的表
usedb1; 当前数据库切换到db1
describetable1; 显示table1表的表结构
运用表达式和别名
selectitem, wholesale+0.15 fromprice;
selectitem,(wholesale+0.15) retail fromprice;
select*fromfriendswherestate<=’la’;
select*fromfriendswherefirstname<>’al’;
select*frompartswherelocation like ’%back%’;
select*fromfriendswherest like ’C_’;
select firstname||lastname entirenamefromfriends;(mysql中不能用)
(mysql可以用selectconcat(name1, ’’,name2)namefromfriends;)
selectlastnamefromvacationwhereyears<=5 and leavetaken>20;
select*fromvacationwherelastname notlike ’B%’;
集合运算,MySQL不一定能用
selectnamefromsoftball union selectnamefromfootball; 合集
selectnamefromsoftball unionall selectnamefromfootball; 全合集
select*fromfootball intersect select*fromsoftball; 交集
select*fromfootball minus select*fromsoftball; 差集
In与Between运算符
select*fromfriendswhereareacodein(100,381,204);
select*frompricewherewholesalebetween0.25and0.75;
内置函数的使用
count 求总数
sum 求总和
avg 求平均
max 求最大
min 求最小
variance 标准方差
stddev 标准差
add_months(date,1) 给日期类型增加一月
date+1 给日期类型增加一天
last_day 返回月份的最后一天
months_between 日期相差的月份
sysdate 系统时间
abs 绝对值
ceil “天花板”
floor “地板”
exp 指数函数
log 上面的反函数
power a^b
sign 正1零0负-1
sqrt 根号
chr 将数字ASSCII码转换成字符
concat 连接字符串
initcap 首字符大写,其他小写
lower 小写
upper 大写
lpad 左填充,原始,填充后总长度,填充字符
rpad 右填充原始,填充后总长度,填充字符
ltrim 左剪切
rtrim 右剪切
trim 去左右空格
replace 字符替换,原始,替换,替换成
substr 获取子字符串,原始,起始,长度
instr 查找字符串,原始,查找,开始,序号
length 字符串长度
to_char 转换成字符串
to_number 转换成数字
子句的运用
(Where,Groupby,Orderby,Having)
select*fromchecks where amount>100;
select*fromchecksorderbycheck; 可以加上asc代表升序
select*fromchecksorderbypayee desc;
select*fromchecksorderby remarks,payee;
select*fromchecksorderby1;
selectpayee,sum(amount),count(payee)fromchecks groupby payee;
selectsum(amount),count(payee)fromchecksgroupby payee,remarks;
where之句中不允许用合计函数,因此下面的语句是错误的。
selectteam,avg(salary)fromorgchartwhereavg(salary)<3800groupbyteam;
这时候需要having子句:
selectteam,avg(salary)fromorgchartgroupbyteamhavingavg(salary)<3800;
…… havingteamin(’pr’,’research’);
交叉连接(笛卡尔积)
select*fromtable1,table2;
selecto.orderedon,o.name,o.partnum,p.partnum,p.description
fromorderso,partp
whereo.partnum=p.partnum;
内部连接
selectp.partnum,p.price,o.name,o.partnum
from partp innerjoin orderso onorders.partnum=54;
除了使用了“on”来代替“where”之外,和交叉连接没多少区别。
外部连接
左连接:
左边的表全部显示
selectp.partnum,p.description,p.price,o.name,o.partnum
frompartp leftouterjoin ordersoono.partnum=54;
右连接:
右边的表全部显示
selectp.partnum,p.description,p.price,o.name,o.partnum
frompartp rightouterjoin ordersoono.partnum=54;
子查询(MySQL不支持)
select*fromorders
wherepartnum=(selectpartnumfrompartwheredescriptionlike “ROAD%”);
下面是较复杂查询示例:
selecto.name,o.orderedon,o.quantity*p.pricetotal
fromorderso,partp
whereo.partnum=p.partnumando.quantity*p.price>
(selectavg(o.quantity*p.price)
fromorderso,partp
whereo.partnum=p.partnum);
Exist使用(判断集合是否存在)
selectname,orderedonfromorderswhereexists
(select*fromorderswherename=’Mostlyharmless’)
数据库三范式
第一范式:
每一列不可再分割。
第二范式:
每一行可以被区分(主键)。
第三范式:
表中不应出现其他表的非主属性。
1、数据集合分成多张表而不只是一张大表,分成的每张表都有主键;主键
2、找出仅仅依赖于主键的列,将其存储在另一个表中;仅依赖的列抽出
3、从一个表中删除不依赖于主键的列。
不依赖的列抽出
缺点:
降低性能。
建立数据库
很难吧?
很难!
不难吧?
不难。
仅仅告诉你最简单情况:
createdatabasePAYMENTS;
建立表
createtablebills(namechar(30),amountnumber,account_idnumber);
createtableempname(idnumber notnull,enamechar(30));
createtablenewtable as select*fromoldtable;
改变表:
增加列
altertableemp add new_col_namechar(20);
改变表:
修改列
altertableemp modify new_col_namechar(21);
改变表:
改列名(Oracle9iRelease2才能用)
altertableemp renamecolumn new_col_name to old_col_name;
改变表:
删除列
altertableemp dropcolumn old_col_name;
删除表
droptable tablename;
删除数据库
dropdatabase databasename;
主键、非空、唯一约束
主键primarykey
非空notnull
唯一unique 除了排序,和primarykey功能一致
createtableemp
(emp_idchar(9) primarykey,
emp_namevarchar2(40) notnull,
phonenumber(10)null unique);
外键约束foreignkey
createtableemp_pay
(emp_idchar(9)notnull,
positionvarchar2(15)notnull,
pay_ratenumber(4,2)notnull);
altertableemp_pay addconstraint emp_id_fk foreignkey(emp_id)
referencesemp(emp_id);
校验约束check
createtableemp
(emp_idchar(9)notnullprimarykey,
emp_namevarchar2(40)notnull,
emp_ratenumber(4,2)notnull,
zipnumber(5)notnull);
altertableemp addconstraint chk_zip check(emp_zip='46234');
altertableemp addconstraint chk_zip check(emp_zipin('47634','13451'));
altertableemp addconstraint chk_zip check(emp_rate<12.5);
emp_rate为校验约束名。
删除约束
altertableemp dropconstraintemp_no_constraint;
emp_no_constraint为约束名。
更新记录
update collectionsetworth=555,price=666whereitemid=1110;
删除记录
delete fromcollectionwhereitemid=1113;
事务处理
……
commit;
……
rollback;
……
savepointsave_it;
……
rollbacktosavepointsave_it;
日期时间
掌握两个函数就可以了。
to_char(empdate,'YYYY/MM/DDHH24:
MI:
SS');
将date转变成字符串:
“2004/11/1216:
30:
02”
to_date('1981/11/1200:
03:
16','YYYY/MM/DDHH24:
MI:
SS');
将字符串“1981/11/1200:
03:
16”转变成日期类型。
还有要注意的事项,在中文Oracle中,'26-JAN-03'并不被认为是合法的日期,
'26-1月-03'才是合法的,真别扭,也蛮郁闷的。
建立视图
create view debtsasselect*frombills;
删除视图
drop view debts;
建立索引
createindexempno_indexonemp(empno);
删除索引
dropindexempno_index;
创建用户
createuserjguogangidentifiedbymypassword;
授予用户角色
grantconnecttojguogang;
grantresourcetojguogang;
grantdbatojguogang;
删除用户的角色
revokeresourcefromjguogang;
给予系统特权
grantalteranytypetopublic;
其中“alteranytype”为一种系统特权,将被授予全部用户。
grantdropanytriggertojguogang;
将系统特权“dropanytrigger”授予用户“jguogang”。
grantcreateusertoconnect;
将系统特权“createuser”授予角色“connect”。
收回系统特权
revokealteranytypefrompublic;
给予对象
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 常用 sql 语句
![提示](https://static.bdocx.com/images/bang_tan.gif)