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.1Connection id: 1044533Current database: release_test_oaCurrent user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server: MySQLServer 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_three select * 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;
23.修改表引擎
ALTER TABLE table_name ENGINE=InnoDB; #将表存储引擎修改为innodb
24.重置autoIncrement的值
ALTER TABLE table_name AUTO_INCREMENT = 1;
25.索引创建 删除 查看
CREATE UNIQUE INDEX index_name ON table_name(字段名) 创建唯一索引DROP INDEX index_name ON talbe_name 删除索引 show index from table_name 查看表索引信息
持续更新........