mysql高级查询篇！修改表名、添加表、修改和删除字段，设置主键、外键关联，子查询.....

示例1：修改表名、添加表、修改和删除字段

   

DROP TABLE IF EXISTS `demo01`;#存在就删除
#创建表
CREATE TABLE IF NOT EXISTS `demo01`(
   `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `name` VARCHAR(8) NOT NULL
);
#修改表名
ALTER TABLE `demo01` RENAME `demo02`;
#添加字段
ALTER TABLE demo02 ADD `password` VARCHAR(32) NOT NULL;
#修改字段
ALTER TABLE demo02 CHANGE `name` `username`CHAR(10) NOT NULL;
#删除字段
ALTER TABLE demo02 DROP `password`;

示例2：设置主键、外键关联

#将grade表中的gradeId设置为主键
ALTER TABLE `grade` ADD CONSTRAINT `pk_grade` PRIMARY KEY `grade`(`gradeId`);

#设置student表的gradId字段与grade表的gradeId字段建立主外键关联
ALTER TABLE `student`  ADD  CONSTRAINT fk_student_grade FOREIGN KEY(`gradeId`) REFERENCES `grade` (`gradeId`);

 

示例3：将查询结果插入新表

CREATE TABLE `phoneList`(
SELECT `studentName`,`phone`
FROM `student`);
 

示例4：使用LIMIT子句查询学生信息

#显示前4条记录
SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` 
FROM `student`
WHERE `gradeId` = 1
ORDER BY studentNo
LIMIT 4;

#从第1条记录开始显示，如果每页显示4条数据，要求显示第2页全部数据，经过计算，应从第5条记录开始显示4条数据
SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` 
FROM `student`
WHERE `gradeId` = 1
ORDER BY studentNo
LIMIT 4，4;

示例5：分步实现查询年龄比“李斯文”大的学生

#查找出“李斯文”的出生日期
SELECT `bornDate` FROM `student` WHERE `studentname` = '李斯文';
#利用WHERE语句筛选出生日期比“李斯文”大的学生
SELECT studentNo, studentName,sex,bornDate,address FROM `student` WHERE bornDate > '1993-07-23';

 

示例7：用表连接查询成绩是60分的学生

#使用表连接实现查询至少一次Logic Java成绩刚好等于60分的学生名单
SELECT `studentName` FROM `student` stu 
 INNER JOIN `result` r ON stu.studentNO = r.studentNo         
 INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo     
WHERE `studentResult` = 60 AND `subjectName` = 'Logic Java';

 

示例8：子查询实现查询成绩是60分的学生

#使用子查询实现查询Logic Java成绩至少一次刚好等于60分的学生名单
SELECT `studentName` FROM `student` WHERE `studentNo` = (
    SELECT `studentNo` FROM `result` 
        INNER JOIN `Subject` ON result.subjectNo= subject.subjectNo 
        WHERE `studentResult`=60 AND `subjectName`='Logic Java'
);

示例9：IN子查询

#使用IN子查询实现查询Logic Java成绩至少一次刚好等于60分的学生名单
SELECT `studentName` FROM `student` 
WHERE `studentNo` IN(
    SELECT `studentNo` FROM `result` 
    WHERE `subjectNo` =  (
        SELECT `subjectNo` FROM `subject`
        WHERE `subjectName`='Logic Java'
    )AND `studentResult` = 60  
);

示例10：IN子查询2

/*采用IN子查询获得参加考试的在读学生名单*/
SELECT `studentNo`, `studentName` FROM `student` WHERE `studentNo` 
IN(
  SELECT `studentNo` FROM `result` 
  WHERE `subjectNo` = (
      #获得参加Java Logic课程最近一次考试的学生学号
      SELECT `subjectNo` FROM `subject` 
      WHERE `subjectName`='Logic Java'
   ) AND `examDate` = (
        #获得Logic Java课程最近一次的考试日期
        SELECT MAX(`examDate`) FROM `result` 
        WHERE `subjectNo` = ( 
             #获得Logic Jav课程的课程编号
             SELECT `subjectNo` FROM `subject` 
             WHERE `subjectName`='Logic Java'  
        ) 
   )
);

示例11：使用NOT IN子查询

/*查询得到未参加Logic Java课程最近一次考试在读学生姓名的功能*/
SELECT `studentNo`, `studentName` FROM `student` WHERE `studentNo` 
NOT IN(
  SELECT `studentNo` FROM `result` 
  WHERE `subjectNo` = (
      #获得参加Logic Java课程最近一次考试的学生学号
      SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java'
   ) AND `examDate` = (
        #获得Logic Java课程最近一次的考试日期
        SELECT MAX(`examDate`) FROM `result`  WHERE `subjectNo` = ( 
             #获得Logic Java课程的课程编号
             SELECT `subjectNo` FROM `subject` 
             WHERE `subjectName`='Logic Java') 
   )
)
AND `gradeID` = (
   SELECT `gradeID` FROM `subject` 
   WHERE `subjectName` = 'Logic Java'
);