sql语句练习

字段函数

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

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

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

max()　　//获取最大值

min()　　//获取最小值

distinct() //聚合字段值,

round()  //四舍五入;

条件筛选

group by st.s_id having AVG(sc.s_score)>=60　　　　//group by聚合  //having聚合里面的条件 

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', '王五');

1.练习

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

SELECT
a.s_id,
a.s_name,
b.s_score AS '01课程',
c.s_score AS '02课程'
FROM
Student a
LEFT JOIN Score b ON a.s_id = b.s_id
LEFT JOIN Score c ON c.s_id = a.s_id
where b.s_score > c.s_score

（二）、查询学生的发帖最高数的前三条

SELECT
a.s_id,
a.s_name,
count(b.s_id) AS num
FROM
Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY
s_id
ORDER BY
num DESC
LIMIT 3

和以上一样结果，用了子句查询

SELECT
*
FROM
Student a
WHERE
a.s_id IN (
SELECT
b.s_id
FROM
Score b
GROUP BY
s_id
ORDER BY
count(b.s_id) DESC
)
LIMIT 3

字段用子查询,字段列只能输出一列，否则会出错

SELECT
a.*, (
SELECT
max(b.s_score)
FROM
Score b
WHERE
a.s_id = b.s_id
GROUP BY
s_id
LIMIT 1
) num
FROM
Student a

合并两个表相同列数的结果

方法一、select * from Course
union all
select * from Score

方法二、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