🔥码云GVP开源项目 12k star Uniapp+ElementUI 功能强大 支持多语言、二开方便! 广告
* student 表的定义: | 字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 | | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-: | | id | 学号 | INT(11) | yes | no | yes | yes | yes | | name | 姓名 | VARCHAR(20) | no | no | yes | no | no | | sex | 性别 | VARCHAR(4) | no | no | no | no | no | | birth | 出生年份 | YEAR | no | no | no | no | no | | department | 院系 | VARCHAR(20) | no | no | yes | no | no | | address | 家庭住址 | VARCHAR(50) | no | no | no | no | no | * score 表的定义 | 字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 | | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-: | | id | 编号 | INT(11) | yes | no | yes | yes | yes | | stu\_id | 学号 | INT(11) | no | no | yes | no | no | | c\_name | 课程名 | VARCHAR(20) | no | no | no | no | no | | grade | 分数 | INT(11) | no | no | no | no | no | * 向 student 表插入记录的 insert 语句如下: ```sql 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 语句如下: ```sql 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); ``` * 试题: 1. 创建 student 和 score 表语句 ```sql CREATE TABLE STUDENT ( ID INT(10) PRIMARY KEY AUTO\_INCREMENT, NAME VARCHAR(20) NOT NULL, SEX VARCHAR(4), BIRTH YEAR, DEPARTMENT VARCHAR(20) NOT NULL, ADDRESS VARCHAR(50) ); CREATE TABLE SCORE ( ID INT(10) PRIMARY KEY AUTO_INCREMENT, STU_ID INT(10) NOT NULL, C_NAME VARCHAR(20), GRADE INT(10) ); ``` 1. 查询 student 表的所有记录 ```sql SELECT * FROM STUDENT; ``` 1. 查询 student 表的第2条到4条记录 ```sql SELECT * FROM STUDENT LIMIT 1, 3; ``` 1. 从 student 表查询所有学生的学号(id)、姓名(name)和院系(department)的信息 ```sql SELECT ID '学号', NAME '姓名', DEPARTMENT '学院' FROM STUDENT; ``` 1. 从 student 表中查询计算机系和英语系的学生的信息(用 IN 关键字) ```sql SELECT * FROM STUDENT WHERE DEPARTMENT IN ('计算机系', '英语系'); ``` 1. 从 student 表中查询年龄18~22岁的学生信息(用 BETWEEN AND) ```sql SELECT * FROM STUDENT WHERE YEAR(NOW()) - BIRTH BETWEEN 28 AND 32; ``` 1. 从 student 表中查询每个院系有多少人 ```sql SELECT DEPARTMENT, COUNT(*) FROM STUDENT GROUP BY DEPARTMENT; ``` 1. 从 score 表中查询每个科目的最高分 ```sql SELECT C_NAME, MAX(GRADE) FROM SCORE GROUP BY C_NAME; ``` 1. 查询李四的考试科目(c\_name)和考试成绩(grade) ```sql SELECT S.NAME, C.C_NAME, C.GRADE FROM SCORE C LEFT JOIN STUDENT S ON C.STU_ID = S.ID WHERE S.NAME = '李四' ``` 1. 用连接的方式查询所有学生的信息和考试信息 ```sql SELECT * FROM SCORE C LEFT JOIN STUDENT S ON C.STU_ID = S.ID; ``` 1. 计算每个学生的总成绩 ```sql SELECT S.NAME, SUM(C.GRADE) FROM SCORE C LEFT JOIN STUDENT S ON C.STU_ID = S.ID GROUP BY S.NAME; ``` 1. 计算每个考试科目的平均成绩 ```sql SELECT C_NAME, AVG(GRADE) FROM SCORE GROUP BY C_NAME; ``` 1. 查询计算机成绩低于95的学生信息 ```sql SELECT S.* FROM SCORE C LEFT JOIN STUDENT S ON C.STU_ID = S.ID WHERE C.C_NAME = '计算机' AND C.GRADE < 95; SELECT * FROM STUDENT WHERE ID IN ( SELECT STU_ID FROM SCORE WHERE C_NAME = '计算机' AND GRADE < 95; ) ``` 1. 查询同时参加计算机和英语考试的学生的信息 ```sql SELECT * FROM STUDENT WHERE ID IN ( SELECT STU_ID FROM SCORE WHERE C_NAME = '计算机' OR C_NAME = '英语' GROUP BY STU_ID HAVING COUNT(*) = 2 ) 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 = '英语' ); ``` 1. 将计算机考试成绩按从高到低进行排序 ```sql SELECT GRADE FROM SCORE WHERE C_NAME = '计算机' ORDER BY GRADE DESC ``` 1. 从 student 表和 score 表中查询出学生的学号,然后合并查询结果 ```sql SELECT ID FROM STUDENT UNION SELECT DISTINCT STU_ID FROM SCORE ``` 1. 查询姓张或者姓王的同学的姓名、院系和考试科目及成绩 ```sql SELECT S.NAME, S.DEPARTMENT, C.C_NAME, C.GRADE FROM SCORE C LEFT JOIN STUDENT S ON C.STU_ID = S.ID WHERE S.NAME LIKE '张%' OR S.NAME LIKE '王%'; ``` 1. 查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩 ```sql SELECT S.NAME, YEAR(NOW()) - S.BIRTH, S.DEPARTMENT, C.C_NAME, C.GRADE FROM SCORE C LEFT JOIN STUDENT S ON C.STU_ID = S.ID WHERE S.ADDRESS LIKE '湖南%'; ```