mysql

一些小技巧：

正则表达式：REGEXP
    select name,email from table where email REGEXP "@163[.,]com$;

RAND()提取随机行
    select * from table order by rand（） limit 5；随机排列后取出前5行

利用group by 的with rollup子句做统计
    select * from table group by id，name ……with rollup；根据id，name做统计   注意：当使用rollup时，不能同时使用order by，limit用在rollup后面


bit_or和bit_and来求出所需要

系统变量lower_case_tables_name等于0，1，2来解决定义大小写问题




SQL优化：

show [session|global]status like 'com_%';  session表示session级（当前连接），global表示global级（自数据库上次启动至今），找出匹配'com_%'的统计结果

explain分析低效SQL的执行计划：
           explain select * from table1 a，table2 b where……

分析表：analyze table tablename；
检查表：check table tablename；
优化表：optimize table tablename；整理一些碎片和空间浪费
在数据库不繁忙的时候执行以上3个操作


大批量插入数据:
                对于MyISAM存储引擎的表：
                alter tablename disable keys;开始
(可以提高速度)  loading the data
                alter tablename enable keys;关闭

                对于InnoDB存储引擎的表：
                    在导入数据前执行set unique_checks=0,关闭唯一性校验，在导入结束后执行set unique_checks=1，恢复唯一性校验，可以提高速度。如果应用使用自动提交的方式，建议在导入                前执行set autocommit=0，关闭自动提交，导入结束后再执行set autocommit=1，打开自动提交，也可以提高速度。

SQL 提示：select SQL_BUFFER_RESULTS *from……   这个语句将强制MySQL生成一个临时结果集，只要临时结果集生成后，所以表上的锁定均被释放。
          1.use index  在查询语句中表名的后面，添加use index来提供希望MySQL去参考的索引列表
          2.ignore index   ………………………………ignore index………………忽略………………
          3.force index  …………………………………………指定一个特定的索引



优化表的数据类型：PROCEDURE ANALYSE()
                 select * from tablename procedure analyse();





数据库备份和恢复：

1.数据库备份：使用mysqldump工具

           备份指定的数据库或数据库中的表： shell>mysqldump [options] db_name [tables]
           备份指定的一个或多个数据库：     shell>mysqldump [options] ---database DB1 [DB2 DB3 ……]        [options]为数据库用户名和密码-uroot -p
           备份所以数据库：                 shell>mysqldump [options] --all-database


2.数据库恢复：
           mysql -uroot -p dbname < bakfile 注意将备份恢复后数据并不完整，还需要将备份后执行的日志进行重做
           mysqlbinlog binlog-file | mysql -u root -p ***


表的导入导出：

可以使用system +操作系统命令来查看
表的导出：方法1.select *from tablename into outfile 'target_file' [option];  [option]中的参数为导出时的格式
          方法2.mysqldump -u username -T target_dir dbname tablename [option];

表的导入：方法1.Load data [local] infile 'filename' into table tablename [option];
          方法2.mysqlimport -u root -p*** [--local] dbname order_tab.txt [option];



忘记MySQL的root密码：
           1.登录到数据库所在服务器，手工kill掉MySQL进程
             kill'cat /mysql-data-directory/hostname.pid'
           2.使用--skip-grant-tables选项重启MySQL服务
             [root@localhost mysql]# ./bin/mysqld_safe --skip-grant-tables --user=zzx &
             [1] 20881
             [root@localhost mysql]# Starting mysqld daemon with databases from /home/zzx/mysql/data
           3.用空密码的root用户连接到MySQL，并且更改root口令
             [root@localhost mysql]# mysql -uroot
MyISAM存储引擎的表损坏：
            myisamchk -r tablename
        或  myisamchk -o tablename
            
             mysql>update user set password=password('123') where='root' and host='localhost';
           4.刷新权限表，使得权限认证重新生效 
             mysql>flush privileges;