《小常识-25》sq随笔2

# 创建表连接测试表
CREATE TABLE T_Customer (
    FId INT NOT NULL,
    FName VARCHAR (20) NOT NULL,
    FAge INT,
    PRIMARY KEY (FId)
)
CREATE TABLE T_OrderType (
    FId INT NOT NULL,
    FName VARCHAR (20) NOT NULL,
    PRIMARY KEY (FId)
)
CREATE TABLE T_Order (
    FId INT NOT NULL,
    FNumber VARCHAR (20) NOT NULL,
    FPrice DECIMAL (10, 2),
    FCustomerId INT,
    FTypeId INT,
    PRIMARY KEY (FId)
)
#插入测试数据
INSERT INTO T_Customer(FId,FName,FAge)
VALUES(1,'TOM',21);
INSERT INTO T_Customer(FId,FName,FAge)
VALUES(2,'MIKE',24);
INSERT INTO T_Customer(FId,FName,FAge)
VALUES(3,'JACK',30);
INSERT INTO T_Customer(FId,FName,FAge)
VALUES(4,'TOM',25);
INSERT INTO T_Customer(FId,FName,FAge)
VALUES(5,'LINDA',NULL);
INSERT INTO T_OrderType(FId,FName)
VALUES(1,'MarketOrder');
INSERT INTO T_OrderType(FId,FName)
VALUES(2,'LimitOrder');
INSERT INTO T_OrderType(FId,FName)
VALUES(3,'Stop Order');
INSERT INTO T_OrderType(FId,FName)
VALUES(4,'StopLimit Order');
INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)
VALUES(1,'K001',100,1,1);
INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)
VALUES(2,'K002',200,1,1);
INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)
VALUES(3,'T003',300,1,2);
INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)
VALUES(4,'N002',100,2,2);
INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)
VALUES(5,'N003',500,3,4);
INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)
VALUES(6,'T001',300,4,3);
INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)
VALUES(7,'T002',100,NULL,1);

#测试查询
SELECT * FROM T_Customer
SELECT * FROM T_OrderType
SELECT * FROM T_Order

#普通查询
SELECT
    FId
FROM
    T_Customer
WHERE
    FName = 'MIKE'

#内链接查询
SELECT
    FNumber,
    FPrice
FROM
    T_Order
INNER JOIN T_Customer ON FCustomerId = T_Customer.FId
WHERE
    T_Customer.FName = 'TOM'

#非等值链接
SELECT
    T_Order.FNumber,
    T_Order.FPrice,
    T_Customer.FName,
    T_Customer.FAge
FROM
    T_Order
INNER JOIN T_Customer ON T_Order.FPrice < T_Customer.FAge * 5

SELECT
    T_Order.FNumber,
    T_Order.FPrice,
    T_Customer.FName,
    T_Customer.FAge
FROM
    T_Order
INNER JOIN T_Customer ON T_Order.FPrice < T_Customer.FAge * 5
AND T_Order.FCustomerId = T_Customer.FId

#交叉连接
SELECT
    c.FId,
    c.FName,
    c.FAge,
    o.FId,
    o.FNumber,
    o.FPrice
FROM
    T_Customer c,
    T_Order o



SELECT
    T_Customer.FId,
    T_Customer.FName,
    T_Customer.FAge,
    T_Order.FId,
    T_Order.FNumber,
    T_Order.FPrice
FROM
    T_Customer
CROSS JOIN T_Order

#自连接
SELECT
    FNumber,
    FPrice,
    FTypeId
FROM
    T_Order
WHERE
    FTypeId = FTypeId


#全连接的效果
SELECT
    o.FNumber,
    o.FPrice,
    o.FCustomerId,
    c.FName,
    c.FAge
FROM
    T_Order o
LEFT OUTER JOIN T_Customer c ON o.FCustomerId = c.FId
UNION
    SELECT
        o.FNumber,
        o.FPrice,
        o.FCustomerId,
        c.FName,
        c.FAge
    FROM
        T_Order o
    RIGHT OUTER JOIN T_Customer c ON o.FCustomerId = c.FId

#删除测试表
DROP TABLE T_Order;
DROP TABLE T_OrderType;
DROP TABLE T_Customer;

#子查询
#创建表
CREATE TABLE T_Reader (
    FId INT NOT NULL,
    FName VARCHAR (50),
    FYearOfBirth INT,
    FCity VARCHAR (50),
    FProvince VARCHAR (50),
    FYearOfJoin INT
)

CREATE TABLE T_Book (
    FId INT NOT NULL,
    FName VARCHAR (50),
    FYearPublished INT,
    FCategoryId INT
)

CREATE TABLE T_ReaderFavorite (
    FCategoryId INT,
    FReaderId INT
)

CREATE TABLE T_Category (
    FId INT NOT NULL,
    FName VARCHAR (50)
)
#插入测试数据
INSERT INTO T_Category(FId,FName)
VALUES(1,'Story');
INSERT INTO T_Category(FId,FName)
VALUES(2,'History');
INSERT INTO T_Category(FId,FName)
VALUES(3,'Theory');
INSERT INTO T_Category(FId,FName)
VALUES(4,'Technology');
INSERT INTO T_Category(FId,FName)
VALUES(5,'Art');
INSERT INTO T_Category(FId,FName)
VALUES(6,'Philosophy');
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(1,'Tom',1979,'TangShan','Hebei',2003);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(2,'Sam',1981,'LangFang','Hebei',2001);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(3,'Jerry',1966,'DongGuan','GuangDong',1995);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(4,'Lily',1972,'JiaXing','ZheJiang',2005);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(5,'Marry',1985,'BeiJing','BeiJing',1999);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(6,'Kelly',1977,'ZhuZhou','HuNan',1995);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(7,'Tim',1982,'YongZhou','HuNan',2001);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(8,'King',1979,'JiNan','ShanDong',1997);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(9,'John',1979,'QingDao','ShanDong',2003);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(10,'Lucy',1978,'LuoYang','HeNan',1996);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(11,'July',1983,'ZhuMaDian','HeNan',1999);
INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)
VALUES(12,'Fige',1981,'JinCheng','ShanXi',2003);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(1,'About J2EE',2005,4);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(2,'Learning Hibernate',2003,4);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(3,'Two Cites',1999,1);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(4,'Jane Eyre',2001,1);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(5,'Oliver Twist',2002,1);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(6,'History of China',1982,2);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(7,'History of England',1860,2);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(8,'History of America',1700,2);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(9,'History of The World',2008,2);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(10,'Atom',1930,3);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(11,'RELATIVITY',1945,3);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(12,'Computer',1970,3);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(13,'Astronomy',1971,3);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(14,'How To Singing',1771,5);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(15,'DaoDeJing',2001,6);
INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)
VALUES(16,'Obedience to Authority',1995,6);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(1,1);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(5,2);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(2,3);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(3,4);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(5,5);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(1,6);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(1,7);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(4,8);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(6,9);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(5,10);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(2,11);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(2,12);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(1,12);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(3,1);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(1,3);
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
VALUES(4,4);

#测试子查询
SELECT  1  AS  f1,2,(SELECT  MIN(FYearPublished)  FROM  T_Book),(SELECT
MAX(FYearPublished) FROM T_Book) AS f4

#测试列值子查询
SELECT T_Reader.FName,t2.FYearPublished,t2.FName
FROM T_Reader,
(SELECT * FROM T_Book WHERE FYearPublished < 1800) t2