使用mysql数据库使用游标进行数据分析,例如计算全校男女比例、各院系的学生人数、各年龄段学生人数
时间: 2023-06-06 11:05:09 浏览: 148
感谢您的提问。我可以提供关于使用MySQL数据库进行数据分析的建议和指导。使用游标可以方便地对数据进行逐行处理,但是也可以使用其他MySQL语句和函数来实现您所需要的数据分析功能,比如SUM、COUNT、GROUP BY等等。对于计算全校男女比例、各院系的学生人数和各年龄段学生人数,您可以使用GROUP BY语句和相应的聚合函数来实现。如果您需要更具体的技术支持,我建议您可以在技术社区或者数据库论坛询问。希望对您有所帮助。
相关问题
用mysql数据库创建一个包含学生信息的表格,包括学生姓名、学号、性别、年龄、所在院系、所在班级、出生日期等字段;插入若干条学生信息;使用游标进行数据分析,例如计算全校男女比例、各院系的学生人数、各年龄段学生人数等。
好的,关于你的问题,我已经明白了你的要求。以下是用mysql创建学生信息表格以及查询各种数据的语句:
1.创建学生信息表格的SQL语句:
CREATE TABLE student_info
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
student_id VARCHAR(20) NOT NULL,
gender VARCHAR(5) NOT NULL,
age INT NOT NULL,
department VARCHAR(20) NOT NULL,
class VARCHAR(20) NOT NULL,
birth DATE NOT NULL
);
2.插入学生信息的SQL语句:
INSERT INTO student_info (name, student_id, gender, age, department, class, birth) VALUES
('张三', '1001', '男', 20, '计算机科学技术系', '软件2020', '2000-01-01'),
('李四', '1002', '男', 21, '计算机科学技术系', '软件2021', '1999-02-02'),
('王五', '1003', '女', 19, '电子信息工程系', '电信2019', '2001-03-03'),
('赵六', '1004', '女', 22, '电子信息工程系', '电信2018', '1998-04-04');
3.使用游标查询全校男女比例的SQL语句:
DELIMITER //
CREATE PROCEDURE sp_query_gender_ratio()
BEGIN
DECLARE male_cnt INT;
DECLARE female_cnt INT;
DECLARE total_cnt INT;
DECLARE gender_ratio DECIMAL(4,2);
SELECT COUNT(*) INTO total_cnt FROM student_info;
SELECT COUNT(*) INTO male_cnt FROM student_info WHERE gender='男';
SET female_cnt = total_cnt - male_cnt;
SET gender_ratio = male_cnt/total_cnt;
SELECT CONCAT('全校男女比例为:', FLOOR(gender_ratio*100), '%/', FLOOR((1-gender_ratio)*100), '%') AS res;
END //
DELIMITER ;
CALL sp_query_gender_ratio();
4.使用游标查询各院系学生人数的SQL语句:
DELIMITER //
CREATE PROCEDURE sp_query_department_cnt()
BEGIN
DECLARE department_name VARCHAR(20);
DECLARE department_cnt INT;
DECLARE cur_department CURSOR FOR SELECT DISTINCT department FROM student_info;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @finished = 1;
DROP TEMPORARY TABLE IF EXISTS tmp_department_cnt;
CREATE TEMPORARY TABLE tmp_department_cnt (department_name VARCHAR(20) NOT NULL, department_cnt INT NOT NULL);
OPEN cur_department;
REPEAT
FETCH cur_department INTO department_name;
IF NOT @finished THEN
SET @cnt_sql = CONCAT('SELECT COUNT(*) INTO @cnt FROM student_info WHERE department="', department_name, '"');
PREPARE cnt_stmt FROM @cnt_sql;
EXECUTE cnt_stmt;
INSERT INTO tmp_department_cnt VALUES(department_name, @cnt);
END IF;
UNTIL @finished END REPEAT;
CLOSE cur_department;
SELECT department_name, department_cnt FROM tmp_department_cnt ORDER BY department_cnt DESC;
END //
DELIMITER ;
CALL sp_query_department_cnt();
5.使用游标查询各年龄段学生人数的SQL语句:
DELIMITER //
CREATE PROCEDURE sp_query_age_cnt()
BEGIN
DECLARE age_group VARCHAR(20);
DECLARE age_cnt INT;
DECLARE cur_age CURSOR FOR SELECT DISTINCT CONCAT(FLOOR(age/10)*10, '岁及以下') age_group FROM student_info ORDER BY age ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @finished = 1;
DROP TEMPORARY TABLE IF EXISTS tmp_age_cnt;
CREATE TEMPORARY TABLE tmp_age_cnt (age_group VARCHAR(20) NOT NULL, age_cnt INT NOT NULL);
OPEN cur_age;
REPEAT
FETCH cur_age INTO age_group;
IF NOT @finished THEN
SET @cnt_sql = CONCAT('SELECT COUNT(*) INTO @cnt FROM student_info WHERE FLOOR(age/10)*10 <= ', SUBSTRING_INDEX(age_group,'岁及以下', 1), ' AND FLOOR(age/10)*10 > ', SUBSTRING_INDEX(age_group,'岁及以下', 1)-10);
PREPARE cnt_stmt FROM @cnt_sql;
EXECUTE cnt_stmt;
INSERT INTO tmp_age_cnt VALUES(age_group, @cnt);
END IF;
UNTIL @finished END REPEAT;
CLOSE cur_age;
SELECT age_group, age_cnt FROM tmp_age_cnt ORDER BY age_group ASC;
END //
DELIMITER ;
CALL sp_query_age_cnt();
希望这些SQL语句能够帮到你。如果你有任何疑问,请提出。
1. 创建一个包含学生信息的表格,包括学生姓名、学号、性别、年龄、所在院系、所在班级、出生日期等字段; 2. 插入若干条学生信息; 3. 使用游标进行数据分析,例如计算全校男女比例、各院系的学生人数、各年龄段学生人数等。
这是一个关于数据库的问题,我可以回答。
1. 创建学生信息表格:
CREATE TABLE student_info (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
student_id VARCHAR(20) NOT NULL UNIQUE,
sex ENUM('男', '女') NOT NULL,
age INT NOT NULL,
department VARCHAR(20) NOT NULL,
class VARCHAR(20) NOT NULL,
birthdate DATE NOT NULL
);
2. 插入若干条学生信息:
INSERT INTO student_info (name, student_id, sex, age, department, class, birthdate)
VALUES
('张三', '1001', '男', 20, '计算机科学与技术', '1班', '2001-01-01'),
('李四', '1002', '女', 19, '计算机科学与技术', '2班', '2002-02-02'),
('王五', '1003', '男', 21, '化学与环境科学', '1班', '2000-03-03'),
('赵六', '1004', '女', 20, '化学与环境科学', '2班', '2001-04-04'),
('钱七', '1005', '男', 18, '物理与电子科学', '3班', '2003-05-05');
3. 使用游标进行数据分析,例如计算全校男女比例、各院系的学生人数、各年龄段学生人数等:
-- 计算全校男女比例
DECLARE @total_male INT;
DECLARE @total_female INT;
SELECT @total_male = COUNT(*) FROM student_info WHERE sex = '男';
SELECT @total_female = COUNT(*) FROM student_info WHERE sex = '女';
SELECT '男女比例为:' + CONVERT(DECIMAL(5,2), CAST(@total_male AS DECIMAL(10,2)) / CAST(@total_female AS DECIMAL(10,2))) AS '男女比例';
-- 计算各院系学生人数
DECLARE @department VARCHAR(20);
DECLARE @department_count INT;
DECLARE cur CURSOR FOR SELECT department FROM student_info GROUP BY department;
OPEN cur;
FETCH NEXT FROM cur INTO @department;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @department_count = COUNT(*) FROM student_info WHERE department = @department;
PRINT @department + '系学生人数为:' + CAST(@department_count AS VARCHAR);
FETCH NEXT FROM cur INTO @department;
END
CLOSE cur;
DEALLOCATE cur;
-- 计算各年龄段学生人数
DECLARE @age INT;
DECLARE @age_count INT;
DECLARE cur_age CURSOR FOR SELECT age FROM student_info GROUP BY age;
OPEN cur_age;
FETCH NEXT FROM cur_age INTO @age;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @age_count = COUNT(*) FROM student_info WHERE age = @age;
PRINT '年龄为' + CAST(@age AS VARCHAR) + '的学生人数为:' + CAST(@age_count AS VARCHAR);
FETCH NEXT FROM cur_age INTO @age;
END
CLOSE cur_age;
DEALLOCATE cur_age;