orcal主键外键约束条件

//创建班级表

create table Classes
(
cno number (6) primary key,
cname varchar (20)
);
alter table Classes modify cno number (8);
alter table Stu modify sno number (8);
alter table Stu modify cno number (8);
drop table Classes;--Classes的主键被Stu当做外键被引用&＃xff0c;所以Classes表不能被删除
drop table Stu;--Stu引用了Classes的主键作为外键&＃xff0c;但是Stu表可以被删除
//创建学生表

create table Stu
(
sno number (6) constraints pk_sno primary key,
cno number (6),
sname varchar (20) not null,
ssex char (2) check (ssex &＃61; &＃39;男&＃39; or ssex &＃61; &＃39;女&＃39;),
sage number (3) check (sage > 0 and sage <200),
sdept varchar (20) default &＃39;计算机科学与技术&＃39;,
semail varchar (30) unique,
foreign key (cno) references Classes (cno),
constraints cno_sname_uni unique (cno,sname)
);
insert into Classes values (68099002,&＃39;fjse52&＃39;);
--插入外键cno cno在classes作为主键必须存在&＃xff0c;插入的cno在classes里必须能找到
insert into Stu (sno,Cno,Sname,Ssex,Sage,Semail) values (20099086,68099002,&＃39;马波&＃39;,&＃39;男&＃39;,&＃39;21&＃39;,&＃39;702014977&＃64;qq.com&＃39;);
select * from Stu;
grant select any table to mabo;
select * from Scott.Emp;
--把表的结构及数据赋给新建的表emp1
create table emp as select * from Scott.Emp;
select * from emp;
--利用条件 1&＃xff01;&＃61;1 把表的结构赋给新建的表emp1
create table emp1 as select * from Scott.Emp where 1 !&＃61; 1;
select * from emp1;

//部分函数
select upper (&＃39;abc&＃39;) as 大写 from dual;
select lower (&＃39;ABC&＃39;) as 小写 from dual;
select length (&＃39;asdf&＃39;) as Length from dual;
select substr (&＃39;hello&＃39;,-4,3) Sub from dual;
select substr (&＃39;hello&＃39;,2,4) Sub from dual;
select replace (&＃39;nihao&＃39;,&＃39;ni&＃39;,&＃39;wo&＃39;) Rep from dual;
select replace (&＃39;hello&＃39;,&＃39;hello&＃39;,&＃39;world&＃39;) Rep from dual;

//字符串连接
select &＃39;学号&＃xff1a;&＃39;||sno||&＃39;   &＃39;||&＃39;姓名&＃xff1a;&＃39;||sname as 简介 from Student;