MySQL 基础知识-大杂烩

 select UNIX_TIMESTAMP(CURDATE()) ;
select FROM_UNIXTIME(1438440901);
 
 -- 优化相关
 1. 
 ANALYZE TABLE xxxxx
 select * from tb_customer_center_keyword_list PROCEDURE ANALYSE(10, 2000);
   ================================================
                       MySQL  优化器动作：
1. 
If there is an ORDER BY clause and a different GROUP BY clause, 
or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.
and these temporary tables can use the MEMORY storage engine if the original table does not contain any BLOB columns.
2. 
If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table.
3.
对大表做数据关联时，尽量不要把大表放到子查询中；有时可以考虑直接关联该表，并用 on 的条件做过滤。
4.
If all columns in ORDER BY and GROUP BY clauses come from the same table, that table is preferred first when joining.


  ===============================================
数据表设计上：
1.  选择合适的数据类型可以节省空间 MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.
2.  Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead 
for testing whether each value is NULL. You also save some storage space, one bit per column. 
3. The primary index of a table should be as short as possible.
4.  表的列名尽量简短；To make your names portable to other SQL servers, consider keeping them shorter than 18 characters.
5. 尽量用数值型代替字符串型，it is faster and takes less memory to transfer and compare them
6.  The BINARY operator casts the string following it to a binary string. This is an easy way to force a comparison to be done byte by byte rather than character by character.
The use of CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column causes the column to be treated as a binary data type. 
For example, the following pairs of definitions are equivalent:


CHAR(10) CHARACTER SET binary
BINARY(10)


VARCHAR(10) CHARACTER SET binary
VARBINARY(10)


TEXT CHARACTER SET binary
BLOB
   ===============================================




-- 存储相关
查看数据库/表分区的存储空间使用情况：
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA='ido_rc' AND TABLE_NAME='tb_pedometer_data_5m'
SELECT * FROM  INFORMATION_SCHEMA.partitions WHERE  TABLE_SCHEMA = 'ido_rc'   AND TABLE_NAME='tb_pedometer_data_5m'; 


5.6 的MySQL 不支持垂直表分区：
 MySQL 5.6 does not support vertical partitioning, in which different columns of a table are assigned to different physical partitions.




--  查询相关
1. MySQL 无分析函数


方法一：
       select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
方法二：
         show procedure status;
 
查看存储过程或函数的创建代码
show create procedure proc_name;
show create function func_name;
========================= 与 Shell 的结合
while [ $i -le 31021000 ]
do
mysql -uweixin -pDM_weIXin_@% ido -N -e "INSERT INTO tb_product_info VALUES($i,'2','计步器','1','1','1','1','蓝牙产品','100',NULL,'2012-04-26 12:00:00','2012-04-26 12:00:00','12345678','0','2015-01-21 12:00:00',NULL,'0','0','2013-03-30 09:11:00');"
let i=i+1
sleep 0.01
done


=========================


###  临时关闭外键校验     
mysql> SET foreign_key_checks=0;
mysql> truncate table tb_product_info;
mysql> SET foreign_key_checks=1  
==========================   存储相关 =========================================
1.  关于表空间： 
MySQL中，表空间概念完全不同于Oracle了；
在innodb_file_per_table 变量值为on时，系统会为每个表，创建单独的数据存储文件；


2.  关于表分区：
类似Oracle了；没有自动创建表分区机制；


3.  关于数据库引擎：
5.5 版本以上，默认为 Innodb，支持事物，行级锁定，外键约束，
存储上限 64TB ；


4.  OPTIMIZE TABLE 
如果已经删除了表的一大部分数据，或者如果已经对含有可变长度行的表（含有 VARCHAR 、 BLOB 或 TEXT 列的表）进行了很多更改，
就应该使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费 。
OPTIMIZE TABLE 命令只对 MyISAM 、 BDB 和 InnoDB 表起作用 。
表优化的工作可以每周或者每月定期执行，对提高表的访问效率有一定的好处，但是
需要注意的是，优化表期间会锁定表，所以一定要安排在空闲时段进行。！！！！！！！！！！！！！！！




注意： 
   MySQL 没有Oracle的 FlashBack 机制，delete 数据前，考虑备份一下；
MySQL 把同一个数据表里的索引总数限制为16个


-- 执行方式查询：EXPLAIN SELECT 
这其实只是简单地给一条普通的 SELECT 命令加一个 EXPLAIN 关键字作为前缀而已。
有了这个关键字，MySQL 将不是去执行那条 SELECT 命令，而是去对它进行分析。MySQL 将以表格的形式把查询的执行过程和用到的索引等信息列出来。


在 EXPLAIN 命令的输出结果里，第1列是从数据库读取的数据表的名字，它们按被读取的先后顺序排列。
type列指定了本数据表与其它数据表之间的关联关系（JOIN）。在各种类型的关联关系当中，效率最高的是 system，
然后依次是 const、eq_ref、ref、range、index 和 All（All 的意思是：对应于上一级数据表里的每一条记录，这个数据表里的所有记录都必须被读取一遍——这种情况往往可以用一索引来避免）。
possible_keys 数据列给出了 MySQL 在搜索数据记录时可选用的各个索引。
key 数据列是 MySQL 实际选用的索引，这个索引按字节计算的长度在 key_len 数据列里给出。比如说，对于一个 INTEGER 数据列的索引，这个字节长度将是4。
如果用到了复合索引，在 key_len 数据列里还可以看到 MySQL 具体使用了它的哪些部分。作为一般规律，key_len 数据列里的值越小越好。
ref 数据列给出了关联关系中另一个数据表里的数据列的名字。row 数据列是 MySQL 在执行这个查询时预计会从这个数据表里读出的数据行的个数。
row 数据列里的所有数字的乘积可以大致了解这个查询需要处理多少组合。
最后，extra 数据列提供了与 JOIN 操作有关的更多信息，比如说，如果 MySQL 在执行这个查询时必须创建一个临时数据表，就会在 extra 列看到 usingtemporary 字样。




启动跟踪文件
mysqld --debug
关闭服务器
mysqladmin -u root shutdown
启动MySQL服务
mysqld --console


service mysqld status
===============================
配置文件： /etc/my.cnf


windows下用net stop/start mysql来停止和启动mysql服务
linux下用service mysql restart
 
 
show databases ;
use mysql;
show tables; 


显示数据表的结构： describe 表名;


限制结果返回行数：select 1/2 from pms_bug limit 3,1;   --从第3 行开始，返回 1 行


   查看MYSQL数据库中所有用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;




mysql有关show的用法 
SHOW DATABASES列出 MySQL Server上的数据库。 SHOW TABLES [FROM db_name]列出数据库中的表。 
SHOW TABLE STATUS [FROM db_name]列出数据库的表信息，比较详细。 
SHOW COLUMNS FROM tbl_name [FROM db_name]列出表的列信息，同 SHOW FIELDS FROM tbl_name [FROM db_name]，DESCRIBE tbl_name [col_name]。 
SHOW FULL COLUMNS FROM tbl_name [FROM db_name]列出表的列信息，比较详细，同 SHOW FULL FIELDS FROM tbl_name [FROM db_name]。  
SHOW INDEX FROM tbl_name [FROM db_name]列出表的索引信息。 
SHOW STATUS列出 Server 的状态信息。 
SHOW VARIABLES列出 MySQL 系参数值 
SHOW PROCESSLIST查看当前mysql查询进程 
SHOW GRANTS FOR user列出用户的授权命令
show ENGINES 列出数据库引擎信息
show warnings; 显示最后一个执行的语句所产生的错误、警告和通知：
show errors 只显示最后一个执行语句所产生的错误：
show variables like 'general_log_file';  查看日志文件位置
select version() ;
show create table tb_app_info;
show global status like '%open%';


KILL语法
KILL [CONNECTION | QUERY] thread_id


每个与mysqld的连接都在一个独立的线程里运行，您可以使用SHOW PROCESSLIST语句查看哪些线程正在运行，并使用KILL thread_id语句终止一个线程。
KILL允许自选的CONNECTION或QUERY修改符：
·         KILL CONNECTION与不含修改符的KILL一样：它会终止与给定的thread_id有关的连接。
·         KILL QUERY会终止连接当前正在执行的语句，但是会保持连接的原状。


============================




数据备份 :    mysqldump
还原：    Mysql Source导入命令
mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql


==================================  用户授权等
1、用管理员登陆mysql
2、创建数据库create database db01;
3、创建用户user01只能本地访问CREATE USER user01@'localhost' IDENTIFIED BY 'password1';
   user02可以远程访问CREATE USER user02@'%' IDENTIFIED BY 'password1';
4、修改user01密码SET PASSWORD FOR 'user01'@'localhost' = PASSWORD('password2');
5、授权a)、user01管理db01全部权限GRANT ALL PRIVILEGES ON db01.* TO user01;
       b)、user02查看权限，并修改密码GRANT SELECT  ON *.* TO 'user02'@'%' IDENTIFIED by 'password2';
       
更改用户密码：
use mysql;
update user set password='iwatch' where user='iwatch';
用户赋权：
grant all privileges on healthmanager_test.* to healthmanager@'%' identified by "healthmanager" with grant option ;
grant all privileges on iwatch_242.* to iwatch@'%' identified by "iwatch" with grant option