mysql常用命令

1、查看数据库的所有用户 转载

SELECT DISTINCT CONCAT(‘User: ‘’’,user,‘’‘@’‘’,host,‘’‘;’) AS query FROM mysql.user;

2、修改账户密码 转载

set password for aicaimultimsg@‘%’ = password(‘aicaimultimsg’);

3、无密码登录 转载

4、添加新用户

create user ‘root’@‘%’ identified by ‘root’;
flush privileges;

5、新建数据库

create database testlink DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
show databases;

6、将数据库表权限赋权给指定用户

show grants for testplatform 查看用户权限 grant all privileges on . to ‘guangcps’@‘syao-qa-001’ identified by ‘guangcps’ with grant option;
flush privileges;

7、数据库备份

mysqldump -h 10.50.81.203 -u apollo -p --all-databases>apolloconfigdb.sql
导入
mysqldump -u root -p --databases db1 db2 > c.sql
进入mysql命令行，source /usr/local/mysql/all.sql

8、mysql数据库表名不区分大小写 转载

9、查看当前连接数

show status like ‘Threads_connected’;当前连接数
SHOW FULL processlist;当前连接数
show variables like ‘%max_connections%’;最大连接数

10、设置最大连接数

set global max_connections = 1000

11、执行.sql文件

mysql -u root -p > filename.sql

12、查看存储过程权限

grant select on mysql.proc to xxxx@‘xxxx’

13、查看数据库所有列名并用逗号分隔

SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR “,”) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ‘test_platform’ AND TABLE_NAME = ‘typeB_score’;

14、查看所有表名，并用逗号分隔

SELECT GROUP_CONCAT(table_name SEPARATOR “,”) FROM information_schema.tables WHERE TABLE_SCHEMA = ‘mars_order’