数据库笔记
JAvaEE:企业级Java开发 web
前端(页面:展示,数据!)
后台(连接点:连接数据库JDBC,链接前端(控制,控制视图跳转,给前端传递数据))
数据库(存储数据,管理数据)
关系型数据库 (SQl)
-
MySql ,Oracle, Sql Server ,DB2,SQLite
-
通过表与表之间,行和列之间的关系进行数据的存储,学员信息表,考勤表……
非关系数据库 (NoSQl)Not Only
-
redis,MongDB
-
非关系型数据库,对象存储,通过对象额自身属性来决定。
DBMS(数据库管理系统)
-
数据库的管理软件,科学有效的管理我们的数据。维护和获取数据
-
MySQL,数据库管理系统
MySQL简介
安装MySql
-
解压
-
把这个给包放到自己的电脑环境目录下
-
配置环境变量(path中添加MySQL的bin文件的目录)
-
在mysql目录下新建my.ini文件 ,写入
[mysqld] basedir=D:\MySql\mysql-8.0.22\ datadir=D:\MySql\mysql-8.0.22\data\ port = 3306 #skip-grant-tables
-
启动管理员模式下的CMD,运行所有的命令
-
安装mysql服务
C:\WINDOWS\system32>D: C:\WINDOWS\system32>cd D:\MySql\mysql-8.0.22\bin //注意看前面的目录 D:\MySql\mysql-8.0.22\bin>mysqld -install D:\MySql\mysql-8.0.22\bin>mysqld --initialize-insecure --user=mysql //Service successfully installed.
-
初始化数据库文件
D:\MySql\mysql-8.0.22\bin>mysqld --initialize-insecure --user=mysql
-
启动mysql,进去修改密码
D:\MySql\mysql-8.0.22\bin>net start mysql //MySQL 服务正在启动 ... //MySQL 服务已经启动成功。 D:\MySql\mysql-8.0.22\bin>mysql -u root -p Enter password: //-p后面没有空格 password:后面也没有空格,都是直接回车
-
进入mysql通过命令行(-p后面不要加空格),修改密码(sql语句后面一营要加分号!)改为123456
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; //Query OK, 0 rows affected (0.17 sec)
刷新权限,关闭服务
-
mysql> privileges; //Query OK, 0 rows affected (0.12 sec) mysql> exit //Bye D:\MySql\mysql-8.0.22\bin>net stop mysql //MySQL 服务正在停止. //MySQL 服务已成功停止。
-
重启mysql,连接测试,如果连接成功就OK了。
D:\MySql\mysql-8.0.22\bin>mysql -u root -p Enter password: ****** //Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.22 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. //最后退出就可以了 mysql> exit Bye
安装SQlyog
连接数据库
命令行连接!
mysql -u root -p123456 -- 连接数据库 flush privileges; -- 刷新权限 ---------------------------- -- 所有的语句都是用;结尾 show databases; -- 查看所有的数据库 use school; -- 切换数据库 use 数据库名 Database changed show tables; -- 查看数据库中所有的表 describe student; -- 显示数据库中所有表的信息 create database; -- 创建一个数据库 exit -- 退出链接 -- 单行注释 /* 多行注释 */
数据库 XXX语言
DDL 定义
DML 操作
DQL 查询
DCL 控制
操作数据库
操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
mysql不区分大小写
-
创建数据库
CREATE DATABASE IF NOT EXISTS westos;
-
删除数据库
DROP DATABASE IF EXISTS westos;
-
使用数据库
-- tab上面的键,如果你的表明或者字段名是一个特殊字符,就需要带`` USE `school`;
-
查看数据库
SHOW DATABASES;
对比:SQLyog的可视化操作
学习思路:
-
对照sqlyog可视化历史记录查看sql
-
固定的语法或关键字必须强行记住!
数值
-
tinyint 十分小的数据 1字节
-
smallint 较小的数据 2个字节
-
mediumint 中等大小的数据 3个字节
-
int 标准的整数 4个字节 常用的 int
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节(精度问题)
-
decimal 字符串形式的浮点数 金融计算的时候,一般使用decmial
字符串
-
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 时间戳 1970.1.1到现在的毫秒数! 也较为常用
-
year 年份表示
null
-
没有值,未知
-
注意,不要使用NULL进行运算,结果为NULL
数据库的字段属性(重点)
unsigned:
-
无符号的整数
-
声明了该列不能为负数
zerofill
-
0填充
-
不足的位数,使用0填充
自增
-
通常理解为自增,自动在上一条记录的基础上+1(默认)
-
通常用来设计唯一的主键~index,必须是整数类型
-
可以自定义审计逐渐自增的起始值和步长
非空
-
假设设置为not null 如果不给它赋值,就会报错
默认
-
设置默认的值!
拓展:
/*每个表,都必须存在以下的字段 id 主键 `version` 乐观锁 is_delete 伪删除 gmt_create 创建时间 gmt_update 修改时间 */
创建数据库表(重点)
-- 目标:创建一个school数据库
-- 创建学生表(列,字段)使用SQL创建
-- 学号int 登陆密码varchar(20)姓名,性别varchar(2),出生日期(datatime),家庭住址,Email
-- 注意点,使用英文(),表的名称和字段进俩个使用``括起来
-- AUTO_TNCREMENT 自增
-- 字符串使用 单引号括起来!
-- 所有的语句后面加;(英文版),最后一个不用加
-- PRIMARY KEY 主键,一般一个表只有一个唯一的主键!
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 '密码', `sex` 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] `表明`( `字段名` 列类型 [属性] [索引] [注释], `字段名` 列类型 [属性] [索引] [注释], `字段名` 列类型 [属性] [索引] [注释] )[表类型] [字符集设置] [注释]
常用命令
DESC student; -- 显示表的结构 SHOW CREATE DATABASE school; -- 查看定义表的语句 SHOW CREATE TABLE student; -- 查看定义数据库的语句
数据表的类型
-- 关于数据库引擎 /* INNODB 默认使用~ MYISAM 早些年使用 */
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
常规使用操作:
-
MYISAM 节约空间 苏队较快
-
INNODB 安全性高,属的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹对应一个书库,本质还是文件的存储!
MySQL引擎在物理文件上的区别
-
innoDB在数据库表中只有一个*.frm文件,以及上级目录的ibdata1文件
-
MYISAM对应文件
-
*.frm 表结构的定义文件
-
*.MYD 数据文件(data)
-
*.MYI 索引文件(index)
-
设置数据库表的字符集编码
CHAREST=utf8
不设置的话,回事mysql默认的字符集编码~(不支持中文!)
MySQL的默认编码是Latin1 ,不支持中文
在my.ini中哦欸之默认的编码
character-set-server=utf8
修改删除表
修改
-- 修改表名:ALTER TABLE 旧表名 RENAME AS 新表名 ALTER TABLE teacher 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(3) -- 删除表的字段 ALTER TABLE 表名 DROP 字段名 ALTER TABLE teacher1 DROP age1
删除
-- 删除表 DROP TABLE IF EXISTS 表名 DROP TABLE IF EXISTS teacher1
MySQL数据管理
外键(了解即可)
创建表时添加外键
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 -- 学生表的gradeid字段 要去引用年级表的gradeid -- 定义外键key -- 给这个外键添加约束(执行引用)reference 引用 CREATE TABLE `student` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT '出生日期', `gradeid` INT(10) NOT NULL COMMENT '学生的年级', `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`), KEY `FK_gradeid` (`gradeid`), CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) ) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
删除有外键关系的表的时候,必须要先删除引用表的的表,在删除被引用的表
建完表后添加外键
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 -- 学生表的gradeid字段 要去引用年级表的gradeid -- 定义外键key -- 给这个外键添加约束(执行引用)reference 引用 CREATE TABLE `student` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT '出生日期', `gradeid` INT(10) NOT NULL COMMENT '学生的年级', `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 -- 创建表的时候没有外键关系 ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`); -- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)
以上操作都是物理外键,数据库级别的外键,不建议使用(避免数据库过多造成困扰)
最佳实践
-
数据库就是单纯的表,只用来存储数据,只有行(数据)和列(字段)
-
我们相使用多张表的数据,想使用外键(程序去实现)
DML语言(全部记住)
添加
insert
-- 插入语句(添加) -- INSERT INTO 表名 (字段名1,字段名2,字段名3) VALUES('值1'),('值2'),('值3') INSERT INTO `grade` (`gradename`) VALUES('大四') -- 一般写插入语句,我们呀数据和字段一一对应 INSERT INTO `grade` VALUES('2','大三') -- 插入多个字段 INSERT INTO `grade`(`gradename`) VALUES('大一'),('大二') INSERT INTO `student`(`name`,`pwd`,`sex`,`gradeid`) VALUES('张三','aaaaa','男','3'),('王刚','12345678','女','2')
语法:-- INSERT INTO 表名 (字段名1,字段名2,字段名3) VALUES('值1'),('值2'),('值3')
注意事项:
-
字段与字段之间使用逗号隔开
-
字段是可以省略的,但要一一对应
修改
update 修改谁(条件) set 原来的值=新值
-- 修改学员名字,带了条件 UPDATE `student` SET `name`='韩信' WHERE id = 1; -- 不指定条件的情况下,会修改所有的表 UPDATE `student` SET `name`='狂铁'; -- 语法: -- UPDATE 表名 SET colnum_name = value,[colnum_name = value] WHERE 条件;
条件:where子句 运算符 id等于某个值,大于某个值,在某个区间内修改
操作符会返回 布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | |||
<>或!= | 都是不等于 | ||
> | |||
< | |||
<= | |||
>= | |||
betewwn......and..... | 在某个范围内 | [2,5] | |
and | 5>1 and 1>2 | false | |
or | 5>1 and 1>2 | true |
-- 通过多个条件定位数据 UPDATE student SET sex = '男' WHERE gradeid = 2 AND sex = '女'
注意:
-
colnum_name时数据库的列,尽量带上``
-
条件,筛选的条件,若果没有指定,则会修改所有的列
-
value,是一个具体的值,也可以是一个变量
-
多个设置的属性之间,使用逗号隔开
UPDATE student SET birthday = CURRENT_TIME WHERE `name`='阿珂'
删除
delete命令
语法:delete from 表名 [where 条件]
-- 删除数据(避免这样写,会全部删除) DELETE FROM student -- 删除指定数据 DELETE FROM student WHERE id = ;
truncate命令
作用:完全清空一个数据库表,表的结构和索引约束不会变!
-- 清空studnet表 TRUNCATE TABLE student;
delete和truncate区别
-
相同点:都能处理数据,都不会删除结构
-
不同:
-
truncate 重新设置 自增列 计数器回归零
-
truncate 不会影响事务
-
delete删除的问题,重启数据库,现象:
-
innoDB 自增列重1开始 (存在内存当中的,断电即失)
-
DQL
(Data Query Language:数据查询语言)
-
所有的查询操作都用它 select
-
简单的查询,复杂的查询它都能做
-
数据库中最核心的语言,最重要的语言
-
使用频率最高的语句
-
select完整语句
-- 查询全部的学生 SELECT * FROM student -- 查询指定字段 SELECT `StudentNo`,`StudentName` FROM student -- 别名 ,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名 SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student -- 函数 Concat(a,b) SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
语法:select 字段, .... from 表
有的时候,列名字不是南无的见明知意 我们起别名 。 as 字段名 as 别名 表名 as 别名
去重 distinct
-- 查询以下有哪些同学参加了考试 SELECT * FROM result -- 查询全部的考生成绩 SELECT `studentno` FROM result -- 查询有哪些同学参加了考试 SELECT DISTINCT `studentno` FROM result -- 去重
数据库的列(表达式)
SELECT VERSION() -- 查询系统版本(函数) SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式) SELECT @@auto_increment_increment -- 查询自增长的步长(变量) -- 学员考试成绩 +1分查看 SELECT `studentno`,`studentresult`+1 AS 提分后 FROM `result
数据库中的表达式:文本框,列,Null,函数,计算表达式,系统变量
select 表达式 from 表
where条件子句
作用:检索数据中符合条件的值
搜索的条件有一个或者多个表达式组成!结果为布尔值
逻辑运算符
运算符 语法 描述 and && a and b a&&b 逻辑与 or || a or b a||b 一真则真 not ! not a !a 逻辑非 尽量使用英文字母
-- 查询成绩大于95的数据 SELECT `studentno`,`studentresult` FROM result WHERE `studentresult`>=95 AND `studentresult`<=100 SELECT `studentno`,`studentresult` FROM result WHERE `studentresult`>=95 && `studentresult`<=100 -- 模糊查询(区间) SELECT `studentno`,`studentresult` FROM result WHERE `studentresult` BETWEEN 85 AND 100 -- 处理1000以外的成绩 SELECT `studentno`,`studentresult` FROM result WHERE NOT `studentno`= 1000 SELECT `studentno`,`studentresult` FROM result WHERE `studentno` != 1000
模糊查询:比较运算符
运算符 语法 描述 is null a is null 判空 is not null a is not null between a between b and c like a like b 可用% _ in a in (a1,a2,a3) 在其中一个值,结果为true -- 查询姓刘的同学 -- like结合 %(代表0到人一个字符) _(一个字符)in不可以使用,因为In需要时具体值 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 `Address` IN ('安徽','河南洛阳') -- null not null -- 查询地址为空 null '' SELECT `studentno`,`studentname` FROM `student` WHERE `Address` IS NULL OR `Address`='' -- 查询没有出生日期的同学 为空 SELECT `studentno`,`studentname` FROM `student` WHERE `BornDate` IS NULL
联接查询join
-- ====================联表查询join======================== -- 查询参加了考试的同学(学号,姓名,科目编号,分数) SELECT * FROM student SELECT * FROM result /*思路 1,分析需求,分析查询的子短短来自哪些表,(连接查询) 2,去欸的那个使用哪种来连接查询? 7种 去欸的那个交叉点(这两个表张哪个数据是相同的) 判断的条件:学生表的中studentNo = 成绩表 studentNo */ -- 内连接 inner join 查询两个表中的结果集中的交集 -- 外连接 outer join /* 左外连接 left join (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充) 右外连接 right join */ -- join(链接的表) on(的条件) -- where 等值查询 SELECT s.studentNo,studentName,SubjectNo,StudentResult FROM student AS s INNER JOIN result AS r WHERE s.studentNo = r.studentNo SELECT s.studentNo,studentName,SubjectNo,StudentResult INNER JOIN result AS r ON s.studentNo = r.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 -- 查询缺考的同学 SELECT s.studentNo,studentName,SubjectNo,StudentResult FROM student s LEFT JOIN result r ON s.studentNo=r.studentNo WHERE r.studentResult IS NULL -- 查询了参加考试的同学信息;学号,学生姓名,学科名,分数 SELECT s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON sub.subjectno = r.subjectno
自连接
自己的表和自己的表连接,核心:一张表拆违两张一样的表即可
-- 创建一个表 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','办公信息')
父类
categoryid categoryName 2 信息技术 3 软件开发 5 美术设计 子类
pid 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
排序
-- 排序:升序 ASC ,降序 DESC -- order by 通过哪个字段排序,怎么排 SELECT s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON sub.subjectno = r.subjectno WHERE sub.subjectname='高等数学-2' ORDER BY StudentResult DESC -- 降序
分页
-- ====================分页=================== -- 100万条数据 -- 缓解数据库压力,给人的体验更好,对应为瀑布流 -- 分页,每页只显示五条数据 -- 语法:linit 起始值,页面的大小 -- 页面应用:当前 总的页数,页的大小 -- limit 0,5 1~5 -- limit 1,5 2~6 SELECT s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON sub.subjectno = r.subjectno WHERE sub.subjectname='高等数学-2' ORDER BY StudentResult DESC -- 降序 LIMIT 0,5 -- 第N页 : limit (pageNo-1)*pageSzie,pageSzie -- [pageNo:页码,pageSize:单页面显示条数]
子查询
/*============== 子查询 ================ 什么是子查询? 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句 嵌套查询可由多个子查询组成,求解的方式是由里及外; 子查询返回的结果一般都是集合,故而建议使用IN关键字; */ -- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列 -- 方法一:使用连接查询 SELECT studentno,r.subjectno,StudentResult FROM result r INNER JOIN `subject` sub ON r.`SubjectNo`=sub.`SubjectNo` WHERE subjectname = '数据库结构-1' ORDER BY studentresult DESC; -- 方法二:使用子查询(执行顺序:由里及外) SELECT studentno,subjectno,StudentResult FROM result WHERE subjectno=( SELECT subjectno FROM `subject` WHERE subjectname = '数据库结构-1' ) ORDER BY studentresult DESC; -- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名 -- 方法一:使用连接查询 SELECT s.studentno,studentname FROM student s INNER JOIN result r ON s.`StudentNo` = r.`StudentNo` INNER JOIN `subject` sub ON sub.`SubjectNo` = r.`SubjectNo` WHERE subjectname = '高等数学-2' AND StudentResult>=80 -- 方法二:使用连接查询+子查询 -- 分数不小于80分的学生的学号和姓名 SELECT r.studentno,studentname FROM student s INNER JOIN result r ON s.`StudentNo`=r.`StudentNo` WHERE StudentResult>=80 -- 在上面SQL基础上,添加需求:课程为 高等数学-2 SELECT r.studentno,studentname FROM student s INNER JOIN result r ON s.`StudentNo`=r.`StudentNo` WHERE StudentResult>=80 AND subjectno=( SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2' ) -- 方法三:使用子查询 -- 分步写简单sql语句,然后将其嵌套起来 SELECT studentno,studentname FROM student WHERE studentno IN( SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=( SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2' ) )
分组查询
-- 查询不同课程的平均分,最高分,最低分 -- 核心:(根据不同的课程分组) SELECT subjectname, AVG(`studentResult`) 平均分,MAX(`studentResult`),MIN(`studentResult`) FROM result r INNER JOIN `subject` sub ON r.subjectno=sub.subjectno GROUP BY sub.subjectno HAVING 平均分>=70
MySQL函数
常用函数
-- ============常用函数============== -- 数学运算 SELECT ABS(-8) -- 绝对值 SELECT CEILING(9.4) -- 向上取整 SELECT FLOOR(9.4) -- 向下取整 SELECT RAND() -- 返回一个0~1之间的随机数 SELECT SIGN(10) -- 判断一个数的符号 0-0,负数返回-1,正数返回1 -- 字符串函数 SELECT CHAR_LENGTH('即使再小的他妈的') -- 字符串的长度 SELECT CONCAT('去','你''丢你','老牟') -- 拼接字符串 SELECT INSERT('你是个呆瓜'1,2,'我是') -- 从某个位置开始替换字符串 SELECT LOWER('kuangshen') -- 小写 SELECT UPPER('kuangshen') -- 大写 SELECT INSTR('kuangshen','h') -- 返回第一次出现的索引位置 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 VERSION(); /*版本*/ SELECT USER(); /*用户*/
聚合函数(常用)
函数名称 描述 count() 计数 sum() 求和 avg() 平均值 max() 最大值 min() 最小值 ........... ........... SELECT COUNT(`BornDate`) FROM student; -- count(字段),会忽略null值 SELECT COUNT(*) FROM student; -- count(*),不会忽略null 本质 计算行数 SELECT COUNT(1) FROM student; -- count(*),不会忽略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 -- 查询不同课程的平均分,最高分,最低分 -- 核心:(根据不同的课程分组) SELECT subjectname, AVG(`studentResult`) 平均分,MAX(`studentResult`),MIN(`studentResult`) FROM result r INNER JOIN `subject` sub ON r.subjectno=sub.subjectno GROUP BY sub.subjectno HAVING 平均分>=70
数据库级别的MD5加密
什么是MD5?
主要增强算法复杂度和不可逆性
MD5不可逆,具体的值的md5是一样的
MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密前的值
-- ===============MD5======================= CREATE TABLE testMD5 ( `id` INT(4) NOT NULL, `name` VARCHAR(20) NOT NULL, `pwd` VARCHAR(50) NOT NULL, PRIMARY KEY(`id`) )ENGINE=INNODB CHARSET=utf8 -- 明文密码 INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456') -- 加密 UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1; -- 插入时加密 INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456')) -- 如何校验,将用户传递进来的密码,进行MD5加密,然后对比加密后的值 SELECT * FROM testmd5 WHERE pwd=MD5('123456')
事务
什么是事务
要么都成功,要么都失败
1,SQL执行,A给B转账 A1000 --->200 B200
2,SQL执行 B收到A的钱 A800 ----> B400
将一组SQL放在一个批次中去执行
事务原则:ACID原则 原子性 一致性 隔离性 持久性 (脏读)(幻读)……
参考博客:
原子性(Atomicity)
事务中的操作要么都发生,要么都不发生
一致性(Consistency)
事务前后数据的完整性必须保持一致
隔离性(Isolation)
多个并发事务之间要相互隔离
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
隔离所导致的一些问题
脏读:
指一个事务读取了另外一个事务未提交的数据
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
虚读(幻读):
是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致
-- =====================事务======================== -- MySQL是默认开启事务自动提交的 SET autocommit = 0; /*关闭*/ SET autocommit = 1; /*开启*/ -- 手动处理事务 -- 事务开启 START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都子啊同一个事务内 INSERT XX INSERT XX -- 事务结束 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(4) NOT NULL AUTO_INCREMENT, `name` VARCHAR(30) NOT NULL, `money` DECIMAL(9,2) NOT NULL, PRIMARY KEY(`id`) )ENGINE=INNODB CHARSET=utf8 ALTER TABLE accunt RENAME AS account INSERT INTO account(`name`,`money`) VALUES('A','2000.00'),('B','10000.00') -- 模拟转账 SET autocommit = 0; -- 关闭自动提交 START TRANSACTION; -- 开启一个事务 UPDATE account SET money = money-500 WHERE `name` = 'A' -- A减500 UPDATE account SET money = money+500 WHERE `name` = 'B' -- B 加500 COMMIT; -- 提交事务 ROLLBACK; -- 回滚 SET autocommit = 1; -- 回复默认值
索引
MySQL官方对索引的定义:索引(index)是帮助MySQL高效获取数据的数据结构。0.5s 0.00001s 提取句子主干,就可以得到索引的本质:索引是数据结构
7.1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
-
主键索引(primary key)
-
唯一的标识,主键不可重复,只能有一个列作为主键
-
-
唯一索引(unique key)
-
避免重复的列出现,唯一索引可以重复,多个列都可以标识位 唯一索引
-
-
常规索引(key/index)
-
默认的,index,key关键字来设置
-
-
全文索引(fulltext)
-
快速定位数据
-
在特定的数据库引擎下才有
-
-- 索引的使用 -- 1,在创建表的死后给字段增加索引 -- 2,创建完毕后,增加索引 -- 显示所有的索引信息 SHOW INDEX FROM student -- 增加一个全文索引 索引名(列名) ALTER TABLE student ADD FULLTEXT `studentname`(`studentname`) -- explain 分析sql执行的状况 EXPLAIN SELECT * FROM student; -- 非全文索引 EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');
测试索引
-- 插入一百万条数据 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`) VALUES(CONCAT('用户',i),'2778408452@qq.com',CONCAT('18',FLOOR((RAND()*(999999999-100000000)+1000000000))), FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100)); SET i = i+1; END WHILE; RETURN i; END; SELECT mock_data(); -- 执行函数 DROP FUNCTION mock_data; -- 删除函数 SELECT * FROM app_user WHERE `name`='用户9999'; -- 2.510 sec SELECT * FROM app_user WHERE `name`='用户9999'; -- 2.233 sec SELECT * FROM app_user WHERE `name`='用户9999'; -- 2.199 sec EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'; -- id_表名_字段名 -- create index 索引名 0n 表(字段) CREATE INDEX id_app_user ON app_user(`name`) SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.106 sec SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0 sec SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0 sec EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
索引原则
-
索引不是越多越好
-
不要对进程变动数据加索引
-
小数据量的表不需要加索引
-
索引一般加载常用来查询的字段上
索引的数据结构
Hash类型的索引
Btree:InnoDB的默认数据结构~
阅读:
用户管理
MySQL备份
为什么要备份:
-
保证重要的数据不丢失
-
数据转移
MySQL数据库备份的方式
-
直接拷贝物理文件
-
在sqlyog这种可视化工具中手动导出
-
在想要导出的表或者库中,右键,选择备份或导出
-
-
使用命令行导出 mysqldump 命令行使用
-- mysqldump -h主机 -u用户名 -p密码 数据库 表名>物理磁盘位置\文件名 C:\WINDOWS\system32>mysqldump -hlocalhost -uroot -p123456 school student >F:\个人资料(C盘移动)\桌面\a.sql -- mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2>物理磁盘位置\文件名 C:\WINDOWS\system32>mysqldump -hlocalhost -uroot -p123456 school student result >F:\个人资料(C盘移动)\桌面\b.sql -- mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置\文件名 C:\WINDOWS\system32>mysqldump -hlocalhost -uroot -p123456 school >F:\个人资料(C盘移动)\桌面\c.sql -- 导入 -- 在登录的情况下,切换到指定的数据库 source F:\个人资料(C盘移动)\桌面\a.sql -- 或者 mysql -u用户名 -p密码 库名<备份文件
假如你要备份数据库,防止数据丢失
把数据库给朋友, sql文件传给别人即可
规范数据设计
为什么需要设计
糟糕的数据可设计
良好的数据库设计
三大范式
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事
第三范式(3NF)
前提:满足第一范式和第二范式
第三范式需要确保数据表中每一列数据和主键直接相关,而不能间接相关
(规范数据库的设计)
规范性和性能的问题
关联查询的表不能超过三张表
-
考虑商业化的需求和目标(成本,用户体验!)数据库的性能更加重要
-
在规范性能的问题的时候,需要适当的考虑一下范性
-
故意给某些表增加一些冗余的字段(从多表查询中变为单表查询)
-
故意增加一些计算列(从大数据量降低为小数据量的查询)
JDBC(重点)
数据库驱动
驱动:显卡,声卡,数据库
我们的程序会通过数据库驱动,和数据库打交道
JDBC
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,俗称JDBC。这些规范的事件有具体的厂商去做~
java.sql
javax.sql
还需要导入一个数据库驱动包
第一个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','zhangsan','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')
1,创建一个普通项目
2,导入数据库驱动
3,编写测试代码
package com.study.day01; import java.sql.*; //我的第一个JDBC程序 public class jdbcFirstDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1,加载驱动 Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动 //2,用户信息和url String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8"; 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(); }
步骤总结:
-
加载驱动
-
连接数据库 DriverManager
-
获得执行sql的对象 Statement
-
获得返回的结果集
-
释放连接
DiverManager
Class.forName("com.mysql.jdbc.Diver");//固定写法,加载驱动 Connection connection = DriverManager.getConnection(url, username, password); //connection 代表数据库 //数据库设置自动提交 //事务提交 //事务滚回 connection.rollback(); connection.commit(); connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8"; //mysql -- 3306 //协议://主机地址:端口号//数据库名?参数1&参数2&参数3 //oralce -- 1521 //jdbc:oracle:thin:@localhost:1521:sid
Statement执行SQL的对象 PrepareStatement执行SQL的对象
String sql = "select * from users"; statement.executeQuery();//查询操作返回ResultSet statement.execute();//查询任何sql statement.executeUpdate();//更新,插入,删除。都是用这个,返回一个受影响的行数
ResultSet 查询的结果集:封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道列类型的情况下使用 //如果知道列类型就是用指定的类型 resultSet.getString(); resultSet.getInt(); resultSet.getFloat(); resultSet.getDate(); ……
遍历,指针
resultSet.beforeFirst();//移动到最前面 resultSet.afterLast();//移动到最后面 resultSet.next();//移动到下一个数据 resultSet.previous();//移动到前一行 resultSet.absolute(row);//移动到指定行
释放连接
resultSet.close(); statement.close(); connection.close();//耗资源 使用完关闭
1.提取工具类
1 package com.study.day02.utils; 2 3 import java.io.InputStream; 4 import java.sql.*; 5 import java.util.Properties; 6 7 public class JdbcUtils { 8 private static String driver = null; 9 private static String url = null; 10 private static String username = null; 11 private static String password = null; 12 static{ 13 try{ 14 InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); 15 Properties properties = new Properties(); 16 properties.load(in); 17 18 driver = properties.getProperty("driver"); 19 url = properties.getProperty("url"); 20 username = properties.getProperty("username"); 21 password = properties.getProperty("password"); 22 //1,驱动只用加载一次 23 Class.forName(driver); 24 } catch (Exception e) { 25 e.printStackTrace(); 26 } 27 } 28 29 //获取连接 30 public static Connection getConnection() throws SQLException { 31 return DriverManager.getConnection(url,username,password); 32 } 33 34 //释放连接资源 35 public static void release(Connection conn,Statement st,ResultSet rs){ 36 if(rs!=null){ 37 try { 38 rs.close(); 39 } catch (SQLException e) { 40 e.printStackTrace(); 41 } 42 } 43 if(st!=null){ 44 try { 45 st.close(); 46 } catch (SQLException e) { 47 e.printStackTrace(); 48 } 49 } 50 if(conn!=null){ 51 try { 52 conn.close(); 53 } catch (SQLException e) { 54 e.printStackTrace(); 55 } 56 } 57 } 58 }
2,编写增删改
1 package com.study.day02; 2 3 import com.study.day02.utils.JdbcUtils; 4 5 import java.sql.Connection; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.sql.Statement; 9 10 public class TestInsert { 11 public static void main(String[] args) { 12 Connection conn = null; 13 Statement st = null; 14 ResultSet rs = null; 15 try { 16 conn = JdbcUtils.getConnection();//获取数据库连接 17 st = conn.createStatement();//获取SQL的执行对象 18 String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)" + 19 "VALUES(4,'kuangshen','123456','2784234567@qq.com','2022-01-01')"; 20 int i = st.executeUpdate(sql); 21 if(i>0){ 22 System.out.println("插入成功!"); 23 } 24 } catch (SQLException e) { 25 e.printStackTrace(); 26 }finally { 27 JdbcUtils.release(conn,st,rs); 28 } 29 30 } 31 } 32 package com.study.day02; 33 34 import com.study.day02.utils.JdbcUtils; 35 36 import java.sql.Connection; 37 import java.sql.ResultSet; 38 import java.sql.SQLException; 39 import java.sql.Statement; 40 41 public class TestUpdate { 42 public static void main(String[] args) { 43 Connection conn = null; 44 Statement st = null; 45 ResultSet rs = null; 46 try { 47 conn = JdbcUtils.getConnection();//获取数据库连接 48 st = conn.createStatement();//获取SQL的执行对象 49 String sql = "UPDATE users SET `name`='kuangshen',`password`='654321' WHERE id=1"; 50 int i = st.executeUpdate(sql); 51 if(i>0){ 52 System.out.println("更新成功!"); 53 } 54 } catch (SQLException e) { 55 e.printStackTrace(); 56 }finally { 57 JdbcUtils.release(conn,st,rs); 58 } 59 60 } 61 } 62 package com.study.day02; 63 64 import com.study.day02.utils.JdbcUtils; 65 66 import java.sql.Connection; 67 import java.sql.ResultSet; 68 import java.sql.SQLException; 69 import java.sql.Statement; 70 71 public class TestDelete { 72 public static void main(String[] args) { 73 Connection conn = null; 74 Statement st = null; 75 ResultSet rs = null; 76 try { 77 conn = JdbcUtils.getConnection();//获取数据库连接 78 st = conn.createStatement();//获取SQL的执行对象 79 String sql = "DELETE FROM users WHERE id =4"; 80 int i = st.executeUpdate(sql); 81 if(i>0){ 82 System.out.println("删除成功!"); 83 } 84 } catch (SQLException e) { 85 e.printStackTrace(); 86 }finally { 87 JdbcUtils.release(conn,st,rs); 88 } 89 90 } 91 } 92 3,查询 93 94 package com.study.day02; 95 96 import com.study.day02.utils.JdbcUtils; 97 98 import java.sql.Connection; 99 import java.sql.ResultSet; 100 import java.sql.SQLException; 101 import java.sql.Statement; 102 103 public class TestSelect { 104 public static void main(String[] args) { 105 Connection conn = null; 106 Statement st = null; 107 ResultSet rs = null; 108 try { 109 conn = JdbcUtils.getConnection();//获取数据库连接 110 st = conn.createStatement();//获取SQL的执行对象 111 String sql = "select * from users where id = 1"; 112 rs = st.executeQuery(sql); 113 while(rs.next()){ 114 System.out.println(rs.getString("name")); 115 } 116 } catch (SQLException e) { 117 e.printStackTrace(); 118 }finally { 119 JdbcUtils.release(conn,st,rs); 120 } 121 122 } 123 }
SQL 注入问题
1 package com.study.day02; 2 3 import com.study.day02.utils.JdbcUtils; 4 5 import java.sql.Connection; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.sql.Statement; 9 10 public class SQL注入 { 11 public static void main(String[] args) { 12 Login("kuangshen","123456"); 13 } 14 15 public static void Login(String username,String password){ 16 Connection conn = null; 17 Statement st = null; 18 ResultSet rs = null; 19 try { 20 conn = JdbcUtils.getConnection();//获取数据库连接 21 st = conn.createStatement();//获取SQL的执行对象 22 23 // SELECT * FROM users WHERE `name`='kuangshen' AND `password`='123456' 24 String sql = "select * from users where `name`='"+username+"' and `password`='"+password+"'"; 25 rs = st.executeQuery(sql); 26 while(rs.next()){ 27 System.out.println(rs.getString("name")); 28 System.out.println(rs.getString("password")); 29 System.out.println("========================================"); 30 } 31 } catch (SQLException e) { 32 e.printStackTrace(); 33 }finally { 34 JdbcUtils.release(conn,st,rs); 35 } 36 37 } 38 }
SQL注入
sql存在漏洞,会被攻击导致数据泄露,SQL会被拼接or
SELECT * FROM users WHERE
name
='' OR '1=1' ANDpassword
='' OR '1=1'1 package com.study.day02; 2 3 import com.study.day02.utils.JdbcUtils; 4 5 import java.sql.Connection; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.sql.Statement; 9 10 public class SQL注入 { 11 public static void main(String[] args) { 12 //Login("kuangshen","123456"); 13 Login(" 'or '1=1"," 'or '1=1");//返回所有的用户和密码 14 } 15 16 public static void Login(String username,String password){ 17 Connection conn = null; 18 Statement st = null; 19 ResultSet rs = null; 20 try { 21 conn = JdbcUtils.getConnection();//获取数据库连接 22 st = conn.createStatement();//获取SQL的执行对象 23 24 // SELECT * FROM users WHERE `name`='kuangshen' AND `password`='123456' 25 String sql = "select * from users where `name`='"+username+"' and `password`='"+password+"'"; 26 rs = st.executeQuery(sql); 27 while(rs.next()){ 28 System.out.println(rs.getString("name")); 29 System.out.println(rs.getString("password")); 30 System.out.println("========================================"); 31 } 32 } catch (SQLException e) { 33 e.printStackTrace(); 34 }finally { 35 JdbcUtils.release(conn,st,rs); 36 } 37 38 } 39 }
PreparedStatrment对象
PreparedStatrment可以防止SQL注入,效率更好!
-
新增
1 package com.study.day03; 2 3 import com.study.day02.utils.JdbcUtils; 4 import java.sql.Connection; 5 import java.sql.PreparedStatement; 6 import java.sql.SQLException; 7 import java.util.Date; 8 9 public class TestInsert { 10 public static void main(String[] args) { 11 Connection conn = null; 12 PreparedStatement st = null; 13 try { 14 conn = JdbcUtils.getConnection(); 15 //区别 16 //使用? 占位符代表参数 17 String sql = "insert into users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)"; 18 st = conn.prepareStatement(sql);//预编译sql,先写sql,然后不执行 19 //手动参数赋值 20 st.setInt(1,4); 21 st.setString(2,"sunwukong"); 22 st.setString(3,"12345643"); 23 st.setString(4,"243576432@qq.com"); 24 st.setDate(5,new java.sql.Date(new Date().getTime())); 25 //执行 26 int i = st.executeUpdate(); 27 if(i>0){ 28 System.out.println("插入成功!"); 29 } 30 } catch (SQLException e) { 31 e.printStackTrace(); 32 }finally { 33 JdbcUtils.release(conn,st,null); 34 } 35 } 36 }
2, 删除
1 package com.study.day03; 2 3 import com.study.day02.utils.JdbcUtils; 4 5 import java.sql.Connection; 6 import java.sql.PreparedStatement; 7 import java.sql.SQLException; 8 import java.util.Date; 9 10 public class TestDelete { 11 public static void main(String[] args) { 12 Connection conn = null; 13 PreparedStatement st = null; 14 try { 15 conn = JdbcUtils.getConnection(); 16 String sql = "delete from users where id = ?"; 17 st = conn.prepareStatement(sql); 18 st.setInt(1,4); 19 int i = st.executeUpdate(); 20 if(i>0){ 21 System.out.println("删除成功!"); 22 } 23 } catch (SQLException e) { 24 e.printStackTrace(); 25 }finally { 26 JdbcUtils.release(conn,st,null); 27 } 28 } 29 }
3,更新
1 package com.study.day03; 2 3 import com.study.day02.utils.JdbcUtils; 4 5 import java.sql.Connection; 6 import java.sql.PreparedStatement; 7 import java.sql.SQLException; 8 import java.util.Date; 9 10 public class TestUpdate { 11 public static void main(String[] args) { 12 Connection conn = null; 13 PreparedStatement st = null; 14 try { 15 conn = JdbcUtils.getConnection(); 16 String sql = "update users set `name` = ? where id = ?;"; 17 st = conn.prepareStatement(sql); 18 st.setString(1,"wanglaowu"); 19 st.setInt(2,1); 20 int i = st.executeUpdate(); 21 if(i>0){ 22 System.out.println("更新成功!"); 23 } 24 } catch (SQLException e) { 25 e.printStackTrace(); 26 }finally { 27 JdbcUtils.release(conn,st,null); 28 } 29 } 30 }
4,查询
1 package com.study.day03; 2 3 import com.study.day02.utils.JdbcUtils; 4 5 import java.sql.Connection; 6 import java.sql.PreparedStatement; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 10 public class TestSelect { 11 public static void main(String[] args) { 12 Connection conn = null; 13 PreparedStatement st = null; 14 ResultSet rs = null; 15 try { 16 conn = JdbcUtils.getConnection(); 17 String sql = "select * from users where id = ?"; 18 st = conn.prepareStatement(sql); 19 st.setInt(1,1); 20 rs = st.executeQuery(); 21 while(rs.next()){ 22 System.out.println(rs.getString("name")); 23 } 24 } catch (SQLException e) { 25 e.printStackTrace(); 26 }finally { 27 JdbcUtils.release(conn,st,rs); 28 } 29 } 30 }
SQL注入
1 package com.study.day03; 2 3 import com.study.day02.utils.JdbcUtils; 4 import java.sql.PreparedStatement; 5 import java.sql.*; 6 7 public class SQL注入 { 8 public static void main(String[] args) { 9 Login("lisi","123456"); 10 //Login(" 'or '1=1"," 'or '1=1");//返回所有的用户和密码 11 } 12 13 public static void Login(String username,String password){ 14 Connection conn = null; 15 PreparedStatement st = null; 16 ResultSet rs = null; 17 try { 18 conn = JdbcUtils.getConnection();//获取数据库连接 19 // SELECT * FROM users WHERE `name`='kuangshen' AND `password`='123456' 20 String sql = "select * from users where `name`=? and `password` =?"; 21 st = conn.prepareStatement(sql);//获取SQL的执行对象 22 st.setString(1,username); 23 st.setString(2,password); 24 rs = st.executeQuery(); 25 while(rs.next()){ 26 System.out.println(rs.getString("name")); 27 System.out.println(rs.getString("password")); 28 System.out.println("========================================"); 29 } 30 } catch (SQLException e) { 31 e.printStackTrace(); 32 }finally { 33 JdbcUtils.release(conn,st,rs); 34 } 35 36 } 37 }
IDEA连接数据库
事务
要么都成功,要么都失败
ACID原则
原子性:要么全部完成,要么都不完成
一致性:总数不变
隔离性:多个进程互不影响
持久性:一旦提交不可逆,持久到数据库了
隔离性的问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表数据发生了改变
虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
代码实现
-
开启事务 conn.setAutoCommit(false);
-
一组业务执行完毕,提交事务
-
可以在catch语句中显示的定义回滚语句,但默认失败就会回滚
1 package com.study.day03; 2 3 import com.study.day02.utils.JdbcUtils; 4 5 import java.sql.Connection; 6 import java.sql.PreparedStatement; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 10 public class TestTransaction2 { 11 public static void main(String[] args) { 12 Connection conn = null; 13 PreparedStatement st = null; 14 ResultSet rs = null; 15 try { 16 conn = JdbcUtils.getConnection(); 17 conn.setAutoCommit(false); 18 String sql1 = "update account set money = money-100 where `NAME`='A'"; 19 st = conn.prepareStatement(sql1); 20 st.executeUpdate(); 21 int i = 1/0; //报错,转账过程出现错误,观察是否会回滚 22 String sql2 = "update account set money = money+100 where `NAME`='B'"; 23 st = conn.prepareStatement(sql2); 24 st.executeUpdate(); 25 26 //业务完毕,提交事务 27 conn.commit(); 28 System.out.println("转账成功!"); 29 st.executeUpdate(); 30 } catch (SQLException e) { 31 //其实不写也会默认回滚 32 try { 33 conn.rollback();//如果失败,就回滚事务 34 } catch (SQLException ex) { 35 ex.printStackTrace(); 36 } 37 e.printStackTrace(); 38 }finally { 39 JdbcUtils.release(conn,st,rs); 40 } 41 } 42 }
数据库连接池
数据库连接->执行完毕-->释放
连接--释放 十分浪费系统资源
池化技术:准备一些预先的资源,过来就是连接预先准备好的
最小连接数:10
最大连接数:15
等待超时:100ms
编写连接池,实现一个接口DataSource
开源数据源实现
DBCP
1 package com.study.day04; 2 3 import com.study.day02.utils.JdbcUtils; 4 import com.study.day04.utils.JdbcUtils_dbcp; 5 6 import java.sql.Connection; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 import java.sql.Statement; 10 11 public class TestDbcp { 12 public static void main(String[] args) { 13 Connection conn = null; 14 Statement st = null; 15 ResultSet rs = null; 16 try { 17 conn = JdbcUtils_dbcp.getConnection();//获取数据库连接 18 st = conn.createStatement();//获取SQL的执行对象 19 String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)" + 20 "VALUES(4,'kuangshen','123456','2784234567@qq.com','2022-01-01')"; 21 int i = st.executeUpdate(sql); 22 if(i>0){ 23 System.out.println("插入成功!"); 24 } 25 } catch (SQLException e) { 26 e.printStackTrace(); 27 }finally { 28 JdbcUtils_dbcp.release(conn,st,rs); 29 } 30 31 32 } 33 }
C3P0
1 package com.study.day04; 2 3 import com.study.day04.utils.JdbcUtils_C3p0; 4 import com.study.day04.utils.JdbcUtils_dbcp; 5 6 import java.sql.Connection; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 import java.sql.Statement; 10 11 public class TestC3p0 { 12 public static void main(String[] args) { 13 Connection conn = null; 14 Statement st = null; 15 ResultSet rs = null; 16 try { 17 conn = JdbcUtils_C3p0.getConnection();//获取数据库连接 18 st = conn.createStatement();//获取SQL的执行对象 19 String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)" + 20 "VALUES(5,'bajie','123456','2784234567@qq.com','2022-01-01')"; 21 int i = st.executeUpdate(sql); 22 if(i>0){ 23 System.out.println("插入成功!"); 24 } 25 } catch (SQLException e) { 26 e.printStackTrace(); 27 }finally { 28 JdbcUtils_C3p0.release(conn,st,rs); 29 } 30 31 32 } 33 }
Druid:阿里巴巴
使用了连接数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了。
结论
-