day27 SQL enhancement and Practice
Course objective: to practice the design of common SQL statements and table structures.
Course overview:
- SQL hardening
- Table structure design (blog system)
1. SQL enhancement
-
Create database & table structure and input data according to the above figure (you can create data by yourself)
create database day27db default charset utf8 collate utf8_general_ci; use day27db; drop database day27db; drop database IF EXISTS day27db;
Use the import database command:
-
Import
mysql -u root -p day27db < /Users/wupeiqi/day27db.sql
-
export
# Structure + data mysqldump -u root -p day27db > /Users/wupeiqi/day27db2.sql # structure mysqldump -u root -p -d day27db > /Users/wupeiqi/day27db3.sql
create table class( cid int not null auto_increment primary key, caption varchar(16) not null )default charset=utf8; INSERT INTO class VALUES ('1', 'Class two of three years'), ('2', 'Three years and three classes'), ('3', 'Two classes a year'), ('4', 'Class 9 of the second year'); create table student( sid int not null auto_increment primary key, gender char(1) not null, class_id int not null, sname varchar(16) not null, constraint fk_student_class foreign key (class_id) references class(cid) )default charset=utf8; INSERT INTO student VALUES ('1', 'male', '1', 'understand'), ('2', 'female', '1', 'Steel egg'), ('3', 'male', '1', 'Zhang San'), ('4', 'male', '1', 'Zhang Yi'), ('5', 'female', '1', 'Zhang Er'), ('6', 'male', '1', 'Zhang Si'), ('7', 'female', '2', 'Hammer'), ('8', 'male', '2', 'Li San'), ('9', 'male', '2', 'Li Yi'), ('10', 'female', '2', 'Li Er'), ('11', 'male', '2', 'Li Si'), ('12', 'female', '3', 'Like flowers'), ('13', 'male', '3', 'Liu San'), ('14', 'male', '3', 'Liu Yi'), ('15', 'female', '3', 'Liu er'), ('16', 'male', '3', 'Liu Si'); create table teacher( tid int not null auto_increment primary key, tname varchar(16) not null )default charset=utf8; INSERT INTO `teacher` VALUES ('1', 'Mr. Zhang Lei'), ('2', 'Miss Li Ping'), ('3', 'Miss Liu Haiyan'), ('4', 'Miss Zhu Yunhai'), ('5', 'Miss Li Jie'); create table course( cid int not null auto_increment primary key, cname varchar(16) not null, teacher_id int not null, constraint fk_course_teacher foreign key (teacher_id) references teacher(tid) )default charset=utf8; INSERT INTO `course` VALUES ('1', 'biology', '1'), ('2', 'Physics', '2'), ('3', 'Sports', '3'), ('4', 'Fine Arts', '2'); CREATE TABLE `score` ( `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `student_id` int NOT NULL, `course_id` int NOT NULL, `num` int NOT NULL, CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) DEFAULT CHARSET=utf8; INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
-
-
Create a user luffy and give all permissions to this database.
create user 'luffy'@'%' identified by 'root123'; grant all privileges on day27db.* TO 'luffy'@'%'; flush privileges;
-
Query the number of teachers surnamed "Li".
select * from teacher where tname like "Lee%";
-
Check the list of students surnamed "Zhang".
select * from student where sname like "Zhang%";
-
Query the number of boys and girls.
select gender,count(1) from student group by gender;
-
Query the list of students with the same name and surname, and count the number of students with the same name.
select sname,count(1) from student group by sname; select sname,count(1) from student group by sname having count(1) > 1;
-
Query all students in "class 2, grade 3".
select * from student left join class on student.class_id = class.cid where class.caption="Class two of three years";
-
Query the class name and class number of each class.
select class_id,count(1) from student group by class_id; select class.caption,count(1) from student left join class on student.class_id = class.cid group by class.caption;
-
Query the student number, name, grade and course name of students whose score is less than 60.
select * from score where num <60; select student.sid, student.sname, score.num, course.cname from score left join student on score.student_id=student.sid left join course on score.course_id =course.cid where num <60;
-
Query all student ID S, student names and grades of "biology course".
select * from score left join course on score.course_id =course.cid where course.cname="biology"; select student.sid, student.sname, score.num from score left join course on score.course_id =course.cid left join student on score.student_id=student.sid where course.cname="biology";
-
Query the ID, name and grade of all students who have taken the "biology course" and whose score is less than 60.
select student.sid,student.sname,score.num from score left join course on score.course_id =course.cid left join student on score.student_id=student.sid where course.cname="biology" and score.num < 60;
-
Query the student number, name, number of courses and total score of all students.
select student_id,count(1),sum(num) from score group by student_id; select student_id,student.sname,count(1),sum(num) from score left join student on score.student_id=student.sid group by student_id;
-
Query the number of elective students in each subject.
select course_id,count(1) from score group by course_id; select course_id,course.cname,count(1) from score left join course on score.course_id =course.cid group by course_id;
-
Query the total score, maximum score and minimum score of each subject, and display: course ID, course name, total score, maximum score and minimum score.
select course_id,course.cname,sum(num), max(num), min(num) from score left join course on score.course_id =course.cid group by course_id;
-
Query the average score of each subject and display: course ID, course name and average score.
select course_id,course.cname,avg(num) from score left join course on score.course_id =course.cid group by course_id;
-
Query the average score of each subject and display: course ID, course name and average score (sorted by average score from large to small).
select course_id,course.cname,avg(num) from score left join course on score.course_id =course.cid group by course_id order by avg(num) desc; select course_id,course.cname,avg(num) as A from score left join course on score.course_id =course.cid group by course_id order by A desc;
-
Query the average score and pass rate of each subject, and display: course ID, course name, average score and pass rate.
10/20 = pass rate select course_id,count(1) from score group by course_id;
select sid, course_id, num, case when score.num >= 60 then 1 else 0 end "Pass or not" from score;
select sid,course_id,num,case when score.num > 60 then 1 else 0 end "Pass or not" from score;
select course_id, course.cname, avg(num), count(1) as total, sum(case when score.num > 60 then 1 else 0 end) from score left join course on score.course_id =course.cid group by course_id;
select course_id, course.cname, avg(num), sum(case when score.num > 60 then 1 else 0 end)/count(1) *100 as percent from score left join course on score.course_id =course.cid group by course_id;
-
Query the student number and average score of all students whose average score is greater than 60;
select student_id,avg(num) from score group by student_id having avg(num) > 60;
-
Query the student number, average score and name of all students with an average score greater than 85.
select student_id,avg(num) from score group by student_id having avg(num) > 85; select student_id,avg(num),student.sname from score left join student on score.student_id=student.sid group by student_id having avg(num) > 85;
-
Query the student number, name, total score and average score of each student in "class two of three years".
SELECT * FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN class ON class.cid = student.class_id;
SELECT * FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN class ON class.cid = student.class_id WHERE class.caption = "Class two of three years";
SELECT student_id, sname, sum( num ), avg( num ) FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN class ON class.cid = student.class_id WHERE class.caption = "Class two of three years" GROUP BY student_id
-
Query the class name, total score, average score and pass rate of each class (sorted by average score from large to small).
SELECT class.cid, class.caption, sum( num ), avg( num ) as av, sum( CASE WHEN score.num > 60 THEN 1 ELSE 0 END ) / count( 1 ) * 100 as JG FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN class ON class.cid = student.class_id GROUP BY class.cid ORDER BY av desc
-
Query the student number and name of the students who have studied "Bodo" teacher's class.
SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "Bodo"
-
Query the student number and name of the students who have not learned the "Bodo" teacher's class.
SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname != "Bodo"
select * from student where sid not in( SELECT student.sid FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "Bodo" )
-
Query the names and grades of the students with the highest scores among the students taking the courses taught by the "sky" teacher (regardless of juxtaposition).
SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "blue sky" ORDER BY score.num DESC LIMIT 1
-
Query the names and grades of the students with the highest scores among the students taking the courses taught by the "sky" teacher (consider juxtaposition).
SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "blue sky" AND score.num = ( SELECT max( num ) FROM score LEFT JOIN course ON score.course_id = course.cid LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "blue sky" )
-
Query the student numbers and names of all students who have only taken one course.
SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid GROUP BY student_id HAVING count( 1 ) =1
-
Query the student number, student name and number of elective courses of at least two courses.
SELECT student.sid, student.sname , count(1) FROM score LEFT JOIN student ON score.student_id = student.sid GROUP BY student_id HAVING count( 1 ) >= 2
-
Query the student number, student name and number of elective courses of students who fail two or more courses.
SELECT student.sid, student.sname , count(1) FROM score LEFT JOIN student ON score.student_id = student.sid where num < 60 GROUP BY student_id HAVING count( 1 ) >= 2
-
Query the student number and name of students who have taken all courses.
SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid GROUP BY student_id HAVING count( 1 ) = ( SELECT count( 1 ) FROM course )
-
Query the student number and name of students who do not take all courses.
SELECT student.sid, student.sname FROM score LEFT JOIN student ON score.student_id = student.sid GROUP BY student_id HAVING count( 1 ) != ( SELECT count( 1 ) FROM course )
-
Query the course number and course name of the course that all students have taken.
SELECT course.cid, course.cname FROM score LEFT JOIN course ON score.course_id = course.cid GROUP BY course_id HAVING count( 1 ) = ( SELECT count( 1 ) FROM student )
-
Query the student numbers and names of all students taking "biology" and "physics" courses.
SELECT student.sid, student.sname FROM score LEFT JOIN course ON score.course_id = course.cid LEFT JOIN student ON score.student_id = student.sid WHERE course.cname in ("biology","Physics") GROUP BY student_id having count(1) = 2;
-
Query the student number and name of other students whose at least one course is the same as the course selected by the student with student number "1".
SELECT student.sid, student.sname FROM score LEFT JOIN course ON score.course_id = course.cid LEFT JOIN student ON score.student_id = student.sid WHERE score.course_id in ( select course_id from score where student_id=1) and score.student_id != 1 GROUP BY student_id HAVING count(1) > 1
-
Query the student numbers and names of other students who are exactly the same as the elective courses of students with student number "2".
SELECT student.sid, student.sname FROM score LEFT JOIN course ON score.course_id = course.cid LEFT JOIN student ON score.student_id = student.sid WHERE score.course_id in ( select course_id from score where student_id=2) and score.student_id in ( select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2 ) GROUP BY student_id HAVING count(1) = ( select count(1) from score where student_id=2 ) # If id=2, the student has the same number of courses as others. select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2 select student_id from score where student_id!=2 group by student_id having count(1) = select count(1) from score where student_id=2
-
Query the student numbers of all students whose grades in "biology" course are higher than those in "physics" course;
SELECT * FROM score LEFT JOIN course ON score.course_id = course.cid where cname in ("biology","Physics");
SELECT *, case cname WHEN "biology" then num else -1 end sw, case cname WHEN "Physics" then num else -1 end wl FROM score LEFT JOIN course ON score.course_id = course.cid where cname in ("biology","Physics");
SELECT student_id, max(case cname WHEN "biology" then num else -1 end) as sw, max(case cname WHEN "Physics" then num else -1 end) as wl FROM score LEFT JOIN course ON score.course_id = course.cid where cname in ("biology","Physics") GROUP BY student_id;
SELECT student_id, max( CASE cname WHEN "biology" THEN num ELSE 0 END ) AS sw, max( CASE cname WHEN "Physics" THEN num ELSE 0 END ) AS wl FROM score LEFT JOIN course ON score.course_id = course.cid WHERE cname IN ( "biology", "Physics" ) GROUP BY student_id HAVING sw > wl;
-
Query the top three with the best scores in each course (regardless of the juxtaposition of scores).
SELECT cid, cname, ( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 0) as "1st place", ( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 1) as "2nd place", ( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 2) as "3rd place" FROM course;
-
Query the top three with the best scores in each course (considering the juxtaposition of scores).
SELECT cid, cname, ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 0) as "Highest score", ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 1) as "Second high score", ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as "Third high score" FROM course;
select * from score left join ( SELECT cid, cname, ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 0) as "Highest score", ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 1) as "Second high score", ( select num from score where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as third FROM course ) as C on score.course_id = C.cid WHERE score.num >= C.third
-
Create a table sc, and then insert all the data in the score table into the sc table.
CREATE TABLE `sc` ( `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `student_id` int NOT NULL, `course_id` int NOT NULL, `num` int NOT NULL, CONSTRAINT `fk_sc_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_sc_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) DEFAULT CHARSET=utf8;
INSERT INTO sc SELECT * from score;
-
Insert some records into the sc table, which are required to meet the following conditions:
- Student ID: the student number of the student who has not attended the course with the course ID of "2";
- Course ID: 2
- Score: 80
-- Yes select student_id from score where course_id =2; -- Never SELECT sid FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 ) -- Construction data SELECT sid, 2, 80 FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 );
INSERT INTO sc ( student_id, course_id, num ) SELECT sid, 2, 80 FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )
-
Insert some records into the sc table, which are required to meet the following conditions:
- Student ID: the student number of the student who has not attended the course with course ID "2".
- Course ID: 2.
- The score is: the highest score of course ID 3.
SELECT sid, 2, (select max(num) from score where course_id=3) as num FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 );
INSERT INTO sc ( student_id, course_id, num ) SELECT sid, 2, (select max(num) from score where course_id=3) as num FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )
2. Design table structure
The corresponding table structure is designed according to the following business requirements, and the following functions need to be covered internally.
- register
- Sign in
- Post a blog
- View the blog list and display the blog title, creation time, number of reads, number of comments, number of likes, etc.
- Blog details, showing blog details, comments, etc.
- Comment
- Like or step on
- Number of readings + 1
Referring to the following pictures, please design the corresponding table structure according to the following functions.
Note: you only need to design the table structure, and you don't need to use python code to realize specific functions (you can better realize it after learning a little more knowledge).
Blog system - table structure design
drop database blog; drop database IF EXISTS blog; create database blog default charset utf8 collate utf8_general_ci; use blog;
create table user( id int not null auto_increment primary key, username varchar(16) not null, nickname varchar(16) not null, mobile char(11) not null, password varchar(64) not null, email varchar(64) not null, ctime datetime not null )default charset=utf8; create table article( id int not null auto_increment primary key, title varchar(255) not null, text text not null, read_count int default 0, comment_count int default 0, up_count int default 0, down_count int default 0, user_id int not null, ctime datetime not null, constraint fk_article_user foreign key (user_id) references user(id) )default charset=utf8; create table comment( id int not null auto_increment primary key, content varchar(255) not null, user_id int not null, article_id int not null, ctime datetime not null, constraint fk_comment_user foreign key (user_id) references user(id), constraint fk_comment_article foreign key (article_id) references article(id) )default charset=utf8; create table up_down( id int not null auto_increment primary key, choice tinyint not null, user_id int not null, article_id int not null, ctime datetime not null, constraint fk_up_down_user foreign key (user_id) references user(id), constraint fk_up_down_article foreign key (article_id) references article(id) )default charset=utf8;