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_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 | |
+---------------+-------+
17.查看临时表
mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | |
| Created_tmp_files | |
| Created_tmp_tables | |
+-------------------------+-------+
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 | | | Yes | |
| armscii8_general_ci | armscii8 | | Yes | Yes | |
| ascii_bin | ascii | | | Yes | |
| ascii_general_ci | ascii | | Yes | Yes | |
| big5_bin | big5 | | | Yes | |
| big5_chinese_ci | big5 | | Yes | Yes | |
| binary | binary | | Yes | Yes | |
| cp1250_bin | cp1250 | | | Yes | |
| cp1250_croatian_ci | cp1250 | | | Yes | |
| cp1250_czech_cs | cp1250 | | | Yes | |
| cp1250_general_ci | cp1250 | | Yes | Yes | |
| cp1250_polish_ci | cp1250 | | | Yes | |
20.查看线程使用的情况
mysql> SHOW STATUS LIKE 'threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | |
| Threads_connected | |
| Threads_created | |
| Threads_running | |
+-------------------+-------+
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 查看表索引信息
26.查询当前MySQL本次启动后的运行统计时间
mysql> show status like 'uptime'; +---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 140459 |
+---------------+--------+
27.查看是否有表锁住
show OPEN TABLES where In_use > 0;
28.查看查询进程
show processlist;
如果不进入mysql命令行也可以进入mysql/bin目录下输入mysqladmin processlist。
如果没有SUPER权限，则只能看到自己发起的线程， 否则可以看到全部的线程。
29.返回不重复数据
SELECT DISTINCT user_name,vistor_username FROM KY_FEED_VISTOR WHERE user_name='shenhy'
单独的distinct只能放在开头 而且distinct()里面只能包含一个字段
SELECT * FROM KY_FEED_VISTOR WHERE user_name='shenhy' GROUP BY user_name, vistor_username ORDER BY vistor_time DESC
30.创建一个新表且新表的结构与查询的表的结构是一样，不过没有索引
create newTable newtable as select * from oldTable where 1=0;
31.模糊查询表
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '数据库名' AND TABLE_NAME LIKE '表名%'
如果想生成清空多个表语句，也可以按照下面方法生成对多张表的truncate语句。
select CONCAT('truncate TABLE ',table_schema,'.',TABLE_NAME, ';') from INFORMATION_SCHEMA.TABLES where table_schema = '数据库名' AND TABLE_NAME LIKE '表名%';
32.复制表信息到另一张表
从一个表中复制所有的列插入到另一个已存在的表中：
INSERT INTO 表名 SELECT * FROM 目标表名;
或者只复制希望的列插入到另一个已存在的表中：
INSERT INTO 表名 (column_name(s)) SELECT column_name(s) FROM 目标表名;
33.获取字段后几个字符
34.mysql select update语句
当我们需要从select结果中去update时候，一般我们会想到update table set a= 1 where id in (select id from table), 但是这样是不行的，如下。
update select 语句需要使用inner join
update kaoqin_grant_record inner join (SELECT id FROM kaoqin_grant_record WHERE invalid_time <= '2019-11-01' AND balance_type = 5 AND operator_type = 1 AND is_has_cleand = 0) AS child on kaoqin_grant_record.id =child.id set kaoqin_grant_record.is_has_cleand = 1
35.获取所有表的行数
SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'hrms' ORDER BY table_rows desc
持续更新........