MySQL Simple exercise

1. find information for all students

[Sql]select * FROM student

Rows affected : 0

Time : 0.000s

2. Identify all the people who have passed the test

[Sql]select * from student where Studentno in (select Studentno from result where Studentresult > 60)

Rows affected : 0

Time : 0.001s

3. identify all students in the designated grade

[Sql]select * from student where Gradeid in ()

Rows affected : 0

Time : 0.001s

4. find out "Zhao" surname classmate

[Sql]select * from student where studentname like ' Zhao % '

Rows affected : 0

Time : 0.000s

5. students are queried by the student number in descending order

[Sql]select * from student ORDER by Studentno DESC

Rows affected : 0

Time : 0.001s

6. students by address ascending / descending sort Query

[Sql]select * from Student ORDER by Address ASC

Rows affected : 0

Time : 0.001s

[Sql]select * from student ORDER by Address DESC

Rows affected : 0

Time : 0.001s

7. Query output 4 Student Information

[Sql]select * FROM student limit 4

Rows affected : 0

Time : 0.001s

8. Query output Article 2 to article 7 Student Information

[Sql]select * FROM student limit 1,6

Rows affected : 0

Time : 0.001s

9. Find out the average class score

[Sql]select avg (studentresult) from result

Rows affected : 0

Time : 0.001s

How many students are there in the school?

[Sql]select Count (*) from student

Rows affected : 0

Time : 0.000s

11. Statistics of the number of students with different addresses through group queries

[Sql]select Address,count (*) as ' number ' from student GROUP by Address

Rows affected : 0

Time : 0.011s

statistics of total scores per subject by group query

[Sql]select s.subjectname,subjectno,sum (Studentresult) as ' total score ' from result R left Join ' subject ' s on R.subje Ctno = S.subjectid GROUP by Subjectno,s.subjectname

Rows affected : 0

Time : 0.001s

13. Get the total score above the above-mentioned disciplines through group queries

Select S.subjectname,subjectno,sum (studentresult) as ' ZS ' from the result R left join ' subject's on r.subjectno = S.subjectid GROUP by Subjectno,s.subjectname

Having ZS > 90

14. Internal connection inquiry student and grade table

[Sql]select * FROM student S joins Grand g on s.gradeid = G.grandid

Rows affected : 0

Time : 0.001s

A. left / Right connection Query score table and chart of accounts

[Sql]select * from result R left join ' subject's on r.subjectno = S.subjectid

Rows affected : 0

Time : 0.002s

16. Create a view for the results of the Student table and class table connection query

[Sql]create or Replace view View1

As

SELECT *

From student S

Left JOIN Grand g on S.gradeid = G.grandid

Rows affected : 0

Time : 0.014s

17. Create a view for "Shanghai" students

[Sql]create or Replace View View2

As

SELECT *

From student

where Address = ' Shanghai '

Rows affected : 0

Time : 0.008s

18. Set up the order form and commodity table, establish the main foreign key relationship

[Sql]create Table G (

' id ' int PRIMARY key NOT NULL auto_increment,

' Name ' VARCHAR (not null),

' Num ' int not null DEFAULT 0

)

Rows affected : 0

Time : 0.017s

[Sql]create Table O (

' oid ' int PRIMARY key NOT NULL auto_increment,

' gid ' int not NULL,

' much ' int not NULL,

KEY ' fk3 ' (' gid '),

CONSTRAINT ' Fk3 ' FOREIGN KEY (' gid ') REFERENCES g (' id ')

)

Rows affected : 0

Time : 0.017s

New Data

20. New Insert trigger,Delete trigger,update trigger

[Sql]create TRIGGER trg_1

After INSERT on O

For each ROW

BEGIN

UPDATE g SET num = num-new.much where id = new.gid;

END;

Rows affected : 0

Time : 0.039s

[SQL] INSERT into o values (1,1,2)

Rows affected : 1

Time : 0.001s

[Sql]create TRIGGER Trg_2

After DELETE on O

For each ROW

BEGIN

UPDATE g SET num = num + old.much where id = old.gid;

END;

Rows affected : 0

Time : 0.032s

[Sql]delete from o where oid = 1

Rows affected : 1

Time : 0.000s

[Sql]create TRIGGER Trg_3

After update on O

For each ROW

BEGIN

UPDATE g SET num = num + old.much-new.much where id = new.gid;

END;

Rows affected : 0

Time : 0.029s

[Sql]update o set much =3 where OID =1

Rows affected : 1

Time : 0.001s

MySQL Simple exercise