SQL基础

--给用户解锁
alter user scott account unlock;
--锁定用户
alter user scott account lock;

--查询表空间
select * from v$tablespace;

--创建表空间
create tablespace t04 datafile 'C:\JAVAprogram\Oracle\oradata\orcl\t0401.dbf' size 50m ;

select autoextensible ,tablespace_name from dba_data_files;

--删除表空间
drop tablespace t04 including contents and datafiles;

--创建用户
create user cqs identified by accp default tablespace users;

--授权限 connect 连接数据库的权限 resourse 操作表空间的数据对象 角色权限
grant connect ,resource to cqs;

--修改用户密码
alter user cqs identified by tiger;

--给用户解锁
alter user scott account unlock;
--锁定用户
alter user scott account lock;

--删除用户
drop user cqs cascade;

--角色权限
--创建一个角色
create role tea ;
grant connect ,resource to tea;
grant tea to cqs;

 

--建表
create table tb_student(
sno varchar2(10),
sname varchar2(10),
tel varchar2(20),
score number
)

insert into tb_teacher values('t01','老马','男','12343454');     添加数据
insert into tb_teacher(tno,tname,tel) values('t02','老马','12343454');
commit;

select * from tb_teacher ; 查询

drop table tb_teacher; 删除表格

insert into tb_student(sno,sname,tel) values('s02','小河','12311233234');--指定添加某些字段

commit;

--添加主键约束
alter table tb_student add constraint pri_student primary key(sno);

--唯一约束
alter table tb_student add constraint uni_tel unique(tel);

--检查约束
alter table tb_student add constraint check_age check(age between 0 and 100);
insert into tb_student values('s01','偶发清','12345678902',45,99);

--查询
select * from tb_student;

--删除数据
delete from tb_student where sno='s01';

--修改表结构
--添加一个字段
alter table tb_student add(sex varchar2(3));
alter table tb_student add(age number);

--删除一个字段
alter table tb_student drop column sex;--第一种方式
alter table tb_student drop(sex);--第二种方式,删除多个列用逗号隔开

--修改
alter table tb_student modify(sname varchar2(20));

--修改字段名
alter table tb_student rename column sname to stuname;

--删除表以及数据
drop table tb_student;

--删除数据，保留表结构
truncate table tb_student;

create table tb_classInfo(
id number primary key,
cname varchar2(10)
)

create table tb_stuInfo(
id number primary key,
sname varchar2(10),
tel varchar2(10),
sex varchar2(3),
class_id number references tb_classInfo(id)

)

insert into tb_stuInfo values(1,'老李','12312313','男',2);
insert into tb_stuInfo values(2,'老王','12313333','女',3);
commit;
drop table tb_stuInfo;

select stu.*,cl.* from tb_stuInfo stu ,tb_classInfo cl where stu.class_id=cl.id;

--连接查询 内链接
select stu.*,cl.cname from tb_stuInfo stu inner join tb_classInfo cl on stu.class_id=cl.id;

select * from tb_stuInfo;

--添加数据
insert into tb_classInfo values (1,'学士后18班');
insert into tb_classInfo values(2,'学士后17班');
commit;

--查询所有数据
select * from tb_classInfo;

--查询某一个或者某些字段
select id,cname from tb_classInfo;

--删除数据
delete from tb_classInfo where id=1;
delete from tb_classInfo where cname='学士后17班';

--修改数据
update tb_classInfo set cname='学士后20班' where id=1;

update tb_classInfo set id=3 where id=1;

 

转载于:https://www.cnblogs.com/yin-dt/p/6690720.html