2024.5.17

31 0 0

今天完成了数据库实验二

1、 开始→程序→Microsoft SQL Server→SQL Server Management Studio。
2、 在“连接到服务器”对话框中，选择“Windows 身份验证”，点击“连接”，进
入 SQL Server Management Studio 操作界面。
3、单击“新建查询”按钮，在上部的空白区导入文件“实验二 SQL 语句.doc”中
的 SQL 语句，然后单击工具栏上 “执行”按钮（红色叹号）。建立起实验要操
作的数据库 test1，并在库中建立表、数据。下部的空白区显示该语句的运行情
况。（注：以下操作均在上部的空白区输入 SQL 语句，单击执行后，下部的空
白区显示该语句的运行情况。）
4、在数据库 test1 中进行下列查询操作，将查询语句与结果写入实验报告。
（1）查询所有供应商情况，先按城市升序排列，城市相同按供应商名称降序排列。
（2）查询所有零件情况，先按零件名称升序排列，零件名称相同按重量降序排列。
（3）查询项目名中含有“厂”的项目情况。
（4）查询供应商名称中第二个字为“方”的供应商情况。
（5）查询所有零件中的最大、最小、平均重量。
（6）查询零件中名为“螺丝刀”的零件的种类数、平均重量。
（7）查询供应商 S1 所供应的各种零件的名称和数量。
（8）查询工程 J1 所使用的各种零件的名称和数量。
（9）查询没有使用红色螺丝刀的工程名称。
（10）查询没有供应红色螺丝刀的供应商名称。
（11）查询所用零件数量超过 500 的工程项目号。
（12）查询所用零件种类超过 3 种的工程项目名称。
（13）查询使用了全部零件的工程项目名称。
（14）查询至少供应了工程 J1 所使用的全部零件的供应商名称。
（15）查询供应情况，显示内容为供应商名称、零件名、工程名称、数量。
（16）查询“东方红”供应商供应情况，显示供应的零件名、工程名称、数量。
5、请为机车厂工程项目建立一个供应情况的视图，包括供应商代码，零件代码， 
 供应数量。针对该试图完成如下查询并将 SQL 语句写入实验报告。
 （1）查询机车厂工程项目使用的各种零件代码及其数量。
 （2）查询处供应商 S2 的供应情况。
6、 在 SQL Server Management Stdio 中了解关于数据库管理的各种直接操作的方
法。将了解的内容写入实验报告

2024.5.17

附录

实验二 SQL 语句.doc

2024.5.17

1、 创建数据库： 
create database test1;
2、 创建二维表： 
Create table s(
Sno char(2) primary key,
sname varchar(10) not null,
status int,
city varchar(10));
create table p(
pno char(2) primary key,
pname varchar(10) not null,
color varchar(6),
weight int);
create table j(
jno char(2) primary key,
jname varchar(20) not null,
city varchar(10));
create table spj(
sno char(2) foreign key references s(sno),
pno char(2) foreign key references p(pno),
jno char(2) foreign key references j(jno),
qty int,
primary key(sno,pno,jno));
3、 为各二维表插入数据： 
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','j3',300);
insert into spj values('s4','p6','j4',200);
insert into spj values('s5','p2','j4',100);
insert int`o spj values('s5','p3','j1',200);
insert into spj values('s5','p6','j2',200);
insert into spj values('s5','p6','j4',500)

2024.5.17

1.2同实验1一样

3.把附录的语句直接输入就行

执行成功后：

2024.5.17

4.1查询所有供应商情况，先按城市升序排列，城市相同按供应商名称降序排列。

SELECT *
FROM s
ORDER BY city ASC, sname DESC;

查询结果：

2024.5.17

4.2查询所有零件情况，先按零件名称升序排列，零件名称相同按重量降序排列。

SELECT *
FROM p
ORDER BY pname ASC, weight DESC;

查询结果：

2024.5.17

SELECT *
FROM s
WHERE SUBSTRING(sname, 2, 1) = '方';

4.3查询项目名中含有“厂”的项目情况。

SELECT *
FROM j
WHERE jname LIKE '%厂%';

查询结果：

2024.5.17

4.4查询供应商名称中第二个字为“方”的供应商情况。

SELECT *
FROM s
WHERE SUBSTRING(sname, 2, 1) = '方';

查询结果：

2024.5.17

4.5查询所有零件中的最大、最小、平均重量。

SELECT MAX(weight) AS max_weight, MIN(weight) AS min_weight, AVG(weight) AS avg_weight
FROM p;

查询结果：

2024.5.17

4.6查询零件中名为“螺丝刀”的零件的种类数、平均重量。

SELECT COUNT(DISTINCT pname) AS num_categories, AVG(weight) AS avg_weight
FROM p
WHERE pname = '螺丝刀';

查询结果：

2024.5.17

4.7查询供应商 S1 所供应的各种零件的名称和数量。

SELECT p.pname, spj.qty
FROM spj
JOIN p ON spj.pno = p.pno
WHERE spj.sno = 's1';

查询结果：

2024.5.17

4.8查询工程 J1 所使用的各种零件的名称和数量。

SELECT p.pname, spj.qty
FROM spj
JOIN p ON spj.pno = p.pno
WHERE spj.jno = 'j1';

查询结果：

2024.5.17

4.9查询没有使用红色螺丝刀的工程名称。

SELECT DISTINCT j.jname
FROM j
LEFT JOIN spj ON j.jno = spj.jno
LEFT JOIN p ON spj.pno = p.pno
WHERE p.pname != '螺丝刀' OR p.color != '红';

查询结果：

2024.5.17

4.10查询没有供应红色螺丝刀的供应商名称。

SELECT DISTINCT s.sname
FROM s
LEFT JOIN spj ON s.sno = spj.sno
LEFT JOIN p ON spj.pno = p.pno
WHERE p.pname != '螺丝刀' OR p.color != '红';

查询结果：

2024.5.17

4.11查询所用零件数量超过 500 的工程项目号。

SELECT jno
FROM spj
GROUP BY jno
HAVING SUM(qty) > 500;

查询结果：

2024.5.17

4.12查询所用零件种类超过 3 种的工程项目名称。

2024.5.17

SELECT j.jname
FROM j
JOIN spj ON j.jno = spj.jno
JOIN p ON spj.pno = p.pno
GROUP BY j.jname
HAVING COUNT(DISTINCT p.pname) > 3;

2024.5.17

查询结果：

2024.5.17

4.13查询使用了全部零件的工程项目名称。

SELECT j.jname
FROM j
JOIN spj ON j.jno = spj.jno
GROUP BY j.jname
HAVING COUNT(DISTINCT spj.pno) = (SELECT COUNT(DISTINCT pno) FROM p);

查询结果：

2024.5.17

4.14查询至少供应了工程 J1 所使用的全部零件的供应商名称。

2024.5.17

SELECT s.sname
FROM s
JOIN spj ON s.sno = spj.sno
WHERE spj.jno = 'j1'
GROUP BY s.sname
HAVING COUNT(DISTINCT spj.pno) >= (
SELECT COUNT(DISTINCT pno)
FROM spj
WHERE jno = 'j1'
);

2024.5.17

查询结果：

2024.5.17

15.查询供应情况，显示内容为供应商名称、零件名、工程名称、数量。

SELECT s.sname, p.pname, j.jname, spj.qty
FROM spj
JOIN s ON spj.sno = s.sno
JOIN p ON spj.pno = p.pno
JOIN j ON spj.jno = j.jno;

查询结果：

2024.5.17

16.查询“东方红”供应商供应情况，显示供应的零件名、工程名称、数量

SELECT p.pname, j.jname, spj.qty
FROM spj
JOIN p ON spj.pno = p.pno
JOIN j ON spj.jno = j.jno
WHERE spj.sno = (SELECT sno FROM s WHERE sname = '东方红');

结果查询：

2024.5.17

5.新建视图的语句

CREATE VIEW locomotive_supply_view AS
SELECT spj.sno AS supplier_code, spj.pno AS part_code, spj.qty AS supply_quantity
FROM spj
JOIN j ON spj.jno = j.jno
WHERE j.jname = '机车厂';

2024.5.17

5.1

查询机车厂工程项目使用的各种零件代码及其数量。

SELECT part_code, SUM(supply_quantity) AS total_quantity
FROM locomotive_supply_view
GROUP BY part_code;

查询结果：

2024.5.17

5.2

查询处供应商 S2 的供应情况。

2024.5.17

创建数据库：

在 SSMS 中，通过执行 CREATE DATABASE 语句或者使用图形化界面创建新数据库。在 “Object Explorer” 窗口中右键单击 “Databases”，然后选择 “New Database”，按照向导指示创建新数据库。

创建表：

使用 CREATE TABLE 语句创建新表。在查询编辑器中编写表的创建语句，或者在 “Object Explorer” 窗口中右键单击特定的数据库，选择 “New Query” 并输入表创建语句。
另外，可以使用图形化界面创建表。在特定的数据库中，右键单击 “Tables”，然后选择 “New Table”，然后在弹出的窗口中定义表结构。

插入数据：

使用 INSERT INTO 语句将数据插入表中。在查询编辑器中编写插入语句，或者使用图形化界面的 “Edit Top 200 Rows” 功能手动插入数据。

执行查询：

在查询编辑器中编写 SQL 查询语句，并执行它们。查询结果将显示在结果窗口中。
另外，可以使用 “Design Query in Editor” 功能构建复杂的查询，并直接在查询设计器中执行。

备份和还原数据库：

在 “Object Explorer” 窗口中，右键单击要备份或还原的数据库，然后选择 “Tasks” -> “Backup” 或 “Restore”，然后按照向导指示进行操作。
可以使用 Transact-SQL 的 BACKUP DATABASE 和 RESTORE DATABASE 命令进行备份和还原。

管理表结构：

在 “Object Explorer” 窗口中，通过右键单击特定的表，可以进行诸如修改表结构、添加索引、添加约束等操作。

执行管理任务：

在 “Object Explorer” 窗口中，通过右键单击特定的数据库，可以执行管理任务，如压缩数据库、清理历史数据等。

# 随笔