目录
一、数据库相关
增改
create database library
use library
go
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)
)
create table reader
(
readerid char(8) primary key,
reader_name char(8) not null,
department char(30),
gender char(2) check(gender in('男','女')),
tel char(11),
)
create table borrow
(
bookid char(10),
readerid char(8),
boorrow_date datetime not null,
primary key(bookid,readerid),
foreign key(readerid) references reader(readerid),
foreign key(bookid) references book(bookid)
)
insert into book
values ('1000000001','计算机','机械工业出版社','李明','计算机引论',18.00),
('1000000002','计算机','机械工业出版社','王小红','数据结构',22.00),
('1000000003','计算机','机械工业出版社','李和明','C语言编程',25.50),
('1000000004','计算机','机械工业出版社','刘宏亮','操作系统',49.80),
('1000000005','计算机','机械工业出版社','王小红','计算机文化',20.00),
('1000000006','计算机','机械工业出版社','刘宏亮','数据结构',21.60),
('2000000007','数学','机械工业出版社','吴非','高等数学',18.00),
('2000000008','数学','机械工业出版社','丁玉应','概率统计',22.30),
('2000000009','数学','电子工业出版社','赵名','线性代数',15.00),
('3000000010','物理','电子工业出版社','张共可','力学',19.80)
insert into reader
values ('10000001','李小明','计算机系','男','13826388323'),
('10000002','王红','计算机系','男','13826388378'),
('10000003','李和平','计算机系','女','13826385523'),
('10000004','刘宏亮','计算机系','男','13826387623'),
('10000005','王小红','数学系','女','13826381223'),
('10000006','刘宏亮','计算机系','男','13826356323'),
('10000007','吴小','数学系','男','13826366323'),
('10000008','丁玉应','数学系','男','13826898323'),
('10000009','赵名','数学系','女','13826348323'),
('10000010','张共可','计算机系','男','13826384523')
insert into borrow
values ('1000000001','10000001','1998-11-25'),
('1000000002','10000002','1998-12-20'),
('1000000003','10000003','1999-6-5'),
('1000000004','10000004','2006-11-25'),
('1000000005','10000005','2006-11-25'),
('1000000006','10000006','2006-11-25'),
('2000000007','10000007','2006-11-25'),
('2000000008','10000008','2006-11-25'),
('2000000009','10000009','2006-11-25'),
('3000000010','10000010','2006-11-25')
alter table reader add Email varchar(30)
create rule emails
as
@value like '%@%'
sp_bindrule emails,'reader.Email'
alter table reader drop column Email
insert into book
values ('1000000001','文史','北京大学','张远','心的出路',36)
/*经验证，不能插入记录,因为实体完整性*/
update book
set bookid='1000000011'
where bookid='1000000001'
/*经观察，因references约束冲突,无变化*/
delete from book
where bookid='1000000002'
/*经观察，因references约束冲突,无变化*/
/*
insert into 表1
select *
from 表2
*/
create database student_course
use student_course
go
create table student
(
studentid char(5) primary key,
student_name char(8) not null,
age smallint default 20,
gender char(2) check(gender in ('男','女')),
department char(20)
)
create table course
(
courseid char(5) primary key,
course_name char(20) not null,
pre_id char(5)
)
create table sc
(
studentid char(5),
courseid char(5),
grade smallint check(grade <= 100 and grade >= 0 or grade=null ),
primary key(studentid,courseid),
foreign key (studentid) references student(studentid),
foreign key (courseid) references course(courseid),
)
insert into student
values ('S1','李明',21,'男','计算机'),
('S2','张小红',21,'男','计算机'),
('S3','李和明',22,'女','计算机'),
('S4','张三',21,'男','计算机'),
('S5','刘宏',23,'男','计算机'),
('S6','王红应',20,'女','计算机'),
('S7','吴非',19,'男','数学'),
('S8','丁玉',21,'男','数学'),
('S9','赵名',21,'女','数学'),
('S12','张共可',22,'男','物理')
insert into course
values ('C1','计算机引论',null),
('C2','数据结构','C3'),
('C3','C语言编程','C1'),
('C4','软件工程','C6'),
('C5','数据库','C2'),
('C6','计算机文化',null),
('C7','高等数学',null),
('C8','概率统计','C9'),
('C9','线性代数','C7'),
('C10','力学',null)
insert into sc
values ('S1','C1',60),
('S2','C1',93),
('S3','C1',null),
('S4','C1',89),
('S1','C2',79),
('S2','C2',null),
('S3','C2',80),
('S4','C3',90),
('S1','C3',92),
('S2','C3',81),
('S1','C7',85),
('S4','C7',75)规则
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(10) default '党员',
家庭所在城市 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'查
/*******************************
创建数据库，数据表
*******************************/
/*创建数据库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
/*******************************
创建数据库，数据表
*******************************/
/*创建数据库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
二、数学建模相关
线性规划
层次分析
三、数据结构相关
二叉树
HaffumanTree
2030
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
