mysql where加排序很慢 mysql where having顺序

转载

蓝月亮 2023-09-07 20:19:39

文章标签 mysql where加排序很慢数据存储过程 ci 文章分类 MySQL 数据库

sql执行顺序

(1)from (3) join (2) on (4) where ---where后的条件表达式里不允许使用聚合函数,可以用having代替(5)group by(开始使用select中的别名，后面的语句中都可以使用)(6) avg,sum.... (7)having (8) select (9) distinct (10) order by(11) limit

from →join →on →where →group by→having→select→order by→limit

(7)    SELECT
(8)    DISTINCT <select_list>
(1)    FROM <left_table>
(3)    <join_type> JOIN <right_table>
(2)    ON <join_condition>
(4)    WHERE <where_condition>
(5)    GROUP BY <group_by_list>
(6)    HAVING <having_condition>
(9)    ORDER BY <order_by_condition>
(10)   LIMIT <limit_number>

having与where的区别:

having是在分组后对数据进行过滤where是在分组前对数据进行过滤having后面可以使用聚合函数where后面不可以使用聚合

having就是来弥补where在分组数据判断时的不足。因为where执行优先级别要快于聚合语句。

show语句

show databases

use database;

show tables;

show columns in table1 / describe table1;显示列状态desc tablename

show status; 显示服务器状态信息

show create database/table/view name; 显示创建数据库/表的语句

show grants; 显示授权用户的权限

show errors/warnings; 显示服务器错误或警告

select column_name1,..2,..3 from table_name;

大小写无所谓，但规范是关键字大写，表名等小写

select * from table_name;Tags:一般不用*，实际数据库都很大，用这个降低性能

p35 select distinct column_name from tablename;检测一列中不同的数据

限制结果:

SELECT column_nameFROM table_nameLIMIT 5;

SELECT column_nameFROM table_name LIMIT 3,4;返回行3开始的4行 3-6或 LIMIT 4 OFFSET 3

表名可以是完全限定的SELECT tablename.column_nameFROM database.table_name;

不排序检索出来的数据顺序是无意义的SELECT column_nameFROM table_nameORDER BY column2_name,column_x_name...;

排序方向SELECT column_nameFROM table_nameORDER BY column2_name DESC;DESC只应用到位于它直接前面的列

（8）SELECT（9）DISTINCT <select_list>
（1）FROM <left_table>
（3）<join_type> JOIN <right_table>
（2）ON <join_condition>
（4）WHERE <where_condition>
（5）GROUP BY <grout_by_list>
（6）WITH {CUTE|ROLLUP}
（7）HAVING <having_condition>
（10）ORDER BY <order_by_list>
（11）LIMIT <limit_number>

排序加limit可以求最值的几个

使用过滤条件：SELECT ... FROM ... WHERE id=5;WHERE子句操作符：= <>!=<<=>>=BETWEEN<>不等于 between a and b 在[a和b]之间

空值NULL 与0 '' ' '不同，检查空值用WHERE ... IS NULLNULL在匹配条件时不返回

更加复杂的过滤：以下都是where后面跟的操作符

AND操作符 OR操作符一个where中两个都有应该加（）表明计算顺序

IN操作符 IN操作符后面的圆括号中提供逗号分隔的合法名单，完成与or相同的工作WHERE id IN (1002,1003)

使用IN更直观清楚
计算次序好管理
比OR更快
最大的优点是可以包含其他select语句

NOT操作符：否定之后的所有条件例如id not in (1,2,3,4)

第八章-通配符：

LIKE操作符：前面所有的操作符都是针对已知的进行过滤，倘若过滤产品名中包含no的产品就要用到通配符，搜索时区分大小写

操作符在作为谓词时不是通配符

1.百分号%通配符：%表示任何字符出现任何次数，例如hello%表示以hello开头的任何字符串常用的 a%b，%aaa%，%a，a%，用的时候注意尾部的空格会干扰搜索，%匹配不到NULL

2.下划线_通配符：匹配一个单个字符

使用通配符的原则：

尽量少使用
真要使用也不要在开始时就使用，这样会很慢
注意通配符式子是不是你想要的

九。正则表达式

REGEXP包含，LIKE匹配一列整个字符串，而REGEXP只要包含就返回

| :正则表达式的OR '1000|2000|3000'

[]: [123]表示匹配1或2或3，[1-3] [a-z]

匹配特殊字符加\

重复匹配符*0个或多个+1个或多个等效于{1,}? 0个或1个等效于{0,1}{n} 匹配指定数目{n,}不少于指定数目{n,m}nm之间

定位符^文本开头$文本结尾[[:<:]]词的开头[[:>:]]词的结尾

十.计算字段

1.拼接Concat()SELECT Concat(name,'(',id,')') from user order by id;

2.RTrim() LTrim() Trim()去掉多余空格

3.使用别名select id as myId from ...

4.算术计算+-*/select id, price*id as myid from ... where ...;

十一数据处理函数：使用时加好注释因为不同dbms函数很不一样

1.文本RTrim() LTrim() Trim()去掉多余空格Upper() Lower() Length() Left() Right()Soundex()发音搜索函数所有发音相似的都会被搜索到

2.日期处理函数Date()值比较日期部分 Time()比较时间部分select id from orderwhere Date(order_date) Between '2020-01-01' AND '2020-05-01';Year() Month()返回日期的年月这样可以不用记下来每月有几天和闰年where Year(data1) = 2021 and Month(data1) = 7;

3.数值处理函数Abs() Cos() Exp() Mod() Pi() Rand() Sin() Sqrt() Tan()

4.聚集函数AVG() COUNT() MAX() MIN() SUM()avg忽略NULL count(*)会统计NULL count(column )不会avg可以和DISTINCT一起用只考虑不同的值AVG(DISTINCT column1)

十三分组GROUP BY p95注意事项

分组将数据分为逻辑组，方便分组计算搜索select id,count(*) as numfrom usergroup by id;

有了group by 顺序变为from where group by order by limit

十四子查询

1.使用INselect cust_idfrom orderswhere order num IN (select order_numfrom orderitemswhere prod_id=123)子查询总是从内到外执行

2.子查询作为一列select cust_id，(select order_numfrom orderitemswhere prod_id=123) as ofrom orders

子查询不应嵌套过多

子查询要注意有相同名字的列时加上全限定名

十五联结表：用select查询出存储在多个表的数据

外键：外键为某个表的一列，它包含另一个表的主键，定义了两表之间的关系

联结的创建:规定要联结的所有表以及他们如何关联select vend_name,prod_name,prod_priceFROM vendors,productsWHERE vendors.vend_id = products.vent_idORDER BY vend_name,prod_name;

列分为不同的表，注意where时最好使用完全限定类名，select的看情况，需要的也要加上

联结与子查询不同，子查询是限定范围，而联结则是为了找分布在多个表中的数据，所以需要在WHERE中操作，也就是比对每一列，所以没有联结条件的查询将返回笛卡尔积

上述用的都是内部链接，也就是利用=联结，下面的示例也可以

select vend_name,prod_name,prod_priceFROM vendors INNER JOIN productsON vendors.vend_id = products.vend_id;

在使用这种语法时用ON指定联结条件而不是WHERE，推荐使用INNER JOIN

联结多个表，直接用WHERE + AND，但应该注意联结很消耗资源，不应该联结不需要的表

mysql where加排序很慢 mysql where having顺序_数据

十六高级联结

使用列别名和表别名

1.缩短SQL语句；2.允许在单条SELECT语句中多次使用相同的表

使用不同类型的联结迄今为止，我们使用的只是称为内部联结或等值联结

现在来看3种其他联结，它们分别是自联结、自然联结和外部联结。

需要表自联结的情况，假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是解决此问题的一种方法：子查询也可以使用自联结，一般处理联结要比子查询快

自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次

自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的。下面举一个例子

外部联结许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如，可能需要使用联结来完成以下工作： 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户； 列出所有产品以及订购数量，包括没有人订购的产品； 计算平均销售规模，包括那些至今尚未下订单的客户。在上述例子中，联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

SELECT cs.cust_id,orders.order_numFROM customers AS cs LEFT OUTER JOIN ordersON orders.cust_id = cs.cust_id;

这条SELECT语句使用了关键字OUTER JOIN来指定联结的类型（而不是在WHERE子句中指定）。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（ RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN

十七组合查询

MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（ union）或复合查询（compound query）。

有两种基本情况，其中需要使用组合查询： 在单个查询中从不同的表返回类似结构的数据； 对单个表执行多个查询，按单个查询返回数据

利用UNION进行组合查询

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。 UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过分析第 17 章组合查询各个列不需要以相同的次序列出）。 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

十八全文本搜索

十九插入数据：可以禁止插入

插入完整的行INSERT INTO CustomersVALUES(NULL,'value1',value2,'value3',...);但一般不用这种依赖于顺序的方法，不安全INSERT INTO Customers（name，id，city）VALUES(name_value,id_value,city_value);

插入行的一部分

插入多行INSERT INTO Customers（name，id，city）VALUES(name_value,id_value,city_value),(...),(...)...;

插入某些查询结果

INSERT INTO Customers（name，id，city）SELECT name，id，city FROM student;

更新和删除数据

更新表中特定行

更新特定行一定注意加WHERE条件UPDATE table_nameSET name='lmy'WHERE cust_id = 1;

更新多列加，就行了UPDATE table_nameSET name='lmy'，grade=3WHERE cust_id = 1;

更新表中所有行

不加where

删除数据：

把值改成NULL 如果允许的话

DELETE，这个更要注意一定要加whereDELETE FROM userWHERE id=3

二十一创建表

CREATE TABLE customers(column1typeNOTNULL/NUllAUTO_INCREMENT,....,.....,主键信息 PRIMARY KEY（id[,name[,...]]）

)ENGINE=InnoDB;

以下是几个需要知道的引擎： InnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文本搜索； MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）； MyISAM是一个性能极高的引擎，它支持全文本搜索（参见第18章），但不支持事务处理

为更新表定义，可使用ALTER TABLE语句。但是，理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

DROP table_name

RENAME TABLE table1 To table2

二十二视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

现在，假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以如下轻松地检索出相同的数据：SELECT name,id FROM productcustomers WHERE id =2;

下面是视图的一些常见应用。 重用SQL语句。 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。 使用表的组成部分而不是整个表。 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试

下面是关于视图创建和使用的一些最常见的规则和限制。 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。 对于可以创建的视图数目没有限制。 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。 ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。 视图不能索引，也不能有关联的触发器或默认值。 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

CREATE VIEW创建视图SHOW CREATE VIEW viewnameDROP VIEW viewname

CREATE VIEW haveemailUser ASSELECT id,name,emailFROM userWHERE email IS NOT NULL;创建有emil用户的视图

二十三存储过程

可以创建存储过程。存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

使用存储过程有3个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将SQL代码转换为存储过程前，也必须知道它的一些缺陷。 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。218

23.3 使用存储过程 165 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程尽管有这些缺陷，存储过程还是非常有用的，并且应该尽可能地使用。

CREATE PROCEDURE product_pricing()BEGINSELECT AVG(price) AS priceaverFROM products;END;

使用时 CALL product_pricing()

删除 DROPPROCEDURE name

使用参数

CREATE PROCEDURE product_pricing(OUT pl DECIMAL(8,2)

)BEGINSELECT AVG(price) AS priceaverINTO plFROM products;END;

CALL product_pricing(@price_avg)

这个变量可以直接select出来

二十四游标

由前几章可知， MySQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行（零行或多行）。使用简单的SELECT语句，例如，没有办法得到第一行、下一行或前10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们）。有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（ cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

创建游标：DECLARE name CURSORFOR select语句

OPEN/CLOSE ordername

二十五触发器

MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？例如： 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写； 每当订购一个产品时，都从库存数量中减去订购的数量； 无论何时删除一行，都在某个存档表中保留一个副本。

创建触发器CREATE TRIGGER trigger_name AFTER/BEFORE 响应活动（delete insert update ）

二十六事务处理

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行

事务（transaction）指一组SQL语句； 回退（rollback）指撤销指定SQL语句的过程； 提交（commit）指将未存储的SQL语句结果写入数据库表； 保留点（ savepoint）指事务处理中设置的临时占位符（ placeholder），你可以对它发布回退（与回退整个事务处理不同）。

使用ROLLBACK；MySQL的ROLLBACK命令用来回退（撤销） MySQL语句START TRANSACTION;selectupdateROLLBACK;

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（ implicit commit），即提交（写或保存）操作是自动进行的。但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句，如下所示：

这些占位符称为保留点。为了创建占位符，可如下使用SAVEPOINT

SAVEPOINT update1;ROLLBACK TO update1;

保留点越多越好

二十八安全管理

 多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表； 某些用户需要读表，但可能不需要更新表； 你可能想允许用户添加数据，但不允许他们删除数据； 某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要； 你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据； 你可能想根据用户登录的地点限制对某些功能的访问。

不过在现实世界的日常工作中，决不能使用root。应该创建一系列的账号，有的用于管理，有的供用户使用，有的供开发人员使用，等等

use mysql ;select user form user;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。