2023年数据库学习笔记

《2023年数据库学习笔记》由会员分享，可在线阅读，更多相关《2023年数据库学习笔记（48页珍藏版）》请在装配图网上搜索。

1、数据库学习笔记Sql基本操作1、基本操作CRUD（增删改查）2、将sql基本操作根据操作对象分类：库操作，表操作（字段），数据操作。2.1库操作：对数据库的增删改查；2.1.1新增数据库：基本语法：Create datebase 数据库名字 库选项库选项：用来约束数据库，分为两个选项l 字符集设定：charset（数据存储的编码格式）：常用的有GBK与UTF8l 校对集设定：collate（数据比较的规则）当创建数据库的sql语句执行之后，发生了什么？1、 在数据库系统中增长了相应的数据库信息；2、 会在保存数据的文献夹下：Date目录，创建一个相应数据库名字的文献夹；3、 每个数据库下都有一

2、个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

3、 数据库名字 库选项；Charset/character set 字符集；Collate 校对集；2.1.4删除数据库：Drop database 数据库名字；当删除数据库语句执行之后发生了什么？1、 在数据库内部看不到相应的数据库；2、 在相应的数据库存储的文献夹内，数据库名字相应的文献夹也被删除（级联删除：里面的数据表所有删除），数据库删除不可逆。2.2表操作表与字段密不可分2.2.1新增数据表语法：Create table if not exists 表名（字段名字 数据类型（x），（假如数据类型是字符型，需要将字符长度加上）字段名字 数据类型， -最后一行不需要逗号）表选项；If no

4、t exists ：假如表名不存在则创建，假如存在则不执行创建代码：检查功能；表选项：控制表的表现，字符集：charset/character set 具体字符集；-保证表中数据存储的字符集；校对集：collate 具体校对集；存储引擎：engine 具体存储引擎（innodb和myisam）；任何一个表的设计都必须指定数据库方案1：显示的指定表所属的数据库Create table if not exists 数据库名.表名（）；-将当前数据表创建到指定的数据库下。方案2：隐式的指定数据表所属数据库：先进入到某个数据库环境，然后这样创建的数据表自动归属到该数据库。进入数据库环境：use 数据库

5、名字；2.2.2查看数据表1、查看所有表 show tables；2、查看部分表：模糊匹配：Show tables likepattern；%:表达匹配多个字符；_:表达匹配单个字符；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 表名 a

6、ddcolumn 字段名 数据类型 列属性 位置；位置：字段名可以存放表中任意位置First：第一个位置；After：在哪个字段之后：after 字段名；默认的是在最后一个字段之后。2、修改字段：Alter table 表名 modify 字段名 数据类型【属性】【位置】；3、重命名字段Alter table 表名 change 旧字段 新字段名 数据类型【属性】【位置】；4、 删除字段：Alter table 表名 drop字段名；注意：假如表中已经存在数据，那么删除字段会清空该字段的所有数据（不可逆）。2.2.4删除数据表Drop table 表名1，表名2，表名3；可以一簇删除多张表。2

7、.3数据操作：2.3.1新增数据：两种方案：方案1、给全表字段插入数据，不需要指定字段列表；规定数据的值出现的顺序必须与表中设计的字段出现的顺序一致，凡是非数值数据都需要使用引号（建议使用单引号）包裹。Insert into 表名 values（值列表）【，（值列表）】；-可以一次插入多条数据。方案2、给部分字段插入数据，需要选定字段列表，字段列表出现的顺序与字段的顺序无关，但是值列表的顺序必须与选定的字段的顺序一致Insert into 表名（字段列表）values （值列表）【，（值列表）】；2.3.2查看数据：1、查看所有数据Select * from 表名；2、查看指定字段，指定条件的

8、数据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开始。显示宽度没有特别

9、的含义，只是默认告诉用户可以显示的形式而已，事实上用户可以控制的，这种控制不会改变数据自身的大小。显示宽度的意义：5、列属性列属性：真正约束字段的是数据类型，但是数据类型的约束很单一，需要有一些额外的约束，来更加保证数据的合法性。列属性有很多：NULL/NOTNULL，default，Primary key，unique key，auto_increment，comment5.1、空属性两个值：NULL（默认的）和NOT NULL虽然默认为空，但实际开发中，尽也许保证所有数据都不为空，由于空数据没故意义，也无法参与运算。5.2、列描述列描述：comment，描述，没有实际含义：是专门用来描述字

10、段的，会根据表创建语句保存，用来给程序员（数据库管理员）来进行了解的。5.3、默认值默认值：某一种数据会经常性的出现某一个具体的值，可以在一开始便指定好，在需要真实数据的时候，用户可以选择性的使用默认值。6、 字段属性6.1、主键：主键：primary key，重要的键，一张表只能有一个主键，用来唯一的约束该字段里的数据，不能反复。6.1.1增长主键增长主键的三种方式：1、 创建表时直接在字段之后跟primary key关键字（主键自身不允许为空）优点：直接；缺陷：只能使用一个字段作为主键。2、 创建表的时候，在所有字段之后使用primary key（主键字段列表）来创建主键（假如有多个字段为

11、主键，可以是复合主键）3、 当表已经创建好之后，再次额外追加主键，可以通过修改表字段属性，也可以直接追加。Alter table 表名 add primary key（字段列表）前提：表中字段相应的数据自身是独立的（不反复）。6.1.2主键约束主键相应的字段中数据不允许反复，一旦反复，数据操作失败。6.1.3更新主键&删除主键没有办法更新主键，主键必须先删除，才干增长。Alter table 表名 drop primary key； 6.1.4、主键分类6.2、自动增长自增长：当相应字段不给值或者给默认值或者给NULL时，自增长被系统触发，系统会从当前字段中已有的最大值进行+1操作，得到一个新

12、的不同字段。自增长通常与主键配合使用；自增长的特点：auto_increment6.2.1新增自增长1、 任何一个字段做自增长，前提自身必须是一个索引（key一栏有值）；2、 字段必须是数字，并且是整型；3、 一张表最多只能有一个自增长。6.2.2自增长使用当相应字段不给值或者给默认值或者给NULL时，自增长被系统触发，系统会从当前字段中已有的最大值进行+1操作，得到一个新的不同字段。假如自增长相应字段输入了值，那么自增长将失效，但是下一次还是能自动的自增长（从最大值+1）如何拟定下一次自增长是什么？可以通过查看创建表语句6.2.3修改自增长自增长假如涉及到字段改变，必须先删除自增长后增长（由

13、于一张表只能有一个自增长）。修改当前自增长已经存在的值，修改只能比当前已有的自增长的最大值大，不能小，（小不生效）。Alter table 表名 auto_increment =？；6.2.4删除字增长自增长是一个属性，可以通过modify来进行修改（保证字段里没有auto_increment即可）Alter table 表名 modify 字段 类型。6.3、唯一键一张表往往有许多字段需要具有唯一性，数据不能反复，但是一张表中只能有一个主键，唯一键（unique key）就可以解决多个字段需要唯一性约束的问题。唯一键本质与主键差不多，唯一键默认的允许自动为空，并且可以对个为空（空字段不参与唯

14、一性比较）。6.3.1增长唯一键基本与主键差不多1、在创建表时，字段之后直接跟unique/unique key2、在所有字段之后增长unique key （字段列表）；-复合唯一。4、 在创建表之后增长唯一键；6.3.2唯一键约束唯一键与主键本质相同，唯一的区别就是唯一键默认允许为空，并且是多空；假如唯一键也不允许为空，与主键的约束作用是一致的。6.3.3更新唯一键&删除唯一键更新唯一键：先删除后新增（唯一键可以有多个，可以不删除）1、删除唯一键Alter table 表名 drop index 索引名字；唯一键默认使用字段名作为索引名字。6.4、索引几乎所有的索引都建立在字段之上索引：系统

15、根据某种算法，将已有的数据（未来也许新增的数据）单独建立一个文献，文献可以实现快速的匹配数据，并且能快速的找到相应表中的记录。索引的意义：1、 提高查询数据的效率；2、 约束数据的有效性（唯一性等）；增长索引的前提条件：索引自身会产生索引文献（有时候也许比数据文献还大），会非常花费磁盘空间，Mysql中提供多种索引：1、 主键索引：primary key；2、 唯一索引：unique key；3、 全文索引：fulltext index；4、 普通索引：index全文索引：针对文章内部的关键字进行索引，全文索引最大的一个问题在于如何拟定关键字。英文很容易：英文单词与单词之间有空格；中文很难：没

16、有空格，并且中文可以各种随意组合（分词：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：

17、去重，查出来的结果，将反复的给去除（所有字段都相同）。2、 字段别名：字段别名：当数据进行查询出来的时候，有时候名字并不一定就满足需求（多表查询的时候，会有同名字段）需要对字段名进行重命名：别名。语法：字段名 【as】 别名；3、 数据源数据源：数据的来源，关系型数据库的来源都是数据表，本质上只要保证数据表类似二维表，最终都可以作为数据源。数据源分为多种：单表数据源，多表数据源，查询语句；1、 单表数据源：select * from 表名；2、 多表数据名：select * from 表名1，表名2，；（笛卡尔积没什么卵用）3、 子查询：数据来源是一条查询语句（查询语句的结果是二维表）Sele

18、ct * 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提供一系列记录函

19、数：Count（）；记录分组后的记录数，每一组有多少记录；Max（）；记录每组中最大值；Min（）；记录每组中最小值；Avg（）；记录平均值；Sum（）；记录和；Count（）函数，里面可以使用两种参数，*代表记录记录，字段名代表记录相应的字段（NULL 不记录）；分组会自动排序，根据分组字段自动排序，默认升序。Group by 字段 【asc|desc（降序）】；-对分组结果合并之后的整个结果进行排序。多字段分组：先根据一个字段进行分组，然后对分组后的结果再次按照其他字段进行分组。5、 having子句having子句：与where子句同样，进行条件判断的。Where是对磁盘数据进行判断的，

20、进入内存之后，会进行分组操作，分组结果就需要having来解决。Having能做where能做的几乎所有事情，但where却不能做having能做的很多事情。5.1、分组记录的结果或者说记录函数都只有having可以使用。5.2、having可以使用字段别名但where不能，where是从磁盘取数据，所以名字只也许是字段名，别名是在字段进入到内存后才产生的。6、 Order by子句：Order by：排序，根据某个字段进行升序后者降序排序，依赖校对集。基本语法：Order by 字段名 【asc|desc】；-asc是升序（默认的），desc是降序。排序可以进行多字段排序，先根据某个字段进行

21、排序，然后排序好的内部再按照某个数据进行再次排序。7、 limit子句limit子句是一种限制结果的语句：限制数量。Limit有两种使用方式：7.1、只用来限制长度（数据量），limit数据量；7.2、限制起始位置，限制数量；limit起始位置，长度；8、 数据库事务的基本特性数据库事务概念什么是数据库事务？事务（transaction）是由一系列操作序列构成的程序执行单元，这些操作要么都做，要么都不做，是一个不可分割的工作单位。数据库事务的四个基本性质（ACID）1. 原子性（Atomicity）事务的原子性是指事务中包含的所有操作要么全做，要么全不做（all or none）。2. 一致性

22、（Consistency）在事务开始以前，数据库处在一致性的状态，事务结束后，数据库也必须处在一致性状态。拿银行转账来说，一致性规定事务的执行不应改变A、B 两个账户的金额总和。假如没有这种一致性规定，转账过程中就会发生钱无中生有，或者不翼而飞的现象。事务应当把数据库从一个一致性状态转换到此外一个一致性状态。3. 隔离性（Isolation）事务隔离性规定系统必须保证事务不受其他并发执行的事务的影响，也即要达成这样一种效果：对于任何一对事务T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始之前已经结束，要么在 T1 完毕之后才开始执行。这样，每个事务都感觉不到系统中有其他事务在并发地

23、执行。4. 持久性（Durability）一个事务一旦成功完毕，它对数据库的改变必须是永久的，即便是在系统碰到故障的情况下也不会丢失。数据的重要性决定了事务持久性的重要性。9、 关系：将实体与实体的关系，反映到最终数据表的设计上来：将关系分为三种：一对一，一对多（多对一）和多对多。所有的关系指的都是表与表之间的关系。9.1一对一：一张表的一条记录只能与另一张表的一条记录一一相应，反之亦然。9.2一对多 9.3多对多：连接查询；join，使用方式:左表join右表左表：在join关键字左边的表右表：在join关键字右边的表10.1交叉连接（理解为取并集）交叉连接（全连接，理解为取并集）：cros

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

25、表.字段；on表达连接条件，条件字段就是代表相同的业务含义。字段别名以及表别名的使用：在查询数据的时候，不同表有同名字段，这个时候需要加上表名才干区分，而表名太长，通常可以使用别名。内连接可以没有连接条件：没有on之后的内容，这个时候系统会保存所有结果（笛卡尔积），相称于交叉连接。内连接还可以使用where代替on关键字（where没有on效率高）10.3外连接外连接：outer join，以某张表为主，取出里面的所有记录，然后每条与此外一张表进行连接，不管能不能匹配上条件，最终都会保存，能匹配，正保证留；不能匹配，其他表的字段都置为NULL。外连接分为2种：左连接（left join）与右连

26、接（right join）基本语法：左表 left/right join 右表 on 左表.字段 = 右表.字段;虽然左连接和右连接有主表的差异，但是显示的结果：左表的数据在左边，右表的数据在右边，左右表连接可以互转。10.4自然连接自然连接；natural join ，就是自动匹配连接条件，系统以字段名字作为匹配模式（同名字段就可作为条件，多个同名字段都作为条件）自然连接分为：自然内连接和自然外连接。自然内连接：左表natural join 右表；自然外连接：左表 natural left/right join 右表；11、外键外键：foreign key，外面的键（键不在自己表中）；假如一

27、张表中有一个字段（非主键）指向此外一张表的主键，那么将该字段称之为外键。11.1增长外键外键可以在创建表的时候或者创建表之后增长（但是要考虑数据问题）1、创建表的时候增长外键：在所有的表字段之后，使用foreign key（外键字段）references 外部表（主键字段）2、 在新增表之后增长外键 修改表结构Alter table 表名 add 【constraint 外键名字】foreign key（外键字段）references 父表（主键字段）12、联合查询联合查询：将多次查询（多条select语句），在记录上进行拼接（字段不会增长）基本语法：多条select语句构成：每一条selec

28、t语句获取的字段必须严格一致（但是字段类型无关）Select 语句1Union【union选项】Select 语句2、Union选项与select选项同样有2个；All:保存所有（不管反复）Distinct：去重（整个反复）：默认的。联合查询的意义:1、 查询统一张表，但是需求不同，如查询学生信息，男生身高升序，女生身高降序。2、 多表查询，多张表的结构是完全同样的，保存的数据结构也是同样的。order by 的使用在联合查询中，order by 不能直接使用，需要对查询语句使用括号才行；若要order by 生效，必须搭配limit，limit使用限定的最大数即可。13、子查询子查询：sub

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

30、是在where之后表子查询：子查询得到的结果是多行多列（出现的位置是在from之后）。13.1标量子查询14、视图：视图：view是一种有结构（有行有列）但是没结果（结构中不存放真实数据）的虚拟表，虚拟表的结构来源不是自己定义，而是从相应的基表中产生（视图的数据来源）。14.1创建视图基本语法：Create view 视图名字 as select 语句；-select语句可以是普通查询，可以是连接查询，可以是联合查询，可以是子查询。14.2查看视图查看视图:查看视图的结构视图是一张虚拟表，表的所有查看方式都合用于视图，show tables【like】；desc 视图名字；show creat

31、e table 视图名字；14.3使用视图使用视图重要是为了查询，将视图当做表同样查询即可。视图的执行：其实本质就是执行封装的select语句。14.4修改视图视图自身不可修改，但是视图的来源是可以修改的；修改视图：修改视图自身的来源语句（select语句）Alter view 视图名字 as 新的select语句；14.5删除视图Drop view 视图名字；14.6视图意义15、数据的备份与还原备份：将当前已有的数据或者记录保存；还原：将已保存的数据恢复到相应表中； 数据备份还原的方式有很多种，数据表备份、单表数据备份、sql备份、增量备份15.1数据表备份不需要通过sql来备份：直接进入

32、到数据库文献夹复制相应的表结构以及数据文献，以后还原的时候直接将备份的内容放进去即可。数据表备份有前提条件，根据不同的存储引擎有不同的区别；存储引擎：mysql进行数据存储的方式：重要有两种：innodb和myisam（免费）16、事务（存储引擎必须是innodb）16.1事务安全：事务：一系列要发生的连续的操作。事务安全：一种保护连续操作同时执行的一种机制。事务安全的意义：保证数据操作的完整性。16.2事务操作：事务操作分为两种：自动事务（默认的）、手动事务手动事务：操作流程1、 启动事务：告诉系统以下所有的操作（写）不要直接写入到数据表，先存放到事务日记；Start transaction

33、；2、 进行事务操作：3、 关闭事务：选择性的将日记文献中操作的结果保存到数据表中（同步），或者说直接清空事务日记（本来操作所有清空）。1、 提交事务：同步数据表（操作成功）：commit；2、 回滚事务：直接清空日记表（操作失败）：rollback；16.2事务原理：事务操作原理：事务启动之后，所有操作都会临时保存在事务日记上，事务日记只有在得到commit命令之后才会同步到数据表，其他任何情况都会清空（rollback、断电、断开连接）。16.3回滚点：回滚点：在某个成功的操作完毕之后，后续的操作有也许成功也有也许失败，但不管成功或者失败，前面的操作都已经成功，可以在当前成功的位置设立一个点，供后续失败操作返回该位置，而不是返回所有操作，这个点称为回滚点。设立回滚点语法：savepoint 回滚点名字