MySQL问题集锦

1.SQL的select语句的定义和执行顺序

一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下:

<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>] 

(1)SELECT子句是必选的,其它子句如WHERE子句、GROUP BY子句等是可选的。

(2)一个SELECT语句中,子句的顺序是固定的。例如GROUP BY子句不会位于WHERE子句的前面。

(3) SELECT语句执行顺序 :

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果

每个子句执行后都会产生一个中间数据结果,即所谓的临时视图,供接下来的子句使用,如果不存在某个子句,就跳过。MySQL和SQL执行顺序基本是一样的。

2.where子句为什么不能使用count的别名

先举一个反例。

SELECT CustomerID,count(*) as orderNum FROM Orders WHERE orderNum>3 group by  CustomerID

上面的sql意在选出订单数超过三条的客户ID和订单数。但这是错误,这是书上的一个反例,错误的原因是如果使用AS赋给列一个别名并且在WHERE子句中使用该列,那么必须通过它的原名来引用,而不能使用别名。根本原因是select的语句的执行顺序。where使用的别名对应的临时视图是在group by子句之后才形成的。此时在group by子句之前使用未形成的临时视图的字段名称当然是错误。因此可使用having子句。

SELECT CustomerID,count(*) as orderNum FROM Orders  group by  CustomerID having orderNum>3

参考上面select语句的执行顺序,我们就比较容易理解了。也就容易理解where和having子句的使用区别了。

3.MySQL将查询的结果保存到新的中间物理表中,并建立索引,提高查询速度

将子查询的结果保存到新建的uinTable表中。注意uinTable不用提前新建,下面的语句会自动新建uinTable表。

create table uinTable select dwFromUin,count(*) as invCnt,count(if(dwInvUINNum>=50,true,null)) as manyCnt from inv_join_group_20160620 group by dwFromUin having manyCnt*2>=invCnt;

查看数据表的建表语句:

show create table uinTable;

查询的结果是:

建立索引。对上面的中间表建立索引来提高查询速度:

alter table uinTable add index(dwFromUin);
--或者
CREATE INDEX index_name ON table_name (column_list)

--当然也可以建立多个索引
alter table B add index(column1, column2);

为什么建立索引可以提高查询速度呢?一般我们会使用如下语句进行查询:

select *  from A where A.XX in (select XX from B);
--B.XX这个字段,最好建个索引;

加入数据表A有1000行,数据表B有100行,那么上面的查询就需要1000*100次比较。因为每一次判断A.XX查询是否存在子表中,都需要遍历一次,循环100次,所以这样的查询效率很低。下面使用explain查看具体的查询次数:

explain select * from inv_join_group_20160620 where dwFromUin  in (select dwFromUin from uinTable);

查询结果:

可见红框中在子查询中使用索引只需要查询一次即可判断dwFromUin是否存在于子查询中。

删除索引。现在我们删除中间表uinTable的索引,再来看一下查询的次数:

--删除数据表的索引
drop index dwFromUin on uinTable;
--或者
alter table uinTable drop index dwFromUin;

查看索引。

--查看数据表的索引
show index from|in uinTable;

现在我再来使用explain来查看一下我们没有在子查询中使用所以的查询效率。查询结果如下:

发现红框中的子查询的行数变成了11多万行,也就意味这要确定dwFromUin是否在子查询中,需要比较11多万次。对于形如下面的查询语句:

select *  from A where A.xx in (select XX from B);

假如A有N行,B有M行,那么查询的时间复杂度就是O(N*M),如果对B建立索引的话。查询的时间复杂度就是O(N*LogM)或者O(N*1)。具体是LogM还是1,要看数据库对索引是如何实现的。如果使用B树或者查找树实现的话,就是前者,如果使用hash的话,就是后者。

4. 可否不新建数据表来存储子查询的结果,同时能够为子查询建立索引提高查询的速度

答案是否定的。MySQL的子查询结果是不能建立索引,速度也会慢,所以还是乖乖的将子查询的结果存储在一个临时表或者数据表中,再建立索引。

5.子查询,临时表和视图的区别

子查询是用于查询语句中辅助主查询完成结果查询的查询语句。子查询存在嵌套查询中,嵌套查询就是多个子查询嵌套在主查询中形成的查询语句。例如下面的查询语句就是嵌套查询: sql select A.XX from A where A.XX in (select B.XX from B) 上面select B.XX from B 就是子查询。

临时表其实就是我们可以将查询或者子查询的结果放在一个新建的临时表中,供后续查询使用。我们可以使用如下语句将查询的结果存入新建的临时表中:

create temporary table temp_grp select B.XX from B

临时表的特点是可以对其建立索引,提高查询速度。当当前连接数据库的会话结束时,临时表会被自动删除,不会永久保存。这里需要注意的是,MySQL中没有像SQL Server中临时表又分为本地临时表和全局临时表,MySQL中只有本地临时表。

视图是一个虚拟表,就是逻辑上存储我们查询或者子查询的结果,供后续查询使用。物理上并不存储实际的数据,这也是视图与临时表的本质区别,此外,视图是不能不能创建索引的,这也是与临时表的一个区别。具体如下:

创建一个视图:

create view view_lvlv as  select * from  tablename limit 4;

在为视图尝试建立索引的时候报错。

6.如何查看当前数据库中建立了哪些临时表

使用命令show tables 是不会显示当前会话创建的临时表有哪些。那么我们如何才能查看我们创建了的哪些临时表呢?

目前尝试了中文百度和英文google都未果,知道的请留言告知!

7. MySQL中查询系统时间的方法

第一种方法:select current_date;

mysql> select current_date as Systemtime; +————+ | Systemtime | +————+ | 2009-07-29 | +————+

第二种方法:select now()

mysql> select now() as Systemtime; +———————+ | Systemtime | +———————+ | 2009-07-29 19:06:07 | +———————+

第三种方法:select sysdate()

mysql> select sysdate() as Systemtime; +———————+ | Systemtime | +———————+ | 2009-07-29 19:06:45 | +———————+

8.mysql 执行命令为什么输出一大堆使用说明

情况简单的描述如下。

我在shell脚本中使用如下方式来执行sql语句是没有问题的。

echo "drop table if exists [tableName];"|mysql -u[username] -p[password] [dbname] 

中括号中替换为相应的名称。

然后我就在终端shell中如法炮制输入如下语句:

mysql -u[username] -p[password] [dbname] "drop table if exists [tableName]"

结果就会出现一大堆mysql的版本介绍以及使用说明。一时间挠破脑袋也想不明白,我到底哪里理解错了。冷静思索,在leader的提醒下,终于弄明白了,原来shell脚本中使用echo的写法是将sql语句作为标准输入传入到mysql命令中,而后面在终端中的写法则是作为命令行参数传入mysql,二者的写法是有着本质的区别。

当然,如果我们使用-e命令选项,也是可以在终端shell下执行sql语句的,格式如下:

mysql -u[username] -p[password] [dbname] -e "drop table if exists [tableName]"

9.mysql -B -N命令选项什么意思

The “-B” or “–batch” option will force the output to be TAB delimited no matter where the output is going. The “-N” option will turn off column names in the output.

意思就是: -B或者–batch:控制mysql查询输出使用Tab制表符作为分隔符; -N:控制mysql查询不输出列名。


参考文献

[1]关于sql和mysql对于别名不能调用的一些理解 [2]视图.百度百科 [3]MySQL_notes

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle 索引监控(monitor index)

      合理的为数据库表上创建战略性索引,可以极大程度的提高了查询性能。但事实上日常中我们所创建的索引并非战略性索引,恰恰是大量冗余或是根本没有用到的索引耗...

11110
来自专栏Java帮帮-微信公众号-技术文章全总结

Java企业面试——数据库

数据库部分 数据表连接问题,左外连接、右外连接、内连接等 一、交叉连接(CROSS JOIN) 交叉连接(CROSS JOIN):有两种,显式的和隐式的,不...

29940
来自专栏技术博文

从MyISAM转到InnoDB需要注意什么

转自 MySql中文网 http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=200910426&idx=1...

370140
来自专栏技术博文

唯一索引与主键索引的比较

唯一索引 唯一索引不允许两行具有相同的索引值。 如果现有数据中存在重复的键值,则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时...

399110
来自专栏PHP在线

Mysql索引和性能优化

使用索引的原则 1. 如果没有唯一性要求,可以选择普通索引 2. 如果列上有唯一性要求,可以选择唯一索引 3. 如果是需要模糊搜索,建议选择全文索引 4. 如果...

31080
来自专栏java一日一条

一次非常有意思的SQL优化经历

发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。

8610
来自专栏技术博文

多表关联是ON和WHERE的区别

很多时候,开发在书写SQL的时候不能正确的理解和运用ON和WHERE的区别。今天就简单演示介绍下(有图有真相)。 原理:数据库在通过连接多张表来返回记录时,都会...

33870
来自专栏后端技术探索

一次非常有意思的sql优化经历

发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。

9410
来自专栏Java帮帮-微信公众号-技术文章全总结

【数据库】MySQL进阶一、主外键讲解

MySQL进阶主外键讲解 1.什么是外键: 主键:是唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性 外键:是另一表的主键, 外键可...

37670
来自专栏云计算教程系列

PostgreSQL中的查询简介

数据库是许多网站和应用程序的关键组成部分,是数据在互联网上存储和交换的核心。数据库管理最重要的一个方面是从数据库中检索数据的做法,无论是临时基础还是已编码到应用...

14130

扫码关注云+社区

领取腾讯云代金券