-
mysql like子查询语句_浅谈mysql的子查询
2021-01-27 16:25:50通过上面测试验证,普通的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)
缩短到了毫秒;
-
mysql like子查询语句_mysql – 使用’LIKE’和SQL子查询的结果
2021-01-19 05:29:40首先,此查询不应该正常工作: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 like 查询_mysql like查询语句
2021-01-18 20:00:06Mysql常见的子查询语句: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 like反义_PHP+MYSQL 子查询 (一个数据表查询结果供另一个 SQL查询调用)
2021-02-01 17:08:58什么是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'
-
mysql like 结果集_MySQL数据库WHERE查询in,like子查询详解
2021-01-27 16:25:14我们已经看到SQL SELECT命令来从MySQL表获取数据。我们可以使用一个条件子句WHERE子句中筛选出来的结果。使用WHERE子句,我们可以指定一个选择的标准来从表中选择所需的记录。语法:下面是通用的SQL WHERE子句来获取... -
mysql like子查询语句_MySQL-SQL语句
2021-02-01 17:09:0510、MySQL子查询 概念:在select 的where条件中又出现了select,查询中嵌套着查询 mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='网络技术'); 11、limit... -
mysql带有子查询的like查询
2019-09-29 22:37:23SELECT * 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-... -
mysql 子查询 as table_MySQL基础三种类型的子查询
2021-01-19 10:54:514、复制表子查询(1)复制表(1)拷贝表结构CREATE TABLE newadmin LIKE admin;(2)拷贝表结构和数据(但约束与索引除外)CREATE TABLE newadmin AS ( SELECT * FROM admin ) ;(3)拷贝表结构+数据CREATE TABLE newadmin ... -
mysql like 排序机制_【mysql】 mysql 子查询、联合查询、模糊查询、排序、聚合函数、分组----------语法.....
2021-01-27 16:24:50第二章 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... -
mysql 子查询、模糊匹配
2020-05-25 18:06:21、模糊匹配,like的...1、子查询 SELECT * FROM human_base_info WHERE id_card IN ( SELECT id_card FROM human_active_record WHERE feature LIKE '%淡黄色的长裙%蓬松的头发%' ) 总结: -
mysql 综合查询_mysql 综合查询练习(关系查询、子查询)
2021-01-19 18:25:54建表语句见文末empsalarygrade1、找到名字为两位的员工信息-- 方法一select * from empwhere length(ename) = 6;... -- 两根下划线2、查询工资多于宋江的员工信息 (子查询)select * from empwhere salary &... -
mysql 查询语句意思_MYSQL like查询语句什么意思?
2021-01-19 04:48:5332313133353236313431303231363533e58685e5aeb931333365633961Mysql常见的子查询语句:1、使用in的子查询:形式:where 字段 in含义:这里,列子查询可能是“多个值”,虽然查询的输出结果是“一列”的,但我们要... -
mysql 子查询索引失效_mysql 索引失效的情况
2021-01-19 13:16:19对于多列索引,不是使用的第一部分,则不会使用索引(靠左原则)3.like查询以%开头的列索引会失效4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引5.如果mysql估计使用全表扫描要比使用... -
mysql 替代like_mysql用instr替代like查询
2021-01-19 00:01:55使用内部函数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 ... -
WordPress / MySQL子查询问题
2014-10-16 09:11:10WHERE 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, ... -
MySql基本查询、连接查询、子查询、正则表达查询讲解
2017-08-24 18:38:305、子查询 6、合并查询结果 7、为表和字段取别名 8、使用正则表达式查询 什么是查询? 怎么查的? 数据的准备如下: [sql] view plain copy create table STUDENT( STU_ID int primary KEY, STU_NAME char(10) ... -
(5)MySQL的查询:模糊查询(通配符查询like)、限制符查询(limit)、排序查询(order by)、分组查询...
2021-01-19 07:52:17并且分组,且计算相同的数量,查找相同数量的数据 子查询(sql语句套sql语句)#不建议用,使用子查询后无法用索引,而且效率比连表查询还慢 select * from tablename1 where 列名 in (select 列名 from tablename2); -
按查询/ Mysql子查询的结果排序
2013-07-12 12:00:59<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 ... -
mysql带有子查询的like查询_浮梦丶天拓_新浪博客
2016-08-18 17:07:29SELECT * FROM by_app_categories WHERE c_name LIKE CONCAT('%', (SELECT `name` FROM b_catelist WHERE type="wangluo"), '%') -
mysql-子查询
2020-11-08 16:58:15UPDATE 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... -
mysql子查询中带有模糊查询以及concat函数
2019-03-15 11:16:31SELECT c1.id, NAME, ( SELECT count(*) FROM collection WHERE location LIKE CONCAT('%',c1.id , '/') ) AS hasSon FROM collectio... -
最大员工小时的MySQL子查询
2013-04-01 21:25:16<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) =...