-- ************************************************************************数据库设计**************************************************

		-- 用户注册的用户名即他的数据库名;	
		-- 获取用户名,查库,无记录则进行注册;
		-- 有记录则注册失败!

		-- 注册:数据库命令:grant all privileges on database_name.* to user_name@'%' identified by "password"; //database_name=user_name;

		-- 删除用户:drop user 'user_name'@'%';

		-- 授权后立即刷新生效:flush privileges;

		-- mysql/user表:User,Host:用户名,访问权限(%是任意位置访问);

		-- 新建用户 websql 用于管理在线数据库应用的数据;

		-- 在应用中,服务器发送往客户端和客户端发送往服务器的以 "#" 分割;

		-- 上传的文件,题目与题目,sql语句与sql语句之间用 "#*#" 分割;
		-- load data local infile fileName into table tableName character set utf8 fields terminated by '\t';

-- **********************************************************************
		drop database if exists websql;
		create database websql;
		use websql;

		 create table User( -- 用户信息表
			 user char(16) not null,
			 passwd char(16) not null,
			 sid char(20) not null,
			 email char(32) not null,
			 name char(8) not null
			 prymary key(user));

		create table Admin(
			user char(16) primary key,
			passwd char(20) not null);

-- ******************************************************************
		create table ExamGrade( -- 考试成绩
			user char(16) not null,
			ExamID varchar(20) not null,
			grade float default 0);


		create table PractiseGrade( -- 作业成绩
			user char(16) not null,
			practiseName varchar(20) not null,
			grade float default 0);

-- ********************************************************************
		create table Subject( -- 考生试卷(试题ID,答案,评分)
		 examName char(20) not null, 
		 user char(16) not null,
		 subjectID int not null,
		 anserData varchar(900),
		 grade float default -1);

		create table ExamDataBank( -- 考试题库
			examName char(20) not null,
			data varchar(255) not null,
			subjectID int auto_increment primary key);

		create table Exam( -- 考试表,记录所有发布过的考试
			examName char(20) primary key,
			startTime datetime,
			endTime datetime,
			whenLong smallint not null,
			subjectCounts smallint not null);

		create table RecentExam( -- 记录当前正在进行的考试
			examName char(20));
-- ****************************************************************************
		create table Practise( -- 学生练习题(习题ID,答案,评分)
		 partName char(20) not null, 
		 user char(16) not null,
		 practiseID int not null,
		 anserData varchar(900),
		 whenLong int,
		 grade float default -1,
		 ranks int default 0); -- grade 为作业等级

		create table Part( -- 课后练习记录表
			partName char(20) not null unique,
			endTime datetime,
			status boolean default 1);
		
		create table PartDataBank( -- 课后练习的题目库
 		partName char(20) not null,
			data varchar(300) not null,
			practiseID int auto_increment primary key);


-- *****************************************************************************

	use mysql;
	drop user if exists 'websql'@'%';
	grant all privileges on *.* to websql@'%' identified by '123456';
	flush privileges;
	use websql;
	insert into Admin values('websql','123456');


	use mysql;
	drop user if exists 'test'@'%';
	drop database if exists test;
	create database test;
	grant all privileges on test.* to test@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('test','123','20151603141001','www.1549138990@qq.com','尹鹏博');

	use mysql;
	drop user if exists 'testa'@'%';
	drop database if exists testa;
	create database testa;
	grant all privileges on testa.* to testa@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('testa','123','20151603141002','www.1549138990@qq.com','黄琪');

	use mysql;
	drop user if exists 'testb'@'%';
	drop database if exists testb;
	create database testb;
	grant all privileges on testb.* to testb@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('testb','123','20151603141003','www.1549138990@qq.com','武旭昶');

	use mysql;
	drop user if exists 'testc'@'%';
	drop database if exists testc;
	create database testc;
	grant all privileges on testc.* to testc@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('testc','123','20151603141004','www.1549138990@qq.com','王超杰');

	use mysql;
	drop user if exists 'testd'@'%';
	drop database if exists testd;
	create database testd;
	grant all privileges on testd.* to testd@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('testd','123','20151603141005','www.1549138990@qq.com','季志成');

	use mysql;
	drop user if exists 'teste'@'%';
	drop database if exists teste;
	create database teste;
	grant all privileges on teste.* to teste@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('teste','123','20151603141006','www.1549138990@qq.com','孙士标');

	use mysql;
	drop user if exists 'testf'@'%';
	drop database if exists testf;
	create database testf;
	grant all privileges on testf.* to testf@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('testf','123','20151603141007','www.1549138990@qq.com','常天昊');

	use mysql;
	drop user if exists 'testg'@'%';
	drop database if exists testg;
	create database testg;
	grant all privileges on testg.* to testg@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('testg','123','20151603141008','www.1549138990@qq.com','王高飞');

	use mysql;
	drop user if exists 'testh'@'%';
	drop database if exists testh;
	create database testh;
	grant all privileges on testh.* to testh@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('testh','123','20151603141009','www.1549138990@qq.com','芦耀文');

	use mysql;
	drop user if exists 'testi'@'%';
	drop database if exists testi;
	create database testi;
	grant all privileges on testi.* to testi@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('testi','123','20151603141010','www.1549138990@qq.com','葛正功');

	use mysql;
	drop user if exists 'test1'@'%';
	drop database if exists test1;
	create database test1;
	grant all privileges on test1.* to test1@'%' identified by '123';
	flush privileges;
	use websql;
	insert into User values('test1','123','20151603141011','www.1549138990@qq.com','石子豪');





-- 修改点:

-- 随机发题存在重复发题现象
-- 为所有执行按钮绑定 快捷键 ;
-- 注册时,需验证用户名和学号是否有重复
-- 点击下一题切换时,将右边执行结果框内容清空
-- 考生提交试卷有问题,答案残缺
-- 批改完成,跳转到了无学生界面
-- 未批改试卷应 存 成绩 -1分作为标记
-- 随机发题是假随机
-- 改卷时答案被改变 
-- 输入框禁止修改
-- 做题时,获得题后立即按照题长初始化相关数组
-- 批改练习后,暂不能提交