Oracle sql例子

create database oracle1
create table class(id int primary key,name varchar(255))
select * from class
insert into class values(1,'一班')
insert into class values(2,'二班')
insert into class values(3,'三班')
--创建学生表
create table student(id int primary key,name varchar(255),cid int ,constraint FK_student_class foreign key(cid) references class(id))
insert into student values(1,'Tom',1)
--联合查询
select * from student student left join class class on student.cid=class.id
select * from class class right join student student on class.id=student.cid
--alter
create table school(id int,name varchar(255))
insert into school values(1,'耶鲁大学')


alter table school add primary key(id)
alter table class add sid int
alter table class add constraint FK_class_school foreign key(sid) references school(id)


update class set sid=1 where id=1


---联合查询  as后面的内容是作为别名存在的，是变量不是字符串
select school.name as 大学,class.name as 班级 ,student.name as 姓名 from student student left join class class on student.cid=class.id left join school school on class.sid=school.id
--union all
select name from school
union all
select name from class
union all
select name from student


/** 查询syscolumns**/
select * from dba_tables
select * from dba_tables where table_name=upper('class')
select * from user_tables where table_name=upper('class')


--数据库本身就是有多权限的系统，有dba的tables有user的tables 个人感觉Oracle的权限分配比sql Server2005要清晰明确的多