SQL
数据分类
关系型数据库：（SQL）
--mysql,oracle,sql server,DB2,SQLlite
--通过表和表之间，行和列之间的关系进行数据存储
非关系型数据表：（nosql) not only sql
--redis,MongDB
--非关系型数据库，对象存储，通过对象的自身属性来决定
数据库列类型
数值
-tinyint 十分小的数据 1个字节
-smallint 较小的数据 2个字节
-mediumint 中等大小的数据 3个字节
-int 标准的整数 4个字节（常用）
-bigint 较大的数据 8个字节
-float 浮点数 4个字节
-double 浮点数 8个字节
-decimal 字符串的形式的浮点数 金融计算时，一般使用decimal
字符串
-char 字符串固定大小的
-varchar 可变字符串 0-65535 常用变量string
-tinytext 微型文本 2^8-1
-text 文本串 2^16-1 保存大文本
时间日期
-date 日期格式
-time 时间格式
-datetime 日期时间
-timestamp 时间戳
-year
属性
-unsigned(无符号的整数，不能声明为负数）
-zerofill(不足的位数，使用0来填充）
-自增（在上一条记录上加1，一般设计为唯一主键）
-非空（如果不赋值，就报错）
补充，项目中：
每个表，必须存在以下五个字段
id-主键
verion-乐观锁
is_delete-伪删除
gmt_create-创建时间
gmt_update-修改时间
创建数据库表
create TABLE IF NOT EXISTS `student`(
id INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号',
name VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名'
)
MyISAM和InnoDB的区别
数据表的类型
innodb-默认使用
myisam-早些年使用的
一个数据库就是一个文件夹,一个文件夹中有很多个表
mysql引擎在物理文件上的区别
-innoDB在数据库表中只有一个.frm文件，以上上级 目录的ibdata1文件
-myisam对应的文件
--.frm 表结构定义的文件
--.MYD 数据文件
--。MYI 索引文件
show create database jjf -- 查看创建数据库的语句
show create table grade --查看XX数据表的定义语句
desc grade --显示表的结构
修改和删除数据
--修改表名
ALTER TABLE teacher RENAME AS teacher1
--增加表的字段
ALTER TABLE teacher1 ADD age INT(11)
--修改表的字段或属性
ALTER TABLE 表名 MODIFY 字段名 列属性
ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性
--删除表的字段
ALTER TABLE 表名 DROP 字段名
所有的创建和删除尽量加上判断，以免报错
注意点：
1、``字段名，使用这个包起来
2、注释：--
3、sql关键字大小写不敏感，尽量用小写
Mysql数据管理
外键（了解即可）
方式一：在创建表的时候，就增加约束（外键），就是把某一列定义为外键，方便和另一张表的引用）
方式二：创建表后，再写语句增加约束，ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGE KEY(作为外键 的列） REFERENCES 那个表（哪个字段）
以上的外键都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰）
最佳实践，数据库就是单纯的表，只用来存数据，只有行和列
想用多张表的数据，想使用外键（程序去实现）
DML语言（全部记住）
DML：数据操作语言
insert
update
delete
添加
--插入语句（添加）
INSERT INTO 表名（【字段名1，字段2】）value('值'),('值2')...)
-- 插入语句
-- 注意列名是用``,飘键上面的，然后值是用''单引号，是不一样的
-- 插入语句，数据和字段一一对应
INSERT INTO `grade`(`gradename`) VALUES ('大四')
-- 插入多个字段
INSERT INTO `grade`(`gradename`)VALUES ('大四'),('大五'),('大六')
INSERT INTO `grade`(`gradename`,`pwd`) VALUES ('大四','111')
修改
-- 修改年级名称
update `grade` set `gradename`='大五' where gradeID =1;
-- 不指定条件的情况下，会改动所有语句
update `grade` set `gradename`='大五';
-- 语法
-- update 表名 set colnum_name = value where 条件
-- 条件：where id等于某个值 大于某个值 在某个区间等等
注意
--colnum name是数据库的列，尽量带上``
--条件，筛选的条件，如果没有指定，会修改所有的列
--value，是一个具体的值，或一个变量
删除
delete from `grade` where gradeID=1;
-- 语法
-- delete from 表名 [where条件]，但是如果不加where，会直接把整个表清空
-- TRUNCATE命令
-- 也是完全清空表 truncate `grade`
-- delete 和 truncate区别
-- truncate会回到1，自增会归零，delete不会
DQL查询数据
data query language:数据查询语言
select
-- 查询全部的学生
select * from student
-- 查询指定的字段
select `sex`,`phone` from student
-- 别名，给结果起一个名字，可以给字段起别名，也可以给表起别名
select `sex` as 性别 ,`phone` as 手机 from student
-- 函数 concat(a,b)
select concat ('姓名：',StudentName) as 新名字 from student
去重
-- 发现重复数据，去重
-- 去除selcet查询数据中重复的数据
select distinct `studentno` from result
-- 补充
select version()
where条件子句
作用：检索数据中符合条件的值
-- 查询考试成绩在95-100分之间
select studentno ,studentresult from result
where studentresult >= 95 && studentresult <= 100
-- 模糊查询（区间）
select studentno ,studentresult from result
where studentresult between 95 and 100
-- 除了1000号学生之外的同学的成绩
select studentno ,studentresult from result
where studentno != 1000
模糊查询：比较运算符
-- like
-- 查询姓刘的同学
-- like结合%（代表0到任意个字符）
select studentno,studentname from student
where studentname like '刘%';
-- 查询姓刘的同学，后面只有一个字的
select studentno,studentname from student
where studentname like '刘_';
-- 查询姓刘的同学，后面只有二个字的
select studentno,studentname from student
where studentname like '刘__';
-- 查询名字带有嘉字的
select studentno,studentname from student
where studentname like '%嘉%';
-- in
-- 查询 1001，1002学员
select studentno,studentname from student
where studentno in (1001,1002)
-- in 是具体的值，不像like,可以加%，_
-- null not null
-- 查询地址为空的学生
select studentno,studentname from student
where address = '' or address is null
select studentno,studentname from student
where borndate is not null
联表查询
join对比
/*思路
1、分析需求，分析查询的字段来自哪些表
2、确定使用哪种连接查询 ？7种
3、确定交叉点（两个表中哪个数据是相同的
判断条件：学生表中的studentno=成绩表中 studentno
*/
-- 要指定要哪个表的studentno,不然会报错
-- 并集
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
where s.studentno = r.studentno 也能用on
-- right join,会把右边的表，right join result r，就是指result所有字段都显示出来，如果没有的值会以空代替
--from 左边的表 right join 就是指以右边的表为基准
select s.studentno,studentname,subjectno,studentresult
from student s
right join result r
on s.studentno = r.studentno
-- left join 会把右边的表，left join result r，就是指student s所有字段都显示出来，如果没有的值会以空代替
select s.studentno,studentname,subjectno,studentresult
from student s
left join result r
on s.studentno = r.studentno
自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表
分页和排序
排序
-- 排序 ：升序 asc,降序desc
select s.studentno,studentname,subjectname,studentresult
from student s
right join result r
on s.studentno=r.studentno
inner join subject sa
on r.subjectno=sa.subjectno
order by studentresult desc
分页
-- 为什么要分页面
-- 缓解数据库压力，给人体验更好，瀑布流
-- 分布，每页显示五条数据
-- 语法： limit 起始值，页面的大小
-- limit 0,5 1-5
-- limit 1,5 2-6
-- 网页怎么做
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5
-- 第N页 limit (n-1)*pagesize,pagesize
-- pagesize:页面大小（数据量）
-- (n-1)*pagesize：起始值
-- n :当前页面
-- 数据总量/页面大小=总页数
select s.studentno,studentname,subjectname,studentresult
from student s
right join result r
on s.studentno=r.studentno
inner join subject sa
on r.subjectno=sa.subjectno
order by studentresult asc
limit 1,3
子查询（也称嵌套查询）
where(这个值是计算出来的）
本质：在where中嵌套一个子查询语句
使用子查询（由里及外）
select studentno,subjectno,studentresult
from result
where subjectno=（
selcet subjectno from subject
where subjectname='数据库结构-1'
)
联表查询更加方便
Mysql常用函数
常用函数
-- 数学运算
select abs(-8)-- 绝对值
select ceiling(9.4) -- 向上取整
-- 字符串函数
select char_length('我我') -- 2
select concat('我','你') -- 我你
需要再查吧
聚合函数（常用）
-- 计数
select count('borndae') from student -- 会忽略所有null值
select count(*) from student -- 本质计算所有行数
-- sum ,avg,max,min
数据库级别的MD5加密（扩展）
什么是MD5？
主要是增强算法复杂度和不可逆性
MD5不可逆，具体的值MD5是一样的
破解网站只是把常用的密码的MD5存起来，如果遇到复杂的密码就破解不了了
-- 明文密码
insert into testtable values(1,'zhangsan','123456'),(2,'lili','123456)
--加密
update testtable set pwd=MD5(pwd) where id =1
-- 插入的时候加密
insert into testtable values (4,'xiaoming',md5('123456'))
--如何校验，将用户传递过来的密码，进行md5加密，然后再对比加密后值
-- 一般密码一样，MD5的值也一样
事务
要么都成功，要么都失败
ACID原则（原子性，一致性，隔离性，持久性）
原子性：要么都成功，要么都失败
一致性：事务前后的数据完整性保持一致
持久性：事务一旦提交则不可逆，被持久到数据库中
隔离性：事务的隔离是多个用户并发访问数据库时，数据库为每一个用户开启事务，不被其他事务的操作所影响
1、sql执行 A给B转账
2、sql执行 B收到A转账
将以上一组SQL放在一个批次中执行
事务操作
执行事务的原理
-- mysql是默认开启事务自动提交的
set autocommit =0 -- 关闭
set autocommit =1 -- 开启
-- 手动处理事务
set autocommit =0
-- 事务开启
start transaction -- 标记一个事务的开始，从这个之后sql都在一个事务内
insert xx
insert xx
--提交：持久化（成功）
commit
-- 回滚：回到原来的样子（失败）
rollback
--事务结束
set autocommit =1 -- 开启自动提交
-- 了解
savepoint 保存点名 --设置一个事务的保存点
rollback to savepoint --回滚到保存点
模拟真实场景
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交
索引
索引（index)就是帮助mysql高效获取数据的数据结构
索引分类
--主键索引(primary key)
唯一标识，主键不可重复，只能一个列作为主键（一定要有值，例如id)
--唯一索引 (unique key)
唯一索引可以重复，多个列可可以标识位（可以空值，例如身份证）
--常规索引 (key/index)
默认的，Index,key关键字来设置
--全文索引（fulltext)
-- 显示所有的索引信息
show index from student
-- 增加一个全文索引（索引名） 列名
alter table student add fulltext index 'studentname'('studentname')
--explain 分析sql执行的状况
explain select *from app_user where 'name'='11' 可以得出这个sql查询了多少行才得到这个结果
索引原则
--索引不是越多越好
--不要对经常变动的数据加索引
--小数据量不需要加索引
--索引一般加在常用来查询的字段上
数据库用户管理
Mysql备份
1、直接拷贝物理文件
2、用可视化工具导出--如果导不出，可以在路径最后于加一个文件名shop --result-file="C:\Users\60237\Desktop\learn.sql"
3、用命令行导出mysqldump导出--这是sql一个mysqldump.exe
-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
可以-w携带备份条件
-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
规范化数据库设计
为什么需要数据库设计
当数据库比较复杂时我们需要设计数据库
--糟糕的数据库设计 :
数据冗余，存储空间浪费
数据库插入和删除都会麻烦、异常，数据更新和插入的异常
程序性能差
--良好的数据库设计 :
节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发
软件项目开发周期中数据库设计 :
需求分析阶段：分析客户的业务和数据处理需求
概要设计阶段：设计数据库的E-R模型图 , 确认需求信息的正确和完整.
--设计数据库步骤：
收集信息，分析需求
与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
标识实体[Entity]
标识数据库要管理的关键对象或实体,实体一般是名词
标识每个实体需要存储的详细信息[Attribute]
标识实体之间的关系[Relationship]
三大范式
第一范式 (1st NF)
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
第二范式(2nd NF)
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
第二范式要求每个表只描述一件事情
第三范式(3rd NF)
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
--规范化和性能的关系（规范数据库的设计）
关联查询的表不得超过三张表
为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询
JDBC
步骤总结：
加载驱动
连接数据库 DriverManager
获得执行SQL的对象 Statement
获得返回的结果集 ResultSet
释放连接
import java.sql.*;
public class jdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动
//2.用户信息和 url
// useUnicode=true&characterEncoding=utf8&useSSL=true
String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username = "root";
String password = "123456";
//3.连接成功,数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行 SQL 的对象
Statement statement = connection.createStatement();
//5.执行 SQL 的对象去执打 SQL,可能存在结果,查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("name=" + resultSet.getObject("NAME"));
System.out.println("pwd=" + resultSet.getObject("PASSWORD"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("birth=" + resultSet.getObject("birthday"));
System.out.println("==================================================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
优化版
把数据库信息写到配置文件db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=UTF-8&useSSL=false
username=root
password=123456
写一个JDBC工具类
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
//驱动只用加载一次
Class.forName(driver);
} catch (IOException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
//获取数据库连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//关闭数据库连接
public static void release(Connection conn, Statement stmt, ResultSet rs) {
if (rs!= null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt!= null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (conn!= null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
} }
直接调用工具库，写对应的sql语句
import java.sql.*;
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection con= null;
Statement stmt= null;
ResultSet rs= null;
try {
Connection connection = JdbcUtils.getConnection();
Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("name=" + resultSet.getObject("NAME"));
System.out.println("pwd=" + resultSet.getObject("PASSWORD"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("birth=" + resultSet.getObject("birthday"));
System.out.println("==================================================");
}
}catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(null, null, null);
}
}
SQL注入问题
通常拼接or，会把其它数据找出来
String sql = "SELECT * FROM users where name='' or 1=1 and password= '' or 1=1";
原理就是or 1=1 ,1=1为true，所以在where条件的时候，or true的时候也找出来了
资料补充
PreparedStatement 对象
PreparedStatement可以防止SQL注入。
增加
package com.kuang.lesson03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TextInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
// 区别
//使用 ? 占位符代替参数
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql); //预编译SQL,先sqL,然后不执行
//手动给参数赋值
st.setInt(1,4);
st.setString(2,"blue");
st.setString(3,"123456");
st.setString(4,"24736743@qq.com");
// 注意点: sqL.Date 数据库 java.sql.Date
// utiL.Date Java new Date().getTime()获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(conn,st,null);
}
}
}
DBCP-C3P0连接池
数据库连接--执行完毕--释放
连接-释放-十分浪费资源，每来一个，都要连接一次，执行SQL，释放一次，来10次sql就得连接10次，释放10次
池化技术:准备一些预先的资源，过来就连接
--开门，业务员：等待--服务
常用连接数：100个
最小连接数：10
最大连接数：15
等待超时：100ms
编写连接池，实现一个接口 Data Source
DBCP或C3P0接口
为什么JDBC频繁地连接释放数据库很浪费资源，需要连接池来解决问题，从原理和代码来解释？？
一、原理分析
1. JDBC每次新建连接的代价
JDBC Connection conn = DriverManager.getConnection(...)
每一次getConnection()，其实都要：
TCP三次握手建立网络连接
授权认证（用户名、密码验证）
数据库资源分配和初始化
用完后 conn.close()，不仅仅销毁Java对象，而是向数据库真正发一个“连接释放”动作，关闭Session与Socket。
上面这些动作，每次都做耗时高、资源消耗大（尤其高并发短操作场景）。
2. 为什么频繁连接/关闭资源消耗大？
网络、数据库服务、连接建链和认证都要耗费CPU、内存和带宽，甚至在高并发压力下，数据库会因为反复创建/释放连接而雪崩。
如果每个请求都全流程新建和释放连接，高QPS下数据库很快被拖死。
3. 连接池机制的工作原理
连接池（如 Druid、HikariCP、C3P0、DBCP）在启动时，先统一地建立一批Connection连接（比如10或50个），放到池里。
来一个业务线程时，从池子里“借”一个已建好的连接，用完后并不是彻底关掉连接，而是把它“归还”到池里（即返回状态）。
下一个请求继续借用池里的连接，池中连接可被循环复用，大大减少了创建和销毁连接的开销。
二、代码对比
1. 不用连接池的普通JDBC操作（低效）：
java
for(int i=0; i<1000; i++) {
Connection conn = DriverManager.getConnection(url, user, pwd); // 每次新建连接
// ...SQL操作
conn.close(); // 每次都彻底关闭
}
结果：每次1000次访问，数据库要1000次建立和销毁TCP连接、Session，消耗极大。
2. 用连接池后的写法（高效）：
先初始化连接池（伪代码，实际用Druid/HikariCP/DBCP等DataSource实现）：
java
// 1. 连接池初始化（如只需一次配置即可）
DataSource ds = ...; // 初始化（如 Druid/HikariCP/DBCP）
for(int i=0; i<1000; i++) {
Connection conn = ds.getConnection(); // 只是借用一个，池里已经持有连接
// ...SQL操作
conn.close(); // 其实不是关闭，而是归还给池，下次可再用
}
结果：1000次业务，数据库侧最多只持有设定数量（如50个）连接，而且都是复用，性能大幅提升！
三、形象比喻
不用池：就像每次你喝水都去盖楼、修管道、装饮水机、喝完再拆。太累太慢！
连接池：就是公司大厅已经有10个饮水机，谁渴了谁用，用完再排走，下个人马上能用。
四、总结
JDBC频繁连接/关闭是极度浪费资源的（因为带宽、CPU、Socket和数据库会话初始化都有消耗）。
连接池本质上是通过预建、复用、生命周期托管，显著提升数据库使用效率，并发场景下成为高性能数据库访问的标配。
实践建议
所有生产环境的Java数据库访问必用连接池（Spring Boot也强制内置了HikariCP）。
配置合理的最大连接数，既保证高并发需求，也防止数据库被打穿。
浙公网安备 33010602011771号