目录
一.数据库的操作
1.显示当前的数据库:SHOW DATABASES;
2.创建数据库: 1.CREATE DATABASE 数据库名;
2.CREATE DATABASE IF NOT EXISTS 数据库名;
3.CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET 数据库名;
3.使用数据库: use 数据库名;
4.删除数据库: DROP DATABASE [IF EXISTS] 数据库名;
二.常用数据类型
1.数值类型
![](https://img-blog.csdnimg.cn/e3f22cb2ae8f488897c6e324d86a4c11.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/ddaa96c1314545c5b08dbafc18f67057.png)
2.字符串类型
![](https://img-blog.csdnimg.cn/0b47f96ca3c14e9d8f647b21d0cba2e6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
3.日期类型
![](https://img-blog.csdnimg.cn/d86f10642ccd45a180cc87efb1e72c45.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
三.表的操作
操作数据库中的表时,需要先使用该数据库
!!!
1.创建表:
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
);
2.查看表: show tables;
3.查看表结构
: desc 表名;
4.删除表:
(1)drop table 表名;
(2)drop table if exists 表名;
四.表的增删查改(CRUD)
1.增
![](https://img-blog.csdnimg.cn/8ac019015d304da8b3cee29765a88ea2.png)
2.查
select操作不会影响到服务器中硬盘上保存的数据,查找的时候只是建立了临时表
![](https://img-blog.csdnimg.cn/cbdcd086645e4c079547fa3d29c205b5.png)
3.改
![](https://img-blog.csdnimg.cn/e30994362a6b49a0a895e1882d57e90b.png)
注意注意:查询操作是临时表,因此他的数据类型不一定和原始的表的诗句类型完全一致;而修改操作因为改的是原始数据,因此数据类型不能超出范围
![](https://img-blog.csdnimg.cn/ccdb68657e1c4cd584c06441f4086073.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/0fd1ec821bfc4c43ad259b1216a239ce.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
4.删
![](https://img-blog.csdnimg.cn/c73c9b7afc9d490abd004f2ddcd9f482.png)
注意:where处如果不写条件,就会把整个表的数据都删除,得到一个空表
五.数据库约束
![](https://img-blog.csdnimg.cn/e65f4bc997f44d569a9211fcc81a5874.png)
六.表的设计
表的设计
:根据实际的问题场景,把表创建出来
设计表的方法是:先找出实际场景中的实体(对象),再分析实体之间的关系
1.实体之间的关系:
1.一对一的关系
以教务系统的账户和学生为例,一个账户对应一个学生,一个学生只有一个账号
![](https://img-blog.csdnimg.cn/9385c5db5a324ce687dd4bf88d657d33.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_9,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/2245434bbe2244c98fbf785e6c33effe.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/57143353ca354b99acb7396b001bb2a5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_13,color_FFFFFF,t_70,g_se,x_16)
2.一对多的关系
以教务系统的班级和学生为例,一个学生处于一个班级里,一个班级可以包含多个学生
![](https://img-blog.csdnimg.cn/9d2daba74b894dde94a8c50cf7ba45a0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_15,color_FFFFFF,t_70,g_se,x_16)
但MySQL没有提供数组类型,因此这个方法不能用
![](https://img-blog.csdnimg.cn/a13106dbe6ce470eb84197ae999d5bc3.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_12,color_FFFFFF,t_70,g_se,x_16)
3.多对多的关系
以教务系统的学生和课程为例,一个学生可以选多个课程,一个课程可以被多个学生选
方法:使用一个关联表
![](https://img-blog.csdnimg.cn/318ba4ae3bd64d56bd621d4a155bdd1e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_18,color_FFFFFF,t_70,g_se,x_16)
七.和查询结合在一起的新增操作
![](https://img-blog.csdnimg.cn/c0ddcfe17de24df99ad6bf76b054f352.png)
先执行查找,针对查找到的每个结果都执行插入操作
注意:查询的表中的查询结果的列的顺序和类型要和要插入的表一样
八.聚合查询(把多个行的数据进行关联运算)
1.聚合函数
![](https://img-blog.csdnimg.cn/0c7a0c85761244d4bc52828a6c67bcd1.png)
2.GROUP BY子句
GROUP BY 列名,根据这一列中的每一行的值,对数据进行分组,把值相同的行都归为一组
![](https://img-blog.csdnimg.cn/77df3263cf61409fa917035b5ba0fb20.png)
3.HAVING
group by可以使用where,只不过where是在分组之前执行;如果要对分组之后的结果进行条件查询,就需使用having
九.多表联合查询
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积
关联查询可以对关联表使用别名
关于笛卡尔积,举一个例子:
![](https://img-blog.csdnimg.cn/94beb175cbfa4a9f996d7c815d598f9e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_18,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/34fb7bc454b7436ca645ed89fe440ad4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_13,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/5e8a712ebd1d46099170a9059ed829eb.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_10,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/6e97900c60aa4aa780b05d45b837e69b.png)
1.内连接
![](https://img-blog.csdnimg.cn/5ce651f946d24839b2ea74025707b300.png)
2.外连接
外连接分为
左外连接和
右外连接。如果联合查询,
左侧的表完全显示我们就说是左外连接;
右侧的表完全显示我们就说是右外连接。
![](https://img-blog.csdnimg.cn/ef47566e5f694f9c918cfce497dea173.png)
![](https://img-blog.csdnimg.cn/8a4cd27783ec43758bb07b46e51c987b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
注意:多表查询的时候,
from 多个表 where是内连接,
而
from 表1 join 表2 on 条件 join 表3 on 条件既可以表示内连接,也可以表示外连接
3.自连接
自连接是指在同一张表连接自身进行查询,把行和行之间的比较条件转化成列和列之间的比较
举个例子:
![](https://img-blog.csdnimg.cn/fa7465ad0b0f417c84670025c22d5103.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/c3be0d2a6d0f4dec8bf986dff4b786d4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/940ed79c099b41d4afc234ff16162df0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/6f534864b9374eb581eee3b973e33356.png)
4.子查询(嵌套查询)
(1)单行子查询
返回多行记录的子查询,把题目分解成两步分别写,写完合并
![](https://img-blog.csdnimg.cn/f38a11710584446aa67861dc4fc57a5d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_18,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/49e4cbd4f05b42a3972bb47066decdc9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
(2)多行子查询
返回多行记录的子查询
![](https://img-blog.csdnimg.cn/cc51b870f9914e6d9c02ed8c421ffd5d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/c59cfea3ca4f45e8995fce933d76688f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
5.合并查询
为了合并多个select的执行结果,可以使用集合操作符 union,union all。使用UNION
和UNION ALL时,前后查询的结果集中,字段需要一致
![](https://img-blog.csdnimg.cn/1cea39ef48de47e19fd7dc640bf5ee28.png)
十.索引(index)
1.索引的作用/目的/优缺点
(1)索引的作用:数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍内容和书籍目录的关系
(2)索引的主要目的:提高查找的效率
(3)索引的优点:提高了查找的效率;缺点:占用了更多空间,拖慢了增删改的效率
(解释一下:因为书的目录也是废纸的,索引也是一样,用索引的时候会消耗额外的存储空间;书的内容进行调整,目录也会跟着调整,同样,增删改的时候,也要调整索引的结构)
2.索引的使用场景
![](https://img-blog.csdnimg.cn/d0ce24a023514ca2ac45f935e5943efc.png)
3.索引的使用及案例(案例在PDF上没有放上来)
创建主键约束(PRIMARY KEY)、唯一约束(UNIQUE)、外键约束(FOREIGN KEY)时,会自动创建对应列的索引
(1)查看索引
![](https://img-blog.csdnimg.cn/2c332833007c48dea5936dfde98206e2.png)
(2)创建索引
![](https://img-blog.csdnimg.cn/8b1f4dbe87f04d4984f6fbd486fd4f06.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_19,color_FFFFFF,t_70,g_se,x_16)
(3)删除索引
![](https://img-blog.csdnimg.cn/124792aa236c48d1a63c06be23626039.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_9,color_FFFFFF,t_70,g_se,x_16)
4.索引的面试问题(注意要通过举例+画图的方式回答)
(1)索引是干啥的
(2)索引的使用场景及付出的代价
(3)索引背后的数据结构
索引可以提高查找效率(这里的查找指的是
按值查找,
注意和按下标查找区分开,按下标查找叫做访问),索引背后的数据结构是:
多叉搜索树---(在MySQL中叫)B+树
B+树是什么样的,举一个例子:
![](https://img-blog.csdnimg.cn/139f1de7eac044789f73c6982fd6b290.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
B+树的特点:
使用B+树进行查找的时候,整体IO的次数比较少;
所有的查询都会落到叶子节点上;每次查询的IO次数也差不多,查询速度是稳定的;
叶子节点用链表链接后,非常适合范围查找;
所有的数据存储(载荷)都在叶子节点上,非叶子节点只保留key的值因此非叶子结点占用的空间很少,可以缓冲到内存中,这时磁盘IO就几乎没有了
拿一个实际例子来看,哪部分是非叶子结点,哪部分是叶子节点:
![](https://img-blog.csdnimg.cn/3c29da7551ef4eaf842fc9fbe721e236.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_6,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/9eaeceea034d44c49b5144e99493f9ba.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_5,color_FFFFFF,t_70,g_se,x_16)
另外索引背后不能是这些数据结构的原因附上:
1.顺序表/链表不行,因为他们是按下标查找
2.二叉树不行,因为二叉树的特点是,当元素多了,高度就高了,对应着比较次数就多了,意味着每次比较磁盘都要IO
3.哈希表不行,哈希表查找速度虽然很快,但是他只能针对"相等"进行判定,不能对"大于小于"或是范围查找进行判定
4.堆不行,因为堆只能找最大/最小
十一.事务
1.什么是事务
事务指逻辑上的一组操作,组成这组操作的各个单元,要么全部成功,要么全部失败
2.事务的目的
将若干个独立的SQL操作打包成一个整体
3.面试问题
3.1事物的四种基本特性
(1)原子性:要么全部执行,要么都不执行,事务不可再细分
但具体操作的时候,每个操作该执行执行,因为无法预知失败,但是!!当出现执行失败的时候,由数据库自动执行一些还原性的工作,来消除前面SQL操作带来的影响(看起来就像没执行一样)
由数据库自动执行一些还原性的工作,来消除前面SQL操作带来的影响--->这个操作叫
回滚(rollback)
![](https://img-blog.csdnimg.cn/1db16d4e4c294de3ac158e9b699ad4f3.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_15,color_FFFFFF,t_70,g_se,x_16)
(2)一致性:事务执行前后,数据库中的数据都是合理合法的
(3)持久性:事务一旦提交,数据就持久化存储起来,数据就写入硬盘了
(4)隔离性:用来解决并发执行事务的时候产生的问题
并发--->一心多用,可以同时解决多个事务
3.2由于隔离性低引发的脏读问题
(1)什么是脏读问题
![](https://img-blog.csdnimg.cn/58f4a4af15c64100b82daabb9c0ff37c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
(2)出现脏读问题的原因:事务和事务之间没有进行隔离
(3)如何处理脏读问题:给
写操作和读操作都加锁(我在进行写操作的时候,你不能读,我写完你才能读;同样的我在进行读操作的时候,你不能写,我读完你才能写)
![](https://img-blog.csdnimg.cn/1a930c005faf492781805fd350e8a417.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)
注意:
一旦加锁,意味着事务之间的隔离性高了,并发性低了.隔离性和并发性是不可兼得的,并发性是注重快,隔离性是注重安全
(4)为何处理脏读问题时要写操作读操作都加锁,而非只给写操作加锁
因为可能会出现:我写完了,解锁了,你可以读了,但你在读的时候,我同时继续写下面的操作,当你往下读的时候,发现代码变了(这里这种情况叫做---->
不可重复读:在一个事务中,包含了多次读操作,多次读操作读出的结果不一致)
--->那么这个时候,为了避免这种情况,我们需要让
读操作也加锁,此时事务的隔离性又高了,并发性更低了
(5)关于脏读问题的"加锁"的进一步解释&可能会出现的幻读问题及解决方法
事务在提高隔离性的时候需要进行一系列加锁,但是这里的加锁不是把整个数据库都加锁,因此我依旧可以在你读操作的时候,我在数据库的其他表中进行写操作.但是这里会出现一个问题:
读代码读着读着发现,代码变多了,原本只有A.java,现在多了一个B.java(幻读问题)
幻读问题:特殊的不可重复读
那怎么
解决幻读问题:彻底串行化执行(就是写操作加锁,读操作加锁,并且读操作时,写操作停止,什么都不干)
![](https://img-blog.csdnimg.cn/5acfde28b1a74206864f6f0ddafd3fcf.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_13,color_FFFFFF,t_70,g_se,x_16)
(6)隔离级别
![](https://img-blog.csdnimg.cn/ffa0468cadc74804b9bf8a6bf30685f1.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5Zyo5LiK5bGx55qEbWVp,size_20,color_FFFFFF,t_70,g_se,x_16)