MySQL Command Practice On Linux

Linux安装mysql

1
sudo apt install mysql-server mysql-client

Linux配置msyql_connection_jar for java

1
cp mysql_connection_jar to /usr/local/jdkxx_xx/jre/ext/

命令行登录mysql

1
2
mysql -u username -p 
mysql -u root -p -S /var/run/mysqld/mysqld.sock

显示系统中的数据库

1
show databases;

进入某一个数据库

1
use database_name;

列出数据库中所有table

1
show tables;

创建table

1
create table table_name(id VARCHAR(10), name VARCHAR(10));

显示table信息

1
2
show columns from table_name;
describe table_name;

显示主键(只对唯一主键有效，多个无效)

1
select _rowid from singers;

查询

1
2
3
select * from table_name;
select distinct column_name from  databaseName.tableName
select count(*) from table_name

插入数据

1
insert into table value("113","Jay");

查询表相关信息

1
show table status like 'singers' \G

查看数据库快照

1
mysqldump -u root -p QQSpider > zhangjie.txt

查看table的索引

1
show index from table_name;

添加索引

1
2
3
alter table_name add index()
create index_name on table_name(table_column)
alter table_name drop index index_name

查看索引

1
show index from table_name;

设置当前会话(Session)事务隔离级别

1
2
3
4
5
set session tx_isolation='READ-UNCOMMITTED';
set session tx_isolation='READ-COMMITTED';
set session tx_isolation='REPEATEABLE READ';
set session tx_isolation='SERIALIZED';
set @@tx_isolation='READ-COMMITTED';

查询

1
select &&tx_isolation

分析某条查询语句执行的情况

1
explain select * from table_name

查询数据库指定所在路径

1
show variables like 'datadir';

查询配置文件所在路径

1
mysql --help | grep my.cnf

查询存储引擎

1
show engines;

复制一个table

1
create table table_2 as select * from table_1;

强制使用索引来查询

1
2
select * from use index(a) where a =1 and b =2;
select * from force index(a) where a =1 and b =2;

查看当前处理进程

1
show processlist

MySQL数据库备份和恢复,

#1-使用root权限运行下面的命令

1
2
mysqldump -h localhost -u root -p --dataases mytest > mytest_bak.sql
mysql -u root -p < mytest_bak.sql

#2-因为secure_file_priv设置了路径，因此备份路径必须为/var/lib/mysql-files

1
2
3
4
mysql mytest -u root -p 'select * into outfile "/var/lib/mysql-files/tt.txt' from tt;
select * into outfile '/var/lib/mysql-files/tt.txt' from tt;

load data infile '/var/lib/mysql-files/tt.txt' into table tt;