MySQL(二)

约束

约束(CONSTRAINT)是表级的强制规定.

0.字段属性

• Unsigned

  • 无符号的整数

  • 该列数据不能为负数

• zerofill

  • 不足的位数,用0来填充
  • 自带unsigned

1.非空约束

1.1作用

• -- 关键字
  NOT NULL
  -- 作用
  限制该字段的数据不能为null
  

1. 默认所有的类型的值都可以是NULL,包括INT,FLOAT等数据类型
2. 非空约束只能出现在表对象的列上,只能某个列单独限定非空,不能组合非空
3. 一个表可以有很多列分别都限定了非空
4. 空字符串不等于NULL,0也不等于NULL

1.2添加

• -- 创建表时添加
  create table test(
  	id INT NOT NULL,
      age TinyInt
  );
  -- alter table时添加
  alter table test modify age TinyInt not null;
  -- 当要添加的属性数据有null值时,无法添加非空约束
  

1.3删除

• -- 在alter table时删除
  alter table test modify age TinyInt null;
  

2.唯一性约束

2.1作用

• -- 关键字
  UNIQUE
  -- 作用
  保证该字段所有数据都是唯一不重复的
  

1. 同一个表可以有多个唯一约束
2. 唯一约束可以是某一个列的值唯一,也可以多个列组合的值唯一
3. 唯一约束允许列值为空
4. 在创建唯一约束时,如果不给唯一约束命名,就默认和列名相同
5. MySQL会给唯一约束的列上默认创建一个唯一索引

2.2添加

• -- 建表时添加
  create table test(
  	id INT UNIQUE,
      name varchar(25),
      age TinyInt,
  	[CONSTRAINT 约束名] UNIQUE(字段名)   
      -- 这里添加的约束称为表级约束
  );
  -- alter table时添加
  -- 方式1
  alter table test modify age TinyInt UNIQUE;
  -- 方式2
  alter table test add [CONSTRAINT 约束名] unique(age);
  

2.3复合型唯一约束

• -- 创建
  create table test(
      name varchar(25),
      password varchar(25),
      UNIQUE(name,password)
  );
  -- 例
  插入数据
  insert into test value('251','123456')
  ,('250','123456'),('251','123451'); --成功
  insert into test value('251','123456'); --失败
  -- 两个数据都完全一样时才会添加失败
  

2.4删除

1. 添加唯一约束时会自动创建一个唯一索引
2. 删除唯一约束只能通过删除唯一索引的方式删除
3. 删除时需要指定唯一索引名,唯一索引名就和唯一约束名一样
4. 如果创建唯一约束时未指定名称
   1. 如果是单列,则默认是列名
   2. 如果是组合列,那么默认和()中排在第一个的列名相同
   3. 也可以自定义唯一约束名

-- 查看表的索引
show index from 表名;
-- 删除唯一约束
alter table 表名 drop index 索引名;
-- 例
alter table test drop index age;

3.主键约束

3.1作用

• -- 关键字
  PRIMARY KEY
  -- 主键是一行数据的唯一标识,要求非空且唯一
  -- 相当于唯一约束+非空约束的组合
  

1. 一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别创建
2. 主键约束对应着表中的一列或者多列(复合主键)
3. 如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复
4. MySQL的主键名总是PRIMARY,就算是自己命名了主键约束名也没用
5. 当创建主键约束时,系统默认会在所在列或列组合上建立对应的主键索引(能根据主键查询就根据主键查询,效率更高).如果删除主键约束,那么主键约束对应的索引就自动删除
6. 需要注意的一点是,不要修改主键字段的值,因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性

3.2添加

• -- 建表时添加
  create table test(
  	id int primary key,
      name varchar(25)
  );
  -- alter table时添加
  alter table test add primary key(id);
  

3.3删除

• alter table 表名 drop primary key;
  

4.自增列

4.1作用

• -- 关键字
  AUTO_INCREMENT
  -- 作用
  自动在上一条记录的基础上+1(默认)
  

1. 一个表最多只能有一个自增长列
2. 当需要产生唯一标识符或者顺序值时,可设置自增长
3. 自增长列约束的列必须是键列(主键列,唯一键列)
4. 自增约束的列的数据类型必须是整数类型
5. 如果自增列指定了0和null,会在当前最大值的基础上自增,如果自增列手动指定了具体值,直接赋值为具体值

4.2添加

• -- 建表时添加
  create table test(
  	id int primary key auto_increment,
      name varchar(25)
  );
  -- alter table时添加
  alter table 表名 modify 字段名 字段类型 auto_increment;
  

4.3删除

• alter table 表名 modify 字段名 字段类型;
  

5.检查约束

5.1作用

• -- 关键字
  CHECK
  -- 作用
  保证字段值满足某一个条件
  

5.2添加

• -- 随便举个例,用的不多
  create table test(
      id int,
      name varchar(25),
      age int check(age>18)
  );
  -- 年龄必须大于18岁
  

6.默认约束

6.1作用

• -- 关键字
  DEFAULT
  -- 作用
  保存数据时如果该字段未有值,则用默认值
  

6.2添加

• -- 建表时添加
  create table test(
      id int,
      name varchar(25) default '张三'
  );
  -- alter table时添加
  alter table test modify name varchar(25) default '张三';
  -- 此时姓名不填的都默认为张三
  

6.3删除

• -- 添加
  alter table test modify name varchar(25) default '张三';
  -- 删除
  alter table test modify name varchar(25) ;
  -- 把默认约束删掉就是删除了
  

视图

1. 视图是一种虚拟表,本身是不具有数据的,占用很少的内存空间,它是SQL中的一个重要概念
2. 视图建立在已有表的基础上,视图赖以建立的这些表称为基表.
3. 视图的创建和删除只影响本身.不影响对应的基表.但是对视图的数据进行增删改操作时,数据表中的数据会相应地发生变化,反之也是.
4. 向视图提供数据内容的语句为select语句,可以将视图理解为存储起来的select语句

1.创建

• 在create view语句中嵌入子查询

• create [OR REPLACE]
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  VIEW 视图名称 [(字段列表)]
  AS 查询语句
  [WITH [CASCADED|LOCAL] CHECK OPTION]
  

• -- 简化写法
  create view 视图名称 AS 查询语句
  

1.1单表创建

• -- 例
  create view test(例:newAge) -- 这里可以给字段起别名
  AS
  select age from student where age>18;
  

1.2多表创建

• -- 例
  create view test
  AS
  -- 查询语句填选多表查询的语句即可 
  

1.3基于视图创建视图

• -- 例
  create view 新视图名
  as
  select 字段名 from 旧视图名;
  

2.查看

• -- 查看视图
  show tables;
  -- 查看视图结构
  desc 视图名;
  -- 查看视图属性信息
  show table status like '视图名';
  -- 查看视图的详细定义信息
  show create view 视图名;
  

3.更新

• update 视图名 set 要修改的字段名=修改后的值 where 条件;
  

4.修改

• -- 方式1
  create or replace view 视图名 -- 在创建表的基础加上or replac就是修改表
  as
  选择语句;
  -- 方式2
  alter view 视图名
  as
  选择语句;
  

5.删除

• drop view [IF EXISTS] 视图名;
  

6.总结

6.1优点

1. 操作简单
2. 减少数据冗余
3. 数据安全
4. 适应灵活多变的需求
5. 能够分解复杂的查询逻辑

6.2缺点

• 维护性差
• 维护成本高