MySQL

• 概念

  • MySQL的理论知识

    • 概念

      • 数据库就是用来存储和管理数据的仓库！

    • 数据库分类

      • 层次型数据库

        • 树型结构，一个子记录可以有一个父记录，一个父记录可以有多个子记录，类似一个二叉树，但是一个父节点可以不止两个子节点，可以有多个子节点

      • 网状型数据库

        • 以网装结构的方式，一个节点可以有多个父节点或子节点

      • 关系型数据库

        • 举例mysql，数据就是以表格方式存储，表与表之间通过外键维护关系。

    • MySQL语句分类

      • DDL

        • 数据定义语言，用来定义数据库对象：库、表、列等；

      • DML

        • 数据操作语言，用来定义数据库记录（数据）；

      • DCL

        • 数据控制语言，用来定义访问权限和安全级别

      • DQL

        • 数据查询语言，用来查询记录

    • 字符集编码分类 （指数据的存储格式）

      • utf8mb4_bin

        • 将字符串每个字符⽤⼆进制数据编译存储，区分⼤⼩写，⽽且可以存⼆进制的内容。推荐使用

      • utf8mb4_general_ci：ci

        • 不区分⼤⼩写。没有实现Unicode排序规则，在遇到某些特殊语⾔或者字符集，排序结果可能不⼀致。但是，在绝⼤多数情况下，这些特殊字符的顺序并不需要那么精确。

      • utf8mb4_unicode_ci

        • 是基于标准的Unicode来排序和⽐较，能够在各种语⾔之间精确排序，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

      • utf8mb4_general_ci

        • 是⼀个遗留的 校对规则，不⽀持扩展，它仅能够在字符之间进⾏逐个⽐较。

      • utf8_general_ci

        • 校对规则进⾏的⽐较速度很快，但是与使用 utf8mb4_unicode_ci的校对规则相比，比较正确性较差。

      • -修改数据库编码：

        • ALTER DATABASE mydb1 CHARACTER SET utf8;

      • 查看排序编码

        • show variables like 'collation_%';

      • 查看字符集

        • show variables like 'character_set_%';

    • 数据类型分类

      • 文本类型（对应java的字符集）

        • CHAR,VARCHAR,TINYBOB，TEXT等

      • 数值类型（对应正数，小数）

        • INT DOUBLE DECIMAL等

      • 日期类型（对应Data类型

        • DATE,TIME,YEAR,DATETIMEDATETIME,TIMESTAMP等

      • 其他类型（二进制文件类型）

    • 数据库命名规范

      • 在表的命名的过程中，最好在表的前面加个前缀tb，例如tb_user。

      • 表名由多个单词构成时：a_b create_time

• sql语句的学习

  • DDL语句

    • 对数据库的操作

      • 切换数据库：USE mydb1，切换到mydb1数据库；

      • 查看当前使用的数据库的名称: select database()；

      • 查看所有数据库名称：SHOW DATABASES

      • CREATE DATABASE [IF NOT EXISTS] 数据库名称；

      • 删除数据库：DROP DATABASE 数据库名称；

      • --修改数据库编码：ALTER DATABASE 数据库名称 CHARACTER SET 编码格式

    • 对表的操作

      • 创建表： CREATE TABLE 表名( 列名 列类型, 列名 列类型, ...... );

      • --查看当前数据库中所有表名称： SHOW TABLES;

      • -查看指定表的创建语句： 查看emp表的创建语句； SHOW CREATE TABLE emp;

      • --查看表结构： DESC emp; --查看emp表结构；

      • --删除表： DROP TABLE emp; --删除emp表；

      • 修改表： --给表添加列：给stu表添加classname列： --1ALTER TABLE stu ADD (classname varchar(100));

      • --2. 修改之修改列类型：修改stu表的gender列类型为CHAR(2)： ALTER TABLE stu MODIFY gender CHAR(2);

      • --3. 修改之修改列名：修改stu表的gender列名为sex： ALTER TABLE stu change gender sex CHAR(2);

      • --4. 修改之删除列：删除stu表的classname列： ALTER TABLE stu DROP classname;

      • 5. 修改之修改表名称：修改stu表名称为student： ALTER TABLE stu RENAME TO student;

  • DML

    • 插入语句

      • INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

    • 修改语句

      • UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

    • 删除数据

      • DELETE FROM 表名 [WHERE 条件]

      • TRUNCATE TABLE 表名 TRUNCATE TABLE stu;

      • 区别：TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。

  • DCL

    • 查询所有用户

      • Select * from mysql.user

    • 创建用户

      • CREATE USER 用户名@地址 IDENTIFIED BY '密码'; --user1用户只能在localhost这个IP登录mysql服务器

      • --user2用户可以在任何电脑上登录mysql服务器 CREATE USER user2@'%' IDENTIFIED BY '123';

    • 用户权限操作

      • 给用户授权

        • GRANT 权限1, … , 权限n ON 数据库.* TO 用户名;

      • 撤销授权

        • REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名;

      • 查看用户权限

        • SHOW GRANTS FOR 用户名; SHOW GRANTS FOR user1@localhost;

      • 删除用户

        • DROP USER 用户名; DROP USER user1@localhost;

      • 修改用户密码

        • USE mysql; UPDATE USER SET authentication_string=PASSWORD('密码') WHERE User='用户名' and Host='IP'; FLUSH PRIVILEGES；例如UPDATE mysql.USER SET authentication_string=PASSWORD('123456') WHERE USER='zhangsan' AND HOST='localhost'; FLUSH PRIVILEGES;

  • DQL语句

    • DQL单表查询

      • 单表查询的顺序

        • Select *(所有列)，列名1，列名2... from 表名 【where 字句】 条件查询 【group by 列名】 #分组 【having 字句】 分组条件筛选 【order by 列名 排序方式】 #排序 【limit [x,y]】 #分页顺序

      • 分析的步骤

        • 1.找表 指定的 根据需要列找出对应的表

        • 2.找查询条件

        • 3.找查询的列

      • 聚合函数

        • COUNT() 统计指定列不为NULL的记录行数

        • MAX() 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算

        • MIN() 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算

        • SUM() 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0

        • AVG() 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

        • ifnull（字段,数据） 如果一个数据为空，则放回指定的数据

      • 注意：

        • where里面不能出现聚合函数

        • 在查询的过程中null是一个特殊的值，任何与null运算都是null，任何与null比较都是false。

    • DQL多表查询

      • 表与表的关系

        • 一对一

          • 通过主外键连结

        • 多对多

          • 使用中间表存储两张表的关系，命名规则：tb_A表名_B表名

        • 一对多

          • 在多方面的表创建外键，唯一表创建主键

      • 连结方式

        • 联合查询

          • 合并结果集就是把两个select语句的查询结果合并到一起！

            • 语句：UNION：去除重复记录SELECT * FROM t1 UNION SELECT * FROM t2； UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

        • 连结查询

          • 连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

            • 语句：SELECT * FROM t1,t2

        • 内连接

          • 一个表跟另外一个表通过join on连结

            • 语句：列如：SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

        • 外连结

          • 左连接

            • join on左边的表显示所有的内容，不管跟它连接的表它是否为null

              • 语句：SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件

          • 右连接

            • join on右边的表显示所有的内容，不管跟它连接的表它是否为null

              • 语句：SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

        • 自连接

          • 自己跟自己连接

            • 例子：select a.name , b.name from emp a , emp b where a.managerid = b.id;

        • 子查询

          • 就是嵌套查询

            • 例子：SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

  • 完整性约束

    • 主键约束

      • 唯一且不为空，每一张表只能有一个主键约束

        • 关键字：PRIMARY KEY

          • 注意：每张表都应该有一个主键，主键在非业务数据上不允许修改

    • 唯一约束

      • 每一张表都可以有多个唯一约束

        • 关键字：UNIQUE

    • 非空约束

      • 限制数据不为null

        • 关键字：NOT NULL

    • 外键约束

      • 用来让两张表的数据之间建立连接，保证数据的一致性和完整性

        • FOREIGN KEY

    • 检查约束

      • 保证字段值满足某一个条件

        • 关键字：CHECK

    • 默认约束

      • 保存数据时，如果未指定该字段的值，则采用默认值

        • 关键字：UNIQUE

• 系统函数

  • 数值函数

    • ABS

      • 求绝对值

    • SQRT

      • 求二次方根

    • MOD

      • 求余数

    • RAND

      • 生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列

  • 字符串函数

    • LENGTH

      • 计算字符串长度函数，返回字符串的字节长度

    • CONCAT

      • 合并字符串函数，返回结果连结字符参数

    • INSERT

      • 替换字符串函数

    • LOWER

      • 将字符串中的字母转换为小写

    • UPPER

      • 将字符串中的字母转换为大写

  • 日期函数

    • WEEK

      • 获取指定日期是一年中的第几周

    • YEAR

      • 获取年份，返回值范围是 1970〜2069

    • LAST_DAY

      • 获取指定日期这个月的最后一天的日期

• 视图

  • 概念：视图只保存了查询的SQL逻辑，不保存查询结果可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图

  • 基本操作

    • 创建视图

      • 语法：CREATE OR REPLACE VIEW emp_v_1 AS SELECT * FROM emp WHERE sal <= 2000;

    • 查询

      • 语法：#查看创建视图语句： SHOW CREATE VIEW 视图名称; #查看视图数据： SELECT * FROM 视图名称 ...... ;

    • 修改

      • CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

    • 删除

      • DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

• 索引

  • 概念：帮助提升查询效率的数据结构，还维护着满足 特定查找算法

  • 优势及劣势

    • 优势

      • 提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

    • 劣势

      • 降低了更新，插入的效率，索引列也是要占用空间的

  • 索引结构

    • B+Tree索引

      • 最常见的索引类型，大部分引擎都支持 B+ 树索引（MySQL也是）

    • Hash索引

      • 底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询

    • R-tree(空间索引）

      • 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

    • Full-text(全文索引)

      • 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

  • 索引分类

    • 主键索引

      • 就是表中主键创造的索引。语法：PRIMARY

    • 唯一索引

      • 避免同一个表中某数据列中的值重复。语法：UNIQUE

    • 快速定位特定数据

      • 可以有多个

    • 全文索引

      • 全文索引查找的是文本中的关键词，而不是比较索引中的值

  • 语句

    • 创建索引

      • CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;

    • 查看索引

      • SHOW INDEX FROM table_name ;

    • 删除索引

      • DROP INDEX index_name ON table_name ;