本文章大致需要一个小时游览
1、初识MySQL
JavaEE: 企业级Java开发 web
前端(页面:展示,数据!)
后台(连接点:连接数据库JDBC,连接前端(控制, 控制视图跳转,和前端传递数据))
数据库(存数据,Txt,Excle,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://www.mysql.com
5.7稳
8.0最新
安装建议:
1、尽量不要使用exe,
2、尽可能使用压缩包安装~
1.5、安装MySQL
教程:https://www.cnblogs.com/hellokuangshen/p/10242958.html
1解压
2把这个包放到自己的电脑环境目录下~
3配置环境变量
4新建mysql配置文件 ini
[mysqld]
basedir=D:\MySQL\mysql-5.7.19\
datadir=D:\MySQL\mysql-5.7.19\data\
port=3306
skip-grant-tables
5启动管理员模式下的CMD,运行所有的命令
6安装mysql服务
7初始化数据库文件
8启动mysql,进去修改密码
9进入mysql通过命令行(-p后面不要加空格)(sql语句一定要加分号!)
10主掉ini中的跳过密码
11重启mysql。连接测试 如果连接成功后就OK了!
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JFwhSaQ2-1592795159068)(C:\Users\邢驰\AppData\Roaming\Typora\typora-user-images\image-20200224171725978.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8EFLOpmO-1592795159071)(C:\Users\邢驰\AppData\Roaming\Typora\typora-user-images\image-20200224171813328.png)]
停止 net stop mysql
启动 net start mysql
mysql -u root -p 进入mysql管理界面
1.7、连接数据库
命令行连接
updata mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost'; --修改用户密码
flush privileges; --刷新权限
----------------------------------------
所有的语句都使用 ; 结尾
show databases; --查看所有的数据库
mysql> use school --切换数据库 use 数据库名
Database changed
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表的信息
create database westos; --创建应该数据库
exit; --退出连接
--单行注释(sql 本来的注释)
/*
(sql 的多行注释)
*/
数据库xxx语言 CRUD 增删改查! CV程序猿 API程序猿 CRUD程序猿!(业务)
DQL 查询:查询语句 凡是 select语句都是DQL
DML 操作: insert delete update 对表当中的数据进行增删改
DDL 定义: create drop alter 对表结构的增删改
DCL 控制:grant授权, revoke回滚事务。
TCL 事务控制语言: commit 提交事务,, rollback 回滚事务。
2、操作数据库
操作数据库>操作数据库中的表>操作数据库中的表的数据
- mysql 关键字不区分大小写
2.1 、操作数据库(了解)
1、创建数据库
CREATE DATABASE [IF NOT EXISTS] WESTOS;
2、删除数据库
DROP DATABASE [IF EXISTS] WESTOS
3、使用数据库
-- tab 键的上面, 如果 你的表名或者字段 是应该特殊字符,就需要 ``
use `school`;
4、查询数据库
SHOW DATABASES --查看所有的数据库
学习思路:
- 固定的语法或关键字必须强行记住!!
2.2、数据库的数据类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节 常用的
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算的时候 一般是用decimal
字符串
- char 字符串固定大小 0~255
- varchar 可变字符串 0~65535 常用的 String
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1 保存大文本
时间日期
java.util.Date
- data YYYY-MM-DD,日期
- time HH:mm :ss 时间格式 时分秒
- datatime YYYY-MM-DD HH:mm :ss 最常用的时间格式
- timestamp 时间戳, 1970.1.1到现在的毫秒数!!! 也较为常用
- year 年份表示
null
- 没有值, 未知
- 注意,不要使用NULL进行运算,结果为NULL
2.3、数据库的字段属性(重点)
Unsigned
- 无符号的整数
- 声明了该列不能声明为负数
zerofill
- 0填充的
- 不足的位数,使用0来填充, int(3), 5 — 005
自增:
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设置唯一的主键~~ index , 必须是整数类型
- 可以通过自定义设计主键自增的七十至和步长
非空NUII not null
- 假设设置为 not null, 如果不给它赋值, 就会报错
- NUII,如果不填写值, 默认就是null.
默认
- 设置默认的值!
- sex,默认值为男,如果不指定该列的值,则会有默认的值!
拓展:听听
/*每一个表,都必须存在一下五个字段!! 未来做项目用的,表示应该记录存在意义!!!
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_updata 修改时间
*/
2.4、创建数据库表(重点)
--目标:创建一个school数据库
--创建学生表(列字段) 使用sql创建
--学号 int 登陆密码 varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
--注意点, 使用英文 (), 表的名称 和字段 尽量使用 `` 括起来
--auto_increment 自增
--字符串使用, 单引号括起来
--所有的语句后面加,(英文的), 最后一个不用加
-- 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 '家庭地址',
primary key(`id`)
)engine=innodb default charset=utf8;
格式
1 create table [if not exists] 表明(
'字段名' 列类型 [属性] [索引] [注释],
'字段名' 列类型 [属性] [索引] [注释],
..........
'字段名' 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]
常用命令
show create database school --查看创建数据库的语句
show create table student --查看student数据表的定义语句
desc student --显示表的结构
2.5、数据表的类型
--关于数据库引擎
/*
innodb 默认使用
myisam 早些年使用的
*/
myisam | innodb | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 表锁 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大 约为 2倍 |
常规的使用操作:
- myisam 节约空间, 速度较快
- innodb 安全性高, 事务的处理 ,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下, 一个文件夹就对应一个数据库
本质还是文件的存储!
MySQL 引擎在物理文件上的区别
- innodb 在数据库表中只有一个 *.frm文件,以及上级目录下的ibdata1文件
- myisam 对应文件
- *.frm -表结构的定义文件
- *.myd 数据文件(data)
- *.myi
设置数据库表的字符集编
charset=utf8
不设置的话,会是mysql默认的字符集编码~~ 不支持中文
MySQL 的默认编码是Latin1,不支持中文
在my.ini中配置默认的编码
character-set-server=utf8
2.6、修改删除表
修改
-- 修改表名: ALTER table 旧表名 rename as 新表名
ALTER TABLE teacher1 RENAME AS teacher
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher ADD age INT ( 11 )
-- 修改表的字段 (重命名, 修改约束!)
--ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher MODIFY age VARCHAR ( 11 ) -- 修改约束
--ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher CHANGE age age1 INT ( 1 ) -- 字段重命名
-- 删除 表的字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher DROP NAME
删除
-- 删除表 (如果 表存在 在删除)
DROP TABLE IF EXISTS teacher
所有的创建和删除操作 尽量加上判断,以免报错
注意点:
- `` 字段名使用这个包裹!!
- 注释 – /* */
- sql 关键字 大小写 不敏感, 建议写小写
- 所有的符号 全部用英文!!!
3、MySQL数据管理
3.1、外键(了解即可)
方式一, 在创建表的时候,增加约束(麻烦,比较复杂)
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
-- 给这个外键 添加约束 (执行引用) references 引用
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 '出生日期',
`gradeid` int(10) not null comment '学生得年纪',
`address` varchar(100) default null comment '家庭地址',
primary key(`id`),
key `KF_gradeid`(`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)engine=innodb 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
-- 给这个外键 添加约束 (执行引用) references 引用
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 '出生日期',
`gradeid` int(10) not null comment '学生得年纪',
`address` varchar(100) 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 那个表(那个字段)
以上的操作都是物理外键 ,数据库级别的外键 ,不建议使用!!(避免数据库过多造成困扰, 这里了解即可)
**最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
3.2、DML语言(全部记住)
**数据库 意义:**数据存储, 数据管理
DML语言:数据操作语言
- insert
- updata
- delete
3.3、添加
insert
-- 插入语句(添加)
-- insert into 表名([字段1,字段2 ,字段3 ]) values ('值1'),('值2'),('值3',)
insert into `grade` (`gradename`) values ('大四')
-- 由于主键自增 我们可以省略,(如果不写表的字段, 就会一一匹配)
insert into `grade` values '大三'
-- 一般写插入语句, 我们一定要数据和字段一一对应。
insert into `student`(name,sex) values ('张三','男')
-- 插入多个字段
insert into `grade`(gradename) values ('大二'),('大一')
insert into `student`(`name`,`pwd`,`sex`)
values ('李四','123324221','男'),
('王五','22422343','女')
insert into `student`
values ('7','星辰','200203','男','2000-04-05','西安')
语法:
insert into 表名(字段1,字段2)values ('值1'),('值2),(值3)
注意事项:
- sql里面符号全为英文
- 字段可以省略,但是后面的值必须一一对应
- 可以同时插入多条数据,values后面的值,需要使用,隔开 values(),(),…
3.4、修改
update 修改值(条件) set原来的值=新值
-- 修改学员名字
update `student` set `name`='星辰' where id=1
-- 不指定条件的情况下, 会改动所有表
update `student` set `name`='帅哥'
-- 修改多个属性
update `student` set `name`='星辰', `birthday`='2000-04-05' where id=1
--语法
-- update 表名 set colnum_name =value [where]
条件:where 子句 运算符 id等于某个值,大于某个值,在某个区间 修改…
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<> 或 != | 不等于 | 5<>6 | true |
> | 大于 | 5>6 | false |
< | 小于 | 5<6 | false |
>= | 大于等于 | 5>=6 | false |
<= | 小于等于 | 5<=6 | true |
between… and… | 在某个范围内 | [2,5] | |
and | 我和你 && | 5>6and 6>5 | false |
or | 我或你|| | 5>6or 6>5 | true |
-- 通过多个属性定位操作
update `student` set `name`='星辰' where `name`='星辰2' and `sex`='女'
语法:update 表名 set colnum_name =value, where[条件]
注意:
- colnum_name 是数据库的列,尽量带上 ``
- 条件, 筛选条件,如果没有指定,则会修改所有的列
- value,是一个具体的值,也可以是一个变量。
update `student` set `birthday`=CURRENT_DATE where `name`='星辰' and `sex`='女'
3.5、删除
delete
语法: delete from 表名 [where 条件]
-- 删除数据 (避免这样写,会全部删除)
delete from `student`
-- 删除指定数据
delete from `student` where id=6;
truncate
作用:完全清空一个数据库表,表的结构和索引约束不会变!!!
-- 清空数据
truncate `student`
delete 和truncate 区别
- 相同点:都能删除数据,都不会删除表结构
- 不同:
- truncate 重新设置自增列 计数器会归零
- truncate 不会影响事务
-- 测试delete 和 truncate 区别
create table if not EXISTS`test`(
`id` int(4) not null auto_increment,
`coll` varchar(20) not null,
PRIMARY key (`id`)
)ENGINE=innodb default charset=utf8
insert into `test` (`coll`) values('1234'),('2'),('22')
delete from `test` -- 不会影响自增
truncate table `test` -- 自增会归零
了解: delete删除的问题,重启数据库,现象s
- innodb 自增列会重1开始 (存在内存当中,断电即失)
- mylsam 继续从上一个自增量开始(存在文件中的,不会丢失)
4、DQL 查询数据(超级重点! )
4.1、DQL
(Data Query Language:数据查询语言)
-
所有的查询操作都用它 Select
-
简单的查询,复杂的查询都能做
-
数据库中最核心的语言,最重要的语句
-
使用频率最高的语句
SELECT完整的语法:
SELECT语法
select [all | distinct]
{* | table | [table.field1[as aliasl][,table.field2[as alias2]][......]]}
from table_name [as table_alias]
[left | right | inner join table name2] -- 联合查询
[where ...] --指定结果需满足的条件
[group by ...] --指定结果按照那几个字段来分组
[having ] -- 过滤分组的记录必须满足的次要条件
[order by...] -- 指定查询记录按一个或多个条件排序
[limit {[offset,]row_count| row_countoffset offset}];
-- 指定查询的记录从那条到那条
注意:[]括号代表是可选的,{}括号代表是必选的
4.2、指定查询字段
-- 查询全部的学生信息 select 字段 from 表
select *from `student`
-- 查询全部的学生信息 select 字段 from 表
select *from grade
-- 查询指定字段
select `id`, `name` from student
-- 别名 ,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
select `id` AS 学号, `name` AS 学生姓名 from student
-- 函数 Concat(a,b)
select CONCAT('姓名:',name) AS 新名字 from student
语法 : select 字段, … from 表
有的时候,列名字不是那么见名知意。 我们起别名 AS 字段名 AS 别名 表名 AS 别名
去重 distinct
作用:去除select查询出来得结果中重复得数据,重复得数据只显示一条
-- 查询一下那些同学参加了考试 成绩
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 `id` , `score`+1 AS '提分后' from student
数据库中的表达式:文本值,列,Null ,函数,计算表达式,系统变量…
select表达式 from 表
4.3、where条件子句
作用:检索数据中 符合条件的值
搜索的条件由一个或者多个表达式组成!结果 布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与 |
or || | a or b a||b | 逻辑或 |
not ! | not a ! a | 逻辑非 |
尽量使用英文字母
-- ====================where==================
select id, score from student
-- 查询考试成绩在 95~100分之间
select id, score from student
where score>=95 and score<=100
-- and &&
select id, score from student
where score>=95 && score<=100set
-- 模糊查询 (区间)
select id, score from student
where score BETWEEN 95 and 100
-- 除了 id等于1 之外的
select id, score from student
where id !=1
-- != not
select id, score from student
where not id=1;
模糊查询: 比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,则结果为真 |
is not null | a is not null | 如果操作符为 not null ,则结果为真 |
between | a between b and c | 若 a 在b 和c之间, 则结果为真 |
like | a like b | SQL 匹配,弱国a匹配b ,则结果 为真 |
ln | a in(a1,a2,a3…) | 假设a在 a1 或a2 或者…其中某一个值,结果为真 |
-- ================模糊查询==================
-- 查询 查询姓星的同学
select `id` ,`name` from student
where name like '星%'
-- 查询 查询姓星的同学名字后面只有一个字的
select `id` ,`name` from student
where name like '星_'
-- 查询 查询姓丁的同学名字后面只有两个字的
select `id` ,`name` from student
where name like '丁__'
-- 查询名字中有佳字的同学 %佳%
select `id` ,`name` from student
where name like '%佳%'
-- ===========in (具体的一个或者多个值)============
-- 查询 4 5 6 号学员
select `id` ,`name` from student
where id in(4,5, 6);
-- 查询在 北京的学生
select `id` ,`name` from student
where `address` in('北京','西安');
-- ===========null not null ===========
-- 查询没有出生日期的学生 null ''
select `id` ,`name` from student
where `score`='80' or birthday is null
-- 查询有出生日期的同学 不为空
select `id` ,`name` from student
where `birthday` is not null
4.4、联表查询
JOIN 对比
操作 | 描述 |
---|---|
inner join | 如果表中至少存在有一个匹配,就返回行 |
left join | 会从左表中返回所以的值,即使右表中没有匹配 |
right join | 会从右表中返回所以的值,即使左表中没有匹配 |
-- ==============联表查询===============
-- 查询 参加了考试的同学 (学号,姓名,科目编号,分数)
select *from student
select * from result
/* 思路
1.分析需求, 分析查询的字段来自那些表,(连接查询)
2.确定使用那种连接查询?? 7种
确定交叉点(这两个表中那个数据是相同的)
判断的条件: 学生表中的s id =成绩表中的id
*/
-- join(连接的表) on(判断的条件) 连接查询
-- where 等值查询
-- inner join
select studentNo,score,studentName, id
from student s
inner join result r
on s.id=r.studentNo
-- right join
select studentNo,score,studentName, id
from student s
RIGHT JOIN result r
on s.id=r.studentNo
-- left join
select studentNo,score,studentName, id
from student s
left JOIN result r
on s.id=r.studentNo
-- 查询 缺考的同学
select studentNo,score,studentName, id
from student s
left JOIN result r
on s.id=r.studentNo
where score is null
/* 思路
1.分析需求, 分析查询的字段来自那些表, student result (连接查询)
2.确定使用那种连接查询?? 7种
确定交叉点(这两个表中那个数据是相同的)
判断的条件: 学生表中的s id =成绩表中的id
*/
select studentNo, studentName,id,score,pwd
from student s
right join result r
on r.studentNo=s.id
inner join `grade` g
on r.studentNo=g.gradeid
-- 思路
-- 我要查询那些数据 select....
-- 从那几张表中查 from 表 XXX join 连接的表 on 交叉条件
-- 假设存在多张表查询,慢慢来,先查询两张表然后慢慢增加
-- from a LEFT JOIN b
-- from a right JOIN b
自连接(了解)
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
父类
category | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryName |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 查询父子信息 :把一张表看成两张一模一样的表
select a.`categoryName` '父栏目', b.`categoryName` as '子栏目'
from `category` as a, `category` as b
where a.`categoryid`=b.`pid`
4.5、分页和排序
排序
-- ======================分页 limit 和排序 order by ======================
-- 排序: 升序 ASC , 降序DESC
-- ORDER BY 通过那个字段排序,怎么排?
-- 查询的结果 根据成绩排序
select `name` ,`studentName`,`id`, `pid`
from student s
inner join `result` r
on s.`name`=r.`studentName`
inner join `category` c
on s.id=c. `pid`
ORDER BY pid desc
分页
-- 为什么要分页
-- 缓解数据库压力,给人的体验更好,瀑布流
-- 分页,每页显示3条数据
-- 语法 limit 起始页,页面的大小
-- limit 0,4
-- limit 1,4
select `name` ,`studentName`,`id`, `pid`
from student s
inner join `result` r
on s.`name`=r.`studentName`
inner join `category` c
on s.id=c. `pid`
ORDER BY pid desc
limit 0,4
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第n 页 limit n-1,5 (n-1)*pageSize, pageSize
-- 【pageSize: 页面大小】
-- 【(n-1)* pageSize起始值】
-- 【 n当前页 】
-- 【数据总数/页面大小 =总页数】
4.6、子查询
where (值时固定的,这个值是计算出来的)
本质: 在where语句中嵌套一个子查询语句
where(select*from)
-- 查询 课程成绩从高到底 ,并且分数大于70的学生信息(学号 成绩 姓名 )
SELECT id ,score ,studentName
from student s
inner JOIN result r
on s.id= r.studentNo
INNER JOIN category c
on c.categoryid=s.id
where score>=70
ORDER BY score desc
limit 0,6
-- ===============where==============
-- 查询 姓名 为星辰 所有的考试结果 (学号 成绩)降序排列
-- 方式一:使用连接查询
select `id`, `score` , `studentNo`
from student s
INNER JOIN result r
on s.id=r.studentNo
where studentName ='星辰'
order by score desc
-- 方式二 使用子查询(由里即外)
select `studentNo`,`id` ,`score`
from result r
INNER JOIN student s
on s.id=r.studentNo
where id=(
-- 查询 所有数据库结构
SELECT id from student ss
where ss.name='云龙'
)
ORDER BY score desc
-- 分数不少于80分 的学生的学号和姓名
SELECT DISTINCT id , `name`
from student s
inner join result r
on r.studentNo=s.id
where score >=80 and `categoryid`=(
SELECT categoryid from category
where categoryName='数据库'
)
4.7、分组和过滤
-- 查询不同课程的平均分 最高分 最低分
-- 核心 (根据不同的课程分组)
select categoryid, avg (score) as 平均分, max(score),min(score),
from result r
INNER JOIN category c
on r.studentNo=s.id
GROUP BY r.studentNo -- 通过什么字段分组
HAVING 平均分>80
。。。。。。。。。。。。。有错误 待完善
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 CEILING(9.4) -- 向上取整
SELECT FLOOR(9.9) -- 向下取整
SELECT rand() -- 返回一个0~1之间的随机数
SELECT SIGN(-5) -- 判断一个数的符号 负数返回-1 正数返回1
-- 字符串函数
select CHAR_LENGTH('即使在小的帆也能远航') -- 字符串长度
select CONCAT('我','爱','你们' ) -- 字符串拼接
select insert('我爱编程helloworld',1,2,'超级热爱') -- 查询,替换 从某个位置替换某个长度
select LOWER('XingChen') -- 转换为小写
SELECT UPPER('XingChen') -- 转换为大写
select INSTR('xingchen','c')
select REPLACE('星辰说坚持就能成功','坚持','努力')
select substr('星辰说坚持就能成功',4,6) -- 返回指定的字符串 起始位置 截取长度
select REVERSE('星辰说坚持就能成功') -- 反转字符
-- 查询姓7星的同学 名字 改为 行
select REPLACE(name,'星','行') from student
where name like '星%'
-- 时间和日期函数 (记住)
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(name) from student -- count (字段), 会忽略所有的null
select count(*) from student -- count(*) 不会忽略null值 本质 计算行数
select count(1) from student -- count(1) 不会忽略null值 本质 计算行数
select sum(`score`) as 总和 from result
select avg(`score`) as 平均数 from result
select MAX(`score`) as 最高分 from result
select min(`score`) as 最低分 from result
5.3、数据库级别的MD5 加密(扩展)
什么是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 default charset=utf8
-- 明文密码
insert into `testmd5` VALUES
(1,'zhangsan' , '123456'),
(2,'lisi' , '123456'),
(3,'wangwu' , '123456'),
(4,'xigchen' , '123456')
-- 加密
UPDATE `testmd5` set pwd= MD5(pwd) where id=1
UPDATE `testmd5` set pwd= MD5(pwd) where id!=1 -- 加密了全部的密码
-- 插入的时候 加密 不应该 后来加密
insert into testmd5 VALUES (5,'xiaoming ',MD5('123456'))
-- 如何校验: 将用户传递过来的密码进行 MD5 加密, 然后比对加密后的值
select * from testmd5 where `name`='xiaoming' and pwd=MD5('123456')
6、事务
要么都成功,要么都失败
1、SQL执行 A给B转账 A 1000 ----->B 200
2、SQL执行 B收到A的钱 A 800----->B 400
将一组SQL放在一个批次中执行~~
事务 原则 : ACID 原则 原子性 ,一致性, 隔离性 , 持久性 (脏读, 幻读…)
参考链接 https://blog.csdn.net/dengjili/article/details/82468576
1、原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
2、一致性(Consistency)
事务前后数据的完整性必须保持一致。
3、隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
4、持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
隔离所导致的一些问题
脏读:
指一个事务读取另外一个事务未提交的数据。
不可重复读
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
页面统计查询值
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
(一般是行影响,多了一行)
执行事务
-- ================事务======================
-- MySQL 默认开启事务
set autocommit =0 -- 关闭
set autocommit =1 -- 开启(默认的值)
-- 手动处理事务
set autocommit =0 -- 关闭自动提交
-- 事务开启
start TRANSACTION -- 标记一个事务的开始, 从这个之后的SQL 都在同一个事务内
-- 提交 :持久化 (成功! )
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`)
)
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 -- 开启 恢复(默认的值)
7、索引
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。 0.5s 0.00001s
提取句子主干,就可以得到索引的本质:索引是一种数据结构。
7.1、索引的分类
在一个表中 主键索引只能有一个,唯一索引可以有多个
-
主键索引: (PRIMARY KEY)
- 唯一标识,主键不可重复,只能有一个列作为主键
-
唯一索引: (unique KEY)
- 避免重复的列出现,可以重复 ,多个列都可以标识为唯一索引
- 常规索引: (KEY/INDEX)
- 默认的, index , key 关键字来设置
-
全文索引: (FullText)
- 在特定的数据库引擎下才有,MyISAM
- 快速定位数据
基础语法
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引
-- 显示所有的索引信息
show index from student
-- 增加一个索引
alter table school.student ADD FullText index `name`(`name`);
-- explain 分析SQL执行的状况
explain SELECT * from student; -- 非全文索引
explain SELECT * from student where match (name) against('丁'); -- 全文索引
7.2、测试索引
-- 插入100万数据
create table `app_user`(
`id` BIGINT(20) unsigned not null auto_increment,
`name` varchar(50) default '' comment '用户昵称',
`email` varchar (50) not null comment '用户邮箱',
`phone` varchar(20) default '' comment '手机号',
`gender` tinyint (4) UNSIGNED default '0' comment '性别 (0:男; 1女; )',
`password` varchar (100) not null comment '密码',
`age` tinyint (4) UNSIGNED default '0' comment '年龄',
`create_time` datetime default CURRENT_TIMESTAMP ,
`update_time` TIMESTAMP null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
primary key (`id`)
)engine=innodb default charset=utf8mb4 comment='app用户表'
delimliter $$ -- 写函数之前必须要写,标志
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),'2225902041@qq.com'
,concat('18',FLOOR(rand()*((999999999-100000000)+1000000)))
,FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
set i=i+1;
end while ;
return i;
end;
select mock_data();
select * from app_user
select * from app_user where `name`='用户9999'-- 1.083s
select * from app_user where `name`='用户9999'-- 1.001s
select * from app_user where `name`='用户9999'-- 0.99s
EXPLAIN select * from app_user where `name`='用户9999'
-- id_表明_字段名
-- create index 索引名 on 表 (字段)
create index id_app_user_name on app_user(`name`);
select * from app_user where `name`='用户9999'-- 0.002s
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6rtdoX0h-1592795159075)(C:\Users\邢驰\AppData\Roaming\Typora\typora-user-images\image-20200530141009614.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gNC7hwox-1592795159077)(C:\Users\邢驰\AppData\Roaming\Typora\typora-user-images\image-20200530141148029.png)]
索引在小数据量的时候,用处不大,但是在大数据时候,区别十分明显~~~
7.3、索引原则
-
索引不是越多越好
-
不要对经常变动的数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上!
索引的数据结构
Hash类型的索引
Btree:innoDB的默认数据结构~~
阅读:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
8、权限管理和数据库备份
8.1、用户管理
可视化管理
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xW8wgbLr-1592795159079)(C:\Users\邢驰\AppData\Roaming\Typora\typora-user-images\image-20200530144127760.png)]
SQL 命令操作
用户表:MySQL.uesr
本质:对这张表进行增删改查
-- 创建用户 create user 用户名 IDENTIFIED BY '密码'
create user xingchen1 IDENTIFIED BY '123456'
-- 修改密码 (修改当前用户密码)
set password =password('123456')
-- 修改密码 (修改指定用户密码)
set password for xingchen =password('123456')
-- 重命名 rename user 原名字 to 新的名字
rename user xingchen2 to xingchen
-- 用户授权 all PRIVILEGES 全部的权限, 库.表
-- all PRIVILEGES 除了给别人授权 其他都能干
GRANT all PRIVILEGES on *.* to xingchen
-- 查看权限
show GRANTS FOR xingchen -- 查看指定用户的权限
-- root 用户的权限 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
show GRANTS for root@localhost
-- 撤销权限 revoke 那些权限 在那个库撤销 , 给谁撤销
revoke all PRIVILEGES on *.* from xingchen
-- 删除用户
drop user xingchen1
8.2、MySQL备份
为什么要备份:
- 保证重要的数据不丢失
- 数据转移 A------>B
MySQL数据库备份的方式
- 直接拷贝物理文件
- 在navicat 这种可视化工具中 手动导出
- 在想要导出的表或者库中 ,右键 ,选择 转储SQL
- 使用命令行导出 mysqldump 命令行使用
# mysqldump -h 主机 -u用户名 -p 密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# mysqldump -h 主机 -u用户名 -p 密码 数据库 表1 表2 表3 ... >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/b.sql
# mysqldump -h 主机 -u用户名 -p 密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql
#导入
#登录的情况下,切换到指定的数据库
#source 备份文件
source d:/a.sql
mysql -u 用户名 -p密码 库名<备份文件
假设你要备份数据库,防止数据丢失。
把数据库给朋友 ! sql文件给别人即可!
9、规范数据库设计
9.1、为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
-
节省内存空间
-
保证数据库的完整性
-
方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图E-R图
设计数据库的步骤:(个人博格)
-
收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 评论表
- 友链表(友链信息)
- 自定义表(系统信息,某个关键的字,或者一些主字段) key:value
- 说说表(发表心情…id… content… create_time)
-
标识实体(把需求落地到每个字段)
-
标识实体之间的关系
- 写博客:user—>blog
- 创建分类:user—>category
- 关注:user—>user
- 友链:links
- 评论表:user—>user–>blog
9.2、三大范式
参考链接:https://www.cnblogs.com/wsg25/p/9615100.html
为什么需求数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式(了解)
第一范式(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 TABLE `websites` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
`url` varchar(255) NOT NULL DEFAULT '',
`alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
`country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO `websites` VALUES
('1', 'Google', 'https://www.google.cm/', '1', 'USA'),
('2', '淘宝', 'https://www.taobao.com/', '13', 'CN'),
('3', '菜鸟教程', 'http://www.runoob.com', '5892', ''),
('4', '微博', 'http://weibo.com/', '20', 'CN'),
('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');
1、创建一个普通项目
2、导入数据库驱动
3、编写测试代码
package com.xing.lesson01;
import java.sql.*;
//我的第一个Jdbc程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver"); //固定写法,加载驱动
//2、用户信息和url
// useUnicode=true&characterEncoding=utf8&useSSL=true
String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username="root";
String password="123456";
//3、连接成功,数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4、执行sql的对象 Statement执行sql的对象
Statement statement = connection.createStatement();
//5、执行sql的对象去执行sql, 可能存在结果,查看返回结果
String sql ="select * from websites";
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("url="+resultSet.getObject("url"));
System.out.println("alexa="+resultSet.getObject("alexa"));
System.out.println("country="+resultSet.getObject("country"));
System.out.println("===================");
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
1、加载驱动
2、连接数据库 DriverManager
3、获得执行sql的对象 Statement
4、获得返回的结果集
5、释放连接
DriverManager
Class.forName("com.mysql.jdbc.Driver"); //固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.commit();
connection.rollback();
connection.setAutoCommit();
URL
String url="jdbc:mysql://localhost:3306/school?use
Unicode=true&characterEncoding=utf8&useSSL=true";
//mysql:3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
//oracle --1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement 执行sql的类 prepareStatement 执行sql的类
String sql ="select * from websites"; //编写sql
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); //移动到指定行
释放资源
//6、释放连接
resultSet.close();
statement.close();
connection.close(); //耗资源,用完关掉!!!
10.4、statement对象
代码实现
1、提取工具类
package com.xing.lesson03.utils;
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 (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、编写增删改的方法, executeUpdate
package com.xing.lesson03;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = jdbcUtils.getConnection(); //获取数据库连接
st=conn.createStatement(); //获得sql的执行对象
String sql="insert into users (id,`name`,`password`,email,`birthday`)" +
"values(6,'xingchen','123455','2225902041@qq.com','2020-01-01')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
package com.xing.lesson03;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = jdbcUtils.getConnection(); //获取数据库连接
st=conn.createStatement(); //获得sql的执行对象
String sql="delete from users where id=6;";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
package com.xing.lesson03;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpDate {
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = jdbcUtils.getConnection(); //获取数据库连接
st=conn.createStatement(); //获得sql的执行对象
String sql="update users set name='xingchen',email='2225902041@qq.com' where id=3";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("更新成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
3、查询 executeQuery
package com.xing.lesson03;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = jdbcUtils.getConnection();
st = conn.createStatement();
//sql
String sql="select * from users where id=3";
rs = st.executeQuery(sql); //查询完毕 会返回一个结果集
while (rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
sql注入的问题
sql存在漏洞,会被攻击导致数据泄露, == SQL会被拼接 ==
package com.xing.lesson03;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) {
//login("xingchen","123456");
login("'or' 0=0","'or' 1=1"); //技巧
}
//登录业务
public static void login(String username,String password){
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = jdbcUtils.getConnection();
st=conn.createStatement();
//SQL select * from users where `name`='xingchen' and password='123456';
//SQL select * from users where `name`='' or' 1=1' and password='' or '1=1';
String sql="select * from users where `name`='"+username+"' and password='"+password+"'" ;
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("=================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
10.5、preparedStatement对象
preparedStatement可以防止SQL注入。效率更好!!
1、新增
package com.xing.lesson04;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
//ResultSet rs=null;
try {
conn = jdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "insert into users (id,`name`,`password`,email,`birthday`)values(?,?,?,?,?)";
st = conn.prepareStatement(sql); //预编译SQL,先写SQL,然后不执行
//手动给参数赋值
st.setInt(1,6);
st.setString(2,"xingchi");
st.setString(3,"123232");
st.setString(4,"222590@qq.com");
//注意点 :sql.Date 数据库 java.sql.Date
// util.Date java new Date().getTime() 获得时间戳
st.setDate(5, new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,null);
}
}
}
2、删除
package com.xing.lesson04;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
try {
conn=jdbcUtils.getConnection();
String sql="delete from users where id=?";
st=conn.prepareStatement(sql);
//手动给参数赋值
st.setInt(1,6);
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,null);
}
}
}
3、更新
package com.xing.lesson04;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
try {
conn=jdbcUtils.getConnection();
String sql="update users set `name` =? where id=?;";
st=conn.prepareStatement(sql);
//手动给参数赋值
st.setString(1,"星辰");
st.setInt(2,1);
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,null);
}
}
}
4、查询
package com.xing.lesson04;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn=jdbcUtils.getConnection();
String sql="select * from users where id=?"; //编写SQL
st=conn.prepareStatement(sql); //预编译
st.setInt(1,1); //传递参数
rs=st.executeQuery(); //执行
if(rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
5、防止SQL注入
package com.xing.lesson04;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.*;
public class SQL注入 {
public static void main(String[] args) {
//login("xingchen","123456");
login("'' or 1=1","123456"); //技巧
}
//登录业务
public static void login(String username,String password){
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn = jdbcUtils.getConnection();
//PreparedStatement 防止SQL注入的本质,把传递过来的参数当作字符
//假设其中存在转义字符,就直接忽略, 比如说 ' 会被直接转义
String sql="select * from users where `name`=? and `password`=? "; // Mybatis
st=conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();
while(rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("=================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
10.6、使用IDEA连接数据库
连接成功后,可以选择数据库
双击数据库
更新数据
连接失败,查看原因
10.7、事务
要么都成功,要么都失败
ACID原则
原子性:要么全部完成,要么都不完成
一致性:总数不变
隔离性:多个进程,互不干扰
持久性:一旦提交不可逆,持久化到数据库了
隔离性的问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表发生了改变
幻读:(虚读):在一个事务内,读取到了别人插入的数据,导致前后读取的结果不一致。
代码实现
1、开启事务:conn.setAutoCommit(false); //开启事务
2、一组业务执行完毕,提交事务
3、可以在catch语句中显式定义 回滚语句, 但默认失败就会回滚。
package com.xing.lesson05;
import com.xing.lesson03.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn=jdbcUtils.getConnection();
//关闭数据库自动提交功能,自动会开启
conn.setAutoCommit(false); //开启事务
String sql1="update account set money =money-100 where name ='A'";
st=conn.prepareStatement(sql1);
st. executeUpdate();
String sql2="update account set money =money+100 where name ='B'";
st=conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕,提交事务
conn.commit();
System.out.println("操作成功");
} catch (SQLException e) {
try {
conn.rollback(); //如果失败则回滚事务
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
10.8、数据库连接池
数据库连接—执行完毕----释放
连接–释放 十分浪费系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
常用连接数 10个
最小连接数 10
最大连接数 15
等待超时:100ms
编写连接池,实现一个接口 DataSource
开源数据源实现 (拿来即用)
DBCP
C3P0
Druid: 阿里巴巴
使用了这些数据库连接池之后,我们在项目开发中就不需要在编写连接数据库代码了
DBCP
需要用到的jar包
commons-pool-1.6.jar
commons-dbcp-1.4.jar
C3P0
需要用到的jar包
c3p0-0.9.5.5
mchange-commons-java-0.2.19
结论
无论使用什么数据源,本质还是一样的,DataSource接口不会变,方法就不会变
Druid
Apache 网站