Oracle数据库开发

Oracle数据库开发

系统用户：sys、system、sysman、scott
sys>system，当用sys用户登录时，必须有管理员或系统操作人员的权限才能登录；而system用户能直接登录。
sysman用户，用于操作企业管理器来使用的，也是管理员级别的用户。
scott默认的密码是tiger。

使用system用户登录：[username/password][@server][as sysdba|sysoper]

1、show user命令  查看当前登录的用户
输入正常命令不需要分号，但当输入sql语句的时候，需要分号，来表示sql语句的结束
2、desc dba_users数据字典
desc查看数据字典
select username from dba_users;  查看系统用户名

（1）启用用户的语句
alter user username account unlock;
例:对scott用户解锁
alter user scott account unlock;
（2）使用scott用户登录SQL Plus
connect scott/tiger;

数据库、表空间、数据文件、表、数据的最好办法就是想象一个装满东西的柜子。数据库其实就是柜子，柜中的抽屉是表空间，抽屉中的文件夹是数据文件，文件夹中的纸是表，写在纸上的信息就是数据。

在oracle中，表空间是存储概念上的，建立表空间需要有对应的数据文件，数据文件建立好之后直接会把一定的磁盘空间分配给它，这样可以对数据库的存储空间进行有效的管理。然后在建表的时候指定对应的表空间，该表的数据就会都存在表空间对应的数据文件上，和Mysql那种每个表一个文件的方式比起来，存储的可控性更强。

oracle和mysql不同，不存在mysql中那种数据库的概念，而是实例的概念，当然，也可以在实例里建立不同的user来区分，每个user对应的表都是相对独立的，比如两个user下可以分别建同名的表，但又可以通过授权来交互使用。

创建表空间：
CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'xx.dbf' SIZE xx;
永久：CREATE TABLESPACE tablespace_name DATAFILE 'xx.dbf' SIZE xx;
临时：CREATE TEMPORARY TABLESPACE tablespace_name TEMPFILE 'xx.dbf' SIZE xx;
没有指定路径时，默认在oracle安装目录下。

查看表空间具体路径
desc dba_data_file查看数据字典字段
select file_name from dba_data_file where tablespace_name='表空间名字要大写';查看永久表空间数据文件
select file_name from dba_temp_file where tablespace_name='表空间名字要大写';查看临时表空间数据文件

修改表空间：（永久表空间）
1、修改表空间的状态：
①设置联机或脱机状态：ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;
（创建表空间后，默认为ONLINE）（设置成脱机状态时，就无法使用）
联机状态可通过数据字典DBA_TABLESPACE的STATUS字段查看。
②设置只读或可读写状态：ALTER TABLESPACE tablespace_name READ ONLY|READ WRITE;
前提条件：ONLINE。默认状态为READ WRITE（与ONLINE状态等价）。
可通过数据字典DBA_TABLESPACE的STATUS字段查看。

修改表空间数据文件
alter tablespace tablespace_name add datafile '文件名.dbf' size xx(大小);
不能删除表空间的第一个数据文件，要删除的话，必须删除整个表空间。
alter tablespace tablespace_name drop datafile '表空间名字';
查询修改的文件
select file_name  from dba_data_file where tablespace_name='表空间名（大写）';

表的建立文件

字符型：
CHAR(n)、NCHAR(n)：固定长度类型，NCHAR是按照Unicode格式进行存放数据的，CHAR(n)n<2000、NCHAR(n)n<1000。一般用NCHAR(n)来存放汉字。
VARCHAR2(n)、NVARCHAR2(n)：可变长度类型，VARCHAR(n)n<4000、NVARCHAR(n)n<2000
数值型：
NUMBER(p,s) p有效数字，s小数点后的位数。如果s为正数，代表从小数点到最低有效数字的位数；如果s为负数，代表从最大有效数字到小数点的位数（数值型通常采用NUMBER类型）
FLOAT(n) 存储二进制数据，能表示的数据是二进制的位数是1到126位，通过乘上0.30103来得到十进制数。
日期型：
DATE 表示范围：公元前4712年1月1日到公元9999年12月31日，能精确到秒。（一般使用）
TIMESTAMP 精确到小数秒。
其他类型：（存放大对象的数据）
BLOB 存放4G的二进制数据
CLOB 存放4G的字符串数据

字段操作

1.修改表名
rename 表名 to 新的表名
2.列改名
alter table 表名 rename column 列名 to  新列名
3.增加列
alter table table_name add 列名(数据类型)
4.删除列
alter table table_name drop column 列名
5.修改列
alter table table_name modify 列名 数据类型;（没有数据才可以修改）

删除表：
TRUNCATE TABLE tbl_name; 删除表中全部数据，速度比DELETE快很多
DROP TABLE tbl_name; 删除整个表结构

数据操作

建表时复制：
create table new_table as select column1,...|* from old_table;
如果对整个表进行复制：as select * from old_table;
如果只复制指定的字段：as select column1,... from old_table;
如果只想复制表结构而不想复制表里面的内容可以加上 where 1=2;
插入数据时复制：
insert into table_name[(column1,...)] select column1,...|* from old_table;

修改表中的数据：update语句
Update table_name 
Set column1=value1,column2=value2,...[where conditions]（可有可不的)；其中数据类型相匹配
主键约束
一张表只能设计一个主键约束，主键约束可以由多个字段构成（联合主键或复合主键）。
表级约束：CONSTRAINT constraint_name constraint_type(column_name1,...);
联合主键：CONSTRAINT constraint_name PRIMARY KEY(column_name1,...);

1.在创建表时设置主键约束(列级)
语法：CREATE TABLE table_name(
column_name datatype PRIMARY KEY,...);
2.在创建表时设置主键约束（表级）
语法：CREATE TABLE table_name(
column_name datatype,...,
CONSTRAINT pk_name PRIMARY KEY(column_name1,...)); //pk_name 通常用pk_column1_column2表示
3.数据字典：user_constraints 可以查看某个表中的主键约束信息。desc user_constraints查看约束的名字：select constraint_name from user_constraints where table_name='tbl_name';表名要大写
外键约束
1.主表的字段必须是主键
2.主从表中相应的字段必须是同一个数据类型
3.从表中外键字段的值必须来自主表中的相应字段的值，或者null
table1从表，table2主表
CREATE TABLE table1
(column_name datatype REFERENCES table2(column_name),...);
查询：
1.基本查询语句：
select [distinct] column_name,.../* from tablename[where conditions]
    给字段设置别名：
SELECT column_name AS new_name,... FROM table_name;
    运算符和表达式：
算数运算符（+，-，*，/）
比较运算符（>,>=,<,<=,=,<>）
逻辑运算符（and,or,not）
    在select语句中使用运算符：    带条件的查询：    模糊查询：
1、通配符的使用：
一个_只能代表一个字符，一个%可以代表0到多个任意字符
2、查询以a开头的用户名：
select * from users where username like 'a%';
3、查询第二个字符为a 的用户名：
select * from users where username like '_a%';
    范围查询：
1、BETWEEN...AND...，查询的是一个闭合区间的结果，包含头也包含尾。
2、IN()/NOT IN(),括号中的为具体的值。
1、范围查询，如查找工资在800到2000的(包括了800和2000)：
select * from users where salary between 800 and 2000;
如果想要不包括800和2000这两个数，就可以这样写：
select * from users where salary not between 800 and 2000;
2、in/not in在和不在这些列举出来的数里面：
select * from users where username in('aaa','bbb');
select * from users where username not in('aaa','bbb');
    对查询结果进行排序：
语法：SELECT [DISTINCT] column_name1,...|* FROM table_name [WHERE conditions] 
ORDER BY column_name1 DESC|ASC,...
其中，DESC是降序排序，ASC是升序排序，默认情况下是升序排序
    case ... when   语句:
1) CASE column_name WHEN value1 THEN result1,... [ELSE result] END
 如：select username,case username 
when 'aaa' then '计算机部' 
when 'bbb' then '市场部' 
else '其他部门' end as '部门' 
from users;
2) CASE WHEN column_name=value1 THEN result1,...[ELSE result] END
 如：select username,
case when salary<800 then '工资低' 
when salary>5000 then '工资高' 
end as 工资水平 from users;
************第二种case...when语句更灵活，表达式运算符可以更丰富***********；
************字段值为字符时，需要用单引号括起来，但是对于as后的别名，作为字段名，不需要单引号括起来*************
    decode函数:
decode与case...when类似
decode(列名，判断条件，条件为真的值，默认值)
decode(col_name,value1,result1,...,defaulvalue)
例如：
select username,decode(username,'aaa','计算机部门','bbb','市场部门','其他') as 部门 from users;
2.在SQL*plus中设置格式：
  
  

   
   
1、作用：配合select语句使用，设置查询结果显示的格式，不会修改数据库表字段。
2、设置字段显示样式
语法：COLUMN column_name HEADING new_name;
注：COLUMN 可以简写成COL
3、设置字段的格式化样式
语法：COLUMN column_name FORMAT dataformat;
注：如果是字符类型的值，只能设置它显示的长度,其中a后面跟数字表示字符串显示长度，例：a10 表示显示10位的长度的字符串；数字则用‘9’代表一位，例：999.9 表示整数位为三位，小数位为一位。如果设置的长度比数据的长度小，则数据显示为‘#’。还可以在数字前面加‘$’,例：$999.9 则查询后显示的结果会在结果前加上'$'符。
4、清除设置格式
语法：COLUMN column_name CLEAR;
  
  
3.查询表中的所有字段及置顶字段：
内置函数：

四舍五入函数:
round(n,[m]) m>0:小数点后m位;m<0:小数点前m位
eg:select round(23.4),round(23.45,1),round(23.45,-1) from dual;
23 23.5 20
取整函数：
ceil(n)--取上限 floor(n)--取下限
eg:select ceil(23.45),floor(23.45) from dual;
24 23
绝对值:
abs(n)--取绝对值
eg:select abs(23.45),abs(-23),abs(0) from dual;
23.45 23 0
取余数:
mod(m,n) m/n 若m,n任何一个值为null，则结果返回null值
eg:select mod(5,2) from dual;
1
select mod(5,null) from dual;
（空值）
m的n次幂:
power(m,n)
eg:select power(2,3),power(null,2) from dual;
8 （空值）
平方根:
sqrt(m)
16的平方根表示为sqrt（16）,结果是4
eg:select sqrt(16)from dual;
4
三角函数:
sin(n)、asin(n)--正弦、反正弦
cos(n)、acos(n)--余弦、反余弦
tan(n)、atan(n)--正切、反正切
n 为弧度
eg:select sin(3.124) from dual;
.017591746

字符函数：
(1)大小写转换函数
upper(char) 将小写字母变成大写字母
lower(char)将大写字母变成小写字母
initcap(char)将首字母进行大写转换
例：select upper('abcd'),lower('ABCd'),initcap('aHDX') from dual; 
结果为：ABCD abcd AHDX
(2)获取子字符串函数
substr(char,[m[,n]]) m表示取子串的开始位置 n表示截取子串的位数
例：select substr('abcde',2,3) ,substr('abcde',2),substr('abcde',-2,1) from dual;
结果为： bcd bcde d
注意：m=0表示从首字母开始截取，n可以省略表示从m的位置截取到字符串末尾；
如果m=0且省略n那么结果仍为源字符串；
如果m为负数表示从字符串的尾部开始截取

查找char1中char2存在的位置

instr(char1,char2) 

3、获取字符串长度函数
 （1）LENGTH(str)：截取长度，包含空格字符
 例：select length('acd ') from dual;结果为4
 4、字符串连接函数
 （1）CONCAT(str1,str2)：连接字符串，与操作符‘||’的作用一样。
例：select concat('ab','cd') from dual;
或者
select 'ab'||'cd' from dual;
(5)去除子串函数
trim(c2 from c1) 表示从c1字符串中去除c2这个字符，注意截取集只能有一个字符
例：select trim('a' from 'abc') from dual; 结果为bc
如果写成select trim('ab' from 'abc') from dual;就会提示出错
ltrim(c1[,c2]) 表示从c1首字符开始去除一个c2，不管c1当中有多少个c2，都只能从头开始去除一个！
例：select ltrim('ababaa','a') from dual; 结果为babaa
rtrim(c1[,c2]) 表示从c1末尾开始去除一个c2，l表示左边、r表示右边。
例：select rtirm('ababaa','a') from dual;结果为abab 会发现截取了两个a，说明这里的截取集只能有一个字符的含义是只能有一种单个字符，既不能出现‘ab’这样的类型，但是截取结果不一定就截取了一个字符，比如截取集是‘a’，如果有相邻的字符会一起被截取，在源字符串中‘ababaa’从末端截去时两个a相邻所以都被截取了。
trim(c1) 表示去除首尾的空格
ltrim(c1)表示去除左端的空格
rtrim(c1)表示去除右端的空格
(6)替换函数
replace(char,s_string[,r_string]) 省略r_string用空格替换
s_string表示要替换的字符串，r_string表示替换成什么样的字符串。
注意若干个字符同样可以被一个字符替换，例：
select replace('abcde','ab','A') from dual;
结果为Acde
r如果r_string不写空格将会替代
select replace('abcd','a') from dual; 
结果为： bcd(b前有个空格)

日期函数：
（1）系统时间
sysdate 默认格式DD-MON-RR 日-月-年
（2）日期操作
add_months(date,i)在当前日期加上i个月，i可以是任何整数；如果i是小数,系统会自动截取整数部分；如果是负数就是在当前日期减去i个月。
next_day(date,char)表示指定日期的下周几的日期，例：
select next_day(sysdate,'星期一') from dual;
表示的是当前日期的下周一是几年几月几号。这个char表示星期几，可以输入中文也可以输入英文。
last_day(date)返回date所在月的最后一天
months_between(date1,date2)表示两个日期之间相隔的月份,返回值是小数
extract(date from datetime)表示获取指定日期的年或月或日，例：
select extract(year from sysdate) from dual;表示返回当前日期的年
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
此外还可以这样使用：
select extract(hour from timestamp '2015-10-1 17:25:13') from dual;意思是获取时间戳的时。
结果为17

转换函数<br>
1、日期转字符<br>
to_char(date[,fmt[,params]])：date表示要转换的日期，fmt表示转换的格式，params表示日期的语言（一般不写）<br>
fmt的取值：年[YY/YYYY/YEAR],月[MM/MONTH],日[DD/DAY],时[HH12/HH24],分[MI],秒[SS]<br>
2、字符转日期<br>
to_date(str[,fmt[,params]])：str表示日期格式的字符串，fmt表示转换的格式，params表示日期的语言（一般不写），该函数会按照系统默认的格式显示<br>
fmt的取值：年[YY/YYYY/YEAR],月[MM/MONTH],日[DD/DAY],时[HH12/HH24],分[MI],秒[SS]<br>
3、数字转字符<br>
to_char(number[,fmt])：number表示要转换的数字，fmt表示转换的格式<br>
fmt的取值：[9]表示显示数字并忽略前面的0，[0]表示显示数字，位数不足，用0补齐，[.或D]表示显示小数点，[,或G]表示显示千位符，[$]表示美元符，[S]表示加正负号（前后都可以）<br>
4、字符转数字<br>
to_number(str[,fmt])：str表示数字格式的字符串，fmt表示转换的格式，可以省略
select to_number('$912,345,64.877','$999,999,99.999') from dual 结果：91234564.877

在查询中使用字符函数：
desc users;   //users是已经建好的员工信息表
select substr(cardid,7,8) from users;   在员工信息表查询出员工的生日
select replace(deptno,'01','信息技术') from users;  将部门号01全部替换成信息技术
select mod(age,10) from users;  将员工信息表中的年龄字段与10取余数
select extract(year from regdate) from users; 取得员工入职的年份
select * from users where extract(month from regdate)=5;查询出5月份入职的员工信息

示例一: 分页查询显示员工信息:显示员工号,姓名,月薪
 要求:(1)每页显示四条记录
		       (2)显示第二页的员工
		       (3)按照月薪降序排列
              注意:rownum是伪列只能使用<,<=,不能使用>,>=（永远从1开始）
	      Oracle 通过拼接子查询方式实现分页操作
分页查询：将子查询中的rownum变为固定的列，而不是伪列
 		select r,empno,ename,sal 
	                       from (select rownum r,empno,ename,sal 
			             from (select rownum,empno,ename,sal from emp order by sal desc) e1
				       where rownum<=8) e2
	       where r>=5;
              查看伪列的行号
              select rownum,r,empno,ename,sal 
	                       from (select rownum r,empno,ename,sal 
			             from (select rownum,empno,ename,sal from emp order by sal desc) e1
				       where rownum<=8) e2
	       where r>=5;
案例二： --查询本部门薪水大于平均工资的员工；（使用表连接查询）
相关子查询：
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno)
多表查询：
select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal>d.avgsal
相关子查询比多表查询占用cpu少
两个方法的结果一样，如何比较优劣
通过比较select语句的执行计划：
explain plan for，一般放在开头，执行结束之后如何查看explain生成的执行计划：
select * from table(dbms_xplan.display)；可以打印查看执行计划，一般放在末尾
然后看耗费了多少CPU的执行资源，结果发现使用相关子查询比多表查询耗费的CPU资源要少

按部门统计员工的人数
函数查询：
select count(*) Total,
sum(decode(to_char(hiredate,'YYYY'),'1980','1','0')) "1980",
sum(decode(to_char(hiredate,'YYYY'),'1981','1','0'))"1981",
sum(decode(to_char(hiredate,'YYYY'),'1982','1','0'))"1982",
sum(decode(to_char(hiredate,'YYYY'),'1987','1','0')) "1987"
from emp;
子查询:
select
(select count(*) from emp)  total,
(select count(*) from emp where to_char(hiredate,'YYYY')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'YYYY')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'YYYY')='1982') "1982",
(select count(*) from emp where to_char(hiredate,'YYYY')='1987') "1987"
from dual;

Oracle：

select e1.id,WM_concat(e2.SN) from X e1,S e2 where instr(e1.S_S,e2.S1) group by e1.id;

Mysql：

select e1.id,group_concat(e2.SN) from X e1,S e2 where instr(e1.S_S,e2.S1) group by e1.id;

分组group by

Select  a，组函数（X） from table group by a；
在select列表中所有未包含在函数中的列（x）都应该包含在group by子句中
特殊：Select  组函数（X） from table group by a；（这个是可以的）
Select a，b，c， 组函数（X） from table group by a，b，c；

执行顺序：
1、先进行FROM操作，获得笛卡尔积
2、进行WHERE进行过滤
3、进行GROUP BY操作分组
4、进行HAVING过滤分组
5、进行SELECT
6、进行ORDER BY排序

group by的增强，group by rollup(a,b）==group by a,b + group by a + group by 没条件;

select deptno,job,sum(sal)
from emp
group by deptno ,job；

select deptno ,sum(sal)
form emp
group by deptno；

select sum(sal)
from emp；

==
select deptno,job,sum(sal)
from emp
group by rollup(deptno,job);

报表：break on deptno skip 2(表示相同部门只显示一次，不同部门之间跳转两行)
      select deptno,job,sum(sal) from emp group by rollup(deptno,job);
      set pagesize 30

1.使用ttitle命令设置报表的名称，col表示列的意思，15表示空15列显示“我的报表”这个标题，然后再空35个列，显示sql.pno,sql.pno表示报表的页码。
2.col也可以用来设置列的别名，比如第二行把deptno标题设置为部门号
3.break on deptno skip 1，在上一节课程已经介绍过，表示遇见重复的部门号就只显示一次，不同的部门号之间空一行

ttitle col 15 '我的报表' col 35 sql.pno
col deptno heading 部门号
col job heading 职位
col sum(sal) heading 工资总额
break on deptno skip 1
外连接：通过外连接，把对于连接条件不成立的记录，仍然包含在最后的结果中。
左外连接 select * from A,B where A.id = B.id(+)
右外连接 select * from A,B where A.id(+) = B.id
左外连接：当连接条件不成立的时候，等号左边的表仍然被包含
右外连接：当连接条件不成立的时候，等号右边的表仍然被包含
自连接：同一张表视为两张表 ,就是对同一张表进行查询时，把同一张表的别名是不同的两张表

查询员工姓名和员工的老板姓名：
select e.ename 员工姓名，b.ename 老板姓名
from emp e,emp b
where e.mgr=b.empno; //员工的老板号=老板的员工号

自连接不适合操作大表。

层次查询：不存在多表查询,查询结果没有自查询直观
层次查询:在某些情况下可以代替自连接，本质是：一个单表查询<br>
select level,empno,ename,sal,mgr
from emp
connect by prior empno=mgr //(上层的员工号)=(老板号)
start with mgr is null  //或者是start with mgr=7839
order by 1;

一般先执行子查询，再执行主查询；但相关子查询例外
1、查询薪水大于本部门平均薪水的员工信息
相关子查询，员工sal大于本部门sal
select,empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno ) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno)=