java 设置数据库最大连接池 java数据库连接池面试题

正文

多表连接查询：

1.什么是连接查询：

在实际开发中大部分都不是从一张表中查询数据，一般都是多张表联合查询取得结果。

实际开发中，一般一个业务对应多张表。比如：学生和班级，起码两张。（因为一张表可能会出现冗余的情况，即数据大量重复！）

2连接查询的分类：

1.根据出现年代来划分，包括：

• sql92（一些老的DBA还在使用的语法，已经被淘汰）
• sql99（比较新的语法）

2.根据表的连接方式划分，包括：

• 内连接： 等值连接

非等值连接

自链接

• 外连接： 左外连接（左连接）

右外连接（右连接）

• *全连接（很少用，了解）

3.笛卡尔积现象：

在表的连接查询方面的一种现象：当两张表进行查询的的时候，没有任何条件限制，最终的查询结果条数是两张表的乘积。

SELECT e.ename,d.dname FROM emp e , dept d ; #会出现笛卡儿积现象（56条数据）（已经被淘汰的方式sql92写法）

关于表别名，使用表别名有什么好处吗？

• 执行效率高
• 可读性好

4.避免笛卡尔积现象

答：当然是加条件了。

SELECT e.ename,d.dname FROM emp e , dept d WHERE e.deptno = d.deptno ;

思考：避免了笛卡尔积会减少记录的匹配次数吗？

不会，还是匹配一样的次数，但是只显示有效条数。（并不能提高效率）

5.内连接

1、等值连接

语法:（inner）join …on…

inner可省略，带着只为可读性好一些。两张表内连接的意思 (常用)

SELECT e.ename,d.dname FROM emp e INNER(可以省略) JOIN dept d ON e.deptno = d.deptno WHERE 过滤条件;

sql99语法：使得表的连接条件和后来的过滤条件分离了！

2、非等值连接

SELECT e.ename,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

sql99语法：表连接条件并不是一个确切的值！

3、自连接

最大特点：一张表看作两张表，自己连自己

SELECT a.ename,b.mgr admin FROM emp a JOIN emp b WHERE a.mgr = b.empno ;

6.外连接

语法格式：left|right（outer）join … on …

outer可省略，带着只为可读性好一些。两张表外连接的意思

• 左外连接（左连接）：表示左边是主表。
• 右外连接（右连接）：表示右边是主表。

左连接有右连接的写法，同样右连接也有左连接的写法 :

---

案例：查询没有员工的部门

SELECT d.* FROM dept d LEFT JOIN emp e ON e.DEPTNO = d.DEPTNO WHERE e.EMPNO IS NULL

7内连接和外连接的区别：

• 内连接：假设有AB两张表，使用内连接，凡是A表B表能够匹配上的记录查询出来，这就是内连接，AB两张表并没有主次之分，两张表是平等的。
• 外连接：假设AB两张表，使用外连接，AB两张表中的一张是主表，一张是副表，主要查询主表中的数据，捎带查询副表，当副表中的数据并没和主表中的数据匹配上，副表自动模拟出NULL与之相匹配（主表数据无条件查询）

8.三个表连接查询

案例一：查询每个员工的部门名称和薪资等级

SELECT e.ename,d.DNAME,s.grade FROM emp e

JOIN dept d ON e.DEPTNO = d.DEPTNO

JOIN salgrade s ON e.SAL BETWEEN s.losal AND s.hisal

案例二：查询每个员工的部门名称、薪资等级、上级领导

SELECT e.ENAME ‘员工’ ,d.DNAME ‘部门’ , s.grade ‘薪资等级’ , e2.ename ‘领导’

FROM emp e

JOIN dept d ON e.DEPTNO = d.DEPTNO

JOIN salgrade s ON e.SAL BETWEEN s.losal AND hisal

LEFT JOIN emp e2 ON e.MGR = e2.empno

子查询：

定义：select语句中嵌套select语句，被嵌套的select语句是子查询。

子查询可以出现在：

select

…(select)…

from

…(select)…

where

…(select)…

1.where后面嵌套子查询

select * from emp where sal > (select avg(sal) from emp) ;

2.from后面嵌套子查询*

SELECT d.dname,AVG(s.grade) ‘部门平均薪资’

FROM emp e

JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

JOIN dept d ON e.deptno = d.deptno

GROUP BY d.dname

3.在select 后面嵌套子查询

找出每个员工所在的部门名称

写法一：看作临时表

SELECT t.ename,d.dname

FROM dept d JOIN (SELECT e.ename,e.deptno FROM emp e) t ON t.deptno = d.deptno

写法二：连接查询

SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno

写法三：select后嵌套子查询

SELECT e.ename,(SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) AS dept FROM emp e ;

4.union

可将查询结果集相加

SELECT ename,job FROM emp e WHERE job = ‘CLERK’

UNION

SELECT ename,job FROM emp e WHERE job = ‘SALESMAN’

而且两张不相干的表中数据也能够因此拼接在一起。（但是列数保持一致）

SELECT ename FROM emp

UNION

SELECT dname FROM dept

5.*limit

重点中的重点，分页查询全靠它

1.limit是mysql中特有的，其他数据库中没有，但是Oracle中有一个相同的机制叫rownum。

2.limit取结果集中的部分数据

3.语法机制：limit startIndex，length

startindex表示起始位置，从0开始，0表示第一条数据。

length表示取几个。

4.limit是sql语句中最后一个执行的环节。

（当前页数-1）* 页的大小 = 当前页起始下标

三.DDL和DML

---

1.创建表：

char 和 varchar 如何选择？

• char 适用于数长度不会发生改变的时候，是定长的，例如：性别，生日
• varchar 当一个字段数据长度不确定，例如：简介、姓名、等都是采用varchar

char是直接开辟一定长度的空间，varchar是根据存储数据的长度动态的开辟空间！

测试案例

#创建表

CREATE TABLE user(

id INT(10),

username VARCHAR(30),

PASSWORD VARCHAR(30) #最后一行不需要,

);

2.删除表：

drop table 表名；（通用）

drop table 表名 if exists 表名； //Oracle不支持这种写法。

使用drop删除表，就像是我们把表中的数据擦干净，但是表的结构还在，可以通过rollback回滚过来数据！

我们如果需要删除大表的话，就需要truncate 来删除，会清空所有数据且执行速度很快，并且永久删除，数据不可恢复！

truncate table name

删除表常用：

drop table if exist 表名（如果存在某个表删除该表）。

3.修改表结构：

用工具：哈哈！设计表选项

（因为在实际开发中表设计好以后，对表结构进行修改是很少的。并且修改表中结构的语句并不会写在java语句当中。）

4.insert：表中插入数据

语法：insert into 表名（字段1，字段2，字段3…）values(value1 , value2 , value3) ;

INSERT INTO dept(deptno,dname,loc)VALUES(50,‘Java开发’,‘ZHENGZHOU’);

需要注意的地方：当一条insert语句执行成功之后，表格必然会多一行记录，及时多的一行记录当中某些字段为null，后期也没办法再通过insert插入数据了，只能通过update更新 ！

**一次插入多行数据：**insert into 表名（字段1，字段2，字段3）values（value1，value2，value3），（value1，value2，value3）

INSERT INTO dept(deptno,dname,loc)VALUES(70,‘大数据开发’,‘ZHENGZHOU’),(60,‘运维部’,‘KAIFENG’);

5.表的复制

了解一下即可！

CREATE TABLE mytable AS SELECT * FROM dept #将将查询结果当作表创建出来。

将查询出来的结果插入道一张表中

INSERT INTO mytable SELECT * FROM dept

6.update：修改表中数据

语法格式：update 表名 set 字段名1 = 值1 ，字段名2 = 值2 …where 条件 ；

注意：没有条件整张表的数据全部更新 ；

UPDATE dept SET dname = ‘测试开发’ ,loc = ‘HUEL’ WHERE DEPTNO = 10 ; #就1个set！

7.delete删除表中数据；

语法格式：delete from 表名 where 条件 ；

注意：没有条件表中数据全部清空！

DELETE FROM dept WHERE deptno = 70 #删除部门号为70的数据

DELETE FROM dept #清空表中数据

8.增删改查有一个术语：CRUD操作

• Create(增加)
• Retrieve(查询)
• Update(修改)
• Delete(删除)

四.约束

---

创建表的时候可以给字段添加相应的约束，约束的目的：保证表中数据的合法性，唯一性，有效性。

1. 非空约束（not null）：约束字段不能为NULL
2. 唯一约束（unique）：约束字段不能重复
3. 主键约束（primary key）：约束字段既不能为NULL也不能重复
4. 外键约束（foreign key）：阿里巴巴手册，已经禁止使用外键了
5. 检查f约束（check）：Oracle数据库有check约束，但是Mysql没有。

注意：我们主要还是记住英文，平时使用的都是英文！

1.非空约束 not null

CREATE TABLE mytable(

id INT(10),

username VARCHAR(30) NOT NULL, #添加非空约束

PASSWORD VARCHAR(30)

);

测试

insert into mytable(id,password) values(1,‘123’) ; #由于我们的的用户字段要求非空，所以会执行失败

ERROR 1364(HY000) : Filed ‘username’ doesn’t have a default value

2.唯一约束(unique)

列级约束

表级约束

测试

3.主键约束(primary key) *

必须记住：一张表的主键约束只有一个 ；最好与别的字段不相关，仅仅代表唯一标识 ！

CREATE TABLE mytable(

id INT(10) PRIMARY KEY, #添加主键约束【属于列级约束】

username VARCHAR(30),

email VARCHAR(30)

);

测试

【表级约束】

主键值自增：在primary key后面添加auto_increment（从1开始，递增）；

了解：Oracle也提供一个自增序列叫做序列（sequence）；

4.外键约束（foreign key）

关于外键约束的相关术语

• 外键约束:foreign key
• 外键字段:添加有外键约束的字段
• 外键值:外键字段中的每一个值。

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

顺序要求:

• 删除数据的时候,先删除子表,再删除父表。
• 添加数据的时候,先添加父表，在添加子表。
• 创建表的时候,先创建父表,再创建子表。
• 删除表的时候,先删除子表,在删除父表。

使用外键【Alibaba规范不推荐使用】

create table t_class(

cno int ,

cname varchar(255) , #被引用：父表

primary key(cno)

);

create table t_student(

sno int ,

sname varchar(255) , #子表

classno int ,

foreign key(classno) reference t_class(cno)

);

#t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表

• reference是引用；
• 外键可以为NULL；
• 此时classno中的数据来自cno；
• 外键引用的字段不一定是主键，但是起码是被unique约束；

五.存储引擎

---

表在数据库中的存储方式。

存储引擎只存在mysql中，（Oracle中有对应机制，但是不叫存储引擎）。

完整的建表语句:

CREATE TABLE mytable(

id INT(10) PRIMARY KEY,

username VARCHAR(30) NOT NULL,

PASSWORD VARCHAR(30)

)ENGINE = InnoDB Default CAHRSET=UTF8;

注意：在MYSQL当中，凡是标识符可以使用飘号括起来，最好别用，不通用！

建表的时候可以指定存储引擎，也可以指定字符集。

mysql默认的存储引擎是InnoDB的方式！默认采用的字符集是UFT-8

mysql> show engines \G #查看当前，mysql版本支持的存储引擎命令! 当前MYSQL版本是5.7.33，支持9种存储引擎！

1.常见的存储引擎：

• MyISAM 存储索引
• InnoDB 存储索引
• MEMORY 存储索引

2.MyISAM 存储引擎:

MyISAM:这种存储引擎不支持事务。是最常用的但不是默认的

Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO #不支持事务！

XA: NO

Savepoints: NO

它管理的表具有以下特征:

使用三个文件表示每个表 ：

• 格式文件 — 存储表的结构（mytable.frm）
• 数据文件 — 存储表的数据(mytable.MYD)
• 索引文件 — 存储表中索引的文件（mytable.MYI）

优点：可被转换为压缩，来节省空间，并且可以转换为只读表，提高检索效率！

缺点：不支持事务！

3.InnoDB 存储引擎:

支持事务，行级锁，外键等 ；这种存储引擎安全。

Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES

XA: YES

Savepoints: YES

• 表的结构存储在xxx.frm文件当中
• 数据存储在tablespace这样的表空间当中（逻辑概念），无法被压缩，无法被转换为只读！
• 这种InnoDB存储引擎在MYSQL数据库崩溃之后提供自动恢复机制！【事务、安全、重量级】
• 支持级联删除和级联更新。

4.MEMORY 存储引擎:

所有数据保存在内存中,断电即失，但是查询速度超级快！

Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO

XA: NO

Savepoints: NO

• 在数据库目录当中，每个表均已.frm格式的文件表示
• 表数据以及索引被存储在内存当中
• 表级锁机制
• 不能包含TEXT（CLOB）或BLOB字段
• 查询速度快

六.事务（Transaction）*

---

1、什么是事务

一个事务是一个完整的业务逻辑单元，不可再分。

比如：银行转账，从A账户向B账务转账10000，需要执行两条update语句

update t_act set balance = balance - 10000 where actno = ‘act-001’ ;

update t_act set balance = balance + 10000 where actno = ‘act-002’ ;

以上两条DML语句必须同时成功，或同时失败，不允许一条成功，一条失败！

想要保证以上的两条DML语句（update、insert、delete）同时成功或者同时失败，那么就需要使用数据库的“事务机制” ！

2、事务原理

假设一个事儿，需要先执行一条insert，再执行一条update，最后执行一条delete，这个事儿才算完成。

开启事务机制（开始)

• 执行insert语句–>insert.(这个执行成功之后，把这个执行记录到数据库的操作历史当中，并不会向文件中保存-条数据，不会真正的修改硬盘上的数据。)
• 执行update语句—> update…(这个执行也是记录一下历史操作，不会真正的修改硬盘上的数据)
• 执行delete语句---->delete…(这个执行也是记录一下历史操作【记录到缓存】，不会真正的修改硬盘上的数据)

提交事务或者回滚事务（结束）

怎么提交事务，怎么回滚事务？

提交事务：commit；语句

回滚事务：rollback；语句==（回滚永远都是只能回到上次事务的提交点！）==

MySQL默认的事务行为是怎么样的？

MySQL默认情况下是支持自动提交事务的。（自动提交）：就是一条DML提交一次！

怎么样才能将MySQL的自动提交事务关闭呢？

先执行如下命令：start transaction ,开启事务，也是将自动提交事务关闭！

在次命令后的指定的DML语句并不会持久化到数据库，等待commit | rollback 操作！

3、事务的特性：ACID

A：原子性（atomicity）：事务是最小的工作单元，不可再分。

C：一致性（consistency）：事务必须保证多条DML语句同时成功或同时失败。

I ：隔离性（isolation）：事务A与事务B之间具有隔离。

D：持久性（durability）：持久性说的时最终的数据必须持久化道硬盘文件中，事务才算成功的结束。

4、事务的隔离性

将事务A与事务B分别比作教室A，B，然而它们之间的墙就是隔离性，墙越厚隔离级别越高！（这种墙分为四个级别）

• 第一级别：读未提交（read uncommitted）：我们当前事务可以读取对方未提交的事务，

存在问题：存在脏读（Dirty read）现象，表示读到了脏的数据。

• 第二级别：读已提交（read committed）：我们当前事务可读取对方已提交的事务，解决了脏读现象，存在问题：不可重复读。
• 第三级别：可重复读（repeatable read）：解决不可重复读，永远读取到的都是开启事务时的数据，==存在问题：读取到的数据是幻像（幻读）==例如：执行一条查询的sql需要从1点3点，那么这条sql读取的一直是1点的数据，即使13点之间存在其他操作，该sql的结果并不会受到影响，类似快照，一直读取的是快照中的数据。
• 第四级别：序列化读/串行化读（serializable）：解决了所有问题。 效率低，需要事务排队（类似于synchronized）。

Oracle数据库默认级别：第二级别；读已提交

Mysql数据库默认级别：第三级别，可重复读

5、验证各种隔离级别

对以上各种隔离级别做一个验证

通过命令 select @@tx_isolation；查看当前事物的隔离级别 !

±----------------+
| @@tx_isolation |
±----------------+
| REPEATABLE-READ |
±----------------+

读未提交（read uncommitted）：

#执行如下命令，设置全局的事务隔离级别为读未提交

set global transaction isolation level read uncommitted;

读已提交（read committed）：

测试 读已提交（read committed）

#执行如下命令，设置全局的事务隔离级别为读已提交

set global transaction isolation level read committed;

可重复读（repeatable read）：

测试：可重复读（repeatable read）

#执行如下命令，设置全局的事务隔离级别为可重复读

set global transaction isolation level read committed;

序列化读/串行化读（serializable）：

测试 序列化读/串行化读（serializable）

#执行如下命令，设置全局的事务隔离级别为可重复读

set global transaction isolation level serializable;

七.索引 *

---

1、索引的概述

索引相当于一本书的目录，通过目录可以快速找到对应的资源。

作用：提高检索效率，缩小扫面范围。

注意：索引虽然提高效率但是依旧不能随意添加，因为索引也是数据库中的对象，也是需要数据库不断的维护的，有维护成本，表中数据经常修改就不适合，因为一旦修改索引需要重新排序，进行维护。

2、怎么创建索引对象

语法格式：create index 索引名称 on 表名 （字段名）。

eg：create index emp_sal_index on emp(sal);

3、怎么删除索引对象

语法格式：drop index 索引名称 on 表名；

4、什么时候给字段添加索引

• 数据量庞大。（根据客户需求，根据线上的环境。）
• 该字段很少的DML操作。
• 该字段经常出现在where子句中。

5、sql语句的执行计划

主键和具有unique约束的字段自动添加索引，根据主键查询效率较高，尽量根据主键检索。

==explain+sql语句可以解释该语句。（执行计划）==可以通过此命令判断sql是否走了索引！

6、索引底层采用的数据结构是：b+tree

索引的实现原理

案例分析

7、索引分类

• 单一索引：给单个字段添加索引
• 复合索引：给多个字段联合起来添加一个索引
• 主键索引：主键上会自动添加索引
• 唯一索引：有unique约束的字段上会自动添加索引

8、索引什么时候失效 ?

1、模糊查询，这个时候索引失效,因为以%开始，无法使用索引！

select ename from emp where ename =‘%a%’ ;
2、使用or的时候会索引失效,因为如果or两端的字段存在一个字段不具备索引,具备索引的字段也会失效！
select ename from emp where ename =‘zhangsan’ or job = ‘Java’ ;
3、使用复合索引，没有使用左侧的列去查找，索引失效
creat index emp_job_sal_index on emp(job,sal) # 为job和sal两个字段添加复合索引
select * from emp where job = ‘java’ ; #会走索引，因为使用job列去查了
select * from emp where sal = 1800 ; #不会走索引，因为没有使用左侧的job列！
4、where当中的索引列参加了运算，索引失效。
select * from emp where sal + 1 = 1800 ;
5、在where当中索引列使用了函数
select * from emp where lower(ename) = ‘smith’ ;

hadow_50,text_Q1NETiBA54iq5rS8aW5n,size_20,color_FFFFFF,t_70,g_se,x_16)

案例分析

7、索引分类

• 单一索引：给单个字段添加索引
• 复合索引：给多个字段联合起来添加一个索引
• 主键索引：主键上会自动添加索引
• 唯一索引：有unique约束的字段上会自动添加索引

8、索引什么时候失效 ?

1、模糊查询，这个时候索引失效,因为以%开始，无法使用索引！
select ename from emp where ename =‘%a%’ ;
2、使用or的时候会索引失效,因为如果or两端的字段存在一个字段不具备索引,具备索引的字段也会失效！
select ename from emp where ename =‘zhangsan’ or job = ‘Java’ ;
3、使用复合索引，没有使用左侧的列去查找，索引失效
creat index emp_job_sal_index on emp(job,sal) # 为job和sal两个字段添加复合索引
select * from emp where job = ‘java’ ; #会走索引，因为使用job列去查了
select * from emp where sal = 1800 ; #不会走索引，因为没有使用左侧的job列！
4、where当中的索引列参加了运算，索引失效。
select * from emp where sal + 1 = 1800 ;
5、在where当中索引列使用了函数
select * from emp where lower(ename) = ‘smith’ ;