MySql database exercise 2
Title Description:
1, Create table
Student list
Chart of accounts
Transcript
Data modification and deletion
1. Update the address of Xi'an to Xi'an Keji 2nd road
2. Change the email of S1001 to an empty string
3. Update the name of the second course to java foundation, the class hour is 60, and the class is grade two
4. Increase the score of S1001 with course No. 2 by 5 points
5. Update the grade of S1004 with course No. 3 to 60 and the examination time to October 10, 2015
6. The absence of S1004 course No. 2 is verified
7. Delete the computer network course
Data query:
1. Query the information of all first grade students.
2. Check the names and telephone numbers of all second grade students.
3. Query the information of all first grade female students.
4. Query the subject information with more than 60 class hours.
5. Query the subject name of grade 2
6. Check the names and addresses of male students in grade 2.
7. Query the name and grade information of students without e-mail.
8. Query the name and grade information of male students whose birth date is after 1992.
9. Score information of the examination of "basic principles of computer" on July 3, 2015
10. Query the first grade student information according to the date of birth.
11. Query the examination information of the subject with No. 1 according to the order from high to low.
12. Query the score information of the top 2 students who took the "MySQL in-depth" test on July 1, 2015.
13. Query the subject name and class hour with the most class hours.
14. Query the grade and name of the youngest student.
15. Query the subject in which the lowest score appears
16. Query all examination information of students with student number "s1001" and display it in chronological order.
17. Query the information of students over the age of 25.
18. Query the information of students whose birthday is in January
19. Check the name and grade of the students whose birthday is today.
20. Freshmen are assigned an Email address when they enter the school. The rules are as follows: S1 + current date +@bd.com
21. Inquire the name, telephone number and address of students whose address is "Yanta District"
22. The query name contains the words "computer", subject name, class hour and grade, and is displayed from low to high by grade.
23. The inquiry telephone contains the student's name, address and telephone number beginning with "130".
24. Inquire the student number, name and address of the surname "Zhao".
25. Count the total number of girls in Grade 1.
26. Inquire about Li Si's total score
27. Total scores of students with student number s1003 in all courses
28. Average score of students with student number s1003.
29. Query the highest score, lowest score and average score of the first grade subject "Mysql".
30. Query the total class hours of each grade and arrange them in ascending order.
31. Query the average score of each participant. (Group by student number)
32. Query the average score of each course and arrange it in descending order. (group by course)
33. Query the total scores of all exams taken by each student and arrange them in descending order. (group by student number)
34. Query the average age of the first grade.
35. Query the number of students in Xi'an area of each grade.
36. Query the records of students who have passed the average score among the students participating in the examination, and arrange them in descending order according to the average score
37. Query the student number, name and failure times of students who failed in at least one examination.
38. Check the student's name, grade name and contact number.
39. Query the subject name, grade name and class hours with grade No. 1.
40. Query the name, score and test date of students participating in the test with subject No. 1.
41. Query the subject name, score and test date of the student with student number s1001.
42. Query the reference information of all subjects (some subjects may not have been tested)
43. Query the information of subjects that have not been tested.
Code result:
create database lianxi2;
use lianxi2;
-- Create table
-- Student list
create table StudentForm(
studentNo varchar(20) primary key,
studentName varchar(20),
loginPassword varchar(50),
sex varchar(5),
phone varchar(200),
address varchar(20) default 'dormitory',
born datetime,
email varchar(200),
gradeId int(5)
);
show create table StudentForm;
/*
CREATE TABLE `studentform` (
`studentNo` varchar(20) NOT NULL,
`studentName` varchar(20) DEFAULT NULL,
`loginPassword` varchar(50) DEFAULT NULL,
`sex` varchar(5) DEFAULT NULL,
`phone` varchar(200) DEFAULT NULL,
`address` varchar(20) DEFAULT 'Dormitory ',
`born` datetime DEFAULT NULL,
`email` varchar(200) DEFAULT NULL,
`gradeId` int DEFAULT NULL,
PRIMARY KEY (`studentNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/
insert into StudentForm values
('s1001','Zhang San','zhagnsan','male','13022225555',default,'1994-01-01',null,1),
('s1002','Li Si','lisi','male','13266669999',default,'1991-07-07',null,1),
('s1003','Zhang Li','123456','female','13099999999',default,'1992-05-06',null,1),
('s1004','Wang Lei','123456','male','15066668888','Xi'an','1991-06-07',null,1),
('s1005','Zhang Dan','zhangdan','female','15036998888',default,'1992-06-07',null,1),
('s1006','Lik','123456','male','15022226669','Yanta District, Xi'an City','1993-12-01','liliang@126.com',1),
('s1007','Li Dan','123456','female','15036699965',default,'1992-11-11','201612011419478126com',1),
('s1008','Liang Wang','123456','male','15022223333','Xi'an City','1993-12-02',null,2),
('s1009','Zhao Long','123456','male','13022229999','Xi'an City','1992-06-07',null,2),
('s1010','Xu Dan','123456','female','15899996666',default,'1993-05-06',null,2);
-- Chart of accounts
create table SubjectForm (
subjectId int(5) primary key auto_increment,
subjectName varchar(200) not null,
classHour int(10) not null,
gradeId int(5)
);
show create table SubjectForm;
/*
CREATE TABLE `subjectform` (
`subjectId` int NOT NULL AUTO_INCREMENT,
`subjectName` varchar(200) NOT NULL,
`classHour` int NOT NULL,
`gradeId` int DEFAULT NULL,
PRIMARY KEY (`subjectId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/
insert into SubjectForm values
(null,'MySQL thorough',65,1),
(null,'C Language process oriented',45,1),
(null,'Basic principles of computer',70,1),
(null,'Introduction to Mao Deng',61,1),
(null,'English',55,1),
(null,'jap',40,2),
(null,'data structure',60,2),
(null,'oracle',65,2),
(null,'computer network',50,2);
-- Transcript
create table AchievementForm(
id int(10) primary key auto_increment,
studentNo varchar(20),
subjectId int(5),
studentScore varchar(10),
examDate datetime
);
alter table AchievementForm add constraint fk_StudentForm_AchievementForm foreign key (studentNo) references StudentForm(studentNo);
alter table AchievementForm add constraint fk_SubjectForm_AchievementForm foreign key (subjectId) references SubjectForm(subjectId);
show create table AchievementForm;
/*
CREATE TABLE `achievementform` (
`id` int NOT NULL AUTO_INCREMENT,
`studentNo` varchar(20) DEFAULT NULL,
`subjectId` int DEFAULT NULL,
`studentScore` int DEFAULT NULL,
`examDate` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_StudentForm_AchievementForm` (`studentNo`),
KEY `fk_SubjectForm_AchievementForm` (`subjectId`),
CONSTRAINT `fk_StudentForm_AchievementForm` FOREIGN KEY (`studentNo`) REFERENCES `studentform` (`studentNo`),
CONSTRAINT `fk_SubjectForm_AchievementForm` FOREIGN KEY (`subjectId`) REFERENCES `subjectform` (`subjectId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/
insert into AchievementForm values
(null,'s1001',1,'80','2015-07-01'),
(null,'s1002',1,'40','2015-07-01'),
(null,'s1001',2,'10','2015-07-01'),
(null,'s1002',2,'20','2015-07-01'),
(null,'s1003',1,'60','2015-07-01'),
(null,'s1001',3,'82','2015-07-03'),
(null,'s1001',4,'90','2015-07-03'),
(null,'s1001',5,'75','2015-07-01'),
(null,'s1002',3,'65','2015-07-03'),
(null,'s1002',4,'35','2015-07-03'),
(null,'s1002',5,'87','2015-07-01'),
(null,'s1003',2,'65','2015-07-01'),
(null,'s1003',3,'45','2015-07-03'),
(null,'s1003',4,'92','2015-07-03'),
(null,'s1003',5,'55','2015-07-01'),
(null,'s1004',1,'65','2015-07-01'),
(null,'s1004',2,'85','2015-07-01'),
(null,'s1004',3,'45','2015-07-03');
-- Data modification and deletion
update StudentForm set address = 'Xi'an Keji 2nd Road' where address = 'Xi'an City'; -- 1.Update the address of Xi'an to Xi'an Keji second road
update StudentForm set email = ' ' where studentNo = 's1001';-- 2.take S1001 of email Modify empty string
update SubjectForm set subjectName = 'java Basics',classHour = 60,gradeId = 2 where subjectId = 2;-- 3.Update the name of the second course to java Basic, class hour is 60, and the class is grade two
update AchievementForm set studentScore = studentScore + 5 where studentNo = 's1001' and subjectId = 2;-- 4.take S1001 ，The grade of course No. 2 will be increased by 5 points
update AchievementForm set studentScore = 60,examDate = '2015-10-10' where studentNo = 's1004' and subjectId = 3;-- 5.take S1004 ,The grade of course No. 3 is updated to 60 and the examination time is updated to 2015-10-10
update AchievemnetForm set studentScore val where studentNo = 's1004' and subjectId = 2;-- 6.Verified S1004 Absence of course number 2
delete from SubjectForm where subjectName = 'computer network';-- 7.Delete the computer network course
-- Data query
select * from StudentForm where gradeId = 1;-- 1.Query the information of all first grade students
select studentName,phone from StudentForm where gradeId = 2;-- 2.Check the names and telephone numbers of all second graders.
select * from StudentForm where gradeId = 1 and sex = 'female';-- 3.Query the information of all first grade female students.
select * from SubjectForm where classHour > 60;-- 4.Query the account information with more than 60 class hours.
select * from SubjectForm where gradeId = 2;-- 5.Query the subject name of grade 2
select studentName,address from StudentForm where gradeId = 2 and sex = 'male';-- 6.Check the names and addresses of male students in grade two.
select studentName,gradeId from StudentForm where email is null;-- 7.Query the name and grade information of students without e-mail.
select studentName,gradeId from StudentForm where born >= 1992-01-01;-- 8.Query the name and grade information of male students whose birth date is after 1992.'
select studentNo,studentScore from AchievementForm where examDate = '2015-07-03' and subjectId = 3;-- 9.Score information of "basic principles of computer" subject examination dated July 3, 2015
select * from studentForm where gradeId = 1 order by born;-- 10,Query the first grade student information according to the date of birth.
select * from AchievementForm where subjectId = 1 order by studentScore desc;-- 11.Query the examination information of the subject with No. 1 in the order of high score to low score.
select * from AchievementForm where examDate = '2015-07-01' order by studentScore desc limit 2;-- 12.Query July 1, 2015“ MySQL Score information of the top 2 students in the "in-depth" examination.
select subjectName,classHour from SubjectForm order by classHour desc limit 1;-- 13.Query the subject name and class hour with the most class hours.
select gradeId,studentName from StudentForm order by born desc limit 1;-- 14.Query the grade and name of the youngest student.
select subjectName from SubjectForm where subjectId = (select subjectId from AchievementForm order by studentScore limit 1);-- 15.Query the subject in which the lowest score of the exam appears
select * from AchievementForm where studentNo = 's1001' order by examDate;-- 16.The inquiry student number is“ s1001"All exam information that students have participated in will be displayed in chronological order.
select * from StudentForm where (year(curdate()) - substring(born from 1 for 4)) > 25;-- 17.Query the information of students over the age of 25.
select * from StudentForm where (substring(born from 6 for 6)) = 1;-- 18.Query the students' birthday information in January
select studentName,gradeId from StudentForm where (substring(born from 6 for 7)) = month(curtime()) and (substring(born from 9 for 10)) = day(curtime());-- 19.Check the name and grade of the students whose birthday is today.
-- 20.New students are enrolled and assigned one Email Address, the rules are as follows: S1+current date +@bd.com(To be supplemented)
select studentName,phone,address from StudentForm where address = 'Yanta District';-- 21.Query the name, telephone number and address of students whose address is "Yanta District"
select * from SubjectForm where subjectName like 'computer%';-- 22.The query name contains the words "computer", subject name, class hour and grade, and is displayed from low to high by grade.
select studentName,address,phone from StudentForm where phone like '130%';-- 23.The inquiry telephone contains the student's name, address and telephone number starting with "130".
select studentNo,studentName,address from StudentForm where studentName like 'Zhao%';-- 24.Inquire the student number, name and address of the surname "Zhao".
select count(sex) from StudentForm where sex = 'female';-- 25.Count the total number of girls in grade one.
select sum(studentScore) from AchievementForm where studentNo = (select studentNo from StudentForm where studentName = 'Li Si');-- 26.Query Li Si's total score
select sum(studentScore) from AchievementForm where studentNo = 's1003';-- 27.Student number is s1003 Students' total scores in all classes
select avg(studentScore) from AchievementForm where studentNo = 's1003';-- 28.Student number is s1003 The average score of students in the exam.
select max(studentScore),min(studentScore),avg(studentScore) from AchievementForm where subjectId = (select subjectId from SubjectForm where subjectName = 'MySQL thorough');-- 29.Query first grade subjects“ Mysql"The highest score, lowest score and average score of.
Unfinished - to be continued