正文

mysql学习笔记2007-10-12 17:30:00

【评论】 【打印】 【字体：大 中 小】 本文链接：http://blog.pfan.cn/hurongliang/30039.html

分享到：

set列类型的用法
binary 和char的区别


mysql学习笔记：
=================
字符串:
字符串可以用单引号或双引号引起来。
字符串中的转义字符以'\'开始，如下：
\0 ASCII 0(NUL)字符。
 
\' 单引号(‘'’)。
 
\" 双引号(‘"’)。
 
\b 退格符。
 
\n 换行符。
 
\r 回车符。
 
\t tab字符。
 
\Z ASCII 26(控制（Ctrl）-Z)。该字符可以编码为‘\Z’，以允许你解决在
 Windows中ASCII 26代表文件结尾这一问题。(如果你试图使用mysql db_name
 < file_name，ASCII 26会带来问题）。
 
\\ 反斜线(‘\’)字符。
 
\% %
 
\_ _
转义字符对大小写敏感，\b’解释为退格，但‘\B’解释为‘B’。

在字符串内用‘"’引用的‘'’不需要特殊处理，不需要用双字符或转义。同样，在字符串内用‘'’引用的‘"’也不需要特殊处理。
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
=================================
十六进制值
在数字上下文中，十六进制数如同整数。在字符串上下文，如同字符串，每对十六进制数字被转换为一个字符：
十六进制值的默认类型是字符串。如果想要确保该值作为数字处理，可以使用CAST(...AS UNSIGNED)：
mysql> SELECT 0x41,0x41+10,CAST(0x41 AS UNSIGNED);
+------+---------+------------------------+
| 0x41 | 0x41+10 | CAST(0x41 AS UNSIGNED) |
+------+---------+------------------------+
| A    |      75 |                     65 |
+------+---------+------------------------+

可以用HEX()函数将一个字符串或数字转换为十六进制格式的字符串：
mysql> select hex('rolia');
+--------------+
| hex('rolia') |
+--------------+
| 726F6C6961   |
+--------------+
===============================
boolean类型
MYSQL保存BOOLEAN值时用1代表TRUE,0代表FALSE，boolean在MySQL里的类型为tinyint(1),
MySQL里有四个常量：true,false,TRUE,FALSE,它们分别代表1,0,1,0，
mysql> select true,false,TRUE,FALSE;
+------+-------+------+-------+
| TRUE | FALSE | TRUE | FALSE |
+------+-------+------+-------+
|    1 |     0 |    1 |     0 |
+------+-------+------+-------+
可以如下插入boolean值：insert into [xxxx(xx)] values(true)，当然也可以values(1);
举例如下：
mysql> alter table test add isOk boolean;
Query OK
mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| isOk  | tinyint(1)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
mysql> insert into test(isOk) values(true);
Query OK
mysql> select isOk from test ;
+------+
| isOk |
+------+
|    1 |
+------+
=================
位字段值BIT
位字段值在MySQL里的类型为bit([n]),n为位数，不指定则为默认值1。
mysql> create table t_bit(b bit(4));
Query OK, 0 rows affected (0.44 sec)
mysql> desc t_bit;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| b     | bit(4) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
插入时用b'value'符号写位字段值，value是一个用0和1写成的二进制值。
mysql> insert into t_bit set b=b'101';
查询时用函数bin([column]+0)进行查询。
mysql> select bin(b+0) from t_bit;
+----------+
| bin(b+0) |
+----------+
| 100      |
| 101      |
+----------+
======================
大小写敏感
操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。
大多数Unix中数据库名和表名对大小写敏感，而在Windows中对大小写不敏感。
这样会导致操作数据库名和表名时出现不一致现象。
在MySQL中如何在硬盘上保存和使用表名和数据库名由lower_case_tables_name系统变量确定，可以在启动mysqld时设置。
lower_case_tables_name可以采用下面的任一值：
值 含义
 
0 使用CREATE TABLE或CREATE DATABASE语句指定的大写和小写在硬盘上保存表名和数据库名。
 称比较对大小写敏感。在Unix系统中的默认设置即如此。
 请注意如果在大小写不敏感的文件系统上用--lower-case-table-names=0强制设为0，
 并且使用不同的大小写访问MyISAM表名，会导致索引破坏。
 
1 表名在硬盘上以小写保存，名称比较对大小写敏感。
 MySQL将所有表名转换为小写以便存储和查找。
 该行为也适合数据库名和表的别名。
 该值为Windows和Mac OS X系统中的默认值。
 
2 表名和数据库名在硬盘上使用CREATE TABLE或CREATE DATABASE语句指定的大小写进行保存，
 但MySQL将它们转换为小写以便查找。名称比较对大小写敏感。
 注释：只在对大小写不敏感的文件系统上适用!
  InnoDB表名以小写保存，例如lower_case_tables_name=1。
想避免由于数据库或表名的大小写造成的数据转移问题，可使用两个选项：
·在任何系统中可以使用lower_case_tables_name=1。
使用该选项的不利之处是当使用SHOW TABLES或SHOW DATABASES时，看不出名字原来是用大写还是小写。
·在Unix中使用lower_case_tables_name=0，在Windows中使用lower_case_tables_name=2。
这样了可以保留数据库名和表名的大小写。不利之处是必须确保在Windows中查询总是用正确大小写引用数据库名和表名。

例外：如果你正使用InnoDB表，在任何平台上均应将lower_case_tables_name设置为1，以强制将名转换为小写。
请注意在Unix中将lower_case_tables_name设置为1之前，重启mysqld之前，必须先将旧的数据库名和表名转换为小写。
=====================
系统变量
查询方法有：
select @@[xxx]
select @@global.[xxx]
select @@session.[xxx]
show variables
show global variables
show session variables
当你用SELECT @@var_name搜索一个变量时，MySQL返回SESSION值（如果存在），否则返回GLOBAL值。
对于SHOW VARIABLES，如果不指定GLOBAL、SESSION或者LOCAL，MySQL返回SESSION值。
设置方法同查询方法雷同
set @@[scope].[xxx]=[value]
如果设置变量时不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION。
========================
注释
MySQL有三种注释方法:
1.从‘#’字符从行尾。
2.从‘-- ’序列到行尾。请注意‘-- ’(双破折号)注释风格要求第2个破折号后面至少跟一个空格符(例如空格、tab、换行符等等)。
3.从/*序列到后面的*/序列。结束序列不一定在同一行中，因此该语法允许注释跨越多行。
===================
字符集
可以用SHOW CHARACTER SET语句查看当前版本的MYSQL所支持的字符集；
character_set_system服务器用来保存识别符的字符集。该值一定是utf8,不可更改。
default-character-set和default-collation设置服务器级别的字符集和校对规则；
character_set_server和collation_server设置当前的服务器字符集和校对规则，运行时可以改变这些值；
character_set_database和collation_database设置默认数据库的字符集和校对规则。无论何时默认数据库更改了，
服务器都设置这两个变量的值。如果没有 默认数据库，这两个变量被设置为相应的服务器级别的变量(character_set_server和collation_server)。
character_set_client设置客户端查询语句的字符集；
character_set_connection设置从客户端的查询语句字符集要转换成什么字符集，
服务器将客户端发送的查询从character_set_client系统变量转换到character_set_connection；
character_set_results变量指示服务器返回查询结果到客户端使用的字符集。

default-character-set将成为一下几种设置的默认值：
character_set_client
character_set_connection
character_set_database
character_set_results
character_set_server

有两个语句影响连接字符集：
SET NAMES 'charset_name'
SET CHARACTER SET charset_name

SET NAMES 'x'语句与这三个语句等价：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x;

SET CHARACTER SET x语句与这三个语句等价：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;

每一个数据库有一个数据库字符集和一个数据库校对规则，它不能够为空。
eg:CREATE DATABASE [db_name] DEFAULT CHARACTER SET [charset_name] COLLATE [collation_name]。
如果两个都没有指定，则使用默认数据库的字符集和校对规则(character_set_database和 collation_database)。

每一个表有一个表字符集和一个校对规则，它不能为空。
eg:CREATE TABLE tbl_name (column_list) DEFAULT CHARACTER SET [charset_name] COLLATE [collation_name]
如果两个都没有指定，则使用数据库字符集和校对规则作为默认值(character_set_database和 collation_database)。

每一个“字符”列（即，CHAR、VARCHAR或TEXT类型的列）有一个列字符集和一个列 校对规则，它不能为空。
eg:col_name {CHAR | VARCHAR | TEXT} (col_length) CHARACTER SET [charset_name] COLLATE [collation_name]
如果两个都没有指定，则采用表字符集和校对规则。

====================
数值类型

各个整数类型存储的范围
TINYINT
1字节  -128~127
SMALLINT
 2字节  -32768~ 32767
 MEDIUMINT
 3字节  -8388608~ 8388607
INT
 4字节  -2147483648～ 2147483647
 BIGINT
 8字节  -9223372036854775808～ 9223372036854775807
 
惯例：
M 表示最大显示宽度。最大有效显示宽度是255。
D 适用于浮点和定点类型，并表示小数点后面的位数。最大可能的值是30，但不应大于M-2。
 方括号(‘[’和‘]’)表示可选部分。

BIT[(M)]
位字段类型。M表示每个值的位数，范围为从1到64。如果M被省略， 默认为1。

BOOL，BOOLEAN
是TINYINT(1)的同义词。zero值被视为假。非zero值视为真。

INT[(M)] [UNSIGNED] [ZEROFILL]
普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。
M表示客显示宽度。用于显示宽度小于指定的列宽度的值时从左侧填满宽度。
显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。
如果实际值的宽度大于M，则原样显示或保存，如果宽度不大于M，则从左侧填满宽度M。

INTEGER[(M)] [UNSIGNED] [ZEROFILL]
这是INT的同义词。

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
很小的整数。带符号的范围是-128到127。无符号的范围是0到255。

 SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
小的整数。带符号的范围是-32768到32767。无符号的范围是0到65535。

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
中等大小的整数。带符号的范围是-8388608到8388607。无符号的范围是0到16777215。

BIGINT[(M)] [UNSIGNED] [ZEROFILL]
大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
小(单精度)浮点数。允许的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38。
这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些区别。
(M,D)表示该值一共允许M位整数，其中D表示显示小数点后的位数。不足补0，超过则四舍五入。
如：float(6,3);
1234.12 ERROR:整数位最多3位。
123.12 显示123.120
123.456 显示123.46
如果M和D被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。
使用浮点数可能会遇到意想不到的问题，因为在MySQL中的所有计算用双精度完成。

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
普通大小(双精度)浮点数。允许的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到 1.7976931348623157E+308。
这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些区别。
。如果M和D被省略，根据硬件允许的限制来保存值。双精度浮点数精确到大约15位小数位。

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]
为DOUBLE的同义词。

FLOAT(p) [UNSIGNED] [ZEROFILL]
浮点数。p表示精度（以位数表示），但MySQL只使用该值来确定是否结果列的数据类型为FLOAT或DOUBLE。
如果p为从0到24，数据类型变为没有M或D值的FLOAT。如果p为从25到53，数据类型变为没有M或D值的DOUBLE。
结果列范围与本节前面描述的单精度FLOAT或双精度DOUBLE数据类型相同。

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
压缩的“严格”定点数。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。小数点和(负数)的‘-’符号不包括在M中。
如果D是0，则值没有小数点或分数部分。DECIMAL整数最大位数(M)为65。支持的十进制数的最大位数(D)是30。
如果D被省略， 默认是0。如果M被省略， 默认是10。
DECIMAL(M)等价于DECIMAL(M,0)

NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
同DECIMAL

 DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
是DECIMAL的同义词。

=====================================
日期和时间类型

每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。TIMESTAMP类型有专有的自动更新特性。

MySQL使用以下规则解释两位年值：
0-99范围的年值转换为1970-1999。
00-69范围的年值转换为2000-2069。

日期总是以年-月-日顺序(例如，'98-09-04')。

当 MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法的值时(如本节开始所描述)，它将该值转换为该类的“零”值。
一个例外是超出范围的TIME值被裁剪到TIME范围的相应端点。

各类“零”值的格式如下：
列类型   “零”值
DATETIME  '0000-00-00 00:00:00'
DATE   '0000-00-00'
TIMESTAMP  00000000000000
TIME   '00:00:00'
YEAR   0000

 “零”值是特殊值，你可以使用值0来保存或引用，写起来更容易。
 insert  into t1 set time_test=0

DATE
日期。支持的范围为'1000-01-01'到'9999-12-31'。MySQL以'YYYY-MM-DD'格式显示DATE值

TIME
时间。范围是'-838:59:59'到'838:59:59'。MySQL以'HH:MM:SS'格式显示TIME值
当你只需要日期值而不需要时间部分时应使用DATE类型。MySQL用'YYYY-MM-DD'格式检索和显示DATE值。
支持的范围是'1000-01-01'到 '9999-12-31'。

DATETIME
日期和时间的组合。支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。MySQL以'YYYY-MM-DD HH:MM:SS'格式显示DATETIME值
当你需要同时包含日期和时间信息的值时则使用DATETIME类型。
MySQL以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。
支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。(“支持”表示尽管先前的值可能工作，但没有保证)。

可以使用任何常见格式指定DATETIME、DATE和TIMESTAMP值：
'YYYY-MM-DD HH:MM:SS'或'YY-MM-DD HH:MM:SS'格式的字符串。
MM DD HH MM SS可以是单位数如2007-8-9 12:8:3
'YYYY-MM-DD'或'YY-MM-DD'格式的字符串。
 'YYYYMMDDHHMMSS'或'YYMMDDHHMMSS'格式的没有间割符的字符串。
   YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字。
  'YYYYMMDD'或'YYMMDD'格式的没有间割符的字符串。
  YYYYMMDD或YYMMDD格式的数字。
  无效DATETIME、DATE或者TIMESTAMP值被转换为相应类型的“零”值('0000-00-00 00:00:00'、'0000-00-00'或者00000000000000)。
  数字值应为6、8、12或者14位长。
  如果一个数值是8或14位长，则假定为YYYYMMDD或YYYYMMDDHHMMSS格式。
  如果数字 是6或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式。
  其它数字被解释为仿佛用零填充到了最近的长度。

尽管可以使用相同的格式指定DATETIME、DATE和TIMESTAMP值，不同类型的值的范围却不同。
例如，TIMESTAMP值不能早于1970或晚于2037。

不同的时间值分配规则：
DATE值分配给DATETIME或TIMESTAMP，结果值的时间部分被设置为'00:00:00'。
DATETIME或TIMESTAMP值分配给DATE，结果值的时间部分被删除。


TIMESTAMP[(M)]
范围是'1970-01-01 00:00:00'到2037年。
TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。
关于timestamp 列的default属性：
用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句，列为默认值使用当前的时间戳，并且自动更新。
如：alter table  table_test add ts timestamp default current_timestamp on update current_timestamp
default 和 on update 都不用和上面的相同。
如：alter table  table_test add ts timestamp
只用DEFAULT CURRENT_TIMESTAMP，列为默认值使用当前的时间戳但是不自动更新。
如：alter table  table_test add ts timestamp default current_timestamp
只用ON UPDATE CURRENT_TIMESTAMP子句，列有默认值0并自动更新。
如：alter table table_test add ts timestamp on update current_timestamp
可以用CURRENT_TIMESTAMP，也可以用NOW()。它们均具有相同的效果。
default 和 on update 的顺序不重要。

一个表中定义的第一个timestamp的default 属性默认为 default current_timestamp on update current_timestamp
其他的timestamp列的default 属性默认为 default 0 on update 0
也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间。
TIMESTAMP值返回后显示为'YYYY-MM-DD HH:MM:SS'格式的字符串，显示宽度固定为19个字符。
如果想要获得数字值，应在TIMESTAMP 列添加+0。
mysql> select ts+0 from test;
+----------------+
| ts+0           |
+----------------+
| 20071012121529 |
| 20071012121641 |
| 20071012121722 |
| 20071012121800 |
| 20071012121813 |
| 20071012121829 |
| 20071012122114 |
| 20071012122147 |
| 20071012122204 |
+----------------+

YEAR[(2|4)]
两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000。在两位格式中，允许的值是70到69，
表示从1970年到2069年。MySQL以YYYY 格式显示YEAR值

========================
字符串类型
[NATIONAL] CHAR(M) [BINARY| ASCII | UNICODE]
固定长度字符串，当保存时在右侧填充空格以达到指定的长度。M表示列长度。M的范围是0到255，如果不写则为默认值1。
CHAR(0)列只占用一位，只可以取值NULL和''(空字符串)。

[NATIONAL] VARCHAR(M) [BINARY]
变长字符串。M 表示最大列长度。M的范围是0到65,535。(VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定。最大有效长度是65,532字节）。
BINARY属性：带binary是用2进制保存   字符串比较的时候就是2进制串的比较   当然就区分大小写，
不带binary是按字符集保存   有些区分大小写   有些不区分！

 BINARY(M)
BINARY类型类似于CHAR类型，但保存二进制字节字符串而不是非二进制字符串。

VARBINARY(M)
VARBINARY类型类似于VARCHAR类型，但保存二进制字节字符串而不是非二进制字符串。

BLOB[(M)]
最大长度为65,535字节的BLOB列。
可以给出该类型的可选长度M。如果给出，则MySQL将列创建为最小的但足以容纳M字节长的值的BLOB类型。

TINYBLOB
最大长度为255字节的BLOB列。

MEDIUMBLOB
最大长度为16,777,215字节的BLOB列。

LONGBLOB
最大长度为4,294,967,295或4GB字节的BLOB列。
LONGBLOB列的最大有效(允许的)长度取决于客户端/服务器协议中配置最大包大小和可用的内存。

TEXT[(M)]
最大长度为65,535字符的TEXT列。
可以给出可选长度M。则MySQL将列创建为最小的但足以容纳M字符长的值的TEXT类型。

TINYTEXT
最大长度为255字符的TEXT列。

MEDIUMTEXT
最大长度为16,777,215字符的TEXT列。

LONGTEXT
最大长度为4,294,967,295或4GB字符的TEXT列。
LONGTEXT列的最大有效(允许的)长度取决于客户端/服务器协议中配置最大包大小和可用的内存。

VARCHAR,BLOB和TEXT的比较
一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，实际的存储需要是字符串的长度 ，加上1个字节以记录字符串的长度。对于字符串'abcd'，L是4而存储要求是5个字节。
BLOB和TEXT类型需要1，2，3或4个字节来记录列值的长度，这取决于类型的最大可能长度。VARCHAR需要定义大小，有255的最大限制；TEXT则不需要。如果你把一个超过列类型最大长度的值赋给一个BLOB或TEXT列，值被截断以适合它。
4个TEXT类型TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT对应于4个BLOB类型，并且有同样的最大长度和存储需求。
BLOB是一个能保存可变数量的数据的二进制的大对象。
BLOB 可以储存图片,TEXT不行，TEXT只能储存纯文本文件。
对BLOB值的排序和比较以大小写敏感方式执行，而对TEXT值是大小写不敏感的。

ENUM('value1','value2',...)
枚举类型。只能有一个值的字符串，从值列'value1'，'value2'，...，NULL中选出。ENUM列最多可以有65,535个截然不同的值。
ENUM值在内部用整数表示。

SET('value1','value2',...)
一个设置。字符串对象可以有零个或多个值，每个值必须来自列值'value1'，'value2'，...SET列最多可以有64个成员。SET值在内部用整数表示。
mysql> alter table test add set_test set('hello','how are you','fine');
Query OK, 33 rows affected (0.80 sec)
Records: 33  Duplicates: 0  Warnings: 0
mysql> insert into test set set_test='hello';
Query OK, 1 row affected (0.11 sec)
mysql> insert into test set set_test='hello,fine';
Query OK, 1 row affected (0.13 sec)
mysql> select set_test from test;
+------------+
| set_test   |
+------------+
| hello      |
| hello,fine |
+------------+

=======================

 

阅读(5176) | 评论(0)


版权声明：编程爱好者网站为此博客服务提供商，如本文牵涉到版权问题，编程爱好者网站不承担相关责任，如有版权问题请直接与本文作者联系解决。谢谢！

评论

暂无评论
您需要登录后才能评论，请 登录 或者 注册