MySQL with conditional query, linked table query

---restore content starts---

1, to set whether the selected data allows duplicate rows (identical rows of data)

All: Allow to appear--default does not write is all (allowed).

Distinct: Not allowed--what is called "de-duplication of rows"

2,where: Conditions

3,group by: Group By field name followed by table, usually only one field is grouped

MySQL table query syntax form: SELECT [All | distinct] field name or expression from table name [where] [group by] [have] [order by] [limit];

Exercises: Total of the following four Tables student table: Student teacher Table: Teacher Timetable: Course score table: Score

1, check the average score of the course with at least 5 students in the score table and start with 3

--Operation table score, grouped with CNO and CNO starting with 3, taking out the total number of CNO, and the average of CNO corresponding to degree

Select count (CNO), AVG (degree) from score where CNO like ' 3% ' GROUP by CNO;

--Find the total number of CNO greater than 5 from the virtual table

SELECT * FROM

(select count (CNO) A,avg (degree) b from score where CNO like ' 3% ' GROUP by CNO) c

where a>5;

2, query all student's Sno, CNAME and degree column

--Find student's Sno
Select Sno from student;

--Find Score's SNO,DEGREE,CNO
Select Sno,degree,cno from Score;

--Find Course's CNO
Select Cno,cname from Course;

--Forming a new table CNO Sno degree
Select A.cno,b.sno,b.sname,a.degree from (select Sno,degree,cno from Score) a joins (select Sno,sname from student) B on a . Sno=b.sno;

--table with a CNAME CNO sn degree sname
Select D.cname,e.cno,e.sno,e.degree,e.sname
From
(Select A.cno,b.sno,b.sname,a.degree from (select Sno,degree,cno from Score) a joins (select Sno,sname from student) B on A.sno=b.sno) as E
Join
(select Cname,cno from course) as D
On D.CNO=E.CNO;

3, the average score of "95033" class is queried

--Take Sno class from student, provided the class is 95033

Select Sno,class from student where class= ' 95033 ';

--Remove the score Sno degree

Select Sno,degree from Score;

--Make a sheet of the above two tables and take the average of degree

Select AVG (degree) from

(select Sno,class from student where class= ' 95033 ') a

Join

(select Sno,degree from score) b

On A.sno=b.sno;

4, the search for elective "3-105" course scores higher than the "109" student scores of all the students record

--Take the results of number 109th out

Select degree from score where sno= ' 109 ' and cno= ' 3-105 ';

--Draw the final table

SELECT * FROM Score

where

Degree> (select degree from score where sno= ' 109 ' and cno= ' 3-105 ')

and cno= ' 3-105 ';

5. Sno, Sname and Sbirthday of all students who were born in the same year as a student of the inquiry and study number 108

--Find the Sbirthday in student Sno 108
Select year (sbirthday) from student where sno= ' 108 ';

--Draw the final table
Select Sno,sname,sbirthday from Student

where

Year (Sbirthday) = (select year (sbirthday) from student where sno= ' 108 ');

MySQL with conditional query, linked table query