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

Read more…