MySQL Database Search Instance

MySQL Database Search Example exercise (basic exercise):

The MySQL database is built with two tables, with the following examples:

CREATE TABLE Student (
Stuno CHAR (8) PRIMARY KEY,
Stuname VARCHAR () not NULL,
Stuage TINYINT not NULL,
Stusex TINYINT,
Birthday DATETIME
);


INSERT into student VALUES (' 10011011 ', ' Zhang San ', 19, 1, ' 1997-1-1 1:1:1 ');
INSERT into student VALUES (' 10011012 ', ' Hunanxiaoliang ', 20, 0, ' 1996-1-1 1:1:1 ');
INSERT into student VALUES (' 10011013 ', ' John Doe ', 16, 0, ' 1999-1-1 1:1:1 ');
INSERT into student VALUES (' 10011014 ', ' Xiao Li iv ', 18, 1, ' 1998-1-1 1:1:1 ');
INSERT into student VALUES (' 10011015 ', ' Xiao Qiang ', 21, 1, ' 1994-1-1 1:1:1 ');
INSERT into student VALUES (' 10011016 ', ' Zhao Yu ', 22,1,null);
INSERT into student VALUES (' 10011017 ', ' Harry ', 25, 1, ' 1991-1-1 1:1:1 ');
INSERT into student VALUES (' 10011018 ', ' small Six ', 17, 0, ' 2000-1-1 1:1:1 ');

CREATE TABLE Stuscore (
Scoreno CHAR (6) PRIMARY KEY,
Stuno CHAR (8) Not NULL,
Javascore TINYINT not NULL,
Htmlscore TINYINT not NULL
);


INSERT into Stuscore VALUES (' 200101 ', ' 10011011 ', 73,84);
INSERT into Stuscore VALUES (' 200102 ', ' 10011012 ', 77,69);
INSERT into Stuscore VALUES (' 200103 ', ' 10011013 ', 89,92);
INSERT into Stuscore VALUES (' 200104 ', ' 10011014 ', 62,84);
INSERT into Stuscore VALUES (' 200105 ', ' 10011015 ', 41,31);
INSERT into Stuscore VALUES (' 200106 ', ' 10011016 ', 37,50);
INSERT into Stuscore VALUES (' 200107 ', ' 10011017 ', 90,88);
INSERT into Stuscore VALUES (' 200108 ', ' 10011018 ', 85,72);

(# #数值时随意添加的)

The search examples are as follows:

--Query all student information
SELECT * from student;
--Query the name of the student Stuno 10011012
SELECT stuname from student WHERE stuno= ' 10011012 ';
--Query student information for Stuno 10011014 and 10011016
SELECT * FROM student WHERE stuno= ' 10011012 ' OR stuno= ' 10011016 ';
--Check the number of all students older than 20 years old
SELECT Stuno from student WHERE stuage>20;
---Query all student information for which the birthday is empty
SELECT * FROM student WHERE birthday is NULL;
--Query all students with a small surname
SELECT * FROM student WHERE stuname like ' small% ';
--Query The second word in all names is a small student information
SELECT * FROM student WHERE stuname like ' _ Small% ';
--Query all names with small student information
SELECT * FROM student WHERE stuname like '% small% '
--Query All student information with a small name of 2 characters
SELECT * FROM student WHERE stuname like ' small _ ';
--Check all student names and ages and order by age
SELECT stuname,stuage from student WHERE stuage ORDER by Stuage;
SELECT stuname,stuage from student WHERE stuage ORDER by Stuage DESC;
--Query all students whose surname is small in descending order of age
SELECT * FROM student WHERE stuname like ' small% ' ORDER by stuage;
--Find the student number with the highest performance in Java
Select Stuno from Stuscore WHERE javascore= (select MAX (Javascore) from Stuscore);
--Query the student number of all HTML scores above the average score
Select Stuno from Stuscore WHERE htmlscore> (select AVG (Htmlscore) from Stuscore);
--Check all student's name, two subjects ' grades
SELECT A.stuname,b.javascore,b.htmlscore
From student A,stuscore b
WHERE A.stuno=b.stuno;
--Query the highest-grade student information in Java
SELECT * FROM student A,stuscore b
WHERE javascore= (SELECT (MAX (Javascore)) from Stuscore)
and A. ' Stuno ' =b. ' Stuno ';
--Query all students whose Java scores are greater than the HTML score
SELECT stuname from student A,stuscore b
WHERE B. ' Javascore ' >b. ' Htmlscore '
and A. ' Stuno ' =b. ' Stuno ';
--Check the names of all students with minor surname and the results of the two subjects in descending order of age
SELECT A.stuname,b.javascore,b.htmlscore
From student A,stuscore b
WHERE A. ' Stuno ' = B. ' Stuno

MySQL Database Search Instance