实验2约束及简单查询

1、实验2 约束及简单查询一、目的和要求(1) 学会在企业管理器中创建数据库和表。(2) 学会使用T-SQL 语句创建数据库和表。二、背景知识（6）创建数据表的语句使用CREATETABLE语句来建立表，其语法如下：CREATE TABLE <表名> （<列名> <数据类型> <列级完整性约束条件> ，<列名> <数据类型> <列级完整性约束条件> ，<表级完整性约束条件> ）；三、实验内容创建学生管理数据库，数据库名为XSGL，包含学院信息、学生信息、课程信息和学生选课信息，并插入原始数据。数据库XS

2、GL包含4个表即学院信息表dept、学生信息表student、课程信息表course及学生选课表sc。各表的结构分别如表2-1，2-2，2-3，2-4所示。表2-1 学院信息表：dept列名数据类型长度是否允许NULL说明DNOChar4×学院编号，唯一性DNAMEChar30×学院名DEANChar20院长姓名表2-2 学生信息表：student列名数据类型长度是否允许NULL说明SNOChar8×学号，唯一性，前4位表示入学年份，第5位表示学院编号，第6位表示专业编号SNAMEChar20×姓名AGEsmallint2年龄SEXChar2性别，只能是

3、男'或'女DNOChar4×学院编号BIRTHDAYdatetime8出生日期表2-3 课程信息表： course列名数据类型长度是否允许NULL说明CNOChar8×课程编号, 主码CNAMEChar30×课程名称TNAMEChar20教师姓名CREDITfloat4课程学分ROOMChar30上课教室表2-4 学生选课表：sc列名数据类型长度是否允许NULL说明SNOChar8×学号， CNOChar8×课程编号GRADEfloat2课程成绩四、实验步骤1. 在企业管理器中创建数据库XSGL要求：数据库XSGL初始大小为10

4、MB，最大大小为50MB，数据库自动增长，增长方式是按5%比例增长；日志文件初始为2MB，最大可增长到5MB(默认为不限制)，按1MB增长(默认是按5%比例增长)。数据库的逻辑文件名和物理文件名均采用默认值，分别为XSGL_data和e:sql XSGL.mdf，其中e:sqldataMSSQL为SQL Server 的系统安装目录；事务日志的逻辑文件名和物理文件名也均采用默认值，分别为XSGL_LOG和e:sql XSGL_Log.ldf。方法：企业管理器->右击数据库->新建数据库，注意：在“数据文件”选项卡和“事务日志”选项卡中，可以分别指定数据库文件和日志文件的物理路径等特

5、性。4. 查看与修改数据库在企业管理器中，先单击“数据库”文件夹，再选中待修改数据库XSGL，然后单击工具栏中的属性图标；或在待修改数据库上单击右键，在快捷菜单中单击“属性”菜单项；或双击右框中的待修改数据库，都将出现“数据库属性”对话框，如图2-1所示。 图2-1XSGL数据库属性对话框 图2-2 分离数据库菜单操作通过数据库属性对话框，可以分别对XSGL数据库进行修改如调整数据库大小等操作。5.在企业管理器中分别创建表dept、student、course和sc。在企业管理器中选择数据库 XSGL->在XSGL上单击鼠标右键->新建->表->输入dept表各字段信息

6、->单击保存图标->输入表名dept，即创建了表dept。按同样的操作过程创建表student、course和sc。6.在企业管理器中删除创建的dept、student、course和sc表在企业管理器中选择数据库XSGL的表student>在student上单击鼠标右键->删除，即删除了表student。按同样的操作过程删除表dept,course和sc。7.使用T-SQL语句创建表dept、student、course和sc表并建立相关约束启动查询分析器->在“查询”窗口中输入以下T-SQL语句：/* 学院信息表 */CREATE TABLE dept(DNO

7、 CHAR(4) NOT NULL , DNAME CHAR(30) NOT NULL, DEAN CHAR(10), PRIMARY KEY (DNO);/* 学生信息表 */CREATE TABLE student(SNO CHAR(8) NOT NULL UNIQUE, SNAME CHAR(10) NOT NULL, AGE SMALLINT, SEX CHAR(2), CHECK (SEX IN ('男','女'), DNO CHAR(4) NOT NULL, BIRTHDAY DATETIME, PRIMARY KEY (SNO), FOREIGN

8、KEY (DNO) REFERENCES dept);/* 课程信息表 */CREATE TABLE course(CNO CHAR(8) NOT NULL UNIQUE, CNAME CHAR(30) NOT NULL, TNAME CHAR(10) , CREDIT FLOAT, ROOM CHAR(30), PRIMARY KEY (CNO);/* 学生选课表 */CREATE TABLE sc( SNO CHAR(8) NOT NULL, CNO CHAR(8) NOT NULL, GRADE FLOAT, CHECK (GRADE BETWEEN 0 AND 100), PRIMAR

9、Y KEY (SNO,CNO), FOREIGN KEY(SNO)REFERENCES student, FOREIGN KEY(CNO)REFERENCES course) GO单击快捷工具栏的执行图标“”执行上述语句，四张表便创建完成。8.将XSGL数据库从服务器分离步骤：（1）在企业管理器中，右击“XSGL”数据库，在弹出的快捷菜单上，选择“所有任务”，在其及联菜单上，选择“分离数据库”，如图2-2所示。（2）在“分离数据库”对话框中，单击“确定”按钮。如图2-3所示。 图2-3 分离数据库对话框 图2-4 附加数据库菜单操作（3）在随后弹出的对话框中，单击“确定”按钮。如图所示。说明：

10、XSGL数据库从SQLSERVER中分离后，在企业管理器中，就看不见XSGL数据库了。建议学生每次在机房练习下课时，将数据库分离出来，以免数据丢失。下次上机，再附加数据库。9.将XSGL数据库附加到服务器中步骤：（1）在企业管理器中，右击“数据库”，在弹出的快捷菜单上，选择“所有任务”，在其及联菜单上，选择“附加数据库”，如图2-4所示。从教师机复制带有原始数据的数据库文件XSGL_Data.MDF和XSGL_log.LDF,为附加数据库作准备。（2）在“附加数据库”对话框中，单击“文件查找”按钮。找出XSGL_Data.MDF文件，再单击“确定”按钮。如图2-5所示。 图2-5 附加数据库对

11、话框 图2-6 附加数据库完成对话框（3）在随后弹出的对话框中，单击“确定”按钮。如图2-6所示。XSGL数据库附加到SQLSERVER中，在企业管理器中，就能看见XSGL数据库，并对其进行操作了。SQL查询 练习1.求学院编号为0001的学生的学号、姓名、性别SELECT SNO,SNAME,SEX FROM student WHERE DNO='0001' 2.求学院编号为0001 的男生的学号、姓名、性别SELECT SNO,SNAME,SEX FROM student WHERE DNO='0001' AND SEX='男' 3.求选修授

12、课班号为327401且成绩在8090之间的学生学号和成绩，并将成绩乘以系数0.8输出，且将SNO列更名为学号，成绩列更名为处理成绩 。 SELECT SNO AS 学号,GRADE*0.8 AS 处理成绩 FROM sc WHERE CNO='327401' AND GRADE BETWEEN 80 AND 90 4求每个学生的年龄，并输出姓名和年龄SELECT SNAME,YEAR(GETDATE()-YEAR(BIRTHDAY) AS 年龄 FROM student5求选修了课程的学生的学号SELECT DISTINCT SNO FROM sc说明：注意在SNO前加DIST

13、INCT与不加DISTINCT的区别6求选修授课班号为327401的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列SELECT SNO,GRADE FROM sc WHERE CNO='327401' ORDER BY GRADE DESC,SNO ASC7求缺少了成绩的学生学号和课程号。SELECT SNO,CNO FROM sc WHERE GRADE IS NULL8统计选课学生人数及最高分成绩和最低分成绩 SELECT COUNT(DISTINCT SNO) ,MAX(GRADE),MIN(GRADE)FROM sc9求学院编号为0001或0002中姓张的学生的信息。 SELECT * FROM student WHERE (DNO='0001' OR DNO='0002' ) AND SNAME LIKE '张%'10求姓名中包含丽的学生信息SELECT * FROM student WHERE SNAME LIKE '%丽%'11求姓名只有两个字，且第二个字为丽的学