æç« ç®å½
- ââåè¨ââ
- âââ³ï¸ 1.ç¯å¢åå¤ââ
- ââð´ 1.1 建表ââ
- ââð´ 1.2 æå ¥æ°æ®ââ
- âââ³ï¸ 2.å®è®ââ
- ââð´ 2.1 åºç¡æ¥è¯¢ââ
- ââð´ 2.2 é«é¶æ¥è¯¢ââ
- âââ³ï¸ 3.SQLèµæââ
åè¨
SQLæ¯ä¸ªäººé½è¦ç¨ï¼ä½æ¯ç¨æ¥è¡¡é产åºç并ä¸æ¯SQLæ¬èº«ï¼ä½ éè¦ç¨è¿ä¸ªå·¥å ·ï¼å»åé å ¶å®çä»·å¼
æç §è¦æ±åçæ¡ï¼çå¨è¯è®ºåºåï¼æä¼ç¹è¯åå¤
â³ï¸ 1.ç¯å¢åå¤
ð´ 1.1 建表
##å¦ç表
create table student(
no VARCHAR(20) primary key COMMENT âå¦å·â,
name VARCHAR(20) NOT NULL COMMENT âå§åâ,
sex char(1) not null COMMENT âå§å«â,
birthday DATE COMMENT âçæ¥â,
class VARCHAR(20) COMMENT âç级â
) CHARSET=utf8mb4 comment âå¦ç表â ;
##æå¸è¡¨
CREATE TABLE teacher (
no VARCHAR(20) PRIMARY KEY COMMENT âæå¸ç¼å·â,
name VARCHAR(20) NOT NULL COMMENT âå§åâ,
sex VARCHAR(10) NOT NULL COMMENT âæ§å«â,
birthday DATE COMMENT âåºçæ¥æâ,
profession VARCHAR(20) NOT NULL COMMENT âè称â,
department VARCHAR(20) NOT NULL COMMENT âé¨é¨â
) charset=utf8mb4 COMMENT âæå¸è¡¨â;
##课ç¨è¡¨
CREATE TABLE course (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
t_no VARCHAR(20) NOT NULL COMMENT âæå¸ç¼å·â,
FOREIGN KEY(t_no) REFERENCES teacher(no)
);
注:t_no æ¥èªäº teacher 表ä¸ç no å段å¼
##æ绩表
CREATE TABLE score (
s_no VARCHAR(20) NOT NULL COMMENT âå¦çç¼å·â,
c_no VARCHAR(20) NOT NULL COMMENT âæå¸ç¼å·â,
degree DECIMAL COMMENT âæ绩â,
FOREIGN KEY(s_no) REFERENCES student(no),
FOREIGN KEY(c_no) REFERENCES course(no),
PRIMARY KEY(s_no, c_no)
);
注:s_no, c_no åå«æ¥èªäº student, course 表ä¸ç no å段å¼
设置 s_no, c_no 为èå主é®
ð´ 1.2 æå ¥æ°æ®
â æ·»å å¦ç表æ°æ®
INSERT INTO student VALUES(â101â, âæ¾åâ, âç·â, â1977-09-01â, â95033â);
INSERT INTO student VALUES(â102â, âå¡æâ, âç·â, â1975-10-02â, â95031â);
INSERT INTO student VALUES(â103â, âç丽â, â女â, â1976-01-23â, â95033â);
INSERT INTO student VALUES(â104â, âæåâ, âç·â, â1976-02-20â, â95033â);
INSERT INTO student VALUES(â105â, âçè³â, â女â, â1975-02-10â, â95031â);
INSERT INTO student VALUES(â106â, âéåâ, âç·â, â1974-06-03â, â95031â);
INSERT INTO student VALUES(â107â, âçé£é£â, âç·â, â1976-02-20â, â95033â);
INSERT INTO student VALUES(â108â, âå¼ å ¨èâ, âç·â, â1975-02-10â, â95031â);
INSERT INTO student VALUES(â109â, âèµµéæ±â, âç·â, â1974-06-03â, â95031â);
â æ·»å æå¸è¡¨æ°æ®
INSERT INTO teacher VALUES(â804â, âæè¯â, âç·â, â1958-12-02â, âå¯ææâ, â计ç®æºç³»â);
INSERT INTO teacher VALUES(â856â, âå¼ æâ, âç·â, â1969-03-12â, â讲å¸â, âçµåå·¥ç¨ç³»â);
INSERT INTO teacher VALUES(â825â, âçèâ, â女â, â1972-05-05â, âå©æâ, â计ç®æºç³»â);
INSERT INTO teacher VALUES(â831â, âåå°â, â女â, â1977-08-14â, âå©æâ, âçµåå·¥ç¨ç³»â);
â æ·»å 课ç¨è¡¨æ°æ®
INSERT INTO course VALUES(â3-105â, â计ç®æºå¯¼è®ºâ, â825â);
INSERT INTO course VALUES(â3-245â, âæä½ç³»ç»â, â804â);
INSERT INTO course VALUES(â6-166â, âæ°åçµè·¯â, â856â);
INSERT INTO course VALUES(â9-888â, âé«çæ°å¦â, â831â);
â æ·»å æ·»å æ绩表æ°æ®
INSERT INTO score VALUES(â103â, â3-105â, â92â);
INSERT INTO score VALUES(â103â, â3-245â, â86â);
INSERT INTO score VALUES(â103â, â6-166â, â85â);
INSERT INTO score VALUES(â105â, â3-105â, â88â);
INSERT INTO score VALUES(â105â, â3-245â, â75â);
INSERT INTO score VALUES(â105â, â6-166â, â79â);
INSERT INTO score VALUES(â109â, â3-105â, â76â);
INSERT INTO score VALUES(â109â, â3-245â, â68â);
INSERT INTO score VALUES(â109â, â6-166â, â81â);
â³ï¸ 2.å®è®
ð´ 2.1 åºç¡æ¥è¯¢
1.æ¥è¯¢ student 表çææè¡
2.æ¥è¯¢ student 表ä¸ç nameãsex å class å段çææè¡
3.æ¥è¯¢ teacher 表ä¸ä¸éå¤ç department å
4.æ¥è¯¢ score 表ä¸æç»©å¨ 50-80 ä¹é´çææè¡
5.æ¥è¯¢ score 表ä¸æ绩为 85, 86 æ 88 çè¡
6.æ¥è¯¢ student è¡¨ä¸ â95033â çææ§å«ä¸º â女â çææè¡
7.以 class éåºçæ¹å¼æ¥è¯¢ student 表çææè¡
8.æ¥è¯¢ â95031â ççå¦ç人æ°
9.æ¥è¯¢ score 表ä¸çæé«åçå¦çå¦å·å课ç¨ç¼å·
10.以class为éåºãçæ¥ä¸ºååºæåºï¼æ¥è¯¢student表
ð´ 2.2 é«é¶æ¥è¯¢
1.æ¥è¯¢æ¯é¨è¯¾çæ绩平åæ绩
2.æ¥è¯¢ score 表ä¸è³å°æ 2 åå¦çéä¿®ï¼å¹¶ä»¥ 3 å¼å¤´ç课ç¨çå¹³ååæ°
3.æ¥è¯¢ææå¦çç nameï¼ä»¥å该å¦çå¨ score 表ä¸å¯¹åºç c_no å degree
4.æ¥è¯¢ææ任课 ( å¨ course 表éæè¯¾ç¨ ) æå¸ç name å department
5.æ¥è¯¢ææå¦çç name ã课ç¨å ( course 表ä¸ç name ) å degree
6.æ¥è¯¢ææå¦çç no ã课ç¨å称 ( course 表ä¸ç name ) åæ绩 ( score 表ä¸ç degree ) å
7.æ¥è¯¢95031ççå¦çæ¯é¨è¯¾ç¨çå¹³åæ绩
8.æ¥è¯¢ â计ç®æºç³»â 课ç¨çæ绩表
9.æ¥è¯¢æé修课ç¨å¤äº 5 个åå¦çæå¸å§å
10.æ¥è¯¢è®¡ç®æºç³»ä¸çµåå·¥ç¨ç³»ä¸çä¸åè称çæå¸
11.æ¥è¯¢è¯¾ç¨3-105ä¸æ绩è³å°é«3-245çscoreä¿¡æ¯ï¼æ绩éåº
12.æ¥è¯¢è¯¾ç¨3-105ä¸æ绩é«äº3-345çscoreä¿¡æ¯
13.æ¥è¯¢æ课ç¨æ绩æ¯è¯¥è¯¾ç¨å¹³åæ绩ä½çscoreä¿¡æ¯
14.æ¥è¯¢student表ä¸è³å°æ3åç·ççclass
15.æ¥è¯¢studnet表ä¸å¹´é¾æ大çåå¹´é¾æå°
16.æ¥è¯¢student表ä¸æ¯ä¸ªå¦ççå§ååå¹´é¾
17.scores_tbæ ¹æ®æ绩ä»é«å°ä½æåºï¼ä½¿ç¨row_numberæè dense_rankå¼çªå½æ°
çæ¡å°±ä¸åäºï¼ä¸»è¦æ¯è®©å¤§å®¶å®è®æä½ï¼çæ¡çå¨è¯è®ºåºå