MySQL

基础概念

• 关系型数据库(SQL)：
  • MySQL,Oracle,Sql Server,DB2,SQLlite
  • 通过表与表之间，列与列之间关系进行数据的存储
• 非关系型数据库(NoSQL)：
  • Redis,MongDB
  • 通过对象存储，由对象自身属性决定

DBMS：数据库管理系统->用于管理数据库

MySQL：最好的RDBMS(关系型数据库)之一，Oracle旗下产品

通过SQLyog来可视化操作MySQL数据库

• DDL定义
• DML操作
• DQL查询
• DCL控制

mySQL中SQL语言不区分大小写

数据类型

• 数值

  

  int(10)表示显示宽度，与存储宽度无关，如括号里面的10表示0填充时自动填充至10位

• 字符串

  

  varchar(20)表示可变宽度

• 时间日期

  

• null

  无值，跟任何数计算结果均为null

字段属性

• unsigned:无符号整数，声明该列不能为负数
• zerofill:0填充，不足的位数用0来填充，如int(3),5->005
• 自增：自动在上一条记录基础上+1；通常用来设置唯一主键，必须为整数型，可以设置起始值和步长
• 非空：插入数据时不填写该数据会报错
• 默认：插入数据时不填写该数据会自动填写为设置的值

数据规范

每个表都必须包含以下字段，表示一个记录的存在意义：

属性	含义
id	主键
‘version’	乐观锁
is_delete	伪删除
gmt_create	创建时间
gmt_update	修改时间

数据库引擎

• INNODB:现在默认使用，安全性高，事务处理，多表操作
• MYISAM:早些年使用，节约空间，速度较快

区别：

区别项	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为前者的2倍

所有数据库文件都存在于data目录下，本质是文件的存储

• INNODB在数据库目录中只有一个*.frm文件，以及上级目录的ibdata1文件
• MYISAM对应文件
  • *.frm表结构定义文件
  • *.MYD数据文件(data)
  • *.MYI索引文件(index)

字符集编码

默认编码=Latin1，不支持中文

要显示中文应设置charset=utf8

事务

事务的四大特征ACID

A 原子性：事务是最小的单位，不可再分割，同一事物中的sql语句必须保证同时成功或同时失败；

C 一致性：最终一致性：事务前后总和相同；

I 隔离性：事务1和事务2之间是有隔离性的，同时进行多个事务处理时，一个事务不能操作另一个事务正在操作的数据；

D 持久性：事务一旦结束(commit,roll back)就不可以返回，事务未提交之前重启数据库则恢复原状，一旦提交则持久化到数据库；

隔离引发相关问题

概念	理解
脏读	一个事务读到了另一个事务没有提交的数据，实际开发下是不允许存在的
不可重复读	一个事务内读取到另一个事务插入的数据，导致前后读取同一个表的数据不一致
虚读(幻读)	两个不同的终端分别访问数据库并开启事务一个终端插入数据另一端不可见但也不可插入相同数据
串行化	一个终端开启事务操作数据时另一个终端进行操作会进入等待状态（卡死），一旦终端commit后另一终端再解除等待

• 串行化问题是性能最差，即隔离级别越高性能越差
• mysql默认隔离级别是repeatable-read

SQL中的事务

set autocommit = 0 -- 关闭自动提交
set autocommit = 1 -- 开启自动提交(默认)

事务全过程

set autocommit = 0 -- 关闭自动提交
start transaction -- 标记事务开始，从这开始的sql都在同一个事务中
-- SQL语句
insert...
insert...
commit -- 提交，事务持久化
rollbak -- 回滚，返回原来状态
set autocommit = 1 --开启自动提交,恢复默认值

savepoint 保存点名 -- 设置保存点
rollback to savepoint 保存点名 -- 保存点
release savepoint 保存点名 --撤销保存点

索引

帮助MySQL高效获取数据的数据结构，小数据时影响不明显，数据量很大时效果很明显，小数据量表不需要加索引，一般加在常来查询的字段上

• 主键索引(primary key)
  • 唯一标识，不可重复，只能有一个列作为主键
• 唯一索引(unique key)
  • 表面重复列出现，可以重复
• 常规索引(key/index)
  • 默认
• 全文索引(full text)
  • 特定数据库引擎才有，快速定位数据

匹配索引

select * from student where match(studentNo) against('刘')

SQL编程

delimiter $$ --函数标志
create function myFunction()
returns int
begin
	declare num int default 1000000;
	declare i int default 0;
	while i<num do
		insert ...
		set i=i+1;
	end while;
end;

索引数据结构：Btree,详细介绍http://blog.codinglabs.org/articles/theory-of-mysql-index.html

MySQL端口号：3306

SQL

基础概念

主要操作

命令结尾跟;

mysql -u root -p	-- 登录
update mysql.user set authentication_string=password('123456')where user='root' and Host='localhost';	-- 修改密码
select version()	-- 查询mysql版本
select 100*3-1 as 计算结果	-- 可以用于简单计算
-- 代表注释
-- 字段加``取消高亮,一般用于字段和表名等

常用命令

show databases/tables	-- 查看数据库/数据表
create database xxx	-- 创建数据库
create table xxx (值)	--创建数据表
use xxx	-- 转到数据库
desc xxx	-- 查看数据表的数据类型
select * from xxx	-- 查看表中(全部)类型的记录
show create database db1	-- 查看创建数据库
show create table tb1	-- 查看tb1数据表的定义语句

条件语句

create database [if not exists] db1		条件语句在剧中可以省略
drop database [if not exists] db1

建库示利

create database shop character set utf8 collate utf8_general_ci

建表示例

格式

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(8) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) NOT NULL DEFAULT '张三' COMMENT '姓名',
	`pwd` VARCHAR(13) NOT NULL DEFAULT '123456' COMMENT '密码',
	`birthday` DATE DEFAULT NULL COMMENT '生日',
	`address` VARCHAR(100) DEFAULT '派出所' COMMENT'住址',
	PRIMARY KEY(`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8

注意：字段用``，值用’’，避免混淆

修改与删除

change用来字段重命名，不能修改字段类型和约束；modify不能字段重命名，只能修改字段类型和约束

alter table tb rename as tb1; -- 更改表名
alter table tb tb1 add age int(11); -- 增加字段

alter table tb modify age varchar(11); -- 修改约束
alter table tb change id id1 int(11); -- 字段重命名

alter table tb drop age -- 删除字段

drop table if exists tb1 -- 删除表

主键约束

primary key()					非空唯一
primary key(,)					多项数据都非空，之和唯一
auto_increment					自增
unique							唯一
not_null						非空
default							默认（一般不用声明）
foreign_key						外键约束

创建外键约束：在副表末尾跟foreign (id) reference xxx(id)，即创建与主表class之间的链接

建表完成后添加约束：
alter table xxx add primary key(id)
alter table xxx modify id int primary key
删除约束：
alter table xxx drop primary key

外键

删除有外键关系的表时，必须先删除从表再删除主表

数据库级别外键

又称物理外键,一般不建议使用，避免数据库过多造成困扰，一般使用时在开发程序的应用层中编写逻辑实现

创建从表时增加外键约束

给student表的gradeid字段添加到grade的外键

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(8) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) NOT NULL DEFAULT '张三' COMMENT '姓名',
	`pwd` VARCHAR(13) NOT NULL DEFAULT '123456' COMMENT '密码',
	`birthday` DATE DEFAULT NULL COMMENT '生日',
	`address` VARCHAR(100) DEFAULT '派出所' COMMENT'住址',
    `gradeid` INT(8) NOT NULL COMMENT '年级id',
	PRIMARY KEY(`id`),
    key `FK_gradeid` (`gradeid`),
    constraint `fk_gradeid` foreign key (`gradeid`) references `grade` (`gradeid`)
)ENGINE INNODB DEFAULT CHARSET=utf8

建表之后添加外键约束

alter table `student`
add constraint `FK_gradeid` foreign key(`gradeid`) references `grade` (`gradeid`);

DML语言

数据操纵语言

• 添加insert

  insert into `student` (`name`) values('李四'); -- 插入特定字段的值
  insert into `student` (`name`) values('李四'),('王五') -- 插入多项数据
  insert into `student` (`name`,`pwd`) values(`李四`,`123`) -- 插入一项多字段的数据，插入时字段一一对应
  insert into `student` values(`李四`,`123`) -- 无字段插入数据，插入时字段与值一一对应

• 修改update

  update `student` set `name`='张三' where id = 1; -- 修改id为1的学员姓名
  update `student` set `name`='张三',`email`=`123@gmail.com` where id = 1; -- 多字段修改
  update `student` set `name`='张三' where id = 1 and sex = `女`; -- 多约束修改

• 删除delete

  delete from `student` where id = 1001;

  切记添加约束

  不使用where进行约束时会将修改应用于所有数据，请务必小心

  truncate删除所有数据

  truncate `student`;

  相同点：两者都能删除数据，不删除表结构

  不同点：truncate重新设置自增列，计数器归零，且不会影响事；delete删除后若为InnoDB引擎重启数据库后计数器归零(计数存在于内存中)，若为MyISAM引擎重启后会继承上次计数(计数存在于文件中)

insert into xxx values()		插入值
delete from xxx					删除数据记录
update xxx set name=''			将表中name修改为''

选择约束

where							添加约束(后加条件语句)
group by xxx					分组查询
having							分组后过滤
between 1 and 5
like '3%'						模糊查询（以3开头）

where后的特殊表达式

<> -- 不等判定，与!=通用
between 1 and 5 --区间判定，两个数字之间
and	-- 并
or -- 或

DQL语言

数据查询语言，数据库核心语言

select完整语法

select语句可以用于其他操作

select version()	-- 查询mysql版本
select 100*3-1 as 计算结果	-- 可以用于简单计算
select @@auto_increment_increment	-- 查询自增步长
explain select ... -- 分析查询情况

简单查询

select * from tb1	-- 查询全部学生
select `字段名` from tb1	-- 查询指定字段
select `字段1` as 指定名1,`字段2` as 指定名2 from tb1 as 指定表1	-- 给字段或表起别名
select concat(`姓名`,StudentName) as 新名字 from student	-- concat函数进行拼接数据
select distinct `StudentNo` from result -- 去重关键字distinct,用来去除多余的重复数据
select `StudentNo`,`StudentResult`+1 as '提分后' from result	-- 学员成绩+1查看
select `StudentNo`,`StudentResult`+1 as '提分后' from result groupby `StudentNo` -- 分组查询

concat函数拼接图例

where条件查询

基本句式

select studentNo, `studentResult` from result
where studentresult between 95 and 100;

select studentNo, `studentResult` from result
where not studentNo = 1000;

where后的逻辑表达式

<> -- 不等判定，与!=通用
between 1 and 5 --区间判定，两个数字之间
a and b	-- 并
a or b	-- 或
not a	-- 非

模糊查询

本质：比较运算符

运算符	语法	描述
IS NULL	a is null	为null则为真
IS NOT NULL	a is not null	不为null则为真
BETWEEN	a between b and c	a在b和c之间为真
like	a like b	SQL匹配，a匹配b则为真
IN	a in (a1,a2,a3…)	假设a是其中一个值则为真

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 '刘_'; -- 以_来模糊查询姓刘且名字只有两个字的学生

select `studentNo`,`studentName` from `student`
where `studentName` like '刘__'; -- 以_来模糊查询姓刘且名字只有三个字的学生

in

一个或多个值

select `studentNo`,`studentName` from `student`
where `studentNo` in (1001,1002,1003); -- 模糊查询标号其中的学生

null/not null

select `studentNo`,`studentName` from `student`
where `address`='' or address is null; -- 模糊查询地址为空的学生

select `studentNo`,`studentName` from `student`
where `BirthDate` is not null; -- 模糊查询地址为空的学生

联表查询

外连接

步骤：

• 需求分析，分析查询的字段来自哪些表
• 确定使用哪种查询方式(7种)
• 确定交叉点和判断条件

-- inner join
select s.`studentNo`,`studentName`,`SubjectNo`,`StudentResult` from `student` s inner join `result` r where s.`studentNo` = r.`studentNo`;	-- 内联查询，将两个表中的共有字段`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`;	-- 左查询，将两个表中的共有字段

-- 连接词on为连接查询，where为等值查询

操作	描述
Inner join	若表中至少有一个匹配则返回该行
left join	返回左表中所有的值，即使右表没有匹配
right join	返回右表中所有的值，即使左表没有匹配

自连接

将一张表理解为两个一摸一样的表

select a.`categoryName` as '父栏目',b.`categoryName` as '子栏目' from `category` as a,`category` as b where a.`category`=b.`pid`

分页和排序

-- order by 字段名 asc/desc
select `StudentNo`,`StudentResult`+1 as '提分后' from result order by `studentResult` asc -- 升序排序

-- limit 起始数据序号 页面数据条数
select `StudentNo`,`StudentResult`+1 as '提分后' from result limit 0,5 -- limit来分页
-- 标准公式 limit (n-1)pagesize, pagesize 其中n为当前页，pagesize为页面大小

子查询

where之后再嵌套另外一个子查询语句，可以理解为将一个查询分解为多个查询，在一些情况中与联表查询同功能

公式where (select ...)

select `StudentNo`,`SubjectNo`,`StudentResult` from `result` where (select `SubjectNo` from `subject` where `SubejectName` = '数据库结构-1') order by `StudentResult` Desc
-- 利用子查询降序查看课程名为数据库结构-1的学生成绩

MySQL常用函数

常用函数

-- 数学运算
select abs(-8); -- 绝对值
select ceiling(9.4); -- 向上取整
select floor(9.4) -- 向下取整
select rand(); -- 返回0-1之间的随机数
select sign(-1); -- 判断数的符号

-- 字符串函数
select char_length('abcde') -- 返回字符串长度
select concat('a','b','c') -- 拼接字符串
SELECT INSERT('需要插入的字符串',1,4,'新字符串'); -- 查询，从某个位置替换某个长度
-- 替换结果：'新字符串的字符串'
select lower('Ab') -- 全转小写
select upper('aB') -- 全转大写
select instr('abcde','h') -- 查询第一次出现子串的索引
select replace('坚持就能成功','坚持','努力') -- 指定替换，一般用insert
select substr('abcdefg',3,4) -- 截取指定字符串，结果cdef
select reverse('abcdefg') -- 反转字符串

-- 时间和日期
select current_date() -- 获取当前日期 
select curdate() -- 获取当前日期
select now() -- 获取当前时间
select localtime() -- 获取系统时间
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() -- 获取版本

聚合函数

select count() -- 计数
select sum() -- 求和
select avg() -- 平均值
select max() -- 最大值
select min() -- 最小值

-- count
select count(`BirthDate`) from `student` -- 忽略所有null
select count(*) from `student` -- 不忽略所有null
select count(1) from `student` -- 忽略所有null

-- having可以对函数的结果进行过滤
select avg(StudentResult) as 平均分 
from result r 
inner join `subject` sub 
on r.`SubjectNo` = sub.`SubjectNo` 
group by r.`SubjectNo` -- 分组
having 平均分>80

MD5加密

信息加密算法，增强算法复杂度，不可逆，具体值的MD5值是一样的，破解网站的原理是字典

-- 明文密码
insert into testmd5 values(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- md5()加密函数
update testmd5 set pwd = MD5(pwd) where id = 1 -- 加密密码1
insert into testmd5 values(1,'张三',md5('123456')) -- 插入时加密
-- 校验：将用户传进的数据进行md5加密，然后对比加密后的值
select * from testmd5 where `name`='张三' and pwd=md5('123456')

MySQL高级

用户管理

SQLyog可以进行对用户的管理，权限操作等

用户表：mysql.user

create user 用户名 identified by '123456' -- 以123456为密码
drop user 用户名 -- 删除用户
set password = password('123456') --设置密码
set password for 用户名 = password('123456') -- 指定用户设置密码
rename user name1 to name2 -- 用户重命名
grant all privileges on *.* to 用户名 -- 设置最高权限(没有grant权限)
show grant for 用户名 -- 查看指定用户权限
show grant for root@localhost -- 查看主机权限
revoke all privilege on *.* for 用户名 -- 撤销全权限

数据库备份

• 保证重要的数据不丢失
• 数据转移

方式：

• 手动copy

• Sqlyog可视化工具导出sql文件

• 命令行导入导出

  • 导出(若在登陆情况下可以不加用户名等选项)

    #格式：mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
    mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
    
    #多表导出： mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名
    
    #导出数据库: mysqldump -h主机 -u用户名 -p密码 库1 库2 >物理磁盘位置/文件名

  • 导入(同上，默认已登录)

    #格式：source 物理磁盘地址/文件名
    source  d:/a.sql

  通过sql文件的传递进行库的备份

规范数据库设计

• 糟糕数据库设计
  • 数据冗余，浪费空间
  • 数据库插入和删除麻烦，异常(屏蔽物理外键)
  • 程序性能差
• 良好的数据库设计
  • 节省内存空间
  • 保证数据库完整性
  • 方便开发系统

设计步骤

• 手机信息，分析需求(以博客网站分析实例)
  • 用户表(用户登录注销，个人信息，写博客，创建分类)
  • 分类表(文章分类，创建者)
  • 文章表(文章信息)
  • 友链表(友链信息)
  • 自定义表(系统信息)
• 标识实体(需求落实到字段)
• 表示实体之间的关系
  • 写博客：user->blog
  • 创建分类：user->category
  • 关注：user->user
  • 友链：links
  • 评论：user-user-blog

三大范式

设计表的三大范式，规范数据库

为什么需要数据规范化

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效信息

第一范式(1NF)

内容：数据库表的每一列都是不可分割的原子数据项

第二范式(2NF)

前提：满足第一范式

内容：

第三范式(3NF)

前提：满足第一范式和第二范式

内容：

规范性和性能问题

关联查询的表不得超过三张(阿里规范)

• 考虑商业化的需求和目标，(成本和用户体验)数据库的性能更加重要
• 在规范性能问题时，适当考虑下规范性
• 故意给一些表增加冗余字段，将多表查询变为单表查询
• 故意增加一些计算列(大数据量降低为小数据量：索引)

JDBC

程序通过数据库驱动和数据库交互

SUN公司为简化开发人员对数据库的统一操作，提供了一个java操作数据库的规范，俗称JDBC

规范的具体实现由厂商完成

两个包java.sql和javax.sql，此外再导入一个数据库驱动包mysql-connector-java-5.1.47.jar

导入jar包：

第一个JDBC程序

步骤

• 加载驱动
• 连接数据库DriverManager
• 获得执行sql的对象Statement
• 获得返回的结果集
• 释放连接

创建数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

JDBC程序

import java.sql.*;

public class JDBCTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        //原本写法：DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息和url后面三个参数分别为：支持中文编码，设定字符集，设置安全连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123456";
        //3.连接成功，数据库对象
        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();
    }
}

Connection对象

数据库对象，可以执行一些数据库级别的指令

Statement对象

执行SQL的对象，常用函数：

statement.execute();//执行任何SQL
statement.executeQuery();//查询操作返回ResultSet
statement.executeUpdate();//更新，插入，删除都能用，返回一个受影响的参数

ResultSet对象

resultSet.getObject();//在不清楚列类型的情况下使用
//若知道列类型则使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();

resultSet.next();//表示下一个数据对象是否存在
resultSet.absolute();//移动到指定行

编写工具类

db.propeities配置文件

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username = root
password = 123456

JdbcUtils.java工具类文件

package Lesson.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    //静态代码块
    static {
        //获取配置文件
        try{
            //通过反射来获得配置文件的输入流
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("Lesson/utils/db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //驱动只需加载一次
            Class.forName(driver);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("静态代码块加载完成");
    }

    //获取mysql连接函数
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    //释放连接函数
    public static void release(Connection conn, Statement st,ResultSet rs) throws SQLException {
        if(rs!=null){
            rs.close();
        }
        if(st!=null){
            st.close();
        }
        if(conn!=null){
            conn.close();
        }
    }
    
    //重载释放函数
    public static void release(Connection conn, Statement st) throws SQLException {
        if(st!=null){
            st.close();
        }
        if(conn!=null){
            conn.close();
        }
    }
}

UtilsTest.java测试函数

package Lesson;

import Lesson.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UtilsTest {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;//查询时更新该参数

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            //编写SQL语句
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "values(4,'shumu','123456','123456@163.com','2021-01-01')";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            //释放连接
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入

实例

//login函数中的查询语句
public static void login(String username, String password){
	...
    //拼接字符串来获得完整的sql语句
    String sql = "select * from users where 'name' = '"+ username + "' and `password` = '" + password + "'";
    rs = st.executeQuery(sql);
    while (rs.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("---------------------");
        }
	...
}

此时用户只需输入username='or '1=1，password='or '1=1即拼接出非正常的sql语句

String sql = "select * from users where 'name' = ''or '1=1' and `password` = ''or '1=1'";

即可破获给数据库的所有数据，形成信息安全漏洞，容易被攻击

PreparedStatement

代替Statement，可以防止SQL注入

本质：检测传入的字符，若出现转义字符则直接转义(如)

PpstTest.java测试函数

import Lesson.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class PpstTest {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        PreparedStatement ppst = null;

        conn = JdbcUtils.getConnection();
        //使用?占位符代替参数
        String sql = "insert into users(id,`name`,`password`,`email`,`birthday`)values(?,?,?,?,?)";
        ppst = conn.prepareStatement(sql);//预编译SQL，先编写SQL但不执行

        //第一个参数表示第几个字段，第二个参数表示用什么代替占位符
        ppst.setInt(1,6);
        ppst.setString(2,"user6");
        ppst.setString(3,"123456");
        ppst.setString(4,"123456@163.com");
        //sql.Date      数据库     java.sql.Date()传入时间戳
        //utils.Date    java       Date().getTime()函数获取时间戳    
        ppst.setDate(5, new  java.sql.Date(new Date().getTime()));

        //直接执行预编译的sql，无参数
        int i = ppst.executeUpdate();

        if (i>0)
            System.out.println("更新成功！");
        else
            System.out.println("更改失败，请重试！");
        JdbcUtils.release(conn,ppst);
    }
}

IDEA导入数据库

必须先导入mysql-connector-java-5.1.47.jar包测试连接，连接数据库，可以在console界面编写执行sql代码，修改数据项后须点击提交以保存至数据库

JDBC中的事务

ACID原则

失败时会自动回滚

package Lesson;

import Lesson.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TransactionTest {
    public static void main(String[] args) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        PreparedStatement ppst = null;

        try{
            //关闭自动提交(开启事务)
            conn.setAutoCommit(false);
            //编写多个sql语句
            String sql1 = "update account set money = money-100 where name = 'A'";
            ppst = conn.prepareStatement(sql1);
            ppst.executeUpdate();

            String sql2 = "update account set money = money+100 where name = 'B'";
            ppst = conn.prepareStatement(sql2);
            ppst.executeUpdate();

            //sql编写完成，提交修改
            conn.commit();

            System.out.println("更新成功！");
        }catch (Exception e){
            //执行失败不需要执行catch中的内容，一旦报错会自动rollback
            e.printStackTrace();
            System.out.println("更新失败，请重试");
        }finally {
            //释放连接
            JdbcUtils.release(conn,ppst);
        }

    }
}

数据库连接池

数据库连接–执行完毕–释放，连接到释放的过程十分浪费资源

池化技术：准备预先的资源，一旦连接就连接预先准备好的资源

根据常用连接数来决定最小连接数

如：

• 最小连接数：10
• 最大连接数：20
• 等待超时：100ms

超过20个连接则会进行等待，超过100ms则会自行停止连接

一般来说编写连接池，实现接口DataSourse接口

开源数据源实现(拿来即用)

使用数据库连接池后可以不用编写连接数据库的代码

• DBCP

  需要导入的jar包：commons-dbcp2-2.8.0.jar,commons-pool2-2.9.0.jar,commons-logging-1.2.jar

• C3P0

  需要导入的jar包：c3p0-0.9.5.5,mchange-commons-java-0.2.19

• Druid(阿里)：

DBCP实例

dbcpconfig.properties配置文件

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

JdbcUtils_DBCP工具类

package Lesson.utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils_DBCP {

    private static BasicDataSource dataSource = null;
    //静态代码块
    static {
        //获取配置文件
        try{
            //通过反射来获得指定文件的输入流
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("Lesson/utils/dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源 工厂模式创建对象
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        }
        catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("静态代码块加载完成");
    }

    //获取mysql连接函数
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    //释放连接函数
    public static void release(Connection conn, Statement st,ResultSet rs) throws SQLException {
        if(rs!=null){
            rs.close();
        }
        if(st!=null){
            st.close();
        }
        if(conn!=null){
            conn.close();
        }
    }

    public static void release(Connection conn, Statement st) throws SQLException {
        if(st!=null){
            st.close();
        }
        if(conn!=null){
            conn.close();
        }
    }
}

DbcpTest.java测试类

package Lesson;

import Lesson.utils.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbcpTest {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        Statement st = null;

        try {
            conn = JdbcUtils_DBCP.getConnection();
            st = conn.createStatement();
            String sql = "insert into users(id,`name`,`password`,`email`,`birthday`)" +
                    "values(7,'user7','123456','123456@163.com','2021-01-01')";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            //释放连接
            JdbcUtils_DBCP.release(conn,st);
        }
    }
}

无论使用什么数据源，本质是一样的，DataSource接口不变，方法就不会变

写在最后：面试高频考点：

• MySql引擎
  • InnoDB
  • MyIsam
• InnoDB底层原理
• 索引
• 索引优化

安装问题

linux系统中安装mysql是没有data目录的，默认的data目录是/var/lib/mysql

在centos中安装mysql启动报错，在mysql配置文件中找对应的错误日志位置cat /etc/my.cnf

一般在/var/log/mysqld.log

• 启动mysql服务systemctl start mysqld，发现没执行数据表初始化mysqld --initialize --console --user-mysql，如果发现Failed to find valid data directory.报错，需要修改对应的文件夹权限:chmod -R /var/lib/mysql mysql:mysql

• 同时这一步会生成临时密码，需要查找日志找出来cat /var/log/mysqld.log|grep localhost

  

• 这时可能会发现另一个报错：The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.，笔者尝试按照提示取删除mysql文件夹中的内容，但是还是无法正常运行，只能更改datadir目录，将/etc/my.cnf中改为datadir=/var/lib/mysql/data，这时才正常初始化成功。当然手动创建的/var/lib/mysql/data权限也需要修改为mysql:mysql

• 当mysqld –initialize初始化完成后，就可以正常登录mysql了：mysql -uroot -p刚才获取到的临时密码，然后再mysql客户端中修改密码alter user root@localhost identified by '新密码';

命令行导入sql文件

source /root/code_demo/lzblog/lzBlog.sql