Oracle笔记

数据库连接：conn[ect]

conn username/password@servicename

disc[onnnect]

passw[ord]

show user

exit

create user 用户名 identiied by 密码

字符类型：char(最大2000), varchar2(), clob(字符型大对象), number(有效位数，小数位数), date, timestamp, blob存放图片等二进制数据

默认日期格式： 'DD-MON-YY'， 改变日期格式： alter session set nls_date_format='yyyy-mm-dd';

显示表结构： desc table

create table student(

id number(4),

name varchar2(20),

sex char(2),

birthday date,

salary number(7, 2)

);

 

添加字段：alter table student add (ClassID number(2));

修改字段： alter table student modify (ClassID number(4));

删除字段： alter table student drop columnsalary;

修改表名： rename student to stu;

删除字段： drop table student;

添加数据： insert into student valuses('A1001', 'ronaldo', 01-5月-05', 1300,12);

                   insert into student(classid, name) valuses('A1001', 'ronaldo');

修改字段： update student set sex='male' where classid = 'A1001';

                   update student set sex='male', classid=3, salary=salary/2 where classid = 'A1001';

删除数据： delete fromstudent; 只删除记录

                   delete from student where classid = 'A1001'

                   savepoint aa; --> delete from student; --> rollback to aa; -> select * from student --恢复数据

                   drop table student; --删除表结构和数据

                   truncate table student; --delete all records,表结构还在, 不写日志，无法恢复