1、初识MySQL
1.1、什么是数据库
存储数据管理数据
1.2、数据库分类
关系型数据库:(行、列) SQL
- mysql
- Oracle
- SQL Server
- SQL Lite
- 通过表与表之间的关系,行和列之间的关系进行数据的存储
- 学生信息表、考勤表等等之间的表都有你的信息都是有关系的
非关系型数据库:{key:value} NoSQL NotOnly
定位的数据等等
- Redis
- MongoDB
- 对象存储 通过对象自身的属性来决定
DBMS(数据库管理系统)
用来管理数据
1.3、连接数据库以及一些常用的命令
命令行的情况下:
--所有的语句都使用;结尾
show databases; --展示拥有的数据库
use '数据库名'; --切换到某个数据库
show tables; --查看数据库中所有的表
describe '表名' --查看某个表
create database '名字'; --创建一个数据库
2、操作数据库
2.1、操作数据库
create database [if not exists] '名字' --创建一个数据库如果不存在的话
drop database [if exists] name --删除一个数据库
2.1、数据库的常用类型
比较生疏且常用的
— timestamp 时间戳(1970.1.1 到现在的毫秒数)
2.2、数据库的字段属性(重点)
Unsigned:
- 无符号的整数
- 声明了该列不能是负数
zerofill - 0填充
- 不足的数位用0来填充
- 假设规定填写10位数,你只填写了一位数,那么最终的结果是0000000001
每一个表都必须存在以下五个字段:
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
2.3、创建数据库表
2.4、数据库引擎
-
innodb 默认使用
-
myisam 早起使用
区别:MYISAM INNODB 事务支持 不支持 不支持 数据行 不支持(支持锁表) 锁定行 外键约束 不支持 支持 全文索引 支持 不支持 表的空间大小 较小 较大,约为MYISAM的两倍 -
MYISAM:节约空间,速度快
INNODB: 安全性高,支持事务的处理,多表多用户操作
设置数据库的字符集编码
CHARSET=utf8
不设置的话,会是MySQL默认的字符集编码Latin(不支持中文)
可以提前在my.ini中配置默认的编码
2.6、修改删除表
修改表
-- 修改表的字段(重命名、修改约束)
alter table teacher modify age varchar(11) -- 修改约束,将int修改为varchar
alter table teacher change age age1 int(1) -- 修改约束并重命名,将varchar修改为int
-- change和modify的区别是:能不能重命名
删除表
3、MySQL数据管理
3.1、外键
**学生表:**id、name、age、gradeId(年级)
**年级表:**id、gradeName
通过gradeId这个字段表明两个表之间的关系
删除有外键关系的表的时候,必须要先删除从表再删除主表
3.2、DML语言
3.3、添加
3.4、修改
-- 修改某个区间中的值
update `studnet` set `name`='zhangsan' where id between 2 and 5
-- 通过多个条件定位数据
update `studnet` set `name`='zhangsan' where id=2 and name="lisi"and ......
3.5、删除
delete from `test` -- 不会影响自增
truncate table `test` -- 自增会归零
4、DQL查询数据
4.1、DQL
数据查询语言
-- 起别名(字段和表名都可以)
select `student_no` as 学号,`student_name` as 姓名 from student
-- 函数
select concat('姓名:',student_name) as 新名字 from student -- 那么字段里面的查询的值就变成了:姓名:xxx
-- 发现重复数据,去重
select distinct `student_no` from result
-- 所有的数值类型的数据+1
select `score`+1 as `提分后` from result
4.2、where条件子句
- like %(代表0到任意个字符) _(一个字符)
4.3、联表查询
CSDN主页–连表查询
4.4、分页查询
select * from student limit 0,5 --limit [起始值],[页面大小]
4.5、子查询
在where语句中嵌套子查询
select studnetNo,subjectNo,javaScore
from score
where subjectNo = (
select subjectNo from subject
where subjectName = 'Java'
)
order by javaScore desc
5、MySQL函数
5.1、常用函数
-- 数学运算
select abs(-8) -- 绝对值
select ceiling(9.4) -- 向上取整
select floor(9.4) -- 向下取整
select rand() -- 返回0-1之间的随机数
select sign(-10) -- 返回一个数的符号
select char_length() -- 返回字符串的长度
select concat('字符串1','字符串2') -- 将字符串拼接
select insert('我爱编程helloword',1,2,'超级热爱') -- 查询,替换 从字符的第1个位置往后数2个字符进行替换(某个位置开始替换某个长度)
select upper('') -- 转大写
select lower('') -- 转小写
instr -- 返回第一次出现的子串的索引
replace -- 替换出现的指定字符串
substr -- 返回指定的子字符串(4,3)从第四个字符串开始截,截三个
SELECT CURRENT_TIME(); -- 获取当前时间
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT LOCALTIME(); -- 获取本地时间
5.2、聚合函数(常用)
- Count() (‘字段’)会忽略所有的null值 (*)(1)不会忽略
- Sum()
- Avg()
- Max()
- Min()
5.3、数据库级别的MD5加密(拓展)
利用MD5函数
insert into testmd5 values(4,'xiaoming',MD5('123456'))
6、事务
6.1、什么是事务
要么都成功,要么都失败
转账实例:A有800元,B有200元
A要给B转账200(A的钱,从800变为600),B接收A的200(B的钱,从200变为400)
执行两个SQL语句
将一组SQL放到一个批次中去执行
ACID
- 原子性
要么都成功,要么都失败
- 一致性
事务前后的数据完整性要保证一致,保证总钱数1000
- 持久性 – 事务提交
事务一旦提交则不可逆,被持久化到数据库中
- 隔离性
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
隔离性主要是解决:两个事务同时进行,其中一个事务读取到另一个事务还没提交的数据(正常情况下,没有事务没有提交的数据是不能被读取的)。
事务的隔离级别:
- 脏读
一个事务读取了另一个事务未提交的数据
- 不可重复读
在一个事务内读取表中的某一行数据,多次读取结果不同:页面统计查询值B为200,生成报表的时候B有人转账(200元)进来了,
那么此时读取的结果变成了500元
- 幻读
是指在一个事务内读取到了别的事务插入到的数据,导致前后读取的数据总量不一致
6.2、事务的提交流程
-- mysql默认开启事务自动提交的
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启
---------------------------
-- 首先关闭mysql的事务自动提交
set autocommit = 0;
-- 事务开启
start transaction
sql语句1
sql语句2
-- 提交事务:持久化(成功!)
commit
-- 回滚:回到之前的样子(失败)
rollback
-- 事务结束
set autocommit = 1 -- 开启自动提交
7、索引
索引的定义:索引是帮助MySQL高效获取数据的数据结构
7.1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引 (primary key)
- 唯一标识,主键不可重复,只有一个列作为主键
- 唯一索引 (unique key)
- 避免重读的列出现,唯一索引可以重复,就是说多个列都可以被表示为唯一索引
- 常规索引 (key/index)
- 默认的,用index或者key来设置
- 全文索引 (fulltext)
- 在特定的数据库引擎下才有
-- 显示所有索引信息
-- 增加一个全文索引
explain select * from student; -- 非全文索引(会遍历数据)
explain select * from student where match(studentName) against('刘') -- 直达查询的数据
7.2、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在-常用来查询的字段上面
索引的数据结构
hash类型的索引(不是INNODB默认的,INNODB默认的是BTREE)
8、规范数据库设计
8.1、数据库的设计
8.2、三大范式
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
常用的有三种,第一第二第三范式。
第一范式:
数据表中的每一个字段都是不可分割的原子数据项
举例子:假设某个字段的值为:家庭信息→其中的value是:‘三口人,北京’。
很明显,这是一个还可以在细分的一个字段,能细分成家庭人口和居住地
第二范式:
在满足第一范式的前提下,需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
举例子:
如图所示:同一个订单可能包括不同的产品,所以订单号不能称为主键,主键必须由“订单号”和“产品号联合组成”,但是由于订单金额和订单时间只与主键的一部分有关,即只跟订单号有关和产品号无关,所以不满足第二范式。所以需要将之拆分。
第三范式:
在第二范式的基础上,确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
上表中满足第二范式,但是班主任性别和年龄的直接依赖是班主任姓名,而不是主键学号。所以需要做出调整。
这样就满足第三范式了
9、JDBC
9.1、SQL注入
什么是SQL注入?(恶意拼接查询)
在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息。
举个例子:
我们使用JDBC去连接数据库并查询(SQL语句)
那么Java总的SQL语句为:
String sql = "select * from users where `NAME`='"+username+"'and `password`='"+password+"'";
// 假如说这里的username传入的并不是单纯’admin‘而是’or 1=1‘ 这样的话直接就修改了这个sql语句的功能了
防止SQL注入:
- 过滤输入内容,校验字符串
通过编程语言将用户输入的不合法字符进行过滤和剔除
- 参数化查询
/**
*参数化查询目前被视作是预防 SQL 注入攻击最有效的方法。参数化查询是指在设计与数据库连接并访问数据时,在需要填入数值或数据的地方,使用参数(Parameter)来给值。
*/
UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4
9.2、Statement和PreparedStatement
Statement有SQL注入的风险
preparedStatement没有
-- preparedStatement使用的是占位符、预编译、固定写法
Connection conn = null;
PreparedStatement st = null;
String sql = "insert into users(id,'username','password') values (?,?,?)";
st = conn.prepareStatement(sql) //预编译sql
// 手动给参数赋值
st.setInt(1,4);
st.setString(2,'zhangsan');
st.setString(3,'123123');
st.executeUpdate();// 返回值为受影响的行数
9.3数据库连接池
java执行一个sql语句:数据库连接–执行完毕–释放
但是连接–释放是十分消耗系统资源的,所以我们提出了池化技术。
**池化技术:**准备好一些预先的资源,过来就连接预先准备好的
举例:
假设我们有10个SQL,每次执行的时候都要连接10次;
所以我们事先预留5个接口供SQL使用;
最小连接数:(设置的数值应该跟常用的连接数有关)
最大连接数:(业务最高的承载上限)
超过最大连接数的业务进行排队等待;
等待超时:(超时退出)