此文章仅限刷题、练习、期末复习等使用,禁止抄袭!!!否则后果自负!
实验报告一
一、实验目的:
1. 掌握数据表的特点。 2. 熟练掌握在SSMS图形界面中及使用T-SQL语句创建、编辑及删除数据表结构的方法。 3. 熟悉数据表记录及使用特点。 4. 熟练掌握在SSMS图形界面中及使用T-SQL语句添加、删除、修改表记录的方法。 5. 掌握主键约束、外键约束、check约束、唯一约束等完整性约束的用法。
二、实验环境: WINDOWS+MS SQL SERVER
三、实验准备:
掌握的知识要点: 1. 表的结构特点。 2. SQL Server的基本数据类型。 3. SSMS中数据表的创建和管理方法。 4. 熟练掌握在SSMS图形界面中及使用T-SQL语句添加、删除、修改表数据的方法。 5. 熟悉各类约束及默认值规则,掌握T-SQL语句实现完整性约束命令。
四、实验内容:
(一)创建医院门诊系统数据库(HISDB)
- 用SQL(命令)或菜单方式创建Doctor表结构。
- 用SQL(命令)或菜单方式创建patient表结构。
- 用SQL(命令)或菜单方式创建curefee表结构。
- 用SQL(命令)或菜单方式在Doctor表中添加两列 sex char(2),age tinyint。
- 为Doctor表添加默认值约束DF_SEX,默认值为“男”。
- 删除Doctor表约束DF_SEX,删除Doctor表列age
(二)创建学生管理数据库
- 创建学生管理数据库”XSGL”,并在其中创建以下3张表。
表1-1 [student]
表结构
字段名称 | 字段类型 | 字段宽度 | 说明 | 备注 |
---|---|---|---|---|
Sno | Char | 9 | 学生学号 | 主键 |
Sname | Char | 6 | 姓名 | 非空 |
Ssex | Char | 2 | 性别 | 允许空 |
Sage | Int | - | 年龄 | 允许空 |
Sdept | Varchar | 20 | 所在院系 | 允许空 |
表1-2 [course]
表结构
字段名称 | 类型 | 字段宽度 | 说明 | 备注 |
---|---|---|---|---|
Cno | Char | 4 | 课程编号 | 主键 |
Cname | Varchar | 20 | 课程名称 | 非空 |
Cpno | Char | 4 | 先修课程号 | 外键,参照本表Cno列取值(自引用) |
Ccredit | Int | - | 课程学分 | 允许空 |
表1-3 [sc]
表结构
字段名称 | 类型 | 字段宽度 | 说明 | 备注 |
---|---|---|---|---|
sno | Char | 9 | 学生学号 | 外键,参照student表Sno;复合主键 |
Cno | char | 4 | 课程编号 | 外键,参照course表Cno;复合主键 |
Grade | float | - | 成绩 | 允许空 |
- 根据上述三张表结构,分析三张表之间的外键约束关系,并建立相关外键约束,并在该数据库的“数据库关系图”节点中,新建关系图,将这三张表添加进去,观察系统自动生成的三表关系图。
- 限定grade字段的取值为大于等于0的数;限定所在系sdept取值只能是“中医系”、“中药系”、“信息系”、“人文系”、“经济系”。
Student 表数据
sno | sname | ssex | sage | sdept |
---|---|---|---|---|
S0001 | 安林 | 女 | 20 | 中医系 |
S0002 | 路霖 | 女 | 20 | 中医系 |
S0003 | 李森 | 男 | 19 | 中药系 |
S0004 | 李洋 | 男 | 19 | 信息系 |
S0005 | 杨青 | 男 | 19 | 信息系 |
Course 表数据
Cno | Cname | Cpno | ccredit |
---|---|---|---|
C001 | 计算机基础 | 3 | |
C002 | C语言 | C001 | 4 |
C003 | 数据结构 | C002 | 4 |
C004 | 数据库原理 | 5 |
Sc 表数据
sno | Cno | Grade |
---|---|---|
S0001 | C001 | 89 |
S0002 | C001 | 78 |
S0003 | C001 | 88 |
S0004 | C003 | 78 |
S0004 | C004 | 87 |
S0005 | C003 | 88 |
S0005 | C004 | 97 |
- 分表向3张表中添加如上几条记录,通过主键表和外键表记录的插入、修改和删除操作,体会外键约束中的“拒绝”“级联”“设置空”含义。(本题要求截取3表的记录图,还要求截取主键表和外键表采取“拒绝”方式的删除规则效果图)
- 请将sc表删除,再用命令方式进行重新建立sc表,并注意添加相应的主键、外键和check约束。
五、参考答案
-- 1.创建Docter表
if not exists(select * from sys.databases where name = 'HISDB')
create database HISDB;
else
print 'HISDB数据库已创建';
go
use HISDB
-- 创建医生表
if not exists(select * from sys.tables where name = 'doctor')
create table doctor(
ID int primary key identity(1,1),
Name nvarchar(10) not null,
)
else
print 'doctor表已创建'
-- 2.用SQL(命令)或菜单方式创建patient表结构
-- 创建患者表
if not exists(select * from sys.tables where name = 'patient')
create table patient(
ID int primary key identity(1,1),
Name nvarchar(10),
)
else
print '患者表已创建';
go
-- 3.用SQL(命令)或菜单方式创建curefee表结构
-- 创建治愈费用表
if not exists(select * from sys.tables where name = 'curefee')
BEGIN
create table curefee(
ID int primary key identity(1,1),
Patient_id INT NOT NULL,
Amount INT NOT NULL,
constraint FK_Curefee_Patient FOREIGN KEY (Patient_id) references patient(ID)
)
print '治愈费用表创建成功!'
END
else
print '治愈费用表已创建'
go
-- 4.用SQL(命令)或菜单方式在Doctor表中添加两列 sex char(2),age tinyint。
-- 给医生表添加列
if not exists(select * from sys.columns
where object_id = OBJECT_ID('doctor') AND name IN ('sex', 'age'))
BEGIN
Alter table doctor
Add
sex char(2), -- 性别,存储2个字符(如 '男'/'女'/'M'/'F')
age tinyint; -- 年龄,使用 TINYINT 类型(0~255)
print '成功添加sex和age列!'
END
else
print 'doctor表已添加过sex和age'
go
-- 5.为Doctor表添加默认值约束DF_SEX,默认值为“男”。
-- 给医生表添加约束
if not exists(select * from sys.objects where name = 'DF_SEX' AND type = 'D')
BEGIN
Alter table doctor
ADD constraint DF_SEX default '男' for sex;
print '成功给医生表添加了性别约束!'
END
else
print '默认值DF_SEX约束已创建'
go
-- 6.删除Doctor表约束DF_SEX,删除Doctor表列age
-- 1.删除约束
if exists(select * from sys.objects where name = 'DF_SEX' AND type = 'D')
BEGIN
Alter table doctor
drop constraint DF_SEX
print '检测到约束,已删除';
END
else
print '没有对应约束!'
-- 2.删除列
if exists(select * from sys.columns
where object_id = OBJECT_ID('doctor') AND name = 'age')
BEGIN
Alter table doctor
Drop column age
print '检测到对应列,已删除age列'
END
else
print 'doctor表中没有age列!'
go
-- 7.创建学生管理数据库”XSGL”,并在其中创建以下3张表
create database XSGL;
go
use XSGL
if not exists(select * from sys.tables where name = 'student')
BEGIN
create table student(
Sno char(9) primary key, -- 学生学号
Sname char(6) not null, -- 姓名
Ssex char(2), -- 性别
Sage int, -- 年龄
Sdept varchar(20), -- 所在院系
)
print '已创建学生表'
END
else
print '学生表已存在'
if not exists(select * from sys.tables where name = 'course')
BEGIN
create table course(
Cno char(4) primary key, -- 课程编号
Cname varchar(20) not null, -- 课程名称
Cpno char(4), -- 先修课程号
Ccredit int, -- 课程学分
)
print '课程表已创建'
END
else
print '课程表已存在'
if not exists(select * from sys.tables where name = 'sc')
BEGIN
create table sc(
sno char(9), -- 学生学号
Cno char(4), -- 课程编号
Grade float, -- 成绩
constraint FK_Sc_Key primary key(sno,Cno), -- 主键
)
print '成绩表已创建'
END
else
print '成绩表已存在'
-- 8.根据上述三张表结构,分析三张表之间的外键约束关系,并建立相关外键约束,
-- 并在该数据库的“数据库关系图”节点中,新建关系图,将这三张表添加进去,
-- 观察系统自动生成的三表关系图。
Alter table course
Add constraint FK_CPNO_CNO Foreign key (Cpno) references course(Cno);
print '成功给课程表添加了约束!'
Alter table sc
Add constraint FK_Sno_Student foreign key (sno) references student(Sno);
print '成功给成绩表添加了第一个约束!'
Alter table sc
Add constraint FK_Cno_Course foreign key (Cno) references course(Cno);
print '成功给成绩表添加了第二个约束!'
-- 9.1 限定grade字段的取值为大于等于0的数;
if not exists(select * from sys.objects where name = 'FK_Sc_Grade')
BEGIN
Alter table sc
Add constraint FK_Sc_Grade check(Grade >= 0)
print '成功创建Grade约束!'
END
else
print 'Grade约束已存在'
-- 9.2 限定所在系sdept取值只能是“中医系”、“中药系”、“信息系”、“人文系”、“经济系”。
if not exists(select * from sys.objects where name = 'FK_Sdept_Student')
BEGIN
Alter table student
Add constraint FK_Sdept_Student
check(sdept in ('中医系', '中药系', '信息系', '人文系', '经济系'))
print '成功创建Sdept约束!'
END
else
print 'Sdept约束已存在'
-- 10.1 给学生表插入数据
insert into student (Sno, Sname,Ssex,Sage,Sdept) values
('S0001','安林','女',20,'中医系'),
('S0002','路霖','女',20,'中医系'),
('S0003','李森','男',19,'中药系'),
('S0004','李洋','男',19,'信息系'),
('S0005','杨青','男',19,'信息系');
select * from student;
-- 10.2 给课程表插入数据
insert into course (Cno,Cname,Cpno,Ccredit) values
('C001','计算机基础',null,3),
('C002','C语言','C001',4),
('C003','数据结构','C002',4),
('C004','数据库原理',null,5);
select * from course;
-- 10.3 给成绩表插入数据
insert into sc (sno,Cno,Grade) values
('S0001','C001',89),
('S0002','C001',78),
('S0003','C001',88),
('S0004','C003',87),
('S0004','C004',78),
('S0005','C003',88),
('S0005','C004',97);
select * from sc;
-- 10.4 拒绝
-- 因为课程表的先修课程号必须是已有的课程编号
-- 查看目前的表数据:
select * from student;
select * from sc;
if exists(select * from sys.objects where name = 'FK_Sno_Student')
alter table sc
Drop constraint FK_Sno_Student;
print 'FK_Sno_Student约束已删除'
Alter table sc
Add constraint FK_Sno_Student foreign key (sno)
references student(Sno) on delete no Action;
print '成绩表的级联约束已建立'
Delete from student where Sno = 'S0005';
-- 再次查看表数据,确定数据未删除
select * from student;
select * from sc;
-- 10.5 级联
-- 先声明级联操作
-- 拿成绩表和学生的关系举例子
-- 先把之前学生表的约束删除
select * from student;
select * from sc;
if exists(select * from sys.objects where name = 'FK_Sno_Student')
alter table sc
Drop constraint FK_Sno_Student;
print 'FK_Sno_Student约束已删除'
Alter table sc
Add constraint FK_Sno_Student foreign key (sno)
references student(Sno) on delete cascade;
print '成绩表的级联约束已建立'
Delete from student where Sno = 'S0005';
-- 再次查询表中数据
select * from student;
select * from sc;
-- 发现和S0005有关的成绩信息都删除了。
-- 10.6 设置空
-- 先把10.5 中更改的数据还原回来。
-- 先更改sc表的sno为非主键,然后再允许
-- 查看初始数据
select * from student;
select * from sc;
Alter table sc
Drop constraint FK_Sc_Key;
print '已删除成绩表的主键约束!'
Alter table sc
Alter Column sno char(9) NULL;
print '已允许sno为空!'
if exists(select * from sys.objects where name = 'FK_Sno_Student')
alter table sc
Drop constraint FK_Sno_Student;
print 'FK_Sno_Student约束已删除'
Alter table sc
Add constraint FK_Sno_Student foreign key (sno)
references student(Sno) on delete set null;
print '课程表的设置空约束已建立'
-- 现在删除学生信息
delete from student where Sno = 'S0001';
-- 再次查询表中数据
select * from student;
select * from sc;
-- 11. 删除sc表 重新创建
Drop table sc;
print 'sc表已删除!'
if not exists(select * from sys.tables where name = 'sc')
BEGIN
create table sc(
sno char(9), -- 学生学号
Cno char(4), -- 课程编号
Grade float, -- 成绩
constraint FK_Sc_Key primary key(sno,Cno), -- 主键
constraint FK_Sno_Student foreign key (sno) references student(Sno),
-- 参照学生表学号取值
constraint FK_Cno_Course foreign key (Cno) references course(Cno),
-- 参照课程表课程号取值
constraint FK_Sc_Grade check(Grade >= 0),
)
print '成绩表已创建'
END
else
print '成绩表已存在'
实验报告二
一、实验目的:
1. 熟练掌握SELECT语句的基本语法和查询条件表示方法。 2. 熟练掌握查询条件各类和表示方法。 3. 掌握连接查询、嵌套查询的表示及使用。 4. 了解集合查询的表示及使用。
二、实验环境: WINDOWS+MS SQL SERVER
三、实验准备:
掌握的知识要点: 1. SELECT语句的基本语法和查询条件表示方法。 2. 查询条件表示种类和表示方法。 3. 连接查询、嵌套查询、集合查询的表示及使用。
四、实验内容:
基于实验二所建的student、course和sc。
按照出生年份升序显示所有的学生的学号、姓名、性别、出生年份及院系信息。(提示:出生年份为计算列)
统计各院系学生人数。
按照课程号、成绩降序显示课程成绩在70到80之间的学生学号、课程号及成绩。
查询选修课程门数大于2的各个学生的学号和选修课程门数。
查询所有学生的数据库原理与应用课程成绩信息,展示学号、姓名、所在系、课程名称和成绩。
按课程号降序显示选修各个课程的总人数、最高分、最低分和平均分。
显示平均成绩大于“S0001”学生平均成绩的各个学生的学号、平均成绩。
查询各门课程的选修及格人数、及格比率。
查询选修课头数最多的学生学号及选修课头数最少的学生学号。
显示各个院系男女生人数,其中在结果集中列标题分别指定“院系名称、男生人数、女生人数”。
列出有两门以上课程(含两门)不及格的学生的学号及该学生的平均成绩。
以“查询选修各门课程的选修及格人数、及格比率”建立一个视图,并测试该视图的查询结果。
查询信息系专业每个学生的学号、姓名、选修课程的平均成绩。
查询同时选修了c003和c004号课程的学生学号、姓名。
五、参考答案
use XSGL;
-- 先插入新的数据以满足进行查询
-- 给学生表插入新数据
INSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES
('S0006', '王芳', '女', 21, '中医系'),
('S0007', '张伟', '男', 20, '中药系'),
('S0008', '刘洋', '男', 22, '信息系'),
('S0009', '陈晨', '女', 19, '中医系'),
('S0010', '赵敏', '女', 20, '中药系'),
('S0011', '周杰', '男', 21, '信息系'),
('S0012', '吴婷', '女', 19, '中医系'),
('S0013', '郑浩', '男', 20, '中药系'),
('S0014', '孙莉', '女', 22, '信息系'),
('S0015', '钱坤', '男', 21, '中医系'),
('S0016', '冯雪', '女', 20, '中药系'),
('S0017', '韩梅', '女', 19, '信息系'),
('S0018', '林涛', '男', 22, '中医系'),
('S0019', '徐静', '女', 21, '中药系'),
('S0020', '黄强', '男', 20, '信息系'),
('S0021', '欧阳明', '男', 20, '中医系'),
('S0022', '司马燕', '女', 21, '中药系'),
('S0023', '诸葛亮', '男', 22, '信息系'),
('S0024', '上官云', '女', 19, '中医系'),
('S0025', '东方红', '女', 20, '中药系'),
('S0026', '令狐冲', '男', 21, '信息系'),
('S0027', '慕容雪', '女', 19, '中医系'),
('S0028', '公孙策', '男', 20, '中药系'),
('S0029', '尉迟恭', '男', 22, '信息系'),
('S0030', '独孤剑', '男', 21, '中医系');
print '成功给学生表添加新数据'
go
-- 给课程表添加新数据
INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES
('C005', '中医基础理论', null, 4),
('C006', '中药学', 'C005', 5),
('C007', '方剂学', 'C006', 4),
('C008', '针灸学', 'C005', 3),
('C009', '计算机网络', 'C001', 4),
('C010', '操作系统', 'C001', 4),
('C011', '软件工程', 'C003', 3),
('C012', '人工智能', 'C004', 5);
print '成功给课程表添加新数据'
go
-- 给成绩表添加新数据
INSERT INTO SC (Sno, Cno, Grade) VALUES
('S0001', 'C002', 76),
('S0001', 'C003', 85),
('S0002', 'C002', 82),
('S0002', 'C005', 91),
('S0003', 'C006', 88),
('S0004', 'C002', 79),
('S0005', 'C002', 93),
('S0006', 'C001', 87),
('S0006', 'C005', 84),
('S0007', 'C006', 90),
('S0007', 'C008', 82),
('S0008', 'C001', 78),
('S0008', 'C009', 85),
('S0009', 'C005', 89),
('S0009', 'C008', 76),
('S0010', 'C006', 94),
('S0010', 'C007', 83),
('S0011', 'C003', 81),
('S0011', 'C010', 87),
('S0012', 'C005', 92),
('S0012', 'C008', 85),
('S0013', 'C006', 78),
('S0013', 'C007', 80),
('S0014', 'C004', 89),
('S0014', 'C010', 91),
('S0015', 'C005', 84),
('S0015', 'C008', 82),
('S0016', 'C006', 90),
('S0016', 'C007', 88),
('S0017', 'C001', 85),
('S0017', 'C009', 87),
('S0018', 'C005', 83),
('S0018', 'C008', 79),
('S0019', 'C006', 92),
('S0019', 'C007', 86),
('S0020', 'C004', 94),
('S0020', 'C010', 90),
('S0020', 'C002', 14),
('S0020', 'C001', 9);
print '成功给成绩表添加新数据'
go
select * from student;
select * from sc;
select * from course;
-- 1.按照出生年份升序显示所有的学生的学号、姓名、性别、出生年份及院系信息。
select Sno,Sname,Ssex,Sage,Sdept,(2025-Sage) as BirthYear from student
order by BirthYear ASC;
-- 2.统计各院系学生人数。
-- 加入数据
INSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES
('S0006', '王明', '男', 18, '中医系'),
('S0007', '赵丽', '女', 21, '中药系'),
('S0008', '张伟', '男', 22, '信息系'),
('S0009', '陈静', '女', 20, '人文系'),
('S0010', '刘强', '男', 19, '经济系');
select Sdept,COUNT(*) '学生人数' from student
group by Sdept;
-- 3.按照课程号、成绩降序显示课程成绩在70到80之间的学生学号、课程号及成绩。
select sno,Cno,Grade from sc where Grade Between 70 and 80
order by Cno,Grade DESC;
-- 4.查询选修课程门数大于2的各个学生的学号和选修课程门数。
-- 添加数据
insert into sc (Sno,Cno,Grade) Values
('S0002','C003',50),
('S0004','C001', 85),
('S0004','C002', 35),
('S0006','C002', 90),
('S0007','C002', 32),
('S0008','C003', 80),
('S0009','C003', 58),
('S0010','C004', 27),
('S0002','C004', 40),
('S0002','C002', 77),
('S0001','C002', 90),
('S0001','C003', 50),
('S0001','C004', 38);
select sno,COUNT(*) as '选修门数' from sc
group by sno
having COUNT(*) > 2;
-- 5.查询所有学生的数据库原理与应用课程成绩信息,
-- 展示学号、姓名、所在系、课程名称和成绩。
select * from course;
select student.Sno,Sname,Sdept,'数据库原理与应用课程' as '课程名称',sc.Grade
from student
join sc on sc.sno = student.Sno
where sc.Cno = 'C004';
-- 6.按课程号降序显示选修各个课程的总人数、最高分、最低分和平均分。
select Cno,COUNT(*) as '选修人数',
MAX(Grade) as '最高分',
MIN(Grade) as '最低分',
AVG(Grade) as '平均分'
from sc
group by Cno
order by Cno DESC;
-- 7.显示平均成绩大于“S0001”学生平均成绩的各个学生的学号、平均成绩。
select sno,AVG(Grade) as '平均成绩'
from sc
group by sno
having AVG(Grade) >
(select AVG(Grade) from sc where sno = 'S0001');
-- 8.查询各门课程的选修及格人数、及格比率。
select Cno,
COUNT(CASE WHEN Grade>=60 then 1 END) as '及格人数',
CAST(ROUND(CAST(COUNT(CASE WHEN Grade>=60 then 1 END) as float) /
COUNT(*) *100 ,2) as varchar(10)) + '%' as '及格率'
from sc
Group by Cno
-- 9.查询选修课头数最多的学生学号及选修课头数最少的学生学号。
select sno as '选课最多的学号' from sc
group by sno
having COUNT(*) =
(select MAX(ct) from (select COUNT(*) as ct from sc group by sno) as t)
select sno as '选课最少的学号' from sc
group by sno
having COUNT(*) =
(select MIN(ct) from (select COUNT(*) as ct from sc group by sno) as t)
-- 10.显示各个院系男女生人数,
-- 其中在结果集中列标题分别指定“院系名称、男生人数、女生人数”。
select Sdept as '院系名称',COUNT(CASE WHEN Ssex = '男' THEN 1 END) as '男生人数',
COUNT(CASE WHEN Ssex = '女' THEN 1 END) as '女生人数'
from student
group by Sdept;
-- 11.列出有两门以上课程(含两门)不及格的学生的学号及该学生的平均成绩。
select sno as '学号', AVG(Grade) as '平均成绩'
from sc
group by sno
having COUNT(CASE WHEN Grade < 60 THEN 1 END) >= 2;
go
-- 12.以“查询选修各门课程的选修及格人数、及格比率”建立一个视图,
-- 并测试该视图的查询结果。
CREATE VIEW CoursePassVIEW as
select Cno,
COUNT(CASE WHEN Grade>=60 then 1 END) as '及格人数',
CAST(ROUND(CAST(COUNT(CASE WHEN Grade>=60 then 1 END) as float) /
COUNT(*) *100 ,2) as varchar(10)) + '%' as '及格率'
from sc
Group by Cno
go
select * from CoursePassVIEW;
-- 13.查询信息系专业每个学生的学号、姓名、选修课程的平均成绩。
select sc.sno, student.Sname,AVG(Grade) as '平均成绩' from sc
join student on student.Sno = sc.sno
where student.Sdept = '信息系'
group by sc.sno, student.Sname
-- 14.查询同时选修了c003和c004号课程的学生学号、姓名。
select sc.sno, student.Sname
from sc
join student on student.Sno = sc.sno
WHERE sc.Cno IN ('C003', 'C004')
group by sc.sno, student.Sname
having COUNT(sc.Cno) = 2
六、小结
1.SQL Server 中 MAX() FROM...
子查询详解
1. 基本概念
MAX() FROM...
是一种标量子查询模式,用于从子查询结果中提取最大值,通常作为外部查询的比较条件或返回值。
2. 核心语法结构
SELECT MAX(column_name) FROM table_name [WHERE conditions]
3. 典型应用场景
3.1 作为条件值使用
-- 在WHERE子句中使用
SELECT *
FROM Orders
WHERE OrderDate = (SELECT MAX(OrderDate) FROM Orders);
-- 在HAVING子句中使用
SELECT department_id, AVG(salary)
FROM Employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT MAX(salary) FROM Interns);
3.2 作为计算字段使用
-- 在SELECT子句中使用
SELECT
product_id,
price,
(SELECT MAX(price) FROM Products) AS max_price
FROM Products;
4. 特殊用法
4.1 多级嵌套查询
-- 找出选课数量等于最大选课数量的学生
SELECT student_id
FROM Course_Selections
GROUP BY student_id
HAVING COUNT(*) = (
SELECT MAX(course_count)
FROM (
SELECT COUNT(*) AS course_count
FROM Course_Selections
GROUP BY student_id
) counts
);
4.2 分区最大值查询
-- 找出各部门最新入职的员工
SELECT e.*
FROM Employees e
WHERE hire_date = (
SELECT MAX(hire_date)
FROM Employees
WHERE department_id = e.department_id
);
2.SQL Server 中 COUNT(CASE WHEN...THEN...)
详解
1. 基本概念
COUNT(CASE WHEN...THEN...)
是一种条件计数模式,用于统计满足特定条件的记录数。
2. 核心语法结构
COUNT(CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END)
3. 典型应用场景
3.1 简单条件计数
-- 统计及格学生人数
SELECT
COUNT(CASE WHEN score >= 60 THEN 1 END) AS pass_count
FROM exam_results;
3.2 多条件分组统计
-- 统计各分数段人数
SELECT
COUNT(CASE WHEN score >= 90 THEN 1 END) AS excellent,
COUNT(CASE WHEN score >= 70 AND score < 90 THEN 1 END) AS good,
COUNT(CASE WHEN score >= 60 AND score < 70 THEN 1 END) AS pass,
COUNT(CASE WHEN score < 60 THEN 1 END) AS fail
FROM exam_results;
3.3 结合GROUP BY使用
-- 统计各部门男女员工数
SELECT
department,
COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count
FROM employees
GROUP BY department;
4. 特殊用法
4.1 与DISTINCT结合
-- 统计有及格成绩的不同学生人数
SELECT
COUNT(DISTINCT CASE WHEN score >= 60 THEN student_id END) AS pass_students
FROM exam_results;
4.2 嵌套使用
-- 统计各科及格率
SELECT
subject,
COUNT(*) AS total,
COUNT(CASE WHEN score >= 60 THEN 1 END) AS pass_count,
CAST(COUNT(CASE WHEN score >= 60 THEN 1 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) AS pass_rate
FROM exam_results
GROUP BY subject;
实验报告三
一、实训环境
MICROSOFT SQL SERVER
二、实验目的:
1. 掌握数据表的特点。
2. 熟练掌握在SSMS图形界面中及使用T-SQL语句创建、编辑及删除数据表结构的方法。
3. 熟悉数据表记录及使用特点。
4. 熟练掌握在SSMS图形界面中及使用T-SQL语句添加、删除、修改表记录的方法。
5. 掌握主键约束、外键约束、check约束、唯一约束等完整性约束的用法。
三、实训内容
(一) 案例训练1: 银行数据库系统 以下是一个模拟银行数据库系统的建库、建表原代码,观察每条命令的格式,在查询分析器中执行代码,并填写后附的实验过程记录,领悟数据库系统的DDL和DML功能。(每段代码按序分批执行,严禁将所有代码拷贝到系统中一次性运行!) 1.创建数据库 创建建库bankDB
CREATE DATABASE bankDB
ON
(
NAME='bankDB_data',
FILENAME='d:\bankDB_data.mdf',
SIZE=5mb,
FILEGROWTH=15%
)
LOG ON
(
NAME= 'bankDB_log',
FILENAME='d:\bankDB_log.ldf',
SIZE=5mb,
FILEGROWTH=15%
)
GO
2.创建新表
USE bankDB
GO
CREATE TABLE userInfo --用户信息表
(
customerID INT IDENTITY(1,1),
customerName CHAR(8) NOT NULL,
PID CHAR(18) NOT NULL,
telephone CHAR(20) NOT NULL,
address VARCHAR(50)
)
GO
CREATE TABLE cardInfo --银行卡信息表
(
cardID CHAR(19) NOT NULL,
curID VARCHAR(10) NOT NULL,
savingID INT NOT NULL,
openDate DATETIME NOT NULL,
openMoney MONEY NOT NULL,
balance MONEY NOT NULL,
pass CHAR(6) NOT NULL,
IsReportLoss BIT NOT NULL,
customerID INT NOT NULL
)
GO
CREATE TABLE tradeInfo --交易信息表
(
tradeDate DATETIME NOT NULL,
tradeType CHAR(4) NOT NULL,
cardID CHAR(19) NOT NULL,
tradeMoney MONEY NOT NULL,
remark TEXT
)
GO
CREATE TABLE Deposit --存款类型表
(
savingID INT IDENTITY(1,1),
savingName VARCHAR(20) NOT NULL,
descript VARCHAR(50)
)
GO
3.向各表添加约束
ALTER TABLE Deposit
ADD CONSTRAINT PK_savingID PRIMARY KEY(savingID)
GO
/* userInfo表的约束
customerID 顾客编号 自动编号(标识列),从1开始,主键
customerName 开户名 必填
PID 身份证号 必填,只能是18位或15位,身份证号唯一约束
telephone 联系电话 必填,格式为xxxx-xxxxxxx或手机号11位或xxx-xxxxxxxx
address 居住地址 可选输入
*/
ALTER TABLE userInfo
ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID),
CONSTRAINT CK_PID CHECK( len(PID)=18 or len(PID)=15 ),
CONSTRAINT UQ_PID UNIQUE(PID),
CONSTRAINT CK_telephone CHECK([telephone] like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR [telephone] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR [telephone] like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
GO
/*cardInfo表的约束
cardID 卡号 必填,主健 , 银行的卡号规则和电话号码一样,一般前8位代表特殊含义,如某总行某支行等。假定该行要求其营业厅的卡号格式为:1010 3576 xxxx xxxx开始
curID 货币类型 必填,默认为RMB
openDate 开户日期 必填,默认为系统当前日期
openMoney 开户金额 必填,不低于1元
balance 余额 必填,不低于1元,否则将销户
pass 密码 必填,6位数字,默认为6个8
IsReportLoss 是否挂失 必填,是/否值,默认为”否”
customerID 顾客编号 必填,表示该卡对应的顾客编号,一位顾客可以办理多张卡
savingID 存款类型ID号 必填,表示该开户卡对应何种类型存款类型(定期、活期、零存整取等)
*/
ALTER TABLE cardInfo
ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID),
CONSTRAINT CK_cardID CHECK(cardID LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
CONSTRAINT DF_curID DEFAULT('RMB') FOR curID,
CONSTRAINT DF_openDate DEFAULT(getdate()) FOR openDate,
CONSTRAINT CK_openMoney CHECK(openMoney>=1),
CONSTRAINT CK_balance CHECK(balance>=1),
CONSTRAINT CK_pass CHECK(pass LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
CONSTRAINT DF_pass DEFAULT('888888') FOR pass,
CONSTRAINT DF_IsReportLoss DEFAULT(0) FOR IsReportLoss,
CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID),
CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID)
GO
/* tradeInfo表的约束
tradeType 必填,只能是存入/支取
cardID 卡号 必填,外健,可重复索引
tradeMoney 交易金额 必填,大于0
tradeDate 交易日期 必填,默认为系统当前日期
remark 备注 可选输入,其他说明
*/
ALTER TABLE tradeInfo
ADD CONSTRAINT CK_tradeType CHECK(tradeType IN ('存入','支取')),
CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID),
CONSTRAINT CK_tradeMoney CHECK(tradeMoney>0),
CONSTRAINT DF_tradeDATE DEFAULT(getdate()) FOR tradeDate
GO
4.向各表添加数据
--存款类型表
INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息')
INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年')
INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年')
INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年')
INSERT INTO deposit (savingName) VALUES ('定活两便')
INSERT INTO deposit (savingName) VALUES ('通知')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年')
INSERT INTO deposit (savingName,descrip) VALUES ('存本取息五年','按月支取利息')
-- 查询该表信息
SELECT * FROM DEPOSIT
-- 向userinfo表和cardinfo表添加记录
INSERT INTO userInfo(customerName,PID,telephone,address )
VALUES('张三','123456789012345','010-67898978','北京海淀')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1234 5678',1,1000,1000,1)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('李四','321245678912345678','0478-4444333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1134',2,1,1,2)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('王五','567891234532124670','010-44443333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1130',2,1,1,3)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('丁六','567891321242345618','0752-4334554')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1004',2,1,1,4)
-- 查询
SELECT * FROM userInfo
SELECT * FROM cardInfo
GO
5.交易模拟
/*
张三的卡号(1010 3576 1234 5678)取款900元,李四的卡号(1010 3576 1212 1134)存款5000元,要求保存交易记录,以便客户查询和银行业务统计。
说明:当存钱或取钱(如900元)时候,会往交易信息表(tradeInfo)中添加一条交易记录,
同时应更新银行卡信息表(cardInfo)中的现有余额(如增加或减少900元)
*/
/*-----取款900交易信息表插入交易记录------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('支取','1010 3576 1234 5678',900)
/*--------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'
/*------存款300-------------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1130',300)
/*-------------更新银行卡信息表中的现有余额---*/
UPDATE cardInfo SET balance=balance+300 WHERE cardID='010 3576 1212 1130'
/*------------存款1000-------------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1004',1000)
/*-------------更新银行卡信息表中的现有余额---*/
UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010 3576 1212 1004'
/*------------取款1900-------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('支取','1010 3576 1212 1130',1900)
/*-------------更新银行卡信息表中的现有余额-----*/
UPDATE cardInfo SET balance=balance-1900 WHERE cardID='010 3576 1212 1130'
-- 注意观察,是否有错。
四、实验要求:
在“实验过程记录“栏中用截图方式,记下实验过程。
**五、实验过程记录
(注意:以上已经给出银行系统各表创建及数据操作有关代码,请按照**1.创建数据库、2、创建新表…的顺序,将每一项代码运行的结果截图展示。少数代码中已经设置错误陷阱,请找出错误,并自行解决,将结果截图展示。)
1.创建数据库
2.创建新表
3.向各表添加约束
4.向各表添加数据
5.交易模拟
(二) 案例训练2:模拟QQ数据库系统
模拟QQ聊天系统,设计该系统的数据库,并模拟基本业务流程,主要包括两大功能模块。
(1) 后台数据库的设计开发
(2) 模拟业务流程,实现对数据库的增删改查等功能
本次项目实战使用SQL SERVER管理工具,开发后台数据库的部分功能,设计一个数据库,并实现常见的增、删、改、查操作。
(3)案例覆盖的技能要点
创建数据库、创建表、添加约束、INSERT、UPDATE、DELETE、select
(4) 问题分析
A.用户表 QQUSER
在聊天前,首先要一个QQ号码,需要进行用户注册,提供必要的用户信息,需要一张表来存储用户的必要信息,可以命名为QQUSER表,结构如下表1: 表1 QQUSER表结构:
列名 | 类型 | 含义说明 |
---|---|---|
QQID | Bigint | 主键(QQ号) |
Password | Varchar | 密码 |
Lastlogtime | Datetime | 最后一次登录时间 |
Online | Int | 在线状态,取值为0、1、2。其中0表示在线、1表示离线、2表示隐身 |
Level | Int | 用户等级 |
B.基本信息表 BASEINFO
进行注册时,一般需要用户进一步填写详细的个人信息,如昵称、性别、年龄、联系方式、地址等。经常会把这部分信息存储在另外一个表中,这作为用户基本信息表BASEINFO,如下表所示。 表2 BASEINFO表结构:
列名 | 数据类型 | 说明 |
---|---|---|
QQID | Bigint | 主键 |
Nickname | Varchar | 昵称 |
Sex | Char | 性别 |
Age | Int | 年龄 |
Province | Varchar | 省份 |
City | Varchar | 城市 |
Address | Varchar | 地址 |
Phone | Varchar | 联系方式 |
C.用户关系表 relation |
用户关系表,是用来存储QQ用户之间的关系,主要包括用户QQ号码、与该用户有关系的用户QQ号码及表示两个用户关系的列。用户之间的关系,有两种:好友或黑名单人物,可以用整数表示二者关系,如0代表两个用户是好友关系,1代表是黑名单关系。该表结构如下所示。 表3 relation表结构
列名 | 类型 | 说明 |
---|---|---|
QQID | Bigint | 用户A的号码 |
RelationID | Bigint | 用户B的号码 |
status | Bit | 取值0代表A和B是好友关系;取值1代表B是A的黑名单人物 |
项目设计 |
1.创建QQ数据库
使用SSMS创建QQ数据库,并为其创建名称为“QQDB”。(数据文件名、日志文件名、文件大小、增长方式、物理路径等自定)
2.创建表结构
在SSMS中根据以上分析的QQDB中表结构创建用户表QQUSER、基本信息表BASEINFO和好友关系表relation。注意:创建各表时,注意各表的主键约束。
3.添加约束
各表约束条件如下:
(1) qq密码不得低于6位;
(2) 在线状态的值必须为0、1、2,分别表示在线、离线和隐身;
(3) 用户等级默认为0;
(4) 性别允许为空,输入则为‘男’或‘女’
(5) 年龄必须是1-100之间的整数;
(6) 用户关系只能是0或1,0表示好友,1表示黑名单人物。
4.添加模拟数据(insert)
根据上述描述建立的3张表,分别向3表中添加记录。
QQUSER表
QQID | PASSWORD | LASTLOGTIME | ONLINE | LEVEL |
---|---|---|---|---|
564389 | Jdsk23wed | 2020-2-4 10:23:22 | 1 | 5 |
564390 | Fagddagg | 2020-5-7 18:44:56 | 0 | 8 |
564391 | Dasgagda | 2020-3-7 18:33:02 | 0 | 9 |
564392 | Afjkdsghk | 2020-3-5 12:04:05 | 1 | 8 |
564393 | Afjdf2345 | 2020-3-7 10:04:05 | 0 | 11 |
564394 | Asdjfkljf | 2020-3-4 06:04:10 | 1 | 7 |
Baseinfo表
QQID | Nickname | Sex | Age | Province | City | Address | Phone |
---|---|---|---|---|---|---|---|
564389 | 涵养 | 男 | 18 | 北京 | 北京 | 朝阳大街 | 73853985946 |
564390 | 不洗澡的雨 | 男 | 25 | 天津 | 天津 | 南开大学 | 43729543 |
564391 | 叶落 | 男 | 78 | 四川 | 成都 | 四川大学 | 83408530280 |
564392 | 花开如梦 | 女 | 3 | 安徽 | 合肥 | 科技大学 | 803584032 |
564393 | 俗世 | 男 | 28 | 江苏 | 苏州 | 苏州大学 | 8084302806823 |
564394 | 香茗 | 女 | 11 | 安徽 | 芜湖 | 师范大学 | 3489852389328 |
Relation表
QQID | RELATIONID | STATUS |
---|---|---|
564389 | 564390 | false |
564389 | 564391 | false |
564389 | 564394 | true |
564389 | 564392 | false |
564390 | 564389 | false |
564390 | 564392 | false |
564390 | 564393 | true |
5.数据查询
(1) 查询每个省份的QQ用户人数。
(2) 查询至少30天没有登录账户的QQ用户。
6.修改数据
(1) 将“564393”的登录状态改为隐身,最后一次登录时间设置为当前系统时间。
(2) 修改“花开如梦”的昵称为“繁花似锦”
(3) 将“564390”的好友“564392”拖入黑名单
7.删除数据:删除多次在QQ中发布违法信息的用户“564390”
六、参考答案
-- 创建数据库
CREATE DATABASE bankDB
ON
(
NAME='bankDB_data',
FILENAME='d:\bankDB_data.mdf',
SIZE=5mb,
FILEGROWTH=15%
)
LOG ON
(
NAME= 'bankDB_log',
FILENAME='d:\bankDB_log.ldf',
SIZE=5mb,
FILEGROWTH=15%
)
GO
-- 2.创建新表
USE bankDB
GO
CREATE TABLE userInfo --用户信息表
(
customerID INT IDENTITY(1,1),
customerName CHAR(8) NOT NULL,
PID CHAR(18) NOT NULL,
telephone CHAR(20) NOT NULL,
address VARCHAR(50)
)
GO
CREATE TABLE cardInfo --银行卡信息表
(
cardID CHAR(19) NOT NULL,
curID VARCHAR(10) NOT NULL,
savingID INT NOT NULL,
openDate DATETIME NOT NULL,
openMoney MONEY NOT NULL,
balance MONEY NOT NULL,
pass CHAR(6) NOT NULL,
IsReportLoss BIT NOT NULL,
customerID INT NOT NULL
)
GO
CREATE TABLE tradeInfo --交易信息表
(
tradeDate DATETIME NOT NULL,
tradeType CHAR(4) NOT NULL,
cardID CHAR(19) NOT NULL,
tradeMoney MONEY NOT NULL,
remark TEXT
)
GO
CREATE TABLE Deposit --存款类型表
(
savingID INT IDENTITY(1,1),
savingName VARCHAR(20) NOT NULL,
descript VARCHAR(50)
)
GO
-- 3.向各表添加约束
ALTER TABLE Deposit
ADD CONSTRAINT PK_savingID PRIMARY KEY(savingID)
GO
/* userInfo表的约束
customerID 顾客编号 自动编号(标识列),从1开始,主键
customerName 开户名 必填
PID 身份证号 必填,只能是18位或15位,身份证号唯一约束
telephone 联系电话 必填,格式为xxxx-xxxxxxx或手机号11位或xxx-xxxxxxxx
address 居住地址 可选输入
*/
ALTER TABLE userInfo
ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID),
CONSTRAINT CK_PID CHECK( len(PID)=18 or len(PID)=15 ),
CONSTRAINT UQ_PID UNIQUE(PID),
CONSTRAINT CK_telephone CHECK([telephone] like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR [telephone] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR [telephone] like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
GO
/*cardInfo表的约束
cardID 卡号 必填,主健 , 银行的卡号规则和电话号码一样,一般前8位代表特殊含义,如某总行某支行等。假定该行要求其营业厅的卡号格式为:1010 3576 xxxx xxxx开始
curID 货币类型 必填,默认为RMB
openDate 开户日期 必填,默认为系统当前日期
openMoney 开户金额 必填,不低于1元
balance 余额 必填,不低于1元,否则将销户
pass 密码 必填,6位数字,默认为6个8
IsReportLoss 是否挂失 必填,是/否值,默认为”否”
customerID 顾客编号 必填,表示该卡对应的顾客编号,一位顾客可以办理多张卡
savingID 存款类型ID号 必填,表示该开户卡对应何种类型存款类型(定期、活期、零存整取等)
*/
ALTER TABLE cardInfo
ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID),
CONSTRAINT CK_cardID CHECK(cardID LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
CONSTRAINT DF_curID DEFAULT('RMB') FOR curID,
CONSTRAINT DF_openDate DEFAULT(getdate()) FOR openDate,
CONSTRAINT CK_openMoney CHECK(openMoney>=1),
CONSTRAINT CK_balance CHECK(balance>=1),
CONSTRAINT CK_pass CHECK(pass LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
CONSTRAINT DF_pass DEFAULT('888888') FOR pass,
CONSTRAINT DF_IsReportLoss DEFAULT(0) FOR IsReportLoss,
CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID),
CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID)
GO
/* tradeInfo表的约束
tradeType 必填,只能是存入/支取
cardID 卡号 必填,外健,可重复索引
tradeMoney 交易金额 必填,大于0
tradeDate 交易日期 必填,默认为系统当前日期
remark 备注 可选输入,其他说明
*/
ALTER TABLE tradeInfo
ADD CONSTRAINT CK_tradeType CHECK(tradeType IN ('存入','支取')),
CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID),
CONSTRAINT CK_tradeMoney CHECK(tradeMoney>0),
CONSTRAINT DF_tradeDATE DEFAULT(getdate()) FOR tradeDate
GO
-- 4.向表中添加数据
--存款类型表
INSERT INTO deposit (savingName,descript) VALUES ('活期','按存款日结算利息')
INSERT INTO deposit (savingName,descript) VALUES ('定期一年','存款期是1年')
INSERT INTO deposit (savingName,descript) VALUES ('定期二年','存款期是2年')
INSERT INTO deposit (savingName,descript) VALUES ('定期三年','存款期是3年')
INSERT INTO deposit (savingName) VALUES ('定活两便')
INSERT INTO deposit (savingName) VALUES ('通知')
INSERT INTO deposit (savingName,descript) VALUES ('零存整取一年','存款期是1年')
INSERT INTO deposit (savingName,descript) VALUES ('零存整取二年','存款期是2年')
INSERT INTO deposit (savingName,descript) VALUES ('零存整取三年','存款期是3年')
INSERT INTO deposit (savingName,descript) VALUES ('存本取息五年','按月支取利息')
select savingID 存款编号, savingName 存款类型, descript 存款描述 from Deposit
-- 向userinfo表和cardinfo表添加记录
INSERT INTO userInfo(customerName,PID,telephone,address )
VALUES('张三','123456789012345','010-67898978','北京海淀')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1234 5678',1,1000,1000,1)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('李四','321245678912345678','0478-4444333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1134',2,1,1,2)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('王五','567891234532124670','010-44443333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1130',2,1,1,3)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('丁六','567891321242345618','0752-4334554')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1004',2,1,1,4)
-- 查询
SELECT customerID 顾客编号,
customerName 开户名,
PID 身份证号,
telephone 联系电话,
address 居住地址 FROM userInfo
SELECT cardID 卡号,
curID 货币类型,
openDate 开户日期,
openMoney 开户金额,
balance 余额,
pass 密码,
IsReportLoss 是否挂失,
customerID 顾客编号,
savingID 存款类型ID号 FROM cardInfo
GO
-- 5.交易模拟
/*
张三的卡号(1010 3576 1234 5678)取款900元,李四的卡号(1010 3576 1212 1134)存款5000元,要求保存交易记录,以便客户查询和银行业务统计。
说明:当存钱或取钱(如900元)时候,会往交易信息表(tradeInfo)中添加一条交易记录,
同时应更新银行卡信息表(cardInfo)中的现有余额(如增加或减少900元)
*/
/*-----取款900交易信息表插入交易记录------------*/
select tradeType 存入或支取,
cardID 卡号,
tradeMoney 交易金额,
tradeDate 交易日期,
remark 备注 from tradeInfo;
SELECT cardID 卡号,
curID 货币类型,
openDate 开户日期,
openMoney 开户金额,
balance 余额,
pass 密码,
IsReportLoss 是否挂失,
customerID 顾客编号,
savingID 存款类型ID号 FROM cardInfo;
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('支取','1010 3576 1234 5678',900)
/*--------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'
/*------存款300-------------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1130',300)
/*-------------更新银行卡信息表中的现有余额---*/
UPDATE cardInfo SET balance=balance+300 WHERE cardID='1010 3576 1212 1130'
/*------------存款1000-------------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1004',1000)
/*-------------更新银行卡信息表中的现有余额---*/
UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010 3576 1212 1004'
/*------------取款1900-------------------*/
SELECT cardID 卡号,
curID 货币类型,
openDate 开户日期,
openMoney 开户金额,
balance 余额,
pass 密码,
IsReportLoss 是否挂失,
customerID 顾客编号,
savingID 存款类型ID号 FROM cardInfo
where cardID = '1010 3576 1212 1130';
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1130',1900)
/*-------------更新银行卡信息表中的现有余额-----*/
UPDATE cardInfo SET balance=balance+1900 WHERE cardID='1010 3576 1212 1130';
(二)
-- 创建QQ数据库
create database QQDB;
if exists(select * from sys.databases where name = 'QQDB')
use QQDB;
-- 1.创建QQUSER表结构
create table QQUSER(
QQID Bigint,
Password Varchar(30),
Lastlogtime Datetime,
Online int,
Level int,
constraint PK_QQUSER primary key(QQID),
)
-- 2.创建用户基本信息表
create table BASEINFO(
QQID Bigint, --主键
Nickname Varchar(30), --昵称
Sex char(2), --性别
Age int, --年龄
Province Varchar(30), --省份
City Varchar(30), --城市
Address Varchar(100), --地址
Phone Varchar(30), --联系方式
constraint PK_BASEINFO primary key(QQID),
)
-- 3.创建好友关系表
create table relation(
QQID Bigint,
RelationID Bigint,
status Bit,
)
-- 4.为各表建立约束
-- (1)qq密码不得低于6位
Alter table QQUSER
Add constraint CK_PASSWORD check(len(Password)>=6);
-- (2)在线状态的值必须为0,1,2,分别表示在线、离线和隐身
Alter table QQUSER
Add constraint CK_ONLINE check(Online in (0,1,2));
-- (3)用户等级默认为0
Alter table QQUSER
Add constraint DK_LEVEL default 0 for Level;
-- (4)性别允许为空,输入则为'男'或者'女'
Alter table BASEINFO
Add constraint CK_SEX check(Sex is NULL or Sex in ('男','女'));
-- (5)年龄必须是1-100之间的整数
Alter table BASEINFO
Add constraint CK_AGE check(Age between 1 and 100);
-- (6)用户关系只能是0或者1
Alter table relation
Add constraint CK_STATUS check(status in (0,1));
-- 5.添加模拟数据
-- 向QQUSER表插入数据
INSERT INTO QQUSER (QQID, Password, Lastlogtime, Online, Level)
VALUES
(564389, 'Jdsk23wed', '2020-2-4 10:23:22', 1, 5),
(564390, 'Fagddagg', '2020-5-7 18:44:56', 0, 8),
(564391, 'Dasgagda', '2020-3-7 18:33:02', 0, 9),
(564392, 'Afjkdsghk', '2020-3-5 12:04:05', 1, 8),
(564393, 'Afjdf2345', '2020-3-7 10:04:05', 0, 11),
(564394, 'Asdjfkljf', '2020-3-4 06:04:10', 1, 7);
-- 向BASEINFO表插入数据
INSERT INTO BASEINFO (QQID, Nickname, Sex, Age, Province, City, Address, Phone)
VALUES
(564389, '涵养', '男', 18, '北京', '北京', '朝阳大街', '73853985946'),
(564390, '不洗澡的雨', '男', 25, '天津', '天津', '南开大学', '43729543'),
(564391, '叶落', '男', 78, '四川', '成都', '四川大学', '83408530280'),
(564392, '花开如梦', '女', 3, '安徽', '合肥', '科技大学', '803584032'),
(564393, '俗世', '男', 28, '江苏', '苏州', '苏州大学', '8084302806823'),
(564394, '香茗', '女', 11, '安徽', '芜湖', '师范大学', '3489852389328');
-- 向RELATION表插入数据
INSERT INTO RELATION (QQID, RelationID, Status)
VALUES
(564389, 564390, 0),
(564389, 564391, 0),
(564389, 564394, 1),
(564389, 564392, 0),
(564390, 564389, 0),
(564390, 564392, 0),
(564390, 564393, 1);
go
select * from QQUSER;
select * from BASEINFO;
select * from relation;
-- 6.数据查询
-- (1)查询每个省份的QQ用户人数。
select Province 省份,COUNT(*) 人数 from BASEINFO
group by Province;
-- (2)查询至少30天没有登录账户的QQ用户。
select * from QQUSER
where DATEDIFF(day, Lastlogtime, getDate()) >= 30;
-- 7.修改数据
-- (1)将“564393”的登录状态改为隐身,最后一次登录时间设置为当前系统时间。
-- 数据更改前
select * from QQUSER where QQID = 564393;
Update QQUSER set Online = 2,Lastlogtime = GETDATE()
where QQID = 564393;
-- 数据修改后
select * from QQUSER where QQID = 564393;
-- (2)修改“花开如梦”的昵称为“繁花似锦”
-- 数据更改前
select * from BASEINFO;
Update BASEINFO set Nickname = '繁花似锦'
where Nickname = '花开如梦'
-- 数据更改后
select * from BASEINFO;
-- (3)将“564390”的好友“564392”拖入黑名单
-- 数据更改前
select * from relation where QQID = 564390 and RelationID = 564392;
Update relation set status = 1
where QQID = 564390 and RelationID = 564392;
-- 数据更改后
select * from relation where QQID = 564390 and RelationID = 564392;
-- 8.删除数据:删除多次在QQ中发布违法信息的用户“564390”
-- 首先删除与该用户相关的所有关系记录
delete from relation
where QQID = 564390 or RelationID = 564390;
-- 然后删除基本信息
delete from BASEINFO
where QQID = 564390;
-- 最后删除用户
delete from QQUSER
where QQID = 564390;
select * from QQUSER;
select * from BASEINFO;
select * from relation;
实验报告四
一、实训环境
MICROSOFT SQL SERVER
二、实验目的
1. 熟练掌握SELECT语句的基本语法和查询条件表示方法。 2. 熟练掌握查询条件各类和表示方法。 3. 掌握连接查询、嵌套查询的表示及使用。 4. 了解集合查询的表示及使用。
三、实验准备
掌握的知识要点: 1. SELECT语句的基本语法和查询条件表示方法。 2. 查询条件表示种类和表示方法。 3. 连接查询、嵌套查询、集合查询的表示及使用。
四、实验内容
(一)、完成附录B患者住院管理数据库(HZZYGL)的创建工作,在该数据库中建立如下表格,并向各表中添加数据。
本实验不需要按步骤截图,可以在第(一)题全部完成后对整个数据库内各表格结构和表格数据进行截图展示即可。
附录B:患者住院管理数据库(HZZYGL)
1、患者表(Patient)的表结构、表记录描述分别详见表B-1和表B-2。
表B-1患者表(Patient)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
Pno | char | 5 | 否 | 患者病历号(主键) |
Pname | varchar | 20 | 否 | 患者姓名 |
Pgender | char | 2 | 否 | 性别 |
Page | tinyint | 否 | 年龄 | |
Pnation | varchar | 10 | 是 | 民族 |
Pnative_place | varchar | 20 | 是 | 籍贯 |
表B-2患者表(Patient)的表记录
Pno | Pname | Pgender | Page | Pnation | Pnative_place |
---|---|---|---|---|---|
51001 | 杨洁 | 男 | 52 | 汉族 | 合肥市 |
51002 | 吴方 | 男 | 36 | 汉族 | 芜湖市 |
51003 | 阿里娅 | 女 | 30 | 回族 | 宿州市 |
51004 | 苏和泰 | 男 | 47 | 满族 | 肥东县 |
51005 | 郑杰 | 男 | 54 | 汉族 | 安庆市 |
51006 | 陈亮 | 男 | 54 | 汉族 | 黄山市 |
51007 | 卡米拉 | 女 | 43 | 回族 | 六安市 |
51008 | 李冰艳 | 女 | 25 | 汉族 | 滁州市 |
51009 | 法哈德 | 男 | 21 | 回族 | 淮北市 |
51010 | 汪涛 | 男 | 32 | 汉族 | 蚌埠市 |
2、床位表(Bed)的表结构、表记录描述分别详见表B-3和表B-4。
表B-3床位表(Bed)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
Bno | char | 5 | 否 | 床位号(主键) |
Rno | char | 5 | 否 | 科室号(外键:参照Room表) |
Pno | char | 5 | 是 | 患者病历号(外键:参照Patient表) |
Ddiagno | char | 5 | 是 | 诊疗号(外键:参照Diagnose表) |
Bbed_price | numeric | (12,2) | 否 | 床位单价(默认300元/天) |
Bstate | bit | 否 | 床位状态(默认为0,没人住院;1有人住院) |
表B-4床位表(Bed)的表记录
Bno | Rno | Pno | Ddiagno | Bbed_price | Bstate |
---|---|---|---|---|---|
52001 | 53001 | 51001 | 57001 | 300 | 1 |
52002 | 53002 | 51002 | 57002 | 300 | 1 |
52003 | 53003 | 51003 | 57003 | 300 | 1 |
52004 | 53004 | 51004 | 57004 | 300 | 1 |
52005 | 53005 | 51005 | 57005 | 300 | 1 |
52006 | 53006 | 51006 | 57006 | 300 | 1 |
52007 | 53007 | 51007 | 57007 | 200 | 1 |
52008 | 53001 | 51008 | 57008 | 300 | 1 |
52009 | 53002 | 51009 | 57009 | 300 | 1 |
52010 | 53003 | 51010 | 57010 | 300 | 1 |
52011 | 53004 | NULL | NULL | 400 | 0 |
52012 | 53005 | NULL | NULL | 300 | 0 |
52013 | 53006 | NULL | NULL | 300 | 0 |
52014 | 53007 | NULL | NULL | 200 | 0 |
52015 | 53001 | NULL | NULL | 300 | 0 |
52016 | 53002 | NULL | NULL | 500 | 0 |
52017 | 53003 | NULL | NULL | 300 | 0 |
52018 | 53004 | NULL | NULL | 300 | 0 |
52019 | 53005 | NULL | NULL | 300 | 0 |
52020 | 53006 | NULL | NULL | 301 | 0 |
3、科室表(Room)的表结构、表记录描述分别详见表B-5和表B-6。
表B-5科室表(Room)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
Rno | char | 5 | 否 | 科室号(主键) |
Rname | varchar | 20 | 否 | 科室名称 |
Rtelephone | varchar | 13 | 否 | 联系方式 |
表B-6科室表(Room)的表记录
Rno | Rname | Rtelephone |
---|---|---|
53001 | 心内科 | 64163009 |
53002 | 肿瘤科 | 64367770 |
53003 | 骨科 | 67207216 |
53004 | 消化科 | 65990870 |
53005 | 耳鼻喉科 | 64431330 |
53006 | 感染科 | 67026263 |
53007 | 泌尿外科 | 64163009 |
4、护士表(Nurse)的表结构、表记录描述分别详见表B-7和表B-8。
表B-7护士表(Nurse)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
Nno | char | 5 | 否 | 护士号(主键) |
Rno | char | 5 | 否 | 科室号(外键: 参照Room表) |
Nname | varchar | 20 | 否 | 姓名 |
Ngender | char | 2 | 否 | 性别 |
Nage | tinyint | 否 | 年龄 | |
Nnation | varchar | 10 | 是 | 民族 |
Nnative_place | varchar | 20 | 是 | 籍贯 |
Npwd | char | 6 | 否 | 登陆密码(默认123456) |
表B-8护士表(Nurse)的表记录
Nno | Rno | Nname | Ngender | Nage | Nnation | Nnative_place | Npwd |
---|---|---|---|---|---|---|---|
54001 | 53001 | 褚昕 | 女 | 34 | 汉族 | 马鞍山市 | 123456 |
54002 | 53002 | 卫正 | 女 | 25 | 回族 | 蚌埠市 | 123456 |
54003 | 53003 | 冯永 | 男 | 23 | 汉族 | 阜阳市 | 313630 |
54004 | 53004 | 钱仨 | 女 | 34 | 汉族 | 亳州市 | 521125 |
54005 | 53005 | 李时代 | 女 | 21 | 汉族 | 天长市 | 125478 |
54006 | 53006 | 秦琪 | 女 | 30 | 汉族 | 宣城市 | 111111 |
54007 | 53007 | 张艳艳 | 女 | 27 | 汉族 | 安庆市 | 123456 |
5、医生表(Doctor)的表结构、表记录描述分别详见表B-9和表B-10。
表B-9医生表(Doctor)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
Dno | char | 5 | 否 | 医生号(主键) |
Rno | char | 5 | 否 | 科室号(外键:参照Room表) |
Dname | varchar | 20 | 否 | 姓名 |
Dgender | char | 2 | 否 | 性别 |
Dage | tinyint | 否 | 年龄 | |
Dnation | varchar | 10 | 是 | 民族 |
Dnative_place | varchar | 20 | 是 | 籍贯 |
Dpwd | char | 6 | 否 | 登陆密码(默认123456) |
表B-10医生表(Doctor)的表记录
Dno | Rno | Dname | Dgender | Dage | Dnation | Dnative_place | Dpwd |
---|---|---|---|---|---|---|---|
55001 | 53001 | 孙云 | 男 | 53 | 汉族 | 安庆市 | 123456 |
55002 | 53002 | 尤璐 | 女 | 38 | 汉族 | 合肥市 | 123456 |
55003 | 53003 | 蒋婷婷 | 女 | 31 | 回族 | 淮北市 | 457836 |
55004 | 53004 | 朱翠翠 | 女 | 29 | 汉族 | 六安市 | 241563 |
55005 | 53005 | 周迪 | 男 | 41 | 汉族 | 铜陵市 | 568974 |
55006 | 53006 | 许菲菲 | 女 | 54 | 汉族 | 淮南市 | 412153 |
55007 | 53007 | 杨天宇 | 男 | 48 | 汉族 | 蚌埠市 | 154289 |
6、药品表(Drug)的表结构、表记录描述分别详见表B-11和表B-12。
表B-11药品表(Drug)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
Dmno | char | 5 | 否 | 药品编号(主键) |
Dmname | varchar | 50 | 否 | 药品名称 |
Dmnum | int | 否 | 库存量 | |
Dmspecs | varchar | 20 | 是 | 规格 |
Dmunit_price | numeric | (12,2) | 否 | 药品单价 |
Dmpd | date | 否 | 生产日期 | |
Dmexp | date | 否 | 有效日期 |
表B-12药品表(Drug)的表记录
Dmno | Dmname | Dmnum | Dmspecs | Dmunit_price | Dmpd | Dmexp |
---|---|---|---|---|---|---|
56001 | 硝苯地平片 | 853 | 0.2mg/片 | 30 | 2016/5/29 | 2021/5/29 |
56002 | 消癌平片 | 253 | 0.3mg/片 | 129 | 2016/7/9 | 2021/7/8 |
56003 | 骨愈灵胶囊 | 970 | 0.4mg/粒 | 24 | 2016/2/19 | 2021/2/17 |
56004 | 布洛芬 | 493 | 0.5mg/片 | 34 | 2015/3/8 | 2020/3/6 |
56005 | 鼻炎康 | 555 | 1.5mg/片 | 54 | 2016/12/21 | 2021/12/20 |
56006 | 奥司他韦 | 437 | 1.6mg/片 | 43 | 2016/9/7 | 2021/9/6 |
56007 | 利胆药 | 760 | 5mg/包 | 32 | 2015/8/29 | 2020/8/27 |
56008 | 柴胡注射液 | 275 | 0.25g/支 | 43 | 2015/11/24 | 2020/11/22 |
56009 | 头孢拉定 | 944 | 1.5mg/片 | 43 | 2016/1/18 | 2021/1/16 |
56010 | 温胃舒颗粒 | 644 | 0.5mg/粒 | 65 | 2015/5/22 | 2020/5/20 |
56011 | 肠康片 | 121 | 1.5mg/片 | 23 | 2016/7/13 | 2021/7/12 |
56012 | 布洛芬 | 187 | 1.6mg/片 | 57 | 2015/11/8 | 2020/11/6 |
56013 | 阿米沙剌 | 399 | 2.5mg/片 | 45 | 2015/9/10 | 2020/9/8 |
56014 | 米卡酚 | 275 | 3.5mg/片 | 32 | 2016/7/19 | 2021/7/18 |
56015 | 阿莫西林 | 902 | 1.5mg/片 | 21 | 2016/6/9 | 2021/6/8 |
56016 | 感冒灵颗粒 | 498 | 0.5mg/粒 | 17 | 2016/7/2 | 2021/7/1 |
56017 | 阿米卡星 | 339 | 1.5mg/片 | 43 | 2016/4/9 | 2021/4/8 |
7、取药表(Take_Drug)的表结构、表记录描述分别详见表B-13和表B-14。
表B-13取药表(Take_Drug)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
TMno | int | 否 | 取药流水号(主键,自动增加) | |
Ddiagno | char | 5 | 否 | 诊疗号(外键:参照Diagnose表) |
Dmno | char | 5 | 否 | 药品编号(外键:参照drug表) |
TMnumber | int | 否 | 取药数量 | |
TMdate | date | 否 | 取药日期 |
表B-14取药表(Take_Drug)的表记录
TMno | Ddiagno | Dmno | TMnumber | TMdate |
---|---|---|---|---|
10000 | 57001 | 56001 | 2 | 2018/6/2 |
10001 | 57002 | 56002 | 2 | 2018/6/1 |
10002 | 57003 | 56003 | 3 | 2018/1/25 |
10003 | 57004 | 56004 | 2 | 2017/12/27 |
10004 | 57005 | 56005 | 3 | 2017/11/12 |
10005 | 57006 | 56006 | 3 | 2017/11/21 |
10006 | 57007 | 56007 | 2 | 2017/7/23 |
10007 | 57008 | 56008 | 2 | 2017/9/12 |
10008 | 57009 | 56009 | 3 | 2017/9/15 |
10009 | 57010 | 56010 | 3 | 2017/10/20 |
8、诊疗表(Diagnose)的表结构、表记录描述分别详见表B-15和表B-16.
表B-15诊疗表(Diagnose)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
Ddiagno | char | 5 | 否 | 诊疗号(主键) |
Pno | char | 5 | 否 | 患者病历号(外键:参照Patient表) |
Dno | char | 5 | 否 | 医生号(外键:参照Doctor表) |
Nno | char | 5 | 是 | 护士号(外键:参照Nurse表) |
Dsymptom | text | 否 | 病症 | |
Dhistory | text | 是 | 病史 | |
Dresult | text | 否 | 检查结果及医嘱 | |
Dpd | varchar | 50 | 是 | 药方 |
表B-16诊疗表(Diagnose)的表记录
Ddiagno | Pno | Dno | Nno | Dsymptom | Dhistory | Dresult | Dpd |
---|---|---|---|---|---|---|---|
57001 | 51001 | 55001 | 54001 | 高血压、心律失常 | 高血压史 | 高血压 | 吃药 |
57002 | 51002 | 55002 | 54002 | 面泛黄疸,右上腹疼痛的症状 | 肺动脉高压史 | 胆囊癌 | 吃药、手术 |
57003 | 51003 | 55003 | 54003 | 左膝盖有敲击痛,伴有间歇性小腿肌肉抽筋,持续1个月 | 膝内侧受伤病史 | 膝盖骨折 | 吃药 |
57004 | 51004 | 55004 | 54004 | 偏头痛,畏寒,持续性喷嚏,流清涕 | 有带状疱疹病史 | 流感 | 手术 |
57005 | 51005 | 55005 | 54005 | 外感风寒,发热恶寒无汗,四肢无力,头痛 | 有I型糖尿病史 | 鼻炎 | 吃药 |
57006 | 51006 | 55006 | 54006 | 发热、皮疹、淋巴结肿大 | 有哮喘病史 | 急性肺炎 | 吃药、手术 |
57007 | 51007 | 55007 | 54007 | 腰腹疼痛、血尿、四肢无力 | 有哮喘病史 | 胆结石 | 手术 |
57008 | 51008 | 55005 | 54005 | 外感风寒,发热恶寒无汗,四肢无力,头痛 | 有I型糖尿病史 | 鼻炎 | 吃药 |
57009 | 51009 | 55006 | 54006 | 发热、皮疹、淋巴结肿大 | 有哮喘病史 | 急性肺炎 | 吃药、手术 |
57010 | 51010 | 55007 | 54007 | 腰腹疼痛、血尿、四肢无力 | 有哮喘病史 | 胆结石 | 手术 |
9、划价收费表(Charges)的表结构、表记录描述分别详见表B-17和表B-18.
表B-17划价收费表(Charges)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
Cno | int | 否 | 票据号(主键,自动增加) | |
Ddiagno | char | 5 | 否 | 诊疗号(外键,参照Diagnose表) |
Pno | char | 5 | 否 | 病历号(外键:参照Patient表) |
Cregd_pay | numeric | (12,2) | 否 | 挂号费用(默认为20) |
Cdrug_pay | numeric | (12,2) | 是 | 药品费用 |
Cbed_pay | numeric | (12,2) | 否 | 床位费用(默认床位单价) |
Chosp_pay | numeric | (12,2) | 否 | 住院费用(默认为0) |
表B-18划价收费表(Charges)的表记录
Cno | Ddiagno | Pno | Cregd_pay | Cdrug_pay | Cbed_pay | Chosp_pay |
---|---|---|---|---|---|---|
10000 | 57001 | 51001 | 20 | 300 | 0 | |
10001 | 57002 | 51002 | 20 | 300 | 0 | |
10002 | 57003 | 51003 | 20 | 300 | 0 | |
10003 | 57004 | 51004 | 20 | 300 | 0 | |
10004 | 57005 | 51005 | 20 | 300 | 0 | |
10005 | 57006 | 51006 | 20 | 300 | 0 | |
10006 | 57007 | 51007 | 20 | 200 | 0 | |
10007 | 57008 | 51008 | 20 | 300 | 0 | |
10008 | 57009 | 51009 | 20 | 300 | 0 | |
10009 | 57010 | 51010 | 20 | 195 | 1500 | 1715 |
10、住院表(Hospitalize)的表结构、表记录描述分别详见表B-19和表B-20。
表B-19住院表(Hospitalize)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
Hno | int | 否 | 住院流水号(主键) | |
Ddiagno | char | 5 | 否 | 诊疗号(外键:参照Diagnose表) |
Pno | char | 5 | 否 | 病历号(外键:参照Patient表) |
Rno | char | 5 | 否 | 科室号(外键: 参照Room表) |
Bno | char | 5 | 是 | 床位号(外键:参照Bed) |
Dno | char | 5 | 否 | 医生号(外键:参照Doctor表) |
Nno | char | 5 | 否 | 护士号(外键:参照Nurse表) |
Hinhosp | date | 否 | 入院时间 | |
Houthosp | date | 是 | 出院时间 |
表B-20住院表(Hospitalize)的表记录
Hno | Ddiagno | Pno | Rno | Bno | Dno | Nno | Hinhosp | Houthosp |
---|---|---|---|---|---|---|---|---|
10000 | 57001 | 51001 | 53001 | 52001 | 55001 | 54001 | 2018/5/22 | |
10001 | 57002 | 51002 | 53002 | 52002 | 55002 | 54002 | 2018/5/29 | |
10002 | 57003 | 51003 | 53003 | 52003 | 55003 | 54003 | 2018/1/18 | |
10003 | 57004 | 51004 | 53004 | 52004 | 55004 | 54004 | 2017/12/21 | |
10004 | 57005 | 51005 | 53005 | 52005 | 55005 | 54005 | 2017/11/8 | |
10005 | 57006 | 51006 | 53006 | 52006 | 55006 | 54006 | 2017/11/16 | |
10006 | 57007 | 51007 | 53007 | 52007 | 55007 | 54007 | 2017/7/19 | |
10007 | 57008 | 51008 | 53001 | 52008 | 55005 | 54005 | 2017/9/7 | |
10008 | 57009 | 51009 | 53002 | 52009 | 55006 | 54006 | 2017/9/12 | |
10009 | 57010 | 51010 | 53003 | 52010 | 55007 | 54007 | 2017/10/16 | 2017/10/20 |
11、管理员表(Manager)的表结构、表记录描述分别详见表B-21和表B-22。
表B-21管理员表(Manager)的表结构
列名 | 数据类型 | 长度 | 可否为空 | 备注 |
---|---|---|---|---|
Mno | char | 5 | 否 | 管理员号(主键) |
Mname | varchar | 20 | 否 | 姓名 |
Mpwd | char | 6 | 否 | 登陆密码(默认123456) |
Mlevel | bit | 否 | 默认0,为医院管理员;1则为系统管理员 |
表B-22管理员表(Manager)的表记录
Mno | Mname | Mpwd | Mlevel |
---|---|---|---|
10000 | 杨浦 | 135790 | 1 |
10001 | 梦岚 | 123456 | 0 |
10002 | 黄强军 | 313630 | 0 |
(二)、根据上面的数据库,完成以下题目。
- 查询所有陈性患者的信息。
- 查询姓苏且全名有三个汉字的患者的信息。
- 查询年龄超过35岁的医生的信息。
- 查询药品名称中含有“颗粒”和最后一个字是“片”的所有药品的信息。
- 查询非汉族护士的姓名、性别、民族和籍贯。
- 查询年龄超过50岁的汉族患者的姓名、性别、年龄和籍贯。
- 按药品单价升序显示药品的所有信息。
- 按取药数量升序、取药日期降序显示取药表的所有信息。
- 按入院时间降序列出住院表中患者病历号、科室表、医生号和入院时间。
- 列出所有患者的病历号和住院天数,并按住院天数降序排列。
- 显示单价为25~55元的药品的名称和单价(按9折显示)。
- 查询药品库存量在前15%的药品的信息。
- 列出划价表中药品费用排在后20%的所有信息。
- 列出籍贯不是合肥市和淮北市的医生的所有信息。
- 查询床位表中空床位的所有信息。
- 统计医生人数。
- 统计护士的平均年龄。
- 统计药品规格数。
- 按照性别统计患者人数。
- 列出患者中民族人数超过3人的名族及人数。
- 按性别显示患者的最高年龄、最低年龄和平均年龄。
- 按药品名称显示药品的最高单价,最低单价和平均单价,并按最高单价降序排序。
六、参考答案
-- 创建数据库
CREATE DATABASE HZZYGL
-- 1.创建患者表
CREATE TABLE Patient (
Pno CHAR(5) NOT NULL, -- 患者病历号
Pname VARCHAR(20) NOT NULL, -- 患者姓名
Pgender CHAR(2) NOT NULL, -- 性别
Page TINYINT NOT NULL, -- 年龄
Pnation VARCHAR(10), -- 民族
Pnative_place VARCHAR(20), -- 籍贯
constraint PK_PATIENT primary key(Pno)
);
-- 2.插入患者数据
INSERT INTO Patient VALUES
('51001', '杨洁', '男', 52, '汉族', '合肥市'),
('51002', '吴方', '男', 36, '汉族', '芜湖市'),
('51003', '阿里娅', '女', 30, '回族', '宿州市'),
('51004', '苏和泰', '男', 47, '满族', '肥东县'),
('51005', '郑杰', '男', 54, '汉族', '安庆市'),
('51006', '陈亮', '男', 54, '汉族', '黄山市'),
('51007', '卡米拉', '女', 43, '回族', '六安市'),
('51008', '李冰艳', '女', 25, '汉族', '滁州市'),
('51009', '法哈德', '男', 21, '回族', '淮北市'),
('51010', '汪涛', '男', 32, '汉族', '蚌埠市');
-- 3.创建科室表
CREATE TABLE Room (
Rno CHAR(5) NOT NULL, -- 科室号
Rname VARCHAR(20) NOT NULL, -- 科室名称
Rtelephone VARCHAR(13) NOT NULL, -- 联系方式
constraint PK_ROOM PRIMARY KEY(Rno)
);
-- 4.插入科室数据
INSERT INTO Room VALUES
('53001', '心内科', '64163009'),
('53002', '肿瘤科', '64367770'),
('53003', '骨科', '67207216'),
('53004', '消化科', '65990870'),
('53005', '耳鼻喉科', '64431330'),
('53006', '感染科', '67026263'),
('53007', '泌尿外科', '64163009');
-- 5.创建医生表
CREATE TABLE Doctor (
Dno CHAR(5) NOT NULL, -- 医生号
Rno CHAR(5) NOT NULL, -- 科室号
Dname VARCHAR(20) NOT NULL, -- 姓名
Dgender CHAR(2) NOT NULL, -- 性别
Dage TINYINT NOT NULL, -- 年龄
Dnation VARCHAR(10), -- 民族
Dnative_place VARCHAR(20), -- 籍贯
Dpwd CHAR(6) NOT NULL DEFAULT '123456', -- 登陆密码
constraint PK_DOCTOR PRIMARY KEY(Dno),
constraint FK_RNO FOREIGN KEY (Rno) REFERENCES Room(Rno)
);
-- 6.插入医生数据
INSERT INTO Doctor VALUES
('55001', '53001', '孙云', '男', 53, '汉族', '安庆市', '123456'),
('55002', '53002', '尤璐', '女', 38, '汉族', '合肥市', '123456'),
('55003', '53003', '蒋婷婷', '女', 31, '回族', '淮北市', '457836'),
('55004', '53004', '朱翠翠', '女', 29, '汉族', '六安市', '241563'),
('55005', '53005', '周迪', '男', 41, '汉族', '铜陵市', '568974'),
('55006', '53006', '许菲菲', '女', 54, '汉族', '淮南市', '412153'),
('55007', '53007', '杨天宇', '男', 48, '汉族', '蚌埠市', '154289');
-- 7.创建护士表
CREATE TABLE Nurse (
Nno CHAR(5) NOT NULL, -- 护士号
Rno CHAR(5) NOT NULL, -- 科室号
Nname VARCHAR(20) NOT NULL, -- 姓名
Ngender CHAR(2) NOT NULL, -- 性别
Nage TINYINT NOT NULL, -- 年龄
Nnation VARCHAR(10), -- 民族
Nnative_place VARCHAR(20), -- 籍贯
Npwd CHAR(6) NOT NULL DEFAULT '123456', -- 登录密码
constraint PK_NURSE_NNO PRIMARY KEY(Nno),
constraint FK_NURSE_RNO FOREIGN KEY (Rno) REFERENCES Room(Rno)
);
-- 8.插入护士数据
INSERT INTO Nurse VALUES
('54001', '53001', '褚昕', '女', 34, '汉族', '马鞍山市', '123456'),
('54002', '53002', '卫正', '女', 25, '回族', '蚌埠市', '123456'),
('54003', '53003', '冯永', '男', 23, '汉族', '阜阳市', '313630'),
('54004', '53004', '钱仨', '女', 34, '汉族', '亳州市', '521125'),
('54005', '53005', '李时代', '女', 21, '汉族', '天长市', '125478'),
('54006', '53006', '秦琪', '女', 30, '汉族', '宣城市', '111111'),
('54007', '53007', '张艳艳', '女', 27, '汉族', '安庆市', '123456');
-- 9.创建药品表
CREATE TABLE Drug (
Dmno CHAR(5) NOT NULL, -- 药品编号
Dmname VARCHAR(50) NOT NULL, -- 药品名称
Dmnum INT NOT NULL, -- 库存量
Dmspecs VARCHAR(20), -- 规格
Dmunit_price NUMERIC(12,2) NOT NULL, -- 药品单价
Dmpd DATE NOT NULL, -- 生产日期
Dmexp DATE NOT NULL, -- 有效日期
constraint PK_DRUG_DMNO PRIMARY KEY(Dmno),
);
-- 10.插入药品数据
INSERT INTO Drug VALUES
('56001', '硝苯地平片', 853, '0.2mg/片', 30, '2016-05-29', '2021-05-29'),
('56002', '消癌平片', 253, '0.3mg/片', 129, '2016-07-09', '2021-07-08'),
('56003', '骨愈灵胶囊', 970, '0.4mg/粒', 24, '2016-02-19', '2021-02-17'),
('56004', '布洛芬', 493, '0.5mg/片', 34, '2015-03-08', '2020-03-06'),
('56005', '鼻炎康', 555, '1.5mg/片', 54, '2016-12-21', '2021-12-20'),
('56006', '奥司他韦', 437, '1.6mg/片', 43, '2016-09-07', '2021-09-06'),
('56007', '利胆药', 760, '5mg/包', 32, '2015-08-29', '2020-08-27'),
('56008', '柴胡注射液', 275, '0.25g/支', 43, '2015-11-24', '2020-11-22'),
('56009', '头孢拉定', 944, '1.5mg/片', 43, '2016-01-18', '2021-01-16'),
('56010', '温胃舒颗粒', 644, '0.5mg/粒', 65, '2015-05-22', '2020-05-20'),
('56011', '肠康片', 121, '1.5mg/片', 23, '2016-07-13', '2021-07-12'),
('56012', '布洛芬', 187, '1.6mg/片', 57, '2015-11-08', '2020-11-06'),
('56013', '阿米沙剌', 399, '2.5mg/片', 45, '2015-09-10', '2020-09-08'),
('56014', '米卡酚', 275, '3.5mg/片', 32, '2016-07-19', '2021-07-18'),
('56015', '阿莫西林', 902, '1.5mg/片', 21, '2016-06-09', '2021-06-08'),
('56016', '感冒灵颗粒', 498, '0.5mg/粒', 17, '2016-07-02', '2021-07-01'),
('56017', '阿米卡星', 339, '1.5mg/片', 43, '2016-04-09', '2021-04-08');
-- 11.创建诊疗表
CREATE TABLE Diagnose (
Ddiagno CHAR(5) NOT NULL, -- 诊疗号
Pno CHAR(5) NOT NULL, -- 患者病历号
Dno CHAR(5) NOT NULL, -- 医生号
Nno CHAR(5), -- 护士号
Dsymptom TEXT NOT NULL, -- 病症
Dhistory TEXT, -- 病史
Dresult TEXT NOT NULL, -- 检查结果及医嘱
Dpd VARCHAR(50), -- 药方
constraint PK_DIAGNOSE PRIMARY KEY(Ddiagno),
constraint FK_DiaGno_Pno FOREIGN KEY (Pno) REFERENCES Patient(Pno),
constraint FK_DiaGno_Dno FOREIGN KEY (Dno) REFERENCES Doctor(Dno),
constraint FK_DiaGno_Nno FOREIGN KEY (Nno) REFERENCES Nurse(Nno)
);
-- 12.插入诊疗数据
INSERT INTO Diagnose VALUES
('57001', '51001', '55001', '54001', '高血压、心律失常', '高血压史', '高血压', '吃药'),
('57002', '51002', '55002', '54002', '面泛黄疸,右上腹疼痛的症状', '肺动脉高压史', '胆囊癌', '吃药、手术'),
('57003', '51003', '55003', '54003', '左膝盖有敲击痛,伴有间歇性小腿肌肉抽筋,持续1个月', '膝内侧受伤病史', '膝盖骨折', '吃药'),
('57004', '51004', '55004', '54004', '偏头痛,畏寒,持续性喷嚏,流清涕', '有带状疱疹病史', '流感', '手术'),
('57005', '51005', '55005', '54005', '外感风寒,发热恶寒无汗,四肢无力,头痛', '有I型糖尿病史', '鼻炎', '吃药'),
('57006', '51006', '55006', '54006', '发热、皮疹、淋巴结肿大', '有哮喘病史', '急性肺炎', '吃药、手术'),
('57007', '51007', '55007', '54007', '腰腹疼痛、血尿、四肢无力', '有哮喘病史', '胆结石', '手术'),
('57008', '51008', '55005', '54005', '外感风寒,发热恶寒无汗,四肢无力,头痛', '有I型糖尿病史', '鼻炎', '吃药'),
('57009', '51009', '55006', '54006', '发热、皮疹、淋巴结肿大', '有哮喘病史', '急性肺炎', '吃药、手术'),
('57010', '51010', '55007', '54007', '腰腹疼痛、血尿、四肢无力', '有哮喘病史', '胆结石', '手术');
-- 13.创建床位表
CREATE TABLE Bed (
Bno CHAR(5) NOT NULL, -- 床位号
Rno CHAR(5) NOT NULL, -- 科室号
Pno CHAR(5), -- 患者病历号
Ddiagno CHAR(5), -- 诊疗号
Bbed_price NUMERIC(12,2) NOT NULL DEFAULT 300, -- 床位单价
Bstate BIT NOT NULL DEFAULT 0, -- 床位状态
constraint PK_BED PRIMARY KEY(Bno),
constraint FK_BED_RNO FOREIGN KEY (Rno) REFERENCES Room(Rno),
constraint FK_BED_PNO FOREIGN KEY (Pno) REFERENCES Patient(Pno),
constraint FK_BED_DDIAGNO FOREIGN KEY (Ddiagno) REFERENCES Diagnose(Ddiagno)
);
-- 14.插入床位数据
INSERT INTO Bed VALUES
('52001', '53001', '51001', '57001', 300, 1),
('52002', '53002', '51002', '57002', 300, 1),
('52003', '53003', '51003', '57003', 300, 1),
('52004', '53004', '51004', '57004', 300, 1),
('52005', '53005', '51005', '57005', 300, 1),
('52006', '53006', '51006', '57006', 300, 1),
('52007', '53007', '51007', '57007', 200, 1),
('52008', '53001', '51008', '57008', 300, 1),
('52009', '53002', '51009', '57009', 300, 1),
('52010', '53003', '51010', '57010', 300, 1),
('52011', '53004', NULL, NULL, 400, 0),
('52012', '53005', NULL, NULL, 300, 0),
('52013', '53006', NULL, NULL, 300, 0),
('52014', '53007', NULL, NULL, 200, 0),
('52015', '53001', NULL, NULL, 300, 0),
('52016', '53002', NULL, NULL, 500, 0),
('52017', '53003', NULL, NULL, 300, 0),
('52018', '53004', NULL, NULL, 300, 0),
('52019', '53005', NULL, NULL, 300, 0),
('52020', '53006', NULL, NULL, 301, 0);
-- 15.创建取药表
CREATE TABLE Take_Drug (
TMno INT NOT NULL IDENTITY(10000,1), -- 取药流水号
Ddiagno CHAR(5) NOT NULL, -- 诊疗号
Dmno CHAR(5) NOT NULL, -- 药品编号
TMnumber INT NOT NULL, -- 取药数量
TMdate DATE NOT NULL, -- 取药日期
constraint PK_TAKE_DRUG PRIMARY KEY(TMno),
constraint FK_TAKE_DRUG_DDIAGNO FOREIGN KEY (Ddiagno) REFERENCES Diagnose(Ddiagno),
constraint FK_TAKE_DRUG_DMNO FOREIGN KEY (Dmno) REFERENCES Drug(Dmno)
);
-- 16.插入取药数据
INSERT INTO Take_Drug (Ddiagno, Dmno, TMnumber, TMdate) VALUES
('57001', '56001', 2, '2018-06-02'),
('57002', '56002', 2, '2018-06-01'),
('57003', '56003', 3, '2018-01-25'),
('57004', '56004', 2, '2017-12-27'),
('57005', '56005', 3, '2017-11-12'),
('57006', '56006', 3, '2017-11-21'),
('57007', '56007', 2, '2017-07-23'),
('57008', '56008', 2, '2017-09-12'),
('57009', '56009', 3, '2017-09-15'),
('57010', '56010', 3, '2017-10-20');
-- 17.创建划价收费表
CREATE TABLE Charges (
Cno INT NOT NULL IDENTITY(10000,1), -- 票据号
Ddiagno CHAR(5) NOT NULL, -- 诊疗号
Pno CHAR(5) NOT NULL, -- 病历号
Cregd_pay NUMERIC(12,2) NOT NULL DEFAULT 20, -- 挂号费用
Cdrug_pay NUMERIC(12,2), -- 药品费用
Cbed_pay NUMERIC(12,2) NOT NULL, -- 床位费用
Chosp_pay NUMERIC(12,2) NOT NULL DEFAULT 0, -- 住院费用
constraint PK_CHARGES PRIMARY KEY(Cno),
FOREIGN KEY (Ddiagno) REFERENCES Diagnose(Ddiagno),
FOREIGN KEY (Pno) REFERENCES Patient(Pno)
);
-- 18.插入划价收费数据
INSERT INTO Charges (Ddiagno, Pno, Cregd_pay, Cdrug_pay, Cbed_pay, Chosp_pay) VALUES
('57001', '51001', 20, NULL, 300, 0),
('57002', '51002', 20, NULL, 300, 0),
('57003', '51003', 20, NULL, 300, 0),
('57004', '51004', 20, NULL, 300, 0),
('57005', '51005', 20, NULL, 300, 0),
('57006', '51006', 20, NULL, 300, 0),
('57007', '51007', 20, NULL, 200, 0),
('57008', '51008', 20, NULL, 300, 0),
('57009', '51009', 20, NULL, 300, 0),
('57010', '51010', 20, 195, 1500, 1715);
-- 19.创建住院表
CREATE TABLE Hospitalize (
Hno INT NOT NULL, -- 住院流水号
Ddiagno CHAR(5) NOT NULL, -- 诊疗号
Pno CHAR(5) NOT NULL, -- 病历号
Rno CHAR(5) NOT NULL, -- 科室号
Bno CHAR(5), -- 床位号
Dno CHAR(5) NOT NULL, -- 医生号
Nno CHAR(5) NOT NULL, -- 护士号
Hinhosp DATE NOT NULL, -- 入院时间
Houthosp DATE, -- 出院时间
constraint PK_HOSPITALIZE PRIMARY KEY(Hno),
constraint FK_HOSPITALIZE_DDIAGNO FOREIGN KEY (Ddiagno) REFERENCES Diagnose(Ddiagno),
constraint FK_HOSPITALIZE_PNO FOREIGN KEY (Pno) REFERENCES Patient(Pno),
constraint FK_HOSPITALIZE_RNO FOREIGN KEY (Rno) REFERENCES Room(Rno),
constraint FK_HOSPITALIZE_BNO FOREIGN KEY (Bno) REFERENCES Bed(Bno),
constraint FK_HOSPITALIZE_DNO FOREIGN KEY (Dno) REFERENCES Doctor(Dno),
constraint FK_HOSPITALIZE_NNO FOREIGN KEY (Nno) REFERENCES Nurse(Nno)
);
-- 20.插入入院数据
INSERT INTO Hospitalize VALUES
(10000, '57001', '51001', '53001', '52001', '55001', '54001', '2018-05-22', NULL),
(10001, '57002', '51002', '53002', '52002', '55002', '54002', '2018-05-29', NULL),
(10002, '57003', '51003', '53003', '52003', '55003', '54003', '2018-01-18', NULL),
(10003, '57004', '51004', '53004', '52004', '55004', '54004', '2017-12-21', NULL),
(10004, '57005', '51005', '53005', '52005', '55005', '54005', '2017-11-08', NULL),
(10005, '57006', '51006', '53006', '52006', '55006', '54006', '2017-11-16', NULL),
(10006, '57007', '51007', '53007', '52007', '55007', '54007', '2017-07-19', NULL),
(10007, '57008', '51008', '53001', '52008', '55005', '54005', '2017-09-07', NULL),
(10008, '57009', '51009', '53002', '52009', '55006', '54006', '2017-09-12', NULL),
(10009, '57010', '51010', '53003', '52010', '55007', '54007', '2017-10-16', '2017-10-20');
-- 21.创建管理员表
CREATE TABLE Manager (
Mno CHAR(5) NOT NULL, -- 管理员号
Mname VARCHAR(20) NOT NULL, -- 姓名
Mpwd CHAR(6) NOT NULL DEFAULT '123456', -- 登录密码
Mlevel BIT NOT NULL DEFAULT 0, -- 级别
constraint PK_MANAGER PRIMARY KEY(Mno)
);
-- 22.插入管理员数据
INSERT INTO Manager VALUES
('10000', '杨浦', '135790', 1),
('10001', '梦岚', '123456', 0),
('10002', '黄强军', '313630', 0);
-- 23.查询所以陈性患者的信息。
select * from Patient
where Pname like '陈%';
-- 24.查询姓苏且全名有三个汉字的患者的信息。
select * from Patient
where Pname like '苏__' AND LEN(Pname) = 3;
-- 25.查询年龄超过35岁的医生的信息。
select * from Doctor
where Dage > 35;
-- 26.查询药品名称中含有“颗粒”和最后一个字是“片”的所有药品的信息。
select * from Drug
where Dmname like '%颗粒%' or Dmname like '%片';
-- 27.查询非汉族护士的姓名、性别、民族和籍贯。
select Nname,Ngender,Nnation,Nnative_place from Nurse
where Nnation != '汉族';
-- 28.查询年龄超过50岁的汉族患者的姓名、性别、年龄和籍贯。
select Pname,Pgender,Page,Pnative_place from Patient
where Page > 50;
-- 29.按药品单价升序显示药品的所有信息。
select * from Drug
order by Dmunit_price asc;
-- 30.按取药数量升序、取药日期降序显示取药表的所有信息。
select * from Take_Drug
order by TMnumber,TMdate desc;
-- 31.按入院时间降序列出住院表中患者病历号、科室表、医生号和入院时间。
select Pno,Rno,Dno,Hinhosp from Hospitalize
order by Hinhosp desc;
-- 32.列出所有患者的病历号和住院天数,并按住院天数降序排列。
select Pno,DATEDIFF(DAY,Hinhosp,Houthosp) 住院天数
from Hospitalize
order by 住院天数 desc
-- 33.显示单价为25~55元的药品的名称和单价(按9折显示)。
select Dmname,Dmunit_price,Dmunit_price*0.9 as 药价9折 from Drug
where Dmunit_price between 25 and 55;
-- 34.查询药品库存量在前15%的药品的信息
select top 15 Percent * from Drug
order by Dmnum desc;
-- 35.列出划价表中药品费用排在后20%的所有信息。
select top 20 percent * from Charges
order by Cdrug_pay;
-- 36.列出籍贯不是合肥市和淮北市的医生的所有信息。
select * from Doctor
where Dnative_place not in ('合肥市','淮北市');
-- 37.查询床位表中空床位的所有信息。
select * from Bed
where Bstate = 0;
-- 38.统计医生人数
select COUNT(*) 医生人数 from Doctor;
-- 39.统计护士的平均年龄。
select AVG(Nage) 平均年龄 from Nurse
-- 40.统计药品规格数
select COUNT(DISTINCT(dmspecs)) 药品规格数
from drug
-- 41.按照性别统计患者人数。
select pgender,COUNT(*) 人数 from Patient
group by pgender
-- 42.列出患者中民族人数超过3人的名族及人数。
select Pnation,COUNT(*) from Patient
Group by Pnation
having COUNT(*) >= 3
-- 43.按性别显示患者的最高年龄、最低年龄和平均年龄。
select Pgender,MAX(Page) 最高年龄,MIN(Page) 最低年龄,AVG(Page) 平均年龄 from Patient
group by Pgender
-- 44.按药品名称显示药品的最高单价,最低单价和平均单价,并按最高单价降序排序。
select dmname,MAX(dmunit_price) 最高单价,
MIN(dmunit_price) 最低单价,
AVG(dmunit_price) 平均单价 from Drug
group by Dmname
order by 最高单价 desc
实验报告五
一、实训环境
MICROSOFT SQL SERVER
二、实验目的
1. 掌握索引的创建、修改及删除方法。 2. 掌握视图的创建、修改及删除方法。 3. 掌握T-SQL编程基础
三、实训内容
(一)索引与视图 课本实验11.7,书写题号(二)t-sql编程已知T-SQL变量命名语法规则如下,请完成第(1)-(3)小题: Ÿ 变量的定义格式: Declare 变量名 变量类型(长度) Ÿ 变量命名规则:以@开头,由数字字母下划线组成; Ÿ 系统变量命名以@@开头; Ÿ 变量赋值的两种方式: select 变量名=变量值 Set变量名=变量值 Ÿ 变量显示的两种方式:
- select 变量名
- Print 变量名 (1)定义三个变量,变量名自定,类型分别为smallint、bit和real,并为之分别赋一个正确的值。
(2)定义两个变量,变量名自定,类型分别为decimal(6,4)和日期类型,并为之分别赋一个正确的值,然后用两种方法显示所赋的值。
(3)在命令方式下求10/3、10%3、10&3的值,并简单分析。 复习流程控制语句,完成第(4)-(8)题。提示:在做以下内容之前先将数据库附加到服务器 (4)使用if语句实现以下功能:任给三个实数,求其中最大的一个。
(5)“水仙花数”是指一个三位数,其各位数的立方和等于该数,如:153=13+53+33 。请使用相应的流程控制语句输出所有的“水仙花数”。
(6)使用相应的流程控制语句实现以下功能:在5秒钟后把课程表中课程号为‘c001’的课程学分改为4分。(waitfor)
(7)查询操作系统课程的考试平均分,如果平均分高于80,打印输出“考试情况良好”,否则,打印输出“考试情况一般”。
(8)查询sc表,在grade列后添加一个计算列“成绩等级”,如果学生成绩高于90分(含),则等级为”优秀”,;如果学生成绩为80-89,则等级为“良好”;如果学生成绩为60-79,则等级为“及格”,否则为“不及格”.
四、实验要求:
在“实验过程记录“栏中用截图方式,记下实验过程。完成实验后,将实验任务书文件,以学号加姓名方式命名(如“16710001张林.doc”),并发送至学习通。
五、实验过程记录(注意:先将每1小题题目复制下去,再用截图方式完成任务)
(一)
为数据表patient建立按照pno升序排列,索引名为pno_index的聚集索引。
给数据表patient建立按照pname降序排列,索引名为pname的非聚集索引。
为数据表bed建立按照bno降序排列,索引名为bno_index的唯一索引。
给数据表diagnose建立按照dno降序,nno升序排列,索引名为dno_nno_index的非聚集索引。
为数据表doctor建立按照dnation升序排列,dage降序排列,索引名为dnation_dage_index的唯一索引。
将数据表patient的索引名pname修改为pname_index。
将数据表diagnose中索引名为dno_nno_index的非聚集索引删除。
查看patient数据表中建立的索引。
重新生成数据表patient中索引名为pname_index的非聚集索引。
重新组织数据表patient中索引名为pname_index的非聚集索引。
更新数据表patient的索引统计信息。
更新患者住院管理数据库中所有数据表的索引统计信息。
显示patient数据表的数据和索引碎片信息。
显示patient数据表中pname_index的索引碎片信息。
创建基于patient表的男性患者视图,视图名为pgender_view。
将视图pgender_view修改为包括'男'或者'汉族'的患者视图。
将视图pgender_view重命名为pgender_pnation_view。
向pgender_pnation_view视图中插入一行数据。('51099','王新','男',35,'汉族','合肥市')
将pgender_pnation_view视图中患者杨洁的page改为48。
从pgender_pnation_view视图中删除男性患者的信息。
删除pgender_pnation_view视图 (二)t-sql编程
已知T-SQL变量命名语法规则如下,请完成第(1)-(3)小题:
- Ÿ 变量的定义格式: Declare 变量名 变量类型(长度)
- Ÿ 变量命名规则:以@开头,由数字字母下划线组成;
- Ÿ 系统变量命名以@@开头;
- Ÿ 变量赋值的两种方式: select 变量名=变量值 Set变量名=变量值
- Ÿ 变量显示的两种方式: select 变量名 Print 变量名 (1)定义三个变量,变量名自定,类型分别为smallint、bit和real,并为之分别赋一个正确的值。
(2)定义两个变量,变量名自定,类型分别为decimal(6,4)和日期类型,并为之分别赋一个正确的值,然后用两种方法显示所赋的值。
(3)在命令方式下求10/3、10%3、10&3的值,并简单分析。 复习流程控制语句,完成第(4)-(8)题。提示:在做以下内容之前先将数据库附加到服务器。 (4)使用if语句实现以下功能:任给三个实数,求其中最大的一个。
(5)“水仙花数”是指一个三位数,其各位数的立方和等于该数,如:153=1^3+5^3+3^3 。请使用相应的流程控制语句输出所有的“水仙花数”。
(6)使用相应的流程控制语句实现以下功能:在5秒钟后把课程表中课程号为‘c001’的课程学分改为4分。(waitfor)
(7)查询操作系统课程的考试平均分,如果平均分高于80,打印输出“考试情况良好”,否则,打印输出“考试情况一般”。
(8)查询sc表,在grade列后添加一个计算列“成绩等级”,如果学生成绩高于90分(含),则等级为”优秀”,;如果学生成绩为80-89,则等级为“良好”;如果学生成绩为60-79,则等级为“及格”,否则为“不及格”.
六、参考答案
(一)
-- 1.为数据表patient建立按照pno升序排列,索引名为pno_index的聚集索引。
CREATE CLUSTERED INDEX pno_index
ON patient(pno);
-- 2.给数据表patient建立按照pname降序排列,索引名为pname的非聚集索引。
CREATE NONClustered index pname
on patient(pname desc)
-- 3.为数据表bed建立按照bno降序排列,索引名为bno_index的唯一索引。
CREATE UNIQUE INDEX bno_index
on bed(bno desc)
-- 4.给数据表diagnose建立按照dno降序,nno升序排列,索引名为dno_nno_index的非聚集索引。
CREATE nonclustered index dno_nno_index
on diagnose(dno desc,nno)
-- 5.为数据表doctor建立按照dnation升序排列,dage降序排列,
-- 索引名为dnation_dage_index的唯一索引。
CREATE UNIQUE INDEX dnation_dage_index
on doctor(dnation, dage desc);
-- 6. 将数据表patient的索引名pname修改为pname_index。
EXECUTE sp_rename 'patient.pname', 'pname_index', 'index';
-- 7.将数据表diagnose中索引名为dno_nno_index的非聚集索引删除。
DROP index diagnose.dno_nno_index
-- 8.查看patient数据表中建立的索引。
EXECUTE sp_helpindex patient
-- 9.重新生成数据表patient中索引名为pname_index的非聚集索引。
ALter index pname_index ON patient REBUILD
-- 10.重新组织数据表patient中索引名为pname_index的非聚集索引。
Alter index pname_index on patient reorganize
-- 11.更新数据表patient的索引统计信息。
Update statistics patient
-- 12.更新患者住院管理数据库中所有数据表的索引统计信息。
use HZZYGL
EXEC sp_updatestats
-- 13.显示patient数据表的数据和索引碎片信息。
DBCC showcontig('patient')
-- 14.显示patient数据表中pname_index的索引碎片信息。
DBCC showcontig('patient',pname_index)
go
-- 15.创建基于patient表的男性患者视图,视图名为pgender_view。
create view pgender_view
as
select * from patient
where Pgender = '男'
go
select * from pgender_view
-- 16. 将视图pgender_view修改为包括'男'或者'汉族'的患者视图。
Alter view pgender_view
as
select *
from patient
where pgender = '男' or Pnation = '汉族';
select * from pgender_view
-- 17.将视图pgender_view重命名为pgender_pnation_view。
EXECUTE sp_rename pgender_view, pgender_pnation_view;
select * from pgender_pnation_view
-- 18.向pgender_pnation_view视图中插入一行数据。('51099','王新','男',35,'汉族','合肥市')
insert into pgender_pnation_view
values
('51099','王新','男',35,'汉族','合肥市');
-- 19.将pgender_pnation_view视图中患者杨洁的page改为48。
update pgender_pnation_view
set page = 48
where pname = '杨洁'
-- 20.从pgender_pnation_view视图中删除男性患者的信息。
delete from pgender_pnation_view where pgender = '男'
-- 禁用约束
ALTER TABLE Diagnose NOCHECK CONSTRAINT FK_DiaGno_Pno;
ALTER TABLE Bed NOCHECK CONSTRAINT FK_BED_PNO;
ALTER TABLE Charges NOCHECK CONSTRAINT FK__Charges__Pno__1ED998B2;
ALTER TABLE Hospitalize NOCHECK CONSTRAINT FK_HOSPITALIZE_PNO;
-- 恢复Diagnose表的外键约束
ALTER TABLE Diagnose WITH CHECK CHECK CONSTRAINT FK_DiaGno_Pno;
-- 恢复Bed表的外键约束
ALTER TABLE Bed WITH CHECK CHECK CONSTRAINT FK_BED_PNO;
-- 恢复Charges表的外键约束
ALTER TABLE Charges WITH CHECK CHECK CONSTRAINT FK__Charges__Pno__1ED998B2;
-- 恢复Hospitalize表的外键约束
ALTER TABLE Hospitalize WITH CHECK CHECK CONSTRAINT FK_HOSPITALIZE_PNO;
-- 21.删除pgender_pnation_view视图
drop view pgender_pnation_view
(二)
-- 1.定义三个变量,变量名自定,类型分别为smallint、bit和real,并为之分别赋一个正确的值。
declare @a smallint = 12
declare @b bit = 1
declare @c real = 8.77
print '变量@a的值为' + cast(@a as varchar(10))
print '变量@b的值为' + cast(@b as varchar(10))
print '变量@c的值为' + cast(@c as varchar(10))
-- 2.定义两个变量,变量名自定,类型分别为decimal(6,4)和日期类型,
-- 并为之分别赋一个正确的值,然后用两种方法显示所赋的值。
declare @e decimal(6,4) = 12.345655
declare @d date = getdate()
print '变量@e的值为' + cast(@e as varchar(10))
print '变量@d的值为' + cast(@d as varchar(10))
-- 3.在命令方式下求10/3、10%3、10&3的值,并简单分析。
SELECT
10/3 AS [10除以3],
10%3 AS [10取模3],
10&3 AS [10与3按位与];
-- 4.使用if语句实现以下功能:任给三个实数,求其中最大的一个。
DECLARE
@f REAL = 12.34,
@g REAL = 56.78,
@h REAL = 9.01,
@max REAL;
-- 使用嵌套IF比较三个数
IF @f > @g
BEGIN
IF @f > @h
SET @max = @f;
ELSE
SET @max = @h;
END
ELSE
BEGIN
IF @g > @h
SET @max = @g;
ELSE
SET @max = @h;
END
-- 输出结果
PRINT '三个数中的最大值是: ' + CAST(@max AS VARCHAR(20));
-- 5.“水仙花数”是指一个三位数,其各位数的立方和等于该数,如:153=1^3+5^3+3^3 。
-- 请使用相应的流程控制语句输出所有的“水仙花数”。
DECLARE @num INT = 100;
DECLARE @i INT, @j INT, @k INT;
PRINT '三位数的水仙花数有:';
WHILE @num <= 999
BEGIN
-- 分解各位数字
SET @i = @num / 100; -- 百位数
SET @j = (@num % 100) / 10; -- 十位数
SET @k = @num % 10; -- 个位数
-- 检查是否为水仙花数
IF POWER(@i, 3) + POWER(@j, 3) + POWER(@k, 3) = @num
PRINT CAST(@num AS VARCHAR(10));
SET @num = @num + 1;
END
-- 6.使用相应的流程控制语句实现以下功能:在5秒钟后把课程表中课程号为‘c001’的课程学分改为4分。(waitfor)
-- 修改前
select * from course
-- 在5秒钟后把课程表中课程号为‘c001’的课程学分改为4分。(waitfor)
PRINT '开始执行,将在5秒后更新课程学分...';
-- 等待5秒
WAITFOR DELAY '00:00:05';
-- 执行更新操作
BEGIN TRY
BEGIN TRANSACTION;
UPDATE course
SET Ccredit = 4
WHERE Cno = 'C001';
-- 检查是否成功更新
IF @@ROWCOUNT > 0
PRINT '成功将课程号c001的学分更新为4分';
ELSE
PRINT '未找到课程号为c001的课程记录';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT '更新失败: ' + ERROR_MESSAGE();
END CATCH
-- 修改后
select * from course
-- 7.查询操作系统课程的考试平均分,
-- 如果平均分高于80,打印输出“考试情况良好”,否则,打印输出“考试情况一般”。
declare @result real =
(select AVG(Grade) from sc where Cno = 'C010')
-- 根据平均分输出评估结果
IF @result > 80
BEGIN
PRINT '课程名称: 操作系统';
PRINT '平均分: ' + CAST(@result AS VARCHAR(10));
PRINT '考试情况: 良好';
END
ELSE
BEGIN
PRINT '课程名称:操作系统 ' ;
PRINT '平均分: ' + CAST(@result AS VARCHAR(10));
PRINT '考试情况: 一般';
END
-- 8.查询sc表,在grade列后添加一个计算列“成绩等级”,
-- 如果学生成绩高于90分(含),则等级为”优秀”;
-- 如果学生成绩为80-89,则等级为“良好”;
-- 如果学生成绩为60-79,则等级为“及格”,否则为“不及格”.
SELECT
Sno AS 学号,
Cno AS 课程号,
Grade AS 成绩,
CASE
WHEN Grade >= 90 THEN '优秀'
WHEN Grade >= 80 THEN '良好'
WHEN Grade >= 60 THEN '及格'
ELSE '不及格'
END AS 成绩等级
FROM SC;
实验报告六
一、实训环境
MICROSOFT SQL SERVER
二、实验目的:
1. 掌握存储过程的创建及调用方法。
2. 掌握各类触发器的创建方法。
三、实验准备:
掌握的知识要点:
1. 带有输入及输出参数的存储过程的创建及调用方法。
2. 前置触发器及后置触发器的编写方法。
四、实验内容
1.创建一个名为“proc_1”的存储过程,用于查看学生表的所有信息。然后调用该存储过程。
2.创建一个名为“proc_2”的存储过程,用于向学生表的所有字段添加一条记录,记录具体内容由调用时决定。然后调用该存储过程。
3.创建一个名为“proc_3”的存储过程,用于删除学生表中指定学号的记录,具体学号由调用时决定。然后调用该存储过程。
4.修改存储过程“proc_3”,用于查询不小于指定成绩的学生的基本信息,具体成绩由调用时决定。
5.创建存储过程“proc4”,用于按指定病人病历编号查询该病人的诊疗费用信息。
观察题1:
创建一个名为“proc_4”的存储过程,用于求一个3位整数的反序数。例如123的反序数为321。
create proc proc_4
@a int
as
declare @b int,@c int,@t int,@s int
begin
set @t=@a % 10
select @b=@a/10,@b=@b % 10
set @c=@a/100
set @s=@t100+@b10+@c
select @s
end
go
exec proc_4 123
6.创建一个名为“trig_1”的触发器,当向学生表添加记录时,该触发器自动显示学生表的所有信息。
7.修改名为“trig_1”触发器,当试图向学生表添加、修改或删除记录时,该触发器自动显示如下信息:“对不起,你无权进行更新操作!”。
8.修改“trig_1”的触发器,当向学生表修改记录时,该触发器自动显示修改前(deleted)和修改后(inserted)的记录。
9.修改“trig_1”的触发器,当向学生表(STUDENT)添加记录时,该触发器自动向成绩表(SC)中添加该学生的‘C001’号和‘c002’号课程成绩记录,其成绩暂时为空值。
(以上关于触发器的各小题,建立好触发器后,需要用代码触发触发器运行,验证效果)
观察题2:
创建一个名为“trig_4”的触发器,当向成绩表添加记录时,该触发器自动显示与该记录相关的学生的学号、姓名和班级。(本题要求创建之前先判断该触发器是否存在)
if exists(select name from sysobjects where name=' trig_4' and type='TR')
drop trigger trig_4
go
create trigger trig_4
on sc
for insert
as
select sno,sname,class
from student
where sno in (select sno from inserted)
go
insert sc
values('20180401','c002',80) --验证触发器
五、参考答案
-- 1.创建一个名为“proc_1”的存储过程,用于查看学生表的所有信息。
-- 然后调用该存储过程。
create proc proc_1
as
BEGIN
select * from student;
END
go
exec proc_1
go
-- 2.创建一个名为“proc_2”的存储过程,用于向学生表的所有字段添加一条记录,
-- 记录具体内容由调用时决定。然后调用该存储过程。
create proc proc_2
@sno CHAR(9),
@sname VARCHAR(6),
@Ssex CHAR(2),
@Sage INT,
@Sdept VARCHAR(20)
as
BEGIN
insert into student values
(@sno,@sname,@Ssex,@Sage,@Sdept)
END
go
exec proc_2 'S0031', '尹家顺', '男', 20, '信息系'
go
-- 3.创建一个名为“proc_3”的存储过程,用于删除学生表中指定学号的记录,
-- 具体学号由调用时决定。然后调用该存储过程。
create proc proc_3
@Sno varchar(15)
as
BEGIN
delete from student where Sno = @Sno
END
go
exec proc_3 'S0031'
go
-- 4.修改存储过程“proc_3”,用于查询不小于指定成绩的学生的基本信息,
-- 具体成绩由调用时决定。
Alter proc proc_3
@Grade int
as
BEGIN
select DISTINCT student.* from student
join sc on sc.sno = student.Sno
where sc.Grade >= @Grade
END
go
exec proc_3 80
go
-- 5.建存储过程“proc4”,用于按指定病人病历编号查询该病人的诊疗费用信息。
ALter proc proc_4
@pno varchar(20)
as
BEGIN
SELECT
cno,
ddiagno,
pno,
Cregd_pay AS '挂号费',
Cdrug_pay AS '药品费',
Cbed_pay AS '床位费',
Chosp_pay AS '住院费'
FROM
Charges
WHERE
pno = @pno
END
GO
exec proc_4 '51001'
go
-- 6.观察题1:
-- 创建一个名为“proc_4”的存储过程,用于求一个3位整数的反序数。
-- 例如123的反序数为321。
Alter proc proc_4
@n int
as
BEGIN
declare @a int
declare @b int
declare @c int
select @a = @n/100
select @b = (@n%100)/10
select @c = (@n%10)
declare @answer int
select @answer = @c*100 + @b*10 + @a
print cast(@answer as varchar(50))
END
exec proc_4 134
go
-- 7.创建一个名为“trig_1”的触发器,当向学生表添加记录时,
-- 该触发器自动显示学生表的所有信息。
create Trigger trig_1
on student
for insert
as
BEGIN
select * from student
END
go
insert into student values
('S0031', '尹家顺', '男', 20, '信息系')
go
-- 9.修改名为“trig_1”触发器,当试图向学生表添加、修改或删除记录时,
-- 该触发器自动显示如下信息:“对不起,你无权进行更新操作!”。
ALTER TRIGGER trig_1
ON STUDENT
FOR INSERT, UPDATE, DELETE
AS
BEGIN
PRINT '对不起,你无权进行更新操作!'
ROLLBACK TRANSACTION
END
GO
delete from student where Sno = 'S0031'
go
-- 10.修改“trig_1”的触发器,当向学生表修改记录时,
-- 该触发器自动显示修改前(deleted)和修改后(inserted)的记录。
alter trigger trig_1
on student
for update
as
BEGIN
select * from deleted
select * from inserted
END
update student set Sage = 25 where Sno = 'S0031'
go
-- 11.修改“trig_1”的触发器,当向学生表(STUDENT)添加记录时,
-- 该触发器自动向成绩表(SC)中添加该学生的‘C001’号和‘c002’号课程成绩记录,
-- 其成绩暂时为空值。
alter trigger trig_1
on student
for insert
as
BEGIN
INSERT INTO SC(sno, cno, Grade)
SELECT sno, 'C001', NULL FROM inserted
INSERT INTO SC(sno, cno, Grade)
SELECT sno, 'C002', NULL FROM inserted
END
go
insert into STUDENT VALUES('S0032', '赵阿', '男', 20, '信息系')
select * from sc
-- 观察题2:
-- 创建一个名为“trig_4”的触发器,当向成绩表添加记录时,
-- 该触发器自动显示与该记录相关的学生的学号、姓名和班级。
-- (本题要求创建之前先判断该触发器是否存在)
if exists(select name from sysobjects where name=' trig_4' and type='TR')
drop trigger trig_4
go
create trigger trig_4
on sc
for insert
as
select sno,sname,Sdept
from student
where sno in (select sno from inserted)
go
insert sc
values('S0011','c002',80) --验证触发器
实验报告七
一、实训环境
MICROSOFT SQL SERVER
二、实验目的:
1. 理解SQL Server的安全性机制。 2. 掌握标准SQL Server登录和集成Windows NT登录下数据库用户账号的建立与取消方法。 3. 掌握数据库用户权限的设置方法。 4. 熟悉数据库用户账号的权限分配、回收方法。
三、实验准备:
掌握的知识要点: 1. SQL Server的安全性机制。 2. SQL Server的3个等级安全性管理:操作系统级、SQL Server级和数据库级。 3. 数据库用户、服务器角色及数据库角色的用法。
四、实验内容:
1.在服务器下创建一个名为“user12”的登陆名,密码为“123456”。 2.将该登陆名“user12”注册为“XSCJGL”数据库的用户。 3.给“user12”用户赋予“student”和“sc”的查询、插入权限。 4.赋予“user12”创建表的权限。 5.使用“user12”身份登陆系统,并验证所获得的查询、插入、创建表权限。(grant) 6.利用管理员身份登录系统,禁止用户“user12”所获得的权限。(deny) 7.使用“user12”身份登录系统,并验证所获得的查询、插入、创建表权限。 8.利用管理员身份登录系统,收回用户“user12”所获得的权限。(revoke) 9.使用“user12”身份登录系统,并验证所获得的查询、插入、创建表权限。 10.为HISDB数据库创建一个名称为“myrole”的数据库角色,设置其对所有表具有select权限,此外,对patient和doctor表还具有修改、插入或删除权限。 11.为HISDB数据库创建一个名为“zhangsan”的客户,并将该用户添加为myrole角色成员。
五、参考答案
-- 1.在服务器下创建一个名为“user12”的登陆名,密码为“123456”。
CREATE LOGIN user12 WITH PASSWORD = '123456';
-- 2.将该登陆名“user12”注册为“XSGL”数据库的用户。
use XSGL
CREATE USER user12 FOR LOGIN user12;
-- 3.给“user12”用户赋予“student”和“sc”的查询、插入权限。
GRANT SELECT, INSERT ON student TO user12;
GRANT SELECT, INSERT ON sc TO user12;
-- 4.赋予“user12”创建表的权限。
GRANT CREATE TABLE TO user12;
ALTER USER user12 WITH DEFAULT_SCHEMA = dbo;
GRANT ALTER ON SCHEMA::dbo TO user12;
-- 5.使用“user12”身份登陆系统,并验证所获得的查询、插入、创建表权限。(grant)
use XSGL
-- 验证查询权限
SELECT * FROM student;
-- 验证插入权限
INSERT INTO student VALUES('S0034', '洪想想', '男', 22, '信息系');
-- 验证创建表权限
CREATE TABLE test_table(id INT);
-- 6.利用管理员身份登录系统,禁止用户“user12”所获得的权限。(deny)
use XSGL
DENY SELECT, INSERT ON student TO user12;
DENY SELECT, INSERT ON sc TO user12;
DENY CREATE TABLE TO user12;
-- 7.使用“user12”身份登录系统,并验证所获得的查询、插入、创建表权限。
use XSGL
-- 验证查询权限
SELECT * FROM student;
-- 验证插入权限
INSERT INTO student VALUES('S0037', '阿松大', '男', 22, '信息系');
-- 验证创建表权限
CREATE TABLE test_table2(id INT);
-- 8.利用管理员身份登录系统,收回用户“user12”所获得的权限。(revoke)
use XSGL
REVOKE SELECT, INSERT ON student TO user12;
REVOKE SELECT, INSERT ON sc TO user12;
REVOKE CREATE TABLE TO user12;
-- 9.使用“user12”身份登录系统,并验证所获得的查询、插入、创建表权限。
use XSGL
-- 验证查询权限
SELECT * FROM student;
-- 验证插入权限
INSERT INTO student VALUES('S0034', 'hsc', '男', 22, '信息系');
-- 验证创建表权限
CREATE TABLE test_table2(id INT);
-- 10.为HISDB数据库创建一个名称为“myrole”的数据库角色,
-- 设置其对所有表具有select权限,
-- 此外,对patient和doctor表还具有修改、插入或删除权限。
USE HISDB;
CREATE ROLE myrole;
-- 对所有表授予SELECT
GRANT SELECT ON SCHEMA::dbo TO myrole;
-- 对特定表授予额外权限
GRANT INSERT, UPDATE, DELETE ON patient TO myrole;
GRANT INSERT, UPDATE, DELETE ON doctor TO myrole;
-- 11.为HISDB数据库创建一个名为“zhangsan”的客户,
-- 并将该用户添加为myrole角色成员。
use master;
CREATE LOGIN zhangsan WITH PASSWORD = 'password123';
USE HISDB;
CREATE USER zhangsan FOR LOGIN zhangsan;
EXEC sp_addrolemember 'myrole', 'zhangsan';