20ss►DBS

DBS-Project3-SQL数据完整性

实验3-SQL数据完整性

###实验目的

  • 掌握MySQL数据库的完整性约束的定义方法；
  • 掌握MySQL数据库的用户的创建方法。
  • 掌握MySQL数据库的权限设置方法。

实验平台

数据库管理系统（MySQL）

实验内容和要求

1、建立数据库school
1
CREATE DATABASE school;
2、数据的定义

利用create创建三张表,同时增加主键外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE S (
	SID VARCHAR ( 20 ) ,
	SNAME VARCHAR ( 20 ),
	AGE INT,
	SEX VARCHAR ( 1 ) ,
	PRIMARY KEY (SID)
);
CREATE TABLE C(
	CID VARCHAR ( 20 ) ,
	CNAME VARCHAR(20),
	TEACHER VARCHAR(20),
	PRIMARY KEY (CID)
);
CREATE TABLE SC(
	SID VARCHAR(20),
	CID VARCHAR(20),
	GRADE INT,
	PRIMARY KEY (SID,CID),
	FOREIGN KEY (SID) REFERENCES S(SID) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (CID) REFERENCES C(CID) ON UPDATE CASCADE ON DELETE CASCADE
);
3、完整性约束设置及其测试

  • 完整性约束设置

    1
    2
    ALTER TABLE S ADD CHECK (sex in ('f','m'));
    ALTER TABLE S ADD CHECK (0 <= age and age <=200) ;
    • 测试
    1
    2
    3
    4
    5
    6
    7
    8
    INSERT INTO S (SID, SNAME, AGE, SEX)
                 VALUES ('S1','Wang feng', 20, 'f');
    INSERT INTO S (SID, SNAME, AGE, SEX)
                 VALUES ('S2','Li feng', 20, 't');
    INSERT INTO S (SID, SNAME, AGE, SEX)
                 VALUES ('S2','Li feng', -1, 'm');
    INSERT INTO S (SID, SNAME, AGE, SEX)
                 VALUES ('S2','Li feng', 14, 'm');
    • 运行结果

image-20200402212557918

image-20200402212646716

image-20200402212702535

  • 触发器定义及测试

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    CREATE TRIGGER student_insert_check BEFORE INSERT
    ON s FOR EACH ROW
    BEGIN
      DECLARE msg varchar(100);
    	IF NEW.age <= 0 OR NEW.age >= 200 
    	THEN
    		SET msg = CONCAT('您输入的年龄值：',NEW.age,' 为无效的年龄，请输入0到200以内的有效数字。');
    		SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
    	END IF;
    END;

    INSERT INTO S (SID, SNAME, AGE, SEX)
    			 VALUES ('S3','Cy', 201, 'f');
    • 结果

image-20200402212531207

  • 外键约束测试

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    INSERT INTO S (SID, SNAME, AGE, SEX)
    			 VALUES ('S1','Wang feng', 20, 'f');
    INSERT INTO S (SID, SNAME, AGE, SEX)
    			 VALUES ('S2','Li feng', 20, 't');
    INSERT INTO S (SID, SNAME, AGE, SEX)
    			 VALUES ('S2','Li feng', -1, 'm');
    INSERT INTO S (SID, SNAME, AGE, SEX)
    			 VALUES ('S2','Li feng', 14, 'm');
    INSERT INTO C (CID, CNAME, TEACHER)
           VALUES ('C1', 'DB', 'Li');
    INSERT INTO C (CID, CNAME, TEACHER)
           VALUES ('C2', 'maths', 'Ma');
    INSERT INTO SC (SID, CID , GRADE)
           VALUES ('S1','C1',70);
    INSERT INTO SC (SID, CID , GRADE)
           VALUES ('S2','C1',81);
    INSERT INTO SC (SID, CID , GRADE)
           VALUES ('S1','C2',65);
    SELECT * FROM SC;
    DELETE FROM S WHERE SID = 'S2' ;
    SELECT * FROM SC;

    • 结果

      image-20200402213304283

      image-20200402213312189

3、MySQL上的用户和权限的设置
1
2
3
4
5
6
7
CREATE USER 'test1'@'localhost' IDENTIFIED by '123';
CREATE USER 'test2'@'localhost' IDENTIFIED by '123';
GRANT INSERT,UPDATE ON school.C TO 'test1'@'localhost' WITH GRANT OPTION;

revoke update on school.C from 'test1'@'localhost';
show grants for 'test2'@'localhost';
Drop user 'test2'@'localhost';

image-20200402214727360

image-20200402215318454