数据库题目整理及详解（一）

Posted on | In
Symbols count in article: 16k | Reading time ≈ 14 mins.

前言

-----冯延巳： 鹊踏枝.清明

六曲阑干偎碧树，杨柳风轻，展尽黄金缕。谁把钿筝移玉柱？穿帘海燕惊飞去。
满眼游丝兼落絮，红杏开时，一霎清明雨。浓睡觉来慵不语，惊残好梦无寻处？

小假前夕，大Boss让当差，于是便可以名正言顺的重温一下数据库；时间还真是一把XXXX，夺走了记忆，失掉了“手熟尔”。

说明

1: 内容：sql语句相关：建表、查询、权限等；
2: 环境：Ubuntu 14.04 LTS下的Mysql5.5；
3: 环境搭建见：ubuntu下mysql数据库的安装配置
4: 规范：解析中的SQL关键字都使用黑粗体标记了，并且有大小写的区分；Mysql中是对sql语句大小写敏感的，为了节省时间，在亲测中都使用小写，详见截图;
5: 每书写一条sql语句，最后都需要加英文分号（“;”）;
6: 每一行sql语句基本都亲测了一下，望诸君戮力，多有指正。

题目解析

1: 设供应商-工程-零件数据库包含如下关系:

Suppliers(Sno, Sname, Status, Scity)
Parts(Pno, Pname, Color, Weight)
Projects(Jno, Jname, Jcity)
SPJ(Sno, Pno, Jno, Quantity)

其中,各关系的主码用下横线标示。Sno, Sname, Status, Scity 分别表示供应商的编号、名称、状态和所在城市;Pno, Pname, Color, Weight 分别表示零件的编号、名称、颜色和重量;Jno, Jname, Jcity 分别表示工程的编号、名称和所在城市; SPJ 是供应关系,Quantity 是特定供应商一次向特定工程供应的特定零件的数量。请使用 SQL 语句表示如下功能:

题目都比较简单，开始之前，做部分初始化：

1
2
create database stu;         ## 创建名为：stu的数据库
use stu;                     ## 使用stu数据库

(1) 使用 SQL 创建基本表,确定每个属性的类型、缺省值和约束条件。

1
2
3
4
5
CREATE TABLE Suppliers (
Sno CHAR (8) PRIMARY KEY,
Sname CHAR (8) NOT NULL,
Status INT,
Scity CHAR(10));
1
2
3
4
5
CREATE TABLE Parts (
Pno CHAR (8) PRIMARY KEY,
Pname CHAR(16) NOT NULL,
Color CHAR(4),
Weight NUMERIC(7, 2));
1
2
3
4
CREATE TABLE Projects (
Jno CHAR (8) PRIMARY KEY,
Jname CHAR(20) NOT NULL,
Jcity CHAR(10));

注意, 该处在操作时，将表名写为：project了，于是使用 alter语句修改表名：alter table project rename to projects;

1
2
3
4
5
6
7
8
9
CREATE TABLE SPJ (
Sno CHAR (8),
Pno CHAR(8),
Jno CHAR(8),
Quantity INT,
PRIMARY KEY (Sno,Pno,Jno),
FOREIGN KEY (Sno) REFERENCES Suppliers(Sno),
FOREIGN KEY (Pno) REFERENCES Parts(Pno),
FOREIGN KEY (Jno) REFERENCES Projects(Jno) );

该处使用了外键，注意单词一定不要拼错，在测试中 PRIMARY KEY、FOREIGN KEY的内容都需要使用括号括起来，不然会报语法错误的提示.

(2) 求上海的所有供应商的信息。

1
SELECT * FROM Suppliers WHERE Scity=‘上海’;

(3) 求位于北京的所有工程的信息。

1
SELECT * FROM Projects WHERE Jcity=‘北京’;

(4) 求数量在 100-150 的供应。

1
SELECT * FROM SPJ WHERE Quantity BETWEEN 100 AND 150;

注意此处: 使用between ... and 语句。

(5) 求为工程 J1 提供零件的供应商号。

1
SELECT Sno FROM SPJ WHERE Jno=‘J1’;

(6) 求供应工程 J1 红色零件的供应商号。

1
2
3
SELECT Sno
FROM SPJ, Parts
WHERE SPJ.Pno=Parts.Pno AND Jno=‘J1’ AND Color=‘红色’;

(7) 求至少提供一种红色零件的供应商名称。

1
2
3
SELECT Sname
FROM SPJ, Parts, Suppliers
WHERE SPJ.Pno=Parts.Pno AND SPJ.Sno=Suppliers.Sno AND Color=‘红色’;

(8) 求不提供零件 P2 的供应商名称。

1
2
3
4
5
6
SELECT Sname
FROM Suppliers
WHERE NOT EXISTS
	(SELECT *
	FROM SPJ
	WHERE SPJ.Sno= Suppliers.Sno AND Pno=‘P2’);

注意此处: 使用not exists 语句。

(9) 求没有使用天津供应商生产的红色零件的工程号。

1
2
3
4
5
6
7
SELECT Jno
FROM Projects
WHERE NOT EXISTS
	(SELECT *
	 FROM SPJ, Parts, SuppliersWHERE SPJ.Sno= Suppliers.Sno AND
	 Parts.Pno=SPJ.Pno AND
	 Color=‘红色’ AND Scity=‘天津’);

(10) 求使用了本地供应商提供的零件的工程号。

1
2
3
4
5
SELECT Projects.Jno, Projects.Jname
FROM Projects,Suppliers,SPJ
WHERE SPJ.Sno= Suppliers.Sno AND
	Projects.Jno=SPJ.Jno AND
	Projects.Jcity= Suppliers.Scity;

或

1
2
3
4
5
6
7
8
SELECT Jno,Jname
FROM Projects
WHERE EXISTS (
	SELECT *
	FROM SPJ, Suppliers
	WHERE SPJ.Sno= Suppliers.Sno AND
		Projects.Jno=SPJ.Jno AND
		Projects.Jcity= Suppliers.Scity);

注意此处: 使用exists 语句。

(11) 求未使用本地供应商提供的零件的工程号和工程名称。

1
2
3
4
5
6
7
8
SELECT Jno,Jname
FROM Projects
WHERE NOT EXISTS (
	SELECT *
	FROM SPJ, Suppliers
	WHERE SPJ.Sno= Suppliers.Sno AND
            Projects.Jno=SPJ.Jno AND
            Projects.Jcity= Suppliers.Scity);

(12) 求至少用了供应商 S1 所供应的全部零件的工程号。

1
2
3
4
5
6
7
8
9
10
11
SELECT Jno
FROM Projects
WHERE NOT EXISTS (
	SELECT *
	FROM SPJ SPJ1
	WHERE SPJ1.Sno = ‘S1’ AND
	NOT EXISTS (
		SELECT *
		FROM SPJ SPJ2
		WHERE SPJ2.Jno= Projects.Jno AND
		SPJ2.Pno= SPJ1.Pno));

(13) 求提供所有零件的供应商名称。

1
2
3
4
5
6
7
8
9
10
SELECT Sname
FROM Suppliers
WHERE NOT EXISTS (
	SELECT *
	FROM SPJ SPJ1
		WHERE NOT EXISTS (
			SELECT *
            FROM SPJ SPJ2
            WHERE SPJ2.Sno= Suppliers.Sno AND
            SPJ2.Pno= SPJ1.Pno));

注意此处: 使用建立别名的机制。

(14) 求提供了零件的供应商的个数。

1
SELECT distinct Sno FROM SPJ;

注意此处的distinct语句。

(15) 求所有零件的平均重量。

1
SELECT avg(weight) FROM Parts;

注意此处的avg函数，用于求取平均值。

(16) 求供应商 S1 提供的每种零件的总数量。

1
SELECT Pno, sum(quantity) FROM SPJ WHERE Sno=‘S1’ GROUP BY Pno;

注意此处的sum函数，用于求和；以及GROUP BY语句用于分组。

(17) 求供应商 S1 供应工程 J1 的每种零件的总重量。

1
2
3
4
SELECT sum(weight)
FROM Parts, SPJ
WHERE SPJ.Pno=Parts.Pno AND Sno=‘S1’ AND Jno=‘J1’
GROUP BY Parts.Pno;

@@@@@@@看来这部分要写成系列的了，今天就到这儿，未完待续…

参考资料

[1]. http://www.shuojuzi.com/s/shicidaquan/128764.html
[2]. https://www.douban.com/note/155415235/
[3]. http://www.cnblogs.com/wzmenjoy/p/4244545.html
[4]. http://www.tanbo.name/html/544.html
[5]. http://jxtm.jzu.cn/?p=258
[6]. http://www.jb51.net/article/23966.htm
[7]. http://jingyan.baidu.com/article/425e69e6bbc6c7be14fc1640.html

0%