第2章 SQL語句的分類和DML語句

1.SQL語句的分類

DDL語句（數(shù)據(jù)定義語句）
主要用于定義數(shù)據(jù)庫對(duì)象的SQL語句
數(shù)據(jù)庫對(duì)象：表(table)，列(column)扶镀，視圖(view)，索引(index)...
1. create: 創(chuàng)建
2. alter: 修改
3. replace: 替換
4. drop: 放棄（刪除）
5. truncate: 截?cái)?/li>
DML語句（數(shù)據(jù)操作語句）
主要用于數(shù)據(jù)的變更
1. insert 插入（新增數(shù)據(jù)）
2. update 更新（修改數(shù)據(jù)）
3. delete 刪除
4. merage 合并
TCL語句（事務(wù)控制語句）
主要用于事務(wù)控制
1. commit 提交事務(wù)
2. rollback 回滾事務(wù)
DQL語句（數(shù)據(jù)查詢語句）
主要用于查詢和檢索數(shù)據(jù)
1. select 查詢
DCL語句（數(shù)據(jù)控制語句）
主要用于數(shù)據(jù)庫權(quán)限管理
1. grant 授權(quán)
2. revoke 回收

2.DML語句（數(shù)據(jù)操作語句）

2.1 新增數(shù)據(jù)（insert）

向表中增加一條記錄

為所有的列都賦值

dept表

INSERT INTO dept VALUES(1,'開發(fā)一部','沈陽');

執(zhí)行后的效果

為指定的列賦值
為指定的列進(jìn)行賦值，未指定的列的值默認(rèn)就是null
如果某個(gè)列是not null硬毕，則它必須被賦值

INSERT INTO dept(deptno,dname) VALUES(2,'開發(fā)二部');

一次插入多條數(shù)據(jù)（MySQL方言）(了解)

INSERT INTO dept VALUES(3,'開發(fā)三部','沈陽'),(4,'開發(fā)四部','沈陽');

2.2 刪除數(shù)據(jù)（delete）

從表中移除一些數(shù)據(jù)

無條件刪除（全刪除）

DELETE FROM dept;

有條件刪除（根據(jù)設(shè)定條件進(jìn)行刪除）

刪除deptno的值是4的部門記錄

DELETE FROM dept WHERE deptno = 4;

刪除loc的值是"沈陽"的部門記錄

DELETE FROM dept WHERE loc = '沈陽';

邏輯-與 AND
X AND Y必須同時(shí)滿足兩個(gè)條件X和Y
刪除loc是“北京”同時(shí)名字是“開發(fā)一部”的記錄

DELETE FROM dept WHERE dname = '開發(fā)一部' AND loc = '北京'

邏輯-或 OR
X OR Y必須滿足X和Y其中任一條件
刪除loc是“北京”或者名字是“開發(fā)一部”的記錄

DELETE FROM dept WHERE dname = '開發(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語句表示刪除表中數(shù)據(jù)吐咳，這個(gè)操作是可以恢復(fù)的
drop DDL語句表示刪除整張表，包含表結(jié)構(gòu)元践，不可恢復(fù)
truncate DDL語句表示截?cái)啾砭录梗粍h除表結(jié)構(gòu)只清空數(shù)據(jù)，數(shù)據(jù)不可恢復(fù)单旁，刪除速度快

2.3 修改數(shù)據(jù)（update）

修改表中一些數(shù)據(jù)

無條件修改（全表進(jìn)行更新）
將dept表中所有數(shù)據(jù)的dname更新為“開發(fā)部”沪羔，loc更新為“北京”

UPDATE dept SET dname = '開發(fā)部',loc = '北京'

將emp表中所有數(shù)據(jù)的sal增加200

UPDATE emp SET sal = sal + 200

有條件修改（符合條件記錄進(jìn)行修改）
將dept表中的dname是“開發(fā)二部”的數(shù)據(jù)的loc設(shè)置為“北京”

UPDATE dept SET loc = '北京' WHERE dname = '開發(fā)二部'

3.事務(wù)的概念

事務(wù)（Transaction），一般是指要做的或所做的事情象浑。在計(jì)算機(jī)術(shù)語中是指訪問并可能更新數(shù)據(jù)庫中各種數(shù)據(jù)項(xiàng)的一個(gè)程序執(zhí)行單元(unit)蔫饰。
比如說轉(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語句必須同時(shí)執(zhí)行組成了一個(gè)完整的執(zhí)行單元，必須同時(shí)成功

事務(wù)的四個(gè)特性

原子性：組成事務(wù)的這些SQL語句是一個(gè)整體愉豺，不可能繼續(xù)劃分
一致性：這些SQL語句要么同時(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è)語句的執(zhí)行結(jié)果同時(shí)執(zhí)行到數(shù)據(jù)庫中

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è)語句的執(zhí)行結(jié)果同時(shí)撤銷杖剪，不會(huì)對(duì)數(shù)據(jù)庫產(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','PROGRAMER',1013,'1995-10-11',3700,NULL,10);