数据库系统SQL实验0313.docx

数据库系统SQL实验0313.docx

《数据库系统SQL实验0313.docx》由会员分享，可在线阅读，更多相关《数据库系统SQL实验0313.docx（14页珍藏版）》请在冰豆网上搜索。

数据库系统SQL实验0313

创建架构

架构是一个对象的容器，可以包含多个数据表，存储过程和视图等基础对象，SQLServer2008采用架构分离的方式，增强了管理对象的灵活性。

架构创建有两种方式：

1、图形化界面

选择“数据库”，→架构→角色→配置权限

2、命令

CreateSchema架构名称（选择数据库已分类定义过）Authorization用户名或角色

例，先创建用户“李雪0501”

后创建架构

createschemaadminauthorization李雪0501

删除架构

dropschemaadmin

1数据定义

1.1模式的定义与删除

1、定义模式

在查询窗口输入，

[例1]createdatabasetestStudCour

创建数据库testStudCour，在对象资源管理器中的目录“数据库”单击右键“刷新”，出现“testStudCour”数据库

[例2]createdatabase[testStudCour2]onprimary

（name=N'lixuetestStudCour',filename=N'd:

\lixuetestStudCour.mdf',size=3072KB,maxsize=unlimited,filegrowth=1024KB）

LOGON

（name=N'lixuetestStudCour_log',filename=N'd:

\lixuetestStudCour_log.ldf',size=1024KB,maxsize=2048GB,filegrowth=10%）

[例3]createdatabasetestStudCour3

CreatetabletestStudCour3.TabStudCour（

学号nchar（9）primarykey,

姓名nchar（10）NULL,

性别nchar

（2）NULL,

系别nchar（20）NULL,）

消息2760，级别16，状态1，第8行

指定的架构名称"testStudCour3"不存在，或者您没有使用该名称的权限。

修改：

先执行createdatabasetestStudCour3

在执行CreatetabletestStudCour3.dbo.TabStudCour（

学号nchar（9）primarykey,

姓名nchar（10）NULL,

性别nchar

（2）NULL,

系别nchar（20）NULL,）

2、删除模式

[例4]dropdatabasetestStudCour2

删除数据库testStudCour，在对象资源管理器中的目录“数据库”单击右键“刷新”，“testStudCour”数据库被删除

[例5]dropdatabasetestStudCour3Restrict

消息156，级别15，状态1，第13行

关键字'Restrict'附近有语法错误。

[例6]dropdatabasetestStudCour3cascade

消息156，级别15，状态1，第13行

关键字'cascade'附近有语法错误。

1.2基本表的定义、删除与修改

1.2.1定义基本表

先执行createdatabasestud_cour

后执行

[例7]createtable[stud_cour].[dbo].student（snonchar（9）primarykey,

snamenchar（20）notnull,

ssexnchar

（2）,

Sageint,

sdeptnchar（20））

[例8]createtable[stud_cour].[dbo].Course（Cnonchar

（2）primarykey,

Cnamenchar（10）notnull,

Cpnonchar

（2）,

Ccreditsmallint）

[例9]createtable[stud_cour].[dbo].SC（Snonchar（9）,

Cnonchar

（2）,

Gradesmallint,

primarykey（Sno,Cno）,

foreignkey（sno）referencesStudent（sno）,

foreignkey（cno）referencesCourse（cno））

1.2.2修改基本表

[例10]altertablestud_cour.dbo.studentaddS_inschoolDate

[例11]altertablestud_cour.dbo.studentalterColumnssexsmallint

[例12]altertablestud_cour.dbo.courseaddunique（cname）

1.2.3删除基本表

[例13]droptablestud_cour.dbo.course

消息3726，级别16，状态1，第2行

无法删除对象'stud_cour.dbo.course'，因为该对象正由一个FOREIGNKEY约束引用。

[例14]

先执行createtable[stud_cour_test].[dbo].Course（Cnonchar

（2）primarykey,

Cnamenchar（10）notnull,

Cpnonchar

（2）,

Ccreditsmallint）

再执行

[例15]droptable[stud_cour_test].[dbo].Course

1.3索引的建立与删除

1.3.1索引的建立

选择要建立索引的数据库对象“stud_cour”

[例16]createindexsno_idxonstudent（sno）

建立的索引文件可以在student表中的“索引”项中可以看见。

[例17]createuniqueindexsname_idxonstudent（sname）

1.3.2索引的删除

[例18]dropindexsno_idx

消息159，级别15，状态1，第6行

必须为DROPINDEX语句指定表名和索引名。

[例19]dropindexsno_idxonstudent

删除的索引文件可以在student表中的“索引”项中可以看见。

[例20]dropindexsname_idxonstudent

2数据查询

2.1单表查询

[例6]查询全体学生的学号与姓名

selectsno,snamefromstudent

查询结果

[例6]查询全体学生的姓名、学号、所在系

SELECT*FROMstudent

[例6]查经过计算的值（查询学生姓名和出生年份）

SELECTSno,2011-sageASSDATEFROMstudent

[例6]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名

SELECTSno,'YearofBirth:

',2011-Sage,LOWER（sdept）FROMstudent

SELECTSno,'YearofBirth:

''YearofBirth:

',2011-Sage出生年份,LOWER（sdept）所在院系FROMstudent

SELECTsnoNAME,'YearofBirth:

'BIRTH,2011-sageBIRTHDAY,LOWER（sdept）DEPARTMENTFROMStudent

选出所有选课的学生学号

SELECTDISTINCTSNOFROMSC

•查计算机系全体学生名单

SELECTsnameFROMstudentWHEREsdept='cs'

查年龄20岁以下学生姓名及年龄

SELECTsno,sageFROMstudentWHEREsage<20

查年龄在18—20间学生姓名及年龄

SELECTsname,sageFROMStudentWHEREsageBETWEEN18AND20

查IS,MA,CS三个系学生姓名和性别

SELECTsname,ssexFROMstudentWHEREsdeptIN（'IS','MA','CS'）

查既不是IS,MA,也不是CS系的学生姓名和性别

SELECTsname,ssexFROMstudentWHEREsdeptNOTIN（'IS','MA','CS'）

学号为200215121的学生情况

SELECT*FROMstudentWHERESNOLIKE'200215121'

查姓刘学生姓名性别

SELECTsname,ssexFROMstudentWHEREsnameLIKE'刘%'

.查年龄在20—29间的学生姓名和年龄

Selectsname,sagefromstudentwheresagelike'2_'

cs系年龄在以下的学生姓名

Selectsnamefromstudentwheresdept='cs'andsage<20

查询DB_Design课程的课程号和学分。

SELECTCno,CcreditFROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\'

选修了3号课程的学生的学号及成绩按成绩降序排列：

Selectsno,gradefromscwherecno=3orderbygradedesc;

查全体学生情况，按所在系升序排列，同一系按年龄降序排列

Select*fromstudentorderbysdept,sagedesc

查询课程号为2且成绩高于分以上的学生

SELECT*FROMScWHERECno='2'ANDGrade>85

查询选修1或2且不低于分的学生

SELECT*FROMScWHERE（Cno='1'ORCno='2'）ANDGrade>=70

查选修了3号课程的学生的平均成绩

Selectavg（grade）平均分fromscwherecno=3;

求200215121学生的总分和平均分

SELECTSUM（Grade）总分,AVG（Grade）平均分FROMScWHERESno='200215121'

求计算机系的学生总数

SELECTCOUNT（Sno）计算机系人数FROMStudentWHERESDept='CS'

或

SELECTCOUNT（*）计算机学生数FROMStudentWHEREsDept='CS'

查询共有几个系

SELECTCOUNT（sDept）院系FROMstudent（x）

SELECTCOUNT（DISTINCTsdept）院系FROMStudent

求课程C1的最高分和最低分以及高低分之间的差距

SELECTMAX（Grade）