MySQL Query small job

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 (+) NOT null comment ' class name '
);
INSERT into class values (1, ' Pui gifted class ');
INSERT into class values (2, ' normal 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 (+) NOT null comment ' learner name ',
Stu_sex varchar (3) NOT null comment ' trainee sex ',
Stu_age tinyint (2) unsigned zerofill comment ' student age ',
class_no Int (2) unsigned zerofill comment ' class number ',
Foreign KEY (CLASS_NO) references class (CLASS_NO)
);
INSERT into student values (01, ' Li Bai ', ' male ', 18, 01);
INSERT into student values (02, ' Du Fu ', ' Male ', 20, 01);
INSERT into student values (03, ' Zhang Fei ', ' Male ', 32, 02);
INSERT into student values (04, ' Han Xin ', ' Male ', 26, 02);
INSERT into student values (05, ' Dragon ', ' Male ', 27, 02);
INSERT into student values (06, ' Big Joe ', ' female ', 17, 01);
INSERT into student values (07, ' Little Joe ', ' female ', 16, 01);
INSERT into student values (08, ' Little Joe ', ' female ', 16, 01);
INSERT into student values (09, ' Guan brother ', ' male ', 32, 02);
INSERT into student values (10, ' Liu Bei ', ' Male ', $, null);
ALTER TABLE student drop foreign key ' student_ibfk_1 ';


1: Check out the oldest student in the student table
SELECT * FROM student where stu_age = (select Max (stu_age) from student);

2: Check out the youngest student in the student table
SELECT * FROM student where stu_age = (select min (stu_age) from student);

3: Find out what the maximum age of class No. 02 is
Select Max (stu_age) from student where class_no = 2;

4: Check out the minimum age of Class No. 01 is the number of
Select min (stu_age) from student where class_no = 1;

5: Check out the number of students in class No. 01
Select COUNT (*) from student where class_no = 1;

6: Check out the average age of Class No. 01 is the number of
Select AVG (stu_age) from student where class_no = 1;

7: Check out students with no classes
(failed) select * FROM student where class_no not in (select Class_no from Class);(query results are empty?)
(successful) select * from student where NOT exists (select Class_no from class where student.class_no = Class.class_no); The particularity of a null value, cannot be queried with not, null value is always False (false) when compared to any value, and an expression containing null always produces a null value

8: Check out the number of male students aged over 30 in class No. 02
SELECT * FROM student where class_no = 2 and Stu_sex = ' male ' and stu_age > 30;

9: Find out all the names of female students and add ' BBW ' names after their names
Select Concat (stu_name, ' BBW ') from student where stu_sex = ' female ';

10: Check out all the male students in class 1th and have a class student
SELECT * FROM student where (class_no = 1 | | Class_no is NULL) and stu_sex = ' male ';
SELECT * FROM student where class_no = 1 and stu_sex = ' Male ' | | Not EXISTS (select Class_no from class where student.class_no = Class.class_no);

11: Check out students aged 20-30
SELECT * FROM student where stu_age between and 30;

12: Check out the average wage of the trainee who has the second character in all the names ' Joe ' (no payroll, change to average age or add salary column)
Select AVG (stu_age) from student where stu_name like (' _ Joe% ');

13: Check out the names of all students in the class, which do not include duplicate
SELECT DISTINCT (stu_name) from student where class_no are NOT null;

14: Query name, gender two fields, not including duplicate records
Select distinct stu_name,stu_age from student;
15: Check out all the students in department 1th, according to the age of ascending order, the same age according to the school number descending order
SELECT * FROM student where class_no = 1 order by stu_age Asc,stu_no desc;

16: Query out the last piece of data
(failed) select * FROM Student limit (select COUNT (stu_no)-1 from student), 1;//parameter cannot be an expression
SELECT * FROM student ORDER BY stu_no DESC LIMIT 1;

17: Check out the 3 data after the school number is 6
SELECT * FROM student where stu_no > 6 limit 3;

18: Check out the previous 3 data with the number 6
SELECT * FROM student where Stu_no < 6 order BY stu_no desc limit 3;

19: Delete the last record (in case you don't know the last record ID)
Delete from student where stu_no in (select Stu_no from (select Stu_no from student ORDER BY stu_no DESC LIMIT 1) as Temt able);//MySQL cannot specify the updated target table in the FROM clause, so the deleted data is first placed in the staging table before being deleted

20: Delete the 3 data after the number 6 (same as (19))
Delete from student where stu_no in (select Stu_no from (select Stu_no from student where Stu_no > 6 limit 3) as Temtab Le);

MySQL Query small job