MySQL学习笔记

1、初识MySQL

javaEE：企业级java开发 Web

前端：（页面：展示，数据）

后台：数据库（DB、DataBase）

数据库是所有软件体系中最核心的存在：DBA

数据库分类

  • 关系型数据库（SQL）：
    • MySQL、Oracle、Sql Server、DB2、SQLlite
    • 通过表和表之间，行和列之间的关系进行数据的存储，（学员信息表、考勤表……）
  • 非关系型数据库（NoSQL）：
    • Redis、MongoDB
    • 非惯性数据库，对象存储，通过对象的自信属性来决定（热点信息）；

DBMS数据库管理软件

  • 数据库的管理软件，科学有效的管理数据（维护 和获取）；

MySQL安装

尽量不要使用exe，（否则卸载难）

1.1、连接数据库

命令行连接：

mysql -u root -proot	-- 连接数据库

-- 修改密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
-- 刷新权限
flush privileges;

--------------------------------------------------------------------------------------
-- 所有sql语句都用分号（;）结尾


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

mysql> use school -- 切换数据库（use 数据库名）
Database changed

show tables;	-- 	查看数据库中所有的表
describe student;	-- 显示数据库中所有表的信息；

create database westos;	-- 创建一个数据库

exit;	-- 退出连接

-- 单行注释（SQL的注释）
/*
(SQL的多行注释)
*/

2、操作数据库

2.1、基本操作

  1. 创建数据库：

    CREATE DATABASE IF NOT EXISTS westos

  2. 删除数据库：

    DROP DATABASE IF EXISTS westos

  3. 选择数据库：

    -- ``   如果表名或字段名是特殊字符，就用反引号包裹；
USE `school`
SELECT `user` FROM student

  4. 查看数据库：

    SHOW DATABASES -- 查看所有的数据库

2.2、数据库数据类型

数值

  • tinyint 十分小的数据 1B
  • smallint 较小的数据 2B
  • mediumint 中等较大的数据 3B
  • int 标准的整数 4B
  • bigint 较大的数据 8B
  • float 浮点数 4B
  • double 浮点数 8B
  • decimal 字符串形式的浮点数 金融计算的时候，一般是使用decimal

字符串

  • char 字符串固定大小的 0~255
  • varchar 可变字符串 0~65535 常用的变量 String
  • tinytext 微型文本 2^8-1
  • text 文本串 2^16-1 保存大文本

时间日期

java.util.Date

  • date YYYY-MM-DD 日期格式
  • time HH：mm：ss 时间格式
  • datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式
  • timestamp 时间戳（常用）
  • year 年份表示

NULL

  • 注意：不要使用NULL进行运算，结果为null

2.3、数据库的字段属性（重点）

  1. Unsigned：
    • 无符号的整数
    • 声明了该列的数不能为负数
  2. zerrofill：
    • 0填充。不足的位置用0填充
  3. 自增：
    • 自动在上一条的基础上+1(默认)
    • 通常用来设计唯一的主键、index，必须是整数类型；
    • 可以自定义设计主键自增的起始值和步长；
  4. 非空：
    • 假设设置为not null ，如果不赋值，就会报错；
    • NULL，如果不写值，默认就是null！
  5. 默认：
    • 设置默认的值

拓展：

  • /* 每一个表，都必须存在以下5个字段！做项目用，表示一个记录存在意义！

id   主键
`version`		乐观锁
is_delete		伪删除
gmt_create		创建时间
gmt_update		修改时间
*/

2.4、创建数据库表（重点）

格式：

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

2.5、数据表的类型

查询创表语句：

SHOW CREATE DATABASE XXXXX
SHOW CREATE TABLES XXXX
DESC XXXXX -- 显示表的具体结构

关于数据库引擎

  • INNODB (默认使用)

  • MYISAM (早些年使用)

  • MYISAM INNODB
    事务支持 N Y
    数据行锁定 N Y
    外键约束 N Y
    全文索引 Y N(5.6.4后---Y)
    表空间的大小 较小 较大(约2倍)
    节约空间，速度快 完全性高，支持事务处理

    在物理空间存在的位置：

    ​ 所有的数据库文件都存在data目录下，一个文件夹对应一个数据库，本质上还是文件存储；

    MySQL引擎在物理文件上的区别：

    • InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
    • MyISAM对应文件结构：
      • *.frm -表结构的定义文件；
      • *.MYD 数据文件(data);
      • *.MYI 索引文件（index）;

设置数据库的字符集编码

CHARSET=utf8

不设置的话，会是MySQL的默认字符集编码(Latin1，不支持中文！)

也可以在my.ini中配置默认的编码(Windows平台，不推荐，兼容问题)

character-set-server=utf8

2.6、修改删除表

修改：（alter)

-- 修改表名：	ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLEteacher RENAME AS teacher1

-- 增加表的字段： ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11)	-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名

-- 删除表的字段： ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1

删除

-- 删除表
DROP TABLE IF EXISTS teacher1
  • 注意：
    • 字段名用反引号包裹：`
    • 注释：-- /**/
    • sql关键字大小写不敏感，建议小写；

3、MySQL数据管理

3.1、外键

方式一：在创建表的时候，增加约束（麻烦，比较复杂）

CREATE TABLE `grade` (
  `gradeid` int(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `gradename` varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- 学生表的gradeid 字段 ，要去引用年级表的 gradeid 字段
-- 1、定义外键key
-- 2、给这个外键key添加约束（执行引用） references 引用
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `pwd` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) COLLATE utf8mb4_bin NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `gradeid` int(10) NOT NULL COMMENT '学生的年级',
  `address` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

注意：删除有外键关系的表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）；

方式二：创建表的时候 没有外键关系，添加外键属性；

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 被引用表(被引用字段);

​ 以上的操作都是属于物理外键，数据库级别的外键，不建议使用！！（避免耦合过多造成困扰）

最佳实现：

  • 数据库就是单纯的表，仅用来存数据；
  • 用程序去实现外键（代码层面）；

3.2、DML语言（全部记住）

数据库意义：数据存储、数据管理

DML语言：数据操作语言

  • insert；
  • update；
  • delete；

3.3、添加（insert）

  • insert
-- 插入语句（添加）
-- insert into 表名([字段名1],[字段名2],....) values('值1','值2',....),('值1','值2',....);
insert into `grade`(`gradename`) values('大一');

-- 如果省略表的字段，它就会根据默认的顺序一一匹配；
insert into `grade` values('2','大二');

-- 一次插入多个行数据
insert into `grade`(`gradename`) values('大三'),('大四');

语法：insert into 表名([字段名1],[字段名2],....) values('值1','值2',....),('值1','值2',....);

3.4、修改（update）

  • update 表名 set column_name=value [,column_name=value,....] where [条件];

条件：where 子句 --运算符；

操作符 含义 范围 返回值
= 等于
<>或 != 不等于
>
<
>=
<=
between .... and ... 再....和...之间 [2,5]
and &&
or ||

注意：

  • column_name 是数据库的列，尽量带上``号；
  • 条件，筛选的条件，若没有指定，则会修改所有的列！！！
  • value 是一个具体的值，也可以是一个变量；
  • 多个设置的属性之间，使用英文逗号隔开；

3.5、删除

delete命令

语法：delete from 表名 [where 条件]

-- 删除数据		（避免这样写，会全部删除）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE id=1;

TRUNCATE 专门清空命令

作用：完全清空一个数据库表，表的结构和索引约束不会变！

-- 清空student表
TRUNCATE `student`

delete 和 TRUNCATE 对比

  • delete：
    • DML语言；
    • 本质操作：逐条删除原表中的数据；
    • 不会影响自增；
  • truncate：
    • DDL语言；
    • 本质操作：删除原表，并重新建立一张同样的无数据的新表。
    • 重新设置自增列，计数器归零；

了解：delete删除问题，重启数据库后：

  • InnoDB 自增列会从1重新开始（它存在内存中，断电易失）；
  • MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）；

4、DQL查询数据（最重点！！）

select 完整语法：

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}];
	-- 指定查询的记录从哪条到哪条

注意：[] 括号代表可选的，{} 代表必须词。

4.1、DQL

（Data Query Language：数据查询语言）

  • 所有的查询操作都用它 select
  • 简单的查询，复杂的查询都能做
  • 数据库中最核心的语言，最重要的语句；
  • 使用频率最高的语句；

4.2、指定查询字段

-- 查询全部的学生		select 字段 from 表
SELECT * FROM student

-- 查询指定字段
SELECT `StudentNo`, `StudentName` FROM student

-- 别名，给结果起一个名字	AS	可以给字段（或表）起别名（备注）
SELECT `StudentNo` AS 学号,`TdentName` AS 学生姓名 FROM student AS stu

-- 函数	concat(a,b)
SELECT CONCAT(`姓名：`,StudentName) AS 新名字 FROM student;

语法：select 字段,... from 表

有时候，列名字不方便阅读时，可以使用AS添加备注~

去重 distinct

语法： SELECT 列名称 FROM 表名称

作用：删除select查询出来的结果中重复的数据；

-- 查询一下有哪些同学参加了考试
SELECT * FROM result -- 查询全部的考试成绩
SELECT `StudentNo` FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` FROM result -- 去重

数据库的列（表达式）

一些特殊的用法：

SELECT VERSION();   -- 查询系统版本（函数）
SELECT 100*C-1 AS 计算结果;	-- 用来计算	（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长

-- 学员考试成绩 + 1分查看
SELECT `StudentNo`,`StudentResult`+1 AS `提分后的成绩` FROM result;

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量……

4.3、where 条件子句

作用：检索数据中符合条件的值；

逻辑运算符

运算符 语法
and && 与
or || 或
not ! 非

注意：尽量使用英文；

模糊查询：比较运算符

运算符 语法 描述
is null a is null 若操作符为null ，结果为真
is not null a is not null 若非空，为真
between a between b and c 若操作符位于b、c之间，为真
like a like b SQL 匹配，若a匹配b，为真
in a in (a1,a2,a3...) 操作符为 (a1,a2,a3...) 中的某一值，为真

实例：like

-- 查询姓刘的同学
-- like 结合 %(代表0到任意个字符)		_(一个字符)
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 (具体的一个或多个值)

-- 查询1001,1002,1003号学员
select `StudentNo`,`StudentName` from `student`
where StudentNo in (1001,1002,1003);

-- 查询在 '安徽','河南洛阳' 的学生
select `StudentNo`,`StudentName` from `student`
where `Address` in ('安徽','河南洛阳');

实例：null 、 not null

-- 查询地址为空的学生
select `StudentNo`,`StudentName` from `student`
where `Address`=null or `Address` is null;

-- 查询出生日期不为空的同学
select `StudentNo`,`StudentName` from `student`
where `BornDate` is not null;

-- 查询出生日期为空的同学
select `StudentNo`,`StudentName` from `student`
where `BornDate` is null;

4.4、联表查询（join on）

img

作用：join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

引用两个表：

  • 方法一：

    • SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P

  • 方法二：

    • SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

join 使用：

  • JOIN 等同于inner join (内连接): 如果表中有至少一个匹配，则返回行；
  • LEFT JOIN : 即使右表中没有匹配，也从左表返回所有的行；
  • RIGHT JOIN : 即使左表中没有匹配，也从右表返回所有的行；
  • FULL JOIN : 只要其中一个表中存在匹配，就返回行；

on 和 where 区别：

在使用 left join 时，on 和 where 条件的区别如下：

  1. on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。

  2. where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。（先生成临时表，再筛选结果）

其实以上结果的关键原因就是 left join,right join,full join 的特殊性。

  • 不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。
  • 注意：而 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。

自连接：

​ 实质上，就是把自己的表和自己的表重新组合连接成一张新表；

实例：

image-20220325193539673 
select a.`categoryname` as '父栏目',b.`categoryname` as '子栏目'
from `category` as a,`category` as b
where a.`categoryid`=b.`pid`

4.5、分页和排序

分页： limit

  • 语法：limit 起始页,分页数
  • 分成N页：第N页--limit (n-1)*pageSize,pageSize;

排序：order by

  • 升序：asc ,ORDER BY 语句默认按照升序对记录进行排序;

  • 降序：desc ;

实例：

SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber;
-- 以字母顺序显示公司名称（Company），并以数字顺序显示顺序号（OrderNumber）：

4.6、子查询

在where中再嵌套查询子语句：where (select * frome table ...)

注意：执行顺序是：由里及外！

实例：

子查询方式：

-- 使用子查询方式：
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result` 
WHERE SubjectNo=(
		SELECT SubjectNo FROM `subject`
    	WHERE SubjectName = '数据库结构-1'
)

结果等同于：

联表查询方式：

SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.SubjectNo = sub.SubjectNo
WHERE SubjectName = '数据库结构-1'
ORDER BY StudentResult DESC

4.7、分组(group by)和过滤(having)

-- 查询不同课程的平均分，最高分，最低分，平均分大于大于80
-- 核心：根据不同的课程分组！
SELECT SubjectName,AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
GROUP BY r.SubjectNo	-- 通过什么字段来分组（学科编号）
HAVING 平均分>80		-- 分组后的过滤条件

5、函数

可以查询官方文档。

5.1、常用函数

很多类似java中存在的方法。

数学运算

  • 绝对值：select abs()
  • 向上取整：select ceiling()
  • 向下取整：select follr
  • 返回0~1之间的随机数：select rand()
  • 判断一个数的符号：select sign()

字符串函数：

  • 字符串长度：select char_length()
  • 拼接字符串：select concat()
  • 插入，从某个位置开始插入（替换）某个长度的字符：select insert()
  • 转换成小写：select lower()
  • 转换成大写：select upper()
  • 返回第一次出现的子串的索引：select instr()
  • 替换出现的指定字符串：select replace()
  • 返回指定的字串（源字符串，截取的位置，截取长度）：select substr()
  • 反转字符串：select reverse()

事件和日期函数（记住）

  • 获取当前日期：select current_date() ( 等价于：select curdate() ）
  • 获取当前时间：select now()
  • 本地时间：select localtime()
  • 系统时间：select sysdate()
  • 获得年/月/日/时/分/秒：select year/month/day/hour/minute/second (date)

系统：

  • select system_user()
  • select user()
  • select version()

5.2、聚合函数（常用）

  • count()
  • sum()
  • avg()
  • max()
  • min()
-- 聚合函数
-- 统计表中数据
SELECT COUNT(`BornDate`) FROM student;	-- count(字段),会忽略所有的null值
SELECT COUNT(*) FROM student;	-- count(*) ,不会忽略null值。本质：计算行数
SELECT COUNT(1) FROM student;	-- count(1) ,不会忽略null值。本质：计算行数

5.3、数据库级别的MD5加密（扩展）

测试：

-- 明文密码
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456');

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
UPDATE testmd5 SET pwd=MD5(pwd)	-- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD%('123456'));

-- 校验：将用户传过来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456');

6、事务

6.1、什么是事务？

解释：同生共死

事务原则：-- ACID原则：

  • 原子性(Atomicity)：
  • 一致性(Consistency)：
  • 隔离性(Isolation)：
  • 持久性(Durability)：

数据读取存在的问题：

  • 脏读：
  • 虚读（幻读）：
  • 不可重复读：

7、索引

什么是索引？

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1、索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个；

  • 主键索引primary key：
    • 唯一的标识，主键不可重复，只能有一个列作为主键；
  • 唯一索引unique key：
    • 避免重复出现的列，多个列都可以标识为唯一索引；
  • 常规索引key/index：
    • 默认的，index。key关键字来设置；
  • 全文索引fulltext：
    • 在特定的数据库引擎下才有，MyISAM；
    • 用于快速定位数据；

基础语法：

-- 索引的使用
-- 1.创建表的时候添加索引
-- 2.创建完毕后，在增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student;

-- 增加一个全文索引（索引名）  列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`StudentName`);

-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student;	-- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘')

8、权限管理和备份

8.1、用户管理

本质：操作用户表----- mysql.user

-- 创建用户CREATE USER 用户名 INDENTIFIED BY `密码`
CREATE USER kuangshen INDENTIFIED BY `123456`;

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456');

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR kuangshen = PASSWORD('123456');

-- 重命名 RENAME USER 原来名字 TO 新名字
RENAME USER kuangshen TO kuangshen2;

-- 用户授权 ALL PRIVILEGES 全部权限 ，   库.表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO kuangshen2;

-- 查询权限
SHOW GRANTS FOR kuangshen2 -- 查看指定用户的权限

-- ROOT用户权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2;

-- 删除用户
DROP USER kuangshen;

8.2、备份

方式一：通过可视化软件备份

方式二：通过命令行导出，mysqldump

  • # mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 ...> 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
  
#导入（恢复）
#登录的情况下，切换到指定的数据库
#source 备份文件
source d:/a.sql
   
mysql -u用户名 -p密码 库名< 备份文件

9、规范数据库设计

9.1、为什么需要设计

当数据库比较复杂的时候，就需要设计

糟糕的数据库设计：

  • 数据冗余，浪费空间；
  • 数据库插入和删除非常麻烦、异常【屏蔽使用物理外键】；
  • 程序的性能差；

良好的数据库设计：

  • 节省内存空间；
  • 保证数据库的完整性；
  • 方便我们开发系统；

软件开发中关于数据库的设计：

  • 分析需求：分析业务和需要处理的数据库的需求；
  • 概要设计：设计关系图E-R图；

设计数据库步骤：

  1. 收集信息，分析需求：

  2. 标识实体：（把需求落地到每个字段）

  3. 标识实体之间的关系：

9.2、三大范式

  • 第一范式（1NF）
    • 原子性：保证每一列不可再分；
  • 第二范式（2NF）
    • 每张表只描述一件事情；
  • 第三范式（3NF）
    • 需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关；

（规范数据库的设计）

规范性 和 性能 之间的问题

在商业过程中，一般规定：

  • 关联查询的表不得超过三张；
  • 数据库的性能更重要（成本、用户体验）；
  • 尽量权衡规范性和性能之间的处理；
  • 有时会故意增加一些冗余的字段，减少数据表的量；（例如订单和商品信息集成在一个表）

10、JDBC（重点）

10.1、什么是JDBC？

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

各数据库厂商只需去实现JDBC和数据库的接口即可；

对于开发人员来讲，只需熟悉掌握JDBC接口的操作即可！

10.2、第一个JDBC程序

需要：mysql-connector-java-5.1.47.jar 包

测试第一个JDBC程序：

//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");	//固定写法，加载驱动

//2.用户信息和url
//问号后面接参数：	使用Unicode编码（支持中文）&utf8编码集（防止乱码）&使用安全的连接
//				?useUnicode=true&characterEncoding=utf8&useSSL=true
//*****常见问题：MySQL版本高于JDBC时：useSSL=true ；否则会报错；
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username= "root";
String password = "123456";

//3.连接成功，数据库对象  connection--代表数据库
Connection connection = DriverManager.getConnection(url,username,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("pwd=" + resultSet.getObject("PASSWORD"));
    System.out.println("email=" + resultSet.getObject("email"));
    System.out.println("birth=" + resultSet.getObject("birth"));
    System.out.println("===========================");
}

//6.释放连接
resultSet.close();
statement.close();
connection.close();

DriverManager //注册驱动

DriverManager.registerDriver(new com.mysql.jdbc.Driver());	//原生写法
Class.forName("com.mysql.jdbc.Driver");	//固定写法，加载驱动

Connection 对象 ---代表数据库

Connection connection = DriverManager.getConnection(url,username,password);

//数据库级别的操作就用这个对象
//数据库设置自动提交、事务提交、回滚等

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

// 协议：//主机地址:端口号/数据库名?参数1&参数2&参数3
// mysql -- 3306
// oracle -- 1521
// jdbc:oracle:thin:@localhost:1521:sid

Statement 对象 ----执行SQL的对象 （PrepareStatement也可以执行SQL）

statement.execute();	//执行任何SQL但是效率没有专门方法高
statement.executeQuery();	//执行查询语句 返回resultSet
statement.executeUpdate();	//更新、插入、删除，都是用这个方法。返回受影响的行数

ResultSet--查询返回的结果集：封装了所有的查询结果

resultSet.getObject();	//在不知道返回值得类型情况下使用，效率不如对应类型的结果集
resultSet.getString();


//遍历
resultSet.next();
resultSet.previous();
resultSet.absolute(row);
resultSet.beforeFirst();	//移动到最前
resultSet.afterLast();	//移动到最后

释放资源

resultSet.close();
statement.close();
connection.close();	//connection对象最耗资源！

提取成工具类

  1. 创建一个db.properties类文件；
  2. 编写一个JdbcUtils工具类，通过JdbcUtils.class.getClassLoader().getResourceAsStream(db.properties)来获取db.properties 的信息。（内含：加载驱动，释放资源的静态方法）；
  3. 编写增删改查、插入方法（Update）；/编写查询方法（query）；

10.3、SQL注入问题

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

本质上是通过拼接字符串，强行获取信息！

例如：

//原本正常的SQL
String sql = "select * from user where name = '"+username+"' and password='"+password+"'";

//利用漏洞的SQL,只要给方法传入的参数拼接上特定的字符串；
//例如
username = "'or'1=1";
password = "'or'1=1";
//此时SQL就变为：
//---->select * from user where name='' or '1=1' and password = '' or '1=1';
//该语句永远成立，可以穿过漏洞获得信息；

10.5、PreparedStatement对象（更安全，更高效）

PreparedStatement对象使用区别：

//PreparedStatement防注入例子
public static void main(String[] args){
    login("'or'1=1","'or'1=1");	//输入漏洞sql
}


//登录业务
public static void login(String username,String password){
    Connection conn =null;
    PreparedStatement st=null;
    ResultSet rs=null;
    try {
		conn = JdbcUtils.getConnection();	//这个  JdbcUtils    是自己建的工具类  
        
        //1.使用  ?  占位符替代参数
        String sql="select * from users where name=? and password=?";
        
        //2.预编译SQL
		st = conn.preparedStatement(sql);
        
        //3.手动给参数赋值
        st.setString(1,username);	//传入参数分别是：(parameterIndex,value)
        st.setString(2,password);
        
        //4.执行
        rs = st.executeQuery();	//获得返回的结果集
        
    while (rs.next()){
        System.out.println("NAME=" + resultSet.getObject("NAME"));
        System.out.println("pwd=" + resultSet.getObject("PASSWORD"));
        }
    }catch(SQLException e){
        e.printStackTrace();
    }finally{
        JdbcUtils.release(conn,st,rs);	
    }
}

执行结果：

​ 最终什么也没有返回。

解释：PreparedStatement对象本质上就是把传递进来的参数当做字符，如果其中存在转义字符会直接忽略，会把SQL中的 引号 ' 直接转义。

10.6、使用IDEA连接数据库

10.7、事务

用jdbc操作事务：

    try {
		conn = JdbcUtils.getConnection();
        
        //关闭数据库的自动提交，就会自动开启事务
        conn.setAutoCommit(false);//开启事务
        
        String sql="update account set money = money-100 where name='A'";
		st = conn.preparedStatement(sql);
        rs = st.executeUpdate();
        
		String sql2="update account set money = money+100 where name='B'";
		st = conn.preparedStatement(sql2);
        rs = st.executeUpdate();
        
        //业务完毕，提交事务
        conn.commit();
        System.out.println("成功！");
   
    }catch(SQLException e){
        //若业务失败，就回滚事务(其实这里默认就会失败回滚，不写回滚也行)
        try{
            conn.rollback();
        } catch (SQLException e1){
            e1.printStackTrace();
        }
        e.printStackTrace();
    }finally{
        JdbcUtils.release(conn,st,rs);	
    }

10.9、数据库连接池

数据库连接 --- 执行完毕 --- 释放；

连接---释放 十分浪费系统资源；

池化技术：准备一些预先的资源，过来就连接预先准备好的；

编写连接池，只需要实现一个DataSource接口就行；

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

DBCP：

C3P0：

Druid：

使用了这些数据库连接池之后，我们在项目开发过程中就不需要编写连接数据库的代码了！

DBCP：

注意：需要用到的jar包： ---> commons-dbcp-1.4、commons-pool-1.6;

此外他还有专门的配置文件：dbcpconfig.properties

JdbcUtils_DBCP工具类：

private DataSource dataSource=null;
static{
    try{
        InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
        Properties properties = new Properties();
        properties.load(in);
        
        //创建数据源   工厂模式--->工厂创建工人
        dataSource = BasicDataSourceFactory.createDataSource(properties);	//传入一个配置文件，返回一个DataSource数据源对象
    }catch(SQLException e){
        e.printStackTrace();
    }
}

//从数据源中获取连接Connection对象
public static Connection getConnection() throws SQLException{
    return dataSource.getConnection;
}

C3P0：

注意：需要用到的jar包： ---> c3p0-0.9.5.5、mchange-commons-java-0.2.19;

此外他还有专门的配置文件：c3p0-config.xml

xml文件可以直接被读取！

private ComboPooledDataSource dataSource=null;
static{
    try{
        //创建数据源   工厂模式--->工厂创建工人
        
        //1.通过对象代码配置
        dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass();
        dataSource.setUser();
        dataSource.setPassword();
        dataSource.setJdbcUrl();
        dataSource.setMaxPoolSize();
        dataSource.setMinPoolSize();
        
        
        //2.配置文件的写法（推荐）
        dataSource = new ComboPooledDataSource("MySQL");	//传入参数   configName。若不穿参数则选择默认值
        
        
        
    }catch(SQLException e){
        e.printStackTrace();
    }
}

//从数据源中获取连接Connection对象
public static Connection getConnection() throws SQLException{
    return dataSource.getConnection;
}

结论：

无论使用什么数据源，本质上只是改变了实现DataSource接口实现，方法不会变！！

posted @ 2022-03-29 01:06  devynlime  阅读(32)  评论(0)    收藏  举报