mysql之基本数据类型及各种约束条件

最新推荐文章于 2021-11-11 19:20:35 发布

BigMasterSun

最新推荐文章于 2021-11-11 19:20:35 发布

阅读量277

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_40406241/article/details/90173714

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、创建表的完整语法:

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

在创建表的时候有三点需要注意的地方
1.在一张表内字段名不能重复
2.字段和类型是必须要有的，而宽度和约束条件是可选的
3.最后一个字段后面不能加逗号！！！

宽度:对存储数据的限制
create database day38;
use day38;
create table t1(name char);
insert into t1 values('jason');

插入语句执行后有两种结果：
# 1.能够成功但是显示的时候只显示一位（因为表没有设置严格模式）
# 2.第二种情况，直接报错

"""
1.没有安全模式的数据库版本，能够存放数据但是只会存进去一个j
2.最新（5.7以后）数据库版本直接报错提示无法存储:Data too long for column 'name' at row 1
"""
select * from t1;

约束条件
create table t2(name char(16) not null);

字段类型与约束条件之间的关系
字段类型限制的是存储数据的类型
约束条件是基于字段类型之上的附加的额外限制

二、基本数据类型

1、整数类型

整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

作用：存储年龄，等级，id，各种号码等

1.1、TINYINT 默认有正负号

--设置有符号的tinyint
mysql> create table t3(id tinyint);  --默认是有符号数，即有正负号
Query OK, 0 rows affected (0.79 sec)

mysql> desc t3;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.07 sec)

mysql> insert into t3 values(-129),(-128),(127),(128);  --没有设置安全模式的不会报错，会把-129存成-128，128存成127，因为其范围就是[-128，127]。
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t3 values(-128),(127);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+------+
| id   |
+------+
| -128 |
|  127 |
+------+
2 rows in set (0.00 sec)


--设置无符号tinyint
MariaDB [db1]> create table t2(x tinyint unsigned);
MariaDB [db1]> insert into t2 values
    -> (-1),
    -> (0),
    -> (255),
    -> (256);
MariaDB [db1]> select * from t2;
+------+
| x    |
+------+
|    0 | -1存成了0
|    0 | #无符号，最小值为0
|  255 | #无符号，最大值为255
|  255 | #256存成了255
+------+

1.2、int类型

特例：唯独整型字段在设置宽度的时候，限制不是存储宽度而是显示宽度
在创建整型字段的时候，不需要指定字段宽度(整型的显示宽度)

create table t6(id int(8)); --如果存入的数字够8位正常显示，如果不够8位默认用空格填充

create table t7(id int(8) zerofill); --如果存入的数字够8位正常显示，如果不够8位用0填充

以下演示针对的是mysql5.7之前的版本：

============有符号和无符号int=============
#int默认为有符号
MariaDB [db1]> create table t3(x int); #默认为有符号整数
MariaDB [db1]> insert into t3 values
    -> (-2147483649),
    -> (-2147483648),
    -> (2147483647),
    -> (2147483648);
MariaDB [db1]> select * from t3;
+-------------+
| x           |
+-------------+
| -2147483648 | #-2147483649存成了-2147483648
| -2147483648 | #有符号，最小值为-2147483648
|  2147483647 | #有符号，最大值为2147483647
|  2147483647 | #2147483648存成了2147483647
+-------------+



#设置无符号int
MariaDB [db1]> create table t4(x int unsigned);
MariaDB [db1]> insert into t4 values
    -> (-1),
    -> (0),
    -> (4294967295),
    -> (4294967296);
MariaDB [db1]> select * from t4;
+------------+
| x          |
+------------+
|          0 | #-1存成了0
|          0 | #无符号，最小值为0
| 4294967295 | #无符号，最大值为4294967295
| 4294967295 | #4294967296存成了4294967295
+------------+

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

无符号最大值为：4294967296-1

有符号最大值：2147483648-1

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

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

2、like模糊查询

%:匹配任意数量的任意字符
_:匹配单个数量的任意字符
show variables like "%mode%";

3、设置严格模式

set session:当前窗口下有效
set global:全局有效
set global sql_mode = "STRICT_TRANS_TABLES";
设置完成后需要退出客户端，重新登录客户端即可，不需要重启服务端

4、浮点型

定点数类型 DEC等同于DECIMAL　　

浮点类型：FLOAT DOUBLE

作用：存储薪资、身高、体重、体质参数等

float(255,30)
double(255,30)
decimal(65,30)

三者区别:
精度不同:
精度由低到高
float
double
decimal

mysql> create table t10(id float(255,30));
Query OK, 0 rows affected (0.61 sec)

mysql>         create table t11(id double(255,30));
Query OK, 0 rows affected (0.42 sec)

mysql>         create table t12(id decimal(65,30));
Query OK, 0 rows affected (1.04 sec)

mysql> insert into t10 values(1.111111111111111111111111111111);
Query OK, 1 row affected (0.14 sec)

mysql>         insert into t11 values(1.111111111111111111111111111111);
Query OK, 1 row affected (0.05 sec)

mysql>         insert into t12 values(1.111111111111111111111111111111);
Query OK, 1 row affected (0.09 sec)
mysql> select * from t10;  --随着小数的增多，精度开始不准确
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t11; --精度比float要准确点，但随着小数的增多，同样变得不准确
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t12; --精度始终准确,d为30，于是只留了30位小数
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+

5、字符型

char:定长
varchar:变长

create table t13(name char(4));  # name字段固定存储四个长度，如果超了直接报错，如果不够默认用空格填充
create table t14(name varchar(4)); # name字段如果超了直接报错，如果不够按实际长度存储

insert into t13 values('a');  --存储结果：'a    '
insert into t14 values('a');  --存储结果：'a'

char_length(name) # 统计字段值所对应的长度

select char_length(name) from t13;
select char_length(name) from t14;

查询对应长度两个都是1，因为没有设置安全模式，如果不想让mysql帮你做自动去除末尾空格的操作，需要再添加一个模式。
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";

char VS varchar
char(4) 定长
优点:存取速度都快，简单粗暴
缺点:浪费空间

varchar(4) 变
给varchar类型的数据自动加上报头用来标识数据的长度
报头通常占1~2bytes
缺点:存取速度都慢
优点:节省空间

6、日期类型

date 年月日
time 时分秒
datetime 年月日时分秒
year 年

mysql> create table t15(
    ->             id int,
    ->             birth date,
    ->             class_time time,
    ->             reg_time datetime,
    ->             yy year
    ->             );
Query OK, 0 rows affected (0.47 sec)

mysql> insert into t15 values(1,"2000-01-21","11:11:11","2019-01-21 11:11:11","2019");
Query OK, 1 row affected (0.10 sec)

mysql> select * from t15;
+------+------------+------------+---------------------+------+
| id   | birth      | class_time | reg_time            | yy   |
+------+------------+------------+---------------------+------+
|    1 | 2000-01-21 | 11:11:11   | 2019-01-21 11:11:11 | 2019 |
+------+------------+------------+---------------------+------+

7、枚举(enum):多选一

mysql> create table t16(
    ->     id int,
    ->     name char(6),
    ->     gender enum('male','female','others')
    -> );
Query OK, 0 rows affected (0.42 sec)

mysql> insert into t16 values(1,'egon','xxx');  --报错，只能从enum给出的选，与oracle的检查约束有些类似
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into t16 values(1,'egon','others');

8、集合(set):多选多虽然是多选多但是你可以只选一个

mysql> create table t17(
    -> id int,
    -> name char(4),
    -> gender enum('male','female','others'),
    -> hobby set('read','DJ','DBJ','run')
    -> );
Query OK, 0 rows affected (0.58 sec)

mysql> insert into t17 values (1,'tank','male','read,DBJ,DJ');
Query OK, 1 row affected (0.11 sec)

mysql> insert into t17 values (1,'tank','male','read');
Query OK, 1 row affected (0.09 sec)

三、约束条件

PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

3.1、not null:限制字段不能为空

mysql> create table t20(
    -> id int,
    -> name char(4) not null);
Query OK, 0 rows affected (0.65 sec)
mysql> insert into t20 values(1,null);  --设置非空约束后，名字不能为空值
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into t20 values(1,'egon');
Query OK, 1 row affected (0.11 sec)

3.2、default：设置字段默认值

mysql> create table t21(
    -> id int,
    -> name char(4),
    -> gender enum('male','female') default 'male');
Query OK, 0 rows affected (0.44 sec)

mysql> insert into t21 values (1,'egon');  --插入数据要与字段一一对应
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t21(id,name) values (1,'egon');  -- 设置了默认值，因此可以不传gender
Query OK, 1 row affected (0.11 sec)

mysql> insert into t21 values (1,'egon','female');  --可以传gender
Query OK, 1 row affected (0.09 sec)

mysql> select * from t21;
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    1 | egon | male   |
|    1 | egon | female |
+------+------+--------+
2 rows in set (0.00 sec)

通常情况下not null和default是配合使用的

3.3、唯一性约束：某个字段对于的值在当前表中是唯一的

3.3.1、单列唯一

mysql> create table t22(
    -> id int unique,  # 单个字段
    -> name char(10));
Query OK, 0 rows affected (0.53 sec)

mysql> insert into t22 values(1,'egon'),(1,'tank');  --插入数据的id必须唯一
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
mysql> insert into t22 values(1,'egon'),(2,'tank');
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

3.3.2、联合唯一（服务器ip和port）

mysql> create table t23(
    -> host char(16),
    -> port int,
    -> unique(host,port));  # 多个字段
Query OK, 0 rows affected (0.64 sec)

mysql> insert into t23 values('127.0.0.1',8080),('127.0.0.1',8081);
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t23 values('127.0.0.1',8080),('127.1.1.1',8080);
ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'host'
mysql> insert into t23 values('127.0.0.1',8080),('127.0.0.1',8080);
ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'host'
mysql> insert into t23 values('127.0.0.1',9090),('127.0.0.1',9090);
ERROR 1062 (23000): Duplicate entry '127.0.0.1-9090' for key 'host'
mysql> select * from t23;
+-----------+------+
| host      | port |
+-----------+------+
| 127.0.0.1 | 8080 |
| 127.0.0.1 | 8081 |
+-----------+------+
2 rows in set (0.00 sec)

3.4、主键约束：

单从限制条件上来说，它就相当于not null + unique非空且唯一

3.4.1、单列作为主键

mysql> create table t26(
    ->     id int primary key,
    ->     name char(16)
    ->     )engine=innodb;
Query OK, 0 rows affected (0.57 sec)

mysql> desc t26;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(16) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

innodb引擎中，所有的表都必须有且只有一个主键，它是innodb引擎用来组织数据的依据，innodb称之为索引组织表

除了建表时直接指定，还可以在后面添加

# 在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); # 创建主键并为其命名pk_name

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

强调：

1、一张表中必须有且只有一个主键。如果没有设置主键，那么会从上到下搜索直到遇到一个非空且唯一的字段自动将其设置为主键。

mysql> create table t25(
    ->         id int,
    ->           name char(16),
    ->           age int not null unique,
    ->           addr char(16) not null unique
    ->     )engine=innodb;
Query OK, 0 rows affected (0.47 sec)

mysql>     desc t25;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(16) | YES  |     | NULL    |       |
| age   | int(11)  | NO   | PRI | NULL    |       |  --自动设为主键
| addr  | char(16) | NO   | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2、如果表里面没有指定任何可以设置为主键的字段，那么innodb会采用自己默认的一个隐藏字段作为主键，隐藏意味着你在查询的时候无法根据这个主键字段加速查询了。

索引：类似于书的目录，没有主键就要一页一页的查

3、一张表中通常都应该有一个id字段，并且通常将该id字段作为主键。

3.4.2、联合主键

# ==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

3.5、auto_increment:自增

约束字段为自动增长，被约束的字段必须同时被key约束

# 不指定id，则自动增长
mysql> create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+


#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
+----+------+------+
row in set (0.00 sec)