因為項目原因, 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;
文章列表
留言列表
