//创建班级表

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当做外键被引用，所以Classes表不能被删除

drop table Stu;--Stu引用了Classes的主键作为外键，但是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 = '男' or ssex = '女'),

sage number (3) check (sage > 0 and sage < 200),

sdept varchar (20) default '计算机科学与技术',

semail varchar (30) unique,

foreign key (cno) references Classes (cno),

constraints cno_sname_uni unique (cno,sname)

);

insert into Classes values (68099002,'fjse52');

--插入外键cno cno在classes作为主键必须存在，插入的cno在classes里必须能找到

insert into Stu (sno,Cno,Sname,Ssex,Sage,Semail) values (20099086,68099002,'马波','男','21','702014977@qq.com');

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！=1 把表的结构赋给新建的表emp1

create table emp1 as select * from Scott.Emp where 1 != 1;

select * from emp1;

//部分函数

select upper ('abc') as 大写 from dual;

select lower ('ABC') as 小写 from dual;

select length ('asdf') as Length from dual;

select substr ('hello',-4,3) Sub from dual;

select substr ('hello',2,4) Sub from dual;

select replace ('nihao','ni','wo') Rep from dual;

select replace ('hello','hello','world') Rep from dual;

//字符串连接

select '学号：'||sno||'   '||'姓名：'||sname as 简介 from Student;