MYSQL数据库总结

MySQL控制台常用命令

• 查看所有数据库

  show databases;
  

• 切换数据库

  use 数据库名;
  

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

  show tables;
  

• 创建一个数据库

  create database 数据库名;
  

• 删除数据库

  drop database 数据库名;
  

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;
    }
}

行列转换

如现有表score

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()

MySQL核心内容

SQL语句

• 数据库和数据表的创建、修改、删除
• 数据完整性(约束)
• 单表增删改查CURD
• 函数
• 多表查询、嵌套查询

数据库设计

• 实体关系模型(ER)
• ER图
• 范式

JDBC

• 连接MySQL所需jar文件

  • 普通java工程需要手动导入jar文件
  • maven项目需要使用依赖自动导入jar文件

• MySQL驱动名

  //mysql5.5之前版本
  Class.forName("com.mysql.jdbc.Driver");
  //mysql8之后版本
  Class.forName("com.mysql.cj.jdbc.Driver"); 
  

• 连接数据库的字符串

  String url="jdbc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai";
  String username="root";
  String password="密码";
  

事务

• 事务的概念和特性

• 事务并发时出现的问题

  • 脏读
  • 不可重复读
  • 幻读

• 事务的隔离级别

  • read uncommitted 可能会出现脏读、不可重复读和幻读问题，效率最高
  • read committed 解决了脏读问题，可能会出现不可重复读和幻读问题
  • repeatable read MySQL默认 解决了脏读和不可重复读问题，可能会出现幻读问题
  • serializable 解决了脏读、不可重复读和幻读问题，效率最低

存储引擎

• MySQL5.5版本之前，默认使用MyIsam存储引擎，不支持事务
• MySQL5.5版本之后，默认使用InnoDB存储引擎，支持事务