MySQL常用命令及SQL

1.查询支持引擎

mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         || PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         || CSV                | YES     | CSV storage engine                                             | 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         || FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       || InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        || MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+8 rows in set (0.00 sec)

2.修改表引擎

mysql>show create table xxx;

mysql>alter table xxx engine=innodb;

 

3.逻辑备份恢复数据库

mysqldump -uroot -p*** --default-character-set=utf8  [dbname] -R -F > dbname.sql

mysql -uroot -p*** --default-character-set=utf8 [dbname] < dbname.sql

 

4.SQL取整函数

mysql> select floor(1.1);+------------+| floor(1.1) |+------------+|          1 |+------------+1 row in set (0.00 sec)mysql> select ceil(1.1);+-----------+| ceil(1.1) |+-----------+|         2 |+-----------+1 row in set (0.00 sec)mysql> select round(1.4);+------------+| round(1.4) |+------------+|          1 |+------------+1 row in set (0.00 sec)mysql> select round(1.5);+------------+| round(1.5) |+------------+|          2 |+------------+1 row in set (0.00 sec)mysql>

 5.查看表、字段信息

mysql> use information_schema;
Database changed
mysql> desc tables;
mysql> desc columns;

 

6.按顺序更新栏位

select @t:=0

update jay set x=@t:=@t+1;

 

7.查询数据库支持的语言集及选用的语言集(charset)

show variables like '%char%';

show charset;

 

8.日期转换date_format()

mysql> select DATE_FORMAT(NOW(), '%Y%m%d%H%i%s' ) date;+----------------+| date           |+----------------+| 20120713092749 |+----------------+1 row in set (0.00 sec)

9.Oracle rownum替代方法

mysql> set @rownum := 0;Query OK, 0 rows affected (0.00 sec)mysql> select @rownum := ifnull(@rownum,0)+1 as level,x from rownum;+-------+------+| level | x    |+-------+------+|     1 |    1 ||     2 |    2 ||     3 |    3 |+-------+------+3 rows in set (0.00 sec)

10.Oracle替代row_number() over (partition by)方法

mysql> create table  test (id int ,dept int ,salary decimal(10,2));Query OK, 0 rows affected (0.01 sec)mysql> insert into test values    -> (1,10,5500.00),    -> (2,10,4500.00),    -> (3,20,1900.00),    -> (4,20,4800.00),    -> (5,40,6500.00),    -> (6,40,14500.00),    -> (7,40,44500.00),    -> (8,50,6500.00),    -> (9,50,7500.00);Query OK, 9 rows affected (0.00 sec)Records: 9  Duplicates: 0  Warnings: 0mysql> select * from test;+------+------+----------+| id   | dept | salary   |+------+------+----------+|    1 |   10 |  5500.00 ||    2 |   10 |  4500.00 ||    3 |   20 |  1900.00 ||    4 |   20 |  4800.00 ||    5 |   40 |  6500.00 ||    6 |   40 | 14500.00 ||    7 |   40 | 44500.00 ||    8 |   50 |  6500.00 ||    9 |   50 |  7500.00 |+------+------+----------+9 rows in set (0.00 sec) SELECT id, dept, salary, rankFROM (  SELECT   tmp.id,   tmp.dept,   tmp.salary ,@rownum :=@rownum + 1,  IF(   @dept = tmp.dept ,@rank :=@rank + 1 ,@rank := 1  )AS rank,  @dept := tmp.dept FROM  (   SELECT    id,    dept,    salary   FROM    test   ORDER BY    dept ASC,    salary DESC  )tmp,  (   SELECT    @rownum := 0,    @dept := NULL ,@rank := 0  )a )result;+------+------+----------+------+| id   | dept | salary   | rank |+------+------+----------+------+|    1 |   10 |  5500.00 |    1 ||    2 |   10 |  4500.00 |    2 ||    4 |   20 |  4800.00 |    1 ||    3 |   20 |  1900.00 |    2 ||    7 |   40 | 44500.00 |    1 ||    6 |   40 | 14500.00 |    2 ||    5 |   40 |  6500.00 |    3 ||    9 |   50 |  7500.00 |    1 ||    8 |   50 |  6500.00 |    2 |+------+------+----------+------+9 rows in set (0.00 sec)

11.查询更改字段comment

SELECT concat(  'alter table ',  table_name,  ' modify ',  COLUMN_NAME,  ' ',  column_type, IF(  IS_NULLABLE = 'NO',  ' NOT NULL ',  ' DEFAULT NULL ' ),IF( ifnull(COLUMN_default, '')= '', '', CONCAT(  ' default ',  '''',  COLUMN_default,  '''' )), extra,IF( ifnull(COLUMN_COMMENT, '')= '', '', CONCAT(  ' comment ',  '''',  COLUMN_COMMENT,  '''' )), ';' )FROM COLUMNS

11.计算QPS、TPS

QPS= Query Per Second

TPS= Transaction Per Secnd

 

mysql> SHOW GLOBAL STATUS LIKE 'Questions';+---------------+----------+| Variable_name | Value    |+---------------+----------+| Questions     | 29340533 |+---------------+----------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Uptime';+---------------+---------+| Variable_name | Value   |+---------------+---------+| Uptime        | 1392479 |+---------------+---------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Com_commit';+---------------+--------+| Variable_name | Value  |+---------------+--------+| Com_commit    | 687702 |+---------------+--------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Com_rollback';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_rollback  | 694   |+---------------+-------+1 row in set (0.01 sec)

 

QPS=Questions/Uptime

TPS=(Com_commit + Com_rollback)/Uptime

12.Kill Process

mysqladmin processlist|grep -i Sleep|awk '{print $2}';
mysqladmin processlist|grep -i Sleep|awk '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill

shell:

for id in `mysqladmin processlist|grep -i locked|awk '{print $2}'`
do
   mysqladmin kill ${id}
done

 

13.将字符串转换为Int order by排序

select varchar+0|varchar*1 as int;

14.Instance Summary

select table_schema,
sum(data_length+index_length)/1024/1024 as total_mb,
sum(data_length)/1024/1024 as data_mb,
sum(index_length)/1024/1024 as index_mb,
count(*) as tables
from information_schema.tables
group by table_schema
order by 2 desc; 


15.Schema Summary
select table_name,engine,row_format, table_rows, avg_row_length,
(data_length+index_length)/1024/1024 as total_mb,
(data_length)/1024/1024 as data_mb,
(index_length)/1024/1024 as index_mb
from information_schema.tables
where table_schema= DATABASE()
order by 6 desc;

16.设定表空间

mysql> show variables like '%innodb_data_file%';+-----------------------+------------------------+| Variable_name         | Value                  |+-----------------------+------------------------+| innodb_data_file_path | ibdata1:10M:autoextend |+-----------------------+------------------------+1 row in set (0.00 sec)

vi /etc/my.cnf

innodb_data_file_path = ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G;ibdata5:10G:autoextend

17.innodb表独立表空间

vi /etc/my.cnf

innodb_file_per_table

18.mysqldump相关命令

mysqldump -p -R > /tmp/kin.sql -- 导出包括存储过程

mysqldump -p -R -d kin > /tmp/kin.sql --导出结构不导数据

mysqldump -p -R -t kin > /tmp/kin.sql --导出数据不导结构

mysqldump -p -R kin > /tmp/kin.sql --导出数据及结构

19.开启general log查看执行语句

set global general_log=1; --开启

set global general_log=0; --关闭

或者

vi /etc/my.cnf

log=/tmp/general.log

show variables like '%general%';

20.mysql执行SQL语句脚本

mysql -h<ip> -uroot -p -e 'sql statement'

mysql>source xxx.sql

21.SQL分组取最大值

SELECT * FROM (SELECT * FROM t2 ORDER BY gid,col2 DESC) tGROUP BY gid; 

22.构造rownum

mysql> SELECT STATUS FROM ref_number;+--------+| STATUS |+--------+|      1 | |      2 | |      3 | |      5 | |      7 | +--------+5 rows in set (0.00 sec)mysql> SELECT                           -> @rownum1 := @rownum1 + 1 AS rn,    -> ref.*    -> FROM    -> (SELECT @rownum1 := 0)rown,    -> (SELECT STATUS FROM ref_number)ref;+------+--------+| rn   | STATUS |+------+--------+|    1 |      1 | |    2 |      2 | |    3 |      3 | |    4 |      5 | |    5 |      7 | +------+--------+5 rows in set (0.00 sec)

23. timestamp

mysql> SELECT  UNIX_TIMESTAMP(now());+-----------------------+| UNIX_TIMESTAMP(now()) |+-----------------------+|            1357521521 |+-----------------------+1 row in set (0.00 sec)mysql> SELECT FROM_UNIXTIME(1357521521);+---------------------------+| FROM_UNIXTIME(1357521521) |+---------------------------+| 2013-01-07 09:18:41       |+---------------------------+1 row in set (0.00 sec)