SQL 数据库上机实验(查询操作)

该实验涉及创建和管理数据库shiyan2,包括创建表S、P、J和SPJ,插入数据,执行多种查询操作,如供应商信息、零件详情、供应关系等。此外,还进行了数据更新、删除、视图创建及查询。实验二关注eshop1数据库,涉及商品、订单、会员信息的查询,如价格筛选、数量范围查询、订单统计等。
摘要由CSDN通过智能技术生成

大二数据库上机实验

实验一

在这里插入图片描述

实验步骤:

1:创建数据库“shiyan2”;
2:创建数据表’s’,’p’,’j’;
3:创建数据表’spj’并设定’sno’,’pno’,’jno’三列为外键,分别关联于s表的’sno’,   p表的’pno’,j表的’jno’列;
4:分别向数据表’s’,’p’,’j’,’spj’中插入数据项;
5:建立三个表S,P,J,SPJ;
6:找出所有供应商的名字和所在城市;
7:找出所有零件的名称,颜色,重量;
8:找出使用供应商S1所供应零件的工程号码;
9:找出工程项目J2使用的各种零件的名称和数量;
10:找出上海厂商供应的所有零件号码;
11:找出使用上海厂的零件的工程名称;
12:找出没有使用天津产的零件的工程号码;
13:把全部红色零件的颜色改成蓝色;
14:由S5供给J4的零件P6改为由S3供应;
15:从供应商关系中删除S2的记录,并从供应关系中删除相应的记录;
16:将(S2,J6,P4,200)插入供应情况关系中;
17:建视图并进行相关查找操作。

实验代码:

    
CREATE DATABASE shiyan2;
USE shiyan2
CREATE TABLE S
(SNO CHAR (9) PRIMARY KEY,
SNAME CHAR(20),
STATUS INT,
CITY CHAR(20),
);
USE shiyan2
CREATE TABLE P
(PNO CHAR (9) PRIMARY KEY,
PNAME CHAR(20),
COLOR CHAR(20),
WEIGHT INT,
);
USE shiyan2
CREATE TABLE J
(JNO CHAR (9) PRIMARY KEY,
JNAME CHAR(20),
CITY CHAR(20),
);
USE shiyan2
CREATE TABLE SPJ
(SNO CHAR (9),
PNO CHAR (9),
JNO CHAR (9),
QTY INT,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY (SNO)REFERENCES S(SNO),
FOREIGN KEY (PNO)REFERENCES P(PNO),
FOREIGN KEY (JNO)REFERENCES J(JNO),
);
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S1','精益',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S2','盛锡',10,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S3','东方红',30,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S4','丰泰盛',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S5','为民',30,'上海');
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P1','螺母','红',12);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P2','螺栓','绿',17);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P3','螺丝刀','蓝',14);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P4','螺丝刀','红',14);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P5','凸轮','蓝',40);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P6','齿轮','红',30);
INSERT INTO J(JNO,JNAME,CITY)VALUES('J1','三建','北京');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J2','一汽','长春');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J3','弹簧厂','天津');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J4','造船厂','天津');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J5','机车厂','唐山');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J6','无线电厂','常州');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J7','半导体厂','南京');
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S1','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S1','P1','J3',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S1','P1','J4',700);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S1','P2','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J4',500);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J5',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P5','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P5','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S3','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S3','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S4','P5','J1',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S4','P6','J3',300);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S4','P6','J4',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S5','P2','J4',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S5','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S5','P6','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S5','P6','J4',500);
/*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 P.PNO=SPJ.PNO AND JNO='J2';
/*5:找出上海厂商供应的所有零件号码*/
SELECT DISTINCT PNO 
FROM S,SPJ
WHERE S.SNO=SPJ.SNO AND CITY='上海';
/*6:找出使用上海厂的零件的工程名称*/
SELECT DISTINCT JNAME
FROM SPJ,S,J
WHERE SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND S.CITY='上海';
/*7:找出没有使用天津产的零件的工程号码*/
SELECT JNO
FROM SPJ
WHERE JNO NOT IN (SELECT DISTINCT  JNO
FROM SPJ,S
WHERE S.SNO=SPJ.SNO AND S.CITY='天津');
/*8:把全部红色零件的颜色改成蓝色*/
UPDATE P
SET COLOR='蓝'
WHERE COLOR='红';
/*9:由S5供给J4的零件P6改为由S3供应*/
UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
/*10:从供应商关系中删除S2的记录,并从供应关系中删除相应的记录*/
DELETE  
FROM S
WHERE SNO='S2';

DELETE 
FROM SPJ
WHERE SNO='S2';
/*11:将(S2,J6,P4,200)插入供应情况关系*/
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S2','盛锡',10,'北京');
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P4','J6',200);
/*12:建视图*/
CREATE VIEW V_SPJ
AS
SELECT SNO,PNO,QTY
FROM SPJ,J
WHERE JNAME='三建' AND SPJ.JNO=J.JNO;
/*1:*/
SELECT PNO,QTY
FROM V_SPJ;
/*2:*/
SELECT SNO,PNO,QTY
FROM V_SPJ
WHERE SNO='S1';

实验二

在这里插入图片描述

实验步骤:


1.运行给定的SQL命令,创建eshop1数据库;
2.查询products表中p_price(商品价格)在800以上的商品详细信息;
3.查询products表中p_quantity(商品数量)在2050之间的商品编号、商品名称和商品数量;
4.查询orders表中各会员购买商品的总量,并以汉字列标题形式输出会员帐号,商品总额;
5.查询members表中家庭地址为“湖南”的会员详细信息;
6.查询members表中年龄大于30且性别为“男”的会员详细信息;
7.查询orders表各商品销售总量前3名的商品编号和销售总量;
8.查询orders表中购买过商品的会员帐号,要求去掉重复行;
9.查询orders表已确认、已支付和已配送的订单详细信息;
10.查询性别为“男”的会员详细信息,查询结果按月薪降序排列;
11.查询购买商品号为’0910810004’总人数;
12.查询201866日前,所有商品的订购总量,要求输出商品号和订购总量;
13.查询所有会员的平均月薪,最高月薪和最低月薪;
14.查询所有会员购买商品的种类和,要求输出会员号和商品种类和;
15.查询购买了商品号为“0910810004”的会员号和姓名,并以汉字标题显示;
16.使用简单查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息;
17.将members表和orders表之间的左向外联接包括所有会员的信息,包括没有购买商品的会员。

实验代码:

CREATE DATABASE eshop1 
ON
(
  NAME=eshop1_dat,
  FILENAME='c:\data\eshop1_dat.mdf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=5
)
LOG ON
(
  NAME=eshop1_log,
  FILENAME='c:\data\eshop1_log.ldf',
  SIZE=10,
  MAXSIZE=25,
  FILEGROWTH=5
)
GO
USE eshop1
GO
CREATE TABLE orders (
	M_account VARCHAR(20) NOT NULL ,
	P_no VARCHAR(20) NOT NULL ,
	O_quantity INT NOT NULL ,
	O_date DATETIME NOT NULL ,
	O_confirm_state BIT NOT NULL,
	O_pay_state BIT NOT NULL ,
	O_send_state BIT NOT NULL)
GO
CREATE TABLE members (
	M_account VARCHAR(20) NOT NULL,
	M_name VARCHAR(20) NOT NULL ,
	M_birth VARCHAR(20)  NULL ,
	M_sex CHAR(2)  NULL ,
	M_address VARCHAR(50) NULL ,
	M_salary decimal(7,1)  NULL ,
	M_password VARCHAR(20) NOT NULL 
) 
GO
CREATE TABLE products (
	P_no VARCHAR(20) NOT NULL,
	P_name VARCHAR(50) NOT NULL ,
	p_date DATETIME NULL ,
	P_quantity INT NOT NULL ,
	P_price SMALLMONEY NOT NULL ,
	P_information VARCHAR(500) NULL ,
) 
GO
INSERT INTO members VALUES('Jinjin', '津津有味', 1982-04-14,'女', '北京市', 8200.0, 'jinjin')
INSERT INTO members VALUES('Lfz', '刘法治',  1976-08-26, '男','天津市', 4500.0, 'lfz0826')
INSERT INTO members VALUES('liuzc518', '刘志成', 1972-05-18,  '男','湖南株洲', 3500.0, 'liuzc518')
INSERT INTO members VALUES('Wangym', '王咏梅',  1974-08-06,'女', '湖南长沙', 4000.0, 'wangym0806')
INSERT INTO members VALUES('Zhangzl', '张自梁', 1975-04-20,'男', '湖南株洲', 4300.0, 'zhangzl')
INSERT INTO members VALUES('zhao888', '赵爱云', 1972-02-12,'男', '湖南株洲', 5500.0, 'zhao888')
INSERT INTO products VALUES('0130810324', '清华同方电脑', '2005-12-11', 7, 8000.0, '优惠多多')
INSERT INTO products VALUES('0140810330', '洗衣粉', '2005-05-31', 1000, 8.6, '特价销售')
INSERT INTO products VALUES('0140810332', '红彤彤腊肉', '2005-05-20', 43, 15.0, '是一种卫生食品')
INSERT INTO products VALUES('0140810333', '力士牌香皂', '2005-05-06', 22, 6.0, '是一种清洁用品')
INSERT INTO products VALUES('0240810330', '电动自行车', '2005-05-31', 10, 1586.0, '价廉物美')
INSERT INTO products VALUES('0240810333', '自行车', '2005-05-31', 10, 586.0, '价廉物美')
INSERT INTO products VALUES('0910810001', '爱国者MP3', '2005-05-31', 100, 450.0, '价廉物美')
INSERT INTO products VALUES('0910810002', '商务通', '2005-05-20', 10, 850.0, '价廉物美')
INSERT INTO products VALUES('0910810003', '名人好记星', '2005-05-31', 100, 550.0, '价廉物美')
INSERT INTO products VALUES('0910810004', '奥美嘉 U盘', '2005-05-31', 100, 350.0, '价廉物美')
INSERT INTO orders VALUES('jinjin', '0910810004', 2, '2005-06-06', 1, 0, 0)
INSERT INTO orders VALUES('jinjin', '0910810004', 1, '2005-08-09', 1, 1, 1)
INSERT INTO orders VALUES('lfz', '0910810001', 1, '2005-08-09', 0, 0, 0)
INSERT INTO orders VALUES('lfz', '0910810004', 2, '2005-06-06', 1, 1, 1)
INSERT INTO orders VALUES('lfz', '0910810004', 2, '2005-08-09', 1, 1, 1)
INSERT INTO orders VALUES('liuzc518', '0140810324', 1, '2005-10-09', 0, 0, 0)
INSERT INTO orders VALUES('liuzc518', '0910810001', 1, '2005-10-09', 1, 1, 0)
INSERT INTO orders VALUES('liuzc518', '0910810004', 2, '2005-10-09', 1, 1, 0)
INSERT INTO orders VALUES('wangym', '0910810001', 1, '2005-08-09', 1, 0, 0)
INSERT INTO orders VALUES('zhao888', '0240810333', 2, '2005-06-06', 1, 1, 0)
SELECT*
FROM products
WHERE P_price>800;
SELECT P_no,P_name,P_quantity
FROM products
WHERE P_quantity>20 and P_quantity<50;
SELECT M_account,O_quantity
FROM orders;
SELECT *
FROM members
WHERE M_address='湖南株洲';
SELECT *
FROM members
WHERE DATEDIFF (yy,M_birth,GETDATE())>30 AND M_sex='男';
SELECT top 3 P_no,SUM(O_quantity) 
FROM orders
GROUP BY (P_no) ORDER BY sum(O_quantity) DESC;
SELECT DISTINCT M_account
FROM orders;
SELECT*
FROM orders
WHERE O_pay_state=1 AND O_send_state=1 AND O_confirm_state=1;
SELECT*
FROM members
WHERE M_sex='男' ORDER BY M_salary DESC;
SELECT COUNT(*)
FROM orders
WHERE P_no='0910810004';
SELECT P_no,SUM(O_quantity)
FROM orders
WHERE O_date<'2018-6-6' GROUP BY P_no;
SELECT AVG(M_salary)+MAX(M_salary)+MIN(M_salary)
FROM members;
SELECT M_account,COUNT(DISTINCT P_no)
FROM orders 
GROUP BY M_account;
SELECT  members.m_account,members.m_name
FROM products,orders,members
WHERE products.p_no=orders.p_no
AND orders.m_account=members.m_account
AND products.p_no='0910810004';
SELECT *
FROM members
WHERE DATEDIFF (YEAR ,M_birth,GETDATE())>30 AND M_address='湖南株洲';
SELECT members.*, orders.*
FROM members
LEFT OUTER JOIN orders
ON members.m_account = orders.m_account;
评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值