Oracle 基本命令

sbian3344

贡献于2015-03-23

字数:10165 关键词: Oracle 数据库服务器

Oracle基本命令 1.1 conn[ect] conn 用户名/密码@网络服务名 当用特权用户身份连接,带上as sysdba/sysoper 1.2 disc[onnect] 断开与当前数据库的连接 1.3 passw[ord] 修改用户密码,如果修改其他用户密码,需要sys/system登录 Oracle用户管理 1.创建用户 create user 用户名 identified by 密码; (密码需要字母开头) 2.给用户修改密码 给自己修改密码 password 用户名 给别的用户修改密码(需要dba权限) alter user 用户名 identified by 新密码 3.删除用户 drop user 用户名 [cascade] 4.赋予角色(系统权限) grant connect to 用户名(允许用户登录数据库) grant resource to 用户名(允许用户建表) 5.赋予对象权限(select/update/insert/delete/all) grant select on emp to 用户 (用户可以查询scott的emp表) revoke select on emp from 用户 (scott收回用户查询权限) 6.权限维护 用户可以将权限继续给别人 |--对象权限 with grant option |--系统权限 with admin option Profile管理用户口令 1.账户锁定 (指定用户scott最多只能尝试3次登陆,锁定时间为2天) creat profile lock_account limit failed_login_attempts 3 password_lock_time 2; alter user scott profile lock_account; 2.用户解锁 alter user 用户 account unlock; 3.删除profile drop profile lock_account [cascade] (加了cascade,会把级联的相关东西一起删除) Oracle表的管理 1.数据类型 字符型 char(最大2000字符) varchar2(变长,最大4000个字符) clob(字符型大对象 最大4G) 数字型 number(5)(5位整数) number(5,2)(5位有效数字,2位小数) 日期类型 date(1-1月-1999) timestamp(精确到毫秒) 图片 blob(存放图片/声音 4G) 2.创建表 --学生表 create table student( xh number(4), //学号 xm varchar2(20), //姓名 sex char(2), //性别 birthday date, //出生日期 sal number(7,1)//奖金 ); --班级表 create table class( classid number(2), cname varchar(2)(40) ); 3.修改表 3.1添加一个字段 alter table student add(classid number(2)); 3.2修改一个字段的长度 alter table student modify(xm char(30)); 3.3删除一个字段 alter table student drop column sal; 3.4修改表的名字 rename student to stu; 3.5删除表 drop table student; 4.添加数据 4.1所有字段都插入数据 insert into student values (‘A001’,’张三’,’男’,‘01-一月-05’,10); 4.2插入部分字段 insert into student(xh,xm,sex) values (‘A002’,’Jone’,’女’); 4.3插入空值 insert into student(xh,xm,sex,birthday) values (‘A003’,’李四’,’女’,null); 4.4查询表中birthday为空 select * from student where birthday is (not) null; 5.修改数据 5.1修改一个字段 update student set sex=’女’ where xh=’A001’; 5.2修改多个字段 update student set sex=’男’,birthday=’1-4月-84’ where xh=’A001’; 5.3修改含有null的数据 select *from student where birthday is null; 6.删除数据 6.1删除一条记录 delete from student where xh=’A001’; 6.2删除所有记录,表还在,可恢复 savepoint a; --创建保存点 delete from student; --删除所有记录 rollback to a; --回复到保存点 6.3删除所有记录,表结构还在,不可恢复 truncate table student; 6.4删除表结构和数据 drop table student; Oracle表查询 1.取消重复行 select distinct deptno,job from emp; 2.查询指定列 select ename,sal,job from emp; 3.查询SMITH所在部门你,工作,薪水 select deptno,job,sal from emp where ename=’SMITH’; 4.使用列的别名 select ename (as) “姓名” from emp; 5.显示雇员的年工资 select sal*13+nvl(comm,0)*13 “年薪”,ename from emp; 6.显示工资高于3000的员工 select ename,sal from emp where sal>3000; 7.工资在2000到3000的员工 select ename,sal from emp where sal>2000 and sal<3000; 8. like操作符 8.1 %:表示任意0到多个字符 _ :表示任意单个字符 8.2 显示首字母为S的员工姓名和工资 select ename,sal from emp where ename like ‘S%’; 8.3显示大三个字位大写O的员工姓名和工资 select ename,sal from emp where ename like ‘__O%’; 9.在where中使用in 9.1显示empno为7844,7839,123,456的雇员 select * from emp where empmo in(788,7839,123,456); 10.使用逻辑操作符号 10.1查询工资高于500或者岗位为MANAGER的雇员,同事满足他们姓名首写字母为大写J select * from emp where (sal>500 or job=’MANAGER’) and ename like ‘J%’; 11.排序order by(默认从低到高 asc)(从高到低 desc) 11.1按工资从低到高顺序排序 select * from emp order by sal; 11.2按部门号升序 工资降序排列 select * from emp order by deptno,sal desc; 11.3使用列的别名排序 select ename as “姓名” from emp order by “姓名” asc; Oracle复杂查询 1.数据分组—max,min,avg,sum,count (如果列中有一个分组函数,其他都必须是分组函数) 2.显示工资最高的员工 select ename,sal from emp where sal=(select max(sal) from emp); 3.显示高于平均工资的员工信息 select * from emp where sal>(select avg(sal) from emp); 4.group by对查询的结果分组统计 5.having限制分组显示结果 6.显示每个部门的最高工资和平均工资 select max(sal),avg(sal),deptno from emp group by deptno; 7.显示每个部门的美中岗位的平均工资和最高工资 select max(sal),avg(sal),deptno,job from emp group by deptno,job; 8.显示平均工资低于2000的部门号和它的平均工资 select avg(sal),deptno from emp group by deptno having avg(sal)<2000; Oracle多表查询 1.显示雇员名,雇员工资及部门名字 select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno; 2.显示部门号为10的部门名,员工名和工资 select d.dname,e.ename,e.sal from emp e,dept d where e.deptno=d.deptno and e.deptno=10; 3.显示各个员工的姓名,工资及工资的级别 select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal; 4.自连接(在同一张表上连接查询) 4.1显示员工‘FORD’的上级 select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.ename; 5.子查询(嵌入其他sql语句中的select语句) 5.1 单行子查询(只返回一行数据的查询语句) eg:显示与SMITH同一个部门的所有员工 1)显示SMITH的部门号 select deptno from emp where ename=’SMITH’; 2)显示 select * from emp where deptno=(select deptno from emp where ename=’SMITH’); 5.2多行子查询(返回多行数据的查询语句) eg:查询和部门10的工作名字相同的雇员信息; select * from emp where job in (select distinct job from emp where deptno=10); eg:显示工资比部门30所有员工工资高的员工信息 select ename,sal,deptno from emp where sal> all(select sal from emp where deptno=30); select * from emp where sal>(select max(sal) from emp where deptno=30); eg:显示工资比部门30的任意一个员工的工资高的员工 select ename,sal,deptno from emp where sal> any(select sal from emp where deptno=30); select * from emp where sal>(select min(sal) from emp where deptno=30); 5.3多列子查询(返回多个列数据的子查询数据) eg:查询与SMITH部门和岗位相同的所有雇员 select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH'); 5.4 在from子句中使用子查询 eg:显示高于自己部门平均工资的员工信息 1.查出各个部门的平均工资和部门 select deptno,avg(sal) as myavg from emp group by deptno; 2.把上面的查询结果看做一张子表 select a1.ename,a1.sal,a1.deptno from emp a1,(select deptno,avg(sal)as myavg from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.myavg; Oracle分页查询 1.Oracle有三种分页方式 1.1按rownum来分 select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6; 1.2按rowid来分 1.3按分析函数来分 *用查询结果创建新表 create table mytable (id ,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp; Oracle合并查询 为了合并多个select语句,可以使用集合操作符号union,union all,intersect,minus 1.union 取得两个结果集的并集,自动去掉重复行 select ename,sal from emp where sal>2500 union select ename,sal from emp where job=’MANAGER’; 2.union all 不会取消重复行 select ename,sal from emp where sal>2500 union all select ename,sal from emp where job=’MANAGER’; 3.intersect 取得两条结果的交集 4.minus 取得两个结果的差集(显示存在第一个集合 而不存在第二个集合中的数据) *to_date函数 to_date(‘1988-12-12’ ,’yyyy-mm-dd’) *用子查询插入数据 insert into kkk(myid,myname,mydept) select empno,ename,deptno from emp where deptno=10; *使用子查询更新语句 update emp set(job,sal,comm)=(select job,sal,comm from emp where ename=’SMITH’) where ename=’SCOTT’; JAVA操作Oracle 1.JDBC_ODBC连接 import java.sql.*; public class OracleTest { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver Connection=DriverManager.getConnection("jdbc:odbc:OraTest","scott","admin"); Statement sm =ct.createStatement(); ResultSet rs = sm.executeQuery("select * from emp"); while(rs.next()){ System.out.println("用户名: "+rs.getString(2)); } } catch (Exception e) { e.printStackTrace(); } } } 2.JDBC连接 import java.sql.*; public class OracleTest2 { public static void main(String[] args) { try { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection_ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","admin"); Statement sm =ct.createStatement(); ResultSet rs = sm.executeQuery("select * from emp"); while(rs.next()){ System.out.println("用名:"+rs.getString(2)); } } catch (Exception e) { e.printStackTrace(); } } } 事务 1.提交事务实行commit语句可以提交事务 2.回退事务 2.1 设置保存点 savepoint a 2.2取消部分事务 rollback to a 2.3取消全部事务 rollback 3.只读事务 只允许执行查询的操作 set transaction read only Oracle的sql函数 1.字符函数 1.1 lower(char):将字符串转换为小写 select lower(ename) from emp; 1.2 upper(char):将字符转换为大写 1.3 length(char):返回字符串的长度 eg:显示名字为5个字符的员工 select * from emp where length(ename)=5; 1.4 substr(char,m,n):去子串,从第m位取n个字符 eg:显示员工前3个字符 select substr(ename,1,3) from emp; *以首字母大写的方式显示员工姓名 select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp; 1.5 replace(char1,search_string,replace_string) eg:显示员工姓名 用小a代替大A select replace(ename,'A','a') from emp; 2.数学函数 2.1 round(n,[m]) 执行四舍五入,如不写m,则四舍五入到整数;m为正数四舍五入到小数点m位后;m为负,四舍五入到小数点m位前 2.2 trunc(n,[m]) 截取数字.不写m,截取小数部分;m是正数,截取到小数点m为后;如果是负数,截取到小数点前m位 2.3 mod(m,n) 取模 2.4 floor(n) 返回小于等于n的最大整数 2.5 ceil(n) 返回大于等于n的最小整数 3.日期函数 3.1 sysdate:返回系统时间 e.g:显示每个员工加入公司的天数 SQL> select ename,trunc(sysdate-hiredate) as "入职天数" from emp; 3.2 add_months(d,n):从d加n个月时间 e.g:查询已经入职8个月多的员工 select * from emp where sysdate>=add_months(hiredate,8); 3.3 last_day(d): 返回制定日期所在月的最后一天 eg:找出各月倒数第3天受雇的员工 select ename,hiredate from emp where hiredate=last_day(hiredate)-2; 4.转换函数 4.1 to_char eg:日期显示时/分/秒 select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp; * 显示薪水,把本地货币单位加在前面 select ename,to_char(sal,'L99999.99') from emp; 9:显示数字,忽略前面的0 0:显示数字,位数不足用0补齐 . :在指定位置显示小数点 , :在制定为止显示逗号 $:在数字前面加美元 L:在数字前面家本地货币符号 C:在数字前面加国际货币符号 G:在指定位置显示组分隔符 D:在指定位置显示小数点符号(.) eg:显示1980年入职的所有员工 select * from emp where to_char (hiredate,'yy')=80; 5.系统函数 5.1 sys_context 1)terminal:当前会话客户所对应的终端标识符 2)language:语言 3)db_name:当前数据库名称 4)nls_date_format:当前会话客户对应日期格式 5)session_user:当前会话客户对应的数据库用户名 6)current_schema:当前会话客户对应的默认方案名 7)host:返回数据库所在主机的名称 eg:查询你正在使用的数据库 select sys_context(‘userenv’,’db_name’) from dual; SYSDBA与SYSOPER权限区别 导入和导出 1.导出:导出表,导出方案,导出数据库 1.1导出表 1)导出自己的表 exp userid=scott/admin@oral tables=(emp,dept) file=d:\e1.dmp; 2)导出其他方案的表(需要dba权或exp_full_database权限),比如system导出scott表 exp userid=system/admin@oral tables=(scott.emp) file=d:\1.dmp 3)导出表结构 exp userid=scott/admin@oral tables=(emp) file=d:\1.dmp rows=n; 4)使用直接导出方式(速度快) exp userid=scott/admin@oral tables=(emp) file=d:\1.dmp direct=y; 1.2导出方案(导出所有对象[表,索引,约束]和数据) 1)导出自己的方案 exp userid=scott/admin@oral owner=scott file=d:\e1.dmp; 2)导出其他方案 exp userid=system/admin@oral owner=(system,scott) file=d:\1.dmp 1.3导出数据库 1)增量备份 exp userid =system/admin@oral full=y inctype=complete file=d:\1.dmp; 2.导入 2.1导入表 1)导入自己的表 imp userid=scott/admin@oral tables=(emp,dept) file=d:\xx.dmp; 2)导入其他方案的表(需要dba权或imp_full_database权限) imp userid=system/admin@oral tables=(scott.emp) file=d:\xx.dmp touser=scott 3)导入表结构 imp userid=scott/admin@oral tables=(emp) file=d:\xx.dmp rows=n; 4)导入数据 imp userid=scott/admin@oral tables=(emp) file=d:\xx.dmp ignore=y; 2.2导入方案 1)导入自己的方案 imp userid=scott/admin file=d:\xx.dmp; 2)导入其他方案 imp userid=system/admin file=d:\xx.dmp fromuser=system touser=scott; 2.3导入数据库 imp userid=system/admin full=y file=d:\xx.dmp; 数据字典 1.user_tables 显示当前用户所拥有的所有表 select table_name from user_tables; 2.all_tabless 显示当前用户可以访问的所有表 select table_name from all_tables; 3.dba_tables 显示所有方案拥有的数据库表 约束 1.not null(非空,必须提供数据) 2.unique(唯一,列值不能重复 可以为null) 3.primary key(主键,唯一标示表行的数据,不重复不为空,一张表最多只能有一个主键,但可以有多个unique) 4.foreign key(外键,用于定义主表和从表的关系,外键定义在从表,主表必须有主键或unique约束) 5.check(强制数据必须满足的条件) 商店售货系统表设计案例 由下面三个表组成: 1.商品goods(商品号goodsId,商品名goodsName,单价unitprice,商品类别category,供应商provider); 2.客户customer(客户号customerId,姓名name,住在address,电邮email,性别sex,身份证cardId); 3.购买purchase(客户号customerId,商品号goodsId,购买数量nums); 1.建表,在定义中要求声明: (1).每个表的主外键; (2).客户的姓名不能为空值; (3).单价必须大于0,购买数量必须在1到30之间; (4).电邮不能够重复; (5).客户的性别必须是男或者女,默认是男; SQL>createtablegoods(goodsIdchar(8) primarykey,--主键goodsNamevarchar2(30), unitpricenumber(10,2) check(unitprice>0), categoryvarchar2(8), providervarchar2(30)); SQL> create table customer(customerId char(8) primary key, name varchar2(30) not null, address varchar2(50), email varchar2(50) unique, sex char(2) default '男' check(sex in ('男','女')), cardId char(18) ); SQL> create table purchase(customerId char(8) references customer(customerId), goodsId char(8) references goods(goodsId), nums number(2) check (nums between 1 and 30));

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享文档获得金币 ]
0 人已下载

下载文档

相关文档