MySQL 学习笔记 此笔记取自哔哩哔哩狂神视频
1、初识MySQL
JavaEE:企业级Java开发Web
前端（页面:展示，数据!)
后台（连接点:连接数据库JDBC，链接前端（控制，控制视图跳转，和给前端传递数据）)
数据库（存数据，Txt，Excel，word)
只会写代码，学好数据库，基本混饭吃! 操作系统，数据结构与算法!当一个不错的程序猿! 离散数学，数字电路，体系结构，编译原理。＋实战经验，高级程序猿~优秀的程序猿~
1.1、为什么学习数据库
1、岗位需求 2、现在的世界，大数据时代~，得数据库者得天下。 3、被迫需求:存数据 4、数据库是所有软件体系中最核心的存在DBA
1.2、什么是数据库
数据库(DB，DataBase) 作用:存储数据，管理数据 概念:数据仓库，软件，安装在操作系统(window ,linux, mac、....之上! SQL，可以存储大量的数据。500万!
1.3、数据库分类
关系型数据库: (SQL)
-
MySQL，Oracle，Sql Server，DB2,SQLlite
-
通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表，.....
非关系型数据库: (NoSQL) Not Only
-
Redis，MongDB
-
非关系型数据库，对象存储，通过对象的自身的属性来决定。
DBMS(数据库管理系统)
-
数据库的管理软件，科学有效的管理我们的数据。维护和获取数据;
-
MySQL，数据库管理系统!
1.4、MySQL简介
MySQL是一个关系型数据库管理系统
前世:瑞典MySQL AB公司
今生:属于Oracle旗下产品
MySQL是最好的RDBMS (Relational Database Management System，关系数据库管理系统)应用软件之一。
开源的数据库软件~ 体积小、速度快、总体拥有成本低，招人成本比较低，所有人必须会~ 中小型网站、或者大型网站，集群!
官网下载地址: https://dev.mysql.com/downloads/mysqL
安装建议∶ 1、尽量不要使用exe，注册表 2、尽可能使用压缩包安装~
1.5、安装MySQL（用压缩包版）
1、解压 2、把这个包放到自己的电脑环境目录下~ 3、配置环境变量 //即把bin目录的路径放在高级系统设置环境变量的设置里 4、新建mysql配置文件my.ini
[mysqld]
#这里要换成自己的目录
basedir=E:\mysql-5.7.19\
#data文件会自己生成，不用创建，data会成为第一个数据库
datadir=E:\mysql-5.7.19\data\
port=3306
#跳过密码的作用，再密码设置好后，要删除或者注释掉
skip-grant-tables
5、启动管理员模式下的CMD，运行所有的命令 6、安装mysql服务 mysqld -install 7、初始化数据库文件 mysqld --initialize-insecure --user=mysql 8、启动mysql，进去修改密码~ net start mysql 9、进入mysql通过命令行（-p后面不要加空壳)，修改密码(sql语句后面一定要加分号! )
#-u 指用户 root ，-p指密码，此时没有密码，所以-p后为空
mysql -u root -p
#设置账号密码 root 123456
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
#刷新权限。此时刷新了账号密码
flush privileges;
#退出mysql
quit
10、注掉my.ini 文件中的跳过密码 11、重启mysql。连接测试，如果连接成功就OK了! net stop mysql -----> net start mysql
登录为mysql -u root -p123456 或者 mysql -u root -p enter后再输密码
12、如果安装出现问题，可以sc delete mysql，清空服务，重新弄
1.6、安装SQLyog
下载网址和密匙：https://blog.csdn.net/wudinaniya/article/details/78735567
安装完毕
1.7、连接数据库
命令行连接
mysql -uroot -p123456 --连接数据库
update mysq1.user set authentication_ _string=password('123456') where user='root' and Host ='localhost';
--修改用户密码
flush privileges; --刷新权限
--所有的语句都使用 ; 结尾
show databases; -- 查看所有的数据库
mysq1> use schoo1 -- 切换数据库use数据库名
Database changed
show tables; --查看数据库中所有的表
describe student; -- 显示数据库中指定的表的信息
create database westos; --创建一个 数据库
exit; --退出连接
--单行注释(SQL的本来的注释)
/* (sq1的多行注释)
he11oi
asdas
dasdas
*/
数据库 xxx 语言
DDL 定义
DML 操作
DQL 查询
DCL 控制.
2. 操作数据库
操作 数据库>表>数据
MySql关键字不区分大小写
2.1 操作数据库(了解)
[ ]：中括号表示可选
1.新建数据库
CREATE DATABASE [IF NOT EXISTS] CLASS;
2.删除数据库
DROP DATABASE CLASS;
3.使用数据库
USE `school` --若表名或字段名是个特殊字符就需要带``
4.查看数据库
SHOW DATABASES
学习思路：
不会的操作看可视化软件的历史记录
固定语法需要记忆
2.2 数据库表中的列类型（重点）
数值
| 数据类型 | 描述 | 占用字符 |
|---|---|---|
| tinyint | 十分小的整数 | 1个字节 |
| smallint | 较小的整数 | 2个字节 |
| int | 标准整数 | 4个字节 |
| middleint | 中等大小的整数 | 3个字节 |
| bigint | 较大的整数 | 8个字符 |
| float | 浮点数 | 4个字节 |
| double | 浮点数 | 8个字节 |
| decimal | 字符串形式的浮点数（金融计算一般浮点数用decimal） |
字符串
-
char 字符串固定大小 0 ~ 255
-
varchar 可变字符串 0 ~ 65535 常用 String
-
tinytext 微型文本 28 ~ 1 存放博客
-
text 文本串 216 ~ 1 保存大文本
时间日期
-
java.util.Date
-
date YYYY-MM-DD , 📅 日期
-
time HH:MM:SS ⏲ 时间日期
-
datetime YYYY-MM-DD HH:MM:SS 最常用的时间格式
-
timestamp 时间戳 1970.1.1到现在的毫秒数！也较为常用
-
year 年份表示
null
-
没有值，未知
-
注意，不要使用NULL进行运算，计算结果一定为NULL
2.3 数据库表中的字段属性（重点）
Unsigned
-
无符号 的整数
-
一旦钩为 无符号 就不能声明为负数
zerofill
-
零填充
-
不足的位数，使用0来填充
自增
-
通常理解为自动递增，自动在上一条记录的基础上+1（默认）
-
通常用来设计唯一的主键 index ，必须是整数
-
可以自定义自增的初始值
非空
-
NULL /NOT NULL
-
假设设置位非空，如果不插入数据就会报错
-
NULL，如果不填值默认就是null ！
默认
-
设置默认值
-
gender 默认值为男，
阿里巴巴手册 (项目级别) 表示记录存在的意义
-
每一个表都必须存在以下几个字段
id --主键
`version` --乐观锁
is_delete --伪删除
gmt_create --创建时间
gmt_update --修改时间
2.4 操作数据库的表
1.在数据库中用指令新建表
-
注意，使用英文括号，表的明字和字段尽量使用 `` 括起来
--AUTO_INCREMENT 自增
--字符串用单引号括起来
--所有的语句后面加,（英文的），最后一个不用加
--COMMENT 注释
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
-- `gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-
创建结果
-
总结格式
-
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
.....
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
123456
2.查看已创建数据库的语句
SHOW CREATE DATABASE db_school_list --查看表，已db_school_list数据库为例子
-
总结格式
-
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
.....
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
123456
2.查看已创建数据库的语句
SHOW CREATE DATABASE db_school_list --查看表，已db_school_list数据库为例子
-
总结格式
-
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
.....
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
2.查看已创建数据库的语句
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE worker -- 查看student数据表的定义语句
DESC worker -- 显示表结构
-- 这是反向思维，如果遇见用sql创建表不会创，就用可视化工具先做好，然后再逆向查看创建代码
2.5 数据表的类型
-
关于数据库引擎
-
INNODB 默认使用
-
MYISAM 5.5即以前的版本
-
INNODB和MYISAM的区别
| 区别 | MYISAM | INNODB |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，前者的两倍 |
常规使用操作:
-
MYISAM 节约空间，速度较快 聚集索引
-
INNODB 安全性高，书屋处理快，多表多用户操作 非聚集索引
物理空间的位置
-
所有的数据库文件都存在在data文件下
-
数据库本质还是文件存储
MySql引擎在物理文件上的区别
-
INNODB在数据库表中只有一个 *.frm 文件，以及上级目录下的ibdata1文件
-
MYISAM对应的文件
-
*.frm 表结构的定义文件
-
*.MYD 数据文件（data）
-
*.MYI 索引文件（index）
-
设置数据库表的字符集编码
CHARSET=utf8
不设置的话，会是mysql默认的字符集编码~ (不支持中文! ) MySQL的默认编码是Latin1,不支持中文 在my.ini中配置默认的编码
character-set-server=utf8
2.6 修改和删除表
修改和删除表和表中的字段
1. 修改表名
--ALTER TABLE 旧表名 RENAME AS 新表名 ALTER TABLE teacher RENAME AS teacher00
2. 增加表的字段
--ALTER TABLE 表名 ADD 字段名 列属性 ALTER TABLE teacher00 ADD age INT(11)
3. 修改表的字段(重命名 修改约束)
--ALTER TABLE 表名 MODIFY 字段名 列属性 --ALTER TABLE 表名 CHANGE 字段名 列属性 ALTER TABLE teacher00 MODIFY age VARCHAR(11) --修改约束 ALTER TABLE teacher00 CHANGE age age1 VARCHAR(11) --字段重命名
4. 删除字段
--ALTER TABLE 表名 DROP 字段名 ALTER TABLE teacher00 DROP age1
5. 删除表
--DROP TABLE [IF EXSITS] 表名 DROP TABLE IF EXSITS teacher00
所有的创建和删除操作尽量都加上判断，以免报错
3. MySql数据管理
3.1 外键（了解）
外键
-
如图，学生表的grade列 引用 年级表里的id （约束）
方式一：在创建时设置外键
--创建年纪表
CREATE TABLE IF NOT EXISTS `grade`(
`gid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gname` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '年纪名称',
PRIMARY KEY (`gid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
--包含外键的新student表
--定义外键
--给这个外键添加约束（执行引用）reference 引用
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`gid` INT(10) NOT NULL COMMENT '学生年级',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_gid` (`gid`),
CONSTRAINT `FK_gid`FOREIGN KEY (`gid`) REFERENCES `grade`(`gid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
--注意在删除引用外键关系的表时，要先删除外键表 再删除引用外间的表
方法二：创建表的时候没有外键关系
/* ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 那个表(哪个字段) */ ALTER TABLE `student` ADD CONSTRAINT `FK_gid` FOREIGN KEY (`gid`) REFERENCES `grade`(`gid`)
以上的操作都是物理外键（既数据库级别的外键），不建议使用！（避免数据库过多造成困扰）
最佳实践
-
数据库就是单纯的表，只用来存储数据，只有行（数据）和列（字段）
-
我们想使用多张表的数据，想使用外键（用程序去实现）
3.2DML语言（全部记住）
数据库意义：数据存储，数据管理
DML语言：数据操作语言
-
Insert
-
Update
-
Delete
3.3 添加
--插入语句
--insert into 表名([字段1,字段2,字段3,])values('值1'),('值2'),('值3'),...,()
--插入的值和前面的字段要一一对应
--可以插入多条数据，VALUES后面的值要用括号和逗号隔开
INSERT INTO `grade`('gname')VALUES('大一'),('大二'),('大三')
INSERT INTO `student`('gender','name','pwd')VALUES('男','张三','888888'),('男','赵云','zy12345'),('女','胡八一','886688')
3.4 修改
-- 修改语句 -- 修改学员名字，带条件 UPDATE `student` SET `name`='小迷弟' WHERE id = 1; -- 在不指定条件的情况下，会改动所有表 UPDATE `student` SET `name`='小迷弟'; -- 语法： -- UPDATE 表名 SET 列属性=新值 WHERE 条件; -- 修改多个属性，逗号隔开 UPDATE `student` SET `name`='小迷弟',`email`='18465@163.com' WHERE id = 1; -- 语法： -- UPDATE 表名 SET 列属性1=新值[,列属性2=新值] WHERE 条件;
条件：where 子句 运算符 id等于某个值
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | 5<>6 | ture |
| >= | 大于等于 | 5>=6 | false |
| <= | 小于等于 | 5<=6 | ture |
| > | 大于 | 5>6 | false |
| < | 小于 | 5<6 | ture |
| BETWEEN… AND… | 在某个范围 | [5,6] | ture |
| AND | && 与 | 5>6 and 1<2 | false |
| OR | || 或 | 5>6 or 1<2 | ture |
注意总结：
-
数据库的列尽量戴上``
-
条件，筛选的条件，如果没有指定，则会修改左右变量
-
value，是一个具体的值，也可以是一个变量
3.5 删除
-- 删除语句 --删除指定数据 DELETE FROM `student` WHERE id=1 --不加条，删除表的所有数据，避免这样 DELETE FROM `student` --清空表，采用这个更好 TRUNCATE `student`
Delete 和Truncate的区别
-
相同点: 都能删除数据，都不会删除表结构
-
不同点：
-
TRUNCATE 重新设置 自增列 计数器会归零
-
TRUNCATE 不会影响事务
-
-
补充
Delete的删除问题
，重启数据库的现象
-
INNODB 自增序列会重1开始
-
MYISAM 继续从上一个自增量开始
-
4. DQL查询语言（最重点）
4.1 DQL
（Data Query Language ： 数据查询语言）
-
所有的查询操作都用它 关键词是 select
-
简单地查询和复杂的查询它都能做
-
它是数据库中最核心的语言，最重要的语句
-
使用频率最高的语句
4.2 查询指定字段
--查询所有学生
SELECT * FROM student
--查询所有学生的成绩
SELECT * FROM result
--查询指定字段
--SELECT 字段名 FROM 表名
SELECT `StudentNo`,`StudentName` FROM student
--别名，给结果起一个名字 关键词 AS 字段和表都可以起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student
--函数 Concat(a,b) 链接字符串
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
--去重 distinct
SELECT DISTINCT `StudentNo` FROM result
--查询版本
SELECT VERSION()
--计算
SELECT 100*3-1 AS 计算结果
--查询自增步长
SELECT @@auto_increment_increment
--查询所有学生的考试成绩并+1分查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result
语法: SELECT 字段名1[,字段名2……,字段名n] FROM 表名
别名: AS 字段和表都可以起别名
去重: distinct 去除SELECT查询出来的结果中重复的值
数据库中的表达式: 文本值 ，列，NULL，函数，计算表达式，系统变量…
语法升级： SELECT 表达式 FROM 表
4.3 where 条件子句
作用: 检索数据中符合条件的值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | x and y | 与 |
| or || | x or y | 或 |
| not ! | not x | 非 |
--where子句 --查询成绩在95-100之间的学生 --方法1 SELECT `StudentNo`,`StudentResult` FROM result WHERE StudentResult>=95 AND StudentResult<=100 --方法2 SELECT `StudentNo`,`StudentResult` FROM result WHERE StudentResult>=95 && StudentResult<=100 --方法3 模糊查询（区间） SELECT `StudentNo`,`StudentResult` FROM result WHERE StudentResult BETWEEN 95 AND 100 --查询除了学号为1000号学生以外的同学的成绩 --方法1 SELECT `StudentNo`,`StudentResult` FROM result WHERE NOT StudentNo = 1000 --方法2 SELECT `StudentNo`,`StudentResult` FROM result WHERE StudentNo != 1000
模糊查询:比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | x is null | 为null，结果为真 |
| IS NOT NULL | x is not null | 不为nul，结果为真 |
| BETWEEN … AND … | BETWEEN 95 AND 100 | x在95-100之间，则为真 |
| LIKE | x like y | SQL匹配，若x匹配到y，则结果为真 |
| IN | x in y1，y2，… | 假设x在y1，y2…之中的某个值，则结果为真 |
--模糊查询
--查询姓胡的同学
--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 '刘__'
--查询名字中间有'姿'字的同学
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`,`Address` FROM student
WHERE Address IN ('浙江','重庆')
--null / not null
--查询地址为空的学生null ''
SELECT `StudentNo`,`StudentName`,`Address` FROM student
WHERE Address='' OR Address IS NULL
注意：%和like联用，不要乱用。
4.4 联表查询
SQL中的七种JOINS理论详解
LEFT JOIN , INNER JOIN , RIGHT JOIN
--查询参加了考试的同学（学号，姓名，科普编号，分数） SELECT * FROM student SELECT * FROM result /* 思路 1. 分析需求，分析查询的字段来自那些表（连接查询） 2. 确定使用那种连接查询? 7种 确定交叉信息 --JOIN ON是固定语法 连接查询 --WHERE 等值查询 */ --查看并集 INNER JOIN SELECT s.StudentNo,StudentName,SubjectNo,StudentResult FROM student AS s INNER JOIN result AS r WHERE r.StudentNo = s.StudentNo --查看并集和右部分 RIGHT JOIN SELECT s.StudentNo,StudentName,SubjectNo,StudentResult FROM student AS s RIGHT JOIN result r ON r.StudentNo = s.StudentNo --查询缺考同学 --查看并集和右部分 RIGHT JOIN SELECT s.StudentNo,StudentName,SubjectNo,StudentResult FROM student AS s LEFT JOIN result r ON r.StudentNo = s.StudentNo WHERE StudentResult IS NULL --查询参加考试同学的信息(学号，学生姓名，科目名称，成绩) 3张表 SELECT DISTINCT s.StudentNo,StudentName,SubjectNo,StudentResult FROM student AS s RIGHT JOIN result r ON r.StudentNo = s.StudentNo INNER JONIN subject sub WHERE sub.SubjectNo = s.SubjectNo --多表查询，先查询两张表再慢慢增加
| 操作 | 描述 |
|---|---|
| INNER JOIN | 如果表中至少有一个匹配，就返回行 |
| LEFT JOIN | 即使由右表中没有匹配，也会从左表中返回所有的值，（例如新建的学生没有成绩） |
| RIGHT JOIN | 即使由左表中没有匹配，也会从右表中返回所有的值 |
自连接
--自连接用例表
CREATE TABLE `category`(
`categoryid` INT(3) NOT NULL COMMENT 'id',
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1',
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 CHARSET=utf8;
INSERT INTO `category` (`categoryid`,`pid`,`categoryname`)
VALUES ('2','1','信息技术'),
('3', '1', '软件开发'),
('5', '1', '美术设计'),
('4', '3', '数据库'),
('8', '2', '办公信息'),
('6', '3', 'web开发'),
('7', '5', 'ps技术');
12345678910111213141516
自己的表和自己的表连接，核心:一张表拆为两张一样的表即可
-
父类
| categoryid | categoryname |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
-
子类
| pid（父类id） | categoryid | categoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
操作:查询父类对应子类的关系
| 父类 | 子类 |
|---|---|
| 软件开发 | 数据库 |
| 信息技术 | 办公信息 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
--查询父子信息 SELECT a.`categoryName` AS '先修课程',b.`categoryName` AS '当前课程' FROM `category` AS a,`category` AS b WHERE a.`categoryid` = b.`pid` --查询学生所属的年级（学号，学生的姓名，年纪名称） SELECT studentNo,studentName,`GraderName` FROM student s INNER JOIN `grade` g ON s.GradeID = g.GradeID --查询科目所属的年级（科目名称，年纪名称） SELECT subjectName,`GraderName` FROM `subject` sub INNER JOIN `grade` g ON sub.GradeID = g.GradeID -- 思考题 查询参加了数据库结构考试的同学的同学信息（学号，学生姓名，科目名，分数） SELECT studentNo,studentName,subjectName,studentResult FROM student s INNER JOIN `subject` sub ON sub.subjectNo = s.subjectNo INNER JOIN `grade` g ON sub.studentNo = s.studentNo WHERE gradeName = '数据库结构'
4.5 分页和排序
分页 limit 和 排序 order by
--查询参加了数据库结构考试的同学的同学信息（学号，学生姓名，科目名，分数） --对查询到的结果根据程序降序/升序排序 --降序 DESC / 升序 ASC SELECT s.StudentNo,StudentName,SubjectName,StudentResult FROM student s INNER JOIN `subject` sub ON sub.SubjectNo = s.SubjectNo INNER JOIN `grade` g ON g.StudentNo = s.StudentNo WHERE GradeName = '数据库结构' ORDER BY StudentResult DESC --分页，每页只显示五条数据 --语法：limit 当前页，页面的大小 --LIMIT 0,5 0~5 这是第一页 --LIMIT 6,10 6~10 这是第二页 --扩展 --网页应用： 当前 总的页数 页面大小 n代表当前页，pagesize代表页面大小，总页数=总数/页面大小 --LIMIT : 5*(n-1)+1,5*(n-1)+pagesize SELECT s.StudentNo,StudentName,SubjectName,StudentResult FROM student s INNER JOIN `subject` sub ON sub.SubjectNo = s.SubjectNo INNER JOIN `grade` g ON g.StudentNo = s.StudentNo WHERE GradeName = '数据库结构' ORDER BY StudentResult DESC LIMIT 0,5 --思考题 查询’JAVA第一学年'课程成绩排名前十的学生且分数不小于80的学生信息（学号，学生姓名，科目名，分数） SELECT s.StudentNo,StudentName,SubjectName,StudentResult FROM student s INNER JOIN `subject` sub ON sub.SubjectNo = s.SubjectNo INNER JOIN `grade` g ON g.StudentNo = s.StudentNo WHERE GradeName = 'JAVA第一学年' AND StudentResult >= 80 ORDER BY StudentResult DESC LIMIT 0,10 1234567891011121314151617181920212223242526272829303132333435363738394041
为什么要分页？
-
缓解数据库压力，给人的体验更好
-
另一种形式 瀑布流 — 百度图片，无限下拉。
4.6子查询和嵌套查询
WHERE (这个值是计算出来的)
子查询的本质：在WHERE语句中嵌套一个子查询语句
--语法
WHERE 字段属性 =/IN (
SELECT...
)
--查询参加了数据库结构考试的同学的同学信息（学号，科目名，分数）,降序排列
--方式一使用：连接查询
SELECT StudentNo,r.SubjectNo,StudentResult
FROM result r
INNER JOIN `subject` sub
ON sub.SubjectNo = r.SubjectNo
WHERE SubjectNo = '数据结构'
ORDER BY StudentResult DESC
--方式二：使用子查询
SELECT StudentNo,SubjectNo,StudentResult
FROM result r
WHERE SubjectNo = (
SELECT SubjectNo
FROM `subject` sub
WHERE SubjectNo = '数据结构'
)
ORDER BY StudentResult DESC
--查询分数不小于80分的学生学号和姓名
SELECT DISTINCT s.StudentNo,StudentName,StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
WHERE StudentResult >= 80
--在这个基础上增加一个科目 是高等数学
--子查询方式：
AND SubjectNo = (
SELECT SubjectNo
FROM subject sub
WHERE SubjectName = '高等数学'
)
--连接查询方式：
--去掉上一行WHERE ，因为WHERE不可以写两个 HAVING可以
INNER JOIN subject sub
ON s.StudentNo = sub.StudentNo
WHERE SubjectName = '高等数学' AND StudentResult >= 80
12345678910111213141516171819202122232425262728293031323334353637383940414243444546
4.7 分组和过滤
HAVING 和 GROUP BY
--查询不同课程的平均分，最高分，最低分,且平均分要要大于80分 SELECT Subjectname,AVE(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 --过滤过的分组要用HAVING不能用WHERE 1234567
4.8 SELECT 小结
小结
5.MySql函数
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
5.1常用函数(但并不常有)
数学运算
--数学运算 --绝对值 SELECT ABS(-8) --向上取整 SELECT CELLING(9.4) --向下取整 SELECT FLOOR(9.4) --返回一个0~1之间的随机数 SELECT RAND() --判断符号,负数返回-1，正数返回1 SELECT SIGN(10)
字符串
--字符串
--字符串长度
SELECT CHAR_LENGTH('不会真的有人会用这些函数吧')
--字符串拼接
SELECT CONCAT('我','正在学','MySql')
--转大写，转小写
SELECT LOWER('SeLEcT')
SELECT UPPER('SeLEcT')
--返回第一次出现的子串的位置
SELECT INSTR('SeLEcT','ect')
--替换字符串
SELECT REPLACE('不会真的有人会用这些函数吧','会用这些函数','只看视频不实操')
--....
时间和日期（比较重要，数据库内使用比较频繁）
--时间和日期 --获取当前日期 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()
5.2 聚合函数（常用）
聚合函数
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 求平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| … | … |
--聚合函数 --查询学生表人数，都能够统计表中的数据 SELECT COUNT(StudentNo) FROM `student` --会忽略null的值 SELECT COUNT(*) FROM `student` --不会忽略null的值 SELECT COUNT(1) FROM `student` --不会忽略null的值 --其余示例 SELECT SUM(`StudentResult`) AS 总和 FROM `result` SELECT AVG(`StudentResult`) AS 平均分 FROM `result` SELECT MAX(`StudentResult`) AS 最大值 FROM `result` SELECT MIN(`StudentResult`) AS 最小值 FROM `result` --查询不同课程的平均分，最高分，最低分,且平均分要要大于80分 SELECT Subjectname,AVE(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 --过滤过的分组要用HAVING不能用WHERE
5.3 数据库级别的MD5加密（扩展）
MD5
信息摘要算法，主要增强算法复杂度和不可逆性。
MD5 不可逆，具体的值的MD5是一样的
MD5破解网站原理，背后有一个字点，MD5加密后的值，加密的前值
sql
--测试MD5 加密
CREATE TABLE 'testmd5'(
`id` INT(4) NOT NULL,
`name` VACHAR(20) NOT NULL,
`pwd` VACHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
--明文密码
INSERT INTO testmd5 VALUES(1,'测试人员1','123456'),(2,'管理员','7894561')
--加密指定密码
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
--加密全部密码
UPDATE testmd5 SET pwd=MD5(pwd)
--在插入的时候加密
INSERT INTO testmd5 VALUES(3,'测试人员2',MD5('123456'))
--如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密值
SELECT * FROM testmd5 WHERE `name` = '测试人员2' AND `psd` = MD5('123456')
6、事务
6.1 什么是事务
要么都成功，要么都失败
-
SQL执行， A给B转账 A 1000–> 200 B200
-
SQL 执行， B收到A的钱 A800 — B400
将一组SQL放在一个批次中执行
事务原则 ： ACID原则 原子性，一致性，隔离性，持久性 （脏读，幻读…）
原子性（Atomicity）
要么都成功，要么都失败
一致性（Consistency）
事务前后的数据完整性要保持一致
持久性（Durability）–事务提交
事务一旦提交就不可逆转，被持久化到数据库中
隔离性
事务产生多并发时，互不干扰
隔离产生的问题
脏读：
指一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。 （一般是行影响，多了一行）
执行事务
-- 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 保存点 -- 删除保存点
模拟场景
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account(`name`,`money`)
VALUES('A',2000),('B',10000)
-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启事务（一组事务）
UPDATE account SET money = money-500 WHERE `name` = 'A' -- A 转账给B
UPDATE account SET money = money+500 WHERE `name` = 'B' -- B 收到钱
COMMIT ; -- 提交事务
ROLLBACK ; -- 回滚
SET autocommit=1 -- 恢复默认值
7、索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
7.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 -- 非全文索引
7.2 测试索引
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入100万数据
DELIMITER $$ -- 写函数之前必写
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
-- 插入语句
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('用户',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('用户',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100))
SELECT mock_data();
SELECT * FROM app_user WHERE `name`='用户9999' -- 接近半秒
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询99999条记录
-- id _ 表名_字段名
-- create index on 字段
CREATE INDEX id_app_user_name ON app_user(`name`); -- 0.001 s
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询一条记录
索引在小数据的时候，用处不大，但是在大数据的时候，区别十分明显
7.3 索引原则
-
索引不是越多越好
-
不要对经常变动的数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上
索引的数据结构
Hash 类型的索引
Btree: 默认innodb 的数据结构
阅读： http://blog.codinglabs.org/articles/theory-of-mysql-index.html
8、权限管理和备份
8.1用户管理
SQLyog 可视化管理
SQL命令操作
用户表：mysql.user
本质：对这张表进行，增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER sanjin IDENTIFIED BY '123456'
-- 修改密码（修改当前密码）
SET PASSWORD = PASSWORD('111111')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR sanjin = PASSWORD('111111')
-- 重命名 rename user 原名字 to 新名字
RENAME USER sanjin TO sanjin2
-- 用户授权 ALL PRIVILEGES 全部的权限 库，表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO sanjin2
-- 查询权限
SHOW GRANTS FOR sanjin2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM sanjin2
-- 删除用户
DROP USER sanjin2
123456789101112131415161718192021222324252627282930
8.2 MySQL备份
为什么备份：
-
保证重要数据不丢失
-
数据转移
MySQL数据库备份的方式
-
直接拷贝物理文件
-
在SQLyog这种可视化工具中手动导出
-
在想要导出的表或者库中，右键选择备份和导出
-
![在这里插入图片描述]()
-
9、规范数据库设计
当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计：
-
数据冗余，浪费空间
-
数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】
-
程序的性能差
良好的数据库设计：
-
节省内存空间
-
保证数据库的完整性
-
方便我们开发系统
软件开发中，关于数据库的设计
-
分析需求：分析业务和需要处理的数据库的需求
-
概要设计：设计关系图 E-R图
设计数据库的步骤（个人博客）
-
收集信息，分析需求
-
用户表（用户登录注销，用户的个人信息，写博客，创建分类）
-
分类表（文章分类，谁创建的）
-
文章表（文章的信息）
-
友链表（友链信息）
-
自定义表（系统信息，某个关键的字，或者某些主字段）
-
说说表（发表心情…id ,content ,time）
-
-
标识实体（把需求落地到每个字段）
-
标识实体之间的关系
-
写博客 user–>blog
-
创建分类 user–>category
-
关注 user–>user
-
友链–>links
-
评论 user–>user
-
9.2三大范式
为什么需要数据规范化？
-
信息重复
-
更新异常
-
插入异常
-
删除异常
-
无法正常显示异常
-
-
删除异常
-
丢失有效的信息
-
三大范式
第一范式（1NF）
原子性：保证每一列不可再分
第二范式（2NF）
前提：满足第一范式
每张表只描述一件事情
第三范式（3NF）
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
（规范数据库的设计）
规范性和性能的问题
关联查询的表，不得超过三张表
-
考虑商业化的需求和目标（成本和用户体验） 数据库的性能更加重要
-
再规范性能的问题的时候，需要适当的考虑一下，规范性
-
故意给某些表加一些冗余的字段（从多表，变成单表）
-
故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
10、JDBC(重点)
10.1 数据库驱动
驱动：声卡，显卡，数据库
我们的程序会通过数据库驱动，和数据库打交道！
10.2 JDBC
SUN 公司为了简化开发人员的（对数据库的统一）操作，提供了一个(Java操作数据库的)规范，JDBC
这些规范的实现由具体的厂商去做
对于开发人员来说，我们只需要掌握JDBC的接口操作即可
java.sql
javax.sql
还需要导入数据库驱动包mysql-connector-java-5.1.47.jar
10.3 第一个JDBC程序
创建测试数据库
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') 1234567891011121314151617
1.创建一个普通项目
2.导入数据库驱动
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-12VBmers-1594142971429)(C:\Users\53424\AppData\Roaming\Typora\typora-user-images\1594046904540.png)]
3.编写测试代码
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();
}
}
123456789101112131415161718192021222324252627282930313233343536373839
步骤总结： 1.加载驱动
2.连接数据库 DriverManager
3.获取执行SQL的对象 Statement
4.获得返回的结果集
5.释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法
Connection connection= DriverManager.getConnection(url,name,password);
//connection代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
1234567891011
URL
String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false"; //mysql 默认3306 //协议://主机地址:端口号/数据库名？参数1&参数2&参数3 //Oracle 1521 //jdbc:oralce:thin:@localhost:1521:sid 12345678
statement 执行SQL的对象 pPrepareStatement 执行SQL的对象
String sql="SELECT * FROM users";//编写Sql statement.executeQuery(); statement.execute(); statement.executeUpdate();//更新，插入，删除，返回一个受影响的行数 123456
ResultSet 查询的结果集，封装了所以的查询结果
获得指定的数据类型
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询的结果
resultSet.getObject();//在不知道列类型下使用
resultSet.getString();//如果知道则指定使用
resultSet.getInt();
1234567
遍历,指针
resultSet.next(); //移动到下一个
resultSet.afterLast();//移动到最后
resultSet.beforeFirst();//移动到最前面
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
123456
释放内存
//6. 释放连接
resultSet.close();
statement.close();
connection.close();//耗资源
12345
10.4 statement对象
Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sq|语句， executeUpdate执行完后， 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发生查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作，示例操作：
Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("插入成功");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作，示例操作：
Statement statement = connection.createStatement();
String sql = "delete from user where id =1";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("删除成功");
}
CURD操作-update
使用executeUpdate(String sql)方法完成数据修改操作，示例操作：
Statement statement = connection.createStatement();
String sql = "update user set name ='' where name = ''";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("修改成功");
}
CURD操作-read
使用executeUpdate(String sql)方法完成数据查询操作，示例操作：
Statement statement = connection.createStatement();
String sql = "select * from user where id =1";
ResultSet rs= statement.executeQuery(sql);
if(rs.next()){
System.out.println("");
}
代码实现
1.提取工具类
package com.kuang.lesson02.utils;
import java.io.IOException;
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("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");
//1.驱动只用加载一次
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//2.获取连接
public static Connection getConnection() throws Exception{
return DriverManager.getConnection(url, username, password);
}
//3.释放资源
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();
}
}
}
2.编写增删改的方法，exectueUpdate
package com.kuang.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import static com.kuang.lesson02.utils.JdbcUtils.*;
public class TestInnsert {
public static void main(String[] args){
Connection conn =null;
Statement st = null;
ResultSet rs =null;
try {
conn = getConnection();//获取连接
st = conn.createStatement();//获取SQL执行对象
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(5,'sanjin','123456','233223@qq.com','2020-01-01')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
JdbcUtils.release(conn,st,rs);
} catch (Exception e) {
e.printStackTrace();
}
}
}
3.查询 executeQuery
package com.kuang.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import static com.kuang.lesson02.utils.JdbcUtils.*;
public class TestInnsert {
public static void main(String[] args) throws SQLException {
Connection conn =null;
Statement st = null;
ResultSet rs =null;
try {
conn = getConnection();//获取连接
st = conn.createStatement();//获取SQL执行对象
String sql = "select * from users";
rs=st.executeQuery(sql);//查询完毕返回结果集
while (rs.next()){
System.out.println(rs.getString("NAME"));
}
JdbcUtils.release(conn,st,rs);
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
123456789101112131415161718192021222324252627282930313233343536
SQL注入问题
sql存在漏洞，会被攻击导致数据泄露 SQL会被拼接 or
package com.kuang.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import static com.kuang.lesson02.utils.JdbcUtils.getConnection;
public class SQL注入 {
public static void main(String[] args) {
//login( "kuangshen","123456");正常登录
login( username: " 'or '1=1", password: "123456");
}
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"));
}
JdbcUtils.release(conn,st,rs);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(conn,st,rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748
10.5 PreparedStatement对象
PreparedStatement 可以防止SQL注入 ，效率更高。
-
新增
-
删除
-
查询
package com.kuang.lesson03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) {
Connection connection= null;
PreparedStatement pstm=null;
try {
connection = JdbcUtils.getConnection();
//区别
//使用问好占位符代替参数
String sql = "insert into users(id,`NAME`) values(?,?)";
pstm = connection.prepareStatement(sql);//预编译sql，先写sql然后不执行
//手动赋值
pstm.setInt(1,8);
pstm.setString(2,"SANJIN");
//执行
int i = pstm.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(connection,pstm,null);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
1234567891011121314151617181920212223242526272829303132333435363738394041
防止SQL注入本质，传递字符 带有“ ”，转义字符会被转义
10.6 使用IDEA连接数据库
连接成功后，可以选择数据库
双击数据库
更新数据
编写sql代码的地方 
10.7 JDBC事务
要么都成功，要么都失败
ACID原则
原子性：要么全部完成，要么都不完成
一致性：结果总数不变
隔离性：多个进程互不干扰
持久性：一旦提交不可逆，持久化到数据库了
隔离性的问题：
脏读： 一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表发生了改变
虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致
代码实现
-
开启事务
conn.setAutoCommit(false); -
一组业务执行完毕，提交事务
-
可以在catch语句中显示的定义回滚，但是默认失败会回滚
package com.kuang.lesson04; import com.kuang.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class Action { public static void main(String[] args) { Connection conn =null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //关闭数据库的自动提交功能， 开启事务 conn.setAutoCommit(false); //自动开启事务 String sql = "update account set money = money-500 where id = 1"; ps =conn.prepareStatement(sql); ps.executeUpdate(); String sql2 = "update account set money = money-500 where id = 2"; ps=conn.prepareStatement(sql2); ps.executeUpdate(); //业务完毕，提交事务 conn.commit(); System.out.println("操作成功"); } catch (Exception e) { try { //如果失败，则默认回滚 conn.rollback();//如果失败，回滚 } catch (SQLException throwables) { throwables.printStackTrace(); } e.printStackTrace(); }finally { try { JdbcUtils.release(conn,ps,rs); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
10.8数据库连接池
数据库连接–执行完毕–释放
连接–释放 十分浪费资源
池化技术： 准备一些预先的资源，过来就连接预先准备好的
常用连接数 100
最少连接数：100
最大连接数 ： 120 业务最高承载上限
排队等待，
等待超时：100ms
编写连接池，实现一个接口 DateSource
开源数据源实现(拿来即用)
DBCP
C3P0
Druid: 阿里巴巴
使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了
DBCP
需要用到的jar包
dbcp.ar
C3P0
结论
无论使用什么数据源，本质是不变的，DateSource接口不会变，方法就不会变
浙公网安备 33010602011771号