华清远见-重庆中心-数据库阶段技术总结与面试题解析

数据库

DataBase，简称为DB

运行在操作系统上，按一定的数据结构，保存数据的仓库。是一个电子化的文件柜。

数据永久保存在硬盘中。

MySQL控制台常用命令

• 查看所有数据库

  show databases;
  

• 切换数据库

  use 数据库名;
  

• 查看当前数据库下的所有表

  show tables;
  

• 创建一个数据库

  create database 数据库名;
  

• 删除数据库

  drop database 数据库名;
  

数据库管理系统图形化管理工具

如果只是用控制台操作数据库系统不方便，所以有很多图形化的管理工具。

如navicat、datagrip、sqlyog等。

Navicat15破解

安装后关闭Navicat，再打开破解工具。

1.点击Patch，如果有安全软件提示风险操作，选择允许，等待弹出成功的界面

2.点击generate，生成注册码，复制后打开Navicat

3.在Navicat中进入注册界面，粘贴复制的注册码

点击激活后会提示失败，再点击手动激活

4.复制请求码，粘贴到激活工具中，点击左下角的generate，会自动复制注册码到Navicat中

使用

连接指定的MySQL

由于现在是自己的电脑链接自己，所以主机名是localhost，之后会连接真实的ip地址。

进入主界面后，点击左上角的连接，选择mysql，只需输入密码即可

创建数据库

在连接上点击右键，新建数据库，只需写数据库名。

切换数据库

只需双击对应的数据库即可

创建数据表

在展开后的数据库中，在表的选项上右键新建表

保存时会提示输入表名

创建表时的注意实现

• 每张表需要一个编号"id"列，通常设置为主键，目的是为了区分每条记录。整个主键列中的数据不能重复，通常还需要设置为自增

• 数据类型和所占长度根据实际情况选择

• 通过"不是null"设置能否允许该字段为空

• 如果某个字段有默认值，可以在设计表的时候设置，字符串用单引号引起来

• 最好写上注释

修改数据表

如果要对某张已存在的表进行修改，在对应的表上右键，设计表。

修改表时，要保存不影响现有数据。

SQL

Structrued Query Language 结构化查询语言

用于操作关系型数据库的一门语言。可以用来创建、维护数据库和数据。

-- mysql中的注释

操作数据库

1.创建数据库

create database 数据库名;

2.切换数据库

use 数据库名;

3.删除数据库

drop database 数据库名;

操作数据表

1.创建数据表

create table 表名(
	字段名 数据类型 [字段特征],
    字段名 数据类型 [字段特征],
    ...
    字段名 数据类型 [字段特征]
)

2.删除数据表

drop table 表名;

3.修改数据表

• 对表重命名

  alter table 旧表名 rename to 新表名;
  

• 添加新字段

  alter table 表名 add column 字段名 数据类型 字段特征;
  

• 修改字段

  alter table 表名 change 旧字段名 新字段名 数据类型 字段特征;
  

• 删除字段

  alter table 表名 drop 字段名;
  

添加约束

1.添加非空约束

alter table 表名 change 旧字段名 新字段名 数据类型 not null;

2.添加主键约束

alter table 表名 add primary key(字段名);

3.添加唯一约束

alter table 表名 add unique(字段名);

4.添加默认值约束

alter table 表名 alter 字段名 set default '默认值';

5.添加外键约束

alter table 从表表名 add foreign key(从表外键字段) references 主表(主表主键字段)

添加约束的操作通常是对已存在的表进行修改和维护时使用。如果是一张新表，最好在创建表的时候设计好约束。

建表的同时添加约束

-- 创建数据库gamedb
create database gamedb;
-- 切换数据库
use gamedb;
-- 创建游戏角色表hero
create table hero(
-- 编号 整型 非空     主键        自增 
	id    int  not null primary key auto_increment comment '编号',
-- 姓名 字符串     非空 		唯一	
	name varchar(20) not null unique comment '姓名',
-- 定位	   字符串			 非空 	
	position varchar(20) not null comment '定位',
-- 性别 字符串 非空	 默认男
	sex char(1) not null default '男' comment '性别',
-- 价格 整型 非空		 默认4800 
	price int not null default '4800' comment '价格',
-- 上架日期 	
	shelf_date date  comment '上架日期'
)

-- 创建战斗表battle
create table battle(
	hero_id int not null ,
	position varchar(20),
    -- 外键  hero_id参考hero表中的id字段
	foreign key (hero_id) references hero(id)
)

数据完整性

数据完整性是指数据精确可靠。不能保存无意义或无效的数据。

如不合理的年龄、全为空的记录、重复记录等。

为了保证保存在数据库中的数据是完整数据，就要在设计数据表时添加一些约束或特征来保证数据完整性。

MySQL中常见的数据类型

整型
tinyint	短整型	对应java中的byte和short
int	整型	对应java中的int
bigint	长整型	对应java中的long

浮点型
float	单精度浮点型	对应java中的float
double	双精度浮点型	对应java中的double
decimal(宽度,精度)	指定保留的小数位数和整体宽度	如decimal(4,2) 3.1415926–>3.14

字符串
char(长度)	定长字符串	char(10)表示占10个字符，就算保存了3个字符，也占10个字符，对应java中的String
varchar(长度)	可变字符串	varchar(10)表示最多占10个字符，保存了3个字符，旧占3个字符，对应java中的String
text	文本

日期
date	日期	yyyy-MM-dd
time	时间	HH:mm:ss
datetime	日期时间	yyyy-MM-dd HH:mm:ss
timestamp(14或8)	毫秒	保存日期的毫秒数.14表示yyyyMMddHHmmss，8表示yyyyMMdd

约束

字段特征	概念	关键字
非空约束	是否允许该字段为null	null表示可以为空，not null表示不可以为空
主键约束	主键(primary key)。用于区分表中的每条记录的一个字段。如果有现成的字段可以区分每条记录时，将该字段设置为主键字段;如果没有现成的字段可以区分每条记录时，通常会额外添加一个id字段用于设置为主键字段。通常一张表只有一个主键字段。	primary key
唯一约束	保证该字段值不能重复	unique
默认值约束	如果在不给该字段添加数据的情况下，保证该字段有一个默认值。	default
外键约束	在主从关系的表中，给从表的某个字段添加外键约束后，该字段的值只能来自于主表中的某个主键字段	foreign key references

操作数据

数据的操作，是指数据的增加create，修改update，查询read和删除delete。

简称为CURD。

数据添加insert

数据添加时，都是整行(一条记录)添加。不能只给一个字段添加数据。

如果只给某个字段添加数据，实际是修改。

给所有字段赋值

insert into 表名 values('值1','值2'...)

• 表名后无需添加字段名，添加时保证值的顺序和字段的顺序一致
• 遇到自增字段，不能省略不写，要使用0、null或default让其自动填充
• 遇到有默认值的字段，不能省略不写，要使用default让其自动填充默认值
• 遇到允许为空的字段，不能省略不写，要使用null让其设置为空

给指定字段赋值

insert into 表名(字段1,字段2...) values('值1','值2'...)

• 没有默认值的非空字段必须要写出来
• 表名后的字段顺序要和值的顺序一致

批量添加

可以用一个insert into语句添加多条记录

insert into 表名[(字段1,字段2)] values
	('值1','值2'...),
	('值1','值2'...),
	...
	('值1','值2'...)

• 可以省略表名后的字段名
• 值的顺序和字段的顺序一致
• 如果一次添加多条记录时，优先使用批量添加，效率更高

数据修改update

修改单个字段的所有值

update 表名 set 字段 = 值;

修改多个字段的所有值

update 表名 set 字段1 = '值',字段2 = '值'...

根据条件修改(where子句)

update 表名 set 字段 = '值' where 条件

指定值

update 表名 set 字段 = '值' where 字段 = '值'

指定范围

• 使用>、<、>=、<=表示范围，使用and、or、&&、||将多个条件关联

  update 表名 set 字段 = '值' where 字段
  

• 使用"字段 between 值1 and 值2"表示字段在闭区间[值1，值2]

  update 表名 set 字段='值' where 字段 between 值1 and 值2
  

• 使用!=或<>表示不等于

  update 表名 set 字段='值' where 字段<>值
  

指定集合

• 在某个集合中 in

  update 表名 set 字段='值' where 字段 in ('值1','值2'...)
  

• 不在某个集合中 not in

  update 表名 set 字段='值' where 字段 not in ('值1','值2'...)
  

空值

• 使用is null表示空

  update 表名 set 字段 = '值' where 字段 is null
  

• 使用is not null表示非空

  update 表名 set 字段 = '值' where 字段 is not null
  

模糊查询

-- 字段 like '%娜%'
-- 带有'娜'字

-- 字段 like '张%'
-- ‘张’字开头

-- 字段 like '%儿'
-- ‘儿’字结尾

-- 字段 like '%瑞_'
-- 倒数第二个字为‘瑞’

-- 字段 like '___'
-- 3个字

update 表名 set 字段 = '值' where 字段 like ‘%文字%’

数据删除delete

数据删除是删除一条或多条记录。

删除所有

delete from 表名;
-- 或
truncate table 表名;

• delete会保留自增列删除前的值，删除后再添加时，自动从删除前的值开始自增
• truncate会重置自增列的值。效率更高
• 如果要删除主从关系且设置了外键的表中的数据，如果从表中有数据，不能直接删除主表中相关数据，先删除从表中的数据后，才能删除主表中的数据

条件删除

delete from 表 where 条件

删除时的条件同修饰时的条件语句

数据查询select

查询所有字段

select * from 表名;

查询指定字段

select 字段名1,字段名2... from 表名;

字段重命名

select 字段1 as '重命名',字段2 '重命名'... from 表名;

查询指定条数

-- 查询前N条记录
select * from 表名 limit N;

-- 查询从索引N开始的M条记录
select * from 表名 limit N,M;

-- 每页显示size条，第page页
select * from 表名 limit (page-1)*size,size

去重复

select distinct 字段名 from 表名;

条件查询

where子句，语法同修改、删除时的where

select * from 表名 where 条件;

排序

select * from 表名 where 条件 order by 排序字段 [ASC/DESC],排序字段 [ASC/DESC]...

• 排序可以是升序或降序
  • 默认不写是升序asc
  • 降序需要写desc
• 排序时如果有条件，where条件写在表名之后，排序之前
• 多字段排序时，在order by之后写多个字段及排序规则，用逗号隔开
  • 按字段顺序优先排序

统计函数(聚合函数)

select 统计函数(字段名) from 表名;

函数名
count(字段名)	统计数量
sum(字段名)	求和
avg(字段名)	平均
max(字段名)	最大
min(字段名)	最小

数学相关函数

函数	作用
abs(值或字段)	绝对值
pow(值或字段)	次幂
sqrt(值或字段)	开平方
round(值或字段)	四舍五入
ceil(值或字段)	向上取整
floor(值或字段)	向下取整

字符串相关函数

函数
length(字符串或字段)	得到字符串长度
trim(字符串或字段)/ltrim(字符串或字段)/rtrim(字符串或字段)	去除字符串首尾/首/尾空格
left(字符串或字段,n)/right(字符串或字段,n)	从字符串左/右开始截取n个字符
substr(字段或字符串,start)	从start开始截取至末尾
substr(字段或字符串,start,length)	从start开始截取length个字符
lcase(字符串或字段)/ucase(字符串或字段)	转换小写/大写
instr(字符串1,字符串2)/locate(字符串2,字符串1)	得到字符串2在字符串1中出现的顺序
reverse(字符串或字段)	翻转字符串
concat(字符串1,字符串2…)	拼接所有字符串
replace(字符串或字段,旧字符串,新字符串)	将字符串中的旧字符串替换为新字符串

时间相关函数

函数
now()	当前日期时间
current_date()/curdate()	当前日期
current_time()/curtime()	当前时间
year(日期)/month(日期)/day(日期)	得到年/月/日部分
datediff(时间1,时间2)	计算时间1与时间2相隔的天数
timediff(时间1,时间2)	计算时间1与时间2相隔的时分秒
TIMESTAMPDIFF(时间单位,时间1,时间2)	计算时间1与时间2之间相隔的指定时间单位

分组

select 分组字段,统计函数 from 表名 group by 分组字段

按指定的字段进行分组，会将该字段值相同的记录归纳到同一组中。

分组通常配合统计函数使用。

如果统计函数作为条件，不能写在where之后，要写在having之后，

having子句放在分组之后。

-- 按图书作者分组，查询平均价格大于50的信息
select book_author,avg(book_price) from book_info 
group by book_author
having avg(book_price)>50

group_concat()函数

将分组后的数据拼接成字符串。

group_concat(字段1或字符串,字段2或字符串...)

select group_concat(字段1,字段2...) from 表名 group by 分组字段
-- 根据图书类型分组，查看每组下的图书名和作者
select group_concat(book_name,'--',book_author) from book_info group by type_id

实际业务对应sql

登录

接收账号密码查询用户表中是否存在记录

select * from 表 where 账号=? and 密码=?

如果能查询到数据，说明存在该用户，可以登录；如果查询结果为Null，说明用户名或密码错误

注册

接收账号密码，判断是否存在该账号，不存在则添加到用户表中

select * from 表 where 账号=?

insert into 表 values(null,账号,密码)

充值

登录成功后，修改余额字段

update 表 set 余额=余额+充值金额 where 用户编号=?

删除

逻辑删除

不删除数据，只是不显示数据

可以给表中添加"是否删除"字段，用0表示未删除，用1表示已删除。

如果删除数据时，执行修改操作，将"是否删除"字段的值为1。

update 表 set deleted=1 where 用户编号=?;

最终在查询所有时，加上"是否删除"=0条件

select * from 表 where deleted=0;

物理删除

将数据真正删除

delete from 表 where 用户编号=?

连接查询

交叉连接、笛卡尔积

将两张表中的数据两两组合，得到的结果就是交叉连接的结果，也称为笛卡尔积

集合A：{a,b}

集合B：{1,2,3}

集合A x 集合B={a1,a2,a3,b1,b2,b3}

select * from 表1,表2;
select * from 表1 cross join 表2;
select * from 表1 inner join 表2;

将两张表中的数据互相组合成一张新表，其中有很多无效数据。

内连接

select * from 表1,表2 where 表1.字段=表2.字段;
select * from 表1 inner join 表2 on 表1.字段=表2.字段;

-- 如查询图书类型表(类型编号、类型名称)和图书详情表(图书编号、类型编号、图书名称)
select * from 图书类型表 t1 ,图书详情表 t2 where t1.类型编号=t2.类型编号;
select * from 图书类型表 t1 inner join 图书详情表 t2 on t1.类型编号=t2.类型编号;

• 通常是通过主表的主键字段关联从表的外键字段
• 如果两张表中关联的字段名一致，一定要通过"表名.字段名"进行区分，通常给表重命名
• 如果使用inner join，带条件时需要加入where子句；如果使用,隔开各个表，带条件时使用and拼接条件
• 内连接只会显示两张表中有关联的数据

左连接

-- 在保证左表数据显示完整的情况下，关联右表中的数据，没有关联的数据用null表示
select * from 表1 left join 表2 on 表1.字段=表2.字段;
-- 以上语句中表1称为左表，表2称为右表,会完整显示表1中的数据

右连接

-- 在保证右表数据显示完整的情况下，关联左表中的数据，没有关联的数据用null表示
select * from 表2 right join 表1 on 表1.字段=表2.字段;
-- 以上语句中表1称为左表，表2称为右表，会完整显示表2中的数据

嵌套查询

将查询出的结果继续嵌套使用在另一个查询语句中

-- 查询大于平均价格的图书
select * from 表 
where price >(select avg(price) from 表)

-- 根据作者分组，查询每组大于平均价格的图书
select * 
from 表 t1,(select author,avg(price) avg_price from 表)t2
where t1.author=t2.author and price>avg_price

Jar

以.jar为后缀的文件，称为Java归档文件，保存的是Java的字节码.class文件。

在Java程序中导入某个.jar文件，就能使用其中的.class文件。

在Java项目中使用.jar文件

1.创建一个Java项目，最好新建一个文件夹，将.jar文件保存在其中

2.在.jar文件上右键，点击“add as library”

JDBC

Java Database Connectivity Java数据库连接

用于Java程序连接不同的数据库。

实际在Java中定义的相关数据库连接时所需的接口，不同的数据库对其进行了实现。

核心接口

• Connection:用于设置连接的数据库的地址、账号、密码
• PreparedStatement:用于预处理、执行sql语句
• ResultSet:用于接收查询后的数据

以上接口都来自于java.sql包中

数据查询

import java.sql.*;

public class JDBCTest {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //连接mysql数据库,实现查询功能
        //连接对象
        //Connection conn;
        //sql预处理对象
        //PreparedStatement pst;
        //结果集对象
        //ResultSet rs;

        //查询的流程
        //1.加载mysql驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.连接指定的mysql
        String url="jdbc:mysql://localhost:3306/gamedb?serverTimezone=Asia/Shanghai";
        String username="root";
        String password="root";
        Connection conn=DriverManager.getConnection(url,username,password);
        //3.构造sql语句
        String sql="select * from hero";
        //4.预处理sql语句
        PreparedStatement pst=conn.prepareStatement(sql);
        //5.执行查询的方法
        ResultSet rs =pst.executeQuery();
        //6.循环读取数据
        while(rs.next()){//rs.next()方便表示读取且判断是否还有后续数据
            //获取读取到的数据  rs.get数据类型(int columnIndex)根据字段索引获取值  rs.get数据类型(String columnName)根据字段名获取值
            int id = rs.getInt(1);
            String name = rs.getString("name");
            String sex = rs.getString("sex");
            String position = rs.getString("position");
            int price = rs.getInt("price");
            String shelf_date = rs.getString(6);
            System.out.println(id+"--"+name+"--"+sex+"--"+position+"--"+price+"--"+shelf_date);
        }
        //7.释放与mysql的连接资源
        rs.close();
        pst.close();
        conn.close();
    }
}

数据增删改

public void addHero(Hero hero) throws SQLException, ClassNotFoundException {
    //1.加载驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2.获取连接对象
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/gamedb?serverTimezone=Asia/Shanghai", "root", "root");
    //3.构造sql,参数使用?占位
    String sql = "insert into hero values(null,?,?,?,?,curdate())";
    //4.预处理sql
    pst = conn.prepareStatement(sql);
    //给?赋值
    pst.setString(1,hero.getName());
    pst.setString(2,hero.getPosition());
    pst.setString(3,hero.getSex());
    pst.setInt(4,hero.getPrice());
    //5.调用更新的方法
    int i = pst.executeUpdate();
    //6.判断执行结果
    if(i>0){
        System.out.println("添加成功");
    }else{
        System.out.println("添加失败");
    }
    //7.释放资源
    pst.close();
    conn.close();
}

SQL注入

在构造sql语句时，如果使用字符串拼接的方式构造动态sql，可能会造成sql注入的风险，导致执行不是预期的sql语句

-- 如删除的sql：String sql = "delete from 表 where id="+参数;
-- 实际传值时，参数为   ''or 1=1   会导致sql变成
delete from battle where id= ''or 1=1
-- 这样就会删除所有数据


-- 查询时的sql:  "select * from 表 where name="+参数+" and pwd= "+参数

-- 实际传值时，第一个参数为   'or 1=1 --   会导致sql变成
select * from hero where id=''or 1=1 -- ' and name='亚索'
-- 这样会查询出所有数据

使用数据库帮助类简化JDBC操作

DBUtil类

package com.hqyj.util;

import java.sql.*;

/*
 * 数据库工具类
 * 连接方法
 * 释放资源方法
 * */
public class DBUtil {

    //连接数据库所需字符串
    private static String url = "jdbc:mysql://localhost:3306/bookdb?serverTimezone=Asia/Shanghai";
    private static String username = "root";
    private static String password = "root";

    /*
     * 静态代码块
     * 加载驱动
     * */
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动异常" + e);
        }
    }

    /*
     * 静态方法
     * 获取连接
     * */
    public static Connection getConn() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            System.out.println("连接数据库异常" + e);
        }
        return connection;
    }

    /*
     * 静态方法
     * 释放资源
     * */
    public static void release(Connection conn, PreparedStatement pst, ResultSet rs) {
        try {
            if(conn!=null){
                conn.close();
            }
            if(pst!=null){
                pst.close();
            }
            if(rs!=null){
                rs.close();
            }
        }catch (SQLException e){
            System.out.println("释放资源异常"+e);
        }
    }
}

实体类BookInfo

package com.hqyj.entity;

/*
 * book_info表对应的实体类
 * */
public class BookInfo {
    private int bookId;
    private String bookName;
    private String bookAuthor;
    private int bookPrice;
    private int bookNum;
    private String publisherDate;

    /*
     * 全参数构造方法
     * 用于查询时创建对象
     * */
    public BookInfo(int bookId, String bookName, String bookAuthor, int bookPrice, int bookNum, String publisherDate) {
        this.bookId = bookId;
        this.bookName = bookName;
        this.bookAuthor = bookAuthor;
        this.bookPrice = bookPrice;
        this.bookNum = bookNum;
        this.publisherDate = publisherDate;
    }

    /*
     * 不含id和出版时间的构造方法
     * 用于添加时创建对象
     * */
    public BookInfo(String bookName, String bookAuthor, int bookPrice, int bookNum) {
        this.bookName = bookName;
        this.bookAuthor = bookAuthor;
        this.bookPrice = bookPrice;
        this.bookNum = bookNum;
    }

    @Override
    public String toString() {
        return "BookInfo{" +
                "bookId=" + bookId +
                ", bookName='" + bookName + '\'' +
                ", bookAuthor='" + bookAuthor + '\'' +
                ", bookPrice=" + bookPrice +
                ", bookNum=" + bookNum +
                ", publisherDate='" + publisherDate + '\'' +
                '}';
    }

    public int getBookId() {
        return bookId;
    }

    public void setBookId(int bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookAuthor() {
        return bookAuthor;
    }

    public void setBookAuthor(String bookAuthor) {
        this.bookAuthor = bookAuthor;
    }

    public int getBookPrice() {
        return bookPrice;
    }

    public void setBookPrice(int bookPrice) {
        this.bookPrice = bookPrice;
    }

    public int getBookNum() {
        return bookNum;
    }

    public void setBookNum(int bookNum) {
        this.bookNum = bookNum;
    }

    public String getPublisherDate() {
        return publisherDate;
    }

    public void setPublisherDate(String publisherDate) {
        this.publisherDate = publisherDate;
    }
}

数据操作类BookInfoDao

package com.hqyj.dao;

import com.hqyj.entity.BookInfo;
import com.hqyj.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*
 * book_info表的操作类
 * */
public class BookInfoDao {
    //定义成员变量，用于操作数据库的3个接口
    Connection conn;
    PreparedStatement pst;
    ResultSet rs;

    /*
     * 查询所有
     * */
    public List<BookInfo> queryAll() {
        //创建集合
        ArrayList<BookInfo> list = new ArrayList<>();
        try {
            //通过数据库工具类获取连接
            conn = DBUtil.getConn();
            //预处理sql
            pst = conn.prepareStatement("select * from book_info");
            //调用查询
            rs = pst.executeQuery();
            //遍历结果集
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(3);
                String author = rs.getString(4);
                int price = rs.getInt(5);
                int num = rs.getInt(6);
                String time = rs.getString(7);
                BookInfo bookInfo = new BookInfo(id, name, author, price, num, time);
                list.add(bookInfo);
            }
        } catch (Exception e) {
            System.out.println("查询所有异常" + e);
        } finally {
            DBUtil.release(conn, pst, rs);
        }
        return list;
    }

    /*
     * 根据id查询
     * */
    public BookInfo queryById(int bookId) {
        try {
            //通过数据库工具类获取连接
            conn = DBUtil.getConn();
            //预处理sql
            pst = conn.prepareStatement("select * from book_info where book_id=?");
            //给?赋值
            pst.setInt(1, bookId);
            //调用查询
            rs = pst.executeQuery();
            //遍历结果集
            if (rs.next()) {
                String name = rs.getString(3);
                String author = rs.getString(4);
                int price = rs.getInt(5);
                int num = rs.getInt(6);
                String time = rs.getString(7);
                BookInfo bookInfo = new BookInfo(bookId, name, author, price, num, time);
                return bookInfo;
            }
        } catch (Exception e) {
            System.out.println("根据id查询异常" + e);
        } finally {
            DBUtil.release(conn, pst, rs);
        }
        return null;
    }

    /*
     * 添加
     * */
    public boolean insert(BookInfo bookInfo) {
        conn = DBUtil.getConn();
        try {
            pst = conn.prepareStatement("insert into book_info values(null,1,?,?,?,?,curdate())");
            pst.setString(1, bookInfo.getBookName());
            pst.setString(2, bookInfo.getBookAuthor());
            pst.setInt(3, bookInfo.getBookPrice());
            pst.setInt(4, bookInfo.getBookNum());
            return pst.executeUpdate() > 0;
        } catch (SQLException e) {
            System.out.println("添加异常" + e);
        } finally {
            DBUtil.release(conn, pst, rs);
        }
        return false;
    }

    /*
     * 修改
     * */
    public boolean update(int bookId, int newPrice, int newNum) {
        conn = DBUtil.getConn();
        try {
            pst = conn.prepareStatement("update book_info set book_price=? ,book_num=? where book_id=?");
            pst.setInt(1, newPrice);
            pst.setInt(2, newNum);
            pst.setInt(3, bookId);
            return pst.executeUpdate() > 0;
        } catch (Exception e) {
            System.out.println("修改异常" + e);
        } finally {
            DBUtil.release(conn, pst, rs);
        }
        return false;
    }

    /*
     * 删除
     * */
    public boolean delete(int bookId) {
        conn = DBUtil.getConn();
        try {
            pst = conn.prepareStatement("delete from book_info where book_id=?");
            pst.setInt(1, bookId);
            return pst.executeUpdate() > 0;
        } catch (Exception e) {
            System.out.println("删除异常" + e);
        } finally {
            DBUtil.release(conn, pst, rs);
        }
        return false;
    }
}

实体关系模型

实体Entity：一个表就是一个实体。

关系Relationship：实体与实体之间的关系。

实体关系模型也称为ER模型。

用图形表示ER模型时，这个图就称为ER图。

用矩形表示实体，用椭圆形表示实体的属性，用菱形表示实体之间的关系，用直线连接各个图形。

实体之间的关系

一对一

实体A与实体B之间唯一对应。

如一个国家有一个领导人，一个人对应一个配偶。

在数据库中创建表的过程

可以使用一张表实现，但如果使用一张表保存时，后期维护扩展时较为不便。

最好使用两张表实现。

• 创建国家表

  create table country(
  	country_id int not null primary key auto_increment,
      country_name varchar(20) not null,
      country_population bigint not null,
      country_area int not null
  )
  

• 创建领导人表

  create table leader(
  	leader_id int not null primary key auto_increment,
      leader_name varchar(20) not null,
      leader_birthday date not null
  )
  

这时两张表是独立的，并不能体现一对一的管理关系，可以通过以下方式体现一对一。

• 方式一：

  创建一个管理表，保存两张表中的主键，添加唯一约束

  create table relation(
  	relation_no int not null primary key auto_increment,
      leader_id int not null unique,
      country_id int not null unique,
      foreign key (leader_id) references leader(leader_id),
      foreign key (country_id) references country(country_id),
  )
  

• 方式二(建议使用)：

  在以上的两个实体表中，选择任意一张表中添加一列，保存另一张表的主键，添加唯一约束

  create table leader(
  	leader_id int not null primary key auto_increment,
      leader_name varchar(20) not null,
      leader_birthday date not null,
      country_id int not null unique,
      foreign key (country_id) references country(country_id) 
  )
  

一对多/多对一

一对多：一个实体A对应多个实体B，一个实体B不能对应多个实体A

多对一：多个实体B对应一个实体A，多个实体A不能对应一个实体B

如一个人有多辆车，一辆车不能对应多个人；

一个教官训练多个学员，一个学员不能对应多个教官；

在数据库中创建表的过程

• 创建主表(一)

  create table coach(
  	coach_id int not null primary key auto_increment,
      coach_name varchar(20) not null,
      coach_leave varchar(20) not null
  )
  

• 创建从表(多)，在表中添加字段关联主表中的主键字段

  create table student(
  	stu_id int not null primary key auto_increment,
      stu_name varchar(20) not null,
      stu_phone varchar(20) not null，
      coach_id int not null,
      foriegn key (coach_id) references coach(coach_id)
  )
  

多对多

一个实体A可以对应多个实体B，一个实体B也可以对应多个实体A。

如一个学生可以学习多门课程，一门课程也可以对应多个学习它的学生。

一个医生可以有多个病人，一个病人也可以对应多个医生。

在数据库中创建表的过程

• 创建课程表

  create table course(
  	course_id int not null primary key auto_increment,
      course_name varchar(20) not null,
      course_score int not null
  )
  

• 创建学生表

  create table student(
  	stu_id int not null primary key auto_increment,
      stu_name varchar(20) not null,
      stu_phone varchar(20) not null
  )
  

• 体现多对多关系的表：成绩表

  该表中必须要包含以上两个实体表中的主键字段

  create table score(
  	s_id int not null,
      course_id int not null,
      stu_id int not null,
      cj int not null,
      -- 可以选择添加外键约束
      foreign key (course_id) references course(course_id),
      foreign key (stu_id) references student(stu_id)
  )
  

总结

一对一：创建各自的实体表，在任意一张表中添加另一张表的主键字段，并添加唯一约束

一对多/多对一：先创建主表(一)，再创建从表(多)，在从表中添加主表中的主键字段，外键可选

多对多：创建各自的实体表，再创建第三张表：“联系表”，在"联系表"中添加两个实体表中的主键字段，外键可选

数据库设计规范

数据库设计的规范，简称为范式(NF)。

范式分为第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、BC范式(BCNF)、第四范式(4NF)和第五范式(5NF)共六种。

这六种范式的级别越高，表示数据库设计的结果越规范，每一个高级版的范式都包含了低级别的范式。

通常设计数据库时，只需满足3NF即可。

如有如下数据，黄色背景为联合主键，暂时不满足任何范式。

当前表如果要做增删改查的操作，会有如下问题：

• 如果要加入一个新的系别，就要添加学生，系主任等数据
• 如果要删除"刘定宇"，他所在的系也会被删除
• 如果要将"王海"的系别改为会计，相应的系主任也要修改
• 当前表中有大量冗余数据

第一范式1NF

数据表中的每一列都是不可再分的原子项。

关系型数据库起码要满足1NF，才能创建表。

第二范式2NF

在满足1NF的基础上，消除部分依赖。

对于联合主键而言，每一个非主属性字段都需要完全依赖于主属性，而不是依赖其中一部分。

第三范式3NF

在满足2NF的基础上，消除传递依赖。

名词解释

主键/主码/主属性

用于唯一区分每条记录的一个字段，称为主键字段，如学号、身份证号。

通常选择能唯一且几乎不会改变的某个字段作为主键。如果没有，自定义一个id列作为主键。

联合主键

如果一个字段不能唯一区分每条记录，而是需要多个字段才能唯一区分时，这些字段一起组成了联合主键。

完全依赖

如果能通过A和B得到C，A和B都不能单独得到C时，称为C完全依赖于A和B。

如(学号+科目)联合主键->分数，分数完全依赖于联合主键。

其他字段完全依赖于学号。

部分依赖

通过A和B可以得到C，单独通过A或B也可以得到C，称为C部分依赖于A和B。

如(学号+科目)联合主键->姓名，其实只通过学号也可以得到姓名，称为姓名部分依赖于联合主键。

除了成绩外，其余字段都是部分依赖于(学号+科目)联合主键

2NF就是消除这种部分依赖，只保留完全依赖。

传递依赖

如果通过A可以得到B，通过B得到C，称为C传递依赖于A。

如学号->系->系主任，其实只通过系也能得到系主任，称为系主任传递依赖于学号。

3NF就是消除这种传递依赖。

行列转换

select stu_id,stu_name,
sum(if(c_name='高等数学',cj,null)) as '高等数学',
sum(if(c_name='思修',cj,null)) as '思修',
sum(if(c_name='大学英语',cj,null)) as '大学英语',
sum(if(c_name='大学体育',cj,null)) as '大学体育'
from score
group by stu_id

视图View

视图可以当做数据库中的一个临时表，保存一些较为复杂的查询后的结果，

之后可以直接通过该视图查询数据，不需要再次编写复杂的sql语句。

视图同时可以隐藏一些查询细节，定制查询数据。

创建视图

create view 视图名 as
查询的sql语句;

使用视图

select * from 视图名

修改视图中的数据，会直接修改原始表中的数据

删除视图

drop view 视图名;

-- 查询平均分最高的课程名及其授课教师

-- 创建视图
create view myview as
select s.c_id,avg(cj)avg_cj from score s,course c where s.c_id = c.c_id
group by s.c_id

-- 视图可以当做表使用
select c_name,t_name from teach t2,teacher t1,course c,
(select c_id from myview where avg_cj=(select max(avg_cj) from myview))t
where t1.t_id=t2.t_id and t.c_id=t2.c_id and c.c_id=t2.c_id

-- 删除视图
drop view myview;

事务transaction

事务是由一组sql语句组成的执行单元，这些sql之间一般都相互依赖。

这个执行单元要么全部执行，要么全部不执行。

转账

1.select * from 表 where id =1 and money>=1000

2.update 表 set money=money-1000 where id=1

3.update 表 set money=money+1000 where id=2

以上的所有sql组成了一个转账的事务。

事务的特性ACID

原子性Atomicity

事务是最小的执行单元

一致性Consistency

事务执行前后，必须让所有数据保持一致状态。(总体数据守恒)

隔离性Isolation

事务并发时相互隔离，互不影响

持久性Durability

事务一旦提交，对数据的改变是永久的

事务的使用

提交：commit

回滚：rollback

mysql中事务是默认自动提交的。

查看事务自动提交开启状态：select @@autocommit 1表示开启自动提交 0表示关闭自动提交

设置事务不自动提交：set @@autocommit=0

如果关闭了事务自动提交，在执行某个事务途中，如果出错，可以使用rollback进行回滚，让数据回到事务执行之前的状态。

如果不出错，通过commit提交事务，一旦提交事务，无法进行回滚。

手动提交/回滚事务

1.关闭事务自动提交：set @@autocommit=0

2.开启事务：start transaction;

3.事务要执行的sql；

4.没有提交之前，如果要回滚，使用rollback;

5.如果要提交，使用commit；一旦提交成功，无法rollback。

事务并发可能出现的问题

在同一时刻同时执行多个事务时，称为事务并发。

事务并发会有可能出现以下问题

问题	描述
脏读	事务A读取到了事务B未提交的数据
不可重复读	事务A中如果要读取两次数据，在这期间，事务B对数据进行了修改并提交，导致事务A读取两次的情况不一致
幻读	事务A读取id为1~10之间的数据，假如只有id为2和5的数据，在读取期间，事务B添加了一条id为3的数据，导致事务A多读到了事务B中的数据

事务隔离级别

为了防止事务并发时出现以上各种问题，数据库中设计了几种事务与事务之间的隔离级别。

隔离级别	能否出现脏读	能否出现不可重复读	能否出现幻读
Read Uncommitted未提交读RU	会	会	会
Read Committed已提交读RC(Oracle默认)	不会	会	会
Repeatable Read可重复读RR(MySQL默认)	不会	不会	会
Serializable可序列化	不会	不会	不会

查看事务隔离级别

select @@transatcion_isolation

设置事务隔离级别

set [session|global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]

触发器trigger

如果要在更新某张表之前或之后，自动执行另一组sql时，可以使用触发器实现。

如表A是客户表，表B是操作日志表，对表A进行更新操作时，将操作的记录保存到表B中。

慎用触发器，因为如果有10000条记录，在修改所有记录时，触发器就会执行10000次，会花费很多时间。

创建触发器

create trigger 触发器名
触发时机 触发操作 on 表名 for each row
begin
	触发时执行的sql;
end

-- 创建操作日志表
create table log(
	log_id int not null primary key auto_increment,
	log_opt varchar(20) not null,
	log_time datetime not null
)

-- 创建触发器，在向客户表中添加记录后，自动向日志表中添加记录
create trigger mytrigger
after insert on customer for each row
begin
	insert into log values(null,'添加了数据',now())
end

使用触发器

一旦创建成功触发器，无需刻意调用，在执行相应的操作时，自动执行触发器

-- 只对customer表做插入操作，会自动向log表中添加记录
insert into customer values(null,'测试插入','123123',0,null);

删除触发器

drop trigger 触发器名;

存储过程procedure

类似于java中的方法，定义一组用于完成特定功能的sql语句。

定义存储过程后，通过调用存储过程名，就可以执行定义时的内容。

存储过程可以有参数。

调用存储过程

-- 调用无参数的存储过程
call 存储过程名();
-- 调用输入型参数的存储过程
call 存储过程名('实参');
-- 调用输出型参数的存储过程
call 存储过程名(@变量);
-- 调用输入输出型参数的存储过程
set @变量
call 存储过程名(@变量)

定义存储过程

create procedure 存储过程名([参数类型 参数名 参数数据类型])-- 参数类型分为输入型/输出型/输入输出型
begin
   sql语句;
end

无参数

create procedure 存储过程名()
begin 
	sql语句
end

-- 创建存储过程，查询每本图书的书名、作者、类型
CREATE PROCEDURE myproc1 () BEGIN
	SELECT
		book_name,
		book_author,
		type_name 
	FROM
		book_info bi,
		book_type bt 
	WHERE
		bi.type_id = bt.type_id;
END
-- 调用存储过程
call myproc1();

输入型参数

create procedure 存储过程名(in 形参名 数据类型)
begin 
	sql语句;
end

-- 根据图书类型查询该类型下的所有图书
CREATE PROCEDURE myproc2 (IN lx VARCHAR ( 20 )) 
BEGIN
	SELECT
		* 
	FROM
		book_info bi,
		book_type bt 
	WHERE
		bi.type_id = bt.type_id 
		AND type_name = lx;
END

-- 调用
call myproc2('杂志')

输出型参数

类似于java中有返回值的方法

create procedure 存储过程名(out 形参名 数据类型)
begin 
	sql语句;
	-- 通常需要将查询出的结果通过into赋值给形参
end

-- 根据作者查询图书数量
CREATE PROCEDURE myproc3 ( IN zz VARCHAR (20), OUT book_count INT ) 
BEGIN
-- 将查询的结果into到参数book_count中
	SELECT
		count( book_id ) INTO book_count 
	FROM
		book_info 
	WHERE
		book_author = zz;
	
	END
-- 调用存储过程,@x表示将存储过程的输出型参数保存到变量x中
call myproc3('金庸',@x)
-- 查询参数中保存的数据
select @x

输入输出型参数

create procedure 存储过程名(inout 形参名 数据类型)
begin 
	sql语句;
end

-- 查询书名中带有指定文字的图书名、作者和类型
create procedure myproc4(inout keyword varchar(20))
begin 
	SELECT
		book_name,
		book_author,
		type_name 
	FROM
		book_info bi,
		book_type bt 
	WHERE
		bi.type_id = bt.type_id 
		AND book_name LIKE concat('%',keyword,'%');
end 
-- 调用存储过程
set @keyword='龙';
call myproc4(@keyword);
select @keyword;

删除存储过程

drop procedure 存储过程名;

MySQL编程

在定义存储过程中，可以定义变量、使用流程控制语句等。

定义变量

create procedure 存储过程名()
begin 
	-- declare 变量名 数据类型;
	declare num int;
	declare name varchar(20);
end

给变量赋值

create procedure 存储过程名()
begin 
	declare num int;
	declare name varchar(20);
	-- 给num赋值 
	-- select 字段/值 into 变量 [from 表];
	select 123 into num;
	select book_name into name from book_info where book_id=1;
end

读取变量的值

create procedure 存储过程名()
begin 
	declare num int;
	declare name varchar(20);
	select 123 into num;
	select book_name into name from book_info where book_id=1;
	-- select 变量;
	select num;
	select name;
end

-- 创建存储过程,查询所有图书总数，保存到变量中
create procedure myproc5()
begin 
	-- 定义变量
	declare sum_num int;
	-- 给变量赋值
	select sum(book_num) into sum_num from book_info ;
	-- 打印变量的值
	select sum_num;
end
-- 调用存储过程
call myproc5()

条件语句

单分支if语句

if 条件
then 
	满足条件时执行的sql；
end if;

-- 根据作者查询图书库存，如果不足1000，输出'库存不足1000'
create procedure myproc6(in zz varchar(20))
begin
	-- 定义变量保存根据作者查询到的图书库存
	declare num int;
	-- 查询sql，将结果保存到变量中
	select sum(book_num) into num from book_info where book_author = zz ;
	-- 判断变量num 
	if num<1000
	then 
		select '库存不足1000';
	end if;
end

call myproc6('郭敬明')

双分支if语句

if 条件
then 
	满足条件时执行的sql;
else 
	不满足条件时执行的sql;
end if;

-- 根据图书类型查询图书数量，如果不足5，输出"不足5种图书"，如果足够5，输出详情
create procedure myproc7(in lx varchar(20))
begin 
	-- 定义变量保存图书数量
	declare num int;
	-- 给变量赋值
	select count(book_id) into num 
	from book_info bi inner join book_type bt on bi.type_id=bt.type_id
	where type_name = lx;
	-- 判断
	if num>=5
	then
		select * from book_info bi inner join book_type bt on bi.type_id=bt.type_id where type_name=lx;
	else
		select '不足5种图书';
	end if;
end
-- 调用存储过程
call myproc7('小说')
call myproc7('漫画')

case语句

CASE 变量
	WHEN 值 THEN
		满足该值时执行sql语句;
	WHEN 值 THEN
		满足该值时执行sql语句;
	ELSE
		没有任何值满足时sql语句;
END CASE;

-- case语句
create procedure myproc8(in num int)
begin
	CASE num
		WHEN 1 THEN
			select '1';
		WHEN 5 THEN
			select '5';
		ELSE
			select '都不是';
	END CASE;
end
-- 调用
call myproc8(6)

循环语句

while循环

while 条件 do
	满足条件时执行的内容;
end while;

-- 添加10个客户
create procedure myproc9()
begin 
	-- 定义循环变量
	declare i int;
	-- 初始化循环变量
	select 1 into i;
	-- while循环
	while i<=10 do
		insert into customer values(null,concat('测试用户',i),'123123',0,null);
		-- 更新循环变量
		set i=i+1;
	end while;
end

call myproc9()

repeat循环

repeat
	循环体;
until 条件 end repeat;

-- repeat循环
create procedure myproc10()
begin 
	declare num int;
	select 50 into num;
	repeat 
		insert into customer values(null,concat('测试用户',num),'123123',0,null);
		set num=num+1;
	until num=60 end repeat;
end
-- 调用
call myproc10()

loop循环

循环名:loop
	循环体;
	if 条件 then leave 循环名;
	end if;
end loop;

-- loop循环
create procedure myproc11()
begin 
	declare num int ;
	select 100 into num;
	test:loop
		insert into customer values(null,concat('测试用户',num),'123123',0,null);
		set num=num-1;
		if num=90 then leave test;
		end if;
	end loop;	
end
-- 调用
call myproc11()

面试题解析

1 、数据库的三范式是什么

• 第一范式：数据表中的每一列都是不可再分的原子项。
• 第二范式：在满足1NF的基础上，消除部分依赖。 对于联合主键而言，每一个非主属性字段都需要完全依赖于主属性，而不是依赖其中一部分。
• 第三范式：在满足2NF的基础上，消除传递依赖。 如果通过A可以得到B，通过B得到C，称为C传递依赖于A

2、一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几？

重启，ID是6；因为InnoDB表只把自增主键的最大ID记录在内存中，如果重启，已删除的最大ID会丢失。
如果表类型是MyISAM，重启之后，最大ID也不会丢失，ID是8；

3、如何获取当前数据库版本？

• SQL语句：select version();
• 命令行：mysql -V 或 mysql --version

4、 char 和 varchar 的区别是什么

• 定长和变长
  char 表示定长，长度固定，varchar表示变长，即长度可变。char如果插入的长度小于定义长度时，则用空格填充；varchar小于定义长度时，还是按实际长度存储，插入多长就存多长。

• 存储的容量不同
  对 char 来说，最多能存放的字符个数 255，和编码无关。
  而 varchar 呢，最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。

5、MySQL 的内连接、左连接、右连接有什么区别？

• 内连接：系统会自动忽略两个表中对应不起来的数据，只显示两个表中关联的数据
• 左连接：一左边的表为基准，将左边的数据全部显示出来，右表中没有对应的数据用null补齐，数据多
  了不显示
• 右连接：以右表为准，将右表的数据全部显示出来，左表中没有相对应的数据用null补齐，数据多了不
  显示

6、MySQL 索引是怎么实现的？

• mysql的索引是由存储引擎来实现，不同的存储引擎实现方式不同。

• 1、B+树中的B不是代表的二叉（Binary） ，而是代表平衡（Balance），因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。

• 2、B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有的记录节点都是按照键值大小顺序存在同一层的叶子节点，由叶子节点指针进行相连。

• 3、B+树在数据库中的特点就是高扇出，因此在数据库中B+树的高度一般都在2~4层，这也就是说查找一个值的记录时，最多只需要2到4次IO,当前的机械硬盘每秒至少可以有100次IO,2-4次IO意味着查询时间只需要0.02到0.04秒。

• 4、B+树索引并不能找到一个给定键值的具体行，B+树索引能找到的只是被查找的键值所在行的页，然后数据库把页读到内存，再内存中进行查找，最后找到要查找的数据。

• 5、数据库中B+树索引可以分为，聚集索引和非聚集索引，但是不管是聚集索引还是非聚集索引，其内部都是B+树实现的，即高度是平衡的，叶子节点存放着所有的数据，聚集索引和非聚集索引不同的是，叶子节点是否存储的是一整行信息。每张表只能有一个聚集索引。

7、怎么验证 MySQL 的索引是否满足需求？

使用方法，在select语句前加上explain就可以了：EXPLAIN SELECT surname,first_name form a,b WHERE a.id=b.id

8、说一下数据库的事务隔离？

• Read uncommitted (读未提交)：最低级别，以上问题均无法解决。
• Read committed (读已提交)：读已提交，可避免脏读情况发生。
• Repeatable Read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在此事务持续期间，禁止其他事务对此字段的更新，可以避免脏读和不可重复读，仍会出现幻读问题。
• Serializable （可序列化）：最严格的事务隔离级别，要求所有事务被串行执行，不能并发执行，可避免脏读、不可重复读、幻读情况的发生。

9、说一下 MySQL 常用的引擎？

• Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是，该引擎是不支持全文搜索的。同时，启动也比较的慢，它是不会保存表的行数的。当进行Select count(*) from table指令的时候，需要进行扫描全表。所以当需要使用数据库的事务时，该引擎就是首选。由于锁的粒度小，写操作是不会锁定全表的。所以在并发度较高的场景下使用会提升效率的。

• .MyIASM引擎，它是MySql的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行Insert插入和Update更新语句时，即执行写操作的时候需要锁定这个表。所以会导致效率会降低。不过和Innodb不同的是，MyIASM引擎是保存了表的行数，于是当进行Select count(*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首先。

10、 说一下 MySQL 的行锁和表锁？

MySQL的存储引擎是从MyISAM到InnoDB，锁从表锁到行锁。后者的出现从某种程度上是弥补前者的不足。比如：MyISAM不支持事务，InnoDB支持事务。表锁虽然开销小，锁表快，但高并发下性能低。行锁虽然开销大，锁表慢，但高并发下相比之下性能更高。事务和行锁都是在确保数据准确的基础上提高并发的处理能力

11、说一下乐观锁和悲观锁？

• 悲观锁：正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

• 乐观锁（ Optimistic Locking ）是相对悲观锁而言，乐观锁假设数据一般情况下不会造成冲突，所以在事务对数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回错误信息，让用户决定如何去做。

• 相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制，一般用记录数据版本的方式实现乐观锁。

12、MySQL 问题排查都有哪些手段？

• 使用 show processlist 命令查看当前所有连接信息。
• 使用 explain 命令查询 SQL 语句执行计划。
• 开启慢查询日志，查看慢查询的 SQL。

13.如何做 MySQL的性能优化？

（1）MySQL数据表字段要选取合适的字段属性。
（2）使用连接Join来代替字查询（sub-Query）。
（3）使用联合Union来代替手动创建临时表。
（4）使用事务机制来完成需要多个SQL操作的数据处理，保证数据的完整性和安全访问。事务一begin关键字开始，commit关键字结束，rollback命令可以把数据库恢复到begin开始之前的状态。（5）使用外键来优化关联表的性能，外键可以用来保证数据的关联性。
（6）使用索引提高MySQL数据库的性能。索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含MAX()，MIN()和ORDER BY这些命令的时候，性能提高更为明显。
（7）通过优化查询语句提高查询速度。多数情况下索引可以提高查询速度，单如果SQL语句使用不恰当的话，索引将无法发挥他应有的作用。1）最好在相同类型的字段间进行比较2）在建有索引的字段上尽量不要使用函数操作.
（8）如果情况允许锁定表的方式会比采用事务的方式拥有更好的性能。

14、mysql 对一个大表做在线ddl，怎么进行实施的才能尽可能降低影响

1、一定要选业务少的时候执行DDL，一定，不要蜜汁自信。

2、执行DDL前，先看一下库中是否有未提交的事务，注意查看事务information_schema.innodb_trx表

3、随时关注服务器日志状况，已有问题要先行解决。show processlist也可以发现一些问题

4、特别危险的操作一定先在预生产环境或测试环境先行模拟，评估风险。

5、尽量避免 kill 会话进程，可能会在某些情况造成数据问题。

6、研发时，预计表就会比较大的时候，要多评审几次，多留一些预置字段，避免DDL操作。

15、 Redis 是什么？都有哪些使用场景？

• Redis：Redis是现在最受欢迎的NoSQL数据库之一，Redis是一个使用ANSI C编写的开源、包含多种数据结构、支持网络、基于内存、可选持久性的键值对存储数据库，其具备如下特性：

  • 基于内存运行，性能高效
  • 支持分布式，理论上可以无限扩展
  • key-value存储系统
  • 开源的使用ANSI C语言编写、遵守BSD协议、支持网络、可基于内存亦可持久化的日志型、Key-Value数据库，并提供多种语言的API

• 使用场景：缓存，数据共享分布式，分布式锁，全局ID，计数器，限流，位统计，购物车等

16、 Redis 有哪些功能？

• 基于本机内存的缓存
• 服务端的Redis
• 持久化（Persistence）
• 哨兵（Sentinel）和复制（Replication）
• 集群（Cluster）

17、Redis 支持的数据类型有哪些？

• 字符串（string）
• 哈希（hash）
• 列表（list）
• 集合（set）
• 有序集合（sorted set）
• 位图 ( Bitmaps )
• 基数统计 ( HyperLogLogs )

18、 怎么保证缓存和数据库数据的一致性？

• （1）对于读写缓存来说，如果我们采用同写回策略，就可以保证缓存和数据库中的数据一致

• （2）对于只读缓存

  • 缓存和数据库的数据不一致一般是由两个原因导致的。其解决方案总结如下：

  • 删除缓冲值和更新数据库失败而导致数据不一致，可以使用重试机制确保删除或者更新操作成功
    在删除缓存值、更新数据库的这两步操作中，有其他线程的并发读操作，导致其他线程读取到旧值，应对方案是延迟双删

19、 Redis 如何做内存优化？

• 缩减键值对象：满足业务要求下 key 越短越好；value 值进行适当压缩
• 共享对象池：即 Redis 内部维护[0-9999]的整数对象池，开发中在满足需求的前提下，尽量使用整数对象以节省内存
• 尽可能使用散列表(hashes)
• 编码优化，控制编码类型
• 控制 key 的数量

20、Redis 常见的性能问题有哪些？该如何解决？

• 1.master写内存快照，seve命令调度rdbsave函数，会阻塞主线程的工程，当快照比较大的时候对性能的影响是非常大的，会间断性暂停服务 。所以master最好不要写内存快照。

• 2.master AOF持久化，如果不重写AOF文件，这个持久化方式对性能的影响是最小的，但是AOF文件会不断增大，AOF文件过大会影响master重启时的恢复速度。master最好不要做任何持久化工作，包括内存快照和AOF日志文件，特别是不要启用内存快照做持久化，如果数据比较关键，某个slave开启AOF备份数据，策略每秒为同步一次。

• 3.master调用BGREWRITEAOF重写AOF文件，AOF在重写的时候会占大量的CPU和内存资源，导致服务load过高，出现短暂的服务暂停现象。

• 4.redis主从复制的性能问题，为了主从复制的速度和连接的稳定性，slave和master最好在同一个局域网内。