MySQL練習(学生表、カリキュラム、選択カリキュラム)超詳細
25851 ワード
mysql練習一、テーマ: 二、学生、課程を作成し、授業表を選択し、データ を挿入する.三、練習問題の答え 環境:win 10システム、MySQLデータベース
一、テーマ:
二、学生の作成、カリキュラム、選択表、データの挿入
三、練習問題の答え
一、テーマ:
:
S(Sno,Sname,Sage,Ssex,Sdept)
C(cno,cname,cpno,ccredit)
SC(sno,cno,grade)
( sno: ;sname: ;Ssex: ;Sdept: ;Sage : ;cno: ;cname: ;
cpno: ;ccredit: ;grade: ),
1、 “CS” ;
2、 “CS” 19 21 、 ;
3、 ;
4、 “ ” , 、 ;
5、 , ;
6、 , ;
7、 “1” “2” ;
8、 “ ” 60 、 ;
9、 3 ;
10、 80 ;
11、 80 ;
12、 80
13、 , 、 ;
二、学生の作成、カリキュラム、選択表、データの挿入
# : , , , ,
CREATE TABLE S
(
Sno VARCHAR(7)PRIMARY KEY,
Sname VARCHAR(10)NOT NULL,
Sage INT,
Ssex VARCHAR(2),
Sdept VARCHAR(20)DEFAULT(' ')
);
# : , , ,
CREATE TABLE C
(
Cno VARCHAR(10)PRIMARY KEY,
Cname VARCHAR (20)NOT NULL,
Cpno VARCHAR(10),
Ccredit INT
);
#
CREATE TABLE SC
(
Sno VARCHAR(7),
Cno VARCHAR(10),
grade INT,
FOREIGN KEY (sno) REFERENCES S(Sno),
FOREIGN KEY (cno) REFERENCES C(cno)
);
# S
INSERT INTO s
(Sno,Sname,Sage,Ssex,Sdept)
VALUE
("10001"," ",20,' ',' '),
("10002"," ",19,' ',' '),
("10003"," ",18,' ','CS'),
("10004"," ",21,' ',' '),
("10006"," ",18,' ',' '),
("10008"," ",21,' ',' '),
("10005"," ",22,' ','CS');
# C
INSERT INTO c
(Cno,Cname,Cpno,Ccredit)
VALUE
("1"," ",NULL,5),
("2"," ",'3',6),
("3"," ",NULL,4),
("4"," ",'3',6),
("5"," ",'1',4),
("6"," ",'4',5);
DELETE FROM c;
# SC
INSERT INTO sc
(Sno,Cno,grade)
VALUE
("10001","1",70),
("10001","6",56),
("10003","4",90),
("10003","5",83),
("10004","1",75),
("10004","3",90),
("10008","1",70),
("10008","5",70),
("10008","6",88),
("10002","1",85),
("10002","6",89);
三、練習問題の答え
# 1、 “CS” ;
SELECT * FROM s WHERE Sdept="CS";
# 2、 “CS” 19 21 、 ;
SELECT Sno,Sname,Sage,Sdept
FROM s
WHERE (Sage<19 OR Sage>21) AND Sdept = "CS";
# 3、 ;
SELECT MAX(Sage) FROM s;
# 4、 “ ” , 、 ;
SELECT Sno, Sname
FROM s
WHERE Sdept=" "
ORDER BY Sage DESC
LIMIT 1;
# 5、 , ;
SELECT Sdept,COUNT(*) ' '
FROM s
GROUP BY Sdept
ORDER BY ' ';
# 6、 , ;
SELECT Sdept,AVG(Sage) ' '
FROM s
GROUP BY Sdept
ORDER BY ' 'DESC;
# 7、 “1” “2” ;
SELECT s.Sno,Sname
FROM s JOIN sc ON s.`Sno`=sc.`Sno`
WHERE Cno IN ("1","2");
# 8、 “ ” 60 、 ;
SELECT s.Sno,Sname
FROM s JOIN sc ON s.`Sno`=sc.`Sno`
JOIN c ON sc.`Cno`=c.`Cno`
WHERE Cname =" " AND grade<60;
# 9、 3 ;
SELECT sno
FROM sc
GROUP BY sc.Sno
HAVING COUNT(*)>=3;
# 10、 80 ;
SELECT sno
FROM sc
GROUP BY sc.Sno
HAVING MAX(grade)>80;
# 11、 80 ;
SELECT sno
FROM sc
GROUP BY sc.Sno
HAVING MIN(grade)>80;
# 12、 80
SELECT sno
FROM sc
GROUP BY sc.Sno
HAVING AVG(grade)>80;
# 13、 , 、 ;
SELECT sno,Sname
FROM s JOIN
(SELECT Sdept,MAX(Sage) Sage
FROM s
GROUP BY Sdept) a
ON (s.`Sage`=a.Sage AND s.`Sdept` = a.Sdept) ;
, 。