1. Single table query
4.1 basic query statement
Query the contents of the field Name and Salary; Query the contents of the whole table.
USE Company; #Please add the implementation code here ########## Begin ########## ########## retrieving the Name and Salary ########## select Name,Salary FROM tb_emp; ########## retrieving all the table ########## select * from tb_emp; ########## End ##########
4.2 query with IN keyword
Query the contents of field Name and Salary when Id is not equal to 1;
select Name,Salary from tb_emp where id not in (1);
4.3 range query with BETWEEN AND
Query the contents of the field Name and Salary when the field Salary range is 3000 ~ 5000.
select Name,Salary from tb_emp where Salary BETWEEN 3000 and 5000;
5.1 character matching query with LIKE
Query the Name and Salary contents of all employees whose Name starts with the letter C;
SELECT Name,Salary from tb_emp WHERE Name LIKE 'C%';
5.2 querying null values and removing duplicate results
1. Use the keyword IS NULL to return the contents of all fields in the data table where the field DeptId IS NULL. Note that the returned contents are the contents of all fields in the specified row; 2. Use the keyword DISTINCT to return the contents of the field Name in the data table that are not duplicate. Note that only the contents of the field Name need to be returned.
USE Company; ######### Begin ######### SELECT * FROM tb_emp WHERE DeptId IS NULL; ######### End ######### ######### Begin ######### SELECT DISTINCT Name FROM tb_emp; ######### End #########
5.3 multi condition query with AND and OR
1. Use the keyword AND to return the contents of all fields IN the data table where the field DeptId is 301 AND the salary is greater than 3000, where the penultimate letter of DeptId is the capital of i; 2. Use the keyword IN to return the contents of all fields with DeptId 301 AND 303 IN the data table.
USE Company; ######### Begin ######### SELECT * FROM tb_emp WHERE DeptId=301 AND Salary>3000; ######### End ######### ######### Begin ######### SELECT * FROM tb_emp WHERE DeptId in(301,303); ######### End #########
6.1 Sorting Query Results
Query all the information of class 1 in the student transcript and display the results in descending order.
########## Query all the information of class 1 and display the results in descending order ########## SELECT * FROM tb_score WHERE class_id=1 ORDER BY score DESC;
6.2 group query
Group query the class names in the class table
########## Group query class name ########## SELECT * FROM tb_class GROUP BY class_id;
6.3 use LIMIT to LIMIT the number of query results
Query the information of the second to fifth students in the class and sort them in descending order according to their grades.
########## Query the information of the second to fifth students in the class ########## SELECT * FROM tb_score ORDER BY score DESC LIMIT 1,4;
2. Connection query
7.1 internal connection query
Query the student name and the corresponding class name in the data table, and name the corresponding column names as studentName and className respectively
########## Query the students' names and corresponding classes in the data table ########## #Please add the implementation code here ########## Begin ########## SELECT tb_student.name as studentName,tb_class.name as className FROM tb_student join tb_class on tb_student.class_id=tb_class.id; ########## End ##########
7.2 external connection query
Use the left outer connection and the right outer connection to query the names of all students and the corresponding class names in the data table. The query result column is named studentName and className respectively.
USE School; ########## Use the left outer connection to query all student names and corresponding classes ########## #Please add the implementation code here ########## Begin ########## SELECT tb_student.name as studentName,tb_class.name as className FROM tb_student left join tb_class on tb_student.class_id=tb_class.id; ########## End ########## ########## Use the right external connection to query all student names and corresponding classes ########## #Please add the implementation code here ########## Begin ########## SELECT tb_student.name as studentName,tb_class.name as className FROM tb_student right join tb_class on tb_student.class_id=tb_class.id; ########## End ##########
7.3 compound conditional connection query
Query the names, grades and classes of students with scores above 90 in all classes. The names of students and classes of students are separately named studentName and className.
SELECT tb_student.name as studentName,tb_student.score,tb_class.name as className FROM tb_student left join tb_class on tb_student.class_id=tb_class.id WHERE tb_student.score>90 order by tb_student.score DESC ;
3. Sub query
8.1 subquery with comparison operator
Query the name and age of employees older than all average ages.
########## Begin ########## #1. Query the names and ages of employees older than the average age SELECT name,age FROM tb_emp where age>(SELECT AVG(age) FROM tb_emp); ########## End ##########
8.2 keyword sub query
1. Query the position name and salary of all employees whose salary is higher than the maximum salary in Java in the salary table;
2. Query the position name and salary of all employees whose salary is higher than the Java minimum wage in the salary table;
3. Query the position name and salary of all employees whose position is Java in salary table.
#1. Query with ALL keyword
SELECT position,salary from tb_salary
where salary>ALL(SELECT salary from tb_salary where position='java');
#2. Use ANY keyword to query
SELECT position,salary from tb_salary
where salary>ANY(SELECT salary from tb_salary where position='java');
#3. Use the IN keyword to query
SELECT position,salary from tb_salary
where position in ('java');
4. Query using aggregate function
11.1COUNT() function
Query the total number of data in the table;
Query the number of students in class 367 in this table, and output it in the format of class id and corresponding number.
########## Query the total number of data in the table ########## select count(*) from tb_class; ########## How many students are there in class 367 in this table ########## select classid,count(*) from tb_class where classid=367;
11.2SUM() function
Query the total score of middle school students in this form;
Query the total score of students' Chinese courses.
########## Query the total scores of all students ########## select sum(score) from tb_class; ########## Query the total score of students' Chinese subjects ########## select course,sum(score) from tb_class where course='language';
11.3AVG() function
In the query table, the average scores of the three students in the class in Chinese and English courses and the corresponding course names.
########## Query the average score of students in Chinese subjects ########## select course,avg(score) from tb_class where course='language'; ########## Query the average score of students' English subjects ########## select course,avg(score) from tb_class where course='English';
11.4MAX() function
Query the highest scores in Chinese and English courses respectively.
########## Query the highest score in the Chinese Course ########## select course,max(score) from tb_class where course='language'; ########## Query the highest scores in English courses ########## select course,max(score) from tb_class where course='English';
11.5MIN() function
Check the minimum scores in Chinese and English courses respectively.
########## Query the minimum score in the Chinese Course ########## select course,min(score) from tb_class where course='language'; ########## Query the minimum scores in English courses ########## select course,min(score) from tb_class where course='English';
10.1GROUP BY and aggregate function
It should be noted that the fields specified in select must either be included after the Group By statement as the basis for grouping; Or it will be included in the aggregate function.
Group the grade Id and gender, and count the total number of boys and girls in grades 2, 3 and 4 respectively.
#1. The total number of men and women in grades 2, 3 and 4 in the query table select gradeId,sex,count(*) from student group by gradeId,sex limit 1,4;
10.2 using HAVING and ORDER BY
The having clause is used to filter the grouped data, that is, having filters the data for the columns in the query results. Therefore, having is usually used with Group by.
In the select statement, the execution order of where, group by, having clauses and aggregate functions is as follows:
1.where clause removes unqualified data from the data source;
2. Then the group by clause collects data rows into each group;
3. Then, the statistical function calculates the statistical value for each group;
4. Finally, the having clause removes each group of data rows that do not meet its group search criteria.
select [Aggregate function] Field name from Table name [where query criteria] [group by Field name] [having Field name filter criteria]
Where clauses can be replaced by having. The difference is that where filters rows and having filters groups;
The function of the where clause is to remove the rows that do not meet the where condition before grouping the query results, that is, filter the data before grouping. The where condition cannot contain clustering functions, and use the where condition to filter out specific rows;
The function of the having clause is to filter the groups that meet the conditions, that is, to filter the data after grouping. The conditions often contain the grouping function. You can use the having condition to filter out specific groups, or you can use multiple grouping criteria for grouping.
Group By and Order By
select [Aggregate function] Field name from Table name [where query criteria] [group by Field name] [order by Field name sort direction] If the grouping field is the same as the sorting field, it is unnecessary order by Keyword, you only need to tell the sorting direction
1. We want to select the "three good" students. The condition is that at least two courses can be qualified only if they have more than 90 points (including 90 points). Please list the student number (sno) of the qualified students and the total number of subjects with more than 90 points (including 90 points);
2. The school selects advanced students. Students with an average score of more than 90 (including 90) are required to be qualified, and the Chinese course must be more than 95 (including 95). Please list the student number (sno) of qualified students and the average score of their subjects.
#1. Information of students with at least two courses above 90 points in the query table
select sno,count(*) from tb_grade
where score>=90
group by sno having count(*)>=2;
#2. Query the information of students whose average score is greater than 90 and whose Chinese course is more than 95 in the table
select sno,avg(score) from tb_grade
where sno in(
select sno from tb_grade
where score>=95 and pno='language'
)
group by sno having avg(score)>=90;
5. Complex query (II)
10.1 query student average score
According to the table and data provided, query the student number (s_id), student name (s_name) and average score (avg_score) of students with an average score of less than 60. The average score is required to retain 2 decimal places. (Note: it includes those with and without scores)
########## Begin ########## select s_id,s_name,ROUND(ifnull(avg(s_score),0.00),2) avg_score from (select student.s_id,student.s_name,score.s_score from student left join score on student.s_id=score.s_id) as emp group by s_id,s_name having ifnull(avg(s_score),0.00)<60; ########## End ##########
10.2 query the same student information
According to the table and data provided, query and s_ Id = information of other students whose courses are exactly the same (student number s_id, name s_name, gender s_sex).
select * from student where s_id in (select s_id from score where s_id in (select s_id from score where s_id not in (select s_id from score where c_id not in (select c_id from score where s_id='01')) group by s_id having count(c_id) = (select count(c_id) from score where s_id='01') and s_id!='01') );
10.3 query and sort the scores of each subject
Query the scores of each subject, sort and display the ranking, and output them by student number (s_id), course number (c_id), student score and rank.
select *, (select count(s_score)+1 from score B where B.c_id=A.c_id and B.s_score>A.s_score) as rank from score A order by c_id,rank,s_id desc;
The output results are as follows:
10.4 query the information of the students with the highest scores in Mr. Zhang's course
Query the information of the students with the highest scores among the students taking the courses taught by teacher Zhang San
select a.*,b.s_score,b.c_id,c.c_name from student a inner join score b on a.s_id=b.s_id inner join course c on b.c_id=c.c_id inner join teacher d on c.t_id=d.t_id where b.s_score in (select MAX(s_score) from score where c_id in (select c_id from course inner join teacher on course.t_id=teacher.t_id and teacher.t_name="Zhang San"))
The output results are as follows:
10.5 query the information of students who failed in two courses
Query the student number (s_id), name (s_name) and avg_score of students who fail two or more courses. It is required to calculate the average score as an integer.
select a.s_id,a.s_name,ROUND(AVG(b.s_score),0) avg_score from student a inner join score b on a.s_id=b.s_id where a.s_id in (select s_id from score where s_score<60) group by s_id having count(*)>=2;
The output results are as follows:
