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

1.查看mysql版本号

MySQL [release_test_oa]> select version();

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

| version() |

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

| 5.6.27-log |

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

1 row in set (0.00 sec)

或者

MySQL [release_test_oa]> status;

--------------

mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:1044533

Current database:release_test_oa

Current user:root@localhost

SSL:Not in use

Current pager:stdout

Using outfile:''

Using delimiter:;

Server:MySQL

Server version:5.6.27-log Source distribution

2.连接本地mysql

mysql -uroot -p 或者 mysql -h127.0.0.1 -uroot -p

连接远程mysql

mysql -u root -p -h 192.168.1.2

3.导出本地数据库某张表(比如导出数据中的USERS表)   回车后要输入数据库密码

mysqldump databases -u root -p --tables USERS>/usr/tmp/users.sql

4.导出远程数据库某张表(USERS表)   回车后要输入数据库密码

mysqldump -h192.168.79.206 -p3306 databases -u root -p --tables USERS>/usr/tmp/users.sql

5.表名重命名

RENAME TABLE USERS TO USERS_BAK

6.查看表索引信息

show index from tables; 或者 show keys from tables;两个命令输出都一样

7.复制表命令

原表是vistor

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

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

8.新增字段

alter table tablename add new_ziduan int(4) default '0';

9.删除字段

alter table tablename drop column;

10.删除表数据

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

11.复制表部分字段数据到另一张表 (同字段类型)

insert into tables_new (ID,BYNAME,STATUS) select ID,BYNAME,STATUS from tables_old;

12.查看表字段

show columns from tablename;

13.查看表状态

show table status; 显示所有的表

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

14.查看表有多少个字段

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

15.修改mysql登录用户密码

use mysql； 选中mysql库

update user set password=password('你要修改的密码') where user='用户名';

然后在刷新权限生效

flush privileges；

可用select length(password('123'))察看加密后密码长度

16.查看mysql端口号

mysql> show global variables like 'port';+---------------+-------+

| Variable_name | Value |

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

| port | 3306 |

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

17.查看临时表

mysql> SHOW STATUS LIKE 'created_tmp%';+-------------------------+-------+

| Variable_name | Value |

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

| Created_tmp_disk_tables | 0 |

| Created_tmp_files | 5 |

| Created_tmp_tables | 0 |

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

18.查看引擎

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 |

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

19.查看字符集

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 |

20.查看线程使用的情况

mysql> SHOW STATUS LIKE 'threads%';+-------------------+-------+

| Variable_name | Value |

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

| Threads_cached | 0 |

| Threads_connected | 1 |

| Threads_created | 1 |

| Threads_running | 1 |

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

21.启动时候指定用户身份

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

22.查看表字段及注释

show full fields from table_name;

持续更新........

内容来源于网络如有侵权请私信删除