Mysql 基础

数据库有以下特点

1. 持久化存储数据，其实数据库就是一个文件系统
2. 方便存储和管理数据
3. 使用了统一方式操作数据库 -- SQL

服务

启动和退出

• mysql
  • -u<账户>：输入账户
  • -p<密码>：输入密码
  • -h<ip>：进入

目录结构

每个数据库服务器会有多个数据库，而一个数据库会有多个表，而表中会有多个数据记录 每个数据库其实就是一个文件夹，在安装好之后会有三个数据库分别对应三个文件夹

• mysql：核心数据库，放置有很多表
• performance_schema：对性能提升做一些操作
• test：空的数据库，用于测试 还有一个没有文件夹的数据库
• information_schema：用来描述mysql里的信息，包括表，库的信息 表对应的就是数据库文件夹里的文件

SQL(Struct Query Language) 结构化查询语言

SQL 其实就是定义了操作所有关系型数据库的规则，但每种数据库操作的方式存在不一样的地方 通用语法

• 以单行或多行书写，以分号结尾

• 可以使用空格和缩进来增强语句的可读性

• Mysql 数据库的SQL语句不区分大小写，关键字建议使用大写 三种注释

• -- 或# 单行注释

• /* */多行注释 SQL 分类

• DDL(Data Definition Language)：数据定义语言，用于定义数据库对象：数据库、表、列等，关键字create，drop，alter等

• DML(Data Manipulation Language)：数据操作语言，用于对数据库中表的数据进行增删改，关键字insert，delete，update等

• DQL(Data Query Language)：用来查询数据库中表的表的记录(数据)，关键字select，where等

• DCL(Data Control Language)：数据控制语言，用于授权 数据库 CRUD

• Create：创建

  • create database <库名>：用于创建一个数据库
  • create database if not exists <库名>：只有当要创建的数据库不存在时创建一个数据库
  • create database <库名> character set <字符集>：只有当要创建的数据库不存在时创建一个数据库

• Retrieve：查询

  • show database：查询所有数据库名称
  • show create datebase <库名>：查询创建某个数据库的创建语句，同时可以查看字符集

• Update：更新

  • alter database <库名> character set <字符集>

• Delete：删除

  • drop database <库名>
  • drop database if exists <库名>：只有当要创建的数据库不存在时创建一个数据库 使用数据库

• select database()：查询正在使用的数据库名称

• use <库名>：使用数据库 表 CRUD ：

• Create：创建

  • create table <表名>(列名 数据类型,)：创建表，数据库类型有以下几种
    • int：整数类型
    • double(<位数>,<小数点后几位>)：小数类型
    • date：只包含年月日的日期yyyy-MM-dd
    • datetime：日期，包含年月日时分秒yyyy-MM-dd HH:mm:ss
    • timestamp：时间戳类型，包含年月日时分秒yyyy-MM-dd HH:mm:ss，如果将来不给这个字段赋值，则默认使用当前的系统时间，来自动赋值
    • varchar(最大多少字符)：字符串类型
  • create table <表名> like <被复制的表名>：用于复制表

• Retrieve：查询

  • show tables：查询某个数据库中的表
  • desc <表名>：展示表结构
  • show create table <表名>：查询表信息

• Update：更新

  • alter table <表名> add <列名> <类型>：添加一列
  • alter table <表名> drop <列名>：删除某列
  • alter table <表名> rename to <新表名>：修改表名
  • alter table <表名> character set utf8：修改表名
  • alter table <表名> change <列名> <新列名> <类型>：修改列名和类型
  • alter table <表名> modify <列名> <类型>：修改类型

• Delete：删除

  • drop table <表名> if exists：删除某个表
  • truncate table <表名>：也是删除表，然后再创建一个一模一样的空表

DML 操作表中数据

• 添加数据

  • insert into <表名>(<列1>,<列2>...) values(<值1>,<值2>...) 注意

  • 列名和表名要一一对应

  • 如果表名后不定义列名，则默认给所有列添加值

  • 除了数字类型，其他类型需要使用引号

• 删除数据

  • delete from <表名> [where <条件>] 注意

  • 如果没加条件则删除表中所有数据，并且有多少条数据就会执行多少条删除的操作，不推荐使用，最好使用truncate

• 修改数据

  • update <表名> set <列名1 = 值1>,<列名2 = 值2>...[where <条件>] 注意

  • 如果不加任何条件，则会将表中所有数据全部修改

DQL 查询语句

• 排序查询

  • order by <排序字段1 排序方式1>, <排序字段2 排序方式2>，如果第一种排序方式的值一致就按第二种排序方式排序，默认为ASC(升序)，还有DESC(降序)。

• 聚合函数：将一列数据作为一个整体，进行纵向的计算，select <函数名>(<列名>) from <表名>，聚合函数的计算会排除null值 如果想把 null 纳入计算，需要加入 ifnull(<列名, 0) 把 null 替换为 0

  • count：计算个数，一般选择非空的列进行计算，比如主键，或是count(*)计算所有列，但不推荐*
  • max：计算最大值
  • min：计算最小值
  • sum：求和
  • avg：计算平均值 可以给聚合函数加别名，在语句其他地方可以使用这个别名代替聚合函数 <函数名>(<列名>) <别名>

• 分组查询：group by <分组字段> 注意：分组之后查询的字段：分组字段、聚合函数

  • select <分组字段>, <聚合函数1>, <聚合函数2>... from <表名> group by <分组字段>，其中select后的为展示的列字段

  • 可以设定参与分组的条件比如where <条件> group by <分组字段>

  • 可以设定分组之后的条件比如group by <分组字段> having <条件> where 和 having 的区别

  • where在分组之前进行限定，如果不满足条件不参与分组，having在分组之后进行限定，不满足条件不会被查询

  • where后不可以跟聚合函数，having可以进行聚合函数的判断

• 分页查询：limit <开始的索引>, <每页条数>

  • select * from limit 0,3：从0开始查，查3条数据

• 基础查询

  • select * from <表名>：查询表中数据
  • select distinct <字段名> from <表名>：主去除重复的结果集，要是加上distinct这个字段，如果写了多个字段则多个字段必须完全一样才会看作一样
  • select <字段1 + 字段2> from <表名>：计算列，一般只进行数值型的就按比如可以通过+来加上一个字段表示多个字段值相加后的值，如果有null参与了计算则值也为null。另外也可以给字段起别名，在字段后加个空格即可

• 条件查询

  • where后跟条件语句
  • 运算符
    • 比较运算符> < <= >= = <>：<>表示不等于，也可以使用!=，null不能使用=或是!=来判断
    • between...and：在一个范围之内比如between 100 and 200
    • in(集合)：集合表示多个值，使用逗号分隔
    • like ''：模糊查询
      • 占位符：
        • _：任意单个字符
        • %：任意多个字符
    • is null | is not null：是否为null
    • and | &&：与，建议使用前者，&&并不通用
    • or | ||：或
    • not | !：非

• 约束：对表中的数据进行限定，保证数据的正确性，有效性和完整性。分类：

  • 主键约束primary key，非空且唯一，一张表只有一个字段为主键，主键就是表中记录的唯一标识。如果某一列是数值类型，使用auto_increment可以完成值自动增长 删除主键约束 alter table <表名> drop primary key

  • 非空约束not null，值不能为null

  • 唯一约束unique，值不能重复。多个null不算重复

  • 外键约束foreign key，就是让本表的外键和其他表的被唯一约束的列联系起来constraint <外键名> foreign key (<外键列名>) references <主表名>(<主表列名>) 删除外键约束 alter table <表名> drop foreign key <外键名>

    添加外键约束 alter table <表名> add constraint <外键名> foreign key (<外键列名>) references <主表名>(<主表列名>)

    级联添加 on update cascader

    级联删除 on delete cascader

  可以在创建表的时候进行约束，比如 name varchar(20) not null ，或是创建表之后添加约束 alter table <表名> modify <列名> <列类型> not null

DCL 管理用户、授权 一般来讲会有 DBA(数据库管理员) 来对数据库进行管理 当 mysql 安装完成之后会有一个 mysql 的文件夹，里面会有一个 user 表用于存放用户信息，所以操作用户其实就是操作这张表，我们首先要

1. 切换到 mysql 数据库use mysql;
2. 查询user表select * from user;，%表示可以在任意主机使用用户登录数据库

• 管理用户
  • create user <用户名>@<主机名> identified by <密码>：创建用户
  • drop user <用户名>@<主机名>：删除用户
• 设置密码
  • set password for <用户名>@<主机名> = password(<新密码>)：更改用户密码
  • 如果忘了root用户的密码怎么办？
    1. net stop mysql：停止 mysql 服务，需要管理员权限
    2. mysql --skip-grant-tables： 使用无验证方式启动 mysql，同时必须在mysql所在的物理主机上才可以操作
    3. update user set password = password('root') where user = 'root'：更新root密码
    4. net start mysql：启动 mysql 服务
• 权限配置
  • show grants for <用户名>@<主机名>;：查询权限
  • grant <权限列表> on <数据库名>.<表名> to <用户名>@<主机名>;：授予权限，权限列表可以有select, delete, update，*.*表示所有权限
  • revoke <权限列表> on <数据库名>.<表名> to <用户名>@<主机名>;：撤销权限

数据库设计

表的设计直接影响到项目开发的难易程度，也影响到项目性能 多表之间的关系

1. 一对一：比如人和身份证，在任意一方添加外键指向另一方并且这个外键唯一
2. 一对多(多对一)：部门和员工，在多的一方建立外键指向一的一方的主键
3. 多对多：学生和课程的关系，多对多关系实现需要借助第三张中间表的，中间表需要包含两个字段，作为外键分别指向两种表的主键。中间表的主键可以是两个表的联合主键primary key(<表一主键，表二主键...>) 数据库范式就是设计数据库时需要遵循的规范，各种范式呈递增规范，越高的范式数据库冗余越小，一般遵循前三种范式就够了，要遵循后面的范式需要先遵循前面的范式 首先有些概念必须了解

• 函数依赖：如果通过 a 属性或是属性组的值可以确定唯一 b 属性的值，则称 b 依赖于 a，比如学号被姓名依赖
• 完全函数依赖：如果 a 是一个属性组，b 属性的确定需要依赖于 a 属性组中所有属性的确定，比如学号和课程确定分数
• 部分函数依赖：属性组中的一个属性可以确定另一个属性的值
• 传递函数依赖：通过 a 属性(属性组)的值可以确定 b 属性的值，再通过 b 属性(属性组)的值可以确定 c 属性的值，则称 c 传递函数依赖于 a，比如学号被系名依赖，系名被系主任依赖
• 码：如果在一张表中，一个属性或属性组被其他所有属性完全依赖，则称这个属性或属性组为该表的码
  • 主属性：码属性组中的属性
  • 非主属性：非码属性组中的属性

1. 第一范式1NF：每一列都是不可分割的原子数据项
2. 第二范式2NF：非码属性必须完全依赖于候选码，在1NF基础上消除非主属性对主码的部分函数依赖
3. 第三范式3NF：在2NF基础上，任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)

数据库的备份和还原

• mysqldump -u<用户名> -p<密码> > <保存的路径>：备份
• 如果还原，需要登录->创建数据库->使用数据库->source <文件路径>

多表查询

select * from <表1>, <表2> ：返回两张表的笛卡尔积，我们要做的就是消除无用数据，这种做法有以下几种

• 内连接查询：当数据没有另一个表的对应信息时，不会查询出来，比如外键值为null，所以它只查询交集部分

  • 隐式内连接：使用where条件消除无用数据，比如select <表1>.<字段>, <表2>.<字段>... from <表1>, <表2> where <表1>.dept_id = <表2>.id from 后可以设定表的别名，使用空格隔开

  • 显式内连接：select <字段列表> from <表1> inner? join <表2> on <条件>

• 外连接查询：查询的是左表所有数据以及其交集部分

  • 左外连接：select <字段列表> from <表1> left outer? join <表2> on <条件>，当第一个表数据没有另一个表的对应信息时，还是会查询出来
  • 右外连接：select <字段列表> from <表1> right outer? join <表2> on <条件>，左外连接反过来而已

• 子查询：查询中嵌套查询，称嵌套查询为子查询，也就是使用一个查询语句表示条件，比如select * from emp where emp.salary = (select max(salary) from emp)

  • 单行单列：子查询可以作为条件，使用运算符> >= < <= =判断
  • 多行单列：可以使用运算符in来判断
  • 多行多列：可将查询语句作为表来使用，select <字段列表> from <表1>, <查询语句> where <条件>

事务

如果一个包含多个步骤的业务操作，被事务管理，这些操作要不同时成功要不同时失败。如果操作失败就回滚，操作如下

1. 开启事务：start transaction
2. 回滚：rollback
3. 提交：commit 比如，张三给李四转 500

# 开启事务
start transaction;
update account set balance = balance - 500 where name = 'zhangsan';
update account set balance = balance + 500 where name = 'lisi';
# 如果没问题提交数据
commit;
# 发现出问题了，回滚事务，回滚到启动事务之前
rollback;

在 Mysql 中事务默认自动提交，一条 DML(赠删改) 会自动提交一次事务，而 Oracle 数据库默认手动提交

• select @@autocommit;：如果值为1表示默认提交方式为自动提交，0为手动提交
• set @@autocommit = 0;：用于设定事务提交方式，可以将默认自动提交的事务改为手动提交才会生效

事务的四大特征

1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
2. 持久性：如果事务一旦提交或回滚后，数据会持久化地保存数据
3. 隔离性：多个事物之间，相互独立
4. 一致性：事务操作前后，数据总量不变

事务的隔离级别

多个事物之间相互独立的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题 存在问题

• 脏读：一个事务读取到另一个事务没有提交的数据
• 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样
• 幻读：一个事务(DML)数据表中所有记录，另一个事务添加了一一条数据，则第一个事务查询不到自己的修改 隔离级别
• read uncommitted：读未提交，产生的问题：脏读、不可重复读、幻读，同一个事务只要更改了数据就会发生变化
• read committed：读已提交，产生的问题：不可重复读、幻读，oracale 默认为这种，同一个事务只要有一个地方commit数据就会发生变化
• repeated read：可重复读，产生的问题：mysql 默认，幻读，也就是同一个事务只有都commit之后读取的数据才会发生变化
• serializable：串行化：可以解决所有问题，当一个事务在处理一张表时，这个表，会变为不可操作，任何操作会进入无限等待状态，当commit之后才会有结果 问题表现
• 脏读：事务读取到未commit的数据
• 不可重复读：也就是两次读取的数据不一样 注意：隔离级别从小到大安全性越来越高，但效率越来越低
• set global transaction isolation level <级别字符串>：数据库设置隔离级别
• select @@tx_isolation：查询隔离级别

JDBC(Java Database Connectivity)

JDBC 为 Java 操作数据库语言，本质是希望使用一套 Java 代码可以操作所有的关系型数据库， JDBC 定义了操作所有关系型数据库的规则(接口)，各个数据库厂商去实现这套接口，提供数据库驱动 jar 包，我们可以使用这套 JDBC 编程，真正执行的是驱动 jar 包中的类

如何使用 JDBC

1. 导入驱动jar包，先复制jar包到libs目录下，然后右键Add As Library
2. 注册驱动Class.forName('com.mysql.jdbc.Driver')
3. 获取数据库连接对象Connection，Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "password")
4. 定义sql：String sql = "update account set balance = 500 where id = 1"
5. 获取执行sql语句的对象Statement：Statement stmt = conn.createStatement();
6. 执行sql，接受返回结果：int count = stmt.executeUpdate(sql)
7. 处理结果：System.out.println(count)
8. 释放资源：stmt.close();conn.close();

各个对象

• DriverManager：驱动管理对象，功能有

  1. static void registerDriver(Driver diver)：注册驱动，而Class.forName('com.mysql.jdbc.Driver')中有静态代码块会自动执行注册操作，但其实也可以不进行注册，在jar包中的META-INF的services中的java.sql.Driver中就有启动语句
  2. static Connection getConnection(String url, String user, String password)，参数
  • url：指定连接的路径，jdbc:mysql://ip地址(域名):端口号/数据库名称
  • user：用户名
  • password：密码

• Connection：数据库连接对象，功能

  1. 获取执行sql的对象
  • Statement createStatement()
  • PreparedStatement prepareStatement(string sql)
  2. 管理事务
  • void setAutoCommit(boolean autoCommit)：调用该方法设置参数为false，即开始事务
  • commit()：提交事务
  • rollback()：回滚事务

• Statement：执行sql的对象，用于执行静态sql并返回其生成的结果的对象，易受到 sql 注入攻击

  • boolean execute(String sql)：执行给定的sql语句，可能返回多个结果，true如果第一个结果是一个ResultSet对象；false如果是更新计数或没有结果
  • int executeUpdate(String sql)：执行DML(insert, update, delete)语句、DDL(create, alter, drop)语句，返回值为影响的行数，可以通过影响的行数判断DML语句是否执行成功，返回值 > 0 的则执行成功，反之失败
  • ResultSet executeQuery(String sql)：执行DQL(select)语句

• ResultSet：结果集对象，用于封装查询结果，可以通过一个游标一个一个指向下一条数据，和构造器类似

  • next()：游标向下移动一行，判断是否是最后一行，如果是返回false，不是则返回true

  • get<数据类型>(<列编号 | 列名>)：获取数据，比如int getInt() 使用步骤

  • 游标向下移动

  • 判断是否有数据

  • 获取数据

  while(rs.next()) {
    int id = rs.getInt(1);
  }

• PreparedStatement：也是执行sql对象但是功能更加强大，执行预编译sql，可防止sql注入，同时效率更高。参数使用?作为占位符
  • PreparedStatement Connection.preparedStatement(String sql)：sql语句使用?作为占位符，比如select * from user where username = ? and password = ?
  • set<类型>(<?的位置>, <?的值>)：用于给占位符赋值

事务

事务为一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则多个步骤要么同时成功，要么同时失败

• setAutoCommit(boolean autocommit)：调用该方法设置参数为false，即开始事务，在执行sql之前开启
• commit()：提交事务，当所有sql执行完后提交事务
• rollback()：回滚事务，在catch中进行事务的回滚

数据库连接池

当用户连接数据库，使用完之后就断开连接会消耗资源，此时我们可以使用一个数据库连接池来存储连接对象，用户需要连接数据库时需要从连接池中获取连接对象，使用完之后将连接对象归还到连接池中，好处：

1. 节约资源
2. 用户访问高效 public DataSource extends CommonDataSource, Wrapper ：一个连接到这个 DataSource 对象所代表的物理数据源的工厂 DataSource 接口由驱动程序供应商实现，有三种类型的实现：
3. 基本实现：生成标准的Connection对象：
4. 连接池实现：生成将自动参与连接池的Connection对象，此实现与中间层连接池管理器配合使用
5. 分布式事务实现：生成可用于分布式事务的Connection对象，并且几乎总是参与连接池。此实现与中间层事务管理器一起工作，并且几乎总是使用连接池管理器 数据库厂商有两种技术来实现数据库池：
6. C3P0

• 导入两个jar包，c3p0和mchange-commons-java，同时记得导入驱动jar包
• 定义配置文件c3p0.properties或c3p0-config.xml，直接放置在src目录下即可
• 创建核心对象，数据库连接对象ComboPooledDataSource
• 获取连接对象getConnection()

2. Druid：新版，由阿里实现

• 导入jar包druid
• 定义配置文件druid.properties，为properties，可以叫任何名称放在任意目录下
• 获取数据库连接池对象，通过工厂来获取DruidDataSourceFactory.createDataSource()
• 获取连接对象getConnection() DataSource 接口实现

1. getConnection()：获取连接，不传参则为默认配置，第一个参数可指定名称配置
2. Connection.close()：如果连接对象是从连接池中获取的，那么调用close()方法不会关闭连接，而是归还到连接池 定义工具类
3. 定义一个类JDBCUtils
4. 提供静态代码块加载配置文件，初始化连接池对象
5. 提供方法
6. 获取连接方法：通过数据库连接池获取连接，public static Connection getConnection() { return ds.getConnection(); }
7. 释放资源：public static void close(Statement stmt, Connection conn)
8. 获取连接池方法：public static DataSource getDataSource()

Spring JDBC

Spring 的 JDBC 简单封装，提供了一个 JDBCTemplate 对象简化 JDBC 的开发 步骤

1. 导入jar包
2. 创建JdbcTemplate对象，依赖于数据源DataSource，JdbcTemplate template = new JdbcTemplate(ds)
3. 调用JdbcTemplate的方法来完成操作

• update()：执行DML语句，增删改语句

    JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "update account set balance = 5000 where id = ?";
    int count = template.update(sql, 3);
    System.out.println(count);
    ```

  + `queryForMap()`：查询结果将结果集封装为`map`集合，其中列名为`key`，值为`value`，这个方法查询到结果集长度只能是`1`
  + `queryForList()`：查询结果将结果集封装为`List`集合，将每一条记录封装为一个`Map`集合，在将`Map`集合装载到`List`集合中
  + `query()`：查询结果将结果封装为`JavaBean`对象，`List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class))`
  + `queryForObject()`：查询结果，将结果封装为对象，一般用于聚合函数的查询