oracle数据库

2.1 简介

sys用户（超级账户）：sqlplus / as sysdba，默认密码是change_on_install

system用户（没有sys权限大）：默认密码是manager

2.2 启动与关闭

启动：

su - oracle   #切换到oracle用户

lsnrctl start   #启动监听

sqlplus / as sysdba   #以sys身份登录

startup   #启动实例

exit   #退出

关闭：

su - oracle

sqlplus / as sysdba

shutdown immediate   #关闭实例exit   #退出

lsnrctl stop   #关闭监听

2.3 oracle11g数据库备份、恢复

• 远程备份：exp username/password@ip/DBNAME file=文件路径
• 备份：exp username/password@DBNAME owner=(system,sys) tables=(table1,table2) file=文件路径 log=/exp.log
• 恢复：imp username/password@DBNAME  file=文件路径 log=/imp.log ignore=y

2.4 远程连接数据库

远程连接oracle11g数据库：sqlplus username/username@//ip:1521/oracle11g

2.5 增删改查

2.5.1 表结构的修改

增加新列：alter table student add (sex varchar2(9));

修改列名：alter table Teacher rename column Tname to Tmingzi;

修改数据类型、尺寸和默认值：alter table student modify (sex varchar2(10));

删除列：alter table student drop column sex;

删除表：drop table student;

删除表中所有数据：truncate table student;

修改表名：rename student to students;

2.5.2 表内容修改

添加数据：insert into student (studentid,studentname) values (12,'zhangsan');

查询：

select column from table 

where condition 

group by group_by_expression 

having group_condition 

order by column desc;

select S#,avg(score) from SC group by S# having avg(score)>90;

select count(Tname) from Teacher where Tname like '李%';

修改：update Teachere set Tname=’阿汤’ where T#=’007’;

删除：delete score from SC where S#=’002’ and C#=’001’;