sql-server review

数据库 SQL-SERVER

1.数据库发展的三个阶段

数据库技术经历了人工管理阶段 文件系统阶段 数据库系统 三个阶段。

2.数据库重要知识点

数据库管理系统是数据库系统的核心。

3.创建数据库

3.1典型例题(一)

创建teaching教学库，主数据库文件的初始大小为5MB，增长方式是按10%的比例自动增长;日志文件初始大小8MB,按1MB增长。

Create Database teaching 
ON PRIMARY
(
    name=teaching_data,
    filename='E:\DATA\teaching_data.mdf',
    size=5MB,
    maxsize=unlimited,
    filegrowth=10%
)
LOG ON
(
    name=teaching_log,
    filename='E:\DATA\teaching_log.ldf',
    size=8MB,
    maxsize=unlimited,
    filegrowth=10%
)

3.2典型例题(二)

创建数据库STUDENTS,包含一个5MB和一个10MB的数据库文件以及两个5MB的事务日志文件。

Create Database STUDENTS
ON PRIMARY
(
    name=studentS1,
    filename='C:\\DATA\\studentS1.mdf',
    size=5MB,
    maxsize=unlimited,
    filegrowth=10%
)
,
FILEGROUP FG1
(
    name=studentS2,
    filename='C:\\DATA\\studentS2.ndf',
    size=5mb,
    maxsize=100mb,
    filegrowth=100mb
)
LOG ON
(
    name=studentSLOG1,
    filename='C:/DATA/studentSLOG1.ldf',
    size=5mb,
    maxsize=50mb,
    filegrowth=1mb
)
,
(
    name=studentSLOG2,
    filename='C:/DATA/studentSLOT2.ldf',
    size=5mb,
    maxsize=50mb,
    filegrowth=1mb
)

4.修改数据库

4.1增加数据库容量

为student2数据库增加容量，原来的数据库文件student2_data的初始分配空间为5MB,现在将student2_data

的分配空间增加到20MB.

Alter DATABASE student2
MODIFY file
(
    name=student2_data,
    size=20MB
)

4.2增加数据库文件

为数据库STUDENT2增加数据库文件STUDNET2_DATA1,初始大小为10MB,最大为50MB,按照5%的比例增加。

Alter DATABASE  STUDENT2
ADD file
(
    name='STUDENT2_DATA1',
    filename='F:\DATA\STUDNET2_DATA1.ndf'
    size=10MB,
    maxsize=50MB,
    filegrowth=5%
)

4.3删除数据库文件

将数据库STUDNET2中增加的数据文件STUDENT2_DATA1删除。

Alter DATABASE STUENT2
Remove file STUENT2_DATA1

5.表的操作

sql-server默认是NULL。

5.1创建表

在teaching教学库中创建course课程表。

USE teaching 
GO
Create Table course
(
    cno       char(4)      PRIMARY,
    cname     nvarchar(20) NOT NULL,
    classhour tinyint,
    credit    tinyint
)

5.2修改表

在student表中修改sname字段的属性，使该字段的数据类型为nvarchar(20)，允许为空。

use teaching 
GO
Alter TABLE student
Alter column sname nvarchar(20) NULL

在course表中添加任课老师---teacher字段，数据类型为nvarchar(10).

use teaching 
GO
Alter Table course 
ADD teacher nvarchar(10)

删除student表中的年龄----sage字段。

USE teaching 
GO
Alter Table student 
Drop column sage 

5.3列约束和表约束

5.3.1Primary Key主键约束

在teaching教学库中创建sc选课表，其中sno和cno的组合为主键!

Create Table sc 
(
    sno char(7),
    cno char(4),
    score int,
    constraint PK_sc PRIMARY KEY(sno,cno)
)

先在student1数据库中创建学生表，然后再通过修改表对学号字段创建PRIMARY KEY约束。

USE student
GO
Create Table 学生
(
    学号 char(6)     NOT NULL,
    姓名 nvarchar(8) NOT NULL,
    身份证号 char(18),
    性别     nchar(1) NOT NULL
) 
Alter Table 学生
ADD Constraint pk_st PRIMARY KEY(学号)

删除Student1数据库中学生表的PRIMARY Key约束pk_st.

DROP Constraint pk_st

5.3.2UNIQUE唯一约束

创建学生1表，主键约束在学号列上，要求身份证号的数据是唯一的。

USE Student1
GO
Create Table 学生1
(
    学号    char(6)     PRIMARY KEY，
    姓名    nvarchar(8) NOT NULL,
    身份证号 char(18)   Constraint uk_st1 UNIQUE,
    性别     nchar(1)   NOT NULL
)

设置学生表的身份证号字段的唯一性。

Alter Table 学生
Add Constraint uk_st2 UNIQUE(身份证号)

删除学生1表中创建的UNIQUE约束。

Alter Table 学生1
Drop Constraint uk_st1 

• 注意UNIQUE约束所在的列允许空值，但是主键约束所在的列不允许空值。

5.3.3Foreign Key外键约束

在数据库STUDENT1中创建一个成绩表中创建一个成绩表，学号外键。

USE STUDENT1
GO
Create Table 成绩
(
    学号 char(6) constraint fk_st Foreign Key references 学生表(学号),
    课程号 char(4),
    成绩 int
)

将teaching教学库中的student表、course表和sc表进行主外关联，student表和course表为主表，其中的sno和cno字段为主键，sc表为从表，将sc表的sno和cno字段定义为外键。

USE Teaching
GO
Alter Table sc
ADD CONSTRAINT fk_sno FOREIGN KEY(sno) references student(sno)
ADD CONSTRAINT fk_cno FOREIGN KEY(cno) references course(cno)

删除外键约束!

Drop Constraint fk_st

通过修改Student1数据库的成绩表，增加字段的CHECK约束。

USE Student1
GO
Alter Table 成绩
ADD Constraint cj_constraint CHECK(成绩>=0 and 成绩<=100)

删除Check约束

Alter Table 成绩
DROP Constrinat cj_constraint 

在Student1数据库中创建ST表，定义入学日期字段的默认值为系统的当前日期。

USE Student1
GO
Create Database ST
(
    入学日期 date Default date(getdate())/*定义外键约束*/  
)

修改STUDENT1数据库中的学生表，添加入学时期字段，并为其设置默认值约束，默认值为当前日期。

USE STUDENT1
GO
Alter Table 学生
ADD 入学日期 date NULL
Constraint Df_date 
DEFAULT getdate()

在teaching数据库的student表中插入一行数据，即"sno,sname,ssex,grade"，值为.

USE teaching 
GO
Insert Into student(sno,sname,ssex,grade)
values('1301015','刘玲玲','女','16级')

在STUDENT1数据库的学生表插入一行数据.

USE STUDENT1
GO
insert into 学生 
values('160102','刘玲','13021219989190926','女')

5.4修改数据

将STUDENT1数据库的学生表中的性别字段的值设为男。

USE STUDENT1
GO
Update 学生set 性别='男'

在STUDENT1数据库的学生表中添加备注字段nvarchar(20),备注字段的信息为已毕业。

USE STUDENT1
GO
Alter TABLE 学生 
ADD 备注 nvarchar(20)
Update 学生 set 备注='已毕业'

在学生表中将学号为160101的学生的姓名改成王无。

USE STUDENT1
GO
Update 学生 set name='王无'
where 学号='160101'

删除"学生"表中"160101"号学生的记录

USE STUDENT1
Delete 学生 where 学号='160101'

6.简单查询

查询teaching库中course表的前3行信息。

USE teaching
select top 3 * from course 

查询teaching库中course表的前50%行的信息。

USE teaching 
select top 50 percent * from course 

三种等价形式

select sno AS 学号,sname AS 姓名 from student 
select 学号=sno,姓名=sname from student
select stno 学号,sname 姓名 from student 

查询sc表，按150分制计算成绩。

select sno,cno,score150 = score*1.50 from sc 
select sno,cno,score*1.50 AS score150 from sc 
select sno,cno,score*1.50 score150 from sc

查询teaching库中成绩大于等于60的学生的学号、课程号和成绩。

USE teaching 
GO
select * where score>=60

查询teaching库中计算机专业的男生的信息

USE teaching 
GO
select * from student 
where specialty='计算机' AND ssex='男'

查询teaching库中计算机专业学生或所有专业男生的信息

USE teaching
Select * from student 
where specialty='计算机' OR ssex='男'

查询teaching库中所有姓"张"的学生的信息。

USE teaching 
select * form student 
where sname like '张%'

从teaching库中的student表中查询所有计算机和通信专业的女生的信息。

USE teaching
GO
select * from student 
where ssex='女' AND (specialty='计算机' OR specialty='通信')
注意优先级:NOT>AND>OR,所以上面的括号一定要加上！

从teaching库的student表中查询某种专业的种类(相同的按一种计算).

USE teaching 
select count(distinct specialty) AS 专业种类数 from student 

在teaching库中查询1302001号学生的平均成绩。

select avg(socre) AS 平均成绩 from sc 
where sno='1302001'

7. 分组和汇总

查询teaching库中男生和女生的人数。

USE teaching 
GO 
select ssex,count(ssex) 人数 
from student 
group by ssex 

在teaching中查询"计算机"专业的学生的学号、姓名和性别，并统计学生总人数!

USE teaching 
GO
select sno,sname,ssex from student 
where specialty='计算机'
computer count(sno)

在teaching 库中查询所有学生的选课信息，并计算每个学生的平均成绩。

select * from sc 
computer avg(score) BY sno 

在teaching库中查询与"沈艳"在同一个专业学习的学生的学号、 姓名和专业。

select sno,sname,specialty from student 
where specialty=
(select specialty from student where sname='沈艳')

在teaching库中查询C001号课的考试成绩比"郑丽"高的学生的学号和姓名
。

select student.sno,sname from student,sc
where student.sno=sc.sno AND
cno='C001' AND score > (slect score from sc where cno='C001' AND sno=(select sno from student where sname='郑丽'))

在teaching库中计算机专业年龄最大的学生的学号和姓名。

select sno,sname
from student 
where specialty='计算机' AND sage>All(select sage from student where specialty='计算机')

查询teaching库中与任何一个通信专业的学生同龄的学生的信息

select * from student 
where sage = ANY(select sage from student where specialty='通信')

在teaching库中查询选修了"C001"号课程的学生的姓名和所在专业。

select sname,specialty from student 
where sno IN (select sno from sc where cno='C001')

在teaching库中查询有两个以上学生的平均成绩超过80分的班级(用年级和专业表示)

select grade,specialty from student s,
(select sno from sc group by sno having avg(score)>80) ss
where s.sno=ss.sno
group by grade,specialty
having count(*)>2

在teaching库中查询成绩比该课的平均成绩低的学生的学号、课程号、成绩。

select sno,cno,score from sc s1
where score < (select avg(score) from sc s2 where s1.sno=s2.sno)

在teching库中查询有两门以上课程的成绩在80分以上的学生的学号、姓名、年级、专业。

select sno,sname,grade,specialty from student s 
where (select count(*) from sc where sc.sno=s.sno AND score>=80)
>=2

查询选修了课程C001和课程C004的学生的姓名

select sname from student,sc
where student.sno=sc.sno AND cno='C001'
UNION
select sname from student,sc 
where student.sno=sc.sno AND cno='C004'

查询sc中学生的成绩和学号，并按成绩(降序)排列，若成绩相同按学号(升序)排序 。

select score,sno from sc
order by score DESC,sno ASC

使用TOP关键字查询course表中学分最高的前两门课。

Select Top 2 cname,credit from course 
Order by credit DESC

在teaching中将查询的学生的姓名、学号、课程名、成绩的相关数据存到“成绩单”表中，并对新表进行查询。

select sname,sno,cname,score INTO 成绩单
from student,sc,course
where student.sno=sc.sno AND course.cno=sc.cno
GO
select * from 成绩单

在teaching库中将1302001号学生选修的操作系统课的成绩改为86分。

//分数score由学号sno和cno决定
Update sc set score=86 where sno=1302001 AND cno=(select cno from course where cname='操作系统') 

在teaching库中将13级计算机专业的张明明选修的C001号课的成绩改为92分。

//分数score由学号sno和课程号cno决定!
Update sc set score=92 where cno='C001' 
AND sno=(select sno from student
where sname='张明胆' and grade='13级' and specialty='计算机') 

在teaching库中将13级通信专业的张明明选修的C001号课删除

Delete sc where cno='C001' AND sno=(select sno from sc where specialty='计算机' AND sname='张明明' grade='13级')

创建s_c_sc视图，包括计算机专业的学生的学号、姓名、以及他们选修的课程号、课程名和成绩。

USE teaching
GO
Create View s_c_sc 
AS
    select sno,sname,cno,cname,score 
    from student,course,sc 
    where student.sno=sc.sno AND course.cno=sc.cno AND specialty='计算机'
GO

创建inve_count库存统计视图，求每种商品的总库存量，要求包括商品编号和商品名称。

Create View inve_count 
AS
    select goods.gno,gname,SUM(number) from goods,invent
    where goods.gno=invent.gno
    group by goods.gno,gname
GO

修改inve_count视图，求每种商品的总库存数量和所在仓库的个数，要求包括商品编号和商品名称。

Alter View inve_count
AS
    Select goods.gno,gname,SUM(number) AS snumber,COUNT(stno) AS storenum from goods,invent
    where goods.gno=invent.gno 
    group by goods.gno,gname

在视图上创建goodscount(商品统计)视图，求每种商品的总库存数量和总价值，要求包括商品编号和商品名称。

Create View goodscount
AS
    select goods.gno,gname,snumber,snumber*price 
    from goods,inve_count
    where goods.gno=inve_count.gno
GO

在视图窗口中查询s_c_sc视图，统计c++语言课程的总分和平均分。

USE teaching 
Select Sumscore=SUM(score),Avgscore=AVG(score) from s_c_sc
where cname='C++语言'

查询inve_count视图中冰箱的商品统计信息。

select * form inve_count 
wh/*+/ere gname='冰箱'

teaching库中student表中姓名列的升序创建一个名为index_sname的普通索引，用于T-SQL语句完成。

Create Index index_sname 
ON student(sname ASC) //默认ASC

inventory库中goods表的商品名称，生产商创建一个名为goods_producer的唯一性复合索引，其中商品名称为升序、生产商降序。

Create UNIQUE Index goods_producer
ON goods(gname ASC,producer DESC)

删除student表中的Index_sname索引。

语法:DROP INDEX tablename.index_name
Drop INDEX student.Index_sname

求选课表中某门课的平均成绩。

USE teaching 
GO
Create Function average(@cn char(4)) returns float
AS
    BEGIN
        Declare @aver float
        Select @aver=(select avg(score) from sc where cno=@cn)
        return @aver
    END 
GO

声明一个Sh1_Cursor，只显示商品表中的第3行和第5行数据。

Declare Sh1_cursor Cursor static For   //声明游标
Select * from goods 
Open Sh1_cursor   打开游标
Fetch Absolute 3 From Sh1_Cursor  提取游标 
Fetch Absolute 5 From Sh1_Cursor  提取游标
Close Sh1_Cursor      关闭游标
Deallocate Sh1_Cursor 释放游标

在inventory数据库中创建带OUTPUT参数的存储过程，用于计算指定商品的平均价格，在存储过程中使用一个输入参数(商品名)和一个输出参数(平均价格)

Create PROC avgprice @gn varchar(20),@avgp int OUTPUT
AS
    Select @avgp=avg(price) From goods where gname=@gn
GO

为student表创建一个DML触发器,在插入和修改数据时都会自动显示所有学生的信息。

Create Trigger print_table ON student
FOR INSERT,UPDATE
AS Select * from student

在student表上创建一个DELETE类型的触发器，删除数据时显示删除学生的个数.

Create Trigger del_count ON student 
FOR DELETE 
AS
    Declare @count varchar(50)
    set @count=STR(@@ROWCOUNT)+'个学生被删除'
    SELETE @count
RETURN

定义一个事件开始 Begin Transaction
提交一个事件 Commit Transaction
回滚事件 Rollback Transaction
在事件内设置保存点 Save transaction

文末