快速熟悉数据库SQL Server命令

一、SQL

以下都是我大学上课做的作业、答案,供大家快速上手,或者是一些学过但又遗忘了的查缺补漏一下,争取掌握最基础的增删改查操作。

2. 第二次上机

建表 注意逗号 和 注意最后一个没有逗号

create建表

create table 客户

(

​ 客户编号 varchar(6) primary key,

​ 客户姓名 varchar(20) unique not null,

​ 性别 char(2),

​ 身份证 varchar(20),

​ 邮政编号 char(6),

​ 地址 varchar(50),

​ 联系电话 varchar(18),

​ 安全码 char(4) ****

)

create table 职员

(

​ 职员编号 varchar(4) primary key,

​ 姓名 varchar(20)unique not null,

​ 性别 char(2),

​ 联系电话 varchar(18),

​ 邮箱 varchar(20),

​ 部门 varchar(50)

)

create table 供应商

(

​ 供应商编号 varchar(4) primary key,

​ 供应商名称 varchar(50) unique,

​ 地址 varchar(50),

​ 邮箱 varchar(20),

​ 联系人 varchar(20),

​ 联系电话 varchar(18)

)

create table 图书信息

(

​ 图书编号 varchar(20) primary key,

​ 图书名称 varchar(100) not null,

​ 价格 Money not null,

​ 出版社 varchar(4) not null,

​ 图书分类编号 varchar(8) not null foreign key references 图书分类(图书分类编号),

​ 作者 varchar(40),

​ 出版日期 date,

​ 版次 smallint,

​ 封面图片 varchar(50),

​ 图书简介 text

)

create table 购物车

(

​ 购物车ID int primary key,

​ 图书编号 varchar(20) not null foreign key references 图书信息(图书编号),

​ 客户编号 varchar(6) not null foreign key references 客户(客户编号),

​ 数量 smallint,

​ 单价 money not null

)

create table 订单

(

​ 订单编号 varchar(20) primary key,

​ 客户编号 varchar(6) not null foreign key references 客户(客户编号),

​ 金额 money not null,

​ 订货日期 date,

​ 送货方式 varchar(50),

​ 经办人 varchar(20),

​ 付款方式ID int not null foreign key references 付款方式(付款方式ID),

​ 包装方式 varchar(50),

​ 是否发货 bit,

​ 是否收货 bit,

​ 收货人 varchar(20),

​ 是否保值 bit

)

create table 订单明细

(

​ 订单明细ID int primary key,

​ 订单编号 varchar(20) not null foreign key references 订单(订单编号),

​ 数量 smallint,

​ 图书编号 varchar(20) not null foreign key references 图书信息(图书编号),

​ 价格 money not null

)

create table 用户

(

​ 用户ID int primary key,

​ 用户名称 varchar(20) not null,

​ 密码 varchar(20) not null,

​ 用户类型 varchar(20),

​ 注册日期 date

)

alter table 购物车 add default 1 for 数量;

alter table 付款方式 add 备注 varchar(50);

alter table 付款方式 add constraint UK unique(支付方式);

alter修改表 add

加个默认序号

altet table 购物车 add default 1 for 数量;

加一个字段

alter table 付款方式 add 备注 varchar(50);

加约束

alter table 付款方式 add constraint UK unique(支付方式);

3. 第三次上机

1.查询学生表的所有记录

select

–记录数:32

select * from 学生

2.在 借阅者信息表 中查询 借阅者的姓名 和 所在部门名称

–记录数:48

select 姓名,部门名称 from 借阅者信息

3.查询教师的姓名、性别和部门名称,并将 部门名称 重命名为“系部”

–记录数:16

select 姓名,性别,部门名称 as 系部  from 教师

4.在图书借阅表中查询借书证编号,图书编号,借出日期和借出天数,其中借出天数为计算字段,即当前时间与借出日期之差(提示:利用datediff()函数)

–记录数:15

select 借书证编号,图书编号,借出日期, datediff(day,借出日期,getdate()) as 借出天数 from 图书借阅

datediff( day, 借出日期, getdate() )

第一个参数day因为我们要算天数 正常还有month year

第二个参数是之前的日期

第三个参数是之后的日期 这里用了getdate() 就是获取当时的系统时间

top 前几条

5.从 藏书信息表 中 查询 总藏书量 最多的10条 藏书记录

–记录数:10

select top 10 总藏书量 from 藏书信息

top 5 percent

6.从藏书信息表中查询总藏书量排前5%的藏书记录

–记录数:5

select top 5 percent 总藏书量 from 藏书信息

distinct 去重

7.从学生表中查询所有班级名称

–记录数:3

select distinct 班级名称 from 学生

where

8.检索学生表中姓名为“王永林”的学生记录

–记录数:1

select * from 学生 where 姓名='王永林'

like 模糊查询

9.检索学生表中 姓王 的学生记录

–记录数:4

select * from 学生 where 姓名 like '王%'

like ‘王_’ 两个字 姓王

10.检索学生表中姓王,且姓名为两个字的学生记录

–记录数:2

select * from 学生 where 姓名 like'王_'

between and

11.在图书信息表中查询图书价格在20-30之间,出版社为1,图书类型为T的图书信息

–记录数:3

select * from 图书信息 where 价格 between 20 and 30 and 出版社 ='1' and 图书类型 ='T'

12.在图书借阅表中查询图书状态为1、2、3的图书借阅信息(用四种方法完成)

–记录数:3

in

select * from 图书借阅 where 图书状态 in(1,2,3)

or

select * from 图书借阅 where 图书状态='1' or 图书状态='2' or 图书状态='3'	

between and

select * from 图书借阅 where 图书状态 between 1 and 3

>= 和<=

select * from 图书借阅 where 图书状态 >=1 and 图书状态 <=3

like’%教程’ …结尾

13.在图书信息表中查询图书名称不以“计算机”开头,但以“教程”结尾的图书信息

–记录数:24

select * from 图书信息 where 图书名称 not like'计算机%' and 图书名称 like'%教程'

count(*) 算人数 条数

14.在学生表中查询软件1101班学生的人数

–记录数:16

select count(*) as 软件1101班学生的人数 from 学生 where 班级名称='软件1101'

sum avg max min 函数

15.从藏书信息表中查询所有图书的总藏书量,平均藏书量,最大藏书量,最小藏书量及藏书种数,并为这些字段自定义名称

–记录数:1

select sum(总藏书量) as 总藏书量,

	  avg(总藏书量) as 平均藏书量,

	  max(总藏书量) as 最大藏书量,

	  min(总藏书量) as 最小藏书量,

	  count(ISBN编号)as 藏书种数  from 藏书信息

❤❤❤❤❤count括号内可以是字段❤❤❤❤❤

order by 排序

❤❤❤❤❤默认升序 asc❤❤❤❤❤

❤❤❤❤❤也可以降序 desc❤❤❤❤❤

16.从图书信息表中统计每个出版社的图书数量,并按降序排列。

–记录数:30

select 出版社,count(*) as 图书数量 from 图书信息 group by 出版社 order by 图书数量 desc

综合全部

17.在图书信息表中检索每个出版社出版的图书类型为T的图书的最高价格和最低价格,

并只输出最高价格不大于50的记录,并按最高价格降序排列。

–记录数:24

select 出版社,max(价格)as 最高价格,min(价格)as 最低价格 

from 图书信息

where 图书类型='T' and 价格<=50 

group by 出版社

order by 最高价格 desc

 

❤❤❤❤❤文字要加引号 数字不用❤❤❤❤❤

4 .第四次上机

where 连接表第一种

直接 from 表名1,表名2 where 表名1.字段1 = 表名2.字段2

–查询所有图书信息,显示图书信息的全部字段,并输出对应的出版社名称

–记录数:100

select 出版社.出版社名称,图书信息.* from 图书信息,出版社 where 图书信息.出版社=出版社.出版社ID

join on 连接查询 第二种

from 表1 join 表2 on 表名1.字段1 = 表名2.字段2

–查询已借阅图书的学生的借书情况。(连接查询)

–记录数:15

select * from 图书借阅 join 借书证 on 图书借阅.借书证编号=借书证.借书证编号

答案

select * from 学生 join 借书证 on 学生.学号=借书证.借阅者编号
join 图书借阅 on 借书证.借书证编号=图书借阅.借书证编号

–检索“计算机系”已借出图书的图书编号、图书名称、借书证编号、借阅者姓名信息。(连接查询两种格式)

select 图书借阅.图书编号,图书名称,借书证.借书证编号,借阅者信息.姓名 
from 藏书信息,图书借阅,图书信息,借书证,借阅者信息
where 藏书信息.ISBN编号=图书信息.ISBN编号 and 图书借阅.图书编号=藏书信息.图书编号
and 借书证.借书证编号=图书借阅.借书证编号 and 借阅者信息.借阅者编号=借书证.借阅者编号 and 部门名称='计算机系'
select 图书借阅.图书编号,图书名称,借书证.借书证编号,借阅者信息.姓名 
from 藏书信息 join 图书借阅 on 图书借阅.图书编号=藏书信息.图书编号
join 图书信息 on 藏书信息.ISBN编号=图书信息.ISBN编号
join 借书证 on 借书证.借书证编号=图书借阅.借书证编号
join 借阅者信息 on 借阅者信息.借阅者编号=借书证.借阅者编号
where 部门名称='计算机系'

exists 子查询 嵌套多一个select语句

–从学生表中查询已借阅图书的学生的基本信息。(子查询)

–记录数:11

select * from 借书证 where exists(select * from 图书借阅 where 借书证.借书证编号=图书借阅.借书证编号)

答案

select * from 学生 where 学号 in (select 借阅者编号 from 借书证 where 借书证编号 in(select 借书证编号 from 图书借阅))

–从图书借阅表中检索作者为陈承欢的图书的借阅情况。(子查询)

–select * from 藏书信息 3 4

–select * from 图书借阅 1 4

–select * from 出版社 2

–select * from 借书证 1

–select * from 图书信息 2 3

–select * from 学生

select * from 图书借阅	where exists(select * from 图书信息 where 作者='陈承欢')
select * from 图书借阅 where 图书编号 in(
select 图书编号 from 藏书信息 where isbn编号 in (select isbn编号 from 图书信息 where 作者='陈承欢'))

left join on

–查询所有学生的借书情况。

select * from 学生 left join 借书证 on 学生.学号=借书证.借阅者编号

join on多个表一起

–检索作者为陈承欢的图书的借阅情况,显示借出图书的借书证编号、图书编号、图书名称、作者、出版社名称及借出日期字段。(连接查询两种格式)

–记录数:8

select 借书证编号,图书借阅.图书编号,图书名称,作者,出版社名称,借出日期 

from 出版社 join 图书信息 on 出版社.出版社ID=图书信息.出版社 

			join 藏书信息 on 图书信息.ISBN编号=藏书信息.ISBN编号

			join 图书借阅 on 藏书信息.图书编号=图书借阅.图书编号

where 作者='陈承欢'

 

group by 分组

–检索借阅者的借书证编号,姓名及所借图书的数量。

select 借书证.借书证编号,姓名,count(*) as 所借图书的数量 from 借书证 join 图书借阅 on 借书证.借书证编号=图书借阅.借书证编号
group by 借书证.借书证编号,姓名

–检索 借阅图书数量最多的 借阅者的 借书证编号,姓名及所借图书的数量。

select top 1 借书证.借书证编号,姓名,count(*) as 所借图书的数量 from 借书证 join 图书借阅 on 借书证.借书证编号=图书借阅.借书证编号
group by 借书证.借书证编号,姓名
order by 所借图书的数量 desc

–检索“石磊”借出的三本图书的图书编号、ISBN编号、图书名称信息(选做)。

select 图书借阅.图书编号,图书信息.ISBN编号,图书名称
from 借书证 join 图书借阅 on 借书证.借书证编号=图书借阅.借书证编号
join 藏书信息 on 藏书信息.图书编号=图书借阅.图书编号
join 图书信息 on 图书信息.ISBN编号=藏书信息.ISBN编号
where 姓名='石磊'
select j1.图书编号,c1.ISBN编号,t1.图书名称, j2.图书编号,c2.ISBN编号,t2.图书名称, j3.图书编号,c3.ISBN编号,t3.图书名称
from 图书借阅 j1 join 图书借阅 j2 on j1.借书证编号=j2.借书证编号 and j1.借阅ID<j2.借阅ID
join 图书借阅 j3 on j1.借书证编号=j3.借书证编号 and j2.借阅ID<j3.借阅ID
join 借书证 on 借书证.借书证编号=j1.借书证编号
join 藏书信息 c1 on c1.图书编号=j1.图书编号
join 藏书信息 c2 on c2.图书编号=j2.图书编号
join 藏书信息 c3 on c3.图书编号=j3.图书编号
join 图书信息 t1 on t1.ISBN编号=c1.ISBN编号
join 图书信息 t2 on t2.ISBN编号=c2.ISBN编号
join 图书信息 t3 on t3.ISBN编号=c3.ISBN编号
where 姓名='石磊'

5. 第五次上机 综合

-------------------------------------------------1.创建数据库------------------------------------------------
操作1.1:创建一个test数据库,其主数据文件逻辑名test_data,物理文件名test_data.mdf,初始大小10MB,最大尺寸为无限大,增长速度1MB;
--数据库日志文件逻辑名称为test_log,物理文件名为test_log.ldf,初始大小为1MB,最大尺寸为5MB,增长速度为10%。
create database test
on
(
	name=test_data,     
	filename='C:\demo\test_data.mdf',
	size=10MB,
	maxsize=Unlimited,
	filegrowth=1MB
)       
log on
(
	name=test_log,     
	filename='C:\demo\test_log.ldf',
	size=1MB,
	maxsize=5MB,
	filegrowth=10%        
) 
-------------------------------------------------2.创建表-------------------------------------------------
--操作2.1:创建学生表:
USE test

CREATE TABLE student
(
	st_id 		nVarChar(9) 	primary key  ,
	st_nm 		nVarChar(8) 	NOT NULL ,
	st_sex 		nVarChar(2) 	NULL ,
	st_birth 	datetime 		NULL ,
	st_score 	int 			NULL ,
	st_date 	datetime 		NULL ,
	st_from 	nVarChar(20)	NULL ,
	st_dpid 	nVarChar(2) 	NOT NULL foreign key references dept(dp_id),
	st_mnt 		tinyint 		NULL
)

--操作2.2:创建课程信息表:
CREATE TABLE course
(
	cs_id 	nVarChar(4) 	primary key NOT NULL ,
	cs_nm 	nVarChar(20) 	NOT NULL ,
	cs_tm 	int 			NULL ,
	cs_sc 	int 			NULL
)

--操作2.3:创建选课表:
CREATE TABLE st_course
(
	cs_id 	nVarChar(4) 	NOT NULL   foreign key references course(cs_id),
	st_id 	nVarChar(9) 	NOT NULL   foreign key references student(st_id),
	score 	int 			NULL ,
	stdate  datetime 		NULL ,
	primary key(cs_id, st_id)
)

--操作2.4:创建院系信息表:
CREATE TABLE dept
(
	dp_id 	nVarChar(2) 	primary key ,
	dp_nm 	nVarChar(20)	NOT NULL ,
	dp_drt	nVarChar(8) 	NULL ,
	dp_tel	nVarChar(12)	NULL
)
-------------------------------------------------3.表中插入数据----------------------------------------------

--操作3.1:向dept表插入一条记录,系号11,系名自动控制系,系主任为李其余,电话81234567
INSERT INTO dept VALUES('11', '自动控制系', '李其余', '81234567')


--操作3.2:向student表插入一条记录,学号070201001,姓名为王小五,性别为男,出生日期为1990年9月9日,系号为11,其余字段为NULL或默认值
INSERT INTO student(st_id, st_nm, st_sex, st_birth, st_dpid)
VALUES ('070201001', '王小五', '男', '1990.9.9', '11' )


--操作3.3:向course表插入一条记录,课程号1234,课程名为操作系统,其余字段为NULL或默认值
INSERT INTO course(cs_id, cs_nm) VALUES ('1234', '操作系统')



--操作3.4:向st_course表插入一条记录,课程号1234,学名070201001,其余字段为NULL或默认值
INSERT INTO st_course(cs_id, st_id) VALUES ('1234', '070201001')
-------------------------------------------------4.修改表中数据-------------------------------------------------
--操作4.1:修改student表记录,将王小五的入学成绩改为88
UPDATE student SET st_score=88 WHERE st_nm='王小五'

--操作4.2:修改couse表记录,将所有记录的学分改为4,学时改为64
UPDATE course SET cs_tm=64, cs_sc=4

--操作4.3:修改st_course表记录,将课程号为1234,学名为070201001的记录的成绩改为77
UPDATE st_course SET score=77 WHERE cs_id='1234' AND st_id='070201001'
-------------------------------------------------5.删除表中数据-------------------------------------------------
--操作5.1:删除st_course表记录,将课程号为1234,学名为070201001的记录删除
DELETE FROM st_course WHERE cs_id='1234' AND st_id='070201001'

--操作5.2:删除course表记录,将课程号为1234的记录删除
DELETE FROM course WHERE cs_id='1234'
-------------------------------------------------6.简单查询-------------------------------------------------
--请自行向几张表中插入若干记录,然后完成以下操作。
--略

--操作6.1:查询所有系的信息
SELECT * FROM dept

--操作6.2:查询所有的课程号与课程名称
SELECT cs_id, cs_nm FROM course

--操作6.3:在查询student表时使用列表达式:入学成绩+400
SELECT st_id, st_nm, st_score, st_score+400 AS new_score FROM student

--操作6.4:使用AS关键字为dept表中属性指定列名:系号、系名、系主任、联系电话
SELECT dp_id AS 系号, dp_nm AS 系名, dp_drt AS 系主任, dp_tel AS 联系电话 FROM dept

--操作6.5:显示所有学生的学号、姓名、性别和入学年份
SELECT st_id, st_nm, st_sex, DATEPART(yy,st_birth) AS 入学年份 FROM student

--操作6.6:显示所有学生学号、姓名、性别和班级(学号前6位)
SELECT st_id, st_nm, st_sex, LEFT(st_id, 6) AS 班级 FROM student

--操作6.7:显示所有学生班级
SELECT DISTINCT LEFT(st_id,6) AS 班级 FROM student

--操作6.8:显示前5条学生记录信息
SELECT TOP 5 * FROM student

--操作6.9:显示前25%条学生记录信息
SELECT TOP 25 PERCENT * FROM student

DATEPART( yy, st_date ) 年份

7.3 6.5 7.9

LEFT(id, 6) 学号前6位

6.6 7.4

<> 不等于

7.5

LIKE ‘[王-张]%’ 王姓到张姓

7.13

BETWEEN ‘1981-7-1’ AND ‘1999-6-30’

7.14

IN (‘11’, ‘12’,‘13’)

7.16

WHERE Left(st_nm,1) IN (‘张’,‘王’,‘李’,‘赵’)

7.17

-------------------------------------------------7.条件查询-------------------------------------------------
--操作7.1:查询dept表中系号为11的院系信息
SELECT * FROM dept WHERE dp_id = '11'

--操作7.2:查询student表中11系的学生学号、姓名、性别和所在系编号
SELECT st_id, st_nm, st_sex, st_dpid FROM student WHERE st_dpid = '11'

--操作7.3:查询student表中2008年及以后入学的学生信息
SELECT * FROM student WHERE DATEPART( yy, st_date )>= 2008

--操作7.4:在查询student表080808班学生的学号、姓名、性别和入学成绩
SELECT st_id, st_nm, st_sex, st_score FROM student WHERE Left(st_id,6)='080808'

--操作7.5:查询student表中非11系的学生信息
SELECT * FROM student WHERE  st_dpid <> '11'

--操作7.6:查询选修了1002号课程且成绩在60以下的学生学号
SELECT st_id FROM st_course WHERE (cs_id='1002') AND (score<60)

--操作7.7:查询2007年入学的11系所有男生信息
SELECT * FROM student WHERE DATEPART(yy,st_date)=2007 AND st_dpid='11' AND st_sex='男'

--操作7.8:查询11系和12系的学生信息
SELECT * FROM student WHERE st_dpid='11' OR st_dpid='12'

--操作7.9:查询11系和12系所有2007年入学的学生信息
SELECT * FROM  student WHERE (st_dpid='11' OR st_dpid='12') AND DATEPART(yy,st_date)=2007

--操作7.10:查询所有“计算机”开头的课程信息
SELECT * FROM course WHERE cs_nm LIKE '计算机%'

--操作7.11:查询所有由三个字组成的“王”姓学生信息
SELECT * FROM student WHERE st_nm LIKE '王__'

--操作7.12:查询所有课程名中包含“信息”的课程信息
SELECT * FROM course WHERE cs_nm LIKE '%信息%'

--操作7.13:查询学生姓名介于王姓到张姓的信息
SELECT * FROM student WHERE st_nm LIKE '[王-张]%'

--操作7.14:查询在1989.7.1到1990.6.30之间出生的学生信息
SELECT st_id, st_nm, st_sex, st_birth FROM student WHERE st_birth BETWEEN '1981-7-1' AND '1999-6-30'

--操作7.15:查询选修了1001号课程且成绩在60到80之间的学生选课信息
SELECT * FROM st_course WHERE cs_id='1001' AND (score BETWEEN 60 AND 80)

--操作7.16:查询11系、12系、13系的学生信息
SELECT * FROM student WHERE st_dpid IN ('11', '12','13')

--操作7.17:查询所有张,王,李,赵姓的学生的学号、姓名、性别
SELECT st_id, st_nm, st_sex FROM student WHERE Left(st_nm,1) IN ('张','王','李','赵')

--操作7.18:查询所有生源为非空的学生信息
SELECT * FROM  student WHERE  st_from IS NOT NULL

--操作7.19:查询选修了1001号课程且成绩为空的学生选课信息
SELECT * FROM st_course WHERE cs_id='1001' AND score IS NULL

order 字段 by (asc desc)

select * into 表 from 保存新表

-------------------------------------------------8.查询排序与查询结果存储-------------------------------------------------
--操作8.1:查询课程信息,按课程名称降序排序
SELECT * FROM course ORDER BY cs_nm DESC

--操作8.2:查询选修了1001号课程成绩非空的学生学号和成绩,并按成绩降序排序
SELECT st_id, score FROM st_course WHERE cs_id='1001' AND score IS NOT NULL ORDER BY score DESC

--操作8.3:查询11系学生学号、姓名和年龄,按年龄升序排序
SELECT st_id,st_nm,DATEPART(yy,GETDATE( ))-DATEPART(yy,st_birth) AS age FROM  student ORDER BY age ASC

--操作8.4:查询学生信息,按姓名升序排序,再按系号降序排序
SELECT * FROM student ORDER BY st_nm, st_dpid DESC

--操作8.5:创建学生表副本student01,仅保留学生学号、姓名和性别
SELECT st_id, st_nm, st_sex INTO  student01  FROM  student

--操作8.6:查询陕西籍学生,将结果保存在新表st_shanxi
SELECT * INTO st_shanxi FROM  student WHERE  st_from='陕西省'

--操作8.7:查询选修了1001号课程学生的选课信息,按学号升序排序,将结果保存在新表slt1001
SELECT * INTO slt1001 FROM st_course WHERE cs_id='1001' ORDER BY st_id

-------------------------------------------------9.查询统计与汇总-------------------------------------------------
–操作9.1:查询课程总数
SELECT COUNT( * ) FROM course

–操作9.2:查询选修1001号课程的学生人数
SELECT COUNT(st_id) FROM st_course Where cs_id = ‘1001’

–操作9.3:查询被选修课程的数量
SELECT COUNT( DISTINCT cs_id ) FROM st_course

–操作9.4:查询选修070101班学生的平均入学成绩
SELECT AVG(st_score) FROM student WHERE LEFT(st_id,6)=‘070101’

–操作9.5:查询070101001号学生选修课程的数量、总分以及平均分
SELECT COUNT(cs_id) AS 课程数量,SUM(score) AS 总分,AVG(score) AS 平均分
FROM st_course WHERE st_id=‘070101001’

–操作9.6:查询选修1001号课程的学生人数、最高分、最低分和平均分
SELECT COUNT(*) AS 学生人数, MAX(score) AS 最高分, MIN(score) AS 最低分, AVG (score) AS 平均分
FROM st_course WHERE cs_id=‘1001’

–操作9.7:求各个课程号和相应的选课人数
SELECT cs_id, COUNT(st_id) FROM st_course GROUP BY cs_id

–操作9.8:统计各班人数
SELECT LEFT(st_id,6) AS 班级, COUNT(st_id) AS 人数 FROM student GROUP BY LEFT(st_id,6)

–操作9.9:依次按系号、班级,对学生进行分类统计人数、入学平均分
SELECT st_dpid AS 系号, LEFT(st_id,6) AS 班级, COUNT(st_nm) AS 人数, AVG(st_score) AS 均分
FROM student GROUP BY LEFT(st_id,6), st_dpid

–操作9.10:查询选修了均分在75以上的课程号及均分
SELECT cs_id AS 课程编号, AVG(score) AS 均分 FROM st_course GROUP BY cs_id HAVING AVG(score)>75

–操作9.11:查询选修了2门以上课程的学生学号
SELECT st_id FROM st_course GROUP BY st_id HAVING COUNT(*)>2

-------------------------------------------------10.连接查询-------------------------------------------------
–操作10.1:用连接查询学生学号、姓名、性别及其所选课程编号
SELECT a.st_id, st_nm, st_sex, cs_id FROM student a, st_course b
WHERE a.st_id = b.st_id ORDER BY a.st_id
–注:除了字段可以起别名,表也可以,代码中的a, b分别为student和st_course表的别名

–操作10.2:用JOIN形式连接查询学生学号、姓名、性别及其所选课程编号
SELECT a.st_id, st_nm, st_sex, cs_id FROM student a INNER JOIN st_course b ON a.st_id = b.st_id
ORDER BY a.st_id

–操作10.3:查询学生学号、姓名及其所选课程名称及成绩
–方法1
SELECT a.st_id, st_nm, cs_nm, score FROM student a, st_course b, course c
WHERE a.st_id = b.st_id AND b.cs_id = c.cs_id ORDER BY a.st_id
–方法2
SELECT a.st_id, st_nm, cs_nm, score FROM st_course a INNER JOIN student b ON a.st_id = b.st_id
INNER JOIN course c ON a.cs_id = c.cs_id ORDER BY b.st_id

–操作10.4:查询选修了1002课程的学生学号、姓名及1002课程成绩
SELECT a.st_id, st_nm, score FROM student a,st_course b
WHERE a.st_id = b.st_id AND b.cs_id = ‘1002’ ORDER BY b.st_id

–操作10.5:查询选修了“数据结构”课程的学生学号、姓名及课程成绩
SELECT a.st_id, st_nm, score FROM student a, st_course b, course c
WHERE a.st_id=b.st_id AND b.cs_id=c.cs_id AND c.cs_nm=‘数据结构’ ORDER BY a.st_id

–操作10.6:用左外连接查询没有选修任何课程的学生学号、姓名
SELECT a.st_id, st_nm, score FROM student a LEFT OUTER JOIN st_course b ON a.st_id = b.st_id
WHERE b.cs_id IS NULL ORDER BY b.st_id

–操作10.7:用右外连接查询选修各个课程的学生学号
SELECT b.cs_id, a.st_id FROM st_course a Right OUTER JOIN course b ON a.cs_id = b.cs_id ORDER BY b.cs_id

6.第六次上机

view 视图

–(1)图形界面的方法略
CREATE VIEW 订单0401
AS
SELECT dbo.订单 . 订单编号 , d b o . 客户 .订单编号, dbo.客户 .订单编号,dbo.客户.客户姓名, dbo.图书信息 . 图书名称 , d b o . 订单明细 .图书名称, dbo.订单明细 .图书名称,dbo.订单明细.数量, dbo.图书信息 . 价格 , d b o . 订单 .价格, dbo.订单 .价格,dbo.订单.金额,
dbo.订单 . 订货日期 F R O M d b o . 订单 .订货日期 FROM dbo.订单 .订货日期FROMdbo.订单 INNER JOIN
dbo.订单明细$ ON dbo.订单 . 订单编号 = d b o . 订单明细 .订单编号 = dbo.订单明细 .订单编号=dbo.订单明细.订单编号 INNER JOIN
dbo.图书信息$ ON dbo.订单明细 . 图书编号 = d b o . 图书信息 .图书编号 = dbo.图书信息 .图书编号=dbo.图书信息.ISBN编号 INNER JOIN
dbo.客户$ ON dbo.订单 . 客户编号 = d b o . 客户 .客户编号 = dbo.客户 .客户编号=dbo.客户.客户编号

–(2)
create view 职员0402
as
select 经办人,count(*) as 订单数量,sum(金额) as 订单总金额
from 订单$
group by 经办人

–(3)
create view view_购物车_05
as
select 购物车ID,图书编号,客户编号,数量,单价
from 购物车$

select sum(数量) as 总数量, sum(单价*数量) as 总金额
from view_购物车_05

–(4)
delete from view_购物车_05 where 购物车ID=1

–(5)
create nonclustered index IX_图书分类_分类名称 on 图书分类$(图书分类名称)

–(6)
create index IX_eBookName on 图书信息$(图书名称)

7. 第七次上机

procedure 存储过程

–(1)编写代码,在eBookShopDB数据库中创建一个命名为“getCustomer”的存储过程,用于根据客户编号或客户姓名查询客户的信息。
create proc getCustomer @num nvarchar(255),@name nvarchar(255)
as
select * from 客户$ where 客户编号=@num or 客户姓名=@name
go
–测试
exec getCustomer NULL,‘安静’
go

–(2)编写代码,在eBookShopDB数据库中创建一个命名为“getOrderInfo”的存储过程,实现根据订单编号获取该订单信息的功能。
create proc getOrderInfo @num nvarchar(255)
as
select * from 订单$ where 订单编号=@num
go
–测试
exec getOrderInfo ‘O20121234’
go

–(3)编写代码,在eBookShopDB数据库中创建一个命名为“insertPayment”的存储过程,实现“付款方式”数据表的插入记录操作。
create proc insertPayment @id float, @type nvarchar(255), @remark nvarchar(255)=NULL
as
insert into 付款方式$ values(@id, @type, @remark)
go
–测试
exec insertPayment 13, ‘面对面’
go

trigger 触发器

–(4)编写代码,在eBookShopDB数据库中创建一个命名为“product_order”的触发器,当客户向“订单”数据表中修改记录时,自动显示被修改的原纪录和新记录。
create trigger product_order on 订单$
after update
as
begin
select * from deleted
union
select * from inserted
end
go
–测试
update 订单$ set 其他费用=40, 收货人地址=‘广州’ where 订单编号=‘O20121234’
go

–(5)编写代码,在eBookShopDB数据库中创建一个命名为“trigger05”的触发器,用于防止数据库中的数据表被删除。
create trigger trigger05 on database
after drop_table
as
begin
print ‘该数据库中的数据表不能被删除’
rollback
end
go
–测试
drop table 订单$
go

8.第八次上机

程序

–(1)编写程序,输入一个数,判定该数是否为素数,如果是素数,则输出“该数是素数”的信息,否则输出“该数不是素数”的信息。(输出50—100之间所有素数)
declare @x int,@i int
set @x=50
while @x<=100
begin
set @i=2
while @i<=sqrt(@x)
begin
if @x%@i=0
break
else
set @i=@i+1
end
if @i>sqrt(@x)
print str(@x)+‘是素数’
else
print str(@x)+‘不是素数’
set @x=@x+1
end

–(2)设纸的厚度为0.5毫米,将纸对折,再对折…,求至少对折多少次,纸张的厚度能达到珠穆朗玛峰的高度8844米。
Declare @h decimal(10,1),@n int
select @h=0.5,@n=0
while @h<8844000 --运算部分
begin
set @h=@h*2
set @n=@n+1
end
print ‘对折’+ltrim(str(@n))+‘次后可以超过珠峰的高度’ --输出部分
go

–(3)编写代码,创建1个自定义函数“getBooks”,用于根据给定的读者姓名获取其借阅的图书信息(ISBN编号、图书名称、出版社名称、作者、单价),并调用。
Create Function getBooks(@readerName varchar(20))
Returns Table
As
Return Select 图书信息.ISBN编号,图书名称,出版社名称,作者,价格
From 图书借阅 join 借书证 on 图书借阅.借书证编号=借书证.借书证编号
join 藏书信息 on 图书借阅.图书编号=藏书信息.图书编号
join 图书信息 on 藏书信息.ISBN编号=图书信息.ISBN编号
join 出版社 on 图书信息.出版社=出版社.出版社ID where 姓名=@readerName
go
–调用:
Select * From dbo.getBooks(‘石磊’)
go

–(4)编写代码,创建1个自定义函数“getBorrowInformation”,用于根据给定的读者姓名获取其借阅的图书数量。如果没有借阅,输出“XX未曾借书”,否则,输出“XX借了X本书”。
Create Function getBorrowInformation(@readerName varchar(20))
Returns int
As
Begin
Declare @bookNum int
Select @bookNum=count(*) From 图书借阅 join 借书证 on 图书借阅.借书证编号=借书证.借书证编号
where 姓名=@readerName
Return @bookNum
End
go
–调用:
declare @name varchar(20),@bn int
set @name=‘石磊’
set @bn=dbo.getBorrowInformation(@name)
if @bn>0
print @name+‘借了’+ltrim(str(@bn))+‘本书’
else
print @name+‘未曾借书’
go

–注:以上问题都有多种实现形式,不必拘泥于我的代码。以上内容仅供参考。希望每位同学都能独立思考!

  • 2
    点赞
  • 13
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值