MySQL常用SQL

环境介绍：
OS:RHEL5.5 && CentOS6.X
MySQL:5.5
1.查看数据库对象
数据的对象包括表，视图，触发器，等等(查看统计信息的必须进入information_schema 数据库)
举例查看表相关的信息，步骤如下
1、使用information_schema 数据库
mysql>use information_schema; 
2、查询所有数据的大小(MB)：
select concat(round(sum(data_length/1024/1024),2),'MB') as data_size from tables; 
3、查看指定数据库(schema)的大小(MB)：
select concat(round(sum(data_length/1024/1024),2),'MB') as DB_size from tables where table_schema='ehr';
4、查看指定数据库的指定表(table)的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as table_size from tables where table_schema='ehr' and table_name='ehr_age';
5、统计客户端连接IP
select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
6、查看MySQL数据库大小
SELECT table_schema "DATABASE Name", SUM(data_length + index_length) / 1024 / 1024" DATABASE Size IN MB"
FROM information_schema.TABLES
GROUP BY table_schema;
7、找出前10的表大小
SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') ROWS, CONCAT(ROUND(data_length / (1024 * 1024 * 1024), 2), 'G') DATA, CONCAT(ROUND(index_length / (1024 * 1024 * 1024), 2), 'G') idx, CONCAT(ROUND((data_length + index_length) / (1024 * 1024 * 1024), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;


2.查看SQL帮助命令
和学习Oracle一样，在使用MySQL时，必须习惯并熟练使用命令
如何查看帮忙？
进入mysql命令行，直接打“？+需要帮助的命令”
mysql> ? show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]




like_or_where:
    LIKE 'pattern'
  | WHERE expr
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values.
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/show.html


mysql> ? view
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER VIEW
   CREATE VIEW
   DROP VIEW




mysql> ? function
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER FUNCTION
   CREATE FUNCTION
   CREATE FUNCTION UDF
   CREATE PROCEDURE
   DROP FUNCTION
   DROP FUNCTION UDF
   DROP PROCEDURE
   SHOW
   SHOW CREATE FUNCTION
   SHOW FUNCTION CODE
   SHOW FUNCTION STATUS




mysql> ? table;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER TABLE
   ANALYZE TABLE
   CHECK TABLE
   CHECKSUM TABLE
   CREATE TABLE
   DROP TABLE
   OPTIMIZE TABLE
   RENAME TABLE
   REPAIR TABLE
   SHOW
   SHOW CREATE TABLE
   SHOW TABLE STATUS
   SPATIAL
   TRUNCATE TABLE


mysql> ? SHOW CREATE TABLE
Name: 'SHOW CREATE TABLE'
Description:
Syntax:
SHOW CREATE TABLE tbl_name
Shows the CREATE TABLE statement that creates the named table. To use
this statement, you must have some privilege for the table. This
statement also works with views.
SHOW CREATE TABLE quotes table and column names according to the value
of the sql_quote_show_create option. See
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/show-create-table.html
Examples:
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM


contents会告诉你你可以查看那些命令


mysql> ? contents;
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility


mysql下载地址
http://mirrors.sohu.com/mysql/  搜狐镜像
http://mysql.mirror.kangaroot.net/Downloads/
http://ftp.ntu.edu.tw/pub/MySQL/Downloads/
mysql帮助文档
http://dev.mysql.com/doc/#manual
mysql配置工具
https://tools.percona.com/wizard