mysql 查询命令_MySQL常用的查询命令

MySQL常用的查询命令

author： headsen chen   2017-10-19  10:15:25

个人原创。转载请注明作者，出处，否则依法追究法律责任

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表要插入的内容在字段上一致。

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| 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 选项，直接接源)