第08課數據庫基礎測試 · 學習講義 2019

* 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 '湖南%'; ```