ORACLE数据库操作总结大全.docx
- 文档编号:6361718
- 上传时间:2023-01-05
- 格式:DOCX
- 页数:21
- 大小:26.68KB
ORACLE数据库操作总结大全.docx
《ORACLE数据库操作总结大全.docx》由会员分享,可在线阅读,更多相关《ORACLE数据库操作总结大全.docx(21页珍藏版)》请在冰豆网上搜索。
ORACLE数据库操作总结大全
Oracle数据库操作总结
目录
1.登录操作2
2.常用工具2
3.SQLPlus常用命令2
4.常用数据类型:
3
5.SQL分类3
6.数据操作(DML)3
6.1.查询语句3
6.2.插入语句6
6.3.修改记录7
6.4.删除记录7
6.5.ROLLBACK(回滚)7
6.6.左连接与右连接7
7.标量函数的使用9
8.对象管理(DDL)12
8.1.表12
8.2.视图13
8.3.序列14
8.4.表空间15
9.约束15
10.用户管理16
11.权限管理17
12.角色管理18
13.表空间与段18
13.1.表空间18
13.2.段19
1.
登录操作
Oracle安装好后,系统默认有超级管理员“sys”和普通用户“scott”。
scott的密码是tiger。
如果要使用scott用户登录,则要做以下工作。
(1)新建一个服务别名myorcl(名字可以自己命名),使服务别名指向要登录的数据库。
(2)用sys超级管理员给scott用户解锁(因为scott这个用户被锁住了,不能登录)。
解锁命令:
ALTERUSERscottACCOUNTUNLOCK。
(3)用户scott用户登录,登录命令:
sysplusscott/tiger@myorcl。
SQLPlus工具登录命令:
系统管理员登录,DOS中执行命令:
sqlplususername/password@serviceNameassysdba
普通用户登录,DOS中执行命令:
sqlplususername/password@serviceName
2.常用工具
SQLPlus
系统管理员登录,DOS中执行命令:
sqlplususername/password@serviceNameassysdba
普通用户登录,DOS中执行命令:
sqlplususername/password@serviceName
SQLPLusW可视化窗口
DOS中执行命令:
sqlplusw
iSQLPlus(网页形式):
访问网页地址:
http:
//192.168.0.1:
5560/isqlplus/
EnterpriseManager(网页形式):
访问网页地址:
http:
//192.168.0.1:
5500/em/
说明:
访问iSQLPlus(网页形式)和EnterpriseManager(网页形式)时需要网址端口号,如果忘记,可在此文件中查:
ORACL_HOME/product/10.2.0/db_1/install/portlist.ini。
3.SQLPlus常用命令
SQLPlus中的常用命令:
select*fromtab;列出当前用户下的所有表。
showuser;察看当前连接的用户
connectscott/password采用scott的用户名/密码连接数据库
desctable_name;察看tableName表结构
quit|exit;退出
disconnect;断开连接
clearscreen;清屏,相当于Windows下的cls命令
@path执行path指定的脚本文件
4.常用数据类型:
varchar2变长字符串
char定长字符串
Integer整型
number(m,n)数字型
smallint短整型
float浮点数
decimal十进制数字
date日期型
5.SQL分类
DML(DataManipulationLanguage,数据操作语言)
用于检索或修改数据
DDL(DataDefinitionLanguage,数据定义语言)
用于定义数据的结构,如创建修改或者删除数据库对象
DCL(DataControlLanguage,数据控制语言)
用于定义数据库用户的权限
6.数据操作(DML)
6.1.查询语句
基本数据查询
select内容from表名;
selectdistinctjobfromemp;去除重复行
限定查询(查询满足条件的行)
SELECT*|列名FROM表名WHERE条件;
SELECTempno,ename,commFROMempWHEREcommISNOTNULL;
限定查询----ISNULL和ISNOTNULL的使用
SELECTempno,ename,commFROMempWHEREcommISNULL查询谁没有奖金
限定查询----AND的使用
查询工资大于1500,并且可以领取奖金的雇员
SELECTempno,ename,sal,commFROMEMPWHEREsal>1500ANDcommISNOTNULL;
限定查询----OR的使用
查询工资大于1500和可以领取奖金的雇员
SELECTempno,ename,sal,commFROMEMPWHEREsal>1500ORcommISNOTNULL;
限定查询----使用NOT对条件整体取反
查询工资不大于1500并且不能领取资金的雇员
SELECTempno,ename,sal,commFROMempWHERENOT(sal>1500ANDcommISNOTNULL);
限定查询----BETWEEN...AND...的使用
查询基本工资大于等于1500并且小于等于3000的雇员
SELECTempno,ename,sal,commFROMempWHEREsalBETWEEN1500AND3000
限定查询----IN的使用
查询出雇员编号是7369,7499,7521的雇员的具体信息
SELECT*FROMempWHEREempnoIN(7369,7499,7521);
限定查询----NOTIN的使用
查询出雇员编号不是7369,7499,7521的雇员的具体信息
SELECT*FROMempWHEREempnoNOTIN(7369,7499,7521);
限定查询----LIKE的使用
查询中雇员的名字第二个字符是M的雇员信息
SELECTempno,ename,comm,salFROMempWHEREenameLIKE'_M%';
说明:
_匹配一个字符,%匹配0个或多个字符
限定查询----LIKE匹配日期
查询1982年入职的所有雇员的信息
SELECT*FROMempWHEREhiredateLIKE'%82%'
限定查询----LIKE匹配数字
查询工资中包含5的雇员信息
SELECT*FROMempWHEREsalLIKE'%5%'
对结果排序----ORDERBY
带有ORDERBY子句的SQL语句基本格式
SELECT列...FROM表WHERE条件ORDERBY列
查询员工资大于1500的信息,按工资排序
SELECT*FROMempWHEREsal>1500ORDERBYsal
查询工资大于1500员工的信息,按工资降序,按雇佣日期升序排序
SELECT*FROMempWHEREsal>1500ORDERBYsalDESC,hiredateASC
说明:
ASC排序,DESC降序,默认ASC
左、右外连接
查询员工编号,姓名,所在部门号,部门名称,将没有员工的部门也显示出来
SELECTe.ename,d.deptno,d.dnameFROMempe,deptdWHEREe.deptno(+)=d.deptno;
注:
(+)在左边,表示右连接,会列出右表中出现但是没有在左表中出现的行
交叉连接(CROSSJOIN):
用来产生笛卡尔积的
SELECT*FROMempCROSSJOINdept;
自然连接(NATURALJOIN):
自动进行关联字段的匹配
SELECT*FROMempNATURALJOINdept;
USING子句:
直接指定操作关联列
SELECT*FROMempJOINdeptUSING(deptno);
ON子句:
用户自己编写连接条件
SELECT*FROMempJOINdeptONemp.deptno=dept.deptno;
RIGHTJOIN:
右外连接
SELECTe.empno,e.ename,d.deptno,d.dnameFROMempeRIGHTJOINdeptdONe.deptno=d.deptno;
子查询---IN的使用
查询和SMITH或JONES在同一部门,同一职位工作的员工
SELECT*FROMempWHERE(deptno,job)IN(SELECTdeptno,jobFROMempWHEREenameIN('SMITH','JONES'));
子查询---ANY的使用
=ANY:
与IN操作符的效果一致
查询和SMITH或JONES在同一部门,同一职位工作的员工
SELECT*FROMempWHERE(deptno,job)=ANY(SELECTdeptno,jopFROMempWHEREenameIN('SMITH','JONES'));
>ANY:
只要大于子查询中的任何一个值即可
SELECT*FROMempWHEREsal>ANY(SELECTMIN(sal)FROMEMPGROUPBYdeptno);
>ALL:
比最大的值大
SELECT*FROMempWHEREsal>ALL(SELECTMIN(sal)FROMempGROUPBYdeptno);
只要小于子查询中的任何一个值即可 SELECT*FROMempWHEREsal 比最小的值小 SELECT*FROMempWHEREsal 6.2.插入语句 intsertinto表名values(值1值2值3…..)插入的数值的字段类型要与定义的时候一样 intsertinto表名values(值1,值2,null,值3………)有不想写的可以用null来标示 intsertinto表名(字段1.字段2,字段3………)values(值1值2值3……)类型对应 注意: 数值的类型个数顺序不能超过最大值 插入记录到表中INSERT的语法 INSERTINTO表名(字段名1,字段名2,......)VALUES(值1,值2......); 为MYEMP中增加一条记录 INSERTINTOmyemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7899,'张三','清洁工','7369','14-2月-1995',9000,300,40) 注: 给定的值要和指定的字段数一致 当为表中的所有列都插入数据数,可以采用如下简单写法 INSERTINTO表名VALUES(值1,值2......); INSERTINTOmyempVALUES(7991,'李四','清洁工','7369','14-2月- 1995',9000,300,40) 注: 要求给定值的数目和表中列的数目一致,并且值的顺序和表中列的顺序一致。 新增加一条记录到MYEMP中,但是该员工没有奖金也没有上级领导 第一种写法: INSERTINTOmyemp(empno,ename,job,hiredate,sal,deptno) VALUES(7899,'张三','清洁工','14-2月-1995',9000,40) 第二种写法: (用null来指定没有值的列) INSERTINTOmyempVALUES(7898,'王五','清洁工',null,'14-2月- 1995',9000,null,40) 新增加一条记录到MYEMP中,但日期格式按'1988-09-09'的方式指定 �TO_DATE函数的使用 INSERTINTOmyemp(empno,ename,job,hiredate,sal,deptno) VALUES(7896,'赵六','清洁工',TO_DATE('1984-09-09','yyyy-mm-dd'),9000,40); 6.3.修改记录 update表名set字段=值,字段=值,字段=值…….where条件; 注意: 条件必须写 6.4.删除记录 deletefrom表名where条件; 注意: 条件必须写 6.5.ROLLBACK(回滚) 在sqlplus中如果使用ROLLBACK的话那么将撤销操作直到上次执行commit的时候的状态 6.6.左连接与右连接 数据表的连接有: 1、内连接、自然连接: 只有两个表相匹配的行才能在结果集中出现 (1)等值连接: 在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列,包括其中的重复列。 (2)不等连接: 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。 这些运算符包括>、>=、<=、<、! >、! <和<>。 (3)自然连接: 自然连接(Naturaljoin)是一种特殊的等值连接,它要求两个关系中进行比较的分量必须是相同的属性组,并且在结果中把重复的属性列去掉。 而等值连接并不去掉重复的属性列。 等值连接与自然连接的区别: (1)等值连接中不要求相等属性值的属性名相同,而自然连接要求相等属性值的属性名必须相同,即两关系只有在同名属性才能进行自然连接。 (2)等值连接不将重复属性去掉,而自然连接去掉重复属性,也可以说,自然连接是去掉重复列的等值连接。 2、外连接: (1)左外连接(左边的表不加限制) (2)右外连接(右边的表不加限制) (3)全外连接(左右两表都不加限制) 3、自连接(连接发生在一张表内) SQL标准连接语法: selecttable1.column,table2.column fromtable1 [inner|left|right|full]jointable2ontable1.column1=table2.column2; 例如: selecta.*,b.* froma leftjoinbona.编号=b.编号 INNERJOIN表示内连接; LEFTJOIN或LEFTOUTERJOIN表示左外连接; RIGHTJOIN或RIGHTOUTERJOIN表示右外连接; FULLJOIN或FULLOUTERJOIN表示完全外连接;不管匹配 ON子句用于指定连接条件。 如果主表的主键列和从表的外部键列名称相同,那么可以使用自然连接(naturaljoin)关键字自动匹配内连接操作。 使用“(+)”符号的左右连接: 右连接: selecta.studentno,a.studentname,b.classname fromstudentsa,classesb wherea.classid(+)=b.classid; 左连接: selecta.studentno,a.studentname,b.classname fromstudentsa,classesb wherea.classid=b.classid(+); "(+)"写在主表的另一侧,主表的所有记录均会被显示。 "(+)"所在位置的另一侧为连接的方向。 右连接说明等号右侧的所有记录均会被显示,无论其在左侧是否得到匹配。 左连接相反。 注意: 如果使用from子句指定内、外连接,则必须要使用on子句指定连接条件; 如果使用(+)操作符指定外连接,则必须使用where子句指定连接条件。 7.标量函数的使用 转化大小写 selectupper(‘sddfa’)fromdualselectlower(‘sddfa’)fromdual 将首字母大写其余小写 selectinitcap(ename)fromemp; 连接字符串: selectcount(deptno)fromdept; selectcontact(‘hello’,’woeld’)fromdual; select‘abc’||‘jkh’||‘sdf’fromdual; 求子串: selectsubstr(字符串,起始位,结束位)fromdual;起始位写0或者1都可以代表第一位 selectsubstr(字符串,起始位)fromdual;从起始位开始往后截取 求字符串长度: selectlength(ename)fromemp; 字符串替换 selectreplace(原始字符串,想要被替换的字符串,替换字符串)fromdual; 输出字符串的后三位 selectename,substr(ename,length(ename)-2)fromdual; selectenamesubstr(ename,-3)fromdual; 四舍五入 selectround(132.78454)fromdual;四舍五入取整 selectround(132.78345,2)fromdual;四舍五入并且指定保留小数点的位数 selectround(1323.7858416,-2)fromdual;小数不要并且整数位两位取整本代码得1300 截断小数位 selecttrunc(546541.151)fromdual不管四舍五入直接截断小数5465541 selecttrunc(789.536,2)fromdual截断小数指定小数的数位789.53 selecttrunc(789.536,-2)fromdual截断小数并且对整数进行截断700 取余 selectmod(10,3)fromdual;取余数1 运算规律: 日期+数字=日期 日期--数字=日期 日期—日期=数字 显示系统时间 selectsysdatefromdual; selectto_char(sysdate,'yyyy-mm-dd,hh24: mi: ss')fromdual; 显示某本门员工进入公司星期数: selectempno,ename,round((sysdate-hiredate)/7)fromempwheredeptno=10; 在指定的日期加上指定的月数后的日期: selectadd_months(sysdate,2)fromdual; 求出给定日期范围的月数: selectempnoenamemonth_between(sysdate,hiredate)fromemp; 下一个给定的星期是那个日期: selectnext_day(sysdate,’星期一’)fromdual; 求出给定日期所在的月份的最后一天的日期 selectlast_day(sysdate)fromdual; 转换函数 转换成字符串(TO_CHAR) 查询雇员号,姓名,以及入职的年份 SELECTempno,ename,TO_CHAR(hiredate,'yyyy')FROMemp; 说明: yyyy匹配年份,mm匹配月份,dd匹配日 使用TO_CHAR设置日期的显示格式 SELECTempno,ename,TO_CHAR(hiredate,'yyyy-mm-dd')FROMemp; 使用fm去掉前导0 SELECTempno,ename,TO_CHAR(hiredate,'fmyyyy-mm-dd')FROMemp; 通过TO_CHAR设置数值的格式 SELECTempno,ename,TO_CHAR(sal,'99,999')FROMemp; 说明: 9代表1位数字 显示金额 SELECTempno,ename,TO_CHAR(sal,'$99,999')FROMemp; 说明: $代表美元,L代表本地 转换成数字(TO_NUMBER) SELECTTO_NUMBER('123')+TO_NUMBER('123')FROMdual; 转换成日期(TO_DATE) SELECTTO_DATE('2009-07-31','yyyy-mm-dd')FROMdual; 通用函数 查询每个员工的年收入 SELECTempno,ename,(sal+NVL(comm,0))*12incomeFROMemp; 说明: (COMM,0)当comm的值为null时,用0替换 常用的列函数 SUM(expression)求和 MAX(expression)求最大值 MIN(expression)求最小值 COUNT(expression)统计记录数 COUNT(DISTINCTCOLNAME)统计去除重复行记录数 SQL语句的执行顺序 FROM WHERE GROUPBY HAVING SELECT ORDERBY 8.对象管理(DDL) 8.1.表 创建表 createtable表名(字段1数值类型defultxx,字段2数值类型,……………); createtableCUSTOMER ( CUSTOMER_IDintnotnull, CUSTOMER_NOvarchar(100), CUSTOMER_NAMEvarchar(200), TYPEINT,--客户类型 SOURCE_TYPE_IDint,--来源 INDUSTRY_TYPE_IDint,--行业 LEVEL_TYPE_IDint,--等级 REGION_TYPE_IDint,--区域 CREDIT_TYPE_IDint,--信用 primarykey(CUSTOMER_ID) ); defult是默认值可写可不写 createtable表名2as表名1复制表2到表1中 createtable表名as(子查询)复制子查询结果这
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 数据库 操作 总结 大全