DB DataBase数据库
数据库分类
- 关系型数据库（SQL），通过表和表之间，行和列之间的关系进行数据的存储
- MySQL
- Oracle
- Sql Server
- DB2
- SQLlite
- 非关系型数据库(NoSQL ,not only)，以对象存储，通过对象的自身属性来决定
- Radis
- MongDB
DBMS(数据库管理系统)
- 数据库的管理软件
入门
一般基字符集用utf8，数据库排序规则utf8_general_ci
表中数据类型，对于文本采用varchar
连接数据库
命令行链接：
//所有的语句都是用分号结尾
show databases//查所有的数据库
use 数据库名//切换数据库
show tables//查看数据库中所有的表
describe 表名//显示数据库中所有表的信息
create databases 数据库名//创建一个数据库
exit//退出连接
-- 单行注释（sql本身注释）
/*
多行注释
*/
数据库xx语言
DDL 定义
DML 操作
DQL 查询
DCL 操作
操作数据库
F12自动整理代码
操作数据 库语句(了解)
mysql不区分大小写
IF NOT EXISTS / IF EXISTS可写可不写，不写的话，可能会报错
添加数据库
CREAT DATABASE IF NOT EXISTS 数据库名
删除数据库
DROP DATABASE IF EXISTS 数据库名
使用数据库，如果表名或者字段名是一个特殊字符，就需要用到``
USE `数据库名`
查看数据库
SHOW DATABASES查看所有的数据库
创建
创建表，内容用（）包含（不是{ }）
用，结尾
列的注释用COMMENT 内容用" "或’ ’
通过PRIMARY KEY设置主键，一般一个表只能有一个主键
查看
SHOW CREATE DATABASE 数据库名 查看创建数据库的语句
SHOW CREATE TABLE 数据表名 查看数据表的语句
DESC 数据表 查看数据表的具体结构
数据库的数据类型（列类型）
数值
- tinyint 十分小的数据 1字节
- smallint 较小的数据 2字节
- mediumint 中等大小的数据 3字节
- int 标准的整数 4字节
- bigint 较大的数据 8字节
- float 单精度 4字节
- double 双精度 8字节 （精度问题！）
- decimal 字符串的浮点数 金融计算的时候一般使用
字符串
- char 字符串固定大小的 0-255
- varchar 可变字符串 0-65535 常用的string
- tinytext 微型文本 2^8-1 一般用来写博客
- text 文本串 2^16-1 保存大文本
时间日期
- date YYYY-MM-DD 日期
- time HH:mm:ss
- datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.1.1 到现在的毫秒数
- year 年份表示
null
- 没有值，未知
- 注意不要使用NULL进行运算
数据库的字段属性（重点）
Unsigned
- 无符号的整数
- 不能声明为负数
zerofill
- 0填充的
- 不足的位数使用0来填充
自增 AUTO_INCREMENT
- 通常理解为自增，自动在上一条记录的基础上+1（默认）
- 通常用来设置唯一的主键，必须是整数类型
- 可以自定义设置自增的起始值和步长
非空 NULL not null
- 假设设置为not null，如果不给他赋值就会报错
- NULL，如果不填写值，默认就是null
默认 DEFAULT
- 设置默认值
- 如果不指定值，则会有默认的值
常用规范字段，开发时每一张表都必须存在以下五个字段
- id：主键
- ‘version’：乐观锁
- is_delete：伪删除
- gmt_create：创建时间
- gmt_updata：修改时间
数据表的类型
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为MYISAM的两倍 |
常规使用操作：
- MYISAM节约空间，速度较快
- INNODB安全性高，支持事务的处理，可以多表多用户操作
设置数据库的字符集编码
不设置会才用默认的字符集编码Latin1（不支持中文）
设置：CHARSET=utf-8
修改和删除表
修改
ALTER TABLE 数据表名 RENAME AS 新名字 修改表名
ALTER TABLE 数据表名 ADD 字段名 字段属性 增加表的字段
ALTER TABLE 数据表名 MODIFY 老字段名 新的字段属性 修改字段的约束
ALTER TABLE 数据表名 CHANGE 老字段名 新字段名（后面可以跟新的字段属性） 字段重命名
删除 最好加上IF EXISTS防止报错
ALTER TABLE 数据表名 DROP 字段名 删除字段
DROP TABLE IF EXISTS 数据表名 如果表存在则删除数据表
MySQL数据管理
外键（了解即可）建议不要输代码，用可视化
KEY `FK_字段名 （\字段名`） 定义外键key
CONSTRAINT `FK_字段名` FOREIGN KEY (`字段名`) REFERENCES `表明` (`字段名`) 给这个外键添加约束
以上是物理外键，数据库级别的外键，不建议使用
最好：
- 数据库就是单纯的表，只用来存数据，只有行和列
- 我们想使用多张表的数据，想使用外键用程序去实现
删除有外键关系的表的时候，必须要先删除引用别人的表，再删除被引用的表
DML语言（背下来）
数据操作语言 ：
- insert
- update
- delete
添加insert
INSERT INTO 表名 ([字段1，字段2....]) VALUES ('值1' , '值2'......)
如果是一次插入多行，VALUES后面要用多个括号，每个括号代表一行
由于主键自增，我们可以省略（如果不写字段，会一一匹配）
修改update
UPDATE 表名 SET 字段名=新的内容,字段名=新的内容（用逗号隔开多个字段名） WHERE id=1（这个id=1可以修改，相当于一个索引，一般找主键。也可以是大小于这些运算符）
新的内容（value）也可以是变量，比如CURRENT_TIME
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| <>或！= | 不等于 |
| > | > |
| < | < |
| BETWEEN… AND… | 区间，闭合 |
| <= | <= |
| >= | >= |
| AND | 多个条件都成立 |
| OR | 或语句|| |
如果没有WHERE指定条件，会改变表内所有的
删除delete
DELETE FROM 表名 WHERE 条件，不写WHERE是全删
TRYNCATE:完全清空一个数据库表，表的结构和索引约束不会变
全删的区别
- 相同点：都可以删除数据，都不会删除表结构
- 不同点：
- TRUNCATE重新设置自增列，计数器会归零
- TRUNCATE不会影响事务
DELETE删除的问题，删除后重启数据库的现象：
- InnoDB引擎，自增列会从1开始（存在内存中，断电即失）
- MyISAM继续从上一个自增量开始（存在于文件中，不会丢失）
DLQ查询数据（最重要）
SELECT语法总结
select 去重 要查询的字段 from 表（表和字段可以取别名）
xxx（join类型） join 要连接的表 on 等值判断
where（具体的值，子查询语句）
group by（通过哪个字段来分组）
having（过滤分组后的信息，条件和where是一样的，位置不同）
order by...（通过哪个字段排序） [升序/降序]
limit 指定查询的记录从哪条至哪条
Date Query Language:数据查询语言
- 所有的查询操作都用它
- 简单的，复杂的查询都能做
- 数据库中最核心的语言
- 使用频率最高
指定查询字段
SELECT 字段 FROM 表 如果字段填通配符*，就是全部
使用别名，给结果起一个名字 AS
SELECT 字段 AS 别名 FROM 表字段一般要加``，但是别名可以不用 不加AS也可以
这样可以让表头显示的不是英语字段名，而是一个更好看懂的别名，也可以给表起别名
函数Concat（a,b）
在字段处，使用CONCAT( ‘内容’ ，字段名)
最终查看的表每一个字段内容都会是 CONCAT中的内容+字段值
去重DISTINCT
用SELECT DISTINCT
表达式
可以在select查询时，在语句中输入表达式，可以让所有结果进行计算后在展示
表达式：文本值，列，Null，函数，计算表达式，系统变量…
where条件子句
作用：检索数据中符合条件的值
搜索的条件可以是多个表达式的组合（&&，||，！，BETWEEN AND）结果是一个布尔值
模糊查询
本质是一群比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 为null |
| IS NOT NULL | a is not null | 不为null |
| BETWEEN | a between b and c | 若a在bc之间 |
| LIKE | a like b | SQL匹配 |
| IN | a in (a1,a2,a3…) | a为a1,a2…中的任意某个值 |
like可以与%（代表0-任意个字符）和_搭配（代表一个字符）。查询后面还有x个字符，用x个_
查询含有a的结果，用%a%
联表查询
JOIN对比
一共有7种join类型，使用join要确定交叉点，找哪个数据是相同的
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s//表1，别名为s
INNER JOIN result AS r//表2，别名为r。取交集inner
WHERE s.studentno = r.studentno//判断语句
| 操作 | |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回 |
| right join | 会从左表中返回所有的值，即使右表没有匹配 |
| left join | 类似 |
join（链接的表）on（判断的条件） 连接查询
where等值查询
将join on改成join where结果一样，但是join in相当于一个整体语句，一般使用这个
自连接
核心：一张表拆成两张一样的表
分页limit和排序order by
排序 升序ASC 降序DESC
ORDER BY 通过哪个字段排序 怎么排
分页
LIMIT 当前页 页面的大小
LIMIT 0,5 0是起始位置（相当于第一条数据），5是显示的数据数。
所以第一页为0,5 第二页为5,5 第三页为10,5 第n页为（n-1)*页面大小，页面大小
子查询
本质：在where中再嵌套一个子查询语句 where(select*from)
分组与过滤
GROUP BY通过什么字段来分组
HAVING过滤分组后的结果
MySQL常用
常用函数
abs()//绝对值
ceiling()//向上取整
floor()//向下取整
rand()//返回一个0-1的随机数
sign()//判断符号，0返回0，正数返回1，负数返回-1
char_length()//字符串长度
concat()//拼接字符串
insert()//查询，从某个位置开始替换某个长度
lower()//小写
upper()//大写
instr()//返回第一次出现的子串的索引
replace()//替换出现的指定字符串
substr()//返回指定的子字符串
reverse()//反转
current_date()//获取当前日期
curdate()//获取当前日期
now()//获取当前时间
localtime()//本地时间
sysdate()//系统时间
聚合函数（更常用）
| 函数 | 描述 |
|---|---|
| count | 计数 |
| sum | 求和 |
| avg | 平均值 |
| max | 最大值 |
| min | 最小值 |
| … | … |
count(指定列) 会忽略所有的null值
count(*) 不会忽略null值 走所有列
cout(1) 不会忽略null值 只走一列
*与1其实差不多（不计效率）
数据库级别的MD5加密（扩展）
事务
什么是事务
要么都成功，要么都失败（多条sql）
事务原则：ACID原则 原子性，一致性，隔离性，持久性 （脏读，幻读…）
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所
扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该
其有任何影响
脏读：
指一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
（一般是行影响，如下图所示：多了一行）
开启事务
mysql默认开启事务自动提交
SET autocommit = 0//关闭。等于1为开启
事务开始
关闭事务自动提交
START TRANSACTION标记一个事务的开始，从这个之后的sql都在同一个事务中
提交：持久化（成功）
COMMIT
回滚：回到原来的样子（失败）
ROLLBACK
事务结束
开启事务自动提交
了解
SAVEPOINT 保存点名 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 回滚到保存点
RELEASE SAVEPOINT 删除保存点
索引
索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个
-
主键索引（primary key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
-
唯一索引（unique key）
- 避免重复的列出现，唯一索引可以重复（多个列都可以标识为唯一索引）
-
常规索引（key/index）
- 默认的，index/key关键字来设置
-
全文索引（fulltext）
- 在特定的数据库引擎下才有
- 快速定位数据
显示所有的索引信息：SHOW INDEX FROM 表名
添加索引（语法1）：ALTER TABLE 表名 ADD 索引类型 索引名（字段名） 主键索引不需要写索引名
添加索引（语法2）：CREATE 索引类型 索引名 ON 表（字段）
索引名命名规范：id_表名_字段名
分析sql执行的状况：EXPLAIN
索引原则
- 索引不是越多越好
- 不要对经常变动的索引加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
权限管理与备份
用户管理
用户表：mysql.user 管理用户其实就是对这张表进行增删改查
创建用户
CREATE USER 用户名 IDENTIFIED BY 登陆密码
修改密码
SET PASSWORD = PASSWORD(新密码)
SET PASSWORD FPR 用户名= PASSWORD(新密码) 修改指定用户的密码
重命名
RENAME USER 用户名 TO 新用户名
用户授权
GRANT ALL PRIVILEGES ON 库.表 TO 用户名 授予用户该表的全部权限，但是没有GRANT权限（不能给别人授权）
查询权限
SHOW GRANTS FOR 用户名
SHOW GRANTS FOR root@localhost 查看ROOT用户的管理权限（最高管理员）
撤销权限
REVOKE 哪些权限 ON 库.表 FROM 用户名
删除用户
DROP USER 用户名
MySQL备份
MySQL数据库备份方式：
- 直接拷贝物理文件（date文件夹）
- 通过SQLyog可视化工具手动导出
- 使用命令行导出 mysqldump 在命令行(cmd)使用
规范数据库设计
设计数据库步骤（以个人博客为例）
- 收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字，一些主字段）
- 标识实体（把需求落到每个字段）
- 标识实体之间的关系
三大范式
第一范式
要求数据库表的每一列都是不可分割的原子数据项
第二范式
前提：满足第一范式
每张表只描述一件事情
确保数据库表中的每一列都与主键相关
eg：主键是订单号；
产品号，产品数量，产品价格是一张表；
订单金额，订单时间是另一张表；
第三范式
前提：满足前两个范式
在第二范式的情况下，消除传递依赖
确保数据表中的每一列数据都是和主键直接相关，而不能间接相关
规范性和性能问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
- 在规范性能问题的时候，适当考虑规范性
- 故意给某些表增加一些冗余的字段（从多表查询变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
JDBC（重点）
第一个程序
package com.zaughter.lesson01;
import java.sql.*;
//第一个jdbc程序
public class JdbcFirst {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法
//2.连接信息，用户信息和url
//分别为：支持中文编码 设置中文字符集为utf-8 使用安全的连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF8&useSSL=true";
String username = "root";
String password = "zhehe2004";
//3.连接成功，得到数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象
Statement statement = connection.createStatement();
//5.执行SQL,查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部的查询出来的结果
System.out.println("===============================================");
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("NAME="+resultSet.getObject("NAME"));
System.out.println("PASSWORD="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
System.out.println("===============================================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
重点记忆：jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF8&useSSL=true";
分别为：支持中文编码 设置中文字符集为utf-8 使用安全的连接
步骤总结：
- 加载驱动
- 连接数据库DriverManager
- 获得执行sql的对象Statement
- 获得返回的结果集
- 释放连接
分析
DriverManager
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");//推荐使用这个
Connection connection = DriverManager.getConnection(url, username, password);
//connection代表数据库，这个对象有一系列方法
//数据库自动提交setAutoCommit
//事务提交commit
//事务回滚rollback
URL
mysql端口号默认3306 协议为jdbc:mysql
协议://主机地址:端口号/数据库名？参数1&参数2&参数3
Statement执行SQL的对象 PrepareStatement也是
String sql = "SELECT * FROM users";//编写SQL
statement.executeQuery()//查询操作返回ResultSet
statement.execute()//执行任何SQL
statement.executeUpdate()//更新，插入，删除。都是用这个，返回一个受影响的函数
ResultSet查询的结果集：封装了所有的查询结果
指定获得的数据类型
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部的查询出来的结果
resultSet.getxxx();//指定类型，如果不知道就用Object
遍历，指针
resultSet.beforeFirst();//指针移动到最前面
resultSet.afterLast();//指针移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(指定行);//移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();
statement对象
增删改
将SQL语句写在String sql中
int num = statement.executeUpdate(sql)
num是被影响的行数
例子
public class Test {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获取数据库连接
statement = connection.createStatement();//获得SQL的执行对象
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
"VALUES(4,'zheng','123456','123@qq.com','2022-12-12')";
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
查
查一般用executeQuery(sql)
sql注入问题 preparestatement可以避免
由于程序中其实是将用户输入的字符串拼接成一个select的sql语句，所以输入的时候可以输入一些恒为真的语句，使程序把所有结果都查询出来放到结果集里面，最后被程序输出。
PrepareStatement对象
可以防止sql注入（本质：把传递进来的参数当作字符），并且效率更高
区别
statement对象需要在sql字符串中将完整的语句写下来，把字符串写死
但是PrepareStatement在写完键后，对于值可以用？作为占位符
public static void main(String[] args) {
Connection connection = null;
PreparedStatement st = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获取数据库连接
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
"VALUES(?,?,?,?,?)";
st = connection.prepareStatement(sql);//预编译SQL，先写sql，然后不执行
//手动给参数赋值，第一个位置是占位符下标（从1开始），第二个位置是值
st.setInt(1,41414)//给id赋值
st.setString(2,"zaughter")//给NAME赋值
//。。。。。。。
st.setDate(5,new java.sql.Date(new Date().getTime()));
/*注意点：util.Date java new Date().getTime()获得时间戳
sql.Date 数据库 java.sql.Date()将他转化为sql.Date
*/
st.executeUpdate();//执行
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
事务
代码实现
- 开启事务
conn.setAutoCommit(false)关闭事务自动提交就会自动开启事务 - 一组业务执行完毕，提交事务
- 开一在catch语句中现实的定义回滚语句，但是默认失败就会回滚
数据库连接池
数据库连接-----执行完毕------释放
其中连接释放十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
编写连接池，实现一个接口：DataSource
开源数据源实现
DBVP
C3P0
Druid：阿里巴巴的德鲁伊
使用了这些数据库连接池之后，在项目开发中就不需要编写连接数据库的代码了
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
