创建数据库
creat e dat abase php
on
(
name=php,
fil ename='d:\php.m df',
size=10mb,
ma xsize=20mb,
fi legro wth=10%
)
创建数据库和日志文件
crea te da tabas e you
on p rimar y
(
name=you,
file name='c:\y ou.md f', size=10mb,
max size=20mb,
fil egrow th=10%
)
l og on
(
n ame=y ou1,
file name='c:\y ou1.l df',
size=2mb,
max size=5mb,
file growt h=10%
)
添加日志文件
u y ou
al ter d ataba yo u
add logfile
(
na me=to u,
f ilena me='d:\tou.ldf',
si ze=2m b,
m axsiz e=3mb,
fi legro wth=10%
)
创建文件组
uphp
a lterdatab a p hp
ad d fil egrou p php
添加文件到文件组
alte r dat abase php
add f ile
(name='hot',
f ilena me='d:\hot.ndf',
si ze=1m b,
m axsiz e=1mb,
fi legro wth=10%)
t o fil egrou p mdd
创建架构
cre ate s chema test auth oriza tiondbo
creat e typ e hha r
fro m var char(4)
CR EATEPARTI TIONFUNCT ION p f_Ord erDat e (da tetim e) AS RANG E RIG HT
FO R VAL UES ('01/01/2003', '01/01/2004', '01/01/2005')
CR EATEPARTI TIONSCHEM E ps_Order Date
AS PA RTITI ON pf_Orde rDate
TO(fg1, fg2, fg3, fg4, fg5)
cre ate t ablest_ta ble
(
sn o int notnullprima ry ke y ide ntity,
sn ame c har(10) no t nul l,
c lasschar(10),
fs i nt
)--创建表
inse rt in to st_tabl e val ues('lucy','com1',400)
in rtintost_ta ble v alues('tom','co m2',430)
i nrt into st_t ablevalue s('mi ke','com1',420)
inse rt in to st_tabl e val ues('andy','com3',410)
in rtintost_ta ble v alues('pet er','com2',450)
inse rt in to st_tabl e val ues('wang','com3',500)
in rtintost_ta ble v alues('li','com1',600)
in rtintost_ta ble v alues('zha ng','com2',700)
inse rt in to st_tabl e val ues('zhao','com3',100)
in rtintost_ta ble v alues('qia n','c om3',200)
inr t int o st_table valu es('s un','com2',120)
inse rt in to st_tabl e val ues('month','co m1',240)
lec t * f rom s t_tab le--查询表的数据
le ct *intobktab le fr om st_tabl e--拷贝表的数据和结构
s elect * fr om st_tabl e whe re fs>300
le ct di stinc t cla ss fr om st_tabl e--显示不重复的记录
se lectclass from st_t able
le ct to p 10* fro m st_table --显示前n条记录
l ect * from st_t ablewhere snobetwe en 3and 5--显示记录的sno在1和20之间的记录
l ect * from st_t ablewhere sno in(1,2,20)--显示记录的sn o在1,2的记录
lec t * f rom s t_tab le wh ere n ot sn o in(1,2,20)--显示记录的s no不在1,2的记录
alt er da tabas e fff
addfileg roupk
se lect@@ver sion
lec t @@l angua ge
pr int @@vers ion
p rint@@lan guage
l ect * from st_t ableorder by s no as c--按照sno排降序，升序用asc
s elect * fr om st_tabl e ord er by snodesc--按照sn o排降序，降序用de sc
s elect snoas 学号,snam e as姓名 fr om st_tabl e--改变列名
s elect top5 * f rom s t_tab le or der b y sno desc--先降序，在取前5条
se lectcount(*) f rom s t_tab le--c ount()函数可以完成统计功能
se lectmax(f s) fr om st_tabl e--ma x()函数可以得到最大的值
lec t avg(fs)fromst_ta ble--avg()函数可以得到平均值
le ct ge tdate() --获得当前的时间
se lectmonth('2010/05/01')--获得当前的月份
lec t cla ss fr om st_tabl e gro up by clas s--按照class进行分组，注意：分组列要在前方出现
se lectclass from st_t ablegroup by c lasshavin g cla ss<>'com1'--筛选条件的表达
式,havi ng必须和group by联合使用
s elect fs,s namefromst_ta ble compu te su m(fs)--按分数分类汇总
le ct *fromst_ta ble o rderby cl ass c omput e sum(fs)by cl ass--按班级分类汇总并求和,comp ute b y必须与o rderby连用
实例
creat e tab le xs
(
s no in t not null prim ary k ey id entit y,
s namechar(10) n ot nu ll,
bsidint,
zyid int,
xbi d int
)
--建立班级表
crea te ta ble b s
(
bsidint n ot nu ll pr imary keyident ity,
bsna me ch ar(10)
)
--建立专业表
cre ate t ablezy
(
zyid intnot n ull p rimar y key iden tity,
zyn ame c har(10)
)
--建立系部表
cr eatetable xb
(
xbi d int notnullprima ry ke y ide ntity,
xb namechar(10)
)
l ect * from xs,b s
--添加数据到系部表
i nrt into xb v alues('com puter')
in rtintoxb va lues('engl ish')
--添加数据到专业表
ins ert i nto z y val ues('elect ric')
inse rt in to zy valu es('m oving')
in rtintozy va lues('writ ing')
inse rt in to zy valu es('r eadin g')
--添加数据到班级表
inr t int o bsvalue s('co m1')
inr t int o bsvalue s('co m2')
--添加数据到学生表中
in rtintoxs va lues('tom',1,1,1)
in rtintoxs va lues('lucy',1,1,2)
i nrt into xs v alues('mik e',1,2,1)
inr t int o xsvalue s('an dy',1,2,2)
inse rt in to xs valu es('z hao',2,1,1)
ins ert i nto x s val ues('wang',2,1,2)
in rtintoxs va lues('li',2,2,1)
ins ert i nto x s val ues('zhang',2,2,2)
i nrt into xs v alues('qia n',3,1,1)
inr t int o xsvalue s('su n',3,1,2)
le ct *fromxs
--在wher e字句中使用逻辑运算符and
lec t xs.sno,x s.bsi d,bs.bsid,bs.bs namefromxs,bs wher e xs.bsid=bs.bs id an d bs.bsid=1 --在where字句中使用逻辑运算符not
s elect * fr om xs wher e not bsid in(1,2)
--在whe re字句中使用逻辑运算符or
lec t * f rom x s whe re bs id=1or bs id=2
--在wh ere字句中使用in
le ct *fromxs wh ere b sid i n(1,2)
--在where字句中使用betwe en...and...
l ect * from xs w herebsidbetwe en 1and 2
--在w here字句中使用b etwea nd...和not
lec t * f rom x s whe re no t bsi d bet ween1 and 2
--使用ord er by字句进行排序升序
s elect * fr om xs orde r bysno
--使用or der b y字句进行排序降序序
le ct *fromxs or der b y sno desc
--使用disti nct参数，消除重复行
l ect d istin ct bs id fr om xs
--改变列名
se lectsno a s 学号,sname as 学生名称f rom x s
--使用topn
l ect t op 5* fro m xs
--使用s um和gr oup b y
l ect b sid,s um(sn o) as学号和fromxs gr oup b y bsi d
