mysql 语法入门 三

7.4.13 与GROUP BY子句一起使用的函数

如果你在不包含GROUP BY子句的一个语句中使用聚合函数，它等价于聚合所有行。

mysql> select student.student_name,COUNT(*)
           from student,course
           where student.student_id=course.student_id
           GROUP BY student_name;

mysql> select COUNT(*) from student;
  

mysql> select COUNT(DISTINCT results) from student;

mysql> select student_name, AVG(test_score)
           from student
           GROUP BY student_name;
  

mysql> select student_name, MIN(test_score), MAX(test_score)
           from student
           GROUP BY student_name;
 

MySQL扩展了GROUP BY的用法。你可以不出现在的GROUP BY部分的SELECT表达式中使用列或计算，这表示这个组的任何可能值。你可以使用它是性能更好，避免在不必要的项目上排序和分组。例如，你在下列查询中不需要在customer.name上聚合：

mysql> select order.custid,customer.name,max(payments)
       from order,customer
       where order.custid = customer.custid
       GROUP BY order.custid;

在 ANSI SQL中，你将必须将customer.name加到GROUP BY子句。在MySQL中，名字是冗余的。

如果你从GROUP BY部分省略的列在组中不是唯一的，不要使用这个功能。

在某些情况下，你可以使用MIN()和MAX()获得一个特定的列值，即使它不是唯一的。下例给出从包含sort列中最小值的行的column值：

substr(MIN(concat(sort,space(6-length(sort)),column),7,length(column)))

注意，如果你正在使用MySQL 3.22(或更早)或如果你正在试图遵从ANSI SQL，你不能在GROUP BY或ORDER BY子句中使用表达式。你可以通过使用表达式的一个别名解决此限制：

mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name
           GROUP BY id,val ORDER BY val;

在MySQL3.23中，你可以这样做：

mysql> SELECT id,FLOOR(value/100) FROM tbl_name ORDER BY RAND();

7.5 CREATE DATABASE句法

CREATE DATABASE db_name

CREATE DATABASE用给定的名字创建一个数据库。允许的数据库名字规则在7.1.5 数据库、桌子、索引、列和别名命名中给出。如果数据库已经存在，发生一个错误。

在MySQL中的数据库实现成包含对应数据库中表的文件的目录。因为数据库在初始创建时没有任何表，CREATE DATABASE语句只是在MySQL数据目录下面创建一个目录。

你也可以用mysqladmin创建数据库。见12.1 不同的MySQL程序的概述。

7.6 DROP DATABASE句法

DROP DATABASE [IF EXISTS] db_name

DROP DATABASE删除数据库中的所有表和数据库。要小心地使用这个命令！

DROP DATABASE返回从数据库目录被删除的文件的数目。通常，这3倍于表的数量，因为每张表对应于一个“.MYD”文件、一个“.MYI”文件和一个“.frm”文件。

在MySQL 3.22或以后版本中，你可以使用关键词IF EXISTS阻止一个错误的发生，如果数据库不存在。

你也可以用mysqladmin丢弃数据库。见12.1 不同的 MySQL 程序的概述。

7.7 CREATE TABLE句法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] (index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [INDEX] [index_name] (index_col_name,...)
  or    [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  or    CHECK (expr)

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

index_col_name:
        col_name [(length)]

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

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
	TYPE = {ISAM | MYISAM | HEAP}
or	AUTO_INCREMENT = #
or	AVG_ROW_LENGTH = #
or	CHECKSUM = {0 | 1}
or	COMMENT = "string"
or	MAX_ROWS = #
or	MIN_ROWS = #
or	PACK_KEYS = {0 | 1}
or	PASSWORD = "string"
or	DELAY_KEY_WRITE = {0 | 1}
or      ROW_FORMAT= { default | dynamic | static | compressed }

select_statement:
	[IGNORE | REPLACE] SELECT ...  (Some legal select statement)

CREATE TABLE在当前数据库中用给出的名字创建一个数据库表。允许的表名的规则在7.1.5 数据库，桌子，索引，列和别名命名中给出。如果当前数据库不存在或如果表已经存在，出现一个错误。

在MySQL3.22或以后版本中，表名可以被指定为db_name.tbl_name，不管有没有当前的数据库都可以。

在MySQL3.23中，当你创建一张表时，你可以使用TEMPORARY关键词。如果一个连接死掉，临时表将自动被删除，并且其名字是按连接命名。这意味着，2个不同的连接能使用相同的暂时表的名字而不会彼此冲突或与相同名字的现有数据库表冲突。（现有的表被隐蔽直到临时表被删除）。

在MySQL3.23或以后版本中，你可以使用关键词IF NOT EXISTS以便如果表已经存在不发生一个错误。注意，无法证实表结构是相同的。

每张表tbl_name由在数据库目录的一些文件表示。在MyISAM类型的表的情况下，你将得到：

对于各种列类型的性质的更多信息，见7.3 列类型。

• 如果既不指定NULL也不指定NOT NULL，列被视为指定了NULL。
• 整型列可以有附加的属性AUTO_INCREMENT。当你插入NULL值（推荐)或0到一个AUTO_INCREMENT列中时，列被设置为value+1，在此value是当前表中的列的最大值。AUTO_INCREMENT顺序从1开始。见20.4.29 mysql_insert_id()。如果你删除了包含一个AUTO_INCREMENT列的最大值的行，值将被重新使用。如果你删除表中所有的行，顺序重新开始。注意：每个表只能有一个AUTO_INCREMENT列，并且它必须被索引。为了使做MySQL兼容一些 ODBC 应用程序，用下列查询你可以找出最后插入的行：

  SELECT * FROM tbl_name WHERE auto_col IS NULL
  

• NULL值对于TIMESTAMP列的处理不同于其他列类型。你不能在一个TIMESTAMP列中存储一个文字NULL；设置列为NULL将把它设成当前的日期和时间。因为TIMESTAMP列表现就这样，NULL和NOT NULL属性不以一般方式运用并且如果你指定它们，将被忽略。在另一方面，为了使它MySQL客户更容易地使用TIMESTAMP列，服务器报告这样的列可以被赋值NULL( 它是对的)，尽管TIMESTAMP实际上绝不包含一个NULL值。当你使用DESCRIBE tbl_name得到有关你的表的描述时，你就会明白。注意，设置一个TIMESTAMP列为0不同于将它设置为NULL，因为0是一个有效的TIMESTAMP值。
• 如果没有为列指定DEFAULT值，MySQL自动地分配一个。如果列可以取NULL作为值，缺省值是NULL。如果列被声明为NOT NULL，缺省值取决于列类型：
  • 对于没有声明AUTO_INCREMENT属性的数字类型，缺省值是0。对于一个AUTO_INCREMENT列，缺省值是在顺序中的下一个值。
  • 对于除TIMESTAMP的日期和时间类型，缺省值是该类型适当的“零”值。对于表中第一个TIMESTAMP列，缺省值是当前的日期和时间。见7.3.6 日期和时间类型。
  • 对于除ENUM的字符串类型，缺省是空字符串。对于ENUM，缺省值是第一个枚举值。
• KEY是INDEX的一个同义词。
• 在MySQL中，一个UNIQUE键只能有不同的值。如果你试图用匹配现有行的键来增加新行，发生一个错误。
• A PRIMARY KEY是一个唯一KEY，它有额外的限制，即所有的关键列必须被定义为NOT NULL。在MySQL中，键被命名为PRIMARY。一张表只能有一个PRIMARY KEY。如果在表中你没有一个PRIMARY KEY并且一些应用程序要求PRIMARY KEY，MySQL将返回第一个UNIQUE键，它没有任何NULL列，作为PRIMARY KEY。
• 一个PRIMARY KEY可以是一个多列索引。然而，你不能在一个列说明中使用PRIMARY KEY的关键字属性创建一个多列索引。这样做将仅仅标记单个列作为主键。你必须使用PRIMARY KEY(index_col_name, ...)语法。
• 如果你不能给索引赋予一个名字，这个索引将赋予与第一个index_col_name相同的名字，用一个可选的suffix(_2, _3, ...)使它唯一。你能使用SHOW INDEX FROM tbl_name看到一张表的索引名字。见7.21 SHOW句法(得到表、列等的信息）。
• 只有MyISAM表类型支持可以有NULL值的列上的索引。在其他情况下，你必须声明这样的列为NOT NULL，否则导致一个错。
• 用col_name(length)语法，你可以指定仅使用部分的CHAR或VARCHAR列的一个索引。这能使索引文件变得更小。见7.3.9 列索引。
• 只有MyISAM表类型支持BLOB和TEXT列的索引。当在一个BLOB或TEXT列上放置索引时，你必须总是指定索引的长度：

  CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
  

• 当你与TEXT或BLOB列一起使用ORDER BY或GROUP BY时，只使用头max_sort_length个字节。见7.3.7.2 BLOB和TEXT类型。
• FOREIGN KEY、CHECK和REFERENCES子句实际上不做任何事情，其语法仅仅提供兼容性，使得它更容易从其他的SQL服务器移植代码并运行借助引用创建表的应用。见5.4 MySQL缺少的功能。
• 每个NULL列占据额外一位，取舍到最接近的字节。
• 最大记录长度以字节计可以如下计算：

  row length = 1
               + (sum of column lengths)
               + (number of NULL columns + 7)/8
               + (number of variable-length columns)
  

• table_options和SELECT选项只在MySQL 3.23和以后版本中被实现。不同的表类型是：

  ISAM	原来的表处理器
  MyISAM	全新二进制可移植的表处理器
  HEAP	用于该表的数据仅仅存储在内存中

  见9.4 MySQL 表类型。其他表选项被用来优化表的行为。在大多数情况下，你不必指定他们任何一个。选项对所有表都适用，如果不是则说明。

  AUTO_INCREMENT	你想要为你的表设定的下一个 auto_increment 值 ( MyISAM ）
  AVG_ROW_LENGTH	你的表的平均行长度的近似值。你只需要为有变长记录的表设置它。
  CHECKSUM	如果你想要MySQL对每行维持一个校验和(使表变得更慢以更新但是使它更容易找出损坏的表)设置它为1 ( MyISAM )
  COMMENT	对于你的表的一篇60个字符的注释
  MAX_ROWS	你计划在表中存储的行的最大数目
  MIN_ROWS	你计划在表中存储的行的最小数目
  PACK_KEYS	如果你想要有更小的索引，将它设为1。这通常使的更新更慢并且读取更快(MyISAM，ISAM)。
  PASSWORD	用一个口令加密.frm文件。该选项在标准MySQL版本中不做任何事情。
  DELAY_KEY_WRITE	如果想要推迟关键表的更新直到表被关闭(MyISAM)，将它设置为1。
  ROW_FORMAT	定义行应该如何被存储(为了将来)。

  当你使用一个MyISAM表时，MySQL使用max_rows * avg_row_length的乘积决定最终的表将有多大。如果你不指定上面的任何选项，对一个表的最大尺寸将是4G(或2G，如果你的操作系统仅支持2G的表)。

• 如果你在CREATE语句后指定一个SELECT，MySQL将为在SELECT中所有的单元创键新字段。例如：

  mysql> CREATE TABLE test (a int not null auto_increment,
             primary key (a), key(b))
             TYPE=HEAP SELECT b,c from test2;
  

  这将创建一个有3个列的HEAP表。注意如果在拷贝数据进表时发生任何错误，表将自动被删除。

7.7.1 隐含的列说明改变

在某些情况下，MySQL隐含地改变在一个CREATE TABLE语句给出的一个列说明。（这也可能在ALTER TABLE。）

• 长度小于4的VARCHAR被改变为CHAR。
• 如果在一个表中的任何列有可变长度，结果是整个行是变长的。因此, 如果一张表包含任何变长的列(VARCHAR、TEXT或BLOB)，所有大于3个字符的CHAR列被改变为VARCHAR列。这在任何方面都不影响你如何使用列；在MySQL中，VARCHAR只是存储字符的一个不同方法。MySQL实施这种改变，是因为它节省空间并且使表操作更快捷。见10.6 选择一种表格类型。
• TIMESTAMP的显示尺寸必须是偶数且在2 ～ 14的范围内。如果你指定0显示尺寸或比14大，尺寸被强制为14。从1～13范围内的奇数值尺寸被强制为下一个更大的偶数。
• 你不能在一个TIMESTAMP列里面存储一个文字NULL；将它设为NULL将设置为当前的日期和时间。因为TIMESTAMP列表现就是这样，NULL和NOT NULL属性不以一般的方式运用并且如果你指定他们，将被忽略。DESCRIBE tbl_name总是报告该TIMESTAMP列可能赋予了NULL值。
• MySQL将其他SQL数据库供应商使用的某个列类型映射到MySQL类型。见7.3.11 只用其他数据库引擎的类型。

如果你想要知道MySQL是否使用了除你指定的以外的一种列类型，在创建或改变你的表之后，发出一个DESCRIBE tbl_name语句即可。

如果你使用myisampack压缩一个表，可能会发生改变某些其他的列类型。见10.6.3 压缩表的特征。 

7.8 ALTER TABLE句法

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
  or    MODIFY [COLUMN] create_definition
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    RENAME [AS] new_tbl_name
  or    table_options

ALTER TABLE允许你修改一个现有表的结构。例如，你可以增加或删除列、创造或消去索引、改变现有列的类型、或重新命名列或表本身。你也能改变表的注释和表的类型。见7.7 CREATE TABLE句法。

如果你使用ALTER TABLE修改一个列说明但是DESCRIBE tbl_name显示你的列并没有被修改，这可能是MySQL因为在7.7.1 隐含的列说明改变中描述的原因之一而忽略了你的修改。例如，如果你试图将一个VARCHAR改为CHAR，MySQL将仍然使用VARCHAR，如果表包含其他变长的列。

ALTER TABLE通过制作原来表的一个临时副本来工作。修改在副本上施行，然后原来的表被删除并且重新命名一个新的。这样做使得所有的修改自动地转向到新表，没有任何失败的修改。当ALTER TABLE正在执行时，原来的桌可被其他客户读取。更新和写入表被延迟到新表准备好了为止。

• 为了使用ALTER TABLE，你需要在表上的select、insert、delete、update、create和drop的权限。
• IGNORE是MySQL对ANSI SQL92 的一个扩充，如果在新表中的唯一键上有重复，它控制ALTER TABLE如何工作。如果IGNORE没被指定，副本被放弃并且恢复原状。如果IGNORE被指定，那么对唯一键有重复的行，只有使用第一行；其余被删除。
• 你可以在单个ALTER TABLE语句中发出多个ADD、ALTER、DROP和CHANGE子句。这是MySQL对ANSI SQL92的一个扩充，SQL92在每个ALTER TABLE语句中只允许一个子句。
• CHANGE col_name、DROP col_name和DROP INDEX是MySQL对 ANSI SQL92 的扩充。
• MODIFY是 Oracle 对ALTER TABLE的扩充。
• 可选的词COLUMN是一个纯粹的噪音且可以省略。
• 如果你使用ALTER TABLE tbl_name RENAME AS new_name而没有任何其他选项，MySQL简单地重命名对应于表tbl_name的文件。没有必要创建临时表。
• create_definition子句使用CREATE TABLE相同的ADD和CHANGE语法。注意语法包括列名字，不只列类型。见7.7 CREATE TABLE句法。
• 你可以使用CHANGE old_col_name create_definition子句重命名一个列。为了这样做，指定旧的和新的列名字和列当前有的类型。例如，重命名一个INTEGER列，从a到b，你可以这样做：

  mysql> ALTER TABLE t1 CHANGE a b INTEGER;
  

  如果你想要改变列的类型而非名字，就算他们是一样的，CHANGE语法仍然需要2个列名。例如：

  mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
  

  然而，在MySQL3.22.16a，你也可以使用MODIFY来改变列的类型而不是重命名它：

  mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
  

• 如果你使用CHANGE或MODIFY缩短一个列，一个索引存在于该列的部分(例如，如果你有一个VARCHAR列的头10个字符的索引)，你不能使列短于被索引的字符数目。
• 当你使用CHANGE或MODIFY改变一个列类型时，MySQL尽可能试图很好地变换数据到新类型。
• 在MySQL3.22或以后，你能使用FIRST或ADD ... AFTER col_name在一个表的行内在一个特定的位置增加列。缺省是增加到最后一列。
• ALTER COLUMN为列指定新的缺省值或删除老的缺省值。如果老的缺省值被删除且列可以是NULL，新缺省值是NULL。如果列不能是NULL，MySQL赋予一个缺省值。缺省值赋值在7.7 CREATE TABLE句法中描述。
• DROP INDEX删除一个索引。这是MySQL对 ANSI SQL92 的一个扩充。
• 如果列从一张表中被丢弃，列也从他们是组成部分的任何索引中被删除。如果组成一个索引的所有列被丢弃，该索引也被丢弃。
• DROP PRIMARY KEY丢弃主索引。如果这样的索引不存在，它丢弃表中第一个UNIQUE索引。(如果没有明确地指定PRIMARY KEY，MySQL标记第一个UNIQUE键为PRIMARY KEY。）
• 用 C API 函数mysql_info()，你能找出多少记录被拷贝， 和(当使用IGNORE时)由于唯一键值的重复多少记录被删除。
• FOREIGN KEY、CHECK和REFERENCES子句实际上不做任何事情，他们的句法仅仅提供兼容性，使得更容易地从其他SQL服务器移植代码并且运行借助引用来创建表的应用程序。见5.4 MySQL缺少的功能。

这里是一个例子，显示了一些ALTER TABLE用法。我们以一个如下创建的表t1开始：

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

重命名表，从t1到t2:

mysql> ALTER TABLE t1 RENAME t2;

为了改变列a，从INTEGER改为TINYINT NOT NULL(名字一样)，并且改变列b，从CHAR(10)改为CHAR(20)，同时重命名它，从b改为c：

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

增加一个新TIMESTAMP列，名为d：

mysql> ALTER TABLE t2 ADD d TIMESTAMP; 

在列d上增加一个索引，并且使列a为主键：

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

删出列c：

mysql> ALTER TABLE t2 DROP COLUMN c;

增加一个新的AUTO_INCREMENT整数列，命名为c：

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);

注意，我们索引了c，因为AUTO_INCREMENT柱必须被索引，并且另外我们声明c为NOT NULL，因为索引了的列不能是NULL。

当你增加一个AUTO_INCREMENT列时，自动地用顺序数字填入列值。

7.9 OPTIMIZE TABLE句法

OPTIMIZE TABLE tbl_name

如果你删除了一个表的大部分或如果你用变长的行对一个表(有VARCHAR、BLOB或TEXT列的表)做了改变，应该使用OPTIMZE TABLE。删除的记录以一个链接表维持并且随后的INSERT操作再次使用老记录的位置。你可以使用OPTIMIZE TABLE回收闲置的空间。

OPTIMIZE TABLE通过制作原来的表的一个临时副本来工作。老的表子被拷贝到新表中(没有闲置的行)，然后原来的表被删除并且重命名一个新的。这样做使得所有更新自动转向新的表，没有任何失败的更新。当时OPTIMIZE TABLE正在执行时，原来的表可被另外的客户读取。对表的更新和写入延迟到新表是准备好为止。

7.10 DROP TABLE句法

DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]

DROP TABLE删除一个或多个数据库表。所有表中的数据和表定义均被删除，故小心使用这个命令！

在MySQL 3.22或以后版本，你可以使用关键词IF EXISTS类避免不存在表的一个错误发生。