mysql常用语句二

转自：http://blog.sina.com.cn/s/blog_62328b290100eydc.html

.行子查询

 

行子查询是一个能返回一个单一行的子查询变量，因此可以返回一个以上的列值。以下是两个例子：

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROMt2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROMt2);
如果在表t2的一个行中，column1=1并且column2=2，则查询结果均为TRUE。

表达式（1，2）和ROW（1，2）有时被称为行构造符。两者是等同的，在其它的语境中，也是合法的。
例如，以下两个语句在语义上是等同的（但是目前只有第二个语句可以被优化）：

  SELECT * FROM t1 WHERE (column1,column2) =(1,1);
  SELECT * FROM t1 WHERE column1 = 1 AND column2 =1;

行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。

例如，以下查询可以答复请求，“在表t1中查找同时也存在于表t2中的所有的行”：
SELECT column1,column2,column3
FROM t1
WHERE  (column1,column2,column3) IN (SELECTcolumn1,column2,column3 FROM t2);


.如果一个子查询返回任何的行，则EXISTS subquery为FALSE。例如：
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

    哪些种类的商店出现在一个或多个城市里
    SELECT DISTINCT store_typeFROM stores WHERE EXISTS (
          SELECT * FROMcities_stores  WHERE cities_stores.store_type =stores.store_type);

    哪些种类的商店没有出现在任何城市里
    SELECT DISTINCT store_typeFROM stores WHERE NOT EXISTS (
          SELECT * FROM cities_storesWHERE cities_stores.store_type = stores.store_type);

    哪些种类的商店出现在所有城市里
    SELECT DISTINCT store_typeFROM stores s1 WHERE NOT EXISTS (
          SELECT * FROM cities WHERENOT EXISTS (
              SELECT * FROM cities_stores WHEREcities_stores.city = cities.city AND cities_stores.store_type =stores.store_type
        )
      );


.相关联的子查询是一个包含对表的引用的子查询。该表也显示在外部查询中。例如：
SELECT * FROM t1 WHERE column1 = ANY(SELECT column1 FROM t2 WHEREt2.column2 = t1.column2);

.完全清空一个表
TRUNCATE TABLE  my_tbl;

与DELETE语句不同的是: 对于InnoDB表，如果有需要引用表的外键限制，则TRUNCATETABLE被映射到DELETE上；
否则使用快速删减（取消和重新创建表）。使用TRUNCATETABLE重新设置AUTO_INCREMENT计数器，设置时不考虑是否有外键限制。


.事务
  START TRANSACTION;
    SELECT @A:=SUM(salary) FROMtable1 WHERE type=1;
    UPDATE table2 SET summary=@AWHERE type=1;
  COMMIT;


.LOCK TABLES和UNLOCK TABLES语法
LOCK TABLES tbl_name [AS alias]  {READ [LOCAL] |[LOW_PRIORITY] WRITE}
      [,tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}]...

UNLOCK TABLES

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
mysql> INSERT INTO t SELECT * FROM t;
  ERROR 1100: Table 't' was not locked with LOCKTABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;
如果您的查询使用一个别名引用一个表，那么您必须使用同样的别名锁定该表。如果没有指定别名，则不会锁定该表。

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
  ERROR 1100: Table 'myalias' was not locked withLOCK TABLES
相反的，如果您使用一个别名锁定一个表，您必须使用该别名在您的查询中引用该表。

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
  ERROR 1100: Table 't' was not locked with LOCKTABLES
mysql> SELECT * FROM t AS myalias


果您正在使用MySQL中的一个不支持事务的存储引擎，则如果您想要确定在SELECT和UPDATE之间没有其它线程，您必须使用LOCKTABLES。本处所示的例子要求LOCK TABLES，以便安全地执行：

      mysql> LOCK TABLES trans READ, customer WRITE;
      mysql> SELECT SUM(value) FROM trans WHEREcustomer_id=some_id;
      mysql> UPDATE customer
          ->    SETtotal_value=sum_from_previous_statement
          ->    WHEREcustomer_id=some_id;
      mysql> UNLOCK TABLES;
如果没有LOCK TABLES，有可能另一个线程会在执行SELECT和UPDATE语句之间在trans表中插入一个新行。


.分析表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]...


.CHECK TABLE语法
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}


.OPTIMIZE TABLE语法
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]...

如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR,BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZETABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZETABLE来重新利用未使用的空间，并整理数据文件的碎片。

在多数的设置中，您根本不需要运行OPTIMIZETABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

对于MyISAM表，OPTIMIZE TABLE按如下方式操作：

  1.如果表已经删除或分解了行，则修复表。
  2.如果未对索引页进行分类，则进行分类。
  3.如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。

对于BDB表，OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。对于InnoDB表，OPTIMIZETABLE被映射到ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。

使用—skip-new或—safe-mode选项可以启动mysqld。通过启动mysqld，您可以使OPTIMIZETABLE对其它表类型起作用。

注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。

OPTIMIZETABLE语句被写入到二进制日志中，除非使用了自选的NO_WRITE_TO_BINLOG关键词（或其别名LOCAL）。已经这么做了，因此，用于MySQL服务器的OPTIMIZETABLE命令的作用相当于一个复制主服务器，在默认情况下，这些命令将被复制到复制从属服务器中。



.REPAIR TABLE语法
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...[QUICK] [EXTENDED] [USE_FRM]


.RESTORE TABLE语法
RESTORE TABLE tbl_name [, tbl_name] ... FROM'/path/to/backup/directory'
用于恢复来自用BACKUP TABLE制作的备份的表。原有的表不会被覆盖；如果您试图覆盖一个原有的表，会发生错误。


.SHOW系列
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW GRANTS [FOR user]
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW [BDB] LOGS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW TRIGGERS
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW WARNINGS [LIMIT [offset,] row_count]

SHOW BINLOG EVENTS
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS

SHOW PROCESSLIST语法
SHOW [FULL] PROCESSLIST
SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladminprocesslist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。请参见13.5.5.3节，“KILL语法”。如果您不使用FULL关键词，则只显示每个查询的前100个字符。

本语句报告TCP/IP连接的主机名称（采用host_name:client_port格式），以方便地判定哪个客户端正在做什么。

如果您得到“too manyconnections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的。MySQL保留一个额外的连接，让拥有SUPER权限的账户使用，以确保管理员能够随时连接和检查系统（假设您没有把此权限给予所有的用户）。

在来自SHOW PROCESSLIST的输出中常见的一些状态：

Checking table
线程正在执行（自动）表格检查。

Closing tables
意味着线程正在刷新更改后的表数据，并正在关闭使用过的表。这应该是一个快速的操作。如果不快，则您应该验证您的磁盘没有充满，并且磁盘没有被超负荷使用。

Connect Out
连接到主服务器上的从属服务器。

Copying to tmp table on disk
临时结果集合大于tmp_table_size。线程把临时表从存储器内部格式改变为磁盘模式，以节约存储器。

Creating tmp table
线程正在创建一个临时表，以保持部分结果。

deleting from main table
服务器正在执行多表删除的第一部分，只从第一个表中删除。

deleting from reference tables
服务器正在执行多表删除的第二部分，从其它表中删除匹配的行。

Flushing tables
线程正在执行FLUSH TABLES，并正在等待所有线程，以关闭表。

FULLTEXT initialization
服务器正在准备执行一个自然语言全文本搜索。

Killed
有人已经向线程发送了一个KILL命令。在下一次检查终止标记时，应放弃。该标记在MySQL的每个大循环中都检查，但是在有些情况下，线程终止只需要较短的时间。如果该线程被其它线程锁定，则只要其它线程接触锁定，终止操作就会生效。

Locked
该查询被其它查询锁定。

Sending data
线程正在为SELECT语句处理行，同时正在向客户端发送数据。

Sorting for group
线程正在进行分类，以满足GROUP BY要求。

Sorting for order
线程正在进行分类，以满足ORDER BY要求。

Opening tables
线程正在试图打开一个表。这应该是非常快的过程，除非打开操作受到阻止。例如，一个ALTER TABLE或一个LOCKTABLE语句可以阻止打开一个表，直到语句完成为止。

Removing duplicates
查询正在使用SELECTDISTINCT。使用时，在早期阶段，MySQL不能优化不同的操作。因此，MySQL要求一个额外的阶段，以便在把结果发送给客户端之前取消所有的复制行。

Reopen table
线程得到一个表锁定，但是在得到锁定后被通知带下方的表结构已更改了。它已经释放了锁定，关闭了表，并试图重新打开它。

Repair by sorting
修复代码正在使用一个分类来创建索引。

Repair with keycache
修复代码正在通过关键缓存一个接一个地使用创建关键字。这比通过分类修复要慢很多。

Searching rows for update
线程正在进行第一阶段，以在更新之前，查找所有匹配的行。如果UPDATE正在更改用于查找相关行的索引，则必须这么做。

Sleeping
线程正在等待客户端，以向它发送一个新语句。

System lock
线程正在等待得到一个用于表的外部系统锁定。如果您没有正在使用多个正在访问同一个表的mysqld服务器，则您可以使用--skip-external-locking选项禁用系统锁定。

Upgrading lock
INSERT DELAYED管理程序正在试图得到一个表锁定，以插入行。

Updating
线程正在搜索行，并正在更新这些行。

User Lock
线程正在等待GET_LOCK()。

Waiting for tables
线程得到一个通知，表的底层结构已经改变，需要重新打开表以得到新的结构。但是，为了能重新打开表，必须等待，直到所有其它的线程已经关闭了正在被质询的表。

如果其它线程已经对正在被质询的表使用了FLUSH TABLES或以下语句之一：FLUSH TABLES tbl_name, ALTERTABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE或OPTIMIZETABLE；则会出现通知。

waiting for handler insert
INSERT DELAYED管理程序已经处理了所有处于等待状态的插入，并正在等待新插入。

多数状态对应于非常快的操作。如果一个线程在这些状态下停留了数秒，则可能是有问题，需要进行调查。

有一些其它的状态，在前面的清单中没有提及，但是其中有很多状态对于查找服务器中的程序错误是有用的