利用SQL语句查询（基于MSSQL）

用SQL语句建立第2章习题6中的4个表，针对建立的4个表用SQL语句完成以下查询

（1）求供应工程J1零件的供应商号码SNO

（2）求供应工程J1零件P1的供应商号码SNO

（3）找出所有供应商的姓名和所在城市

（4）找出所有零件的名称、颜色和重量

（5）找出使用供应商S1所供应零件的工程号码

（1）先建数据库，建表和导入数据：

CREATE DATABASE SPJ 
on primary
(
name=SPJ_data,
filename='E:\Data2\SPJ_data.mdf',
size=10MB,
filegrowth=10%,
maxsize=UNLIMITED
)
log on
(
name=SPJ_log,
filename='E:\Data2\SPJ_log.ldf',
maxsize=10MB,
filegrowth=1MB
)

USE SPJ;
CREATE TABLE S(
SNO CHAR(3) PRIMARY KEY,
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10));
 
CREATE TABLE P(
PNO CHAR(3),
PNAME CHAR(10),
COLOR CHAR(4),
WEIGHT INT,
PRIMARY KEY(PNO));
 
CREATE TABLE J(
JNO CHAR(3),
JNAME CHAR(10),
CITY CHAR(10),
PRIMARY KEY(JNO));
 
CREATE TABLE SPJ(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
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','天津'),
('S2','盛锡','10','北京'),
('S3','东方红','30','北京'),
('S4','丰泰盛','20','天津'),
('S5','为民','30','上海');
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES
('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);
 
INSERT INTO J VALUES
('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无限电厂','常州'),
('J7','半导体厂','南京');
 
INSERT INTO SPJ VALUES
('S1','P1','J1',200),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',50);

 

（2）开始用SQL语句查询

（1）求供应工程J1零件的供应商号码SNO

select distinct SNO from SPJ where JNO='J1';

（2）求供应工程J1零件P1的供应商号码SNO

select distinct SNO from SPJ where JNO='J1' and PNO='p1';

（3）找出所有供应商的姓名和所在城市

select SNAME,CITY from S ;

（4）找出所有零件的名称、颜色和重量

select PNAME,COLOR,WEIGHT FROM P;

（5）找出使用供应商S1所供应零件的工程号码

select distinct PNO from SPJ where SNO='S1';

 额 ，先写这么多，后面再总结下常见的在MSSQL上的SQL语句查询，今天不想写了😶