Nineth. mysql in case of limit and not

The Nineth chapter in MySQL, limit and not in case I. Project of the case 1. Create a database statement:

#创建数据库

CREATE DATABASE ' schooldb ';

Use ' schooldb ';

#创建学生表

CREATE TABLE ' Student ' (

' Sid ' INT (4) auto_increment not NULL PRIMARY KEY COMMENT ' student number ',

' Stuname ' VARCHAR (+) not NULL COMMENT ' student name ',

' Age ' INT (4) is not NULL COMMENT ' ages ',

' Sex ' INT (1) Not NULL COMMENT ' student sex '

Engine=innodb DEFAULT Charset=utf8 comment= ' Student Information Form ';

#创建课程表

CREATE TABLE ' Course ' (

' CID ' INT (4) PRIMARY KEY COMMENT ' Course number ',

' CName ' VARCHAR (not NULL COMMENT ' course name '),

' Tid ' INT (4) Not NULL COMMENT ' teacher number '

Engine=innodb DEFAULT Charset=utf8 comment= ' Course Information Form ';

#创建成绩表

CREATE TABLE ' score ' (

' Sid ' INT (4) COMMENT ' Student number ',

' CID ' INT (4) COMMENT ' Course number ',

' Score ' INT (4) Not NULL COMMENT ' score '

Engine=innodb DEFAULT Charset=utf8 comment= ' score table ';

#创建教师 table

CREATE TABLE ' Teacher ' (

' Tid ' INT (4) PRIMARY KEY COMMENT ' teacher number ',

' Teaname ' VARCHAR (not NULL COMMENT ' teacher name ')

Engine=innodb DEFAULT Charset=utf8 comment= ' teacher Information Form ';

#创建 ' score table ' primary key

ALTER TABLE ' score ' ADD CONSTRAINT ' pk_score ' PRIMARY KEY ' score ' (' Sid ', ' CID ');

#创建课程表外键外键关联 Teacher's form teacher number

ALTER TABLE ' course ' ADD CONSTRAINT ' fk_course_teacher ' FOREIGN KEY (' tid ') REFERENCES ' teacher ' (' tid ');

Student number #创建成绩表外键关联 Student form

ALTER TABLE ' score ' ADD CONSTRAINT ' fk_score_student ' FOREIGN KEY (' Sid ') REFERENCES ' student ' (' Sid ');

#创建成绩表外键关联 Curriculum Number

ALTER TABLE ' score ' ADD CONSTRAINT ' fk_score_course ' FOREIGN KEY (' cid ') REFERENCES ' Course ' (' CID ');

#插入学生表 data

INSERT into ' student ' VALUES (1, ' Zhang San ', 16, 1);

INSERT into ' Student ' VALUES (2, ' Li Sansi ', 17, 1);

INSERT into ' Student ' VALUES (3, ' Li Dazi ', 16, 1);

INSERT into ' Student ' VALUES (4, ' Wang Xiaohua ', 16,0);

INSERT into ' Student ' VALUES (5, ' Liu Live ', 17, 1);

INSERT into ' student ' VALUES (6, ' Thank you ', 16, 1);

INSERT into ' student ' VALUES (7, ' Wu Sangui ', 16, 1);

INSERT into ' student ' VALUES (8, ' Osmanthus ', 16,0);

#插入教师表 data

INSERT into ' teacher ' VALUES (101, ' Zhu Da Fat ');

INSERT into ' teacher ' VALUES (102, ' Fang Fang ');

INSERT into ' teacher ' VALUES (103, ' Dr. Liu ');

#插入课程表 data

INSERT into ' course ' VALUES (1011, ' language ', 101);

INSERT into ' Course ' VALUES (1012, ' English ', 102);

INSERT into ' course ' VALUES (1013, ' math ', 103);

#插入成绩表 data

INSERT into ' score ' VALUES (1,1011,90);

INSERT into ' score ' VALUES (1,1012,70);

INSERT into ' score ' VALUES (1,1013,60);

INSERT into ' score ' VALUES (2,1011,88);

INSERT into ' score ' VALUES (2,1012,70);

INSERT into ' score ' VALUES (2,1013,70);

INSERT into ' score ' VALUES (3,1011,92);

INSERT into ' score ' VALUES (3,1012,71);

INSERT into ' score ' VALUES (3,1013,65);

INSERT into ' score ' VALUES (4,1011,99);

INSERT into ' score ' VALUES (4,1012,78);

INSERT into ' score ' VALUES (4,1013,88);

INSERT into ' score ' VALUES (5,1011,87);

INSERT into ' score ' VALUES (5,1012,79);

INSERT into ' score ' VALUES (5,1013,62);

INSERT into ' score ' VALUES (6,1011,56);

INSERT into ' score ' VALUES (6,1012,65);

INSERT into ' score ' VALUES (6,1013,75);

INSERT into ' score ' VALUES (7,1011,50);

INSERT into ' score ' VALUES (7,1012,55);

INSERT into ' score ' VALUES (7,1013,51);

INSERT into ' score ' VALUES (8,1011,95);

INSERT into ' score ' VALUES (8,1012,59);

INSERT into ' score ' VALUES (8,1013,64);

2. Execute SQL query

Use ' schooldb ';

/*1. Inquire about the student's number and name in the course taught by the "Fang Fang" teacher. (Assume that the students have taken the course and take the exam). */

#测试通过

SELECT ' Sid ', ' stuname ' from ' student ' WHERE ' Sid ' in (

SELECT ' Sid ' from ' score ' WHERE ' cid ' = (

SELECT ' cid ' from ' Course ' WHERE ' tid ' = (

SELECT ' tid ' from ' teacher ' WHERE ' teaname ' = ' Fang Fang '

)

)

);

3.NOT in reverse Classic applications

/*2. Check the student number and name of each course with a score of less than 60. */

#改一下数据才有符合要求的数据

UPDATE ' score ' SET ' score ' =50 WHERE ' sid ' =7 and ' cid ' = 1013;

SELECT ' Sid ', ' stuname ' from ' student ' WHERE ' Sid ' isn't in (

SELECT ' Sid ' from ' score '

WHERE ' score ' >60

);

/* Difficulty: Step analysis:

1. Query all student numbers with scores greater than 60

2. Take the reverse, not all students greater than 60 points, is all students with a score of less than 60

*/

4.LIMIT workaround for sub-queries not supported

/*3. Query "Fang Fang" Teacher's "database" course results ranked in the 3rd to 6th place student number, name,

and insert this record into the new table Tempscore. (Requires use of the LIMIT clause) */

CREATE TABLE ' Tempscore ' (

SELECT ' Sid ', ' stuname ' from ' student ' WHERE ' Sid ' in (

#查询方芳老师所教课程第3-Number of 6 participants

SELECT ' Sid ' from (

SELECT ' Sid ' from ' score ' WHERE ' CID ' in (

SELECT ' cid ' from ' Course ' WHERE ' tid ' = (

SELECT ' tid ' from ' teacher ' WHERE ' teaname ' = ' Fang Fang '

)

) GROUP by ' score ' DESC LIMIT 2,4

) as Ta

)

);

/* Difficulty: The problem occurs when MySQL does not support using LIMIT error in subqueries: This version of MySQL doesn ' t yet supports ' limit & in/all/any/some subquery

The workaround is to add a layer of query "select ' Sid ' from () outside the limit clause

and alias The from result set, no error is given.

*/

4.HAVING Grouping of applications

/*4. Check the number of students who have failed two or more courses and their average scores. */

SELECT S.sid, AVG (Sc.score)

From ' student ' as S, ' score ' as SC

WHERE S.sid in

(

SELECT ' Sid ' from ' score '

WHERE ' score ' < 60

GROUP by ' Sid '

Having COUNT (' CID ') >= 2

)

and S.sid=sc.sid

GROUP by S.sid

/* Use the HAVING group */

5. Create a View

/*5. Create a view Student_view, which is used to query all students for their student number, name, number of exam courses, and total scores of each section. */

CREATE VIEW ' Student_view '

As

SELECT S.sid,s.stuname,count (C.cid), SUM (Sc.score) from ' student ' as S

INNER JOIN ' score ' as SC on Sc.sid=s.sid

INNER JOIN ' Course ' as C on c.cid = Sc.cid

GROUP by S.sid;

Nineth. mysql in case of limit and not