Mysql——第二次作业

一、作业内容二、建库建表create database mydb11_stu;use mydb11_stu;CREATE TABLE student(id INT(10) NOT NULL UNIQUE PRIMARY KEY,name VARCHAR(20) NOT NULL,sex VARCHAR(4),birth YEAR,department VARCHAR(20),address VARCHAR(50));CREATE TABLE score(id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,stu_id INT(10) NOT NULL,c_name VARCHAR(20),grade INT(10));三、插入数据向student表插入学生数据INSERT INTO student VALUES(901,张三丰,男,2002,计算机系,北京市海淀区);INSERT INTO student VALUES(902,周全有,男,2000,中文系,北京市昌平区);INSERT INTO student VALUES(903,张思维,女,2003,中文系,湖南省永州市);INSERT INTO student VALUES(904,李广昌,男,1999,英语系,辽宁省阜新市);INSERT INTO student VALUES(905,王翰,男,2004,英语系,福建省厦门市);INSERT INTO student VALUES(906,王心凌,女,1998,计算机系,湖南省衡阳市);向score表插入成绩数据INSERT INTO score VALUES(NULL,901,计算机,98);INSERT INTO score VALUES(NULL,901,英语,80);INSERT INTO score VALUES(NULL,902,计算机,65);INSERT INTO score VALUES(NULL,902,中文,88);INSERT INTO score VALUES(NULL,903,中文,95);INSERT INTO score VALUES(NULL,904,计算机,70);INSERT INTO score VALUES(NULL,904,英语,92);INSERT INTO score VALUES(NULL,905,英语,94);INSERT INTO score VALUES(NULL,906,计算机,49);INSERT INTO score VALUES(NULL,906,英语,83);四、查询(1) 分别查询两张表的所有记录SELECT * FROM student;SELECT * FROM score;(2) 查询student表第2条到第5条记录SELECT *FROM student LIMIT 1,4;(3) 查询计算机系和英语系的学生信息SELECT *FROM studentWHERE department IN (计算机系,英语系);(4) 查询年龄小于22岁的学生信息SELECT *FROM studentWHERE YEAR(CURDATE()) - birth 22;#最小的是王翰,男,2004今年2026年等于22岁。(5) 查询每个院系的人数SELECT department AS 院系, COUNT(*) AS 人数FROM studentGROUP BY department;(6) 查询每个科目的最高分SELECT c_name AS 科目, MAX(grade) AS 最高分FROM scoreGROUP BY c_name;(7) 查询李广昌的考试科目和成绩SELECT s.c_name, s.gradeFROM score sJOIN student st ON s.stu_id st.idWHERE st.name 李广昌;(8) 连接查询所有学生的信息和考试信息SELECT st.*, s.c_name, s.gradeFROM student stLEFT JOIN score s ON st.id s.stu_id;(9) 计算每个学生的总成绩SELECT st.id, st.name, SUM(s.grade) AS 总成绩FROM student stLEFT JOIN score s ON st.id s.stu_idGROUP BY st.id, st.name;(10) 计算每个考试科目的平均成绩SELECT c_name AS 科目, AVG(grade) AS 平均成绩FROM scoreGROUP BY c_name;(11) 查询计算机成绩低于95的学生信息SELECT st.*FROM student stJOIN score s ON st.id s.stu_idWHERE s.c_name 计算机 AND s.grade 95;(12) 计算机考试成绩从高到低排序SELECT *FROM scoreWHERE c_name 计算机 ORDER BY grade DESC;(13) 两张表查询学号并合并去重SELECT id AS 学号 FROM studentUNIONSELECT stu_id AS 学号 FROM score;(14) 查询姓张/姓王同学的姓名、院系、考试科目及成绩SELECTst.name,st.department,s.c_name,s.gradeFROM student stLEFT JOIN score s ON st.id s.stu_idWHERE st.name LIKE 张% OR st.name LIKE 王%;(15) 查询湖南籍学生的姓名、年龄、院系和考试科目及成绩SELECTst.name,YEAR(CURDATE()) - st.birth AS 年龄,st.department, s.c_name,s.gradeFROM student stLEFT JOIN score s ON st.id s.stu_idWHERE st.address LIKE 湖南%;