2023年数据库学习笔记.docx

数据库学习笔记
Sql基本操作
1、基本操作CRUD（增删改查）
2、将sql基本操作根据操作对象分类：库操作，表操作（字段），数据操作。
2.1库操作：
对数据库的增删改查；
2.1.1新增数据库：
基本语法：
Create datebase 数据库名字 [库选项]
库选项：用来约束数据库，分为两个选项
l 字符集设定：charset（数据存储的编码格式）：常用的有GBK与UTF8
l 校对集设定：collate（数据比较的规则）

当创建数据库的sql语句执行之后，发生了什么？
1、 在数据库系统中增长了相应的数据库信息；
2、 会在保存数据的文献夹下：Date目录，创建一个相应数据库名字的文献夹；
3、 每个数据库下都有一个opt文献，保存了库选项。

2.1.2：查看数据库：
1、查看所有数据库
Show databases；
2、查看指定部分的数据库：模糊查询
Show databases like ‘pattern’；---pattern是匹配模式
%:表达匹配多个字符；
_:表达匹配单个字符；

3、查看数据库创建语句：
Show create database 数据库名字；
eg: SHOW CREATE DATABASE imooc;

数据库在执行sql语句之前会优化sql，系统保存的结果是优化后的结果。

2.1.3更新数据库：
数据库名字不可修改；
数据库的修改仅限库选项：字符集和校对集（校对集依赖字符集）。
Alter database 数据库名字 [库选项]；
Charset/character set 字符集；
Collate 校对集；

2.1.4删除数据库：
Drop database 数据库名字；
当删除数据库语句执行之后发生了什么？
1、 在数据库内部看不到相应的数据库；
2、 在相应的数据库存储的文献夹内，数据库名字相应的文献夹也被删除（级联删除：里面的数据表所有删除），数据库删除不可逆。

2.2表操作
表与字段密不可分
2.2.1新增数据表
语法：
Create table [if not exists] 表名（
字段名字 数据类型（x），（假如数据类型是字符型，需要将字符长度加上）
字段名字 数据类型，
······· -----最后一行不需要逗号
）[表选项]；
If not exists ：假如表名不存在则创建，假如存在则不执行创建代码：检查功能；
表选项：控制表的表现，
字符集：charset/character set 具体字符集；--保证表中数据存储的字符集；
校对集：collate 具体校对集；
存储引擎：engine 具体存储引擎（innodb和myisam）；
任何一个表的设计都必须指定数据库
方案1：显示的指定表所属的数据库
Create table [if not exists] 数据库名.表名（）；--将当前数据表创建到指定的数据库下。
方案2：隐式的指定数据表所属数据库：先进入到某个数据库环境，然后这样创建的数据表自动归属到该数据库。
进入数据库环境：use 数据库名字；

2.2.2查看数据表
1、查看所有表 show tables；
2、查看部分表：模糊匹配：
Show tables like‘pattern’；
%:表达匹配多个字符；
_:表达匹配单个字符；
3、 查看表的创建语句；
Show create table 表名；

4.查看表结构：查看表中的字段信息
Desc/describe/show columns（列）from 表名；

2.2.3修改数据表
2.2.3.1修改表自身
表自身可以修改：表名和表选项；
1、修改表名：
Rename table 老表名 to 新表名；
2、修改表选项：
A

2.2.3.2修改字段（增、删、改、查）
1、新增字段：
Alter table 表名 add[column] 字段名 数据类型 [列属性] [位置]；
位置：字段名可以存放表中任意位置
First：第一个位置；
After：在哪个字段之后：after 字段名；默认的是在最后一个字段之后。

2、修改字段：
Alter table 表名 modify 字段名 数据类型【属性】【位置】；

3、重命名字段
Alter table 表名 change 旧字段 新字段名 数据类型【属性】【位置】；

4、 删除字段：
Alter table 表名 drop字段名；

注意：假如表中已经存在数据，那么删除字段会清空该字段的所有数据（不可逆）。

2.2.4删除数据表
Drop table 表名1，表名2，表名3···；可以一簇删除多张表。

2.3数据操作：
2.3.1新增数据：
两种方案：
方案1、给全表字段插入数据，不需要指定字段列表；规定数据的值出现的顺序必须与表中设计的字段出现的顺序一致，凡是非数值数据都需要使用引号（建议使用单引号）包裹。
Insert into 表名 values（值列表）【，（值列表）】；--可以一次插入多条数据。

方案2、给部分字段插入数据，需要选定字段列表，字段列表出现的顺序与字段的顺序无关，但是值列表的顺序必须与选定的字段的顺序一致
Insert into 表名（字段列表）values （值列表）【，（值列表）】；

2.3.2查看数据：
1、查看所有数据
Select * from 表名；

2、查看指定字段，指定条件的数据
Select 字段列表 from 表名 【where条件】；

2.3.3更新数据：
Update 表名 set 字段 = 值【where条件】；---建议都有where：要不是更新所有

2.3.4删除数据：
Delete from 表名【where条件】；

3、中文数据问题
中文数据问题本质是字符集问题。

4、数据类型（列类型）
Sql中将数据类型分为三大类：数值类型，字符串类型和时间类型

4.1、整数型（tinyint，int）

常用Tinyint和Int。

SQL中的数据类型所有都是默认有符号，分正负；
有的时候需要使用无符号的数据，需要给数据类型限定：int unsigned，----无符号从0开始。

显示宽度没有特别的含义，只是默认告诉用户可以显示的形式而已，事实上用户可以控制的，这种控制不会改变数据自身的大小。
显示宽度的意义：

5、列属性
列属性：真正约束字段的是数据类型，但是数据类型的约束很单一，需要有一些额外的约束，来更加保证数据的合法性。

列属性有很多：NULL/NOTNULL，default，Primary key，unique key，auto_increment，comment
5.1、空属性
两个值：NULL（默认的）和NOT NULL
虽然默认为空，但实际开发中，尽也许保证所有数据都不为空，由于空数据没故意义，也无法参与运算。

5.2、列描述
列描述：comment，描述，没有实际含义：是专门用来描述字段的，会根据表创建语句保存，用来给程序员（数据库管理员）来进行了解的。
5.3、默认值
默认值：某一种数据会经常性的出现某一个具体的值，可以在一开始便指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

6、 字段属性
6.1、主键：
主键：primary key，重要的键，一张表只能有一个主键，用来唯一的约束该字段里的数据，不能反复。
6.1.1增长主键
增长主键的三种方式：
1、 创建表时直接在字段之后跟primary key关键字（主键自身不允许为空）

优点：直接；缺陷：只能使用一个字段作为主键。
2、 创建表的时候，在所有字段之后使用primary key（主键字段列表）来创建主键（假如有多个字段为主键，可以是复合主键）

3、 当表已经创建好之后，再次额外追加主键，可以通过修改表字段属性，也可以直接追加。
Alter table 表名 add primary key（字段列表）

前提：表中字段相应的数据自身是独立的（不反复）。
6.1.2主键约束
主键相应的字段中数据不允许反复，一旦反复，数据操作失败。

6.1.3更新主键&删除主键
没有办法更新主键，主键必须先删除，才干增长。
Alter table 表名 drop primary key；

6.1.4、主键分类

6.2、自动增长
自增长：当相应字段不给值或者给默认值或者给NULL时，自增长被系统触发，系统会从当前字段中已有的最大值进行+1操作，得到一个新的不同字段。
自增长通常与主键配合使用；
自增长的特点：auto_increment
6.2.1新增自增长
1、 任何一个字段做自增长，前提自身必须是一个索引（key一栏有值）；
2、 字段必须是数字，并且是整型；
3、 一张表最多只能有一个自增长。

6.2.2自增长使用
当相应字段不给值或者给默认值或者给NULL时，自增长被系统触发，系统会从当前字段中已有的最大值进行+1操作，得到一个新的不同字段。

假如自增长相应字段输入了值，那么自增长将失效，但是下一次还是能自动的自增长（从最大值+1）

如何拟定下一次自增长是什么？可以通过查看创建表语句
6.2.3修改自增长
自增长假如涉及到字段改变，必须先删除自增长后增长（由于一张表只能有一个自增长）。
修改当前自增长已经存在的值，修改只能比当前已有的自增长的最大值大，不能小，（小不生效）。
Alter table 表名 auto_increment =？；

6.2.4删除字增长
自增长是一个属性，可以通过modify来进行修改（保证字段里没有auto_increment即可）
Alter table 表名 modify 字段 类型。
6.3、唯一键
一张表往往有许多字段需要具有唯一性，数据不能反复，但是一张表中只能有一个主键，唯一键（unique key）就可以解决多个字段需要唯一性约束的问题。
唯一键本质与主键差不多，唯一键默认的允许自动为空，并且可以对个为空（空字段不参与唯一性比较）。
6.3.1增长唯一键
基本与主键差不多
1、在创建表时，字段之后直接跟unique/unique key

2、在所有字段之后增长unique key （字段列表）；--复合唯一。

4、 在创建表之后增长唯一键；

6.3.2唯一键约束
唯一键与主键本质相同，唯一的区别就是唯一键默认允许为空，并且是多空；
假如唯一键也不允许为空，与主键的约束作用是一致的。

6.3.3更新唯一键&删除唯一键
更新唯一键：先删除后新增（唯一键可以有多个，可以不删除）
1、删除唯一键
Alter table 表名 drop index 索引名字；唯一键默认使用字段名作为索引名字。

6.4、索引
几乎所有的索引都建立在字段之上
索引：系统根据某种算法，将已有的数据（未来也许新增的数据）单独建立一个文献，文献可以实现快速的匹配数据，并且能快速的找到相应表中的记录。
索引的意义：
1、 提高查询数据的效率；
2、 约束数据的有效性（唯一性等）；
增长索引的前提条件：索引自身会产生索引文献（有时候也许比数据文献还大），会非常花费磁盘空间，
Mysql中提供多种索引：
1、 主键索引：primary key；
2、 唯一索引：unique key；
3、 全文索引：fulltext index；
4、 普通索引：index

全文索引：针对文章内部的关键字进行索引，全文索引最大的一个问题在于如何拟定关键字。
英文很容易：英文单词与单词之间有空格；
中文很难：没有空格，并且中文可以各种随意组合（分词：sphinx）。

7、 数据的高级操作
数据操作：增删改查
7.1新增数据
基本语法：
Insert into 表名 【（字段列表） values （值列表）】；
7.2查询数据
基本语法：
Select 字段列表/* from 表名 【where 条件】
完整语法：
Select【select 选项】字段列表【字段别名】/* from 数据源 【where 条件子句】【group by 子句】【having 子句】【order by 子句】【limit 子句】；

1、 select 选项：
select对查出来的结果的解决方式。
All：默认的，保存所有结果；
Discinct：去重，查出来的结果，将反复的给去除（所有字段都相同）。

2、 字段别名：
字段别名：当数据进行查询出来的时候，有时候名字并不一定就满足需求（多表查询的时候，会有同名字段）需要对字段名进行重命名：别名。
语法：
字段名 【as】 别名；

3、 数据源
数据源：数据的来源，关系型数据库的来源都是数据表，本质上只要保证数据表类似二维表，最终都可以作为数据源。
数据源分为多种：单表数据源，多表数据源，查询语句；
1、 单表数据源：select * from 表名；

2、 多表数据名：select * from 表名1，表名2，····；（笛卡尔积没什么卵用）
3、 子查询：数据来源是一条查询语句（查询语句的结果是二维表）
Select * from （select 语句） as 表名；

4、where子句
where子句：用来判断数据，筛选数据。
Where子句返回结果，0或者1,0代表false，1代表true。
判断条件：

条件查询1：规定找出学生id为1或者3或者5的学生；

条件查询2： 查询区间落在180,190身高之间的学生;

Between自身是闭区间，between左边的值必须小于或者等于右边的值。

4、 group by子句
group by ：分组的意思，根据某个字段进行分组（相同一组，不同的分到不同组）
基本语法：
Group by 字段名；
分组的意义在于记录数据（按组记录，按分组字段进行数据记录）
SQL提供一系列记录函数：
Count（）；记录分组后的记录数，每一组有多少记录；
Max（）；记录每组中最大值；
Min（）；记录每组中最小值；
Avg（）；记录平均值；
Sum（）；记录和；

Count（）函数，里面可以使用两种参数，*代表记录记录，字段名代表记录相应的字段（NULL 不记录）；
分组会自动排序，根据分组字段自动排序，默认升序。
Group by 字段 【asc|desc（降序）】；---对分组结果合并之后的整个结果进行排序。
多字段分组：先根据一个字段进行分组，然后对分组后的结果再次按照其他字段进行分组。

5、 having子句
having子句：与where子句同样，进行条件判断的。
Where是对磁盘数据进行判断的，进入内存之后，会进行分组操作，分组结果就需要having来解决。
Having能做where能做的几乎所有事情，但where却不能做having能做的很多事情。
5.1、分组记录的结果或者说记录函数都只有having可以使用。

5.2、having可以使用字段别名但where不能，where是从磁盘取数据，所以名字只也许是字段名，别名是在字段进入到内存后才产生的。

6、 Order by子句：
Order by：排序，根据某个字段进行升序后者降序排序，依赖校对集。
基本语法：
Order by 字段名 【asc|desc】；--asc是升序（默认的），desc是降序。

排序可以进行多字段排序，先根据某个字段进行排序，然后排序好的内部再按照某个数据进行再次排序。

7、 limit子句
limit子句是一种限制结果的语句：限制数量。
Limit有两种使用方式：
7.1、只用来限制长度（数据量），limit数据量；

7.2、限制起始位置，限制数量；limit起始位置，长度；

8、 数据库事务的基本特性
数据库事务概念
什么是数据库事务？
事务（transaction）是由一系列操作序列构成的程序执行单元，这些操作要么都做，要么都不做，是一个不可分割的工作单位。
数据库事务的四个基本性质（ACID）
1. 原子性（Atomicity）
事务的原子性是指事务中包含的所有操作要么全做，要么全不做（all or none）。
2. 一致性（Consistency）
在事务开始以前，数据库处在一致性的状态，事务结束后，数据库也必须处在一致性状态。
拿银行转账来说，一致性规定事务的执行不应改变A、B 两个账户的金额总和。假如没有这种一致性规定，转账过程中就会发生钱无中生有，或者不翼而飞的现象。事务应当把数据库从一个一致性状态转换到此外一个一致性状态。
3. 隔离性（Isolation）
事务隔离性规定系统必须保证事务不受其他并发执行的事务的影响，也即要达成这样一种效果：对于任何一对事务T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始之前已经结束，要么在 T1 完毕之后才开始执行。这样，每个事务都感觉不到系统中有其他事务在并发地执行。

4. 持久性（Durability）
一个事务一旦成功完毕，它对数据库的改变必须是永久的，即便是在系统碰到故障的情况下也不会丢失。数据的重要性决定了事务持久性的重要性。
9、 关系：
将实体与实体的关系，反映到最终数据表的设计上来：将关系分为三种：一对一，一对多（多对一）和多对多。所有的关系指的都是表与表之间的关系。
9.1一对一：
一张表的一条记录只能与另一张表的一条记录一一相应，反之亦然。

9.2一对多

9.3多对多：

连接查询；join，使用方式:左表join右表
左表：在join关键字左边的表
右表：在join关键字右边的表
10.1交叉连接（理解为取并集）
交叉连接（全连接，理解为取并集）：cross join，从一张表中循环取出每一条记录，每条记录都去另一张表进行匹配，匹配一定保存（没有条件匹配），而连接自身字段就会增长（保存），最终形成的结果叫做笛卡尔积。
基本语法：左表 cross join 右表；=======from 左表，右表；

注意
1、是先进行连接，后进行查询；
2、笛卡尔积没故意义，应当尽量避免使用；
3、交叉连接的意义，保证连接这种结构的完整性。
10.2内连接（取交集）
内连接：【inner】join ，从左表中取出每一条记录，去右表中与所有的记录进行匹配，匹配必须是左表中某个条件与右表中相同最终才会保存结果，否则不保存。
基本语法：
左表 【inner】join 右表 on 左表.字段 = 右表.字段；on表达连接条件，条件字段就是代表相同的业务含义。

字段别名以及表别名的使用：在查询数据的时候，不同表有同名字段，这个时候需要加上表名才干区分，而表名太长，通常可以使用别名。
内连接可以没有连接条件：没有on之后的内容，这个时候系统会保存所有结果（笛卡尔积），相称于交叉连接。

内连接还可以使用where代替on关键字（where没有on效率高）
10.3外连接
外连接：outer join，以某张表为主，取出里面的所有记录，然后每条与此外一张表进行连接，不管能不能匹配上条件，最终都会保存，能匹配，正保证留；不能匹配，其他表的字段都置为NULL。
外连接分为2种：左连接（left join）与右连接（right join）
基本语法：
左表 left/right join 右表 on 左表.字段 = 右表.字段;
虽然左连接和右连接有主表的差异，但是显示的结果：左表的数据在左边，右表的数据在右边，左右表连接可以互转。
10.4自然连接
自然连接；natural join ，就是自动匹配连接条件，系统以字段名字作为匹配模式（同名字段就可作为条件，多个同名字段都作为条件）
自然连接分为：自然内连接和自然外连接。
自然内连接：左表natural join 右表；
自然外连接：左表 natural left/right join 右表；

11、外键
外键：foreign key，外面的键（键不在自己表中）；假如一张表中有一个字段（非主键）指向此外一张表的主键，那么将该字段称之为外键。
11.1增长外键
外键可以在创建表的时候或者创建表之后增长（但是要考虑数据问题）
1、创建表的时候增长外键：在所有的表字段之后，使用foreign key（外键字段）references 外部表（主键字段）

2、 在新增表之后增长外键 修改表结构
Alter table 表名 add 【constraint 外键名字】foreign key（外键字段）references 父表（主键字段）

12、联合查询
联合查询：将多次查询（多条select语句），在记录上进行拼接（字段不会增长）
基本语法：
多条select语句构成：每一条select语句获取的字段必须严格一致（但是字段类型无关）
Select 语句1
Union【union选项】
Select 语句2、、、、
Union选项与select选项同样有2个；
All:保存所有（不管反复）
Distinct：去重（整个反复）：默认的。

联合查询的意义:
1、 查询统一张表，但是需求不同，如查询学生信息，男生身高升序，女生身高降序。
2、 多表查询，多张表的结构是完全同样的，保存的数据结构也是同样的。
order by 的使用
在联合查询中，order by 不能直接使用，需要对查询语句使用括号才行；
若要order by 生效，必须搭配limit，limit使用限定的最大数即可。

13、子查询
子查询：sub query ，查询是在某个查询结果之上进行的（一条select语句中包含此外一条select语句）。
子查询的分类：按位置分类；按结果分类。
按位置分类：子查询（select语句）在外部查询（select语句）中出现的位置。
From子查询：子查询跟在from之后；
Where子查询：子查询出现在where条件中；
Exists子查询：子查询出现在exists里面。
按结果分类：根据子查询得到的数据进行分类（理论上讲任何一个子查询得到的结果都可以理解为二维表）
标量子查询：子查询得到的结果是一行一列；
列子查询：子查询得到的结果是一列多行；
行子查询：子查询得到的结果是多行一列（多行多列）
上面几个出现的位置都是在where之后
表子查询：子查询得到的结果是多行多列（出现的位置是在from之后）。
13.1标量子查询

14、视图：
视图：view是一种有结构（有行有列）但是没结果（结构中不存放真实数据）的虚拟表，虚拟表的结构来源不是自己定义，而是从相应的基表中产生（视图的数据来源）。
14.1创建视图
基本语法：
Create view 视图名字 as select 语句；---select语句可以是普通查询，可以是连接查询，可以是联合查询，可以是子查询。

14.2查看视图
查看视图:查看视图的结构
视图是一张虚拟表，表的所有查看方式都合用于视图，
show tables【like】；
desc 视图名字；
show create table 视图名字；
14.3使用视图
使用视图重要是为了查询，将视图当做表同样查询即可。
视图的执行：其实本质就是执行封装的select语句。
14.4修改视图
视图自身不可修改，但是视图的来源是可以修改的；
修改视图：修改视图自身的来源语句（select语句）
Alter view 视图名字 as 新的select语句；

14.5删除视图
Drop view 视图名字；
14.6视图意义

15、数据的备份与还原
备份：将当前已有的数据或者记录保存；
还原：将已保存的数据恢复到相应表中；
数据备份还原的方式有很多种，数据表备份、单表数据备份、sql备份、增量备份
15.1数据表备份
不需要通过sql来备份：直接进入到数据库文献夹复制相应的表结构以及数据文献，以后还原的时候直接将备份的内容放进去即可。
数据表备份有前提条件，根据不同的存储引擎有不同的区别；
存储引擎：mysql进行数据存储的方式：重要有两种：innodb和myisam（免费）

16、事务（存储引擎必须是innodb）
16.1事务安全：
事务：一系列要发生的连续的操作。
事务安全：一种保护连续操作同时执行的一种机制。
事务安全的意义：保证数据操作的完整性。
16.2事务操作：
事务操作分为两种：自动事务（默认的）、手动事务
手动事务：操作流程
1、 启动事务：告诉系统以下所有的操作（写）不要直接写入到数据表，先存放到事务日记；
Start transaction；

2、 进行事务操作：

3、 关闭事务：选择性的将日记文献中操作的结果保存到数据表中（同步），或者说直接清空事务日记（本来操作所有清空）。
1、 提交事务：同步数据表（操作成功）：commit；
2、 回滚事务：直接清空日记表（操作失败）：rollback；
16.2事务原理：
事务操作原理：事务启动之后，所有操作都会临时保存在事务日记上，事务日记只有在得到commit命令之后才会同步到数据表，其他任何情况都会清空（rollback、断电、断开连接）。

16.3回滚点：
回滚点：在某个成功的操作完毕之后，后续的操作有也许成功也有也许失败，但不管成功或者失败，前面的操作都已经成功，可以在当前成功的位置设立一个点，供后续失败操作返回该位置，而不是返回所有操作，这个点称为回滚点。
设立回滚点语法：savepoint 回滚点名字