sql语句练习

字段函数

sum()　　  //合并相加字段值

count()　　//合并统计字段条数

avg()　　  //获取字段值平均数

max()　　//获取最大值

min()　　//获取最小值

distinct() //聚合字段值,

round()  //四舍五入;

 

DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course` (
`c_id` varchar(20) COLLATE utf8mb4_bin NOT NULL,
`c_name` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`t_id` varchar(20) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Records of Course
-- ----------------------------
INSERT INTO `Course` VALUES ('01', '语文', '02');
INSERT INTO `Course` VALUES ('02', '数学', '01');
INSERT INTO `Course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for Score
-- ----------------------------
DROP TABLE IF EXISTS `Score`;
CREATE TABLE `Score` (
`s_id` varchar(20) COLLATE utf8mb4_bin NOT NULL,
`c_id` varchar(20) COLLATE utf8mb4_bin NOT NULL,
`s_score` int(3) DEFAULT NULL,
PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Records of Score
-- ----------------------------
INSERT INTO `Score` VALUES ('01', '01', '80');
INSERT INTO `Score` VALUES ('01', '02', '90');
INSERT INTO `Score` VALUES ('01', '03', '99');
INSERT INTO `Score` VALUES ('02', '01', '70');
INSERT INTO `Score` VALUES ('02', '02', '60');
INSERT INTO `Score` VALUES ('02', '03', '80');
INSERT INTO `Score` VALUES ('03', '01', '80');
INSERT INTO `Score` VALUES ('03', '02', '80');
INSERT INTO `Score` VALUES ('03', '03', '80');
INSERT INTO `Score` VALUES ('04', '01', '50');
INSERT INTO `Score` VALUES ('04', '02', '30');
INSERT INTO `Score` VALUES ('04', '03', '20');
INSERT INTO `Score` VALUES ('05', '01', '76');
INSERT INTO `Score` VALUES ('05', '02', '87');
INSERT INTO `Score` VALUES ('06', '01', '31');
INSERT INTO `Score` VALUES ('06', '03', '34');
INSERT INTO `Score` VALUES ('07', '02', '89');
INSERT INTO `Score` VALUES ('07', '03', '98');

-- ----------------------------
-- Table structure for Student
-- ----------------------------
DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student` (
`s_id` varchar(20) COLLATE utf8mb4_bin NOT NULL,
`s_name` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`s_birth` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`s_sex` varchar(10) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Records of Student
-- ----------------------------
INSERT INTO `Student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `Student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `Student` VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `Student` VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `Student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `Student` VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `Student` VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO `Student` VALUES ('08', '王菊', '1990-01-20', '女');

-- ----------------------------
-- Table structure for Teacher
-- ----------------------------
DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher` (
`t_id` varchar(20) COLLATE utf8mb4_bin NOT NULL,
`t_name` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Records of Teacher
-- ----------------------------
INSERT INTO `Teacher` VALUES ('01', '张三');
INSERT INTO `Teacher` VALUES ('02', '李四');
INSERT INTO `Teacher` VALUES ('03', '王五');

 

( 一)、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT
    a.*, b.s_score AS '01课程',
    c.s_score '02课程'
FROM
    Student a,
    Score b,
    Score c
WHERE
    a.s_id = b.s_id
AND a.s_id = c.s_id
AND b.c_id = '01'
AND c.c_id = '02'
AND b.s_score > c.s_score;

 

（二）、查询 平均成绩大于等于60分的同学 的 学生编号和学生姓名和平均成绩

SELECT
    a.s_id,a.s_name,avg(b.s_score) avgscore
FROM
    student a
LEFT JOIN score b ON a.s_id = b.s_id
GROUP BY b.s_id
HAVING AVG(b.s_score)>= 60

//条件筛选
//group by b.s_id HAVING AVG(b.s_score)>=60　　　　//group by聚合  //having聚合里面的条件 

 

(三)、查询每个学生最高的一门课程分数

//字段用子查询,字段列只能输出一列，否则会出错
SELECT
    a.*, (
        SELECT
            max(s_score)
        FROM
            score b
        where a.s_id = b.s_id
        GROUP BY
            s_id
        LIMIT 1
    ) num
FROM
    student a

 

(四)、横向和纵向合并两个表的结果集方法

//1.横向
SELECT
    *
FROM
    course
UNION ALL
SELECT
    *
FROM
    course

//2.纵向
SELECT * FROM Course a,Course b

 

连表修改数据

Update Course join (
select b.c_id,avg(b.order_number) as num from Course b group by b.t_id
) c set Course.order_number = c.num where Course.c_id = c.c_id