ð¢ð¢ð¢ð£ð£ð£
åå½ï¼å¤§å®¶å¥½ï¼ææ¯ãITé¦å¾·ãï¼10å¹´DBAå·¥ä½ç»éª
ä¸ä½ä¸è¿å¿å足çã大æ°æ®é¢åå主ãï¼ððð
ä¸å½DBAèç(ACDU)æåï¼ç®åä»äºDBAåç¨åºç¼ç¨ï¼Bç«åè ¾è®¯è¯¾å 讲å¸ï¼ç´æéç ´10W
æ é¿ä¸»æµæ°æ®OracleãMySQLãPG è¿ç»´å¼åï¼å¤ä»½æ¢å¤ï¼å®è£ è¿ç§»ï¼æ§è½ä¼åãæ éåºæ¥å¤ççã
⨠å¦ææ对ãæ°æ®åºãæå ´è¶£çãå°å¯ç±ãï¼æ¬¢è¿å ³æ³¨ãITé¦å¾·ãððð
â¤ï¸â¤ï¸â¤ï¸æè°¢åä½å¤§å¯ç±å°å¯ç±ï¼â¤ï¸â¤ï¸â¤ï¸
SQL对äºç°å¨çäºèç½å ¬å¸äº§ç å²ä½å ä¹æ¯ä¸ä¸ªå¿ å¤æè½ï¼ä½ä» ä¼SQLçè¯ï¼åºè¯¥æ¯ä»ä¹é½åä¸äºã
1.å¦æä½ æ¯æ°æ®åæå¸ï¼ä½ éè¦çç»å°æèªå·±èåéçæ°æ®åææ éæ±ç¿»è¯æSQLé»è¾å»æ¥è¯¢æ°æ®ï¼è¿èå®æèªå·±çæ°æ®åææ¥åçï¼ä½ ç产åºæ¯åææ¥åï¼èä¸æ¯SQL代ç ï¼
2.å¦æä½ æ¯æ°ä»å·¥ç¨å¸ï¼ååºç¨å±ï¼ï¼ä½ éè¦æ ¹æ®ä¸å¡é»è¾å»è®¾è®¡æ¨¡åï¼ç¼åè°åº¦ä»»å¡å»äº§åºæ°æ®ï¼ä»¥ä¾ä¸å¡äººå使ç¨ï¼ä½ ç产åºæ¯æ°æ®æ¨¡åå表ï¼
3.å¦æä½ æ¯ç®æ³å·¥ç¨å¸ï¼ä½ å¯è½éè¦ç¨SQLæ¥å®ç°ç¨æ·æ ç¾ãç¹å¾å·¥ç¨çå·¥ä½ï¼ä½æ¯è¿äºæ¯ä¸ºä½ ç模åè®ç»è¯ä¼°ååºç¡åå¤å·¥ä½ï¼ä½ ç产åºæ¯å¯ä»¥æåæäºææ çç®æ³æ¨¡åã
æ以ï¼SQLæ¯ä¸ªäººé½è¦ç¨ï¼ä½æ¯ç¨æ¥è¡¡é产åºç并ä¸æ¯SQLæ¬èº«ï¼ä½ éè¦ç¨è¿ä¸ªå·¥å ·ï¼å»åé å ¶å®çä»·å¼ã
ITé¦å¾·èå¸å¸¦ä½ æ为SQLé«æï¼é£æ们å¼å§å§~
æç« ç®å½
- âââ³ï¸1.ANYç¨æ³ââ
- âââ³ï¸2.ALLçç¨æ³ââ
- âââ³ï¸3. èªè¿æ¥ââ
- âââ³ï¸4. æåºââ
- ââð 4.1 å¤å段æåºââ
- ââð 4.2 ç级å¤çââ
- ââð 4.3 æåââ
ç¯å¢åå¤ï¼éè¿ä»¥ä¸å»ºè¡¨
-- å建æ°æ®åº
CREATE DATABASE mes_db charset utf8mb4;
-- åæ¢æ°æ®åº
USE mes_db;
-- å建å¦ç表
CREATE TABLE student (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE, -- çæ¥
class VARCHAR(20) -- æå¨ç级
);
-- å建æå¸è¡¨
CREATE TABLE teacher (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE,
profession VARCHAR(20) NOT NULL, -- è称
department VARCHAR(20) NOT NULL -- é¨é¨
);
-- å建课ç¨è¡¨
CREATE TABLE course (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
t_no VARCHAR(20) NOT NULL, -- æå¸ç¼å·
-- 表示该 t_no æ¥èªäº teacher 表ä¸ç no å段å¼
FOREIGN KEY(t_no) REFERENCES teacher(no)
);
-- æ绩表
CREATE TABLE score (
s_no VARCHAR(20) NOT NULL, -- å¦çç¼å·
c_no VARCHAR(20) NOT NULL, -- 课ç¨å·
degree DECIMAL, -- æ绩
-- 表示该 s_no, c_no åå«æ¥èªäº student, course 表ä¸ç no å段å¼
FOREIGN KEY(s_no) REFERENCES student(no),
FOREIGN KEY(c_no) REFERENCES course(no),
-- 设置 s_no, c_no 为èå主é®
PRIMARY KEY(s_no, c_no)
);
-- æ¥çææ表
SHOW TABLES;
-- æ·»å å¦ç表æ°æ®
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');
â³ï¸1.ANYç¨æ³
æ¥è¯¢è¯¾ç¨ 3-105 ä¸æ绩è³å°é« 3-245 ç score 表ï¼DESC ( éåº )
#ANY: 符å SQL è¯å¥ä¸çä»»ææ¡ä»¶
#ä¹å°±æ¯è¯´ï¼3-105æ绩ä¸ï¼åªè¦æä¸ä¸ªå¤§äºä»3-245çéåºæ¥çä»»æè¡å°±ç¬¦åæ¡ä»¶
#æåæ ¹æ®éåºæ¥è¯¢ç»æ
SELECT * FROM score WHERE c_no = â3-105â AND degree > ANY(
SELECT degree FROM score WHERE c_no = â3-245â
) ORDER BY degree DESC;
â³ï¸2.ALLçç¨æ³
æ¥è¯¢è¯¾ç¨ 3-105 ä¸æ绩é«äº 3-245 ç score 表
#åªé对ä¸ä¸éé¢ç¨ä½ä¿®æ¹ã
#ALL: 符å SQL è¯å¥ä¸çæææ¡ä»¶ã
#ä¹å°±æ¯è¯´ï¼å¨3-105æ绩ä¸ï¼é½è¦å¤§äºä»3-245çéåºæ¥å ¨é¨è¡æç®ç¬¦åæ¡ä»¶
SELECT * FROM score WHERE c_no = â3-105â AND degree > ALL(
SELECT degree FROM score WHERE c_no = â3-245â);
â³ï¸3. èªè¿æ¥
æ¥è¯¢æ课ç¨æ绩æ¯è¯¥è¯¾ç¨å¹³åæ绩ä½ç score 表
#å°è¡¨ b ä½ç¨äºè¡¨ a ä¸æ¥è¯¢æ°æ®
#score å«åa,b å¦æ¤å°±è½ç¨ a.c_no=b.c_no ä½ä¸ºæ¡ä»¶æ§è¡æ¥è¯¢
SELECT * FROM score a WHERE degree < (
(SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no));
â³ï¸4. æåº
ð 4.1 å¤å段æåº
以 class å birthday ä»å¤§å°å°ç顺åºæ¥è¯¢ student 表
SELECT * FROM student ORDER BY class DESC, birthday;
ð 4.2 ç级å¤ç
建ç«ä¸ä¸ª grade 表代表å¦ççæ绩ç级ï¼å¹¶æå ¥æ°æ®ï¼
CREATE TABLE grade (
low INT(3),
upp INT(3),
grade char(1)
);
INSERT INTO grade VALUES (90, 100, âAâ);
INSERT INTO grade VALUES (80, 89, âBâ);
INSERT INTO grade VALUES (70, 79, âCâ);
INSERT INTO grade VALUES (60, 69, âDâ);
INSERT INTO grade VALUES (0, 59, âEâ);
SELECT * FROM grade;
âæ¥è¯¢ææå¦çç s_no ãc_no å grade å
SELECT s_no, c_no, grade FROM score, grade
WHERE degree BETWEEN low AND upp;
ð 4.3 æå
create table scores_tb (
id int auto_increment primary key,
xuehao int not null,
score int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into scores_tb (xuehao,score)
values (1001,89),
(1002,99),
(1003,96),
(1004,96),
(1005,92),
1006,90),
(1007,90),
(1008,94);
select * from scores_tb;
MySQL8.0 ä¸å¯ä»¥å©ç¨ ROW_NUMBER()ï¼DENSE_RANK()ï¼RANK()
ä¸ä¸ªçªå£å½æ°å®ç°ä¸è¿°ä¸ç§æå
æ®éæåï¼
æåæ°é«ä½ç´æ¥æåï¼ä» 1 å¼å§ï¼å¾ä¸æï¼ç±»ä¼¼äº row number
select xuehao,score,
ROW_NUMBER() OVER(order by score desc)
as row_r from scores_tb;
åæ°ç¸åï¼å次ç¸åï¼æåæ é´é
select xuehao,score,
DENSE_RANK() OVER(order by score desc) as dense_r
from scores_tb;
并åæåï¼æåæé´é
select xuehao,score,
RANK() over(order by score desc) as r
from scores_tb;