Mysql connection query for small jobs, mysql connection jobs

Mysql connection query for small jobs, mysql connection jobs

# Data preparation
Drop table if exists class;
Create table class (
Class_no int (2) unsigned zerofill primary key auto_increment comment 'class number ',
Class_name varchar (30) not null comment 'class name'
);
Insert into class values (1, 'excellent class ');
Insert into class values (2, 'ordinary class ');
Insert into class values (3, 'advanced class ');

Drop table if exists student;
Create table student (
Stu_no int (2) unsigned zerofill primary key auto_increment comment 'student number ',
Stu_name varchar (30) not null comment 'student name ',
Stu_sex varchar (3) not null comment 'student Gender ',
Stu_age tinyint (2) unsigned zerofill comment 'student age ',
Grade double (5, 2) zerofill comment 'score ',
Class_no int (2) unsigned zerofill comment 'class No ',
Foreign key (class_no) references class (class_no)
);
Insert into student values (01, 'Li Bai ', 'male', 18, 60, 01 );
Insert into student values (02, 'dual', 'male', 20, 76, 01 );
Insert into student values (03, 'zhang fee', 'male', 32, 80, 02 );
Insert into student values (04, 'Han xin', 'mal', 26, 98, 02 );
Insert into student values (05, 'long', 'male', 27, 56, 02 );
Insert into student values (06, 'Joe ', 'female', 17, 88, 01 );
Insert into student values (07, 'Joe ', 'female', 16, 96, 01 );
Insert into student values (08, 'Joe ', 'female', 16, 90, 01 );
Insert into student values (09, 'guan go', 'mal', 32, 80, 02 );
Insert into student values (10, 'Liu bei ', 'male', 36, 98, null );
Alter table student drop foreign key 'student _ ibfk_1 ';
**************************************** **************************************** **************************************** *********************

1: Find the students in the 'excellent class'
// Subquery
Select * from student where class_no = (select class_no from class where class_name = "");
// Internal connection
Select * from student inner join class on student. class_no = class. class_no and class_name = "";
// Natural connection
Select * from student natural join class where class_name = "";

2: The score of the 'ordinary class' student is higher than 85.
Select * from student where class_no = (select class_no from class where class_name = "normal class") and grade> 85;
Select * from student inner join class on student. class_no = class. class_no and class_name = "normal class" and grade> 85;
Select * from student natural join class where class_name = "normal class" and grade> 85;

3: Write the query results of a dikar set.
Select * from student cross join class;
Select * from student inner join class;

4: query the average score of each class.
// Contains the result that the class number is null.
Select class_no, avg (grade) from student group by class_no;
// Does not include the result of null class number
Select class_no, avg (grade) from student inner join class using (class_no) group by class_no; // The result does not include null for class_no.

5: query the name of each student and the name of the class.
Select stu_name, class_name from student inner join class using (class_no );
Select stu_name, class_name from student inner join class on student. class_no = class. class_no;
Select class_name, stu_name from student natural join class;

6: query the score of the training class
Select min (grade) from student where class_no = (select class_no from class where class_name = "");
Select min (grade) from student inner join class on class. class_no = student. class_no and class_name = "";
Select min (grade) from student natural join class where class_name = "";

7: query the student information with the lowest score in the training class (the worst score is not necessarily a person)
Select * from student where class_no = (select class_no from class where class_name = "") and grade = (select min (grade) from student where class_no = (select class_no from class where class_name = ""));
Select * from student where (class_no, grade) = (select class_no, min (grade) from student natural join class where class_name = "");

8: query the student information with the best scores in the ordinary class.
Select * from student natural join class where class_name = "normal class" order by grade desc limit 1;
(Just change the conditions for Question 7)

9: query the names of the students with the best scores and their class names.
// Query results for multiple records
Select stu_name, class_name from student natural left join class where grade = (select max (grade) from student );

10: The difference between the number of male and female students is queried.
Select (select count (*) from student where stu_sex = "male")-(select count (*) from student where stu_sex = "female") as "differences between male and female ";