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接口不会变,方法就不会变