mysql Data_type
发表于：2024-01-10 | 分类： MySQL
字数统计: 19k | 阅读时长: 17 mins.分钟 | 阅读量：

mysql 数据类型

修改字段名称及类型

root@localhost [y36]>alter table  student  CHANGE c sex CHAR(5);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

整数类型

Type 存储（字节） 最小值 有符号 最小值 无符号 最大值 有符号 最大值 无符号
TINYINT 1 -128 0 127 255
SMALLINT 2 -32768 0 32767 65535
MEDIUMINT 3 -8388608 0 8388607 16777215
INT 4 -2147483648 0 2147483647 4294967295
BIGINT 8 -263 0 263-1 264-1

案例： 根据数据类型

root@localhost [y36]>create  table student(id int,name char(50),age int, sex char(5), SFID char(50), tel char(11));

root@localhost [y36]>desc  student;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(50) | YES  |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
| sex   | char(5)  | YES  |     | NULL    |       |
| SFID  | char(50) | YES  |     | NULL    |       |
| tel   | char(11) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
root@localhost [y36]>insert into  student (id,name,age,sex,SFID,tel)values(1,'张三',10,'男','140427190000202020','18301002516');
Query OK, 1 row affected (0.01 sec)

root@localhost [(none)]>select * from  y36.student;
+------+--------+------+------+--------------------+-------------+
| id   | name   | age  | sex  | SFID               | tel         |
+------+--------+------+------+--------------------+-------------+
|    1 | 张三   |   10 | 男   | 140427190000202020 | 18301002516 |
+------+--------+------+------+--------------------+-------------+
1 row in set (0.00 sec)

浮点数类型：

  • FLOAT：单精度浮点数，适用于表示小数值。
  • DOUBLE：双精度浮点数，适用于表示更大或更精确的小数值。

字符串类型：

  • CHAR：固定长度的字符，适用于存储定长字符串。
  • VARCHAR：可变长度的字符，适用于存储不定长字符串。
  • TEXT：用于存储较大文本数据的类型。

4. 日期和时间类型：

DATE： 用于存储日期，格式为YYYY-MM-DD。

TIME： 用于存储时间，格式为HH:MM:SS。

DATETIME： 用于存储日期和时间，格式为YYYY-MM-DD HH:MM:SS。

TIMESTAMP： 类似于DATETIME，但在写入时会自动更新为当前时间。

案例：

<font color=red>错误的语法</font>
root@localhost [(none)]>insert  into  y36.t1(t,dt,ts)values('1000-01-01','2022-12-14 11:44:00','2038-01-19 03:14:07.499999');
ERROR 1292 (22007): Incorrect time value: '1000-01-01' for column 't' at row 1

root@localhost [(none)]>insert  into  y36.t1(dt,ts)values('2022-12-14 11:44:00','2038-01-19 03:14:07.499999');
Query OK, 1 row affected (0.01 sec)

root@localhost [(none)]>insert  into  y36.t1(t,dt,ts)values('11:44:00','2022-12-14 11:44:00','2038-01-19 03:14:07.499999');
Query OK, 1 row affected (0.03 sec)

root@localhost [(none)]>insert  into  y36.t1(t,dt,ts)values('11:44:00','2022-12-14 11:44:00','2038-01-19 03:14:07.499999');^C
root@localhost [(none)]>select * from y36.t1;
+--------------+----------------------------+---------------------+
| t            | dt                         | ts                  |
+--------------+----------------------------+---------------------+
| NULL         | 2022-12-14 11:44:00.000000 | 2038-01-19 03:14:07 |
| 11:44:00.000 | 2022-12-14 11:44:00.000000 | 2038-01-19 03:14:07 |
+--------------+----------------------------+---------------------+

案例2： 在原有的表中添加日期时间的字段

# 修改表的的字段（添加一个字段）
root@localhost [(none)]>alter  table y36.student add  dt DATETIME;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 更新表里的数据
root@localhost [(none)]>update  y36.student set dt='2023-12-14 12:02:20' where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 验证
root@localhost [(none)]>select  * from y36.student;
+------+--------+------+------+--------------------+-------------+---------------------+
| id   | name   | age  | sex  | SFID               | tel         | dt                  |
+------+--------+------+------+--------------------+-------------+---------------------+
|    1 | 张三   |   10 | 男   | 140427190000202020 | 18301002516 | 2023-12-14 12:02:20 |
+------+--------+------+------+--------------------+-------------+---------------------+
1 row in set (0.00 sec)

获取当前

root@localhost [y36]>desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| t     | time(3)     | YES  |     | NULL    |       |
| dt    | datetime(6) | YES  |     | NULL    |       |
| ts    | timestamp   | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

root@localhost [y36]>insert into  t1(t,dt,ts)values(CURTiME(),NOW(),CURDATE());
Query OK, 1 row affected (0.01 sec)

root@localhost [y36]>select * from  t1;
+--------------+----------------------------+---------------------+
| t            | dt                         | ts                  |
+--------------+----------------------------+---------------------+
| NULL         | 2022-12-14 11:44:00.000000 | 2038-01-19 03:14:07 |
| 11:44:00.000 | 2022-12-14 11:44:00.000000 | 2038-01-19 03:14:07 |
| 14:35:38.000 | 2023-12-14 14:35:38.000000 | 2023-12-14 00:00:00 |
+--------------+----------------------------+---------------------+
3 rows in set (0.01 sec)

回顾：

SQL 的基础语法
创建表
插入表中的数据类型

  • 数字类型
    1. 整数类型
      1. int()
    2. 浮点数类型
      1. float()
      2. double()
  • 字符类型 （String type）
  1. char() 类型
  2. varchar()
  3. text
  • 时间类型
  1. time
  2. date
  3. datetime
  4. timestamp

条件约束

数据完整性约束用于确保数据库中数据的合法性和一致性，防止不符合预期的数据被插入或修改。以下是常见的数据完整性约束：

  1. NOT NULL：

    • NOT NULL 约束用于确保列中的值不能为空值（NULL）。
    • 这意味着在插入或更新数据时，必须为该列提供一个非空值，否则会抛出错误。
    • 示例：name VARCHAR(50) NOT NULL 表示 name 列不能包含空值。

  2. UNIQUE：

    • UNIQUE 约束用于确保列中的值是唯一的，即不允许有重复的值。
    • 可以在一个或多个列上创建 UNIQUE 约束。
    • 示例：email VARCHAR(100) UNIQUE 表示 email 列中的值必须是唯一的，不能有重复的电子邮件地址。

  3. PRIMARY KEY：

    • PRIMARY KEY 约束用于定义表中的主键，确保每一行数据都有唯一的标识。
    • 一个表只能有一个主键，主键可以由一个或多个列组成。
    • 示例：student_id INT PRIMARY KEY 表示 student_id 列是主键，用于唯一标识每个学生。

  4. FOREIGN KEY：

    • FOREIGN KEY 约束用于建立表之间的关联，确保一个表的数据与另一个表的数据保持一致。
    • 外键引用另一个表的主键，用于建立父表和子表之间的关系。
    • 示例：course_id INT, FOREIGN KEY (course_id) REFERENCES Courses(course_id) 表示 course_id 列是外键，引用了 Courses 表的主键 course_id。

  5. CHECK：

    • CHECK 约束用于限制列中的值必须满足指定的条件。
    • 可以使用表达式来定义 CHECK 约束，如果表达式为假，则不允许插入或更新数据。
    • 示例：age INT CHECK (age >= 0) 表示 age 列的值必须大于等于0。

案例：不能为空值


create table y36.class2(id int not null, name char(20) not null, age int(3) not null, sex char(2) not null, SFid  char(20) not null, tel char(11) not null, )

root@localhost [(none)]>desc y36.class2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   |     | NULL    |       |
| name  | char(20) | NO   |     | NULL    |       |
| age   | int      | NO   |     | NULL    |       |
| sex   | char(2)  | NO   |     | NULL    |       |
| SFid  | char(20) | NO   |     | NULL    |       |
| tel   | char(11) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
6 rows in set (0.01 sec)

案例2： 用户不需要输入ID
ID 列设置为自增

root@localhost [(none)]>CREATE TABLE y36.class3(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    NAME CHAR(20) NOT NULL,
    age INT(3) NOT NULL,
    sex CHAR(2) NOT NULL,
    SFid CHAR(20) NOT NULL,
    tel CHAR(11) NOT NULL
);

# 插入数据
root@localhost [(none)]>insert  into y36.class3(name,age,sex,SFid,tel)values('lisi',18,'m','14041900024100111','18301002516');
Query OK, 1 row affected (0.02 sec)

root@localhost [(none)]>insert  into y36.class3(name,age,sex,SFid,tel)values('lisi',18,'m','14041900024100111','18301002516');
Query OK, 1 row affected (0.01 sec)

root@localhost [(none)]>insert  into y36.class3(name,age,sex,SFid,tel)values('lisi',18,'m','14041900024100111','18301002516');
Query OK, 1 row affected (0.01 sec)

root@localhost [(none)]>insert  into y36.class3(name,age,sex,SFid,tel)values('lisi',18,'m','14041900024100111','18301002516');
Query OK, 1 row affected (0.01 sec)

root@localhost [(none)]>insert  into y36.class3(name,age,sex,SFid,tel)values('lisi',18,'m','14041900024100111','18301002516');
Query OK, 1 row affected (0.01 sec)

root@localhost [(none)]>insert  into y36.class3(name,age,sex,SFid,tel)values('lisi',18,'m','14041900024100111','18301002516');
Query OK, 1 row affected (0.01 sec)

root@localhost [(none)]>insert  into y36.class3(name,age,sex,SFid,tel)values('lisi',18,'m','14041900024100111','18301002516');
Query OK, 1 row affected (0.01 sec)

root@localhost [(none)]>insert  into y36.class3(name,age,sex,SFid,tel)values('lisi',18,'m','14041900024100111','18301002516');
Query OK, 1 row affected (0.01 sec)

案例2： SFid 不能出现重复

# 修改表中SFid字段的属性
root@localhost [(none)]>alter table y36.class3 modify column SFid  char(20)  not null UNIQUE;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0


# 查看表的格式
root@localhost [(none)]>desc  y36.class3
    -> ;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int      | NO   | PRI | NULL    | auto_increment |
| name     | char(20) | NO   |     | NULL    |                |
| age      | int      | NO   |     | NULL    |                |
| sex      | char(2)  | NO   |     | NULL    |                |
| SFid     | char(20) | YES  | UNI | NULL    |                |
| tel      | char(11) | NO   |     | NULL    |                |
| datetime | datetime | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

root@localhost [(none)]>insert  into y36.class3(name,age,sex,SFid,tel)values('lisi',18,'m','14041900024100111','18301002516');
Query OK, 1 row affected (0.82 sec)

设置条件

总结

创建表的属性及约束条件

一、data_type

data_type表示列定义中的数据类型。有关可用于指定列数据类型的语法的完整描述，以及有关每种类型属性的信息

二、NOT NULL | NULL

如果既没有指定NULL也没有指定NOT NULL，则该列将被视为已指定NULL。

三、 AUTO_INCREMENT 自增

整数或浮点列可以具有附加属性AUTO_INCREMENT。当您在索引的AUTO_INCREMENT列中插入NULL（推荐）或0的值时，该列将设置为下一个序列值。通常这是value+1，其中value是当前表中列的最大值。AUTO_INCREMENT序列以1开头

四、主键 PRIMARY KEY

一个唯一的索引，其中所有键列都必须定义为NOT NULL。如果它们没有被明确声明为NOT NULL，MySQL会隐式（和无声地）声明它们。一张桌子只能有一个PRIMARY KEY。PRIMARY KEY的名称始终为主PRIMARY，因此不能用作任何其他类型索引的名称。

如果您没有PRIMARY KEY，并且应用程序在表中要求输入主PRIMARY KEY，MySQL将返回第一个具有noNULL列作为PRIMARY KEY的UNIQUE索引

五、UNIQUE（字段中不能出现重复字段）

UNIQUE索引创建一个约束，使得索引中的所有值都必须是不同的。如果您尝试添加具有与现有行匹配的键值的新行，则会出现错误。对于所有引擎，UNIQUE索引允许为可能包含NULL的列提供多个NULL值。

六、外键(FOREIGN KEY)

  • 外键用于建立表之间的关系，它引用另一个表的主键，将两个表关联起来。
  • 外键通常位于一个表中，指向另一个表中的主键列，用于建立数据之间的关联。
  • 外键的作用是维护数据表之间的引用完整性，确保引用的数据存在于关联的表中。
  • 当在父表中插入、更新或删除记录时，外键可用于自动处理相关联的子表的操作，以保持数据一致性。

七、索引（Index）：

  • 索引用于加快数据库查询操作，通过创建一个特定列或列组的快速访问结构，从而提高查询性能。
  • 索引允许数据库更快地找到满足特定条件的行，而不需要扫描整个表。
  • 索引可以单独创建，也可以在表的创建过程中定义。
  • 尽管索引提高了查询速度，但同时也会增加写入操作的开销，因为每次插入、更新或删除数据时，都需要维护索引的正确性。

主键 外键和索引

Alt text

案例：

create database y37;
CREATE TABLE y37.student (
    id INT NOT NULL,
    PRIMARY KEY (id)
);

create  table  y37.stu_info(
    id INT,
    student_id INT,
    INDEX stude_id (student_id),
    FOREIGN KEY (student_id)
        REFERENCES student(id)
        ON DELETE CASCADE
);
MySQL
上一篇:
mysql 配置环境变量
下一篇:
mysql install mysql 8
本文目录
本文目录
    •

    Copyright © 2019 - 2020 yuang01 | Powered by Hexo | Theme Bamboo

    本站总访问量： 次 |