数据库基本知识

以下为自己在学习数据库的一些笔记

什么是数据库?

数据库：DataBase（DB），是存储和管理数据的仓库

数据库管理系统：DataBase Management System (DBMS)，操纵和管理数据库的大型软件。 SQL：Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

MySQL概述

MySQL安装

此次采用的是MySQL的最新社区版

官网下载地址：https://dev.mysql.com/downloads/mysql/

Windows (x86, 64-bit), ZIP Archive

MYSQL连接

mysql -u用户名 -p密码 [ -h数据库服务器IP地址 -P端口号 ]

也可以这样输入

关系型数据库（RDBMS）: 建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

SQL简介

SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准

SQL语句可以单行或多行书写，以分号结尾。 SQL语句可以使用空格/缩进来增强语句的可读性。 MySQL数据库的SQL语句不区分大小写。 注释： 单行注释：-- 注释内容 或 # 注释内容(MySQL特有) 多行注释： /* 注释内容 */

SQL分类

DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

数据库设计-DDL

DDL 英文全称是 Data Definition Language，数据定义语言，用来定义数据库对象(数据库、表)。

DDL（数据库操作）

查询所有数据库：show databases; 查询当前数据库：select database();

使用数据库：use 数据库名 ;

创建数据库：create database [ if not exists ] 数据库名 ;

删除数据库：drop database [ if exists ] 数据库名 ;

上述语法中的database，也可以替换成 schema。如：create schema db01;

MySQL客户端工具

MySQL客户端工具-图形化工具

本次学习使用idea自带的数据库工具

连接以后即可使用

DDL（表操作）

create table 表名(

          字段1 字段类型 [ 约束 ] [ comment 字段1注释 ] ,

           ......

          字段n 字段类型 [ 约束 ] [ comment 字段n注释 ]

)[ comment 表注释 ] ;

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。 目的：保证数据库中数据的正确性、有效性和完整性。

约束	描述	关键字
非空约束	限制该字段值不能为null	not  null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary  key
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign  key

查询当前数据库所有表：show tables; 查询表结构：desc 表名; 查询建表语句：show create table 表名;

添加字段：alter table 表名 add 字段名 类型(长度) [comment 注释] [约束]; 修改字段类型：alter table 表名 modify 字段名 新数据类型(长度); 修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型 (长度) [comment 注释] [约束]; 删除字段：alter table 表名 drop column 字段名; 修改表名： rename table 表名 to 新表名;

删除表：drop table [ if exists ] 表名;

数据库操作-DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

添加数据（INSERT） 修改数据（UPDATE） 删除数据（DELETE）

指定字段添加数据：insert into 表名 (字段名1, 字段名2) values (值1, 值2);

全部字段添加数据：insert into 表名 values (值1, 值2, ...);

批量添加数据（指定字段）：insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);

批量添加数据（全部字段）：insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);

注意：插入数据时，指定的字段顺序需要与值的顺序是一一对应的。

字符串和日期型数据应该包含在引号中。

插入的数据大小，应该在字段的规定范围内。

修改数据：update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where 条件 ] ;

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据：delete from 表名 [ where 条件 ];

注意：DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

         DELETE 语句不能删除某一个字段的值(如果要操作，可以使用UPDATE，将该字段的值置为NULL)。

数据库操作-DQL

DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库表中的记录。 关键字：SELECT

select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数

基本查询 条件查询（where） 分组查询（group by） 排序查询（order by） 分页查询（limit）

DQL-基本查询

查询多个字段：select 字段1, 字段2, 字段3 from 表名;

查询所有字段（通配符）：select * from 表名;

设置别名：select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;

去除重复记录：select distinct 字段列表 from 表名;

* 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。

DQL-条件查询

条件查询：select 字段列表 from 表名 where 条件列表 ;

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>  或 !=	不等于
between ... and ...	在某个范围之内(含最小、最大值)
in(...)	在in之后的列表中的值，多选一
like  占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
is null	是null

逻辑运算符	功能
and  或  &&	并且 (多个条件同时成立)
or  或  ||	或者 (多个条件任意一个成立)
not  或  !	非 , 不是

DQL-分组查询

介绍：将一列数据作为一个整体，进行纵向计算。

语法：select 聚合函数(字段列表) from 表名 ;

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

null值不参与所有聚合函数运算。 统计数量可以使用：count(*) count(字段) count(常量)，推荐使用count(*)。

DQL-分组查询

分组查询：select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];

where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

注意：

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。 执行顺序: where > 聚合函数 > having 。

DQL-排序查询

条件查询：select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 … ;

ASC：升序（默认值） DESC：降序

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

DQL-分页查询

分页查询：select 字段列表 from 表名 limit 起始索引, 查询记录数 ;

注意：

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

多表设计

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多(多对一)

在多的一方添加外键，关联另外一方的主键。

一对一

任意一方，添加外键，关联另外一方的主键。  

多对多

通过中间表来维护，中间表的两个外键，分别关联另外两张表的主键。

物理外键 

概念：使用 foreign key 定义外键关联另外一张表。

缺点： 影响增、删、改的效率（需要检查外键关系）。

仅用于单节点数据库，不适用与分布式、集群场景。

容易引发数据库的死锁问题，消耗性能。

逻辑外键

概念：在业务层逻辑中，解决外键关联。

通过逻辑外键，就可以很方便的解决上述问题。

多表查询

多表查询: 指从多张表中查询数据

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合(A集合 和 B集合)的所有组合情况

概述

连接查询

       内连接：相当于查询A、B交集部分数据

外连接 左外连接：查询左表所有数据(包括两张表交集部分数据)

右外连接：查询右表所有数据(包括两张表交集部分数据)

子查询

内连接

隐式内连接：select 字段列表 from 表1 , 表2 where 条件 ... ;

显式内连接：select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;

外连接

左外连接：select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ; 右外连接：select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;

子查询

介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

形式：select * from t1 where column1 = ( select column1 from t2 … );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

标量子查询：

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：= <> > >= < <=

列子查询：子查询返回的结果是一列（可以是多行） 常用的操作符：in 、not in等

行子查询：子查询返回的结果是一行（可以是多列）。 常用的操作符：= 、<> 、in 、not in

表子查询：子查询返回的结果是多行多列，常作为临时表 常用的操作符：in

事务

事务 是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作 要么同时成功，要么同时失败。

注意

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

开启事务：start transaction; / begin ;

提交事务：commit;

回滚事务：rollback;

四大特性 (ACID)

原子性

事务是不可分割的最小单元，要么全部成功，要么全部失败

一致性

事务完成时，必须使所有的数据都保持一致状态 隔离性

数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

持久性

事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

索引

索引（index）是帮助数据库 高效获取数据 的 数据结构

优点

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

缺点

索引会占用存储空间。 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是指默认的 B+Tree 结构组织的索引。

创建：create [ unique ] index 索引名 on 表名 (字段名,... ) ; 查询：show index from 表名; 删除：drop index 索引名 on 表名;

主键字段，在建表时，会自动创建主键索引。 添加唯一约束时，数据库实际上会添加唯一索引。