Contents
什么是约束?
约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性、唯一性。
常见的约束方式
- 1.字段修饰符
- 2.清空列表
- 3.索引
- 4.外键
- 5.视图
字段修饰符
Mysql的字段 | 作用 |
---|---|
auto_increment | 自动按照上一数值+1.且定义了数值唯一性(primary key) |
primary key | 每条记录都必须具有唯一性,内容不允许为空,内容必须唯一。 |
not null 和 null | 空值是不占用空间的 null空占用空间 |
defaults | 如果时间字段,默认为当前的时间,当你插入0时,默认为当前时间。如果是enum类型,默认为第一个元素。(枚举选择第一个) |
enum类型 | 默认为第一个元素。(枚举选择第一个) |
Not null 不允许赋null值
Create table t4(
Name char(5) not null,
Level int(3) zerofill
);
Insert into t2 values(null,99) #null不能用双引号括起来
错误实例:
Insert into t2 values(“null”,99) #null括起来表示以字符串形式写入表
Insert into t2 values(“”,99) #不输入不代表空值,null是唯一空值
auto_increment 字段约束
create table itmes(id int not null auto_increment primary key , label varchar (20))not null;
insert into items (id) values(5);
inserr into items (label) values('asd');
select * from itmes;
自动按照上一数值+1.且定义了数值唯一性(primary key)
primary key 主键
create table firewall(host varchar(15) not null,port smallint(4) not null,access enum('deny','allow') not null,primary key(host,port));
清除表中的记录
清空表中的所有记录:
方法一:
delete 不加 where 条件,清空所有的表记录。*但是DELECT不会清除auto_increment的值。
delete from items 删除所有的记录但是自动增长的字段没有完全删除。
方法二:
解决无法删除自动增长的字段记录:
truncate:删除表的所有记录,并清零auto_incremengt值,新插入的数值从1开始
语法:trucate table 字段名
truncate table items;
字段键值(Key键值类型:普通索引 唯一索引 全文索引 主键 外键)
设置在表中字段上的,作用时约束如何给字段赋值。同时会给字段做索引
解释:索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)
优点:
– 加快查询表记录的速度
缺点:
– 1.减慢编辑表记录的速度,且占用磁盘的物理存储空间
– 2.索引是以文件储存的。如果索引过多,占用磁盘空间教大,且它影响:insert,update,delete执行的速度。
– 3.当表中数据更新后,索引也要同步更新,这就降低了效率。
索引 (index)
- 解释:索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)
- 优点:加快搜索速度,减少查询时间。
- 缺点:
– 1.索引是以文件储存的。如果索引过多,占用磁盘空间教大,且它影响:insert,update,delete执行的速度。
– 2.当表中数据更新后,索引也要同步更新,这就降低了效率。
-一个表只能有一个primary key主键
-对应的字段值不允许有重复,且不允许赋NULL值
-如果有多个字段都作为primary key,称为复合主键,必须一起创建
-主键字段的Key标志是PRI
-通常与AUTO_INCREMENT连用
-经常把表中能够唯一标识记录的字段设置为主键字段(记录编号字段)
索引的类型:
(1)普通:不具备唯一性,只是加快查询速度。
创建索引
方法一:
create table 表名(
#列定义
index/key 索引名称 (字段) #索引的名称可以不加,不加以字段名称作为索引名
index/key 索引名称 (字段) #key=index通用
);
方法二:
create table demo(id int not null auto_increment primary key ,name varchar(8) not null,pwd varchar(20),key (pwd) );
当表创建完成的时候,使用alter为表添加索引:
alter table 表名 add index/key 索引名称 (字段一,字段二,...);
desc 表名 查看索引
key 列是MUL,就是一般索引,该列的值可以重复且可以含有null
删除索引
alter table 表名 drop key 索引名称;
(2)唯一性
创建索引
方法一:
create table 表名(
#列定义
unique key 索引名称 (字段);
);
3.主键索引
4.复合索引
全文索引(fulltext index)
说明:为目前的搜索引擎的一种关键性技术,它能够利用【分词技术】【算法】分析出文本文字中关键字迹频率及其重要性,智能的筛选出我们需要的结果。
mysql在数据量比较大的情况下,高并发连接的情况下。
例子:select 语句 where bname like ‘%网%’;
使用%通配符,不通过索引,直接扫描全表。
- 缺点:数据库资源占用大。
*从mysql 5.7.6开始mysql内置ngram全文检索插件,针对中文。
创建全文索引:
方法一:
create tabke 表名(
列定义
fulltext key 索引名 (字段)
);
方法二:
alter table 表名 add fulltext 索引名 (字段);
强烈注意:mysql自带的全文索引只能用于数据库引擎为myisam的数据表,其他格式的不支持全文检索。
索引设计的原则:
- 1.索引并非越多越好
- 2.数据不大不需要
- 3.列中的值变化不多不需要建立索引 row id
- 4.经常排序(order by 字段)和分组(group by 字段)的列最好建立索引
- 5.唯一性约束对应使用唯一性索引
什么外键约束呢?
foreign key 就是表与表之间的某种约定的关系,由于这一种关系的存在,我们能够让表与表之间的数据更加的完整关联性更强。
举个例子:2张表格
一张订单表有用户名之类的
第二张也是相关的表
如果删除表1中的某个用户的数据。在表二中就无法与之相对应的数据,会错误。
所以建立外键约束,表1删除,表二相同的用户数据一并删除,保证数据的完整性。
创建外界约束
外键:每次插入或更新时候都会检查数据的完整性。
方法一:
语法
create table 数据表名称(
【CONSTARINT[约束名称]】foreing key 【外键字段】
references 【外键表名】(外键字段,....)
【on delete cascade】级联删除
【on update cascade】级联更新
);
级联意思就是当你更新或删除主键表,那外键表也会一起更新或者删除。
精简后的语法:
foreign key 当前表的字段 refernces 外部表名 (关联的字段) ENGINE=innodb
注意点:
创建成功必须满足4个条件
1.确保参照的表和字段存在
2.组成外键的字段被索引
3.必须使用engine指定存储引擎为:innodb
4.外键字段和关联字段,数据类型必须一致
例子创建两个相关联的数据表:
user用户表:
create table `user`(id int(11) not null auto_increment,name varchar(50)not null default '',sex int(1)not null default '0',primary key(id))ENGINE=innodb;
订单表:
create table `order`(o_id int(11) auto_increment,u_id int(11) default '0',username varchar(50),money int(11),primary key(o_id),index(u_id),foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb;
插入数据
订单表:
insert into `order` (u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256);
user用户表:
insert into firewall values('192.168.1.1',21,'deny');
insert into firewall values('192.168.1.2',22,'deny');
insert into firewall values('192.168.1.3',23,'allow');
图形化:
mysql> select * from `order`;
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 1 | 1 | HA | 234 |
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
mysql> select * from `user`;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | HA | 1 |
| 2 | LB | 2 |
| 3 | HPC | 1 |
+----+------+-----+
【删除user表ID=1的,查看order表相关联是否一并删除:】
mysql> delete from user where id=1;
Query OK, 1 row affected (0.32 sec)
mysql> select * from `order`;
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
【更新修改ID值,级联条件下on update cascade】
mysql> updata user set id=6 where id=2;
mysql> select * from `order`;
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 6 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
方法二:
先建立好表,再修改关联性
语法:
alter table 数据表名称 add
[constraint [约束名称] ] foreign key (外键字段,..) references 数据表(参照字段,...)
[on update cascade|set null|no action]
[on delete cascade|set null|no action]
)
简写例子:
alter table order add foreign key (u_id)references user(id) on delete cascade on update cascade,ENGINE=innodb;
删除外键
语法:
alter table 数据表名称 drop foreign key 约束(外键)名称
We’re a group of volunteers and starting a new scheme in our community. Your web site offered us with valuable info to work on. You have done a formidable job and our whole community will be grateful to you.
But wanna say that this is very beneficial , Thanks for taking your time to write this.