今天完成了数据库实验二
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 中了解关于数据库管理的各种直接操作的方 法。将了解的内容写入实验报告
附录
实验二 SQL 语句.doc
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)
1.2同实验1一样
3.把附录的语句直接输入就行
执行成功后:
4.1查询所有供应商情况,先按城市升序排列,城市相同按供应商名称降序排列。
SELECT * FROM s ORDER BY city ASC, sname DESC;
查询结果:
4.2查询所有零件情况,先按零件名称升序排列,零件名称相同按重量降序排列。
SELECT * FROM p ORDER BY pname ASC, weight DESC;
查询结果:
SELECT * FROM s WHERE SUBSTRING(sname, 2, 1) = '方';
4.3查询项目名中含有“厂”的项目情况。
SELECT * FROM j WHERE jname LIKE '%厂%';
查询结果:
4.4查询供应商名称中第二个字为“方”的供应商情况。
SELECT * FROM s WHERE SUBSTRING(sname, 2, 1) = '方';
查询结果:
4.5查询所有零件中的最大、最小、平均重量。
SELECT MAX(weight) AS max_weight, MIN(weight) AS min_weight, AVG(weight) AS avg_weight FROM p;
查询结果:
4.6查询零件中名为“螺丝刀”的零件的种类数、平均重量。
SELECT COUNT(DISTINCT pname) AS num_categories, AVG(weight) AS avg_weight FROM p WHERE pname = '螺丝刀';
查询结果:
4.7查询供应商 S1 所供应的各种零件的名称和数量。
SELECT p.pname, spj.qty FROM spj JOIN p ON spj.pno = p.pno WHERE spj.sno = 's1';
查询结果:
4.8查询工程 J1 所使用的各种零件的名称和数量。
SELECT p.pname, spj.qty FROM spj JOIN p ON spj.pno = p.pno WHERE spj.jno = 'j1';
查询结果:
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 != '红';
查询结果:
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 != '红';
查询结果:
4.11查询所用零件数量超过 500 的工程项目号。
SELECT jno FROM spj GROUP BY jno HAVING SUM(qty) > 500;
查询结果:
4.12查询所用零件种类超过 3 种的工程项目名称。
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;
查询结果:
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);
查询结果:
4.14查询至少供应了工程 J1 所使用的全部零件的供应商名称。
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'
);
查询结果:
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 = (SELECT sno FROM s WHERE sname = '东方红');
结果查询:
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 = '机车厂';
5.1
查询机车厂工程项目使用的各种零件代码及其数量。
SELECT part_code, SUM(supply_quantity) AS total_quantity FROM locomotive_supply_view GROUP BY part_code;
查询结果:
5.2
查询处供应商 S2 的供应情况。
6.
创建数据库:
在 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” 窗口中,通过右键单击特定的数据库,可以执行管理任务,如压缩数据库、清理历史数据等。