Several SQL statements for MySQL Learning

I want to learn several SQL statements that need to be paid attention to when using mysql. It is helpful for beginners:

1. Insert batch data from one table to another table with the same structure

Insertintotable1 (select * fromtable2)

2. Insert a large number of SQL statements as follows.

Insertintotable1values
(1, 'A '),

(2, 'B '),

(3, 'A '),

(4, 'B '),

...............

3. mysql gets the next auto-increasing id, and the data obtained is connected independently, that is, mysql automatically maintains the maximum id that should be obtained by no link.

That is to say, if two links are inserted into each record of the table at the same time, mysql will automatically return the same final id.

SELECTLAST_INSERT_ID () fromcreateuseridlimit0, 1

4. Return the first non-empty string. If the data in the columnname field is null, the aaa value is returned.

COALESCE (columnname, 'aaa ')

This method is equivalent to isnull (columnname, 'default') in sqlserver and returns the first non-null string.

5. Repair the table. If the mysql table is grayed out and cannot be read, and the linux User Group of the table is correct, run the following command to repair the table:

Repairtabletablename;

6. If the time in the field is the number of milliseconds of the long type, use selectFROM_UNIXTIME (875996580) to convert it to the date type. Note:

After the length exceeds a certain number of digits, you must extract the first few digits. Otherwise, the conversion fails.

7. Description of the adddate parameter of the date function

The function calculates the date. The first parameter is the field, and the second parameter is the meaning of the interval, which is a keyword.

The third parameter is the sum of values, and the subsequent parameter is the sum unit.

Selectadddate (regdate, interval0day) fromtablenamedesclimit0, 100

8. For the sub-select statement, you must select the record range of the sub-statement.

Example: select * fromtable1whereidin (selectidfromtable2)

If you want to add conditions, you must add the where clause.