SQL 数据库上机实验（查询操作）
程序员文章站
2022-05-08 17:41:27
...
实验一
实验步骤：
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（商品数量）在20和50之间的商品编号、商品名称和商品数量；
4．查询orders表中各会员购买商品的总量，并以汉字列标题形式输出会员帐号，商品总额；
5．查询members表中家庭地址为“湖南”的会员详细信息；
6．查询members表中年龄大于30且性别为“男”的会员详细信息；
7．查询orders表各商品销售总量前3名的商品编号和销售总量；
8．查询orders表中购买过商品的会员帐号，要求去掉重复行；
9．查询orders表已确认、已支付和已配送的订单详细信息；
10．查询性别为“男”的会员详细信息，查询结果按月薪降序排列；
11．查询购买商品号为’0910810004’总人数；
12．查询2018年6月6日前，所有商品的订购总量，要求输出商品号和订购总量；
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;
上一篇： 如何使用脚本化pipeline
下一篇： 数据库sql查询语句备忘