DDL
CREATE TABLE `result` (
`rid` int(11) NOT NULL AUTO_INCREMENT COMMENT '成绩编号',
`testName` varchar(255) DEFAULT NULL COMMENT '测试名称',
`score` double(4,2) DEFAULT NULL COMMENT '成绩',
`studentId` int(11) DEFAULT NULL COMMENT '学生id',
PRIMARY KEY (`rid`),
KEY `studentId` (`studentId`),
CONSTRAINT `result_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `student` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
DML
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`createDate` datetime DEFAULT NULL,
`userName` varchar(20) DEFAULT NULL,
`pwd` varchar(36) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`sex` char(2) DEFAULT '男',
`introduce` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
DQL
#存储过程的框架
delimiter $$#分隔符开始
#声明存储过程函数
create PROCEDURE get_student_userName(in p_userName VARCHAR(20))
#开始撰写存储过程
BEGIN
#sql语句
DECLARE p_introduce VARCHAR(255);
SELECT introduce INTO p_introduce FROM student WHERE userName=p_userName;
SELECT now() '时间',p_introduce '读取的简介';
end $$ #分隔符终止
delimiter ; #分隔符结束
#使用存储过程(调用存储过程)
call get_student_userName('赵灵儿');