MySQL实验之英语助手

英语单词助手

基本要求：实现单词的管理，英语单词常用例句的管理，测试题目随机生成的管理。

（1）建立相应的数据库及相关表、属性、约束、规则、默认等；

（2）使用SQL语句，在你设计的每个表中插入至少10条数据，要求记录满足数据约束要求，且尽量真实可信；

（3）自行设计查询要求，给出经常需要用到的查询语句；

（4）编写进行数据管理的存储过程、视图、触发器。

功能需求参考：

（1）实现英语单词的检索、翻译

（2）实现例句的检索

（3）根据难度随机生成单词题目

（4）删除单词时，相关信息是否删除

base查询语句：

CREATE DATABASE IF NOT EXISTS english;

CREATE TABLE IF NOT EXISTS `vocabularies`(
   `word_id` INT UNSIGNED NOT NULL,
   `word` VARCHAR(20),
   `translate` VARCHAR(20),
   `level` CHAR(4) CHECK(level in ('四级','六级')),
   PRIMARY KEY ( `word_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO vocabularies VALUES(1,'abandon','抛弃、丢弃','四级');
INSERT INTO vocabularies VALUES(2,'abnormal','不正常的、变态的','四级');
INSERT INTO vocabularies VALUES(3,'entrepreneur','企业家、主办人','六级');
INSERT INTO vocabularies VALUES(4,'match','竞赛；对手','四级');
INSERT INTO vocabularies VALUES(5,'delete','删除','四级');
INSERT INTO vocabularies VALUES(6,'embrace','拥抱、包围','四级');
INSERT INTO vocabularies VALUES(7,'fashionable','流行的、时髦的','四级');
INSERT INTO vocabularies VALUES(8,'generally','一般地、通常地','四级');
INSERT INTO vocabularies VALUES(9,'climax','顶点、高潮','六级');
INSERT INTO vocabularies VALUES(10,'illustration','说明、图解','四级');
INSERT INTO vocabularies VALUES(11,'journalist','记者、新闻工作者','四级');
INSERT INTO vocabularies VALUES(12,'knowledge','知识、学识、知道','四级');
INSERT INTO vocabularies VALUES(13,'lawn','草坪、草地、草场','四级');
INSERT INTO vocabularies VALUES(14,'capacity','容量、能力','四级');
INSERT INTO vocabularies VALUES(15,'accommodate','容纳；供应、供给','六级');
INSERT INTO vocabularies VALUES(16,'academy','私立中学、专科院校','六级');
INSERT INTO vocabularies VALUES(17,'bruise','青肿、伤痕','六级');
INSERT INTO vocabularies VALUES(18,'brutal','残忍的、野蛮的','六级');
INSERT INTO vocabularies VALUES(19,'hardware','五金器具、硬件','四级');
INSERT INTO vocabularies VALUES(20,'clause','子句；条款','六级');
INSERT INTO vocabularies VALUES(21,'dock','船坞、码头、船厂','六级');
INSERT INTO vocabularies VALUES(22,'doctrine','教义、主义、学说','六级');
INSERT INTO vocabularies VALUES(23,'bargain','交易','四级');
INSERT INTO vocabularies VALUES(24,'envisage','面对、正视、想象','六级');

# DROP TABLE vocabularies

CREATE TABLE IF NOT EXISTS `examples`(
   `sentence_id` INT UNSIGNED NOT NULL PRIMARY KEY,
   `sentence` VARCHAR(100),
   `translate` VARCHAR(100),
   `word_in_sentence_id` INT UNSIGNED,
     FOREIGN KEY(word_in_sentence_id) REFERENCES vocabularies(word_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO examples VALUES(1,'The baby had been abandoned by its mother.','这个婴儿被母亲遗弃了。',1);
INSERT INTO examples VALUES(2,'This stops the cells from growing abnormally.','这阻止了细胞畸形发育。',2);
INSERT INTO examples VALUES(3,'An entrepreneur is more than just a risk taker. He is a visionary.','企业家不仅要能承担风险，还应富于远见。',3);
INSERT INTO examples VALUES(4,'He was watching a football match.','他正在观看一场足球比赛。',4);
INSERT INTO examples VALUES(5,'Your name has been deleted from the list.','你的名字已从名单上删掉。',5);
INSERT INTO examples VALUES(6,'They embraced and promised to keep in touch.','他们互相拥抱，许诺将保持联系。',6);
INSERT INTO examples VALUES(7,'It is becoming fashionable to have long hair again.','现在又开始流行蓄长发了。',7);
INSERT INTO examples VALUES(8,'I generally get up at six.','我一般六点钟起床。',8);
INSERT INTO examples VALUES(9,'The festival will climax on Sunday with a gala concert.','星期天的音乐盛会将把这次会演推向高潮。',9);
INSERT INTO examples VALUES(10,'The statistics are a clear illustration of the point I am trying to make.','这些统计数字清楚地阐明了我要陈述的要点。',10);
INSERT INTO examples VALUES(11,'The main character is a journalist in flight from a failed marriage.','主角是一个逃避失败婚姻的记者。',11);
INSERT INTO examples VALUES(12,'He has a wide knowledge of painting and music.','他在绘画和音乐方面知识渊博。',12);
INSERT INTO examples VALUES(13,'In summer we have to mow the lawn twice a week.','夏天我们每周得修剪草坪两次。',13);
INSERT INTO examples VALUES(14,'The theatre has a seating capacity of 2 000.','那座剧院能容纳2 000名观众。',14);
INSERT INTO examples VALUES(15,'The hotel can accommodate up to 500 guests.','这家旅馆可供500位旅客住宿。',15);
INSERT INTO examples VALUES(16,'It is a military academy.','这是一所军校。',16);
INSERT INTO examples VALUES(17,'She had slipped and badly bruised her face.','她滑了一跤，摔得鼻青脸肿。',17);
INSERT INTO examples VALUES(18,'He was the victim of a very brutal murder.','他是一桩恶性谋杀案的受害者。',18);
INSERT INTO examples VALUES(19,'Do I have the hardware?','有必备的硬件设施么？',19);
INSERT INTO examples VALUES(20,'He has a clause in his contract which entitles him to a percentage of the profits.','他的合同中有一项条款，使他有权分得一定比例的利润。',20);
INSERT INTO examples VALUES(21,'The ship was in dock .','船泊在船坞。',21);
INSERT INTO examples VALUES(22,'The doctrine was based on three fundamental principles. ','这个学说建立在三条基本原理之上。',22);
INSERT INTO examples VALUES(23,'He and his partner had made a bargain to tell each other everything.','他和他的合伙人约定，要互通信息，毫无保留。',23);
INSERT INTO examples VALUES(24,'What level of profit do you envisage?','你预计会有什么样的利润水平？',24);

CREATE TABLE IF NOT EXISTS `vocabulary_tiku`(
   `vocabulary_tiku_id` INT UNSIGNED NOT NULL PRIMARY KEY,
     `title` VARCHAR(50),
   `A` VARCHAR(50),
   `B` VARCHAR(50),
     `C` VARCHAR(50),
     `D` VARCHAR(50),
     `word_level` CHAR(4) CHECK(level in ('四级','六级')),
   `word_in_tiku_id` INT UNSIGNED NOT NULL,
     FOREIGN KEY(word_in_tiku_id) REFERENCES vocabularies(word_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO vocabulary_tiku VALUES(1,'下面哪个单词的意思是"抛弃、丢弃"？',"abandon","abc","abc","abc","四级",1);
INSERT INTO vocabulary_tiku VALUES(2,'下面哪个单词的意思是"不正常的、变态的"？',"abnormal","abc","abc","abc","四级",2);
INSERT INTO vocabulary_tiku VALUES(3,'下面哪个单词的意思是"企业家、主办人"？',"entrepreneur","abc","abc","abc","六级",3);

INSERT INTO vocabulary_tiku VALUES(4,'下面哪个单词的意思是"竞赛；对手"？',"match","abc","abc","abc","四级",4);
INSERT INTO vocabulary_tiku VALUES(5,'下面哪个单词的意思是"删除"？',"delete","abc","abc","abc","四级",5);

INSERT INTO vocabulary_tiku VALUES(6,'下面哪个单词的意思是"拥抱、包围"？',"embrace","abc","abc","abc","四级",6);
INSERT INTO vocabulary_tiku VALUES(7,'下面哪个单词的意思是"流行的、时髦的"？',"fashionable","abc","abc","abc","四级",7);
INSERT INTO vocabulary_tiku VALUES(8,'下面哪个单词的意思是"一般地、通常地"？',"generally","abc","abc","abc","四级",8);
INSERT INTO vocabulary_tiku VALUES(9,'下面哪个单词的意思是"顶点、高潮"？',"climax","abc","abc","abc","六级",9);
INSERT INTO vocabulary_tiku VALUES(10,'下面哪个单词的意思是"说明、图解"？',"illustration","abc","abc","abc","四级",10);
INSERT INTO vocabulary_tiku VALUES(11,'下面哪个单词的意思是"记者、新闻工作者"？',"journalist","abc","abc","abc","四级",11);
INSERT INTO vocabulary_tiku VALUES(12,'下面哪个单词的意思是"知识、学识、知道"？',"knowledge","abc","abc","abc","四级",12);
INSERT INTO vocabulary_tiku VALUES(13,'下面哪个单词的意思是"草坪、草地、草场"？',"lawn","abc","abc","abc","四级",13);
INSERT INTO vocabulary_tiku VALUES(14,'下面哪个单词的意思是"容量、能力"？',"capacity","abc","abc","abc","四级",14);
INSERT INTO vocabulary_tiku VALUES(15,'下面哪个单词的意思是"容纳；供应、供给"？',"accommodate","abc","abc","abc","六级",15);
INSERT INTO vocabulary_tiku VALUES(16,'下面哪个单词的意思是"私立中学、专科院校"？',"academy","abc","abc","abc","六级",16);
INSERT INTO vocabulary_tiku VALUES(17,'下面哪个单词的意思是"青肿、伤痕"？',"bruise","abc","abc","abc","六级",17);
INSERT INTO vocabulary_tiku VALUES(18,'下面哪个单词的意思是"残忍的、野蛮的"？',"brutal","abc","abc","abc","六级",18);
INSERT INTO vocabulary_tiku VALUES(19,'下面哪个单词的意思是"五金器具、硬件"？',"hardware","abc","abc","abc","四级",19);
INSERT INTO vocabulary_tiku VALUES(20,'下面哪个单词的意思是"子句；条款"？',"clause","abc","abc","abc","六级",20);
INSERT INTO vocabulary_tiku VALUES(21,'下面哪个单词的意思是"dock"？',"船坞、码头、船厂","abc","abc","abc","六级",21);
INSERT INTO vocabulary_tiku VALUES(22,'下面哪个单词的意思是"教义、主义、学说"？',"doctrine","abc","abc","abc","六级",22);
INSERT INTO vocabulary_tiku VALUES(23,'下面哪个单词的意思是"交易"？',"bargain","abc","abc","abc","四级",23);
INSERT INTO vocabulary_tiku VALUES(24,'下面哪个单词的意思是"面对、正视、想象"？',"envisage","abc","abc","abc","六级",24);

delete查询语句：

#创建触发器：根据单词删除该单词的例句、题库
CREATE TRIGGER `deleteAssociationByWord`
AFTER DELETE
ON `vocabularies` FOR EACH ROW
BEGIN
    DELETE FROM examples
    WHERE word_in_sentence_id=OLD.`word_id`;
    DELETE FROM vocabulary_tiku
    WHERE word_in_tiku_id=OLD.`word_id`;
END

#删除触发器
DROP TRIGGER deleteAssociationByWord

#先关闭外键约束检查，再执行删除语句，最后再开启外键约束检查
SET foreign_key_checks = 0;
DELETE FROM vocabularies
WHERE word="climax";
SET foreign_key_checks = 1;

lookFor查询：

# 根据单词查询该单词的翻译、等级
CREATE PROCEDURE queryTranslateByWord(IN wordParam VARCHAR(20))
BEGIN
    SELECT word,translate,vocabularies.`level`
    FROM vocabularies
    WHERE word like wordParam;
END

# 删除该存储过程
DROP PROCEDURE IF EXISTS queryTranslateByWord

# 调用方法
CALL queryTranslateByWord('%ab%')

# 根据单词查询该单词的例句
CREATE PROCEDURE queryExampleByWord(IN wordParam VARCHAR(20))
BEGIN
    SELECT word,sentence,examples.translate
    FROM vocabularies,examples
    WHERE vocabularies.word_id=examples.word_in_sentence_id AND word=wordParam;
END

# 删除该存储过程
DROP PROCEDURE IF EXISTS queryExampleByWord

# 调用方法
CALL queryExampleByWord('climax')

produce查询：

# 根据级别（四级 六级）生成简单测试题
CREATE PROCEDURE produceTestByLevel(IN levelParam CHAR(4))
BEGIN
    SELECT title,A,B,C,D
    FROM vocabulary_tiku
    WHERE word_level=levelParam
    ORDER BY RAND() LIMIT 5;
END

# 删除该存储过程
DROP PROCEDURE IF EXISTS produceTestByLevel

# 调用方法
CALL produceTestByLevel('四级');

view查询：

#创建视图
CREATE VIEW theView
AS
SELECT vocabularies.word,vocabularies.translate,vocabularies.`level`,examples.sentence
FROM vocabularies INNER JOIN examples ON vocabularies.word_id=examples.word_in_sentence_id

SELECT *
FROM theView

#删除视图
DROP VIEW theView