数据库.简单学生管理系统

建立表

建立年级表

create table class( 
id int not null primary key auto_increment,
class tinyint ,
name varchar(4) not null)
engine=innodb default charset=utf8; 

---

建立成绩表

create table grade( 
id int not null primary key auto_increment,
number char(50) ,
subject_id int,
score double,
test_time date)
engine=innodb default charset=utf8;

---

建立学生表

create table students( 
student_id int not null auto_increment,
student_name varchar(4) not null,
login_password char(32),
sex enum('男','女'),
class_id TINYINT ,
phone char(11),
address char(50),
birthdate date,
email char(50),
primary key(student_id)
)
engine=innodb default charset=utf8;

---

建立科目表

create table subjects( 
subject_id int not null auto_increment,
subject_name char(50) not null,
period tinyint,
class_id int,
primary key(subject_id)
)
engine=innodb default charset=utf8;

---

添加数据

1. 年级表添加数据

INSERT into class(id,class,name) VALUES
(1,1,'大一'),
(2,2,'大二'),
(3,3,'大三'),
(4,4,'大四');

2. 成绩表添加数据

INSERT INTO grade(number,subject_id,score,test_time) VALUES(['1',1,100,20201010),
('2',1,100,20201010),
('3',1,100,20201010),
('4',1,102,20201010),
('5',1,101,20201010),
('8',8,59,20200202),
('s1101001',1,100,20201010);

3. 学生表添加数据

INSERT INTO  students(student_name,login_password,sex,class_id,phone,address,birthdate,email )  VALUES
('张三',md5(123),'男',1,'12345678901','山东',19890202,'123@qq.com'),
('张三',md5(123),'男',1,'12345678901','山东',19890203,'123@qq.com'),
('金蝴蝶',md5(123),'男',1,'12345678901','山东',19890202,'123@qq.com'),
('金蝶',md5(123),'男',1,'12345678901','山东',19890203,'123@qq.com'),
('张三',md5(123),'女',2,'12345678901','北京',19890202,'123@qq.com');
INSERT INTO students(student_name) VALUES('凌xx');

4.科目表添加数据

insert INTO subjects(subject_id,subject_name,period,class_id) VALUES
(1,'数学',40,1),
(2,'科学',60,3),
(3,'基于.NET平台的软件系统分层开发',40,1),
(4,'设计MYSchool数据库',40,1),
(5,'面向对象程序设计',40,1);

完成需求

1. INSERT into class(name) VALUES(‘就业期’);
2. UPDATE class set class=5 where id=3;
3. SELECT number from grade where score=100;
4. SELECT student_name FROM students where birthdate>19890101 and birthdate<19900101;
5. SELECT * FROM students WHERE student_name=‘金蝶’;
6. SELECT number,score FROM grade where subject_id=8 and score<60;
7. SELECT * FROM subjects where period>50;
8. SELECT * FROM grade where number=‘s1101001’;
9. SELECT * FROM students where class_id=2 and sex=‘女’;
10. SELECT period FROM subjects where subject_name=‘基于.NET平台的软件系统分层开发’;
11. SELECT subject_name, period FROM subjects where subject_name in(‘设计MYSchool数据库’,‘面向对象程序设计’);
12. SELECT * FROM students where address=‘山东’;
13. SELECT * FROM students where student_name like ‘凌%’;
14. SELECT * FROM students where class_id=1 ORDER BY birthdate;
15. SELECT * FROM grade where subject_id=3 ORDER BY id desc;
16. SELECT MAX(period) FROM subjects WHERE class_id=2;
17. SELECT* FROM students WHERE address=‘北京’;
18. SELECT* FROM subjects WHERE period<50;
19. SELECT* FROM subjects WHERE class_id=2;
20. SELECT AVG(score) FROM grade WHERE subject_id=8;
21. SELECT max(period),min(period) from subjects where class_id=3;
22. SELECT subject_id,count(*) from grade GROUP BY subject_id;
23. SELECT class_id,avg(period) from subjects GROUP BY class_id;
24. SELECT class_id,sex,count(*) from students GROUP BY class_id,sex;