SQL语句测试
来源:互联网 发布:asp读取excel数据 编辑:程序博客网 时间:2024/06/16 20:03
Course(C#,Cname,T#) 课程表 C#,课程编号;Name:课程名字;T#:教师编号
SC(S#,C#,score) 成绩表 S#:学号;C#,课程编号;Score:成绩
Teacher(T#,Tname) 教师表 T#:教师编号; Name:教师名字
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
2、查询没有学全所有课的同学的学号、姓名;
3、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
4、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
5、查询各科成绩前三名的学生
一、select SC1.`S#` from SC SC1 JOIN SC SC2 ON SC1.`S#`=SC2.`S#` WHERE SC1.`C#`='1' AND SC2.`C#`='2' AND SC1.Score > SC2.Score
一、select Student.`S#` from (select `s#`,score from SC where `C#`='1') Student,(select `S#`,score from SC where `C#`='2') Course WHERE Student.`S#`=Course.`S#`
二、select Student.`S#`,Student.`Name` from Student,SC where Student.`S#`=SC.`S#` group by Student.`S#`,Student.`Name` having count(`C#`) < (select count(*) from Course)
三、select `C#` as '课程ID',MAX(Score) as '最高分',MIN(Score) as '最低分' from SC group by `C#`
四、select distinct SC.`S#`,Student.`Name` from SC JOIN Student ON SC.`S#`=Student.`S#` where SC.`S#` not in (select `S#` from sc where `C#` not in (select `C#` from sc where `S#`='2')) AND SC.`S#`<>2 group by SC.`S#` having count(*) = (select count(*) from sc where `S#`='2');
五、SELECT a.`S#` '学生ID',a.`C#` '课程ID',a.Score '分数' FROM sc a WHERE (SELECT COUNT(`C#`) FROM sc WHERE `C#`=a.`C#` AND a.Score<Score)<3 ORDER BY `C#` ASC, Score DESC
/*Navicat MySQL Data TransferSource Server : windows_mysqlSource Server Version : 50547Source Host : 127.0.0.1:3306Source Database : sqlTarget Server Type : MYSQLTarget Server Version : 50547File Encoding : 65001Date: 2016-06-28 21:24:49*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for course-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` ( `C#` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) DEFAULT NULL, `T#` varchar(255) DEFAULT NULL, PRIMARY KEY (`C#`)) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=gbk;-- ------------------------------ Records of course-- ----------------------------INSERT INTO `course` VALUES ('1', '软件工程', '1');INSERT INTO `course` VALUES ('2', '项目优化', '2');INSERT INTO `course` VALUES ('3', '二次开发', '3');INSERT INTO `course` VALUES ('4', '框架实战', '4');INSERT INTO `course` VALUES ('5', 'PHP从入门到放弃', '5');-- ------------------------------ Table structure for sc-- ----------------------------DROP TABLE IF EXISTS `sc`;CREATE TABLE `sc` ( `S#` int(11) DEFAULT NULL, `C#` int(11) DEFAULT NULL, `Score` int(255) DEFAULT NULL) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;-- ------------------------------ Records of sc-- ----------------------------INSERT INTO `sc` VALUES ('1', '1', '50');INSERT INTO `sc` VALUES ('2', '1', '123');INSERT INTO `sc` VALUES ('1', '2', '26');INSERT INTO `sc` VALUES ('2', '2', '5');INSERT INTO `sc` VALUES ('5', '3', '23');INSERT INTO `sc` VALUES ('6', '3', '34');INSERT INTO `sc` VALUES ('7', '4', '56');INSERT INTO `sc` VALUES ('2', '4', '78');INSERT INTO `sc` VALUES ('1', '3', '56');INSERT INTO `sc` VALUES ('1', '4', '123');INSERT INTO `sc` VALUES ('2', '3', '12');-- ------------------------------ Table structure for student-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `S#` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) DEFAULT NULL, `Age` int(11) DEFAULT NULL, `Sex` varchar(255) DEFAULT NULL, PRIMARY KEY (`S#`)) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES ('1', '王浩', '10', '男');INSERT INTO `student` VALUES ('2', '付兴', '11', '女');INSERT INTO `student` VALUES ('3', '刘丹', '11', '男');INSERT INTO `student` VALUES ('4', '赵鑫', '11', '女');INSERT INTO `student` VALUES ('5', '高玉', '11', '男');INSERT INTO `student` VALUES ('6', '李敏', '11', '女');INSERT INTO `student` VALUES ('7', '马军', '11', '男');INSERT INTO `student` VALUES ('8', '江文', '11', '女');-- ------------------------------ Table structure for teacher-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` ( `T#` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) DEFAULT NULL, PRIMARY KEY (`T#`)) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=gbk;-- ------------------------------ Records of teacher-- ----------------------------INSERT INTO `teacher` VALUES ('1', '赵伟');INSERT INTO `teacher` VALUES ('2', '张恩');INSERT INTO `teacher` VALUES ('3', '李朋');INSERT INTO `teacher` VALUES ('4', '张鹏');INSERT INTO `teacher` VALUES ('5', '李明');
- 测试SQL语句执行时间
- 测试sql语句性能
- 测试sql语句性能
- sql语句效率测试
- SQL语句性能测试
- sql 语句性能测试
- 测试SQL语句
- SQL语句测试
- SQL语句执行效率测试
- Loadrunner 测试sql语句性能
- sql 执行语句性能测试
- LoadRunner测试SQL语句性能
- 两表测试SQL语句
- 测试sql语句的执行时间
- 游戏测试中的sql语句
- SQL语句执行效率测试
- 查看sql语句执行时间/测试sql语句性能
- 查看sql语句执行时间/测试sql语句性能
- 一个有序的二维数组,自上而下,自左至右都是升序,判断输入的整数是否在数组中
- Android EditText的TextWatcher监听回调参数详解
- Android EditText实现只能输入几位整数几位小数
- libcurl 使用
- LeetCode - 232. Implement Queue using Stacks
- SQL语句测试
- node 安装 卸载 版本控制
- jms
- 树的非递归前序遍历
- Update 多行数据(将一张表中某列的值更新到另外一张表中)
- pat.求整数段的和
- QQ好友分组添加好友(UITableView实现)
- Java基础与案例开发详解の数组及常用技巧
- Shell 循环语句:if,case esac,for,while