Mysql约束条件以及各类情况整理

 

mysql建表约束.sql

—————主键约束 ————-
它能够唯一确定一张表中的一条记录,也就是我们通过给某个字段添加约束,就可以使得该字段不重复且不为空

create table user(
id int primary key,
name varchar(20)
);
mysql> desc user;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+——-+————-+——+—–+———+——-+

insert into user values(1,’张三’);
mysql> insert into user values(1,’张三’);
Query OK, 1 row affected
mysql> insert into user values(1,’张三’);
1062 – Duplicate entry ‘1’ for key ‘PRIMARY’

主键只能独自存在一个!!!!

mysql> insert into user values(2,’张三’);
mysql> select * from user;
+—-+——+
| id | name |
+—-+——+
| 1 | 张三 |
| 2 | 张三 |
+—-+——+

mysql> insert into user values(null,’张三’);
1048 – Column ‘id’ cannot be null
—-主键约束不能为空!!!!!——

———联合主键———
–只要联合的主键值加起来不重复就可以,但两个主键都不可以为空!
create table user2(
id int,
name varchar(20),
password varchar(20),
primary key(id,name)
);
mysql> insert into user2 values(1,’张三’,’123′);
Query OK, 1 row affected
mysql> insert into user2 values(2,’张三’,’123′);
Query OK, 1 row affected

mysql> select * from user2;
+—-+——+———-+
| id | name | password |
+—-+——+———-+
| 1 | 张三 | 123 |
| 2 | 张三 | 123 |
+—-+——+———-+
2 rows in set

——自增约束———–
name varchar(20))’ at line 3
mysql> create table user3(
-> id int primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected
mysql> insert into user3(name) values(‘张三’);
Query OK, 1 row affected
mysql> select * from user3;
+—-+——+
| id | name |
+—-+——+
| 1 | 张三 |
+—-+——+
1 row in set

mysql> insert into user3(name) values(‘张三’);
Query OK, 1 row affected
mysql> select * from user3;
+—-+——+
| id | name |
+—-+——+
| 1 | 张三 |
| 2 | 张三 |
+—-+——+
2 rows in set

————-如果我们创建表的时候,忘记创建吗主键约束了?该怎么办?——
create table user4(
id int ,
name varchar(20)
);
mysql> desc user4;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set
—-修改表结构,添加主键
alter table user4 add primary key(id);
mysql> desc user4;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set

—–如何删除?
alter table user4 drop primary key;
mysql> desc user4;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set
——使用modify修改字段,添加约束
alter table user4 modify id int primary key;
mysql> desc user4;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set

————-唯一约束———-
create table user5(
id int,
name varchar(20)
);
alter table user5 add unique(name);
mysql> desc user5;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set

insert into user5 values(1,’张三’);
mysql> insert into user5 values(1,’张三’);
1062 – Duplicate entry ‘张三’ for key ‘name’
—–name=李四
mysql> insert into user5 values(1,’李四’);
Query OK, 1 row affected

—————申请unique的几种方法——————
create table user6(
id int,
name varchar(20),
unique(name)
);
create table user7(
id int,
name varchar(20) unique
);
—————–unique(id,name)表示两个键在一起不重复就行
create table use8(
id int,
name varchar(20) unique
unique(id,name)
);

—————–如何删除唯一约束?
alter table user7 drop index name;————不加index会把name整个字段给删除了
mysql> desc user7;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set

————–modeify添加
alter table user7 modify name varchar(20) unique;
mysql> desc user7;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set

—–总结:
1.建表的时候就添加约束
2.可以使用alter….add…..
3.alter…modif…….
4.删除alter…..drop…….

————————–非空约束————–
—-约束的字段不能为空null
mysql> create table user9(
-> id int,
-> name varchar(20) not null unique);
Query OK, 0 rows affected
mysql> desc user9;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
+——-+————-+——+—–+———+——-+
id为null,insert into user9(id) values(1)不可以执行,name不能为Null
mysql> insert into user9 values(1,”张三”)

—–默认约束——————–
—就是当我们插入字段值的时候,如果没有传值,就会使用默认值deafault
mysql> create table user10(
-> id int,
-> name varchar(20),
-> age int default 10
-> );
Query OK, 0 rows affected
mysql> desc user10;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | 10 | |
+——-+————-+——+—–+———+——-+
3 rows in set
mysql> insert into user10 (id,name) values(1,’李四’);///////这里没有定义age的值,但默认为10
Query OK, 1 row affected
mysql> select*from user10;
+—-+——+—–+
| id | name | age |
+—-+——+—–+
| 1 | 李四 | 10 |
+—-+——+—–+
—————————传了值就不会使用默认值——————
mysql> insert into user10 values(1,’zhangsan’,19);
Query OK, 1 row affected
mysql> select * from user10;
+—-+———-+—–+
| id | name | age |
+—-+———-+—–+
| 1 | 李四 | 10 |
| 1 | zhangsan | 19 |
+—-+———-+—–+
2 rows in set
————————————————————

————————外键约束————————–
–涉及到两个表:父表,子表
–主表,附表。

–班级
create table classes(
id int primary key,
name varchar(20)
);

create table students(
id int primary key,
name varchar(20),
class_id int,
foreign key(class_id) references classes(id)
);

mysql> desc students;
+———-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+——-+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+———-+————-+——+—–+———+——-+
3 rows in set

insert into classes values(1,’一班’);
insert into classes values(2,’二班’);
insert into classes values(3,’三班’);
insert into classes values(4,’四班’);
mysql> select * from classes;
+—-+——–+
| id | name |
+—-+——–+
| 1 | 一班 |
| 2 | 浜岀彮 |
| 3 | 涓夌彮 |
| 4 | 鍥涚彮 |
+—-+——–+
4 rows in set——————这里我是四个命令一起执行的,出现了乱码,要注意有些编辑器不能将代码一起复制进去执行

mysql> insert into students values(1001,’张三’,1);
Query OK, 1 row affected

mysql> insert into students values(1001,’张三’,6);
1062 – Duplicate entry ‘1001’ for key ‘PRIMARY’
在学生表里只能插入班级表里存在班级的班级号5,这里并不存在6班,所以出现了错误

–1.主表(父表)classes中没有的数据值,在副表(子表)中,是不可以使用的
–2.主表中的记录被副表引用,是不可以被删除的。
mysql> delete from classes where id= 1;
1451 – Cannot delete or update a parent row: a foreign key constraint fails (`gramdem`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

———————————————————————–栗子——————
create table depatment(
Department int not null primary key comment ‘部门编号,关键字’,
DepartmentName varchar(30) not null comment ‘部门名称’,
Manager char(8) comment ‘部门主管’,
Depart_Desdription varchar(50) comment ‘备注,有关部门的说明’
);

create table Employee(
EmployeeID int not null primary key,
EmployeeName varchar(50) not null comment ‘姓名’,
Sex char(2) not null check(sex in(‘男’,’女’)),
irthDate datetime comment ‘出生年月’,
HireDate datetime comment ‘聘任日期’,
Salary decimal comment ‘工资’,
DepartmentID int not null ,
foreign key(DepartmentID) references depatment(Department)
);

create table Customer(
CustomerID int not null primary key,
CompanyName varchar(50) not null comment ‘公司名称’,
ContacName char(8) not null comment ‘联系人的姓名’,
Phone varchar(15) comment ‘联系人电话’,
address varchar(100) comment ‘客户的地址’,
EmailAddress varchar(20) comment ‘客户的Email地址’
);
create table Provider(
ProviderID int not null primary key,
ProviderName varchar(50) comment ‘供应商名称’,
ContactName char(8) comment ‘联系人的姓名’,
ProviderPhone varchar(15) comment ‘供应商联系电话’,
Provideraddress varchar(100) comment ‘供应商的地址’,
ProviderEmail varchar(20) comment ‘供应商的email地址’
);
create table Product(
ProductId int not null primary key,
ProductName varchar(50) not null comment ‘商品名称’,
price decimal(18,2) comment ‘单价’,
ProductStockNumber int comment ‘现有库存量’,
ProductSellNumber int comment ‘已经销售的商品量’
);
create table Sell_Order(
SellOrderID int not null primary key,
ProductID int comment ‘商品编号,描述该订单定够的商品’,
EmployeeID int comment’员工号,描述该订单由哪位员工签订’,
CustomID int comment ‘客户号,描述该订单与哪位客户签订’,
SellOrderNumber int comment ‘订货数量’,
SellOrderDate datetime comment’订单签订的日期’,
foreign key(ProductID) references Product(ProductID),
foreign key(EmployeeID) references Employee(EmployeeID),
foreign key(CustomId) references Customer(CustomerID)
);
create table Purchase_order(
PurchaseOrderId int not null primary key,
ProductID int comment ‘商品编号,描述该订单定够的商品’,
EmployeeID int comment’员工号,描述该订单由哪位员工签订’,
CustomID int comment ‘客户号,描述该订单与哪位客户签订’,
PurchaseOrderNumber int comment ‘采购数量’,
PurchaseOrderDate datetime comment ‘订单签订的日期’,
foreign key(ProductID) references Product(ProductID),
foreign key(EmployeeID) references Employee(EmployeeID),
foreign key(CustomId) references Customer(CustomerID)
);

本文发表于www.hanzier.com,未经许可,严禁转载!
菡秭技术 » Mysql约束条件以及各类情况整理

发表评论

Copy Protected by Chetan's WP-Copyprotect.