一厢汹、數(shù)據(jù)操作語言
Data Manipulation Language ,簡稱DML霉咨，主要用來實現(xiàn)對數(shù)據(jù)庫表中的數(shù)據(jù)進行操作脖阵。
1.插入數(shù)據(jù)
-
使用INSERT語句向表中插入數(shù)據(jù)污秆，語法結構如下：
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
例如：
INSERT INTO dept (deptno, dname, loc)
VALUES (50, 'DEVELOPMENT', 'DETROIT');
-
插入空值NULL
隱含法: 在列名列表中忽略該列劈猪。
INSERT INTO dept (deptno, dname )
VALUES (60, 'MIS');
顯示法: 指定 NULL關鍵字或者''。
INSERT INTO dept
VALUES (70, 'FINANCE', NULL);
-
插入日期值
SYSDATE() 函數(shù)記錄當前日期和時間
INSERT INTO emp (empno, ename, job,mgr, hiredate, sal, comm,deptno)
VALUES (7196, 'GREEN', 'SALESMAN',7782, SYSDATE(), 2000, NULL,6 10);
-
批量插入數(shù)據(jù)
使用insert語句可以一次性地向表批量插入多條記錄良拼，語法格式如下：
INSERT INTO EMP(EMPNO, ENAME, JOB, SAL) VALUES
('8881', '張三', '部門經(jīng)理', 6000),
('8882', '李四', '職員', 3000),
('8883', '王五', '職員', 3500),
('8884' ,'趙六', '部門經(jīng)理', 6500),
('8885', '高七', '職員', 2500),
('8886', '馬八', '職員', 3100),
('8887', '錢九', '部門經(jīng)理', 5000),
('8888', '孫十', '職員', 2800);
-
創(chuàng)建manager表
CREATE TABLE manager
AS SELECT * FROM emp WHERE 1=0;
-
向manager表中插入職位為MANAGER的記錄
INSERT INTO manager
SELECT *
FROM emp
WHERE job = 'MANAGER';
2.修改數(shù)據(jù)
修改數(shù)據(jù)使用UPDATE子句完成战得，語法結構如下：
UPDATE table
SET column = value [, column = value]
[WHERE condition];
? WHERE子句用來限定修改哪些行。
? SET子句用來限定修改哪些列庸推。
? WHERE子句中的更新條件是一個邏輯表達式常侦，通常需要使用到關系運算符和邏輯運算符浇冰，返回True或者False。
例：
把員工編號為7782的部門編號修改為20
UPDATE emp
SET deptno = 20
WHERE empno = 7782;
把所有員工的部門編號修改為20
UPDATE emp
SET deptno = 20;
3.刪除數(shù)據(jù)
刪除數(shù)據(jù)主要用來按照指定條件從表中刪除某些行聋亡。
- 刪除選中記錄
– 刪除職位是CLERK的員工記錄
DELETE FROM emp
WHERE job = 'CLERK';
- 刪除全部記錄
– 刪除所有員工記錄
DELETE FROM emp;
- 截斷表
– 截斷表語法
TRUNCATE TABLE emp;
二湖饱、事務處理
-
事務(Transaction)概念
事務：也稱工作單元，是由一個或多個SQL語句所組成的操作序列杀捻，這些SQL語句作為一個完整的工作單元井厌，要么全部執(zhí)行成功，要么全部執(zhí)行失敗致讥。在數(shù)據(jù)庫中仅仆，通過事務來保證數(shù)據(jù)的一致性。
事務處理語言：Transaction Process Language ,簡稱TPL垢袱，主要用來對組成事務的DML語句的操作結果進行確認或取消墓拜。確認也就是使DML操作生效，使用提交(COMMIT)命令實現(xiàn)请契；取消也就是使DML操作失效咳榜，使用回滾(ROLLBACK)命令實現(xiàn)。通過事務的使用爽锥，能防止數(shù)據(jù)庫中出現(xiàn)數(shù)據(jù)不一致現(xiàn)象涌韩。
如兩個銀行賬戶進行轉賬，涉及到兩條更新操作氯夷，這兩條更新操作只允許全部成功或失敗臣樱，否則數(shù)據(jù)會出現(xiàn)不一致的現(xiàn)象。
-
事務組成
– 在數(shù)據(jù)庫中腮考，事務由一組相關的DML或SELECT語句雇毫，加上一個TPL語句（COMMIT、ROLLBACK）或一個DDL語句(CREATE踩蔚、ALTER棚放、DROP、TRUNCATE等)或一個DCL（GRANT馅闽、REVOKE）語句飘蚯。
– 例：如下語句組成兩個事務。
? INSERT….
? UPDATE….
? DELETE….
? SELECT….
? INSERT…
? COMMIT;-- 前6條語句捞蛋，組成第1個事務
? UPDATE…
? DELETE….
? CREATE… ;--后3條語句孝冒，組成第2個事務
-
事務特征可用四個字母的縮寫表示：即ACID
– 原子性（Atomicity）
事務就像“原子”一樣，不可被分割拟杉，組成事務的DML操作語句要么全成功，要么全失敗量承，不可能出現(xiàn)部分成功部分失敗的情況搬设。
– 一致性（Consistency）
一旦事務完成穴店，不管是成功的，還是失敗的拿穴，整個系統(tǒng)處于數(shù)據(jù)一致的狀態(tài)泣洞。
– 隔離性（Isolation）
一個事務的執(zhí)行不會被另一個事務所干擾。比如兩個人同時從一個賬戶從取錢默色，通過事務的隔離性確保賬戶余額的正確性球凰。
– 持久性（Durability）
也稱為永久性，指事務一旦提交腿宰，對數(shù)據(jù)的改變就是永久的呕诉，不可以再被回滾。
-
MySQL的事務處理主要有兩種方法
1.用begin,rollback,commit來實現(xiàn)
begin開始一個事務
rollback事務回滾
commit 事務提交
2.直接用set來改變MySQL的自動提交模式
MySQL默認是自動提交的凌节，也就是你提交一個sql眉厨，就直接執(zhí)行天试！可以通過
set autocommit = 0 禁止自動提交
set autocommit = 1 開啟自動提交
來實現(xiàn)事務的處理。
但要注意當用set autocommit = 0 的時候伊者，以后所有的sql都將作為事務處理，直到用commit確認或 rollback結束间护，注意當結束這個事務的同時也開啟了新的事務亦渗！按第一種方法只將當前的做為一個事務!
-
隱式結束
1.隱式提交：當下列任意一種情況發(fā)生時，會發(fā)生隱式提交
-執(zhí)行一個DDL(CREATE汁尺、ALTER央碟、DROP、TRUNCATE均函、RENAME）語句亿虽；
-執(zhí)行一個DCL(GRANT、REVOKE)語句苞也；
2.隱式回滾：當下列任意一種情況發(fā)生時洛勉，會發(fā)生隱式回滾
-客戶端強行退出
-客戶端連接到服務器端異常中斷
-系統(tǒng)崩潰
-
設置保存點：
如果在一個事務內(nèi)，想要回滾到指定位置如迟，不是回滾到事務的起始點收毫，可以通過保存點(SAVEPOINT)來實現(xiàn)。
– SAVEPOINT savepointname殷勘；--定義一個保存點語句此再；
– ROLLBACK TO savepointname;--回滾到指定保存點
– 注意：如上兩條語句不結束事務的執(zhí)行。
BEGIN; ——開始事務操作
DELETE FROM test ;
ROLLBACK; ——撤消DELETE操作
INSERT INTO test VALUES(’A’);
SAVEPOINT insert_a; ——定義insert_a保存點
INSERT INTO test VALUES(’B’);
SAVEPOINT insert_b; ——定義insert_b保存點
INSERT INTO test VALUES(’C’);
ROLLBACK TO insert_b; ——撤消操作到insert_b保存點
DELETE FROM test WHERE test_str = ‘A’;
COMMIT; ——將所有修改寫入數(shù)據(jù)庫
ROLLBACK; -—所有操作已經(jīng)COMMIT提交玲销，不能回滾
課后作業(yè)
1.使用如下語句输拇，創(chuàng)建學生表student和班級表class
create table student ( --學生表
xh char(4),--學號
xm varchar(10),--姓名
sex char(2),--性別
birthday date,--出生日期
sal double(7,2), --獎學金
studentcid int(2) --學生班級號
);
Create table class ( --班級表
classid in(2), --班級編號
cname varchar(20),--班級名稱
ccount int(3) --班級人數(shù)
);
2.基于上述學生表和班級表，完成如下問題
（1）添加三個班級信息為：
1贤斜，JAVA1班策吠，null
2逛裤，JAVA2班，null
3猴抹，JAVA3班带族，null
（2）添加學生信息如下：‘A001’,‘張三’,‘男’,‘01-5月-05’,100,1
（3）添加學生信息如下：'A002','MIKE','男','1905-05-06',10
（4）插入部分學生信息： 'A003','JOHN','女’
（5）將A001學生性別修改為'女‘
（6）將A001學生信息修改如下：性別為男，生日設置為1980-04-01
（7）將生日為空的學生班級修改為Java3班
（8）請使用一條SQL語句蟀给，使用子查詢蝙砌，更新班級表中每個班級的人數(shù)
字段
3.使用如下語句，建立以下表
CREATE TABLE copy_emp (
empno int(4),
ename varchar(20),
hiredate date,
deptno int(2),
sal double(8,2));
4.在第三題表的基礎上跋理，完成下列問題
(1)在表copy_emp中插入數(shù)據(jù)择克，要求sal字段插入空值，部門號50薪介，參加工作時間為2000年1月1日祠饺，其他字段隨意
(2)在表copy_emp中插入數(shù)據(jù)，要求把emp表中部門號為10號部門的員工信息插入
(3)修改copy_emp表中數(shù)據(jù)汁政，要求10號部門所有員工漲20%的工資
(4)修改copy_emp表中sal為空的記錄道偷，工資修改為平均工資
(5)把工資為平均工資的員工，工資修改為空
(6)另外打開窗口2查看以上修改
(7)執(zhí)行commit记劈，窗口2中再次查看以上信息
(8)刪除工資為空的員工信息
(9)執(zhí)行rollback
CREATE TABLE student(
xh CHAR(4),
xm VARCHAR(10),
sex CHAR(2),
birthday date,
sal DOUBLE(7,2),
studentcid INT(2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE class( -- 班級表
classid INT(2),-- 班級編號
cname VARCHAR(20),-- 班級名稱
ccount INT(3)-- 班級人數(shù)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT class(classid,cname,ccount)
VALUE (1,'JAVA1班',NULL),
(2,'JAVA2班',NULL),
(3,'JAVA3班',NULL);
INSERT student(xh,xm,sex,birthday,sal,studentcid)
VALUE ('A001','張三','男','2001-05-05',100,1),
('A002','MIKE','男','1905-05-06',10,NULL);
INSERT student(xh,xm,sex)
VALUE ('A003','JOHN','女');
UPDATE student
SET sex='女'
WHERE xh='A001';
UPDATE student
SET sex='男',birthday='1980-04-01'
WHERE xh='A001';
UPDATE student
SET studentcid='3'
WHERE student.birthday is NULL;
UPDATE class
SET ccount=20
WHERE classid=1;
CREATE TABLE copy_emp(
empno INT(4),
ename VARCHAR(20),
hiredate date,
deptno INT(2),
sal DOUBLE(8,2)
);
INSERT copy_emp(empno,ename,hiredate,deptno,sal)
VALUE (50,'SALE','2001-01-01',50,NULL);
INSERT INTO copy_emp
(SELECT empno,ename,hiredate,deptno,sal FROM emp WHERE deptno=10);
UPDATE copy_emp
SET sal=sal*1.2
WHERE deptno=10;
CREATE TABLE ave(
average VARCHAR(30));
INSERT INTO ave
(SELECT avg(sal) FROM copy_emp);
UPDATE copy_emp
SET sal=(SELECT average FROM ave)
WHERE sal IS NULL;
UPDATE copy_emp
SET sal=NULL
WHERE empno = '50';
COMMIT;
DELETE FROM copy_emp
WHERE sal is NULL;
ROLLBACK;