MySQL
文章目录
1. jdbc操作数据库流程
第一步:Class.forName()加载数据库连接驱动
Class.forName(driver);
第二步:DriverManager.getConnection()获取数据连接对象
connection = DriverManager.getConnection(url,username,password);
第三步:获取sql会话对象
statement = connection.createStatement();
第四步:执行SQL 处理结果集
resultSet = statement.executeQuery("select name,price from hero where id = 1");
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getInt("price"));
}
第五步:关闭结果集(先开的后关)
if (resultSet != null){ // 关闭结果集
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){ // 关闭会话
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){ // 关闭连接
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
完整代码:
package com.example.test;
import java.sql.*;
public class JDBCTest {
public static void main(String[] args) {
// 数据库驱动类名的字符串
String driver = "com.mysql.cj.jdbc.Driver";
// 数据库连接串
String url = "jdbc:MySQL://localhost:3306/hero_db";
// 用户名
String username = "root";
// 密码
String password = "root";
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
// 1.Class.forName()加载数据库连接驱动
Class.forName(driver);
// 2.DriverManager.getConnection()获取数据连接对象
connection = DriverManager.getConnection(url,username,password);
// 3.获取sql会话对象
statement = connection.createStatement();
// 4.执行SQL 处理结果集
resultSet = statement.executeQuery("select name,price from hero where id = 1");
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getInt("price"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
// 5.关闭结果集、关闭会话、关闭连接。(先开的后关)
if (resultSet != null){ // 关闭结果集
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){ // 关闭会话
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){ // 关闭连接
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
2. 关系数据库中连接池的机制是什么
前提:为数据库连接建立一个缓冲池
- 从连接池获取或创建可用连接
- 使用完毕后,把连接返回给连接池
- 在系统关闭前,断开所有连接并释放连接占用的系统资源
- 能够处理无效连接,限制连接池中的连接总数不低于或者不超过某个限定值
3. SQL的select语句完整地执行顺序
- from 子句组装来自不同数据源的数据
- where 子句基于指定的条件对记录行进项筛选
- group by 子句将数据划分为多个分组
- 使用聚集函数进行计算
- 使用 having 子句筛选分组
- 计算所有的表达式
- select 的字段
- 使用 order by 对结果集进行排序
4. MySQL的事务
4.1 事务的基本要素(ACID)
- 原子性(Atomicity):事务开始后所有的操作,要么全部做完,要么全部不做,不可能停滞在中间环节.事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没发生一样.也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位.
- 一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏.比如A向B转账,不可能A扣了钱,B却没收到.
- 隔离性(Isolation):同一时间,只允许一个事务请求统一数据,不同的事务之间彼此没有任何干扰.比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账
- 持久性(Durability):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚
4.2 事务的并发问题
- 脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
- 不可重复读:事务A多次读取同一数据,事务B在事务A多次读取过程中,对数据做了更新并提交,导致事务A多次读取同一数据时,结果不一致
- 幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后,发现还有一条数据没有改过来,就好像发生了幻觉一样,这就叫幻读
不可重复读和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除.解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表
4.3 MySQL事务隔离级别
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（read-uncommitted） | 是 | 是 | 是 |
| 不可重复读（read-committed） | 否 | 是 | 是 |
| 可重复读（repeatable-read） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
5. 行锁,表锁
| MyISAM | InnoDB | |
|---|---|---|
| 行表锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其他行有影响,适合高并发的操作 |
6. BTree 和 B+Tree 的区别
- 非叶子节点只存储键值信息
- 所有叶子节点之间都有一个链指针
- 数据记录都存放在叶子节点中
7. 简述在MySQL数据库中MyISAM和InnoDB的区别
- MyISAM存储引擎:主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用
- 不支持事务,支持表锁和全文索引,操作速度快
- InnoDB存储引擎:主要面向OLTP(Online Transaction Processing，在线事务处理)方面的应用
- 特点:行锁设计,支持外键;
8. MySQL性能优化
-
尽量选择较小的列
-
将where中用的比较频繁的字段建立索引
-
select 子句中避免使用’*’
-
避免在索引列上使用计算,not in 和<>等操作
-
当只需要一行数据的时候使用limit 1
-
保证表单数据不超过200W,适时分割表.针对查询较慢的语句,可以使用explain来分析该语句具体的执行情况
-
避免改变索引列表的类型
-
选择最有效的表名顺序,from字句中写在最后的表是基础表,将被最先处理,在from子句中包含多个表的情况下,必须选择记录条数最少得表作为基础表
-
避免在索引列上面进行计算
-
尽量缩小子查询的结果
9. SQL语句优化案例
-
where 子句中可以对字段进行null值判断吗？
可以，比如:
select id from hero where price is null;这样的sql 也是可以的。但是最好不要给数据库留NULL，尽可能的使用 NOTNULL 填充数据库，不要以为NULL不需要空间，比如char（100）型,在字段建立时,空间就固定了,不管是否插入值(NULL也包括在内),都是占用100个字符空间的,如果是varchar这样的变长字段,null不占用空间,可以在price上设置默认值0,确保表中price列没有null值,然后这样查询:
select id from hero where price = 0; -
如何优化下面的语句:
selct * from admin left join log on admin.admin_id where log.admin_id>0;优化为:
select * from (select * from admin where admin_id>0)T1 left join log on T1.admin_id = log.admin_id;使用JOIN的时候，应该用小的结果驱动大的结果(left join 左边结果尽量小,如果有条件应该放在左边先处理,right join同理反向),同时尽量把涉及到多表联合的查询拆分多个query(多个表查询效率低,容易到之后锁表和阻塞)
-
limit 的基数比较大时使用between
例如:
select * from admin order by admin_id limit 100000,10;优化为:
select * from admin where admin_id between 100000 and 100010 order by admin_id; -
尽量避免在列上做运算,这样 导致索引失效
例如:
select * from admin where year(admin_time)>2022;优化为:
select * from admin where admin_time > '2022-01-01';
10.常见面试sql
- 用一条SQL语句查询出每门课都大于80分的学生姓名
| name | curriculum | fration |
|---|---|---|
| 刘备 | 语文 | 82 |
| 刘备 | 数学 | 79 |
| 关羽 | 语文 | 70 |
| 关羽 | 数学 | 89 |
| 张飞 | 语文 | 83 |
| 张飞 | 数学 | 100 |
答1:
select distinct name
from table
where name not in (select distinct name from table where fration <=80);
答2:
select name from table group by name having min(fration)>80
- 删除除了自动编号不同，其他都相同的学生冗余信息
| id | student_id | name | curriculum_id | curriculum | fration |
|---|---|---|---|---|---|
| 1 | 2022001 | 刘备 | 0001 | java | 98 |
| 2 | 2022002 | 关羽 | 0001 | java | 88 |
| 3 | 2022001 | 刘备 | 0001 | java | 98 |
答:
delete from table
where id not in (select min(id)
from table
group by student_id,name,curriculum_id,curriculum,fration);
- 一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合.
答:
Select a.name,b.name
From team as a,team as b
Where a.name<b.name
order by a.name,b.name
- 怎么把这样一个表
| year | mouth | amount |
|---|---|---|
| 2021 | 1 | 1.1 |
| 2021 | 2 | 1.2 |
| 2021 | 3 | 1.3 |
| 2021 | 4 | 1.4 |
| 2022 | 1 | 2.1 |
| 2022 | 2 | 2.2 |
| 2022 | 3 | 2.3 |
| 2022 | 4 | 2.4 |
查成如下结果
| year | m1 | m2 | m3 | m4 |
|---|---|---|---|---|
| 2021 | 1.1 | 1.2 | 1.3 | 1.4 |
| 20022 | 2.1 | 2.2 | 2.3 | 2.4 |
答:
select year,
(select amount from table t where mouth = 1 and t.year=table.year) as m1,
(select amount from table t where mouth = 2 and t.year=table.year) as m2,
(select amount from table t where mouth = 3 and t.year=table.year) as m3,
(select amount from table t where mouth = 4 and t.year=table.year) as m4
from table group by year;
- 复制表(只复制结构,源表名：a新表名：b)
答:
SQL:
select * into b from a where 1<>1
# <>(不等于)比较两个表达式,当使用此运算符比较非空表达式时,如果左操作数不等于右操作数,则结果为 TRUE 否则,结果为 FALSE.
# where1=1，拷贝表结构和数据内容
ORACLE:
create table b as select * from a where 1=2
- 原表
| courseid | coursename | score |
|---|---|---|
| 1 | java | 70 |
| 2 | oracle | 90 |
| 3 | xml | 40 |
| 4 | jsp | 30 |
| 5 | servlet | 80 |
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
| courseid | coursename | score | mark |
|---|---|---|---|
| 1 | java | 70 | pass |
| 2 | oracle | 90 | pass |
| 3 | xml | 40 | fail |
| 4 | jsp | 30 | fail |
| 5 | servlet | 80 | pass |
答:
select courseid,coursename,score,if(score>=60,'pass','fail') as mark from table
-
给出所有购入商品为两种或两种以上的购物人记录
表名:shopping_info
| shopper | trade_name | count |
|---|---|---|
| A | 甲 | 2 |
| B | 乙 | 4 |
| C | 丙 | 1 |
| A | 丁 | 2 |
| B | 丙 | 5 |
答:
select shopper,trade_name,count
from shopping_info
where shopper in
(select shopper from shopping_info group by shopper having count(*)>=2);
-
表名:info
date result 2022-06-25 win 2022-06-25 lose 2022-06-25 lose 2022-06-25 lose 2022-06-26 win 2022-06-26 lose 2022-06-26 lose 如果要生成下列结果, 该如何写sql语句?
date win lose 2022-06-25 2 2 2022-06-26 1 2 答1:
select date, sum(case when result = 'win' then 1 else 0 end)as 'win', sum(case when result = 'lose' then 1 else 0 end)as 'lose' from info group by date;答2:
select a.date,a.result as win,b.result from (select date,count(result) as result from info where result = 'win' group by date) as a join (select date,count(result) as result from info where result = 'lose' group by date) as b on a.date = b.date;文章到这里就先结束了，后面还会持续更新，希望能帮助到各位大佬。如果文章有需要改进的地方还请各位大佬斧正。
扫一扫
8946
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
