FenFeiblue

创建数据库

create database mydb

连接到数据库

connect to mydb

表的操作

创建表 tbl_stuinfo和tbl_stus

create table tbl_stuinfo(
stuno char(5) not null,
stuname varchar(20),
studept varchar(20)

create table tbl_stus(
stuno char(5) not null,
stuscore int not null
)

删除表

create table tbl_stuinfo

增加字段

alter table tbl_stuinfo add stuaddr varchar(40)

alter table tbl_stuinfo add stutel varchar(40)

修改字段属性

DB2只能对字段的长度进行修正，不可以改类型，改名

alter table tbl_stuinfo alter column stutel set data type varchar(11)

删除字段

alter table tbl_stuinfo drop column stutel

主键约束

alter table tbl_stuinfo add primary key(stuno)

alter table tbl_stus add primary key(stuno)

增加数据

insert into tbl_stuinfo (stuno,stuname,studept ) values(’1′,’黑子’,'数学’)

insert into tbl_stuinfo (stuno,stuname,studept ) values(’2′,’大妈’,'八镁’)

insert into tbl_stuinfo (stuno,stuname,studept ) values(’3′,’炮姐’,'v5′)

insert into tbl_stuinfo (stuno,stuname,studept ) values(’4′,’路飞’,'新世界’)
insert into tbl_stuinfo (stuno,stuname,studept ) values(’5′,’山治’,'新世界’)

insert into tbl_stuinfo (stuno,stuname,studept ) values(’6′,’yijigo’,'虚’)
insert into tbl_stuinfo (stuno,stuname,studept ) values(’7′,’aizen’,'虚’)

insert into tbl_stus (stuno,stuscore)  values(’1′,20)

修改数据

update tbl_stuinfo set studept=’把妹’ where stuno=’2′

删除数据

delete from tbl_stuinfo where stuno=’3′

查询所有列

select * from tbl_stuinfo

查询一列

select  studept from tbl_stuinfo

查询多列

select  stuno,studept from tbl_stuinfo

查询一列不显示重复

select  distinct studept from tbl_stuinfo

限制行

select stuname ,studept from tbl_stuinfo where studept=’新世界’

select stuname ,studept from tbl_stuinfo where stutel is null

select stuname ,studept from tbl_stuinfo where studept like ‘新%’

select * from tbl_stus where stuscore>60 and stuscore <80

select * from tbl_stus where stuscore in (70,90)

select * from tbl_stus where stuscore between 70 and 90

select * from tbl_stus order by stuscore

select * from tbl_stus order by stuscore desc

select * from tbl_stus order by stuscore desc,stuno desc

多表查询

select a.stuno,a.stuname,b.stuname from tbl_studentinfo a,tbl_studentinfo b
where a.upstuno=b.stuno

select a.stuno,a.stuname,b.stuname from tbl_studentinfo a inner join tbl_studentinfo b
on a.upstuno=b.stuno

select a.stuno,a.stuname,b.score from tbl_studentinfo a,tbl_scoreinfo b
where a.stuno=b.stuno and b.classno=’002′

select a.stuno,a.stuname,b.score from tbl_studentinfo a left outer join tbl_scoreinfo b
on a.stuno=b.stuno and b.classno=’002′

select a.stuno,a.stuname,b.score,c.classname from  tbl_scoreinfo b right outer join

tbl_studentinfo a
on a.stuno=b.stuno and b.classno=’002′
left outer join tbl_classinfo c
on b.classno=c.classno

select a.stuname ,b.score ,c.classname
from tbl_studentinfo a join tbl_scoreinfo b
on a.stuno=b.stuno
join tbl_classinfo c on
b.classno=c.classno

select a.stuno,a.stuname,nvl(b.score,0),c.classname from  tbl_scoreinfo b right outer

join tbl_studentinfo a
on a.stuno=b.stuno and b.classno=’002′
left outer join tbl_classinfo c
on b.classno=c.classno

select a.stuname,b.classname,c.score from tbl_studentinfo a,tbl_classinfo b,tbl_scoreinfo c
where a.stuno=c.stuno and b.classno=c.classno
and b.classname in(‘英语’,'日语’)

select a.stuname,b.classname,c.score from tbl_studentinfo a,tbl_classinfo b,tbl_scoreinfo c
where a.stuno=c.stuno and b.classno=c.classno
and (b.classname =’英语’ or b.classname =’日语’)

select a.stuname,b.classname,c.score from tbl_studentinfo a,tbl_classinfo b,tbl_scoreinfo c
where a.stuno=c.stuno and b.classno=c.classno
and b.classname =’日语’
union all
select a.stuname,b.classname,c.score from tbl_studentinfo a,tbl_classinfo b,tbl_scoreinfo c
where a.stuno=c.stuno and b.classno=c.classno
and b.classname =’英语’

select stuname,classname,score from(
select a.stuname,b.classname,c.score from tbl_studentinfo a,tbl_classinfo b,tbl_scoreinfo c
where a.stuno=c.stuno and b.classno=c.classno
and b.classno =’002′
union all
select a.stuname,b.classname,c.score from tbl_studentinfo a,tbl_classinfo b,tbl_scoreinfo c
where a.stuno=c.stuno and b.classno=c.classno
and b.classno =’003′ )
order by score desc ,classname

常用函数
select avg(score) from tbl_scoreinfo

select max(score) from tbl_scoreinfo

select max(score) from tbl_scoreinfo

select count(stuno) from tbl_studentinfo

select sum(score) from tbl_scoreinfo

select avg(score),classno from tbl_scoreinfo
group by classno

select avg(a.score),b.classname  from tbl_scoreinfo a,tbl_classinfo b
where a.classno=b.classno
group by classname

select b.classname
from tbl_scoreinfo a,tbl_classinfo b
where a.classno=b.classno
group by classname
having avg(a.score) >80

检索子查询
查询所有学生的姓名 课程名 成绩

普通
select a.stuname,c.score,b.classname
from tbl_studentinfo a, tbl_scoreinfo c,tbl_classinfo b
where a.stuno=c.stuno and b.classno=c.classno  

检索子查询方式
select (select stuname from tbl_studentinfo where stuno=b.stuno),
(select classname from tbl_classinfo where b.classno=classno),
b.score

限制行数 只返回第一行
select (select stuname from tbl_studentinfo where stuno=b.stuno),
(select classname from tbl_classinfo where b.classno=classno),
b.score
from tbl_scoreinfo b

虚拟表子查询

查询每科最高分的学生

不输出科目名字
select b.classno,a.stuname,c.maxscore
from tbl_studentinfo a ,tbl_scoreinfo b ,(select max(score) as maxscore,classno from

tbl_scoreinfo group by classno) c
where c.maxscore=b.score and a.stuno=b.stuno and b.classno=c.classno

输出科目名字 并以分数排序
select b.classno,a.stuname,c.maxscore,d.classname
from tbl_studentinfo a ,tbl_scoreinfo b ,(select max(score) as maxscore,classno from

tbl_scoreinfo group by classno) c,tbl_classinfo d
where c.maxscore=b.score and a.stuno=b.stuno and b.classno=c.classno and

c.classno=d.classno
order by c.maxscore desc

条件子查询

in
select c.score
from tbl_scoreinfo c
where c.stuno in (select stuno from tbl_studentinfo where stuname=’张三’)
and c.classno in (select classno from tbl_classinfo where classname=’计算机’)

建立视图

create view myscore as
select a.stuname,b.classname,c.score
from tbl_studentinfo a,tbl_classinfo b,tbl_scoreinfo c
where a.stuno=c.stuno and b.classno=c.classno

select * from myscore

序列

create table tbl_studentinfo2552(
    orderid  int  generated always as identity(start with 1  increment by 1 ),
    stuno char(10) not null
);