论初学Mysql

最新推荐文章于 2024-10-05 20:46:09 发布

原创最新推荐文章于 2024-10-05 20:46:09 发布 · 126 阅读

CC 4.0 BY-SA版权

博客围绕数据库的基础操作展开，主要介绍了各种连接查询。包括创建customers、orders等表，插入数据，展示了内连接、外连接、右连接的不同写法，还涉及多表查询。此外，详细阐述了子查询的多种用法，如使用ANY、SOME、ALL等关键字，以及EXISTS关键字的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

基础操作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);