[大数据]我的MySQL学习

这个知识重要吗？有什么用？

一、数据库概述

持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。

• db 数据库
• dbms 数据库管理系统
• sql 结构化查询语言

数据库管理系统(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

（1.1）关系型数据库

是最古老的数据库联系，关系型数据库模型是把复杂的数据结构归结为简单的二元关系 （即二维表格形式）。
以行列的形式存储数据，称为表。

ORM思想 (Object Relational Mapping)体现：
数据库中的一个表 <—> Java或Python中的一个类
表中的一条数据 <—> 类中的一个对象（或实体）
表中的一个列 <----> 类中的一个字段、属性(field)

二、SQL

（2.1）SQL的分类

• ddl 数据定义语言，以用来创建、删除、修改数据库和数据表的结构。
• dml 数据操作语言，用于添加、删除、更新和查询数据库记录，并检查数据完整性。
• dcl 数据控制语言，用于定义数据库、表、字段、用户的访问权限和
  安全级别。

（2.2）SQL的规范

• 注释
  • # 文字 -- 文字 /* 文字 */
• 习惯将关键字大写、数据列和表名小写
• desc 显示表结构

（2.3）Select

• SELECT [字段] FROM

在生产环境下，不推荐你直接使用 SELECT * 进行查询。

• AS 别名 重命名一个列

• DISTINCT 去除重复行，DISTINCT 需要放到所有列名的前面，实是对后面所有列名的组合进行去重。

• 所有运算符或列值遇到null值，运算的结果都为null。

• ` 着重号，告诉数据库是自定义的字段，而不是关键字。

• where 过滤数据

三、运算符

• +、-、*、/、% 加减乘除取模，如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算，整数和整数还是整数，整数和浮点数结果是浮点数。

• = 判断两个变量是否相等，相等返回1，不等返回0。

• <=> 安全的判断两个变量是否相等，可以的null 进行判断，均为null时，返回1，否则为0。

• <> != 判断两个变量是否不等，不等返回1，否则返回0。

• is null 判断是否为空

• is notnull 判断是否不为空

• least 取最小值

• greatest 取最大值

• between A and B 判断是否在[A,B] 之间

• isnull 判断是否为空

• in(值) 判断是否属于列表中的值

• not in() 判断是否不属于

• like 模糊匹配

  • % 零个或多个
  • _ 只能匹配一个

• regexp 正则表达式

• rlike 正则表达式

• not ! 逻辑非

• and && 逻辑与

• or || 逻辑或

• xor 逻辑异或

四、排序与分页

• order by 字段 [选项] 进行排序，默认升序，当对多列进行排序时，第一列必须相同。
  • asc 升序
  • desc 降序
• limit [位置偏移量] 行数 位置偏移量从0开始，显示行数行，LIMIT 子句必须放在整个SELECT语句的最后。

五、多表查询

多表查询也称为关联查询，指的是多个表一起完成查询工作。

要控制连接表的数量。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
来源：阿里巴巴《Java开发手册》

（5.1）笛卡尔错误

笛卡尔积的错误会在下面条件下产生：
省略多个表的连接条件（或关联条件）
连接条件（或关联条件）无效
所有表中的所有行互相连接
为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

（5.2）等值连接与非等值连接

等值连接：多个表是判断一个值是否相等来进行关联的。

• 多个连接条数使用 and
• 具有相同列时，必须加上表名前缀
• 表起别名，简化查询
• 连接n个表必须n-1个条件

（5.3）自连接和非自连接

自连接：当前表复制一份，虚拟成两张表，再进行查询。

（5.4）内连接和外连接

内连接：结果集中不包括不匹配的行。
外连接：结果集中除了满足条件的行，还包括左表中不满足条件的行，称为左连接。

左连接：左表为主表，右表为从表。

（5.5）join on

join 表 on 连接条件

JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接。

left join on

（5.6）UNION

合并查询结果

union 返回两个结果集的并集，去除重复记录

union all 返回两个结果集的并集，不去除重复记录

六、聚合函数

输入一组数据的集合，输出单个值。

• AVG() SUM() 取平均值，和总和。

• MIN() MAX() 取最大值，最小值。

• count() 返回记录总数，count(*) = count(1)，而当列为null时，count不会进行计数。

• group by 字段 可以使用GROUP BY子句将表中的数据分成若干组，WHERE一定放在FROM后面，将具有相同字段的行合并。

• HAVIBG 必须跟着group by 一起使用，在结果集中进行筛选。

七、Select的顺序

• 关键字顺序

  1. select
  2. from
  3. where
  4. group by
  5. having
  6. order by
  7. limit

• 执行顺序

  1. from
  2. where
  3. group by
  4. having
  5. select 选择字段
  6. distinct
  7. order by
  8. limit
     在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步
     骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

八、子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

先查询出一个结果，然后和结果进行比较。

就是在一个结果集里面进行查询。

（8.1）单行子查询

单行子查询只能使用单行比较操作符
= > >= < <= <>

（8.2）多行子查询

• in 等于列表中的任意一个
• any 和单行比较符一起使用，和子查询返回的某个值进行比较。
• all 与子查询返回的全部值进行比较
• some = any

九、创建和管理表

（9.1）创建和管理数据库

1. CREATE DATABASE 数据库名;
2. CREATE DATABASE 数据库名 CHARACTER SET 字符集;
3. CREATE DATABASE IF NOT EXISTS 数据库名;

DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

（9.2）使用数据库

• show databases 查看所有数据库
• select database() 查看当前使用的数据库
• show tables from 数据库名 查看指定数据库下的所有表。
• show create database 数据库名 查看数据库的创建信息。
• use 数据库名 切换数据库

（9.3）修改数据库

• ALTER DATABASE 数据库名 CHARACTER SET 字符集; 更改数据库字符集

（9.4）删除数据库

• drop database 数据库名; 删除指定的数据库
• drop database if exists 数据库名 删除指定的数据库，如果存在的话。

（9.5）创建表

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);

必须指定：表名、列名（数据类型，长度）
可选指定：约束条件，默认值。

（9.6）修改表

修改表指的是修改数据库中已经存在的数据表的结构。

• 使用 ALTER TABLE 语句可以实现：
  1. 向已有的表中添加列
     ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
  2. 修改现有表中的列
     ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名 2】;
  3. 删除现有表中的列
     ALTER TABLE 表名 DROP 【COLUMN】字段名
  4. 重命名现有表中的列
     ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

（9.7）删除表

当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

DROP TABLE 语句不能回滚

（9.8）清空表

TRUNCATE TABLE语句：
删除表中所有的数据
释放表的存储空间

TRUNCATE TABLE detail_dept;

十、增删改

（10.1）插入数据

• 按照默认顺序插入数据

INSERT INTO 表名
VALUES (value1,value2,....);

• 指定字段插入数据

sql INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);

• 插入多条数据

INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

多行的INSERT语句在处理过程中效率更高。

（10.2）更新

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

（10.3）删除

DELETE FROM table_name [WHERE <condition>];

table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。

十一、约束

（11.1）非空约束

（11.2）唯一性约束

用来限制某个字段/某列的值不能重复。

alter table 表名称 add unique key(字段列表);
字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的。

（11.3）主键约束

用来唯一标识表中的一行记录。
主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。

（11.4）自增列

十二、视图

一个或者多个数据表里的数据的逻辑显示，视图并不存储数据。

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。

视图是一种 虚拟表 ，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。

视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

视图的创建和删除不会影响基表，对视图增加删除修改，基表会发生变化。

可以将视图理解为存储起来的 SELECT 语句。

（12.1）创建视图

（12.2）查询视图

select * from 视图

十三、存储过程

（13.1）存储过程

存储过程就是一组经过预先编译的 SQL 语句的封装。

好处：简化操作，减少失误，减少网络传输量，减少风险。

而存储过程是程序化的 SQL，可以直接操作底层数据表，存储过程没有返回值。

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。

（13.2）分类

存储过程是没有返回值的，这里的返回值指的是return ，但是它传入的参数可以进行修改，再传回去。

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：
1、没有参数（无参数无返回）
2、仅仅带 IN 类型（有参数无返回）
3、仅仅带 OUT 类型（无参数有返回）
4、既带 IN 又带 OUT（有参数有返回）
5、带 INOUT（有参数有返回）

（13.3）创建存储过程

十四、存储函数

指导复习的问题

容易犯的错误

开发者涨薪指南

48位大咖的思考法则、工作方式、逻辑体系