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有Ｍ行，那么查询的时间复杂度就是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