mysql查询几年内数据库_【数据库】mysql常用查询命令-年年的回答

蔚落 2022-10-25 04:59 157阅读 0赞

1,查询现在的时间：

mysql> select now();

\+---------------------+

| now() |

\+---------------------+

| 2017-09-22 11:22:17 |

\+---------------------+

1 row in set (0.00 sec)

2，查询主从灾备的主的server\_id:

mysql> show global variables like 'server\_id';

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| server\_id | 1 |

\+---------------+-------+

1 row in set (0.00 sec)

3,查询数据库是否开启了二进制日志：

mysql> show global variables like 'log\_bin';

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| log\_bin | ON |

\+---------------+-------+

1 row in set (0.00 sec)

4，将查询的数据作为插入内容：

mysql> insert into c2(id) select \* from c1;

Query OK, 100000 rows affected (0.05 sec)

Records: 100000 Duplicates: 0 Warnings: 0

mysql> desc c1; --------------确保c1的查询出来的内容要和c2表要插入的内容在字段上一致。

\+-------+---------+------+-----+---------+-------+

\+-------+---------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

\+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)

5，向某张表中插入另外一张表的某个字段的所有值

mysql> create table c51(id int,name char(30));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into c51(id) select \* from chen.c3; (直插入id字段的值，其他字段的不插入，不插入的采取默认值：NULL或者空)

Query OK, 10000 rows affected (0.00 sec)

Records: 10000 Duplicates: 0 Warnings: 0

mysql> select count(\*) from c51;

\+----------+

| count(\*) |

\+----------+

| 10000 |

\+----------+

1 row in set (0.00 sec)

mysql>

默认值为非空的name字段(加上 name char not null的情况)

| 9997 | |

| 9998 | |

| 9999 | |

| 10000 | |

\+-------+------+

10000 rows in set (0.01 sec)

默认值为NULL的name字段( name char 的情况)

| 9996 | NULL |

| 9997 | NULL |

| 9998 | NULL |

| 9999 | NULL |

| 10000 | NULL |

\+-------+------+

10000 rows in set (0.00 sec)

5，将某个文件中内容导入到表中：

\[root@paris mysql\]\# seq 1 100000 >/a/a

mysql> load data infile '/a/a' into table c1;

Query OK, 100000 rows affected (0.05 sec)

Records: 100000 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select count(\*) from c1;

\+----------+

| count(\*) |

\+----------+

| 100000 |

\+----------+

1 row in set (0.00 sec)

6，查看MySQL的主从复制的从设备的运行状况：

mysql> show slave status\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 10.0.0.2

Master\_User: haha

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: binlog.000003

Read\_Master\_Log\_Pos: 106

Relay\_Log\_File: slave\_relay\_log.000009

Relay\_Log\_Pos: 248

Relay\_Master\_Log\_File: binlog.000003

Slave\_IO\_Running: Yes ---------------------------------- 确保这两个线程是正确的开启状态

Slave\_SQL\_Running: Yes ---------------------------------- ..............................

7，互为主从的mysql的配置文件：

配置前提是两个数据库的内容是一致的。若不一致，先要手动做到一致。

10.0.0.2：\[root@paris mysql\]\# cat /etc/my.cnf

\[mysqld\]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

\# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

server\_id=1

log\_bin=binlog

log\_bin\_index=binlog.index

master\_host=10.0.0.3

master\_user=haha

master\_password=123

relay\_log=slave3\_relay\_log

relay\_log\_index=slave3\_relay\_log.index

\[mysqld\_safe\]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

10.0.0.3：\[root@localhost mysql\]\# cat /etc/my.cnf

\[mysqld\]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

\# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

server\_id=2

master\_host=10.0.0.2

master\_user=haha

master\_password=123

relay\_log=slave\_relay\_log

relay\_log\_index=slave\_relay\_log.index

log\_bin=binlog4

log\_bin\_index=binlog4.index

\[mysqld\_safe\]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

8，热备份命令总结：

全备：

\[root@localhost mysql\]\# mysqldump -u root -p --all-databases -x >/a/all.sql

恢复：\[root@localhost mysql\]\# mysql -u root -p

实例：

\[root@localhost mysql\]\# rm -rf \*

\[root@localhost mysql\]\# ls

\[root@localhost mysql\]\# service mysqld start

\[root@localhost mysql\]\# mysql -u root -p mysql> show tables;

\+----------------+

| Tables\_in\_chen |

\+----------------+

| c1 |

| c2 |

| c3 |

| c4 |

| c5 |

| c6 |

| c7 |

| c8 |

\+----------------+

8 rows in set (0.00 sec) ------------- 数据未丢失

单个数据库备份：

备份：\[root@localhost mysql\]\# mysqldump -u root -p --database chen -x >/a/chen.sql

恢复：\[root@localhost mysql\]\# mysql -u root -p

实例：

\[root@localhost mysql\]\# mysqldump -u root -p --database chen -x >/a/chen.sql

mysql> drop database chen;

\[root@localhost mysql\]\# mysql -u root -p mysql> show databases;

\+--------------------+

| Database |

\+--------------------+

| information\_schema |

| chen |

| mysql |

| test |

\+--------------------+

4 rows in set (0.00 sec)

但张表的备份：

备份：\[root@localhost mysql\]\# mysqldump -u root -p chen c1 c2 >/a/biao.sql

Enter password:

恢复：\[root@localhost mysql\]\# mysql -u root -p chen Enter password:

实例：

\[root@localhost mysql\]\# mysqldump -u root -p chen c1 c2 >/a/biao.sql

mysql> drop table c1,c2;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

\+----------------+

| Tables\_in\_chen |

\+----------------+

| c3 |

| c4 |

| c5 |

| c6 |

| c7 |

| c8 |

\+----------------+

6 rows in set (0.00 sec)

\[root@localhost mysql\]\# mysql -u root -p chen Enter password:

mysql> show tables;

\+----------------+

| Tables\_in\_chen |

\+----------------+

| c1 |

| c2 |

| c3 |

| c4 |

| c5 |

| c6 |

| c7 |

| c8 |

\+----------------+

8 rows in set (0.00 sec)

mysql> select count(\*) from c2;

\+----------+

| count(\*) |

\+----------+

| 100000 |

\+----------+

1 row in set (0.00 sec)

9,在表中的数据全部插入原表中

mysql> insert into c1 select \* from c1;

(省略values 选项，直接接源)

mysql查询几年内数据库_【数据库】mysql常用查询命令-年年的回答

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql按年或季度查询数据

相关 MySql——数据库常用命令

相关 MYSQL数据库常用命令

相关 Mysql数据库常用命令

相关 mysql数据库命令大全_Mysql数据库常用命令

相关 mysql查询几年内数据库_【数据库】mysql常用查询命令-年年的回答

相关 Mysql数据库常用命令

相关数据库的常用查询命令

相关数据库----mysql查询

相关 MySql按年月日分组查询