频道澳门葡京手机版网址
登录注册
澳门葡京手机版网址 > 网络 > 云计算 > 正文

大数据兼云计算之字段约束

2018-08-13 10:52:11           
收藏   我要投稿

字段约束

常用几种约束:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
not null:             非空约束,指定某列不为空
null:                   空约束,指定某列为空,默认会取第一字符
unique:              唯一约束,指定某列和几列组合的数据不能重复
primary key:        主键约束,指定某列的数据不能重复、唯一
foreign key:        外键,指定该列记录属于主表中的一条记录,参照另一条数据(其实就是表太大的拆表)
check:               检查,指定一个表达式,用于检验指定数据
default_value:     为列指定默认值。如果没有为列指定默认值,mysql> 自动地分配一个。如果列可以取NULL作为值,缺省值是NULL。
                          如果列被声明为NOT NULL,缺省值取决于列类型的value值
auto_increment: 自增模式,设置自增后在插入数据的时候就不需要给该列插入值了。
index:               作用加快查询速度,但会带来增删改的速度
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

注意:
------------------------------------------------------------------------------------
不支撑check约束,但可以使用check约束,而没有任何效果;


根据约束数据列限制,约束可分为:
------------------------------------------------------------------------------------
单列约束:每个约束只约束一列
多列约束:每个约束约束多列数据


表级约束和列级约束
------------------------------------------------------------------------------------
1. 对一个数据列建立的约束,称为列级约束《实际开发中多用》
2. 对多个数据列建立的约束,称为表级约束
3. 列级约束既可以在列定义时声明,也可以在列定义后声明
4. 表级约束只能在列定义后声明。





not null约束
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
非空约束用于确保当前列的值不为空值,非空约束只能出现在表对象的列上。
Null类型特征:
所有的类型的值都可以是null,包括int、float等数据类型
空字符串是不等于null,0也不等于null

语法:
字段 数据类型 not null


举例:
create table temp(
id int not null,
name varchar(2) not null default 'abc',
sex char null
);

ERROR 1067 (42000): Invalid default value for 'name'

报错,default值取错了,只能为2个字符

注意:default value值取决于字段类型的value,这里varchar定为2,所以default数只能输入2个字符,同时也限定了name字段的字符个数

在操作一次

create table t(
     id int not null,
     name varchar(2) not null default 'ab',
     sex char null
);

mysql> desc temp; 
+-------+--------------+------+-------+-----------+--------+
| Field | Type       | Null | Key | Default | Extra |
+-------+--------------+------+-------+-----------+--------+
| id     | int(11)     | NO |       | NULL    |         |
| name| varchar(3)| NO |       | abc       |        |
| sex   | char(1)    |YES |       | NULL    |         |
+-------+--------------+------+-------+-----------+--------+
3 rows in set (0.00 sec)

mysql> insert into t values(1234,'df,'f')

mysql> select * from t;
+--------+------+------+
| id   | name | sex |
+--------+------+------+
| 1234 | df   | s    |
+--------+------+------+
1 row in set (0.00 sec)

查看记录只录入了df
上面的table加上了非空约束,也可以用alter来修改或增加非空约束

增加非空约束
mysql> alter table temp modify sex varchar(2) not null;
mysql> desc temp;
+-------+--------------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id  | int(11)   | NO  |   | NULL  |    |
| name | varchar(255) | NO  |   | abc   |    |
| sex  | varchar(2)  | NO  |   | NULL  |    |
+-------+--------------+------+-----+---------+-------+

取消非空约束
mysql> alter table temp modify sex varchar(2) null;
mysql> desc temp;
+-------+--------------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id  | int(11)   | NO  |   | NULL  |    |
| name | varchar(255) | NO  |   | abc   |    |
| sex  | varchar(2)  | YES |   | NULL  |    |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

取消非空约束,增加默认值
mysql> alter table temp modify sex varchar(2) default ab null;


unique唯一约束
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
唯一约束是指定table的列或列组合不能重复,保证数据的唯一性。虽然唯一约束不允许出现重复的值,但是可以为多个null
同一个表可以有多个唯一约束,多个列组合的约束。在创建唯一约束的时候,如果不给唯一约束名称,就默认和列名相同。


添加唯一约束
------------------
语法:
constraint 约束名 unique(列1,列2,...)

举例:
create table t200(id int,name varchar(25),password varchar(16),constraint np_cu unique(name,password));

唯一约束
------------
语法:
1. 列(长度) 数据类型 unique
2. unique(列1,列2,...)

举例:
create table temp(id int,name varchar(25),password varchar(16) unique);
create table t1(id int,name varchar(25),password varchar(16),unique(password));


创建表后,添加唯一约束
---------------------------------
语法:
alter table 表名 add unique(列1,列2,...);
alter table 表名 modify 列 数据类型 unique;

举例:
alter table temp add unique(name, password);
alter table temp modify name varchar(25) unique;

删除约束
------------
语法:
alter table 语法 drop index 列;

举例:
alter table temp drop index name;



会给唯一约束的列上默认创建一个唯一索引;
create table temp (
    id int not null,
    name varchar(25),
    password varchar(16),
    --使用表级约束语法,
    constraint uk_name_pwd unique(name, password)
);

表示用户名和密码组合不能重复
constraint是约束把name与password设置成主键约束,并且约束名为uk_name_pwd


用户名与密码都只能是唯一,在添加记录会提示不能添加
create table baidu(id int not null,
          name varchar(5) unique,
          password varchar(16) unique);

mysql> desc sina;
+----------+-------------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id    | int(11)   | NO  |   | NULL  |    |
| name   | varchar(5) | YES | UNI | NULL  |    |
| password | varchar(16) | YES | UNI | NULL  |    |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into baidu values(1,'wang','wang');

mysql> select * from baidu;
+----+------+----------+
| id | name | password |
+----+------+----------+
| 1 | wang | wang   |
+----+------+----------+
1 row in set (0.00 sec)

加添加一次同样名就不让了

mysql> insert into baidu values(2,'wang','wang');
ERROR 1062 (23000): Duplicate entry 'wang' for key 'name'

mysql> insert into sina values(2,'aaa','ttt');

mysql> select * from sina;
+----+------+----------+
| id | name | password |
+----+------+----------+
| 1 | wang | wang   |
| 2 | aaa | ttt   |
+----+------+----------+
2 rows in set (0.00 sec)

unique(会添加到key里)表示记录不能重复,比如下面添加一个mike帐号,再添加一个相同的记录就会报错。就跟申请邮箱时一样,名字不能重复。

mysql> insert into t9 values('mike','mike@123.com');

mysql> insert into t9 values('mike','mike@123.com');
ERROR 1062 (23000): Duplicate entry 'mike@123.com' for key 'email'

mysql> alter table t9 drop index email;                删除索引unique(删除key里的unique)



primary key主键约束
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值;如果的多列组合的主键约束,
那么这些列都不允许为空值,并且组合的值不允许重复。
每个表最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别上创建。mysql的主键名总是PRIMARY,
当创建主键约束时,系统默认会在所在的列和列组合上建立对应的唯一索引。

语法:
1. 列 数据类型 primary key
2. primary key (列1,列2,...);

列模式:
mysql> create table jing(id int primary key,name varchar(25));

mysql> desc jing;
+-------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id  | int(11)   | NO  | PRI | NULL  |    |
| name | varchar(25) | YES |   | NULL  |    |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into jing values(1,'wang');

在添加一条id记录,就提示了设置了PRIMARY,不能重复

mysql> insert into jing values(1,'wang');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

这一次添加id记录为2,就可以了
mysql> insert into jing values(2,'wang');

mysql> select * from jing;
+----+------+
| id | name |
+----+------+
| 1 | wang |
| 2 | wang |
+----+------+
2 rows in set (0.00 sec)


create table temp(
  /*主键约束*/
  id int primary key,
  name varchar(25)
);

create table temp2(
  id int not null,
  name varchar(25),
  pwd varchar(15),
  constraint pk_temp_id primary key(id)
);
constraint是约束把id设置成主键约束,并且约束名为pk_temp_id

组合模式:
create table temp2(
  id int not null,
  name varchar(25),
  pwd varchar(15),
  constraint pk_temp_id primary key(name, pwd)
);

alter删除主键约束
语法:
alter table 表 drop primary key;

举例:
alter table temp drop primary key;

alter添加主键
语法:
alter table 表 add primary key(列1,列2,...);

举例:
alter table temp add primary key(name, pwd);

alter修改列为主键
语法:
alter table 表 modify 列 数据类型 primary key;

举例:
alter table temp modify id int primary key;






auto_increment设置主键自增
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
语法:
列 数据类型 auto_increment

create table bei(
    id int auto_increment primary key,
    name varchar(20),
    pwd varchar(16)
);
auto_increment自增模式,设置自增后在插入数据的时候就不需要给该列插入值了。

mysql> desc bei;
+-------+-------------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+----------------+
| id  | int(11)   | NO  | PRI | NULL  | auto_increment |
| name | varchar(20) | YES |   | NULL  |        |
| pwd  | varchar(16) | YES |   | NULL  |        |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

添加id记录为空值
mysql> insert into bei values('','wang','ping');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from bei;
+----+------+------+
| id | name | pwd |
+----+------+------+
| 1 | wang | ping |
+----+------+------+
1 row in set (0.00 sec)
在添加id记录为空值,这次就为2了,就是因为设置了auto_increment自增模式
mysql> insert into bei values('','wang','ping');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from bei;
+----+------+------+
| id | name | pwd |
+----+------+------+
| 1 | wang | ping |
| 2 | wang | ping |
+----+------+------+
2 rows in set (0.00 sec)








foreign key 外键约束
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1. 外键简单讲就是拆表,把大表拆成小表,在通过相同列值来保持两张的唯一性

你肯定会问为什么会拆表?

答:就是一张表不能过多字段,分两张表来存储,那么两张还都需要相同的列值来约束每条记录的唯一性

你又会问为什么不能有过多字段?

答:一张表字段过多会造成查询速度缓慢。

2. 外键其实就是一张表参照另一张表的相同的列,通过主表的列值来保持从表的记录唯一性,所以(从表关联列值)与(主表关联列值)必须相同,如果(父表)没有相关的记录,(子表)不能插入



作用:
------------------------------------------------------------------------------------------------------------
1. 用于约束(两张表)或(多张表)记录的对应关系
2. 增加(子表)记录时,是否与(父表)记录相对应
3. 如果(父表)没有相关的记录,(子表)不能插入
------------------------------------------------------------------------------------------------------------

注意:外键约束的参照列,在主表中引用的只能是主键或唯一键约束的列,假定引用的主表列不是唯一的记录,那么从表引用的数据就不确定记录的位置。同一个表可以有多个外键约束。



MySQL有两种常用的引擎类型:MyISAM和InnoDB。目前只有InnoDB引擎类型支撑外键约束。

InnoDB中外键约束定义的语法如下:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION


外键的使用需要满足下列的条件:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1. 两张表必须都是InnoDB表,并且它们没有临时表。
2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。
3. 建立外键关系的对应列必须建立了索引。
4. 假如显式的给出了CONSTRAINT symbol,那symbol在数据库中必须是唯一的。假如没有显式的给出,InnoDB会自动的创建。

如果子表试图创建一个在父表中不存在的外键值,InnoDB会拒绝任何INSERT或UPDATE操作。
如果父表试图UPDATE或者DELETE任何子表中存在或匹配外键值,最终动作取决于外键约束定
义中的ON UPDATE和ON DELETE选项。InnoDB支撑5种不同的动作,如果没有指定ON DELETE或
者ON UPDATE,默认的动作为RESTRICT:


限制
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1. CASCADE: 从父表中删除或更新对应的行,同时自动的删除或更新自表中匹配的行。
    ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支撑。
2. SET NULL: 从父表中删除或更新对应的行,同时将子表中的外键列设为空。
    注意,这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支撑。
3. NO ACTION: InnoDB拒绝删除或者更新父表。
4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT(或者NO ACTION)和忽略ON DELETE或者ON UPDATE选项的效果是一样的。
5. SET DEFAULT: InnoDB目前不支撑。


外键约束使用最多的两种情况无外乎:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1)父表更新时子表也更新,父表删除时如果子表有匹配的项,删除失败;
2)父表更新时子表也更新,父表删除时子表匹配的项也删除。

前一种情况,在外键定义中,大家使用ON UPDATE CASCADE ON DELETE RESTRICT;
后一种情况,可以使用ON UPDATE CASCADE ON DELETE CASCADE。


InnoDB允许你使用ALTER TABLE在一个已经存在的表上增加一个新的外键:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

InnoDB也支撑使用 ALTER TABLE 来删除外键:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;





表级别创建外键约束:
语法:
foreign key(外键名) references 主表(主表上的列)

举例:

1.创建主表
---------------
create table tb1(
    id int auto_increment primary key,
    name varchar(20)
);


2.创建从表
---------------
create table student(
    id int auto_increment primary key,
    name varchar(25),
    zd int,
    foreign key(zd) references tb1(id)
);


3.创建从表            (这里只是举个例子,不加 foreign key 也能创建)
---------------
create table tb2(
    id int auto_increment,
    name varchar(22),
    constraint pk_id primary key(id),
    zd int references tb1(id)
);

references 意思是参照,通常先建主表,然后再建从表,这样从表的参照引用的表才存在

上面的创建外键的方法没有指定约束名称,系统会默认给外键约束分配外键约束名称,命名为student_ibfk_n,
其中student是表名,n是当前约束从1开始的整数。


指定约束名称:
create table student(
    id int auto_increment primary key,
    name varchar(25),
    classes_id int,
    /*指定约束名称*/
    constraint fk_classes_id foreign key(classes_id) references classes(id)
);


多列外键组合,必须用表级别约束:

语法:
foreign key(外键名1,外键名2,...) references 主表(主表列1,主表列2,...)


举例:

1.创建主表
---------------
create table classes(
    id int,
    name varchar(20),
    number int,
    primary key(name, number)
);

2.以主表,添加外键约束
---------------------------------
create table student(
    id int auto_increment primary key,
    name varchar(20),
    classes_name varchar(20),
    classes_number int,
    /*表级别联合外键*/
    foreign key(classes_name, classes_number) references classes(name, number)
);


删除外键约束:
语法:
alter table 表名 drop foreign key 外键名;

举例:
alter table student drop foreign key student_ibfk_1;
alter table student drop foreign key fk_student_id;

增加外键约束
语法:
alter table 表 add foreign key(外键名1,外键名2,...) references 主表(主表列1,主表列2,...);

举例:
alter table student add foreign key(classes_name, classes_number) references classes(name, number);

自引用、自关联(递归表、树状表)
create table tree(
    id int auto_increment primary key,
    name varchar(50),
    parent_id int,
    foreign key(parent_id) references tree(id)
);

级联删除:删除主表的数据时,关联的从表数据也删除,则需要在建立外键约束的后面增加on delete cascade
或on delete set null,前者是级联删除,后者是将从表的关联列的值设置为null。
create table student(
    id int auto_increment primary key,
    name varchar(20),
    classes_name varchar(20),
    classes_number int,
    /*表级别联合外键*/
    foreign key(classes_name, classes_number) references classes(name, number) on delete cascade
);





check约束
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
可以使用check约束,但check约束对数据验证没有任何作用。

create table temp(
    id int auto_increment,
    name varchar(20),
    age int,
    primary key(id),
/*check约束*/
check(age 20)
);
上面check约束要求age必须大于20,但没有任何作用。但是创建table的时候没有任何错误或警告。


default修饰符
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
default_value:为列指定默认值。如果没有为列指定默认值,mysql> 自动地分配一个。如果列可以取NULL作为值,缺省值是NULL。
如果列被声明为NOT NULL,缺省值取决于列类型的value值

语法:
列 数据类型 default 默认值;


举例:
mysql> create table t2 (id int,dep char(10) default 'mis');

mysql> desc t2;
+-------+-------------+------+------+-----------+--------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-------------+------+------+-----------+--------+
| id     | int(11)   | YES |      | NULL    |         | 
| dep  | char(10) | YES |      | mis       |         | 
+-------+-------------+-------+-----+-----------+---------+

mysql> insert into t2 values(1,'hr');

mysql> insert into t2(id) values(2);

mysql> alter table t2 modify id int not null;

mysql> insert into t2 values();

mysql> select * from t2;
+----+------+
| id | dep |
+----+------+
| 1 | hr  | 
| 2 | mis | 
| 0 | mis | 
+----+------+
3 rows in set (0.00 sec)

上一篇:spark和hive整合常识点分享
下一篇:Hadoop大数据开发学习路线
相关文章
图文推荐

关于大家 | 联系大家 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 澳门葡京手机版网址_澳门新莆京娱乐_www.88807.com - 点此进入--致力于做实用的IT技术学习网站

XML 地图 | Sitemap 地图