æç« ç®å½
- 1.DDL
-
- 1.1æä½æ°æ®åº
- 1.2æä½è¡¨
- 2.DML
-
- 2.1æå ¥æ°æ®
- 2.2ä¿®æ¹æ°æ®
- 2.3å é¤æ°æ®
- 3.DCL
- 4.DQL
-
- 4.1åºç¡æ¥è¯¢
- 4.2æ¡ä»¶æ¥è¯¢
- 4.3模ç³æ¥è¯¢
- 4.4å段æ§å¶æ¥è¯¢
- 4.5æåº
- 4.6èåå½æ°
- 4.7åç»æ¥è¯¢
- 4.8å页æ¥è¯¢
- MYSQLæ°æ®åºçåºæ¬ç¥è¯ä¸DDLãDMLãDCLãDQL就讲å°è¿äº
1.DDL
DDLï¼Data Definition Languageï¼ï¼æ°æ®å®ä¹è¯è¨ï¼ç¨æ¥å®ä¹æ°æ®åºå¯¹è±¡ï¼åºã表ãåçï¼
常ç¨çè¯å¥å ³é®åæ CREATE(å建)ãDROP(å é¤)ãALTER (ä¿®æ¹)ç**
1.1æä½æ°æ®åº
å建æ°æ®åº:
#è¯æ³:CREATE DATABASE [IF NOT EXISTS] æ°æ®åºå [DEFAULT CHARACTER SET å符é COLLATE æåºè§åå符é]ï¼
#å建æ°æ®åºï¼
CREATE DATABASE [IF NOT EXISTS] mydb1ï¼
è¿éç IF NOT EXISTSæ¯å¤ææ°æ®åºæ¯å¦åå¨;
DEFAULT CHARACTER: é»è®¤çå符é, å¦æ没æ设置,é»è®¤æ¯utf8;æ¨è使ç¨utf8mb4;
COLLATE: æåºè§å, å¦æ没æ设置,é»è®¤æ¯: utf8_general_ci;æ¨è使ç¨uft8mb4_bin.
æ åå建æ°æ®åºè¯å¥:
CREATE DATABASE IF NOT EXISTS mydb2 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
å é¤æ°æ®åºè¯å¥:DROP DATABASE [IF EXISTS] mydb1;
ä¿®æ¹æ°æ®åºè¯å¥ :ALTER DATABASE mydb1 CHARACTER SET utf8;
è¿éä¿®æ¹æ°æ®åºmydb1çç¼ç 为utf8ã
è¦æ³¨æçæ¯ï¼å¨MySQLä¸ææçUTF-8ç¼ç é½ä¸è½ä½¿ç¨ä¸é´çâ-âï¼å³UTF-8è¦ä¹¦å为UTF8ã
1.2æä½è¡¨
å建表è¯å¥:
CREATE TABLE 表å(
åå åç±»å,
åå åç±»å,
......
);
å建å¦ç表:
CREATE TABLE tb_stu (
sid CHAR(6),
sname VARCHAR (20),
age INT,
gender VARCHAR (10)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'å¦ç表' ;
ä¸é¢æ建äºä¸ä¸ªå为tb_stuç表.
ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT âå¦ç表â; è¯å¥æ¯ç¨æ¥è®¾ç½®è¡¨ç使ç¨çæ°æ®åºå¼æ, é»è®¤å符é,é»è®¤æåºè§å:
2.DML
DMLï¼Data Manipulation Languageï¼ï¼æ°æ®æä½è¯è¨ï¼ç¨æ¥å®ä¹æ°æ®åºè®°å½ï¼æ°æ®ï¼ï¼
è¦æ¯å¯¹æ°æ®è¿è¡å¢å ãå é¤ãä¿®æ¹æä½ã常ç¨çè¯å¥å ³é®åæ INSERT(æå ¥)ãUPDATE(æ´æ°)ã
DELETE()å é¤ çã
2.1æå ¥æ°æ®
æå ¥æ°æ®çæå®è¯æ³:
INSERT INTO 表å(åå1,åå2, â¦) VALUES(å¼1, å¼2);
ä¸é¢æ们æ¥å¯¹tb_stuæå ¥æ°æ®:
INSERT INTO tb_stu (sid, sname, age, gender)
VALUES
('s_1001', 'zhangSan', 23, 'male');
è¿éè¦æ³¨æçæ¯è¦è®°å¾èªå·±å建表æ设置å个ååçæ°æ®ç±»å;
2.2ä¿®æ¹æ°æ®
ä¿®æ¹æ°æ®çæå®è¯æ³:
UPDATE 表å SET åå1=å¼1, ⦠åån=å¼n [WHERE æ¡ä»¶];
ä¸é¢æ们æ¥å¯¹tb_stuä¿®æ¹æ°æ®:
UPDATE tb_ stu SET sname='zhangSanSan', age='32', gender='female' WHERE sid='s_1001';
UPDATE tb_stu SET sname='wangWu', age='30' WHERE age>60 OR gender='female';
UPDATE tb_stu SET sname='liSi', age='20' WHERE age>50 AND gender='male';
UPDATE tb_stu SET gender='female' WHERE gender IS NULL;
UPDATE tb_stu SET age=age+1 WHERE sname='zhaoLiu';
2.3å é¤æ°æ®
å é¤æ°æ®çæå®è¯æ³:
DELETE FROM 表å [WHERE æ¡ä»¶];
ä¸é¢æ们æ¥å¯¹tb_stuå é¤æ°æ®:
DELETE FROM tb_stu WHERE sid='s_1001';
DELETE FROM tb_stu WHERE sname='chenQi' OR age > 30;
--å é¤æææ°æ® æ
ç¨
DELETE FROM stu;
3.DCL
DCLï¼Data Control Languageï¼ï¼æ°æ®æ§å¶è¯è¨ï¼ç¨æ¥å®ä¹è®¿é®æéåå®å ¨çº§å«ï¼
主è¦æ¯ç¨æ¥è®¾ç½®/æ´æ¹æ°æ®åºç¨æ·æéã常ç¨å ³é®åæ GRANT(ææ)ãREVOKE(æ¤é) çã
ä¸è¬äººåå¾å°ç¨å°DCLè¯å¥ãææè¿éå°±ä¸å¤è®²DCL
4.DQL
DQLï¼Data Query Languageï¼ï¼æ°æ®æ¥è¯¢è¯è¨ï¼ç¨æ¥æ¥è¯¢è®°å½ï¼æ°æ®ï¼ã
主è¦æ¯å¯¹æ°æ®è¿è¡æ¥è¯¢æä½ã常ç¨å ³é®åæ SELECTãFROMãWHERE çã
4.1åºç¡æ¥è¯¢
æ¥è¯¢ææçå:
SELECT * FROM stu;
æ¥è¯¢æå®çå:
SELECT å段1, å段2, å段3 ... FROM 表å ;
SELECT sid, sname, age FROM stu;
4.2æ¡ä»¶æ¥è¯¢
å®é å¼åè¿ç¨ä¸,æ¡ä»¶æ¥è¯¢å¯ä»¥è¯´æ¯æ¯ç¨æå¤çæ¥è¯¢.
æ»çæ¥è¯´,æ¡ä»¶æ¥è¯¢å°±æ¯å¨æ¥è¯¢æ¶ç»åºWHEREåå¥ï¼å¨WHEREåå¥ä¸å¯ä»¥ä½¿ç¨å¦ä¸è¿ç®ç¬¦åå ³é®åï¼
å ³é®å
- =ã!=ã<>(ä¸çäº)ã<ã<=ã>ã>=
- BETWEENâ¦AND
- IN(set)
- IS NULL //nullä¸ä»»ä½å¼æ¯è¾è®¡ç®,ç»ææ°¸è¿ä¸ºç©º
- AND
- OR
- NOT
é»è¾è¿ç®ç¬¦
AND æ && å¹¶ä¸ (å¤ä¸ªæ¡ä»¶åæ¶æç«)
OR æ || æè (å¤ä¸ªæ¡ä»¶ä»»æä¸ä¸ªæç«)
NOT æ ! é , ä¸æ¯
ä¸é¢æ们æ¥åå æ¡æ¡ä»¶æ¥è¯¢è¯å¥:
1.æ¥è¯¢æ§å«ä¸ºå¥³ï¼å¹¶ä¸å¹´é¾50çè®°å½:
SELECT * FROM tb_stu WHERE gender = 'female' AND ge < 50 ;
2.æ¥è¯¢å¦å·ä¸æ¯S_1001ï¼S_1002ï¼S_1003çè®°å½:
SELECT * FROM tb_student WHERE s_number NOT IN ('S_1001','S_1002','S_1003');
3.æ¥è¯¢å¹´é¾å¨20å°40ä¹é´çå¦çè®°å½:
SELECT * FROM tb_stu WHERE age BETWEEN 20 AND 40;
4.3模ç³æ¥è¯¢
1.æ¥è¯¢å§åç±5个åæ¯ææï¼å¹¶ä¸ç¬¬5个åæ¯ä¸ºâiâçå¦çè®°å½
SELECT * FROM tb_stu WHERE sname LIKE '____i';
2.æ¥è¯¢å§å以âzâå¼å¤´çå¦çè®°å½
SELECT * FROM tb_stu WHERE sname LIKE 'z%';
è¿éè¦æ³¨æçæ¯"%"çè¿ç¨æ¯å¹é 0~n个任ä½åæ¯
3.æ¥è¯¢å§åä¸å å«âaâåæ¯çå¦çè®°å½
SELECT * FROM tb_stu WHERE sname LIKE '%a%';
4.4å段æ§å¶æ¥è¯¢
1.å»é¤éå¤è®°å½
å½åºç°ä¸¤è¡æ两è¡ä»¥ä¸è®°å½ä¸ç³»åçä¸çæ°æ®é½ç¸å,æ们å¯ä»¥ç¨DISTINCTæ¥è¿è¡å»é¤éå¤,è¿è¡æ¥è¯¢
SELECT DISTINCT åå FROM 表å;
2.å½è¡¨ä¸çååææ°æ®ç±»åç¸åé½æ¯æ°å¼ç±»åæ¶,æ们å¯ä»¥å¯¹æ°æ®ç±»åç¸åé½æ¯æ°å¼ç±»åçååè¿è¡è¿ç®,æ¥çéåçæèªä¸å¥éä¹å
SELECT *,sal+IFNULL(comm,0) FROM emp;
è¿éè¦æ³¨æçæ¯,å®é ä¸å¹¶ä¸æ¯æ¯ä¸ªäººé½æcomm(å¥é),ä»»ä½ä¸è¥¿ä¸NULLç¸å ç»æè¿æ¯NULLï¼æ以ç»ç®ç»æå¯è½ä¼åºç°NULLãæ以使ç¨äºæNULL转æ¢ææ°å¼0çå½æ°IFNULLè¿è¡ç¸å
4.5æåº
æåºè¯æ³:
SELECT å段å表 FROM 表å ORDER BY å段1 æåºæ¹å¼1 , å段2 æåºæ¹å¼2 ,...;
æåºæ¹å¼
- ASC : ååº(é»è®¤å¼)
- DESC: éåº
注æäºé¡¹ï¼
⢠å¦ææ¯ååº, å¯ä»¥ä¸æå®æåºæ¹å¼ASC ;
⢠å¦ææ¯å¤å段æåºï¼å½ç¬¬ä¸ä¸ªå段å¼ç¸åæ¶ï¼æä¼æ ¹æ®ç¬¬äºä¸ªå段è¿è¡æåº ;
1.æ¥è¯¢ææå¦çè®°å½ï¼æå¹´é¾ååºæåº
SELECT * FROM stu ORDER BY sage ASC;
2.æ¥è¯¢ææå¦çè®°å½ï¼æå¹´é¾éåºæåº
SELECT * FROM stu ORDER BY age DESC;
3.æ¥è¯¢ææéåï¼ææèªéåºæåºï¼å¦ææèªç¸åæ¶ï¼æç¼å·ååºæåº
SELECT * FROM emp ORDER BY sal DESC,empno ASC;
4.6èåå½æ°
å½æ° | æè¿° |
---|---|
COUNT() | ç»è®¡æå®åä¸ä¸ºNULLçè®°å½è¡æ° |
MAX() | 计ç®æå®åçæ大å¼ï¼å¦ææå®åæ¯å符串类åï¼é£ä¹ä½¿ç¨å符串æåºè¿ç® |
MIN() | 计ç®æå®åçæå°å¼ï¼å¦ææå®åæ¯å符串类åï¼é£ä¹ä½¿ç¨å符串æåºè¿ç® |
SUM() | 计ç®æå®åçæ°å¼åï¼å¦ææå®åç±»åä¸æ¯æ°å¼ç±»åï¼é£ä¹è®¡ç®ç»æ为0 |
AVG() | 计ç®æå®åçå¹³åå¼ï¼å¦ææå®åç±»åä¸æ¯æ°å¼ç±»åï¼é£ä¹è®¡ç®ç»æ为0 |
èåè¯æ³:
SELECT èåå½æ°(å段å表) FROM 表å ;
注æ:NULLæ¯ä¸åä¸ææèåå½æ°çè¿ç®,ææå®é ä¸æ们è¦ç¨IFNULL对NULLè¿è¡å¤ç;
èåå½æ°éé¢ä¸è½ååµå¥èåå½æ°,æ¯å¦ä¸è½SUM(MIN());
1.æ¥è¯¢tb_stu表ä¸è®°å½æ°
SELECT COUNT(*) AS cnt FROM tb_stu;
è¿éASåé¢çcntæ¯ä¸ä¸ªå«å,å³å¯¹æ¥è¯¢è¡¨ä¸è®°å½æ°çç»æèµ·çå«å;
4.7åç»æ¥è¯¢
å½éè¦åç»æ¥è¯¢æ¶éè¦ä½¿ç¨GROUP BYåå¥ï¼ä¾å¦æ¥è¯¢æ¯ä¸ªé¨é¨çå·¥èµåï¼è¿è¯´æè¦ä½¿ç¨é¨åæ¥åç»ã
è¯æ³:
SELECT å段å表 FROM 表å [ WHERE æ¡ä»¶ ] GROUP BY åç»å段å [ HAVING åç» åè¿æ»¤æ¡ä»¶ ];
1.æ¥è¯¢å¦ççç¼å·ä»¥åæ¯ä¸ªå¦çå¹´é¾å¤§äº20ç人æ°ï¼
SELECT sid FROM tb_stu WHERE age > 20 GROUP BY sid;
2.æ¥è¯¢å¦çå¹´é¾å¤§äº20çå¦çç¼å·ä»¥åæ§å«ä¸ºå¥³
SELECT sid,gender FROM tb_stu GROUP BY age>20 HAVING gender='female';
whereä¸havingåºå«
æ§è¡æ¶æºä¸åï¼whereæ¯åç»ä¹åè¿è¡è¿æ»¤(å³æ´å¼ 表è¿è¡çé,ä½äºgroup byåé¢,ç¬ç«åå¨)ï¼ä¸æ»¡è¶³whereæ¡ä»¶ï¼ä¸åä¸åç»ï¼èhavingæ¯åç»åççé(ä½äºgroup byåé¢ä¸å¿ é¡»è·çgroup by)ä¹å对ç»æè¿è¡è¿æ»¤ã
å¤ææ¡ä»¶ä¸åï¼whereä¸è½å¯¹èåå½æ°è¿è¡å¤æï¼èhavingå¯ä»¥ã
注æäºé¡¹:
åç»ä¹åï¼æ¥è¯¢çå段ä¸è¬ä¸ºèåå½æ°ååç»å段ï¼æ¥è¯¢å ¶ä»å段æ ä»»ä½æä¹ã
æ§è¡é¡ºåº: where > èåå½æ° > having ã
æ¯æå¤å段åç», å ·ä½è¯æ³ä¸º : group by columnA,columnB
WHEREæ¯å¯¹åç»åè®°å½çæ¡ä»¶ï¼å¦ææè¡è®°å½æ²¡æ满足WHEREåå¥çæ¡ä»¶ï¼é£ä¹è¿è¡è®°å½ä¸ä¼åå åç»ï¼èHAVINGæ¯å¯¹åç»åæ°æ®ç约æã
4.8å页æ¥è¯¢
å页æä½å¨ä¸å¡ç³»ç»å¼åæ¶ï¼ä¹æ¯é常常è§çä¸ä¸ªåè½ï¼æ们å¨ç½ç«ä¸çå°çåç§åæ ·çå页æ¡ï¼åå°
é½éè¦åå©äºæ°æ®åºçå页æä½ãMySQL使ç¨LIMITç¨æ¥éå®æ¥è¯¢ç»æçèµ·å§è¡ï¼ä»¥åæ»è¡æ°ã
è¯æ³:
SELECT å段å表 FROM 表å LIMIT èµ·å§ç´¢å¼, æ¥è¯¢è®°å½æ° ;
注æäºé¡¹:
èµ·å§ç´¢å¼ä»0å¼å§ï¼èµ·å§ç´¢å¼ = ï¼æ¥è¯¢é¡µç - 1ï¼* æ¯é¡µæ¾ç¤ºè®°å½æ°ã
å页æ¥è¯¢æ¯æ°æ®åºçæ¹è¨ï¼ä¸åçæ°æ®åºæä¸åçå®ç°ï¼MySQLä¸æ¯LIMITã
å¦ææ¥è¯¢çæ¯ç¬¬ä¸é¡µæ°æ®ï¼èµ·å§ç´¢å¼å¯ä»¥çç¥ï¼ç´æ¥ç®å为 limit 10ã
1.æ¥è¯¢5è¡è®°å½ï¼èµ·å§è¡ä»0å¼å§
SELECT * FROM emp LIMIT 0, 5;
æè
SELECT * FROM emp LIMIT 5;
2.æ¥è¯¢10è¡è®°å½ï¼èµ·å§è¡ä»3å¼å§
SELECT * FROM emp LIMIT 3, 10;