Shulou(Shulou.com)05/31 Report--

How to realize SQL programming in MYSQL? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Demand:

1. Realize that all the data from last month are imported into this month, and the time should be adjusted accordingly, that is, from 10:10:11 on 2010-08-10 to 2010-09-10 10:10:11.

two。 How to achieve the most efficient import (the table has about 20 fields and nearly 30W records)

3. How to modify the serial numbers of a table to rearrange them, or to arrange the serial numbers of certain rows according to the numbers we want, is equivalent to swapping the order of several rows.

[@ more@]

Simple ideas:

1.

Copy table operation:

Create table tbl1 as select * from tbl2

Calculated on the basis of the difference of days between two months:

Select date_add (@ dt,interval 31 day)

Calculated according to the number of months between two months:

Select date_add (@ dt,interval 1 month)

Convert to numeric type:

Select unix_timestamp (date_add (@ dt,interval 1 month))

Update tbl1 set dt=date_add (@ dt,interval 1 month)

Calculated in terms of the difference in seconds:

+-+

| | unix_timestamp ('2010-07-16 10, 14, 10, 14, 10, 10, 10) | |

+-+

| | 1279246210 |

+-+

1 row in set (0.22 sec)

Mysql > select unix_timestamp ('2010-08-16 10 10')

+-+

| | unix_timestamp ('2010-08-16 10 10') |

+-+

| | 1281924610 |

+-+

1 row in set (0.00 sec)

Mysql > select (1281924610-1279246210)

+-+

| | (1281924610-1279246210) |

+-+

| | 2678400 |

+-+

1 row in set (0.00 sec)

Mysql > select from_unixtime (127924621002678400)

+-+

| | from_unixtime (1279246210 / 2678400) | |

+-+

| | 2010-08-16 10:10:10 |

+-+

1 row in set (0.00 sec)

Mysql >

Note: the following export method is imported into a directory on the server machine rather than a directory on the local client

Mysql > select tablename into outfile'c. Txt 'from dbaudit_index

ERROR 1086 (HY000): File'cvirtual c.txt' already exists

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > notee

Outfile disabled.

Mysql > select tablename into outfile'c _ from dbaudit_index _

Query OK, 14 rows affected (0.00 sec)

Mysql > select tablename into outfile'cvirtual e.txt' from dbaudit_index

Query OK, 14 rows affected (0.00 sec)

Mysql > exit

Bye

Export a table. Fields are separated by | and enclosed by "

Mysql > select tablename from dbaudit_index into outfile'cvirtual f.txt' fields termi

Nated by'| 'enclosed by' "'

Query OK, 14 rows affected (0.02 sec)

This is the answer to the question about how to implement SQL programming in MYSQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.