Mysql基础(二)约束

什么是约束?

约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性、唯一性。

常见的约束方式

  • 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)

  1. 解释:索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)
  2. 优点:加快搜索速度,减少查询时间。
  3. 缺点:

– 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 约束(外键)名称

Mysql基础(二)约束》有2个想法

发表评论