mysql

/*
SQLyog Ultimate v12.3.1 (64 bit)
MySQL - 5.7.17-log : Database - xscj

*/

/*!40101 SET NAMES utf8 */;

/!40101 SET SQL_MODE=’’/;

/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;
CREATE DATABASE /!32312 IF NOT EXISTS/xscj /*!40100 DEFAULT CHARACTER SET utf8 */;

USE xscj;

/*Table structure for table kc */

DROP TABLE IF EXISTS kc;

CREATE TABLE kc (
课程号 CHAR(3) NOT NULL,
课程名 VARCHAR(20) NOT NULL,
授课教师 VARCHAR(10) DEFAULT NULL,
开课学期 TINYINT(1) NOT NULL DEFAULT ‘1’,
学时 TINYINT(1) NOT NULL,
学分 TINYINT(1) DEFAULT NULL,
PRIMARY KEY (课程号),
UNIQUE KEY index_kcm (课程名)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

/*Data for the table kc */

INSERT INTO kc(课程号,课程名,授课教师,开课学期,学时,学分) VALUES

(‘101’,‘计算机文化基础’,‘李平’,1,32,2),

(‘102’,‘计算机硬件基础’,‘童华’,1,80,5),

(‘103’,‘程序设计基础’,‘王印’,2,64,4),

(‘104’,‘计算机网络’,‘王可均’,2,64,4),

(‘105’,‘云计算基础’,‘郎景成’,2,64,4),

(‘106’,‘云操作系统’,‘李月’,3,64,4),

(‘107’,‘数据库’,‘陈一波’,3,64,4),

(‘108’,‘网络技术实训’,‘张成本’,3,40,2),

(‘109’,‘云系统实施与维护’,‘唐成林’,4,64,4),

(‘110’,‘云存储与备份’,‘路一业’,4,64,4),

(‘111’,‘云安全技术’,‘李华华’,4,80,5),

(‘112’,‘phthonn程序设计’,‘周治伟’,5,64,4),

(‘114’,‘JAVA程序设计’,‘张山’,5,64,4);

/*Table structure for table xs_kc */

DROP TABLE IF EXISTS xs_kc;

CREATE TABLE xs_kc (
学号 char(10) NOT NULL,
课程号 char(3) NOT NULL,
成绩 float DEFAULT NULL,
学分 tinyint(1) DEFAULT NULL,
PRIMARY KEY (学号,课程号),
KEY 课程号 (课程号),
CONSTRAINT xs_kc_ibfk_2 FOREIGN KEY (课程号) REFERENCES kc (课程号)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table xs_kc */

insert into xs_kc(学号,课程号,成绩,学分) values

(‘2016110101’,‘101’,83,2),

(‘2016110101’,‘102’,64,5),

(‘2016110101’,‘103’,58,0),

(‘2016110102’,‘102’,72,5),

(‘2016110102’,‘103’,75,4),

(‘2016110103’,‘101’,78,2),

(‘2016110104’,‘103’,54,0),

(‘2016110105’,‘101’,65,2),

(‘2016110105’,‘105’,67,4),

(‘2016110106’,‘101’,56,0),

(‘2016110106’,‘102’,57,0),

(‘2016110201’,‘106’,78,4),

(‘2016110202’,‘106’,81,4),

(‘2016110202’,‘107’,85,4),

(‘2016110203’,‘108’,61,2),

(‘2016110204’,‘109’,18,0),

(‘2016110301’,‘110’,63,4),

(‘2016110401’,‘101’,57,0),

(‘2016110401’,‘110’,84,4);

/*Table structure for table xsqk */

DROP TABLE IF EXISTS xsqk;

CREATE TABLE xsqk (
学号 char(10) NOT NULL,
姓名 varchar(10) NOT NULL,
性别 char(2) NOT NULL DEFAULT ‘男’,
出生日期 date NOT NULL,
专业名 varchar(20) NOT NULL,
所在学院 varchar(20) NOT NULL,
联系电话 char(11) DEFAULT NULL,
总学分 tinyint(1) DEFAULT NULL,
备注 varchar(50) DEFAULT NULL,
PRIMARY KEY (学号)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table xsqk */

insert into xsqk(学号,姓名,性别,出生日期,专业名,所在学院,联系电话,总学分,备注) values

(‘2016050102’,‘王真’,‘男’,‘1998-09-06’,‘云计算’,‘计算机学院’,‘13574112544’,NULL,NULL),

(‘2016050202’,‘王成’,‘男’,‘1998-09-06’,‘云计算’,‘计算机学院’,‘13855652224’,NULL,NULL),

(‘2016110101’,‘朱博’,‘男’,‘1998-10-15’,‘云计算’,‘计算机学院’,‘13834838223’,NULL,‘班长’),

(‘2016110102’,‘龙婷婷’,‘女’,‘1998-11-05’,‘云计算’,‘计算机学院’,‘13534323422’,NULL,NULL),

(‘2016110103’,‘张庆国’,‘男’,‘1999-01-09’,‘云计算’,‘计算机学院’,‘13712354932’,NULL,NULL),

(‘2016110104’,‘张小博’,‘男’,‘1998-04-06’,‘云计算’,‘计算机学院’,‘13504832433’,NULL,NULL),

(‘2016110105’,‘钟鹏香’,‘女’,‘1998-05-03’,‘云计算’,‘计算机学院’,‘13634958348’,NULL,NULL),

(‘2016110106’,‘李家琪’,‘男’,‘1998-04-07’,‘云计算’,‘计算机学院’,‘13603493433’,NULL,NULL),

(‘2016110201’,‘曹科梅’,‘女’,‘1998-06-09’,‘信息安全’,‘计算机学院’,‘13443657543’,NULL,NULL),

(‘2016110202’,‘江杰’,‘男’,‘1999-02-06’,‘信息安全’,‘计算机学院’,‘13564943433’,NULL,NULL),

(‘2016110203’,‘肖勇’,‘男’,‘1998-04-12’,‘信息安全’,‘计算机学院’,‘13743242256’,NULL,NULL),

(‘2016110204’,‘周明悦’,‘女’,‘1998-05-18’,‘信息安全’,‘计算机学院’,‘15893954323’,NULL,NULL),

(‘2016110205’,‘蒋亚男’,‘女’,‘1998-04-06’,‘信息安全’,‘计算机学院’,‘13893434356’,NULL,NULL),

(‘2016110301’,‘李娟’,‘女’,‘1998-08-24’,‘网络工程’,‘计算机学院’,‘13355436788’,NULL,‘学习秀员’),

(‘2016110302’,‘成兰’,‘女’,‘1999-01-06’,‘网络工程’,‘计算机学院’,‘13843435643’,NULL,NULL),

(‘2016110303’,‘李图’,‘男’,‘1998-11-25’,‘网络工程’,‘计算机学院’,‘13643432567’,NULL,NULL),

(‘2016110401’,‘陈勇’,‘男’,‘1997-12-23’,‘机器人设计’,‘计算机学院’,‘13735436434’,NULL,‘生活委员’),

(‘2016110404’,‘赵真’,‘女’,‘1998-04-06’,‘机器人设计’,‘计算机学院’,‘13644643534’,NULL,NULL);

/* Function structure for function func_cj */

/*!50003 DROP FUNCTION IF EXISTS func_cj */;
DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost FUNCTION func_cj(xh CHAR(10),kch VARCHAR(3)) RETURNS double(5,1)
BEGIN
RETURN(SELECT 成绩
FROM xs_kc
WHERE xs_kc.学号=xh AND xskc.课程号=kch);
END */$$
DELIMITER ;

/* Function structure for function func_cj1 */

/*!50003 DROP FUNCTION IF EXISTS func_cj1 */;
DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost FUNCTION func_cj1(xh char(10),kch varchar(3)) RETURNS double(5,1)
BEGIN

return (select 成绩 from xs_kc where xs_kc.学号=xh and xs_kc.课程号=kch);

END */$$

DELIMITER ;

/* Procedure structure for procedure count_zym */

/*!50003 DROP PROCEDURE IF EXISTS count_zym */;

DELIMITER $$

/!50003 CREATE DEFINER=root@localhost PROCEDURE count_zym(in zym varchar(20),out count_num int)
READS SQL DATA
begin
select count() into count_num from xsqk where 专业名=zym;
end */$$
DELIMITER ;

/* Procedure structure for procedure proc_xskc */

/*!50003 DROP PROCEDURE IF EXISTS proc_xskc */;

DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost PROCEDURE proc_xskc()
BEGIN

select count(*) number from xs_kc where 课程号=‘101’;

END */$$

DELIMITER ;

/* Procedure structure for procedure proc_xsqk */

/*!50003 DROP PROCEDURE IF EXISTS proc_xsqk */;

DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost PROCEDURE proc_xsqk()
READS SQL DATA
begin
select 学号,姓名,性别,出生日期,专业名,所在学院 from xsqk where 专业名 like ‘%信息安全%’ order by 学号;
end */$$
DELIMITER ;

/* Procedure structure for procedure sum_add */

/*!50003 DROP PROCEDURE IF EXISTS sum_add */;

DELIMITER $$

/*!50003 CREATE DEFINER=root@localhost PROCEDURE sum_add(in x int,in y int)
begin
declare z int default 0;
set z=x+y;
select z as z;
end */$$
DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;