【如何成為SQL高手】第二關：表記錄的插入、更新、洗掉

👨?🎓 博主介紹：
IT邦德，江湖人稱jeames007，10年DBA作業經驗
中國DBA聯盟(ACDU)成員，目前從事DBA及程式編程

SQL對于現在的互聯網公司產研崗位幾乎是一個必備技能，但僅會SQL的話，應該是什么都做不了，
1.如果你是資料分析師，你需要熟練地把自己腦子里的資料和指標需求翻譯成SQL邏輯去查詢資料，進而完成自己的資料分析報告等，你的產出是分析報告，而不是SQL代碼；
2.如果你是數倉工程師（偏應用層），你需要根據業務邏輯去設計模型，撰寫調度任務去產出資料，以供業務人員使用，你的產出是資料模型和表；
3.如果你是演算法工程師，你可能需要用SQL來實作用戶標簽、特征工程等作業，但是這些是為你的模型訓練評估做基礎準備作業，你的產出是可以提升某些指標的演算法模型，

所以，SQL每個人都要用，但是用來衡量產出的并不是SQL本身，你需要用這個工具，去創造其它的價值，
IT邦德老師帶你成為SQL高手，那我們開始吧~

1.插入記錄

1.1 單一插入

按照下圖的內容向tb_class表中插入記錄

##建表
CREATE TABLE tb_class (
 classNo CHAR(6) PRIMARY KEY NOT NULL,
 className VARCHAR(20) NOT NULL,
 department VARCHAR(20),
 grade ENUM('1','2','3','4'), 
 classNum TINYINT,
 constraint uq_class unique(className)
) engine=InnoDB default charset=gb2312;

INSERT INTO tb_class(classNo,department,className) VALUES('AC1301', '會計學院', '會計 13-1 班');
INSERT INTO tb_class(classNo,department,className) VALUES('AC1302', '會計學院', '會計 13-2 班');
INSERT INTO tb_class(classNo,department,className) VALUES('CS1401', '計算機學院', '計算機 14-1 班');
INSERT INTO tb_class(classNo,department,className) VALUES('IS1301', '資訊學院', '資訊系統 13-1 班');
INSERT INTO tb_class(classNo,department,className) VALUES('IS1401', '資訊學院', '資訊系統 14-1 班');

1.2 批量插入

使用批量插入記錄的方法，一次性向tb_student表中插入下圖所示的記錄

##建表
CREATE TABLE tb_student (
 studentNo CHAR(10) NOT NULL,
 studentName VARCHAR(10) NOT NULL,
 sex CHAR(2),
 birthday DATE, 
 native VARCHAR(20),
 nation VARCHAR(20) default '漢',
 classNo CHAR(6),
 constraint pk_student primary key(studentNo)
) engine=InnoDB default charset=gb2312;

INSERT INTO tb_student
values ('2013110101', '張曉勇', '男', '1977-12-11','山西','漢','AC1301'),
('2013110103', '王一敏', '女', '1996-03-25','河北','漢','AC1301'),
('2013110201', '江山', '女', '1996-09-17','內蒙','錫伯','AC1302'),
('2013110202', '李明', '男', '1996-01-14','廣西','壯','AC1302'),
('2013310101', '黃菊', '女', '1995-09-30','北京','漢','IS1301'),
('2013310102', '林海', '男', '1996-01-18','北京','滿','IS1301'),
('2013310103', '吳昊', '男', '1995-11-18','河北','漢','IS1301'),
('2014210101', '劉濤', '男', '1997-04-03','湖南','侗','CS1401'),
('2014210102', '郭志堅', '男', '1997-04-03','上海','漢','CS1401'),
('2014210103', '王玲', '女', '1998-02-21','安徽','漢','CS1401'),
('2014310101', '王林', '男', '1996-10-09','河南','漢','IS1401'),
('2014310102', '李怡然', '女', '1996-12-31','遼寧','漢','IS1401');

1.3 其他

向tb_student表中插入一條新的記錄，學號為’2015310103’,姓名為’李彤’，
性別為’男’，民族為‘傣’，班級編號為’IS1401’（注意，這是部分欄位的值）

INSERT INTO tb_student(studentNo,studentName,sex,nation,classNo) 
values('2015310103','李彤','男','傣','IS1401');

向tb_student1表中插入tb_student表中所有漢族學生的資訊

##創建tb_student1表
create table tb_student1 select * from tb_student where 1=3;

##插入記錄
insert into tb_student1 select * from tb_student where nation='漢';

2.更新記錄

在tb_student表中，使用replace 陳述句把學號為“2014310102”的學生姓名替換為李怡
update tb_student set studentName=replace(studentName,‘然’,’’) where studentNo=‘2014310102’;

使用update陳述句，把tb_student表中學號為’2014210101’的學生姓名更改為’黃濤’.
update tb_student set studentName=‘黃濤’ where studentNo=‘2014210101’;

3.洗掉記錄

洗掉tb_student表中姓名為“王一敏“的學生資訊，
delete from tb_student where studentName=‘王一敏’;

使用truncate陳述句洗掉tb_student1表中的所有資料
truncate table tb_student1;

4.技能拓展

4.1 drop、truncate和delete的區別

（1）DELETE陳述句執行洗掉的程序是每次從表中洗掉一行，
并且同時將該行的洗掉操作作為事務記錄在日志中保存以便進行進行回滾操作，

 TRUNCATE TABLE 則一次性地從表中洗掉所有的資料并不把單獨的洗掉操作記錄記入日志保存，
 洗掉行是不能恢復的，并且在洗掉的程序中不會激活與表有關的洗掉觸發器，執行速度快，

（2）表和索引所占空間，
   當表被TRUNCATE 后，這個表和索引所占用的空間會恢復到初始大小，
   DELETE操作不會減少表或索引所占用的空間，
   drop陳述句將表所占用的空間全釋放掉，
   
（3）一般而言，drop > truncate > delete

（4）應用范圍，
   TRUNCATE 只能對TABLE；         
   DELETE可以是table和view

（5）TRUNCATE 和DELETE只洗掉資料， DROP則洗掉整個表（結構和資料），

（6）truncate與不帶where的delete ：只洗掉資料，
而不洗掉表的結構（定義）
drop陳述句將洗掉表的結構被依賴的約束（constrain),觸發器（trigger)索引（index);依賴于該表的存盤程序/函式將被保留，但其狀態會變為：invalid，

（7）delete陳述句為DML（data maintain Language),這個操作會被放到 rollback segment中,事務提交后才生效，如果有相應的 tigger,執行的時候將被觸發，

（8）truncate、drop是DLL（data define language),操作立即生效，原資料不放到 rollback segment中，不能回滾

（9）在沒有備份情況下，謹慎使用 drop 與 truncate，
要洗掉部分資料行采用delete且注意結合where來約束影響范圍，
回滾段要足夠大，
要洗掉表用drop;若想保留表而將表中資料洗掉，如果于事務無關，
用truncate即可實作，如果和事務有關，或老師想觸發trigger,還是用delete，

（10） Truncate table 表名 速度快,而且效率高,因為: 
truncate table 在功能上與不帶 WHERE 子句的 DELETE 陳述句相同：二者均洗掉表中的全部行，
但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少，
DELETE 陳述句每次洗掉一行，并在事務日志中為所洗掉的每行記錄一項，
TRUNCATE TABLE 通過釋放存盤表資料所用的資料頁來洗掉資料，并且只在事務日志中記錄頁的釋放， 

（11） TRUNCATE TABLE 洗掉表中的所有行，但表結構及其列、約束、索引等保持不變，
新行標識所用的計數值重置為該列的種子，如果想保留標識計數值，請改用 DELETE，
如果要洗掉表定義及其資料，請使用 DROP TABLE 陳述句， 

（12） 對于由 FOREIGN KEY 約束參考的表，不能使用 TRUNCATE TABLE，
而應使用不帶 WHERE 子句的 DELETE 陳述句，
由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發器，

4.2 總結

1、在速度上，一般來說，drop> truncate > delete，
2、在使用drop和truncate時一定要注意，雖然可以恢復，但為了減少麻煩，還是要慎重，
3、如果想洗掉部分資料用delete，注意帶上where子句，回滾段要足夠大；
   如果想洗掉表，當然用drop； 
   如果想保留表而將所有資料洗掉，如果和事務無關，用truncate即可；
   如果和事務有關，或者想觸發trigger，還是用delete；
   如果是整理表內部的碎片，可以用truncate跟上reuse stroage，再重新匯入/插入資料，

大家點贊、收藏、關注、評論啦 👇🏻👇🏻👇🏻微信公眾號👇🏻👇🏻👇🏻