SQL数据库实训报告

网络数据库编程实训报告

实训日期：

实训地点：

学号：

姓名：

班级：

指导老师：

日期：20xx年4月27 第九周栋三楼机房 1113261113 欧开润计机111 朱强星期六 16

计划完成内容：创建学生成绩（XSCJ）数据库并完成各项操作：创建课程（KC）表：

USE [XSCJ]

/****** Object: Table [dbo].[KC] Script Date: 05/03/2013 22:30:03 ******/ SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[KC](

(

[KCH] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'只能为~8' , @level0type=N'SCHEMA',@level0name=N'dbo',

@level1type=N'TABLE',@level1name=N'KC',

@level2type=N'COLUMN',@level2name=N'KKXQ'

ALTER TABLE [dbo].[KC] ADD CONSTRAINT [DF_KC_KKXQ] DEFAULT ((1)) FOR [KKXQ] 创建学生（XS）表：

USE [XSCJ]

/****** Object: Table [dbo].[XS] Script Date: 05/03/2013 22:33:37 ******/ [KCH] [char](3) NOT NULL, [KCM] [char](16) NOT NULL, [KKXQ] [tinyint] NOT NULL, [XS] [tinyint] NOT NULL, [XF] [tinyint] NULL, CONSTRAINT [PK_KC] PRIMARY KEY CLUSTERED

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[XS](

(

[XH] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET ANSI_PADDING OFF

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'男女' , @level0type=N'SCHEMA',@level0name=N'dbo',

@level1type=N'TABLE',@level1name=N'XS',

@level2type=N'COLUMN',@level2name=N'ZYM'

ALTER TABLE [dbo].[XS] ADD CONSTRAINT [DF_XS_ZYM] DEFAULT ((1)) FOR [ZYM] GO

创建课程（XS_KC）表:

USE [XSCJ]

/****** Object: Table [dbo].[XS_KC] Script Date: 05/03/2013 22:35:37 ******/ SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON [XH] [char](6) NOT NULL, [XM] [varchar](8) NOT NULL, [ZYM] [varchar](10) NULL, [XB] [bit] NOT NULL, [CSSJ] [datetime] NOT NULL, [ZXF] [int] NULL, [BZ] [text] NULL, CONSTRAINT [PK_XS] PRIMARY KEY CLUSTERED

SET ANSI_PADDING ON

CREATE TABLE [dbo].[XS_KC](

SET ANSI_PADDING OFF

各项查询及代码：

1． USE XSCJ

SELECT XM,ZYM,ZXF

FROM XS

2． SELECT XH,XM,ZXF

FROM XS

WHERE ZYM= '计算机'

SELECT* FROM XS

3． SELECT XH AS number, XM AS name, ZXF AS mark FROM XS

WHERE ZYM= '计算机'

4． SELECT XH, XM,

等级=

CASE

WHEN ZXF IS NULL THEN '尚未选课'

WHEN ZXF < 50 THEN '不及格'

WHEN ZXF >=50 and ZXF<=52 THEN '合格' ELSE '优秀'

END

5．SELECT DISTINCT ZYM, ZXF

FROM XS

6．SELECT TOP 6 XM,ZYM,ZXF

FROM XS

7．SELECT * FROM XS WHERE ZYM='计算机' [XH] [char](6) NOT NULL, [KCH] [char](3) NOT NULL, [CJ] [tinyint] NOT NULL, [XF] [tinyint] NULL ) ON [PRIMARY]

FROM XS

WHERE ZYM = '通信工程'

AND ZXF >= 42

8．SELECT *

FROM XS

WHERE ZYM LIKE '计算机' GO

9．SELECT *

FROM XS

WHERE XM LIKE '王_'

10．SELECT *

FROM XS

WHERE CSSJ NOT BETWEEN '1979-1-1'GO

11．SELECT *

FROM XS

WHERE ZYM IN ('计算机', '通信工程',GO

12．select*

from XS

WHERE ZXF IS NULL

13．SELECT *

FROM XS

WHERE XH IN

( SELECT XH

FROM XS_KC

WHERE KCH = '101')

14．SELECT *

FROM XS

WHERE XH NOT IN

( SELECT XH

FROM XS_KC

WHERE KCH IN

( SELECT KCH

FROM KC

WHERE KCM = '离散数学'

)

15．SELECT *

FROM XS

WHERE CSSJ <ALL and '1979-12-31' 无线电') '

( SELECT CSSJ

FROM XS

WHERE ZYM = '计算机'

)

16．SELECT XH

FROM XS_KC

WHERE KCH = '206' AND CJ !< ANY

( SELECT CJ

FROM XS_KC

WHERE KCH = '101'

)

17．SELECT XM

FROM XS

WHERE EXISTS

( SELECT *

FROM XS_KC

WHERE XH = XS.XH AND KCH = '206'

)

18．SELECT XM

FROM XS

WHERE NOT EXISTS

(SELECT *

FROM KC

WHERE NOT EXISTS

( SELECT *

FROM XS_KC WHERE XH=XS.XH

AND KCH=KC.KCH )

)

19．SELECT DISTINCT XH

FROM XS_KC AS CJ1

WHERE NOT EXISTS

( SELECT *

FROM XS_KC AS CJ2

WHERE CJ2.XH = '081102'

AND NOT EXISTS

( SELECT *

FROM XS_KC AS CJ3

WHERE CJ3.XH= CJ1.XH

AND CJ3.KCH = CJ2.KCH

)

20．SELECT m.stu_name,m.speciality

FROM ( SELECT * FROM XS WHERE CSSJ<'19800101' ) AS m ( num,stu_name,speciality,sex,birthday,score,mem )

21．SELECT XS.* , XS_KC.*

FROM XS , XS_KC

WHERE XS.XH = XS_KC.XH

22．SELECT XM , CJ

FROM XS , XS_KC

WHERE XS.XH = XS_KC.XH

AND KCH = '206'

AND CJ >= 80

23．SELECT XS.XH, XM, KCM, CJ

FROM XS , KC , XS_KC

WHERE XS.XH = XS_KC.XH

AND KC.KCH = XS_KC.KCH

AND KCM = '计算机基础'

AND CJ >= 80

24．SELECT a.XH,a.KCH,b.KCH,a.CJ

FROM XS_KC a JOIN XS_KC b

ON a.CJ=b.CJ AND a.XH=b.XH AND a.KCH!=b.KCH

25．SELECT XS.* , KCH

FROM XS LEFT OUTER JOIN XS_KC

ON XS.XH = XS_KC.XH

26．SELECT XS_KC.* , KCM

FROM XS_KC RIGHT JOIN KC

ON XS_KC.KCH= KC.KCH

27．SELECT AVG(CJ) AS '课程平均成绩'

FROM XS_KC

WHERE KCH = '101'

28．SELECT MAX(CJ) AS '课程的最高分' ,

MIN(CJ) AS '课程的最低分' FROM XS_KC

WHERE KCH= '101'

29．SELECT COUNT(*) AS '学生总数'

FROM XS

30．SELECT COUNT(DISTINCT XH)

FROM XS_KC

31．SELECT COUNT(CJ) AS '离散数学分以上的人数'

FROM XS_KC

WHERE CJ >= 85

AND KCH IN

( SELECT KCH

FROM KC

WHERE KCM = '离散数学'

)

32．SELECT ZYM

FROM XS

GROUP BY ZYM

33．SELECT ZYM, COUNT(*) AS '学生数' FROM XS

GROUP BY ZYM

34．SELECT KCH, AVG(CJ) AS '平均成绩' ,

COUNT(XH) AS '选修人数' FROM XS_KC

GROUP BY KCH

35．SELECT ZYM, XB , COUNT(*) AS '人数' FROM XS

GROUP BY ZYM,XB

WITH ROLLUP

36．SELECT XH, AVG(CJ) AS '平均成绩'

FROM XS_KC

GROUP BY XH

HAVING AVG(CJ) > =85

37．SELECT XH

FROM XS_KC

WHERE CJ >= 80

GROUP BY XH

HAVING COUNT(*) > 2

38．SELECT *

FROM XS

WHERE ZYM = '通信工程'

ORDER BY CSSJ

39．SELECT XM, KCM, CJ

FROM XS, KC, XS_KC

WHERE XS.XH = XS_KC.XH

AND XS_KC.KCH = KC.KCH

AND KCM = '计算机基础' AND ZYM= '计算机' ORDER BY CJ DESC

40．USE XSCJ

CREATE VIEW CS_XS

SELECT *

FROM XS

WHERE ZYM = '计算机'

41．CREATE VIEW CS_KC WITH ENCRYPTION

SELECT XS.XH,KCH, CJ

FROM XS, XS_KC WHERE XS.XH = XS_KC.XH

AND ZYM = '计算机'

WITH CHECK OPTION

42．CREATE VIEW CS_KC_AVG(num,score_avg)

SELECT XH,AVG(CJ)

FROM CS_KC

GROUP BY XH

43．SELECT XH, KCH

FROM CS_KC

44．CREATE VIEW XS_KC_AVG ( num,score_avg )

SELECT XH,AVG(CJ)

FROM XS_KC

GROUP BY XH

SELECT *

FROM XS_KC_AVG

WHERE score_avg>=80

45. CREATE VIEW XS12_VIEW

SELECT *

FROM XS1

UNION ALL

SELECT *

FROM XS2

CREATE TABLE XS1

( xh char(6) PRIMARY KEY CHECK (xh BETWEEN '004001' AND '004050'), xm char(8) NOT NULL,

zym char(10) NULL,

xb bit NOT NULL,

zxf tinyint NULL

)

CREATE TABLE XS2

( xh char(6) PRIMARY KEY CHECK (xh BETWEEN '003001' AND '003050'), xm char(8) NOT NULL,

zym char(10) NULL,

xb bit NOT NULL,

zxf tinyint NULL

)

46. INSERT INTO XS12_VIEW

VALUES('004010','蓝源源','外语',0,40)

47. UPDATE XS12_view

SET zxf = zxf+ 10

48. ALTER VIEW CS_KC WITH ENCRYPTION

SELECT XS.xh,XS.xm,XS_KC.kch,KC.kcm,cj

FROM XS,XS_KC,KC

WHERE XS.xh = XS_KC.xh

AND XS_KC.kch = KC.kch

AND zym = '计算机'

WITH CHECK OPTION

49. DECLARE XS_CUR3 CURSOR

LOCAL SCROLL SCROLL_LOCKS

FOR

SELECT xh,xm,zxf

FROM XS

FOR UPDATE OF zxf

OPEN XS_CUR3

SELECT '游标XS_CUR3数据行数' = @@CURSOR_ROWS

51. use XSCJ

Declare @sex bit Set @sex=0

Select xh,xm From XS Where xb=@sex

52. Use XSCJ Declare @CursorVar

cursor Set @CursorVar=cursor scroll For Select xh,xm

From XS Where xm like '王%' Open @CursorVar Fetch next

from @CursorVar while @@fetch_status=0 Begin

Fetch next from @CursorVar

End Close @CursorVar Deallocate @CursorVar

53. select xm,kcm,cj from XS

where xh in(select xh from XS_CJ where cj>(select max(cj) from xm in('刘丰')))

54. select xh,xm

from XS

where zxf not between'40'and'50'

55 select * from KC

where kcm like 'C%' or kcm like 'A%'

56. select xm

from XS

where xh in (select xh from XS_KC)

57. use XSCJ

SELECT cj=

CASE

WHEN avg(cj)=75 THEN '平均成绩高于分'

ELSE avg(cj)

END

FROM XS_KC WHERE kch='212' go

日期：20xx年4月28 星期日

完成图书管理系统的设计和操作：

2-1

create table borrow(

cno int foreign key references cards (cno),

bno int foreign key references books (bno),

radte datetime,

primary key (cno,bno)

)

2-2

SELECT cno,借图书册数=COUNT(*)

FROM borrow

GROUP BY cno

HAVING COUNT(*)>5

2-3

use tushuguan

select nameo,class

from card

where cno=(select cno from borrow where bno in (select bno from books where bname in('水浒')))

2-4

SELECT * FROM borrow

WHERE rdate<GETDATE()

2-5

use tushuguan

select bno,bname,author

from books

where bname like '网络%'

2-6

use tushuguan

select top 1 bname,author

from books

order by price desc

2-7

use tushuguan

select cno

from borrow

where bno=(select bno from books where bname in('计算方法')and bname !=('计算方法习题集'))

2-8

UPDATE b SET rdate=DATEADD(Day,7,b.rdate)

FROM cards a,BORROW b

WHERE a.CNO=b.CNO AND a.CLASS=N'C01'

2-9

DELETE A FROM books

WHERE NOT EXISTS( SELECT * FROM borrow WHERE borrow.bno= books.bno ) 2-10

use orcl

select emp.ename,emp.empno,dept.dname

from emp,dept

where emp.job in('销售员')

2-11

CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)

2-12

SELECT a.cno

FROM borrow a,books b

WHERE a.bno=b.bno and b.bname in('计算方法','组合数学')

GROUP BY a.cno

HAVING COUNT(*)=2

ORDER BY a.cno DESC

select borrow.cno 借书卡号,name 姓名,class 班级

from cards inner join borrow on cards.cno= borrow.cno

inner join books on borrow.bno=books.bno

where bname='计算方法' or bname='组合数学'

group by borrow.cno,cards.name,cards.class

HAVING COUNT(*)=2

order by borrow.cno asc

2-13

alter table books add PRIMARY KEY(BNO)

日期：20xx年5月2 星期四

设计企业员工信息数据库并完成其操作：

3-1

USE [master]

/****** 对象: Database [orcl] 脚本日期: 05/02/2013 09:33:59 ******/ CREATE DATABASE [orcl] ON PRIMARY

( NAME = N'orcl', FILENAME = N'D:\新建文件夹\第三题\orcl.mdf' , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ),

( NAME = N'orcl1', FILENAME = N'D:\新建文件夹\第三题\orcl1.ndf' , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB )

LOG ON

( NAME = N'orcl_log', FILENAME = N'D:\新建文件夹\第三题\orcl_log.ldf' , SIZE =

20480KB , MAXSIZE = 2048GB , FILEGROWTH = 5120KB )

COLLATE Chinese_PRC_CI_AS

EXEC dbo.sp_dbcmptlevel @dbname=N'orcl', @new_cmptlevel=90 GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin

EXEC [orcl].[dbo].[sp_fulltext_database] @action = 'disable' end

ALTER DATABASE [orcl] SET ANSI_NULL_DEFAULT OFF

ALTER DATABASE [orcl] SET ANSI_NULLS OFF

ALTER DATABASE [orcl] SET ANSI_PADDING OFF

ALTER DATABASE [orcl] SET ANSI_WARNINGS OFF

ALTER DATABASE [orcl] SET ARITHABORT OFF

ALTER DATABASE [orcl] SET AUTO_CLOSE OFF

ALTER DATABASE [orcl] SET AUTO_CREATE_STATISTICS ON GO

ALTER DATABASE [orcl] SET AUTO_SHRINK OFF

ALTER DATABASE [orcl] SET AUTO_UPDATE_STATISTICS ON GO

ALTER DATABASE [orcl] SET CURSOR_CLOSE_ON_COMMIT OFF GO

ALTER DATABASE [orcl] SET CURSOR_DEFAULT GLOBAL

ALTER DATABASE [orcl] SET CONCAT_NULL_YIELDS_NULL OFF GO

ALTER DATABASE [orcl] SET NUMERIC_ROUNDABORT OFF

ALTER DATABASE [orcl] SET QUOTED_IDENTIFIER OFF

ALTER DATABASE [orcl] SET RECURSIVE_TRIGGERS OFF

ALTER DATABASE [orcl] SET ENABLE_BROKER

ALTER DATABASE [orcl] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO

ALTER DATABASE [orcl] SET DATE_CORRELATION_OPTIMIZATION OFF GO

ALTER DATABASE [orcl] SET TRUSTWORTHY OFF

ALTER DATABASE [orcl] SET ALLOW_SNAPSHOT_ISOLATION OFF

ALTER DATABASE [orcl] SET PARAMETERIZATION SIMPLE

ALTER DATABASE [orcl] SET READ_WRITE

ALTER DATABASE [orcl] SET RECOVERY FULL

ALTER DATABASE [orcl] SET MULTI_USER

ALTER DATABASE [orcl] SET PAGE_VERIFY CHECKSUM

ALTER DATABASE [orcl] SET DB_CHAINING OFF

2、3、5

USE [orcl]

/****** 对象: Table [dbo].[dept] 脚本日期: 05/02/2013 14:50:09 ******/ SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[dept](

(

[empno] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF [empno] [int] IDENTITY(1000,1) NOT NULL, [ename] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [job] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [mgr] [int] NULL, [hiredate] [timestamp] NULL, [sal] [dbo].[工资] NULL, [comm] [dbo].[工资] NULL, [deptno] [int] NULL, [sex] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_dept] PRIMARY KEY CLUSTERED

use orcl

insert into emp(ename,job,mgr,hiredate,sal,comm,sex)

values('张三','经理' ,null,'1982-12-7','800',null, '20' )

insert into emp(ename,job,mgr,hiredate,sal,comm,sex)

values('王新','销售员','1001' ,'1981-2-20','1600','300' ,'30' )

insert into emp(ename,job,mgr,hiredate,sal,comm,sex)

values('李立','销售员','1001' ,'1981-2-22','1250','500' ,'30' )

8、

use orcl

insert into dept(dname,Loc)values('销售部','北京')

insert into dept(dname,Loc)values('会计部','北京')

insert into dept(dname,Loc)values('生产部','上海')

use orcl

select *from emp

where empno in('20')

use orcl

select emp.ename,emp.empno,dept.dname

from emp,dept

where emp.job in('销售员')

use orcl

select * from emp

where deptno in('20')and job in('销售员')or deptno in('30')and job in('经理')

use orcl

select * from emp

where ename like '__'

use orcl

select * from emp

where ename not like'张%'

use orcl

select ename,job,sal

from emp

order by sal desc

日期：20xx年5月3 星期五

实训报告

为期四天的SQL sever的实训一转眼就过去了，经过这次实训，既让我巩固了课堂上学到的知识，也让我领会了很多新的的知识，提高了自己的实操能力，开拓了自己的视野。

这次我们实训的内容是从数据库、数据表的创建和修改、查询开始的，表是建立关系数据库的基本结构，在表的操作过程中，有查看表信息、查看表属性、修改表中的数据、删除表中的数据及修改表和删除表的操作。从实训中让我更明白一些知识，表是数据最重要的一个数据对象，表的创建好坏直接关系到数数据库的成败，表的内容是越具体越好，但是也不能太繁琐，以后在实际应用中多使用表，对表的规划和理解就会越深刻。

我们实训的另一个内容是数据库的视图与查询的操作。从中我们了解到查询语句的基本结构，和简单SELECT语句的使用。以前上课的时候都是对着书本看老师在操作，自己很少会去动手去使用，经过这次实训，我懂得了一般数据库的查询和表的查询。我知道数据库的目的最终就是方便使用者，为使用者提供查询操作，方便对数据统一的管理和收集。

在完成老师给的任务的同时，我同时也对数据库一些其它的功能进行了了解，例如在视图的操作中，也了解到了视图是常见的数据库对象，是提供查看和存取数据的另一种途径，对查询执行的大部分操作，使用视图一样可以完成。使用视图不仅可以简化数据操作，还可以提高数据库的安全性，不仅可以检索数据，也可以通过视图向基表中添加、修改和删除数据。还有了解了一下存储过程、触发器，在操作中有建立存储过程，执行存储过程，及查看和修改存储过程，这些都是非常基础的东西，也是我需要去提高的知识。

最后还有在实训过程中，我遇到不懂的问题会去翻书、会去和同学讨论、会上网去搜集资料，这些也丰富了我学习的渠道，让我可以更加全面去了解那些问题，从而去解决它们，这也提高了我对这门课程的掌握程度。

相关推荐

SQL数据库实训报告

专栏推荐