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', '张二&#