相关文章推荐
豁达的帽子  ·  王亚军在延边朝鲜族自治州考察调研·  2 月前    · 
酒量大的钥匙  ·  “披着头发”用英语怎么说？_手机搜狐网·  6 月前    · 
文质彬彬的热带鱼  ·  广告中的“创意”英文怎么说·  1 年前    · 
会开车的小马驹  ·  暨南大学思想政治教育专业高级研修课程2021 ...·  1 年前    · 
稳重的胡萝卜  ·  马嘉祺可爱系图片：花儿与你都很好~·  1 年前    · 
小百科  ›  mysql 主键 mysql主键可以为空吗_kekenai的技术博客_51CTO博客
mysql select varchar 主键约束
骑白马的豆芽
1 年前

主键不能为空(NULL)、不能重复！！

唯一约束不能重复，但可以为空。

1.单主键约束 primary key 

mysql> create table user1(
    -> id int primary key,
    -> name varchar(20)
Query OK, 0 rows affected (0.01 sec)
mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

由于id是主键，因此插入的数据不能有相同的id： 

mysql> insert into user1 values(1,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user1 values(1,'李四');
ERROR 1062 (23000): Duplicate entry '1' for key 'user1.PRIMARY'

但非主键相同可以： 

mysql> insert into user1 values(2,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> select *from user1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 张三   |
+----+--------+
2 rows in set (0.00 sec)

主键不能为空，非主键可以： 

mysql> insert into user1 values(NULL,'张三');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into user1 values(3,NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select *from user1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 张三   |
|  3 | NULL   |
+----+--------+
3 rows in set (0.01 sec)

2.联合主键约束 

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key(id,name)
Query OK, 0 rows affected (0.01 sec)
mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

添加数据，只要主键的两项不全一致就行： 

mysql> insert into user2 values(1,'张三','123');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user2 values(2,'张三','123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user2 values(1,'李四','123');
Query OK, 1 row affected (0.00 sec)
mysql> select *from user2;
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  1 | 张三   | 123      |
|  1 | 李四   | 123      |
|  2 | 张三   | 123      |
+----+--------+----------+
3 rows in set (0.00 sec)

但主键不能为空，任何一个都不行： 

mysql> insert into user2 values(NULL,'李四','123');
ERROR 1048 (23000): Column 'id' cannot be null

3.自增约束 auto increment 

mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(20)
Query OK, 0 rows affected (0.01 sec)
mysql> desc user3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

仅添加非主键部分，id默认从1开始： 

mysql> insert into user3(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user3(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user3(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> select *from user3;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
|  3 | zhangsan |
+----+----------+
3 rows in set (0.00 sec)

如果对id有定义，可以仅添加一次： 

mysql> delete from user3 where name='zhangsan';
Query OK, 3 rows affected (0.00 sec)
mysql> insert into user3 values(202201,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user3(name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user3(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> select *from user3;
+--------+----------+
| id     | name     |
+--------+----------+
| 202201 | zhangsan |
| 202202 | zhangsan |
| 202203 | zhangsan |
+--------+----------+
3 rows in set (0.00 sec)

4.建表后添加与删除主键 

mysql> create table user4(
    -> id int,
    -> name varchar(20)
Query OK, 0 rows affected (0.00 sec)
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加主键： 

mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除主键： 

mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加和删除联合主键一样的道理： 

mysql> alter table user4 add primary key(id,name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改主键约束，和添加主键差不多，一般不用这种方法： 

mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5.唯一约束------约束修饰的字段的值不可以重复(可以为空)

5.1建表后添加 

mysql> create table user5(
    -> id int,
    -> name varchar(20)
Query OK, 0 rows affected (0.01 sec)
mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加数据(唯一约束下的name字段不能重复)： 

mysql> insert into user5 values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user5 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user5.name'
mysql> insert into user5 values(1,'lisi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user5 values(1,'NULL');
Query OK, 1 row affected (0.00 sec)
mysql> select *from user5;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    1 | lisi     |
|    1 | NULL     |
+------+----------+
3 rows in set (0.00 sec)

5.2建表时添加 

mysql> create table user6(
    -> id int,
    -> name varchar(20),
    -> unique(name)
Query OK, 0 rows affected (0.01 sec)
mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

或 

mysql> create table user7(
    -> id int,
    -> name varchar(20) unique
Query OK, 0 rows affected (0.01 sec)
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

5.3添加多个唯一约束(依旧是两个不完全一样就行) 

mysql> create table user8(
    -> id int,
    -> name varchar(20),
    -> unique(id,name)
Query OK, 0 rows affected (0.01 sec)
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into user8 values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user8 values(2,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> select *from user8;
+------+----------+
| id   | name     |
+------+----------+
| NULL | NULL     |
|    1 | zhangsan |
|    2 | zhangsan |
+------+----------+
3 rows in set (0.01 sec)

5.4删除唯一约束 

mysql> alter table user7 drop index name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

5.5修改唯一约束 

mysql> alter table user7 modify name varchar(20) unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

6.非空约束 not null 

mysql> create table user9(
    -> id int,
    -> name varchar(20) not null
Query OK, 0 rows affected (0.01 sec)
mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into user9(id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into user9 values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user9(name) values('lisi');
Query OK, 1 row affected (0.00 sec)
mysql> select *from user9;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
| NULL | lisi     |
+------+----------+
2 rows in set (0.00 sec)

7.默认约束--------当我们插入字段值的时候，如果没有传值，就会使用默认值 

mysql> insert into user10(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> select *from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
+------+----------+------+
1 row in set (0.00 sec)
mysql> insert into user10 values(1,'zhangsan',19);
Query OK, 1 row affected (0.00 sec)
mysql> select *from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
|    1 | zhangsan |   19 |
+------+----------+------+
2 rows in set (0.00 sec)

8.外键约束----------涉及到两个表(主表、副表) 

mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
Query OK, 0 rows affected (0.01 sec)
mysql> create table students(
    -> id int primary key,
    -> name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id)
Query OK, 0 rows affected (0.01 sec)
mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into classes values(1,'1班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values(2,'2班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values(3,'3班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values(4,'4班');
Query OK, 1 row affected (0.00 sec)
mysql> select *from classes;
+----+------+
| id | name |
+----+------+
|  1 | 1班  |
|  2 | 2班  |
|  3 | 3班  |
|  4 | 4班  |
+----+------+
4 rows in set (0.00 sec)

--主表classes中没有的数据值，在副表中是不可以使用的

--主表中的记录被副表引用，是不可以被删除的 

mysql> insert into students values(1001,'张三',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values(1002,'张三',2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into students values(1003,'张三',3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values(1004,'张三',4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values(1005,'张三',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
mysql> select *from students;
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
| 1001 | 张三   |        1 |
| 1002 | 张三   |        2 |
| 1003 | 张三   |        3 |
| 1004 | 张三   |        4 |
+------+--------+----------+
4 rows in set (0.00 sec)



java 按某种顺序像list中添加元素 list按顺序添加和取出

List最为Collection接口的子接口，当然可以使用Collection接口里的全部方法。而且由于List是有序集合，因此List集合里增加了一些根据索引来操作集合元素的方法：public class ListTest { public static void main(String[] args) { List names = new ArrayList();

node_module使用yarn下载后没有 node_modules

1.认识node_modulesjs 前端语言 运行在浏览器 浏览器的js 没有读写文件的功能 input:type=file 可以选中自己本地的资源Node.js:js在服务器运行（基于Node运行），能够对服务器文件进行操作读写 把Node安装再来服务器上 在Node环境下运行js，node赋予了js进行I/O操作的能力 I/O I:input输入 O：output:输出

 
推荐文章
豁达的帽子  ·  王亚军在延边朝鲜族自治州考察调研
2 月前
酒量大的钥匙  ·  “披着头发”用英语怎么说？_手机搜狐网
6 月前
文质彬彬的热带鱼  ·  广告中的“创意”英文怎么说
1 年前
会开车的小马驹  ·  暨南大学思想政治教育专业高级研修课程2021年报读指南
1 年前
稳重的胡萝卜  ·  马嘉祺可爱系图片：花儿与你都很好~
1 年前
今天看啥   ·   Py中国   ·   codingpro   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
小百科 - 百科知识指南
© 2024 ~ 沪ICP备11025650号