正文

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 |
+------------+

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

 

阅读(5180) | 评论(0)


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

评论

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