使用SQL语句创建及使用SQLServer数据库

SQL语言在SQL Sever中的应用

1.     数据库的建立

    在企业管理器中，建立教务数据库——EDUCATION。

       1截图

        

       2说明

         在导航中的数据库上右键，新建数据库。

         填写数据库名称：EDUCATION。

         除了文件类型和文件组其余均可编辑。

由截图可知：

主数据文件的逻辑名称为EDUCATION；文件类型是行数据；文件组默认属于PRIMARY；主数据文件的初始大小为5MB；文件自动增量为1MB，增长无限制；存储路径为C:\Program Files\MicrosoftSQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\。

日志文件的逻辑名称为EDUCATION_log；文件类型是日志；不适用文件组；日志文件的初始大小为2MB；文件自动增量为10%，增长无限制；存储路径为C:\Program Files\MicrosoftSQL

 Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\。

       3与鼠标操作过程等价的建立数据库数据文件和日志文件的SQL语句

         create  database EDUCATION

        //数据文件的SQL语句

         on primary(                  //默认属于PRIMARY文件组，可省略

                     name='EDUCATION',    //主数据文件的逻辑名称

                     filename='C:\ProgramFiles\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\EDUCATION.mdf',                     //主数据文件的物理名称

                     size=5mb,             //主数据文件的初始大小

                     maxsize=unlimited,      //主数据文件增长的最大值

                     filegrowth=1mb         //主数据文件自动增量

)

//日志文件的SQL语句

log on(

                     name='EDUCATION_log', //日志文件的逻辑名称

                     filename='C:\Program Files\Microsoft SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\EDUCATION_log.ldf',                     //日志文件的物理名称

                     size=2mb,                         //日志文件的初始大小

                     maxsize=unlimited,          //日志文件增长的最大值

                     filegrowth=10%                //日志文件自动增量

)

2. 数据表的建立

1建立学生表——Student表

createtable Student(

               sno int primary key,           //列名为sno（学号），int 类型，主键

              sn char(20) not null,           //列名为sn（姓名），char(20)类型,不为空

              sex char(2) default '男'check(sex in('男','女'))not null,               //列名为sex（性别）,char(2)类型,默认为男,取值范围为男或女,不为空

              bor date not null,           //列名为bor（生日），类型为date，不为空

              clano char(10) default null,  //列名为clano（班号），类型为char（10），默认为空

              age int check(age>=0 and age<=100) notnull        //列名为age（年龄），类型为int，取值范围为[1,100]，不为空

              )

  

2建立课程表——Course表

createtable Course(

               cnochar(20) primary key,   //列名为cno（课程号），char(20)类型，主键

               cnvarchar(50) not null, //列名为cn（课程名）, varchar(50)类型，不为空

               tnoint not null            //列名为tno（教师号），int类型，不为空

               )

 

3建立分数表——Score表

createtable Score(

               snoint not null,            //列名为sno（学号），int类型，不为空

               cnochar(20) not null,    //列名为cno（课程号），char(20)类型，不为空

              score float check(score>=0 andscore<=100),               //列名为score（分数），float类型，取值范围为[1，100]

               primarykey (sno,cno)    // sno（学号）和cno（课程号）共同作为主键

              )

  

4建立教师表——Teacher表

create table Teacher(

               tnoint primary key,     //列名为tno（教师号），int类型，主键

               tnchar(20) not null,     //列名为tn（教师名），char(20)类型，不为空

               sexchar(2) default '男'check(sex in('男','女'))not null,        //列名为sex（性别）,char(2)类型,默认为男,取值范围为男或女,不为空

               bordate not null,       //列名为bor（生日），类型为date，不为空

               pro varchar(50) not null, //列名为pro（职称）,类型为varchar(50),不为空

               deptvarchar(50)       //列名为dept（系别），类型为varchar(50)

               )

3. 表中内容的添加

 1使用SQL语句在数据库中Student表插入对应表格前2行元组。

insert into Student(sno,sn,sex,bor,clano,age)values(108,'曾华','男','1992-09-01','09033',22);

insert into Student(sno,sn,sex,bor,clano,age)values(105,'匡明','男','1990-10-2','09031',24);

 2使用SQL语句在数据库中Course表中插入对应表格前2行元组。

     insertinto Course(cno,cn,tno) values('3-105','计算机导论',825);

     insert into Course(cno,cn,tno) values('3-245','操作系统',804);

 3对Student表中其他元组，通过查询分析器的数据输入界面输入到Student表格中。

 4对Course表中其他元组，通过查询分析器的数据输入界面输入到Course表格中。

 5对Teacher表中元组，通过查询分析器的数据输入界面输入到Teacher表格中。

 6对Score表中元组，通过查询分析器的数据输入界面输入到Score表格中。

4. 查询及视图的综合练习

  编写SQL语句实现下列查询，查看执行结果。

1检索计算机系男老师的姓名；

  select tn from Teacher where sex='男'and dept='计算机';

  说明：

从Teacher表中选出满足sex为'男'，dept为'计算机'的记录，然后选出他们的tn这一列。

 

2检索学号为109选修的课程号；

  select cno from Score where sno=109;

  说明：

从Score表中选出满足sno为109的记录，然后选出他们的cno这一列。

 

3统计课程号为3-105的最高分和平均分；

select max(score) as 最高分,avg(score) as 平均分 from Score wherecno='3-105';

说明：

从Score表中选出满足cno为'3-105'的记录，然后通过聚集函数max(score)和avg(score)算出他们的最高分和平均分并将max(score)和avg(score)重命名为最高分和平均分，让其显示的更加清晰。

4检索王老师所教授课程的课程号和课程名；

    select cno,cn from Course,Teacher whereTeacher.tno=Course.tno and tn like '王%';

    说明：

Course表和Teacher表进行笛卡尔积，根据Teacher.tno=Course.tno条件得出结果表（其中课程表中的教师编号与教师表中的同样的为一行），从结果表中选出tn列是以 '王'开头的记录，然后选出这些记录的cno列和cn列。

   

5检索出生日期为1990年以后的女学生的学号和姓名；

  select sno,sn from Student where sex='女' and YEAR(bor)>'1990';

  说明：

从Student表中选出满足sex为'女'，出生日期在1990年以后（从1991年开始）的记录（通过函数YEAR（）来实现），然后选出他们的sno列和sn列。

 

6检索学号为107的学生所学课程的课程名与任课老师；

    select cn,tn from Score as s,Course asc,Teacher as t where s.cno=c.cno and c.tno=t.tno and sno=107;

    说明：

Course表、Teacher表和Score表进行笛卡尔积，根据s.cno=c.cno 和 c.tno=t.tno条件得出结果表（其中课程表中的教师编号与教师表中的同样的，且成绩表的课程号和课程表的课程号同样的为一行），从结果表中选出满足sno为107的记录，然后选出这些记录的cn列和tn列。

   

7检索王同学不学习且为助教教授的课程名和任课教师；

    Select Course.cn,Teacher.tn fromTeacher,Course where Teacher.tno=Course.tno and Course.cno not in(selectScore.cno from Score where sno in(select Student.sno from Student where sn like'王%')) and pro='助教';

    说明：

        首先从Student表中选出sn列是以'王'开头的记录，然后选出他们的sno列作为下一句的条件。（选出王同学的学号）

                从Score表中选出sno列满足上一句的结果（即sno的值从上一个结果中选）的记录，然后选出他们的cno列作为下一句的条件。（选出王同学选课的课程号）

                Course表和Teacher表进行笛卡尔积，根据Teacher.tno=Course.tno条件得出结果表（其中课程表中的教师编号与教师表中的同样的为一行），从结果表中选出满足pro为'助教'，cno列满足其取值不在上一句的结果中（即cno的值从上一个结果以外的值中选）的记录，然后选出这些记录的cn列和tn列。

   

8检索至少选修两门课程的学生学号；

select Score.sno fromScore Group By Score.sno Having count(*)>=2;

说明：

Score表中按sno分组（用Group By来完成），通过count(*)统计每个分组中的记录条数（因为sno和cno能共同确定一条记录），选出满足记录条数>=2的分组（由Having进行分组内的筛选），然后选出这些分组的sno显示（在select指定的字段，要么包含在Group By语句后面作为分组依据，要么被包含在聚合函数中）。

9按学号列出每个学生所选修课程中最高分的课程名称及其分数；

selectScore.sno,Course.cn,Score.score as 最高分 from Score,Course where Score.cno=Course.cnoand Score.score in(select max(Score.score) from Score group by Score.sno);

说明：

                首先Score表中按sno分组（用Group By来完成），然后在每组中利用聚合函数max（）选出每组的最高分作为下一句的条件。（每位同学的最高分）

Course表和Score表进行笛卡尔积，根据Score.cno=Course.cno条件得出结果表（其中课程表中的课程号与成绩表中的同样的为一行），从结果表中选出score列满足其取值在上一句的结果中（即score的值从上一个结果的值中选）的记录，然后选出这些记录的sno列、cn列和score列（取别名为最高分）。

或

10建立视图ViewA，显示各班的学生人数；

create view ViewA asselect clano,count(sno) as 人数 from Student group by clano;

说明：

用create view ViewA as 创建名为ViewA 的视图。Student表中按clano分组（用Group By来完成），然后选出这些分组的clano和班级人数（用count(sno)来计算每组的记录数（取别名为“人数”））显示。（在select指定的字段，要么包含在Group By语句后面作为分组依据，要么被包含在聚合函数中）

11建立视图ViewB，显示各门课程中男学生的选修人数以及该门课的任课教师姓名；

create view ViewB asselect Course.cno,Course.cn,count(Score.sno) as 人数,Teacher.tn fromTeacher,Student,Course,Score  whereStudent.sno=Score.sno and Course.tno=Teacher.tno and Course.cno=Score.cno andStudent.sex='男' group byCourse.cno,Course.cn,Teacher.tn;

说明：

用create view ViewB as 创建名为ViewB 的视图。Course表、Teacher表、Student表和Score表进行笛卡尔积，根据Student.sno=Score.sno 、Course.tno=Teacher.tno 和Course.cno=Score.cno条件得出结果表（其中课程表中的教师编号与教师表中的同样的，且成绩表的课程号和课程表的课程号同样的，且成绩表的学号和学生表的学号同样的为一行），结果表按Course.cno、Course.cn和Teacher.tn分组（用Group By来完成），从结果表中选出满足Student.sex为'男'的记录，然后选出这些记录的Course.cno列、Course.cn列、选课人数（用count(Score.sno)来计算每组的记录数（取别名为“人数”））和Teacher.tn列显示。

12建立视图ViewC，显示学生学号和姓名；

  create view ViewC as select sno as 学号,sn as 姓名 from Student;

  说明：

用create view ViewC as 创建名为ViewC 的视图。从Student表中选出sno列和sn 列（重命名为学号和姓名）。

 

5. 索引及约束的综合练习

  在EDUCATION数据库中，使用SQL添加如下内容：

1在学生表中，为学生姓名建立非聚集索引；

   create nonclustered index sname onStudent(sn);

  说明：

               nonclusteredindex为非聚集索引。

                 sname为新建非聚集索引名称。

                 建立非聚集索引的对象为学生表中的学生姓名Student(sn)。

 

2在学生表中，添加年龄为[0,100]的约束；

  alter table Student add constraint agecheck(age>=0 and age<=100);

  说明：

               给Student表中添加名为age的约束，约束年龄列的值[0,100]。

                 alter（改变） table Student，指明是Student表。

                 add表示添加。

                 constraint指明为列。

                 age是新约束的名称。

                 check(age>=0 andage<=100)表示约束age列中值的范围为[0,100]。

 

分析：

1.    使用SQL命令 删除数据表：

       DROP TABLE<表名>

       只能删除自己建立的表，不能删除其他用户所建的表。

2.    NULL表示“不知道”、“不确定”或“没有数据”的意思。

   主键列不允许出现空值。

3.    PRIMARY KEY约束（主键约束）：

用于定义基本表的主键，起惟一标识作用，不能为NULL，不能重复。

PRIMARY KEY与UNIQUE 的区别：

        一个基本表中只能有一个PRIMARY KEY，但可多个UNIQUE。

对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL。

不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束。

4.    CHECK约束用来检查字段值所允许的范围。一个表中可以定义多个CHECK约束。每个字段只能定义一个CHECK约束。在多个字段上定义的CHECK约束必须为表约束。当执行INSERT、UPDATE语句时CHECK约束将验证数据。[CONSTRAINT <约束名>] CHECK (<条件>)

5.    使用SQL命令 修改数据表：

例：ALTER TABLE S ADD Class_NoCHAR(6),Address CHAR(40)

使用此方式增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束。

        ALTER TABLE S ALTER COLUMN   SNCHAR(10)

不能改变列名；不能将含有空值的列的定义修改为NOT NULL约束；若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型；只能修改NULL/NOT NULL约束，其他类型的约束在修改之前必须先将约束删除，然后再重新添加修改过的约束定义。

6.    聚集索引：排列的结果存储在表中只有一个。

非聚集索引：排列的结果不存储在表中可以有多个。

唯一索引：有UNIQUE，自动建立非聚集的惟一索引。

有PRIMARY KEY，自动建立聚集索引。

复合索引：将两个或多个字段组合起来建立的索引，单独的字段允许有重复的值。

CREATE [UNIQUE] [CLUSTER]INDEX <索引名> ON <表名> (<列名> [次序] [{,<列名>}] [次序]…)

7.    在select指定的字段，要么包含在Group By语句后面作为分组依据，要么被包含在聚合函数中。

8.    Date也是一个数据类型，year（）函数可以查询年份。

注：

SQL Server中企业管理器和查询分析器使用

1.    使用企业管理器

1开启企业管理器的方法

点击SQL Sever 2012，进入管理器的登录页面。要输入三类信息——服务器类型（一般选择数据库引擎）、服务器名称和身份验证（Windows身份验证简单）。

2使用企业管理器中数据库导航

数据库文件夹是我们操作的文件夹，右键点击可以新建数据库。在新建数据库中的表上右键，可以新建表。在用户文件夹上右键可以新建用户。在视图文件夹上右键可以新建视图。

3熟悉企业管理器菜单

可新建查询、执行、调试和分析（勾）SQL语句、保存和选择所在数据库等。

4查看已有数据库的属性

选择已有数据库的名称，会显示数据库的相关信息。

2.    使用查询分析器

1开启查询分析器

企业管理器菜单栏中选择新建查询。

2查询分析器界面的组成

上面是输入SQL语句的地方，下面是显示查询结果的地方，第三个是查询状态栏。