Mysql and mysql download

Mysql and mysql download

For project reasons, mysql has been in use for two years, but it has never been summarized. Recently, it was the lead who reminded me of the summary of the use of mysql.

I. Create

1. Single insert, SQL format: insert into (column name) values (column value );

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

The auto-increment Id does not need to be written. The database will automatically generate the Id, but if you write it accidentally, as long as your Id value does not exist in the database, yes.

In mysql, data can be inserted successfully even if the inserted id is negative. If the database already contains the id value you want to insert, an error is reported.

 

2. Multiple inserts. SQL format: insert into (column name) values (column value), (column value), (column value );

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() );

When multiple insert statements are added, you can call a single insert statement cyclically to insert them. However, this method is not recommended because it consumes more performance and time.

 

3. Table insertion

You can create a temporary table: 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`)) 

Here is a clever way to obtain the table creation SQL

show create table tch_teacher;

Modify the table name and delete the primary key (or not.

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

 

4. Performance Comparison

I roughly tested the data size of 100,000 and 1000 million data records and inserted data records. tch_teacher table. I created three indexes: Sex, BId, and IsDoublePosition.

1). Do not use transactions, insert one by one, and cycle the following statement for 1000 times

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 ). when a transaction is used, it is still a loop statement. The difference is that at the end of the loop, the transaction commit is added (This insertion is based on the last data volume, that is, before this insertion, 1000 more data records than the previous one)

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 ). when splicing an SQL statement, because it is in the format of "(), (), ();", all parameters are included in the SQL statement. Here, the parameterization method is not easy to do. (This insertion is based on the last data volume. That is to say, before this insertion, there are 1000 more data records than the last one)

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());

This method has two disadvantages: one is not parameterized, and the other is that if too much data is inserted, the SQL statement will be too long, so it is not recommended

 

4). How to create a temporary table. Here, I created a temporary table in advance, and no temporary table is created in the code.

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();

In this way, it is difficult to create a new table and delete the table every time. The test here does not include creating or deleting the table.

 

Result:

100,000 (MS) million (MS)

SQL concatenation 230 359

Transaction commit 412 511

Temporary table 661 1424

One piece, one piece, 27606, 36620

 

In addition to one commit method, other commit methods are acceptable in terms of time, but 2nd are recommended. transaction commit methods are easy to use, clear, and easy to use.

 

Ii. Delete

The deletion process is much simpler. The SQL format for deleting: delete from table name where Condition

If the where condition is not added, the data of the entire table is deleted, which is equivalent to where 1 = 1;

delete from tch_teacher where id=1;

This is the simplest statement.

The auto-increment of the primary key is not affected when the primary key is deleted. For example, the primary key is 1, 2, 3.

At this time, 3 is deleted, and another piece of data is inserted. The primary key starts from 4.

If you want to make the primary key start from 1 again, you need to use truncate

truncate table tch_teacher ;

In this way, the table returns to the initial state.

Sometimes, after searching through where, you can get a lot of data, but I only want to delete the first few of them. What can I do?

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

This statement deletes the first six data entries that meet the conditions.

 

Iii. Update

SQL format: update table name set column = value where Condition

The where condition of update can also be left empty. If not, all data is modified.

Update tch_teacher set name = 'black tea 'where id = 3;

You can also use a table connection to modify data.

Update tch_teacher, tch_contact set tch_teacher. 'name' = 'black tea 'where tch_teacher.Id = tch_contact.TId and tch_contact.Id = 1003;