新浦京81707con > 注册购买 > MySQL子查询操作实例详解,MySQL单表查询常见操作

原标题:MySQL子查询操作实例详解,MySQL单表查询常见操作

浏览次数:134 时间:2019-07-17

SUM函数

【例.15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值:

SELECT num1
FROM tbl1
WHERE num1 > ALL (SELECT num2 FROM tbl2);
SELECT AVG(f_price) AS avg_price
FROM fruits
WHERE s_id = 103;
SELECT f_price, f_name
FROM fruits
ORDER BY f_price DESC, f_name;
SELECT s_id, f_name FROM fruits
WHERE s_id <>
(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');

本文实例汇报了MySQL使用集结函数举行询问操作。分享给我们供大家参照他事他说加以考察,具体如下:

SELECT f_name, f_price
FROM fruits
WHERE f_price BETWEEN 2.00 AND 10.20;

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

AVG函数

SELECT s_id, GROUP_CONCAT(f_name) AS Names
FROM fruits
GROUP BY s_id;
SELECT * from fruits
WHERE EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);

SELECT * from fruits
WHERE f_price>10.20 AND EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);

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

【例.41】在fruits表中找找市场上等价钱格最高的瓜果,SQL语句如下:

SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE 'b%';

<>所有非

【例.44】在fruits表中寻觅百货店上等价钱格低于的瓜果,SQL语句如下:

SELECT s_id, COUNT(*) AS Total
FROM fruits
GROUP BY s_id WITH ROLLUP;

越来越多关于MySQL相关内容感兴趣的读者可查看本站专项论题:《MySQL常用函数大汇总》、《MySQL日志操作手艺大全》、《MySQL事务操作技能汇总》、《MySQL存款和储蓄进程手艺大全》及《MySQL数据库锁相关本领汇总》

【例.34】查询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(10) NULL,
 c_contact char(50) NULL,
 c_email  char(255) NULL,
 PRIMARY KEY (c_id)
);
INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email)
VALUES(10001, 'RedHook', '200 Street ', 'Tianjin', '300000', 'LiMing', 'LMing@163.com'),
(10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo','Jerry@hotmail.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong', NULL),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', 'sam@hotmail.com');

【例.61】在suppliers表中查询s_city等于Tianjin的供应商s_id,然后在fruits表中查询全部非该供应商提供的瓜果的门类,SQL语句如下:

SELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005;
SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;

【例.31】查询订单价格当先100的订单号和总订单价格

SELECT s_id, f_name FROM fruits
WHERE s_id <>
(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');
SELECT COUNT(c_email) AS email_num
FROM customers;

【例.2】查询当前表中f_name列全体水果名称,输入如下语句:

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);
SELECT MAX(f_price) AS max_price FROM fruits;
SELECT s_id, MAX(f_price) AS max_price FROM fruits GROUP BY s_id;
SELECT MAX(f_name) from fruits;

【例.4】查询价格为10.2元的瓜果的名目,输入如下语句:

exists重大字的子查询

【例.39】在fruits表中,查询s_id=103的供应商的水果价格的平均值,SQL语句如下:

SELECT DISTINCT s_id FROM fruits;
select f_name from fruits ORDER BY f_name;

概念多少个表tb1和tb2

SELECT s_id, MIN(f_price) AS min_price
FROM fruits
GROUP BY s_id;
SELECT * From fruits LIMIT 4, 3;
SELECT c_id
FROM orders
WHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');

SELECT c_id
FROM orders
WHERE o_num NOT IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');
SELECT COUNT(*) AS cust_num from customers;
SELECT COUNT(c_email) AS email_num FROM customers;
SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num;

【例.21】查询fruits表中s_id字段的值,并重临s_id字段值不得另行

你可能感兴趣的文章:

  • 详尽陈述MySQL中的子查询操作
  • Mysql子查询IN中应用LIMIT应用示范
  • MySQL中表子查询与关联子查询的功底学习课程
  • 详解MySql基本查询、连接查询、子查询、正则表达查询
  • MySQL子查询的二种常见款式介绍
  • MySQL的子查询中FROM和EXISTS子句的施用教程
  • mysql in语句子查询功能慢的优化才具示例
  • 浅谈MySQL中的子查询优化本事
  • mysql优化连串DELETE子查询改写优化
  • MySQL优化之使用连接(join)替代子查询
  • MYSQL子查询和嵌套查询优化实例解析

您恐怕感兴趣的稿子:

  • php mysql ajax完结单表多字段多种要词查询的措施
  • 剖析mysql中:单表distinct、多表group by查询去除重复记录
  • MySQL单表多首要字模糊查询的贯彻格局
  • MySQL使用正则表明式举办查询操作特出实例总括
  • mysql中模糊查询的多样用法介绍
  • Mysql联合查询UNION和UNION ALL的利用介绍
  • MySQL查询in操作 查询结果按in集结顺序展现
  • MySQL中挑交州的多表连接查询教程
  • mysql分页原理和高效能的mysql分页查询语句
  • 浅谈MySQL中优化sql语句询问常用的30种办法
  • mysql查询明天 二十八日前 十二月前年前的数据
  • MySQL单表查询周边操作实例计算
SELECT f_name, f_price FROM fruits;
SELECT num1
FROM tbl1
WHERE num1 > ALL (SELECT num2 FROM tbl2);
SELECT COUNT(*) AS cust_num from customers;
SELECT * From fruits LIMIT 4,3;
CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);

【例.43】在fruits表中查找f_name的最大值,SQL语句如下

按价格降序排列,desc为降序,暗中认可为升序。

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

MIN函数

常用查询语句:

【例.57】查询表suppliers表中是还是不是存在s_id=107的供应商,即便不设有则查询fruits表中的记录

COUNT函数

SELECT * FROM fruits;
select f_name,f_price from fruits;
select f_name,f_price from fruits where f_price=10.2;
select f_name,f_price from fruits where f_price<10;
select * from fruits where s_id in (101,102) order by f_name;
select * from fruits where s_id not in (101,102) order by f_name;
select f_name,f_price from fruits where f_price between 2.00 and 10.20;
select f_name,f_price from fruits where f_price not between 2.00 and 10.20;
select f_name,f_price from fruits where f_name like "b%";
select f_name,f_price from fruits where f_name like "%g%";
select f_name,f_price from fruits where f_name like "b%y";
select f_name,f_price from fruits where f_name like "____y";

概念五个表tb1和tb2

SELECT s_id, MAX(f_price) AS max_price
FROM fruits
GROUP BY s_id;
SELECT * FROM fruits;
SELECT f_id, s_id ,f_name, f_price FROM fruits;

企望本文所述对大家MySQL数据库计有所支持。

SELECT MIN(f_price) AS min_price FROM fruits;
SELECT s_id, MIN(f_price) AS min_price FROM fruits GROUP BY s_id;

【例.25】查询fruits表,先按f_price降序排序,再按f_name字段升序排序,SQL语句如下:

【例.54】再次来到tbl1表的中比tbl2表num2 列全数值都大的值

mysql>SELECT MIN(f_price) AS min_price FROM fruits;

group by 子句中使用with rollup

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

更加多关于MySQL相关内容感兴趣的读者可查看本站专项论题:《MySQL常用函数大汇总》、《MySQL日志操作工夫大全》、《MySQL事务操作技艺汇总》、《MySQL存款和储蓄进度技艺大全》及《MySQL数据库锁相关技巧汇总》

盼望本文所述对我们MySQL数据库计有所帮忙。

SELECT * from fruits
WHERE f_price>10.20 AND EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);

【例.45】在fruits表中研究差别供应商提供的价格低于的水果

select f_name,f_price
from fruits
where s_id in (101 ,102);

【例.53】重返tbl2表的享有 num2 列,然后将 tbl1 中的 num1 的值与之实行比较,只要超越 num2的其余值为符合查询条件的结果

SELECT AVG(f_price) AS avg_price FROM fruits WHERE s_id = 103;
SELECT AVG(f_price) AS avg_price FROM fruits group by s_id;
SELECT * From fruits LIMIT 4;

in新萄京娱乐在线官网,重在字的子查询

【例7.42】在fruits表中搜寻不一致供应商提供的价格最高的水果

SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id IN (101,102)
ORDER BY f_name;
CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);

期待本文所述对我们MySQL数据库计有所支持。

SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id IN(101,102);

向几个表中插入数据

SELECT o_num, SUM(quantity) AS items_total
FROM orderitems
GROUP BY o_num;

【例.33】在fruits 表中,使用LIMIT子句,重临从第5个记录开首的,行数长度为3的笔录

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);
mysql>SELECT MAX(f_price) AS max_price FROM fruits;

字段不另行

【例.55】查询表suppliers表中是还是不是存在s_id=107的供应商,借使存在则查询fruits表中的记录

SELECT MAX(f_name) from fruits;

【例.12】在fruits表中,查询f_name中蕴藏字母‘g'的笔录

SELECT num1
FROM tbl1
WHERE num1 > ANY (SELECT num2 FROM tbl2);

【例.35】查询customers表中有电子邮箱的买主的总额,输入如下语句:

【例.27】根据s_id对fruits表中的数据开始展览分组,将各种供应商的鲜果名称展现出来

SELECT s_id, f_name FROM fruits
WHERE s_id =
(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');
SELECT SUM(quantity) AS items_total
FROM orderitems
WHERE o_num = 30005;

【例.6】查询价格小于10的鲜果的名称,输入如下语句:

带比较运算符的子查询

【例.40】在fruits表中,查询每一个供应商的水果价格的平均值,SQL语句如下:

SELECT DISTINCT s_id FROM fruits;
SELECT num1
FROM tbl1
WHERE num1 > ANY (SELECT num2 FROM tbl2);

本文由新浦京81707con发布于注册购买,转载请注明出处:MySQL子查询操作实例详解,MySQL单表查询常见操作

关键词: 新浦京81707con

上一篇:ini配置文件的方法,MySQL配置文件无法修改的解决

下一篇:没有了