MySQL常用操作及优化总结

MySQL连接
    1：主机IP
    2：主机PORT
    3：数据库用户及密码
    4：使用MySQL不需要申请表空间，但需要schema

linux通过MySQL客户端连接数据库
    mysql -h127.0.0.1 -P3316 -umysql -pmysql

schema
    1：亦可以叫做database，库、数据库，是MySQL中组织和管理表的一个结构。
    2：进行创建表及相关curd操作，必须先有schema
    3：MySQL默认包含4个schema，test用户可以操作，其余3个系统使用

MySQL使用
    1：创建数据库  create database 数据库名;
    2：查看数据库  show databases;
    3：选择数据库  use 数据库名;
    4：删除数据库  drop database 数据库名;
    5：查看建表语句    show create table 表名;
    6：mysql支持标准SQL

MySQL基本操作
    1：查看表结构    desc tablename
    2：复制表        create table 新表名 like 源表名
    3：删除表        drop table 表名1[,表名2...]
    4：修改表        alter table 表名 更改动作1[,更改动作2...]
                    -- alter table student add addr(25)  --增加表字段
                    -- alter table student changes addr text --更改表字段名和字段类型
                    -- alter table student drop addr --删除表字段
                    -- alter table student rename to stu --更改表名称

limit
    MySQL使用limit n,m分页，limit位于select整个查询语句之后
    limit n,m 表示从第n行开始取m行数据，n可省略，默认从0开始

事务控制
    1：设置事务提交方式
        set autocommit = 0|1  --自动提交:1（mysql默认自动提交） --自行控制提交:0
    2：开始事务    start transaction|begin
    3：提交事务    commit
    4：回滚事务    rollback

MySQL存储引擎
    1：MySQL支持多种存储引擎，在处理不同的应用时，可以通过选择不同的存储引擎来提高应用效率，灵活存储
    2：MySQL存储引擎包括:InnoDB、MyISAM、BDB、MEMORY、等，其中只有InnoDB和BDB提供事务安全表，其它存储
    引擎都是非事务安全表
    3：创建表时可以指定表使用的存储引擎和字符集
        -- create table mydb(id int,name char(20)) engine=InnoDB charset=utf8
    4：InnoDB存储引擎
        -- MySQL默认存储引擎
        -- 支持事务
        -- 行锁设计，提供高并发性
        -- 支持外键，确保数据完整
        -- 独立的ibd文件存储表结构和数据
        -- 支持全文索引
        适用场景：强调可靠性和需要支持事务的处理
    5：MyISAM存储引擎
        -- 不支持事务（指的是对数据不能进行回滚这些，并不是部分成功部分失败，数据库本身会保证原子性）
        -- 表锁设计
        -- 不支持外键
        -- 支持全文索引
        -- 使用数据压缩和索引优化
        -- 特点：可靠性高、适用范围广、检索速度快
        适用场景：强调数据检索速度，读写并发不多场景，写少读多

MySQL支持的基本数据类型
    -- Numeric类型
    -- data and time 类型
    -- String类型
    选择原则：根据选定的存储引擎，确定合适的数据类型
    1：按照存储引擎选择数据类型
        InnoDB存储引擎和数据列，建议使用varchar类型
        --对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有的数据行都指向数据列值
            的头指针）因此在本质上，使用固定长度的char列不一定比使用可变长度varchar列简单。因而，主要
            性能的因素是数据行使用的存储总量。由于char占用的平均空间多余varchar，因此使用varchar来最
            化需要处理的数据行。
        5：MyISAM存储引擎和数据列
        -- MyISAM数据表，最好使用固定长度的数据列代替可变长度的数据列
    2：数据类型选择
        1：数字型，字符串
            优先使用数字类型，占用空间少，处理速度快
        2：优先使用ENUM或SET
            字符串，固定选项值
            -- `sex` enum('F','M') comment '性别'
        3：避免使用null字段
            -- 很难进行查询优化
            -- null列加索引，需要额外空间，需要避免
            -- 含null复合索引无效
        4：少用并拆分text/blob
            -- 很难进行查询优化
            -- 强制生成硬盘临时表，浪费更多空间
            -- 若必须使用则拆分成单独的表

MySQL的索引
    1：MySQL表所有列都可以建索引
    2：在MySQL5.6中，对于InnoDB表，整字段索引最大支持767字节，前缀最大可以支持3072字节长。
    索引的建立
        1：搜索的所以列，不一定是要选择的列，最适合索引的列是出现在where字句中的列
        2：使用唯一索引，考虑某列中值的分布
        3：使用短索引，如果对串列进行索引，应该指定一个前缀长度。
        4：使用利用最左前缀

SQL语句优化
    1：sql要尽可能简单，复杂sql要尽量拆成小的sql
    2：事务要简单，整个事务的时间长度不要太长
    3：避免使用触发器、函数、存储过程
    4：降低业务耦合度，业务各自使用独立的库、表
    5：避免在数据库中进行数字运算
    6：不要用select*，查询那几个字段就select这几个字段即可
    7：sql中使用or的改写为in(),因为or的效率没有in高，in里边的数字个数建议控制1000以内
    8：limit分页注意效率，limit越大，效率越低
    9：使用union all 代替 union
    10：避免使用大表的join
    11：使用group by 分组排序
    12：在sql语句中，尽量避免前缀是%的like
    13：SQL语句中不可以出现隐式转换，比如select id from stu where id='1' 会把id隐式转换为字符

Oracle与MySQL的一些差异
    1：自动增长的数据类型处理
        MySQL有自动增长数据类型，插入数据时不用操作此字段，会自动获得数据值。
        Oracle没有此类型
        MySQL自增列的定义：
            CREATE TABLE t1 (
                             c1 INT(11) NOT NULL AUTO_INCREMENT,
                             c2 VARCHAR(10) DEFAULT NULL,
                             PRIMARY KEY (c1)
                            ) ENGINE=InnoDB;
        默认自增长从1开始增长；
    2：单引号处理
        MySQL里可以用单引号、双引号包起字符串，Oracle里只可以用单引号包起字符串。
        在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。
    3：翻页的sql语句处理
        MySQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数；Oracle处理翻页的SQL语句就比较
        繁琐了。每个结果集只有一个ROWNUM字段标明它的位置。
        -- MySQL:select * from test limit 1,10;
    4：日期字段处理
        MySQL日期时间字段分DATE、DATETIME、TIMESTAMP、TIME、YEAR，
        Oracle日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE, 精确到秒，
        或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’) 年-月-日 24小时:分钟:秒
        的格式YYYY-MM-DD HH24:MI:SS TO_DATE()日期型字段转换成字符串函数TO_CHAR(‘2001-08-01’,’YYYY-MM-DD
        HH24:MI:SS’) 日期字段的数学运算公式有很大的不同。MySQL找到离当前时间7天用 DATE_FIELD_NAME > SUBDATE（（NOW（），INTERVAL 7
        DAY）Oracle找到离当前时间7天用 DATE_FIELD_NAME >SYSDATE - 7；
    5：空字符的处理
        MySQL的非空字段也有空的内容，Oracle里定义了非空字段就不容许有空的内容。 
        按MySQL的NOT NULL来定义Oracle表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，
        如果为NULL或空字符，需要把它改成一个空格的字符串。