java 开源数据库计算引擎

转载

mob6454cc6ba5a5 2024-09-03 04:28:47

文章标签 java 开源数据库计算引擎 java 经验分享字段主键 文章分类 Java 后端开发

文章目录

一.条件查询

1.distinct
2.where
3.like
4.null
5.between and
6.limit
7.order by 升序和降序

二.聚合 aggregation

1.count 求数量
2.max / min
3.sum / avg

三.分组 group

1.group by
2.having

四.拓展

1.char和varchar有什么区别？
2.中文乱码
3.注释
4.主键、外键、唯一索引的区别？
5.drop、delete和truncate之间的区别？

五.事务transaction

1.概述
2.事务4个特性ACID
3. 隔离级别:
4.事务管理的方式

六.字段约束

1.默认约束
2.检查约束
3.外键约束

七.表关联 association

1.概述

八.多表联查 join

1.笛卡尔积 Cartesian product
1.三种连接查询 join
2.列出research部门下的所有员工的信息%
3.怎么用内链接 INNER JOIN 实现上面的需求？
4.inner join、left join、right join的区别？
5.子查询 subquery

1.概念
2.单行子查询 =
3.多行子查询 in

6.SQL的执行顺序

九.索引 index

1.定义
2.分类
3.创建索引
4.索引扫描类型
5.最左特性
6.总结

优点:
缺点:

一.条件查询

1.distinct

使用distinct关键字,去除重复的记录行

select loc from dept;
select distinct loc from dept;

2.where

select * from emp where empno=100 --唯一条件

select * from emp where ename='tony' and deptno=2 --相当于两个条件的&关系

select * from emp where ename='tony' or deptno=1 --相当于两个条件的|关系

select name, sal from emp where sal=1400 or sal=1600 or sal=1800;

-- 或

select name, sal from emp where sal in(1400,1600,1800);

select name, sal from emp where sal not in(1400,1600,1800);

3.like

通配符%代表0到n个字符，通配符下划线_代表1个字符

select * from emp where ename like 'l%' --以l开头的

select * from emp where ename like '%a' --以a结束的

select * from emp where ename like '%a%' --中间包含a的

select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置

4.null

select * from emp where mgr is null --过滤字段值为空的

select * from emp where mgr is not null --过滤字段值不为空的

5.between and

select * from emp where sal<=3000 and sal>=10000  [3000,10000]
select * from emp where sal between 3000 and 10000

6.limit

limit n，返回前n条

select * from emp limit 2 --列出前两条

select * from emp limit 1,2 --从第二条开始,展示2条记录

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

7.order by 升序和降序

SELECT * FROM emp order by sal #默认升序

SELECT * FROM emp order by sal desc #降序

二.聚合 aggregation

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NqEBtZCU-1648115739322)(C:\Users\Lkx\AppData\Roaming\Typora\typora-user-images\1642315317517.png)]$

1.count 求数量

举例:统计员工的总人数
SELECT COUNT(ename) FROM emp#不推荐使用参数是字段名
SELECT COUNT(comm) FROM emp#不准确,不统计null元素
SELECT COUNT(1) FROM emp #推荐!!
SELECT COUNT(*) FROM emp #推荐!!

2.max / min

举例:统计员工的最高工资
SELECT MAX(sal) FROM emp
举例:统计岗位是员工的最高工资
SELECT MAX(sal) FROM emp WHERE job='员工'

3.sum / avg

举例:查询平均工资
SELECT AVG(sal),sal FROM emp
举例:统计19年入职的员工的总工资
SELECT SUM(sal) FROM emp WHERE YEAR(hiredate)=2019

三.分组 group

用于对查询的结果进行分组统计

group by表示分组, having 子句类似where过滤返回的结果

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-N1WVFJ6b-1648115739323)(C:\Users\Lkx\AppData\Roaming\Typora\typora-user-images\1642315331098.png)]$

1.group by

#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对

SELECT deptno,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno #按照deptno分组

SELECT job,MAX(sal),AVG(sal) FROM emp

GROUP BY job #按照job分组

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno,job #deptno和job都满足的

2.having

#平均工资小于8000的部门

select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having

#deptno出现的次数
SELECT deptno,COUNT(deptno) FROM emp

GROUP BY deptno #按deptno分组

HAVING COUNT(deptno)>1 #次数多的

四.拓展

1.char和varchar有什么区别？

char为定长字符串，char(n)，n最大为255

varchar为不定长字符串，varchar(n)，n最大长度为65535

char(10)和varchar(10)存储abc，那它们有什么差别呢？

char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。

2.中文乱码

如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令：

set names gbk;

3.注释

/* 很多注释内容 */

#行注释内容

4.主键、外键、唯一索引的区别？

1.Primary Key 主键约束，自动创建唯一索引
2.Foreign Key 外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写
3.Unique Index 唯一索引，唯一值但不是主键
对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

5.drop、delete和truncate之间的区别？

drop删除库或者表，数据和结构定义

delete和truncate只是删除表的数据

delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录

对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数

五.事务transaction

1.概述

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

2.事务4个特性ACID

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3. 隔离级别:

read uncommitted: 读未提交,安全性最差,但是效率高.
read committed: 读已提交,安全性有所提升,但是效率降低一些.也是Oracle数据库的默认隔离级别
repeatable read : 可重复读,安全性有所提升,但是效率又会低一些.也是MySQL数据库的默认隔离级别
Serializable: 串行化,安全性最高,但是性能最低

4.事务管理的方式

方式1, 使用MySQL数据库为我们提供的,自动事务管理. 默认会为每条SQL提供事务.
方式2, 手动管理事务,必须有两个过程: 开启事务 … 结束事务(commit / rollback)
测试3, 采用方式2 来模拟事务的管理过程:

窗口1:

mysql> start transaction;  #开启事务
mysql> insert into dept values(10,'php','bj');  #执行SQL
mysql> commit;  #提交事务

窗口2:

mysql> use cgb211101;
mysql> select * from dept; #查询(1号窗口提交后,2号窗口才能查到)

六.字段约束

1.默认约束

哪个字段添加了默认约束,哪个字段的值就有了默认值,使用default来实现.

#1.默认约束:给字段添加默认值--用的少!
CREATE TABLE test01(
 id INT PRIMARY KEY AUTO_INCREMENT,
 sex CHAR(3) DEFAULT '男'#默认约束
)
#虽然sex设置了默认值,但是只是手动录入时有效,发起insert语句时还是要写具体值的
INSERT INTO test01 VALUES(NULL,'男')

2.检查约束

#2.检查约束:检查字段的值的合理性
CREATE TABLE test02(
 id INT PRIMARY KEY AUTO_INCREMENT,
 age INT,
 CHECK(age>0) #检查约束,了解即可!
)
INSERT INTO test02 VALUES(NULL,10) #ok的
INSERT INTO test02 VALUES(NULL,-10) #会报错,没有通过检查约束

3.外键约束

#3.外键约束:为了省内存,使用对方表的主键来描述两张表的关系
#情况1:子表里的主键的值 必须 取自于 主表
#情况2:主表里的记录想要删除时,必须保证子表没有引用才行
CREATE TABLE tb_user(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(10),
 age INT,
 phone VARCHAR(11)
)
CREATE TABLE tb_user_address(
 user_id INT PRIMARY KEY ,#不能自增!!!
 address VARCHAR(100),
 #1.创建外键FK,描述和1号表的关系
 #foreign key(本表的主键) references 对方表名(对方的主键)
 FOREIGN KEY(user_id) REFERENCES tb_user(id)
)

七.表关联 association

1.概述

表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。

下面我们讨论表的关系分为四种：

一对一 one to one QQ和QQ邮箱，员工和员工编号
一对多 one to many 最常见，部门和员工，用户和订单
多对一 many to one 一对多反过来，员工和部门，订单和用户
多对多 many to many 老师和学生，老师和课程

八.多表联查 join

1.笛卡尔积 Cartesian product

多表查询是指基于两个和两个以上的表的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示员工表emp中不只显示deptno，还要显示部门名称，而部门名称dname在dept表中。

#把两个表的数据都拼接起来

SELECT * FROM dept,emp

1.三种连接查询 join

内连接 inner join
左（外）连接 left join
右（外）连接 right join

2.列出research部门下的所有员工的信息%

java 开源数据库计算引擎_java_03

SELECT * FROM emp
 
WHERE deptno = ( SELECT deptno FROM dept WHERE dname='research' )

3.怎么用内链接 INNER JOIN 实现上面的需求？

SELECT d.dname,e.ename,e.job
 
FROM emp e INNER JOIN dept d
 
ON e.deptno=d.deptno
 
WHERE d.dname='research'
 
换成left join和right join，看看有什么不同呢？

4.inner join、left join、right join的区别？

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ci29AqHG-1648115739323)(C:\Users\Lkx\AppData\Roaming\Typora\typora-user-images\1642317535093.png)]$

INNER JOIN两边都对应有记录的才展示，其他去掉
LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充
RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充

5.子查询 subquery

1.概念

子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用，记录多时最好使用其它方式替代。

2.单行子查询 =

--列出tony所在部门的所有人员
 
select deptno from emp where ename='tony';
 
select * from emp where deptno = (select deptno from emp where ename='tony');

3.多行子查询 in

select * from emp where job in ('经理','员工');

6.SQL的执行顺序

(1) FROM [left_table] 选择表
 
(2) ON <join_condition> 链接条件
 
(3) <join_type> JOIN <right_table> 链接
 
(4) WHERE <where_condition> 条件过滤
 
(5) GROUP BY <group_by_list> 分组
 
(6) AGG_FUNC(column or expression),... 聚合
 
(7) HAVING <having_condition> 分组过滤
 
(8) SELECT (9) DISTINCT column,... 选择字段、去重
 
(9) ORDER BY <order_by_list> 排序
 
(10) LIMIT count OFFSET count; 分页

九.索引 index

1.定义

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

2.分类

单值索引：一个索引只包括一个列，一个表可以有多个列
唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
复合索引：一个索引同时包括多列

3.创建索引

#查看索引，主键会自动创建索引
show index from dept;

#创建普通索引
#create index 索引名字 on 表名(字段名); #创建索引
create index loc_index on dept(loc); #创建索引

#创建唯一索引--索引列的值必须唯一
CREATE UNIQUE INDEX 索引名 ON 表名(字段名)
CREATE UNIQUE INDEX bindex ON dept(loc)

# 创建复合索引
#如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX 索引名 ON 表名 (字段1, 字段2)
CREATE INDEX PIndex ON Persons (LastName, FirstName)

# 删除索引
alter table dept drop index fuhe_index

4.索引扫描类型

type：

ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描，其次慢的方式
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
null MySQL不访问任何表或索引，直接返回结果

5.最左特性

explain
 
select * from dept where loc='二区' #使用了loc索引
 
explain
 
select * from dept where dname='研发部'#使用了dname索引
 
explain
 
select * from dept where dname='研发部' and loc='二区' #使用了dname索引

6.总结

优点:

索引是数据库优化
表的主键会默认自动创建索引
每个字段都可以被索引
大量降低数据库的IO磁盘读写成本，极大提高了检索速度
索引事先对数据进行了排序，大大提高了查询效率

缺点:

索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：javafx screen builder 弹出框

下一篇：华三交换机配置access口

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯