1.SQL語(yǔ)句的分類
-
DDL語(yǔ)句（數(shù)據(jù)定義語(yǔ)句）
主要用于定義數(shù)據(jù)庫(kù)對(duì)象的SQL語(yǔ)句
數(shù)據(jù)庫(kù)對(duì)象：表(table)鸭限，列(column)，視圖(view)，索引(index)...- create: 創(chuàng)建
- alter: 修改
- replace: 替換
- drop: 放棄（刪除）
- truncate: 截?cái)?/li>
-
DML語(yǔ)句（數(shù)據(jù)操作語(yǔ)句）
主要用于數(shù)據(jù)的變更- insert 插入（新增數(shù)據(jù)）
- update 更新（修改數(shù)據(jù)）
- delete 刪除
- merage 合并
-
TCL語(yǔ)句（事務(wù)控制語(yǔ)句）
主要用于事務(wù)控制- commit 提交事務(wù)
- rollback 回滾事務(wù)
-
DQL語(yǔ)句（數(shù)據(jù)查詢語(yǔ)句）
主要用于查詢和檢索數(shù)據(jù)- select 查詢
-
DCL語(yǔ)句（數(shù)據(jù)控制語(yǔ)句）
主要用于數(shù)據(jù)庫(kù)權(quán)限管理- grant 授權(quán)
- revoke 回收
2.DML語(yǔ)句（數(shù)據(jù)操作語(yǔ)句）
2.1 新增數(shù)據(jù)（insert）
語(yǔ)法：
INSERT INTO 表名稱 VALUES (值1, 值2,....)
向表中增加一條記錄
-
為所有的列都賦值
image
INSERT INTO dept VALUES(1,'開(kāi)發(fā)一部','沈陽(yáng)');
image
- 為指定的列賦值
為指定的列進(jìn)行賦值，未指定的列的值默認(rèn)就是null
如果某個(gè)列是not null痪伦，則它必須被賦值
INSERT INTO dept(deptno,dname) VALUES(2,'開(kāi)發(fā)二部');
- 一次插入多條數(shù)據(jù)（MySQL方言）(了解)
INSERT INTO dept VALUES(3,'開(kāi)發(fā)三部','沈陽(yáng)'),(4,'開(kāi)發(fā)四部','沈陽(yáng)');
2.2 刪除數(shù)據(jù)（delete）
從表中移除一些數(shù)據(jù)
- 無(wú)條件刪除（全刪除）
DELETE FROM dept;
- 有條件刪除 （根據(jù)設(shè)定條件進(jìn)行刪除）
- 刪除deptno的值是4的部門(mén)記錄
DELETE FROM dept WHERE deptno = 4;
- 刪除loc的值是"沈陽(yáng)"的部門(mén)記錄
DELETE FROM dept WHERE loc = '沈陽(yáng)';
- 邏輯-與 AND
X AND Y必須同時(shí)滿足兩個(gè)條件X和Y
刪除loc是“北京”同時(shí)名字是“開(kāi)發(fā)一部”的記錄
DELETE FROM dept WHERE dname = '開(kāi)發(fā)一部' AND loc = '北京'
- 邏輯-或 OR
X OR Y必須滿足X和Y其中任一條件
刪除loc是“北京”或者名字是“開(kāi)發(fā)一部”的記錄
DELETE FROM dept WHERE dname = '開(kāi)發(fā)一部' OR loc = '北京'
- 邏輯-非 NOT
表示邏輯取反 X = 12 取反 X != 12 X <> 12
X > 12 取反 X <= 12
刪除學(xué)生考試分?jǐn)?shù)在[80,90]區(qū)間的記錄
DELETE FROM student WHERE score >= 80 and score <= 90
DELETE FROM student WHERE score between 80 and 90;
刪除學(xué)生考試分?jǐn)?shù)不在[80,90]區(qū)間的記錄
DELETE FROM student WHERE score < 80 or score > 90
DELETE FROM student WHERE score NOT between 80 and 90;
面試題：delete，drop和truncate的區(qū)別雹锣？
delete DML語(yǔ)句 表示刪除表中數(shù)據(jù)网沾，這個(gè)操作是可以恢復(fù)的
drop DDL語(yǔ)句 表示刪除整張表，包含表結(jié)構(gòu)蕊爵， 不可恢復(fù)
truncate DDL語(yǔ)句 表示截?cái)啾砘愿纾粍h除表結(jié)構(gòu)只清空數(shù)據(jù)， 數(shù)據(jù)不可恢復(fù)在辆，刪除速度快
2.3 修改數(shù)據(jù)（update）
修改表中一些數(shù)據(jù)
- 無(wú)條件修改（全表進(jìn)行更新）
將dept表中所有數(shù)據(jù)的dname更新為“開(kāi)發(fā)部”证薇，loc更新為“北京”
UPDATE dept SET dname = '開(kāi)發(fā)部',loc = '北京'
將emp表中所有數(shù)據(jù)的sal增加200
UPDATE emp SET sal = sal + 200
- 有條件修改（符合條件記錄進(jìn)行修改）
將dept表中的dname是“開(kāi)發(fā)二部”的數(shù)據(jù)的loc設(shè)置為“北京”
UPDATE dept SET loc = '北京' WHERE dname = '開(kāi)發(fā)二部'
3.事務(wù)的概念
事務(wù)（Transaction），一般是指要做的或所做的事情匆篓。在計(jì)算機(jī)術(shù)語(yǔ)中是指訪問(wèn)并可能更新數(shù)據(jù)庫(kù)中各種數(shù)據(jù)項(xiàng)的一個(gè)程序執(zhí)行單元(unit)浑度。
比如說(shuō)轉(zhuǎn)賬的例子是事務(wù)(101賬號(hào)給102賬號(hào)轉(zhuǎn)賬100)
update 賬戶 set 余額 = 余額 - 100 where 賬號(hào)= 101
update 賬戶 set 余額 = 余額 + 100 where 賬號(hào)= 102
兩條SQL語(yǔ)句必須同時(shí)執(zhí)行組成了一個(gè)完整的執(zhí)行單元，必須同時(shí)成功
事務(wù)的四個(gè)特性
- 原子性：組成事務(wù)的這些SQL語(yǔ)句是一個(gè)整體鸦概，不可能繼續(xù)劃分
- 一致性：這些SQL語(yǔ)句要么同時(shí)成功箩张，要么同時(shí)失敗。不允許只成功一部分
- 隔離性：不同事務(wù)之間不能互相干擾
- 持久性：事務(wù)執(zhí)行成功后窗市，數(shù)據(jù)有效地保存
事務(wù)控制
set AutoCommit false; -- 關(guān)閉自動(dòng)提交
update 賬戶 set 余額 = 余額 - 100 where 賬號(hào)= 101;
update 賬戶 set 余額 = 余額 + 100 where 賬號(hào)= 102;
commit; -- 手動(dòng)提交
當(dāng)執(zhí)行commit時(shí)會(huì)將兩個(gè)語(yǔ)句的執(zhí)行結(jié)果同時(shí)執(zhí)行到數(shù)據(jù)庫(kù)中
set AutoCommit false; -- 關(guān)閉自動(dòng)提交
update 賬戶 set 余額 = 余額 - 100 where 賬號(hào)= 101;
update 賬戶 set 余額 = 余額 + 100 where 賬號(hào)= 102;
rollback; -- 手動(dòng)回滾
當(dāng)執(zhí)行rollback時(shí)會(huì)將兩個(gè)語(yǔ)句的執(zhí)行結(jié)果同時(shí)撤銷先慷，不會(huì)對(duì)數(shù)據(jù)庫(kù)產(chǎn)生影響
4.查詢數(shù)據(jù)準(zhǔn)備
4.1 建表
- 創(chuàng)建location表
CREATE TABLE location (
locid varchar(64) NOT NULL,
adress varchar(128) DEFAULT NULL,
PRIMARY KEY (locid)
)
- 創(chuàng)建dept表
CREATE TABLE dept (
deptno int(8) NOT NULL,
dname varchar(64) NOT NULL,
loc varchar(64) DEFAULT NULL,
PRIMARY KEY (deptno)
)
- 創(chuàng)建emp表
CREATE TABLE emp (
empno int(8) NOT NULL,
ename varchar(64) NOT NULL,
job varchar(64) DEFAULT NULL,
mgr int(8) DEFAULT NULL,
hiredate date DEFAULT NULL,
sal int(8) DEFAULT NULL,
comm int(8) DEFAULT NULL,
deptno int(8) DEFAULT NULL,
PRIMARY KEY (empno),
KEY `FK_EMP_DEPTNO` (deptno),
CONSTRAINT `FK_EMP_DEPTNO` FOREIGN KEY (deptno) REFERENCES dept (deptno)
)
4.2 創(chuàng)建數(shù)據(jù)
- location表數(shù)據(jù)
insert into location(locid,adress) values ('BOSTON','110, NO.5 Street');
insert into location(locid,adress) values ('DALLAS','No.12 Farm');
insert into location(locid,adress) values ('NEW YORK','NO 121 Queen Street ');
- dept表數(shù)據(jù)
insert into dept(deptno,dname,loc) values (10,'RESEARCH','BOSTON');
insert into dept(deptno,dname,loc) values (20,'SALES','NEW YORK');
insert into dept(deptno,dname,loc) values (30,'MANAGE','DALLAS');
insert into dept(deptno,dname,loc) values (40,'HRD','NEW YORK');
- emp表數(shù)據(jù)
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1001,'TOM','PROGRAMER',1004,'1994-12-22',3800,NULL,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1002,'SMITH','PROGRAMER',1004,'1994-07-05',3500,NULL,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1003,'JACK','CLERK',1004,'1992-02-12',2500,NULL,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1004,'SCOTT','ANALIST',1007,'1992-01-15',4500,NULL,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1005,'ALEX','CLERK',1006,'1993-05-07',2800,NULL,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1006,'FORD','MANAGER',1007,'1994-07-05',4300,NULL,NULL);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1007,'KING','PESIDENT',NULL,'1992-01-01',7000,NULL,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1008,'MOON','MANAGER',1007,'1992-01-15',5000,1200,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1009,'TED','SALESMAN',1008,'1994-07-14',2500,1500,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1010,'JUCY','SALESMAN',1008,'1998-08-07',2200,800,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1011,'LEE','SALESMAN',1008,'1999-07-12',2200,500,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1012,'AMY','CLERK',1008,'1994-08-05',2200,NULL,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1013,'EMLIY','ANALIST',1007,'1996-06-29',4500,NULL,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1014,'ZORO','PROGRAME