mysql的sql语句练习

数据库sql：

/*
Navicat MySQL Data Transfer

Source Server         : local
Source Server Version : 50527
Source Host           : localhost:3306
Source Database       : test

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

Date: 2017-04-10 13:58:44
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `CID` int(255) NOT NULL AUTO_INCREMENT,
  `CNAME` varchar(255) DEFAULT NULL,
  `TID` int(255) DEFAULT NULL,
  PRIMARY KEY (`CID`),
  KEY `T#` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '001', null);
INSERT INTO `course` VALUES ('2', '002', null);

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `SID` int(255) DEFAULT NULL,
  `CID` int(255) DEFAULT NULL,
  `SCORE` int(255) DEFAULT NULL,
  KEY `SID` (`SID`),
  KEY `CID` (`CID`),
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`SID`) REFERENCES `student` (`SID`),
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`CID`) REFERENCES `course` (`CID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '100');
INSERT INTO `sc` VALUES ('1', '2', '90');
INSERT INTO `sc` VALUES ('2', '1', '90');
INSERT INTO `sc` VALUES ('2', '2', '100');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `SID` int(255) NOT NULL AUTO_INCREMENT,
  `SNAME` varchar(255) DEFAULT NULL,
  `SAGE` int(255) DEFAULT NULL,
  `SSEX` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`SID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '小明', null, null);
INSERT INTO `student` VALUES ('2', '小红', null, null);

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `TID` int(255) NOT NULL AUTO_INCREMENT,
  `TNAME` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '张久帅');
INSERT INTO `teacher` VALUES ('2', '王天');

练习：

1.查询”001“课程比”002“课程成绩高的所有学生的姓名、”001“成绩、”002“成绩。（纵向转横向进行比较），建议采用方法二
方法一：

（1）步骤：
    （1）查出所有学生的所有科目的所有成绩（new_tab）；
    （2）从new_tab表中查出”001“课程和”002“课程课程的成绩，并把这两个表进行关联，通过sid,这样就可以将同一列（字段）的不同科目的成绩放在同一行（纵向转横向）；
    （3）添加比较条件
select
    score1.sid,
    score1.sname,
    score1.score001, 
    score2.score002 
from 
    (
        select
            new_tab.sid,
            new_tab.sname,
            new_tab.score score001 
        from 
            (
                SELECT 
                    s.sid,
                    s.sname, 
                    sc.score,
                    c.cid, c.cname 
                FROM 
                    STUDENT S 
                    LEFT JOIN 
                    SC 
                    ON S.SID=SC.SID 
                    LEFT JOIN 
                    COURSE C 
                    ON SC.CID=C.CID
            ) new_tab 
        where 
            new_tab.cname='001'
    ) score1 
    left join 
    (
        select 
            new_tab.sid, 
            new_tab.sname,
            new_tab.score score002 
        from 
            (
                SELECT 
                    s.sid, 
                    s.sname, 
                    sc.score, 
                    c.cid,
                    c.cname 
                FROM 
                    STUDENT S 
                    LEFT JOIN
                    SC 
                    ON S.SID=SC.SID 
                    LEFT JOIN 
                    COURSE 
                    C 
                    ON SC.CID=C.CID
            ) new_tab 
        where 
            new_tab.cname='002'
    ) score2 
    on 
    score1.sid=score2.sid 
where 
score1.score001>score2.score002;

（2）步骤：
    （1）查出所有学生的”001“课程的成绩（score1）和”002“课程的成绩（score2）；
    （2）并把这两个表进行关联，通过sid,这样就可以将同一列（字段）的不同科目的成绩放在同一行（纵向转横向）；
    （3）添加比较条件

SELECT
    score1.sid,
    score1.sname,
    score1.score001,
    score2.score002
FROM
    (
        SELECT
            s.sid,
            s.sname,
            sc.score score001,
            c.cid,
            c.cname
        FROM
            STUDENT S
        LEFT JOIN SC ON S.SID = SC.SID
        LEFT JOIN COURSE C ON SC.CID = C.CID
        WHERE
            c.cname = '001'
    ) score1
LEFT JOIN (
    SELECT
        s.sid,
        s.sname,
        sc.score score002,
        c.cid,
        c.cname
    FROM
        STUDENT S
    LEFT JOIN SC ON S.SID = SC.SID
    LEFT JOIN COURSE C ON SC.CID = C.CID
    WHERE
        c.cname = '002'
) score2 ON score1.sid = score2.sid
WHERE
    score1.score001 > score2.score002;



方法二：（最简单最实用方法，第一种方法有些冗余啰嗦）
步骤：
    （1）SC表进行自关联；
    （2）自关联表SC1条件查询”001“成绩，自关联表SC2条件查询”002“成绩，这样就科目纵向转横向，并进行比较；

SELECT
    SC1.SID, S.SNAME, SC1.SCORE score001, SC2.SCORE score001
FROM
    student S 
JOIN SC SC1 ON S.SID = SC1.SID
JOIN SC SC2 ON SC1.SID = SC2.SID
WHERE
    SC1.CID = (
        SELECT
            CID
        FROM
            COURSE C
        WHERE
            C.CNAME = '001'
    )
AND SC2.CID = (
    SELECT
        CID
    FROM
        COURSE C
    WHERE
        C.CNAME = '002'
)
AND SC1.SCORE > SC2.SCORE;

2.查询学过叶平老师所教的所有课程的同学的学号、姓名和成绩；
SELECT * FROM TEACHER T LEFT JOIN COURSE C ON T.TID=C.TID LEFT JOIN SC ON SC.CID=C.CID LEFT JOIN STUDENT S ON S.SID=SC.SID WHERE T.TNAME='叶平';


3.查询每门课程成绩最好的前两名；
mysql:
相关子查询：主要是子查询用到了主查询的数据，主查询执行一次子查询根据主查询的数据进行查询；
非相关子查询：子查询能够独立进行，且只执行一次；
解析下面语句：
外层为主查询，外层查出一行数据，子查询根据主查询的数据进行条件查询，并计数如果满足条件的数量小于等于2则证明外层查询的数据满足前两名的条件打印出数据，相反，则不满足忽略；
select  sid,cid,score
from sc r1       
where  (select count(1) from sc r2 where r2.cid=r1.cid and r2.score >= r1.score) <=2;
oracle: