我手中有一本达内的oracle数据库基础的学员用书,因为有一个达内的学员来我们公司,我看到了就借来看看。
对于这本书,我的评价还蛮高的,特别简介易懂,是那种你自己看就能很容易上手的书。
结构化查询语句(sql)
数据定义语言(ddl)
数据操作语言(dml)
事物控制语言(tcl)
数据查询语言(dql)
数据控制语言(dcl)
sql(ddl ,dml)
oracle数据类型
number(p,s)
char(n)
varchar2(n)
data: DD-MON-RR(11-APR-71)
创建表
create
例子:
create table employee(
id number(4),
name varchar2(20),
gender char(1),
birth DATE,
salary number(6,2),
job varchar2(30),
deptno number(2)
)
desc
desc table_name
default
drop table employee;
create table employee(
id number(4),
name varchar2(20),
gender char(1) default 'm',
birth DATE,
salary number(6,2),
job varchar2(30),
deptno number(2)
);
not null
drop table employee;
create table employee(
id number(4),
name varchar2(20) not null,
gender char(1) ,
birth DATE,
salary number(6,2),
job varchar2(30),
deptno number(2)
);
修改表
rename old_name to new_name
add
alter table myemp add(hiredate date default sysdate);
drop
alter table myemp drop (hiredate)
modify
alter table myemp
modify(job varchare(40) default 'clerk')
insert into myemp(id,name,job,salary)
values(1001,'rose','programmer' 5500);
insert
insert into myemmp (id,name,job,birth)
values(1003,'donna','MANAGER',
to_date('2009-09-01','YYYY-MM-DD'));
update myemp set salary = 8500
where name='rose';
delete
delete from myemp where job is null;
delete from myemp;(dml)效率低,
可以回退
truncate
table myemp;(ddl)效率高,不可以回退
telnet登陆数据库服务器
案列
create table student(
id number(4),
name varchar(20),
gender char(1),
gee number(7,2)
);
create table student2(
id number(4),
name varchar2(20),
gender char(1),
birth date
);
create table emplyee(
id number(4),
name varchar2(20) not null,
gender char(1) default 'm'
birth date,
salary number(6,2),
comm number(6,2),
job varchar2(30),
manager number(4),
deptno number(2)
);
rename employee to myemp;
desc myemp;
alter table myemp and (hiredatte date default sysdate);
desc myemp;
alter table myemp
modify(job varchar2(40) default 'clerk');
desc myemp;
alter table myemp drop (hiredate);
desc myemp;
insert into myemp (id,name,job,salary)
values(1001,'rose','programmer',5500);
使用默认日期格式插入记录
insert into myemp (id,name,job,birth)
values(1002,'martha','analyst','01-SEP-89');
如果为中文环境,那么日期的格式应该为'01-9月-89'。
insert into myemp (id,name, job, birth)
values(1003, 'dono', 'MANAGER', TO_DATE('
197809-01
','YYYY-MM-DD'));
update myemp set salary = 8500 where name = 'rose';
update myemp set salary = 6500, job ='analyst'
where id = 1003
select * from myemp
delete from myemp where job is null;
delete from myemp where name = 'ROSE';
select * from myemp where name='ROSE';