MySQL连接查询实例详细明白

本文实例讲述了MySQL连接查询。分享给大家供大家参考,具体如下:

本文实例讲述了MySQL
Union合并查询数据及表别名、字段别名用法。分享给大家供大家参考,具体如下:

本文实例总结了MySQL子查询操作。分享给大家供大家参考,具体如下:

创建表suppliers:

union关键字

定义两个表tb1和tb2

CREATE TABLE suppliers( s_id int NOT NULL AUTO_INCREMENT, s_name char(50) NOT NULL, s_city char(50) NULL, s_zip char(10) NULL, s_call CHAR(50) NOT NULL, PRIMARY KEY (s_id)) ;INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call)VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),(102,'LT Supplies','Chongqing','400000','44333'),(103,'ACME','Shanghai','200000','90046'),(104,'FNK Inc.','Zhongshan','528437','11111'),(105,'Good Set','Taiyuang','030000', '22222'),(106,'Just Eat Ours','Beijing','010', '45678'),(107,'DK Inc.','Zhengzhou','450000', '33332');
SELECT s_id, f_name, f_priceFROM fruitsWHERE f_price  9.0UNION ALLSELECT s_id, f_name, f_priceFROM fruitsWHERE s_id IN(101,103);
CREATE table tbl1 ( num1 INT NOT NULL);CREATE table tbl2 ( num2 INT NOT NULL);

内连接

为表取别名

向两个表中插入数据:

SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruits ,suppliersWHERE fruits.s_id = suppliers.s_id;
SELECT * from orders AS oWHERE o.o_num = 30001;

SELECT c.c_id, o.o_numFROM customers AS c LEFT OUTER JOIN orders AS oON c.c_id = o.c_id;
INSERT INTO tbl1 values(1), (5), (13), (27);INSERT INTO tbl2 values(6), (14), (11), (20);

使用 inner join 语法进行内连接查询

为字段取别名

any澳门金沙vip, some关键字的子查询

SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruitsINNER JOIN suppliers ON fruits.s_id = suppliers.s_id;
SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_priceFROM fruits AS f1WHERE f1.f_price  8;

SELECT CONCAT(RTRIM(s_name) , ' (', RTRIM(s_city), ')')FROM suppliersORDER BY s_name;

SELECT CONCAT(RTRIM(s_name) , ' (', RTRIM(s_city), ')')as suppliers_titleFROM suppliersORDER BY s_name;
SELECT num1FROM tbl1WHERE num1  ANY (SELECT num2 FROM tbl2);

创建表orders:

查询所有价格小于9的水果的列表,查询s_id等于101和103所有的水果种类,使用UNION连接查询结果

all关键字的子查询

CREATE TABLE orders( o_num int NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id int NOT NULL, PRIMARY KEY (o_num)) ;INSERT INTO orders(o_num, o_date, c_id)VALUES(30001, '2008-09-01', 10001),(30002, '2008-09-12', 10003),(30003, '2008-09-30', 10004),(30004, '2008-10-03', 10005),(30005, '2008-10-08', 10001);
SELECT s_id, f_name, f_priceFROM fruitsWHERE f_price  9.0UNION ALLSELECT s_id, f_name, f_priceFROM fruitsWHERE s_id IN(101,103);
SELECT num1FROM tbl1WHERE num1  ALL (SELECT num2 FROM tbl2);

左连接

查询所有价格小于9的水果的列表,查询s_id等于101和103所有的水果种类,使用UNION ALL连接查询结果,SQL语句如下

exists关键字的子查询

SELECT customers.c_id, orders.o_numFROM customers LEFT OUTER JOIN ordersON customers.c_id = orders.c_id;
SELECT s_id, f_name, f_priceFROM fruitsWHERE f_price  9.0UNION ALLSELECT s_id, f_name, f_priceFROM fruitsWHERE s_id IN(101,103);
SELECT * from fruitsWHERE EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);SELECT * from fruitsWHERE f_price10.20 AND EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);SELECT * from fruitsWHERE NOT EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);

右连接

为orders表取别名o,查询订30001订单的下单日期

in关键字的子查询

SELECT customers.c_id, orders.o_numfrom customers RIGHT OUTER JOIN ordersON customers.c_id = orders.c_id;
SELECT * from orders AS oWHERE o.o_num = 30001;
SELECT c_idFROM ordersWHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');SELECT c_idFROM ordersWHERE o_num NOT IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');

复合条件连接查询

为customers和orders表分别取别名,并进行连接查询

带比较运算符的子查询