Mysql - 增刪改

標籤：on()   bit   通過   .com   upd   資料庫   原因   測試   影響   

因為項目原因, mysql用了兩年了, 但是一直都未曾去總結過. 最近也是領導讓總結項目, 才想起把mysql的使用小結一下.

一、 Create

1. 單條插入, sql格式: insert into (列名) values(列值);

INSERT INTO test.tch_teacher (  Sex, BId, NO, NAME, IsDoublePosition, CreateDate )VALUES (  1, ‘123123123‘, ‘123123123‘, ‘Insert‘, 0, NOW() );

對於自增的 Id, 是不需要寫的, 資料庫會自動產生, 但是如果一不小心寫上去了, 只要你的Id值, 在資料庫中不存在, 是可以插入進去的.

在mysql中, 就算你插入的 id 為負數, 也是可以插入成功的. 如果資料庫中已存在你想插入的 id 值, 則會直接報錯.

 

2. 多條插入, sql格式: insert into (列名) values(列值),(列值),(列值);

INSERT INTO test.tch_teacher (  Sex, BId, NO, NAME, IsDoublePosition, CreateDate ) VALUES ( 2, ‘123123123‘, ‘123123123‘, ‘Insert‘, 0, NOW() ),( 3, ‘123123123‘, ‘123123123‘, ‘Insert‘, 0, NOW() ),( 4, ‘123123123‘, ‘123123123‘, ‘Insert‘, 0, NOW() );

新增多條的時候, 也可以迴圈調用單條插入語句去插入, 不過, 這種方式並不推薦使用, 因為, 這種方式, 消費更多效能和時間.

 

3. 表插入

可以建立一張暫存資料表: tch_teacher_temp

CREATE TABLE `tch_teacher_temp` (  `Sex` smallint(6) DEFAULT NULL,  `BId` varchar(36) CHARACTER SET utf8 DEFAULT NULL,  `No` varchar(20) CHARACTER SET utf8 DEFAULT NULL,  `Name` varchar(30) CHARACTER SET utf8 DEFAULT NULL,  `IsDoublePosition` bit(1) DEFAULT NULL,  `CreateDate` datetime DEFAULT NULL,  PRIMARY KEY (`Id`)) 

這裡有一個取巧的方式, 來擷取建表sql

show create table tch_teacher;

然後修改一下表名,刪除主鍵(也可不刪)就可以了.

insert into tch_teacher (Sex, BId, NO, NAME, IsDoublePosition, CreateDate) select * from tch_teacher_temp;-- 或者insert into tch_teacher (Sex, BId, NO, NAME, IsDoublePosition, CreateDate) select Sex, BId, NO, NAME, IsDoublePosition, CreateDate from tch_teacher_temp;

 

4. 效能比較

我粗略測試了一下, 十萬級和百萬級的資料量, 插入1000條資料. tch_teacher表, 我建了三個索引:Sex, BId, IsDoublePosition

1). 不使用事務, 一條一條插入, 迴圈以下這條語句, 1000次

for (int i = 0; i < 1000; i++){    var param = new { BId = Guid.NewGuid(), Name = names[ran.Next(9)] + names[ran.Next(9)] + i, IsDoublePosition = i % 2, CreateDate = DateTime.Now, Sex = i % 2, No = ran.Next(100000, 9999999) };    conn.Execute(insertSql, param);}

 

2). 在使用事務的情況下,  還是迴圈這條語句, 不同的是, 在迴圈結束處, 加入事務提交(這次的插入是在上次的資料量基礎上, 也就是說, 這次插入前, 資料比上次多1000條)

var tran = conn.BeginTransaction();for (int i = 0; i < 1000; i++){    var param = new { BId = Guid.NewGuid(), Name = names[ran.Next(9)] + names[ran.Next(9)] + i, IsDoublePosition = i % 2, CreateDate = DateTime.Now, Sex = i % 2, No = ran.Next(100000, 9999999) };    conn.Execute(insertSql, param, tran);}tran.Commit();

 

3). 拼接sql語句的情況下, 由於是"(),(),();"格式的, 所以參數我全做入sql中了, 這裡參數化的方式, 不好做. (這次的插入也是在上次的資料量基礎上, 也就是說, 這次插入前, 資料比上次多1000條)

StringBuilder sb = new StringBuilder("insert into tch_teacher(BId,Sex,No, Name, IsDoublePosition, CreateDate) values ", 10000);for (int i = 0; i < 1000; i++){    sb.Append(string.Format("(‘{0}‘, {1}, ‘{2}‘, ‘{3}‘, {4}, ‘{5}‘),",        Guid.NewGuid(),            i % 2,            ran.Next(100000, 9999999),        names[ran.Next(9)] + names[ran.Next(9)] + i,        i % 2,        DateTime.Now.ToString("yyyy-MM-dd")));}sb.Remove(sb.Length - 1, 1);conn.Execute(sb.ToString());

這種方式, 有兩個不好的地方, 一個是不能參數化, 另一個是如果插入資料較多, 會導致sql語句太長, 所以並不推薦

 

4). 建暫存資料表的方式, 這裡我是事先吧暫存資料表建好的, 在代碼裡面就沒有建了

var insertSql = @"insert into tch_teacher_temp(BId,Sex,No, Name, IsDoublePosition, CreateDate) values(@BId, @Sex, @No, @Name, @IsDoublePosition, @CreateDate);";var tran = conn.BeginTransaction();for (int i = 0; i < 1000; i++){    var param = new { BId = Guid.NewGuid(), Name = names[ran.Next(9)] + names[ran.Next(9)] + i, IsDoublePosition = i % 2, CreateDate = DateTime.Now, Sex = i % 2, No = ran.Next(100000, 9999999) };    conn.Execute(insertSql, param, tran);}conn.Execute(@"insert into tch_teacher (Sex, BId, NO, NAME, IsDoublePosition, CreateDate) select Sex, BId, NO, NAME, IsDoublePosition, CreateDate from tch_teacher_temp;", null, tran1);tran.Commit();

這種方式, 每次都要建立表, 刪除表. 也是挺麻煩的, 這裡的測試, 就沒有包含建立表和刪除表了

 

結果:

                     十萬(ms)              百萬(ms)

sql拼接           230                      359

事務提交         412                      511

暫存資料表            661                     1424

一條一條       27606                   36620

 

除了一條一條提交方式, 其他的在時間上, 還是能接受的, 但是推薦使用第2種, 事務提交方式, 易用, 清晰, 省事.

 

二、Delete

刪除就相對簡單多了. 刪除sql的格式: delete from 表名 where 條件

刪除的時候, 如果不加where條件, 就是刪除整張表的資料, 相當於 where 1=1 ;

delete from tch_teacher where id=1;

這是一條最簡單的語句了.

刪除的時候, 對於主鍵的自增沒有影響. 比如主鍵為 1,2,3

這時候刪除了3, 再插入一條資料, 主鍵為從4開始.

如果想要讓主鍵又從1開始的話, 需要使用truncate

truncate table tch_teacher ;

這樣, 表迴歸初始狀態.

有時候, 通過where尋找後, 能得出很多條資料, 但是我只想刪除其中的前幾條, 那怎麼辦呢. 有辦法

delete from tch_teacher where isDoublePosition=1 order by id limit 6;

這條語句, 就是刪除 滿足條件的, 前6條資料

 

三、Update

sql格式: update 表名  set 列=值 where 條件

update的where條件也是可以不加的, 不加的情況下, 修改的就是全部資料.

update tch_teacher set name=‘黑茶‘ where id=3;

修改的時候, 也是可以通過連表的方式, 去修改資料的.

update  tch_teacher , tch_contact set tch_teacher.`Name`=‘紅茶‘ where tch_teacher.Id=tch_contact.TId and tch_contact.Id=1003;

 

Mysql - 增刪改