5.23实验二 SQL 语言的使用

一、实验目的：

掌握使用 SQL 语言进行各种查询的操作和视图的操纵方法。

二、实验要求：

在现有的数据库上进行各种查询操作，对视图的创建、使用等操作。

三、实验步骤：

1、 开始→程序→Microsoft SQL Server→SQL Server Management Studio。

2、 在“连接到服务器”对话框中，选择“Windows 身份验证”，点击“连接”，进

入 SQL Server Management Studio 操作界面。

3、单击“新建查询”按钮，导入SQL 语句，建立起实验要操作的数据库 test1，并在库中建立表、数据。

（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)

4、在数据库 test1 中进行下列查询操作。

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

SELECT *

FROM s

ORDER BY city ASC, sname DESC;

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

SELECT *

FROM p

ORDER BY pname ASC, weight DESC;

（3）查询项目名中含有“厂”的项目情况。

SELECT *

FROM j

WHERE jname LIKE '%厂%';

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

SELECT *

FROM s

WHERE SUBSTRING(sname, 2, 1) = '方';

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

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

FROM p;

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

SELECT COUNT(DISTINCT pname) AS part_count, AVG(weight) AS avg_weight

FROM p

WHERE pname = '螺丝刀';

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

SELECT p.pname, spj.qty

FROM spj

JOIN p ON spj.pno = p.pno

WHERE spj.sno = 's1';

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

SELECT p.pname, spj.qty

FROM spj

JOIN p ON spj.pno = p.pno

WHERE spj.jno = 'j1';

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

SELECT jname

FROM j

WHERE jno NOT IN (SELECT jno FROM spj WHERE pno = 'p4' AND color = '红');

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

SELECT sname

FROM s

WHERE sno NOT IN (SELECT sno FROM spj WHERE pno = 'p4' AND color = '红');

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

SELECT jno

FROM spj

GROUP BY jno

HAVING SUM(qty) > 500;

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

SELECT jname

FROM j

WHERE jno IN (SELECT jno FROM spj GROUP BY jno HAVING COUNT(DISTINCT pno) > 3);

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

SELECT jname

FROM j

WHERE jno IN (SELECT jno FROM spj GROUP BY jno HAVING COUNT(DISTINCT pno) = (SELECT COUNT(DISTINCT pno) FROM p));

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

SELECT sname

FROM s

WHERE sno IN (SELECT sno FROM spj WHERE jno = 'j1' GROUP BY sno HAVING COUNT(DISTINCT pno) = (SELECT COUNT(DISTINCT pno) FROM spj WHERE jno = 'j1'));

（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;

（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 = 's3' AND sname = '东方红';

5、请为机车厂工程项目建立一个供应情况的视图，包括供应商代码，零件代码， 

供应数量。

建立试图：

CREATE VIEW locomotive_project_supply AS

SELECT sno, pno, qty

FROM spj

WHERE jno = 'j5';

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

SELECT pno, SUM(qty) AS total_quantity

FROM locomotive_project_supply

GROUP BY pno;

（2）查询处供应商 S2 的供应情况。

SELECT pno, qty

FROM spj

WHERE sno = 's2';

6、 在 SQL Server Management Stdio 中了解关于数据库管理的各种直接操作的方

法。

1. 创建数据库：使用 T-SQL 命令 `CREATE DATABASE database_name;` 来创建新的数据库。

2. 创建表：使用 T-SQL 命令 `CREATE TABLE table_name (column1 datatype, column2 datatype, …);` 来创建新的表。

3. 插入数据：使用 T-SQL 命令 `INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);` 来向表中插入数据。

4. 更新数据：使用 T-SQL 命令 `UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;` 来更新表中的数据。

5. 删除数据：使用 T-SQL 命令 `DELETE FROM table_name WHERE condition;` 来删除表中的数据。

6. 查询数据：使用 T-SQL 命令 `SELECT column1, column2, … FROM table_name WHERE condition;` 来查询表中的数据。

7. 管理索引：使用 SSMS 的图形界面或 T-SQL 命令来创建、修改或删除索引以提高数据库性能。

8. 备份和恢复数据库：使用 SSMS 的图形界面或 T-SQL 命令进行数据库的备份和恢复操作。

9. 监控数据库性能：使用 SSMS 的性能监视器和执行计划功能来监控数据库的性能并进行优化。

四、体会：

通过完成上述数据库操作实验，我对数据库管理有了更深入的了解。创建数据库和表格是数据库设计的基础，而插入、更新、删除数据则是数据库操作中常见的任务。通过查询数据和创建视图，我能够从数据库中检索所需的信息并组织成有意义的形式。视图的创建使得可以将复杂的查询结果存储为虚拟表格，方便后续的查询和分析。

同时了解如何管理索引以提高数据库性能是非常重要的。索引的正确使用可以加快查询速度，提升数据库的效率。此外，备份和恢复数据库也是数据库管理中至关重要的一部分，保证数据的安全性和可靠性。