计算机二级 20101113

应先建一个数据库，在程序里执行一下操作

create table 系(系编号 c(2) primary key ,系名 c(20))

创建学生表

create table 学生(学号 c(11) primary key ,;

系编号 c(2),姓名 c(8),性别 c(2) check (性别="男" or 性别="女") error "性别只能是男女." default "男",;

出生日期 d,民族 c(10) default "汉族",; 奖学金 n(7,2) check (奖学金>=200 and 奖学金<=5000) error "范围是200到5000之间" default 1000,;

foreign key 系编号 tag 系编号 references 系)

创建课程表

create table 课程(课程编号 c(3) primary key ,课程名 c(20))

创建成绩表

create table 成绩(学号 c(11),; 课程编号 c(3),;

分数 n(3,0),;

primary key 学号+课程编号 tag tsh,; foreign key 学号 tag 学号 references 学生,;

foreign key 课程编号 tag 课程编号 references 课程)

添加系表

insert into 系 values ('01',"经管系") insert into 系 values ('02',"机电系") insert into 系 values ('03',"中文系") insert into 系 values ('04',"数学系") insert into 系 values ('05',"计科系") insert into 系 values ('06',"体育系") 添加学生表

insert into 学生 values ('20080235201','01',"张敏","女",{^1989-8-1},"汉",4500)

insert into 学生 values ('20080235202','01',"李敏","女",{^1989-8-1},"汉",1500)

insert into 学生 values ('20080235203','02',"张佳","女

",{^1989-7-1},"汉",500)

insert into 学生

('20080235204','01',"张敏values ","女",{^1989-8-1},"汉",1500)

insert into 学生 ('20080235205','01',"张龙",{^1989-3-1},"汉",2500)

insert into 学生 ('20070235206','03',"张会",{^1988-8-1},"汉",500)

insert into 学生 ('20080235207','07',"张凌宇",{^1989-4-1},"汉",1500)

添加成绩表

insert into 成绩 ('20080235201','c0',76)

insert into 成绩 ('20080235201','c1',56)

insert into 成绩 ('20080235201','c2',78)

insert into 成绩 ('20080235201','c3',56) values 男values 女values ","男values values values values ","","

insert into 成

('20080235201','c4',78)

insert into 成

('20080235201','c5',56)

insert into 成

('20080235201','c6',78)

insert into 成

('20080235202','c0',56)

insert into 成

('20080235202','c1',78)

insert into 成

('20080235202','c2',56)

insert into 成

('20080235202','c3',78)

insert into 成

('20080235202','c4',56)

insert into 成

('20080235202','c5',78)

insert into 成

('20080235202','c6',56)

insert into 成绩 绩 绩 绩 绩 绩 绩 绩 绩 绩 绩 values values values values values values values values values values values

('20080235203','c0',76)

insert into 成

('20080235203','c1',56)

insert into 成

('20080235203','c2',78)

insert into 成

('20080235203','c3',56)

insert into 成

('20080235203','c4',78)

insert into 成

('20080235203','c5',56)

insert into 成

('20080235203','c6',78)

insert into 成

('20080235204','c0',76)

insert into 成

('20080235204','c1',56)

insert into 成

('20080235204','c2',78)

insert into 成

('20080235204','c3',50) 绩 绩 绩 绩 绩 绩 绩 绩 绩 绩 values values values values values values values values values values

insert into 成

('20080235204','c4',78)

insert into 成

('20080235204','c5',65)

insert into 成

('20080235204','c6',78)

insert into 成

('20080235205','c0',76)

insert into 成

('20080235205','c1',56)

insert into 成

('20080235205','c2',78)

insert into 成

('20080235205','c3',56)

insert into 成

('20080235205','c4',78)

insert into 成

('20080235205','c5',56)

insert into 成

('20080235205','c6',58)

insert into 成绩 绩 绩 绩 绩 绩 绩 绩 绩 绩 绩 values values values values values values values values values values values

('20080235206','c0',76)

insert into 成

('20080235206','c1',56)

insert into 成

('20080235206','c2',70)

insert into 成

('20080235206','c3',56)

insert into 成

('20080235206','c4',78)

insert into 成

('20080235206','c5',96)

insert into 成

('20080235206','c6',78)

insert into 成

('20080235207','c0',90)

insert into 成

('20080235207','c1',59)

insert into 成

('20080235207','c2',78) 绩 绩 绩 绩 绩 绩 绩 绩 绩 values values values values values values values values values

insert into 成绩 values ('20080235207','c3',52)

insert into 成绩 values ('20080235207','c4',78)

insert into 成绩 values ('20080235207','c5',93)

insert into 成绩 values ('20080235207','c6',89)

添加课程表

insert into 课程 values ('c1',"预备级英语")

insert into 课程 values ('c2',"化学") insert into 课程 values ('c0',"管理学") insert into 课程 values ('c4',"物理") insert into 课程 values ('c5',"语文") insert into 课程 values ('c6',"会计")

select 姓名,学生.学号,学生.系编号,分数,成绩.课程编号;

from 学生,系,成绩,课程;

where 学生.学号=成绩.学号 and 系.系编号=学生.系编号 and 课程.课程编号=

成绩.课程编号

set century on

?{^1990-8-1}

set century off

?{^1988-5-5}

set mark to "."

?{^1988-5-5}

超连接查询

select 学生.*,系.系名,课程.课程编号,课程名,分数;

from 系 inner Join 学生 inner Join 成绩 inner Join 课程;

on 课程.课程编号=成绩.课程编号; on 学生.学号=成绩.学号;

on 系.系编号=学生.系编号;

where 分数<60

select top 3 分数;

from 成绩;

where 分数>67;

order by 分数（一个字段名而已） 常用函数

?sign (-9) -1

?sign (9) 1

?sign (0) 0

?sqrt (100) 10.00

?pi() 3.14

?round (pi (),9) 3.141592654 ?int (6.89) 6

?ceiling (-2.9) -2

?ceiling (2.9) 3

?floor (2.9) 2

?floor (-2.9) -3

a=56.574839

?round (a,3) 56.575

?round (a,-2) 100

?round(a,-1) 60

?mod(10,4) 2

?10%3 1

?10%-3 -2

?-10%3 2

?-10%-3 -1

?max(9,4,1) 9

?max('汽车','飞机','轮船') 汽车 ?min ('汽车','飞机','轮船') 飞机 a="河南省新乡市长垣县芦岗乡" ?len (a) 24

?len(a)*2 48

?len(a)**2 576.00

?({^1990-8-1}-{^1990-8-16})*2 -30 a="what do you mean? i don't understand your words "

?upper (a) WHAT DO YOU MEAN ?I DON’T UNDERSTAND YOUR WORDS b="OH,MY GOD! "

?LOWER(b) oh,my god！

a=" "

?len(a) 1

b=" "

?len (b) 8

?"my"+space(8)+"name" my

name

a="henansheng"

b=" xinxiangshi"

c="guangongliu"

?len (trim(b)) 16

?len(ltrim(b)) 11

?a+ltrim(b)+c

henanshengxinxiangshiguangongliu a="yuanlairuci"

?left(a,2) yu

?right(a,5) iruci

?substr(a,4,7) nlairuc

?substr(a,5,7) lairuci

?occurs("a","aweibckzvbasjaajhjvxa") 5 ?at("a","aweibckzvbasjaajhjvxa",3) 14 ?at("a","aweibckzvbasjaajhjvxa",5) 21 a="tongxueliulingyu"

?stuff(a,8,9,"zhaosan") tongxuezhaosan ?stuff(a,8,9,"") tongxue

set mark to "-"

?date() 12-12-10

?"xianzaishijian:"+time()

xianzaishijian:09:24:21

?"xianzairiqi:"+dtoc(date())

xianzairiqi:12-12-10

?year(date()) 2010

?year(date())*2 4020

?year(datetime())*2 4020

select student.学号,姓名,course.课程名,sc.成绩 from student,course,sc;

where student.学号=sc.学号 and course.课程号=sc.课程号;

order by 课程名,成绩 desc into table sclist

本文来源：https://www.bwwdw.com/article/rdd1.html