sql查询

sql查询

sqlmysqlselect

 

 

表的结构

Student(sno,sname,ssex,sage,sdept)

Course(cno,cname,credit)

SC(sno,cno,grade）

建立上述表单，使用Navicat

 

ER图

 

查询要求

询所有同学的选课情况，属性显示“姓名”和“课程名”

方法一

简单的连接查询

语句

SELECT s.sname as '姓名', c.cname as '课程名' from student as s ,course as c,sc where s.sno=sc.sno and c.cno=sc.cno ORDER BY s.sno

结果

 

结果一

 

上述分析可以知道，姓名为1的学生选择了课程名为a，b,c的课程。

方法二

简单的子查询
经过仔细考虑，由于这个是要查询所同学的选课纪路所以这种方式，没有意义

sql文件

/*
 Navicat Premium Data Transfer

 Source Server         : how
 Source Server Type    : MySQL
 Source Server Version : 80017
 Source Host           : localhost:3306
 Source Schema         : student

 Target Server Type    : MySQL
 Target Server Version : 80017
 File Encoding         : 65001

 Date: 26/03/2020 19:45:21
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cno` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `ccredit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('a', 'a', 'a');
INSERT INTO `course` VALUES ('b', 'b', 'b');
INSERT INTO `course` VALUES ('c', 'c', 'c');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `sno` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `cno` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `grade` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sno`, `cno`) USING BTREE,
  INDEX `cno`(`cno`) USING BTREE,
  CONSTRAINT `c_cno` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `c_sno` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', 'a ', '1');
INSERT INTO `sc` VALUES ('1', 'b', '1');
INSERT INTO `sc` VALUES ('1', 'c', '1');
INSERT INTO `sc` VALUES ('2', 'a', '2');
INSERT INTO `sc` VALUES ('2', 'b', '2');
INSERT INTO `sc` VALUES ('2', 'c', '2');
INSERT INTO `sc` VALUES ('3', 'c', '3');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sno` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `ssex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sage` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sdept` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '1', '1', '1', '1');
INSERT INTO `student` VALUES ('2', '2', '2', '2', '2');
INSERT INTO `student` VALUES ('3', '3', '3', '3', '3');
INSERT INTO `student` VALUES ('4', '4', '4', '4', '4');
INSERT INTO `student` VALUES ('5', '5', '5', '5', '5');
INSERT INTO `student` VALUES ('6', '6', '6', '6', '6');

SET FOREIGN_KEY_CHECKS = 1;