oracle 初级学习（王二暖 视频 笔记）

Oracle 入门

Oracle卸载

1用Oracle universal installer 卸载 

  

Sqlplus 常用命令

1、  connect（conn） conn scott/xsb5@orcl

2、  show user 显示用户

3、  clear screen 清屏

4、  spool 指定sqlplus中结果保存到指定位置

spool d:\12.txt

执行查询命令（select * from dept；）

spool off

5、  edit 对上一次命令进行编辑和修改

6、  set time on 将时间显示在左边

set time off 取消时间显示

7、  show error 查看上次执行sql命令的错误

8、  host <DOS命令> host （DOS命令）在sqlplus中调用操作系统命令

9、  @d:\1.sql 执行sql脚本文件；

Oracle11g网络配置简介

两个重要的文件

tnsnames.ora 位于客户端

listener.ora位于服务器

 

超级管理员登陆

Win+r sqlplus /nolog

conn /as sysdba

Em控制（网页版）

https://localhost:1158/em

用户名 sys

Xsb51

方式：sysdba

启动关闭数据库

启动数据库应以超级管理员账号登陆

启动数据库，输入命令： startup；

Startup [nomount] [mount] [open] [force][resetrict] [pfile=filename]

Nomount 启动实例不加载数据库

Mount 启动实例、加载数据库并使数据库处于关闭状态 通常在进行数据库维护时使用。如

执行数据库的完全恢复操作、更改数据库的归档模式等。

Open 默认选项、启动实例、加载并打开数据库

Force 终止实例并重新启动数据库这种启动方式具有一定的强制性，当其他启动模式失效时，可以尝试使用这种启动模式。

Resetrict 以受限的会话方式启动数据库

Pfile 用于指定启动实例时所使用的文本参数文件。

 

 

关闭数据库，输入命令： shutdown immediate；

Shutdown [normal] [transactional][immediate] [abort]

Normal正常方式关闭数据库 对关闭数据库的时间没有限制时，

采用该方式。执行以下操作 阻止任何用户建立新连接，等待当前所有正在连接的用户主动断开连接，当所有用户断开连接后关闭数据库。（消耗时间太长，一般不采用）

Transactional 在当前所有的活动事物被提交完毕之后，关闭数据库。

        

Immediate 在尽可能短的时间内立即关闭数据库

Abort 以终止的方式关闭数据库。

         终止关闭方式，终止关闭方式具有一定的强制性和破坏性。强制中断任何数据库操作，这样有可能丢失一部分数据信息。（除了由于使用其他3种方式无法关闭数据库而使用它之外，应尽量避免这种使用方式。）

 

注意必须以sysdba的身份登录才可以关闭和启动数据库实例。

oracle用localhost连接数据库能连上,用ip连接不上

解决方法1:

打开"开始"菜单->oracle...->配置和移植工具-->Net Configuration Assistant;

删除安装或者建库时默认创建的listener;

然后重新添加一个新的Listener;

 

解决办法2：

1、修改配置文件listener.ora将HOST=你自己的主机名

LISTENER =

 (DESCRIPTION_LIST =

   (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1))

     (ADDRESS = (PROTOCOL = TCP)(HOST = sweetboy_pc)(PORT = 1521))

    )

  )

文件所在路径：

C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

2、检查监听设置，重新启动监听服务。

1.cmd

2.使用“Lsnrctl start” 启动监听程序

3.使用“Lsnrctl stop” 停止监听程序

4. 使用“Lsnrctl status” 查看监听程序

原因:

原来的Listener中host是localhost; 不兼容机器名;

新添加的Listener中host用的是机器名; 兼容localhost;

 注意在本机上访问orcal数据库可以不开listener。由于在本机上客户端与服务器在同一台机子上。而通过网络访问数据库就必须的数据库端开启listener服务。

查看数据库中包含的所有表

select * from tab;

清空回收站

purge recyclebin;

基本sql语句和函数

基本sql语句

DDL（数据定义语言）增删改

create

create table student (sno number(6),snamevarchar2(10),birthday date);

creat table student2 as select* fromstudent;创建student2 复制student的表结构和数据

creat table shudent3 as select * fromstudent where 1>2;  创建student3只复制student的表结构

(sql server中：select *intoshudent2 from student)

alter

alter table student add tele vachar2(11);不加colum

alter table student modify tele varchar2(20);

alter table student drop column tele;加colum

truncate（删除表中的信息）

truncate table student2;(截断表)不用提交、不进入日志

delete table student2; commit;要提交 、会进入日志

drop

drop table student;

drop table student purge; 永久删除

desc 查询表结构

desc student

DML（数据操纵语言）

insert

insert into student values(1,’A’,’11-1月-01’);

alter session set nls_date_format=’yyyymmdd’;更改当前会话的时间格式

insert into studentvalues(1,’A’,’20110101’);

insert into student values(3,null,to_date(‘19810223’,’yyyymmdd’))

insert into student values(3,,to_date(‘19810223’,’yyyymmdd’))

insert into student2 select * from student;(将student中的数据插入到student2中)

select（dsl）

null

select * from student where sname is null;查询null

select count(*) from student2 where snameis not null;查询非空

count(*)

select count(*) from student2;查询行数

upper

select * from student2 where sname = ‘a’;(sql中的字符必须区分大小写)

select * from student2 where upper(sname) =‘A’; 将sname转换为大写

like

select *from student2 where sname like‘A%’;匹配sname 以A开头的学生

select *from student2 where sname like ‘A_’;匹配sname以A开头且A后面只有一个字符的学生

length

在使用length是oracle将汉字和字母的长度看成一样

update student2 set sname=’张三’ where sno=1;

select * from student2 wherelength(sname)=2；返回的结果中包含sname为张三的学生

 

select * from student order by sno desc,sname;sno降序排列 sname升序排列

select * from student2 order by 1 desc,2;第一列降序 第二列降序排列。

 

使用别名的方式

select sno 学号 ,sname “姓名” fromstudent;

select sno “学 号”from student;

 

查询结果连接(||)

select ‘学号是：’||sno||’姓名是： ’||snamefrom student;

 

聚合函数

select sno,sum(score) from group by sno;查看每个学生的总成绩

select KM,avg(score) from 成绩 group bykm;查看每门课程的总成绩

select sno,avg(score) from 成绩 group bysno having (avg(score)>60);查询平均成绩大于60的学生

select sno,avg(score) from 成绩 group bysno having (avg(score)>(select avg(score) from 成绩));查询平均成绩大于所有人平均成绩的学生

聚合函数对每个分组进行

 

delete

delete student where sno=4;

update

update student2 set sname=’AA’ where sno=3;

update student2 set sname = ‘A’’B’ wheresno=4;将sno为4 的学生sname改为A’B

TCL（事务控制语言）

commit

savepoint

rollback

DCL（数据控制语言）

grant（赋予权限）

revoke（删除权限）

函数

to_char 将日期转换为字符串

to_char(sysdate,’yyyymmdd hh24:mi:ss’)

to_date 将字符串转换为日期

to_date(‘19810223’,’yyyymmdd’)

length()求长度

length()

sum()求和

与分组函数相配合 select sno,sum(score) from group by sno;

avg()求平均值

select KM,avg(score) from 成绩 group bykm;

having() 分组限定

select sno,avg(score) from 成绩 group bysno having (avg(score)>60);

 

 

upper（）将小写转化为大写

数据类型

字符（char nchar() varchar2 long）

char或nchar存放固定长度的字符串、型储存字母数字值、1到2000个字节

varchar2支持可变长度的字符串、储存字母数字值、1-4000个字节

varchar尽量少用、高版本不支持

long 储存可变长度的字符数据、最多可储存2GB字节

数值

number[(p[,s])]

可以储存整数、浮点数和实数最高精度38位负的10的38次方到10的38次方

p表示精度（总共多少位），s表示小数点的位数，

如number(3,2) 如1.23，number(3)总共3位的整数若插入小数自动四舍五入。

 

日期时间

data 储存日期和时间部分，精确到秒

timestamp 储存日期、时间和时区信息，秒值精确到小数殿后6位

select sysdate from dual; （查询系统当前时间）

其中dual为了保证sql语言的完整性。

 

select to_char(sysdate,’yyyymmddhh24:mi:ss’) from dual;精确到秒

select to_char(systimestamp,’yyyymmddhh24:mi:ss:ssxff6’) from dual; 精确到小数点后六位

（sql server中： select getdate()）

更改当前时间格式

alter session set nls_date_format=’yyyymmdd’;

raw/long raw（图片等二进制类型）

raw储存二进制文件、最多能储存2000字节

long raw 用于储存可变长度二进制数据最多能储存2GB

lob(大对象类型)

最多能存储多达128T的非结构化信息

lob数据类型允许对数据进行高效、随机、分段的访问

clob：字符型lob 存放大量字符数据

blob：二进制lob存放较大的二进制对象，如图形、视频剪辑和声音文件（为了图片的安全）

bfile：外部二进制文件，存放指针。

伪列（rowid和rownum）

伪列就像一个表列，但是他并不存储在表中

可以从表中查询，但不能插入、更新和删除他们的值

常用的伪列有rowid和rownum

rowid是表中行的储存地址，改地址可以唯一地标示数据库的一行，可以使用rowid快速的定位表中的一行

rownum是查询返回结果集行的序号，可以使用它来限制查询返回的行数，类似（sql server中的top）用到分页

sql函数

单行函数

对于从表中查询的每一行只返回一个值，可以出现在 SELECT 子句中和WHERE 子句中

字符函数

函数	含义	输入	输出
Initcap(char)	首字大写	Select initcap(‘hello’) from dual;	Hello
Lower(char)	全小写	Select lower(‘FUN’) from dual;	fun
Upper(char)	全大写	Select upper(‘sun’) from dual;	SUN
Ltrim(char,set)		Select ltrim( ‘xyzadams’,’xyz’) from dual;	adams
Rtrim(char,set)		Select rtrim(‘xyzadams’,’ams’) from dual;	xyzad
Translate(char, from, to)		Select translate(‘jack’,’j’ ,’b’) from dual;	back
Replace(char, searchstring,[rep string])	替换	Select replace(‘jack and jue’ ,’j’,’bl’) from dual;	Black and blue
Instr (char, m, n)		Select instr (‘worldwide’,’d’) from dual;	5
Substr (char, m, n)	从m取n个	Select substr(‘abcdefg’,3,2) from dual;	cd
Concat (expr1, expr2)	连接到一起	Select concat (‘Hello’,’ world’) from dual;	Hello world

 

 

CHR和ASCII   chr(23)得到23对应的字符， ascii（’A’）得到A对应的ascii值

 

LPAD和RPAD 

lpad(‘abcd’,10,’x’) from dual;左用x补至10位

select lpad(‘abcd’,10,’x’) from dual;

 

TRIM

SELECT TRIM(9 from 9999876789999) FROMdual;返回 87678

 

LENGTH

 

DECODE(在2.2视频中讲解)

 

日期时间函数

日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果

日期函数包括：

ADD_MONTHS

select add_months(sysdate,5)from dual;现在的时间加上5个月

MONTHS_BETWEEN

selectmonths_between(sysdate,to_date('20160906','yyyymmdd'))from dual;

LAST_DAY 获取该月的最后一天

select last_day(sysdate) from dual;

 

ROUND

 

NEXT_DAY

 

TRUNC

 

EXTRACT 提取时间

select extract(day from sysdate) from dual;提取当前日

select extract(mouth from sysdate) fromdual;提取当前日

select extract(year from sysdate) fromdual;提取当前日

 

数字函数

floor函数保留整数位

round 日期取整（有类似的四舍五入）

select round(to_date(‘20100611’,’yyyymmdd’),’year’) fromdual;获取日期的年份第一天；

select round(12.3) from dual; 12

select round(12.3,-1)from dual;10

trunk()直接截断

selectround(to_date(‘20100611’,’yyyymmdd’),’year’) from dual;

转换函数

to_char 将数字转换为字符

select to_char(33,’L99.99’)from dual;即将33转换为本地货币符号

select to_char(sysdate,'yyyy"年"mm"月"dd"日"hh24:mi:ss')from dual;

to_number将字符转换为数字

select to_number(‘123’) from dual;

to_date

混合函数

decode()

NVL，第一为空返回二；否则返回一。

select nvl(‘’,1)from dual; 1

select nvl(null,1) from dual; 1

select nvl(2,1)from dual;2

NVL2，第一个不空则返回二；否则返回三。

select nvl2(11,22,33)from dual;22

NULLIF，两个表达式，相等则返回空；否则第一个

select nullif(2,2)from dual;null

分组函数

avg、 min、max 、count、 groupby 、having

分析函数

见复杂sql语句\分析函数

sql多表连接

相等连接(显示相等部分的笛卡尔积)

相等连接(第一种写法)：

  select table1.column,table2.column

  from table1, table2

  where table1.column1=table2.column2

  可以使用表的别名，为了书写的简化。

select s.sno,s.sname,a.sno,a.zz

from student s,address a

where s.sno=a.sno;

相等连接(第二种写法)：

  select table1.column,table2.column

  from table1 inner join table2

  on  table1.column1=table2.column2

  可以使用表的别名，为了书写的简化。

select s.sno,s.sname,a.sno,a.zz

from student s inner join address a

on s.sno=a.sno;

左外联接（左表的全部与右表相等部分的笛卡尔积）

左外连接(第一种写法)：

select table1.column,table2.column

from  table1 left  outer join table2

on  table1.column1=table2.column2

可以使用表的别名，为了书写的简化。

select s.sno,s.sname,a.sno,a.zz

from student s left outer join address a

on s.sno=a.sno;

 

左外连接(第二种写法)：

select table1.column,table2.column

from  table1, table2

where table1.column1=table2.column2(+)

可以使用表的别名，为了书写的简化。

select s.sno,s.sname,a.sno,a.zz

from student s,address a

where s.sno=a.sno(+);

右外联接（右边表的全部与左表相等部分的笛卡尔积）

右外连接(第一种写法)：

select table1.column,table2.column

from  table1 right  outer join table2

on  table1.column1=table2.column2

可以使用表的别名，为了书写的简化。

select s.sno,s.sname,a.sno,a.zz

from student s right  outerjoin address a

on s.sno=a.sno;

 

左外连接(第二种写法)：

select table1.column,table2.column

from  table1, table2

where  table1.column1(+)=table2.column2;

可以使用表的别名，为了书写的简化。

select s.sno,s.sname,a.sno,a.zz

from student s,address a

where s.sno(+)=a.sno;

全联接（左表的全部与右表全部的笛卡尔积）

右外连接(第一种写法)：

select table1.column,table2.column

from  table1 full outer  join table2

on  table1.column1=table2.column2

可以使用表的别名，为了书写的简化。

select s.sno,s.sname,a.sno,a.zz

from student s full  outerjoin address a

on s.sno=a.sno;

集合操作符

union 并集

合并两个集合只保留一次相同的行

select * from student

union

select * from student where sno=1;

 

union all

合并两个集合保留两次相同的行

select * from student

union all

select * from student where sno=1;

 

 

minus 差集

select * from student

minus

select * from student where sno=1;

注：小集合减去大集合是不行的

 

intersect 交集

select * from student

intersect

select * from student where sno=1;

 

重命名

重命名表：rename table_name1 to table_name2;

重命名列：alter table table_name rename column col_oldname to colnewname ;

复杂sql语句

SQL语句的执行顺序

常见的select、from、where的顺序：

1, from 2, where  3, select

完整的select、from、where、group by、having、order by的顺序：

1, from 2, where  3, group by  4,having 5, select  6, order by

EXISTS的使用

EXISTS用来判断查询所得的结果中，是否有满足条件的纪录存在（存在不存在呢  返回true 或者 flase）

select *　from student

where exists(select * from address where zz='郑州');

若 address表中存在address为郑州的记录则返回student中的所有记录

如address表中不存在address为郑州的记录则返回null

 

子查询的使用

create table student(sno number(6),birthday date, sname varchar2(10));

insert into student values(1, '11-1月-81' , '张三');

insert into student values(2, '10-3月-82' , '李四');

insert into student values(3, '06-1月-83' , '王五');

insert into student values(4, '26-1月-83' , '赵六');

create table address(sno number(6) , zzvarchar2(10));

insert into address values(1,   '郑州');

insert into address values(2,   '开封');

insert into address values(3,   '洛阳');

insert into address values(4,   '郑州');

找出zz是郑州的学生中，sno最大的学生的sname 。

select sname from student where sno=(select max(a.sno) from address a where a.zz='郑州' );

group by 练习

创建student表

 create table student(xh number,xm varchar2(10),nl int);

  insert into student values (1,'A',21); 

 insert into student values (2,'B',22);

 insert into student values (3,'A',23); 

 insert into student values (4,'A',24);

 insert into student values (5,'A',25); 

 insert into student values (6,'C',26);

  insert into student values (7,'B',27);

查找xm有相同的纪录，并显示出来

select * from student where xm in (select xm from student group byxm having count(*)>1)order by xm;

自联结

CREATE TABLE  管理人员  (

  编号  char(10) ,

  姓名  varchar2(10) ,

  管理人员编号  char(10)

);

insert into 管理人员 values('001', '张一', '004');

insert into 管理人员 values('002', '张二&#