MySQL Query statement Practice

Source: http://blog.sina.com.cn/s/blog_767d65530101861c.html

Definition of sutdent table

Field name	Field description	Data type	Primary key	FOREIGN key	Non-empty	Only	Self-increment
Id	School Number	INT (10)	Is	Whether	Is	Is	Is
Name	Name	VARCHAR (20)	Whether	Whether	Is	Whether	Whether
Sex	Gender	VARCHAR (4)	Whether	Whether	Whether	Whether	Whether
Birth	Year of birth	Year	Whether	Whether	Whether	Whether	Whether
Department	Faculties and Departments	VARCHAR (20)	Whether	Whether	Is	Whether	Whether
Address	Home Address	VARCHAR (50)	Whether	Whether	Whether	Whether	Whether

Definition of score table

Field name	Field description	Data type	Primary key	FOREIGN key	Non-empty	Only	Self-increment
Id	Number	INT (10)	Is	Whether	Is	Is	Is
stu_id	School Number	INT (10)	Whether	Whether	Is	Whether	Whether
C_name	Course Name	VARCHAR (20)	Whether	Whether	Whether	Whether	Whether
Grade	Scores	INT (10)	Whether	Whether	Whether	Whether	Whether

1. Create student and score tables

CREATE TABLE Student (

ID INT (Ten) is not NULL UNIQUE PRIMARY KEY,

Name VARCHAR () is not NULL,

Sex VARCHAR (4),

Birth year,

Department VARCHAR (20),

Address VARCHAR (50)

);

Create a score table. the SQL code is as follows:

CREATE TABLE Score (

ID INT (Ten) is not NULL UNIQUE PRIMARY KEY auto_increment,

stu_id INT (Ten) is not NULL,

C_name VARCHAR (20),

Grade INT (10)

);

2. adding records for student and score tables

INSERT statement that inserts a record into the student table is as follows:

INSERT into student VALUES (901, ' Zhang Lu ', ' male ', 1985, ' computer Department ', ' Beijing Haidian District ');

INSERT into student VALUES (902, ' Zhang Dick ', ' male ', 1986, ' Chinese Department ', ' Beijing Changping District ');

INSERT into student VALUES (903, ' Zhang San ', ' female ', 1990, ' Chinese Department ', ' Hunan Province Yongzhou ');

INSERT into student VALUES (904, ' John Doe ', ' male ', 1990, ' English Department ', ' Liaoning Province Fuxin ');

INSERT into student VALUES (905, ' Harry ', ' female ', 1991, ' English Department ', ' Xiamen, Fujian province , ');

INSERT into student VALUES (906, ' wangliuqi ', ' male ', 1988, ' computer Department ', ' Hunan Province Hengyang ');

INSERT statement that inserts a record into the score table is as follows:

INSERT into score VALUES (null,901, ' computer ', 98);

INSERT into score VALUES (null,901, ' English ', +);

INSERT into score VALUES (null,902, ' computer ', +);

INSERT into score VALUES (null,902, ' Chinese ', n);

INSERT into score VALUES (null,903, ' Chinese ', up);

INSERT into score VALUES (null,904, ' computer ', +);

INSERT into score VALUES (null,904, ' English ', ();

INSERT into score VALUES (null,905, ' English ', 94);

INSERT into score VALUES (null,906, ' computer ', +);

INSERT into score VALUES (null,906, ' English ', ");

DEMO:

1. Querying all records of the student table

SELECT * from student;

2. query The student table of article 2 to 4 Records

SELECT * FROM Student LIMIT 1, 3;

3. from the student table, check the student's number (ID), nameand faculties ( Department ) of Information

Select Id,name,department from student;

4. Check the information from the Student table for students in the Department of Computer Science and English

SELECT * FROM Student

Where department = ' computer Department ' or department= ' English Department ';

SELECT * FROM Student

Where department in (' Computer department ', ' English Department ');

5. Check the student table For information on students aged 18~22

SELECT * FROM Student

where 2013-birth between and 22;

6. from the student table, find out how many people are in each faculty

Select Department,count (ID)

From student

GROUP by department;

7. Query the highest score for each account from the score table

Select C_name,max (grade) from score GROUP by C_name;

8. Query John Doe exam subjects (c_name) and exam results (grade)

Select C_name,grade from Score

where stu_id = (

SELECT ID from student where name = ' John Doe '

);

9. Access all Students ' information and exam information in a connected way

SELECT S.id,s.name,s.sex,s.birth,s.department,s.address,sc.c_name,sc.grade

From Student S,score SC

where s.id = sc.stu_id;

10. Calculate the total of each student

Select Stu_id,sum (grade) from score GROUP by stu_id;

11. Calculate the average score for each test subject

Select C_name,avg (grade) from score GROUP by C_name;

12. Check The student information of computer score below

SELECT * FROM student S

WHERE S.id in (

SELECT stu_id from score where c_name= ' computer ' and grade<95

);

Check the information of students who participate in both computer and English exams .

SELECT * FROM student S

WHERE S.id in (

SELECT stu_id from Score

where c_name= ' computer ' and stu_id in (

Select stu_id from score WHERE c_name = ' English '

)

);

Select s.* from student s,score s1,score s2

where s.id = s1.stu_id

and s1.c_name= ' computer '

and s.id = s2.stu_id

and S2.c_name= ' English ';

The computer test scores are sorted from highest to lowest

Select S.name,sc.grade from Student S,score SC

where s.id = sc.stu_id

and sc.c_name = ' computer '

ORDER by Sc.grade Desc;

15. Query the student's number from the Student table and the score table, then merge the query results

Select S.id from student s

Union select stu_id from score;

Check the name of the student surnamed Zhang or Wang surname, department and examination subjects and results

Select S.name,s.department,sc.c_name,sc.grade from Student S,score SC

where s.id = sc.stu_id

and s.name like ' Zhang% ' or s.name like ' King% ';

17. Enquiries are the names, ages, faculties and examinations of students in Hunan province and their scores

Select S.name,s.department,sc.c_name,sc.grade from Student S,score SC

where s.id = sc.stu_id

and s.address like ' Hunan% ';

MySQL Query statement Practice