MySQL
1. 数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节 常用的
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算的时候,一般使用decimal
字符串
- char 字符串固定大小的0~255
- varchar 可变字符串 0~65535 常用的String
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1 保存大文本
时间日期
java.util.Date
- datetime yyyy-MM-dd HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数
null
- 没有值,未知
2. 数据库的字段属性
Unsigned:
- 无符号整数
- 声明了该列不能为负数
zerofill:
- 0填充的
- 不足的位数,使用0来填充,int(3),5–005
自增:
- 通常理解为自增,自动在上一条记录的基础上+1,(默认)
- 通常用来设计唯一主键~index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空 NULL not null
- 假设设置为not null,如果不给他赋值,就会报错
- NULL,如果不填写值,默认就是null
默认
- 设置默认的值
- sex,默认值为男,如果不指定该列的值,则会有默认的值
**拓展:**项目规范,必须要有这些字段
- id 主键
- version 乐观锁
- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间
3. 数据表的类型
数据库引擎:
- INNODB 默认使用
- MYISAM 早些年使用
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
4. DML数据管理语言
4.1 外键
阿里JAVA规范:【强制】不得使用外键与级联,一切外键概念必须在应用层解决
4.2 DML语言
- insert
- update
- delete
4.3 添加
INSERT INTO student(username,pwd,sex) VALUES('zhangsan','123456','1');
INSERT INTO student(username,pwd,sex) VALUES('jack','123456','1'),('rose','123456','0');
4.4 修改
UPDATE student SET username='lisi' where id=2;
UPDATE student SET username='lisi',sex='0' where id=2;
4.5 删除
DELETE FROM student WHERE id=2
TRUNCATE与DELETE
TRUNCATE 作用:完全清空一个数据库表,表的结构和索引约束不会变
- 相同点:都能删除数据,都不会删除表结构
- 不同点:TRUNCATE 重新设置自增列,计数器会归零,不会影响事务
了解即可:DELETE删除的问题,重启数据库后的现象
- InnoDB 自增列会从1开始(存在内存中的,断电即失)
- MyISAM 继续从上一个自增量开始(存在文件中的,不会丢失)
5. DQL数据查询语言
5.1 指定查询字段
-- 查询全部
SELECT * FROM tb_category
-- 查询指定列
SELECT name,parent_id FROM tb_category
-- 起别名AS
SELECT name AS 姓名,parent_id AS 父id FROM tb_category AS c
-- CONCAT
SELECT CONCAT('名字:',name) AS 新名字 FROM tb_category
-- 去重DISTINCT
SELECT DISTINCT parent_id FROM tb_category
-- 查询系统版本
SELECT VERSION()
-- 用来计算(表达式)
SELECT 100*3-1 AS 计算结果
-- 查询自增的步长(变量)
SELECT @@auto_increment_increment
-- 查看排序,并+1查看
SELECT sort+1 AS 新排序 FROM tb_category
5.2 where条件子句
-- AND
SELECT id,sort FROM tb_category WHERE sort>=1 AND sort<=3
-- 模糊查询(区间)
SELECT id,sort FROM tb_category WHERE sort BETWEEN 1 AND 3
-- != not
SELECT id,sort FROM tb_category WHERE sort!=1
SELECT id,sort FROM tb_category WHERE NOT sort=1
-- like %(代表0到任意个字符) _(一个字符)
SELECT id,name FROM tb_category WHERE name LIKE '%电子%'
SELECT id,name FROM tb_category WHERE name LIKE '电子_'
-- IN
SELECT id,name FROM tb_category WHERE parent_id IN (0,1,2,3)
-- NULL
SELECT id,name FROM tb_category WHERE name='' OR name IS NULL
-- NOT NULL
SELECT id,name FROM tb_category WHERE name!='' OR name IS NOT NULL
5.3 联表查询
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行(取交集),只要两个表的公共字段有匹配值,就将这两个表中的记录组合起来。以一个共同的字段求两个表中符合要求的交集,并将每个表符合要求的记录以共同的字段为牵引合并起来。 |
left join | 从左表中返回所有的值,即使右表中没有匹配 |
right join | 从右表中返回所有的值,即使左表中没有匹配 |
-- inner join
SELECT
s.studentno,
studentname,
subjectno,
studentresult -- studentno前面要加点,要么从s表查,要么从r表查
FROM
student AS s -- 因为两个表都有studentno,所以给他们分别命名否则会报错
INNER JOIN result AS r
WHERE
s.studentno = r.studentno
-- right join
SELECT
s.studentno,
studentname,
subjectno,
studentresult
FROM
student s
RIGHT JOIN result r ON s.studentno = r.studentno
-- left join
SELECT
s.studentno,
studentname,
subjectno,
studentresult
FROM
student s
LEFT JOIN result r ON s.studentno = r.studentno
-- 查询缺考的同学
SELECT
s.studentno,
studentname,
subjectno,
studentresult
FROM
student s
LEFT JOIN result r ON s.studentno = r.studentno
WHERE
studentresult IS NULL
-- 思考题 (查询了参加考试的同学信息:学号,学生姓名,科目名,分数)
SELECT
s.studentno,
studentname,
subjectname,
studentresult
FROM
student s
RIGHT JOIN result r ON r.studentno = s.studentno
INNER JOIN SUBJECT sub ON r.subjectNo = sub.subjectNo
自连接:自己的表和自己的表连接(一张表拆为两张一样的表即可)
SELECT
a.NAME AS '父',
b.NAME AS '子'
FROM
tb_category AS a,
tb_category AS b
WHERE
a.id = b.parent_id
5.4 分页和排序
分页:limit
排序:order by 升序ASC 降序DESC
-- 分页,要求每页只显示5条数据
-- 语法: limit 起始值,页面的大小
-- limit 0,5 是1-5
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第N页 limit (N-1)*5
-- 规律: (n-1)*pageSize,pageSize,n表示当前页
-- [数据总数/页面大小 = 总页数]
SELECT
s.`StudentNo`,
`StudentName`,
`SubjectName`,
`StudentResult`
FROM
`student` s
INNER JOIN `result` r ON s.studentNo = r.StudentNo
INNER JOIN `subject` sub ON r.`subjectNo` = sub.`SubjectNo`
WHERE
`subjectName` = '数据库结构-1'
ORDER BY
studentResult ASC -- 升序
LIMIT 0,5
-- 查询,Java第一学年,课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,课程名称,分数)
SELECT
s.`StudentNo`,
`StudentNmae`,
`SubjectName`,
`StudentResult`
FROM
`student` s
INNER JOIN `result` r ON s.StudentNo = r.`SubjectNo`
WHERE
SubjectName = `Java第一学年`
AND StudentResult >= 80
ORDER BY
StudentResult DESC
LIMIT 0,10
5.5 子查询
/*
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/
-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT
studentno,
r.subjectno,
StudentResult
FROM
result r
INNER JOIN `subject` sub ON r.`SubjectNo` = sub.`SubjectNo`
WHERE
subjectname = '数据库结构-1'
ORDER BY
studentresult DESC;
-- 方法二:使用子查询(执行顺序:由里及外)
SELECT
studentno,
subjectno,
StudentResult
FROM
result
WHERE
subjectno =(
SELECT
subjectno
FROM
`subject`
WHERE
subjectname = '数据库结构-1'
)
ORDER BY
studentresult DESC;
-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT
s.studentno,
studentname
FROM
student s
INNER JOIN result r ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub ON sub.`SubjectNo` = r.`SubjectNo`
WHERE
subjectname = '高等数学-2'
AND StudentResult >= 80
-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT
r.studentno,
studentname
FROM
student s
INNER JOIN result r ON s.`StudentNo` = r.`StudentNo`
WHERE
StudentResult >= 80
-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT
r.studentno,
studentname
FROM
student s
INNER JOIN result r ON s.`StudentNo` = r.`StudentNo`
WHERE
StudentResult >= 80
AND subjectno =(
SELECT
subjectno
FROM
`subject`
WHERE
subjectname = '高等数学-2'
)
-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT
studentno,
studentname
FROM
student
WHERE
studentno IN (
SELECT
studentno
FROM
result
WHERE
StudentResult >= 80
AND subjectno =(
SELECT
subjectno
FROM
`subject`
WHERE
subjectname = '高等数学-2'
)
)
5.6 分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:(根据不同的课程分组)
SELECT
`SubjectName`,
AVG( StudentResult ),
MAX( StudentResult )
FROM
result r
INNER JOIN `Subject` sub ON r.SubjectNo = sub.SubjectNo
GROUP BY
r.SubjectNo -- 通过什么字段来分组
HAVING
AVG( StudentResult )> 80
6. MySQL函数
6.1 常用函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('2323232') -- 返回字符串长度
SELECT CONCAT('我','233') -- 拼接字符串
SELECT INSERT('java',1,2,'cccc') -- 从某个位置开始替换某个长度
SELECT UPPER('abc')
SELECT LOWER('ABC')
SELECT REPLACE('坚持就能成功','坚持','努力')
-- 查询姓 周 的同学 ,改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'
-- 时间跟日期函数(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
6.2 聚合函数
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
… | … |
6.3 数据库级别的MD5加密
什么是MD5
主要增强算法复杂度不可逆性。
MD5不可逆,具体的MD5是一样的
MD5破解原理,背后有一个字典,MD5加密后的值,加密前的值
7. 事务
https://blog.csdn.net/weixin_46204056/article/details/122647681
ACID
- 原子性(Atomicity):要么都成功,要么都失败
- 一致性(Consistency):事务前后的数据完整性要保持一致
- 持久性(Durability)–事务提交:事务一旦提交就不可逆转,被持久化到数据库中
- 隔离性(Isolation):事务产生多并发时,互不干扰
隔离产生的问题:
- 脏读:指一个事务读取了另外一个事务未提交的数据。
- 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
- 虚读(幻读):在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。(一般是行影响,多了一行)
-- mysql 自动开启事务提交
SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启(默认的)
-- 手动处理事务
SET autocommit =0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的SQP都在同一个事务内
INSERT XX
INSERT XX
-- 提交 : 持久化(成功)
COMMIT
-- 回滚: 回到原来的样子(失败)
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 删除保存点
8. 索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。索引在小数据的时候,用处不大,但是在大数据的时候,区别十分明显
8.1 索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引 (PRIMARY KEY)
- 唯一的标识,主键不可重复,只能有一个列作为主键
- 唯一索引 (UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识唯一索引
- 常规索引(KEY/INDEX)
- 默认的,index,key关键字来设置
- 全文索引(FULLTEXT)
- 在特点的数据库引擎下才有,MyISAM
- 快速定位数据
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后,增加索引
-- 显示所有的索引信息
SHOW INDEX FROM 表
-- 增加一个索引
ALTER TABLE 表 ADD FULLTEXT INDEX 索引名(字段名)
-- EXPLAIN 分析sql执行状况
EXPLAIN SELECT * FROM student -- 非全文索引
8.2 索引原则
索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash 类型的索引
Btree: 默认innodb 的数据结构
阅读:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
9. MySQL备份
保证重要数据不丢失
MySQL数据库备份的方式
- 直接拷贝物理文件
- 在可视化工具中手动导出:在想要导出的表或者库中,右键选择备份和导出
10. 规范数据库设计
10.1 数据库设计
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦,异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图 E-R图
设计数据库的步骤(个人博客)
1、收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 友链表(友链信息)
- 自定义表(系统信息,某个关键的字,或者某些主字段)
- 说说表(发表心情…id ,content ,time)
2、标识实体(把需求落地到每个字段)
3、 标识实体之间的关系
- 写博客 user–>blog
- 创建分类 user–>category
- 关注 user–>user
- 友链–>links
- 评论 user–>user
10.2 三大范式
为什么需要数据规范化?
-
信息重复
-
更新异常
-
插入异常
-
删除异常
-
无法正常显示异常
-
丢失有效的信息
-
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。(规范数据库的设计)
规范性和性能的问题
关联查询的表,不得超过三张表
- 考虑商业化的需求和目标(成本和用户体验) 数据库的性能更加重要
- 再规范性能的问题的时候,需要适当的考虑一下,规范性
- 故意给某些表加一些冗余的字段(从多表,变成单表)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
11. JDBC
11.1 测试代码
package com.kuang.lesson01;
//我的第一个JDBC程序
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcFirstDemo {
public static void main(String[] args) throws Exception {
//1. 加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法
//2. 用户信息和url
//useUnicode=true&characterEncoding=utf8&&useSSL=true
String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";
String name = "root";
String password = "123456";
//3. 连接成功,返回数据库对象 connection代表数据库
Connection connection= DriverManager.getConnection(url,name,password);
//4. 执行SQL的对象 statement 执行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("password+"+resultSet.getObject("PASSWORD"));
System.out.println("email+"+resultSet.getObject("email"));
System.out.println("birthday+"+resultSet.getObject("birthday"));
}
//6. 释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
1.加载驱动
2.连接数据库 DriverManager
3.获取执行SQL的对象 Statement
4.获得返回的结果集
5.释放连接
11.2 statement对象
Jdbc中的statement对象用于向数据库发送SQL语句,对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sq|语句, executeUpdate执行完后, 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发生查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
11.3 SQL注入问题
sql存在漏洞,会被攻击导致数据泄露 SQL会被拼接 or
package com.thb.study2;
import com.thb.study2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import static com.thb.study2.Utils.JdbcUtils.getConnection;
public class SQL {
public static void main(String[] args) {
//SQL注入
login("' or '1=1","' or '1=1");
}
public static void login(String name,String password){
Connection conn =null;
Statement st = null;
ResultSet rs =null;
try {
conn = getConnection();//获取连接
st = conn.createStatement();//获取SQL执行对象
String sql = "select * from users where `NAME`='"+ name +"' AND `PASSWORD`='"+ password +"'" ;
rs=st.executeQuery(sql);//查询完毕返回结果集
while (rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
System.out.println("==========================================");
}
JdbcUtils.release(conn,st,rs);
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
11.4 PreparedStatement对象
PreparedStatement 可以防止SQL注入 ,效率更高。
防止SQL注入本质,传递字符 带有“ ”,转义字符会被转义,使用?占位符
conn = getConnection();//获取连接
String sql = "SELECT * FROM users WHERE `NAME`=? and `PASSWORD`=?";
st = conn.prepareStatement(sql);//获取SQL执行对象
st.setString(1, name);
st.setString(2, password);
rs = st.executeQuery();//查询完毕返回结果集
11.5 JDBC事务
- 原子性:要么全部完成,要么都不完成
- 一致性:结果总数不变
- 隔离性:多个进程互不干扰
- 持久性:一旦提交不可逆,持久化到数据库了
隔离性的问题:
- 脏读: 一个事务读取了另一个没有提交的事务
- 不可重复读:在同一个事务内,重复读取表中的数据,表发生了改变
- 虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
代码实现:
- 开启事务
conn.setAutoCommit(false);
- 一组业务执行完毕,提交事务
- 可以在catch语句中显示的定义回滚,但是默认失败会回滚
package com.thb.study3;
import com.thb.study2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test03 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JdbcUtils.getConnection();
connection.setAutoCommit(false);
String sql = "update account set money=money-100 where `name`='A'";
ps = connection.prepareStatement(sql);
ps.executeUpdate();
String sql2 = "update account set money=money+100 where `name`='B'";
ps = connection.prepareStatement(sql2);
ps.executeUpdate();
connection.commit();
System.out.println("成功d=====( ̄▽ ̄*)b");
} catch (SQLException e) {
//默认失败会回滚
e.printStackTrace();
}finally {
JdbcUtils.release(connection,ps,null);
}
}
}
12. 数据库连接池
数据库连接–执行完毕–释放
连接–释放 十分浪费资源
池化技术: 准备一些预先的资源,过来就连接预先准备好的
编写连接池,实现一个接口 DateSource
- DBCP
- C3P0
- Druid: 阿里巴巴