[SQL] Backup of the same library table (structure) and SQL aggregation &navicat use

Backup-assignment table structure and data SQL statements for the same library table

Reference

Sometimes when we work on a table, we need to back up the table to the current library before executing SQL. From the SQL perspective, you don't need to back up in mysqldump or in a way like export SQL. Easier backup of the same library table

1. Copy table structure and data to a new table

CREATE TABLE 新表 SELECT * FROM 旧表

2. Copy the table structure to the new table only

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2即:让WHERE条件不成立.方法二:(由tianshibao提供)CREATE TABLE 新表 LIKE 旧表

3. Copy the data from the old table to the new table (assuming two table structures)

INSERT INTO 新表 SELECT * FROM 旧表

4. Copy the data from the old table to the new table (assuming that the two table structures are different)

INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表

Autocommit of SQL

Reference
Executes the command SET autocommit=0, which is used to disallow automatic submission of the current session.

mysql> set autocommit=0;Query OK, 0 rows affected (0.01 sec)mysql>  show variables like "autocommit";+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | OFF   |+---------------+-------+

Using NAVICAT requires special attention to two points

• there is a particular need to note that I found that if Navicat login after the first sentence does not perform set autocommit = 0; is automatically autocommit, even if you set auto in the second sentence, it will not take effect for this session.
• The 2nd is that autocommit is for sessions.

Navicat shortcut keys

4.ctrl+r           运行查询窗口的sql语句5.ctrl+shift+r   只运行选中的sql语句

Correct use of navicat posture

SET autocommit = 0;CREATE DATABASE maotai;DROP DATABASE maotai;ROLLBACK; #如果我后悔了即可COMMIT;   #已确认sql没问题

General execution SQL I use Navicat to do, on the one hand to facilitate the verification results copied to the other side feedback, execution is also handy, because the SQL will be automatically prompted. , on the one hand he had shortcut keys at the time of execution

MySQL's business

What's going on with the business?

张三给李四转账500元。那么在数据库中应该是以下操作：1，先查询张三的账户余额是否足够2，张三的账户上减去500元3，李四的账户上加上500元以上三个步骤就可以放在一个事务中执行提交，要么全部执行要么全部不执行，如果一切都OK就commit提交永久性更改数据；如果出错则rollback回滚到更改前的状态。利用事务处理就不会出现张三的钱少了李四的账户却没有增加500元或者张三的钱没有减去李四的账户却加了500元。

The difference between the MyISAM and the InnoDB engine

InnoDB支持事务，MyISAM不支持InnoDB支持外键，MyISAM不支持

SQL aggregate functions

- 用sql语句做数据汇聚,查出status 和 对应的总数, 按照状态码总数来排序select status,sum(count) from log group by status;

[SQL] Backup of the same library table (structure) and SQL aggregation &navicat use