1、初识MySQL
-
JavaEE:企业级Java开发 Web
-
前端(页面:展示,数据!)
-
后台(连接点:连接数据库JDBC,连接前端(控制,控制视图跳转,和给前端传递数据))
-
数据库(存数据,txt,excel,world)
第一类人:只会写代码,学好数据库,基本混饭吃!
第二类人:会操作系统,数据结构与算法!当一个不错的程序猿!
第三类人:离散数学,数字电路,体系结构,编译原理。+实战经验,高级程序猿~优秀的程序猿
1.1、为什么学习数据库
-
岗位需求
-
现在的世界里,大数据时代~,得数据库者得天下。
-
被迫需求:存数据
-
数据库是所有软件体系中最核心的存在 DBA
1.2、什么是数据库
数据库(DB,DataBase)
-
概念:数据仓库,软件,安装在操作系统(window,linux,mac,....)之上!
-
SQL 可以存储大量的数据 500万以下,以上要优化
-
作用:存储数据,管理数据
1.3、数据库分类
关系型数据库:(SQL)
-
MySQL,Oracle,Sql Server,DB2,SQLlite
-
通过表和表之间,行和列之间的关系进行数据存储, 学员信息表,考勤表,.....
非关系型数据库:(NoSQL)Not Only SQL
-
Redis,MongDB
-
非关系型数据库
1.4、MySQL简介
-
MySQL是一个关系型数据库管理系统
-
前世:瑞典MySQL AB 公司
今生:属于Oracle旗下产品
-
MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一
-
开源的数据库软件~
-
体积小、速度快、总体拥有成本低,招人成本低,所有人必须会~
-
中小型网站、或者大型网站,集群!
安装建议:
-
尽量不要使用exe,(卸载麻烦,会写注册表)
-
尽可能使用压缩包
1.5、安装MySQL
-
下载后得到zip压缩包
-
解压到自己想要安装到的目录。本人解压到的是D:\Environment\mysql-8.0.31
-
添加环境变量
-
我的电脑->属性->高级->环境变量
-
选择PATH,在起后面添加:你的mysql安装文件下面的bin文件夹
-
在D:\Environment\mysql-8.0.31下新建my.ini文件
-
编辑my.ini文件,注意替换文件路径
[mysqld]
basedir:D:\Environment\mysql-8.0.31\
datadir=D:\Environment\mysql-8.0.31\data
post=3306 -
启动管理员模式下的CMD,并将路径切换至mysql下的bin目录,再输入mysql -install(安装mysql)
-
再输入
mysqld --initialize-insecure --user=mysql
初始化数据文件 -
然后再次启动mysql,然后命令mysql -u root -p进入mysql管理界面(密码为空)
-
更改root密码
-
user mysql;
-
select host,user,plugin from user;
-
update user set plugin='mysql_native_password' where user='root';
-
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
-
update mysql.user set host='%' where user='root';
-
flush privileges; 刷新权限
-
为什么还要使用mysql_native_password? 虽然最新版MySql换了新的身份验证插件(caching_sha2_password), 原来的身份验证插件为(mysql_native_password)。但是一些客户端工具比如Navicat 中还找不到新的身份验证插件(caching_sha2_password),因此,我们需要将mysql用户使用的 登录密码加密规则 还原成 mysql_native_password,方便客户端工具连接使用。
-
-
重启mysql即可正常使用
net stop mysql
net start mysql -
连接上出现以下结果就安装好了
-
1.6、安装SQLyog
-
无脑安装
-
注册
-
打开连接数据库
-
新建一个数据库school
每一个sqlyog的执行操作,本质就是对应了有个sql,可以再(工具)->(历史记录)查看
-
新建一张表student
字段:id,name,age
-
查看表
-
添加记录
1.7、连接数据库
-
命令行连接!
mysql -uroot -p123456 --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost'; --修改用户名密码
flush privileges; --刷新权限
------------------------------
--所有的语句都使用;结尾
show database; --查看所有的数据库
mysql> use school --切换数据库 use 数据库名
Database changed
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有表的信息
-- Ctrl+c
create database westos; --创建一个数据库
exit; --退出连接
-- 单行注释(sql本来的注释)
/*
多行注释
*/
-
数据库 xxx 语言 CRUD 增删改查! CV程序猿 API程序猿 CRUD程序猿!(业务)
DDL 定义
DML 操作
DQL 查询
DCL 控制
2、操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
myql关键字不区分大小写
2.1、操作数据库
1、创建数据库
CREATE DATABASE [IF NOT EXISTS] weston
[ ] 可选
{ } 必选
2、删除数据库
DROP DATABASE [IF EXISTS] weston
3、使用数据库
--tab键的上面,如果你的表名或者字段名是有个特殊字符,就需要带``
USE `school`
4、查看数据库
SHOW DATABASES --查看所有的数据库
2.2、数据类型
数值
数据类型 | 表示的范围 | 字节数 |
---|---|---|
tinyint | 十分小的数 | 1个字节 |
smallint | 较小的数据 | 2个字节 |
mediemint | 中等大小的数据 | 3个字节 |
int(常用的) | 标准的整数 | 4个字节 |
bigint | 较大的数据 | 8个字节 |
float | 浮点数 | 4个字节 |
double(精度问题) | 浮点数 | 8个问题 |
decimal(金融计算) | 字符串形式的浮点数 |
字符串
数据类型 | 表示范围 | 取值范围 |
---|---|---|
char | 字符串固定大小的 | 0~255 |
varchar(常用变量) | 可变字符串 | 0~65535 |
tinytext | 微型文本 | 2^8-1 |
text(保存文本) | 文本串 | 2^16-1 |
时间日期
数据类型 | 格式 | 用途 |
---|---|---|
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填充的
-
不足位数,使用0来填充,int(3),5 -----> 005
auto-increment自增
-
通常理解为自增,自动在上一条记录的基础上+1(默认)
-
通常用来设计唯一的主键~index,必须是整数类型
-
可以自定义设计主键自增的起始和步长
非空 NULL not null
-
假设设置为not null,如果不给它赋值,就会报错!
-
NULL,如果不填写值,默认就是null!
默认
-
设置默认的值!
-
sex,默认值为 男,如果不指定该列的值,则会有默认的值!
拓展
/*每一个表,都必须存在以下五个字段!未来做项目用,表示一个记录存在意义
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
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 '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
......
PRIMARY KEY('id')
)[表类型][字符集设置][s]
常用命令
SHOW CREATE DATABASE school /*查看创建数据库的语句*/
SHOW CREATE TABLE student /*查看student数据表的定义语句*/
DESC student /*显示表的结构*/
2.5、数据表的类型
--关于数据引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
INNODB | MYISAM | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
常规使用操作:
-
MYISAM 节约空间,速度较快
-
INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹对应一个数据库
本质还是文件存储
MYSQL引擎在物理文件上区别
-
INNODB在数据库表中只有*.frm文件,以及上级目录下的ibdata1文件
-
MYISAM对应文件对应文件
-
*.frm -表结构对应的定义文件
-
*.MYD 数据文件(data)
-
*.MYI 索引文件(index)
-
设置数据库的字符集编码
CHARSET =utf8
不设置的话,会是mysql默认的字符集编码~(不支持中文)
MySQL的默认编码是Latin1,不支持中文
在my.ini中配置默认的编码
character-set-sever=
2.6、修改删除表
修改
-- 修改表名:ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段:ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段(重命名,修改约束!)
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) --修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1) --字段重命名
-- 删除表的字段:ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1
删除
-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS teacher1
所有的创建和删除操作尽量加上判断,以免报错~
注意点:
-
`` 字段名用这个来包裹
-
注释 -- /**/
-
sql关键字的小写不敏感,建议大家写小写
-
所有符号用英文
3、MySQL数据管理
3.1、外键
方式一、在创建表的时候,增加约束(麻烦,比较复杂)
CREATE TABLE IF NOT EXISTS`grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4
/*
学生表的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 '家庭住址',
`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=utf8mb4
删除有外键关系的表的时候,必须先删除引用了别人的表(从表),再删除被引用的表(主表)
方式二、创建表成功后,添加外键约束
CREATE TABLE IF NOT EXISTS`grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4
/*
学生表的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 '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4
/*
创建表的时间没有外键关系
*/
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCE `grade`(`gradeid`);
/*
alter table 表
add constraint 约束名 foreign key(作为外键的列) reference 哪个表(哪个字段);
*/
以上的操作都是物理外键,数据库级别的外键,我们不建议使用!(避免数据库过多造成困扰,了解即可)
最佳实现
-
数据库就是单纯的表,只用来存数据,有行(数据)和列(字段)
-
我们想使用多张表的数据,想使用外键(程序去实现)
3.2、DML语言
数据库意义:数据存储,数据管理
DML语言:数据操作语言
-
insert
-
update
-
delete
3.3、添加
/*
插入语句(添加)
insert into 表名([字段1,字段2,字段三]) values(‘值1’),('值2'),(‘值3’)....
*/
/*
一般插入语句,我们一定要数据和字段一一对应
插入多个字段
*/
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES(1,'大一')
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES(2,'大二')
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES(3,'大三')
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES(4,'大四')
INSERT INTO `student`(`id`,`name`,`pwd`,`sex`,`birthday`,`gradeid`,`address`,`email`) VALUES('1','xie','asdfg','男','2000-12-22','3','江西','[email protected]')
语法:insert into 表名([字段1,字段2,字段三]) values(‘值1’),('值2'),(‘值3’,...)
注意事项:
-
字段和字段之间使用英文逗号隔开
-
字段和面的值必须要一一对应
-
多个设置的属性之间,使用英文逗号隔开
3.4、修改
update 修改谁 (条件) set原来的值=新值
/*
修改学生姓名,带了简介
*/
UPDATE `student` SET `name`= '狂神' WHERE id=1;
/*
不指定条件的情况下,会改动所有的表
*/
UPDATE `student` SET `name`='kuangshen'
/*
修改多个属性,逗号隔开
*/
UPDATE `student` SET `name`='狂神',`email`='[email protected]' WHERE id = 2
语法:
update 表名 set colnum_name = value,[colnum_name = value,...] where [条件]
条件: where子句 运算符 id等于某个值,大于某个值,在某个区间内修改
操作符返回布尔值
-
= 等于
-
<> 或 != 不等于
-
between...and... 在某个范围内
-
and 我和你&&
-
or 我或你||
3.5、删除
delete命令
语法:delete from 表名 [where 条件]
-- 删除数据(避免这样,会全部删除)
delete from `student`
-- 删除指定数据
delete from `student`where id = 1;
TRUNCATE命令
作用:完全清空一个数据库表,表的结构和索引约束不会变
-- 清空student表
TRUNCATE `student`
delete 和 TRUNCATE 的区别
-
相同点:都能删除数据,都不会删除表结构
-
不同:
-
TRUNCATE 重新设置自增列计数器会归零
-
TRUNCATE 不会影响事务
-
--测试delete 和 TRUNCATE 的区别
create table `test`(
`id` int(4) not null auto_increment,
`coll` varchar(20) not null,
primary key (`id`)
)engine=innodb default charset=utf8mb4
insert into `test`(`id`,`coll`) values(1,`1`);
insert into `test`(`id`,`coll`) values(2,`3`);
insert into `test`(`id`,`coll`) values(3,`3`);
delete from `test`
truncate table `test`
了解即可:delete删除的问题,重启数据库,现象
-
InnoDB 自增列会重1开始(存在内存当中的,断电即失)
-
MyISAM 继续从上一个子增量开始(存在文件中的,不会丢失)
4、DQL查询数据(*)
4.1、DQL
(Data Query Language:数据查询语言)
-
所有的查询操作都在用它 select
-
简单的查询,复杂的查询它都能做~
-
数据库中最核心的语言,最重要的语句
-
使用频率最高的语句
select语法
SELECT [ALL | DISTINCT]
{* | table.* | [table.field[as alias1][,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 `StudentNO`,`StudentName` from student
--别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
select `StudentNo` as 学号,`StudentName` as 学生姓名 from student as s
-- 函数 concat(a,b)
select concat('姓名:',StudentName) as 新名字 from student
语法:select 字段,.... from 表
有的时候,列名字不是那么的见名知意。我们起别名 AS
字段名 as 别名 表名 as 别名
去重 distinct
作用:去除select语句查询出来的结果中重复的数据,重复的数据只显示一条
-- 查询一下有哪些同学参加了考试
selcet * from result -- 查询全部的考试成绩
select `StudentNo` from result -- 查询有哪些同学参加了考试
select distinct `StudentNo` from result -- 发现重复数据,去重
数据库的列(表达式)
select version() -- 查询系统版本(函数)
select 100*3-1 as 计算结果 -- 用来计算(表达式)
select @@auto_increment_increment --查询自增的步长(变量)
-- 学员考试成绩+1分 查看
select `StudentNo`,`StudentResult`+1 as '提分后' from result
数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量....
select 表达式 from 表
4.3、where条件子句
作用:检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成!结果 返回布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个为真,结果为真 |
or || | a or b a||b | 逻辑或,其中一个为真则结果为真 |
not ! | not a !a | 逻辑非,真为假,假为真! |
尽量使用英文字母
-- where
select StudentNo,`StudentResult` from reslut
-- 查询考试成绩在95~100分之间
select StudentNo,`StudentResult` from reslut
where StudentResult>=95 and StudentResult<=100
-- and &&
select StudentNo,`StudentResult` from reslut
where StudentResult>=95 && StudentResult<100
-- 模糊查询(区别)
select StudentNo,`StudentResult` from reslut
where StudentResult between 95 and 100
-- 除了1000号学生之外的同学成绩
select StudentNo,`StudentResult` from reslut
where StudentNo != 1000;
-- != not
select StudentNo,`StudentResult` from reslut
where not StudentNo = 1000
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,结果为真 |
is not nul | a is not null | 如果操作符不为null,结果为真 |
between | a between b and c | 若a在b和c之间,则结果为真 |
like | a like b | SQL匹配,如果a匹配b,则结果为真 |
in | a in (a1,a2,a3...) | 假设a在某一集合中,结果为真 |
/*
模糊查询
查询姓刘的同学
like结合 %(代表0到任意个字符) _(一个字符)
*/
select `StudentNo`,`StudentName` from `studnet`
where StudentName like '刘%'
--查询姓刘的同学,名字后面只有一个字的
select `StudentNo`,`StudentName` from `studnet`
where StudentName like '刘_'
--查询姓刘的同学,名字后面只有2个字的
select `StudentNo`,`StudentName` from `studnet`
where StudentName like '刘__'
-- =======in=======
--查询1001,1002,1003号学员
select `StudentNo`,`StudentName` from `studnet`
where StudentNo in (1001,1002,1003);
-- 查询在北京、上海、南京的学生
select `StudentNo`,`StudentName` from `studnet`
where `Adress` in ('北京','上海','南京');
--=============null 、not null==============
--查询地址为空的学生 null
select `StudentNo`,`StudentName` from `studnet`
where adress='' or adress is null
--查询由出生日期的同学 不为空
select `StudentNo`,`StudentName` from `studnet`
where `BornDate` is not null
4.4、联表查询
JOIN 对比
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
--=========== 联表查询 join =================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
/*思路
1.分析需求,分析查询的字段来自哪些表,(连接查询)
2.确定使用哪种连接查询? 7种
确定交叉点(这两个表中哪个数据是共同的)
判断条件:学生表中studnetNo = 成绩表 studnetNo
*/
--join(连接的表) on(判断条件) 连接查询
--where 等值查询
select s.StudentNo,StudentName,SubjectNo,StudentResult
from student as s
inner join result as r
where s.StudentNo = r.StudentNo
--Right join
select s.StudentNo,StudentName,SubjectNo,StudentResult
from student s
right join result r
on s.StudentNo=r.StudentNo
--left join
select s.StudentNo,StudentName,SubjectNo,StudentResult
from student s
left join result r
on s.StudentNo=r.StudentNo
-- 查询缺考的同学
select s.StudentNo,StudentName,SubjectNo,StudentResult
from student s
left join result r
on s.StudentNo=r.StudentNo
where StudentResult is null
/*思考题(查询参加考试的同学信息:学号,学生姓名,科目名,分数)
思路
1.分析需求,分析查询的字段来自哪些表,student,result,subject(连接查询)
2.确定使用哪种连接查询? 7种
确定交叉点(这两个表中哪个数据是共同的)
判断条件:学生表中studnetNo = 成绩表 studnetNo
*/
select s.StudentNo,StudnetName,SubjectName,StudnetResult
from student s
right join result r
on r.StudentNo = s.StudentNo
inner join `subject` sub
on r.SubjectNo = sub.SubjectNo
要查询哪些数据 select...
从哪几个表中查 from 表 xxx join 连接的表 on交叉条件
假设存在一种多张表查询,先查两张表然后一个一个增加
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
-- 查询父子信息:把一张表看为两个一模一样的表
select a.`categoryName` as '父栏目',b.`categoryName` as '子栏目'
from 'category' as a,'category' as b
where a.'categoryid' = b.'pid'
-- 练习
-- 查询学员所属的年级(学号,学生姓名,年级名称)
select studentNo,studentName,GradeName
from student s
inner join 'grade' g
on s.'GradeID' = g."GradeID"
-- 查询科目所属的年级(科目名称,年级名称)
select 'SubjectName','GradeName'
from 'subject' sub
inner join 'grade' g
on sub.'GradeID' = g.'GradeID'
-- 查询参加了 数据库结构-1 考试的同学信息:学号,学生姓名,科目名,分数
select s.'StudentNo','StudentName','SubjectName','StudentResult'
from student s
inner join 'result'r
on s.StudnetNo = r.StudentNo
inner join 'subject' sub
on r.'SubjectNo' = sub.'SubjectNo'
where sbujectName = '数据库结构-1'
4.5、分页和排序
排序
-- 排序:升序 ASC , 降序 DESC
-- ORDER BY 通过哪个字段,怎么排
-- 查询的结果根据 成绩降序 排序
select s.'StudentNo','StudentName','SubjectName','StudentResult'
from student s
inner join 'result'r
on s.StudnetNo = r.StudentNo
inner join 'subject' sub
on r.'SubjectNo' = sub.'SubjectNo'
where sbujectName = '数据库结构-1'
order by StudentReslt ASC
分页
-- 为什么要分页?
-- 缓解数据库压力,给人的体验更好,瀑布流
-- 分页,每页只显示五条数据
-- 语法:limit 起始页,页面大小
-- limit 0,5 1~5 (1-1)*5 第一页
-- limit 5,5 (2-1)*5 第二页
-- 起始页:(n-1)*pageSize 第n页
-- 参数【pageSize ()*pageSize n 数据总数/页面大小=总页数】
select s.'StudentNo','StudentName','SubjectName','StudentResult'
from student s
inner join 'result'r
on s.StudnetNo = r.StudentNo
inner join 'subject' sub
on r.'SubjectNo' = sub.'SubjectNo'
where sbujectName = '数据库结构-1'
order by StudentReult ASC
limit 0,5
语法:linit (查询起始下标,pageSize)
-- 练习
-- 查询 JAVA第一学年,课程成绩排名前十的学生,并且分数要求大于80的学生信息(学号,姓名,课程名称,分数)
select s.StudentNo,StudentName,SubjectName,StudentResult
from student s
inner join result r
on s.StudentNo = r.StudentNo
inner join `subject` sub
on sub.SubjectNo = r.SubjectNo
where SubjectName = 'JAVA第一学年' and StudentResult>=80
order by StudentResult DESC
limit 0,10
4.6、子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),降序排序
-- 方式一: 使用连接查询
select `StudentNo`,r.`SubjectNo`,`StudentResult`
from `result` r
inner join `subject` sub
on r.SubjectNo = sub.SubjectNo
where SubjectName = '数据结构-1'
order by StudentResult DESC
-- 方式二:使用子查询(由里及外)
select `StudentNo`,`SubjectNo`,`StudentResult`
from `result`
where SubjectNo = (
select SubjectNo from `subject`
where SubjectName = '数据库结构-1'
)
-- 分数不小于80分的学生的学号和姓名
select distinct s.`StudentNo`,`StudnetName`
from student s
innerr join result r
on r.StudentNo=s.StudentNo
where `StudentResult`>=80
--在这个基础上增加一个科目, 高等数学-2
select distinct s.`StudentNo`,`StudentName`
from student s
inner join result r
on r.StudentNo = s.StudentNo
where `StudentResult`>=80 and `SubjectNo` =(
select `SubjectNo` from `subject`
where `SubjectName` = '高等数学-2'
)
-- 查询课程为 高等数学-2 且分数不小于 80 的同学学号、姓名(由里及外)
select StudentNo,StudentName from student where StudentNo in(
select StudentNo from result where StudentResult>80 and SubjectNo =(
select SubjectNo from `subject` where `SubjectName` = `高等数学-2`
)
)
-- 练习:查询 C语言-1 前5名同学的成绩信息(学号、姓名、分数)
-- 使用子查询
4.7、分组过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:(根据不同的课程分组)
select SubjectName,AVG(StudentResult) as 平均分,MAX(StudentResult) as 最高分,MIN(StudentResult) as 最低分
from result r
inner join `subject` sub
or r.`SubjectNo` = sub.`SubjectNo`
group by r.SubjectNo -- 通过什么字段来分组
having 平均分>80
5、MySQL函数
5.1、常用函数
-- 数学运算
select abs(-8) -- 绝对值
select ceiling(9.4) -- 向上取整
select floor(9.4) --向下取整
select rand() -- 返回一个 0~1 之间的随机数
select sign(10) -- 判断一个数的符号 0-0 负数 - -1 正数 - 1
-- 字符串函数
select char_length('asfgdsg') -- 字符串长度
select contat('sdf','shg','hui') -- 拼接字符串
select insert('我爱编程helloworld',1,2,'超级热爱') -- 查询从某个位置开始替换某个长度
select lower('Kuangshen') -- 小写字母
select upper('Kuangshen') -- 大写字母
select instr('Kuangshen','h') -- 返回第一次出现的字串索引
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 system_user()
select user()
select version()
5.2、聚合函数(常用)
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
-- ========== 聚合函数 ============
-- 都能够统计 表中的数据 (想查询一个表中由多少记录,就用这个count())
select count('BornDate') from student; -- count(字段),会忽略所有null值
select count(*) from student; -- count(*),不会忽略null值,本质计算行数
select count(1) from result; -- count(1),不会忽略所有的null值, 本质 计算行数
5.3、数据库级别的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=utf8mb4
-- 明文密码
insert into testmd5 values(1,'zhangsan','123456')
-- 加密
update testmd5 set pwd=MD5(pwd) where id = 1
update testmd5 set pwd=MD5(pwd) -- 加密全部
-- 插入时加密
insert into testmd5 values(2,'xiaoming',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行MD5加密,然后对比加密后的值
select * from testmd5 where `name`='xiaoming' and pwd=MD5('123456')
6、事务
6.1、什么是事务
要么成功,要么失败
1、SQL执行 A给B转账 A 1000 ---->200 B 200
2、SQL执行 B收到A的钱 A 800 ---->B 400
将一组SQL放在一个批次中去执行~
事务原则:ACID原则 原子性,一致性,隔离性,持久性 (脏读,幻读...)
参考博客链接:(17条消息) 事务ACID理解dengjili的博客-CSDN博客事务acid
原子性(Atomicity)
要么成功,要么失败
一致性(Consistency)
事务前后的数据完整性要保证一致
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离
持久性(Durability)
事务一旦提交则不可逆,被持久化到数据库中!
隔离所导致的一些问题
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。
执行事务
-- mysql 是默认开启事务自动提交的
set autocommit = 0 /* 关闭 */
set autocommit = 1 /* 开启 */
-- 手动处理事务
-- 事务开启
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`)
)engine=innodb default charset=utf8
insert into account(`name`,`money`) values ('A',2000.00)
insert into account(`name`,`money`) values ('B',10000.00)
-- 模拟转账: 事务
set autocommit = 0; -- 关闭自动提交
start transaction -- 开启一个事务(一组事务)
update accoount set money =money-500 where `name` = 'A' -- A减500
update accoount 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 `studentName`(`studentName`);
-- explain 分析sql执行情况
explain select * from student; -- 非全文索引
explain select * from student where match(studnetName) against('刘')
7.2、测试索引
create table `app_user` (
`id` bigint(20) unsigned not null auto_increment,
`name` varchar(50) default '' comment '用户昵称',
`email` varchar(50) not null comment '用户邮箱',
`phone` carchar(20) default '' comment '手机号',
`gender` tinyint(4) unsigned default '0' comment '性别(0:男;1:女)',
`password` varchar(100) not null comment '密码',
`age` tinyint(4) 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用户表'
-- 插入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`)
values(concat('用户',i),'[email protected]',concat('18',floor(rand()*((999999999-100000000)+100000000))),floor(rand()*2),uuid(),floor(rand()*100));
set i = i + 1;
end while
return i;
end;
explain select * from app_user where `name`='用户9999'; -- 0.993 sec(查询了991749 rows)
select * from student; -- 对比
- id_表名_字段名
-- create index 索引名 on 表(字段)
create index id_app_user on app_user(`name`);
explain select * from app_user where `name`='用户9999'; -- 0.001 sec(查询了1 row)
索引在小数据量的时候,用处不大,但是在大数据的时候,区别十分明显
7.3、索引原则
-
索引不是越多越好
-
不要对进程变动数据加索引
-
小数据量的表不要加索引、
-
索引一般一般加在常用来查询的字段上
索引的数据结构
Hash类型的索引
Btree:InnoDB的默认数据结构
正常的业务代码
8、权限管理和备份
8.1、用户管理
SQL yog可视化管理
SQL命令操作
用户表:mysql.user
本质:对这张表进行增删改查
-- 创建用户 create user 用户名 identified by '密码'
create user kagnshen identified by '123456'
-- 修改密码(修改当前用户密码)
set password = password('123456')
-- (修改指定用户密码)
set password for kuangshen = password('123456')
-- 重命名 rename user 原来名字 to 新名字
rename user kuangshen to kuangshen2
-- 用户授权 all privileges 全部权限 ,库.表
-- all privileges 除了给别人授权,其它的都能干
grant all privileges on *.* to kuagnshen2
-- 查询权限
show grants for kuangshen2 -- 查看指定用户的权限
show grants foe root@localhost -- 查看root权限
-- root用户权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 revoke 哪些权限,在哪个库撤销,给谁撤销
revoke all privileges on *.* from kuangshen2
-- 删除用户
drop user kuangshen
8.2、MySQL备份
为什么要备份:
-
保证重要的数据不丢失
-
数据转移
MySQL数据库备份的方式
-
直接拷贝物理文件
-
在SQLyog这种可视化工具中手动导出
-
在想要导出的表或库中,右键,选择备份或导出
-
-
使用命令行导出
mysqldump
命令行使用
# mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 student result >D:/b.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql
# 导入
# 登录的情况下,切换到指定的数据库
# source 备份文件
source d:/a.sql
mysql -u用户名 -p密码 库名< 备份文件
9、规范数据库设计
9.1、为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
-
数据冗余,浪费空间
-
数据库插入和删除忽麻烦、异常【屏蔽使用物理外键】
-
程序的性能差
良好的数据库设计:
-
节省内存空间
-
保证数据库的完整性
-
方便我们开发系统
软件开发中,关于数据库的设计
-
分析需求:分析业务和需求处理数据库的需求
-
概要设计:设计关系图E-R图
设计数据库的步骤:(个人博客)
-
收集信息,分析需求
-
分类表(用户登录注销,用户的个人信息,写博客,创建分类)
-
分类表(文章分类,谁创建的)
-
文章表(文章的信息)
-
评论表
-
友链表(友链信息)
-
自定义表(系统信息,某个关键字,或者一些字段) key :value
-
说说表(发表心情.. id...content...creat_time)
-
-
标识实体 (把需求落到每个字段)
-
标识之间的关系
-
写博客:user --> blog
-
创建分类:user -->category
-
关注:user -->user
-
友链:links
-
评论:user -->user -->blog
-
为什么需要数据规范化?
-
信息重复
-
更新异常
-
插入异常
-
无法正常显示信息
-
-
删除异常
-
丢失有效的信息
-
9.2、三大范式
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事
第三范式(3NF)
前提:满足第一范式和第二范式
第三范式需要确保数据表中每一列数据都和主键直接相关,而不能间接相关
(规范数据库的设计)
规范性 和 性能的问题
关联查询的表不得超过三张表
-
考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要
-
在规范性能问题的时候,需要适当的考虑一下 规范性!
-
故意给某些表增加一些冗余的字段。(从多表查询变为单表查询)
-
故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
10、JDBC(重点)
10.1、数据库驱动
驱动:声卡、显卡、数据库
我们的程序会通过数据库驱动,和数据库打交道!
10.2、JDBC
SUN公司为简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,俗称 JDBC
这些规范的实现由具体的厂商去做~
对于开发人员来说,我们只需要掌握JDBC接口的操作即可
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-8.0.30.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','[email protected]','1980-12-04');
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(2,'lisi','123456','[email protected]','1981-12-04');
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(3,'wangwu','123456','[email protected]','1979-12-04');
SELECT * FROM users
1、创建一个普通i项目
2、导入数据库驱动
3、编写测试代码
//我的第一个JDBC程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1. 加载驱动
// Class.forName("com.mysql.jdbc.Driver"); //固定写法
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 用户信息和url
//useUnicode=true&characterEncoding=utf8&useSSL=true
//支持中文编码 设置中文字符集 安全连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
//3. 连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL对象
Statement statement = connection.createStatement();
//5. 用SQL对象去执行SQL,可能存在结果,查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql); // 结果集中封装了我们全部的查询出来的结果
while (resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("NAME"));
System.out.println("pwd="+resultSet.getObject("PASSword"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birth="+resultSet.getObject("birthday"));
System.out.println("=================================");
}
// 6. 释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
1、加载驱动
2、连接数据库 DriverManager
3、获得执行sql的对象 Statement
4、获得返回的结果集
5、释放连接
DriverManager
// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动
// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
//useUnicode=true&characterEncoding=utf8&useSSL=true
//支持中文编码 设置中文字符集 安全连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
// mysql -- 3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
// oralce -- 1521
// jdbc:oracle:thin:@localhost:1521:sid
Statement执行SQL的对象 PrepareStament 执行SQL的对象
String sql = "SELECT * FROM users"; // 编写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对象
Jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象
CRUD操作-create
使用executeUpdate(String sql) 方法完成数据库添加操作,示例操作:
Statement st = conn.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!");
}
CRUD操作-delete
使用executeUpdate(String sql) 方法完成数据库删除操作,示例操作:
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
使用executeUpdate(String sql) 方法完成数据库修改操作,示例操作:
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
使用executeQuery(String sql) 方法完成数据查询操作,示例操作:
Statement st = conn.createStatement();
String sql = "select * from users where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
// 根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}
代码实现
1、提取工具类
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
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(4,'kuagnshen','123456','[email protected]','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);
}
}
}
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 =4";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
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`='kuangshen' WHERE id=1";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("更新成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、查询executeQuery
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 =1";
rs = st.executeQuery(sql); //查询完会返回一个结果集
while (rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("email"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入的问题
sql存在漏洞,会被攻击导致数据泄露 SQL会被拼接 or
// sql注入
public class SqlInjection {
public static void main(String[] args) {
// login("kuangshen","123456");
login(" 'or '1=1"," '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`='kuangshen' AND `password`= '123456'
//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("email"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.5、PreparedStatement对象
PreparedStatement可以防止SQL注入,效率更好!
1、新增
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = 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,4);
st.setString(2,"xie");
st.setString(3,"123456");
st.setString(4,"[email protected]");
// 注意点:数据库的Date和util包下的Date不一样 获取时间戳
st.setDate(5,new Date(new java.util.Date().getTime()));
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
2、删除
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);// 预编译SQL,先写SQL,然后不执行
//手动给参数赋值
st.setInt(1,4);
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
3、更新
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);// 预编译SQL,先写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、查询
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=?";
st = conn.prepareStatement(sql);// 预编译SQL,先写SQL,然后不执行
//手动给参数赋值
st.setInt(1,1);
//执行
rs = st.executeQuery();
while (rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
5、防止SQL注入
public class SqlInjection {
public static void main(String[] args) {
// login("kuangshen","123456");
login(" 'or '1=1"," 'or '1=1"); //技巧
// login("lisi","123456");
}
// 登录业务
public static void login(String username,String password){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//SQL
//SELECT * FROM users WHERE `name`='kuangshen' AND `password`= '123456'
//SELECT * FROM users WHERE `name`=''or'1=1' AND `password`= ''or'1=1'
String sql = "select * from users where `name`=? and `password`=?";
//PreparedStatement 防止SQL注入的本质,把传递进来的参数当作字符
//假设其中存在转义字符,比如说 ` 会被直接转义
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("email"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.6、IDEA连接数据库
双击数据库,查看
更新数据
10.7、事务
要么成功要么失败
ACID原则
原子性:要么全部完成,要么全部都不完成
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆,持久化到数据库了
隔离性问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表数据发生了变化
虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
代码实现
1、开启事务conn.setAutoCommit(false);
2、一组业务执行完毕,提交事务
3、可以在catch语句中显示地定义回滚语句,但默认失败就会回滚
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
最大连接数:100 业务最高承载上限
等待超时:100ms
编写连接池,实现一个接口 DataSource
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库连接池之后,我们在项目开发中就不需要写连接数据库的代码了
DBCP
需要用到jar包
commons-dbcp-1.4、commons-pool-1.6
C3P0
需要用到的jar包
c3p0-0.9.5.5、mchange-commons-java-0.2.19
结论
无论使用什么数据源,本质还是一样的,DataSource接口不会变,方法就不会变
Druid
Apache
标签:--,数据库,st,conn,MySQL,狂神,null,select From: https://www.cnblogs.com/cxzgemc/p/16844002.html