精华内容
下载资源
问答
  • 通过上面测试验证,普通的mysql子查询写法性能上是很差的,为mysql的子查询天然的弱点,需要将sql进行改写为关联的写法: select tradedto0_.* from a1 tradedto0_ ,(select orderdto1_.tradeoid from a2 orderdto1_...

    再进行执行计划分析:

    db@3306 :explain select tradedto0_.* from a1 tradedto0_ where tradedto0_.tradestatus='1' and (tradedto0_.tradeoid in (select orderdto1_.tradeoid from a2 orderdto1_ where orderdto1_.proname like '%??%' or orderdto1_.procode like '%??%')) and tradedto0_.undefine4='1' and tradedto0_.invoicetype='1' and tradedto0_.tradestep='0' and (tradedto0_.orderCompany like '0002%') order by tradedto0_.tradesign ASC, tradedto0_.makertime desc limit 15;

    +----+--------------------+------------+-------+-----------------+----------------------+---------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+--------------------+------------+-------+-----------------+----------------------+---------+

    | 1 | PRIMARY | tradedto0_ | ref | ind_tradestatus | ind_tradestatus | 345 | const,const,const,const | 8962 | Using where; Using filesort |

    | 2 | DEPENDENT SUBQUERY | orderdto1_ | index | NULL | ind_a2 | 777 | NULL | 41005 | Using where; Using index |

    +----+--------------------+------------+-------+-----------------+----------------------+---------+

    发现性能还是上不去,关键在两个表扫描的行数并没有减小(8962*41005),上面添加的索引没有太大的效果,现在查看t表的执行结果:

    db@3306 :select orderdto1_.tradeoid from t orderdto1_ where orderdto1_.proname like '%??%' or orderdto1_.procode like '%??%';

    Empty set (0.05 sec)

    结果集为空,所以需要将t表的结果集做作为驱动表;

    4.通过上面测试验证,普通的mysql子查询写法性能上是很差的,为mysql的子查询天然的弱点,需要将sql进行改写为关联的写法:

    select tradedto0_.* from a1 tradedto0_ ,(select orderdto1_.tradeoid from a2 orderdto1_ where orderdto1_.proname like '%??%' or orderdto1_.procode like '%??%')t2 where tradedto0_.tradestatus='1' and (tradedto0_.tradeoid=t2.tradeoid ) and tradedto0_.undefine4='1' and tradedto0_.invoicetype='1' and tradedto0_.tradestep='0' and (tradedto0_.orderCompany like '0002%') order by tradedto0_.tradesign ASC, tradedto0_.makertime desc limit 15;

    5.查看执行计划:

    db@3306 :explain select tradedto0_.* from a1 tradedto0_ ,(select orderdto1_.tradeoid from a2 orderdto1_ where orderdto1_.proname like '%??%' or orderdto1_.procode like '%??%')t2 where tradedto0_.tradestatus='1' and (tradedto0_.tradeoid=t2.tradeoid ) and tradedto0_.undefine4='1' and tradedto0_.invoicetype='1' and tradedto0_.tradestep='0' and (tradedto0_.orderCompany like '0002%') order by tradedto0_.tradesign ASC, tradedto0_.makertime desc limit 15;

    +----+-------------+------------+-------+---------------+----------------------+---------+------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+------------+-------+---------------+----------------------+---------+------+

    | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |

    | 2 | DERIVED | orderdto1_ | index | NULL | ind_a2 | 777 | NULL | 41005 | Using where; Using index |

    +----+-------------+------------+-------+---------------+----------------------+---------+------+

    6.执行时间:

    db@3306 :select tradedto0_.* from a1 tradedto0_ ,(select orderdto1_.tradeoid from a2 orderdto1_ where orderdto1_.proname like '%??%' or orderdto1_.procode like '%??%')t2 where tradedto0_.tradestatus='1' and (tradedto0_.tradeoid=t2.tradeoid ) and tradedto0_.undefine4='1' and tradedto0_.invoicetype='1' and tradedto0_.tradestep='0' and (tradedto0_.orderCompany like '0002%') order by tradedto0_.tradesign ASC, tradedto0_.makertime desc limit 15;

    Empty set (0.03 sec)

    缩短到了毫秒;

    展开全文
  • 首先,此查询不应该正常工作:SELECT *From CustomersWHERE Customers.ContactName = (SELECT FirstNamefrom Employees as E, orders as OWHERE LIMIT 1);因为WHERE LIMIT 1不是正确的SQL.而且,您应该学会使用正确的...

    首先,此查询不应该正常工作:

    SELECT *

    From Customers

    WHERE Customers.ContactName = (SELECT FirstName

    from Employees as E, orders as O

    WHERE LIMIT 1);

    因为WHERE LIMIT 1不是正确的SQL.而且,您应该学会使用正确的连接语法.据推测,你打算:

    SELECT c.*

    From Customers c

    WHERE c.ContactName = (SELECT FirstName

    FROM Employees as E JOIN

    Orders as O

    ON . . .

    LIMIT 1

    );

    你可以想象在子查询中添加LIKE而不是=和’%’:

    WHERE c.ContactName LIKE (SELECT CONCAT('%', FirstName, '%') . . .

    但我会用EXISTS写这个:

    SELECT c.*

    From Customers c

    WHERE EXISTS (SELECT 1

    FROM Employees as E JOIN

    Orders as O

    ON . . .

    WHERE c.ContactName LIKE CONCAT('%', FirstName, '%')

    );

    这与查询完全不同.它做了更合理的事情.它不是比较子查询中的一个随机名称,而是确定子查询中是否存在任何匹配.这似乎是查询更合理的意图.

    展开全文
  • Mysql常见的子查询语句:1、使用in的子查询:32313133353236313431303231363533e59b9ee7ad9431333365643662形式:where 字段 in含义:这里,列子查询可能是“多个值”,虽然查询的输出结果是“一列”的,但我们要...

    展开全部

    搜索以字母 1a 开头的所有字符串。

    Mysql常见的子查询语句:

    1、使用in的子查询:32313133353236313431303231363533e59b9ee7ad9431333365643662

    形式:where  字段  in

    含义:这里,列子查询可能是“多个值”,虽然查询的输出结果是“一列”的,但我们要理解是“多个值的列表”,其相当于:where  字段  in (值1,值2,....);比如: where  age  in (18, 28, 38); 表示age为其中任意一个就可以。

    2、使用any的子查询:

    形式:where  字段  比较运算符  any

    含义:表示,该字段的值,对该列子查询所查询出来的多个值,有一个满足该比较运算符,就算满足了。

    3、使用some的子查询:

    形式:where  字段  比较运算符  some  (列子查询)

    含义:同any。即some是any的同义词。

    4、使用all的子查询:

    形式:where  字段  比较运算符  all  (列子查询)

    含义:表示,该字段的值,要对该列子查询的所有结果数据,全都满足该运算符,才算符合条件。

    5、使用exists的子查询:

    形式:where  exists ( 任何子查询 )

    含义:该子查询如果“有数据结果”, 则该exists()的结果为“true”。该子查询如果“没有数据结果”,则该exists()的结果为“false”。

    展开全文
  • 什么是MYSQL 子查询MYSQL 子查询是将一个 SELECT 语句的查询结果作为中间结果,供另一个 SQL 语句调用。查询语句如下:$sql = "SELECT * FROM e_user WHERE id IN (SELECT uid FROM subject WHERE fun>80)";等同...

    什么是MYSQL 子查询

    MYSQL 子查询是将一个 SELECT 语句的查询结果作为中间结果,供另一个 SQL 语句调用。

    查询语句如下:

    $sql = "SELECT * FROM e_user WHERE id IN (SELECT uid FROM subject WHERE fun>80)";

    等同于

    SELECT * FROM e_user WHERE id IN(1,2)

    标量子查询

    $sql = "SELECT * FROM e_user WHERE id=(SELECT min(uid) FROM subject)";

    查询之后插入到另一个表

    INSERT INTO other(id,sid,name,age,subject,total) SELECT null,s.id, s.name,s.age,j.subject,j.total from SUBJECT as j LEFT JOIN student as s on s.id=j.sid

    除了子查询之外,还有内连接查询。

    INNER JOIN(内连接):

    取得两个表中存在连接匹配关系的记录

    $sql="SELECT * FROM subject as a INNER JOIN e_user as b ON a.uid=b.id";

    //也可以用以下方法

    SELECT article.aid,article.title,user.username FROM article,user WHERE article.uid = user.uid

    LEFT JOIN (左外连):会取得左表(table1)全部记录,即使右表(table2)并无对应匹配记录

    $sql="SELECT * FROM subject as a LEFT JOIN e_user as b ON a.uid=b.id";

    在上面的例子中,对于右表中没有对应匹配的数据记录,其所有的列都被置为 NULL,因此要查询这部分记录(如在上面例子中体现为查找 id=4 这类无对应用户的记录),可以附加 IS NULL 条件:

    $sql="SELECT * FROM subject as a LEFT JOIN e_user as b ON a.uid=b.id WHERE b.id IS NULL";

    RIGHT JOIN(右外连): 会取得右表(table2)全部记录,即使左表(table2)并无对应匹配记录

    $sql="SELECT * FROM subject as a RIGHT JOIN e_user as b ON a.uid=b.id";

    除了常用的两个表连接之外,SQL(MySQL) JOIN 语法还支持多表连接

    SELECT article.aid,article.title,user.username,type.typename FROM article INNER JOIN user

    ON article.uid=user.uid INNER JOIN type ON article.tid=type.tid

    //LEFT JOIN

    SELECT article.aid,article.title,user.username,type.typename FROM article LEFT JOIN user

    ON article.uid=user.uid LEFT JOIN type ON article.tid=type.tid

    //RIGHT JOIN

    SELECT article.aid,article.title,user.username,type.typename FROM article RIGHT JOIN user

    ON article.uid=user.uid RIGHT JOIN type ON article.tid=type.tid

    WHERE 表达式中运算符说明:

    参数说明:

    运算符

    说明

    =

    等于

    !=

    不等于,某些数据库系统也写作 <>

    >

    大于

    <

    小于

    >=

    大于或等于

    <=

    小于或等于

    BETWEEN … AND …

    介于某个范围之内,例:WHERE age BETWEEN 20 AND 30

    NOT BETWEEN …AND …

    不在某个范围之内

    IN(项1,项2,…)

    在指定项内,例:WHERE city IN('beijing','shanghai')

    NOT IN(项1,项2,…)

    不在指定项内

    LIKE

    搜索匹配,常与模式匹配符配合使用

    NOT LIKE

    LIKE的反义

    IS NULL

    空值判断符

    IS NOT NULL

    非空判断符

    NOT、AND、OR

    逻辑运算符,分别表示否、并且、或,用于多个逻辑连接。

    优先级:NOT > AND > OR

    %

    模式匹配符,表示任意字串,例:WHERE username LIKE '%user'

    展开全文
  • 我们已经看到SQL SELECT命令来从MySQL表获取数据。我们可以使用一个条件子句WHERE子句中筛选出来的结果。使用WHERE子句,我们可以指定一个选择的标准来从表中选择所需的记录。语法:下面是通用的SQL WHERE子句来获取...
  • 10、MySQL子查询 概念:在select 的where条件中又出现了select,查询中嵌套着查询 mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='网络技术'); 11、limit...
  • SELECT * FROM by_app_categories WHERE c_name LIKE CONCAT('%', (SELECT `name` FROM b_catelist WHERE type="wangluo"), '%') ...
  • Mysql子查询

    2013-05-17 15:34:51
    <p>t1_id in (select table1_id from table2 t2 where 1=1 and (t2.t2_name like '%wangwu%' or t1.t1_name like '%zhansan%') );   <p>table1    <p>+---------------------+ | ;">t1_id</span> | ; line-...
  • 4、复制表子查询(1)复制表(1)拷贝表结构CREATE TABLE newadmin LIKE admin;(2)拷贝表结构和数据(但约束与索引除外)CREATE TABLE newadmin AS ( SELECT * FROM admin ) ;(3)拷贝表结构+数据CREATE TABLE newadmin ...
  • 第二章 mysql一、模糊查询like1. 字段 like '河北省%' %代表任何N个字符2 字段 like '河北省____' _代表任意1个字符二、IN语法:SELECT 字段列1,字段2 ,…FROM 表名 WHERE 字段x IN ( 值1,值2,值3…)三、排序语法:...
  • mysql子查询锁表

    2019-10-19 23:29:27
    数据库的隔离级别 (1) read uncommitted 存在脏读,不可重复读,幻读 (2) read commited 存在不可重复读,幻读 (3) repeatable read ...mysql的隔离级别是repeatable read。...show global variables like '%i...
  • 、模糊匹配,like的...1、子查询 SELECT * FROM human_base_info WHERE id_card IN ( SELECT id_card FROM human_active_record WHERE feature LIKE '%淡黄色的长裙%蓬松的头发%' ) 总结:
  • 建表语句见文末empsalarygrade1、找到名字为两位的员工信息-- 方法一select * from empwhere length(ename) = 6;... -- 两根下划线2、查询工资多于宋江的员工信息 (子查询)select * from empwhere salary &...
  • 32313133353236313431303231363533e58685e5aeb931333365633961Mysql常见的子查询语句:1、使用in的子查询:形式:where 字段 in含义:这里,列子查询可能是“多个值”,虽然查询的输出结果是“一列”的,但我们要...
  • 对于多列索引,不是使用的第一部分,则不会使用索引(靠左原则)3.like查询以%开头的列索引会失效4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引5.如果mysql估计使用全表扫描要比使用...
  • 使用内部函数INSTR,代替传统的LIKE方式查询,不仅速度更快,而且省去了通配符检查,因此更加安全。INSTR()函数返回字符串中子字符串第一次出现的位置。如果在str中找不到字符串,则INSTR()函数返回零(0)。下面...
  • PHP mysql子查询过滤

    2012-04-11 15:25:01
    <p>I have a table like this: <pre><code>id price condition username ---------------------------------------- 1 20 New Kim 2 30 Used Kim 3 40 Used George 4 60 New Tom </code></pre> <p>My ...
  • WHERE post_type LIKE 'shop_order' AND ID = ANY (SELECT post_id FROM wp_postmeta WHERE meta_value BETWEEN DATE_SUB( NOW( ) , INTERVAL 30 DAY AND NOW( ) ); LIMIT 0 , 30 </code></pre> <p>However, ...
  • 5、子查询 6、合并查询结果 7、为表和字段取别名 8、使用正则表达式查询 什么是查询? 怎么查的? 数据的准备如下: [sql] view plain copy create table STUDENT( STU_ID int primary KEY, STU_NAME char(10) ...
  • 并且分组,且计算相同的数量,查找相同数量的数据 子查询(sql语句套sql语句)#不建议用,使用子查询后无法用索引,而且效率比连表查询还慢 select * from tablename1 where 列名 in (select 列名 from tablename2);
  • <p>Once this was done, I wanted to order those 10 selected rows by show_moyenne.moyenne with something like that: <pre><code>SELECT * (FROM show_episode, shows, show_episode_airdate, show_moyenne ...
  • SELECT * FROM by_app_categories WHERE c_name LIKE CONCAT('%', (SELECT `name` FROM b_catelist WHERE type="wangluo"), '%')
  • mysql-子查询

    2020-11-08 16:58:15
    UPDATE cms_user SET age=age-3 WHERE username LIKE ‘____’; – 更新前3条记录,让已有年龄+10 UPDATE cms_user SET age=age+10 LIMIT 3; UPDATE cms_user SET age=age+10 LIMIT 0,3; – 按照id降序排列,更新前3...
  • SELECT c1.id, NAME, ( SELECT count(*) FROM collection WHERE location LIKE CONCAT('%',c1.id , '/') ) AS hasSon FROM collectio...
  • <p>I'm thinking I need a Subquery but I'm not very good with MySQL, and I cannot wrap my head around the query I need. I want to select all rows from this month with: <pre><code>"... MONTH(time_on) =...

空空如也

空空如也

1 2 3 4 5 ... 15
收藏数 289
精华内容 115
关键字:

likemysql子查询

mysql 订阅