建立表
建立年级表
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;
添加数据
年级表添加数据
INSERT into class(id,class,name) VALUES
(1,1,'大一'),
(2,2,'大二'),
(3,3,'大三'),
(4,4,'大四');
成绩表添加数据
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);
学生表添加数据
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);
完成需求
INSERT into class(name) VALUES(‘就业期’);UPDATE class set class=5 where id=3;SELECT number from grade where score=100;SELECT student_name FROM students where birthdate>19890101 and birthdate<19900101;SELECT * FROM students WHERE student_name=‘金蝶’;SELECT number,score FROM grade where subject_id=8 and score<60;SELECT * FROM subjects where period>50;SELECT * FROM grade where number=‘s1101001’;SELECT * FROM students where class_id=2 and sex=‘女’;SELECT period FROM subjects where subject_name=‘基于.NET平台的软件系统分层开发’;SELECT subject_name, period FROM subjects where subject_name in(‘设计MYSchool数据库’,‘面向对象程序设计’);SELECT * FROM students where address=‘山东’;SELECT * FROM students where student_name like ‘凌%’;SELECT * FROM students where class_id=1 ORDER BY birthdate;SELECT * FROM grade where subject_id=3 ORDER BY id desc;SELECT MAX(period) FROM subjects WHERE class_id=2;SELECT* FROM students WHERE address=‘北京’;SELECT* FROM subjects WHERE period<50;SELECT* FROM subjects WHERE class_id=2;SELECT AVG(score) FROM grade WHERE subject_id=8;SELECT max(period),min(period) from subjects where class_id=3;SELECT subject_id,count(*) from grade GROUP BY subject_id;SELECT class_id,avg(period) from subjects GROUP BY class_id;SELECT class_id,sex,count(*) from students GROUP BY class_id,sex;
