MySQL-高级查询

DROP TABLE money;

/*
高级查询：对原始数据进行处理和分析
聚合函数：
1.count():计数、统计
2.max()/min():最大值/最小值
3.avg():平均数
4.sum():求和
5.group_concat():列举该部分所有值
常和group by子句联合使用：分组查询
*/

CREATE DATABASE xhdb;
USE xhdb;
CREATE TABLE class(
cid INT PRIMARY KEY,-- 主键
cname VARCHAR(10) NOT NULL -- 班级名称
);
CREATE TABLE stu(
sid INT PRIMARY KEY,-- 主键
sname VARCHAR(10) NOT NULL,
ssex ENUM('男','女'),
sage TINYINT,
sscore INT,
sclass INT,
CONSTRAINT sclass FOREIGN KEY (sclass) REFERENCES class(cid)
);
INSERT INTO class VALUES (1,'大互2001'),(2,'电商2001'),
(3,'电商2101'),(4,'电竞2001');
SELECT * FROM class;
DESC stu;
INSERT INTO stu VALUES (101,'李国','男',20,150,1),
(102,'李波','男',20,150,2),
(103,'宁瑶','女',21,170,3),
(104,'陈平安','男',18,158,4),
(105,'周钰静','女',19,160,3),
(106,'高显梅','女',22,170,1),
(107,'陈伟','男',21,175,2),
(108,'周振','男',23,166,4),
(109,'王恒','男',17,159,2);
INSERT INTO stu VALUES (110,'罗翔',NULL,NULL,NULL,4);
SELECT * FROM stu,sname,sclass FROM stu;
-- 查出每个学生的班级名字:从多表中查询数据
SELECT sid,sname,sclass,cname FROM stu,class
WHERE stu.`sclass`=class.`cid`;
/*
统计分析：
count(*):统计所有字段值
count(A):统计A字段的所有值
select count(),字段 from 表1 表2 
where 条件
group by 分组字段;
 */
 -- 统计所有
SELECT COUNT(*) FROM stu;
SELECT COUNT(sage) FROM stu;
-- 分组统计:统计各个性别的人数
SELECT COUNT(ssex),ssex FROM stu GROUP BY ssex;
-- 分组统计：统计各班级人数
SELECT COUNT(sid),sclass,cname FROM stu,class
WHERE stu.`sclass`=class.`cid`
GROUP BY sclass;
-- 分组统计：统计各班级男女生人数
-- GROUP_CONCAT(字段名)：列举该分组下所有的值
SELECT COUNT(*),ssex,sclass,cname FROM stu,class 
WHERE stu.`sclass`=class.`cid`
GROUP BY ssex,sclass DESC ORDER BY sclass;
SELECT * FROM stu;
-- 最大值
SELECT MAX(sage) FROM stu;
-- 最小值
SELECT MIN(sage) FROM stu;
-- 总和
SELECT SUM(sscore) FROM stu;
-- 平均数
SELECT AVG(sscore) FROM stu;
SELECT MAX(sage) FROM stu;

SELECT * FROM stu;
SELECT sname,sage FROM stu
WHERE sage=(SELECT MAX(sage)FROM stu);

-- 各班级分数在100~130之间的学生个数并列出所有学生名字
SELECT COUNT(*)AS "人数",GROUP_CONCAT(sname),
AS "分数在100~130之间" FROM stu,
WHERE sscore BETWEEN 100 AND 130;