论初学Mysql

基础操作5——各种连接查询

CREATE TABLE customers
(
    c_id INT PRIMARY KEY AUTO_INCREMENT,
    c_name CHAR(50) NOT NULL,
    c_address CHAR(50) NULL,
    c_city CHAR(50) NULL,
    c_zip CHAR(10) NULL,
    c_contact CHAR(50) NULL,
    c_email CHAR(50) NULL
)ENGINE=INNODB;

CREATE TABLE orders
(
    o_num INT PRIMARY KEY AUTO_INCREMENT,
    o_date DATETIME NOT NULL,
    c_id INT NOT NULL
)ENGINE=INNODB;

INSERT INTO customers VALUE(1001,'5号楼','广东',3000,'云','4399@qq.com');
INSERT INTO customers VALUE(1002,'1号楼','圣诞岛',3000,'游','4399@qq.com');
INSERT INTO customers VALUE(1003,'1号楼','塞维利亚',3000,'四','4399@qq.com');
INSERT INTO customers VALUE(1004,'1号楼','北京',3000,'海','4399@qq.com');
INSERT INTO customers VALUE(1005,'1号楼','上海',3000,'JAVA','4399@qq.com');
INSERT INTO customers VALUE(1006,'1号楼','天津',3000,'HTML','4399@qq.com');
INSERT INTO customers VALUE(1007,'0号楼','成都',3000,'Mysql','4399@qq.com');

INSERT INTO orders VALUE(3001,NOW(),1001);
INSERT INTO orders VALUE(3002,NOW(),1002);
INSERT INTO orders VALUE(3003,NOW(),1003);
INSERT INTO orders VALUE(3004,NOW(),1004);
INSERT INTO orders VALUE(3005,NOW(),1005);

-- 内连接写法一
SELECT
    customers.c_id,
    c_address,
    o_num,
    o_date
FROM
    customers,
    orders
WHERE
    customers.c_id = orders.c_id;
-- 内连接写法二————性能更高
SELECT
    customers.c_id,
    c_address,
    o_num,
    o_date
FROM
    customers
INNER JOIN orders ON customers.c_id = orders.c_id;
-- 外链接查询写法
SELECT
    customers.c_id,
    c_address,
    o_num,
    o_date
FROM
    customers
LEFT OUTER JOIN orders ON customers.c_id = orders.c_id;
-- 右链接查询写法
SELECT
    customers.c_id,
    c_address,
    o_num,
    o_date
FROM
    customers
RIGHT OUTER JOIN orders ON customers.c_id = orders.c_id;

-- 物流表
CREATE TABLE  logistics
(
    l_id INT PRIMARY KEY AUTO_INCREMENT,
    l_name VARCHAR(20) NOT NULL,
    l_send INT DEFAULT 0
)ENGINE=INNODB;

CREATE TABLE cover
(
    l_id INT PRIMARY KEY AUTO_INCREMENT,
    c_name VARCHAR(20) NOT NULL,
    c_send INT
)ENGINE=INNODB;

INSERT INTO logistics VALUE(9001,'顺丰',1);
INSERT INTO logistics VALUE(NULL,'邮政',1);
INSERT INTO logistics VALUE(NULL,'中通',0);
INSERT INTO logistics VALUE(NULL,'申通',0);
INSERT INTO logistics VALUE(NULL,'优速',0);

INSERT INTO cover VALUE(9001,'一号',1);
INSERT INTO cover VALUE(NULL,'二号',0);
INSERT INTO cover VALUE(NULL,'三号',0);
INSERT INTO cover VALUE(NULL,'四号',0);

SELECT
    customers.c_id,
    c_contact,
    o_num,
    l_send
FROM
    customers,
    orders,
    logistics,
    cover
WHERE
    customers.c_id = orders.c_id
AND cover.l_id = logistics.l_id
AND l_send=0;

-- 子查询
SELECT c_id FROM orders;
SELECT * FROM customers WHERE c_id = ANY (SELECT c_id FROM orders);
SELECT * FROM customers WHERE c_id = SOME (SELECT c_id FROM orders);
SELECT * FROM customers WHERE c_id > ALL (SELECT c_id FROM orders);
SELECT * FROM customers WHERE c_id = (SELECT c_id FROM orders WHERE o_num=3003);
-- 关键字子查询
SELECT * FROM customers WHERE c_id > IN (SELECT c_id FROM orders);
-- EXISTS关键字，通过判断子查询是否有结果，如果返回TRUE则执行外面的查询，如果查不到不执行外面的查询。
SELECT * FROM customers WHERE c_id EXISTS (SELECT c_id FROM orders WHERE c_id=1001);