python之str字符串内部职能目录,mysql数据类型

作者: 编程应用  发布:2019-09-26

capitalize ---------------------------- 首单词字母变大写
casefold ------------------------------ 全部字母变成小写
center -------------------------------- 设置字体居中显示
count --------------------------------- 统计字符串里某个字符出现的次数
encode -------------------------------- 修改字符串编码
endswith ------------------------------ 是否以 xxx 结束
expandtabs ---------------------------- 替换字符串中的tab符号
find ----------------------------------检测字符串中是否包含子字符串 str
format --------------------------------格式化字符串
format_map ----------------------------
index ---------------------------------
isalnum -------------------------------
isalphaasd ----------------------------
isdecimal -----------------------------
isdigit -------------------------------
isidentifier --------------------------
islower -------------------------------
isnumeric -----------------------------
isprintable ---------------------------
isspace -------------------------------
istitle -------------------------------
isupper -------------------------------
join ----------------------------------
ljust ---------------------------------
lower ---------------------------------
lstrip --------------------------------
maketrans -----------------------------
partition -----------------------------
replace -------------------------------
rfind ---------------------------------
rindex --------------------------------
rjust ---------------------------------
rpartition ----------------------------
rsplit --------------------------------
rstrip --------------------------------
split ---------------------------------
splitlines ----------------------------
startswith ----------------------------
strip ---------------------------------
swapcase ------------------------------
title ---------------------------------
translate -----------------------------
upper ---------------------------------
zfill ---------------------------------

MySQL基础知识06数据类型(六)类型转换,mysql数据类型

1. cast()的用法

MySQL中大多数类型转化可以使用cast(值 as 目标类型)的语法来完成。

 

cast后面必须紧接着左括号:

mysql> select cast(123 as char);

+-------------------+

| cast(123 as char) |

+-------------------+

| 123               |

+-------------------+

1 row in set (0.00 sec)

 

cast和左括号之间有一个空格时报错:

mysql> select cast (123 as char);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'char)' at line 1

mysql>

 

2. 数值类型和字符串类型的转换

使用cast()将数值转换为字符串类型:

mysql> select cast(123.456 as char);

+-----------------------+

| cast(123.456 as char) |

+-----------------------+

| 123.456               |

+-----------------------+

1 row in set (0.00 sec)

转化为字符串时,参数只能使用char,而不能使用varchar。使用varchar则报错:

mysql> select cast(123 as varchar(10));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(10))' at line 1

 

 

mysql> select cast(123 as char(10));

+-----------------------+

| cast(123 as char(10)) |

+-----------------------+

| 123                   |

+-----------------------+

1 row in set (0.00 sec)

 

mysql> select cast(123 as char(2));

+----------------------+

| cast(123 as char(2)) |

+----------------------+

| 12                   |

+----------------------+

1 row in set, 1 warning (0.00 sec)

 

mysql> show warnings;

+---------+------+------------------------------------------+

| Level   | Code | Message                                  |

+---------+------+------------------------------------------+

| Warning | 1292 | Truncated incorrect CHAR(2) value: '123' |

+---------+------+------------------------------------------+

1 row in set (0.00 sec)

可以使用char(N)指定宽度,超过宽度的字符自动截断。

 

 

使用cast()将字符串转换为数值类型。

mysql> select cast('123' as decimal(10,0)) + cast('456' as decimal(10,0));

+-------------------------------------------------------------+

| cast('123' as decimal(10,0)) + cast('456' as decimal(10,0)) |

+-------------------------------------------------------------+

|                                                         579 |

+-------------------------------------------------------------+

1 row in set (0.00 sec)

转化为数值时,参数只能是decimal,而不能是int等。

 

mysql> select cast('123' as  decimal);

+-------------------------+

| cast('123' as  decimal) |

+-------------------------+

|                     123 |

+-------------------------+

1 row in set (0.00 sec)

 

 

mysql> select cast('123' as decimal(3,0));

+-----------------------------+

| cast('123' as decimal(3,0)) |

+-----------------------------+

|                         123 |

+-----------------------------+

1 row in set (0.00 sec)

 

mysql> select cast('123' as decimal(2,0));

+-----------------------------+

| cast('123' as decimal(2,0)) |

+-----------------------------+

|                          99 |

+-----------------------------+

1 row in set, 1 warning (0.00 sec)

 

mysql> show warnings;

+---------+------+----------------------------------------------------------------------+

| Level   | Code | Message                                                              |

+---------+------+----------------------------------------------------------------------+

| Warning | 1264 | Out of range value for column 'cast('123' as decimal(2,0))' at row 1 |

+---------+------+----------------------------------------------------------------------+

1 row in set (0.00 sec)

 

当超出表示范围时,自动转化为符合范围要求的最接近的数值。

 

当使用decimal之外的类型作为目标类型时,则报错:

mysql> select cast('123' as float(10));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'float(10))' at line 1

mysql> select cast('123' as  int);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)' at line 1

mysql> select cast('123' as  numeric(10,0));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'numeric(10,0))' at line 1

mysql>

 

 

3. 日期类型和其它类型之间的转换

 

日期相关类型也可以使用cast()与字符串类型进行转换。

mysql> select cast('2017-09-01' as date);

+----------------------------+

| cast('2017-09-01' as date) |

+----------------------------+

| 2017-09-01                 |

+----------------------------+

1 row in set (0.00 sec)

 

mysql> select cast('2017-09-01' as datetime);

+--------------------------------+

| cast('2017-09-01' as datetime) |

+--------------------------------+

| 2017-09-01 00:00:00            |

+--------------------------------+

1 row in set (0.01 sec)

 

mysql> select cast(now() as char);

+---------------------+

| cast(now() as char) |

+---------------------+

| 2017-09-01 17:38:24 |

+---------------------+

1 row in set (0.00 sec)

 

日期类型也可以通过date_format()函数取得格式化后的字符串。

mysql> select date_format(now(),'%Y-%m-%d %H:%i:%s');

+----------------------------------------+

| date_format(now(),'%Y-%m-%d %H:%i:%s') |

+----------------------------------------+

| 2017-09-01 17:41:22                    |

+----------------------------------------+

1 row in set (0.00 sec)

 

 

日期类型在一些函数中,可以隐式的转换为字符串类型。

mysql> select concat(now(),'abc');

+------------------------+

| concat(now(),'abc')    |

+------------------------+

| 2017-09-01 17:38:45abc |

+------------------------+

1 row in set (0.00 sec)

 

 

字符串类型在一些日期相关函数中也可以隐式的转换为日期类型。

mysql> select year('2017-09-01');

+--------------------+

| year('2017-09-01') |

+--------------------+

|               2017 |

+--------------------+

1 row in set (0.00 sec)

 

 

4. JSON类型与其它类型的转换

使用CAST进行类型转换,如果结果是JSON类型,可使用JSON_TYPE()查看具体类型。

 

mysql> select json_type(cast('1' as json));

+------------------------------+

| json_type(cast('1' as json)) |

+------------------------------+

| INTEGER                      |

+------------------------------+

1 row in set (0.00 sec)

 

mysql> select json_type(cast("1" as json));

+------------------------------+

| json_type(cast("1" as json)) |

+------------------------------+

| INTEGER                      |

+------------------------------+

1 row in set (0.00 sec)

 

mysql> select json_type(cast("abc" as json));

ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.

mysql> select json_type(cast('"abc"' as json));

+----------------------------------+

| json_type(cast('"abc"' as json)) |

+----------------------------------+

| STRING                           |

+----------------------------------+

1 row in set (0.00 sec)

 

 

mysql> select json_type(cast('true' as json));

+---------------------------------+

| json_type(cast('true' as json)) |

+---------------------------------+

| BOOLEAN                         |

+---------------------------------+

1 row in set (0.00 sec)

 

mysql> select json_type(cast(true as json));

+-------------------------------+

| json_type(cast(true as json)) |

+-------------------------------+

| BOOLEAN                       |

+-------------------------------+

1 row in set (0.00 sec)

 

 

mysql> select json_type(cast('{"a":"123","b":"345"}' as json));

+--------------------------------------------------+

| json_type(cast('{"a":"123","b":"345"}' as json)) |

+--------------------------------------------------+

| OBJECT                                           |

+--------------------------------------------------+

1 row in set (0.00 sec)

 

 

如果仅仅为了获取JSON的具体类型,也可以不使用cast转换,直接使用单引号括起来就可以。

mysql> select json_type('true');

+-------------------+

| json_type('true') |

+-------------------+

| BOOLEAN           |

+-------------------+

1 row in set (0.00 sec)

 

mysql> select json_type('{"a":"123","b":"345"}');

+------------------------------------+

| json_type('{"a":"123","b":"345"}') |

+------------------------------------+

| OBJECT                             |

+------------------------------------+

1 row in set (0.00 sec)

 

 

 

但是,如果不使用单引号括起来,则报错:

mysql> select json_type(true);

ERROR 3146 (22032): Invalid data type for JSON data in argument 1 to function json_type; a JSON

string or JSON type is required.

 

  1. cast() 的用法 MySQL 中大多数类型转化可以使用 cast( 值 as 目标类型 ) 的语法来完成。...

本文由金沙澳门官网送注册58发布于编程应用,转载请注明出处:python之str字符串内部职能目录,mysql数据类型

关键词:

上一篇:语法糖之
下一篇:ecshop验证码