如何编写可重用的MySQL查询

时间：2022年12月11日

来源：hwei123

编辑：本站小编

以下是小编给大家收集的如何编写可重用的MySQL查询，本文共7篇，欢迎大家前来参阅。本文原稿由网友“hwei123”提供。

篇1：如何编写可重用的MySQL查询

当人们提及可重用的查询的时候，立即映入脑海的往往就是存储过程了，虽然这些存储过程是编写可重用代码不可分割的一部分，但要记住的是，它们只是很少的一部分而已，而非全部。此外，其它可重用代码包括视图、内置函数以及用户定义的函数。在本文中，我们将向读者详细介绍如何组合这些元素，以令我们的选择语句可以更好的适用于各种查询。

一、关于视图

视图的用途很多，例如简化复杂的模式及查询，或者提供安全性等等。视图提供安全性的一种途径是对开发者隐藏审计字段。视图还可通过减少列的数目来提高性能。这个想法是只引用索引字段，而索引字段的搜索速度是非常之快的。实际上，这种想法实现起来很费劲，因为你必须确保不会访问隐藏列。然而，我们这里主要是利用视图模拟两个或更多个表之间的连接，以降低查询的复杂性。很多时候，要想将数据库中用户的概要信息整理成符合第三范式的形式，可能需要多达六次连接操作，例如：

select*

fromUsers u

innerjoinUserPhoneNumbers upnonu.user_id=upn.user_id

innerjoinUserScreenNames usnonu.user_id=usn.user_id

innerjoinUserAffiliations uaonu.user_id=ua.user_id

innerjoinAffiliations aona.affiliation_id=ua.affiliation_id

innerjoinUserWorkHistory uwhonu.user_id=uwh.user_id

innerjoinAffiliations

篇2：MySQL按年龄段查询

COUNT(tr.id)AS '体检总人数',

SUM(CASE WHEN s.sex=1 THEN 1 ELSE 0 END) AS '男体检总数',

SUM(CASE WHEN s.sex=0 THEN 1 ELSE 0 END) AS '女体检总数',

SUM(CASE WHEN s.sex=1 AND tr.age >=18 AND tr.age <=29 THEN 1 ELSE 0 END) AS '男18--29岁',

SUM(CASE WHEN s.sex=0 AND tr.age >=18 AND tr.age<=29 THEN 1 ELSE 0 END) AS '女18--29岁',

SUM(CASE WHEN s.sex=1 AND tr.age <=45 AND tr.age>=30 THEN 1 ELSE 0 END) AS '男30--39岁',

SUM(CASE WHEN s.sex=0 AND tr.age<=45 AND tr.age>=30 THEN 1 ELSE 0 END) AS '女30--39岁',

SUM(CASE WHEN s.sex=1 AND tr.age <=50 AND tr.age>=46 THEN 1 ELSE 0 END) AS '男40--49岁',

SUM(CASE WHEN s.sex=0 AND tr.age<=50 AND tr.age>=46 THEN 1 ELSE 0 END) AS '女40--49岁',

SUM(CASE WHEN s.sex=1 AND tr.age <=60 AND tr.age>50 THEN 1 ELSE 0 END) AS '男50--59岁',

SUM(CASE WHEN s.sex=0 AND tr.age<=60 AND tr.age>50 THEN 1 ELSE 0 END) AS '女50--59岁',

SUM(CASE WHEN s.sex=1 AND tr.age <=70 AND tr.age>60 THEN 1 ELSE 0 END) AS '男60--69岁',

SUM(CASE WHEN s.sex=0 AND tr.age<=70 AND tr.age>60 THEN 1 ELSE 0 END) AS '女60--69岁',

SUM(CASE WHEN s.sex=1 AND tr.age <=80 AND tr.age>70 THEN 1 ELSE 0 END) AS '男70--79岁',

SUM(CASE WHEN s.sex=0 AND tr.age<=80 AND tr.age>70 THEN 1 ELSE 0 END) AS '女70--79岁',

SUM(CASE WHEN s.sex=1 AND tr.age >80 THEN 1 ELSE 0 END) AS '男80岁以上',

SUM(CASE WHEN s.sex=0 AND tr.age>80 THEN 1 ELSE 0 END) AS '女80岁以上'

我拿其中一句解释一下

SUM(CASE WHEN s.sex=1 AND tr.age >=18 AND tr.age <=29 THEN 1 ELSE 0 END) AS '男18--29岁',

先看sum的括号里面的部分

case when sex = 1 and age>=18 and age <=29 then 1 else 0 end

它表示的含义是：如果性别为1（也就是男），并且年龄在18-29岁之间成立为1，不成立为0.

case和end 是一个关键字你可以理解为语句的开始和结束，

MySQL按年龄段查询

，

when相当于if做判断，then就是判断之后显示的结果。如果成立显示为1，不成立显示为0

sum就是将各个值相加。形如：1+1+0+1+0+1+1+0+1+1

项目最后统计的结果截图形如：

篇3：MySQL查询优化explain

在分析查询性能时，考虑EXPLAIN关键字同样很管用，EXPLAIN关键字一般放在SELECT查询语句的前面，用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让查询优化器能够更好的工作。

一、MySQL 查询优化器是如何工作的

MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。最终目标是提交 SELECT 语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快,那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试,查询就可以执行地更快。

EXPLAIN 的每个输出行提供一个表的相关信息,并且每个行包括下面的列:

项说明idMySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。select_type 查询类型说明SIMPLE简单的 select 查询,不使用 union 及子查询PRIMARY最外层的 select 查询UNIONUNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集DEPENDENT UNIONUNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集SUBQUERY子查询中的第一个 select 查询,不依赖于外部查询的结果集DEPENDENT SUBQUERY子查询中的第一个 select 查询,依赖于外部查询的结果集DERIVED用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询, 把结果放在临时表里。UNCACHEABLE SUBQUERY结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估。UNCACHEABLE UNIONUNION 中的第二个或随后的 select 查询,属于不可缓存的子查询项说明table输出行所引用的表type 重要的项,显示连接使用的类型,按最优到最差的类型排序说明system表仅有一行(=系统表)。这是 const 连接类型的一个特例。constconst 用于用常数值比较 PRIMARY KEY 时。当查询的表仅有一行时,使用 System。eq_refconst 用于用常数值比较 PRIMARY KEY 时。当查询的表仅有一行时,使用 System。ref连接不能基于关键字选择单个行,可能查找到多个符合条件的行。叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一个常数,或者是来自一个表里的多表查询的结果值ref_or_null如同 ref, 但是 MySQL 必须在初次查找的结果里找出 null 条目,然后进行二次查找。index_merge说明索引合并优化被使用了。unique_subquery在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)index_subquery在某些 IN 查询中使用此种类型 , 与 unique_subquery 类似,但是查询的是非唯一性索引: value IN (SELECT key_column FROM single_table WHERE some_expr)range只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引。当使用=、、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range。index全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。all最坏的情况,从头到尾全表扫描。项说明possible_keys指出 MySQL 能在该表中使用哪些索引有助于查询。如果为空,说明没有可用的索引。项说明keyMySQL 实际从 possible_key 选择使用的索引。如果为 NULL,则没有使用索引。很少的情况下,MYSQL 会选择优化不足的索引。这种情况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引项说明key_len使用的索引的长度。在不损失精确性的情况下,长度越短越好。项说明ref显示索引的哪一列被使用了项说明rowsMYSQL 认为必须检查的用来返回请求数据的行数项说明rowsMYSQL 认为必须检查的用来返回请求数据的行数

extra 中出现以下 2 项意味着 MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

extra 项说明Using filesort表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序”Using temporary表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

下面来举一个例子来说明下 explain 的用法。

先来一张表:

>CREATE TABLE IF NOT EXISTS `article` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`author_id` int(10) unsigned NOT NULL,`category_id` int(10) unsigned NOT NULL,`views` int(10) unsigned NOT NULL,`comments` int(10) unsigned NOT NULL,`title` varbinary(255) NOT NULL,`content` text NOT NULL,PRIMARY KEY (`id`));

再插几条数据:

>INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(1, 1, 1, 1, '1', '1'),(2, 2, 2, 2, '2', '2'),(1, 1, 3, 3, '3', '3');

需求:

查询 category_id 为 1 且 comments 大于 1 的情况下,views 最多的 article_id。

先查查试试看:

>EXPLAINSELECT author_idFROM `article`WHERE category_id = 1 AND comments > 1ORDER BY views DESCLIMIT 1\\G

看看部分输出结果:

>*************************** 1. row *************************** id: 1 select_type: SIMPLE table: article type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: Using where; Using filesort1 row in set (0.00 sec)

很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

嗯,那么最简单的解决方案就是加索引了。好,我们来试一试。查询的条件里即 where 之后共使用了 category_id,comments,views 三个字段。那么来一个联合索引是最简单的了。

>ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` );

结果有了一定好转,但仍然很糟糕:

>*************************** 1. row *************************** id: 1 select_type: SIMPLE table: article type: rangepossible_keys: x key: x key_len: 8 ref: NULL rows: 1 Extra: Using where; Using filesort1 row in set (0.00 sec)

type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。但是我们已经建立了索引,为啥没用呢?这是因为按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时,因comments >1 条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。

那么我们需要抛弃 comments,删除旧索引:

> DROP INDEX x ON article;

然后建立新索引:

>ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;

接着再运行查询:

>*************************** 1. row *************************** id: 1 select_type: SIMPLE table: article type: refpossible_keys: y key: y key_len: 4 ref: const rows: 1 Extra: Using where1 row in set (0.00 sec)

可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。

再来看一个多表查询的例子。

首先定义 3个表 class 和 room。

>CREATE TABLE IF NOT EXISTS `class` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`card` int(10) unsigned NOT NULL,PRIMARY KEY (`id`));CREATE TABLE IF NOT EXISTS `book` (`bookid` int(10) unsigned NOT NULL AUTO_INCREMENT,`card` int(10) unsigned NOT NULL,PRIMARY KEY (`bookid`));CREATE TABLE IF NOT EXISTS `phone` (`phoneid` int(10) unsigned NOT NULL AUTO_INCREMENT,`card` int(10) unsigned NOT NULL,PRIMARY KEY (`phoneid`)) engine = innodb;

然后再分别插入大量数据。插入数据的php脚本：

<?php$link = mysql_connect(“localhost”,“root”,“870516”);mysql_select_db(“test”,$link);for($i=0;$i<10000;$i++){ $j = rand(1,20); $sql = “ insert into class(card) values({$j})”; mysql_query($sql);}for($i=0;$i<10000;$i++){ $j = rand(1,20); $sql = “ insert into book(card) values({$j})”; mysql_query($sql);}for($i=0;$i<10000;$i++){ $j = rand(1,20); $sql = “ insert into phone(card) values({$j})”; mysql_query($sql);}mysql_query(“COMMIT”);?>

然后来看一个左连接查询:

explain select * from class left join book on class.card = book.card\\G

分析结果是:

显然第二个 ALL 是需要我们进行优化的。

建立个索引试试看:

>ALTER TABLE `book` ADD INDEX y ( `card`);

可以看到第二行的 type 变为了 ref,rows 也变成了 1741*18,优化比较明显。这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。

删除旧索引:

>DROP INDEX y ON book;

建立新索引。

>ALTER TABLE `class` ADD INDEX x ( `card`);

结果

基本无变化，

然后来看一个右连接查询:

>explain select * from class right join book on class.card = book.card;

分析结果是:

>*************************** 1. row *************************** id: 1 select_type: SIMPLE table: book type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 20000 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: class type: refpossible_keys: x key: x key_len: 4 ref: test.book.card rows: 1000 Extra: 2 rows in set (0.00 sec)

优化较明显。这是因为 RIGHT JOIN 条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引。

删除旧索引:

>DROP INDEX x ON class;

建立新索引。

>ALTER TABLE `book` ADD INDEX y ( `card`);

结果

基本无变化。最后来看看 inner join 的情况:

>explain select * from class inner join book on class.card = book.card;

结果:

删除旧索引:

>DROP INDEX y ON book;

结果

建立新索引。

>ALTER TABLE `class` ADD INDEX x ( `card`);

结果

综上所述,inner join 和 left join 差不多,都需要优化右表。而 right join 需要优化左表。

我们再来看看三表查询的例子

添加一个新索引：

1

>ALTER TABLE `phone` ADD INDEX z ( `card`);ALTER TABLE `book` ADD INDEX y ( `card`);

>explain select * from class left join book on class.card=book.card left join phone on book.card = phone.card;

>*************************** 1. row *************************** id: 1 select_type: SIMPLE table: class type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 20000 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: book type: refpossible_keys: y key: y key_len: 4 ref: test.class.card rows: 1000 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: phone type: refpossible_keys: z key: z key_len: 4 ref: test.book.card rows: 260 Extra: Using index3 rows in set (0.00 sec)

后 2 行的 type 都是 ref 且总 rows 优化很好,效果不错。

MySql 中的 explain 语法可以帮助我们改写查询,优化表的结构和索引的设置,从而最大地提高查询效率。当然,在大规模数据量时,索引的建立和维护的代价也是很高的,往往需要较长的时间和较大的空间,如果在不同的列组合上建立索引,空间的开销会更大。因此索引最好设置在需要经常查询的字段中。

篇4：mysql优化查询的方法

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引，

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

3.应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id

select id from t where datediff(day,createdate,'-11-30')=0--‘2005-11-30’生成的id

应改为:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致，

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(...)

13.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免大事务操作，提高系统并发能力。

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

篇5：mysql的查询、子查询及连接查询分析

一、mysql查询的五种子句

where(条件查询)、having（筛选）、group by（分组）、order by（排序）、

limit（限制结果数）

1、where常用运算符：

比较运算符

>, < ,= , != （< >）,>= , <=

in(v1,v2..vn)

between v1 and v2 在v1至v2之间（包含v1,v2）

逻辑运算符

not ( ! ) 逻辑非

or ( || ) 逻辑或

and ( && ) 逻辑与

where price>=3000 and price <= 5000 or price >=500 and price <=1000

取500-1000或者3000-5000的值

where price not between 3000 and 5000

不在3000与5000之间的值

模糊查询

like 像

通配符:

% 任意字符

_ 单个字符

where goods_name like '诺基亚%'

where goods_name like '诺基亚N__'

2、group by 分组 www.hanwangtx.com

一般情况下group需与统计函数（聚合函数）一起使用才有意义

如：select goods_id,goods_name,cat_id,max(shop_price) from

goods group by cat_id;

这里取出来的结果中的good_name是错误的！

因为shop_price使用了max函数，那么它是取最大的，而语句中使用了group by 分组，

那么goods_name并没有使用聚合函数，它只是cat_id下的第一个商品，并不会因为shop_price改变

而改变

mysql中的五种统计函数：

（1）max：求最大值

select max(goods_price) from goods

这里会取出最大的价格的值，只有值

#查询每个栏目下价格最高的

select cat_id,max(goods_price) from goos group by cat_id;

#查出价格最高的商品编号

select goods_id,max(goods_price) from goods group by goods_id;

（2）min：求最小值

（3）sum：求总数和

#求商品库存总和

select sum(goods_number) from goods;

（4）avg：求平均值

#求每个栏目的商品平均价格

select cat_id,avg(goods_price) from goods group by cat_id;

（5）count：求总行数

#求每个栏目下商品种类

select cat_id,count(*) from goods group by cat_id;

###要把每个字段名当成变量来理解，它可以进行运算###

例：查询本店每个商品价格比市场价低多少；

select goods_id,goods_name,goods_price-market_price from goods;

查询每个栏目下面积压的货款

select cat_id,sum(goods_price*goods_number) from goods

group by cat_id;

###可以用as来给计算结果取个别名###

select cat_id,sum(goods_price * goods_number) as hk from

goods group by cat_id

不仅列名可以取别名，表单也可以取别名 www.hanwangtx.com

3、having 与where 的异同点

having与where类似，可以筛选数据，where后的表达式怎么写，having后就怎么写

where针对表中的列发挥作用，查询数据

having对查询结果中的列发挥作用，筛选数据

#查询本店商品价格比市场价低多少钱，输出低200元以上的商品

select goods_id,good_name,market_price - shop_price as s from goods having s>200 ;

//这里不能用where因为s是查询结果，而where只能对表中的字段名筛选

如果用where的话则是：

select goods_id,goods_name from goods where market_price - shop_price >200;

#同时使用where与having

select cat_id,goods_name,market_price - shop_price as s from goods where cat_id = 3 having s >200;

#查询积压货款超过2万元的栏目，以及该栏目积压的货款

select cat_id,sum(shop_price * goods_number) as t from goods group by cat_id having s >0

#查询两门及两门以上科目不及格的学生的平均分

思路：

#先计算所有学生的平均分

select name,avg(score) as pj from stu group by name;

#查出所有学生的挂科情况

select name,score<60 from stu;

#这里score<60是判断语句，所以结果为真或假，mysql中真为1假为0

#查出两门及两门以上不及格的学生

select name,sum(score<60) as gk from stu group by name having gk >1;

#综合结果

select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk >1;

4、order by

（1） order by price //默认升序排列

（2）order by price desc //降序排列

（3）order by price asc //升序排列，与默认一样

（4）order by rand //随机排列，效率不高

#按栏目号升序排列，每个栏目下的商品价格降序排列

select * from goods where cat_id !=2 order by cat_id,price desc;

5、limit

limit [offset,] N

offset 偏移量，可选，不写则相当于limit 0,N

N 取出条目

#取价格第4-6高的商品

select good_id,goods_name,goods_price from goods order by good_price desc limit 3,3; www.2cto.com

###查询每个栏目下最贵的商品

思路：

#先对每个栏目下的商品价格排序

篇6：Mysql入门系列：如何处理MYSQL查询MySQL综合

6.6 处理查询

我们已经知道了如何开始和结束与服务器的会话，现在应该看看如何控制会话，本节介绍了如何与服务器通信以处理查询。执行的每个查询应包括以下几步：

1) 构造查询。查询的构造取决于查询的内容—特别要看是否含有二进制数据。

2) 通过将查询发送到服务器执行来发布查询。

3) 处理查询结果。这取决于发布查询的类型。例如， SELECT 语句返回数据行等待处理，INSERT 语句就不这样。构造查询的一个要素就是使用哪个函数将查询发送到服务器。较通用的发布查询例程是mysql_ real _ query ( )。该例程给查询提供了一个计数串（字符串加上长度）。必须了解查询串的长度，并将它们连同串本身一起传递给mysql_real_query() 。因为查询是一个计数的字符串，

所以它的内容可能是任何东西，其中包括二进制数据或者空字节。查询不能是空终结串。另一个发布查询的函数， mysql_ query ( )，在查询字符串允许的内容上有更多的限制，但更容易使用一些。传递到mysql_query() 的查询应该是空终结串，这说明查询内部不能含有空字节（查询里含有空字节会导致错误地中断，这比实际的查询内容要短）。一般说来，如果查询包含任意的二进制数据，就可能包含空字节，因此不要使用mysql_ query( )。另一方面，当处理空终结串时，使用熟悉的标准C 库字符串函数构造查询是很耗费资源的，例如strcpy ( )和sprintf( )。

构造查询的另一个要素就是是否要执行溢出字符的操作。如果在构造查询时使用含有二

进制数据或者其他复杂字符的值时，如引号、反斜线等，就需要使用这个操作。这些将在

6.8.2节“对查询中有疑问的数据进行编码”中讨论。

下面是处理查询的简单轮廓：

mysql_query() 和mysql_real_query() 的查询成功都会返回零值，查询失败返回非零值。查询成功指服务器认为该查询有效并接受，而且能够执行，并不是指有关该查询结果。例如，它不是指SELECT 查询所选择的行，或DELETE 语句所删除的行。检查查询的实际结果要包括其他的处理。

查询失败可能有多种原因，有一些常见的原因如下：

■ 含有语法错误。

■ 语义上是非法的—例如涉及对表中不存在的列的查询。

■ 没有足够的权利访问查询所引用的数据。

查询可以分成两大类：不返回结果的查询和返回结果的查询。INSERT、DELETE和UPDATE等语句属于“不返回结果”类的查询，即使对修改数据库的查询，它们也不返回任何行。可返回的唯一信息就是有关受作用的行数。SELECT 语句和SHOW 语句属于“返回结果”类的查询；发布这些语句的目的就是要返回某些信息。返回数据的查询所生成的行集合称为结果集，在MySQL中表示为MYSQL_RES 数据类型，这是一个包含行的数据值及有关这些值的元数据（如列名和数据值的长度）的结构。空的结果集（就是包含零行的结果）要与“没有结果”区分开。

6.6.1处理不返回结果集的查询

处理不返回结果集的查询，用mysql_query() 或mysql_real_query() 发布查询。如果查询成功，可以通过调用mysql_ a ffected_rows() 找出有多少行需要插入、删除或修改。下面的样例说明如何处理不返回结果集的查询：

请注意在打印时mysql

关键字：MYSQL

篇7：教你编写高质量高性能的MySQL语法

在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一，系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。

在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。

SQL语句编写注意问题

下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低，

1. IS NULL 与 IS NOT NULL

不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

2. 联接列

对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表(employee)，对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME)，现在要查询一个叫比尔.克林顿(Bill Cliton)的职工。

下面是一个采用联接查询的SQL语句，

select * from employss

where

first_name||''||last_name ='Beill Cliton'

上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。