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

数据库 专栏收录该内容
21 篇文章 2 订阅

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语句的地方,下面是显示查询结果的地方,第三个是查询状态栏。


评论 4 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:技术黑板 设计师:CSDN官方博客 返回首页

打赏作者

MyAnqi

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值