mysql的sql语句优化及效率

0 0

mysql的sql语句优化及效率0

mysql数据库建表语句如下，也可看附件。
一共四张表——A老师表，B课程表，C分数表，D学生表
A与B是一对多
B与C是一对多
C与D是多对一

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50147
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50147
File Encoding         : 65001

Date: 2013-07-24 22:02:32
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `text_course`
-- ----------------------------
DROP TABLE IF EXISTS `text_course`;
CREATE TABLE `text_course` (
  `cno` varchar(255) NOT NULL,
  `cname` varchar(255) NOT NULL,
  `tno` varchar(255) NOT NULL,
  PRIMARY KEY (`cno`,`tno`),
  KEY `tno` (`tno`),
  KEY `cno` (`cno`),
  CONSTRAINT `text_course_ibfk_1` FOREIGN KEY (`tno`) REFERENCES `text_teacher` (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of text_course
-- ----------------------------
INSERT INTO `text_course` VALUES ('c001', 'J2SE', 't002');
INSERT INTO `text_course` VALUES ('c002', 'Java Web', 't002');
INSERT INTO `text_course` VALUES ('c003', 'SSH', 't001');
INSERT INTO `text_course` VALUES ('c004', 'Oracle', 't001');
INSERT INTO `text_course` VALUES ('c005', 'SQL SERVER 2005', 't003');
INSERT INTO `text_course` VALUES ('c006', 'C#', 't003');
INSERT INTO `text_course` VALUES ('c007', 'JavaScript', 't002');
INSERT INTO `text_course` VALUES ('c008', 'DIV+CSS', 't001');
INSERT INTO `text_course` VALUES ('c009', 'PHP', 't003');
INSERT INTO `text_course` VALUES ('c010', 'EJB3.0', 't002');

-- ----------------------------
-- Table structure for `text_sc`
-- ----------------------------
DROP TABLE IF EXISTS `text_sc`;
CREATE TABLE `text_sc` (
  `sno` varchar(255) NOT NULL,
  `cno` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`sno`,`cno`),
  KEY `cno` (`cno`),
  CONSTRAINT `text_sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `text_course` (`cno`),
  CONSTRAINT `text_sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `text_student` (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of text_sc
-- ----------------------------
INSERT INTO `text_sc` VALUES ('s001', 'c001', '79');
INSERT INTO `text_sc` VALUES ('s001', 'c002', '83');
INSERT INTO `text_sc` VALUES ('s001', 'c003', '59');
INSERT INTO `text_sc` VALUES ('s002', 'c001', '81');
INSERT INTO `text_sc` VALUES ('s002', 'c002', '73');
INSERT INTO `text_sc` VALUES ('s003', 'c001', '82');
INSERT INTO `text_sc` VALUES ('s003', 'c002', '82');
INSERT INTO `text_sc` VALUES ('s004', 'c001', '61');

-- ----------------------------
-- Table structure for `text_student`
-- ----------------------------
DROP TABLE IF EXISTS `text_student`;
CREATE TABLE `text_student` (
  `sno` varchar(255) NOT NULL,
  `sname` varchar(255) NOT NULL,
  `sage` int(11) NOT NULL,
  `ssex` varchar(255) NOT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of text_student
-- ----------------------------
INSERT INTO `text_student` VALUES ('s001', '张三', '23', '男');
INSERT INTO `text_student` VALUES ('s002', '李四', '23', '男');
INSERT INTO `text_student` VALUES ('s003', '吴鹏', '25', '男');
INSERT INTO `text_student` VALUES ('s004', '琴沁', '20', '女');
INSERT INTO `text_student` VALUES ('s005', '王丽', '20', '女');
INSERT INTO `text_student` VALUES ('s006', '李波', '21', '男');
INSERT INTO `text_student` VALUES ('s007', '刘玉', '21', '男');
INSERT INTO `text_student` VALUES ('s008', '萧蓉', '21', '女');
INSERT INTO `text_student` VALUES ('s009', '陈萧晓', '23', '女');
INSERT INTO `text_student` VALUES ('s010', '陈美', '22', '女');

-- ----------------------------
-- Table structure for `text_teacher`
-- ----------------------------
DROP TABLE IF EXISTS `text_teacher`;
CREATE TABLE `text_teacher` (
  `tno` varchar(255) NOT NULL,
  `tname` varchar(255) NOT NULL,
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of text_teacher
-- ----------------------------
INSERT INTO `text_teacher` VALUES ('t001', '刘阳');
INSERT INTO `text_teacher` VALUES ('t002', '谌燕');
INSERT INTO `text_teacher` VALUES ('t003', '胡明星');

------------------------------------------------------
现在我要做的查询是：
/*查询没学过“谌燕”老师课的同学的学号、姓名*/

我写个三种查询语句，那么其在性能或其他方面具体的差别在哪里？或者有没有更好的语句或其他建议

具体三个语句如下：

select 
      SNO,SNAME,TNAME
from 
     ( select 
            student.SNO    as SNO,
            student.SNAME  as SNAME,
            student.SAGE   as SAGE,
            student.SSEX   as SSEX,
            score.SCORE    as SCORE,
            course.CNAME   as CNAME,
            teacher.TNAME  as TNAME
       from
            TEXT_STUDENT student right join TEXT_SC score
            on  student.SNO=score.SNO left join TEXT_COURSE  
            course on score.CNO=course.CNO left join
            TEXT_TEACHER teacher on course.TNO=teacher.TNO
    ) oop
where oop.tname!='谌燕'

select 
      student.SNO,student.SNAME,teacher.TNAME
from
      TEXT_STUDENT student right join TEXT_SC score on
                                 student.SNO=score.SNO 
                           left join TEXT_COURSE course on 
                                 score.CNO=course.CNO
                           left join TEXT_TEACHER teacher on 
                                 course.TNO=teacher.TNO
where teacher.tname!='谌燕'

select 
    student.SNO,student.SNAME,teacher.TNAME
from
    TEXT_STUDENT student,
    TEXT_TEACHER teacher,
    TEXT_SC score ,
    TEXT_COURSE course
WHERE student.SNO=score.SNO and 
      score.CNO=course.CNO and 
      course.TNO=teacher.TNO and 
      teacher.tname!='谌燕'