oracle基础练习.docx

基础概念:数据库:存储数据的数据库,Oracle一般只有一个全局数据库 XE,ORCL。表空间:是Oracle存储数据的地方,由若干个数据文件组成,*.dbf。模式和模式对象:模式就是用户，模式对象是数据库对象的集合，比如表、视图、索引、存储过程。卸载Oracle的注意事项:0:需要先备份重要数据1:得合法删除(通过程序自带的卸载工具卸载,如360,程序和功能)2:卸载完成后,需要手动移除安装文件夹3:查看系统服务,如果有Oracle开始的服务,删除服务打开服务快捷方式:window+R(services.msc)安装Oracle的注意事项:1.安装目录中不能有中文目录,最好不要有空格2.最好不要安装到系统盘,怕数据丢失Oracle:8i/9i-Oracle10g/11g-Oracle12C版本:企业版 (enterprise Edition)个人版 (Express Edition)占用端口号:1521 和 8080 ,tomcat换端口号,否则登录Oracle数据库SSH:Struts+spring+hibernateSSI:SpringMVC+Spring+IBatis配置监听器:Oracle.ListenerOracleService如何配置监听器:主机名和listener里的不一样Oracle的基本概念:1.数据库:存储数据的仓库,数据一般只有一个(个人版XE,企业版orcl)2.用户(模式 schema):Oracle是通过不同的用户来区分数据库对象3.模式(用户/数据库)对象:指的是这个用户所拥有的表/视图/索引/过程.4.表空间:是Oracle存储数据的地方,一个表空间对应一个或者多个的数据文件*.dbf如何登陆Oracle数据库1.pl/sql developersqlplus:系统用户:sys(只能以sysdba,sysoper的身份登陆) system(normal身份)登陆/切换用户的sql命令:conn 用户/密码 as sysdba;Oracle 的sql应用1查询不重复的列值:stuName,stuAge()select distinct stuName,stuAge from student;select stuName,stuAge from student group by stuName,stuAge having count(1)=1;2如何起别名?(空格 或者 as)如何排序?如何添加条件?select stuName as 姓名,stuAge as 年 龄 from student;order by 列名 asc | desc;where3如何根据现有表创建一个新表create table 表名asselect * from studentwhere 1=2;创建新表并添加一列create table 表名asselect s.*,T113 as cname from student s;常用的sql命令:1.show user:显示当前登录的用户 中括号里的超级管理员才加2.conn 用户名/密码as sysdba:切换用户,登录数据库3.desc :描述表结构在Oracle中增删改语句会自动开启事务,必须提交事务查看表:select * from 表名;查看表结构:desc 表名;sql语句的分类:4类数据操纵语音(DML):insert update delete select事务控制语音(TCL):commit rollback savepoint数据定义语音(DDL):create drop alter truncate数据控制语音(DCL):grant revoke在editor中编辑,点击齿轮运行:么事别乱敲回车,check约束可以直接写,选tables可以直接添加数据失效后,更改:tools-pre.-key conf.-reset to default-okOracle常用的数据类型有哪些?char varchar2 number date lob(blob clob)标准日期格式:(1-6月 2016) to_date(2016-6-1,yyyy-MM-dd)date2016-6-1或者直接编辑 选择myobject,tables ,edit data分析函数:rank() over (partition by 列名 order by 列名 )查询平均工资大于3000的部门/*select * from dept d where (select avg(sal) from employee e where e.deptno = d.deptno)3000;*/=创建表空间注意事项:1.数据文件不要放在容易看到和删除的地方(比如桌面,磁盘根目录)2.表空间不能非法删除,否则数据库无法启动修改表空间大小:1.修改数据文件大小alter database datafiled:datad.dbfresize 10M;2.添加数据文件alter datafiled:datad2.dbfsize 5Mautoextend on;3.修改表空间为只读状态alter tablespace hello read only;alter tablespace hello read write;删除表空间drop tablespace helloincluding contents and datafiles;连带文件夹和数据一同删除创建用户create user zhaoidentified by sadefault tablespace hello;privilege 权限expire:密码定期过期授权:系统权限:grant connect,resource to zhao;revoke connect,resource from zhao;grant create synonym to zhao;grant create public synonym to zhao;grant select on dept to public;对象权限:grant all on emp to zhao;revoke all on emp from zhao;创建序列:create sequence seq_deptstart with 50increment by 10;1.添加数据insert into dept(列名) values (seq_dept.nextval);2.查看当前序列值select seq_dept.currval from dual;3.drop sequence 名创建同义词:在其他用户中创建Scott的同义词;create or replace synonym emp for Scott.emp;创建公有同义词:表是谁的谁创建(前提条件,其他用户得有权限访问同义词对应的对象)create public synonym emp for Scott;-查询工资总额大于1000的部门信息（2种方式）1. 分组：select * from dept where deptno in (select deptno from emp group by deptno having sum(sal) 1000)2. 子查询select * from dept d where (select sum(sal) from emp e where e.deptno=d.detpno) 1000;-删除数据库不删除结构/*truncate table dept;*/-查询所有/*select * from dept;*/-添加列/*alter table dept add(looc varchar2(10);*/-删除列/*alter table dept drop column looc;*/-添加数据/*insert into dept values(40,accp4,邯郸4);*/-选择无重复的数据查询/*select distinct dname,loc from dept;*/-升序排列/*select * from dept order by loc ASC;*/-降序排序/*select * from dept order by loc DESC;*/-用别名显示/*select depton as 学号, dname as 课程, loc 地区, FROM dept;*/-复制个表出来吧数据,和结构，复制过来/*create table dept2asselect * from dept;*/-复制个表出来不复制数据,只结构，复制i过来/*create table dept3asselect * from dept where 1=2;*/-查看表中的函数/*select count(1) from dept;*/-查看表中的函数(效率低)/*select count(*) from dept;*/-取出列不重复的数据/*select dname,locfrom deptgroup by dname,lochaving(count(dname|loc)=5 AND rn=9;*/-SQL操作符-1.算数操作符号 +-*/加减乘除-2.比较运算符 = = AND IN LIKE-3.逻辑运算符 or and not 或者，和，非-4.集合操作符 nuion(联合) unionall(联合所有) intersect(交集) minus(减级)-union 联合查询两个（不重复）的数据/*select * from deptunionselect * from dept2;*/-union联合查询排序/*select loc from deptunionselect loc from dept2order by loc;*/-union all 查询所有的数据/*select * from deptunion allselect * from dept2;*/-intersect 交集 返回两个都有的行/*select * from deptintersectselect * from dept2;*/-minus 查询第一个中在第二个里面没有的行/*select * from deptmiunsselect * from dept2;*/-链接操作符-链接运算符|用于将两个或者多个字符串合并成一个字符串，或者将一个字符与一个数值合并在一起/*select dname|_|loc from dept;*/-主键/*alter table deptadd constraint depton PRIMARY KEY (depton);*/-自增-sql函数-to_char把时间转换为varchar2数据/*select to_char(sysdate,YYYY年fmMM月fmDD日HH24:MI:ss)from dual;*/-将数值转换为字符串并且用货币当为前缀/*select to_char(1210.7,$9,999.00) from dual;*/*select to_char(sysdate,YYYY年fmMM月fmDD日hh:mi:ss) from dual;*/*select to_char(1200.2,$9,999.00) from dual;*/*select to_char(sysdate,YYYY年fmMM月fmDD日HH24:MI:SS) from dual;select to_char(123.1,$9,999.00) from dual;*/-将char varchar2转换为日期函数/*select to_date(2017-01-01,yyyy-mm-dd) from dual;*/-nvl 的用法如果money2是个null 那么什么和它想加都是null了，nvl意思是如果money2是个null就把它换成0-select stuname,money+nvl(money2,0) from stuinfo;-判断money2如果money2不是null就返回money+money2,如果是null就返回money./*select stuname,nvl2(money2,money+money2,money) from stuinfo;*/-转换date和Java中的switch语句条件如果是01就是一月 如果02就二月 else就是后十月/*select decode(to_char(day,fmmm),01,一月,02,二月,后十月) from stuinfo;*/-插入数据/*insert into emp values(1111,雷老虎,管理,2,to_date(1991-01-02,yyyy/mm/dd),1000,1000,12);*/-分析函数-rank/*select empno,ename,sal,DENSE_rank() over(order by sal desc) as 排名 from emp;*/*select empno,ename,sal,ROW_NUMBER() over(order by sal desc) from emp;*/-SQL综合练习-显示员工的就职年度-使用round对年份四舍五入/*select empno,ename,hiredate, to_char(round(hiredate,mm),YYYY)|年份 from emp;*/-列出只有3个雇员的部门having 是在group by 后面使用/*select deptno,count(1)from emp group by deptno having count(1)3;*/-查看是不是最后一天入职的/*select ename, hiredate,last_day(hiredate) from emp where hiredate=last_day(hiredate);*/-创建个表空间/*create tablespace EPET datafileD:orcaleappOracleoradataXEEPET.DBF size 5m autoextend on;*/-删除表空间数据文件需要手动删除/*drop tablespace EPET;*/-删除表空间数据文件一起删除/*drop tablespace EPET including contents and datafiles;*/-修改表空间的大小 改变数据文件的大小/*alter database datafile D:orcaleapporacleoradataXEEPET.DBF resize 10m;*/ -表空间只读/*alter tablespace EPET read only;*/-表空间可读可写/*alter tablespace EPET read write;*/-创建用户 /*create user liidentified by yapengdefault tablespace epet;-默认表空间*/-删除用户/*drop user li cascade;*/-用户授权/*grant resource to li;*/-撤销权限/*revoke resource from li;*/-授权查询/*grant select on emp to li;*/-序列/*create table toys( toyid number not null, toyname varchar2(10), toydate date);*/*create sequence se start with 0 increment by 1 maxvalue 2000 nocycle cache 30; */* insert into toys (toyid,toyname,toydate) values(se.nextval,李逵,to_date(2012/01/01,YYYY,MM,dd); */-查看当前序列的值/*select se.currval from toys; */-更改序列/*-dba给予创建同义词权限grant create synonym to T111;*/-创建同义词/*create or replace synonym emp for system.emp;*/-dba给予创建公共同义词权限/*grant create public synonym to system;*/-创建公共同义词/*create or replace public synonym d for emp;*/-把查询权限给所有人/*grant select on emp to public;*/-查看用户有哪些同义词/*select * from user_synonyms;*/-索引-1：B树索引 是Oracle的标准索引用于数值，适用于高基数列-2: 反向键索引，主要解决IO瓶颈问题的-3：位图索引用于取值较少的（主键不适合）-表分区范围分区/*CREATE TABLE SALES1( SALES_ID NUMBER, PRODUCT_ID VARCHAR2(5), SALES_DATE DATE NOT NULL)PARTITION BY RANGE (SALES_DATE)( PARTITION P1 VALUES LESS THAN (to_date(2013-04-1, yyyy-mm-dd), PARTITION P2 VALUES LESS THAN (to_date(2013-07-1, yyyy-mm-dd), PARTITION P3 VALUES LESS THAN (to_date(2013-10-1, yyyy-mm-dd), PARTITION P4 VALUES LESS THAN (to_date(2014-01-1, yyyy-mm-dd), PARTITION P5 VALUES LESS THAN (maxvalue);-要查看在第三季度的数据SELECT * FROM SALES1 partition(P3);-要删除第三季度的数据DELETE FROM SALES1 partition(P3);*/-间隔分区-创建间隔分区表CREATE TABLE SALES2( SALES_ID NUMBER, PRODUCT_ID VARCHAR2(5), SALES_DATE DATE NOT NULL) PARTITION BY RANGE(SALES_DATE) INTERVAL(NUMTOYMINTERVAL(3,MONTH) (PARTITION P1 VALUES LESS THAN (to_date(2013-04-1,yyyy/mm/dd);-插入数据 INSERT INTO sales2 VALUES (1,a,to_date(2013-08-1),10,1);-获得分区情况 SELECT table_name,partition_name FROM user_tab_partitions WHERE table_name=UPPER(sales2);-查询输出结果，系统自动根据输入数据情况创建新分区“SYS_P82” TABLE_NAME PARTITION_NAME - SALES2 P1 SALES2 SYS_P82-查询分区数据 SELECT * FROM sales2 PARTITION(sys_P82);/*=| 利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表=*/*准备工作*/-1.创建普通SALES表CREATE TABLE SALES( SALES_ID NUMBER, PRODUCT_ID VARCHAR2(5), SALES_DATE DATE NOT NULL);-删除数据库不删除结构/*truncate table dept;*/-查询所有/*select * from dept;*/-添加列/*alter table dept add(looc varchar2(10);*/-删除列/*alter table dept drop column looc;*/-添加数据/*insert into dept values(40,accp4,邯郸4);*/-选择无重复的数据查询/*select distinct dname,loc from dept;*/-升序排列/*select * from dept order by loc ASC;*/-降序排序/*select * from dept order by loc DESC;*/-用别名显示/*select depton as 学号, dname as 课程, loc 地区, FROM dept;*/-复制个表出来吧数据,和结构，复制过来/*create table dept2asselect * from dept;*/-复制个表出来不复制数据,只结构，复制i过来/*create table dept3asselect * from dept where 1=2;*/-查看表中的函数/*select count(1) from dept;*/-查看表中的函数(效率低)/*select count(*) from dept;*/-取出列不重复的数据/*select dname,locfrom deptgroup by dname,lochaving(count(dname|loc)=5 AND rn=9;*/-SQL操作符-1.算数操作符号 +-*/加减乘除-2.比较运算符 = = AND IN LIKE-3.逻辑运算符 or and not 或者，和，非-4.集合操作符 nuion(联合) unionall(联合所有) intersect(交集) minus(减级)-union 联合查询两个（不重复）的数据/*select * from deptunionselect * from dept2;*/-union联合查询排序/*select loc from deptunionselect loc from dept2order by loc;*/-union all 查询所有的数据/*select * from deptunion allselect * from dept2;*/-intersect 交集 返回两个都有的行/*select * from deptintersectselect * from dept2;*/-minus 查询第一个中在第二个里面没有的行/*select * from deptmiunsselect * from dept2;*/-链接操作符-链接运算符|用于将两个或者多个字符串合并成一个字符串，或者将一个字符与一个数值合并在一起/*select dname|_|loc from dept;*/-主键/*alter table deptadd constraint depton PRIMARY KEY (depton);*/-自增-sql函数-to_char把时间转换为varchar2数据/*select to_char(sysdate,YYYY年fmMM月fmDD日HH24:MI:ss)from dual;*/-将数值转换为字符串并且用货币当为前缀/*select to_char(1210.7,$9,999.00) from dual;*/*select to_char(sysdate,YYYY年fmMM月fmDD日hh:mi:ss) from dual;*/*select to_char(1200.2,$9,999.00) from dual;*/*select to_char(sysdate,YYYY年fmMM月fmDD日HH24:MI:SS) from dual;select to_char(123.1,$9,999.00) from dual;*/-将char varchar2转换为日期函数/*select to_date(2017-01-01,yyyy-mm-dd) from dual;*/-nvl 的用法如果money2是个null 那么什么和它想加都是null了，nvl意思是如果money2是个null就把它换成0-select stuname,money+nvl(money2,0) from stuinfo;-判断money2如果money2不是null就返回money+money2,如果是null就返回money./*select stuname,nvl2(money2,money+money2,money) from stuinfo;*/-转换date和java中的switch语句条件如果是01就是一月 如果02就二月 else就是后十月/*select decode(to_char(day,fmmm),01,一月,02,二月,后十月) from stuinfo;*/-插入数据/*insert into emp values(1111,雷老虎,管理,2,to_date(1991-01-02,yyyy/mm/dd),1000,1000,12);*/-分析函数-rank/*select empno,ename,sal,DENSE_rank() over(order by sal desc) as 排名 from emp;*/*select empno,ename,sal,ROW_NUMBER() over(order by sal desc) from emp;*/-SQL综合练习-显示员工的就职年度-使用round对年份四舍五入/*select empno,ename,hiredate, to_char(round(hiredate,mm),YYYY)|年份 from emp;*/-列出只有3个雇员的部门having 是在group by 后面使用/*select deptno,count(1)from empgroup by deptnohaving count(1)3;*/-查看是不是最后一天入职的/*select ename, hiredate,last_day(hiredate) from emp where hiredate=last_day(hiredate);*/-创建个表空间/*create tablespace EPETdatafileD:orcaleappOracleoradataXEEPET.DBFsize 5mautoextend on;*/-删除表空间数据文件需要手动删除/*drop tablespace EPET;*/-删除表空间数据文件一起删除/*drop tablespace EPET including contents and datafiles;*/-修改表空间的大小 改变数据文件的大小/*alter databasedatafile D:orcaleapporacleoradataXEEPET.DBFresize 10m;*/-表空间只读/*alter tablespace EPET read only;*/-表空间可读可写/*alter tablespace EPET read write;*/-创建用户/*create user liidentified by yapengdefault tablespace epet;-默认表空间*/-删除用户/*drop user li cascade;*/-用户授权/*grant resource to li;*/-撤销权限/*revoke resource from li;*/-授权查询/*grant select on emp to li;*/-序列/*create table toys(toyid number not null,toyname varchar2(10),toydate date);*/*create sequence sestart with 0increment by 1maxvalue 2000nocyclecache 30; */* insert into toys (toyid,toyname,toydate)values(se.nextval,李逵,to_date(2012/01/01,YYYY,MM,dd); */-查看当前序列的值/*select se.currval from toys;*/-更改序列/*-dba给予创建同义词权限grant create synonym to T111;*/-创建同义词/*create or replace synonym emp for system.emp;*/-dba给予创建公共同义词权限/*grant create public synonym to system;*/-创建公共同义词/*create or replace pub