MySQL练习题

CREATE TABLE STUDENT(
    ID INT(10) PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    SEX VARCHAR(4) NOT NULL,
    BIRTH YEAR,
    DEPARTMENT VARCHAR(20) NOT NULL,
    ADDRESS VARCHAR(21)
);

CREATE TABLE SCORE(
    ID INT(10) PRIMARY KEY AUTO_INCREMENT,
    STU_ID INT(10) NOT NULL,
    C_NAME VARCHAR(20),
    GRADE INT(10)
);


#向student表添加数据
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
#向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, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);


#查询Student表的第2到第4条数据
SELECT * FROM STUDENT WHERE ID BETWEEN 902 AND 904;

#从Student表查询所有学生的学号，姓名和院系
SELECT ID '学号',NAME '姓名',DEPARTMENT '院系' FROM STUDENT;

#从Student表中查询计算机系和英语系的学生
SELECT ID '学号',NAME '姓名',DEPARTMENT '院系' FROM STUDENT WHERE DEPARTMENT IN('计算机系','英语系');

#从Student表中查询年龄在18~22岁的学生信息
SELECT ID '学号',NAME '姓名',BIRTH '出生年月' FROM STUDENT WHERE 2019-BIRTH BETWEEN 18 AND 22;

#从student表中查询每个院系有多少人
SELECT DEPARTMENT '院系',COUNT(ID) '人数' FROM STUDENT GROUP BY(DEPARTMENT);

#从Score表中查询每个科目的最高分
SELECT C_NAME '科目',MAX(GRADE) '最高分' FROM SCORE GROUP BY(C_NAME);

#查询李四的考试科目
SELECT NAME '姓名',C_NAME '考试科目' FROM STUDENT ST,SCORE SC WHERE ST.ID=SC.STU_ID AND NAME='李四';

#用连接的方式查询所有学生的姓名、院系、科目和考试成绩
SELECT NAME '姓名',C_NAME '科目',GRADE '考试成绩' FROM STUDENT ST,SCORE SC WHERE ST.ID=SC.STU_ID;

#计算每个学生的总成绩
SELECT NAME '姓名',SUM(GRADE) '总成绩' FROM STUDENT ST,SCORE SC WHERE ST.ID=SC.STU_ID GROUP BY(NAME);

#计算每个考试科目的平均成绩
SELECT C_NAME '科目',AVG(GRADE) '平均分' FROM SCORE GROUP BY(C_NAME);

#查询计算机成绩低于95分的学生信息
SELECT NAME '姓名',C_NAME '科目',GRADE '考试成绩' FROM STUDENT ST,SCORE SC WHERE ST.ID=SC.STU_ID AND C_NAME='计算机' AND GRADE<95;

#查询同时参加计算机和英语考试的学生信息
SELECT * FROM STUDENT WHERE id in(select stu_id from score where stu_id in(select stu_id from score where C_NAME='计算机') and C_NAME='英语') ;

#将计算机考试成绩按照从高到低进行排序
SELECT C_NAME '科目',GRADE '考试成绩' FROM SCORE WHERE C_NAME='计算机' ORDER BY(GRADE) DESC;

#从student表和score表中查询出学生的学号，然后合并查询结果
SELECT DISTINCT NAME '姓名',ST.ID+STU_ID '学号' FROM STUDENT ST,SCORE SC WHERE ST.ID=SC.STU_ID; 

#查询姓张或者姓王的同学的姓名、院系和考试科目以及成绩（提示，模糊查询关键字like 例如查询以A开头的姓名  selec * from 表名 where name like ‘A%’）
SELECT NAME '姓名',DEPARTMENT '院系',C_NAME '科目',GRADE '考试成绩' FROM STUDENT ST,SCORE SC WHERE ST.ID=SC.STU_ID AND NAME LIKE  OR '王%';

#查询都是湖南的学生的姓名、年龄、院系和考试科目以及成绩
SELECT NAME '姓名',2019-BIRTH '年龄',DEPARTMENT '院系',C_NAME '科目',GRADE '考试成绩' FROM STUDENT ST,SCORE SC WHERE ST.ID=SC.STU_ID AND ADDRESS LIKE '湖南%';