ã1ãSQLè¯è¨å ¥é¨
æ们é½ç¥éï¼æ°æ®åºç®¡ç人åï¼DBAï¼éè¿æ°æ®åºç®¡çç³»ç»ï¼DBMSï¼å¯ä»¥å¯¹æ°æ®åºï¼DBï¼ä¸çæ°æ®è¿è¡æä½ï¼ä½å ·ä½æ¯å¦ä½æä½çå¢ï¼è¿å°±æ¶åå°æ们æ¬èè¦è®²çSQLè¯è¨ã
SQLï¼Structured Query Languageï¼æ¯ç»æåæ¥è¯¢è¯è¨çç®ç§°ï¼å®æ¯ä¸ç§æ°æ®åºæ¥è¯¢åç¨åºè®¾è®¡è¯è¨ï¼åæ¶ä¹æ¯ç®å使ç¨æ广æ³çå ³ç³»åæ°æ®åºæä½è¯è¨ãå¨æ°æ®åºç®¡çç³»ç»ä¸ï¼ä½¿ç¨SQLè¯è¨æ¥å®ç°æ°æ®çååãæ¥è¯¢ãæ´æ°çåè½ãSQLæ¯ä¸ç§éè¿ç¨åè¯è¨ï¼åªéæåºâåä»ä¹âï¼èä¸éè¦ææâæä¹åâã
SQLæ¯ç±IBMå ¬å¸å¨1974~1979å¹´ä¹é´æ ¹æ®E.J.Coddå表çå ³ç³»æ°æ®åºç论为åºç¡å¼åçï¼å ¶å身æ¯âSEQUELâï¼åæ´å为SQLãç±äºSQLè¯è¨å ·æéæ°æ®æ¥è¯¢ãæ°æ®æ纵ãæ°æ®å®ä¹åæ°æ®æ§å¶åè½äºä¸ä½ï¼ç±»ä¼¼èªç¶è¯è¨ãç®åæç¨ä»¥åéè¿ç¨åçç¹ç¹ï¼å¾å°äºå¿«éçåå±ï¼å¹¶äº1986å¹´10æï¼è¢«ç¾å½å½å®¶æ ååä¼ï¼American National Standards Instituteï¼ANSIï¼éç¨ä¸ºå ³ç³»æ°æ®åºç®¡çç³»ç»çæ åè¯è¨ï¼å为å½é æ ååç»ç»ï¼International Organization for Standardizationï¼ISOï¼é纳为å½é æ åã
ã2ãSQLè¯è¨å为äºä¸ªé¨åï¼
1ãæ°æ®æ¥è¯¢è¯è¨ï¼Data Query Languageï¼DQLï¼ï¼DQL主è¦ç¨äºæ°æ®çæ¥è¯¢ï¼å ¶åºæ¬ç»ææ¯ä½¿ç¨SELECTåå¥ï¼FROMåå¥åWHEREåå¥çç»åæ¥æ¥è¯¢ä¸æ¡æå¤æ¡æ°æ®ã
2ãæ°æ®æä½è¯è¨ï¼Data Manipulation Languageï¼DMLï¼ï¼DML主è¦ç¨äºå¯¹æ°æ®åºä¸çæ°æ®è¿è¡å¢å ãä¿®æ¹åå é¤çæä½ï¼å ¶ä¸»è¦å æ¬ï¼
1) INSERTï¼å¢å æ°æ®
2) UPDATEï¼ä¿®æ¹æ°æ®
3) DELETEï¼å é¤æ°æ®
3ãæ°æ®å®ä¹è¯è¨ï¼Data Definition Languageï¼DDLï¼ï¼DDL主è¦ç¨é对æ¯æ°æ®åºå¯¹è±¡ï¼æ°æ®åºã表ãç´¢å¼ãè§å¾ã触åå¨ãåå¨è¿ç¨ãå½æ°ï¼è¿è¡å建ãä¿®æ¹åå é¤æä½ãå ¶ä¸»è¦å æ¬ï¼
1) CREATEï¼å建æ°æ®åºå¯¹è±¡
2) ALTERï¼ä¿®æ¹æ°æ®åºå¯¹è±¡
3) DROPï¼å é¤æ°æ®åºå¯¹è±¡
4ã æ°æ®æ§å¶è¯è¨ï¼Data Control Languageï¼DCLï¼ï¼DCLç¨æ¥æäºæåæ¶è®¿é® æ°æ®åºçæéï¼å ¶ä¸»è¦å æ¬ï¼
1) GRANTï¼æäºç¨æ·æç§æé
2) REVOKEï¼åæ¶æäºçæç§æé
5ãäºå¡æ§å¶è¯è¨ï¼Transaction Control Languageï¼TCLï¼ï¼TCLç¨äºæ°æ®åºçäºå¡ç®¡çãå ¶ä¸»è¦å æ¬ï¼
1) START TRANSACTIONï¼å¼å¯äºå¡
2) COMMITï¼æ交äºå¡
3) ROLLBACKï¼åæ»äºå¡
4) SET TRANSACTIONï¼è®¾ç½®äºå¡çå±æ§
ã3ã认è¯æ°æ®åºè¡¨
表ï¼Tableï¼æ¯æ°æ®åºä¸æ°æ®åå¨æ常è§åæç®åçä¸ç§å½¢å¼ï¼æ°æ®åºå¯ä»¥å°å¤æçæ°æ®ç»æç¨è¾ä¸ºç®åçäºç»´è¡¨æ¥è¡¨ç¤ºãäºç»´è¡¨æ¯ç±è¡ååç»æçï¼åå«é½å å«çæ°æ®ï¼å¦è¡¨æ示ã
æ¯ä¸ªè¡¨é½æ¯ç±è¥å¹²è¡ååç»æçï¼å¨æ°æ®åºä¸è¡¨ä¸çè¡è¢«ç§°ä¸ºè®°å½ï¼è¡¨ä¸çå被称为æ¯è¿äºè®°å½çå段ã
è®°å½ä¹è¢«ç§°ä¸ºä¸è¡æ°æ®ï¼æ¯è¡¨éçä¸è¡ãå¨å ³ç³»åæ°æ®åºç表éï¼ä¸è¡æ°æ®æ¯æä¸æ¡å®æ´çè®°å½ã
å段æ¯è¡¨éçä¸åï¼ç¨äºä¿åæ¯æ¡è®°å½çç¹å®ä¿¡æ¯ãå¦ä¸è¡¨æ示çå¦çä¿¡æ¯è¡¨ä¸çå段å æ¬âå¦å·âãâå§åâãâæ§å«âåâå¹´é¾âãæ°æ®è¡¨çä¸åå å«äºæ个ç¹å®å段çå ¨é¨ä¿¡æ¯ã
ã4ãå建æ°æ®åºè¡¨ t_student
建ç«ä¸å¼ ç¨æ¥åå¨å¦çä¿¡æ¯ç表
å段å å«å¦å·ãå§åãæ§å«ï¼å¹´é¾ãå ¥å¦æ¥æãç级ï¼emailçä¿¡æ¯
å¦å·æ¯ä¸»é® = ä¸è½ä¸ºç©º + å¯ä¸
å§åä¸è½ä¸ºç©º
æ§å«é»è®¤å¼æ¯ç·
Emailå¯ä¸
ï¼1ï¼å建æ°æ®åºï¼
ï¼2ï¼æ°å»ºæ¥è¯¢ï¼
ï¼3ï¼å建æ°æ®åºè¡¨ï¼
##è¿æ¯ä¸ä¸ªåè¡æ³¨é
/*
å¤è¡æ³¨é
å¤è¡æ³¨é
å¤è¡æ³¨é
*/
/*
建ç«ä¸å¼ ç¨æ¥åå¨å¦çä¿¡æ¯ç表
å段å
å«å¦å·ãå§åãæ§å«ï¼å¹´é¾ãå
¥å¦æ¥æãç级ï¼emailçä¿¡æ¯
*/
-- å建æ°æ®åºè¡¨ï¼
create table t_student(
sno int(6), -- 6æ¾ç¤ºé¿åº¦
sname varchar(5), -- 5个å符
sex char(1),
age int(3),
enterdate date,
classname varchar(10),
email varchar(15)
);
-- æ¥ç表çç»æï¼å±ç¤ºè¡¨çå段详ç»ä¿¡æ¯
desc t_student;
-- æ¥ç表ä¸æ°æ®ï¼
select * from t_student;
-- æ¥ç建表è¯å¥ï¼
show create table t_student;
/*
CREATE TABLE `t_student` (
`sno` int DEFAULT NULL,
`sname` varchar(5) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`age` int DEFAULT NULL,
`enterdate` date DEFAULT NULL,
`classname` varchar(10) DEFAULT NULL,
`email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/
ã5ãæ°æ®åºè¡¨åç±»å
1.æ´æ°ç±»å
MySQLæ¯æéæ©å¨è¯¥ç±»åå ³é®ååé¢çæ¬å·å æå®æ´æ°å¼çæ¾ç¤ºå®½åº¦(ä¾å¦ï¼INT(4))ãæ¾ç¤ºå®½åº¦å¹¶ä¸éå¶å¯ä»¥å¨åå ä¿åçå¼çèå´ï¼ä¹ä¸éå¶è¶ è¿åçæå®å®½åº¦çå¼çæ¾ç¤º
主é®èªå¢ï¼ä¸ä½¿ç¨åºåï¼éè¿auto_incrementï¼è¦æ±æ¯æ´æ°ç±»å
2.æµ®ç¹æ°ç±»å
éè¦æ³¨æçæ¯ä¸æ´æ°ç±»åä¸ä¸æ ·çæ¯ï¼æµ®ç¹æ°ç±»åç宽度ä¸ä¼èªå¨æ©å ã score double(4,1)
score double(4,1)âå°æ°é¨å为1ä½ï¼æ»å®½åº¦4ä½ï¼å¹¶ä¸ä¸ä¼èªå¨æ©å ã
3.å符串类å
CHARåVARCHARç±»åç¸ä¼¼ï¼åç¨äºåäºè¾ççå符串ï¼ä¸»è¦çä¸åä¹å¤å¨äºåå¨æ¹å¼ãCHARç±»åé¿åº¦åºå®ï¼VARCHARç±»åçé¿åº¦å¯åã
å 为VARCHARç±»åè½å¤æ ¹æ®å符串çå®é é¿åº¦æ¥å¨ææ¹åæå åèç大å°ï¼æ以å¨ä¸è½æ确该åæ®µå ·ä½éè¦å¤å°å符æ¶æ¨è使ç¨VARCHARç±»åï¼è¿æ ·å¯ä»¥å¤§å¤§å°è约ç£ç空é´ãæé«åå¨æçã
CHARåVARCHAR表示çæ¯å符ç个æ°ï¼èä¸æ¯åèç个æ°
4.æ¥æåæ¶é´ç±»å
TIMESTEMPç±»åçæ°æ®æå®æ¹å¼ä¸DATETIMEåºæ¬ç¸åï¼ä¸¤è çä¸åä¹å¤å¨äºä»¥ä¸å ç¹ï¼
(1) æ°æ®çåå¼èå´ä¸åï¼TIMESTEMPç±»åçåå¼èå´æ´å°ã
(2) å¦ææ们对TIMESTAMPç±»åçå段没ææç¡®èµå¼ï¼ææ¯è¢«èµä¸äºNULLå¼ï¼MySQLä¼èªå¨å°è¯¥å段èµå¼ä¸ºç³»ç»å½åçæ¥æä¸æ¶é´ã
(3) TIMESTEMPç±»åè¿å¯ä»¥ä½¿ç¨CURRENT_TIMESTAMPæ¥è·åç³»ç»å½åæ¶é´ã
(4) TIMESTEMPç±»åæä¸ä¸ªå¾å¤§çç¹ç¹ï¼é£å°±æ¯æ¶é´æ¯æ ¹æ®æ¶åºæ¥æ¾ç¤ºçãä¾å¦ï¼å¨ä¸å «åºæå ¥çTIMESTEMPæ°æ®ä¸º2017-07-11 16:43:25ï¼å¨ä¸ä¸åºæ¾ç¤ºæ¶ï¼æ¶é´é¨åå°±åæäº15:43:25ï¼å¨ä¸ä¹åºæ¾ç¤ºæ¶ï¼æ¶é´é¨åå°±åæäº17:43:25ã
ã6ãDML_æ·»å æ°æ®
注æäºé¡¹
int 宽度æ¯æ¾ç¤ºå®½åº¦ï¼å¦æè¶ è¿ï¼å¯ä»¥èªå¨å¢å¤§å®½åº¦ intåºå±é½æ¯4个åè
æ¶é´çæ¹å¼å¤æ · â1256-12-23â â1256/12/23â â1256.12.23â
å符串ä¸åºååå¼å·ååå¼å·
å¦ä½åå ¥å½åçæ¶é´ now() , sysdate() , CURRENT_DATE()
char varchar æ¯å符ç个æ°ï¼ä¸æ¯åèç个æ°ï¼å¯ä»¥ä½¿ç¨binaryï¼varbinary表示å®é¿åä¸å®é¿çåè个æ°ã
å¦æä¸æ¯å ¨å段æå ¥æ°æ®çè¯ï¼éè¦å å ¥å段çåå
-- æ¥ç表记å½ï¼
select * from t_student;
-- å¨t_studentæ°æ®åºè¡¨ä¸æå
¥æ°æ®ï¼
insert into t_student values (1,'å¼ ä¸','ç·',18,'2022-5-8','软件1ç','[email protected]');
insert into t_student values (10010010,'å¼ ä¸','ç·',18,'2022-5-8','软件1ç','[email protected]');
insert into t_student values (2,'å¼ ä¸','ç·',18,'2022.5.8','软件1ç','[email protected]');
insert into t_student values (2,"å¼ ä¸",'ç·',18,'2022.5.8','软件1ç','[email protected]');
insert into t_student values (7,"å¼ ä¸",'ç·',18,now(),'软件1ç','[email protected]');
insert into t_student values (9,"æçåçº",'ç·',18,now(),'软件1ç','[email protected]');
insert into t_student (sno,sname,enterdate) values (10,'æå','2023-7-5');
ã7ãDML_ä¿®æ¹ï¼å é¤æ°æ®
注æäºé¡¹
1.å ³é®åï¼è¡¨åï¼å段åä¸åºå大å°å
2.é»è®¤æ åµä¸ï¼å 容ä¸åºå大å°å
3.å é¤æä½fromå ³é®åä¸å¯ç¼ºå°
4.ä¿®æ¹ï¼å é¤æ°æ®å«å¿è®°å éå¶æ¡ä»¶
-- ä¿®æ¹è¡¨ä¸æ°æ®
update t_student set sex = '女' ;
update t_student set sex = 'ç·' where sno = 10 ;
UPDATE T_STUDENT SET AGE = 21 WHERE SNO = 10;
update t_student set CLASSNAME = 'java01' where sno = 10 ;
update t_student set CLASSNAME = 'JAVA01' where sno = 9 ;
update t_student set age = 29 where classname = 'java01';
-- å é¤æä½ï¼
delete from t_student where sno = 2;
ã8ãDDL_ä¿®æ¹ï¼å é¤æ°æ®åºè¡¨
-- æ¥çæ°æ®ï¼
select * from t_student;
-- ä¿®æ¹è¡¨çç»æï¼
-- å¢å ä¸åï¼
alter table t_student add score double(5,2) ; -- 5:æ»ä½æ° 2ï¼å°æ°ä½æ°
update t_student set score = 123.5678 where sno = 1 ;
-- å¢å ä¸åï¼æ¾å¨æåé¢ï¼
alter table t_student add score double(5,2) first;
-- å¢å ä¸åï¼æ¾å¨sexåçåé¢ï¼
alter table t_student add score double(5,2) after sex;
-- å é¤ä¸åï¼
alter table t_student drop score;
-- ä¿®æ¹ä¸åï¼
alter table t_student modify score float(4,1); -- modifyä¿®æ¹æ¯åçç±»åçå®ä¹ï¼ä½æ¯ä¸ä¼æ¹ååçåå
alter table t_student change score score1 double(5,1); -- changeä¿®æ¹ååååçç±»åçå®ä¹
-- å é¤è¡¨ï¼
drop table t_student;
ã9ãDDLåDMLçè¡¥å
ã1ãsqlå±ç¤ºï¼
-- å建表ï¼
create table t_student(
sno int(6) primary key auto_increment,
sname varchar(5) not null,
sex char(1) default 'ç·' check(sex='ç·' || sex='女'),
age int(3) check(age>=18 and age<=50),
enterdate date,
classname varchar(10),
email varchar(15) unique
);
-- æ·»å æ°æ®ï¼
insert into t_student values (null,'å¼ ä¸','ç·',21,'2023-9-1','java01ç','[email protected]');
insert into t_student values (null,'æå','ç·',21,'2023-9-1','java01ç','[email protected]');
insert into t_student values (null,'é²é²','ç·',21,'2023-9-1','java01ç','[email protected]');
-- æ¥çå¦ç表ï¼
select * from t_student;
-- æ·»å ä¸å¼ 表ï¼å¿«éæ·»å ï¼ç»æåæ°æ®è·t_student é½æ¯ä¸è´ç
create table t_student2
as
select * from t_student;
select * from t_student2;
-- å¿«éæ·»å ï¼ç»æè·t_studentä¸è´ï¼æ°æ®æ²¡æï¼
create table t_student3
as
select * from t_student where 1=2;
select * from t_student3;
-- å¿«éæ·»å ï¼åªè¦é¨ååï¼é¨åæ°æ®ï¼
create table t_student4
as
select sno,sname,age from t_student where sno = 2;
select * from t_student4;
-- å é¤æ°æ®æä½ :æ¸
空æ°æ®
delete from t_student;
truncate table t_student;
ã2ãdeleteåtruncateçåºå«
ä»æç»çç»ææ¥çï¼è½ç¶ä½¿ç¨TRUNCATEæä½å使ç¨DELETEæä½é½å¯ä»¥å é¤è¡¨ä¸çå ¨é¨è®°å½ï¼ä½æ¯ä¸¤è è¿æ¯æå¾å¤åºå«çï¼å ¶åºå«ä¸»è¦ä½ç°å¨ä»¥ä¸å 个æ¹é¢ï¼
(1)DELETE为æ°æ®æä½è¯è¨DMLï¼TRUNCATE为æ°æ®å®ä¹è¯è¨DDLã
(2) DELETEæä½æ¯å°è¡¨ä¸ææè®°å½ä¸æ¡ä¸æ¡å é¤ç´å°å é¤å®ï¼TRUNCATEæä½åæ¯ä¿çäºè¡¨çç»æï¼éæ°å建äºè¿ä¸ªè¡¨ï¼ææçç¶æé½ç¸å½äºæ°è¡¨ãå æ¤ï¼TRUNCATEæä½çæçæ´é«ã
(3)DELETEæä½å¯ä»¥åæ»ï¼TRUNCATEæä½ä¼å¯¼è´éå¼æ交ï¼å æ¤ä¸è½åæ»ï¼å¨ç¬¬åç« ä¸ä¼è®²è§£äºå¡çæ交ååæ»ï¼ã
(4)DELETEæä½æ§è¡æååä¼è¿åå·²å é¤çè¡æ°ï¼å¦å é¤4è¡è®°å½ï¼åä¼æ¾ç¤ºâAffected rowsï¼4âï¼ï¼æªææä½ä¸ä¼è¿åå·²å é¤çè¡éï¼ç»æé常æ¯âAffected rowsï¼0âã
DELETEæä½å é¤è¡¨ä¸è®°å½åï¼å次å表ä¸æ·»å æ°è®°å½æ¶ï¼å¯¹äºè®¾ç½®æèªå¢çº¦æå段çå¼ä¼ä»å é¤å表ä¸è¯¥å段çæ大å¼å 1å¼å§èªå¢ï¼TRUNCATEæä½åä¼éæ°ä»1å¼å§èªå¢ã