一.数据定义
1.创建表
-- 1. 创建数据库，命名为学号后三位
CREATE DATABASE 107;
-- 2. 在个人学号数据库中创建如下的三张表，并输入记录内容：
CREATE TABLE JS (
Tno VARCHAR ( 7 ),
Tname VARCHAR ( 10 ),
Tsex VARCHAR ( 2 ),
Birthday date,
Dept VARCHAR ( 20 ),
Sid VARCHAR ( 18 )
);
CREATE TABLE course ( Cno VARCHAR ( 10 ), Cname VARCHAR ( 20 ), Credit SMALLINT, property VARCHAR ( 10 ), Hours INT );
CREATE TABLE Sk ( Tno VARCHAR ( 7 ), Cno VARCHAR ( 10 ), Hours INT );
-- 1) 定义表students，其中字段有：SNO CHAR(10) 、 SNAME CHAR(8) 、 AGE NUMERIC(3,0)、 SEX CHAR(2)、 BPLACE CHAR(20)、Polity CHAR(20)。要求SNO和SNAME不为空，SNO为主键。
CREATE TABLE student (
sno CHAR ( 10 ) NOT NULL PRIMARY KEY,
sname CHAR ( 8 ),
age NUMERIC ( 3, 0 ),
sex CHAR ( 2 ),
bplace CHAR ( 20 ),
polity CHAR ( 20 )
);
-- 3) 根据students表，建立一个只包含学号、姓名、年龄的女学生表，表名为GRIL。
CREATE TABLE GIRL ( sno CHAR ( 10 ) NOT NULL PRIMARY KEY, sname CHAR ( 8 ), age NUMERIC ( 3, 0 ) );
-- 4) 建立一成绩表，表名为score，其中字段有：SNO CHAR（10），CNO CHAR（10），scoreNUMERIC(6,0)，并输入部分记录，内容自定
CREATE TABLE score ( SNO CHAR ( 10 ), CNO CHAR ( 10 ), score NUMERIC ( 6, 0 ) );
CREATE TABLE JS1 (
Tno VARCHAR ( 7 ),
Tname VARCHAR ( 10 ),
Tsex VARCHAR ( 2 ),
Birthday date,
Dept VARCHAR ( 20 ),
Sid VARCHAR ( 18 )
);
CREATE TABLE class ( class VARCHAR ( 10 ), Csno VARCHAR ( 10 ), sname VARCHAR ( 8 ), sno VARCHAR ( 10 ) );
-- 1) 在数据库中的新建一个数据表，名为js1，结构与js表相同。为js1表创建一个唯一聚集索引，索引字段为Sid，索引名为I_js_sid。
CREATE UNIQUE INDEX I_js_sid ON js1 ( Sid );
-- 2) 为数据库中的course数据表，创建一个复合索引，索引名为I_cource_xf，使用Cno和Credit字段
CREATE INDEX I_cource_xf ON course ( Cno, Credit );
2.插入数据
JS表
INSERT INTO Js VALUES('Too1','刘威','女','1971-3-20','电信','551021197103203121');
INSERT INTO Js VALUES('Too2','张琪劲','男','1963-7-21','数理','32010119630713318X');
INSERT INTO Js VALUES('Too3','李子文','女','1973-9-15','电信','551021197103203121');
INSERT INTO Js VALUES('Too4','江海防','女','1960-2-18','社科','560102196002185623');
INSERT INTO Js VALUES('Too5','李铁','男','1977-10-11','数理','230103197710118632');
INSERT INTO Js VALUES('Too6','吴天一','男','1962-4-23','电信','320104196204237516');
INSERT INTO Js VALUES('Too7','赵志华','男','1968-8-27','社科','321102196808277214');
INSERT INTO Js VALUES('Too8','钱进','男','1980-7-10','电信','570102198007103452');
INSERT INTO Js VALUES('Too9','孙星南','女','1981-3-2','外语','110102198103024125');
Course 表
INSERT into course values('01010101','大学英语1',4,'考试',64);
INSERT into course values('01010102','普通物理1',4,'考试',64);
INSERT into course values('01010103','高等数学1',6,'考试',96);
INSERT into course values('01010104','形势政策',2,'考查',32);
INSERT into course values('01010105','计算机基础',4,'考查',64);
SK 表
INSERT INTO SK VALUES('Too1','01010105',64);
INSERT INTO SK VALUES('Too2','01010102',64);
INSERT INTO SK VALUES('Too9','01010101',64);
INSERT INTO SK VALUES('Too4','01010104',32);
INSERT INTO SK VALUES('Too5','01010103',96);
INSERT INTO SK VALUES('Too6','01010105',64);
INSERT INTO SK VALUES('Too3','01010101',64);
3.修改表结构
-- 3. 修改表结构
-- 1) 在授课表中添加一个授课类别，列名为Type，类型为char（4）
alter table SK add column Type char(4);
-- 2) 将授课表中的Hours的类型改为Smallint
alter table SK modify column Hours SMALLINT;
-- 3) 删除课程表的Hours列
alter table course drop column hours;
-- 2) 在教师表JS中增加住址列，字段名为ADDR，类型为 CHAR，长度50。
alter table Js add column ADDR char(50);
二.完整性约束
-- 对教师表JS、课程表course和授课表SK进行完整性约束设置
alter table js add PRIMARY KEY(Tno);
alter table js modify Tname varchar(10) NOT NULL;
alter table js add CONSTRAINT c_Tsex CHECK (Tsex in ('男','女'));
alter table js add UNIQUE(Sid);
alter table course add PRIMARY KEY(Cno);
alter table course modify Cname varchar(20) NOT NULL;
alter table course add CONSTRAINT c_Credit CHECK (Credir>0);
alter table course modify property varchar(10) DEFAULT '必修';
alter table SK add primary key(Tno,Cno);
alter table SK add foreign key(Tno) references js(Tno);
alter table SK add foreign key(Cno) references course(Cno);
alter table sk add CONSTRAINT c_hours CHECK (hours>0);
三.数据操纵
-- 查询所有男学生的姓名、出生日期
select Sname,age from student;
-- 查询男女教师的人数。
select Tsex,COUNT(*) from js GROUP BY Tsex;
-- 找出年龄在20～23岁之间的学生的学号、姓名和年龄，并按年龄升序排序。
select sno,sname,age from student where 20<age<23 order by age asc;
-- 找出年龄超过平均年龄的学生姓名。
select sname from student where age>(select avg(age) from student);
-- 查询成绩不及格的学生信息，包括姓名、学号、课程名和成绩。
select student.sname,student.sno,cname,score from student,course,score where score<60 and score.sno=student.sno and score.cno=course.cno;
-- 查询所有讲授“01010105”课程的教师信息。
select js.* from js,sk where sk.cno=01010105 and js.tno=sk.tno;
-- 查询1971年以前（含1971年）出生的所有教师的任课信息，包括教师姓名、出生日期、所授课程名、学时数。
select Tname,birthday,cname,hours from js,sk,course where birthday<'1971-1-1' and js.tno=sk.tno and course.cno=sk.cno;
-- 查询所有未授课的教师信息
select * from js where js.tno not in (select sk.tno from sk);
-- 把所有学生的年龄增加一岁
update student set age=age+1;
-- 将所有选修某一指定课程的学生成绩增加5分。
update score set score=score+5 where cno=01010101;
-- 将某个学生的所有成绩置0。
update score set score=0 where sno=102;
-- 从教师表JS中删除年龄已到60岁的退休教师的数据。
delete from js where DATEDIFF('2019-11-19',Birthday)>21900;
-- 将学生表student中的某个学生删除，并删除其他表中与该学生相关的信息。
delete from student where sno=104;
delete from score where sno=104;
四.数据库编程
-- 1． 在score表中求某班学生某门课程的最高分和最低分的学生信息，包括学号、姓名、课程名、成绩四个字段。
SELECT
score.sno,
student.sname,
course.cname,
score.score
FROM
score,
student,
course
WHERE
score.cno = '01010101'
AND (
score.score = ( SELECT min( score ) FROM score WHERE cno = '01010101' )
OR score.score = ( SELECT max( score ) FROM score WHERE cno = '01010101' )
)
AND course.cno = score.cno
AND score.sno = student.sno;
-- 2． 查询某班的学生信息，要求列出的字段为：班级、本班内的学号、姓名、性别、出生日期、政治面貌
SELECT
class,
csno,
sex,
DATE_SUB( '2019-11-20', INTERVAL CAST( age AS CHAR ( 10 ) ) YEAR ) birthday,
polity
FROM
student,
class
WHERE
class.class = '1'
AND class.sno = student.sno;
-- 3． 在student表中先插入三条新记录，其中的Pollity字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示为“群众”
SELECT
sno,
sname,
age,
sex,
bplace,
IFNULL( polity, "群众" ) AS polity
FROM
student;
-- 4． 根据score表中考试成绩，查询某班学生某门课程的平均成绩，并根据平均成绩输出相应的提示信息
SELECT
avg( score ) '平均成绩'
FROM
score
WHERE
sno LIKE '10_'
AND cno = '01010101';
-- 5． 根据score表中考试成绩，查询某班学生的考试情况，并使用CASE将课程号替换为课程名称
SELECT
sno '学号',
CASE
cno
WHEN '01010101' THEN
'大学英语1'
WHEN '01010102' THEN
'普通物理1'
WHEN '01010103' THEN
'高等数学1'
WHEN '01010104' THEN
'形势政策'
WHEN '01010105' THEN
'计算机基础'
END '课程名',
score '成绩'
FROM
score
WHERE
sno LIKE '10_'
ORDER BY
SNO;
-- 6． 根据t_score表中考试成绩，查询某班学生的考试情况，并根据考试分数输出考试等级。
SELECT
sno '学号',
cno '课程号',
score '成绩',
CASE
WHEN score < 60 THEN
'不及格'
WHEN 60 < score
AND score < 70 THEN
'及格'
WHEN 70 < score
AND score < 80 THEN
'中'
WHEN 80 < score
AND score < 90 THEN
'良'
WHEN 90 < score
AND score < 100 THEN
'优'
END '等级'
FROM
score
WHERE
sno LIKE '10_'
ORDER BY
SNO;
-- 1．创建一个存储过程stuscoreinfo，完成的功能是在表student、表course和表score中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。
DELIMITER $$
CREATE PROCEDURE stuscoreinfo ( ) BEGIN
SELECT
class.class,
student.sno,
student.sname,
sex,
course.cname,
score
FROM
student,
class,
score,
course
WHERE
student.sno = class.sno
AND score.sno = student.sno
AND score.cno = course.cno;
END $$DELIMITER $$
-- 2． 创建一个带有参数的存储过程stu_info，该存储过程根据传入的学生编号，在student中查询此学生的信息。
CREATE PROCEDURE stu_info ( in_sno VARCHAR ( 10 ) ) BEGIN
SELECT
*
FROM
student
WHERE
sno = in_sno;
END $$DELIMITER $$
-- 3． 创建一个带有参数的存储过程stu_age，该存储过程根据传入的学生编号，在student中计算此学生的年龄，并根据程序执行结果返回不同的值，程序执行成功，返回整数0，如果执行错误，则返回错误号。
CREATE PROCEDURE stu_age ( IN in_sno VARCHAR ( 10 ), OUT out_age NUMERIC ( 3, 0 ), OUT result INT ) BEGIN
SET result = 0;
SET out_age = ( SELECT age FROM student WHERE sno = in_sno );
END $$
-- 4． 执行stuscoreinfo存储过程（无参）
CALL stuscoreinfo ( );
-- 5． 执行存储过程stu_info（该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值）。
CALL stu_info ( '101' );
-- 6． 执行存储过程stu_age（该存储过程有一个输入参数“学号”和一个输出参数@age。存储过程执行完后应有一个返回的状态值，这个值可以从返回的错误号得到）。
CALL stu_age ( '101', @out_age, @result );
-- 8． 删除存储过程stuscoreinfo
DROP PROCEDURE stuscoreinfo;
-- 1． 使用自定义函数fun_sumscores。求score表中各班级的各门课程的平均分。主程序调用该函数，显示各班级、各课程的平均分。
DELIMITER $
CREATE FUNCTION fun_sumscores ( ) RETURNS INT BEGIN
DECLARE
c INT;
SELECT
avg( score )
FROM
score INTO c;
RETURN c;
END $
SELECT fun_sumscores ( );
-- 2． 编写一个用户自定义函数fun_sumscores。要求根据输入的班级号和课程号，求得此班此门课程的总分。主程序调用该函数，查询指定班级的某课程的总分。
DELIMITER $
CREATE FUNCTION fun_sumscores2 ( in_class VARCHAR ( 10 ), in_cno VARCHAR ( 10 ) ) RETURNS INT BEGIN
DECLARE
c INT;
SELECT
sum( score )
FROM
score
WHERE
cno = in_cno
AND sno LIKE CONCAT( in_class, '0_' ) INTO c;
RETURN c;
END $
SELECT
fun_sumscores2 ( '1', '01010101' );
-- 3． 编写一自定义函数，用于查询给定姓名的学生，如果没找到，返回0，否则返回满足条件的学生人数。主程序调用该函数，查询“李浩”的学生，并根据函数的返回值进行输出。
DELIMITER $
CREATE FUNCTION fun_sumscores3 ( in_sname VARCHAR ( 10 ) ) RETURNS INT BEGIN
DECLARE
c INT;
SELECT
count( * )
FROM
student
WHERE
sname = in_sname INTO c;
RETURN c;
END $
SELECT
fun_sumscores3 ( '李浩' );
五.图书馆部分
创建
create table JS(
Jsno varchar(6) key,
Jname varchar(10),
Jdw varchar(20)
);
create table TS(
Tsno varchar(15) key,
Tname varchar(20),
Tnum SMALLINT,
Tpos varchar(30),
Cno varchar(4)
);
create table CBS(
Cno varchar(4) key,
Cname varchar(20),
Ctel varchar(12),
Cyb varchar(6),
Caddr varchar(40)
);
create table JY(
Jsno varchar(6),
Tsno varchar(15),
Jydate DATE,
Hdate DATE,
primary key(Jsno,Tsno)
);
修改数据库
alter table JS modify column Jsno char(6);
创建视图
create view Ts_view as select TS.Tsno,TS.Tname,CBS.Cname,TS.Tnum from TS,CBS where TS.Cno=CBS.Cno;
create view Jy_view as select JY.jsno,Js.Jname,Ts.Tname,CBS.cname,JYdate,Hdate from JS,TS,CBS,JY where JY.jsno=Js.jsno and JY.Tsno=TS.Tsno and TS.Cno=CBS.cno;
创建视图
create index I_JS_Jsno on Js(Jsno);
create index I_TS_Tsno on Ts(Tsno);
create index I_CBS_Cno on CBS(Cno);
create index I_JY_Jsno on JY(Jsno);
插入数据
insert into js values('111111','q','q');
insert into js values('222222','w','w');
insert into js values('333333','e','e');
insert into TS values('1','1',1,'1','1');
insert into CBS values('1','1','1','1','1');
insert into JY values('1','1','2019-1-1','2019-1-10');
完整性约束
alter table js add primary key(Jsno);
alter table js modify jname VARCHAR(10) not null;
alter table ts add primary key(Tsno,cno);
alter table ts modify Tname VARCHAR(20) not null;
alter table TS add CONSTRAINT c_Tnum CHECK (Tnum>0);
alter table TS add foreign key(Cno) references CBS(Cno);
alter table CBS add primary key(Cno);
alter table CBS modify Cname VARCHAR(20) not null;
alter table CBS add CONSTRAINT c_Cyb CHECK (Cyb in ('0','1','2','3','4','5','6','7','8','9'));
alter table JY add primary key(Jsno,Tsno);
alter table jy add foreign key(Jsno) references Js(Jsno);
alter table jy add foreign key(Tsno) references Ts(Tsno);
alter table TS add CONSTRAINT c_date CHECK (Jydate>Hdate);
浙公网安备 33010602011771号