MYSQL

Pfst of cict

已于 2022-07-29 17:03:39 修改

阅读量413

点赞数

文章标签： mysql 数据库服务器

于 2022-07-29 17:00:14 首次发布

本文链接：https://blog.csdn.net/weixin_48195998/article/details/126059536

版权

在这里插入图片描述

配置环境

配置环境变量
新建配置文件my.ini

[mysqld]
basedir=D:\mysql-5.7.29-winx64\  //注意填写自己数据库安装的位置
datadir=D:\mysql-5.7.29-winx64\data\
port=3306
skip-grant-tables

管理员模式下cmd输入命令

在这里插入图片描述

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uPuvCQoO-1659084549672)(D:\桌面\javaPicture\image-20211221190942066.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W7y0JqFn-1659084549673)(D:\桌面\javaPicture\image-20211221191000140.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O4TyoJ5d-1659084549673)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20211221190751699.png)]$

# mysqld --initialize-insecure --user=mysql

net start mysql

mysql -u root -p

update mysql .user set authentication_string=password(‘123456’) where user=‘root’ and Host=‘localhost’;

flush privileges;

连接数据库

mysql -uroot -p

在这里插入图片描述

连接数据库

命令行连接

mysql -uroot -p123456  --连接数据库

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

show database    --查看所有数据库
 mysql>use school

 mysql> show tables; ---查看数据库中所有的	表
 
 describe student;--- 展示表中的信息

 create database westos;----创建数据库
 
  show databases;----展示数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HQxg7ppZ-1659084549675)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20211221212006970.png)]

操作数据库

创建数据库
```
create database [if not exists] westos
```
删除数据库
```
DROP DATABASE IF EXISTS westos
```

使用数据库

--TABLE 键的上面  如果表名 或者字段名是一个特殊字符会显示高亮 就需要带这个符号
use `school`

查看数据库

show databases  --查看所有数据库

数据库的列类型

数值

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
**int 标准的整数 4个字节 ** 常用
big 较大的数据 8个字节
float 单精度浮点数 4个字节
double 双精度浮点数 8个字节
decimal 字符串形式的浮点数金融计算的时候一般使用decimal

字符串

char 字符串固定大小 0~255

varchar 可变字符串 0~65535 对应java String

tinytext 微型文本 2^8-1

text 文本串 2^16-1 保存大文本

utf-8 一个汉字等于3个varchar2

gbk 一个汉字等于2个varchar2

时间日期

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来填充，int（3），5—005

自增：

通常理解为自增，自动在上一条记录的基础上+1（默认）
通常用来设计唯一的主键~ index，必须为整数类型
可以自定义设计的主键自增的起始值和步长

非空：Null not null

假设设置为not null，如果不给他赋值，就会报错
Null，如果不填写值，默认就是null

默认：

设置默认的值！
sex，默认值为男，如果不指定该列的值则会有默认的值

创建数据库表

--注意点，使用英文（）,表的名称和段尽量使用``括起来
--AUTO_INCREMENT自增
--字符串使用单引号括起来
--所有的语句后面加，（英文的），最后一个不用加符号
--只有主键和数据约束需要加括号
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;

show CREATE DATABASE school
CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */
SHOW CREATE TABLE student
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', --DEFAULT 后面不需要()
  `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

`名称` 类型（长度） 是否允许为空 默认为什么''  备注'' //主键有时需要设置自动递增auto_increment

--查看表的结构
desc student
--修改表名：ALTER TABLE 旧表名 rename as 新表名
ALTER TABLE student rename as student1
--增加字段：ALTER TABLE 表名 ADD 字段 类型（长度）
ALTER table student1 add age int(11)
---修改表的字段
ALTER TABLE student1 modify age VARCHAR(11) ---修改约束
ALTER TABLE student1 CHANGE age1 age int(11) 
---删除表的字段
ALTER TABLE student1 DROP age
---删除表(如果存在再删除 ，加上判断条件)
drop TABLE if EXISTS student

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ABiUWrHc-1659084549676)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20211222182103546.png)]

外键连接

1 了解即可

CREATE table `grade`(
`gradeid` INT(10) not null auto_increment COMMENT '年级id',
`gradename` varchar(10) not null COMMENT '年级名称',
PRIMARY key(`gradeid`)
)engine=innodb default charset utf8
---学生表的gradeid字段 要去引用年级表的gradeid
---定义外键key
---给这个外键添加约束（执行引用） references 引用
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`gradeid` INT(10) not null  COMMENT '年级id',
  `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`),
	key `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN key (`gradeid`) references `grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 --默认的 不写也ok

CREATE table `grade`(
`gradeid` INT(10) not null auto_increment COMMENT '年级id',
`gradename` varchar(10) not null COMMENT '年级名称',
PRIMARY key(`gradeid`)
)engine=innodb default charset utf8


CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`gradeid` INT(10) not null  COMMENT '年级id',
  `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



/*创建表的时候没有外键关系*/
/*ALTER TABLE `表名`
ADD constraint `自定义外键约束名` FOREIGN KEY (`当前表当做外键字段`) REFERENCES `外键表名`(`外键字段`); */
ALTER TABLE `student`
ADD constraint `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);
--在创建外键表的时候出现了 Cannot add foreign key constraint 。 这里如果设置pid 为外键，有两个必要的条件： 1：pid 不能为该表的主键（primary key ) 2：参照建必须为参照表的主键 3：子表和父表的数据类型必须完全一致， 4： 出现错误原因： 因为你要添加外键时，你的外键类型是int 而引用的主键类型也是 int，如果你的主键设置auto_increment（那么你的字段类型实际上是unsigned_int） 主键，外键类型不一致，所以无法添加外键。

以上属于物理外键，数据库级别的的外键，不建议使用（避免数据库过多，删除表示有困扰）

DML语言

DML：数据操作语言

insert
update
delete

添加

语法：insert into 表名（字段1，字段2，字段3）values(‘值1’，‘值2’，‘值3’)

#插入语句（添加）
#insert into 表名（[字段1,字段2,字段3]）VALUE('值1'),('值2'），（'值3'），....)
INSERT INTO `grade`(`gradename`)VALUES('大四')
#由于主键自增可以省略主键（如果不写表的字段，他们就会一一匹配）
insert into `grade` values ('5','大shi')
insert into teacher values(null,1000000,'江宁周岗和平','张宇'); --null代表空也可以代表空赋值,系统自动赋值
#一般写插入语句，我们一定要数据和字段一一对应！
#插入多个字段
insert  into `grade`(`gradename`) values('大二'),('大一')

insert into `grade`(`gradename`) values ('大三')
#插入多个字段
insert into `student`(`name`) VALUES ('张三')

insert into `student`(`name`,`psd`,`sex`) VALUES ('张三','654321','女'),('李四','654321','男')

注意事项：

字段与字段之间使用英文逗号分开
字段是可以省略的，但是后面的值必须要一一对应，不能少（自增的字段可以使用null代替）
可以同时插入条数据，values后面的值需要使用逗号隔开 values（），（）…
1146 - Table ‘school.tacher’ doesn’t exist 多半是表名拼错了

修改

update 修改

update 表名 set 需更改的字段=’ 修改的数据’ where 标志字段=

#修改学员名字
UPDATE `student`set `name`='张宇' where id=1
#不指定条件会更改所有的表
UPDATE `student`set `name`='张宇'

#修改学员名字
UPDATE `student`set `name`='张宇' where id=1

#不指定条件会更改所有的表
UPDATE `student`set `name`='张宇'

#更改多个数据
update `student` set `name`='大红',`psd`='222222' where `id`BETWEEN 7 and 9

#通过条件定位多个数据 条件限制无上限
update `student` set `name`='萧炎',`psd`='3333' where `sex`='男' and `address`='大别墅'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5tvHxPBU-1659084549676)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20211223180207902.png)]

注意：

需更改的字段是数据库的列尽量带上``
条件，删选的条件如果没有指定（where）则会修改所有的列
修改的数据是一个具体的值也可以是一个变量
多个设置的属性之间要用逗号隔开（set name=张三，sex=男）

删除

delete命令

语法：delete from 表名 where 条件

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

# 删除指定数据
delete FROM `student` WHERE id=1

完全删除数据库表

完全清除一个数据库表，表的索引和约束不会变
truncate `student`

delete 和 truncate的区别

相同点：都能删除数据，都不会删除表的结构

不同

ttruncated 重新设置自增列，计数器会清零

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RyuOoAyr-1659084549676)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20211223190620763.png)]
truncate 不会影响事物

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EvtahMQi-1659084549677)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20211223190747970.png)]

DQL查询数据（重点！！！！）

语法：

#查询全部的学生，Select * from 表

#查询指定字段 select '字段'，'字段'，from 表

#别名 给结果起一个名字 AS 可以字段起别名，也可以给表起别名
select `name`AS 姓名,`psd`AS 密码 from student AS s

#插入语句（添加）
#insert into 表名（[字段1,字段2,字段3]）VALUE('值1'),('值2'），（'值3'），....)
INSERT INTO `grade`(`gradename`)VALUES('大四')
#由于主键自增可以省略主键（如果不写表的字段，他们就会一一匹配）
insert into `grade` values (null,'大shi')
#一般写插入语句，我们一定要数据和字段一一对应！
#插入多个字段
insert  into `grade`(`gradename`) values('大二'),('大一')

insert into `student` values(null,'林动','124578','男','2000-06-07','大别墅') 

insert into `grade`(`gradename`) values ('大三')
#插入多个字段
insert into `student`(`name`) VALUES ('张三')

insert into `student`(`name`,`psd`,`sex`) VALUES ('张三','654321','女'),('李四','654321','男')

insert into `student`values(null,'张一','232112','男','2000-06-07','大别墅'),(null,'张二','232112','男','2000-06-07','大别墅'),(null,'张三','232112','男','2000-06-07','大别墅'),(null,'张四','232112','男','2000-06-07','大别墅'),(null,'张五','232112','男','2000-06-07','大别墅'),(null,'张六','232112','男','2000-06-07','大别墅'),(null,'张七','232112','男','2000-06-07','大别墅')
,(null,'张八','232112','男','2000-06-07','大别墅')

#修改学员名字
UPDATE `student`set `name`='张宇' where id=1
#不指定条件会更改所有的表
UPDATE `student`set `name`='张宇'
#更改多个数据
update `student` set `name`='大红',`psd`='222222' where `id`BETWEEN 7 and 9
#通过条件定位多个数据 条件限制无上限
update `student` set `name`='萧炎',`psd`='3333' where `sex`='男' and `address`='大别墅'

update `student` set `birthday`=CURRENT_TIME WHERE `id`=7



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

# 删除指定数据
delete FROM `student` WHERE id=1 

完全清除一个数据库表，表的索引和约束不会变
truncate `student`

create table `test`(
`id` int(4) not null auto_increment,
`name` VARCHAR(5) not null COMMENT'姓名',
PRIMARY key(`id`)
)engine=innodb default charset=utf8

INSERT into `test` VALUES(null,'张三'),(null,'李四')

DELETE from `test` where `id`=2

truncate `test`

use `school`

drop table if exists `grade`

CREATE table `grade`(
`gradeid` int(4) not null auto_increment,
`gradename` VARCHAR(10) not null,
PRIMARY key(`gradeid`)
)engine=innodb DEFAULT charset=utf8
#查询全部的学生，Select * from 表
select * from `student`
select * from  grade

#查询指定字段 select '字段'，'字段'，from 表
select  `psd`,`address`FROM student
#别名 给结果起一个名字 AS 可以字段起别名，也可以给表起别名
select `name`AS 姓名,`psd`AS 密码 from student AS s
#函数 concat（'a',b）
select concat('姓名:',name) AS 新名字 FROM student 
select CONCAT('学号:',StudentNo,'姓名:',StudentName) as 新名字 from Student;

有时候，列名字不是那么的见名知意，我们起别名 AS 字段名 AS别名表名 AS 别名

#查询全部学生
SELECT*FROM grade
#去除select查询出来的结果中重复的数据
select distinct `gradename`from grade


# 查询版本
select version
select 100*3-1 AS 计算结果
select @@auto_increment_increment # 查询自增的步长

select `address`AS 地址,`psd`+999 as 修改后 from student

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

where 条件子句

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

尽量使用英文

运算符	语法	描述
and &&	a and b a&&b	逻辑与
or \|\|	a or b a\|\|b	逻辑或
not !	not a !a	逻辑非

 --查询成绩在90-100之间的学生
SELECT studentno,studentresult from result where studentresult>90 and studentresult<100; 
SELECT studentno,studentresult from result where studentresult>90 && studentresult<100;
select studentno,studentresult from result where studentno!=444;
select studentno,studentresult from result where not studentno=444;

运算符	语法	描述
is null	a is null	如果a为nulll结果为真
is not null	a is not null	如果a不为null结果为真
between	a between b and c	若a在b和c之间则为真
like	a like b	sql匹配,如果a匹配b则结果为真
in	a in (a1,a2,a3…)	假设a在a1或者a2或者a3中就为真

--查询姓王的同学
%代表多个字符,_代表一个字符
 select studentno,studentName from student where studentname like '王%';
 --查询姓张且只有两个字的同学
 select studentno,studentname from student where studentname like '张_';
 -- --查询名字里带有非的
 select studentno,studentname from student where studentname like '%非%'or
 studentname like '非%' ;
 --查询456年级的学生
 select studentno,studentname from student where gradeid in(4,5,6);
 --查询张1 张2两名同学的分数
 select studentno studentname from student where studentname in('张2','张1');
 --查询地址
 select studentno from student where address=''or address is null;
 --查询地址不为空的
 select studentno,studentname from student where address is not null ;

/*
1.分析需求,分析查询的字段来自哪些表(连接查询)
2. 确定使用哪种连接查询?7种
3.确定交叉点(这两个表中哪个数据是相同的)
判断条件:where/on
*/
--查询学生表中学生的成绩
select s.studentno,studentname,phone,studentresult,subjectno from student as s inner join result as r where s.studentno=r.studentno;
--right join 
--(个人理解)左边的表中需要查询的字段数据与其对应右边表中的需要查询的字段全出来,共同的连接字段部分只出来需要查找的数据.
select r.studentno,studentname,phone,subjectno,studentresult from result r left join student s on s.studentno=r.studentno;
--(个人理解)右边的表中需要查询的字段数据与其对应左边表中的需要查询的字段全出来,共同的连接字段部分只出来需要查找的数据.
select r.studentno,studentname,phone,subjectno,studentresult from result r right join student s on s.studentno=r.studentno;

由于内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所有在内连接时on和where的结果是相同的。

描述	操作
inner	返回两个表中联结字段相等的行
left join	返回左表中所有查询的值,即使右表中没有匹配
right join	返回右表中所有查询的值,即使左表中没有匹配

--查询参加了考试同学的信息(学号,学生姓名,科目名,分数)
/*思路 
1. 分析需求,分析查询的字段来自哪些表,student\result\subject
2.确定交叉点(这两个表中那个属于是相同的)判断的条件:学生表总 studentno=成绩表studentno*/
select s.studentno,studentname,subjectname,studentresult from student s right join result  r on s.studentno=r.studentno inner join subject sub on r.subjectno=sub.subjectno;

我要查询哪些数据 select…从哪几个表中查from表 xxxjoin连接的表 on 交叉条件
假设存在一种多张表的查询,慢慢来,先查询两张表然后再慢慢增加
使用join时考虑采用哪个表中的全部数据例如考试的同学成绩就right join result 或者 result left join
from a left join b
from a right join b

自连接(了解但公司常用)

--自连接
create table category(
categoryid int(10) UNSIGNED not null auto_increment comment '主题id',
pid  int(10) not null,
categoryname varchar(50) not null ,
primary key(categoryid)
)ENGINE=INNODB DEFAULT charset=utf8;
insert into category(categoryid,pid,categoryname) values(2,1,'信息技术'),(3,1,'软件开发'),(4,3,'美术设计'),(5,1,'数据库'),(6,3,'办公信息'),(7,5,'web开发'),(8,2,'ps技术');
--categoryid 是唯一id主键 他下面的项目pid等于他的categoryid
select a.categoryname as 父栏目, b.categoryname as 子栏目 from category a,category b where b.pid=a.categoryid;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vw5PO8c9-1659084549677)(D:\桌面\javaPicture\1646321294200.png)]

连表查询

**on是生成符合条件的临时表,该临时表应不应用看inner(临时表根据字段匹配进新表,新表无法匹配的字段部分仍然存在) left(临时表替换但保留左表数据) 和 right (临时表替换但保留右表数据) **

-- 查询学员所属的年级(学号 姓名 年级名称)
select studentno,studentname,gradename from student s inner join grade g on s.gradeid=g.gradeid;	 --两个表对应年级号相等的所有学生
--查询科目所属的年级(科目名称 年级名称)
select subjectname,gradename from subject s inner join grade g on
s.gradeid=g.gradeid;
--查询参加了大学英语考试的学生信息(学号 姓名 科目名 分数)
select s.studentno,studentname,subjectname,studentresult from student s right join result r on s.studentno=r.studentno inner join subject sub on r.subjectno=sub.subjectno where subjectname='大学英语';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HXMIdcmh-1659084549677)(D:\桌面\javaPicture\1646362306074.png)]

语法顺序不能调换 where 不能卸载 left right上面

1052 - Column ‘studentno’ in field list is ambiguous 无法区分查询字段是哪张表中的字段

排序

order by 排序字段 asc (升序)

order by 排序字段 desc (升序)

--查询参加了大学英语考试的学生信息(学号 姓名 科目名 分数)并通过成绩进行降序排序
select s.studentno,studentname,subjectname,studentresult from student s right join result r on s.studentno=r.studentno inner join subject sub on r.subjectno=sub.subjectno where subjectname='大学英语' order by studentresult desc ;
--根据成绩升序排序
select s.studentno,studentname,subjectname,studentresult from student s right join result r on s.studentno=r.studentno inner join subject sub on r.subjectno=sub.subjectno where subjectname='大学英语' order by studentresult asc ;

分页

limit 查询起始数据下标,页面数据条数

select s.studentno,studentname,subjectname,studentresult from student s right join result r on s.studentno=r.studentno inner join subject sub on r.subjectno=sub.subjectno where subjectname='大学英语' order by studentresult desc LIMIT 0,1;

--查询大学英语课程成绩前两名,并且分数要大于80分的学生信息(学号 姓名 课程名 分数)
select s.`studentno`,`studentname`,`subjectname`,`studentresult` from `student` s inner join `result` r on  s.studentno=r.studentno  inner join `subject` sub on r.subjectno=sub.subjectno where `subjectname`='大学英语'and studentresult>80
order by studentresult desc

子查询和嵌套查询

--查询参加了大学英语考试的学生信息(学号 姓名 科目名 分数)并通过成绩进行降序排序 每页只显示1条数据
select s.studentno,studentname,subjectname,studentresult from student s right join result r on s.studentno=r.studentno inner join subject sub on r.subjectno=sub.subjectno where subjectname='大学英语' order by studentresult desc LIMIT 0,1;
--根据成绩升序排序
select s.studentno,studentname,subjectname,studentresult from student s right join result r on s.studentno=r.studentno inner join subject sub on r.subjectno=sub.subjectno where subjectname='大学英语' order by studentresult asc ;

--查询大学英语课程成绩前两名,并且分数要大于80分的学生信息(学号 姓名 课程名 分数)
select s.`studentno`,`studentname`,`subjectname`,`studentresult` from `student` s inner join `result` r on  s.studentno=r.studentno  inner join `subject` sub on r.subjectno=sub.subjectno where `subjectname`='大学英语'and studentresult>80
order by studentresult desc 
--查询高等数学所有考试结果(学号 科目编号 成绩)降序排列
--方式一 使用连接查询
select s.`studentno`,sub.`subjectno`,`studentresult` from `student` s inner join `result` r on s.`studentno`=r.`studentno` inner join subject sub on r.`subjectno`=sub.`subjectno` where subjectname='高等数学' order by `studentresult` desc;

select `studentno`,r.`subjectno`,`studentresult` from `result` r inner join `subject` sub on r.subjectno=sub.subjectno where subjectname='高等数学' order by `studentresult` desc;
--方式二 当子查询得到的数据不止一个的时候where 连接用in 不要用=
select studentno,subjectno,studentresult from result r where subjectno in(select subjectno from subject where subjectname='高等数学') order by studentresult desc;
----高等数学分数不小于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 studentresult>=80 and subjectname='高等数学';
--分数不小于80分的学号和姓名(子查询)
select studentno,studentname from student where studentno in (select studentno from result where studentresult>=80);
--高等数学分数不小于80分的学号和姓名(子查询2)
select studentno, studentname from student s  where studentno in(select studentno from result where subjectno in (select subjectno from `subject` where subjectname='高等数学') and studentresult>=80);


select s.studentno,studentname from student s inner join result r on s.studentno=r.studentno where studentresult>=80 and subjectno in(select subjectno from `subject` where subjectname='高等数学');

--练习:查询高等数学前二名同学的成绩信息(学号 姓名 分数)
select s.studentno ,studentname, studentresult from result r inner join student s on r.studentno=s.studentno where subjectno in(select `subjectno` from `subject` where subjectname='高等数学') order by studentresult desc limit 0,2;

函数

 --函数 
 select abs(-8)#绝对值
select ceiling(9.4)#向上取整
 select floor(9.4)#向下取整
select rand()#随机数
select sign(-8)#判断符号
-- 字符串函数
select char_length('我要成为千万富翁!');-- 字符串长度
select concat('我','要','成','为','千','万','富','翁','!');-- 连接字符成字符串
select  insert ('我要成为千万富翁',1,2,'张宇');-- 替换
select lower('ZHANGYU');-- 转小写
select UPPER('zhangyu');-- 转大写
select instr('zhangyu','z');-- 返回第一次出现的字串的索引
select replace('张宇要成为百万富翁','百万','千万'); --替换指定的字符串
select substr('张宇要成为百万富翁',4,6); 从第四个开始返回六个字符
select reverse('张宇梦想成为亿万富翁');-- 反转
select replace(studentname,'张','李')from student where studentname 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();

聚合函数

函数名称	描述
count(字段名) count(1) count(*)	计数
sum()	求和
avg()	求平均值
max()	最大值
min()	最小值
…

-- 聚合函数
select count(studentname)from student;-- count(字段) 会忽略所有的null值
select count(*)from student;
-- 不会忽略null值,本质计算行数
select count(1) from result;
-- 不会忽略null值,本质计算行数
 
select AVG(studentresult) from result;
select sum(studentresult) from result;
select max(studentresult) from result;
select min(studentresult) from result;
set sql_mode =’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;
-- 查询不同课程的平均分 最高分 最低分
 select subjectname,sum(studentresult)as 总分,avg(studentresult)as 平均分,max(studentresult) as 最高分,min(studentresult) as 最低分 from result r inner join `subject` sub on r.subjectno=sub.subjectno group by subjectname having 最高分>90;

group by 后面的字段必须要在select 后面的列字段当中

**where只能在group by 前面 group by后面要使用having 来过滤条件 **

数据库级别MD5加密

什么是md5:主要增强算法复杂度和不可逆性.

-- c测试MD5 加密
create table testMD5(
id int(10) not null ,
`name` varchar(20) not null ,
pwd varchar(100) not null ,
PRIMARY key(id)
)ENGINE=INNODB default charset=utf8;
ALTER table testmd5 MODIFY pwd varchar(100);
-- 明文密码
INSERT into testmd5 values(1,'张三','123456'),(2,'李三','123456'),(3,'王三','123456');
-- 加密
update testmd5 set pwd=md5(pwd) where id=1;
SELECT * from testmd5;
update testmd5 set pwd=md5(pwd);
insert into  testmd5  values(4,'杨三',MD5('123456'));
select *from testmd5 where pwd=md5('123456'); -- 只能查到加密一次的数据

select 小结

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yBh8XH9Y-1659084549678)(D:\桌面\javaPicture\1646462396776.png)]

事物

什么是事物

指是程序中一系列严密的逻辑操作 ,数据双方要么成功,要么失败

SQL执行 A给B转账 A1000-200
SQL执行 B收到A的钱 B200+200

将一组SQL放在一个批次中去执行

事物原则:ACID原则 原子性,一致性,隔离性,持久性

原子性(Atomicity)

对数据的操作要么都成功,要么都失败

一致性(Consistency)

事务前后的数据完整性要保证一致,比如转账前后两边总金额不变.

持久性(Durability)

事务一旦提交则不可逆,被持久化到数据库中!

隔离性(Isolation)

事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启事务,不能被其他事务操作数据库所干预,事务之间要相互隔离.

隔离所导致的一些问题

脏读

A事务还未提交 B事务就就读到了A事务的结果

不可重复读

在一个事务内读取表中的某一行数据,多次读取结果不同(这不一定是错误的,只是某些场合不对) 破坏了一致性(原本的数据在第二次读的时候改变了)

虚度(幻读)

是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致 .也破坏了一致性(即多出了一行)

================事务================
-- mysql是默认开启事务自动提交的
set autocommit=0/*关闭*/
set autocommit=1/*开启(默认的)*/
-- 手动处理事务
set autocommit=0 -- 关闭自动提交
-- 开启事务
start transaction -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
insert xxx
insert xxx
-- 提交 持久化(成功)
COMMIT
-- 回滚:回到原来的样子(失败!)
rollback
-- 事务结束
set autocommit=1-- 开启自动提交

-- 了解
savepoint 保存点名  -- 设置一个事务的保存点
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 撤销保存点

================事务================
-- mysql是默认开启事务自动提交的
set autocommit=0/*关闭*/
set autocommit=1/*开启(默认的)*/
-- 手动处理事务
set autocommit=0 -- 关闭自动提交
-- 开启事务
start transaction -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
insert xxx
insert xxx
-- 提交 持久化(成功)
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(20) not null,
money decimal(9,2) not null ,
PRIMARY key(id)
)ENGINE=INNODB default charset=utf8;
insert into account values(null,'张宇',100000),(null,'李四',1000);
delete  from account where id=3;
-- 模拟转账事务
select *from account ;
set autocommit=0;
start transaction;
update account set money=money-500 where name='张宇';
update account set money=money+500 where name='李四';
rollback;
commit; -- 提交之后数据库才会显示变化后的数据
set autocommit=1;

索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容.

索引分类

主键索引(primary key)
- 唯一的表示,主键不可重复只能有一个列作为主键
唯一索引(uniique key) 查询快数据不重复可以有多个
- 避免索引的列中出现重复的值,唯一索引可以有多个,即多个列都可以标识为唯一索引
常规索引(key/index) 查询快
- 默认的,用index或key关键字来设置
全文索引(fulltext)
- 在特定的数据库引擎下才有,myisam.
主键索引与唯一索引的区别 :主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。. 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。. 唯一性索引列允许空值，而主键列不允许为空值。**. 主键列在创建时，已经默认为空值 + 唯一索引了。. 主键可以被其他表引用为外键，而唯一索引不能。. 一个表最多只能创建一个主键，但可以创建多个唯一索引。. 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

索引的使用

在创建表的时候给字段增加索引
创建完毕后增加索引

-- 查询表中的所有索引
show index from student;
-- 设置索引
alter table student add index studentname_index(studentname);
create index student_index on student(studentname)

drop index student_index on studnet

alter table student drop PRIMARY key;

索引在小数据量的时候用处不大,但在大数据的时候区别十分明显.

创建索引的列会以(B、二叉)树的形式存放小值放左边,大值放右边

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kf3ti37Y-1659084549678)(D:\桌面\javaPicture\1646489001489.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wblUyFav-1659084549679)(D:\桌面\javaPicture\1646491514304.png)]

数据库用户管理

-- 创建用户 create 用户名 identified '密码';
create user zhangyu  identified by '123456';

-- 修改密码(修改当前用户密码)
set password=password('111111');
set password=password('123456')
-- 修改指定用户密码
set password for zhangyu=password('123456')
set password for daYuZhou=password('123123')
-- 重命名 rename user 原来的名字 to
rename user zhangyu to zhangyu1;
-- 用户授权 all prrvileges 全部的权限,库 表	
grant all privileges on *.* to zhangyu1;
 -- 查看权限
 show grants for zhangyu1
show grants for root@localhost

-- 撤销权限
revoke all privileges on *.* from zhangyu1;
 -- 删除用户
 drop user zhangyu1;

Mysql备份

为什么要备份:

保证重要的数据不丢失
数据转移

Mysql数据库备份的方式

直接拷贝物理文件(mysql下的data文件)
在sqlyog这种可视化工具中手动导出
使用命令行导出 mysqldump 命令行使用

导出

mysqldump -h主机 -u用户名 -p密码 数据库名 表名 >位置
mysqldump -hlocalhost -uroot -p123456 demo laptop >d:/桌面/a.sql
-- 多表导出备份
mysqldump -h主机 -u用户名 -p密码 数据库名 表名1 表名2 >位置
mysqldump -hlocalhost -uroot -p123456 demo laptop alptop2 >d:/桌面/b.sql
-- 数据库导出备份
mysqldump -h主机 -u用户名 -p密码 数据库名 >位置
mysqldump -hlocalhost -uroot -p123456 demo >d:/桌面/c.sql

导入

```
/* mysql -u用户名 -p密码
use 数据库
source 文件存放位置
*/
mysql -uroot -p123456 
use school
source d:\桌面\a.sql
```
规范数据库设计

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wh9Ro6d0-1659084549679)(D:\桌面\javaPicture\1646564097539.png)]

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

分析需求:分析义务和需要处理的数据库的需求
概要设计:设计关系图 E-R图

设计数据库的步骤举例个人博客系统

收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表 (文章分类,谁创建的)
- 文章表(文章的信息)
- 友链表(友链信息)
- 自定义表(系统信息,某个关键的字,或者一些主字段)
- 标识实体(把需求落地到每)
- 标识实体之间的关系
  - 写博客: user—>blog
  - 创建分类:user—>category
  - 关注:user—>user
  - 评论: user—>user—>blog

三大范式

第一范式(1NF)

原子性:保证每一列不可再分

第二范式(2NF)

前提要满足第一范式
数据库表中的每一列数据都和主键完全相关[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4kI29RzZ-1659084549679)(D:\桌面\javaPicture\1646568219403.png)]

上表订单金额和产品号并不是完全相关,只是一部分订单金额属于该产品,所以不满足第二范式

第三范式(3NF)

前提要满足第一范式和第二范式
确保每一列数据都和主键直接相关而不是间接相关

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UsUD9vBQ-1659084549679)(C:\Users\ADMINI~1\AppData\Local\Temp\1646568303055.png)]

上表班主任性别和班主任的名字相关,而和学生姓名是间接相关.

规范性和性能的矛盾

关联查询的表不得超过三张表

考虑商业化的需求和目标,(成本,用户体验!) 数据库的性能更加重要
在规范性能的问题的时候,需要适当考虑一下规范性!
故意给某些表增加一些冗余的字段(从多表查询变为单表查询)

数据库驱动和JDBC

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P4lqJPib-1659084549680)(D:\桌面\javaPicture\1646716409900.png)]

java.sql

javax.sql

还需要导入一个数据库驱动包 mysql.connector-java-5.1.47.jar

第一个JDBC程序

 -- 创建测试数据库
drop database   Jdbc_Study
create database Jdbc_Study character set utf8 collate utf8_general_ci;
 create table users(
 id int(10) primary key,
 `name` varchar(40),
 pwd varchar(100),
 email varchar(100),
 birthday date
 );
 insert into users values(1,'zhangsan','1324165@qq.com','123456','2000-1-2'),(2,'lisi','1324165@qq.com','123456','2000-1-2'),(3,'wangwu','1324165@qq.com','123456','2000-1-2')

创建一个普通项目
导入数据库驱动

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GJ72oblX-1659084549680)(D:\桌面\javaPicture\1646718845833.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yQowNsEP-1659084549681)(D:\桌面\javaPicture\1646718858802.png)]

编写测试代码

package com.DYZ.Study;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcTest1 {
    public static void main(String[] args) throws Exception {
        // 加载驱动       “Class.forName(xxx.xx.xx)返回的是一个类。Class.forName(xxx.xx.xx)的作用是要求JVM查找并加载指定的类，也就是说JVM会执行该类的静态代码段。在java中Class.forName()和ClassLoader都可以对类进行加载”
        Class.forName("com.mysql.jdbc.Driver"); //固定写法
        //需要用户信息和sql主机地址
        String url="jdbc:mysql://localhost:3306/jdbc_study?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String userName="root";
        String passWord="123456";
        //连接数据库,返回数据库对象     connection代表数据库
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        //创建sql对象
        Statement statement = connection.createStatement();
        //执行sql  execute能用于插入,删除,更新 查询 而executeQuery只能用于查询
        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("email="+resultSet.getObject("email"));
            System.out.println("pwd="+resultSet.getObject("pwd"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }
        //释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结

加载驱动

//DriverManager.registerDriver(newcom.mysql.jdbcDriver()); 等于注册了两次 没必要 不采用该写法
Class.forName("com.mysql.jdbc.Driver");

创建sql主机地址和用户信息

//jdbc:mysql://主机地址:端口号//数据库名?是否使用中文编码&字符编码&是否采用安全连接
String url="jdbc:mysql://localhost:3306/jdbc_study?useUnicode=true&characterEncoding=utf8&useSSL=false";

连接数据库DriverManager

        //连接数据库,返回数据库对象     connection代表数据库 数据库中的操作基本都用connection
        Connection connection = DriverManager.getConnection(url, userName, passWord);
       // connection.setAutoCommit(false);
       // connection.commit();
       // connection.rollback()；

获得执行sql的对象Statement

//创建执行sql的对象
Statement statement = connection.createStatement();
//执行sql  execute能用于插入,删除,更新 而executeQuery只能用于查询
String sql="select *from users";
ResultSet resultSet = statement.executeQuery(sql); //executeUpdate 执行增删改等不能执行select 返回一个受影响的行数  execute 执行任何sql 返回boolean值	
//如果知道类型就返回指定的类型
//        resultSet.getInt();
//        resultSet.getString();
//        resultSet.getFloat();
//        resultSet.getObject();

获得返回的结果集

         resultSet.getObject();//不知道类型的情况下使用
      //如果知道类型就返回指定的类型
//        resultSet.getInt();
//        resultSet.getString();
//        resultSet.getFloat();

遍历指针

//        resultSet.beforeFirst();//移动到最前面
//        resultSet.afterLast();//移动到最后面
//        resultSet.next();//移动到下一个数据
//        resultSet.previous();//移动到前一行
//        resultSet.absolute();//移动到指定行

释放连接

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

Statement对象详解

jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可.

Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sql语句,executeUpdate执行完成后,将会返回一个整数(即增删改语句导致了数据库几行发生了变化).

Statement.executeQuery方法用于向数据库发送查询语句,executeQuery返回代表查询结果的ResultSet对象.

CRUD操作-create

Statement st=conn.createStatement();
String sql="insert into user(...) values(....) ";
int num=st.executeUpdate(sql)
    if(num>0){
        System.out.println("插入成功!!!");
    }

CRUD操作-delete

Statement st=conn.createStatement();
String sql="delete from user where id=1 ";
int num=st.executeUpdate(sql)
    if(num>0){
        System.out.println("删除成功!!!");
    }

CRUD操作-Update

Statement st=conn.createStatement();
String sql="update user set name'' where name='' ";
int num=st.executeUpdate(sql)
    if(num>0){
        System.out.println("修改成功!!!");
    }

CRUD操作-read

Statement st=conn.createStatement();
String sql="update user set name'' where name='' ";
ResultSet rs=st.executeQuery(sql)
    while(rs.nex()){
        //根据获取的数据类型,分别调用rs的相应方法映射到java对象中
    }

db.properties

db.properties 注: 一定要在src目录下创建

//不要带有分号 将两个字符串用等号分开以此来表示键值
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_study?useUnicode=true&characterEncoding=utf8&useSSL=false
userName=root
passWord=123456

JdbcUtils

JdbcUtils

package com.DYZ.JDBC1;

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");

           //驱动只需加载一次
            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();
            }
        }
    }

}

InsertTest (只要里面的sql需要变)

package com.DYZ.JDBC1;

import com.DYZ.JDBC1.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class InsertTest1 {
    public static void main(String[] args) {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
             conn = JdbcUtils.getConnection();
             st=conn.createStatement();
             String sql="insert into users values(8,'中宇宙','1048383261','360669145@qq.com','2000-06-07')";
             int num=st.executeUpdate(sql);
             if(num>0){
                 System.out.println("插入成功");
             }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

select

package com.DYZ.JDBC1;

import java.sql.*;

public class SelectTest1 {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql="select* from users where id=3";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println("id="+resultSet.getInt("id"));
                System.out.println("name="+resultSet.getString("name"));
                System.out.println("pwd="+resultSet.getString("pwd"));
                System.out.println("email="+resultSet.getString("email"));
                System.out.println("birthday="+resultSet.getString("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils.release(connection,statement,resultSet);
        }

    }
}

防止SQL注入 PrepareStatement

需将CreateStatement方法改为PrepareStatement方法

package com.DYZ.JDBC1;

import java.sql.*;

class PrepareStatementTest1 {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement prepareStatement=null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtils.getConnection();
            //区别  使用?来替代参数
            String sql="insert into users values(?,?,?,?,?)";
            //预编译 先写sql 不执行
            prepareStatement = connection.prepareStatement(sql);
            //手动设置参数赋值
            prepareStatement.setInt(1,9);
            prepareStatement.setString(2,"张宇");
            prepareStatement.setString(3,"147852369");
            prepareStatement.setString(4,"21151@qq.com");
            prepareStatement.setString(5,new java.sql.Date(new java.util.Date().getTime()).toString());
            prepareStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils.release(connection,prepareStatement,resultSet);
        }

    }
}

package com.DYZ.JDBC1;

import java.sql.*;

class PrepareStatementTest2 {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement prepareStatement=null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtils.getConnection();
            //区别  使用?来替代参数
            String sql="delete from users where id=?" ;
            //预编译 先写sql 不执行
            prepareStatement = connection.prepareStatement(sql);
            prepareStatement.setInt(1,8);
            int i= prepareStatement.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils.release(connection,prepareStatement,resultSet);
        }

    }
}

package com.DYZ.JDBC1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

class PrepareStatementTest3 {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement prepareStatement=null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtils.getConnection();
            //区别  使用?来替代参数
            String sql="update users set pwd=? where id=?" ;
            //预编译 先写sql 不执行
            prepareStatement = connection.prepareStatement(sql);
            prepareStatement.setInt(1,360669145);
            prepareStatement.setInt(2,7);
            int i= prepareStatement.executeUpdate();
            if(i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils.release(connection,prepareStatement,null);
        }

    }
}

package com.DYZ.JDBC1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

class PrepareStatementTest4 {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtils.getConnection();
            String sql="select *from users where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,5);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                System.out.println("id="+resultSet.getInt("id"));
                System.out.println("name="+resultSet.getString("name"));
                System.out.println("pwd="+resultSet.getString("pwd"));
                System.out.println("email="+resultSet.getString("email"));
                System.out.println("birthday="+resultSet.getString("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement, resultSet );
        }


    }
}

当查询的数据是一个变量时如何有效的防止sql注入

package com.DYZ.JDBC1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;



class PrepareStatementTest6 {
    public static void main(String[] args) {
        login("'' or 1=1","123456");

    }
    //登录业务
    public static void login(String userName,String passWord){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtils.getConnection();
            String sql="select *from users where name=? and pwd=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,userName);
            preparedStatement.setString(2,passWord);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                System.out.println("id="+resultSet.getInt("id"));
                System.out.println("name="+resultSet.getString("name"));
                System.out.println("pwd="+resultSet.getString("pwd"));
                System.out.println("email="+resultSet.getString("email"));
                System.out.println("birthday="+resultSet.getString("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement, resultSet );
        }


    }
}

使用IDEA连接数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aAmWjQTN-1659084549681)(D:\桌面\javaPicture\1646801398890.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kIaY5ljz-1659084549681)(C:\Users\ADMINI~1\AppData\Local\Temp\1646805626990.png)]

连接成功后选择数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DiDyVaAi-1659084549682)(C:\Users\ADMINI~1\AppData\Local\Temp\1646801603589.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FiGEOXG1-1659084549682)(D:\桌面\javaPicture\1646805047914.png)]

JDBC操作事务

package com.DYZ.JDBC1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TransectionTest1 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=JdbcUtils.getConnection();
            //关闭自动提交 即开启事务
            conn.setAutoCommit(false);
            String sql1="update account set money=money-1000 where id=1";
            ps = conn.prepareStatement(sql1);
            ps.executeUpdate();
            int x=1/0; //测试事务, 让代码执行失败
            String sql2="update account set money=money+500 where id=2";
            ps=conn.prepareStatement(sql2);
            ps.executeUpdate();
            String sql3="update account set money=money+500 where id=3";
            ps=conn.prepareStatement(sql3);
            ps.executeUpdate();
            //业务完毕 提交事务
            conn.commit();
            System.out.println("工资发放成功");
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback(); //如果遇到异常 则捕获并回滚 如失败系统也会自动回滚 可写可不写
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            JdbcUtils.release(conn,ps,null);
        }

    }
}

DBCP-C3P0连接池

数据库连接—执行完毕—释放十分浪费资源

池化技术:准备一些预先备好的资源,过来就执行,完毕就放回去.

最小连接数

最大连接数

等待超时

编写连接池,实现一个接口Datasource

开源数据源实现

DBCP C3P0 Druid

使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接.

DBCP

commons-dbcp-1.4
commons-pool-1.6

DBCPconfig.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_study?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSl=false
username=root
password=123456
#初始化连接数
initialSize=10
#最大连接数
maxActive=50
#最大空闲连接
maxIdle=20
#最小空闲连接
minIdle=5
#最长等待超时时间 以毫秒为单位
maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=UTF8

defaultAutoCommit=true

defaultReadOnly=

defaultTransactionIsolation=READ_UNCOMMITTED

JdbcUtils_DBCP

package com.DYZ.dbcp.util;

        import org.apache.commons.dbcp.BasicDataSource;
        import org.apache.commons.dbcp.BasicDataSourceFactory;
        import javax.sql.DataSource;
        import java.io.InputStream;
        import java.sql.*;
        import java.util.Properties;

public class JdbcUtils_DBCP {
    private static DataSource dataSource=null;
    static{
        try{
            InputStream in= com.DYZ.dbcp.util.JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);//将用等号分割的两个字符串(键值)装进properties里
            //创建数据源  工厂模式--->创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    //释放连接资源
    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();
            }
        }
    }

}

DBCPTest

package com.DYZ.dbcp;

import com.DYZ.dbcp.util.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DbcpTest1 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
           conn = JdbcUtils_DBCP.getConnection();
           String sql="select `name` ,`money` from account where id=1";
           ps = conn.prepareStatement(sql);
           rs = ps.executeQuery();
            while(rs.next()){
                System.out.println("姓名:"+rs.getString("name"));
                System.out.println("余额:"+rs.getBigDecimal("money"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils_DBCP.release(conn,ps,rs);
        }
    }
}

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_study?serverTimezone=Asia/Shanghai</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
    <!-- This app is massive! -->
    <named-config name="MYSQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_study?serverTimezone=Asia/Shanghai</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>

</c3p0-config>

JdbcUtils_C3P0

package com.DYZ.dbcp.util;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource  dataSource=null;
    static{
        try{
            //配置文件写法  代码版配置
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setJdbcUrl();
            dataSource = new ComboPooledDataSource("MYSQL");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建连接
    public static Connection getConnection() throws SQLException {
            return dataSource.getConnection();
    }
    //释放连接资源
    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();
            }
        }
    }

}

C3P0Test1

package com.DYZ.dbcp;

import com.DYZ.dbcp.util.JdbcUtils_C3P0;
import com.DYZ.dbcp.util.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class C3P0Test1 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
           conn = JdbcUtils_C3P0.getConnection();
           String sql="select `name` ,`money` from account where id=2";
           ps = conn.prepareStatement(sql);
           rs = ps.executeQuery();
            while(rs.next()){
                System.out.println("姓名:"+rs.getString("name"));
                System.out.println("余额:"+rs.getBigDecimal("money"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils_C3P0.release(conn,ps,rs);
        }
    }
}