mysql常用命令语句（含常见报错），多年总结

/* * 查看mysql版本 **/
select version();

-- 允许远程连接
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'a510978768' WITH GRANT OPTION; 
FLUSH PRIVILEGES;

/* * 显示数据库 **/
show databases;

/* * 主从状态查看，读写分离 **/
SHOW MASTER STATUS;

/* * 数据库是否支持分区 **/
SHOW VARIABLES LIKE '%partition%';

/* * 显示数据库存储路径 **/
show variables like '%dir%';

/* * 显示数据库引擎及特性 **/
show engines;

/* * 查看InnoDB为YES,即表示数据库支持InnoDB了 **/
show variables like 'have_%';

/* * 临时取消外键限制（约束） **/
SET foreign_key_checks=0;//禁用

SET foreign_key_checks=1;//启用

/* *  查询哪个表数据量最大，gx210413为数据库名 **/
use information_schema;
select table_name,table_rows 
from tables where table_schema='gx210413' 
order by table_rows desc limit 10;

/* *  使用delete TABLE 表名 删除表数据后，如果需要释放空间执行以下操作 **/
OPTIMIZE TABLE 表名;

/* *  性能查询 **/
EXPLAIN SELECT SQL_NO_CACHE ……

type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
key_len列，索引长度。
rows列，扫描行数。该值是个预估值。
extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

/* *************************************** 数据库相关报错 ****************************** */
/* * root@%报错，数据库授权问题解决 **/
grant all privileges on *.* to root@"%" identified by ".";
flush privileges;

/* ** Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. *** */
1.在配置文件中添加关闭严格模式的配置：
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
2.在配置文件中添加使用独立表空间的配置：
    innodb_file_per_table=1
3.在数据库中执行：
    SHOW GLOBAL VARIABLES LIKE '%innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
+--------------------------+-----------+
确保innodb_file_format 使用的是Barracuda，如果不是执行以下语句：SET GLOBAL innodb_file_format = barracuda;
此时，即可解决mysql报错1118的问题。

/* *************************************** 函数 ***************** */
1、判断空值
    ISNULL(v)：函数仅仅是用于判断空值的，接受一个参数并返回一个布尔值，
                SELECT ISNULL('i like yanggb'); // 0
                SELECT ISNULL(NULL); // 1
    IFNULL(v1, v2)：它接受两个参数，如果第一个参数不是NULL，则返回第一个参数；否则，将返回第二个参数。两个参数都可以是文字值或表达式。
                SELECT IFNULL(NULL, 'i like yanggb'); // i like yanggb

2、多个判断条件：CASE WHEN         
    场景1：分数区间判断
        有分数score，score<60返回不及格，score>=60返回及格，score>=80返回优秀
        SELECT STUDENT_NAME,
            (CASE WHEN score < 60 THEN '不及格'
                WHEN score >= 60 AND score < 80 THEN '及格'
                WHEN score >= 80 THEN '优秀'
                ELSE '异常' END) AS REMARK
        FROM TABLE        

    场景2：经典行转列，并配合聚合函数做统计
        现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果
        有能耗表如下：其中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗

            E_CODE    E_VALUE     E_TYPE
            北京    28.50    0
            北京    23.51    1
            北京    28.12    2
            北京    12.30    0
            北京    15.46    1
            上海    18.88    0
            上海    16.66    1
            上海    19.99    0
            上海    10.05    0
        SELECT 
            E_CODE,
            SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
            SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
            SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
        FROM 
            THTF_ENERGY_TEST
        GROUP BY
            E_CODE
            
/* *************************************** linux操作命令集 ***************** */
service mysqld start    --启动服务
service mysqld stop        --停止服务