mysql教程

1.常见的MySQL优化方案
    MySQL 数据库常见的优化手段分为三个层面：SQL 和索引优化、数据库结构优化、系统硬件优化等，然而每个大的方向中又包含多个小
1.1    SQL 和索引优化（使用正确的索引 。索引类型：普通索引、主键索引、唯一索引、组合索引、全文索引）
1.2    sql书写时的注意（适当使用前缀索引，关于JOIN优化，我们要尽量使用小表驱动大表的方式，进行查询增加冗余字段可以减少大量的连表查询，尽量用 union all 代替 union，尽量少排序 order by，SQL语句中IN包含的值不应过多，对于联合索引来说，要遵守最左前缀法则）
2.数据库备份
    备份的方法（是否需要数据库离线）可以将备份分为：热备（Hot Backup）（逻辑备份，裸文件备份）冷备（Cold Backup）温备（Warm Backup）
2.1 MySQL mysqldump备份数据库
 语法 mysqldump -u username -p dbname [tbname ...]> filename.sql  比如mysqldump -u（用户名） -p'（密码）' -B --all-databases （数据名称）>$(date "+%Y-%m-%d").sql（输入位置—名称）
  （多个数据库备份）mysqldump -u username -P --databases （名称1）（名称2）... > filename.sql（文件名称）
 （备份所以数据库）ysqldump -u username -P --all-databases>filename.sql（文件名称）
 MySQL恢复数据库（mysql命令）
    mysql -u username -P [dbname] < filename.sql（文件名称）
MySQL使用SELECTI...INTO OUTFILE导出表数据（SELECT * FROM tb_students_info INTO OUTFILE '1.txt'（位置名称）;）
2.2 二进制日志
    1.查看
    1.1查看二进制日志文件列表（SHOW binary logs）
    1.2查看当前正在写入的二进制日志文件（SHOW master status）
    1.3查看二进制日志文件内容（mysqlbinlog filename.number）
    2.删除
    2.1. 删除所有二进制日志(RESET MASTER;)
    2.2. 根据编号删除二进制日志(PURGE MASTER LOGS TO 'filename.number';)列如下面删除 mylog.000004 之前的二进制日志，代码如下（PURGE MASTER LOGS TO 'mylog.000004';）
    2.3 根据创建时间删除二进制日志（PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';）下面删除 2019-12-20 15:00:00 之前创建的二进制日志（PURGE MASTER LOGS TO '2019-12-20 15:00:00";）
    3.暂时停止二进制日志
    1.SET 语句来暂停/开启二进制日志功能(SET SQL_LOG_BIN=0/1;)也可通过配置my.ini设置（expire_logs_days = 10  expire_logs_day 定义了 MySQL 清除过期日志的时间、二进制日志自动删除的天数 max_binlog_size = 1​00M）
    4.二进制日志还原数据库
        mysqlbinlog filename.number（二进制文件名称） | mysql -u root -p
3用户管理
    一 创建用户
    1.使用 CREATE USER 语句创建用户
        CREATE USER <用户> [ IDENTIFIED BY [ PASSWORD ] 'password' ] [ ,用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]
        CREATE USER 创建一个用户，用户名是 test1，密码是 test1，主机名是 localhost（CREATE USER 'test1'@'localhost' IDENTIFIED BY 'test1';）
        可以使用 password() 函数获取密码的哈希值，查看 test1 哈希值的 SQL （select password（‘test1’））
        
        （CREATE USER 'test1'@'localhost'IDENTIFIED BY PASSWORD '*06C0BF5B64ECE2F648B5F048A71903906BA08E5C';）
        1) 用户：指定创建用户账号，格式为 user_name'@'host_name。这里的user_name是用户名，host_name为主机名，即用户连接 MySQL 时所用主机的名字。如果在创建的过程中，只给出了用户名，而没指定主机名，那么主机名默认为“%”，表示一组主机，即对所有主机开放权限。
        3) IDENTIFIED BY子句 ：用于指定用户密码。新用户可以没有初始密码，若该用户不设密码，可省略此子句。
        2) PASSWORD 'password' ：PASSWORD 表示使用哈希值设置密码，该参数可选。如果密码是一个普通的字符串，则不需要使用 PASSWORD 关键字。'password' 表示用户登录时使用的密码，需要用单引号括起来
    2.在 mysql.user 表中添加用户
        使用 INSERT 语句新建用户（INSERT INTO mysql.user(Host, User,  authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('hostname', 'username', PASSWORD('password'), '', '', '');
         FLUSH 命令让用户生效 ：FLUSH PRIVILEGES;
    3.使用 GRANT 语句创建用户
        GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password
    priv_type 参数表示新用户的权限；（看权限表）
    database.table 参数表示新用户的权限范围，即只能在指定的数据库和表上使用自己的权限；
    user 参数指定新用户的账号，由用户名和主机名构成；
    IDENTIFIED BY 关键字用来设置密码；
    password 参数表示新用户的密码。
        *.*” 表示所有数据库下的所有表。结果显示创建用户成功
    2.修改用户（RENAME USER）
        RENAME USER <旧用户> TO <新用户>（RENAME USER 'test1'@'localhost' TO  'testUser1'@'localhost';）（必须名字和主键同时修改）
        *使用 RENAME USER 语句，必须拥有 mysql 数据库的 UPDATE 权限或全局 CREATE USER 权限。
    3删除用户（DROP/DELETE USER）
        DROP USER 'test1'@'localhost';（使用 DROP USER 语句必须拥有 mysql 数据库的 DELETE 权限或全局 CREATE USER 权限。在 DROP USER 语句的使用中，若没有明确地给出账户的主机名，则该主机名默认为“%”。）
    DELETE语句删除普通用户
        DELETE FROM mysql.user WHERE Host='hostname' AND User='username';（Host 和 User 这两个字段都是 mysql.user 表的主键。因此，需要两个字段的值才能确定一条记录。）
        DELETE FROM mysql.user WHERE Host='localhost'AND User='test2';
    4查看用户权限
        SHOW GRANTS FOR 'username'@'hostname';（username 表示用户名，hostname 表示主机名或主机 IP）
    5用户授权（拥有 GRANT 权限的用户才可以执行 GRANT 语句）
        GRANT priv_type [(column_list)] ON database.table
        TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user[IDENTIFIED BY [PASSWORD] 'password']] ...
        [WITH with_option [with_option]...]
        priv_type 参数表示权限类型；
        columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；
        database.table 用于指定权限的级别；
        user 参数表示用户账户，由用户名和主机名构成，格式是“'username'@'hostname'”；
        IDENTIFIED BY 参数用来为用户设置密码；
        password 参数是用户的新密码。
               6REVOKE：删除用户权限
        REVOKE priv_type [(column_list)]...ON database.table FROM user [, user]...
        REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
        REVOKE 语法和 GRANT 语句的语法格式相似，但具有相反的效果。
        要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限。
4 数据库
    MySQL查看或显示数据库（SHOW DATABASES语句）（SHOW DATABASES [LIKE '数据库名'];）
    4.1创建并查看数据库（CREATE DATABASE test_db（数据库名称）;）
    CREATE DATABASE [IF NOT EXISTS] <数据库名>（IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。）
    [[DEFAULT] CHARACTER SET <字符集名>] 
    [[DEFAULT] COLLATE <校对规则名>];（[ ]中的内容是可选的。语法说明如下）
    （CREATE DATABASE IF NOT EXISTS test_db_char
             DEFAULT CHARACTER SET utf8
          DEFAULT COLLATE utf8_chinese_ci;）
    4.2修改
    ALTER DATABASE [数据库名] { 
    [ DEFAULT ] CHARACTER SET <字符集名> |
    [ DEFAULT ] COLLATE <校对规则名>}
    （ALTER DATABASE test_db
        DEFAULT CHARACTER SET gb2312
         DEFAULT COLLATE gb2312_chinese_ci;）
    4.3删除数据库（DROP DATABASE语句）
        DROP DATABASE [ IF EXISTS ] <数据库名>
    （<数据库名>：指定要删除的数据库名。
    IF EXISTS：用于防止当数据库不存在时发生错误。
    DROP DATABASE：删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心，以免错误删除。如果要使用 DROP DATABASE，需要获得数据库 DROP 权限）
    4.4选择数据库（MySQL USE语句）
        USE <数据库名>
5，查看系统变量
    SHOW GLOBAL VARIABLES; 
    MySQL 中的系统变量以两个“@”开头。
    @@global 仅仅用于标记全局变量；
    @@session 仅仅用于标记会话变量；
    @@ 首先标记会话变量，如果会话变量不存在，则标记全局变量。
6.数据表    
    SHOW CREATE TABLE <数据表名>;(查看表中的约束)
    1.MySQL创建数据表（CREATE TABLE语句）create table A as select * FROM B 复制A表创建B表
        CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];( PRIMARY KEY(主键约束),字段名 数据类型 AUTO_INCREMENT，FOREIGN KEY（外键），
<字段名> <数据类型> UNIQUE ，CHECK <表达式>，<字段名> <数据类型> DEFAULT <默认值>，<字段名> <数据类型> NOT NULL;;)
        [CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]REFERENCES <主表名> 主键列1 [，主键列2，…]
        *AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）
    2.ALTER TABLE：修改数据表
        ALTER TABLE <旧表名> RENAME [TO] <新表名>
        ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;
        ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；
        ALTER TABLE <表名> MODIFY <字段名> <数据类型>
        ALTER TABLE <表名> DROP <字段名>
        ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];（位置选择）FIRST（第一行）， AFTER <已经存在的字段名>;
    创建时添加约束条件
        ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);
        ALTER TABLE <数据表名> DROP PRIMARY KEY;
        ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
        ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
        ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
        ALTER TABLE <表名> DROP INDEX <唯一约束名>;
        ALTER TABLE tb_<表名> ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)
        ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;
        ALTER TABLE <数据表名>CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;
        ALTER TABLE <数据表名>CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;
        ALTER TABLE <数据表名>CHANGE COLUMN <字段名><字段名> <数据类型> NOT NULL;
        ALTER TABLE <数据表名>CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
    3.删除数据表（DORP TABLE语句）
        DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
    4，以表格的形式展示表结构
        DESCRIBE <表名>;DESC <表名>;
        以SQL语句的形式展示表结构（SHOW CREATE TABLE（表名））

7.操作表
    1.SELECT：数据表查询语句(DISTINCT(过滤重复数据)DISTINCT <字段名>,<表名> [AS] <别名>)
        SELECT
    {* | <字段列名>}            {*|<字段列名>}包含星号通配符的字段列表，表示所要查询字段的名称
    [
    FROM <表 1>, <表 2>…        <表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个
    [WHERE <表达式>            WHERE <表达式>是可选项，如果选择该项，将限定查询数据必须满足该查询条件。
    [GROUP BY <group by definition>    GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组
    [HAVING <expression> [{<operator> <expression>}…]]
    [ORDER BY <order by definition>]    [ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。
    [LIMIT[<offset>,] <row count>]    [LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。(LIMIT 初始位置，记录数)
    ]
    WHERE 查询条件
    查询条件可以是：
    带比较运算符和逻辑运算符的查询条件(多个查询条件时用逻辑运算符 AND（&&）、OR（||）或 XOR 隔开。)
    带 BETWEEN AND 关键字的查询条件([NOT] BETWEEN 取值1 AND 取值2)NOT：可选参数，表示指定范围之外的值。如果字段值不满足指定范围内的值，则这些记录被返回。
    带 IS NULL 关键字的查询条件(IS [NOT] NULL)IS NULL 是一个整体，不能将 IS 换成“=”
    带 IN 关键字的查询条件
    带 LIKE 关键字的查询条件([NOT] LIKE  '字符串'带有“%”通配符的查询*匹配的字符串必须加单引号或双引号。如果查询内容中包含通配符，可以使用“\”转义符)
    使用GROUP BY分组查询
        GROUP BY  <字段名>:GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。
        聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。其中，COUNT() 用来统计记录的条数；SUM() 用来计算字段值的总和；AVG() 用来计算字段值的平均值；MAX() 用来查询字段的最大值；MIN() 用来查询字段的最小值
        WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。
    WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
    CROSS JOIN：交叉连接
        SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句] SELECT <字段名> FROM <表1>, <表2> [WHERE子句]
    INNER JOIN：内连接 
        SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
    LEFT/RIGHT JOIN：外连接
        SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>(左连接)
        SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>(右连接)
    WHERE <表达式> <操作符> (子查询)：其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字
    2.INSERT：插入数据（添加数据）
        INSERT…VALUES 语句和 INSERT…SET 语句
        INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]VALUES (值1) [… , (值n) ];
        INSERT INTO <表名>SET <列名1> = <值1>,<列名2> = <值2>,
        整表复制
        INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中
        INSERT INTO（表名）（字段）SELECT（字段）FROM （表名）（INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。
）
    3.UPDATE：修改数据（更新数据）
        UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ][ORDER BY 子句] [LIMIT 子句]
    <表名>：用于指定要更新的表名称。
    SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
    WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
    ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
    LIMIT 子句：可选项。用于限定被修改的行数。
        注意：保证 UPDATE 以 WHERE 子句结束，通过 WHERE 子句指定被更新的记录所需要满足的条件，如果忽略 WHERE 子句，MySQL 将更新表中所有的行
    4.DELETE：删除数据
        DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
        <表名>：指定要删除数据的表名。
        ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
        WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
        LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
        TRUNCATE：清空表记录
    TRUNCATE 和 DELETE 的区别
        从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，但是在某些情况下，两者在使用上有所区别。
        DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
        DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
        DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
        DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
        DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
        DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。
    总结
    当不需要该表时，用 DROP；当仍要保留该表，但要删除所有记录时，用 TRUNCATE；当要删除部分记录时，用 DELETE。
8.MySQL视图、索引
    1.1.创建视图
    要注意区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
    CREATE VIEW <视图名> AS <SELECT语句>
    <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
    <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
    2查看视图
    DESC 视图名;
    查看视图的详细信息
    SHOW CREATE VIEW 视图名;
    所有视图的定义都是存储在 information_schema 数据库下的 views 表中，也可以在这个表中查看所有视图的详细信息，SQL 语句如下：
    SELECT * FROM information_schema.views;
    3.修改视图
        ALTER VIEW <视图名> AS <SELECT语句>
    4.删除视图
        DROP VIEW <视图名1> [ , <视图名2> …]
    2.1创建索引（CREATE INDEX）
        CREATE INDEX<索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
    <索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
    <表名>：指定要创建索引的表名。
    <列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
    <长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
    ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。
     CREATE TABLE（表名）CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)
    ALTER TABLE 语句
    ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)
    ADD PRIMARY KEY [<索引类型>] (<列名>,…)
    ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
    ADD FOREIGN KEY [<索引名>] (<列名>,…)
    查看索引
    SHOW INDEX FROM <表名> [ FROM <数据库名>]
    DROP INDEX <索引名> ON <表名>