数据库期末课程设计:数据库设计针对学籍管理系统,使用python语言和MySQL实现的,针对老师、学生、管理员用户,功能齐全,使用了hash函数单向加密等密码学技术。
APACHE-2.0 License
(1)
sqlsql
(2)
""""
:
1. :
2. :
3.:
** 1 **
1 varchar 10
2 varchar 10
3 varchar 10
**2 **
1 varchar 10
2 char 10
**3 **
1 varchar 20
2 varchar 20
3 Varchar 2
4 Int 4
5 Varchar 10
6 Datetime
**4 **
1 varchar 10
2 varchar 10
3 varchar 2
4 varchar 10
5 varchar 10
6 Varchar 20
**5 **
1 varchar 10
2 char 10
3 Int 2
4 Int 2
5 varchar 10
6 Varchar 10
7 Varchar 10
**6 **
1 varchar 10
2 varchar 10
3 int 3
**7 **
1 varchar 10
2 varchar 10
8
1 varchar 20
2 varchar 20
9
1 varchar 20
2 varchar 100
3 Varchar 100
pythonhash)SQL
select count(sno) from student;
()
()
()
()
)
****)
E-R
E-R
()
()
()
()
)
****)
CREATE TABLE `department` (
`dmpno` varchar(5) NOT NULL,
`dname` varchar(10) NOT NULL,
`dmphead` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `major` (
`mno` varchar(5) NOT NULL,
`mname` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `student` (
`sno` varchar(20) NOT NULL,
`sname` varchar(10) NOT NULL,
`ssex` varchar(2) NOT NULL,
`sage` int(4) NOT NULL,
`sclass` varchar(10) NOT NULL,
`intime` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `account` (
`username` varchar(20) NOT NULL,
`passward` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `course` (
`cno` varchar(10) NOT NULL,
`cname` varchar(10) NOT NULL,
`tno` varchar(10) NOT NULL,
`c_period` varchar(10) NOT NULL,
`clocation` varchar(10) NOT NULL,
`credit` int(2) NOT NULL,
`ctime` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
**
CREATE TABLE `teacher` (
`tno` varchar(20) NOT NULL,
`tname` varchar(10) NOT NULL,
`tsex` varchar(2) NOT NULL,
`tphone` varchar(20) NOT NULL,
`dmpno` varchar(10) NOT NULL,
`profess` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `teacher_course` (
`tno` varchar(10) NOT NULL,
`cno` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `stu_course` (
`sno` varchar(20) NOT NULL,
`cno` varchar(10) NOT NULL,
`grade` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `stu_reward_punishment` (
`sno` varchar(20) NOT NULL,
`reward` varchar(100) DEFAULT NULL,
`punishment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
""
1. insertintostudentvalues(`202032120777`,``,``,20,`1701`,`2017-09-01`);
2. insertintostudentvalues(`201932120778`,``,``,21,`1601`,`2020-09-01`);
3. insertintostudentvalues(`202132120779`,``,``,20,`1801`,`2018-09-01`);
4. insertintostudentvalues(`202032120768`,``,``,21,`1701`,`2017-09-01`);
5. insertintostudentvalues(`201932120720`,``,``,19,`1901`,`2019-09-01`);
6. insertintostudentvalues(`201932120711`,``,``,22,`1601`,`2020-09-01`);
7. insertintostudentvalues(`202032120762`,``,``,21,`1701`,`2017-09-01`);
8. insertintostudentvalues(`202132120719`,``,``,19,`1801`,`2018-09-01`);
9. insertintostudentvalues(`201932120710`,``,``,22,`1601`,`2020-09-01`);
10.
11. insertintocoursevalues(`c001`,``,`122118`,`10-12`,`3-311`,3,80);
12. insertintocoursevalues(`c002`,``,`122124`,`10-12`,`22-402`,6,60);
13. insertintocoursevalues(`c003`,``,`122125`,`1-3`,`4-301`,2,60);
14. insertintocoursevalues(`c004`,``,`122110`,`1-4`,`20-201`,6,80);
15. insertintocoursevalues(`c005`,``,`122110`,`1-4`,`3-310`,6,80);
16. insertintocoursevalues(`c006`,``,`122122`,`8-9`,`5-301`,4,80);
17.
18. insertintoteachervalues(`122118`,``,``,`17398447611`,`d001`,``);
19. insertintoteachervalues(`122124`,``,``,`17398440222`,`d001`,``);
20. insertintoteachervalues(`122125`,``,``,`17398449118`,`d002`,``);
21. insertintoteachervalues(`122110`,``,``,`17398443912`,`d003`,``);
22. insertintoteachervalues(`122122`,``,``,`17398442121`,`d002`,``);
23.
24. insertintodepartmentvalues(`d001`,``,``);
25. insertintodepartmentvalues(`d002`,``,``);
26. insertintodepartmentvalues(`d003`,``,``);
27.
28. insertintomajorvalues(`m001`,``);
29. insertintomajorvalues(`m002`,``);
30. insertintomajorvalues(`m003`,``);
31.
32. insertintostu_coursevalues(`202032120777`,`c001`,null);
33. insertintostu_coursevalues(`202032120777`,`c002`,null);
34. insertintostu_coursevalues(`201932120778`,`c004`,null);
35. insertintostu_coursevalues(`201932120778`,`c005`,null);
36. insertintostu_coursevalues(`202132120779`,`c003`,null);
37. insertintostu_coursevalues(`202032120768`,`c004`,70);
38. insertintostu_coursevalues(`202032120762`,`c002`,80);
39. insertintostu_coursevalues(`202132120719`,`c003`,90);
40. insertintostu_coursevalues(`202132120719`,`c006`,92);
41.
42. insertintoteacher_coursevalues(`122118`,`c001`);
43. insertintoteacher_coursevalues(`122124`,`c002`);
44. insertintoteacher_coursevalues(`122125`,`c003`);
45. insertintoteacher_coursevalues(`122110`,`c004`);
46. insertintoteacher_coursevalues(`122110`,`c005`);
47. insertintoteacher_coursevalues(`122122`,`c006`);
48.
49. insertintoclass_stu_numvalues(`1701`,2);
50. insertintoclass_stu_numvalues(`1601`,3);
51. insertintoclass_stu_numvalues(`1701`,1);
52. insertintoclass_stu_numvalues(`1801`,2);
53. insertintoclass_stu_numvalues(`1901`,1);
54.
55. insertintostu_reward_punishment(sno,reward)values(`202032120777`,``);
56. insertintostu_reward_punishment(sno,punishment)values(`202032120777`,``);
57. insertintostu_reward_punishment(sno,punishment)values(`201932120711`,``);
58.
59. insertintoaccountvalues(`stu`,`1224`);
60. insertintoaccountvalues(`stu1`,`1224`);
61. insertintoaccountvalues(`teach`,`1224`);
62. insertintoaccountvalues(`admin`,`1224`);
63. insertintoaccountvalues(`student`,`1224`);
64. \"\"
**5.1 **
5.2
a:
B:
F/f
![sadfa](https://s2.loli.net/2022/01/03/OiwcqYCWIfAKbDy.png)
202006010300