数据库(MySQL)开发技术题库(大题)
有一个关于商品供应及顾客订单的数据库。其中包括四个表,表中信息如下: 供应表apply(id、name、sid、price) 说明:id 供应厂家编号,name 供应厂家名称,sid 商品编号,price 商品价格。 顾客表customers(gid、name、address、balance) 说明:gid 顾客编号,address 地址,balance 余额。 订单表orders(sid、gid、date) 说明:sid 商品编号,gid 顾客编号,date 订单日期。 商品表goods(sid、name、count) 说明:sid 商品编号,name 商品名称,count 商品数量 写出SQL完成如下操作的SQL语句:
1)查询出2008-8-8这一天顾客的订单信息,要求包括顾客姓名、商品名称及订单日期.
2)将商品表中商品编号为'204'的商品名称更改为百事可乐
3)将顾客表上余额不足1000元的,将其订单日期延后10天
4) 删除订单表中商品编号为102的订单记录 5)将商品表中没有顾客订购的商品信息删除
学生答案:
1.select*fromcustomerswherecustomers.gid=orders.gidanddate="2008-8-8"
2.updategoodssetsid=’百事可乐’wheresid=’204’;
3.selectbalanceDATE_ADD(NOW(),INTERVAL10day)fromcustomerswherebalance<1000;
4.deletefromorderswheresid=’102’;
10
已知进销存数据库Jxcxt包含如下数据表: 1)商品表:商品id(自增1), 商品名称, 单位, 单价, 商品状态 2)库存表:库存id(自增1), 商品id, 库存数量 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上两个表的SQL语句;
2、在商品表上创建一个触发器(名字自定),当删除商品时自动删除该商品在库存表中的记录。
学生答案:
- CREATETABLE商品表(商品idINTNOTNULLAUTO_INCREMENTPRIMARYKEY,商品名称VARCHAR(20)NOTNULL,单位VARCHAR(10),单价DOUBLE,商品状态VARCHAR(10));
- CREATETABLE库存表(库存idINTNOTNULLAUTO_INCREMENTPRIMARYKEY,商品idINTNOTNULL,库存数量INT,FOREIGNKEY(商品id)REFERENCES商品表(商品id));2>CREATETRIGGERdelChainBEFOREDELETEON商品表FOREACHROWDELETEFROM库存表WHERE商品id=old.商品id;
10
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上三个表的SQL语句;
2、创建视图 V_选课,其中包含这些字段: 选课id, 学号, 姓名, 课程名, 成绩;
3、查询张三的数据开发技术课程的成绩。
学生答案:
1.学生表:CREATETABLEstudent(xsidINTPRIMARYKEY,xhVARCHAR(13),xmVARCHAR(5),zyVARCHAR(10),njCHAR(2),xmpysxVARCHAR(5))CHARSET=utf8;课程表:CREATETABLEcourse(kcidINTPRIMARYKEY,kcmcVARCHAR(9),xfSMALLINT)CHARSET=utf8;选课表:CREATETABLEsc(xkidINTPRIMARYKEY,xsidINT,kcidINT,gradeDOUBLE(3,2),FOREIGNKEY(xsid)REFERENCESstudent(xsid),FOREIGNKEY(kcid)REFERENCEScourse(kcid))CHARSET=utf8;
2.CREATEVIEWv_选课ASSELECTs.xkid,st.xh,st.xm,c.kcmc,s.gradeFROMscsINNERJOINstudentstONs.xsid=st.xsidINNERJOINcoursecONc.kcid=s.kcid;
10
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 系统已建有getstrpy(p_name VARCHAR(100))用户自定义函数。 请完成如下操作的SQL语句,表名和列名必须和上面的一致: 1、创建以上三个表的SQL语句; 2、创建一个存储过程(名字自定),实现学生表的插入,姓名拼音缩写需要调用getstrpy函数实现。
学生答案: CREATEDATABASExkglxt;CREATETABLE学生表(学生idCHAR(4)PRIMARYKEY,学号CHAR(4),姓名CHAR(4),专业CHAR(4),年级CHAR(4),姓名拼音缩写CHAR(8))CHARSET=utf8;CREATETABLE课程表(课程idCHAR(4)PRIMARYKEY,课程名称CHAR(10),学分CHAR(4))CHARSET=utf8;CREATETABLE选课表(选课idCHAR(4)PRIMARYKEY,学生idCHAR(4),课程idCHAR(4),成绩INT,FOREIGNKEY(学生id)REFERENCES学生表(学生id),FOREIGNKEY(课程id)REFERENCES课程表(课程id))CHARSET=utf8;SELECT学号,姓名,专业,年级FROM学生表,课程表WHERE学分>=100;
7
已知选课管理系统Xkglxt包含如下数据表和自定义函数(函数已定义好可直接使用): 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)自定义函数拼音缩写查询: pysx('中文字符串') 返回 该中文字符串的拼音缩写 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建选课管理系统数据库以及学生表的SQL语句;
2、在学生表上创建一个触发器(名字自定),当新增学生时自动处理姓名拼音缩写。
学生答案:
1、CREATEDATABASEXkglxtCHARSETutf8;CREATETABLE学生(学生idINTPRIMARYKEY,学号INT,专业VARCHAR(30),年纪INT,姓名VARCHAR(30),姓名拼音缩写VARCHAR(30));
2DELIMITER$$CREATETRIGGERmytriggerAFTERINSERTON学生FOREACHROWBEGININSERTINTO学生(姓名拼音缩写)VALUES(pysx(new.姓名));END$$DELIMITER;
10
已知产品销售数据库cpxs包含如下数据表: 1)产品表:产品编号,产品名称,价格,库存量。 2)销售商表:客户编号,客户名称,地区,负责人,电话。 3)产品销售表:销售日期,产品编号,客户编号,数量,销售额。 写出SQL完成如下操作的SQL语句:
1.写出产品表的建表语句
2.向产品表中插入如下记录: 0001 空调 3000 200 0002 冰箱 2500 300
3.将产品表中每种商品价格打八折后进行显示
4.查找价格在2000到2900之间的产品名称。 5.在产品销售表上创建“冰箱”产品表的视图bxcp
学生答案:
1、CREATETABLE产品表(产品编号char(4)PRIMARYKEY,产品名称VARCHAR(20)NOTNULL,价格float(7,2),库存量int(5));CREATETABLE销售商表(客户编号char(5)PRIMARYKEY,客户名称VARCHAR(20)NOTNULL,地区VARCHAR(10),负责人VARCHAR(10),电话VARCHAR(11));CREATETABLE产品销售表(销售日期date,产品编号char(4),客户编号char(5),数量int,销售额float(8,2));
2、insertinto产品表values(’0001’,’空调’,3000,200);insertinto产品表values(’0002’,’冰箱’,2500,300);
3、update产品表set价格=价格*0.8;
4、select产品名称from产品表where价格between2000and2900;5、createviewbxcpasselect*from产品表where产品名称=冰箱withcheckoption;
10
已知进销存数据库Jxcxt包含如下数据表: 1)商品表:商品id, 商品名称, 单位, 单价, 商品状态 2)库存表:库存id, 商品id, 库存数量 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上两个表的SQL语句;
2、创建一个存储过程(名字自定),实现商品表的的插入,需要判断当前商品名称是否存在。
学生答案:
CREATEDATABASEJxcxt;CREATETABLE商品表(商品idCHAR(4)PRIMARYKEYAUTO_INCREMENT,商品名称CHAR(4),单位CHAR(4),单价INT,商品状态CHAR(4))CHARSET=utf8;CREATETABLE库存表(库存idCHAR(4)PRIMARYKEYAUTO_INCREMENT,商品idCHAR(4),库存数量INT,FOREIGNKEY(商品id)REFERENCES商品表(商品id))CHARSET=utf8;CREATEVIEWV_库存(库存id,商品名称,单位,库存数量,库存金额)ASSELECT*FROM(商品表,库存表)WITHCHECKOPTION;
10
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上三个表的SQL语句;
2、创建一个存储过程(名字自定),实现选课表的插入,需要判断当前课程id和学生id是否存在。
学生答案:
CREATEDATABASExkglxt;CREATETABLE学生表(学生idCHAR(4)PRIMARYKEY,学号CHAR(4),姓名CHAR(4),专业CHAR(4),年级CHAR(4),姓名拼音缩写CHAR(8))CHARSET=utf8;CREATETABLE课程表(课程idCHAR(4)PRIMARYKEY,课程名称CHAR(10),学分CHAR(4))CHARSET=utf8;CREATETABLE选课表(选课idCHAR(4)PRIMARYKEY,学生idCHAR(4),课程idCHAR(4),成绩INT,FOREIGNKEY(学生id)REFERENCES学生表(学生id),FOREIGNKEY(课程id)REFERENCES课程表(课程id))CHARSET=utf8;
SELECT学号,姓名,专业,年级FROM学生表,课程表,选课表WHERE成绩<60;
10
有一个关于商品供应及顾客订单的数据库。其中包括四个表,表中信息如下: 供应表apply(id、name、sid、price) 说明:id 供应厂家编号,name 供应厂家名称,sid 商品编号,price 商品价格。 顾客表customers(gid、name、address、balance) 说明:gid 顾客编号,address 地址,balance 余额。 订单表orders(sid、gid、date) 说明:sid 商品编号,gid 顾客编号,date 订单日期。 商品表goods(sid、name、count) 说明:sid 商品编号,name 商品名称,count 商品数量
1)从供应表中查询全体供应厂商的基本信息
2)从顾客表中查询地址在"长春"的顾客的顾客编号、顾客姓名及余额.
3)从商品表中查询以"可乐"两个字结尾的商品名称及数量,并按商品数量降序排序
4)从订单表中查询购买商品编号为"101"商品的顾客编号及订单日期.
5)向商品表中追加一条纪录(204,可口可乐,900)
6)从商品表中查询最多商品数量、最少商品数量及商品总数量的记录信息
学生答案:
1、select*fromapply;
2、selectgid,name,balancefromcustomerswhereaddress="长春";
3、selectname,countfromgoodswherenamelike"%可乐"orderbycountdesc;
4、selectgid,datefromorderswheresid=101;
5、insertintogoodsvalues(204,"可口可乐",900);
6、selectmax(count),min(count),sum(count)fromgoods;
10
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 请完成如下操作的SQL语句,表名和列名必须和上面的一致: 1、创建以上三个表的SQL语句; 2、创建一个存储过程(名字自定),实现课程表的插入,需要判断当前课程名称的课程是否存在。
学生答案:
- CREATETABLE`temp`.`学生表`(`学生id`VARCHAR(20),`学号`VARBINARY(20),`姓名`VARCHAR(20),`专业`VARCHAR(20),`年级`VARCHAR(20),`姓名拼音缩写`VARCHAR(10))CHARSET=utf8;CREATETABLE`temp`.`课程表`(`课程id`VARCHAR(20),`课程名称`VARCHAR(20),`学分`VARCHAR(20))CHARSET=utf8;
- CREATETABLE`temp`.`选课表`(`选课id`VARCHAR(20),`学生id`VARCHAR(20),`课程id`VARCHAR(20),`成绩`VARCHAR(20))CHARSET=utf8;2.SELECT`学生表`.`学号`,`学生表`.`姓名`,`学生表`.`专业`,`学生表`.`年级`FROM`学生表`,`选课表`,`课程表`WHERE`学生表`.`学生id`=`选课表`.`学生id`AND`课程表`.`课程id`=`选课表`.`课程id`AND`课程表`.`课程名称`="数据库原理";
8
已知选课管理系统Xkglxt包含如下数据表和自定义函数(函数已定义好可直接使用): 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)自定义函数拼音缩写查询: pysx('中文字符串') 返回 该中文字符串的拼音缩写 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建选课管理系统数据库以及学生表的SQL语句;
2、在学生表上创建一个触发器(名字自定),当修改学生表的姓名时自动处理姓名拼音缩写
学生答案:
1、CREATEDATABASEXkglxtCHARSETutf8;CREATETABLE学生(学生idINTPRIMARYKEY,学号INT,专业VARCHAR(30),年纪INT,姓名VARCHAR(30),姓名拼音缩写VARCHAR(30));
2`DELIMITER$$CREATETRIGGERmytriggerAFTERINSERTON学生FOREACHROWBEGININSERTINTO学生(姓名拼音缩写)VALUES(pysx(new.姓名));END$$DELIMITER;
8
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 请完成如下操作的SQL语句,表名和列名必须和上面的一致: 1、创建以上三个表的SQL语句; 2、在课程表上创建一个触发器(名字自定)。,当删除课程信息时自动删除该课程的选课信息。
学生答案:
1、1、CREATETABLE学生(学生idINTPRIMARYKEY,学号INT,姓名VARCHAR(30),专业VARCHAR(30),年纪INT,姓名拼音缩写VARCHAR(10))CHARSETutf8;CREATETABLE课程(课程idINTPRIMARYKEY,课程名称VARCHAR(30),学分INT)CHARSETutf8;CREATETABLE选课(选课idINTPRIMARYKEY,学生idINT,课程idINT,成绩INT)CHARSETutf8;
2、DELIMITER$$CREATETRIGGERmytriggerAFTERDELETEON课程FOREACHROWBEGINDELETEFROM选课WHERE选课.课程id=old.课程id;END$$DELIMITER;
8
已知进销存数据库Jxcxt包含如下数据表:
1)商品表:商品id(自增1), 商品名称, 单位, 单价, 商品状态
2)库存表:库存id(自增1), 商品id, 库存数量
请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上两个表的SQL语句;
2、在商品表上创建一个触发器(名字自定),当新增商品时自动在库存表中插入该商品的库存信息(库存数量为0)。
学生答案:
1)
createtable商品表(商品idintnotnullauto_increment,商品名称varchar(255),单位varchar(10),单价int,商品状态varchar(10),primarykey(商品id))charsetutf8;createtable库存表(库存idintnotnullauto_increment,商品idint,库存数量int,foreignkey(商品id)references商品表(商品id),primarykey(库存id))charsetutf8;
2)delimiter$$createtrigger增商品触库存afterinserton商品表foreachrowbegininsertinto库存表values(null,new.商品id,0);end$$
10
已知选课管理系统Xkglxt包含如下数据表: 1)学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 2)课程表: 课程id, 课程名称, 学分 3)选课表: 选课id, 学生id, 课程id, 成绩 请完成如下操作的SQL语句,表名和列名必须和上面的一致:
1、创建以上三个表的SQL语句;
2、创建一个存储过程(名字自定),实现学生表的插入,需要判断当前学号的学生是否存在。
学生答案:
1>CREATETABLE学生表(学生idINTNOTNULLPRIMARYKEY,学号VARCHAR(20)NOTNULL,姓名VARCHAR(10)NOTNULL,专业VARCHAR(20),年级VARCHAR(10),姓名拼音缩写VARCHAR(10));CREATETABLE课程表(课程idINTNOTNULLPRIMARYKEY,课程名称VARCHAR(20),学分FLOAT);CREATETABLE选课表(选课idINTNOTNULLPRIMARYKEY,学生idINTNOTNULL,课程idINTNOTNULL,成绩FLOAT,FOREIGNKEY(学生id)REFERENCES学生表(学生id),FOREIGNKEY(课程id)REFERENCES课程表(课程id));2>DELIMITER$$CREATEPROCEDUREisInsert(id1INT,id2INT,id3INT,gradeFLOAT)BEGINIF((SELECTCOUNT(*)FROM课程表,学生表WHERE学生表.学生id=id2AND课程表.课程id=id
3)<>0)THENINSERTINTO选课表VALUES(id1,id2,id3,grade);ENDIF;END$$DELIMITER;
10
四、计算题
1、按照要求,完成以下操作。
(1)、建立一个通信录数据库,要求需要有以下三个表:
lxrenb(联系人表)其字段为(联系人编号、姓名、联系电话、家庭地址),
thjlb(通话记录表)其字段为(通话记录编号、联系人编号、通话开始时间、通知结束时间、通话类别、通话状态),
dxjlb(短信记录表)其字段为(短信记录编号、联系人编号、接发时间、短信内容、短信类别)。
请在SQL SERVER 下建立这个数据库,数据库名为:TXLSJK。字段名以及字段数据类型可自拟。
(2)、在联系人表中插入以下5条记录:
(1,朱红敏,15857455696,河南洛阳市中学路1号)
(2,张丽花,18953991767,河南洛阳市武汉路2号)
(3,梁宝莉,13752317719,河南洛阳市大学路3号)
(4,王君志,13057676112,河南洛阳市大学路4号)
(5,李保江,13752309239,河南洛阳市大学路5号)
(3)、在短信记录表中插入以下一条记录
(1,1,当前系统时间,你的书已到请速来领取,1)
(4)、更新联系人编号为4的联系人电话为13938801001
(5)、依据联系人表和通话记录表建立视图:v_lxren_thjlb(联系人编号、姓名、通话类别、通话状态)
答:(1)create table lxrenb(id int NOT NULL,name varchar(20) NOT NULL,tel varchar(11),address varchar(255),primary key(id))
---同上
(2)insert into lxrenb (id, name, tel, address) values(1,’ 朱红敏’,’ 15857455696’,’ 河南洛阳市中学路1号’)
---同上
(3)insert into dxjlb
(4)update lxrenb set ... where
(5)CREATE VIEW v_lxren_thjlb (联系人编号、姓名、通话类别、通话状态) AS
SELECT ... WHERE ...
2、学生选课系统,主要表有:学生基本信息表steudents(学号sid、姓名sname、性别ssex、专业spro、出生年月sbirthday等),选课表courses_selection(学号sid、课程号cid、分数score),课程表courses(课程号cid、课程名cname、所属专业professional、学分credits)要求:
写出SQL语句。
1) 查询所有选SQL课程的学生学号,姓名和专业。
2) 删除所有选择数学的同学的选课记录。
3) 查询有哪些课程没有被任何同学报选。
答:
- select … from
- delete table_name where
- select … from courses where cid not in (select cid from courses_selection)
实验一:项目名称:网络课程平台
- 需要建立的表:
课程章节表:
chapter(chapterId,chapterName,questionCount)
课程题库表:
questionBank(questionId,questionContent,questionAnswer,type,difficulty,chapterId)
教师表:
teacher(teacherId,teacherNUM,teacherName,teacherPW)
班级表:
class(classId,className,classSize,teacherId)
学生表:
student(studentId,studentNUM,studentName,studentPW,classId)
作业规则表:
work_rule(work_ruleId,chapterId,type,difficulty,count)
实验规则表:
experiment_rule(experiment_ruleId,chapterId,type,difficulty,count)
试卷规则表:
exam_rule(exam_ruleId,fromchapterId,tochapterId,type,difficulty,count)
课程作业总表:
work(workId,chapterId,studentId,fromTime,toTime,score)
课程作业明细表:
work_detail(work_detailId,workId,questionId,answer,grade)
课程实验总表:
experiment(experimentId,chapterId,studentId,fromTime,toTime,score)
课程实验明细表:
experiment_detail(experiment_detailId,experimentId,questionId,answer,grade)
课程试卷总表:
exam(examId,chapterId,studentId,fromTime,toTime,state,score)
课程试卷明细表:
exam_detail(exam_detailId,examId,questionId,answer,grade)
一、编写存储过程1
(一)插入教师表
DELIMITER $$
USE `network_course`$$
DROP PROCEDURE IF EXISTS `insert_teacher`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_teacher`(
IN num0 VARCHAR(20),
IN name0 VARCHAR(20),
IN pw0 VARCHAR(20)
)
BEGIN
DECLARE count0 INT;
SELECT COUNT(*) INTO count0 FROM teacher WHERE teachernum=num0;
IF count0 = 0 THEN
INSERT INTO teacher(teachernum,teachername,teacherpw)VALUES(num0,name0,pw0);
END IF;
END$$
DELIMITER ;
(二)插入学生表
DELIMITER $$
USE `network_course`$$
DROP PROCEDURE IF EXISTS `insert_student`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_student`(
IN num0 VARCHAR(20),
IN name0 VARCHAR(20),
IN pw0 VARCHAR(20),
IN classid0 INT
)
BEGIN
DECLARE count1 INT;
SELECT COUNT(*) INTO count1 FROM student WHERE studentnum=num0;
IF count1 = 0 THEN
INSERT INTO student(studentnum,studentname,studentpw,classid)VALUES(num0,name0,pw0,classid0);
END IF;
END$$
DELIMITER ;
(三)按帐号查询学生表
DELIMITER $$
USE `network_course`$$
DROP PROCEDURE IF EXISTS `select_student`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `select_student`(
IN param1 VARCHAR(20)
)
BEGIN
SELECT studentnum,studentname,studentpw,classid FROM student WHERE studentnum=param1;
END$$
DELIMITER ;
(四)按学生姓名查询学生表
DELIMITER $$
USE `network_course`$$
DROP PROCEDURE IF EXISTS `select_student1`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `select_student1`(
IN name0 VARCHAR(20)
)
BEGIN
SELECT studentnum,studentname,studentpw,classid,classname FROM v_class_student WHERE studentname LIKE CONCAT('%', name0, '%');
END$$
DELIMITER ;
(五)批量插入学生表
DELIMITER $$
USE `network_course`$$
DROP PROCEDURE IF EXISTS `insert_student1`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_student1`(
IN stustr VARCHAR(5000)
)
BEGIN
DECLARE str1 VARCHAR(200);
DECLARE num0 VARCHAR(20);
DECLARE name0 VARCHAR(20);
DECLARE pw0 VARCHAR(20);
DECLARE classid0 INT;
DECLARE flag INT;
WHILE stustr > '' DO
SET str1 = MID(stustr,1,LOCATE(';',stustr)-1);
SET num0 = MID(str1,1,LOCATE(',',str1)-1);
SET str1 = MID(str1,LOCATE(',',str1)+1);
SET name0 = MID(str1,1,LOCATE(',',str1)-1);
SET str1 = MID(str1,LOCATE(',',str1)+1);
SET pw0 = MID(str1,1,LOCATE(',',str1)-1);
SET classid0 = CAST(MID(str1,LOCATE(',',str1)+1) AS SIGNED);
CALL insert_struent(num0,name0,pw0,classid0);
SET stustr = MID(stustr,LOCATE(';',stustr)+1);
END WHILE;
END$$
DELIMITER ;
(六)编写一存储过程(两上参数1功能号,2字符串)实现插入教师表、学生表、按帐号查询学生表、按学生姓名查询学生
DELIMITER $$
USE `network_course`$$
DROP PROCEDURE IF EXISTS `proc1`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`(
IN param1 INT,
IN param2 VARCHAR(2000)
)
BEGIN
DECLARE num0 VARCHAR(20);
DECLARE name0 VARCHAR(20);
DECLARE pw0 VARCHAR(20);
DECLARE classid0 INT;
DECLARE count0 INT;
CASE param1
WHEN 1 THEN -- 插入教师;
SET num0 = MID(param2,1,LOCATE('|',param2)-1);
SET param2 = MID(param2,LOCATE('|',param2)+1);
SET name0 = MID(param2,1,LOCATE('|',param2)-1);
SET pw0 = MID(param2,LOCATE('|',param2)+1);
SELECT COUNT(*) INTO count0 FROM teacher WHERE teachernum = num0;
IF count0 = 0 THEN
INSERT INTO teacher(teachernum,teachername,teacherpw)VALUES(num0,name0,pw0);
END IF ;
WHEN 2 THEN -- 插入学生
SET num0 = MID(param2,1,LOCATE('|',param2)-1);
SET param2 = MID(param2,LOCATE('|',param2)+1);
SET name0 = MID(param2,1,LOCATE('|',param2)-1);
SET param2 = MID(param2,LOCATE('|',param2)+1);
SET pw0 = MID(param2,1,LOCATE('|',param2)-1);
SET classid0 = CAST(MID(param2,LOCATE('|',param2)+1) AS SIGNED);
SELECT COUNT(*) INTO count0 FROM student WHERE studentnum = num0;
IF count0 = 0 THEN
INSERT INTO student(studentnum,studentname,studentpw,classid)VALUES(num0,name0,pw0,classid0);
END IF;
WHEN 3 THEN -- 学生帐号查询
SELECT studentnum,studentname,studentpw,classid,classname FROM v_class_student WHERE studentnum=param2;
WHEN 4 THEN -- 按姓名查询
SELECT studentnum,studentname,studentpw,classid,classname FROM v_class_student WHERE studentname LIKE CONCAT('%', param2, '%');
END CASE;
END$$
DELIMITER ;
二、编写存储过程2
(一)判断用户登陆状态
输入参数:学生帐号和密码
输出当前学生帐号的状态(登录成功,用户名或密码不正确,密码过于简单)
DELIMITER $$
USE `network_course`$$
DROP PROCEDURE IF EXISTS `login`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `login`(
IN num0 VARCHAR(20),
IN pw0 VARCHAR(20)
)
BEGIN
DECLARE count0 INT;
SELECT COUNT(*) INTO count0 FROM student WHERE studentnum=num0 AND studentpw=pw0;
IF count0 = 1 THEN
SET count0 = checkpw(pw0);
ELSE
SET count0 = 0;
END IF ;
SELECT count0 AS count0;
END$$
DELIMITER ;
/*判断口令是否合法的函数*/
DELIMITER $$
USE `network_course`$$
DROP FUNCTION IF EXISTS `checkpw`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `checkpw`(pw0 VARCHAR(20)) RETURNS INT(11)
BEGIN
DECLARE flag INT;
DECLARE ls_i INT;
DECLARE ls_str VARCHAR(20);
SET flag = 1;
IF (LENGTH(pw0)<6 ) OR (LOCATE(pw0,'01234567890123456789876543210987654321')>0) THEN
SET flag = 2;
ELSE
SET ls_i = 1;
SET ls_str = '';
WHILE (ls_i<=LENGTH(pw0)) DO
IF(LOCATE(MID(pw0,ls_i,1),ls_str)<=0) THEN
SET ls_str = CONCAT(ls_str,MID(pw0,ls_i,1));
END IF ;
SET ls_i = ls_i + 1;
END WHILE ;
IF LENGTH(ls_str)<3 THEN
SET flag = 2;
END IF;
END IF ;
RETURN flag;
END$$
DELIMITER ;
(二)生成学生试卷
输入参数:学生帐号,按照规则生成学生试卷总表以及试卷明细表
USE `network_course`$$
DROP PROCEDURE IF EXISTS `insert_stu_exam`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_stu_exam`(
IN studentid0 INT
)
BEGIN
DECLARE examid0 INT;
DECLARE fromc0 INT;
DECLARE toc0 INT;
DECLARE type0 INT;
DECLARE diff0 INT;
DECLARE c0 INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT `fromchapterid`,`tochapterid`,`type`,`difficulty`,`count` FROM exam_rule ORDER BY exam_ruleid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SELECT COUNT(*) INTO c0 FROM exam WHERE studentid=studentid0;
IF c0=0 THEN
INSERT INTO exam(studentid,state,score)VALUES(studentid0,0,0);
SELECT LAST_INSERT_ID() INTO examid0;
OPEN cur;
FETCH cur INTO fromc0,toc0,type0,diff0,c0;
WHILE(NOT done) DO
INSERT INTO exam_detail(examid,questionid,answer,grade)SELECT examid0 AS examid,questionid,'' AS answer,0 AS grade FROM questionbank WHERE chapterid>=fromc0 AND chapterid<=toc0 AND `type`=type0 AND difficulty=diff0 ORDER BY RAND() LIMIT c0;
FETCH cur INTO fromc0,toc0,type0,diff0,c0;
END WHILE;
CLOSE cur;
END IF ;
END$$
DELIMITER ;
(三)生成班级试卷
输入参数:班级ID,按照规则生成班级所有学生的试卷总表以及试卷明细表
DELIMITER $$
USE `network_course`$$
DROP PROCEDURE IF EXISTS `insert_class_exam`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_class_exam`(
IN classid0 INT
)
BEGIN
DECLARE examid0 INT;
DECLARE stuid0 INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT studentid FROM student WHERE classid=classid0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
FETCH cur INTO stuid0;
WHILE(NOT done) DO
CALL insert_stu_exam (stuid0);
FETCH cur INTO stuid0;
END WHILE;
CLOSE cur;
END$$
DELIMITER ;
三、编写触发器
(一)章节试题数量触发器
当插入、删除试题时,修改章节表的试题数量
DELIMITER $$
USE `network_course`$$
DROP TRIGGER /*!50032 IF EXISTS */ `update_questionCount0`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_questionCount0` AFTER INSERT ON `questionbank`
FOR EACH ROW BEGIN
UPDATE `chapter` SET `chapter`.`questionCount` = `chapter`.`questionCount` + 1 WHERE chapterid = new.chapterid;
END;
$$
DELIMITER ;
DELIMITER $$
USE `network_course`$$
DROP TRIGGER /*!50032 IF EXISTS */ `update_questionCount1`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_questionCount1` BEFORE DELETE ON `questionbank`
FOR EACH ROW BEGIN
UPDATE `chapter` SET `chapter`.`questionCount` = `chapter`.`questionCount` - 1 WHERE chapterid = old.chapterid;
END;
$$
DELIMITER ;
(二)班级学生人数触发器
当插入、删除学生时,修改班级表的学生人数
DELIMITER $$
USE `network_course`$$
DROP TRIGGER /*!50032 IF EXISTS */ `update_classsize0`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_classsize0` AFTER INSERT ON `student`
FOR EACH ROW BEGIN
UPDATE `class` SET `class`.`classsize` = `class`.`classsize` + 1 WHERE classid = new.classid;
END;
$$
DELIMITER ;
DELIMITER $$
USE `network_course`$$
DROP TRIGGER /*!50032 IF EXISTS */ `update_classsize1`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_classsize1` BEFORE DELETE ON `student`
FOR EACH ROW BEGIN
UPDATE `class` SET `class`.`classsize` = `class`.`classsize` - 1 WHERE classid = old.classid;
END;
$$
DELIMITER ;
实验三:进销存管理系统
- 需要建立的表:
操作员表(czyb):
操作员ID号(czyid)、操作员代码(czydm)、操作员口令(czykl)、操作员类别(czylb)
商品表(spb):商品ID号(spid)、商品名称(spmc)、名称首拼(spmcsx)、商品条码(sptm)、商品单位(spdw)、备注(spbz)
供货商表(ghsb):供应商ID号(gysid)、供应商名称(gysmc)、名称首拼(spmcsx)、供应商地址(gysdz)、联系方式(gyslxfs)、备注(gysbz)
客户表(khb):客户ID号(khid)、客户名称(khmc)、名称首拼(khmcsx)、客户地址(khdz)、联系方式(khlxfs)、备注(khbz)
仓库表(ckb):仓库ID号(ckid)、仓库名称(ckmc)、仓库地址(ckdz)
进货总表(jhzb):进货ID号(jhid)、供应商ID号(gysid)、进货日期(jhrq)、进货金额(jhje)、操作员ID号(czyid)、备注(jhbz)
进货明细表(jhmxb):进货明细ID号(jhmxid)、进货ID号(jhid)、商品ID号(spid)、进货单价(jhdj)、进货数量(jhsl)
销售总表(xszb):销售ID号(xsid)、客户ID号(khid)、销售日期(xsrq)、销售金额(xsje)、操作员ID号(czyid)、备注(xsbz)
销售明细表(xsmxb):销售明细ID号(xsmxid)、销售ID号(xsid)、商品ID号(spid)、销售单价(xsdj)、销售数量(xssl)
库存表(kcb):库存ID号(kcid)、仓库ID号(ckid)、商品ID号(spid)、进货平均单价(jhpjdj)、销售平均单价(xspjdj)、库存数量(kcsl)、最高库存量(zgkcl)、最低库存量(zdkcl)。
- 编写触发器实现:当插入或修改商品名称时,修改名称拼音缩写
DELIMITER $$
USE `mypos`$$
DROP TRIGGER /*!50032 IF EXISTS */ `updatepysx`$$
CREATE
TRIGGER `updatepysx` BEFORE INSERT ON `spb`
FOR EACH ROW BEGIN
SET new.spmcsx=qxmjp(new.xsxm);
END;
$$
DELIMITER ;
DELIMITER $$
USE `mypos`$$
DROP TRIGGER /*!50032 IF EXISTS */ `updatepysx1`$$
CREATE
TRIGGER `updatepysx1` BEFORE UPDATE ON `spb`
FOR EACH ROW BEGIN
IF (old.xsxm != new.xsxm) THEN
SET new.xmjp=qxmjp(new.xsxm);
END IF;
END;
$$
DELIMITER ;
CREATE DEFINER=`root`@`localhost` FUNCTION `qxmjp`(p_name VARCHAR(100) ) RETURNS VARCHAR(100) CHARSET utf8
BEGIN
DECLARE V_COMPARE VARCHAR(255);
DECLARE V_RETURN VARCHAR(255);
DECLARE I INT;
SET I = 1;
SET V_RETURN = '';
WHILE I < LENGTH(P_NAME) DO
SET V_COMPARE = SUBSTR(P_NAME, I, 1);
IF (V_COMPARE != '') THEN
SET V_RETURN = CONCAT(V_RETURN, qpysx(V_COMPARE));
END IF;
SET I = I + 1;
END WHILE;
IF (ISNULL(V_RETURN) OR V_RETURN = '') THEN
SET V_RETURN = P_NAME;
END IF;
RETURN V_RETURN;
END$$
DELIMITER ;
DELIMITER $$
USE `pjsjk`$$
DROP FUNCTION IF EXISTS `qpysx`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `qpysx`(p_name VARCHAR(10)) RETURNS VARCHAR(10) CHARSET utf8
BEGIN
DECLARE V_RETURN VARCHAR(255);
SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(P_NAME USING gbk),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
IF v_return IS NOT NULL THEN
RETURN V_RETURN;
ELSE
RETURN p_name;
END IF ;
END$$
DELIMITER ;
- 编写触发器实现:当插入或删除进货明细表时修改库存表的库存数量。
DELIMITER $$
USE `mypos`$$
DROP TRIGGER /*!50032 IF EXISTS */ `updatekcsl`$$
CREATE
TRIGGER `updatekcsl` BEFORE INSERT ON `jhmxb`
FOR EACH ROW BEGIN
Udpate kcb set kcsl = kcsl + new.jhsl where spid=new.spid;
END;
$$
三、缩写存储过程插入进货记录总表和明细表
/*先插入进货总表,取总表的ID,@@IDENTITY; 再依次插入明细表,做成事务,(start transaction;)commit;rollback;*/
DELIMITER $$
/*插入进货表和进货明细表,供应商ID号(gysid)、进货日期(jhrq)、进货金额(jhje)、操作员ID号(czyid)、备注(jhbz)*/
/*进货ID号(jhid)、商品ID号(spid)、进货单价(jhdj)、进货数量(jhsl)*/
CREATE PROCEDURE `jhjlcp`(gysid INT,jhje NUMERIC(18,2),czyid INT,jhbz VARCHAR(100),jhmx VARCHAR(8000))
BEGIN
/* 插入总表,并且将明细表解析*/
DECLARE @strls varchar(8000);
DECLARE @jhid INT;
DECLARE @spid INT;
DECLARE @jhdj NUMERIC(18,2);
DECLARE @jhsl NUMERIC(18,2);
SET autocommit = 0;
/** 标记是否出错 */
DECLARE t_error int default 0;
/** 如果出现sql异常,则将t_error设置为1后继续执行后面的操作 */
DECLARE continue handler for sqlexception set t_error=1;
START TRANSACTION;
insert into jhzb(gysid,jhrq,jhje,czyid,jhbz)values(gysid,curdate(),jhje,czyid,jhbz)
SET @jhid = @@identity;;
WHILE (jhmx>'') DO
SET @strls = MID(jhmx,1,LOCATE(';',jhmx)-1);
SET @spid=CAST(MID(@strls,1,LOCATE(',',@strls)-1) AS SIGNED);
SET @strls = MID(@strls,LOCATE(',',@strls)+1);
SET @jhdj=CAST(MID(@strls,1,LOCATE(',',@strls)-1) AS DECIMAL);
SET @jhje=CAST(MID(@strls,LOCATE(',',@strls)+1) AS DECIMAL);
INSERT INTO jhmxb(jhid,spid,jhdj,jhsl)values(@jhid,@spid,@jhdj,@jhsl);
SET jhmx = MID(jhmx,LOCATE(';',jhmx)+1);
END WHILE;
IF t_error=1 THEN
ROLLBACK; -- 事务回滚
ELSE
COMMIT; -- 事务提交
END IF;
END */$$
DELIMITER ;