澳门金沙vip 3

【澳门金沙vip】mysql连接查询

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

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

内连接查询

定义两个表tb1和tb2

union关键字

创建suppliers

CREATE table tbl1 ( num1 INT NOT NULL);CREATE table tbl2 ( num2 INT NOT NULL);
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);
-- 创建suppliers
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)
);

向两个表中插入数据:

为表取别名

创建fruits

INSERT INTO tbl1 values(1), (5), (13), (27);INSERT INTO tbl2 values(6), (14), (11), (20);
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;
-- 创建fruits
CREATE TABLE fruits
(
f_id CHAR(10) not null,
s_id int not null,
f_name CHAR(255) not NULL,
f_price DECIMAL(8,2) not NULL,
PRIMARY KEY(f_id)
);

any some关键字的子查询

为字段取别名

两张表都有s_id字段

SELECT num1FROM tbl1WHERE num1  ANY (SELECT num2 FROM tbl2);
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;
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','20000','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');

INSERT INTO fruits
(f_id,s_id,f_name,f_price)
VALUES
('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'cocount',9.2),
('co',101,'cherry',3.2),
('a2',103,'apricot',2.2),
('l2',104,'lemon',6.4),
('b2',104,'berry',7.6),
('m1',106,'mango',16.5),
('m2',105,'xbabay',2.6),
('t4',107,'xbababa',3.6),
('m3',105,'xxtt',11.6),
('b5',107,'xxxx',3.6)

SELECT f_id,suppliers.s_id,fruits.s_id,s_name,f_name,f_price from fruits,suppliers where fruits.s_id=suppliers.s_id

all关键字的子查询

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

澳门金沙vip 1

SELECT num1FROM tbl1WHERE num1  ALL (SELECT num2 FROM tbl2);
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);

使用内连接

exists关键字的子查询

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

SELECT f_id,suppliers.s_id,fruits.s_id,s_name,f_name,f_price from fruits
INNER JOIN suppliers on fruits.s_id=suppliers.s_id
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);
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);

或者,逗号分隔

in关键字的子查询

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

SELECT f_id,suppliers.s_id,fruits.s_id,s_name,f_name,f_price 
from 
    fruits,suppliers 
where fruits.s_id=suppliers.s_id
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');
SELECT * from orders AS oWHERE o.o_num = 30001;

 

带比较运算符的子查询

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

最终结果和上面的查询结果一模一样.

SELECT s_id, f_name FROM fruitsWHERE s_id =(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');
SELECT c.c_id, o.o_numFROM customers AS c LEFT OUTER JOIN orders AS oON c.c_id = o.c_id;

使用where子句定义连接条件比较简单明了,而inner join 语法是ansi
sql的标准规范,使用inner join 连接语法能够确保

所有非

查询fruits表,为f_name取别名fruit_name,f_price取别名fruit_price,为fruits表取别名f1,查询表中f_price
8的水果的名称

不会忘记连接条件,而且,where子句在某些时候会影响查询的性能.

SELECT s_id, f_name FROM fruitsWHERE s_id (SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');
SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_priceFROM fruits AS f1WHERE f1.f_price  8;

自连接查询

定义两个表tb1和tb2

查询suppliers表中字段s_name和s_city,使用CONCAT函数连接这个两个字段值,并取列别名为suppliers_title

如果在一个连接查询中,涉及到的两个表都是同一个表,这种查询称为自连接查询.自连接是一种特殊的内连接,

CREATE table tbl1 ( num1 INT NOT NULL);CREATE table tbl2 ( num2 INT NOT NULL);

如果没有对连接后的值取别名,其显示列名称将会不够直观,输入如下SQL

它是之相互连接的表在物理上为同一张表,但可以在逻辑上分为两张表.

向两个表中插入数据

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

查看f_id=’a1’的水果供应商提供其他水果种类

INSERT INTO tbl1 values(1), (5), (13), (27);INSERT INTO tbl2 values(6), (14), (11), (20);

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

SELECT f1.f_id,f1.f_name,f1.s_id,f2.s_id,f2.f_id,f2.f_name from fruits as f1,fruits as f2
where f1.s_id=f2.s_id and f2.f_id='a1';

返回tbl2表的所有 num2 列,然后将 tbl1 中的 num1
的值与之进行比较,只要大于 num2的任何值为符合查询条件的结果

希望本文所述对大家MySQL数据库计有所帮助。

澳门金沙vip 2

SELECT num1FROM tbl1WHERE num1  ANY (SELECT num2 FROM tbl2);

上面的意思可以用这个sql语句表示

返回tbl1表的中比tbl2表num2 列所有值都大的值

SELECT * from fruits where s_id=101
SELECT num1FROM tbl1WHERE num1  ALL (SELECT num2 FROM tbl2);

澳门金沙vip 3

查询表suppliers表中是否存在s_id=107的供应商,如果存在则查询fruits表中的记录

外连接查询

SELECT * from fruitsWHERE EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);

外连接查询分为左外连接和又外连接

查询表suppliers表中是否存在s_id=107的供应商,如果存在则查询fruits表中的f_price大于10.20的记录

left join(左连接):返回包括左表中的所有记录和右表中连接字段相等的记录

SELECT * from fruitsWHERE f_price10.20 AND EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);

right join(右连接):返回包括右表中的所有记录和右表中连接字段相等的记录.

查询表suppliers表中是否存在s_id=107的供应商,如果不存在则查询fruits表中的记录

-- 创建表customers
CREATE TABLE customers
(
c_id int not null auto_increment,
c_name CHAR(50) not NULL,
c_address CHAR(50) NULL,
c_city CHAR(50) NULL,
c_zip CHAR(50) NULL,
c_contact CHAR(50) NULL,
c_email CHAR(255) NULL,
PRIMARY KEY(c_id)
);
SELECT * from fruitsWHERE NOT EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);

插入数据: