(1) σ A = 10 ( S ) \sigma_{A=10}(S) σA=10(S)
SELECT *
FROM S
WHERE A=10;
(2) Π A , B ( S ) \Pi_{A,B}(S) ΠA,B(S)
SELECT A,B
FROM S;
(3)
S
⋈
T
S\bowtie T
S⋈T
自然连接:在等值连接的前提下,把重复的目标列去掉
SELECT A,B,S.C,S.D,E,F
FROM S,T
WHERE S.C=T.C AND S.D=T.D;
(4) S ⋈ S . C = T . C T S\underset{S.C=T.C}\Join T SS.C=T.C⋈T
SELECT A,B,S.C,S.D,E,F
FROM S,T
WHERE S.C=T.C ;
(5) S ⋈ A < E T S\underset{A<E}\Join T SA<E⋈T
SELECT A,B,S.C,S.D,E,F
FROM S,T
WHERE S.A<T.E ;
(6) Π C , D ( S ) × T \Pi_{C,D}(S)\times T ΠC,D(S)×T
SELECT S.C,S.D,T.*
FROM S,T;
4.
/*建表*/
CREATE TABLE S
(SNO char(5) PRIMARY KEY,
SNAME char(10),
STATUS int,
CITY char(10)
);
/*插入数据*/
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,'上海');
/*查询表*/
SELECT *
FROM S;
CREATE TABLE P /*建表*/
(PNO char(5) PRIMARY KEY,
PNAME char(10),
COLOR char(5),
WEIGHT int
);
/*插入数据*/
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);
/*查询表*/
SELECT *
FROM P;
CREATE TABLE J /*建表*/
(JNO char(5) PRIMARY KEY,
JNAME char(10),
CITY char(5)
);
/*插入数据*/
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','半导体厂','南京');
/*查询表*/
SELECT *
FROM J;
CREATE TABLE SPJ /*建表*/
(SNO char(4),
PNO char(4),
JNO char(4),
QTY int
);
/*插入数据*/
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);
/*查询*/
SELECT * FROM SPJ;
SELECT SNO
FROM SPJ
WHERE JNO='J1';
/*优化一下,去掉重复的*/
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO='J1';
这时的查询结果会有重复行,想去掉重复行必须指定DISTINCT,如果没有指定DISTINCT关键词,就默认ALL(保留所有行)
SELECT SNO
FROM SPJ
WHERE JNO='J1' AND PNO='P1';
SELECT SNO
FROM SPJ,P
WHERE JNO='J1' AND COLOR='红' AND SPJ.PNO=P.PNO;
先找出使用天津供应商生产的红色零件,然后用NOT IN 取不在这一集合的元组。
SELECT JNO
FROM SPJ
WHERE JNO NOT IN
(SELECT JNO
FROM SPJ,S,P
WHERE SPJ.PNO=P.PNO AND P.COLOR='红' AND SPJ.SNO=S.SNO AND S.CITY='天津');
/*去重*/
SELECT DISTINCT JNO
FROM SPJ
WHERE JNO NOT IN
(SELECT JNO
FROM SPJ,S,P
WHERE SPJ.PNO=P.PNO AND P.COLOR='红' AND SPJ.SNO=S.SNO AND S.CITY='天津');
至少:双重否定NOT EXISTS
SELECT JNO
FROM SPJ
WHERE NOT EXISTS
(SELECT *
FROM S S1
WHERE NOT EXISTS
(SELECT *
FROM S S2
WHERE SPJ.SNO=S2.SNO AND S2.SNO='S1'
)
);
5.
SELECT SNAME,CITY
FROM S;
SELECT PNAME,COLOR,WEIGHT
FROM P;
SELECT JNO
FROM SPJ
WHERE SPJ.SNO='S1';
SELECT PNAME,QTY
FROM SPJ,P
WHERE SPJ.JNO='J2' AND SPJ.PNO=P.PNO ;
SELECT PNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY='上海';
/*去重*/
SELECT DISTINCT PNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY='上海';
SELECT JNAME
FROM SPJ,J,S
WHERE SPJ.SNO=S.SNO AND S.CITY='上海' AND SPJ.JNO=J.JNO;
/*去重*/
SELECT DISTINCT JNAME
FROM SPJ,J,S
WHERE SPJ.SNO=S.SNO AND S.CITY='上海' AND SPJ.JNO=J.JNO;
SELECT JNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY <> '天津';
天津产的有S1,S4
重复的有点多,我感觉去重后的更清楚。
SELECT DISTINCT JNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY <> '天津';
UPDATE P
SET COLOR='蓝'
WHERE P.COLOR='红';
SELECT * FROM P;
UPDATE SPJ
SET SPJ.SNO='S3'
WHERE SPJ.SNO='S5' AND SPJ.JNO='J4' AND SPJ.PNO='P6';
SELECT * FROM SPJ;
DELETE
FROM S
WHERE SNO='S2' ;
DELETE
FROM SPJ
WHERE SNO='S2' ;
INSERT
INTO SPJ
VALUES ('S2','J6','P4',200);
属性列与建表的顺序相同
9.
建立视图
CREATE VIEW IS_SPJ(SNO,PNO,QTY)
AS
SELECT SNO,PNO,QTY
FROM J,SPJ
WHERE SPJ.JNO=J.JNO AND J.JNAME='三建';
SELECT PNO,QTY
FROM IS_SPJ;
SELECT *
FROM IS_SPJ
WHERE SNO='S1';