MySQL Simple query statement

I. Establishment of teacher and student tables

CREATE TABLE Teacher (
tid int unsigned auto_increment primary key,
Tname varchar (10),
Tdesc varchar (10)
) auto_increment = 1;

Insert into teacher values (null, ' teacher ', ' Professor of Music '), (null, ' Miss Zhang ', ' Professor of Mathematics '), (null, ' Miss Li ', ' language Professor '), (null, ' Miss Liu ', ' English professor '), (null, ' Teacher Wang ', ' computer Professor ');
SELECT * from teacher;
--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------
CREATE TABLE Student (
Sid int unsigned auto_increment primary key,
Sname varchar (10),
Sbirthday Char (8),
Sdept varchar (10),
Stid int unsigned,
Sscore int unsigned,
Constraint TSFK foreign KEY (Stid) References teacher (tid) on delete set NULL on UPDATE CASCADE--Add foreign key
) auto_increment = 15100;

INSERT into student values (null, ' Dragon ', ' 19660912 ', ' Computer Science and Technology ', ' 1 ', ' + '), (null, ' Jet Li ', ' 19630112 ', ' Art ', ' 3 ', ' Max '), (null , ' Andy Lau ', ' 19611111 ', ' Internet Engineering ', ' 2 ', ' 99 '),
(NULL, ' Li Chenglin ', ' 19660912 ', ' Math ', ' 4 ', ' All '), (null, ' Zhang Chenglong ', ' 19850912 ', ' Computer Science and Technology ', ' 1 ', ' all ') ', (null, ' Liu Long ', ' 19770923 ', ' Computer Science and Technology ', ' 4 ', ' 92 '),
(NULL, ' Undeterred ', ' 19660912 ', ' English ', ' 5 ', ' '), (null, ' Zhang Juzheng ', ' 19550912 ', ' math ', ' 2 ', ' a ') ', (null, ' Wang Long ', ' 19970616 ', ' Internet Engineering ', ' 1 ', ' 67 '),
(NULL, ' Lichenggong ', ' 19660912 ', ' language ', ' 1 ', ' a ') ', (null, ' Zhang Ju ', ' 19620312 ', ' Music ', ' 3 ', ' + '), (null, ' Into the Dragon ', ' 19690928 ', ' Art ', ' 2 ', ' 94 '),
(NULL, ' Li Shulin ', ' 19660912 ', ' Art ', ' 2 ', ' 19540113 '), (null, ' Zhang Zheng ', ' 19630712 ', ' Art ', ' 4 ', ' n '), (null, ' Off Dragon ', ' 4 ', ' Math ', ' 83 ' ),
(NULL, ' Lee Forest ', ' 19660912 ', ' Computer Science and Technology ', ' 3 ', ' "), (null, ' Zhang Liang ', ' 19621112 ', ' Physical ', ' 5 ', ' The '"), (null, ' Guan Yu ', ' 19780827 ', ' Chinese ', ' 3 ', ' 96 '),
(null, ' Li Lin ', ' 19660912 ', ' Music ', ' 4 ', ' + '), (null, ' Zhang Fei ', ' 19220912 ', ' Computer Science and Technology ', ' 5 ', ' The ' "), (null, ' Liu Bei ', ' 19790718 ', ' Computer Science and Technology ', ' 1 ', ' 100 '),
(null, ' Cheng Li ', ' 19660912 ', ' Computer Science and Technology ', ' 5 ', ' 80 ');
SELECT * from student;

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------
--Select query statement
Select Sid, Sname, sdept from student;
--Column Aliases
Select SID Number, sname name, sdept Professional from student;
--Table aliases
Select S.sid from student s;
--Query criteria
--General Conditions = < > <= >=! = <> and Or not
SELECT * FROM student where sdept = ' Computer Science and Technology ' and Sscore >= 70;

SELECT * FROM student where sdept <> ' computer Science and technology ';

--between and not between and
SELECT * FROM student where sbirthday between ' 19660101 ' and ' 19970909 ';
SELECT * FROM student where sbirthday not between ' 19660101 ' and ' 19970909 ';

-In () No in ()
SELECT * FROM student where sdept in (' Art ', ' mathematics ');
SELECT * FROM student where sdept isn't in (' Art ', ' math ');

--is NULL if the query is not NULL

SELECT * FROM student where sscore is null;

--Fuzzy query like _ an arbitrary character% 0 or more arbitrary characters
SELECT * FROM student where sname like ' li% ';

Select *from student where sname like ' __ ';--query name is a two-word name

--Query sort

Select Sscore from Student order by sscore ASC;

Select Sname name, Ifnull (Round (Sscore), 0) score from student order by sscore Desc;
--Limit page usage for result rows
SELECT * FROM Student ORDER by sscore ASC limit 5;
SELECT * FROM student ORDER BY sscore desc limit 0, 3;

--Group Query

Select distinct sdept from student;

Select COUNT (Distinct sdept) from student;

Select Sdept Professional, COUNT (*) number, max (Sscore), min (sscore) Minimum, avg (Sscore) average from student
Group BY Sdept;

-Where query conditions have a grouping condition after group by
Select Sdept, COUNT (*), Max (Sscore), Min (sscore), avg (Sscore)
From student where true
GROUP BY Sdept has avg (Sscore) <80 ORDER by AVG (sscore) DESC limit 5;

--Query Display level
Select SID Number, sname name, Sdept Professional, Sscore score, if (sscore>=90, ' excellent ', if (sscore>=80, ' good ', if (sscore>=60, ' Pass ', ' retake ')) Grade
from student;

--Sub-query

Select T.dj level, COUNT (*) Number of people
From
(Select Sid,sname,sdept,sscore,if (sscore>=90, ' excellent ', if (sscore>=80, ' good ', if (sscore>=60, ' Pass ', ' retake '))) DJ
From student
) T
Group BY T.DJ;

--Error
SELECT * FROM student where Sscore = max (Sscore);

--Using sub-queries to achieve results
SELECT * FROM student where Sscore = (select Max (sscore) from student);

--------------------------------------------------------------------------------------------------------------- -------------------------------
--Advanced Query Connection query

SELECT * from student a,student B;

--Please inquire the name of the school name grade substitute teacher
Select S.sid,s.sname,t.tname
From student s joins teacher T on T. ' Tid ' = S. ' Stid ';

--Select S.sid,s.sname,t.tname from student s left join teacher T on T.tid = S.stid

MySQL Simple query statement