mysql 高级查询总结_MySQL高级查询之理解与使用实例

一.单词部分

①constraint约束②foreign外键③references参考

④subquery子查询⑤inner内部的⑥join连接

二.预习部分

1.修改表SQL语句的关键字是什么

RENAME 修改表名CHANGE修改字段

2.哪个关键字可以按指定行数返回查询结果集

LIMIT

3.表连接都可以用子查询替换吗

可以

三.练习部分

1. 创建数据表，并实现对表的修改操作

#上机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.使用SQL语句为myschool数据库中的result表添加约束

#上机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.为学生表，科目表，成绩表添加数据

#上机3

USE myschool;

INSERT INTO `subject` (`subjectName`, `classHour`, `gradeID`) VALUES

('HTML', '160', '1'),

('Java OOP', '230', '2');

4.修改学生表，科目表数据

#上机4

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.查询学生信息(查询2016年2月17日前五名 的学生的学号和分数)

#上机5

SELECT `studentNo`,`studentResult` FROM `result` WHERE `exameDate`

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.查询指定学生的考试成绩

#上机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.查询某学期开设的课程

#上机7

SELECT subjectName FROM `subject`

WHERE subjectNo IN(SELECT subjectNo FROM `subject`

WHERE gradeId=(SELECT gradeId FROM grade WHERE gradeName='S1'));

8.查询某课程最近一次考试缺考的学生名单

#上机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)));

五.总结部分

多表联查实现的两种方式：

①表连接

②子查询