【第22期】观点:IT 行业加班,到底有没有价值?

MySQL语法项目练习(2)

原创 2015年11月17日 16:54:28

项目名称: MySQL语法练习项目(2)

项目简介: MySQL语法练习, 包括Constraint, Trigger, Grant.

项目来源: 南京大学 · 2015年秋季 · 数据库概论 · 课程实验二


Environment and Installation


Software Version
OS Windows8.1
DBMS MySQLCommunityServer5.6.27(Windows x86,64-bit)

Problems and Solutions


创建表

创建一个新的数据库,在该数据库下按要求创建如下表格,将所有SQL语句和要求的实验截图放在实验报告中。

   职工(姓名,工号,出生日期,家庭地址,年薪,所在部门编号)

   部门(部门名称,部门编号,部门负责人的工号)

   项目(项目名称,项目编号,主管部门编号)

   工作(职工工号,项目编号,工作时间)

  • Create Table Employee
DROP TABLE IF EXISTS E;
CREATE TABLE E(  -- Employee
    eid INT,     -- Employee Identifier
    en CHAR(32), -- Employee Name
    ebd DATE,    -- Employee Birthdate
    ea CHAR(32), -- Employee Address
    es INT,      -- Employee Salary
    did INT      -- Department Identifier
);
INSERT INTO E VALUES
    (0001, 'Jack', '1970-01-01', 'NewYork', 90000, 1001),
    (0002, 'Rose', '1975-10-10', 'Beijing', 50000, 1001),
    (0003, 'York', '1980-10-10', 'Nanjing', 30000, 1001),
    (0004, 'WuZi', '1990-12-31', 'Washton', 70000, 1002);
SELECT * FROM E;
eid en ebd ea es did
1 Jack 1970-01-01 NewYork 90000 1001
2 Rose 1975-10-10 Beijing 50000 1001
3 York 1980-10-10 Nanjing 30000 1001
4 WuZi 1990-12-31 Washton 70000 1002

  • Create Table Department
DROP TABLE IF EXISTS D;
CREATE TABLE D(  -- Department
    did INT,     -- Department Identifier
    dn CHAR(32), -- Department Name
    eid INT      -- Employee Identifier in charge
);
INSERT INTO D VALUES
    (1001, 'Produce', 0001),
    (1002, 'Sales',   0004);
SELECT * FROM D;
did dn eid
1001 Produce 1
1002 Sales 4

  • Create Table Project
DROP TABLE IF EXISTS P;
CREATE TABLE P(   -- Project
    pid INT,      -- Project Identifier
    pn CHAR(32),  -- Project Name
    did INT       -- Department Identifier in charge
);
INSERT INTO P VALUES
    (2001, 'Chocolate', 1001),
    (2002, 'Adverting', 1002);
SELECT * FROM P;
pid pn did
2001 Chocolate 1001
2002 Adverting 1002

  • Create Table Work
DROP TABLE IF EXISTS W;
CREATE TABLE W(  -- Work
    eid INT,     -- Employee Identifier
    pid INT,     -- Project Identifier
    wt INT       -- Work Time
);
INSERT INTO W VALUES
    (0001, 2001, 14),
    (0002, 2001, 15),
    (0003, 2002, 25),
    (0004, 2002, 99);
SELECT * FROM W;
eid pid wt
1 2001 14
2 2001 15
3 2002 25
4 2002 99

添加属性约束

分析该关系模式中存在的各种数据约束,为这些表添加约束,包括主键、非空、外键等。在父表上update记录时,同步update子表上外键的值;当父表上记录被当做外键在子表中使用时,阻止对该记录的delete操作。

-- Add attribute constraints in table E
ALTER TABLE E ADD CONSTRAINT pk_E PRIMARY KEY(eid);
ALTER TABLE E MODIFY en CHAR(32) NOT NULL;
# ALTER TABLE E ADD CONSTRAINT chk_E CHECK(en IS NOT NULL);  -- MySQL does not support.

-- Add attribute constraints in table D
ALTER TABLE D ADD CONSTRAINT pk_E PRIMARY KEY(did);
ALTER TABLE D MODIFY dn CHAR(32) NOT NULL;

-- Add attribute constraints in table 
ALTER TABLE P ADD CONSTRAINT pk_P PRIMARY KEY(pid);
ALTER TABLE P MODIFY pn CHAR(32) NOT NULL;

-- Add attribute constraints in table
ALTER TABLE W MODIFY eid INT NOT NULL;
ALTER TABLE W MODIFY pid INT NOT NULL;

ALTER TABLE E ADD CONSTRAINT fk_E_did FOREIGN KEY(did) REFERENCES D(did) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE D ADD CONSTRAINT fk_D_eid FOREIGN KEY(eid) REFERENCES E(eid) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE P ADD CONSTRAINT fk_P_did FOREIGN KEY(did) REFERENCES D(did) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE W ADD CONSTRAINT fk_W_eid FOREIGN KEY(eid) REFERENCES E(eid) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE W ADD CONSTRAINT fk_W_pid FOREIGN KEY(pid) REFERENCES P(pid) ON UPDATE CASCADE ON DELETE RESTRICT;

执行违反数据约束的操作

-- primary key must be unique
INSERT INTO E VALUE (0001, 'WuZi', '1990-12-31', 'Washton', 70000, 1002);
-- primary key must be not-null
INSERT INTO E VALUE (NULL, 'WuZi', '1990-12-31', 'Washton', 70000, 1002);
-- a not-null attribute must be not-null
INSERT INTO E VALUE (0010, NULL, '1990-12-31', 'Washton', 70000, 1002);
INSERT INTO D VALUE (1010, NULL, 0001);
-- foreign key must exist
INSERT INTO D VALUE (1003, 'Analysis', 0009);
-- foreign key can be null
INSERT INTO D VALUE (1003, 'Analysis', NULL);

1


创建触发器

(a) 当插入一项工作时, 如果工作时间大于24, 则将其设置为24.

SELECT * FROM W;
DROP TRIGGER IF EXISTS setWorkTime;
DELIMITER $$
CREATE TRIGGER setWorkTime
	BEFORE INSERT ON W
    FOR EACH ROW
    BEGIN
		SET NEW.wt = IF(NEW.wt > 24, 24, NEW.wt);
	END; $$
DELIMITER ;
INSERT INTO W VALUE (0001, 2002, 25);
SELECT * FROM W;

‘Create Trigger’语句执行前:

eid pid wt
1 2001 14
2 2001 15
3 2002 25
4 2002 99

‘Create Trigger’语句执行后:

eid pid wt
1 2001 14
2 2001 15
3 2002 25
4 2002 99
1 2002 24
(b) 当职工参加一个新的项目时, 年薪增加2%. 可能为在表W中插入新元祖:

SELECT * FROM E;
DROP TRIGGER IF EXISTS setSalaryBI;
DELIMITER $$
CREATE TRIGGER setSalaryBI
	BEFORE INSERT ON W
    FOR EACH ROW
    BEGIN
		UPDATE E
			SET es = es * 1.02
            WHERE eid = NEW.eid;
    END; $$
DELIMITER ;
INSERT INTO W VALUE (0001, 2002, 25);
SELECT * FROM E;

‘Create Trigger’语句执行前:

eid en ebd ea es did
1 Jack 1970-01-01 NewYork 90000 1001
2 Rose 1975-10-10 Beijing 50000 1001
3 York 1980-10-10 Nanjing 30000 1001
4 WuZi 1990-12-31 Washton 70000 1002

‘Create Trigger’语句执行后:

eid en ebd ea es did
1 Jack 1970-01-01 NewYork 91800 1001
2 Rose 1975-10-10 Beijing 50000 1001
3 York 1980-10-10 Nanjing 30000 1001
4 WuZi 1990-12-31 Washton 70000 1002
可能为在表W中修改旧元祖:

SELECT * FROM E;
DROP TRIGGER IF EXISTS setSalaryBU;
DELIMITER $$
CREATE TRIGGER setSalaryBU
	BEFORE UPDATE ON W
    FOR EACH ROW
    BEGIN
		UPDATE E
			SET es = es * 1.02
            WHERE eid = NEW.eid;
    END; $$
DELIMITER ;
UPDATE W SET pid = '2002' WHERE eid = '0002';
SELECT * FROM E;

‘Create Trigger’语句执行前:

eid en ebd ea es did
1 Jack 1970-01-01 NewYork 91800 1001
2 Rose 1975-10-10 Beijing 50000 1001
3 York 1980-10-10 Nanjing 30000 1001
4 WuZi 1990-12-31 Washton 70000 1002

‘Create Trigger’语句执行后:

eid en ebd ea es did
1 Jack 1970-01-01 NewYork 91800 1001
2 Rose 1975-10-10 Beijing 51000 1001
3 York 1980-10-10 Nanjing 30000 1001
4 WuZi 1990-12-31 Washton 70000 1002
***

### 创建新用户并授权 1. 创建一个用户worker 2. 将“职工”表的查询权限以及“职工”表“家庭地址”列的修改权限赋予该用户 3. 使用worker用户登录,并尝试修改“职工”表的“家庭住址”和“出生日期” 4. 完成后删除用户worker。 Code for administrator ‘root’:
CREATE USER 'worker'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON E TO 'worker'@'localhost';
GRANT UPDATE(ea) ON E TO 'worker'@'localhost';
DROP USER 'worker'@'localhost';

Logging in as the user ‘worker’:

2

Code for user ‘worker’@’localhost’:

SELECT * FROM E;
SET SQL_SAFE_UPDATES = 0;
UPDATE E
    SET ea = 'London'
    WHERE en = 0001;
SELECT * FROM E;
UPDATE E
    SET ebd = '1990-01-10'
    WHERE en = 0001;
SELECT * FROM E;

‘Update’语句执行前:

eid en ebd ea es did
1 Jack 1970-01-01 NewYork 91800 1001
2 Rose 1975-10-10 Beijing 51000 1001
3 York 1980-10-10 Nanjing 30000 1001
4 WuZi 1990-12-31 Washton 70000 1002

‘Create Trigger’语句执行后:

eid en ebd ea es did
1 Jack 1970-01-01 London 91800 1001
2 Rose 1975-10-10 Beijing 51000 1001
3 York 1980-10-10 Nanjing 30000 1001
4 WuZi 1990-12-31 Washton 70000 1002

![3](http://img.blog.csdn.net/20151117164154534) ***

## ALL Codes *** Code for administrator ‘root’:
DROP DATABASE IF EXISTS WDB;
CREATE DATABASE WDB;
USE WDB;

# Create Table
-- Create Table Employee
DROP TABLE IF EXISTS E;
CREATE TABLE E(  -- Employee
    eid INT,     -- Employee Identifier
    en CHAR(32), -- Employee Name
    ebd DATE,    -- Employee Birthdate
    ea CHAR(32), -- Employee Address
    es INT,      -- Employee Salary
    did INT      -- Department Identifier
);
INSERT INTO E VALUES
    (0001, 'Jack', '1970-01-01', 'NewYork', 90000, 1001),
    (0002, 'Rose', '1975-10-10', 'Beijing', 50000, 1001),
    (0003, 'York', '1980-10-10', 'Nanjing', 30000, 1001),
    (0004, 'WuZi', '1990-12-31', 'Washton', 70000, 1002);
SELECT * FROM E;

-- Create Table Department
DROP TABLE IF EXISTS D;
CREATE TABLE D(  -- Department
    did INT,     -- Department Identifier
    dn CHAR(32), -- Department Name
    eid INT      -- Employee Identifier in charge
);
INSERT INTO D VALUES
    (1001, 'Produce', 0001),
    (1002, 'Sales',   0004);
SELECT * FROM D;

-- Create Table Project
DROP TABLE IF EXISTS P;
CREATE TABLE P(   -- Project
    pid INT,      -- Project Identifier
    pn CHAR(32),  -- Project Name
    did INT       -- Department Identifier in charge
);
INSERT INTO P VALUES
    (2001, 'Chocolate', 1001),
    (2002, 'Adverting', 1002);
SELECT * FROM P;

-- Create Table Work
DROP TABLE IF EXISTS W;
CREATE TABLE W(  -- Work
    eid INT,     -- Employee Identifier
    pid INT,     -- Project Identifier
    wt INT       -- Work Time
);
INSERT INTO W VALUES
    (0001, 2001, 14),
    (0002, 2001, 15),
    (0003, 2002, 25),
    (0004, 2002, 99);
SELECT * FROM W;

# Add Attribute Constraint

-- Add attribute constraints in table E
ALTER TABLE E ADD CONSTRAINT pk_E PRIMARY KEY(eid);
ALTER TABLE E MODIFY en CHAR(32) NOT NULL;
# ALTER TABLE E ADD CONSTRAINT chk_E CHECK(en IS NOT NULL);  -- MySQL does not support.

-- Add attribute constraints in table D
ALTER TABLE D ADD CONSTRAINT pk_E PRIMARY KEY(did);
ALTER TABLE D MODIFY dn CHAR(32) NOT NULL;

-- Add attribute constraints in table 
ALTER TABLE P ADD CONSTRAINT pk_P PRIMARY KEY(pid);
ALTER TABLE P MODIFY pn CHAR(32) NOT NULL;

-- Add attribute constraints in table
ALTER TABLE W MODIFY eid INT NOT NULL;
ALTER TABLE W MODIFY pid INT NOT NULL;

ALTER TABLE E ADD CONSTRAINT fk_E_did FOREIGN KEY(did) REFERENCES D(did) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE D ADD CONSTRAINT fk_D_eid FOREIGN KEY(eid) REFERENCES E(eid) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE P ADD CONSTRAINT fk_P_did FOREIGN KEY(did) REFERENCES D(did) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE W ADD CONSTRAINT fk_W_eid FOREIGN KEY(eid) REFERENCES E(eid) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE W ADD CONSTRAINT fk_W_pid FOREIGN KEY(pid) REFERENCES P(pid) ON UPDATE CASCADE ON DELETE RESTRICT;

# Do something violating the constraints
-- primary key must be unique
INSERT INTO E VALUE (0001, 'WuZi', '1990-12-31', 'Washton', 70000, 1002);
-- primary key must be not-null
INSERT INTO E VALUE (NULL, 'WuZi', '1990-12-31', 'Washton', 70000, 1002);
-- a not-null attribute must be not-null
INSERT INTO E VALUE (0010, NULL, '1990-12-31', 'Washton', 70000, 1002);
INSERT INTO D VALUE (1010, NULL, 0001);
-- foreign key must exist
INSERT INTO D VALUE (1003, 'Analysis', 0009);
-- foreign key can be null
INSERT INTO D VALUE (1003, 'Analysis', NULL);

# Create Triggers
SELECT * FROM W;
DROP TRIGGER IF EXISTS setWorkTime;
DELIMITER $$
CREATE TRIGGER setWorkTime
	BEFORE INSERT ON W
    FOR EACH ROW
    BEGIN
		SET NEW.wt = IF(NEW.wt > 24, 24, NEW.wt);
	END; $$
DELIMITER ;
INSERT INTO W VALUE (0001, 2002, 25);
SELECT * FROM W;

SELECT * FROM E;
DROP TRIGGER IF EXISTS setSalaryBI;
DELIMITER $$
CREATE TRIGGER setSalaryBI
	BEFORE INSERT ON W
    FOR EACH ROW
    BEGIN
		UPDATE E
			SET es = es * 1.02
            WHERE eid = NEW.eid;
    END; $$
DELIMITER ;
INSERT INTO W VALUE (0001, 2002, 25);
SELECT * FROM E;

SELECT * FROM E;
DROP TRIGGER IF EXISTS setSalaryBU;
DELIMITER $$
CREATE TRIGGER setSalaryBU
	BEFORE UPDATE ON W
    FOR EACH ROW
    BEGIN
		UPDATE E
			SET es = es * 1.02
            WHERE eid = NEW.eid;
    END; $$
DELIMITER ;
UPDATE W SET pid = '2002' WHERE eid = '0002';
SELECT * FROM E;

# Create user and grant
CREATE USER 'worker'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON WDB.E TO 'worker'@'localhost';
GRANT UPDATE(ea) ON WDB.E TO 'worker'@'localhost';
DROP USER 'worker'@'localhost';

Code for user ‘worker’@’localhost’:

SELECT * FROM E;
SET SQL_SAFE_UPDATES = 0;
UPDATE E
    SET ea = 'London'
    WHERE en = 0001;
SELECT * FROM E;
UPDATE E
    SET ebd = '1990-01-10'
    WHERE en = 0001;
SELECT * FROM E;

References



版权声明:本文为博主原创文章,未经博主允许不得转载。 举报

相关文章推荐

MySQL语法练习项目(1)

MySQL语法练习(基础语句)

100offer举办的「寻找实干和坚持的技术力量」开源项目投票排名分析程序

由于100offer举办的「寻找实干和坚持的技术力量」</

程序员升职加薪指南!还缺一个“证”!

CSDN出品,立即查看!

MySql 数据库编程 笔记

9月3日 数据库的历史:发展于六十年代 数据库术语: 数据(Data):数据库中存储的基本对象 数据的定义:描述事物的符号记录 数据的种类:文字,图形,图像,声音 数据的特点:数据与其语义是不可分的 数据库(Database,简称DB):长期储存计算机内,有组织的,可共享的大量数据集合...

MySQL 语法

常见关系化数据库有哪些?  收费产品 免费产品 SQL Server : 微软公司产品,中等规模数据库 收费产品,运行在windows平台上 ----- .net平台 + SQL...
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)