【mysql】工作中mysql常用命令及语句

create table vistor_two like vistor;             这个会把原表的索引信息带过去,   用上面查看索引命令可以查看的到 ,但只是复制表结构；

create table vistor_three select * from vistor;  这个不会把原表的索引信息带过去，会把数据复制过去； (最好不要用这个，之前工作中因为表没自增主键导致程序插入的数据主键字段id值都为0)

delete from tablename ； 或者 truncate tablename ；   第一个删除会有删除记录，误删可以通过日志恢复记录；truncate是删除了旧表，重新创建了这个表，之前所有的状态都相当于新表；

show table status;  显示所有的表
show table status from database_name like 'task%'; 显示数据库database_name中表名以task开头的表。

select count(*) from information_schema.COLUMNS where TABLE_SCHEMA='数据库名' and table_name='表名'

use mysql； 选中mysql库
update user set password=password('你要修改的密码') where user='用户名'; 
然后在刷新权限生效
flush privileges；

mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+

mysql>  SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

mysql> SHOW COLLATION;
+----------------------------+----------+-----+---------+----------+---------+
| Collation                  | Charset  | Id  | Default | Compiled | Sortlen |
+----------------------------+----------+-----+---------+----------+---------+
| armscii8_bin               | armscii8 |  64 |         | Yes      |       1 |
| armscii8_general_ci        | armscii8 |  32 | Yes     | Yes      |       1 |
| ascii_bin                  | ascii    |  65 |         | Yes      |       1 |
| ascii_general_ci           | ascii    |  11 | Yes     | Yes      |       1 |
| big5_bin                   | big5     |  84 |         | Yes      |       1 |
| big5_chinese_ci            | big5     |   1 | Yes     | Yes      |       1 |
| binary                     | binary   |  63 | Yes     | Yes      |       1 |
| cp1250_bin                 | cp1250   |  66 |         | Yes      |       1 |
| cp1250_croatian_ci         | cp1250   |  44 |         | Yes      |       1 |
| cp1250_czech_cs            | cp1250   |  34 |         | Yes      |       2 |
| cp1250_general_ci          | cp1250   |  26 | Yes     | Yes      |       1 |
| cp1250_polish_ci           | cp1250   |  99 |         | Yes      |       1 |

mysql> SHOW STATUS LIKE 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+

[root@localhost bin]# ./mysqld --user=mysql    指定用mysql用户身份