一、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
–注：以上问题都有多种实现形式，不必拘泥于我的代码。以上内容仅供参考。希望每位同学都能独立思考！
3276
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
