数据库基础知识超详细解析~‍（进阶/复习版）

---

前言

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

一、数据库的操作

1.登入数据库

mysql -u root -p

2.创建数据库

create database 表名 charset utf8;

3.显示当前数据库

show databases;

4.使用数据库

use 数据库名;

5.删除数据库

drop database 数据库名;

二、常用数据类型

数据类型	大小(字节)
tinyint	1
smallint	2
int	4
bigint	8
float	4
double	8
decimal(M,D)	M/D最大值+2
varchar	0-65535
datetime	8
timestamp	4

三、数据库的约束

1约束类型

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
DEFAULT - 规定没有给列赋值时的默认值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标
识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略check字句.

2NULL约束

指定id不为空

 create table student(id int not null,name varchar(20));

3UNIQUE:唯一约束

指定class_id列为唯一的，不重复的

create table student(id int not null, class_id int unique,name varchar(20));

4DEFAULT：默认值约束

指定插入数据时，name列为空，默认值unkown

create table student(id int not null, class_id int unique,name varchar(20) default 'unknow');

5 PRIMARY KEY：主键约束

指定id列为主键

create table student(id int not null primary key , class_id int unique,name varchar(20) default 'unknow');

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大
值+1。

--主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
id int primary key auto_increment,

6 FOREIGN KEY：外键约束

创建班级表classes，id为主键

create table class(id int primary key auto_increment,name varchar(20));

创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，classes_id为外键，关联班级表id

create table student(id int primary key auto_increment,name varchar(20),class_id int,foreign key(class_id) references class(id));

7 CHECK约束（了解）

MySQL使用时不报错，但忽略该约束

drop table if exists test_user;
create table test_user (id int,name varchar(20),sex varchar(1),check (sex ='男' or sex='女')
);

四、表的设计

一对一：eg.一个学生只有一个学号，一个学号只能表示一个学生。
一对多：eg.一个学生对应一个班级，但一个班级有多个学生。
多对多：eg.一个学生可以选择多门课程，一门课程也能被多个学生选择。

五、表的增删改查

1.查看表结构

desc 表名;

2.创建表

create table 表名(字段1 类型1,字段2 类型2,...
);

3.新增

创建一个学生表student，插入两个学生信息，再将已有的学生信息复制到test表中。

 create table student(id int primary key auto_increment,name varchar(20));insert into student values (1,'张三'),(2,'李四');create table test(id int primary key auto_increment,name varchar(20));insert into test select * from student;select * from test;

4.查询

4.1聚合查询

4.1.1聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有

函数	说明
count	返回查询到的数据的 数量
sum	返回查询到的数据的总和，不是数字没有意义
avg	返回查询到的数据的平均值，不是数字没有意义
max	返回查询到的数据的最大值，不是数字没有意义
min	返回查询到的数据的最小值，不是数字没有意义

CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);
-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);
select * from exam_result;

--统计有多少条数据select count(*) from exam_result;-- 统计数学成绩总分select sum(math) from exam_result;-- 统计平均总分select avg(chinese+math+english) as '平均分' from exam_result;-- 返回英语最高分select max(english) from exam_result;-- 返回 > 70 分以上的数学最低分select min(math) from exam_result where math>70;

4.1.2GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

create table emp(id int primary key auto_increment,name varchar(20) not null,role varchar(20) not null,salary numeric(11,2)
);
insert into emp(name, role, salary) values
('zs','教师', 10000),
('ls','教师', 12000),
('ww','管理员', 8000),
('zl','管理员', 9500),
('yq','老板', 13500);

--查询每个角色的最高工资、最低工资和平均工资select role,max(salary),min(salary),avg(salary) from emp group by role;

4.1.3HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING

--显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<10000;

4.2联合查询/多表查询（面试考察频率高）

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

1. 笛卡儿积：两个表之间的运算方式，本质就是进行排列组合，笛卡儿积的列数就是前两个表的列数之和，笛卡儿积的行数就是前两个表的行数之积。select * from 表1，表2；得到的结果绝大部分都是“无效数据”。
2. 指定连接条件。
3. 指定其他条件。
4. 针对列进行精简、表达式运算、聚合查询。

--创建班级表
create table classes (id int primary key auto_increment,name varchar(20),`desc` varchar(100)
);--创建学生表
create table student (id int primary key auto_increment,sn varchar(20),name varchar(20),qq_mail varchar(20),classes_id int
);
-- 创建课程表
CREATE TABLE course (id int primary key auto_increment,name varchar(20)
);
-- 创建课程学生中间表：考试成绩表
create table score (score decimal(3, 1),student_id int,course_id int
);

insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','[e-mail]',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','[e-mail]',1),
('00054','不想毕业',null,1),
('51234','好好说话','[e-mail]',2),
('83223','tellme',null,2),
('09527','老外学中文','[e-mail]',2);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

（1）查询“许仙”同学的 成绩

1. 笛卡儿积：两个表之间的运算方式，本质就是进行排列组合，笛卡儿积的列数就是前两个表的列数之和，笛卡儿积的行数就是前两个表的行数之积。select * from 表1，表2；得到的结果绝大部分都是“无效数据”

select * from student,score;

2. 指定连接条件

select * from student,score where student.id=score.student_id;

3. 根据需求给出其他条件

select * from student,score where student.id=score.student_id and student.name='许仙';

4. 针对列进行精简/表达式运算/聚合查询

--写法一
select student.name,score.score from student,score where student.id=score.student_idand student.name='许仙';
--写法二select * from 表1 join 表2 on 连接条件;
select student.name,score.score from student join score on student.id=score.student_id and student.name='许仙';

（2）查询所有同学的总成绩，及同学的个人信息:

1. 笛卡尔积

select * from student,score;

2. 指定连接条件，对这里的有效记录进行筛选

select * from student,score where student.id=score.student_id;

3. 补充一些其他条件
   由于此处是需要知道所有同学的总成绩，不需要对同学进行进一步筛选，这个步骤就没有啦~

4. 针对列进行精简/表达式运算/聚合查询

select student.name,sum(score.score) as total from student join score on student.id=score.student_id group by student.name;

（3）查询所有同学的成绩及同学的个人信息

1. 笛卡尔积
   

2. 指定连接条件，对这里的有效记录进行筛选
   此时三个表的笛卡儿积可以理解为student和score先算笛卡儿积,连接条件为student.id=score.student_id
   上诉结果再和course表计算笛卡儿积，连接条件score.course_id=course.id
   

3. 指定其他条件，此处和第二个例子一样也是不需要指定的。

4. 针对列进行精简/表达式运算/聚合查询

 select student.name,course.name,score.score from student,course,score where student.id=score.student_id and course.id=score.course_id;

4.2.1内连接

eg1.学生表和课程表的数据是一一对应的，学生表中的任何一个记录都能在分数表中体现出来，反之，分数表中的每个记录也能在学生表体现。此时，内连接和外连接结果是一样的。

create table student(id int,name varchar(20));
create table score(studentId int,score int);
insert into student values(1,'张三'),(2,'李四'),(3,'王五');
insert into score values(1,67),(2,90),(3,85);

select * from student;
select * from score;
--写法一
select * from student,score where student.id=score.studentId;
--写法二（可以省略inner）
select * from student inner join score on student.id=score.studentId;

4.2.2 外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接(left)；右侧的表完全显示我们就说是右外连接(right)。

eg1.

--左外连接
select * from student left join score on student.id=score.studentId;
--右外连接
select * from student right join score on student.id=score.studentId;

eg2.如果对这两个表的数据进行修改，让这两个表的记录不再一一对应，此时内连接和外连接就不一样了（少数情况）

--更改id3为4
update score set studentID=4 where score = 85;
--内连接
select * from student join score on student.id=score.studentId;

--左外连接
select * from student left join score on student.id=score.studentId;
--右外连接
select * from student right join score on student.id=score.studentId;

左外连接：

右外连接

4.2.3自连接

eg.显示所有“计算机原理”成绩比“Java”成绩高的成绩信息[代码见4.2]
在这个例子中，要想完成不同科目的比较需要比较行之间的大小（sql中无法直接做到，就只能把行转成列）

1. 笛卡尔积

--给表起别名
select * from score as s1,score as s2;

2. 指定连接条件（由于此处要关注的是每个学生自己的两门课情况，按照学生id作为连接条件）

select * from score as s1,score as s2 where s1.student_id=s2.student_id;

3. 根据需求给出其他条件
   需要筛选出所有左表为3，右表为1的学生记录

 select * from score as s1,score as s2 where s1.student_id=s2.student_id and s1.course_id=3 and s2.course_id=1;

 select * from score as s1,score as s2, student where s1.student_id=s2.student_id and s1.course_id=3 and s2.course_id=1 and s1.score>s2.score and student.id=s1.student_id;

4. 针对列进行精简、表达式运算、聚合查询。

select student.name,s1.score as '计算机原理',s2.score as 'Java' from score as s1,score as s2, student where s1.student_id=s2.student_id and s1.course_id=3 and s2.course_id=1 and s1.score>s2.score and student.id=s1.student_id;

4.2.4 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

eg.查询与“不想毕业” 同学的同班同学[代码见4.2]

select name from student where name  != '不想毕业' and classes_id =(select classes_id from student where name = '不想毕业');

对于子查询非常不建议使用。

4.2.5 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使UNION 和UNION ALL时，前后查询的结果集中，字段需要一致。
union该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

eg.查询id小于3，或者名字为“英文”的课程[代码见4.2]

select * from course where name ='英文' union select * from course where id <3;

六、MySQL索引事务

1.索引

1.1概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现

1.2 作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。 索引所起的作用类似书籍目录，可用于快速定位、检索数据。
索引对于提高数据库的性能有很大的帮助。

1.3特点

• 加快查询的速度。
• 索引自身是一定的数据结构，也要占据存储空间。
• 当我们需要进行新增、删除、修改的时候，也需要对索引进行更新。（额外的开销）

1.4适用场景

1）对于存储空间，要求不高。（存储空间比较充裕）
2）应用场景中，查询较多，增加修改删除不多。

1.5使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

• 查看索引

show index from 表名;

• 创建索引
  对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

• 删除索引

 drop index 索引名 on 表名;

2.事务

2.1事务的概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

2.2事务的使用

（1）开启事务：start transaction;
（2）执行多条SQL语句
（3）回滚或提交：rollback/commit;
说明：rollback即是全部失败，commit即是全部成功。

2.3事务的特性（ACID）

• 原子性（Atomicity，或称不可分割性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  
  
  一致性（Consistency）：相当于原子性的延伸，在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作（通过约束避免数据出现非法情况）。
  隔离性（Isolation，又称独立性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致（通过隔离性对问题进行权衡，希望数据尽量准确?/速度尽量快？）。
  
  持久性（Durability）：事务任何的修改，都是持久化存在（写入硬盘的），无论是重启主机，修改都不会丢失（数据库本身就是为了持久化存储）。

关于隔离性的事务并发执行可能产生的bug----

2.4事务的隔离级别

读未提交【Read Uncommitted】： 在该隔离级别，所有的事务都可以看到其他事务没有提交的执行结果。（实际生产中不可能使用这种隔离级别的），但是相当于没有任何隔离性，也会有很多并发问题，如脏读，幻读，不可重复读等，我们上面为了做实验方便，用的就是这个隔离性。
读提交【Read Committed】：该隔离级别是大多数数据库的默认的隔离级别（不是 MySQL 默认的）。它满足了隔离的简单定义:一个事务只能看到其他的已经提交的事务所做的改变。这种隔离级别会引起不可重复读， 即一个事务执行时，如果多次select， 可能得到不同的结果。
可重复读【Repeatable Read】： 这是 MySQL默认的隔离级别，它确保同一个事务，在执行中，多次读取操 作数据时，会看到同样的数据行。但是会有幻读问题。
串行化【Serializable】: 这是事务的最高隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读的问题。它在每个读的数据行上面加上共享锁，。但是可能会导致超时和锁竞争（这种隔离级别太极端， 实际生产基本不使用）

七、JDBC

7.1JDBC优势

Java语言访问数据库操作完全面向抽象接口编程
开发数据库应用不用限定在特定数据库厂商的API
程序的可移植性大大增强

7.2JDBC开发

7.3JDBC使用步骤总结

1.创建数据库连接Connection
2. 创建操作命令Statement
3. 使用操作命令来执行SQL
4. 处理结果集ResultSet
5.释放资源

7.4面试问答

1）数据库连接有哪些方式？分别有什么区别？
数据库连接有三种方式：本地连接、远程连接和 socket连接。
本地连接：数据库和应用程序运行在同一台服务器上，通过本地主机IP地址或者localhost进行连接。这种方式速度快，安全性高，适合一些单机小型项目。
远程连接：数据库和应用程序运行在不同的服务器上，通过远程主机IP地址进行连接。这种方式适合分布式系统，但由于数据传输经过网络，可能会存在安全风险和连接速度较慢的问题。
socket连接：通过TCP/IP协议的socket连接进行数据库连接。这种连接方式相对于本地连接和远程连接来说更加灵活，更适用于大型分布式系统，但需要考虑网络环境的稳定性和安全性。

2） 数据库Statement和PreparedStatement有什么区别？
数据库中的Statement和PreparedStatement都是用来执行SQL语句的类，但是它们之间有一些区别。
Statement是一种普通的SQL执行方式，每次执行SQL语句时都会将SQL语句发送给数据库进行解析和编译，这样会增加数据库的负担。而PreparedStatement是一种预编译的SQL执行方式，它会在首次执行SQL语句时将SQL语句发送给数据库进行解析和编译，然后将编译结果缓存起来，之后再执行相同SQL语句时直接使用已经编译好的结果进行执行，从而提高执行效率。
使用Statement执行SQL语句时，如果SQL语句中存在变量，需要手动拼接SQL语句，这样存在SQL注入的风险。而PreparedStatement允许在SQL语句中设置占位符，然后通过set方法设置参数值，从而防止SQL注入。
PreparedStatement通常比Statement更安全，更快速，特别是在需要频繁执行相同SQL语句的情况下。所以在开发中一般推荐使用PreparedStatement而不是Statement。

---

最后，码字不易，如果觉得对你有帮助的话请点个赞吧，关注我，一起学习，一起进步！