sql查询练习

最新推荐文章于 2023-05-21 19:05:06 发布

一个月只能改一次欧

最新推荐文章于 2023-05-21 19:05:06 发布

阅读量80

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/y943711797/article/details/115916504

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

/*
 Navicat Premium Data Transfer

 Source Server         : dnjs
 Source Server Type    : MySQL
 Source Server Version : 80023
 Source Host           : localhost:3333
 Source Schema         : 查询sql练习

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

 Date: 20/04/2021 19:08:09
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `cid` int(0) NOT NULL AUTO_INCREMENT,
  `caption` varchar(15) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `grade_id` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '一年一班', 1);
INSERT INTO `class` VALUES (2, '二年一般', 2);
INSERT INTO `class` VALUES (3, '三年二班', 3);
INSERT INTO `class` VALUES (4, '一年二班', 1);
INSERT INTO `class` VALUES (5, '一年三班', 1);
INSERT INTO `class` VALUES (6, '一年四班', 1);
INSERT INTO `class` VALUES (7, '一年五班', 1);
INSERT INTO `class` VALUES (8, '一年六班', 1);

-- ----------------------------
-- Table structure for class_grade
-- ----------------------------
DROP TABLE IF EXISTS `class_grade`;
CREATE TABLE `class_grade`  (
  `gid` int(0) NOT NULL AUTO_INCREMENT,
  `gname` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  PRIMARY KEY (`gid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class_grade
-- ----------------------------
INSERT INTO `class_grade` VALUES (1, '一年级');
INSERT INTO `class_grade` VALUES (2, '二年级');
INSERT INTO `class_grade` VALUES (3, '三年级');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cid` int(0) NOT NULL AUTO_INCREMENT,
  `cname` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `teacher_id` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '生物', 1);
INSERT INTO `course` VALUES (2, '体育', 1);
INSERT INTO `course` VALUES (3, '物理', 2);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `sid` int(0) NOT NULL AUTO_INCREMENT,
  `student_id` int(0) NULL DEFAULT NULL,
  `course_id` int(0) NULL DEFAULT NULL,
  `score` double(8, 1) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 60.0);
INSERT INTO `score` VALUES (2, 1, 2, 59.0);
INSERT INTO `score` VALUES (3, 2, 1, 99.0);
INSERT INTO `score` VALUES (4, 2, 3, 100.0);
INSERT INTO `score` VALUES (5, 1, 3, 88.0);
INSERT INTO `score` VALUES (6, 1, 4, 46.0);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(0) NOT NULL,
  `sname` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `gender` enum('男','女') CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `class_id` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '乔丹', '女', 1);
INSERT INTO `student` VALUES (2, '艾弗森', '女', 1);
INSERT INTO `student` VALUES (3, '科比', '男', 2);

-- ----------------------------
-- Table structure for teach2cls
-- ----------------------------
DROP TABLE IF EXISTS `teach2cls`;
CREATE TABLE `teach2cls`  (
  `tcid` int(0) NOT NULL AUTO_INCREMENT,
  `tid` int(0) NULL DEFAULT NULL,
  `cid` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`tcid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teach2cls
-- ----------------------------
INSERT INTO `teach2cls` VALUES (1, 1, 1);
INSERT INTO `teach2cls` VALUES (2, 1, 2);
INSERT INTO `teach2cls` VALUES (3, 2, 1);
INSERT INTO `teach2cls` VALUES (4, 3, 3);

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tid` int(0) NOT NULL,
  `tname` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '张三');
INSERT INTO `teacher` VALUES (2, '李四');
INSERT INTO `teacher` VALUES (3, '王五');

SET FOREIGN_KEY_CHECKS = 1;

查询：
#1利用id查询学生总人数。
SELECT
	count( sid ) 
FROM
	student;
	
	#2先找出生物和物理的科目 在找出科目大于60分的
SELECT
	sid,
	sname 
FROM
	student 
WHERE
	sid IN ( SELECT student_id FROM score WHERE score >= 60 AND course_id IN ( SELECT cid FROM course WHERE cname = "生物" OR cname = "物理" ) );
	
	#3学生表和class表内连接 在和年级表连接
SELECT
	gname,
	count( sname ) AS "年级人数" 
FROM
	student
	INNER JOIN class ON student.class_id = class.cid
	JOIN class_grade ON class.grade_id = class_grade.gid 
GROUP BY
	gname;
	
	#4左连接teacher表和teach2cls表   like实现模糊查询
SELECT
	count( teacher.tname ) AS '姓李的老师的个数',
	count( teach2cls.tid ) AS '带的班级数' 
FROM
	teacher
	LEFT JOIN teach2cls ON teacher.tid = teach2cls.tid 
WHERE
	teacher.tname LIKE '李%' 
GROUP BY
	teacher.tname;
	
	#5内连接class和class_gread 表 对grade_id进行分组利用count筛选出小于五的班级数
SELECT
	gid,
	b.gname AS '年级' 
FROM
	class a
	INNER JOIN class_grade b ON a.grade_id = b.gid 
GROUP BY
	grade_id 
HAVING
	count( caption ) < 5;
	
	#6 内连接四张表 学生表/分数表/老师表/课程表  选出"张三"，对姓名和id分组 筛选出大于2的学生姓名
SELECT
	student.sid,
	student.sname 
FROM
	student
	INNER JOIN score ON score.student_id = student.sid
	INNER JOIN course ON score.course_id = course.cid
	INNER JOIN teacher ON course.teacher_id = teacher.tid 
WHERE
	teacher.tname = '张三' 
GROUP BY
	student.sname,
	student.sid 
HAVING
	count( teacher.tname ) >= 2;#6
SELECT
	student.sid,
	student.sname 
FROM
	student
	RIGHT JOIN score ON student.sid = score.student_id 
	WHERE
	
	#7.使用右连接将分数表和学生表连接，将where分数表中编号为1，2
SELECT
	student.sid,
	student.sname 
FROM
	student
	RIGHT JOIN score ON student.sid = score.student_id 
WHERE
	score.course_id = 1 
	OR score = 2;
	
	#8没有带过高年级学生的id 左连接 teach2cls和teacher表 让cid不等于3，对tid分组
SELECT
	teach2cls.tid,
	teacher.tname 
FROM
	teach2cls
	LEFT JOIN teacher ON teach2cls.tid = teacher.tid 
WHERE
	cid NOT IN ( SELECT cid FROM class WHERE grade_id = 3 ) 
GROUP BY
	tid;