• 周四. 2月 29th, 2024

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

msq 表操作与其数据类型

admin

11月 28, 2021

一:表介绍

表相当于文件, 表中的一条记录就相当于文件的一行内容, 不同的是,表中的一条记录有对应的标题,称为表的字段:

id,name, age, sex,称为字段, 其余的一行内容称为一条记录。

二:创建表

语法:
create table 表名(
字段名1 类型[(宽度) 约束条件]),
字段名2 类型[(宽度) 约束条件]),
字段名3 类型[(宽度) 约束条件])
);

解释:
类型:使用限制字段必须以什么样的数据类型传值
约束条件:约束条件是在类型之外添加一种额外的限制 注意
1:在同一张表中, 字段名 是不能相同的 2:宽度和约束条件可选 3:字段名和类型是必须的
4:注意注意注意:表中的最后一个字段不要加逗号

三 查看表结构

MariaDB [db1]> describe t1; #查看表结构,可简写为desc 表名
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+


MariaDB [db1]> show create table t1G; #查看表详细结构,可加G

四:数据类型

存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的

mysql 常用的数据类型概览

1数字:
整型:tinyinit     int      bigint
小数:
        float: 在位数比较短的情况下不精准
        double:在位数比较长的情况下不精准
            0.000001230123123123
            存成:0.000001230000
            
          decimal:(如果用小数,则推荐使用decimal)
                    精准
                     内部原理是以字符串形式去存

2  字符串
    char (6):简单粗暴,浪费空间,存取速度快
        root存成root00(#这里00表示空格,方便看)
     

     varchar:精准,节省空间, 存取速度慢

      sql 优化:创建表时,定长的类型往前放,变长的往后放
                比如性别       比如地址或描述信息



       > 255个字符,超了就把文件路径存放到数据库中。
               比如图片,视频等找一个文件服务器, 数据库中值存路径或url.


3:时间类型:
最常用:datetime

4:
枚举类型与集合类型

一:数值类型

1、整数类型

整数类型:TINYINT SMALLINT MEDIUMINT INT BIGINT

作用:存储年龄, 等级, id,各种号码等

==============================
tinyint[(m)] [unsigned]  [zerofill]

小整数,数据类型用于保存一些范围的整数数值范围:
有符号:
-128~127

无符号:
0~255

ps:MySQL 中无布尔值,使用tinyint(1)构造。

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

   int[(m)][unsigned][zerofill]
 整数,数据类型用于保存一些范围的整数数值范围:
有符号:
     -21474836482147483647
  无符号:
      04294967295

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

        bigint[(m)][unsigned][zerofill]
大整数 数据类型用于保存一些范围的整数数值范围:
有符号:
 -92233720368547758089223372036854775807

无符号:
 018446744073709551615

测试验证

#创建W1 表 字段名为X ,为其赋值 128,-129。 都超出 了 tinyint 有符号的范围, 结果显示如下, 为127,-128.

int      0 ~ 4294967295

 

强调:

对于整型来说,数据类型后的宽度并不是存储限制,而是显示限制,

所以在创建表时,如果字段采用的是整型类型,完全无需指定显示宽度,默认的显示宽度

足够显示完整当初存放的数据。

如下:

查看以及修改 sql_mode :

mysql> show variables like "%sql_mode%";
+----------------------------+---------------------+
| Variable_name              | Value               |
+----------------------------+---------------------+
| binlogging_impossible_mode | IGNORE_ERROR        |
| block_encryption_mode      | aes-128-ecb         |
| gtid_mode                  | OFF                 |
| innodb_autoinc_lock_mode   | 1                   |
| innodb_strict_mode         | OFF                 |
| pseudo_slave_mode          | OFF                 |
| slave_exec_mode            | STRICT              |
| sql_mode                   | STRICT_TRANS_TABLES |
+----------------------------+---------------------+
8 rows in set (0.00 sec)

#修改sql_mode为严格模式:在该模式下,如果插入的数据超过限制,则会立即报错
mysql> set global sql_mode="strict_trans_tables";

int的存储宽度是4个Bytes,即32个bit,即2**32

无符号最大值为:4294967296-1

有符号最大值:2147483648-1

有符号和无符号的最大数字需要的显示宽度均为10,而针对有符号的最小值则需要11位才能显示完全,所以int类型默认的显示宽度为11是非常合理的

最后:整形类型,其实没有必要指定显示宽度,使用默认的就ok

2:浮点型 float:

作用:存储身高, 体重,薪资

====================================
#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
定义:
单精度浮点数(非准确小树值),m 是数字总个数,d 是小数点后个数,m最大值为255,d最大值为30.

有符号:
           -3.402823466E+38 to -1.175494351E-38,
           1.175494351E-38 to 3.402823466E+38
无符号:
           1.175494351E-38 to 3.402823466E+38


精确度: 
           **** 随着小数的增多,精度变得不准确 ****


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

#DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

定义:
           双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30

有符号:
           -1.7976931348623157E+308 to -2.2250738585072014E-308
           2.2250738585072014E-308 to 1.7976931348623157E+308

无符号:
           2.2250738585072014E-308 to 1.7976931348623157E+308
            
精确度:
           ****随着小数的增多,精度比float要高,但也会变得不准确 ****

=========================================
decimal[(m[,d])] [unsigned] [zerofill]

定义:
          准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。


精确度:
           **** 随着小数的增多,精度始终准确 ****
           对于精确数值计算时需要用此类型
           decaimal能够存储精确值的原因在于其内部按照字符串存储

相同点

1:对于三者来说都能存放30位小数,

不同点:

1:精度的排序从低到高:floa,double,decimal

2:float 与double 类型能存放的整数位比decimal 更多。

验证如下:

3、字符串类型

有char 和varchar字符串类型;注意:char 和varchar 括号内的参数指的都是字符的长度

char 类型:定长 ,简单粗暴, 浪费空间, 存取速度快
    字符长度范围:0-255(一个中文是一个字符,是utf8编码的3个字节)
    存储:
        存储char类型的值时, 会往右填充空格来满足长度
        例如:指定长度为10 时,存>10个字符则报错, 存<10 个字符则用空            格填充直到凑够10个字符存储
        

        检索:
在检索或者说查询时, 查出的结果会自动删除尾部的空格, 除非我们打开pad_char_to_full_length SQL模式(SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';)


#varchar 类型:变长,精准, 节省空间, 存取速度慢
    字符长度范围 0-65535(如果大于21845会提示用其他类型 。mysql行最大限制为65535字节,字符编码为utf-8:
存储:
        varchar类型存储数据的真实内容,不会用空格填充,如果'ab  ',尾部的空格也会被存起来
        强调:varchar类型会在真实数据前加1-2Bytes的前缀,该前缀用来表示真实数据的bytes字节数(1-2Bytes最大表示65535个数字,正好符合mysql对row的最大字节限制,即已经足够使用)
        如果真实的数据<255bytes则需要1Bytes的前缀(1Bytes=8bit 2**8最大表示的数字为255)
        如果真实的数据>255bytes则需要2Bytes的前缀(2Bytes=16bit 2**16最大表示的数字为65535)
    
    检索:
        尾部有空格会保存下来,在检索或者说查询时,也会正常显示包含空格在内的内容

 官网图:在字符数小于限定值时, 用varchar 更省空间, 但是当字符数到到限定值时, 用char 更省空间

#备注:了解两个函数

length:查看字节数

char_length:查看字符数

#注意:

针对 char 类型,mysql 在存储时会将数据用空格补全放到硬盘中

但会在读出结果时,自动去掉末尾的空格, 因为末尾的空格在以下场景中是无用

mysql> select * from t14 where name="lxx"; # name字段明确地等于一个值,该值后填充空格是没有用


mysql> select * from t14 where name like "lxx"; # name字段模糊匹配一个值,该值后填充空格是有用的

总结:

常用字符串系列:char 与varchar

注:虽然varchar 使用起来较为灵活,但是从整个系统的性能角度来说,char数据类型的处理书读更快,  有时甚至可以超出varchar 处理书读的50%,因此用户在设计数据库时应当综合考虑各方面的因素 ,以求达到最佳的平衡。

4:日期类型:

作用:存储用户注册时间, 文章发布时间,员工入职时间, 出生时间, 过期时间等

        YEAR
            YYYY(1901/2155)

        DATE
            YYYY-MM-DD(1000-01-01/9999-12-31)

        TIME
            HH:MM:SS('-838:59:59'/'838:59:59')

        DATETIME

            YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59    Y)

        TIMESTAMP

            YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)


============注意啦,注意啦,注意啦===========
1. 单独插入时间时,需要以字符串的形式,按照对应的格式插入
2. 插入年份时,尽量使用4位值
3. 插入两位年份时,<=69,以20开头,比如50,  结果2050      
                >=70,以19开头,比如71,结果1971

表示方法如下:

date:1999-01-27
time: 11:11:11
datetime:1999-01-27 11:11:11
year:1999

测试:

datetime 与timestamp 的 区别

在实际应用的很多场景中,MySQL的这两种日期类型都能够满足我们的需要,存储精度都为秒,但在某些情况下,会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。

1.DATETIME的日期范围是1001——9999年,TIMESTAMP的时间范围是1970——2038年。

2.DATETIME存储时间与时区无关,TIMESTAMP存储时间与时区有关,显示的值也依赖于时区。在mysql服务器,操作系统以及客户端连接都有时区的设置。

3.DATETIME使用8字节的存储空间,TIMESTAMP的存储空间为4字节。因此,TIMESTAMP比DATETIME的空间利用率更高。

4.DATETIME的默认值为null;TIMESTAMP的字段默认不为空(not null),默认值为当前时间(CURRENT_TIMESTAMP),如果不做特殊处理,并且update语句中没有指定该列的更新值,则默认更新为当前时间。

5:枚举类型与集合类型

字段的值只能在给定范围中选择, 如单选框吗多选框

enum单选,只能在给定的范围内选一个值, 如性别sex, 男 male /女 female

set 多选  在给定的范围内可以选择一个或者一个以上的值(爱好1,爱好2, 爱好3。。。)

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注