MySQL-初见

目录

初识MySQL

MySQL是一个 关系型数据库 管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。

MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

关系型数据库(SQL)

  1. MySQL,Oracle,DB2
  2. 通过表与表之间,行与行之间的关系进行存储

非关系型数据库(NoSQL)

  1. Redis,MongDB
  2. 非关系型数据库,对象存储

数据库基本命令

--连接数据库
mysql -u root -p [密码] 

--查询所有数据库
show databases;

--切换数据库
user [数据库名];

--查看所有表
show tables;

--显示表信息
describe [表名];

--创建数据库
create database [数据库名];

--查看创建数据库的语句
show create database school;

--查看创建表的定义语句
show create table student;

--查看表结构
desc student;

--查看SQL执行的状况EXPLAIN
EXPLAIN select * from user

--操作数据库
--操作数据库> 操作数据库中的表>操作表中的数据
MySQL中的SQL语句不区分大小写

--创建数据库
create database [if not exists] westos;

--删除数据库
drop database [if exists] student;

--使用数据库
--如果表名或库名是一个特殊字符,就需要带 ``符
user `westos`;

创建数据库表

整数:
tinyint 十分小的数据 1个字节
smallint 较小数据 2个字节
mediumint 中等大小的数据 2个字节
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 保持大文本

时间格式
date YYYY-MM-DD 日期格式
time HH:mm:ss 时间格式
datetime YYYY-MM-DDHH:mm:ss 最常用的时间格式
timestamp 时间戳 1970.1.1 到现在的毫秒数
year 年份表示

null
没有值 未知
不要使用null进行运算

创建数据库表完整语句

CREATE 	TABLE IF NOT EXISTS `t_te_user`(
      `id` INT(4) NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(30) NOT NULL ,   
      `pwd` VARCHAR(20) NOT NULL ,   
      `sex` VARCHAR(30) NOT NULL ,     
     `address` VARCHAR(100) DEFAULT NULL,
      PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

--格式
CREATE 	TABLE [IF NOT EXISTS] `表名`(
        `字段名` 列类型 [属性] [索引] [注释],
        `字段名` 列类型 [属性] [索引] [注释],
        `字段名` 列类型 [属性] [索引] [注释],
        `字段名` 列类型 [属性] [索引] [注释],
          
)[表类型][字符集][注释]

--设置数据库表的字符集编码

CHARSET=utf8

不设置的话,会是mysql默认的字符集编码,不支持中文!

注意点

  • 字段名尽量使用``符号包裹
  • 注释使用--符号
  • SQL大小写不敏感,但是建议写小写
  • 所有的标点符号,使用英文

数据库引擎

数据库引擎

InnoDB MYISAM
事务支持 支持 不支持
数据行锁定 支持 不支持
外键约束 支持 不支持
全文索引 不支持 支持
表空间的大小 较大,约为2倍 较小

​

MYISAM:节约空间,速度最快

InnoDB:安全性高,事务的处理,多表多用户操作

在物理空间的位置

所有的数据库文件都存放在data目录下,一个文件夹就代表一个数据库

Windows默认安装目录在C:\Program Files\

本质还是文件存储

MySQL引擎在屋里文件上的区别:

  1. InnoDB 在数据库表中只有一个*.frm文件,以及上级目录下的idbdata1文件
  2. MYISAM 对应的文件: *.frm-表结构的定义文件, .MYD-数据文件(data),.MYI-索引文件(index)

修改和删除表字段

--修改表名

ALTER TABLE [旧表名] RENAME AS [新表名]

--新增字段

ALTER TABLE [表名] ADD [字段名][类型]

--修改字段约束(如:int变成varchar)

ALTER TABLE [表名] MODIFY [字段名][字段约束]

--字段重命名

ALTER TABLE [表名] CHANGE [旧字段名][新字段名]



--最终结论: change用来字段重命名，不能修改字段类型和约束;

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



--删除

--删除字段

ALTER TABLE [表名] DROP [字段名]


--删除表

DROP TABLE IF EXISTS [表名]


--所有的删除和修改尽量都加上判断(`if exists`),避免报错

MySQL数据管理

外键

概念

如果 公共 关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。

由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

建表时指定外键约束

-- 创建外键的方式一 : 创建子表同时创建外键

-- 年级表 (id\年级名称)
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
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`);


--注意 : 删除具有主外键关系的表时 , 要先删子表 , 后删主表
-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

以上内容都是 物理外键

数据库级别的外键,不建议使用,避免数据库过多造成困扰

以后使用外键都是在应用层实现(代码实现)

DML语言

--INSERT新增详解

--插入命令格式:

insert into [表名]([字段名],[字段名]) values([值],[值])

--注意事项:

--一般写插入语句,字段和数据一定要一一对应

--字段或值之间用英文逗号隔开

--’ 字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 .

--可同时插入多条数据 , values 后用英文逗号隔开



--UPDATE修改详解

--修改命令格式:

 UPDATE [表名] SET [字段名]=[修改值] WHERE `id`='4';

--注意:
--where语句之后为筛选条件 , 如不指定则修改该表的所有列数据



--DELETE删除详解

--删除命令格式:

DELETE FROM 表名 [WHERE 条件匹配];

--注意：

--where后为筛选条件 , 如不指定则删除该表的所有列数据



--TRUNCAT删除详解

--删除命令格式:

TRUNCATE TABLE [表名];

--作用：用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;

--注意：区别于DELETE命令

--相同 :

--都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
--不同 :

--使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器,自增会归零

--使用TRUNCATE TABLE不会对事务有影响

了解即可:「DELETE删除的问题， 重启数据库， 现象
●InnoDB 自增列会重1开始(存在内存当中的, 断电即失)
●MyISAM 继续从上一一个自增量开始(存在文件中的，不会丢失)

DQL查询数据

DQL( Data Query Language 数据查询语言 )

  1. 查询数据库数据 , 如SELECT语句
  2. 简单的单表查询或多表的复杂查询和嵌套查询
  3. 是数据库语言中最核心,最重要的语句
  4. 使用频率最高的语句

语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY  ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

单表查询

--查询表的全部信息

select * from [表名]

--查询指定字段信息

select [字段],[字段] from [表名]

--查询指定字段并取别名

select [字段] as [别名],[字段] as [别名] from [表名]

--字符串拼接函数 Concat(a,b)

select concat('姓名:',[字段]) from [表名]

--去除重复数据 关键字:DISTINCT

select DISTINCT [字段名]from [表名]

--其他查询

select version() --查询系统版本
select 100*3-1 as --计算结果
select @@auto_increment_increment --查询自增步长

--学员考试成绩集体提分一分查看
SELECT studentno,StudentResult+1 AS '提分后' FROM result;

--数据库中的表达式 : 一般由文本值 , 列值 , NULL , 函数和操作符等组成

WHERE条件子句

可以简单的理解为 : 有条件地从表中筛选数据

搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假

--查询id等于1的用户

select * from user where id=1;

--查询id等于1并且姓名是金角大王的用户

select * from user where id=1 and name='金角大王';

--查询id=1 或者 id=2的用户

select * from user where id=1 or id=2;;

模糊查询:比较操作符

注意：

  1. 数值数据类型的记录之间才能进行算术运算 ;
  2. 相同数据类型的数据之间才能进行比较 ;
IS NULL
--查询email为空的用户

select * from user where email is null;

IS NOT NULL
--查询email不为空的用户

select * from user where email is not null;

BETWEEN
--区间模糊查询between and 查询id大于4小于50的用户

select * from user where id between 4 and 50

LIKE
--一般搭配通配符%使用

--查询姓名是金字开头的用户

select * from user where name like '金';

--查询名字中间有佳字的用户

select * from user where name like '%佳%';

IN
--匹配多个结果 具体的一个或多个值

--查询id=1,2,3的用户

select * from user where id in (1,2,3);

联表查询

JOIN

连接查询

如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询

内连接 inner join
查询两个表中的结果集中的交集

格式:

select a.*,b.* from user a 
inner join student b on a.id=b.id;

左外连接 left join
以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充

格式:

select a.*,b.* from user a 
left join student b on a.id=b.id;

右外连接 right join
以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充

格式:

select a.*,b.* from user a 
right join student b on a.id=b.id;

等值连接

SELECT s.*,r.*
FROM `user` s , `student` r
WHERE r.studentno = s.studentno

自连接

数据表与自身进行连接

将一张表拆成两张表

-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

--查询方式一
select a.categoryName as '父',b.categoryName as'子' 
from category as a,category as b
where a.categoryid=b.pid;
--查询方式二
select a.categoryName as '父',b.categoryName as'子' 
from category as a
inner join category as b on a.categoryid=b.pid;

  1. 等值连接中不要求属性值完全相同，而自然连接要求两个关系中进行比较的必须是相同的属性组（属性名可以不同），即要求必须有相同的值域。

  2. 等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接（如图所示）

排序查询ORDER BY

排序查询和分页查询的关键字在整个SQL语句的最后面,其语法位置是固定不变的,不能放在where或者having的前面

升序:ASC

语法:

select * from [表名]
order BY id asc

降序:BESC

语法:

select * from [表名]
order BY id desc

分组查询GROUP by

语法:

select * from user
GROUP by [分组的字段]

分组查询之后使用HAVING来过滤分组之后的数据

select * from user
GROUP  by sex
HAVING age>20

分页查询Limit

语法:

select * from [表名]
limit [起始值],[每页大小]

查找规律可以推导出计算每页的公式:
第一页 : limit 0,5 公式:(1-1)*5
第二页 : limit 5,5 公式:(2-1)*5
第三页 : limit 10,5 公式:(3-1)*5
…
第N页 : (N-1)*页面大小
limit (pageNo-1)*pageSzie,pageSzie
[pageNo:页码,pageSize:单页面显示条数]

嵌套查询(子查询)

  1. 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
  2. 嵌套查询可由多个子查询组成,求解的方式是由里及外;
  3. 子查询返回的结果一般都是集合,故而建议使用IN关键字;

语法:

select * from [表名] 
where id in ( select id from [表名] )

MySQL常用函数

常用函数

数学函数

select abs(-8) --绝对值
select CEILING(9.4); --向上取整
select RAND(); --返回随机数
select SIGN(0); --符号函数:负数返回-1 正数返回1 0返回0

字符串函数

select CHAR_LENGTH('你就是根基吧'); --字符串长度
select CONCAT('郑','在','稿');--合并字符串
select INSERT('你就是根基吧',4,2,'郑在稿'); --替换字符串
select LOWER("SKSSSS");--小写
select UPPER('ssssss'); --大写
select LEFT('hello,word',5); --从左边截取
select RIGHT('hello,word',5); --从右边截取
select REPLACE('狂神说坚持就能成功','坚持','成功');--替换字符串
select SUBSTR('狂神说坚持就能成功',4,6);--截取字符串
select REVERSE('狂神说坚持就能成功'); --反转

时间日期函数

--日期和时间函数
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());

聚合函数

函数名称 描述 :
~
COUNT() 返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】
~
SUM() 返回数字字段或表达式列作统计，返回一列的总和。
~
AVG() 通常为数值字段或表达列作统计，返回一列的平均值
~
MAX() 可以为数值字段，字符字段或表达式列作统计，返回最大的值。
~
MIN() 可以为数值字段，字符字段或表达式列作统计，返回最小的值。

COUNT()

格式:

select count([字段]) from [表名] --会忽略所有null
select count(*) from [表名] --不会忽略null
select count(1) from [表名] --不会忽略null

一般情况下count(1)比count(*)速度快

其他聚合函数

格式:

select sum(id) from user --求和
select AVG(id) from user --平均分
select MIN(id) from user --最小分
select MAX(id) from user --最高分

数据库事务

  1. 事务就是将一组SQL语句放在同一批次内去执行
  2. 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
  3. MySQL事务处理只支持InnoDB和BDB数据表类型

ACID

原子性(Atomic)

要么全部成功,要么全部失败



一致性(Consist)

事务执行前后数据的完整性要保持一致



隔离性(Isolated)

事务在并发访问的时候,为每个用户开启单独事务,事务与事务之间要保持隔离



持久性(Durable)

事务一旦提交便不可逆

隔离导致的问题

脏读

一个事务读取了另一个没有提交的事务

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数

幻读

在同一个事务内,读取到了别人插入的数据,导致前后读出来结果不一致

不可重复读

在同一个事务内,重复读取表中的数据,表数据发生了改变

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

基本语法

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

索引

MySQL官方对索引的定义为: 索引(index)是帮助MySQL高效获取数据的数据结构

显示表的索引信息

show index from [表名]
1

创建索引

create index [索引名] on [表]([字段])

索引分类

主键索引 (Primary Key)

主键 : 某一个属性组能 唯一 标识一条记录

特点 :

  1. 最常见的索引类型
  2. 确保数据记录的唯一性
  3. 确定特定数据记录在数据库中的位置
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

唯一索引 (Unique Key)

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别:

  • 主键索引只能有一个
  • 唯一索引可能有多个
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
--这条语句创建索引的值必须是唯一的。

常规索引 (Index)

作用 : 快速定位特定数据

注意 :

  • index 和 key 关键字都可以设置常规索引
  • 应加在查询找条件的字段
  • 不宜添加太多常规索引,影响数据的插入,删除和修改操作
CREATE TABLE `result`(
   -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)

ALTER TABLE tbl_name ADD INDEX index_name (col_list);
--添加普通索引，索引值可出现多次。

全文索引 (FullText)

作用 : 快速定位特定数据

1
2
全文索引 (FullText)
作用 : 快速定位特定数据

注意 :

  • 只能用于MyISAM类型的数据表
  • 只能用于CHAR , VARCHAR , TEXT数据列类型
  • 数据量小的情况下全文索引不生效
  • 适合大型数据集
-- 创建后添加全文索引
ALTER TABLE [表名] ADD FULLTEXT INDEX [索引名]([列名]);


--全文索引的使用:

select * from student where MATCH([全文索引列名]) AGAINST ([查找的内容])

强制索引

如果查询优化器忽略索引，您可以使用FORCE INDEX提示来指示它使用索引。

1
强制索引
如果查询优化器忽略索引，您可以使用FORCE INDEX提示来指示它使用索引。

以下说明了FORCE INDEX提示语法：

SELECT * 
FROM table_name 
FORCE INDEX (index_list)
WHERE condition;

索引原则

  1. 索引不是越多越好
  2. 不要对经常变动的数据加索引
  3. 小数据量的表建议不要加索引
  4. 索引一般应加在查找条件的字段

索引数据结构

hash类型的索引：查询单条快，范围查询慢

btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

索引删除方法

DROP INDEX index_name ON tbl_name;
// 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;

权限管理

创建用户命令格式:

CREATE USER [用户名] IDENTIFIED BY '[密码]'

修改当前用户:

set password=password('[密码]')

修改指定用户密码:

set password for [用户名]=password('[密码]')

重命名

rename user [旧用户名] to [新用户名]

赋予用户全部权限

--用户授权ALL PRIVILEGES全部的权限,库.表 on to 用户名
-- 除了给别的用户授权的权限没有 其他都能做
GRANT ALL PRIVILEGES ON *.* TO [用户名]

查询用户权限

show GRANTS for [用户] -- 普通用户
show GRANTS for root@localhost -- 查看root用户权限

撤销用户权限

REVOKE ALL PRIVILEGES ON *.* from [用户名]

删除用户

DROP [用户名]

数据库备份

  1. 保证重要数据不丢失
  2. 数据转移

MySQL数据库备份方法:

mysqldump命令行导出

mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)

-- 导出
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)

数据导入:

  1. 命令行导入
2. 在登录mysql的情况下：-- source D:/a.sql
　　source 备份文件
3. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

数据库设计三大范式

第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范化和性能的关系

关联查询的表不得超过三张表

  • 为满足某种商业目标 , 数据库性能比规范化数据库更重要(成本,用户体验)
  • 在数据规范化的同时 , 要综合考虑数据库的性能
  • 通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间(增加冗余

JDBC

创建项目导入依赖，写代码

/**
 * 测试JDBC程序
 */
@SpringBootTest
public class JdbcFirstDemoTest {

    @Test
    public void TestJDBC() throws Exception {
        
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法
        //用户信息和url
        String password="123456";
        String username="root";
        String url="jdbc:mysql://localhost:3306/jtdb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
        //连接成功和数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);
        //执行SQL对象
        Statement statement = connection.createStatement();

        //使用对象去运行SQL 存在结果 返回对象
        ResultSet resultSet = statement.executeQuery("select * from user");

        //遍历结果
        while (resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
            System.out.println(resultSet.getObject("age"));
            System.out.println(resultSet.getObject("sex"));
        }
        //释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

connection对象详解

connection 代表数据库,数据库相关操作都在这里面.

如:设置事务自动提交,事务开启,回滚等等

connection.setAutoCommit(false);//关闭自动提交
connection.commit();//提交
connection.rollback();//回滚

statement对象详解

statement.execute("");//执行所有类型的SQL
statement.executeQuery("");//执行查询SQL
statement.executeUpdate("");//新增 修改  删除

resultSet结果集详解

获取指定类型的数据

resultSet.getObject();//不知道字段类型时使用
resultSet.getDate();//字段是时间类型
resultSet.getString();//字段是varchar
resultSet.getInt();
resultSet.getBoolean();

遍历,指针

resultSet.next();//指针下移一位
resultSet.beforeFirst();//指针移到最前面
resultSet.afterLast();//指针移到最后面
resultSet.previous();//指针上移一位
resultSet.absolute(1);//指针移动到指定位置

编写JDBC工具类

/**
 * JDBC工具类
 */
public class JdbcUtil {

    private static String url;
    private static String username;
    private static  String password;

    static {
        //反射读取配置文件 src目录下
        InputStream inp = JdbcUtil.class.getClassLoader().getResourceAsStream("JDBC.properties");
        try {
            Properties properties = new Properties();
            properties.load(inp);//从输入流中读取配置信息
           url = properties.getProperty("url");
           username = properties.getProperty("username");
           password = properties.getProperty("password");
            Class.forName(properties.getProperty("Driver"));//加载数据库驱动
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

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

    //释放连接
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息;

//SQL预编译 insert into user(id,name,age,sex) values (?,?,?,?)
PreparedStatement preparedStatement = connection.prepareStatement("insert into user(id,name,age,sex) values (?,?,?,?)");

 //手动给参数赋值
preparedStatement.setInt(1,100);
preparedStatement.setString(2,"金角银角");
preparedStatement.setInt(3,12);
preparedStatement.setString(4,"男");

//执行
int i = preparedStatement.executeUpdate();
System.out.println("执行结果:"+i);

JDBC操作事务

  @Test
    public void Test2()  {

        //加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");//固定写法
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //用户信息和url
        String password="123456";
        String username="root";
        String url="jdbc:mysql://localhost:3306/jtdb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
        //连接成功和数据库对象
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        try {
            connection = DriverManager.getConnection(url, username, password);
            //关闭数据库自动提交 自动会开启事务
            connection.setAutoCommit(false);
            //SQL预编译 insert into user(id,name,age,sex) values (?,?,?,?)
            preparedStatement = connection.prepareStatement("insert into user(id,name,age,sex) values (?,?,?,?)");

            //手动给参数赋值
            preparedStatement.setInt(1,100);
            preparedStatement.setString(2,"金角银角");
            preparedStatement.setInt(3,12);
            preparedStatement.setString(4,"男");

            //执行
            int i = preparedStatement.executeUpdate();
            System.out.println("执行结果:"+i);
            connection.commit();//提交事务
        } catch (SQLException throwables) {
            try {
                connection.rollback();//回滚
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

数据库连接池

数据库连接>执行>释放 十分浪费资源

池化技术:准备一些预选准备的资源,需要时就直接连接准备好的

编写连接池,只需要实现一个接口DataSource,各大厂商需要让自己的连接池实现这个接口

目前市场上常见的连接池有DBCP连接池, C3P0连接池, Druid连接池

无论使用什么数据源,本质还是一样的,DataSource接口不会变

使用C3P0连接池

导入依赖

 <!--C3P0连接池 -->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>

编写C3P0配置文件

创建c3p0-config.xml配置文件 文件名必须是c3p0-config.xml

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jtdb?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </default-config>
    
    <!-- This app is massive! -->
    <named-config name="intergalactoApp">
        <property name="acquireIncrement">50</property>
        <property name="initialPoolSize">100</property>
        <property name="minPoolSize">50</property>
        <property name="maxPoolSize">1000</property>
    
        <!-- intergalactoApp adopts a different approach to configuring statement caching -->
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>
    
        <!-- he's important, but there's only one of him -->
        <user-overrides user="master-of-the-universe">
            <property name="acquireIncrement">1</property>
            <property name="initialPoolSize">1</property>
            <property name="minPoolSize">1</property>
            <property name="maxPoolSize">5</property>
            <property name="maxStatementsPerConnection">50</property>
        </user-overrides>
    </named-config>

</c3p0-config>

编写工具类

/**
 * C3P0连接池
  */
   public class JdbcUtilsC3P0 {

   private static DataSource dataSource=null;

   static {
       dataSource = new ComboPooledDataSource();
   }
   //获取连接
   public static Connection getConnection() throws SQLException {
       return dataSource.getConnection();
   }


}

测试

  //测试C3P0连接池
    @Test
    public void TEst4() throws SQLException {
        Connection connection = JdbcUtilsC3P0.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from user");
        //遍历结果
      while (resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
           System.out.println(resultSet.getObject("age"));
            System.out.println(resultSet.getObject("sex"));
       }
    }

数据库级别的MD5

MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。

实现数据加密

1.对所有password进行加密

update testmd5 set pwd = md5(pwd);

2.对单挑记录进行加密

INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
update testmd5 set pwd = md5(pwd) where name = 'zwt';

3.新增数据自动加密

3.新增数据自动加密

INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));

4.查询登录用户信息(MD5对比加密以后的密码)

SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');
posted @ 2021-08-12 12:54  Ricardo_ML  阅读(229)  评论(3)  编辑  收藏  举报