数据表的设计

最新推荐文章于 2024-07-24 23:35:30 发布

花峪海

最新推荐文章于 2024-07-24 23:35:30 发布

阅读量59

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_58741911/article/details/133324133

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

数据表的设计

数据类型

数值类型

Int

float

Decimal(M,D)

Bit(M)

Int

整行 + UNSIGNED

类型名称	字节数	无符号取值范围	有符号取值范围
TINYINT	1	0 ~ 255	-128 ~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT	4	0~4294967295	-2147483648~2147483647
BIGINT	8	0~2^64 -1	-6^63~2^63 -1

mysql> create database school;                //创建数据库

Query OK, 1 row affected (0.12 sec)

mysql> use school;                           //选择数据库

Database changed

mysql> create table my_int(                                 //创建数据表

    -> int_1 int,

    -> int_2 int unsigned,

    -> int_3 tinyint,

    -> int_4 tinyint unsigned);

Query OK, 0 rows affected (0.06 sec)



mysql> desc my_int;                                            //查看数据表

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

| Field | Type                | Null | Key | Default | Extra |

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

| int_1 | int(11)             | YES  |     | NULL    |       |

| int_2 | int(10) unsigned    | YES  |     | NULL    |       |

| int_3 | tinyint(4)          | YES  |     | NULL    |       |

| int_4 | tinyint(3) unsigned | YES  |     | NULL    |       |

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

4 rows in set (0.00 sec)

mysql>



mysql> insert into my_int values(1000,1000,100,100);        //插入数据

Query OK, 1 row affected (0.10 sec)



mysql> select *from my_int;                              //查看数据

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

| int_1 | int_2 | int_3 | int_4 |

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

|  1000 |  1000 |   100 |   100 |

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

1 row in set (0.00 sec)



mysql>

float

Float (单精度类型)	6位
DouBle（双进度类型）	15位

：四舍五入

mysql> create table my_float(

    -> f1 float,

    -> f2 double);

Query OK, 0 rows affected (0.07 sec)



mysql> desc my_float;

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

| Field | Type   | Null | Key | Default | Extra |

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

| f1    | float  | YES  |     | NULL    |       |

| f2    | double | YES  |     | NULL    |       |

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

2 rows in set (0.00 sec)



mysql> insert into my_float values(111111,11111111111);

Query OK, 1 row affected (0.07 sec)



mysql> insert into my_float values(1.11111,1.1111111111);

Query OK, 1 row affected (0.02 sec)



mysql> select *from my_float;

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

| f1      | f2           |

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

|  111111 |  11111111111 |

|  1.11111 |  1.1111111111 |

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

2 rows in set (0.00 sec)



mysql>

定点数DECIMAL(M,D)

M	0~65	表示全局数，包括小数
D	0~30	只表示小数的位数，并且会四舍五入,
M>=D 确保精度的小数

举例 decimal(5,2)

123.123

mysql> create table my_decimal(

    -> d1 decimal(4,2),

    -> d2 decimal(4,2));

Query OK, 0 rows affected (0.12 sec)



mysql> desc my_decimal;

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

| Field | Type         | Null | Key | Default | Extra |

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

| d1    | decimal(4,2) | YES  |     | NULL    |       |

| d2    | decimal(4,2) | YES  |     | NULL    |       |

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

2 rows in set (0.00 sec)



mysql> insert into  my_decimal values(1.234,1.345);

Query OK, 1 row affected, 2 warnings (0.02 sec)



mysql> select * from my_decimal;

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

| d1   | d2   |

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

| 1.23 | 1.35 |

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

1 row in set (0.00 sec)



mysql>



mysql> alter  table my_decimal add d3 decimal(5,2);       //插入add数据段

mysql> insert into my_decimal values(11.245,11,56.235);

Query OK, 1 row affected, 2 warnings (0.02 sec)



mysql> select * from my_decimal                   //查看数据

    -> ;

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

| d1    | d2    | d3    |

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

|  1.23 |  1.35 |  NULL |

| 21.12 |  1.35 |  NULL |

| 11.25 | 11.00 | 56.24 |

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

3 rows in set (0.00 sec)



mysql>

BIT(M)（位）类型

BIT（M）	1~64
	用于存储二进制数据
本案会用到函数
ASCII()	用于查看指定字符的ASCII()
BIN()	将十进制转换为二进制数
LENGTH()	用于获取字符串的长度

对ASCII | length(bin(65)) 的查看

mysql> select ascii('A');

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

| ascii('A') |

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

|         65 |

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

1 row in set (0.00 sec)



mysql> select bin(65),length(bin(65));

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

| bin(65) | length(bin(65)) |

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

| 1000001 |               7 |

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

1 row in set (0.00 sec)



mysql>

mysql> create table my_bit (b bit(7));             //创建数据表 bit

Query OK, 0 rows affected (0.11 sec)



mysql> insert into my_bit values(65);             //添加bit数据

Query OK, 1 row affected (0.06 sec)



mysql> select * from my_bit;

+------+

| b    |

+------+

| A    |

+------+

1 row in set (0.00 sec)



mysql> select bin(b) from my_bit;

+---------+

| bin(b)  |

+---------+

| 1000001 |

+---------+

1 row in set (0.00 sec)



mysql> select length(bin(b)) from my_bit;              //查看数据表中bin 长度

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

| length(bin(b)) |

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

|              7 |

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

1 row in set (0.00 sec)



mysql>

日期和时间

Year	1	1901~2155	YYYY	年份值
Date	3	1000-01-01~9999-12-31	YYYY-MM-DD	日期值
Time	3	-838:59:59 ~ 838:59:59	HH:MM:DD	时间值
Datetime	8	1000 ~ 9999		日期和时间
Timestamp	4	1970 ~ 2038

Year

mysql> create table my_year(y year);

Query OK, 0 rows affected (0.10 sec)



mysql> insert into my_year values(now());

Query OK, 1 row affected (0.01 sec)



mysql> insert into my_year values(25);

Query OK, 1 row affected (0.07 sec)



mysql> select * from my_year

    -> ;

+------+

| y    |

+------+

| 2023 |

| 2025 |

+------+

Date

mysql> create table my_date (d date);

Query OK, 0 rows affected (0.07 sec)



mysql> insert into my_date values(030601);

Query OK, 1 row affected (0.10 sec)



mysql> insert into my_date values(now());

Query OK, 1 row affected, 1 warning (0.00 sec)



mysql> select * from my_date;

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

| d          |

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

| 2003-06-01 |

| 2023-09-26 |

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

2 rows in set (0.00 sec)



mysql>

Time

mysql> create table my_time (t time);

Query OK, 0 rows affected (0.12 sec)

mysql> insert into my_time values(345454);

Query OK, 1 row affected (0.09 sec)



mysql> insert into my_time values('11:30:50');

Query OK, 1 row affected (0.09 sec)



mysql> select * from my_time;

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

| t        |

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

| 34:54:54 |

| 11:30:50 |

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

2 rows in set (0.00 sec)



mysql>

Datetime

mysql> create table my_datetime (dt datetime);

Query OK, 0 rows affected (0.10 sec)



mysql> insert into my_datetime values(030601112035);

Query OK, 1 row affected (0.05 sec)



mysql> insert into my_datetime values(now());

Query OK, 1 row affected (0.09 sec)



mysql> select * from my_datetime;

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

| dt                  |

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

| 2003-06-01 11:20:35 |

| 2023-09-26 17:58:26 |

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

2 rows in set (0.00 sec)



mysql>

Timestamp

1970 ~ 2038

mysql> create table my_timestamp (ts timestamp);

Query OK, 0 rows affected (0.10 sec)

mysql> insert into my_timestamp  values(now());

Query OK, 1 row affected (0.02 sec)

mysql> insert into my_timestamp  values(030601121520);

Query OK, 1 row affected (0.01 sec)

mysql> insert into my_timestamp  values(20380101000001);

Query OK, 1 row affected (0.05 sec)

mysql> select * from my_timestamp;

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

| ts                  |

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

| 2023-09-26 18:05:50 |

| 2003-06-01 12:15:20 |

| 2038-01-01 00:00:01 |

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

4 rows in set (0.00 sec)



mysql>

字符串类型

Char	固定长度的字符串	0~255
Varchar	可变长度的字符串	0~65535 \0 结束字符
Binary	固定长度的二进制字符串
Varbinary	可变长度的二进制字符串
Blob	普通二进制数据	存储图片\|pdf文档\|音频\|视频
Text	普通文本数据	文章
Enum	枚举类型	相对与二选一
Set	字符串对象，可用有多个零或多个值	多选题

Char(M) | Varchar(M)

Binary（M）| Varbinary(M)

Text

Tinytext	短文本数据	L<2^8
Text	普通文本数据	L<2^16
Mediumtext	中等文本数据	L<2^24
Longtext	超大文本数据	L<2^32

Blob

Tinyblob	短二进制数据	L<2^8
Blob	普通二进制数据	L<2^16
Mediumblob	中等二进制数据	L<2^24
Longblob	超大二进制数据	L<2^32

Enum

mysql> create table my_enum(gender enum('male','female'));

Query OK, 0 rows affected (0.07 sec)

mysql> insert into my_enum values('male');

Query OK, 1 row affected (0.07 sec)

mysql> select * from my_enum where gender='male';      //指定查看

+--------+

| gender |

+--------+

| male   |

+--------+

1 row in set (0.00 sec)



mysql> select * from my_enum ;      //查看所有

+--------+

| gender |

+--------+

| male   |

+--------+

1 row in set (0.00 sec)



mysql>

Set

mysql> create table my_set (hobby set('book','game','code'));

Query OK, 0 rows affected (0.08 sec)

mysql> insert into my_set values(''),('book'),('book,code');

Query OK, 3 rows affected (0.07 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from my_set;

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

| hobby     |

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

|           |

| book      |

| book,code |

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

3 rows in set (0.00 sec)

表的约束