SQL statement grouping/sorting/calculation totals/joins and other SQL statement writing

1. What is a table connection?

A: For example, two tables, to get information from two tables, you need to use the form of a foreign key to the two table connection. Final postpartum combination information.

A table connection is connected through a join. Table Connection White is a big table. Table joins are also used on queries, and user queries get a variety of information.

2. What is the use of grouping?

A: When it comes to each "xxx field", it is a typical grouping, using the Group by XXX field.

When grouping, it is essential to show the grouped fields so that you can distinguish what data is in the grouped field.

For example , from the student table to find out how many people in each faculty

Mysql> SELECT Department, COUNT (ID) from student GROUP by department;

Select displays fields with Department, Department group by, so the group by IS followed by a department

Grouping does not have anything to do with table joins, the table joins the plain is to produce a large table.

What are the mathematical functions commonly used in 3.mysql?

A: Count (xx field) calculates the total quantity. For example, how many people are in the faculty.

Max (xx field), calculates the maximum value, such as calculating the highest score for each account

8. From the student table, find out how many people are in each faculty

Mysql> SELECT Department, COUNT (ID) from student GROUP by department;

+------------+-----------+

| Department | COUNT (ID) |

+------------+-----------+

|         Computer Systems | 2 |

|         English Department | 2 |

|         Chinese Department | 2 |

+------------+-----------+

9. query the highest score for each account from the score table

Mysql> SELECT C_name,max (grade) from score GROUP by C_name;

+--------+------------+

| C_name | MAX (grade) |

+--------+------------+

|         Computer | 98 |

|         English | 94 |

|         English | 95 |

+--------+------------+

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

Mysql> SELECT C_name, Grade

-From score WHERE stu_id=

--(SELECT ID from student

WHERE name= ' John Doe ');

+--------+-------+

| C_name | Grade |

+--------+-------+

|    Computer | 70 |

|    English | 92 |

+--------+-------+

11. Access all students ' information and exam information in a connected way

Mysql> SELECT Student.id,name,sex,birth,department,address,c_name,grade

From Student,score

WHERE student.id=score.stu_id;

+-----+--------+------+-------+------------+--------------+--------+-------+

| ID | name | sex | Birth | Department | Address | C_name | Grade |

+-----+--------+------+-------+------------+--------------+--------+-------+

| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |    Computer | 98 |

| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |    English | 80 |

| 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |    Computer | 65 |

| 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |    English | 88 |

| 903 | Zhang San |  Women | 1990 | Chinese Department | Hunan Province Yongzhou |    English | 95 |

| 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |    Computer | 70 |

| 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |    English | 92 |

| 905 | Harry |  Women | 1991 | English Department | Xiamen, Fujian |    English | 94 |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    Computer | 90 |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    English | 85 |

+-----+--------+------+-------+------------+--------------+--------+-------+

12. Calculate the total of each student

Mysql> SELECT Student.id,name,sum (grade) from Student,score

--WHERE student.id=score.stu_id

GROUP by ID;

+-----+--------+------------+

| ID | name | SUM (grade) |

+-----+--------+------------+

| 901 |        Boss Zhang | 178 |

| 902 |        Double Dick | 153 |

| 903 |         Zhang San | 95 |

| 904 |        John Doe | 162 |

| 905 |         Harry | 94 |

| 906 |        Wangliuqi | 175 |

+-----+--------+------------+

13. Calculate the average score for each test subject

Mysql> SELECT C_name,avg (grade) from score GROUP by C_name;

+--------+------------+

| C_name | AVG (grade) |

+--------+------------+

|    Computer | 80.7500 |

|    English | 87.7500 |

|    English | 91.5000 |

+--------+------------+

14. Check The student information of computer score below

Mysql> SELECT * FROM student

--WHERE ID in

--(SELECT stu_id from score

WHERE c_name= "Computer" and grade<95);

+-----+--------+------+-------+------------+--------------+

| ID | name | sex | Birth | Department | Address |

+-----+--------+------+-------+------------+--------------+

| 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |

| 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |

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

Mysql> SELECT * FROM student

--WHERE ID =any

--(SELECT stu_id from score

, WHERE stu_id in (

SELECT stu_id from

Score WHERE c_name= ' computer ')

and C_name= ' English ');

+-----+--------+------+-------+------------+--------------+

| ID | name | sex | Birth | Department | Address |

+-----+--------+------+-------+------------+--------------+

| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |

| 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |

+-----+--------+------+-------+------------+--------------+

Mysql> SELECT a.* from student A, score B, score C

--WHERE a.id=b.stu_id

and b.c_name= ' computer '

and a.id=c.stu_id

and C.c_name= ' English ';

+-----+--------+------+-------+------------+--------------+

| ID | name | sex | Birth | Department | Address |

+-----+--------+------+-------+------------+--------------+

| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |

| 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |

+-----+--------+------+-------+------------+--------------+

16. Sort computer test scores from highest to lowest

Mysql> SELECT stu_id, Grade

-From score WHERE c_name= ' computer '

ORDER by grade DESC;

+--------+-------+

| stu_id | Grade |

+--------+-------+

|    901 | 98 |

|    906 | 90 |

|    904 | 70 |

|    902 | 65 |

+--------+-------+

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

Mysql> SELECT ID from student

UNION

SELECT stu_id from score;

+-----+

| ID |

+-----+

| 901 |

| 902 |

| 903 |

| 904 |

| 905 |

| 906 |

+-----+

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

Mysql> SELECT Student.id, name,sex,birth,department, address, C_name,grade

From student, score

, WHERE

--(name like '% ' OR ' name like ' King% ')

and

student.id=score.stu_id;

+-----+--------+------+-------+------------+--------------+--------+-------+

| ID | name | sex | Birth | Department | Address | C_name | Grade |

+-----+--------+------+-------+------------+--------------+--------+-------+

| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |    Computer | 98 |

| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |    English | 80 |

| 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |    Computer | 65 |

| 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |    English | 88 |

| 903 | Zhang San |  Women | 1990 | Chinese Department | Hunan Province Yongzhou |    English | 95 |

| 905 | Harry |  Women | 1991 | English Department | Xiamen, Fujian |    English | 94 |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    Computer | 90 |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    English | 85 |

+-----+--------+------+-------+------------+--------------+--------+-------+

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

Mysql> SELECT Student.id, name,sex,birth,department, address, C_name,grade

From student, score

-WHERE address like ' Hunan% ' and

student.id=score.stu_id;

+-----+------+------+-------+------------+--------------+--------+-------+

| ID | name | sex | Birth | Department | Address | C_name | Grade |

+-----+------+------+-------+------------+--------------+--------+-------+

| 903 | Zhang San |  Women | 1990 | Chinese Department | Hunan Province Yongzhou |    English | 95 |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    Computer | 90 |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    English | 85 |

+-----+------+------+-------+------------+--------------+--------+-------+

SQL statement grouping/sorting/calculation totals/joins and other SQL statement writing