mysql命令

最新推荐文章于 2024-10-11 17:28:57 发布

ora600

最新推荐文章于 2024-10-11 17:28:57 发布

阅读量668

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/heizistudio/article/details/8260153

mysql 专栏收录该内容

112 篇文章

订阅专栏

[root@dd]mysqlshow booksite

Database: xinannl Table: booksite

[root@dd]mysqlshow --key xinannl title
Database: xinannl Table: title
+------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field      | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id         | int(11)      |                 | NO   | PRI |         | auto_increment | select,insert,update,references |         |
| No         | int(11)      |                 | YES |     |         |                | select,insert,update,references |         |
| name       | varchar(255) | utf8_general_ci | YES |     |         |                | select,insert,update,references |         |
| bid        | int(11)      |                 | YES |     |         |                | select,insert,update,references |         |
| bname      | varchar(255) | utf8_general_ci | YES |     |         |                | select,insert,update,references |         |
| cid        | int(11)      |                 | YES | MUL |         |                | select,insert,update,references |         |
| cname      | varchar(255) | utf8_general_ci | YES |     |         |                | select,insert,update,references |         |
| insertTime | datetime     |                 | YES |     |         |                | select,insert,update,references |         |
| status     | int(11)      |                 | YES |     |         |                | select,insert,update,references |         |
| url        | varchar(255) | utf8_general_ci | YES |     |         |                | select,insert,update,references |         |
+------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| title | 0          | PRIMARY           | 1            | id          | A         | 0           |          |        |      | BTREE      |         |               |
| title | 1          | FK694225871F9FFDD | 1            | cid         | A         | 0           |          |        | YES | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
[root@dd]

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2012-12-05 15:18:35 |
+---------------------+
1 row in set (0.03 sec)

mysql> select now(),user(),version(0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select now(),user(),version();
+---------------------+----------------+------------+
| now() | user() | version() |
+---------------------+----------------+------------+
| 2012-12-05 15:19:23 | root@localhost | 5.5.15-log |
+---------------------+----------------+------------+
1 row in set (0.01 sec)

mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)

mysql>

mysql> ?

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Network Support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql> help

For server side help, type 'help contents'

mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1

Connection id:          15
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.15-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn. characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 13 days 21 min 28 sec

Threads: 1 Questions: 1682696 Slow queries: 8 Opens: 41 Flush tables: 1 Open tables: 32 Queries per second avg: 1.496

mysql> select database(),
-> version()\c -----退出当前查询

[root@dd]more 2.sql
create table test001(id int)

mysql> select * from test001;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+

mysql> drop table if exists test01;

查表的信息

mysql> show table status\G;
*************************** 1. row ***************************
           Name: test001
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4
Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 979369984
Auto_increment: NULL
    Create_time: 2012-12-05 15:39:25
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

查索引信息

mysql> show index from test001;
Empty set (0.00 sec)

optimize table出错解决方法

一个某某库大概360W,为了提高查询效率，做点优化，使用optimize table table.name;出现
Table does not support optimize, doing recreate + analyze instead
果断谷歌，查到一段E文。。。

Everytime you do optimize MySQL, by using mysqlcheck -A -o or using ./mysql_optimize from here.
You may see the output

Table does not support optimize, doing recreate + analyze instead.

It is because the table that you are using is InnoDB.

You can optimize the InnoDB tables by using this.

ALTER TABLE table.name ENGINE='InnoDB';

This will create a copy of the original table, and drop the original table, and replace to the original place.

Although this is safe, but I suggest you do backup and test first before doing this.

原来如此，大致意思是说innodb的数据库不支持optimize，可以用

ALTER TABLE table.name ENGINE='InnoDB';

该方法会对旧表以复制的方式新建一个新表，然后删除旧表。虽然这个过程是安全的，但是在进行操作时还是先进行备份为好

=================================================
还有一种方式：

You can make OPTIMIZE TABLE work on other storage engines by starting mysqld with the --skip-new or --safe-mode option. In this case, OPTIMIZE TABLE is just mapped toALTER TABLE.

上面是说要求我们在启动的时候指定--skip-new或者--safe-mode选项来支持optimize功能

mysql> select * from weather procedure analyse();

mysql> flush logs;
Query OK, 0 rows affected (0.14 sec)

或者 mysqladmin flush-logs

刷新二进制日志