mysql 语法入门 3
GROUP BY
子句一起使用的函数如果你在不包含GROUP BY
子句的一个语句中使用聚合函数,它等价于聚合所有行。
COUNT(expr)
返回由一个SELECT
语句检索出来的行的非NULL
值的数目。mysql> select student.student_name,COUNT(*) from student,course where student.student_id=course.student_id GROUP BY student_name;
COUNT(*)
在它返回的检索出来的行数目上有些不同,不管他们是否包含NULL
值。如果SELECT
从一个表检索,或没有检索出其他列并且没有WHERE
子句,COUNT(*)
被优化以便快速地返回。例如:
mysql> select COUNT(*) from student;
COUNT(DISTINCT expr,[expr...])
返回一个不同值的数目。mysql> select COUNT(DISTINCT results) from student;
在MySQL中,你可以通过给出一个表达式列表以得到不同的表达式组合的数目。在 ANSI SQL中,你可能必须在CODE(DISTINCT ..)
内进行所有表达式的连接。
AVG(expr)
返回expr
的平均值。mysql> select student_name, AVG(test_score) from student GROUP BY student_name;
MIN(expr)
MAX(expr)
返回expr
的最小或最大值。MIN()
和MAX()
可以有一个字符串参数;在这种的情况下,他们返回最小或最大的字符串值。mysql> select student_name, MIN(test_score), MAX(test_score) from student GROUP BY student_name;
SUM(expr)
返回expr
的和。注意,如果返回的集合没有行,它返回NULL! STD(expr)
STDDEV(expr)
返回expr
标准差(deviation)。这是对 ANSI SQL 的扩展。该函数的形式STDDEV()
是提供与Oracle的兼容性。 BIT_OR(expr)
返回expr
里所有位的位或。计算用 64 位(BIGINT
)精度进行。 BIT_AND(expr)
返回expr
里所有位的位与。计算用 64 位(BIGINT
)精度进行。 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;
在MySQL
3.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程序的概述。
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 程序的概述。
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 DEFAULTtable_options:TYPE = {ISAM | MYISAM | HEAP}orAUTO_INCREMENT = #orAVG_ROW_LENGTH = #orCHECKSUM = {0 | 1}orCOMMENT = "string"orMAX_ROWS = #orMIN_ROWS = #orPACK_KEYS = {0 | 1}orPASSWORD = "string"orDELAY_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类型的表的情况下,你将得到:
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
表。注意如果在拷贝数据进表时发生任何错误,表将自动被删除。
在某些情况下,MySQL隐含地改变在一个CREATE TABLE
语句给出的一个列说明。(这也可能在ALTER TABLE
。)
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 压缩表的特征。?
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
列时,自动地用顺序数字填入列值。
OPTIMIZE TABLE
句法OPTIMIZE TABLE tbl_name
如果你删除了一个表的大部分或如果你用变长的行对一个表(有VARCHAR
、BLOB
或TEXT
列的表)做了改变,应该使用OPTIMZE TABLE
。删除的记录以一个链接表维持并且随后的INSERT
操作再次使用老记录的位置。你可以使用OPTIMIZE TABLE
回收闲置的空间。
OPTIMIZE TABLE
通过制作原来的表的一个临时副本来工作。老的表子被拷贝到新表中(没有闲置的行),然后原来的表被删除并且重命名一个新的。这样做使得所有更新自动转向新的表,没有任何失败的更新。当时OPTIMIZE TABLE
正在执行时,原来的表可被另外的客户读取。对表的更新和写入延迟到新表是准备好为止。
DROP TABLE
句法DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]
DROP TABLE
删除一个或多个数据库表。所有表中的数据和表定义均被删除,故小心使用这个命令!
在MySQL 3.22或以后版本,你可以使用关键词IF EXISTS
类避免不存在表的一个错误发生。?