oracle学习17-命令学习5

21建立班级表

CREATE TABLE class(
    classId NUMBER(2),
    cName VARCHAR2(40)

);

insert into class values(001,'计算机科学与技术');

insert into class values(002,'计算机科学与技术');

insert into class values(003,'计算机科学与技术');

insert into class values(004,'计算机科学与技术');

insert into class values(005,'财务管理');

insert into class values(006,'财务管理');

insert into class values(007,'会计专业');

insert into class values(008,'会计专业');

22

alter table class add(name varchar(20));

UPDATE class SET name = '歌谣' WHERE classid = '1';

UPDATE class SET name = '歌谣' WHERE classid = '2';

UPDATE class SET name = '歌谣' WHERE classid = '3';

UPDATE class SET name = '歌谣' WHERE classid = '4';

UPDATE class SET name = '歌谣' WHERE classid = '5';

UPDATE class SET name = '歌谣' WHERE classid = '6';

UPDATE class SET name = '歌谣' WHERE classid = '7';

UPDATE class SET name = '歌谣' WHERE classid = '8';

 

23 select name from student union all select name from class;

--Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

--Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

24在两个表中查找共同行

create view E

as

select xh,sex,name from student

where name='歌谣'

select * from E

 

25从一个表中查找另一个表中没有的值

select name from student

minus

select name from class--查找class中没有的行