前言
本来想把前面的章节不上，想想 如果大家是看视频 做了笔记，比我发现成的文档要强的多，强调下 我不是偷懒 不是偷懒
一、group by 的 Having子句
having子句在select语句里与group by子句联合使用时，用于告诉group by子句在输出里包含哪些组，having对于group by的作用相当于where对于select的作用，即设定条件用的。使用having子句可以让结果集里包含或去除整组的数据
二、ALTER命令
1.新增表字段
ALTER TABLE [table_name] ADD [column_name] [dataType];
2.修改表字段
ALTER TABLE [table_name] MODIFY [column_name] varchar(200);
ALTER TABLE [table_name] change [old_column_name] [new_column_name] varchar(200);
3.删除表字段
ALTER TABLE [table_name] DROP [column_name]
4.修改表名
ALTER TABLE [table_name] RENAME TO [new_table_name];
三、索引
1.索引原理：
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
2.索引的数据结构：
任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。
3.索引的类别:
3.1.INDEX(普通索引)
ALTER TABLE [table_name] ADD INDEX index_name ( [column] );
3.2.主键索引
ALTER TABLE [table_name] ADD PRIMARY KEY ( [column] );
3.3.UNIQUE(唯一索引)
ALTER TABLE [table_name] ADD UNIQUE ([column]);
3.4.FULLTEXT(全文索引)
ALTER TABLE [table_name] ADD FULLTEXT ([column]);
3.5.多列索引
ALTER TABLE [table_name] ADD INDEX index_name ( [columns...,]);
4.索引失效的几种情况
4.1 索引字段可以为null
使用is null或is not null时，可能会导致索引失效
4.2 like通配符可能会导致索引失效
like查询以%开头时，会导致索引失效。
select * from [table_name] where [column_name] like '%name' ;
4.3 在索引列上使用内置函数，一定会导致索引失效
select * from [table_name] where DATE_ADD(create_time, INTERVAL 1 DAY) = 7;
四、事务的特征
原子性（ Atomicity ）**：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性(Isolation)**：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性(Durability)**：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
1. 事务的操作
五、InnoDB与MyISAM的区别
5.1 InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一
5.2 InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败
六、存储函数、存储过程、触发器、视图
6.1 常用函数：
sum ：求和
min ：最小数
max ：最大数count ：统计个数group_concat([column_name]) ：分组并合并此字段
substr([column_name],pos,len) ：截取字符串函数
......
6.2 视图
定义:
存储的查询语句,当调用的时候,产生结果集,视图充当的是虚拟表的角色.
CREATE VIEW myview AS <selectSql>;
6.3 存储函数
定义变量：
使用DECLARE定义局部变量，只能在存储过程和存储函数中使用
在流程语句的分析中，我们在存储过程中使用变量的声明与设置，由于这些变量也只能在存储过程中使用，因此也称为局部变量，变量的声明可以使用以下语法：
DECLARE 变量名[,变量名2...] 数据类型(type) [DEFAULT value];
DECLARE num INT DEFAULT 10 ;
-- 定义变量num，数据类型为INT型，默认值为10
DECLARE mult_a,mult_b int； //定义多个变量
给变量赋值：
1、使用set
set var =value;
2、使用select
select var := value;
语法格式
CREATE FUNCTION 函数([参数类型 数据类型[,….]]) RETURNS 返回类型
BEGIN
SQL语句.....
RETURN (返回的数据)
END
6.4存储过程
输入参数、输出参数和输入输出参数
输入参数： IN 输入参数：OUT 输入输出参数： INOUT
查询存储过程：
show procedure STATUS where Db='数据库名称'
SHOW PROCEDURE STATUS //查询所有的存储过程
SHOW PROCEDURE STATUS LIKE 'pattern' //查询符号要求的存储过程
如：
SHOW PROCEDURE STATUS LIKE 'myuse' 查询的就是存储过程名称 为myuse 的信息
查询数据库字符编码情况
show variables where Variable_name like 'collation%';
删除存储过程：
DROP PROCEDURE IF EXISTS myuse //IF EXISTS最好带上 不然会报错
创建存储过程的基本样式：
存储函数与存储过程有些类似，简单来说就是封装一段sql代码，完成一种特定的功能，并返回结果。其语法如下：
create procedure 存储过程名称（ 参数的种类 参数1 数据类型，
参数的种类 参数2数据类型...参数的种类 参数n 数据类型）
BEGIN
// 处理内容 其中可以用到函数和条件语句等
END
语法
CREATE PROCEDURE 存储过程名称()
BEGIN
//sql
END
与存储函数的区别
1.与存储过程不同的是，存储函数中不能指定输出参数( OUT)和输入输出参数( INOUT)类型。存储函数只能指定输入类型而且不能带IN。同时存储函数可以通过RETURN命令将处理的结果返回给调用方。
2.存储函数必须在参数列表后的RETURNS并指令返回的数据类型，存储函数必须要有返回值，而存储过程可以没有返回值。
3.调用方式不同，存储过程使用select ,存储函数使用 call
4.存储函数参数不能使用 in 、out 、inout
6.5 触发器
定义:
触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
触发事件(tigger_event)：
七、JDBC
6.1 名称解释
Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。
6.2 JDBC 编程步骤
加载驱动程序：
Class.forName(driverClass)
--->具体的使用
Class.forName("com.mysql.jdbc.Driver") //加载MySql驱动
Class.forName("oracle.jdbc.driver.OracleDriver")//加载Oracle驱动
获得数据库连接：
DriverManager.getConnection("<URL>", "账号", "密码");
---- mysql具体的使用
DriverManager.getConnection("jdbc:mysql://<Ip地址>:<port>/<databaseName>", "账号", "密码");
创建Statement\PreparedStatement对象：
conn.createStatement();
conn.prepareStatement(sql);//获取预编译语句对象 [推荐]
6.2 jdbc重要的接口及api
PreparedStatement
ResultSet
| 方法 | 作用 | 备注 | |
|---|---|---|---|
| next() | 将光标从当前位置向前移动一行。一个ResultSet光标最初被定位在第一排之前；对该方法的第一次调用使第一行成为当前行；这个第二次调用使第二行成为当前行，依此类推。 | ||
| getString(int columnIndex) | 当前行中指定列的值是String类型 | ||
| getBoolean(int columnIndex) | 当前行中指定列的值是Bool类型 | ||
| getByte(int columnIndex) | 当前行中指定列的值是byte类型 | ||
| getBytes(int columnIndex) | 当前行中指定列的值是byte数组类型 | ||
| getInt(int columnIndex) | |||
| getLong(int columnIndex) | |||
| getDouble(int columnIndex) | |||
| getTime(int columnIndex) | 当前行中指定列的值是Time类型 | 返回的Time是属于java.sql.Time包里面 | |
| getTimestamp(int columnIndex) | 当前行中指定列的值是Timestamp类型 | 返回的Time是属于java.sql.Time包里面 |
6.3 jdbc的封装
目的：
通过实体对象 反射生成插入语句 并执行插入语句
原材料：
0.数据库sql
CREATE TABLE `t_book_info` (
`id` int(20) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`author` varchar(50) DEFAULT NULL,
`push_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1.实体对象(TBookInfo)
package com.jdbc.demo;
import java.util.Date;
/**
* <ul>
* <li>Title: TBookInfo</li>
* <li>Description: TODO </li>
* </ul>
*
* @author 程序员ken
* @date 2021-12-22 16:11
*/
@TableName("t_book_info")
public class TBookInfo {
@TableField(name = "id",autoIncrement=true)
private int id;
@TableField(name = "name")
private String name;
@TableField(name = "author")
private String author;
@TableField(name = "push_time")
private Date pushTime;
//get set 方法省略
}
2.注解类（TableName 和 TableField 分别作用到类上面 和 字段上面）
TableName 类
package com.jdbc.demo;
import java.lang.annotation.*;
import static java.lang.annotation.ElementType.FIELD;
import static java.lang.annotation.ElementType.TYPE;
@Documented
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface TableName {
String value();
}
TableField类
package com.jdbc.demo;
import java.lang.annotation.*;
@Documented
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface TableField {
String name();
boolean exist() default true;
boolean autoIncrement() default false;
}
3.实际代码（需要用到反射机制）
package com.jdbc.demo;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* <ul>
* <li>Title: JdbcUtil</li>
* <li>Description: TODO </li>
* </ul>
*
* @author:程序员ken
* @date 2021-12-22 15:55
*/
public class JdbcUtil {
private static String url ="jdbc:mysql://127.0.0.1:3306/mytest?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true";
private static String user ="root";
private static String password ="123456";
//获取连接的方法
private static Connection getConnect() {
Connection con =null;
try {
con = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
// 封装统一的新增
//实体类 =》 泛型 T =》生成需要sql语句
/**
* 功能描述:
* @param t
* @return: void
* @author:程序员ken
* @date: 2021-12-22 16:08
*/
public static <T> void insert(T t) {
Class<?> aClass = t.getClass();
Field[] fields = aClass.getDeclaredFields();
String insertSqlTemp = "insert into #{tableName} (#{columns}) values(#{columnValues})";
StringBuffer columnsSbf = new StringBuffer();
StringBuffer columnValuesSbf = new StringBuffer();
TableName tableName = aClass.getAnnotation(TableName.class);
if(tableName!=null){
insertSqlTemp = insertSqlTemp.replace(" #{tableName}",tableName.value());
}
List<Field> fieldList =new ArrayList<Field>();
TableField tableField =null;
for (Field field : fields) {
tableField = field.getAnnotation(TableField.class);
if(tableField!=null && tableField.exist() && !tableField.autoIncrement()){
columnsSbf.append(","+tableField.name());
columnValuesSbf.append(",?");
fieldList.add(field);
}
field.setAccessible(true);
}
if(columnsSbf.length()>0){
insertSqlTemp =insertSqlTemp.replace("#{columns}",columnsSbf.delete(0,1)).replace("#{columnValues}",columnValuesSbf.delete(0,1));
}
Connection conn = null;
PreparedStatement pst = null;
try {
conn = getConnect();
pst= conn.prepareStatement(insertSqlTemp);
int ind =1;
for (Field field : fieldList) {
field.setAccessible(true);
try {
pst.setObject(ind,field.get(t));
} catch (IllegalAccessException e) {
e.printStackTrace();
}
ind++;
}
int row = pst.executeUpdate();
System.out.println("影响行数:"+row);
} catch (SQLException e) {
e.printStackTrace();
}catch (ClassNotFoundException e) {
e.printStackTrace();
}finally {
try {
if(pst!=null){
pst.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
其他示例
批量预编译语句
package com.ken.sys.common.util;
import com.ken.sys.common.entity.DbSettingInfo;
import com.ken.sys.common.ifs.PrepareFunction;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;
public class JdbcUtil {
private static Connection conn = null;
private static String finaUrl= "jdbc:mysql://192.168.0.118:3306/erp-sy?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true";
private static String finaUser = "root";
private static String finaPass = "123456";
//默认是 mysql
private static String driver ="com.mysql.jdbc.Driver";
//默认最大提交数
private static final int COMMIT_MAX_COUNT = 20000;
static {
try {
String rootPath = JdbcUtil.class.getClassLoader().getResource("").getPath();
//读取SQL配置文件 配置文件在src下
InputStream in = new FileInputStream( rootPath+"jdbc.properties");
Properties properties = new Properties();
properties.load(in);
//获取参数
driver = properties.getProperty("jdbc.driver");
finaUrl = properties.getProperty("jdbc.url");
finaUser = properties.getProperty("jdbc.username");
finaPass = properties.getProperty("jdbc.password");
} catch (IOException e) {
e.printStackTrace();
}
}
//建立连接 根据属性文件的配置
public static Connection getConnection(){
return getConnection(driver,finaUrl,finaUser,finaPass);
}
//建立连接 根据属性文件的配置
private static Connection getConnection(DbSettingInfo dbSettingInfo){
if(dbSettingInfo==null){
throw new Error("dbSettingInfo is not allowed to be null");
}
return getConnection(dbSettingInfo.getDriver(),dbSettingInfo.getDbUrl(),dbSettingInfo.getUserName(),dbSettingInfo.getPassword());
}
public static Connection getConnection(String driver,String url, String user, String pass){
try {
Class.forName(driver);
conn= DriverManager.getConnection(url,user,pass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeConnection(Connection conn, PreparedStatement stmt, ResultSet rs){
try {
if(rs!=null) {
rs.close();
}
if(stmt!=null) {
stmt.close();
}
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 功能描述: 批量执行sql
* @param dbSettingInfo
* @param executeSql
* @param list
* @return: void
* @author:程序员ken
* @date: 2021-12-09 10:44
*/
public static <T> void executeBatch(DbSettingInfo dbSettingInfo
,String executeSql, List<T> list){
executeBatch(dbSettingInfo,executeSql,list,COMMIT_MAX_COUNT,null);
}
/**
* 功能描述: 批量执行sql
* @param dbSettingInfo
* @param executeSql
* @param list
* @param commitMaxCount
* @return: void
* @author:程序员ken
* @date: 2021-12-09 10:44
*/
public static <T> void executeBatch(DbSettingInfo dbSettingInfo
,String executeSql, List<T> list, int commitMaxCount){
executeBatch(dbSettingInfo,executeSql,list,commitMaxCount,null);
}
/**
* 功能描述: 批量执行sql
* @param dbSettingInfo 数据库信息
* @param executeSql 执行sql
* @param list 数据源
* @param commitMaxCount 最大提交数
* @param prepareFunction 一般可以预处理参数 可以做外部的干预
* @return: void
* @author:程序员ken
* @date: 2021-12-09 10:44
*/
public static <T> void executeBatch(DbSettingInfo dbSettingInfo
,String executeSql, List<T> list, int commitMaxCount, PrepareFunction prepareFunction){
PreparedStatement pstmt =null;
Connection conn =null;
try {
commitMaxCount = commitMaxCount>0 && commitMaxCount<=COMMIT_MAX_COUNT ?commitMaxCount:COMMIT_MAX_COUNT;//默认最大提交2000条
conn = getConnection(dbSettingInfo);
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(executeSql);
int ind =0;
boolean hand =true;
for (T t : list) {
if(prepareFunction!=null){
hand = prepareFunction.hand(t, pstmt);//参数在此设置
}
//不执行此条数据
if(!hand){
continue;
}
pstmt.addBatch();
ind++;
if(ind==commitMaxCount){
pstmt.executeBatch();//批量提交
pstmt.clearParameters();//清除参数
ind =0;
}
}
//不足提交最大数 仍有插入数 进行提交
if(ind>0){
pstmt.executeBatch();//批量提交
pstmt.clearParameters();//清除参数
}
//提交，设置事务初始值
conn.commit();
conn.setAutoCommit(true);
} catch (Exception e) {
//提交失败，执行回滚操作
try {
if(conn!=null){
conn.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
try {
if(pstmt!=null){
pstmt.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
八、数据库备份和还原
8.1 数据库备份**（MySQL 导出数据）**
8.1.1 使用 SELECT ... INTO OUTFILE 语句导出数据
语法：
select * from [table_name] INTO OUTFILE "[文件具体位置精确到文件名]";
1.注意文件反斜杠（\） 用双反斜杠 (\)
2.操作是在mysql中操作 ，而不是命令行中操作，所以可以嵌套在mybatis的xml中使用
<update id="selectExport" >
SELECT * FROM [table_name] INTO OUTFILE "[具体导出文件全路径]";
</update>
[
遇到的错误：
select * from t_project_info INTO OUTFILE "E:\\数据库备份\\backup_data.txt"
> 1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
> 时间: 0s
解决方式:
使用show VARIABLES like '%secure%'; 查询secure_file_priv是否设置值，这个值是导出文件的根目录，设置如图，代表导出的文件只能存在C盘下面。如果需要修改 参考这个，对mysql配置文件进行修改，配置文件名一般为**my.ini **。
secure-file-priv的值有三种情况：
secure_file_prive=null ––限制mysqld 不允许导入导出
secure_file_priv=/path/ – --限制mysqld的导入导出只能发生在默认的/path/目录下
secure_file_priv='' – --不对mysqld 的导入 导出做限制
[
8.1.2 使用[mysqldump ]命令行
如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。
语法
1.单个数据库 所有表数据导出
mysqldump -h [ipAddress] -u [user] -p[password] [database] --default-character-set=utf8 --hex-blob >[backUpPath][exportFileName]
示例
mysqldump -h 127.0.0.1 -u root -p123456 qrcode-beiyong --default-character-set=utf8 --hex-blob >E:\数据库备份\qrcode_2021.sql
2.单个数据库 所有表数据导出
mysqldump -u [user] -p[password] [database] --tables table_name1 table_name2 table_name3 >[backUpPath][exportFileName]
示例
mysqldump -u root -p123456 qrcode-beiyong --tables t_project_info t_bid_auth_peopel_info >E:\数据库备份\qrcode_2022.sql
2.所有数据库 所有表数据导出
mysqldump -u [user] -p[password] –all-databases >[backUpPath][exportFileName]
示例
mysqldump -u root -p123456 –all-databases>E:\数据库备份\qrcode_2021.sql
参数说明
| 参数名 | 缩写 | 含义 |
|---|---|---|
| --host | -h | 服务器IP地址 |
| --port | -P | 服务器端口号 |
| --user | -u | MySQL 用户名 |
| --pasword | -p | MySQL 密码 |
| --databases | 指定要备份的数据库 | |
| --all-databases | 备份mysql服务器上的所有数据库 | |
| --compact | 压缩模式，产生更少的输出 | |
| --comments | 添加注释信息 | |
| --complete-insert | 输出完成的插入语句 | |
| --lock-tables | 备份前，锁定所有数据库表 | |
| --no-create-db/--no-create-info | 禁止生成创建数据库语句 | |
| --force | 当出现错误时仍然继续备份操作 | |
| --default-character-set | 指定默认字符集 | |
| --add-locks | 备份数据库表时锁定数据库表 |
注意：
1.--default-character-set=utf8 设置编码集为utf8 可自定义编码集 也可以不用
2.--hex-blob 用于导出系统中存在二进制数据，如果没有可以二进制数据 可以去掉
3.-h [ipAddress] 代表这连接远程地址 ，如果这块没有 默认是连接主机
4.–all-databases 代表 导出所有数据库
5.主机-p[password] -p和password不要用空格，密码可以为空 但后一步命令行可能会验证密码
8.2 数据库还原[导入数据]
8.2.1 LOAD DATA 命令导入
语法：
LOAD DATA LOCAL INFILE '[文件具体位置]' INTO TABLE [table_name];
示例:
LOAD DATA LOCAL INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.6\\Uploads\\backup_data.txt' INTO TABLE t_project_info
注意:
1.这个不是在mysql的命令行下执行，是作为mysql语句执行 ，所有可以嵌入到xml【map层】的标签中
2.常常是使用mysqldump 命令导出的数据作为数据源
8.2.2 mysql 命令导入
语法：
mysql -u[user] -p[password] < [backUpPath][exportFileName]
示例:
mysql -uroot -p123456 qrcode-backup< E:\数据库备份\qrcode_2021.sql
注意:
1.这个必须在mysql的命令行下执行
2.常常是使用SELECT ... INTO OUTFILE 语句导出的数据作为数据源
视频链接：
欢迎关注我的公众号：程序员ken，程序之路，让我们一起探索，共同进步。
