MYSQL basics 01 (add, modify, delete) _ MySQL

INSERT1. insert a record. The simple standard sqlinsertintomytable (col1, col2, col3) values (#39; val1 #39;, #39; val2 #39;, 4); description: please pay attention to the last semicolon. mssql is used. the most unsuitable part is that every SQL statement in mysql must be... INSERT

1. Insert a record, simple standard SQL

Insert into mytable (col1, col2, col3) values ('val1', 'val2', 4 );

Note: Please pay attention to the last semicolon. when mssql is used, the least uncomfortable thing is that each SQL statement in mysql must be opened with semicolons. Otherwise, if you want to execute multiple statements at a time, an error is reported.

In addition, if auto-incremental ID is used, you can set the auto-incremental ID value during insertion without reporting an error. this is a great convenience for data migration.

2. copy the table data, which is the same as mssql.

Insert into mytable (col1, col2, col3)

Select col1, col2, col3 from copytable;

3. add a table and copy data at the same time. This is different from mssql (ms: select * into newtable from copytable)

Create table newtable SELECT * FROM copytable WHERE 1 = 2;

Note: like mssql, only the structure of the table is copied, and the index is not copied. if the where condition is removed, all data is copied.

4. batch insert. mysql features a bit like chained writing (quite easy to use !)

Insert into mytable (col1, col2, col3) values ('A', 'val1', 1), ('B', 'val2', 2), ('C ', 'val3', 3 );

Note: The execution efficiency is quite high, and tens of thousands of records can be inserted within 10 seconds at the same time. In addition, batch insertion is quite easy for handling characters in the project. I especially like this statement.

UPDATE

1. standard SQL

UPDATE mytable SET col1 = 'a', col2 = 'val1', col3 = 1 where id = '20140901 ';

2. update table connections and update multiple columns simultaneously, partially consistent with mssql

UPDATE newtable a, oldtable B set a. col1 = B. col1, a. col2 = B. col2 where a. id = B. id;

(Mssql statements are more flexible and support this syntax: update newtable set col1 = oldtabel. col1, col2 = oldtabel. col2 from oldtabel where newtable. id = oldtabel. id)

3. batch update

Replace into mytable (id, name) VALUES ('01', 'Hello'), ('02', 'haha ');

Note: the format of batch insertion is the same. when the data after values is inserted into mytable, it will not cause a primary key conflict or a unique index conflict, then data will be directly added, otherwise, the data will be modified. simply put, if a record exists, it will be updated. if the record does not exist, it will be inserted. in the above example, if the insert operation is executed, the number of affected rows is 2. if the modification is performed, the number of affected rows is 4; therefore, the essence of the modification operation is to delete the operation before inserting it. therefore, if the updated field is not correct, other fields will be changed to the default value. The following is the enhanced version of this function.

Insert into mytable (id, name, col) VALUES (78, '000000', '55'), (88, '000000', 'D ') on duplicate key update name = VALUES (name), col = VALUES (col );

Insert into mytable (id, name, col) VALUES (78, '000000', '55'), (88, '000000', 'D ') on duplicate key update name = VALUES (name); -- UPDATE name only

Note: replace is the same. The only difference is that you can select an update column. the values of other columns are not affected.

DELETE

1. standard SQL

Delete from mytable where id = '001 ';

2. clear the table, which is the same as mssql.

Truncate table mytable;

Note: after execution, the auto-increment ID starts from 1.

Precautions for data processing

1. when inserting data into a column of the date type, if the inserted value does not conform to the date format, mysql will not report an error, but will save it as '2017-00-00 00:00:00, the general program does not recognize the data, so you will find that there is no problem in reading data in the background, and the application will report an error when reading data. I am so miserable.

The solution found on the internet is as follows, and there is no actual test

Add the zeroDateTimeBehavior parameter to the jdbc url:

Datasource. url = jdbc: mysql: // localhost: 3306/testdbuseUnicode = true & characterEncoding = utf8 & zeroDateTimeBehavior = convertToNull & transformedBitIsBoolean = true

ZeroDateTimeBehavior = round is used to specify the default value of the DateTime field in MySql for query. by default, an exception is thrown,

For records with a value of 0000-00-00 00:00:00 (default), the following two types of configuration return different results:

ZeroDateTimeBehavior = round // Result: 0001-01-01 00:00:00. 0

ZeroDateTimeBehavior = convertToNull // The result is null.

At present, the new mysql update and deletion are the above content. in case of any new situation, add it.