MySQL数据库知识总结
- 一、基本概念及其介绍
- 二、数据库中的数据类型
- (一)数值类型
- (二)字符串类型
- (三)日期类型
- 三、数据库基础语法
- (一)数据库的常用操作
- (二)数据表的常用操作
- 1.创建、查看、新增、删除表
- 2.select查找表数据
- (1)全列查询
- (2)指定列查询
- (3)查询列加工后的结果
- (4) 查询字段指定别名
- (5)针对查询结果去重
- (6)对查询结果进行排序
- 3.update 修改表数据
- 4.delete 删除表数据
- 四、 数据库的约束
- (一)约束类型
- (二)主键约束
- (三)外键约束
- 五、表的设计
- (一)一对一的关系
- (二)一对多的关系
- (三)多对多的关系
- 六、复杂语法
- (一)select + insert
- (二)复杂查询
- 1.聚合查询
- 2.分组查询
- 3.联合查询
- (1)笛卡尔积
- (2)内链接
- (3)外链接
- (4)自链接
- (5)子查询
- (6)合并查询
- 七、索引和事务
- (一)索引
- 1.基本介绍
- 2.B树索引
- 3.B+ 树索引
- (二)事务
- 1.基本介绍
- 2.事务的四个基本属性(ACID)
- 3.并发执行可能存在的问题
- (1)丢失修改
- (2)读脏数据
- (3)不可重复读
- (4)幻读问题
- 4.MySQL数据库四种隔离级别
- 5.封锁粒度
- 八、JDBC
- (一)JDBC链接MySQL
- 1.创建数据源
- 2.建立连接
- 3.构造SQL语句
- 4.执行SQL
- 5.释放资源
- (二)数据库操作
- 1.增加、修改、删除
- 2.查询数据
一、基本概念及其介绍
- 大多数数据库中的数据保存在硬盘(外存)上,例如 MySQL,但也有少部分数据库是把数据存储在内存中的,例如Redis、Tair
- 关系型数据库按照表格的方式来组织,例如MySQL、Oracle、SQL Server
- 非关系型数据库按照文档/键值对的方式组织,例如Redis、HBase、MongoDB
二、数据库中的数据类型
(一)数值类型
数据类型 | 大小 | java对应 | C对应 |
---|---|---|---|
BIT[M] | M指定位数,默认1 | Boolean | char[] |
TINYINT | 1字节 | Byte | signed char |
SMALLINT | 2字节 | Short | short int |
INT | 4字节 | Interger | int |
BIGINT | 8字节 | Long | long long int |
FLOAT(M,D) | 4字节 | Float | float |
DOUBLE(M,D) | 8字节 | Double | double |
DECIMAL(M,D) | M/D最大值+2 | BigDecimal | char[] |
NUMERIC(M,D) | M/D最大值+2 | BigDecimal | cahr[] |
- 上表中的 BIT[M] 代表二进制数,M范围从 1 到 64,存储数值范围从 0 到 2^M-1。数据类型中的 FLOAT(M,D) 和 DOUBLE(M,D),M指定长度,D指定小数位数,但是在实际使用时可能会发生精度丢失,于是便使用 DECIMAL(M,D) 和 NUMERIC(M,D) 以表示精确数值。
- 注意 JAVA 中没有 unsigned 类型,因此为了防止溢出,在 MySQL 中尽量不使用 unsigned 类型。
(二)字符串类型
数据类型 | 大小 | 说明 | java对应 | C对应 |
---|---|---|---|---|
VARCHAR(SIZE) | 0-65535字节 | 可变长度字符串 | String | char[] |
TEXT | 0-65535字节 | 长文本数据 | String | char[] |
MEDIUMTEXT | 0-16 777 215字节 | 中等长文本数据 | String | char[] |
BLOB | 0-65535字节 | 二进制形式的长文本数据 | byte[] | char[] |
- 其中 VARCHAR(SIZE) 可以指定字符串的大小,SIZE 的单位为字符;BLOB 用于表示二进制数据,例如mp3、jpg、class等二进制文件。在 SQL 中表示字符串,使用单引号或双引号都可,因为 SQL 没有单独的字符类型。
(三)日期类型
数据类型 | 大小 | 说明 | java对应 | C对应 |
---|---|---|---|---|
DATATIME | 8字节 | 从1000年到9999年,不会进行时区的检索及转换 | java.Date、java.sql.Timesstamp | MYSQL TIME |
TIMESTAMP | 4字节 | 从1970年到2038年,自动检索当前时区并进行转换 | java.Date、java.sql.Timesstamp | MYSQL TIME |
三、数据库基础语法
在MySQL数据库中,大小写不进行区分
(一)数据库的常用操作
- 显示当前服务器上有哪些数据库:show databases;
- 创建数据库:create database 数据库名;
- 选中/使用数据库:use 数据库名;
- 删除数据库:drop database;
(二)数据表的常用操作
1.创建、查看、新增、删除表
(1)创建表 :creat table 表名(列名 类型,列名 类型…);
- 进行表操作之前,需要先选中数据库(use),创建表后面可以加comment '说明’表示给某列加上注释
(2)查看所有表信息 :show tables;
(3)查看表结构,查看某表中有几列,每一列的名字、类型、补充信息 :desc 表名;
(4)删除表:drop table 表名;
(5)新增表中的一行:insert into 表名 values(列的值) ,
- values后的括号内容要和列头匹配
- 如果某一列是 datetime 类型,可以通过指定格式字符串进行插入,例如 2024-12-12 或 2024-12-12 21:21:00;也可通过 now() 函数来插入一个当前的系统时间,例如 insert into test value(1,now());
- insert进行插入时,可以只插入其中的某一列或某几列,其余列将采用默认值
- 如果需要插入中文,需要把数据库配置成 utf-8 编码方式(MySQL默认拉丁文编码)
- insert也可以一次插入多行,()之间用逗号隔开,例如:insert into student values(1,‘zhang’,19),(2,‘aaa’,20); 且一次插入N条数据比一次插入一条数据分成N次要快很多倍。
2.select查找表数据
select 查找的结果是一个“临时表”,这个“临时表”不存储在硬盘上,而是在内存中,输出时候,数据也被释放了,即select不会影响到服务器这边硬盘上保存的数据。
(1)全列查询
-
展示表中所有信息: select * from 表名;
-
这个操作是一个危险操作,当生产环境保存的数据量达到 TB 级别时,此时 MySQL 服务器就会疯狂的读取硬盘数据,但硬盘的读取速度存在上线,此时就会瞬间把硬盘的 IO 给吃满;同时 MySQL 服务器又会立即返回响应数据,但返回的数据也很大,则会把网卡的带宽吃满,而生产环境的服务器无时不刻需要给普通用户提供相应,一旦服务器的硬盘和网络被吃满,此时数据库服务器就难以对其他客户端的请求做出相应。
(2)指定列查询
- 展示指定列的所有信息 :select 列名, 列名…from 表名;
- 查询时会显式的告诉数据库要查的是哪些列,数据库就会有针对性的返回数据了,相比于全列查询,这种查询效率要高很多。
(3)查询列加工后的结果
- select 列操作…from 表名;
- select可以返回对指定列操作后的结果,例如select name, chinese+10 from exam;原始数据库服务器上的数据并没有发生改变,只是把加工后的结果作为临时表显示出来了
- 也可以返回各个列相加的总和例如:select name,chinese+math+english from exam,若对应的成绩类型设定保留三位有效数字,例如设定chinese 数据类型为decimal(3,1),保留3位有效数据精度为1,而总和得到的结果变为四位有效数据,是因为得到的临时表的类型会自动适应,以保证计算结果的正确。
(4) 查询字段指定别名
- select 列名, 列名 as 别名…from 表名; 给列操作后的结果起一个别名,例如select name,chinese+math+english as sum from exam,其中的as可以省略,但省略之后容易混淆。
(5)针对查询结果去重
- 去掉重复的记录select distinct 列名, 列名…from 表名; 针对多个列进行去重,需要多个列的值都相同才视为重复
(6)对查询结果进行排序
- 对查询结果(临时表)进行排序,不会影响到数据库服务器上的原始数据 select 列名… from 表名 order by 列名 asc/desc;
- asc为升序,desc为降序,省略不写时默认为升序asc。有的数据记录中是带有NULL值的,此时默认NULL是最小的。
- 排序可以按照表达式或者别名来进行排序,例如:select name,chinese+math+english as sum from exam order by sum;
- 排序也可以指定多个列进行排序,先按照第一列排序,如果第一列相同,相同结果之间按照第二个列进行排序:select name,math chinese from exam order by matn,chinese;
(7)条件查询:select 列名 from 表名 where 条件;
- 比较运算符
运算符 | 说明 |
---|---|
>、>=、<、<= | 基本比较 |
= | 等于,特殊的当NULL=NULL时结果为NULL |
<=> | 比较相等,和 ’=‘ 一样,但是比较两个NULL值的时候结果为真 |
!=、<> | 不等于 |
BETWEEN a and b | 匹配范围[a,b] |
IN {option,…} | 如果是option中的任意一个返回TRUE(1) |
IS NULL | 是NULL |
IS NOT NULL | 不是NULL |
LIKE | 模糊匹配,%表示任意多个(包括0个)任意字符,_表示任意一个字符 |
- 逻辑运算符
运算符 | 说明 |
---|---|
AND | 逻辑与 |
OR | 逻辑或 |
NOT | 逻辑非 |
- where语句不能使用别名进行查询,例如:select name,chinese+math+english as sum from exam where sum<200 会产生错误
- 查询时 AND 的优先级高于 OR ,同时使用时候如果想要打破优先级,需要用小括号 () 包裹优先执行的部分,例如select * from exam where chinese>80 or math>70 and english>70;  语句等价于select * from exam where chinese>80 or (math>70 and english>70);
- 在模糊查询中,语句:select * from exam where name like ‘王%’; 表示以王开头的所有字符串,当表示为%王%时则表示含有 ‘王’ 的任意字符串
- 对于NULL值的判断,一般来说使用 IS NULL 语句,因为 NULL=NULL 的结果为 false,NULL<=>NULL 的结果为true
(8)分页查询,SQL中使用limit来实现分页查询:select 列名 from 表名 limit 查找数量 offset 查找下标;
- 举例来说,select * from exam limit 3; 表示只取出结果中的三条,select * from exam limit 3 offset 3; 表示从下标为3 的记录开始,再向后找三条(下标从0开始)
3.update 修改表数据
使用 update 进行修改,修改会改变数据库服务器上面的原始数据:update 表名 set 列名 =数值, 列名 =数值 … where 条件; 其中 set 不能省略,where 后加的条件表示对那几行进行修改,省略 where 的话就是对所有记录进行修改。
- update 修改操作可以和 order by 排序和 limit 分页查询等操作一起使用,例如:update math=math+10 order by chinese+math+english limit 3;表示对总成绩倒数后三位的同学,其数学成绩加10
4.delete 删除表数据
delete from 表名 where 条件
- 注意区分drop和delete操作,drop一般用作删除整个表,而delete操作用于删除表中的每行数据,即使删除了表中所有的数据delete from 表名;表还在但内容为空。
四、 数据库的约束
(一)约束类型
约束针对各个列之间,包含以下类型
类型 | 说明 |
---|---|
NOT NULL | 某列数据不能为空 ,插入空值会报错 |
UNIQUE | 表示数据唯一,不能插入数值重复的数据 |
DEFAULT | 规定没有给列赋值时的默认值 |
PRIMARY KEY | 主键约束,设定数据的唯一身份标识,相当于是 NOT NULL 和 UNIQUE 的结合 |
FOREIGN KEY | 外键约束,保证一个表中的数据匹配另一个表中的值的参照完整性 |
CHECK | 确保列中的值符合指定的条件,但MySQL并不支持 |
(二)主键约束
- 主键约束对于一个表来说,只能指定一个列
- 可以设定主键为一个自增的序列(1、2、3 …)语法为:create table student (id int primary key auto_increment,name varchar(20)); 设定好自增主键后就可以不指定自增主键的值了,直接交给MySQL分配即可,直接插入insert into student values(null,‘张三’); 每次新增一个记录,都会产生一个新的id
- 自增主键也可以手动指定,但是一旦指定过后,后续新插入的数据就会从当前id开始自增,例如表中已有 id 为1,2,3,此时insert into students values(10,‘张三’);&nbnsp;后面插入的数据会从10之后开始排
(三)外键约束
- 外键约束描述的是两张表的两个列之间的’依赖关系‘,子表依赖于父表,要求子表中对应的记录在父表中存在,假设列名1设定的外键,为语法格式为:creat table 子表名 (列名1 类型,列名2 类型… foreign key 子表名(列名1) references 父表名(列名1));
- 外键约束写在表的最后,例如表 student 中的 classId 和 class 表中的 classId 需要对应,即当前 student 表中的 classId 需要依赖于 class 表中的 classId ,语法为:creat table student (studentId int primary key auto_increment,name varchar(20),classId int,foreign key(classId) references class(classId)); 再新增/修改记录之前,MySQL会拿着新增/修改的对应值在父表中先查询,查询到之后才能执行操作,否则会报错
- 外键约束同样约束着父表,当父表中的某个记录被子表依赖的时候,进行删除操作会报错,没有被依赖的数据可以删除成功
- 在父表中先查询,查询后新增/修改,这里的查询操作可能是一个成本较高的操作,外键约束其实要求父表中被依赖的这一列必须要有索引,通过索引大大提高查询速度
五、表的设计
实体与实体之间并不是孤立存的,而是存在对应关系,这样的对应关系也需要体现在表当中,实体间的关系包含:一对一、一对多、多对多
(一)一对一的关系
- 对于一对一的关系,在数据库设计中可以通过以下两种方式进行表示:1.两个实体用一张表来表示2.用两张表来表示,其中一张表包含了另一个表的id
(二)一对多的关系
- 对于一对多的关系,在数据库设计中同样可以通过以下两种方式进行表示,以下面的学生表和教室表为例
- 例如一个学生处于一个班级当中,一个班级可以包含多个学生。此时可以在班级表中新增一列,表示这个班级里的所有的学生id,或者班级表不变,在学生表中增加一列表示classId,通过这两种方式,能够知道一个班级对应哪些同学
- 对于一对多的关系,由于 MySQL 没有提供数组类型,因此只能采用方法2。但是 Redis 这样的数据库有数组类型,可以考虑方法1
(三)多对多的关系
- 对于多对多的关系,在数据库设计中使用建立关联表的方式来表示两个实体之间的关系
- 例如,一个学生可以选多个课程,一个课程可以包含多个学生,学生表student(studentId,name)、课程表course(courseId,name),此时创建一个关联表学生-课程表student_course(studentId,courseId),通过两个列之间的对应能够知道每个同学选了哪些课程,每个课上有哪些同学
六、复杂语法
(一)select + insert
- 从上一个表中查询得到的结果,作为下一个表要插入的数据
- 例如,需要把A的记录插入到B中,insert into B select * from A; 在这个语句中,会先执行查找操作,针对查找到的每个结果执行插入操作,需要保证从 A 中查询结果的列数和类型和 B 表匹配
- 对于两个表 A 和 B,只需要保证列数和类型相同,顺序不匹配时也能进行插入,例如表B(name varchar(20),id int),表A(id int,name varchar(20)),此时也能插入成功
- 另外,还可以给后面的 select 指定一些其他的条件/排序/去重,插入的结果是select 执行结果的临时表
(二)复杂查询
1.聚合查询
聚合查询把多行的数据进行了关联操作,MySQL内置了许多聚合函数
函数 | 说明 |
---|---|
COUNT( [DISDINCT] expr) | 返回查询到的数据的数量 |
SUM( [DISDINCT] expr) | 返回查询到的数据的总和,不是数字没有意义 |
AVG( [DISDINCT] expr) | 返回查询到的数据的平均值,不是数字没有意义 |
MAX( [DISDINCT] expr) | 返回查询到的数据的最大值,不是数字没有意义 |
MIN( [DISDINCT] expr) | 返回查询到的数据的最小值,不是数字没有意义 |
- ( [DISDINCT] expr) 中的DISDINCT表示去重,expr表示列名或者表达式
- 需要注意,当指定返回某列的count总和时,NULL值不会进行记录,例如 select count(chinese) from exam; 返回的结果没有计算其中 ( name=‘张三’ , chinese=NULL ) 的这条数据,但是select count(*) from exam; 语句返回的结果为表的行数,会计算该条数据
- 聚合查询 还可以搭配where子句进行使用,首先基于条件进行筛选,然后对筛选结果进行聚合,例如select sum(english) from exam where english>70;
2.分组查询
SELECT 中使用GROUP BY自己可以对指定列进行分组查询,针对分组之后的结果,可以使用 HAVING 进行条件过滤,而where 则是在分组之前执行
- 使用GROUP BY语句进行查询时,select 指定的字段必须是"分组依据字段",其他字段若想出现在select中必须包含在聚合函数中
- select role,max(salary),min(salary),arg(salary) from emp group by role;求得不同角色的最高、最底、平均工资,先进行分组,再根据分组分别来执行每个组的聚合函数
- 例如select role,arg(salary) from emp where name!=‘张’ group by role; 这个语句会先把姓名不是张的行筛选出来,再进行分组
- select role,avg(salary) from emp group by role having avg(salsry)<10000; 这个语句先执行分组,分组后再根据平均工资进行筛选,选择得到平均工资小于10000的结果
3.联合查询
联合查询即把多个表记录合并在一起再进行查询
(1)笛卡尔积
- 可以理解为两张表之间的排列组合,针对两张表 A,B 笛卡尔积的列数就是 A 的列数 + B 的列数,行数就是 A 的行数* B 的行数
- 在MySQL中进行笛卡尔积,直接select 列名 from 表名1,表名2 from 后面可以跟多个表名,表明之间用逗号隔开
- 笛卡尔积是一个单纯无脑的排列组合,这些组合结果并不一定是有意义的,例如对于两张表:学生表student(studentId int,name varchar(20),classId int),教室表class(classId int,name varchar(20)),两张表中都有classId,classId相等的记录就是需要保留的记录,此时的classId相等这样的条件,就称为"链接条件"
- 依据上一个例子,可以通过链接条件保留需要的数据,select student.name, class.name from student,class where student.classId = class.classId;
- 笛卡尔积也相当于一个临时表
(2)内链接
内连接的语法格式为:select 列名 from 表1 inner join 表2 on 条件 其中inner join表示内链接,inner可以省略,或者写成select 列名 from 表1,表2 from where 条件
现在创建下面的表,涉及三个实体:学生、班级、课程。学生和班级是一对多的关系,学生和课程之间是多对多的关系,分数表实际上是学生表和课程表之间的一个关联表。表中添加了若干信息
--班级表,包括班级id、班级名
creat table class(id int primary key auto_increment,name varchar(20),'desc' varchar(20)
);
--学生表,包括学生id、学号、姓名、qq邮箱
creat table student(studentId int primary key auto_increment,sn varchar(20),name varchar(20),qq_mail varchar(20)
);
--课程表,包括课程id、课程名
creat table course(id int primary key auto_increment,name varchar(20)
);
--分数表,包括成绩、学生id、课程id
creat table score(score decimal(3,1),student_id int,course_id int
);
-
获取张三学生的成绩,需要在student表中获取学生信息,再从score表中获取分数信息,需要针对student表和score表进行笛卡尔积,语法为:select student.name,score.score from student,score where student.id = score.student_id and student.name=‘张三’;
或者写成:select student.name,score.score from student join score on student.id = score.student_id and student.name=‘张三’; -
查询所有同学的总成绩,及同学的个人信息:select student.name,sum(score.score) from student,score where student.id = score.student_id group by student.id;
-
查询所有同学的单科成绩,及同学的个人信息涉及三张表student、score、course,语法为:select student.name, corse.name, score.score from student,score,course on where student.id = score.student_id and course.id = score.course_id;
或写成:select student.name, corse.name, score.score from student join score on student.id = score.student_id join course on course.id = score.course_id; -
对于三张表之间的内连接,语法格式为:from 表1,表2,表3 from where 条件 或者 from 表1 join 表2 on 条件 join 表3 on 条件
(3)外链接
左外连接的语法为: select 列名 from 表1 left join 表2 on 条件
右外连接的语法为: select 列名 from 表1 right join 表2 on 条件
举例来说,对于这样两张表:student 表和class表。student 表,包含学生编号、姓名、课程编号,其具体信息如下,
id | name | classId |
---|---|---|
1 | 张三 | 1 |
2 | 李四 | 1 |
3 | 王五 | 2 |
4 | 赵六 | 3 |
class表,包含课程编号、课程名,其具体信息如下,
id | name |
---|---|
1 | C语言 |
2 | 高等数学 |
若设置内连接 select * from student inner join class on student .classId = class.id; 查询得到的结果如下,可以发现赵六所对应的数据消失了
id | name | classId | id | name |
---|---|---|---|---|
1 | 张三 | 1 | 1 | C语言 |
2 | 李四 | 1 | 1 | C语言 |
3 | 王五 | 2 | 2 | 高等数学 |
此时设置左外连接select * from student left join class on student .classId = class.id;得到的结果表如下,赵六所对应的数据显示出来了,但由于class表中没有对应数据,因此右侧为NULL
id | name | classId | id | name |
---|---|---|---|---|
1 | 张三 | 1 | 1 | C语言 |
2 | 李四 | 1 | 1 | C语言 |
3 | 王五 | 2 | 2 | 高等数学 |
4 | 赵六 | 3 | NULL | NULL |
- 总的来说 :左外链接是以左侧的表为主,左侧表中的每个记录在左外连接中都有体现,右侧的列可以为NULL
右外链接是以右侧的表为主,右侧表中的每个记录在右外连接中都有体现,左侧的列可以为NULL
(4)自链接
自链接指在同一张表连接自身进行查询,即自己和自己进行笛卡尔积。自链接的本质是把行和行之间的比较转化成列和列之间的比较
以内连接创建的四个表为例,包含班级表、学生表、课程表、分数表。现在需要寻找语文分数大于数学分数的成绩信息,举例有下面的一些信息,首先需要在课程表corse中找到两门课对应的编号,分别为1,2
id | name |
---|---|
1 | 语文 |
2 | 数学 |
3 | 英语 |
再从分数表score中寻找student_id相同,corse_id=1的成绩 > corse_id=2的成绩的结果,此时需要每行之间进行比较
score | student_id | course_id |
---|---|---|
80 | 1 | 1 |
79.5 | 1 | 2 |
99 | 1 | 3 |
90 | 2 | 1 |
91 | 2 | 2 |
90 | 2 | 3 |
80 | 3 | 1 |
76 | 3 | 3 |
此时可以通过自链接将行之间的比较转化成列的,首先执行语句 select * from score as s1,score as s2 where s1.student_id=s2.student_id; 得到的笛卡尔积如下(仅展示了student_id=1笛卡尔积得到的结果,其余不进行显示),此时s1.course_id=1和s2.course_id=2就可以进行列之间的比较
score | student_id | course_id | score | student_id | course_id |
---|---|---|---|---|---|
80 | 1 | 1 | 80 | 1 | 1 |
80 | 1 | 1 | 79.5 | 1 | 2 |
80 | 1 | 1 | 99 | 1 | 3 |
79.5 | 1 | 2 | 80 | 1 | 1 |
79.5 | 1 | 2 | 79.5 | 1 | 2 |
79.5 | 1 | 2 | 99 | 1 | 3 |
99 | 1 | 3 | 80 | 1 | 1 |
99 | 1 | 3 | 79.5 | 1 | 2 |
99 | 1 | 3 | 99 | 1 | 3 |
… | … | … | … | … | … |
为了减少数据量,可以指定s1表只保留s1.course_id=1的记录,s2表只保留s2.course_id=2的记录,执行语句为:select * from score as s1,score as s2 where s1.student_id=s2.student_id and s1.course_id=1 and s2.course_id=2; 得到的笛卡尔积如下所示,得到的数据大大减少
score | student_id | course_id | score | student_id | course_id |
---|---|---|---|---|---|
80 | 1 | 1 | 79.5 | 1 | 2 |
90 | 2 | 1 | 91 | 2 | 2 |
最后只需再添加条件s1.course>s2.course,最终的sql语句为:select s1.student_id,s1.course,s2.course from score as s1,score as s2 where s1.student_id=s2.student_id and s1.course_id=1 and s2.course_id=2 and s1.course>s2.course;
(5)子查询
子查询是指嵌套在其他sql语句中的select语句,也叫嵌套查询
-
单行子查询:返回一行记录的子查询
-
同样采用上述例子,查找与"张三"同班的同学,此时需要先知道张三同学的班级id,再从学生表student中查找id相同的学生,此时的查询语句为:select name from student where class_id=(select class_id from student where name=‘张三’);
-
多行子查询:返回多行记录的子查询
-
有的时候子查询可能会查询出多条记录,就不能使用 = ,可能要用到 in、exists 等操作。
-
查找语文或者英语课程的成绩信息,需要先查询语文和英语的课程id,再根据id找到对应的成绩信息,语句为 select * from score where course_id in (select id from course where name=‘语文’ or name=‘英语’)
-
或使用exists操作: select * from score as sco where exists (select sco.id from course as cou where (name=‘语文’ or name=‘英语’) and cou.id = sco .id)
-
使用not exists操作:select * from score as sco where not exists (select sco.id from course as cou where (name=‘语文’ and name=‘英语’) and cou.id = sco .id)
(6)合并查询
合并查询即把多个查询语句的结果合并到一起,主要用到 union 、union all关键字,union操作会自动进行去重,而union all不会进行去重
- 通过union把两个sql的查询结果合并到一起,合并的前提是两个sql语句查询的列是相同的
- 例如,查询id小于3,或者名字为“英文”的课程,sql语句为:select * from course where name =‘英文’ union select * form course where id<3
- 也可以用or来替换,但是在使用or时,必须保证针对的是同一个表来指定的多个条件查询,而union则不一定针对同一张表
七、索引和事务
(一)索引
1.基本介绍
索引 (index)的主要作用就是进行查找,通过索引来提高查找的效率,创建索引语句为: creat index 索引名 on 表名(列名); ,删除索引的语句为:drop index 索引名 on 表名;
- 索引也是需要一定的额外存储空间的,数据量越大,索引消耗的额外空间也就越多。此外,进行增删改查时往往也要同步的调整索引的结构。因此 索引提高了查找的效率,但是也相应占用了更多的空间,降低了增删改的速度
- 数据库中的查找可以遍历表,但是遍历表的效率比较低,尤其是当数据量达到千万级别时,且MySQL中的数据存放在硬盘中,而硬盘IO的速度比内存的速度慢3-4个数量级,这样遍历表的效率就会非常低。此时就可以通过特殊的数据结构,来表示一些记录的特征,通过这些特征来减少比较次数,加快比较的速率
- 主键约束primary和数据唯一约束unique自带索引
- 当表里面已经有很多数据时,创建和删除索引是非常低效的,因此我们最好在创建表的时候把索引规划好,针对线上的数据库不要贸然去创建索引
- 对于索引的数据结构,使用二叉树时,当元素的数量较多,二叉树的 高度越高,对应的比较次数会变大;而对于哈希表来说,虽然哈希查找的速度很快,但是只能针对相等的情况进行判定,不能对指定范围内的数据进行查找。使用多叉搜索树做索引的结构最为合适,在数据库索引中B+树是最常见的索引结构
2.B树索引
B树的每个节点上都会存储N个key值,N个key值划分出了N+1个区间,在B树中查找元素,过程和二叉树非常相似,从根节点出发,根据待比较的元素确定下一个区间
数据是以节点为单位进行磁盘IO比较的,因此每次比较的次数可能相比于二叉树并不会减少,但是整体的速率会提升
3.B+ 树索引
B+树也是一个N叉搜索树,每个节点上都包含了多个key值,节点有N个key值,就会被分成N个区间,父节点的值都会 在叶子结点中体现出来,父结点中的值会作为子结点中的最大值(最小值),最下面的叶子节点,就使用链表按顺序排列
下图为最大值的情况
- B+树非常适用于数据库的索引,使用B+树进行索引时,整体的IO次数是比较少的
- 所有的查询都会落到叶子节点上,即每次查询的IO次数差不多查询速度稳定
- 叶子节点用链表链接后,非常适合于范围的查找,例如查找3到11范围内的数据,只需要找到3、11在叶子节点中的位置,然后顺着链表即可找到
- 所有的数据存储(载荷)都是放到叶子节点上的(例如查找学生的id=1,找到叶子节点1,其中叶子节点包含了id=1的学生信息例如姓名年龄等数据),非叶子节点只保存key值即可,因此非叶子节点整体占用的空间较小,甚至可以缓存到内存中,若可以缓存到内存中,此时磁盘IO几乎就没了
(二)事务
1.基本介绍
事务是指逻辑上的一组操作,组成这个操作的各个单元,要么全部成功,要么全部失败,其目的就是把若干个独立的操作打包成一个整体
-
在SQL中,有的复杂的任务需要多个SQL来执行,有时也需要打包在一起,即前一个SQL为后一个SQL提供支持,如果后一个SQL不执行了或者执行出问题了,前一个SQL也就失去意义了
-
事务保证了原子性:即要么全部执行完,要么一个都不执行,即任务不能在被细分了
-
当出现执行失败后,由数据库自动执行一些“还原”性的工作,来消除前面的SQL带来的影响,看起来好像一个都没执行一样,这样的“还原”性的工作就是“回滚”
-
数据库先执行第一个SQL语句,接下来执行第二个SQL语句的时候出现了意外导致无法执行,那么接下来数据库就会还原之前的操作,即把之前改动过的数据给还原回去
-
数据库会把执行的过程给记录下来,方便回滚,但是数据库要想记录详细的操作,也是需要消耗大量的时间和空间的,所以一般只保存最近几天的记录,不会保存全部记录
开启事务语句为:start transaction; 后接多条SQL语句,最后回滚或者提交:rollback/commit,rollback即全部失败,commit即全部成功
start transaction:
update accout set money = money - 200 where name = '张三';
update accout set money = money + 200 where name = '李四';
commit;
2.事务的四个基本属性(ACID)
- 原子性(Atomicity):一个事务要么全部执行完,要么一个都不执行,即任务不能在被细分了
- 一致性(Consistency):在事务执行之前和执行之后,数据库中的数据都必须是合理合法的,即从一个一致状态转化到另一个一致状态(例如,转账后不能出现账户余额是负数的情况、从A用户中减少转账后必须有其他账户的余额增加)
- 隔离性(Isolation):一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的,并发执行的各个事务之间不能互相干扰。
- 持久性(Durability):一个事务一旦提交,其操作的数据就持久化存储起来了,即数据就写入到硬盘中了
3.并发执行可能存在的问题
隔离性描述的是事务并发执行时,多个事务在尝试修改/读取同一份数据时容易出现一些问题,例如“读脏数据”(读取了修改之前的数据),此时可以通过给写操作加锁来避免读脏数据,这样事务之间的隔离性就变高了,并发性就降低了
并发性和隔离性是不可兼得的
(1)丢失修改
- 丢失修改(写-写冲突):多个事务在尝试修改同一份数据时容易出现的问题,两个事务在并发下同时进行数据修改,后一个事务的更新覆盖了前一个事务的操作,就好像前一个事务没有进行操作一样,即同时进行两个写操作,第一个进行的写操作被覆盖了
- 例如,事务T1、T2同时对A进行操作,T1先读取A的内容,随后T2读取A的内容,T1将A的值减一返回到A,之后T2同样进行此操作,得到的A值为减1,并不是减2,导致事务T1的修改被丢失
(2)读脏数据
- 读脏数据(读-写冲突):修改事务更新了数据,之后读取事务在此时读取了同一份数据,由于某些原因,修改事务RollBack了操作,导致读取事务所读取的数据就会是不正确的,即在写的过程中读,导致读取了未修改的数据,通过给写操作加锁,让事务在写的过程中不能读来解决读脏数据
- 例如,事务T1对数据C进行修改,之后事务T2读取C=200,之后事务T1回滚了之前的操作,C的值恢复为100,导致T2事务读取到了错误的‘脏数据’
(3)不可重复读
- 不可重复读(读-写冲突):一个事务包含多次的读操作,读操作过程中插入了写操作,多次读操作得出的结果并不一致,即在读的过程中写,导致两次读的内容不一致,通过给读操作加锁,让读的时候不能写来解决不可重复读的问题
- 例如,事务T1读数据A、B进行了求和,之后事务T2对数据B进行了修改,事务T1重复读取数据A、B进行求和验算得到的结果不正确
(4)幻读问题
- 幻读问题,一个事务执行过程中进行多次查询,多次查询的结果不一样(这里特指多数据或少数据)是一种特殊的不可重复读
- 要解决幻读问题,就需要彻底串行化执行,这样的隔离性最高,并发程度最低,数据最可靠,速度最慢
4.MySQL数据库四种隔离级别
根据实际需要来调整数据库的隔离级别,通过不同的隔离级别控制事物之间的隔离性/并发程度,MySQL数据库的隔离级别可以分为以下四种,我们可以通过修改my.ini这个配置文件来设置当前的隔离级别
- read uncommitted; 允许读取未提交的数据,并发程度最高,隔离性最低,会引入脏读+不可重复读+幻读+丢失修改问题
- read committed; 只允许读另一个事务提交之后的数据,相当于写加锁,并发程度降低了,隔离程度提高了,可以解决脏读问题,但会带来不可重复读+幻读+丢失修改问题
- repeatable read; 开始读取数据时,不允许对数据进行修改,相当于给读和写都加锁,解决了脏读+不可重复读问题,但会带来幻读+丢失修改问题
- serializable;串行化,并发程度最低,隔离程度最高,解决了脏读+不可重复读+幻读+丢失修改问题,但是执行速度最慢
5.封锁粒度
封锁对象的大小称为封锁的粒度。封锁对象可以对整个数据库进行加锁,可也可对某个属性值进行加锁。
- 封锁粒度与系统的并发度和并发控制的开销密切相关 ,封锁的对象越大,能够封锁的数据单元越少,并发度越小,隔离性越高,系统开销也就越小
封锁粒度 | 被封锁的对象个数 | 并发度 | 系统开销 |
---|---|---|---|
大 | 少 | 小 | 小 |
小 | 多 | 高 | 大 |
- 封锁粒度的一般原则:需要处理大量元组的用户事务,以关系为封锁单元;需要处理多个关系的大量元组的用户事务,以数据库为封锁单位;只处理少量元组的用户事务,以元组为封锁单位
- 事务虽然为了保证隔离性要进行一系列加锁,但是也不是整个数据库都锁定了,还可以进行其他表或数据的修改
八、JDBC
JAVA 约定了一组API,即为JDBC,这里的API就包含一些类和方法,通过这些类和方法来实现数据库的基本操作。再由各种厂商提供各自的 “数据库驱动包” 来和IDBC的API对接,这样只需要掌握这一套JDBC API就可以操作各种数据库
(一)JDBC链接MySQL
JDBC链接MySQL首先要在下载JDBC的驱动,点击进入下载地址,注意下载需要对应的自己的MySQL版本,下载后解压,将jar包放入到项目的lib文件目录中,然后右键lib文件夹Add as Library
JDBC链接MySQL包含下面的几个步骤
1.创建数据源
//1.创建好数据源DataSource dataSource = new MysqlDataSource();//设置数据库所在的地址((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/databaseName?characterEncoding=utf8&useSSL=false");//设置登录数据库的用户名((MysqlDataSource) dataSource).setUser("root");//设置登录数据库的密码((MysqlDataSource) dataSource).setPassword("123");
(1)首先要创建DataSource对象,这个对象描述了数据库服务器在哪里。
- DataSource 是 JDBC 自带的接口,MysqlDataSource来自第三方数据包
- 第一步操作通过先向上转型,在向下转型的操作,主要是为了后续其他代码中如果有需要用到DataSource,相关的参数类型也是DataSource,数据库进行切换后不需要改动代码
- DataSource 内置了连接池,在频繁创建/断开链接的时候,这里的 DataSource 会比 DriverManager 的方式更加高效
(2) 设置数据库所在的地址
- 设置URL地址时 " jdbc:mysql " 表示这个URL是用于JDBC MySQ的;
- " 127.0.0.1 “表示IP地址,或MySQL服务器主机的IP地址,一般用于确定网络上主机的位置,127.0.0.1默认为自己的主机,与” localhost "等价
- " 3306 "表示端口号,即访问主机上的哪个程序,安装MySQL时设置了其默认端口号为3306
- " databaseName? "表示数据库名,即要链接的MySQL中的哪个数据库
- " characterEncoding=utf8 "表示字符编码,此时 设定为utf-8
- " useSSL=false "表示是否在传输的过程中加密,此处设置为不加密
(3) 设置数据库的用户名和密码
- " root "是MySQL默认自带的管理员用户
- 密码是安装MySQL时候设置的密码
2.建立连接
public static void main(String[] args) throws SQLException {...//2.让代码和数据库服务器建立连接Connection connection = dataSource.getConnection();...}
- 对于getConnection()方法,由于建立连接可能会失败,由此抛出异常,因此需要显式(受查异常)
3.构造SQL语句
//3.对数据库进行操作(构造SQL语句)String sql = "insert into customer values(1,'z','','','','')";PreparedStatement statement = connection.prepareStatement(sql);
- JDBC中的SQL不需要带上分号’ ; ', ’ ; ’ 只在命令行中用来区分不同的语句,在代码中直接操作中并不需要
- 将String包装成一个" 语句对象 ",即将字符串风格的SQL转化成了JDBC里面的一个对象
4.执行SQL
//4.执行SQLint ret = statement.executeUpdate();
- SQL里如果是 insert,update,delete 都使用 executeUpdate() 方法
- SQL里如果是 select 就使用 executeQuery() 方法
- 返回值表示这个操作影响到了几行,相当于在控制台输入SQL后得到的数字
5.释放资源
//5.SQL执行完毕 释放资源statement.close();connection.close();
- 当创建好相关的连接后,JVM就会从系统中申请到一些硬件资源,这些资源不用时需要及时释放
- 释放资源时遵循先创建的后释放
完整的代码如下
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class TestJDBC {public static void main(String[] args) throws SQLException {//1.创建好数据源DataSource dataSource = new MysqlDataSource();//((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/databaseName?characterEncoding=utf8&useSSL=false");((MysqlDataSource) dataSource).setUser("root");((MysqlDataSource) dataSource).setPassword("123");//2.让代码和数据库服务器建立连接Connection connection = dataSource.getConnection();//3.对数据库进行操作(构造SQL语句)String sql = "insert into student values(1,'z')";PreparedStatement statement = connection.prepareStatement(sql);//4.执行SQLint ret = statement.executeUpdate();//5.SQL执行完毕 释放资源statement.close();connection.close();}
}
(二)数据库操作
1.增加、修改、删除
- prepareStatement 除了能够用于描述一个SQL外,还能辅助进行动态拼接,其具体的语句如下
- 通过setInt(1,id); 把第一个’ ? ’ 替换成用户输入的 id,把第二个’ ? ’ 替换成name
- 增加、修改、删除操作模板相同,只需修改对应的SQL语句
public class TestJDBC {public static void main(String[] args) throws SQLException {Scanner scanner = new Scanner(System.in);//用户输入一个待插入的数据System.out.println("请输入学号:");int id = scanner.nextInt();System.out.println("请输入姓名:");String name = scanner.next();//1.创建好数据源//2.让代码和数据库服务器建立连接//3.对数据库进行操作(构造SQL语句)String sql = "insert into student values(?,?)";PreparedStatement statement = connection.prepareStatement(sql);//这里的下标从1开始排列statement.setInt(1,id);//把第一个?替换成idstatement.setString(2,name);//把第二个?替换成name//4.执行SQLint ret = statement.executeUpdate();//5.SQL执行完毕 释放资源}
}
2.查询数据
- 对于查询数据来说,需要注意使用statement.executeQuery()方法,此时查找操作返回的不是一个int数据了,而是一个"临时表",使用ResultSet表示这个表
- 对于ResultSet 接收的临时表,通过遍历结果集合,先获取到每一行,再获取到这一行中的若干列, next方法表示获取到一行记录,同时把光标往后移动,如果遍历到表的结束为止,此处的nex t就返回 false 了
- 通过while循环搭配resultSet.next()来依次获取到表中的每一行,针对当前这一行来获取到其中的列,通过列的名字来对应数据库中的具体数据
- 最后注意释放 ResultSet 的资源
public class TestJDBCSelect {public static void main(String[] args) throws SQLException {//1.创建好数据源//2.让代码和数据库服务器建立连接//3.对数据库进行操作(构造SQL语句)String sql = "select * from student ";PreparedStatement statement = connection.prepareStatement(sql);//4.执行SQLResultSet resultSet = statement.executeQuery();//遍历结果集合while(resultSet.next()){int id = resultSet.getInt("id");String name = resultSet.getString("name");System.out.println("id=" + id + ",name=" + name);}//5.SQL执行完毕 释放资源resultSet.close();statement.close();connection.close();}
}