MySQL 学习笔记(三):完整性和触发器设计

(一)完整性设计

方法一、在设计表时定义约束 删除数据库school,建立新数据库school1

drop database school;
create database school;
use school;

1.定义约束 

create table Student(
  Sno char(9) primary key,    /*主键约束 提示primary key */ 
  Sname char(20) unique,    /* 唯一约束  提示 unique */ 
  Ssex char(2) check (Ssex='男' or Ssex='女'),  /* 检查约束 提示 性别只能是男或女 check */
  Sage int check (sage <= 100 and sage>=1), /* 检查约束 提示年龄介于1 到 100之间 check*/
  Sdept char(20)  default 'CS'  /* 缺省 缺省值为CS, default() */
 );

create table Course(
  Cno char(4) primary key, /*主键约束 */
  Cname char(40) unique, /* 唯一约束 */
  Cpno char(4) not null, /*非空约束 提示not null */
  Ccredit int default 1 /* 缺省值为1 */
 );

create table SC(
  Sno char(9),
  Cno char(4),
  Grade int check (grade>=0 and grade<=100), /* 约束取值为0 到100 * check /
  primary key(Sno, Cno),
  foreign key (Sno) references Student(sno) on delete cascade on update cascade,
  foreign key (Cno) references Course(Cno) on delete cascade
    /*定义(Sno,Cno)为主键 */
   /*定义sno为外键参考student表的主键sno,并且实现级联删除更新SC表中相应的元组 */
   /* 提示 foreign key .. references ... on delete cascade on update cascade,
   /*定义Cno为外键参考course表的主键Cno,并且实现级联删除SC表中相应的元组 */
 );

 2. 检验约束

插入下列记录

insert into  student values ('95001','李勇','男',20,'CS');
insert into student values ('95002', '刘晨','女',19,'IS');
insert into  student values('95003', '王敏', '女',18, 'MA');
insert into student values('95004', '张立', '男',19, 'IS');

insert into Course values(1,'数据库', 5,4);
insert into Course values(2, '数学', 3 ,2);
insert into Course values(3, '信息系统', 1, 4);
insert into Course values('4', '操作系统', '6', 3);
insert into Course values('5', ' 数据结构', '7', 4);
insert into Course values('6', '数据处理',  '',  2);
insert into Course values('7',  'PASCAL语言', '6', 4);

insert into SC values('95001', '1', 92);
insert into SC values('95001', '2', 85);
insert into SC values('95001', '3', 88);
insert into SC values('95002', '2', 90);
insert into SC values('95002', '3', 80);

请设计系列测试用例逐一检查每一个定义的约束是否生效

例如:检查student表的主键约束是否生效,测试用例

insert into  student values ('95001','测试','男',20,'CS');

报错:ERROR 1062 (23000): Duplicate entry '95001' for key 'PRIMARY' 

检查student表的唯一约束是否生效,测试用例

insert into  student values ('95005','李勇','男',20,'CS');

报错:ERROR 1062 (23000): Duplicate entry '李勇' for key 'Sname' 

检查student表的检查约束是否生效,测试用例

 insert into  student values ('95005','李刚','牛',20,'CS');

插入成功 Query OK, 1 row affected (0.00 sec)。其实这里理论上来说应该是插入失败的,因为 student 有检查约束,但不同于SQL,在MYSQL中,CHECK只是一段可调用但无意义的子句。MySQL会直接忽略。

方法二、在表定义完成后,添加修改约束

建立新数据库school2

create databaes school2;
use school2;

1. 定义表

  create table student(
  Sno char(9),  
  Sname char(20), 
  Ssex char(2) , 
  Sage int , 
  Sdept char(20)
 );

create table Course(
  Cno char(4), 
  Cname char(40),
  Cpno char(4) ,
  Ccredit int 
 );

create table SC(
  Sno char(9),
  Cno char(4),
  Grade int
 );

 2.添加修改约束

(1). 添加主键约束

alter table 表名 add constraint 约束名(形如:PK_表名) primary key (主键)

alter table Student add constraint PK_Student primary key(Sno);
alter table Course add constraint PK_Course primary key(Cno);
alter table Sc add constraint PK_SC primary key(Sno, Cno);

 (2). 添加外键约束

alter table 从表 add constraint 约束名(形如:FK_从表_主表) foreign key(属性名) references 主表(属性名)

alter table SC add constraint SC_Student foreign key(sno) references Student(sno);
alter table SC add constraint SC_Course foreign key(cno) references Course(cno);

(3). 外键约束 + 级联(删除/更新)

alter table 从表 add constraint 约束名 foreign key(属性名) references 主表(属性名)  ON DELETE CASCADE ON UPDATE CASCADE

alter table SC add constraint SC_Students foreign key(sno) references Student(sno) on delete cascade on update cascade;
alter table SC add constraint SC_Courses foreign key(cno) references Course(cno) on delete cascade;

发现报错Can't write; duplicate key in table '#sql-1f2_7',发现原来是这里的约束名和前面的重复了,需要重新命名一下。

(4). 添加唯一约束

alter table 表名 add constraint 约束名(形如:UQ_表名) unique (属性名)

alter table Student add constraint UQ_Student unique(sname);
alter table Course add constraint UQ_Course unique(cname);

(5). 添加默认约束

alter table表名alter column字段名drop default; (若本身存在默认值,则先删除) alter table表名 alter column字段名 set default默认值;(若本身不存在则可以直接设定)

alter table student alter column sdept set default 'CS';
alter table course alter column ccredit set default 1;

(6). 添加检查check约束

alter table 表名 add constraint 约束名(形如:CK_表名) check (属性名 约束条件 )

alter table Student add constraint CK_Student check(Ssex='男' or Ssex='女');
alter table Student add constraint CK_Student check(sage <= 100 and sage>=1);
Alter table Course add constraint CK_Course check(grade>=0 and grade<=100);

2.检查约束是否生效 参考方法一设计测试用例,逐一检查! 结果:测试后发现跟方法一出现的结果是一样的。

3. 删除约束 删除主键约束:alter table 表名 drop primary key; 删除外键约束:alter table 表名 drop foreign key 外键(区分大小写); 设计测试用例,检查删除约束后是否生效? 添加多个约束,情况如何? 这里我删除约束的时候报错了:ERROR 1025 (HY000): Error on rename of './school/#sql-1f2_7' to './school/student' (errno: 150 - Foreign key constraint is incorrectly formed) 查了下资料,发现 MySQL建立外键的字段必须和引用表的字段一模一样的类型。 这里我创建外键的时候 sno 中有些表有主键约束,有些却没有,所以当我删除主键约束的时候它会提示外键被错误建立起来了。但我不明白为什么建立外键的时候没有报错。

(二)触发器

MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象,当触发器所在表上出现指定事件时,将调用该对象,即表的操作事件触发表上的触发器的执行。 创建触发器 在MySQL中,创建触发器语法如下:

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中: trigger_name:标识触发器名称,用户自行指定; trigger_time:标识触发时机,取值为 BEFORE 或 AFTER; trigger_event:标识触发事件,取值为 INSERT、UPDATE 或 DELETE; tbl_name:标识建立触发器的表名,即在哪张表上建立触发器; trigger_stmt:触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句。

删除触发器

drop trigger 触发器名

写个触发器:

delimiter $$
create trigger hiChange after insert on student for each row
begin
select 'hi,成功插入数据' into @ee;    /*使用 select 语句来打印输出*/
end
$$
delimiter ;

刚开始创建的时候报错:Not allowed to return a result set from a trigger。原因:在mysql的trigger和function中不能出现select * from table形式的查询,因为其会返回一个结果集;而这在mysql的trigger和function中是不可接受的,但是在存储过程中可以。在 select 语句后加上 into @ee 就可以了。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Ken的杂谈

CentOS 7下InfluxDB部署与使用入门

InfluxDB里存储的数据被称为时间序列数据,InfluxDB存储方式跟传统关系型数据库不同的是:传统关系型数据库通过数据库+表+字段组织数据,InfluxD...

7844
来自专栏禅林阆苑

mysql 命令完全总结 【原创】

mysql 命令完全总结 Write By CS逍遥剑仙 我的主页: www.csxiaoyao.com GitHub: github.com...

34911
来自专栏杨建荣的学习笔记

闪回归档的简单测试(r8笔记第68天)

11g里面的新特性闪回归档,为历史数据的定制查询提供了一种可能。抽时间学习了一下这个特性,发现还是比较清晰易用。如果使用得当还是很不错的一个特性。 我们来简单模...

3285
来自专栏Albert陈凯

SQL语句各个部分执行顺序

http://www.tuicool.com/articles/fERNv2 下面先给出上面的SQL语句的执行顺序,然后进行讲解: (8)  select (...

3257
来自专栏腾讯云Elasticsearch Service

mysql数据实时同步到Elasticsearch

业务需要把mysql的数据实时同步到ES,实现低延迟的检索到ES中的数据或者进行其它数据分析处理。本文给出以同步mysql binlog的方式实时同步数据到ES...

4.5K7
来自专栏奇梦博客

CentOS下mysql数据库常用命令总结 MySQL 参数配置

1441
来自专栏java达人

MySQL 加锁处理分析

背景 MySQL/InnoDB的加锁分析,一直是一个比较困难的话题。我在工作过程中,经常会有同事咨询这方面的问题。同时,微博上也经常会收到MySQL锁相关的私信...

2895
来自专栏python3

mysql-数据库的操作

nformation_schema: 虚拟库,不占用磁盘空间,存储的是数据库启动后的一些参数,如用户表信息、列信息、权限信息、字符信息等

1092
来自专栏csxiaoyao

mysql 命令完全总结

4487
来自专栏java一日一条

MySQL处理数据库和表的常用命令

学习如何管理和导航MySQL数据库和表是要掌握的首要任务之一,下面的内容将主要对MySQL的数据库和表的一些常用命令进行总结,一些我们不得不掌握的命令,一些信手...

1073

扫码关注云+社区

领取腾讯云代金券