Mysql queries small jobs and Mysql jobs

Mysql queries small jobs and Mysql 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 ');

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 ',
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, 01 );
Insert into student values (02, 'dual', 'male', 20, 01 );
Insert into student values (03, 'zhang fee', 'male', 32, 02 );
Insert into student values (04, 'Han xin', 'mal', 26, 02 );
Insert into student values (05, 'long', 'male', 27, 02 );
Insert into student values (06, 'Joe ', 'female', 17, 01 );
Insert into student values (07, 'Joe ', 'female', 16, 01 );
Insert into student values (08, 'Joe ', 'female', 16, 01 );
Insert into student values (09, 'guan go', 'mal', 32, 02 );
Insert into student values (10, 'Liu bei ', 'male', 36, null );
Alter table student drop foreign key 'student _ ibfk_1 ';


1: query the students with the largest age in the student table.
Select * from student where stu_age = (select max (stu_age) from student );

2: query the students of the youngest age in the student table.
Select * from student where stu_age = (select min (stu_age) from student );

3: Check the maximum age in the 02 class.
Select max (stu_age) from student where class_no = 2;

4: query the minimum age in the 01 class.
Select min (stu_age) from student where class_no = 1;

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

6: query the average age of the 01 class.
Select avg (stu_age) from student where class_no = 1;

7: No class student found
(Failed) select * from student where class_no not in (select class_no from class); (the query result is blank ?)
(Successful) select * from student where not exists (select distinct (class_no) from class where student. class_no = class. class_no); // special nature of the null value. It cannot be queried using not in. The NULL value is always FALSE when compared with any value ), and an expression that contains NULL always produces a NULL value.

8: the number of male students aged over 30 in class 02 is displayed.
Select * from student where class_no = 2 and stu_sex = 'mal' and stu_age> 30;

9: query the names of all female students, and add the 'Female name' to the name.
Select concat (stu_name, 'beauty ') from student where stu_sex = 'femal ';

10: Check whether all the male students in Class 1 have any other students.
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: query the students aged 20-30
Select * from student where stu_age between 20 and 30;

12: query the average salary of all students whose second character is 'job' (no salary column, change to average age or add a salary column)
Select avg (stu_age) from student where stu_name like ('_ Joe % ');

13: query the names of all students in the course, excluding duplicate
Select distinct (stu_name) from student where class_no is not null;

14: query the name and gender fields, excluding duplicate records
Select distinct stu_name, stu_age from student;
15: all the students in department 1 are sorted by age in ascending order, and those of the same age are sorted by student ID in descending order.
Select * from student where class_no = 1 order by stu_age asc, stu_no desc;

16: query the last data record.
(Failed) select * from student limit (select count (stu_no)-1 from student), 1; // The parameter cannot be an expression
Select * from student order by stu_no desc limit 1;

17: query the three data records after student ID 6.
Select * from student where stu_no> 6 limit 3;

18: query the first three data records whose student ID is 6.
Select * from student where stu_no <6 order by stu_no desc limit 3;

19: Delete the last record (if you do not know the id of the last record)
Delete from student where stu_no in (select stu_no from student order by stu_no desc limit 1) as temtable); // MySQL cannot specify the target table to be updated in the FROM clause, therefore, put the deleted data in the temporary table before deleting it.

20: delete 3 data records after student ID 6 (likewise (19 ))
Delete from student where stu_no in (select stu_no from student where stu_no> 6 limit 3) as temtable );