数据库实验 答案笔记(MySQL实现版本)

/* @Author:Tony , a student in SCUT . 为了记录软件学院的数据库实验的笔记
全部sql语句都上机过, 人工检查过没有错误, 不排除人工理解错误或者人为疏忽.
本指南只在win7+mysql workbench5.2.37上运行正确. 其他数据库或者linux等很有可能不行
推荐一个web技术教程,里面的SQL语法讲解不错,有对应多种数据库示例
https://www.wendangku.net/doc/7310502027.html,/sql/index.asp
*/

/*建表..以下sql命令大小写基本不敏感.也不区分单双引号*/
create table s(NO char(2) not null primary key unique, NAME char(10) unique, SEX char(2), AGE int, CLASS char(5));
/*另外指定 完整性约束的方法
alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;
*/

/*数字可以直接转换成char(),但是字符不行(汉字和字母都不行)*/
insert into s values (25, "李明", "男", 21, "95031");
insert into s values (10, "王丽", "女", 20, 95101);
insert into s values (30, "郑和", null, null, 95031);

/*增删索引.alter语句才能操作primary key索引
索引不能更改,只能删了重建
create index index_name on table_name (column_list) ;
create unique index index_name on table_name (column_list) ;
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
*/
create unique index sno on s(NO)
create index sage on s(age)

alter table s add comedate datetime
drop index sage on s
alter table s modify age smallint

/*删除唯一性约束.因为系统默认为unique创建索引,删除索引即可*/
alter table s drop index name

/*最后一步,真是伟大而又有意义的一步啊*/
drop table s





/*开始下一步,建表..不过MySQL的数据库都是小写名字*/
create database db_SPJ;

/*记得双击选中db_spj数据库,免得把表见到test数据库中了*/
/*建表,约束性,请看书本第四章4.2*/
/*特别说明:MySQL对check分析,但是实际执行会忽略check子句.可以考虑用trigger代替
其中foreign key约束也是比较新的数据库版本才支持的,注意版本问题.
*/
create table S(SNO char(2) not null unique primary key, SNAME char(3), STATUS smallint, CITY char(4) default "北京");

create table P(PNO char(2) not null unique primary key, PNAME char(3), COLOR char, WEIGHT smallint, check(COLOR in("红",'蓝', "绿")));

create table J(JNO char(2) not null unique primary key, JNAME char(4), CITY char(4));

create table SPJ(SNO char(2), PNO char(2), JNO char(2), QTY int,
primary key(SNO, PNO, JNO),
foreign key(SNO) references S(SNO) on delete cascade on update cascade,
foreign key(PNO) references P(PNO) on delete cascade on update cascade,
foreign key(JNO) references J(JNO) on delete cascade on update cascade) ;

/*添加spj表的qty为正数的检查, 使用trigger替代check约束

*/
delimiter //
create trigger qty_check before insert on spj
for each row
begin
if new.qty <= 0 then
insert into a_table_never_exist values(aa);
end if;
end;//
delimiter ;

/*为了测试方便,以下先插入数据
一次插入多条数据 insert into table_name values(,,),(,,);*/

insert into s values("S1", "精益", 20, "天津");
insert into s values("S2", "盛锡", 10, "北京");
insert into s values("S3", "东方红", 30, "北京");
insert into s values("S4", "丰泰", 20, "天津");
insert into s values("S5", "为民", 30, "上海");

insert into p values("P1", "螺母", "红", 12);
insert into p values("P2", "螺栓", "绿", 17);
insert into p values("P3", "螺丝刀", "蓝", 14);
insert into p values("P4", "螺丝刀", "红", 14);
insert into p values("P5", "凸轮", "蓝", 40);
insert into p values("P6", "齿轮", "红", 30);

insert into j values("J1", "三建", "北京");
insert into j values("J2", "一汽", "长春");
insert into j values("J3", "弹簧厂", "天津");
insert into j values("J4", "造船厂", "天津");
insert into j values("J5", "机车厂", "唐山");
insert into j values("J6", "无线电厂", "常州");
insert into j values("J7", "半导体厂", "南京");

insert into spj values("S1", "P1", "J1", 200);
insert into spj values("S1", "P1", "J3", 100);
insert into spj values("S1", "P1", "J4", 700);
insert into spj values("S1", "P2", "J2", 100);
insert into spj values("S2", "P3", "J1", 400);
insert into spj values("S2", "P3", "J2", 200);
insert into spj values("S2", "P3", "J4", 500);
insert into spj values("S2", "P3", "J5", 400);
insert into spj values("S2", "P5", "J1", 400);
insert into spj values("S2", "P5", "J2", 100);
insert into spj values("S3", "P1", "J1", 200);
insert into spj values("S3", "P3", "J1", 200);
insert into spj values("S4", "P5", "J1", 100);
insert into spj values("S4", "P6", "J1", 100);
insert into spj values("S4", "P6", "J4", 200);
insert into spj values("S5", "P2", "J4", 100);
insert into spj values("S5", "P3", "J1", 200);
insert into spj values("S5", "P6", "J2", 200);
insert into spj values("S5", "P6", "J4", 500);

/*测试主键.唯一值
Error Code: 1062. Duplicate entry 'S1' for key 'PRIMARY'
*/
insert into s values("S1", "你懂得", 21,"日本");

/*测试主键.空值[注意:空串/空格/null的含义通常不相同]
insert into s values(null, "你好", 22, "美女")
Error Code: 1048. Column 'SNO' cannot be null
*/
insert into s values(null, "你好", 22, "美女");

/*外键测试:参照完整性..
insert into spj values("S6", "P6", "J4", 500)
Error Code: 1452. Cannot add or update a child row:
a foreign key constraint fails (`db_spj`.`spj`, CONSTRAINT `spj_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `s` (`SNO`))
*/
insert into spj values("S6", "P6", "J4", 500);

/*外键测试:级联约束处理*/
update S set SNO = "S6" where SNO = "S3";/*spj中的SNO也对应更改了*/
delete from S where SNO =

"S6";/*spj中S6(原S3)的记录不见了*/
/*恢复数据*/
insert into s values("S3", "东方红", 30, "北京");
insert into spj values("S3", "P1", "J1", 200);
insert into spj values("S3", "P3", "J1", 200);

/*默认值测试. MySQL 4.0.3以上版本才支持insert语句使用default关键字*/
insert into s values("S6", "结衣", 24, default);
/*恢复原样数据*/
delete from s where SNO = "s6";



/*开始下一步, SQL中DML表操作.一概不考虑查询优化,mysql可能有优化;优化的课程还没上完.*/
/*1 找出所有供应商的姓名和所在城市。*/
select sname, city
from s


/*2 找出所有零件的名称、颜色和重量。*/
select pname, color, weight
from p


/*3 找出使用了供应商S1所供应的零件的工程号码。*/
select JNO
from spj
where SNO = "S1"


/*4 找出工程J2使用的各种零件的名称和数量。*/
select pname, qty
from P, spj
where spj.JNO = "J2" and p.pno = spj.pno


/*5 找出上海供应商供应的所有零件的零件号码。未去重*/
select PNO /*select distinct PNO*/
from spj, s
where spj.sno = s.sno and s.city = "上海"


/*6 找出使用了上海供应商供应的零件的工程名称。*/
select distinct jname
from j, spj, s
where j.jno = spj.jno and spj.sno = s.sno and s.city = "上海"


/*7 找出供应工程J1零件的供应商号SNO。*/
select distinct sno
from spj
where jno = "J1"


/*8 找出供应工程J1零件P1的供应商号SNO。*/
select distinct sno
from spj
where jno = "J1" and pno = "p1"


/*9 找出供应工程J1红色零件的供应商号SNO。*/
select distinct sno, color
from p, spj
where spj.pno = p.pno and spj.jno = "j1" and p.color = '红'


/*10 找出没有使用天津供应商生产的红色零件的工程号JNO。*/
select distinct jno
from s, p, spj
where s.sno = spj.sno and p.pno = spj.pno and color != '红' and city != "天津"


/*11 求所有有关project 的信息。*/
select *
from j


/*12 求在北京的所有project 的信息。*/
select *
from j
where city = "北京"


/*13 求为project（工程）J1 提供part（零件）的supplier（供应商）的号码。*/
select distinct sno
from spj
where jno = "J1"


/*14 求数量在300 到750 之间的发货。*/
select pno, sum(qty)
from spj
group by jno
having sum(qty)>=300 and sum(qty)<=750


/*15 求所有的零件颜色 / 城市对。注意：这里及以后所说的“所有”特指在数据库中。*/
select distinct color, city
from p, spj, s
where p.pno = spj.pno and spj.sno = s.sno


/*16 求所有的supplier-number / part-number / project-number 对。其中所指的供应商和工程在同一个城市。*/
select s.sno, pno, j.jno
from s, spj, j
where s.sno = spj.sno and spj.jno = j.jno and s.city = j.city


/*17 求所有的supplier-number / part-number / project-number 对。其中所指的供应商和工程不在同一个城市。*/
select s.sno,

pno, j.jno
from s, spj, j
where s.sno = spj.sno and spj.jno = j.jno and s.city != j.city


/*18 求由北京供应商提供的零件的信息。*/
select distinct p.*
from s, spj, p
where s.sno = spj.sno and spj.pno = p.pno and s.city = "北京"


/*19 求由北京供应商为北京工程供应的零件号。*/
select distinct pno
from s, spj, j
where s.city = "北京" and j.city = "北京" and s.sno = spj.sno and spj.jno = j.jno


/*20 求满足下面要求的城市对，要求在第一个城市的供应商为第二个城市的工程供应零件。*/
/* 可用with子句进行复杂查询,with子句是SQL99才加入的,Oracle, DB2, MS-SQL才支持.用潜逃子查询
in MySQL, limit语法: limit m,n 从第m行开始往后选n行记录.mn从0开始计数.limit 2,从0行开始选2行*/

select p.*
from s, j, spj, p
where s.sno = spj.sno and spj.jno = j.jno and spj.pno = p.pno
and s.city = (select city
from s
limit 0,1)
and j.city = (select city
from j
limit 1,1)


/*21 求供应商为工程供应的零件的号码，要求供应商和工程在同一城市。*/
select distinct pno
from s, spj, j
where s.sno = spj.sno and spj.jno = j.jno and s.city = j.city


/*22 求至少被一个不在同一城市的供应商供应零件的工程号。*/
select distinct j.jno
from s, spj, j
where s.sno = spj.sno and spj.jno = j.jno and s.city != j.city


/*23 求由同一个供应商供应的零件号的对。*/
/*嗯, 这句中文我不太懂. 按照我的猜测写一个*/
select distinct sno, pno
from spj


/*24 求所有由供应商S1 供应的工程号。*/
select distinct pno
from spj
where sno = "S1"


/*25 求供应商S1 供应的零件P1 的总量。*/
select sum(qty)
from spj
where sno = "S1"


/*26 对每个供应给工程的零件，求零件号、工程号和相应的总量。*/
select pno, jno, sum(qty)
from spj
group by pno, jno


/*27 求为单个工程供应的零件数量超过350 的零件号。*/
/*MySQL语法中, 是不支持书上那种from子句改名方法的.要逐列改名,最后改表名*/
select distinct tp
from (select pno as tp, jno as tj, sum(qty) as ts
from spj
group by pno, jno)
as dd
where ts > 350


/*28 求由S1 供应的工程名称。*/
select distinct jname
from spj, j
where spj.jno = j.jno and sno = "S1"


/*29 求由S1 供应的零件颜色。*/
select distinct color
from p, spj
where spj.pno = p.pno and sno = "S1"


/*30 求供应给北京工程的零件号。*/
select distinct pno
from spj, j
where spj.jno = j.jno and city = "北京"


/*31 求使用了S1 供应的零件的工程号。*/
select distinct jno
from spj
where sno = "S1"


/*32 求status 比S1 低的供应商号码。*/
select s.sno
from s as T, s
where s.status < T.status and T.sno = "S1"


/*33 求所在城市按字母排序为第一的工程号。*/

select jno
from j
order by city
limit 1


/*34 求被供应零件P1 的平均数量大于供应给工程J1 的任意零件的最大数量的工程号。*/
/*各个工程的P1零件的平均数量
select pno, jno, avg(qty) as avg_qty
from spj
group by pno, jno
having pno = "p1" */

/*供应给J1的任意零件的和
select pno, jno, sum(qty)
from spj
group by pno, jno
having jno = "j1" */

/*结果为空*/
select pno, jno, avg(qty) as avg_qty
from spj
group by pno, jno
having pno = "p1" and avg_qty >

(select max(temp_max)
from (select sum(qty) as temp_max
from spj
group by pno, jno
having jno = "j1" )
as dd
)


/*35 求满足下面要求的供应商号码，该供应商供应给某个工程零件P1 的数量大于这个工程被供应的零件P1 的平均数量。
结果为空*/

select distinct sno
from spj, (
select pno, jno, avg(qty) as avg_P1
from spj
group by pno, jno
having pno = "P1") as dd
where spj.jno = dd.jno and spj.pno = "P1" and spj.qty > dd.avg_P1


/*36 求没有被北京供应商供应过红色零件的工程号码。*/
/*mysql没有实现差集,交集的运算, 有并集union.
差集替代: 左连接,右表为空..
判断空集:也可以用dd.jno is null.千万不要用dd.jno = null来判断.
*/

select j.jno
from (j left outer join
(select jno
from s, spj, p
where s.sno = spj.sno and spj.pno = p.pno and s.city = "北京" and p.color = "红") dd
on j.jno = dd.jno)
where ISNULL(dd.jno)


/*全称量词的查询, 有时可以用聚集函数巧解.
正统方法可以参看课本2.3.3 除运算..或者google"sql查询 全称量词"..或者ppt*/
/*37 求所用零件全被S1 供应的工程号码。*/
select jno
from (select sno, jno
from spj
group by jno
having count(sno) = 1 and sno = "S1"
) as dd


/*38 求所有北京工程都使用的零件号码。*/
/*思路:(∨x)P≡!(Ex(!P)).不存在北京工程没有使用的零件.*/
select pno
from p
where not exists(
select *
from j
where j.city = "北京" and not exists(
select *
from spj
where p.pno = spj.pno and spj.jno = j.jno
)
)


/*39 求对所有工程都提供了同一零件的供应商号码。
工程x, 零件y, 供应商z, P(z,y,x):z提供y给x.
推导:VxEyP(z,y,x)===Ey !Ex !P(z,y,x)
结果为空*/
select sno
from s
where exists(
select *
from p
where not exists(
select *
from j
where not exists(
select *
from spj
where s.sno = spj.sno and p.pno = spj.pno and j.jno = spj.jno
)
)
)


/*40 求使用了S1 提供的所有零件的工程号码。
虽然逻辑表达式做出来是这样, 但是我人工检

查答案觉得不对*/
select jno
from j
where not exists(
select *
from spj as t1
where t1.sno = "S1" and not exists(
select *
from spj as t2
where t1.pno = t2.pno and t2.jno = j.jno
)
);


/*41 求至少有一个供应商、零件或工程所在的城市。*/
(select s.city from s where exists(
select *
from spj
where s.sno = spj.sno
)
)union
(select j.city from j where exists(
select *
from spj
where j.jno = spj.jno
)
)


/*42 求被北京供应商供应或被北京工程使用的零件号码。*/
select distinct pno
from spj, s, j
where spj.sno = s.sno and spj.jno = j.jno
and (s.city = "北京" or j.city = "北京")


/*43 求所有supplier-number / part-number 对，其中指定的供应商不供应指定的零件。*/
select sno, pno
from s, p
where not exists(
select *
from spj
where spj.sno = s.sno and p.pno = spj.pno
)


/*44 向p表追加如下记录（P0,PN0,蓝）。*/
insert into p values("P0", "PNO", "蓝", null);


/*45 把零件重量在15到20之间的零件信息追加到新的表p1中。*/
create table p1
as( select *
from p
where weight <=20 and weight >= 15)


/*46 向s表追加记录（s1, n2, ’上海’）能成功吗?为什么？*/
不可以, sno是主键, 非空且唯一......原表已经有sno为S1的记录了.


/*47 把s、p、j三个表中的s#,p#,j#列进行交叉联接，把结果追加到spj1表中（如果只考虑下面表格中的原始数据，应该在spj1表中追加多少条记录？你是如何计算记录条数的？）。交叉连接跟from子句中的,逗号连接连接一样, 都是笛卡儿连接*/
count(sno)*count(pno)*count(jno)-count(sno,pno,jno)


/*48 向spj表追加（s6,p1,j6,1000）本操作能正确执行吗？为什么？如果追加(s4,p1,j6,-10) 行吗？如果现在想强制追加这两条记录该怎么办？*/
insert into spj values("S6", "P1", "J6", 1000);
外键约束, 不存在外键引用S6
insert into spj values("S4", "P1", "J6", -10);
check约束(mysql, 本例使用qty_check替代)发货量不能是负数
(知道的同学告诉一下)好像没有强制追加记录的方法, 唯有先解除或者更改外键约束和check约束(trigger)


/*49 把s1供应商供应的零件为p1的所有项目对应的数量qty改为500。*/
update spj
set qty = 500
where sno = "S1" and pno = "P1"


/*50 把qty值大于等于1000的所有供应商城市更改为‘北京’ 。*/
SET SQL_SAFE_UPDATES=0;
update s
set s.city = "北京"
where sno in (select sno
from spj
group by sno
having sum(qty) >= 1000);
SET SQL_SAFE_UPDATES=1;


/*51 把j1更改成j7，本操作能正确执行吗？为什么？如果改成j0呢

？spj表中记录有何变化？为什么？*/
j7不能, jno主键, 已经有了j7主键的项
j0可以, spj中原本是j1的地方全部变成j0了....因为在外键引用的时候, 设置了级联更新


/*52 把零件重量低于15的增加3，高于15的增加2。*/
SET SQL_SAFE_UPDATES=0;
update p
set weight = case
when weight < 15 then weight + 3
when weight > 15 then weight + 2
end
SET SQL_SAFE_UPDATES=1;


/*53 删除为j7工程供应零件的所有供应商信息（如果建立外键时没有带级联删除选项，本操作能正确执行吗？为什么？）*/
可以删除, 因为作为外键的jno, 没有表引用了j7这一项,如果是j1就不行了.


/*54 删除p1表中所有记录。*/
delete from p1


/*54 删除供应商和工程在同一个城市的供应商信息。*/
delete from s
where sno in(
select sno
from spj, j
where s.sno = spj.sno and spj.jno = j.jno
and s.city = j.city)