数据库TSQL基础.docx
- 文档编号:14441932
- 上传时间:2023-06-23
- 格式:DOCX
- 页数:21
- 大小:20.86KB
数据库TSQL基础.docx
《数据库TSQL基础.docx》由会员分享,可在线阅读,更多相关《数据库TSQL基础.docx(21页珍藏版)》请在冰点文库上搜索。
数据库TSQL基础
--1、创建“订货管理数据库”
createdatabase订货管理数据库
go
use订货管理数据库
go
/*
2、在该数据库下创建四个表:
1)创建仓库表,由以下属性组成:
仓库号(CHAR型,长度为5,主键);
城市(CHAR型,长度为10,非空唯一);
面积(INT型,创建检查约束:
面积大于零)
*/
createtable仓库
(
仓库号char(5)constraintPK_仓库_仓库号primarykey,
城市char(10)constraintNN_仓库_城市notnullconstraintUQ_仓库_城市unique,
面积intconstraintCK_仓库_面积check(面积>0)
)
/*
2)创建职工表,由以下属性组成:
仓库号(CHAR型,长度为5,创建外键约束:
与仓库表的外键约束);
职工号(CHAR型,长度为5,主键);
工资 (INT型,创建检查约束:
工资大于等于1000,小于等于5000;默认值为
1200);
*/
createtable职工
(
仓库号char(5)constraintFK_职工_仓库号references仓库(仓库号),
职工号char(5)constraintPK_职工_职工号primarykey,
工资intconstraintCK_职工_工资check(工资>=1000and工资<=5000)constraintDF_职工_工资default1200
)
/*
3)创建供应商表,由以下属性组成:
供应商号(CHAR型,长度为5,主键);
供应商名(CHAR型,长度为20,非空唯一);,
地址(CHAR型,长度为20)
*/
createtable供应商
(
供应商号char(5)constraintPK_供应商_供应商号primarykey,
供应商名char(20)constraintNN_供应商_供应商号notnullconstraintUQ_供应商_供应商号unique,
地址char(20)
)
/*
4)创建订购单表,由以下属性组成:
职工号(CHAR型,长度为5,非空,创建外键约束:
与职工表的外键约束);
供应商号(CHAR型,长度为5,创建外键约束:
与供应商表的外键约束);
订购单号(CHAR型,长度为5,主键);
订购日期(DATETIME型)
*/
createtable订购单
(
职工号char(5)constraintNN_订购单_职工号notnullconstraintFK_订购单_职工号references职工(职工号),
供应商号char(5)constraintFK_订购单_供应商号references供应商(供应商号),
订购单号char(5)constraintPK_订购单_订购单号primarykey,
订购日期datetime
)
altertable订购单add订购量int
altertable订购单dropcolumn订购量
--3、修改表
--往订购单关系中增加一个新属性“完成日期”;
altertable订购单表
add完成日期datetime
--将订购单关系的完成日期属性的数据类型修改为SMALLDATETIME;
altertable订购单表
altercolumn完成日期smalldatetime
/*删除职工表中工资属性的约束(注意:
在创建职工表时,工资的约束必须有约束名,否则不能删除其约束,);*/
altertable职工表
dropconstraintck_职工表_工资
go
altertable职工表
addconstraintck_职工表_工资check(工资>=1000AND工资<=5000)
--删除订购单关系中的“完成日期”属性;
altertable订购单表
dropcolumn完成日期
--删除订购单表。
droptable订购单表
-5、用T-Sql语句,对表中的数据进行修改:
--1)给WH1仓库的职工提高10%的工资;
update职工表
set工资=工资*1.1
where仓库号='wh1'
--2)给职工表中所有职工增加10%的工资;
update职工表
set工资=工资*1.1
--3)把供应商S3的供应商名改为“湖南涉外”,地址改为“长沙”;
update供应商表
set供应商名='湖南涉外',地址='长沙'
where供应商号='S3'
--4)把e7对应的订购日期改为2009年3月9日;
update订购单表
set订购日期='2009-3-9'
where职工号='e7'
--5)把所有仓库的面积增加100;
update仓库表
set面积=面积+100
--6)删除仓库关系中仓库号值是WH2的元组。
delete订购单表
where职工号='e1'or职工号='e4'
delete职工表
where职工号='e1'or职工号='e4'
delete职工表
where仓库号='wh2'
delete仓库表
where仓库号='wh2'
--简单查询
--1)从职工表中检索所有工资值(无重复行);
selectdistinct工资
from职工表
--2)给职工表中所有职工工资加200,别名“新增工资”(无重复行);
selectdistinct工资+200as"新增工资"
from职工表
--3)检索仓库表中的所有元组;
select*
from仓库表
--4)检索出全部职工信息;
select职工号,仓库号,工资
from职工表
--5)查询仓库表的仓库号和城市,将其显示在一列里。
select仓库号+'-'+城市
from仓库表
--6)检索职工表所有信息,显示前2行元组;
selecttop2*
from职工表
--7)从订购单表中检索所有职工号(无重复行);
selectdistinct职工号
from订购单表
--4、用查询分析器,对表中的数据进行查询(多表连接查询):
--1)找出工资多于1230元的职工号和他们所在的城市(用where和join格式完成);
select职工表.职工号,仓库表.城市
from职工表join仓库表on职工表.仓库号=仓库表.仓库号
where职工表.工资>1230
--2)找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市(用where和join格式完成);
--where
select职工表.职工号,仓库表.城市
from职工表,仓库表
where(职工表.仓库号=仓库表.仓库号)and(仓库表.面积>400)
--join
select职工表.职工号,仓库表.城市
from职工表join仓库表on职工表.仓库号=仓库表.仓库号and仓库表.面积>400
--3)给出有北京仓库订购单的北京供应商的名称(用where和join格式完成);
--where
selectdistinct供应商表.供应商名
from仓库表,职工表,订购单表,供应商表
where供应商表.地址='北京'and供应商表.供应商号=订购单表.供应商号and订购单表.职工号=职工表.职工号and职工表.仓库号=仓库表.仓库号
--join
selectdistinct供应商表.供应商名
from仓库表join职工表join订购单表join供应商表on供应商表.供应商号=订购单表.供应商号on订购单表.职工号=职工表.职工号on职工表.仓库号=仓库表.仓库号and供应商表.地址='北京'
--4)使用左连接,对仓库表和职工表两个关系通过仓库号进行连接;
select*
from仓库表leftjoin职工表on仓库表.仓库号=职工表.仓库号
--5)使用右连接,对仓库表和职工表两个关系通过仓库号进行连接;
select*
from仓库表rightjoin职工表on仓库表.仓库号=职工表.仓库号
--6)使用全连接,对仓库表和职工表两个关系通过仓库号进行连接;
select*
from仓库表fulljoin职工表on仓库表.仓库号=职工表.仓库号
--4、用查询分析器,对表中的数据进行查询(嵌套查询):
--1)哪些城市至少有一个仓库的职工的工资为1250元?
select城市
from仓库表
where仓库号in(select仓库号from职工表where工资=1250)
--2)找出和职工E4挣同样工资的所有职工。
select职工号
from职工表
where职工号!
='e4'and工资in(select工资from职工表where职工号='e4')
--3)找出哪些城市的仓库向北京的供应商发出了订购单;
select城市
from仓库表
where仓库号in(select仓库号from职工表where职工号in(select职工号from订购单表where供应商号in(select供应商号from供应商表where地址='北京')))
--4)检索有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库号(分别使用“>=ANY”和“>=”完成);
selectdistinct仓库号
from职工表
where仓库号!
='wh1'and工资>=(selectmin(工资)from职工表where仓库号='wh1')
selectdistinct仓库号
from职工表
where仓库号!
='wh1'and工资>=any(select工资from职工表where仓库号='wh1')
--5)检索有职工的工资大于或等于WH1仓库中所有职工的工资的仓库号(分别使用“>=ALL”和“>=”完成);
select仓库号
from职工表
where仓库号!
='wh1'and工资>=(selectmax(工资)from职工表where仓库号='wh1')
select仓库号
from职工表
where仓库号!
='wh1'and工资>=all(select工资from职工表where仓库号='wh1')
--6)检索那些仓库中还没有职工的仓库的信息(分别使用NOTEXISTS和NOTIN完成);
select*
from仓库表
wherenotexists(select*from职工表where仓库表.仓库号=职工表.仓库号)
select*
from仓库表
where仓库号notin(select仓库号from职工表where仓库表.仓库号=职工表.仓库号)
--7)检索那些仓库中至少已经有一个职工的仓库的信息(分别使用EXISTS和IN完成);
select*
from仓库表
whereexists(select*from职工表where仓库表.仓库号=职工表.仓库号)
select*
from仓库表
where仓库号in(select仓库号from职工表where仓库表.仓库号=职工表.仓库号)
视图操作
--创建数据库
createdatabase仓库
on
(name='仓库data',
filename='C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\data\仓库_data.mdf')
logon
(name='仓库log',
filename='C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\data\仓库_log.ldf')
go
--创建表
use仓库
go
createtableS
(SNOchar(4),
SNAMEvarchar(20)notnull,
STATUSsmallint,
CITYvarchar(30),
primarykey(SNO)
);
use仓库
go
createtableP
(PNOchar(4)Primarykey,
PNAMEvarchar(20)notnull,
COLORchar(6),
WEIGHTrealdefault0
);
use仓库
go
createtableJ
(JNOchar(4)primarykey,
JNAMEvarchar(20)notnull,
CITYvarchar(20),
);
use仓库
go
createtableSPJ
(SNOchar(4),
PNOchar(4),
JNOchar(4),
QTYintnotnull,
primarykey(sno,pno,jno),
foreignkey(sno)referencess(sno),
foreignkey(pno)referencesp(pno),
foreignkey(jno)referencesj(jno),
check(qtybetween1and1000)
);
--插入数据:
insertintosvalues('s1','精益',20,'天津')
insertintosvalues('s2','盛锡',10,'北京')
insertintosvalues('s3','东方红',30,'北京')
insertintosvalues('s4','丰泰盛',20,'天津')
insertintosvalues('s5','为民',30,'上海')
insertintopvalues('p1','螺母','红',12)
insertintopvalues('p2','螺栓','绿',17)
insertintopvalues('p3','螺丝刀','蓝',14)
insertintopvalues('p4','螺丝刀','红',14)
insertintopvalues('p5','凸轮','蓝',40)
insertintopvalues('p6','齿轮','红',30)
insertintojvalues('j1','三建','北京')
insertintojvalues('j2','一汽','长春')
insertintojvalues('j3','弹簧厂','天津')
insertintojvalues('j4','造船厂','天津')
insertintojvalues('j5','机车厂','唐山')
insertintojvalues('j6','无线电厂','常州')
insertintojvalues('j7','半导体厂','南京')
insertintospjvalues('s1','p1','j1',200)
insertintospjvalues('s1','p1','j3',100)
insertintospjvalues('s1','p1','j4',700)
insertintospjvalues('s1','p2','j2',100)
insertintospjvalues('s2','p3','j1',400)
insertintospjvalues('s2','p3','j2',200)
insertintospjvalues('s2','p3','j4',500)
insertintospjvalues('s2','p3','j5',400)
insertintospjvalues('s2','p5','j1',400)
insertintospjvalues('s2','p5','j2',100)
insertintospjvalues('s3','p1','j1',200)
insertintospjvalues('s3','p3','j1',200)
insertintospjvalues('s4','p6','j1',100)
insertintospjvalues('s4','p6','j3',300)
insertintospjvalues('s5','p2','j4',200)
insertintospjvalues('s5','p3','j4',200)
insertintospjvalues('s5','p6','j1',200)
insertintospjvalues('s5','p6','j2',500)
--视图实验:
--1.为S表的sno和sname属性建立名为S_View的视图
createviewS_View
as
selectsno,sname
froms
select*
fromS_View
--2.为"三建"工程项目建立一个名为SanJian_View的供应情况视图,该视图包括供应商代码(sno),零件代码(pno),供应数量(qty)
createviewSanJian_View
as
selectsno,pno,qty,jname
fromj,spj
wherej.jno=spj.jnoandjname='三建'
select*
fromSanJian_View
--3.在SanJian_View中找出"三建"工程项目使用的各种零件代码及其数量
selectpno,qty
fromSanJian_View
--4.在SanJian_View中找出供应商s1的供应情况
select*
fromSanJian_View
wheresno='s1'
--5.建立颜色为红色且重量大于25的零件视图P_View?
?
?
createviewP_view
as
selectpno,pname,color,weight
fromp
wherecolor='红'andweight>25
select*
fromp_view
--6.将供应商号以及它为所有工程所供应零件的总数定义为一个视图S_Q_View
CreateviewS_Q_View
as
selectsnoas'供应商号',sum(qty)as'零件总数'
fromSPJ
groupbysno
select*
fromS_Q_View
--dropviewS_Q_View
/*7.在SPJ表上按属性SNO分组,求出每組在属性QTY属性上的
最大值、最小值和平均值,且将它们置于视图RVE_View中,
然后查询视图RVE_View中供应商号为“S4”的记录。
*/
CreateviewRVE_View
as
selectsno,max(qty)as'最大的',min(qty)as'最小的',avg(qty)as'平均值'
fromspj
groupbysno
select*
fromRVE_View
--8.将S_View视图中供应商号为s5的供应商名修改为“为国”
updateS_View
setsname='为国'
wheresno='s5'
select*
fromS_View
/*9.在S_View视图中插入一个新的记录,供应商号为“s6”,
供应商名为“华誉”*/
insertintoS_Viewvalues('s6','华誉')
select*
fromS_View
--10.删除SanJian_View视图中供应商号为s1的相应记录
deleteSanJian_View
wheresno='s1'
--11.为视图S_View建立供应商名sname的视图S_Sname_View
createviewS_Sname_View
as
selectsname
fromS_View
select*
fromS_Sname_View
--12.删除视图S_View
dropviewS_View
useteachdb
--1、如果袁敏的平均成绩为60分以上,显示其成绩情况,否则显示文本:
成绩状态不理想;(用IF-ELSE语句完成)
if(selectavg(score)fromchoicewheres_no=(selects_nofromstudentwheres_name='袁敏'))>60
selectscorefromchoicewheres_no=(selects_nofromstudentwheres_name='袁敏')
else
print'成绩状态不理想'
--2、根据c_no的数据显示其中文名字c_name;(用简单型CASE语句完成)
declare@cnamechar(20)
selectc_no,c_name=
casefloor(c_no-1000)
when11then'C语言'
when12then'数据结构'
when13then'微机原理'
when14then'数字电路'
when15then'高等数学'
end
fromcourse
--3、已知c_no和s_no,根据其对应成绩的范围显示:
90<=score<=100,显示‘A’;80<=score<90,显示‘B’;
-----70<=score<80,显示‘C’;60<=score<70,显示‘D’;score<80,显示‘E’;(用搜索型CASE语句完成)
selectc_no,s_no,score,
(
case
whenscore>=90andscore<=100then'A'
whenscore>=80andscore<90
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 TSQL 基础