数据库笔记

关系型数据库

关系型数据库是建立在关系模型基础上的数据库，简单说数据库是由多张能互相连接的二维表组成的

优点

• 都是使用表结构，格式一致，易于维护
• 使用通用的 SQL 语言操作，使用方便，可用于复杂查询
• 数据存储在磁盘中，比较安全

语法

• --: 单行注释
• # 单行注释(MySQL特有)
• /*...*/ 多行注释
• ; 一条 SQL 语句结束
• 不区分大小写，官方建议大写

SQL分类

CRUD: 增删改查

DDL 数据定义语言

操作数据库或者表

• show databaes: 查看所有的库
• use: 库名 使用数据库
• creat databases: 库名 创建数据库
• drop databases: 库名 删除数据库
• show tables: 查看所有的表
• desc: 表名 查看表的结构
• 创建表

create table 表名 {
    列名(字段名) 数据类型 [约束](可省略),
    列名2        数据类型 [约束],
    
    ...
    最后一行不加逗号
}

• drop table 表名: 删除表
• 修改表
  • 修改表名: alter table 表名 rename to 新表名
  • 添加一列: alter table 表名 add 列名 数据类型
  • 修改数据类型: alter table 表名 modify 列名 新数据类型
  • 修改列名和数据类型: alter table 表名 change 列名 新列名 新数据类型
  • 删除列: alter table 表名 drop 列名

DML 数据操纵语言

对数据进行增删改

• 给指定列添加数据: insert into 表名(列名1, 列名2, ...) values(值1, 值2, ...)
• 给全部列添加数据: insert into 表名 values(值1, 值2, ...)
• 批量添加数据: insert into 表名(列名1, 列名2, ...) values(值1, 值2, ...), (值1, 值2, ...), ...、insert into 表名 values(值1, 值2, ...), (值1, 值2, ...), ...
• 修改数据: update 表名 set 列名1 = 值1, 列名2 = 值2, ... [where 条件]
• 删除数据: delete from 表名 [where 条件]

DQL 数据查询语言

对表中数据进行查询

• 查询语法

select 
    字段列表
from 
    表名列表
where
    条件列表
group by
    分组条件
having
    分组后条件
order by
    排序字段
limit
    分页限定

• 去除重复数据: select distinct 字段列表 from 表名
• 起别名: as 别名
• 两数之间: between and
• 不等号: <> !=
• 多选一: in()
• 与: and &&
• 或: or ||
• 非: not !
• 模糊查询:
  • 任意单个字符: _
  • 任意字符: %
• 排序: order by
  • 升序: asc
  • 降序: desc
• 聚合函数
  • count()
  • max()
  • min()
  • sum()
  • avg()
  • ifnull(列名, 默认值)

注

• 不要用 * 查询所有数据
• null 值的比较不能使用 = 要使用 is
• sum、count 等函数针对单字段时会跳过 null ，可以添加 ifnull 将 null 设置默认值

where和having的区别

• 执行时机不一样: where是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤
• 可判断的条件不一样，where不能对聚合函数进行判断，having可以
• 执行顺序: where > 聚合函数 > having

分页查询

select 列 from 表名 limit 起始行数, 每页数据数量

DCL 数据控制语言

对数据库进行权限操作

TCL 事务控制语言

约束

概念

• 约束是作用于表中列上的规则，用于限制加入表的数据
• 约束的存在保证了数据库中数据的正确性，有效性和完整性

约束

• 非空约束: 列中所有数据不能有 null 值 NOT NULL
• 唯一约束: 保证列中所有数据各不相同 UNIQUE
• 主键约束: 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY
• 检查约束: 保证列中的值满足某一条件(MySQL不支持)
• 默认约束: 保存数据时，未指定值(null也是指定值)则采用默认值 DEFAULT 值
• 外键约束: 外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性
• AUTO_INCREMENT: 当不指定时自动增长

外键约束

• 格式:

CONSTRAINT fk_表1_表2 FOREIGN KEY (外键字段) REFERENCES 连接表(字段)
FOREIGN KEY (外键字段) REFERENCES 连接表(字段)

数据库设计

设计理念

• 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型
• 建立数据库中的表结构以及表与表之间的关联关系的过程
• 有哪些表？表里有哪些字段？表和表之间有什么关系？

数据库设计的步骤

1. 需求分析: 数据是什么？数据具有哪些属性？数据与属性的特点是什么
2. 逻辑分析: 通过ER图对数据进行逻辑建模，不需要考虑我们所选用的数据库管理系统
3. 物理分析: 根据数据库自身的特点把逻辑设计转换为物理设计
4. 维护设计: 对新的需求进行建表 表优化

表关系

一对一

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

一对多

一个数据对应多个数据(例: 一个部门对应多个员工)

多对多

例: 商品和订单 一个商品对应多个订单，一个订单包含多个商品

连接查询

内连接

查询 A B 表的交集信息

隐式内连接

select * from A, B where 条件;

显示内连接

select * from A join B on 条件;

外连接

• 左外连接: 相当与查询A表所有数据和交集部分的数据
• 右外连接: 相当与查询B表所有数据和交集部分数据

子查询

• 单行单列: 作为条件值，使用 = != > < 等进行条件判断

select 字段列表 from 表 where 字段名 = (子查询);

• 多行单列: 作为条件值，使用in等关键字进行条件判断

select 字段列表 from 表 where 字段名 in (子查询);
select 字段列表 from (子查询) where 条件;

事物

• 开始事务: start transaction; 放在完整业务之前
• 提交事务: commit
• 回滚事务: rollback

事物的四大特性

• 原子性(Atomicity): 事物是无可分割的最小操作单位，要么同时成功，要么同时失败
• 一致性(Consistency): 事物完成时，必须使所有的数据都保持一致状态
• 隔离性(Isolation): 多个事务之间，操作的可见性
• 持久性(Durabilit): 事物一旦提交或回滚，它对数据库中的数据的改变就是永久的

设计表思路

• 关联字段、辅助字段、基础字段

注

• 修改库的字符集: alert database 库名 character set utf8
• 修改表的字符集: alter table 表名 convert to character set utf8

数据类型

• double(总长度, 小数点后保留的位数)
• char(指定长度) 存储数据长度不足指定长度，用空格补齐，存储的性能高，浪费空间，最大255字节
• varchar(指定长度) 变长存储，节省空间，存储性能不高(实际相差不多)，最大65535字节

JDBC

JDBC就是使用Java语言操作关系型数据库的一套API，本质上是一套操作所有关系型数据库的规则，即接口

步骤

1. 导入jar包
2. 注册驱动
3. 获取连接
4. 定义sql
5. 获取执行sql的对象 Statement
6. 执行sql
7. 处理返回结果
8. 释放资源

public class Test {
    public static void main(String[] args) throws Exception {
        // 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 获取连接
        String url = "jdbc:mysql://...:3306/db3";
        String username = "...";
        String password = "...";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 定义sql
        String sql = "insert into goods \n" +
                "values(10, '可乐', 3.5, '400ml装', '2022-10-10', 10),\n" +
                "(11, '雪碧', 3.0, '300ml听装', '2021-12-11', 2),\n" +
                "(12, '面包', 2.0, '面包', '2022-11-22', 10),\n" +
                "(13, '薯片', 5.5, '薯片', '2022-3-1', 11),\n" +
                "(14, '泡面', 5, '泡面', '2022-1-11', 100);";

        // 获取执行sql语句的执行对象
        Statement stat = conn.createStatement();

        // 执行sql
        int i = stat.executeUpdate(sql);
        System.out.println("修改了: " + i);

    }
}

MySQL数据模型

关系型数据库

关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能够互相连接的 二维表 组成的数据库

优点

• 都是使用表结构，格式一致，易于维护
• 使用通用的SQL语言操作，使用方便，可用于复杂查询
• 数据存储在磁盘中，安全

JDBC API

DriverManager

驱动管理类

作用

• 注册驱动
• 获得数据库连接

注

• MySql5 之后的驱动包可以不用注册驱动
• url 中如果连接的是本地的数据库，可以不写 localhost 和端口: jdbc:mysql:///database
• 配置 useSSL = false 参数，禁用安全连接方式，解决警告提示

Connection

数据库连接对象

• 执行 sql 语句
  • 普通执行 sql 对象: Statement createStatement()
  • 预编译 sql 的执行对象，防止 sql 注入: PreparedStatement prepareStatement(sql)
  • 执行存储过程的过程: CallableStatement prepareCall(sql)
• 事物管理
  • MySql 事务管理
    • 开启事务: begin/start transaction
    • 提交事务: commit
    • 回滚事务: rollback
  • JDBC 事务管理
    • 开启事务: setAutoCommit(boolean); // true为自动提交事务，false为手动提交事务
    • 提交事务: commit()
    • 回滚事务: rollback()

Statement

执行 sql

• executeUpdate(): 返回影响的行数
• executeQuert(): 返回结果集

ResultSet

• next(): 光标向前移动一行，并判断当前行是否为有效行
• getXxx():
  • 参数为 int: 类的编号，从1开始
  • 参数为 String: 列的名称

PreparedStatement

sql注入

通过操作输入来修改事先定好的 sql 语句，用以达到执行代码对服务器进行攻击的方式

select * from 用户表 where username = '' and password = '' or '1' = '1';

PreparedStatement使用步骤

1. 获取 PreparedStatement 对象

String sql = "select * from user where username = ? and password = ?"

PreparedStatement pstat = conn.prepareStatement(sql);

2. 设置参数的值

setXxx(参数索引, 参数值)

3. 执行 sql： 不需要再传 sql 语句

好处

• 预编译 sql，性能更高
• 防止 sql 注入: 将敏感字符进行转义

预编译功能开启

jdbc url 参数 useServerPrepStmts=true

原理

• 在获取 PreparedStatement 对象时，将 sql 语句发送给 MySQL 服务器进行检查，编译(这些步骤很耗时)
• 执行时就不用再进行这些步骤了，速度更快
• 如果 sql 模板一样，就只需要进行一次检查、编译

Druid 连接池

概述

• 连接池是个容器，负责分配、管理数据库连接
• 它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个
• 释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库资源重用

标准接口DataSource

功能

• 获取连接: Connection getConnection()

使用步骤

1. 导入 jar 包
2. 定义配置文件
3. 加载配置文件

Properties prop = new Properties();

// 方法1
prop.load(new FileInputStream("src/druid.properties"));

// 方法2
prop.load(类名.class.getClassLoader().getResourceAsStream("druid.properties"));

4. 获取连接池对象

DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

5. 获取连接池连接 Connection

Connection connecion = dataSource.getConnecton();

配置 MySql 执行日志 (my.ini)

log-output=FILE
general-log=1
general_log_file="D:\mysql.log"
slow-query-log=1
slow_query_log_file="D:\mysql_slow.log"
long_query_time=2
urceAsStream("druid.properties"));

实用函数

• 如果为空，赋初值: ifnull(, 0)
• 四舍五入:
  1. convert('12.345', decimal(15, 0))
  2. cast('12.345' as decimal(15, 0))

问题

• 使用group by时，出现SQL 错误 [1055] [42000]: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tb.mark' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with

  问题出在mysql5.7.x版本默认开启了only_full_group_by(严格模式)，这个模式下的group_by只能获取受到其影响的字段信息，不能和其他没有收到影响的字段共存，可以通过 select @@global.sql_mode;命令查看是否开启了only_full_group_by模式

  解决方案:

  • 方法1，修改模式:

  set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  

  • 方法2，修改配置文件/etc/my.cnf在mysqld下添加一列:

  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  

  重启mysql服务

  • 方法3(推荐)，使用group by时，将没有参与分组的字段使用ANY_VALUE()包裹:

  select ANY_VALUE(grade), sex, count(*) from students GROUP BY sex;