030、SQL语句之数据类型与表达式

数值数据类型

整数数据类型

注意unsigned: 不允许负数

insert into T values(pow(2,10));  # pow： 2^10 2的10次方 

定点数据类型

用于精确数值：整数、小数或两者

• 数据类型：

  • decimal:
    • 固定十进制
    • 保持精度

• 示例
  货币: cost decimal (10,2)
  输出：385.72
  10: 整数+小数一共多少位
  2： 小数的位数

浮点数据类型

用于近似值：整数、小数或两者

数值字面值

• 精确值字面值：
  • 在SQL语句中显示的即为其具体数值
  • 写为整数或十进制，灭有指数
    -近似值
  • 并不总按照SQL语句中指定的那样使用
  • 用科学计数法写成浮点，带指数
  • 有误差

mysql> select 1.1+2.2,1.1E0+2.2E0;
+---------+--------------------+
| 1.1+2.2 | 1.1E0+2.2E0        |
+---------+--------------------+
|     3.3 | 3.3000000000000003 |
+---------+--------------------+
1 row in set (0.00 sec)

BIT数据类型

BIT 类型
比特值类型。M 表示比特位的长度，取值范围从1到64，其默认值是1。

BIT[(M)]

示例
存储4位: status_column BIT(4)
字面值可表达为：
b’1101’
0b1101

mysql> create table tt(id bit(2));
Query OK, 0 rows affected (0.40 sec)

mysql> insert into tt values(b'0101010');
ERROR 1406 (22001): Data too long for column 'id' at row 1
mysql> insert into tt values(b'010');
Query OK, 1 row affected (0.04 sec)

mysql> select collation(id) from tt;
+---------------+
| collation(id) |
+---------------+
| binary        |
+---------------+
1 row in set (0.02 sec)

mysql> select hex(id) from tt; # hex : 转16进制
+---------+
| hex(id) |
+---------+
| 2       |
+---------+
1 row in set (0.01 sec)

布尔表达式

布尔类型，别名为 BOOL，和 TINYINT(1) 等价。零值被认为是 False，非零值认为是 True。在 TiDB 内部，True 存储为 1，False 存储为 0。

mysql> select 1=true,1=TRUE,True;
+--------+--------+------+
| 1=true | 1=TRUE | TRUE |
+--------+--------+------+
|      1 |      1 |    1 |
+--------+--------+------+
1 row in set (0.00 sec)

时间数据类型

timestamp : 受时区影响，并且最大范围是2038年

mysql> create table dropme(day datetime);
Query OK, 0 rows affected (0.17 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.02 sec)

mysql> insert into dropme(day) values('0000 00-00-00');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> set sql_mode='strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dropme(day) values('0000 00-00-00');
ERROR 1292 (22007): Incorrect datetime value: '0000 00-00-00' for column 'day' at row 1

时间戳和时区

• 当前时区： select @@time_zone;
• 在被存储时，timestamp值从当前时区转换为UTC
• 在被查询时，数据从UTC转换为当前时区

mysql> set time_zone='+08:00';
Query OK, 0 rows affected (0.01 sec)

mysql> create table dropme(d1 timestamp,d2 datetime);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into dropme values(now(),now());
Query OK, 1 row affected (0.04 sec)

mysql> select * from dropme;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 2023-06-30 06:48:44 | 2023-06-30 06:48:44 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone='+09:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dropme;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 2023-06-30 07:48:44 | 2023-06-30 06:48:44 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone='+10:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dropme;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 2023-06-30 08:48:44 | 2023-06-30 06:48:44 |
+---------------------+---------------------+
1 row in set (0.00 sec)

时间间隔关键字

• interval 用于指定持续时间的关键字

mysql> select '2022-01-01' + interval 10 day,'2022-01-01' + interval '5 2:3:4' day_second;  # day_second  指定天和秒
+--------------------------------+----------------------------------------------+
| '2022-01-01' + interval 10 day | '2022-01-01' + interval '5 2:3:4' day_second |
+--------------------------------+----------------------------------------------+
| 2022-01-11                     | 2022-01-06 02:03:04                          |
+--------------------------------+----------------------------------------------+
1 row in set (0.00 sec)

字符串数据类型

字符集 单个字符字节数 VARCHAR 最大列长度的取值范围
ascii 1 (0, 65535]
latin1 1 (0, 65535]
binary 1 (0, 65535]
utf8 3 (0, 21845]
utf8mb4 4 (0, 16383]

文本数据类型

• tinytext: 最大列长度为255
• text： 最大列长度为65535
• mediumtext/longtext: 这两个数据类型的最大列长度受TiDB的txn_entry_size_limit 和 TiKV的raft-entry-max-size参数影响

二进制

• binary: 和char类似，固定长度二进制字节字符串。bin_val binary(6)
• varbinary: 和varchar类似，可变长度二进制字节字符串. varbin_val varbinary (6)
• 示例

mysql> create table bbb(id blob);
Query OK, 0 rows affected (0.18 sec)

mysql> insert into bbb values(cast('SD' as binary));
Query OK, 1 row affected (0.01 sec)

mysql> insert into bbb values('SD');
Query OK, 1 row affected (0.01 sec)

mysql> select id,cast(id as char) from bbb;
+------------+------------------+
| id         | cast(id as char) |
+------------+------------------+
| 0x5344     | SD               |
| 0x5344     | SD               |
+------------+------------------+
2 rows in set (0.01 sec)

ENUM 类型

枚举类型是一个字符串，它只能有一个值的字符串对象。其值必须是从一个固定集合中选取，这个固定集合在创建表的时候定义，语法是：

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

例如：

ENUM(‘apple’, ‘orange’, ‘pear’)
枚举类型的值在 TiDB 内部使用数值来存储，每个值会按照定义的顺序转换为一个数字，比如上面的例子中，每个字符串值都会映射为一个数字：

mysql> create table t5(id enum('Sunday','Monday','Tuesday','Wednesda,y','Thursday','Friday','Saturday'));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t5 values ('Monday');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
+--------+
| id     |
+--------+
| Monday |
+--------+
1 row in set (0.00 sec)

mysql> create table s1(id set('A','B','C','a','ac','bca','ad') defaullt null);
Query OK, 0 rows affected (0.10 sec)

mysql> desc s1;
+-------+--------------------------------------+------+------+---------+-------+
| Field | Type                                 | Null | Key  | Default | Extra |
+-------+--------------------------------------+------+------+---------+-------+
| id    | set('A','B','C','a','ac','bca','ad') | YES  |      | NULL    |       |
+-------+--------------------------------------+------+------+---------+-------+
1 row in set (0.00 sec)

mysql> insert into s1 values ('B');
Query OK, 1 row affected (0.02 sec)

mysql> insert into s1 values ('ABCDE');
ERROR 1265 (01000): Data truncated for column 'id' at row 1
mysql> insert into s1 values ('ABCD');
ERROR 1265 (01000): Data truncated for column 'id' at row 1
mysql> insert into s1 values ('ABC');
ERROR 1265 (01000): Data truncated for column 'id' at row 1
mysql> insert into s1 values ('C,B');
Query OK, 1 row affected (0.02 sec)

mysql> select * from s1;
+------+
| id   |
+------+
| B    |
| B,C  |
+------+
2 rows in set (0.00 sec)

SET 类型

集合类型是一个包含零个或多个值的字符串，其中每个值必须是从一个固定集合中选取，这个固定集合在创建表的时候定义，语法是：

SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

例如：

SET('1', '2') NOT NULL
上面的例子中，这列的有效值可以是：
'' ,'1','2','1,2'

字符串数据类型比较

引号的使用

• 使用’ 或者"
• ansi_quotes SQL模式会将双引号内的字符解释为标识符
• 为了便于一直，应首选单引号

字符集和排序规则

• character set 是一组符号和编码
  • 所有字符串都属于一个特定的字符集
  • TiDB中默认的字符集是utf8mb4
• collation 是一组用于比较字符集中字符和字符排序顺序的规则
  • 影响字符和字符串的比较
  • TiDB中的默认规则是utf8mb4_bin
• show character set ： 显示所有支持的字符集
• show collation: 显示所有支持的排序规则

select 'A'='a' collate utf8mb4_bin,'A'='a' collate utf8mb4_general_ci,'A'='a' ;

CAST函数

• 语法
  cast(expression as type)
• cast函数将任何类型的值转换为具有指定类型的值。目标类型可以是：BINARY,CHAR,DATE,DATETIME,TIME,DECIMAL,SIGNED,UNSIGNED
• 例如： select cast(’ ‘as binary),binary(’ ');

select cast(' 'as binary),binary(' ');
select cast('123' as decimal),cast('	123' as decimal),cast('123	' as decimal),cast('	1	23	' as decimal);

选择数据类型

• ABC原则
  • Appropriate：合适
  • Brief：消耗最少的资源
  • Complete： 数据不可丢失
• 主键的设计和考虑

Null

• NULL 是一个SQL关键字，用于定义允许缺失值的数据类型

  • 未知：存在值，但目前尚不知道精确值
  • 不适用： 如果指定了某个值，则该值将无法准确地具有代表性
  • 默认情况下允许空值
  • 如果列不允许空值，适用not null声明确保数据完整性
  • null 是order by中的最小值

• 示例

select NULL = NULL,NULL != NULL,NULL <=> NULL , NULL<=> 'X'

null 和谁计算都是null (除了<=> 这个符号)文章来源地址https://www.toymoban.com/news/detail-535818.html

到了这里，关于030、SQL语句之数据类型与表达式的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！