The result for # # SQL to create the student table, welcome add

The CREATE TABLE Student (
Sno int PRIMARY KEY auto_increment, sname VARCHAR (55) NOT NULL, sage int the NOT NULL); Truncate table student; INSERT into student VALUES (1001, "zhao", 18); INSERT into student VALUES (1002, "money", 18); INSERT into student VALUES (1003, "the sun", 25). INSERT into student VALUES (1004, "li", 25); INSERT into student VALUES (1005, "zhou", 29). INSERT into student VALUES (1006, "wu", 22); The CREATE TABLE Course (cno INT PRIMARY KEY auto_increment, cname VARCHAR (55), NOT NULL). INSERT INTO course VALUES (1, "Chinese"); INSERT INTO course VALUES (2, "mathematics"); INSERT INTO course VALUES (3, "English"); The CREATE TABLE Score (sno int, cno int, Score DOUBLE the not null, PRIMARY KEY (sno, cno)); - the CONSTRAINT fk_sno FOREIGN KEY REFERENCES (sno) student (sno) INSERT INTO the score VALUES (1001,1,60); INSERT INTO score VALUES (1001,2,61); INSERT INTO score VALUES (1001,3,59); INSERT INTO score VALUES (1002,1,80); INSERT INTO score VALUES (1003,1,61); INSERT INTO score VALUES (1003,2,61); INSERT INTO score VALUES (1005,1,50); INSERT INTO score VALUES (1005,2,55); INSERT INTO score VALUES (1005,3,54); INSERT INTO score VALUES (1006,1,80); INSERT INTO score VALUES (1006,2,90); INSERT INTO score VALUES (1006,3,100);


Create the insert, update

1, avg Max min sum () () () () the count ()
- the average query, maximum, minimum, sum, the total number of article
Select avg (s.s age), the Max (s.s age), min (s.s age), sum (s.s age), count (s.s no)
The from student s

2, distinct
- not to repeat value
querySelect distinct sage from student;
Select count (distinct sage) from student;

3, where the order by
- query data within a specified range id
Select sno, sname
The from student
Where sno (2 and 6) in/between the like
The order by sno asc/desc

4, left the join right join inner jion
- a family all of them didn't test
The select stu. Sno, stu. Sname
The from student stu
Left the join score sc
On stu. Sno=sc. Sno
Where sc. Sno is null;

5, abbreviated
- to participate in the examination of student performance
The select stu. Sno, stu. Sname, Arthur c. name, s.s core
The from student stu, score s course c
Where stu. Sno=s.s no and s.c no=Arthur c. no
The order by stu sname, s.s core desc;

The select stu. Sno, stu. Sname, Arthur c. name, s.s core
The from student stu
Left the join score s on stu. Sno=s.s no
Left the join course on s.c no c=Arthur c. no
The order by stu sname, s.s core desc

6, group by having
- query averaged more than 60 students number, name, score
The select s.s no, s.s name, avg (sc) score)
The from score as sc, student as s
Where s.s no=sc. Sno
Group by s.s no
Having avg (sc) score) & gt; 60;

The select s.s no, s.s name, avg (sc) score)
The from score as sc
Inner join student as s
On s.s no=sc. Sno
Group by s.s no
Having avg (sc) score) & gt; 60;

7, the subquery
- lack of take an examination of all the students who
The select stu. Sno, stu. Sname from student stu
Left the join score sc
On stu. Sno=sc. Sno
Group by stu. Sno
Having a count (sc) sno) & lt;> (select count (*) from course);

8 - to take an examination, but there are people with low test
The select stu. Sno, stu. Sname from student stu
Left the join score sc
On stu. Sno=sc. Sno
Where sc. Sno is not null
Group by stu. Sno
Having a count (sc) sno) & lt;> (select count (*) from course);

9, - all subjects are people who did not flunk
The select s.s name from student s where s.s no not in
(select s.s no from student s left join score sc on s.s no=sc. Sno and sc. Score & gt; 60);

10 - queries all student's student id, name, course number, grade;
The select stu. Sno, stu. Sname, count (s.s no) 'reference number of subjects', the sum (s.s core)' overall '
The from student stu
Left the join score s on stu. Sno=s.s no
Left the join course on s.c no c=Arthur c. no
Group by stu. Sno
The order by the sum (s.s core) desc;