MySQL advanced query understanding and usage examples

1. Word part

①constraint constraint②foreign foreign key③references reference

④subquery subquery⑤inner internal⑥join connection

2. Preview part

1. What are the keywords for modifying the table SQL statement?

RENAME Modify the table name CHANGE Modify the fields

2. Which keyword can return the query result set according to the specified number of rows

LIMIT

3. Can table connections be replaced with subqueries?

Yes

3. Exercise part

1. Create a data table and implement the query Table modification operation

#On-line computer 1
USE test;
CREATE TABLE person(
number INT(4) AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL,
sex CHAR(2),
bornDate DATETIME
);
ALTER TABLE person RENAME tb_person;
ALTER TABLE tb_person DROP `bornDate`;
ALTER TABLE tb_person ADD bornDate DATETIME ;
ALTER TABLE tb_person CHANGE number id BIGINT;

2. Use SQL statements to add constraints to the result table in the myschool database

#Laboratory 2
USE myschool;
ALTER TABLE result ADD CONSTRAINT re PRIMARY KEY result(`studentNo`,`subjectNo`,`exameDate`);
ALTER TABLE result ADD CONSTRAINT fk_result_student FOREIGN KEY (studentNo) REFERENCES student (studentNo);

3. Add data to the student table, subject table, and grade table

#Go to the computer 3
USE myschool;
INSERT INTO `subject` (`subjectName`, `classHour`, `gradeID`) VALUES
('HTML', '160', '1'),
('Java OOP', '230', '2');

4. Modify the student table and subject table Data

#USE myschool;
INSERT INTO `myschool`.`result` (`studentNo`, `subjectNo`, `exameDate`, `studentResult`) VALUES ('1002 ', '1', '2014-8-8', '78'),
('1003', '2', '2017-7-7', '98'),
('1004 ', '1', '2015-8-8', '78'),
('1005', '2', '2013-8-8', '78'),
('1006 ', '1', '2017-8-8', '66'),
('1007', '1', '2014-8-8', '55'),
('1008 ', '1', '2014-8-8', '13'),
('1009', '1', '2014-8-8', '89');
UPDATE student SET eamil='stu200000@163.com',loginPwd='000' WHERE studentNo='20000';
UPDATE `subject` SET `classHour`=`classHour`-10 WHERE `classHour`>200 AND `subjectNo` =1;
DROP TABLE IF EXISTS student_grade1;
CREATE TABLE student_grade1(SELECT `studentName`,`sex`,`bornDate`,`phone` FROM student WHERE `gradeID`=1);

5. Query student information (query the student numbers and scores of the top five students on February 17, 2016)

#Go to the computer 5

SELECT `studentNo`,`studentResult` FROM `result` WHERE `exameDate`<'2016-2-17' ORDER BY studentResult DESC LIMIT 5;

SELECT studentName,(YEAR(NOW())-YEAR(bornDate)) AS age,bornDate,phone FROM student

WHERE sex='女'
ORDER BY bornDate ASC
LIMIT 1,6;

SELECT YEAR(bornDate) AS nian,COUNT(studentNo) AS num FROM student GROUP BY bornDate HAVING COUNT (studentNo)>=2;

SELECT MAX(`studentResult`),MIN(`studentResult`),AVG(`studentResult`) FROM `result` WHERE `exameDate`='2016-02-17 ' GROUP BY `studentNo`;

6. Query the test results of the specified student

#Go to the computer 6

SELECT MAX(`studentResult`),MIN(`studentResult`) FROM result
WHERE `exameDate`=(SELECT `exameDate` FROM `result` ORDER BY exameDate DESC LIMIT 1) AND
`subjectNo`=(SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java' );
#select max(exameDate) from result

7. Query the courses offered in a certain semester

#手机7

SELECT subjectName FROM ` subject`
WHERE subjectNo IN(SELECT subjectNo FROM `subject`
WHERE gradeId=(SELECT gradeId FROM grade WHERE gradeName='S1'));

8. Query the most recent exam absence for a course List of students who took the exam

#On the computer 8

SELECT `studentName` FROM student WHERE `studentNo` IN(SELECT `studentNo` FROM student WHERE studentNo NOT IN(SELECT `studentNo` FROM `result`
WHERE `subjectNo`=(SELECT `subjectNo` FROM `subject` WHERE `subjectName`='HTML') AND
`exameDate`=(SELECT `exameDate` FROM `result` WHERE subjectNo=(SELECT `subjectNo` FROM `subject` WHERE `subjectName`='HTML')
ORDER BY exameDate DESC LIMIT 1)));

5. Summary part

Two ways to implement multi-table joint query ：

①Table connection

②Subquery

The above is the detailed content of MySQL advanced query understanding and usage examples. For more information, please follow other related articles on the PHP Chinese website!