数据库基础练习实验

最新推荐文章于 2023-07-06 12:05:22 发布

Link2Points

最新推荐文章于 2023-07-06 12:05:22 发布

阅读量294

点赞数

分类专栏：学习日志（一）文章标签： 1024程序员节

本文链接：https://blog.csdn.net/Link2Points/article/details/127498183

版权

学习日志（一）专栏收录该内容

24 篇文章 0 订阅

订阅专栏

基于SQL Sever。注：或有错误的地方

实验一

创建表
修改表
添加规则
插入数据
更新数据

create database 学生信息
use 学生信息
go

create table 学生信息
(
编号 int identity(1,1),
学号 varchar(8) primary key not null,
学生姓名 varchar(20) unique,
性别 varchar(2) check(性别 in ('男','女')),
年龄 varchar(20),
密码 varchar(20) not null,
家庭所在城市 varchar(50),
)

create rule 家庭所在城市
as
@value in ('北京市','广州市','南京市','上海市','深圳市','天津市','西安市','郑州市')
sp_bindrule 家庭所在城市,'学生信息.家庭所在城市'

alter table 学生信息 add 身份证号 varchar(18) not null
create rule 身份证号_规则
as
@value like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or 
@value like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][X]'

create rule 身份证号_规则2
as
@value like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9,X]'
sp_bindrule 身份证号_规则,'学生信息.身份证号'

insert into 学生信息(学号,学生姓名,性别,密码,家庭所在城市,身份证号)
values('SW17010','你黑','男','SW17010','上海市','350012199902128123')/*因为身份证号不为空*/
 
update 学生信息
set 学生姓名='王者荣耀'
where 学号='SW17010'

实验二

嵌套查询
组合及统计查询

/*******************************
创建数据库，数据表
*******************************/
/*创建数据库student_course*/
USE MASTER  
GO 
IF db_id('student_course') IS NOT NULL
	DROP DATABASE  student_course
GO
CREATE DATABASE student_course
GO
USE student_course

/*1.学生表*/
create table student(
studentid char(5) primary key,
student_name char(8) not null,
age smallint default 20,
gender char(2),
department char(20),
check (gender in ('男','女'))
)
GO

/*2.课程表*/
create table course(
courseid char(5) primary key,
course_name char(20) not null,
pre_id char(5)
);
GO

/*3.选修表*/
create table sc(
studentid char(5),
courseid char(5) not null,
grade smallint,
check (grade between 0 and 100),
primary key (studentid,courseid),
foreign key (studentid) references student(studentid),
foreign key (courseid) references course(courseid),
);
GO

/*增加数据表样本数据*/
insert 
into student
values('S1','李明',21,'男','计算机');
insert 
into student
values('S2','张小红',21,'男','计算机');
insert 
into student
values('S3','李和明',22,'女','计算机');
insert 
into student
values('S4','张三',21,'男','计算机');
insert 
into student
values('S5','刘宏',23,'男','计算机');
insert 
into student
values('S6','王红应',20,'女','计算机');
insert 
into student
values('S7','吴非',19,'男','数学');
insert 
into student
values('S8','丁玉',21,'男','数学');
insert 
into student
values('S9','赵名',21,'女','数学');
insert 
into student
values('S12','张共可',22,'男','物理');



insert 
into course
values('C1','计算机引论',null);
insert 
into course
values('C2','数据结构','C3');
insert 
into course
values('C3','C语言编程','C1');
insert 
into course
values('C4','软件工程','C6');
insert 
into course
values('C6','数据库','C2');
insert 
into course
values('C5','计算机文化',null);
insert 
into course
values('C7','高等数学',null);
insert 
into course
values('C8','概率统计','C9');
insert 
into course
values('C9','线性代数','C7');
insert 
into course
values('C10','力学',null);


insert 
into sc
values('S1','C1',60);
insert 
into sc
values('S2','C1',93);
insert 
into sc
values('S3','C1',null);
insert 
into sc
values('S4','C1',89);
insert 
into sc
values('S1','C2',79);
insert 
into sc
values('S2','C2',null);
insert 
into sc
values('S3','C2',80);
insert 
into sc
values('S4','C3',90);
insert 
into sc
values('S1','C3',92);
insert 
into sc
values('S2','C3',81);
insert 
into sc
values('S1','C7',85);
insert 
into sc
values('S4','C7',75);


select * from student
select * from course
select * from sc
--1、嵌套查询
/*1）求选修了C语言编程的学生学号和姓名。*/
select studentid,student_name
from student
where studentid in
(
	select studentid
	from sc
	where courseid in
	(
		select courseid
		from course
		where course_name='C语言编程'
	)
)
/*2）求C1课程的成绩高于李明的学生学号和成绩。*/
select studentid ,grade
from sc
where courseid='C1' and grade >
(	
	select grade
	from sc,student s
	where s.student_name='李明'  and courseid='C1' and s.studentid=sc.studentid
)
/*3）求没有选修C7课程的学生姓名。*/
select student_name
from student
where not exists
(
	select *
	from sc
	where courseid='C7' and student.studentid=sc.studentid
)
/*4）查询选修了全部课程的学生的姓名。*/
select student_name
from student
where not exists
(
	select *
	from sc
	where not exists
	(
		select *
		from course
		where course.courseid=sc.courseid and student.studentid=sc.studentid
	)
)
/*5）查询被学生S1选修，但没有被S3选修的课程号*/
select courseid
from sc
where studentid='S1' and courseid not in
(
	select courseid
	from sc sc1
	where sc1.studentid='S3'
)
/*6）查询至少选修了学号为S2的学生所选修的全部课程的学生学号和姓名。*/
select studentid,student_name
from student
where not exists
(
	select courseid
	from sc sc1
	where sc1.studentid='S2' and not exists
	(
		select courseid
		from sc sc2
		where sc2.studentid=student.studentid and sc1.courseid=sc2.courseid and sc2.studentid!='S2'
	)
)
/*7) 查询与 丁玉 在同一个系学习的学生*/
select *
from student
where department in
(
	select department
	from student
	where student_name='丁玉'
)
/*8) 查询 每个学生超过他 选修课程平均成绩的课程号*/
select studentid,courseid
from sc sc1
where grade>
(
	select AVG(grade)
	from sc sc2
	where sc1.courseid=sc2.courseid
	
)
order by sc1.studentid asc
---------------------------------------------------------
select * from student
select * from course
select * from sc
--2、组合查询及统计查询
--（2）在student_course库中，编写SQL语句，实现以下查询要求
--1）查询选修C1课程，并且也选修C2课程的学生学号。
select distinct sc1.studentid
from sc sc1,sc sc2
where sc1.courseid='C1' and sc2.courseid='C2' and sc1.studentid=sc2.studentid
--2）查询选修了C1课程但没有选修C2课程的学生学号。
select distinct sc1.studentid
from sc sc1,sc sc2
where sc1.courseid='C1' and sc1.studentid=sc2.studentid and sc2.courseid not in 
(
	select courseid
	from sc sc3
	where sc3.courseid='C2' 
)
--3）查询学生的总人数
select count(studentid)'学生的总人数'
from student
--4）查询选修了课程的学生人数。
select count(studentid)'选修了课程的学生人数'
from student s
where studentid in
(
	select studentid
	from sc
	where s.studentid=sc.studentid
)
--5）查询课程和选修该课程的人数。
select sc1.courseid,count(sc1.studentid)'选修该课程的人数'
from sc sc1,sc sc2
where sc1.studentid=sc2.studentid and sc1.courseid=sc2.courseid
group by sc1.courseid
--6）查询选修课超过3门课的学生学号
select studentid
from sc
group by studentid
having count(studentid)>3

实验三

条件查询

/*******************************
创建数据库，数据表
*******************************/
/*创建数据库library*/
USE MASTER  
GO 
IF db_id('library') IS NOT NULL
	DROP DATABASE  library
GO
CREATE DATABASE library
GO
USE library

/*1.图书表*/
create table book(
bookid char(10) primary key,
book_type char(12) not null,
publisher char(30),
author char(20),
book_name char(50) not null,
price money,
check (price > 0)
)
go

/*2.读者表*/
create table reader(
readerid char(8) primary key,
reader_name char(8) not null,
department char(30),
gender char(2),
tel char(11),
check (gender in ('男','女'))
)
go

/*3.借阅表*/
create table borrow(
bookid char(10),
readerid char(8),
borrow_date datetime not null,
primary key (bookid,readerid),
foreign key (bookid) references book(bookid),
foreign key (readerid) references reader (readerid)
)
go

/*增加数据表样本数据*/
insert 
into book
values('1000000001','计算机','机械工业出版社','李明','计算机引论',18.00);
insert 
into book
values('1000000002','计算机','机械工业出版社','王小红','数据结构',22.00);
insert 
into book
values('1000000003','计算机','机械工业出版社','李和明','C语言编程',25.50);
insert 
into book
values('1000000004','计算机','电子工业出版社','刘宏亮','操作系统',49.80);
insert 
into book
values('1000000006','计算机','机械工业出版社','刘宏亮','数据结构',21.60);
insert 
into book
values('1000000005','计算机','电子工业出版社','王小红','计算机文化',20.00);
insert 
into book
values('2000000007','数学','机械工业出版社','吴非','计算机文化',18.00);
insert 
into book
values('2000000008','数学','机械工业出版社','丁玉应','概率统计',22.30);
insert 
into book
values('2000000009','数学','电子工业出版社','赵名','线性代数',15.00);
insert 
into book
values('2000000010','物理','电子工业出版社','张共可','力学',19.80);

insert 
into reader
values('10000001','李小明','计算机系','男','13826388323');
insert 
into reader
values('10000002','王红','计算机系','男','13826388378');
insert 
into reader
values('10000003','李和平','计算机系','女','13826385523');
insert 
into reader
values('10000004','刘宏亮','计算机系','男','13826387623');
insert 
into reader
values('10000006','刘宏亮','计算机系','男','13826387623');
insert 
into reader
values('10000005','王小红','数学系','女','13826381223');
insert 
into reader
values('10000007','吴小','数学系','男','13826366323');
insert 
into reader
values('10000008','丁玉应','数学系','男','13826366323');
insert 
into reader
values('10000009','赵名','数学系','女','13826348323');
insert 
into reader
values('10000010','张共可','计算机系','男','13826384523');

insert 
into borrow
values('1000000001','10000001','1998-11-25');
insert 
into borrow
values('1000000002','10000002','1998-12-20');
insert 
into borrow
values('1000000003','10000003','1999-6-5');
insert 
into borrow
values('1000000004','10000004','2006-11-25');
insert 
into borrow
values('1000000006','10000004','2006-11-25');
insert 
into borrow
values('1000000005','10000001','2006-11-25');
insert 
into borrow
values('2000000007','10000001','2006-11-25');
insert 
into borrow
values('2000000008','10000003','2006-11-25');
insert 
into borrow
values('2000000009','10000004','2006-11-25');
insert 
into borrow
values('2000000010','10000001','2006-11-25');


--select *from book
--1）查询机械工业出版社出版的各类图书的平均定价。
select avg(price)'平均定价'
from book
where publisher='机械工业出版社'
--2）查询计算机类或机械工业出版社出版的图书。
select *
from book
where book_type='计算机' or publisher='机械工业出版社'
--3) 查询同一出版社出版的同名 图书
select b1.*
from book b1,book b2
where b1.publisher=b2.publisher and b1.book_name=b2.book_name and b1.bookid!=b2.bookid
--4）查找某种图书类别，要求该类别中最高图书定价不低于每个类别的平均定价
select book_type
from book
group by book_type
having max(price)>=
(
	select avg(price)
	from book
)
--5) 查询同一作者在不同出版社出版的图书
select distinct b1.*
from book b1,book b2
where b1.author=b2.author and b1.publisher!=b2.publisher

select * from reader
select * from borrow
--6) 查询读者总人数
select count(readerid)'读者总人数'
from reader
--7) 查询有借书的读者人数
select count(readerid)'有借书的读者人数'
from reader r
where readerid in
(
	select readerid 
	from borrow b
	where r.readerid=b.readerid
)
--8) 查询不同读者借书的册数
select b1.readerid,count(b1.readerid)'借书的册数'
from borrow b1,borrow b2
where b1.readerid=b2.readerid and b1.bookid=b2.bookid
group by b1.readerid
--9) 查询借书数目超过1本的读者编号，姓名
select b.readerid, r.reader_name
from borrow b,reader r
where b.readerid=r.readerid
group by b.readerid , r.reader_name
having count(b.readerid)>1