5.约束条件

最新推荐文章于 2024-01-28 19:55:08 发布

开局签到Python基础

最新推荐文章于 2024-01-28 19:55:08 发布

阅读量162

点赞数

分类专栏： 2.MySQL

风

本文链接：https://blog.csdn.net/qq_46137324/article/details/119214834

版权

2.MySQL 专栏收录该内容

15 篇文章 2 订阅

订阅专栏

本文介绍了MySQL中插入数据的两种方式：顺序传值和指定字段传值，并展示了默认值设定及唯一约束的用法。主键的概念被详细解释，包括其在InnoDB存储引擎中的重要性，以及单个字段和联合字段作为主键的情况。此外，还讨论了自增字段的使用，以及如何通过`delete`和`truncate`命令影响自增计数。

摘要由CSDN通过智能技术生成

文章目录

1.传值

1.2 顺序传值

依据定义时的顺序依次传入值。
insert into 表名 values(对应参数1, 对应参数2);

create table t1(id int, name varchar(16));
insert into t1 values(1, 'kid');

mysql> create table t1(id int, name varchar(16));
Query OK, 0 rows affected (0.02 sec)

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

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | kid  |
+------+------+

1.2指定字段

自己指定字段的顺序，按指定字段的顺序传入值。
insert into 表名 (参数z， 参数a) values(参数z， 参数a);

mysql> insert into t2(name, id) values ('kid', 18);
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | kid  |
|   18 | kid  |
+------+------+

2.默认值

defauit 默认值

create table t3(
	id int,
	name varchar(16),
	gender enum('male', 'female', 'others') default 'male'
);

mysql> desc t3;
+--------+--------------------------------+------+-----+---------+-------+
| Field  | Type                           | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| id     | int(11)                        | YES  |     | NULL    |       |
| name   | varchar(16)                    | YES  |     | NULL    |       |
| gender | enum('male','female','others') | YES  |     | male(设置默认)   |   
+--------+--------------------------------+------+-----+---------+------

insert into t3(id, name) values(1, 'kid');
insert into t3 values(2, 'qz', 'female');

mysql> insert into t3(id, name) values(1, 'kid');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values(2, 'qz', 'female');
Query OK, 1 row affected (0.01 sec)

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

3.唯一

unique 限制字段唯一

单列唯一：一个字段唯一。
联合唯一：单个都可以重复，但是加在一起必须唯一。

creatE table t4(
id int unique,      # 设置id唯一
name varchar(16)
);

mysql> desc t4;               # 唯一 Key UNI
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

insert into t4 values(1, 'kid');
insert into t4 values(1, 'qz');

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

mysql> insert into t4 values(1, 'qz');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

create table t5(
	id int,
	ip varchar(16),
	port int,
	unique(ip, port)   # 设置联合唯一
);

insert into t5 values(1, '127.0.0.1', 3306);
insert into t5 values(2, '127.0.0.1', 3305);
insert into t5 values(3, '127.0.0.2', 3306);
insert into t5 values(4, '127.0.0.1', 3306);

mysql> create table t5(
    -> id int,
    -> ip varchar(16),
    -> port int,
    -> unique(ip, port)   # 设置联合唯一
    -> );
Query OK, 0 rows affected (0.01 sec)

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

mysql> insert into t5 values(2, '127.0.0.1', 3305);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(3, '127.0.0.2', 3306);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(4, '127.0.0.1', 3306);
ERROR 1062 (23000): Duplicate entry '127.0.0.1-3306' for key 'ip' # 报错
mysql> select * from t5;
+------+-----------+------+
| id   | ip        | port |
+------+-----------+------+
|    1 | 127.0.0.1 | 3306 |
|    2 | 127.0.0.1 | 3305 |
|    3 | 127.0.0.2 | 3306 |
+------+-----------+------+

4.主键

primary key 主键
1.单从效果上来看 primary key 等价于 not null + unique 非空且唯一。
2.Innodb 存储引擎在创建表的时候必须要有primary key,它是这个引擎组织的依据。（类试书的目录，能提高查询的效率并且也是建表的依据）

2.1 一张表中有且只有一个主键，如果你没有设置主键，那么会从上至下搜索直到遇到一个非空且唯一的字段将它自动升级为主键。
2.2 如果表中没有主键也没有非空且唯一的字段，那么Innodb会采用自己内部提供的一个隐藏字段作为主键。（隐藏意味着你无法使用它，就 无法提升查询速度）

2.3 单个字段组件：一张表中通常应该有一个主键字段并且通常将表头编号id字段作为主键。
2.4 多个字段联合主键： 多个字段联合起来作为变的主键本质还是一个主键。

4.1情况1

create table t6(id int primary key);

insert into t6 values(null);   # 直接报错
insert into t6 values(1),(1);  # 直接报错

mysql> create table t6(id int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t6;  # Null NO Key PRI
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> insert into t6 values(null);    # 不能为空
ERROR 1048 (23000): Column 'id' cannot be null

mysql>  insert into t6 values(1),(1);  # id 唯一
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

4.2情况2

# 2.1
create table t7(
	id int,
	name varchar(16),
	age int not null unique,
	addr varchar(32) not null unique
);

mysql> create table t7(
    -> id int,
    -> name varchar(16),
    -> age int not null unique,
    -> addr varchar(32) not null unique
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t7;   # 没有设置primary key 第一个非空且唯一 被设置为PRI 升级为主键
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int(11)     | NO   | PRI | NULL    |       |
| addr  | varchar(32) | NO   | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

# 2.3 单个字段组件
create table t8(
	id int primary key,
	name varchar(16)
);

mysql> create table t8(
    -> id int primary key,
    -> name varchar(16)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

# 2.4  联合组建
create table t9(
ip varchar(16),
port int,
primary key(ip, port)
);

mysql> create table t9(
    -> ip varchar(16),
    -> port int,
    -> primary key(ip, port)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ip    | varchar(16) | NO   | PRI |         |       |
| port  | int(11)     | NO   | PRI | 0       |       |
+-------+-------------+------+-----+---------+-------+

4.3 总结

以后在创建表的id（数据的唯一标识id uid sid）等字段的时候添加。

5.自增

auto_inctrment 自增 一帮配合primary key使用。
auto_increment 通常加在主键上，不能给普通字段加。

create table t1(
	id int primary key auto_increment,
	name varchar(16)
);

mysql> create table t1(
    -> id int primary key auto_increment,
    -> name varchar(16)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

insert into t1 (name) values ('kid'),('qz'),('xxx');

mysql> insert into t1 (name) values ('kid'),('qz'),('xxx');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | kid  |
|  2 | qz   |
|  3 | xxx  |
+----+------+

5.1 自曾计算器

delete from 在删除表中的数据主键的自增计算不会重置。
truncate 表名称 清空表数据并且重置主键。

mysql> delete from t1;
Query OK, 3 rows affected (0.00 sec)

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

insert into t1 (name) values ('A'),('B'),('C');

mysql> insert into t1 (name) values ('A'),('B'),('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  4 | A    |
|  5 | B    |
|  6 | C    |
+----+------+

truncate t1;

mysql> truncate t1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (name) values ('A'),('B'),('C');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+

mysql> truncate t1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (name) values ('A'),('B'),('C');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+