mysql常用sql语句一

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

重命名表

ALTER TABLE tbl1 RENAME tbl2;

.重命名表
RENAME TABLE old_table TO backup_table;

重命名多个表
RENAME TABLE old_table TO backup_table, new_table TOold_table;

.把表从一个库移动到另一个库
RENAME TABLE db1.tbl_name TO db2.tbl_name;

.删去字段
ALTER TABLE tbl DROP COLUMN filed1, DROP COLUMN field2;

删除列c：
ALTER TABLE t2 DROP COLUMN c;

.把INTEGER类型的列的名称从old_name变更到new_name
mysql> ALTER TABLE tbl CHANGEold_name 

new_name  INTEGER;


.修改列的类型 (需要列出两次列名)
  ALTER TABLE tbl CHANGE filed1  filed1  BIGINT NOT NULL;

或用modify(只需列出一次列名即可)
  ALTER TABLE tbl MODIFY field1 BIGINT NOTNULL;


  把列a从INTERGER更改为TINYINT NOTNULL（名称保持不变），并把列b从CHAR(10)更改为CHAR(20)，同时把列b重新命名为列c：
  ALTER TABLE tbl MODIFY a TINYINT NOT NULL,CHANGE b c CHAR(20);


.添加列
  添加一个新的TIMESTAMP列，名称为d：
  ALTER TABLE t2 ADD d TIMESTAMP;

.在列d和列a中添加索引：
  ALTER TABLE t2 ADD INDEX (d), ADD INDEX(a);
 

.创建有名索引
  CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEXindex_name  [USING index_type]  ON tbl_name (index_col_name,...)

  列表(index_col_name,...)用于创建一个多列的组合索引

  创建索引时，可以指定var或char类型的前几个字符即可，索引使用列名称的前10个字符。
  CREATE INDEX part_of_name ON customer(name(10));

.创建索引时指定类型
  部分储存引擎允许在创建索引时指定索引类型。index_type指定语句的语法是USINGtype_name。
  不同的储存引擎所支持的type_name值已显示在下表中。如果列有多个索引类型，当没有指定index_type时，第一个类型是默认值。

  存储引擎    允许的索引类型
    MyISAM      BTREE
    InnoDB      BTREE
    MEMORY/HEAP  HASH, BTREE

  示例：
  CREATE TABLE lookup (id INT) ENGINE =MEMORY;
  CREATE INDEX id_index USING BTREE ON lookup(id);
 
  TYPE type_name可以作为USINGtype_name的同义词，用于指定索引类型。但是，USING是首选的格式。
  另外，在索引规约语法中，位于索引类型前面的索引名称不能使用TYPE。这是因为，与USING不同，
  TYPE不是保留词，因此会被认为是一个索引名称。


.添加一个新的AUTO_INCREMENT整数列，名称为c：
  ALTER TABLE t2 ADD c INT UNSIGNED NOT NULLAUTO_INCREMENT, ADD PRIMARY KEY (c);


.InnoDB取消外键：
  ALTER TABLE tbl DROP FOREIGN KEYfk_symbol;


.要把表默认的字符集和所有字符列（CHAR, VARCHAR, TEXT）改为新的字符集，应使用如下语句：
  ALTER TABLE tbl  CONVERT TOCHARACTER SET new_charset_name;


.修改列的字符集
  ALTER TABLE tbl CHANGE field1 field1TEXT  CHARACTER SET utf8;


.仅仅改变表的默认字符集
ALTER TABLE tbl DEFAULT CHARACTER SET new_charset_name;


.如果InnoDB表在创建时，使用了.ibd文件中的表空间，则这样的文件可以被删除和导入。使用此语句删除.ibd文件：
  ALTER TABLE tbl_name DISCARD TABLESPACE;


.要把备份的.ibd文件还原到表中，需把此文件复制到数据库目录中，然后书写此语句：
  ALTER TABLE tbl_name IMPORT TABLESPACE;


.ALTERTABLE也可以用于对带分区的表进行重新分区，功能包括添加、取消、合并和拆分各分区，还可以用于进行分区维护
假设您有一个按照以下方式创建的带分区的表：

CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN(1991),
    PARTITION p1 VALUES LESS THAN(1995),
    PARTITION p2 VALUES LESS THAN(1999)
);   


可以在表中增加一个新的分区p3，该分区用于储存小于2002的值。添加方法如下：
ALTER TABLE t1 ADD PARTITION p3 VALUES LESS THAN (2002);
注释：您不能使用ALTER TABLE向一个没有进行分区的表添加分区。


以采用如下方法取消名称为p0和p1的分区：
ALTER TABLE DROP PARTITION p0, p1;



COALESCEPARTITION可以用于使用HASH或KEY进行分区的表，以便使用number来减少分区的数目。例如，假设您使用下列方法创建了表t2：

CREATE TABLE t2 (
    name VARCHAR (30),
    started DATE
)
PARTITION BY HASH(YEAR(started))
PARTITIONS (6);

您可以使用以下命令，把t2使用的分区的数目由6个减少到4个：
ALTER TABLE t2 COALESCE PARTITION 2;



[注] ADD PARTITION和DROP PARTITION目前不支持IF [NOT] EXISTS
    也不可能对一个分区或一个已分区的表进行重命名。
    要对一个分区进行重命名，您必须取消分区，再重新建立；
    如果您希望对一个已分区的表进行重新命名，您必须取消所有分区，然后对表进行重命名，再添加被取消的分区



.创建表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(
    create_definition1,
    create_definition2,
    ...
  )]
  [table_options] [select_statement];

  或：

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS]tbl_name
    [(] LIKE old_tbl_name[)];


column_definition:
    col_name type [NOT NULL |NULL] [DEFAULT default_value]
        [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY]KEY]
        [COMMENT 'string'] [reference_definition]


create_definition:
    column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type](index_col_name,...)
  | KEY [index_name] [index_type](index_col_name,...)
  | INDEX [index_name] [index_type](index_col_name,...)
  | [CONSTRAINT [symbol]] UNIQUE [INDEX]
        [index_name] [index_type](index_col_name,...)
  | [FULLTEXT|SPATIAL] [INDEX] [index_name](index_col_name,...)
  | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...)[reference_definition]
  | CHECK (expr)


type:
    TINYINT[(length)] [UNSIGNED][ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED][ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED][ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED][ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED][ZEROFILL]
  | DECIMAL(length,decimals) [UNSIGNED][ZEROFILL]
  | NUMERIC(length,decimals) [UNSIGNED][ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | CHAR(length) [BINARY | ASCII | UNICODE]
  | VARCHAR(length) [BINARY]
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
  | TEXT [BINARY]
  | MEDIUMTEXT [BINARY]
  | LONGTEXT [BINARY]
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)
  | spatial_type

index_col_name:
    col_name [(length)] [ASC |DESC]

reference_definition:
    REFERENCES tbl_name[(index_col_name,...)]
              [MATCH FULL | MATCH PARTIAL | MATCHSIMPLE]
              [ON DELETE reference_option]
              [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL| NO ACTION

table_options: table_option [table_option] ...

table_option:
    {ENGINE|TYPE} =engine_name
  | AUTO_INCREMENT = value
  | AVG_ROW_LENGTH = value
  | [DEFAULT] CHARACTER SET charset_name [COLLATEcollation_name]
  | CHECKSUM = {0 | 1}
  | COMMENT = 'string'
  | CONNECTION = 'connect_string'
  | MAX_ROWS = value
  | MIN_ROWS = value
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = 'string'
  | DELAY_KEY_WRITE = {0 | 1}
  | ROW_FORMAT ={DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | UNION = (tbl_name[,tbl_name]...)
  | INSERT_METHOD = { NO | FIRST | LAST }
  | DATA DIRECTORY = 'absolute path todirectory'
  | INDEX DIRECTORY = 'absolute path todirectory'

partition_options:
    PARTITION BY
          [LINEAR] HASH(expr)
        |  [LINEAR]KEY(column_list)
        |  RANGE(expr)
        |  LIST(column_list)
    [PARTITIONS num]
    [  SUBPARTITION BY
          [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
      [SUBPARTITIONS(num)] 
    ]
    [(partition_definition),[(partition_definition)], ...]

partition_definition:
    PARTITIONpartition_name
        [VALUES {
                  LESS THAN(expr) | MAXVALUE
                | IN (value_list) }]
        [[STORAGE] ENGINE [=] engine-name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] (tablespace_name)]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition),[(subpartition_definition)], ...]

subpartition_definition:
    SUBPARTITIONlogical_name
        [[STORAGE] ENGINE [=] engine-name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] (tablespace_name)]
        [NODEGROUP [=] node_group_id]

select_statement:
    [IGNORE | REPLACE] [AS]SELECT ...  (Some legal select statement)
CREATE TABLE用于创建带给定名称的表。您必须拥有表CREATE权限。


.字符列的定义可以包括一个CHARACTERSET属性，用来指定字符集，也可以指定列的整序。CHARSET是CHARACTER SET的同义词。
  CREATE TABLE t (
    c CHAR(20) CHARACTER SET utf8COLLATE utf8_bin
  );


对于一个给定的表，您可以使用SHOW CREATE TABLE语句来查看那些列有明确的DEFAULT子句。

  COMMENT    :对于列的评注。评注通过SHOW CREATE TABLE和SHOW FULL COLUMNS语句显示。

  SERIAL      : 属性SERIAL可以用作BIGINT UNSIGNED NOT NULLAUTO_INCREMENT UNIQUE的别名。

  KEY        : 通常是INDEX同义词。如果关键字属性PRIMARYKEY在列定义中已给定，则PRIMARY KEY也可以只指定为KEY。
                这么做的目的是与其它数据库系统兼容。

  UNIQUE      :在UNIQUE索引中，所有的值必须互不相同。如果您在添加新行时使用的关键字与原有行的关键字相同，
                则会出现错误。例外情况是，如果索引中的一个列允许包含NULL值，则此列可以包含多个NULL值。
        此例外情况不适用于BDB表。在BDB中，带索引的列只允许一个单一NULL。

  PRIMARY KEY : 是一个唯一KEY，此时，所有的关键字列必须定义为NOTNULL。如果这些列没有被明确地定义为NOT NULL，
                MySQL应隐含地定义这些列。一个表只有一个PRIMARYKEY。如果您没有PRIMARY KEY并且一个应用程序要求
        在表中使用PRIMARYKEY，则MySQL返回第一个UNIQUE索引，此索引没有作为PRIMARY KEY的NULL列。
             
      在已创建的表中，PRIMARYKEY的位置最靠前，然后是所有的UNIQUE索引，然后是非唯一索引。这可以帮助MySQL优化
  程序选择优先使用哪个索引，并且更快速的检测出重复的UNIQUE关键字。

  PRIMARY KEY可以是一个多列索引。但是，在列规约中使用PRIMARYKEY关键字属性无法创建多列索引。这么做只能把一个
  列标记为主列。您必须使用一个单独的PRIMARY KEY（index_col_name,...）子句。

  如果PRIMARYKEY或UNIQUE索引只包括一个列，并且此列为整数类型，则您也可以在SELECT语句中把此列作为_rowid引用。

  在MySQL中，PRIMARYKEY的名称为PRIMARY。对于其它索引，如果您没有赋予名称，则索引被赋予的名称与第一个已编入
  索引的列的名称相同，并自选添加后缀（_2, _3,...），使名称为唯一名称。您可以使用SHOWINDEX FROM tbl_name来查
  看表的索引名称。请参见13.5.4.11节，“SHOW INDEX语法”。

  部分存储引擎允许您在创建索引时指定索引类型。index_type指示语句的语法是USINGtype_name。


.部分存储引擎允许您在创建索引时指定索引类型。index_type指示语句的语法是USINGtype_name。
  CREATE TABLE lookup (
    id INT,
    INDEX USING BTREE (id)
  )
  ENGINE = MEMORY;


.重新设置AUTO_INCREMENT值
  ALTER TABLE tbl_name AUTO_INCREMENT = n;


.COMMENT 表的注释，最长60个字符。


.MySQL会对SELECT中的所有项创建新列。举例说明：
  CREATE TABLE test (
      a INT NOTNULL AUTO_INCREMENT,
      PRIMARYKEY (a), KEY(b)
  )
  TYPE=MyISAM SELECT b,c FROM test2;
 
  本语句用于创建含三个列（a, b,c）的MyISAM表。注意，用SELECT语句创建的列附在表的右侧，而不是覆盖在表


  mysql> CREATE TABLE bar (m INT)SELECT n FROM foo;
  Query OK, 1 row affected (0.02 sec)

  mysql> SELECT * FROM bar;
        +------+---+
        | m    | n|
        +------+---+
        | NULL | 1 |
        +------+---+
        1 row in set (0.00 sec)


.删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name；


.删除索引
DROP INDEX index_name ON tbl_name;


.删除表
DROP [TEMPORARY] TABLE [IF EXISTS]  tbl_name [,tbl_name] ...  [RESTRICT | CASCADE]；

例:删除一个表
  DROP TABLE IF EXISTS my_tbl1;
 
  删除多个表
  DROP TABLE IF EXISTS my_tbl1, my_tbl2;


.可以同时删除许多个表中的行，并使用其它的表进行搜索：
  DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.idAND t2.id=t3.id;
或：
  DELETE FROM t1, t2 USING t1, t2, t3 WHEREt1.id=t2.id AND t2.id=t3.id;


.向表中插入数据语法
INSERT INTO tbl_name (f1, f2, f3)  VALUES(val1,val2, val3);


.指定了ON DUPLICATE KEY UPDATE，插入行后会导致在一个UNIQUE索引或PRIMARYKEY中出现重复值，则执行旧行UPDATE。

例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATEc=c+1;

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEYUPDATE c=VALUES(a)+VALUES(b);


.Load data导入数据
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;


.如果需要LOAD DATA来从一个管道中读取，可以使用以下方法（此处我们把/目录清单载入一个表格）：
mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
find / -ls > /mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x
有些输入记录把原有的记录复制到唯一关键字值上。REPLACE和IGNORE关键字用于控制这些输入记录的操作。


.如果所有读入的行都含有一个共用前缀，则您可以使用'prefix_string'来跳过前缀（和前缀前的字符）。
如果某行不包括前缀，则整个行被跳过。注释：prefix_string会出现在一行的中间。

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test LINES STARTING BY"xxx";


.给定别名时，AS关键词是自选的
  SELECT CONCAT(last_name,', ',first_name) ASfull_name FROM mytable ORDER BY full_name;
或
  SELECT CONCAT(last_name,',',first_name)    full_name FROMmytable ORDER BY full_name;


.连接表(您不应对ON部分有任何条件。ON部分用于限定在结果集合中您想要哪些行。但是，您应在WHERE子句中指定这些条件)
  SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON(t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c);
相当于
  SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3CROSS JOIN t4)  ON (t2.a=t1.a AND t3.b=t1.b ANDt4.c=t1.c)；


.子查询
  SELECT * FROM t1 WHERE column1 = (SELECT column1FROM t2);


.多步子查询
DELETE FROM t1
WHERE s11 > ANY(
  SELECT COUNT(*) FROM t2  WHERENOT EXISTS(
    SELECT * FROM t3 WHEREROW(5*t2.s1,77)= (
        SELECT 50,11*s1 FROM t4 UNION SELECT 50,77FROM(
      SELECT *FROM t5) AS t5
    )
    )
  );


SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROMt2);

SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 <>ANY  (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 <> SOME(SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROMt2);