MySQL complex query
1. Index
MySQL Index building for MySQL The efficient operation of is very important, and the index can be greatly improved MySQL Search speed. Take the table of contents page (index) of a Chinese dictionary for example. We can quickly find the required words according to the table of contents (index) sorted by pinyin, strokes, partial radicals, etc. The index is divided into single column index and combined index. Single column index, that is, an index contains only a single column. A table can have multiple single column indexes, but this is not a composite index. Composite index, that is, an index contains multiple columns. When creating an index, you need to ensure that the index is applied in SQL Conditions for query statements(General Act WHERE Condition of clause). In fact, the index is also a table that holds the primary key and index fields and points to the records of the entity table. Note: if too many indexes are used, the speed of updating the table will be reduced, such as updating the table INSERT,UPDATE and DELETE. Because when updating the table, MySQL Save not only the data, but also the index file. Index files that take up disk space when indexing. Index in mysql It is divided into ordinary index, unique index and full-text index
1.1 general index:
CREATE INDEX indexName ON `users` (username(length))
For CHAR and VARCHAR types, the length can be less than the actual length of the field; Length must be specified for BLOB and TEXT types.
1.2. Unique index:
It is similar to the previous ordinary index, except that the value of the index column must be unique, but null values are allowed.
CREATE UNIQUE INDEX indexName ON mytable(username(length))
1.3 full text index:
Content search for large TEXT
CREATE FULLTEXT INDEX indexName ON `users` (username)
Because the full-text index needs word segmentation for this field, it does not support Chinese very well, so it is not recommended to use full-text index in actual development. It is generally used in some large text content management systems
Format using full-text index: MATCH (columnName) AGAINST ('string') eg: SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('Intelligent')
Delete index:
ALTER TABLE `users` DROP INDEX `indexName`
practice:
1. The reason why the index can greatly speed up the query speed is that the underlying database adopts tree structure storage, and the type of tree is also changing with the upgrade of MySQL version. Search the corresponding blog to understand the advantages of tree data used in MySQL and the advantages of each version.
2. Connection query
Data preparation: download
CREATE TABLE `client` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `tel` varchar(20) NOT NULL, `emp_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `emp_id_fk` (`emp_id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4; insert into `client`(`id`,`name`,`tel`,`emp_id`) values (2,'WangTwo ','123',2); insert into `client`(`id`,`name`,`tel`,`emp_id`) values (4,'Pockmarks','234',2); insert into `client`(`id`,`name`,`tel`,`emp_id`) values (5,'Xiao Ming','345',1); insert into `client`(`id`,`name`,`tel`,`emp_id`) values (6,'Xiao Hong','456',1); insert into `client`(`id`,`name`,`tel`,`emp_id`) values (7,'I don't know who to look for','789',NULL); CREATE TABLE `emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `pwd` varchar(50) NOT NULL, `birth` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4; insert into `emp`(`id`,`name`,`pwd`,`birth`) values (1,'Zhang San','123456','2020-06-04'); insert into `emp`(`id`,`name`,`pwd`,`birth`) values (2,'Li Si','13456','2020-06-04'); insert into `emp`(`id`,`name`,`pwd`,`birth`) values (3,'Xiaofeng','123','2020-06-05');
The connection of two meters is divided into internal connection and external connection:
inner join: returns the data rows in the connection table that meet the connection conditions and query conditions.
External connection: divided into
left outer join can be abbreviated as left join
right outer join can be abbreviated as right join
Different from the inner connection, the outer connection returns not only the data rows in the connection table that meet the connection conditions and query conditions, but also the data rows in the left table (when the left outer connection) or the right table (when the right outer connection) that only meet the query conditions but do not meet the connection conditions.
from Table 1 connection type table 2 [on (Connection conditions)] [where (query criteria)]
Internal connection query (key)
SELECT e.id 'Employee number',e.`name` 'Employee name',c.`name` 'Customer name',c.`tel` 'Customer telephone' FROM `emp` e ,`client` c WHERE e.`id`=c.`emp_id`; SELECT e.id 'Employee number',e.`name` 'Employee name',c.`name` 'Customer name',c.`tel` 'Customer telephone' FROM `emp` e INNER JOIN `client` c ON e.`id`=c.`emp_id`; SELECT e.id 'Employee number',e.`name` 'Employee name',c.`name` 'Customer name',c.`tel` 'Customer telephone' FROM `emp` e LEFT OUTER JOIN `client` c ON e.`id`=c.`emp_id`;
Left outer connection query
Use the left outer join keyword to set the join conditions in the on clause. The join conditions include not only the data rows that meet the join conditions, but also all the data in the left table. Those not connected to the right table are not displayed.
3. Sub query and joint query
Subquery is also called nested query, which means that select query statements are embedded in the select clause or where clause.
SELECT * FROM `emp` WHERE id IN(SELECT emp_id FROM `client` WHERE id IN(2,4,5))
Joint query
Merge the query results of two query statements, remove the duplicate data rows, and then return the query results without duplicate data rows. Union keyword is used in union query
SELECT * FROM `client` WHERE emp_id = 2 UNION SELECT * FROM `client` WHERE emp_id = 1; SELECT `id`,`name` FROM `client` WHERE emp_id = 2 UNION SELECT `id`,`name` FROM `emp`;
Note: the number of structures and fields returned by two query statements must be the same, otherwise they cannot be merged.
4. Report function
Report functions are also called aggregate functions, which are generally used to summarize database table information. It is usually used in combination with grouping function. If grouping function is not used, all data of the table is considered as one group. Common report functions are as follows:
1. COUNT function - COUNT. If the parameter is a field name, COUNT the number of records whose field is not empty.
Select count(*)|count(Listing) from tablename [WHERE where_definition]
Case:
SELECT COUNT(*) FROM `client`
2. Summation function - SUM
Select sum(Listing){,sum(Listing)...} from tablename [WHERE where_definition]
Case:
SELECT SUM(`chinese`),SUM(`english`),SUM(`math`) FROM `student` WHERE `name` LIKE '%Lee%'
4. Average function - AVG
Select avg(Listing){,avg(Listing)...} from tablename [WHERE where_definition]
Case:
SELECT AVG(`chinese`),AVG(`english`),AVG(`math`) FROM `student` WHERE `name` LIKE '%Lee%'
5. Corner function - MAX/MIN
Select max(Listing) from tablename [WHERE where_definition]
5. Group query
Group by one or more fields. Those with the same field values are grouped as a group for statistics. The syntax format is:
[select ...] from ... [where...] [ group by ... [having... ]] [ order by ... ]
The group by clause specifies which fields to group by. The having clause sets the grouping query criteria.
Data preparation:
CREATE TABLE `staff` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `dept` varchar(10) DEFAULT NULL, `salary` double DEFAULT NULL, `edlevel` int(11) DEFAULT NULL, `hiredate` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4; insert into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (1,'Zhang San','Development Department',2000,3,'2020-02-10 15:19:59'); insert into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (2,'Li Si','Development Department',2500,3,'2019-07-16 15:20:01'); insert into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (3,'Wang Wu','Design Department',2600,5,'2019-09-25 15:20:03'); insert into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (4,'Wang Liu','Design Department',2300,4,'2020-06-05 15:20:05'); insert into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (5,'Ma Qi','Design Department',2100,4,'2019-11-11 15:20:07'); insert into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (6,'Zhao Ba','Sales Department',3000,5,'2020-06-05 15:20:19'); insert into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (7,'Qian Jiu','Sales Department',3000,7,'2020-06-05 15:20:21'); insert into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (8,'Sun Shi','Sales Department',3000,7,'2020-06-05 15:20:26');
Case:
1.List the results of the highest salary in each department SELECT `dept`, MAX(`salary`) AS 'Maximum salary' FROM `staff` GROUP BY `dept`; 2.Query the total salary of each department SELECT `dept`, SUM( `salary` ) AS 'Total salary' FROM `staff` GROUP BY `dept`; 3.Query the highest salary of each level in each department of the company in 2020 SELECT `dept`, `edlevel`, MAX( `salary` ) AS maximum FROM staff WHERE `hiredate` > '2020-01-01' GROUP BY `dept`, `edlevel`; 4.Query the maximum and minimum salaries of departments with more than 2 employees and query in ascending order of departments SELECT `dept`, MAX( `salary` ) AS maximum, MIN( `salary` ) AS minimum FROM `staff` GROUP BY `dept` HAVING COUNT(*) > 2 ORDER BY DEPT 5.Query the maximum and minimum salaries of departments where the average salary of employees is greater than or equal to 2300, and sort by the maximum salary SELECT `dept`, MAX( `salary` ) AS maximum, MIN( `salary` ) AS minimum FROM `staff` GROUP BY `dept` HAVING AVG( `salary` ) >= 2300 ORDER BY MAX(`salary`)
6. Packet connection function
Merge the row data with the same group in MySQL to use group_concat function. The syntax of this function is as follows:
group_concat([DISTINCT] Fields to connect [Order BY ASC/DESC sort field] [Separator 'Separator'])
Case: group by id, print the value of the name field on one line, separated by commas (default)
select id,group_concat(name) from aa group by id;
Case: grouped by id, print the value of the name field in one line, separated by semicolons
select id,group_concat(name separator ';') from aa group by id;
Case: grouped by id, print the value of the de redundant name field on one line, separated by commas
select id,group_concat(distinct name) from aa group by id;
Case: group by id, print the value of the name field on one line, separated by commas, in reverse order by name
select id,group_concat(name order by name desc) from aa group by id;
7. Leftmost index matching principle (self-study)
Test tables are created and composite (Federated) indexes (a, b, c) are established
create table test(
a int ,
b int,
c int,
d int,
key index_abc(a,b,c)
)engine=InnoDB default charset=utf8;
Insert 10000 pieces of data
DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=10000 DO
INSERT INTO test(a,b,c,d) VALUES(i,i,i,i);
SET i = i+1;
END WHILE;
END $
CALL proc_initData();
The explain directive looks at the following SQL
explain select * from test where a<10 ; explain select * from test where a<10 and b <10; explain select * from test where a<10 and b <10 and c<10;
Change the order of a, b and a, b and c
explain select * from test where b<10 and a <10; explain select * from test where b<10 and a <10 and c<10;
After checking the following data, it is found that the mysql query optimizer will determine the order in which to correct this sql statement, and the highest efficiency will be achieved. Finally, the real execution plan will be generated. Therefore, of course, we can make the best use of the query order when indexing, so the mysql query optimizer will finally execute the query in this order.
mysql> explain select * from test where b<10 and a <10; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | test | NULL | range | index_abc | index_abc | 5 | NULL | 9 | 33.33 | Using index condition | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
Why B < 10 and C < 10 do not use indexes? And a < 10 and C < 10?
mysql> explain select * from test where b<10 and c <10; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 10005 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
When the data item of the b + tree is a composite data structure, such as (name,age,sex), the b + number establishes the search tree from left to right. For example, when data such as (Zhang San, 20,F) is retrieved, the b + tree will give priority to comparing the name to determine the search direction in the next step. If the name is the same, then compare age and sex in turn to get the retrieved data; However, when there is no name data like (20,F), the b + tree does not know which node to query next, because name is the first comparison factor when establishing the search tree. You must search according to name first to know where to query next. For example, when retrieving data such as (Zhang San, F), the b + tree can use name to specify the search direction, but the next field age is missing, so we can only find the data whose name is equal to Zhang San, and then match the data whose gender is F. this is a very important property, that is, the leftmost matching feature of the index.
8. Chapter exercise: (dictation of the first question for 30 minutes)
1. Data preparation: Data download
CREATE TABLE `class` (
`classid` int(11) NOT NULL AUTO_INCREMENT,
`classname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`classid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
insert into `class`(`classid`,`classname`) values (1,'G1T01');
insert into `class`(`classid`,`classname`) values (2,'G1T02');
insert into `class`(`classid`,`classname`) values (3,'G1T03');
insert into `class`(`classid`,`classname`) values (4,'G1T04');
insert into `class`(`classid`,`classname`) values (5,'G1T05');
insert into `class`(`classid`,`classname`) values (6,'G1T06');
insert into `class`(`classid`,`classname`) values (7,'G1T07');
CREATE TABLE `computer` (
`studentid` varchar(20) DEFAULT NULL,
`score` float DEFAULT NULL,
KEY `studentid` (`studentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into `computer`(`studentid`,`score`) values ('2010001',90);
insert into `computer`(`studentid`,`score`) values ('2010002',80);
insert into `computer`(`studentid`,`score`) values ('2010003',70);
insert into `computer`(`studentid`,`score`) values ('2010004',60);
insert into `computer`(`studentid`,`score`) values ('2010005',75);
insert into `computer`(`studentid`,`score`) values ('2010006',85);
insert into `computer`(`studentid`,`score`) values ('2010007',70);
insert into `computer`(`studentid`,`score`) values ('2010008',60);
insert into `computer`(`studentid`,`score`) values ('2010009',75);
insert into `computer`(`studentid`,`score`) values ('2010010',85);
CREATE TABLE `student` (
`studentid` varchar(20) NOT NULL,
`studentname` varchar(20) DEFAULT NULL,
`studentage` int(11) DEFAULT NULL,
`studentsex` char(10) DEFAULT NULL,
`studentaddress` varchar(50) DEFAULT NULL,
`classid` int(11) DEFAULT NULL,
PRIMARY KEY (`studentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010001','Jack',21,'male','Xiangfan, Hubei',1);
insert into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010002','Scott',22,'male','Wuhan, Hubei',2);
insert into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010003','Lucy',23,'female','Wuhan, Hubei',3);
insert into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010004','Alan',19,'female','Xiangfan, Hubei',4);
insert into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010005','Bill',20,'male','Xiangfan, Hubei',5);
insert into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010006','Bob',21,'male','Yichang, Hubei',6);
insert into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010007','Colin',22,'female','Xiangfan, Hubei',6);
insert into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010008','Fred',19,'male','Yichang, Hubei',5);
insert into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010009','Hunk',19,'male','Wuhan, Hubei',4);
insert into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010010','Jim',18,'male','Xiangfan, Hubei',3);
practice:
1 Find out the student's number, name and computer score 2 Query the information of students who have taken the exam 3 Find out the student's number, name, class name and computer score 4 Find out the number, name and computer score of students over 19 years old 5 The queried name contains c Student number, name, computer score 6 Find out the student number, name and class name whose computer score is greater than 80 7 Find out the information of all students and computer score information 8 Find out the average score, the highest score and the lowest score of the students in each class 9 Query and display the class name and average score information of the class whose computer average score is greater than 75, and display them in descending order of average score 10 Query out and Jim Basic information about students living in the same place 11 Query the basic information of students whose class number is greater than 3 12 Find out the computer average score information of students with class number greater than 3 13 Query the student information of boys with class number greater than 3 14 Query the average computer score, highest score and lowest score of boys and girls 15 Change the age of students who have taken the exam to 20 16 Query the average score of students in each class (the query result includes the average score and class name) 17 Delete name contains“ c"Character student computer score 18 Find out G1T07 Number, name, class name and computer score of class students 19 Find out that the age is 20-25 Number, name, age and computer score of students between 20 Find out the number, name, computer score and class name of the student with the highest score 21 Query and count the average score of each class, display the information that the average score exceeds 70, and display the information in descending order
2. Practice
Data preparation: Data download
Table structure Preview
– student form (SID, Sname, Sage, Ssex) -- Sid student number, Sname student name, Sage date of birth, Ssex student gender
– Course(CId,Cname,TId) --CId course number, Cname course name, TId teacher number
– teacher table (TID, Tname) -- TID teacher number, Tname teacher name
– transcript SC (SID, CId, score) - Sid student number, CId course number, score
CREATE TABLE `course` (
`cid` varchar(10) DEFAULT NULL,
`cname` varchar(10) DEFAULT NULL,
`tid` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
insert into `course`(`cid`,`cname`,`tid`) values ('01','language','02');
insert into `course`(`cid`,`cname`,`tid`) values ('02','mathematics','01');
insert into `course`(`cid`,`cname`,`tid`) values ('03','English','03');
CREATE TABLE `sc` (
`sid` varchar(10) DEFAULT NULL,
`cid` varchar(10) DEFAULT NULL,
`score` decimal(18,1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
insert into `sc`(`sid`,`cid`,`score`) values ('01','01','80.0');
insert into `sc`(`sid`,`cid`,`score`) values ('01','02','90.0');
insert into `sc`(`sid`,`cid`,`score`) values ('01','03','99.0');
insert into `sc`(`sid`,`cid`,`score`) values ('02','01','70.0');
insert into `sc`(`sid`,`cid`,`score`) values ('02','02','60.0');
insert into `sc`(`sid`,`cid`,`score`) values ('02','03','80.0');
insert into `sc`(`sid`,`cid`,`score`) values ('03','01','80.0');
insert into `sc`(`sid`,`cid`,`score`) values ('03','02','80.0');
insert into `sc`(`sid`,`cid`,`score`) values ('03','03','80.0');
insert into `sc`(`sid`,`cid`,`score`) values ('04','01','50.0');
insert into `sc`(`sid`,`cid`,`score`) values ('04','02','30.0');
insert into `sc`(`sid`,`cid`,`score`) values ('04','03','20.0');
insert into `sc`(`sid`,`cid`,`score`) values ('05','01','76.0');
insert into `sc`(`sid`,`cid`,`score`) values ('05','02','87.0');
insert into `sc`(`sid`,`cid`,`score`) values ('06','01','31.0');
insert into `sc`(`sid`,`cid`,`score`) values ('06','03','34.0');
insert into `sc`(`sid`,`cid`,`score`) values ('07','02','89.0');
insert into `sc`(`sid`,`cid`,`score`) values ('07','03','98.0');
CREATE TABLE `student` (
`sid` varchar(10) DEFAULT NULL,
`sname` varchar(10) DEFAULT NULL,
`sage` datetime DEFAULT NULL,
`ssex` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
insert into `student`(`sid`,`sname`,`sage`,`ssex`) values ('01','Lei Zhao','1990-01-01 00:00:00','male');
insert into `student`(`sid`,`sname`,`sage`,`ssex`) values ('02','Qian Dian','1990-12-21 00:00:00','male');
insert into `student`(`sid`,`sname`,`sage`,`ssex`) values ('03','Sun Feng','1990-05-20 00:00:00','male');
insert into `student`(`sid`,`sname`,`sage`,`ssex`) values ('04','Li Yun','1990-08-06 00:00:00','male');
insert into `student`(`sid`,`sname`,`sage`,`ssex`) values ('05','Zhou Mei','1991-12-01 00:00:00','female');
insert into `student`(`sid`,`sname`,`sage`,`ssex`) values ('06','Wu Lan','1992-03-01 00:00:00','female');
insert into `student`(`sid`,`sname`,`sage`,`ssex`) values ('07','Zheng Zhu','1989-07-01 00:00:00','female');
insert into `student`(`sid`,`sname`,`sage`,`ssex`) values ('08','Ju Wang','1990-01-20 00:00:00','female');
CREATE TABLE `teacher` (
`tid` varchar(10) DEFAULT NULL,
`tname` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
insert into `teacher`(`tid`,`tname`) values ('01','Zhang San');
insert into `teacher`(`tid`,`tname`) values ('02','Li Si');
insert into `teacher`(`tid`,`tname`) values ('03','Wang Wu');
Title:
1,Query the student numbers of all students whose grades in "01" course are higher than those in "02" course; 2,Query the student number and average score of students whose average score is greater than 60; 3,Query the student number, name, number of courses selected and total score of all students 4,Query the number of teachers surnamed "Li"; 5,Query the student number and name of the students who have not learned the "Zhang San" teacher's class; 6,Query the student number and name of the students who have studied the course numbered "01" and also studied the course numbered "02"; 7,Query the student number and name of the students who have learned the lessons taught by teacher Zhang San; 8,Query the student numbers and names of all students whose grades of course number "01" are lower than those of course number "02"; 9,Query the student number and name of all students whose course scores are less than 60; 10,Query the student number and name of students who have not learned all courses; 11,Query the student number and name of at least one course that is the same as that of the student with student number "01"; 12,Query and"01"The student number and name of other students whose courses are exactly the same 13,Handle“ SC"The scores of the courses taught by "Zhang San" in the table are changed to the average score of this course; 14,I haven't learned"Zhang San"Names of students in any course taught by the teacher 15,Query the student number, name and average score of students who fail two or more courses 16,retrieval"01"Student information with a course score less than 60 in descending order 17,Displays the average scores of all students from high to low 18,Query the highest score, lowest score and average score of each subject: displayed in the following form: Course ID，curriculum name，Highest score, lowest score, average score, pass rate 19,According to the order of the average score of each subject from low to high and the percentage of passing rate from high to low 20,Query the total score of students and rank them 21,Query the average score of different courses taught by different teachers from high to low 22,Query the information of the second to third students in all courses and the course results 23,Count the number of students in each score section of each subject: course number,Course name,[100-85],[85-70],[70-60],[0-60]And percentage 24,Query students' average grades and their ranking 25,Query the top three records of each subject 26,Query the number of students selected for each course 27,Find out the student numbers and names of all students who have taken only one course 28,Query the number of boys and girls 29,Query name contains"wind"Word student information 30,Query the list of same-sex students with the same name and count the number of students with the same name 31,Check the list of students born in 1990(Note: Student In the table Sage The type of column is datetime) 32,Query the average score of each course. The results are arranged in ascending order according to the average score. If the average score is the same, it is arranged in descending order according to the course number 37,Query the failed courses and arrange them according to the course number from large to small 38,The query course number is"01"Student number and name of students with course scores of more than 60; 40,Query the names and grades of the students with the highest scores among the students taking the courses taught by teacher Zhang San 42,Check the top two with the best grades in each subject 43,Count the number of elective students for each course (only for courses with more than 5 people). It is required to output the course number and the number of elective students. The query results are arranged in descending order by the number of students. If the number of students is the same, they are arranged in ascending order by the course number 44,Retrieve the student number of at least two courses 45,Query the student information of all courses 46,Query the age of each student 47,Inquire about the students whose birthday is this week 48,Check the students whose birthday is next week 49,Query students whose birthdays are this month 50,Query the students whose birthday is next month
Refer to answer 1:
--1 Find out the student's number, name and computer score select student.studentid number,studentname full name,score Computer achievement from student inner join computer on student.studentid = computer.studentid; select stu.studentid Student number,stu.studentname Student name,com.score as Computer achievement from student as stu,computer as com where stu.studentid=com.studentid; --2 Query the information of students who have taken the exam select * from student where studentid in(select studentid from computer); --3 Find out the student's number, name, class name and computer score select stu.studentid as Student number,stu.studentname as full name,cl.classname as Class name,com.score as Computer achievement from class as cl,student as stu, computer as com where cl.classid=stu.classid and stu.studentid=com.studentid; --4 Find out the number, name and computer score of students over 19 years old select stu.studentid as Student number,stu.studentname as Student name,com.score as Computer achievement from student as stu, computer as com where stu.studentid=com.studentid and stu.studentage>19; --5 The queried name contains c Student number, name, computer score select stu.studentid as Student number,stu.studentname as Student name,com.score as Computer achievement from student as stu, computer as com where stu.studentid=com.studentid and stu.studentname like '%c%'; --6 Find out the student number, name and class name whose computer score is greater than 80 select stu.studentid as Student number,stu.studentname as Student name,com.score as Computer achievement from student as stu, computer as com where stu.studentid=com.studentid and com.score>80; --7 Find out the information of all students and computer score information select stu.studentid number,stu.studentname full name,stu.studentage Age,stu.studentsex Gender,stu.studentaddress address,stu.classid Class number,com.score achievement from student stu left join computer as com on stu.studentid=com.studentid; --8 Find out the average score, the highest score and the lowest score of the students in each class select c.classname as class,avg(co.score) as average,max(co.score) as Highest score,min(co.score) as Lowest score from class as c left join student as s on c.classid=s.classid left join computer as co on s.studentid=co.studentid group by c.classname; --9 Query and display the class name and average score information of the class whose computer average score is greater than 75, and display them in descending order of average score select c.classname as Class name,avg(co.score) as average from class as c inner join student as s on c.classid=s.classid inner join computer as co on s.studentid=co.studentid group by c.classname having average>75 order by average desc; --10 Query out and Jim Basic information about students living in the same place select * from student where studentaddress=(select studentaddress from student where studentname='Jim'); --11 Query the basic information of students whose class number is greater than 3 select * from student where classid>3; --12 Find out the computer average score information of students with class number greater than 3 select avg(co.score) as average from student as s,computer as co where co.studentid=s.studentid and s.classid>3; --13 Query the student information of boys with class number greater than 3 select * from student where classid>3 and studentsex='male'; --14 Query the average computer score, highest score and lowest score of boys and girls select s.studentsex as Gender, avg(c.score) as average,max(c.score) as Highest score,min(c.score) as Lowest score from student as s inner join computer as c on s.studentid=c.studentid group by s.studentsex; --15 Change the age of students who have taken the exam to 20 update student set studentage=20 where studentid in(select studentid from computer); --16 Query the average score of students in each class (the query result includes the average score and class name) select cl.classname as Class name, avg(co.score) as average from computer as co,class as cl, student as s where cl.classid=s.classid and s.studentid=co.studentid group by s.classid; --17 Delete name contains“ c"Character student computer score delete from computer where studentid in (select studentid from student where studentname like '%c%'); --18 Find out G1T07 Number, name, class name and computer score of class students select s.studentid as number,s.studentname as full name,c.classname as class,co.score as achievement from student as s, class as c,computer as co where s.studentid=co.studentid and c.classid=s.classid and c.classname='G1T07'; --19 Find out that the age is 20-25 Number, name, age and computer score of students between select s.studentid as number,s.studentname as full name,s.studentage as Age,c.score as achievement from student as s inner join computer as c on s.studentid=c.studentid where s.studentage between 20 and 25; select s.studentid as number,s.studentname as full name,s.studentage as Age,c.score as achievement from student as s, computer as c where s.studentid=c.studentid and s.studentage between 20 and 25; --20 Find out the number, name, computer score and class name of the student with the highest score select s.studentid as number, s.studentname as full name,c.score as achievement,cl.classname as Class name from student as s inner join computer as c on s.studentid=c.studentid inner join class as cl on cl.classid=s.classid order by c.score desc limit 1; --21 Query and count the average score of each class, display the information that the average score exceeds 70, and display the information in descending order select classname class ,AVG(score) average from class inner join student on class.classid = student.classid inner join computer on computer.studentid = student.studentid group by classname having average>70 order by average desc;
Note: limit 1 is a MySQL specific syntax, not a SQL Standard Specification. It is not used in Oracle.
Refer to answer 2:
1,Query the student numbers of all students whose grades in "01" course are higher than those in "02" course; SELECT DISTINCT t1.sid AS sid FROM (SELECT * FROM sc WHERE cid='01')t1 LEFT JOIN (SELECT * FROM sc WHERE cid='02')t2 ON t1.sid=t2.sid WHERE t1.score>t2.score 2,Query the student number and average score of students whose average score is greater than 60; SELECT sid,AVG(score)FROM sc GROUP BY sid HAVING AVG(score>60) 3,Query the student number, name, number of courses selected and total score of all students SELECT student.sid AS sid,sname,COUNT(DISTINCT cid) course_cnt,SUM(score) AS total_score FROM student LEFT JOIN sc ON student.sid=sc.sid GROUP BY sid,sname 4,Query the number of teachers surnamed "Li"; SELECT COUNT(DISTINCT tid) AS teacher_cnt FROM teacher WHERE tname LIKE 'Lee%' 5,Query the student number and name of the students who have not learned the "Zhang San" teacher's class; SELECT sid,sname FROM student WHERE sid NOT IN(SELECT sc.sid FROM teacher LEFT JOIN course ON teacher.tid=course.tid LEFT JOIN sc ON course.cid=sc.cid WHERE teacher.tname='Zhang San' ) 6,Query the student number and name of students who have studied "01" and also studied the course numbered "02"; SELECT t.sid AS sid ,sname FROM ( SELECT sid ,COUNT(IF(cid='01',score,NULL)) AS count1 ,COUNT(IF(cid='02',score,NULL)) AS count2 FROM sc GROUP BY sid HAVING COUNT(IF(cid='01',score,NULL))>0 AND COUNT(IF(cid='02',score,NULL))>0 )t LEFT JOIN student ON t.sid=student.sid 7,Query the student number and name of the students who have learned the lessons taught by teacher Zhang San; SELECT student.sid ,sname FROM ( SELECT DISTINCT cid FROM course LEFT JOIN teacher ON course.tid=teacher.tid WHERE teacher.tname='Zhang San' )course LEFT JOIN sc ON course.cid=sc.cid LEFT JOIN student ON sc.sid=student.sid GROUP BY student.sid,sname 8,Query the student numbers and names of all students whose grades of course number "01" are lower than those of course number "02"; SELECT t1.sid,sname FROM ( SELECT DISTINCT t1.sid AS sid FROM (SELECT * FROM sc WHERE cid='01')t1 LEFT JOIN (SELECT * FROM sc WHERE cid='02')t2 ON t1.sid=t2.sid WHERE t1.score>t2.score )t1 LEFT JOIN student ON t1.sid=student.sid 9,Query the student number and name of all students whose course scores are less than 60; SELECT t1.sid,sname FROM ( SELECT sid,MAX(score) FROM sc GROUP BY sid HAVING MAX(score<60) )t1 LEFT JOIN student ON t1.sid=student.sid 10,Query the student number and name of students who have not learned all courses; SELECT t1.sid,sname FROM ( SELECT COUNT(cid),sid FROM sc GROUP BY sid HAVING COUNT(cid) < (SELECT COUNT(DISTINCT cid) FROM course) )t1 LEFT JOIN student ON t1.sid=student.sid 11,Query the student number and name of at least one course that is the same as that of the student with student number "01"; SELECT DISTINCT sc.sid FROM ( SELECT cid FROM sc WHERE sid='01' )t1 LEFT JOIN sc ON t1.cid=sc.cid 12,Query and"01"The student number and name of other students whose courses are exactly the same #Note that the courses are exactly the same as those of '01', but the number of non learning courses is the same. Here I use the left connection to solve this problem SELECT t1.sid,sname FROM ( SELECT sc.sid ,COUNT(DISTINCT sc.cid) FROM ( SELECT cid FROM sc WHERE sid='01' )t1 LEFT JOIN sc ON t1.cid=sc.cid GROUP BY sc.sid HAVING COUNT(DISTINCT sc.cid)= (SELECT COUNT(DISTINCT cid) FROM sc WHERE sid = '01') )t1 LEFT JOIN student ON t1.sid=student.sid WHERE t1.sid!='01' 13,Handle“ SC"The scores of the courses taught by "Zhang San" in the table are changed to the average score of this course; #Skip the update topic temporarily 14,I haven't learned"Zhang San"Names of students in any course taught by the teacher SELECT sname FROM student WHERE sid NOT IN ( SELECT DISTINCT sid FROM sc LEFT JOIN course ON sc.cid=course.cid LEFT JOIN teacher ON course.tid=teacher.tid WHERE tname='Zhang San' ) 15,Query the student number, name and average score of students who fail two or more courses SELECT t1.sid,sname,avg_score FROM ( SELECT sid,COUNT(IF(score<60,cid,NULL)),AVG(score) AS avg_score FROM sc GROUP BY sid HAVING COUNT(IF(score<60,cid,NULL)) >=2 )t1 LEFT JOIN student ON t1.sid=student.sid 16,retrieval"01"Student information with a course score less than 60 in descending order SELECT sid,IF(cid='01',score,100) FROM sc WHERE IF(cid='01',score,100)<60 ORDER BY IF(cid='01',score,100) DESC 17,Displays the average scores of all students from high to low SELECT sid,AVG(score) FROM sc GROUP BY sid ORDER BY AVG(score) DESC 18,Query the highest score, lowest score and average score of each subject: displayed in the following form: Course ID，curriculum name，Highest score, lowest score, average score, pass rate SELECT sc.cid ,cname ,MAX(score) max_score ,MIN(score) min_score ,AVG(score) avg_score ,COUNT(IF(score>=60,sid,NULL))/COUNT(sid) pass_rate FROM sc LEFT JOIN course ON sc.cid=course.cid GROUP BY sc.cid 19,According to the order of the average score of each subject from low to high and the percentage of passing rate from high to low #The questions here are sorted according to the average score first, and then according to the passing percentage. The questions are a little strange SELECT cid ,AVG(score) AS avg_score ,COUNT(IF(score>=60,sid,NULL))/COUNT(sid) AS pass_rate FROM sc GROUP BY cid ORDER BY avg_score,pass_rate DESC 20,Query the total score of students and rank them SELECT sid ,SUM(score) AS sum_score FROM sc GROUP BY sid ORDER BY sum_score DESC 21,Query the average score of different courses taught by different teachers from high to low SELECT tid ,AVG(score) AS avg_score FROM course LEFT JOIN sc ON course.cid=sc.cid GROUP BY tid ORDER BY avg_score DESC 22,Query the information of the second to third students in all courses and the course results SELECT sid,rank_num,score,cid FROM ( SELECT rank() over(PARTITION BY cid ORDER BY score DESC) AS rank_num ,sid ,score ,cid FROM sc )t WHERE rank_num IN (2,3) 23,Count the number of students in each score section of each subject: course number,Course name,[100-85],[85-70],[70-60],[0-60]And percentage SELECT sc.cid ,cname , COUNT(IF(score BETWEEN 85 AND 100,sid,NULL))/COUNT(sid) , COUNT(IF(score BETWEEN 70 AND 85,sid,NULL))/COUNT(sid) , COUNT(IF(score BETWEEN 60 AND 70,sid,NULL))/COUNT(sid) , COUNT(IF(score BETWEEN 0 AND 60,sid,NULL))/COUNT(sid) FROM sc LEFT JOIN course ON sc.cid=course.cid GROUP BY sc.cid,cname 24,Query students' average grades and their ranking SELECT sid ,avg_score ,rank() over (ORDER BY avg_score DESC) FROM ( SELECT sid ,AVG(score) AS avg_score FROM sc GROUP BY sid )t 25,Query the top three records of each subject SELECT sid,cid,rank1 FROM ( SELECT cid ,sid ,rank() over(PARTITION BY cid ORDER BY score DESC) AS rank1 FROM sc )t WHERE rank1<=3 26,Query the number of students selected for each course SELECT COUNT(sid) ,cid FROM sc GROUP BY cid 27,Find out the student numbers and names of all students who have taken only one course #Just find out the sid, and don't bother to hand in the student form later SELECT sid FROM sc GROUP BY sid HAVING COUNT(cid) =1 28,Query the number of boys and girls SELECT ssex ,COUNT(DISTINCT sid) FROM student GROUP BY ssex 29,Query name contains"wind"Word student information SELECT sid,sname FROM student WHERE sname LIKE '%wind%' 30,Query the list of same-sex students with the same name and count the number of students with the same name #The title is ambiguous. The quality of this set of questions is a little poor SELECT ssex ,sname ,COUNT(sid) FROM student GROUP BY ssex,sname HAVING COUNT(sid)>=2 31,Check the list of students born in 1990(Note: Student In the table Sage The type of column is datetime) SELECT sid,sname,sage FROM student WHERE YEAR(sage)=1990 32,Query the average score of each course. The results are arranged in ascending order according to the average score. If the average score is the same, it is arranged in descending order according to the course number SELECT cid,AVG(score) AS avg_score FROM sc GROUP BY cid ORDER BY avg_score,cid DESC 37,Query the failed courses and arrange them according to the course number from large to small #Problematic topic SELECT cid,sid,score FROM sc WHERE score<60 ORDER BY cid DESC,sid 38,The query course number is"01"Student number and name of students with course scores of more than 60; SELECT sid,cid,score FROM sc WHERE cid='01' AND score>60 40,Query the names and grades of the students with the highest scores among the students taking the courses taught by teacher Zhang San SELECT sc.sid,sname,cname,score FROM sc LEFT JOIN course ON sc.cid=course.cid LEFT JOIN teacher ON course.tid=teacher.tid LEFT JOIN student ON sc.sid=student.sid WHERE tname='Zhang San' ORDER BY score DESC LIMIT 1; 42,Check the top two with the best grades in each subject SELECT cid,sid,rank1 FROM ( SELECT cid ,sid ,rank() over(PARTITION BY cid ORDER BY score DESC) AS rank1 FROM sc )t WHERE rank1 <=2 43,Count the number of elective students for each course (only for courses with more than 5 people). It is required to output the course number and the number of elective students. The query results are arranged in descending order by the number of students. If the number of students is the same, they are arranged in ascending order by the course number SELECT cid ,COUNT(sid) AS cnt FROM sc GROUP BY cid HAVING cnt>=5 ORDER BY COUNT(sid) DESC,cid 44,Retrieve the student number of at least two courses SELECT sid ,COUNT(cid) FROM sc GROUP BY sid HAVING COUNT(cid)>=2 45,Query the student information of all courses #Not very rigorous, but there should be no problem in practice. If you need to be rigorous, see the train of thought in question 12 SELECT sid ,COUNT(cid) FROM sc GROUP BY sid HAVING COUNT(cid)=(SELECT COUNT(DISTINCT cid) FROM sc) 46,Query the age of each student SELECT sid,sname,YEAR(CURDATE())-YEAR(sage) AS sage FROM student 47,Inquire about the students whose birthday is this week SELECT sid,sname,sage FROM student WHERE WEEKOFYEAR(sage)=WEEKOFYEAR(CURDATE()) 48,Check the students whose birthday is next week SELECT sid,sname,sage FROM student WHERE WEEKOFYEAR(sage) = WEEKOFYEAR(DATE_ADD(CURDATE(),INTERVAL 1 WEEK)) 49,Query students whose birthdays are this month SELECT sid,sname,sage FROM student WHERE MONTH(sage) = MONTH(CURDATE()) 50,Query the students whose birthday is next month SELECT sid,sname,sage FROM student WHERE MONTH(DATE_SUB(sage,INTERVAL 1 MONTH)) = MONTH(CURDATE())