Mysql索引学习笔记
1.初始数MySQL
1.1什么是数据库
数据库(DB,DataBase)
概念:数据仓库,软件,SQL,可以存储大量数据
作用:存储数据,管理数据
1.2数据库分类
关系型数据库
- MySQL、Oracle、SQL sever、DB2、SQLLite
- 通过表和表之间的关系，行和列的关系进行数据的存储
非关系型数据库
- Redis、MongoDB
- 非关系型数据库，对象存储通过对象自身的属性来决定
DBMS（数据库管理系统）
- 数据库的管理软件,科学有效的管理我们的数据,维护和获取数据
- MySQL, 数据库管理系统!
1.3下载MySQL
操作步骤
- 下载得到zip压缩包
- 解压到自己想要安装到的目录,我是:D:\software\mysql-8.0.26\bin
- 添加环境变量
- 新建MySQL配置文件(重点)
点进my.ini中,将以下代码打进去
提示:
1. **basedir = 你自己mysql安装的目录**
2. **datadir = basedir+data\ **
[mysqld]
basedir = D:\software\mysql-8.0.26\
datadir = D:\software\mysql-8.0.26\data\
port = 3306
skip-grant-tables
-
启动管理员模式下的CMD，并将路径切换到mysql下的bin目录，然后输入mysqld -install（安装mysql）
-
再输入 初始化数据
- 假如没有报错，会在D:\software\mysql-8.0.26\目录下创建一个data文件夹
2. 假如出现报错
5. 则将my.ini里面的内容改为
[mysqld]
basedir = D:\\software\\mysql-8.0.26
datadir = D:\\software\\mysql-8.0.26\\data
port = 3306
skip-grant-tables
-
启动mysql，进去修改密码（mysql -u root -p）
- 在命令行输入 net start mysql 启动服务器
问题1：当你输入完，返回 ‘net’ 不是内部或外部命令，也不是可运行的程序 或批处理文件。
解决办法：在系统变量的Path中添加 C:\windows\system32，即可
-
启动关闭mysql
- net stop mysql
- net start mysql
1.4使用数据库
- 新建一个数据库school
- 新建一张表student
字段：id，name，age
1.5连接数据库
命令行连接
mysql -uroot -p123456 --连接数据库
-------------------------------------
--所以的语句以;结尾
show databases; --查看所以的数据库
mysql> use school --切换数据库 use 数据库名
Datebase changed
show tables; --查看数据库中所有表
describe student; --查看数据库中student表信息
create database westos;--创建westos数据库
exit; --退出连接
-- 单行注释
/* 多行注释 */
数据库xxx语言 CRUD 增删改查
DDL 定义
DML 操作
DQL 查询
DCL 控制
2.操作数据库
操作数据库>操作数据库中的表>操作数据库中标的数据
mysql关键字不区分大小写
2.1操作数据库（了解）
1.创建数据库
create DATABASE [if NOT EXISTS] west;
2.删除数据库
DROP DATABASE 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 | 字符串类型的浮点数 |
字符串
| 字符串 | ||
|---|---|---|
| char | 字符串固定大小的 | 0~255 |
| varchar（常用） | 可变字符串 | 0~65535 |
| tinytext | 微型文本 | 2^8 -1 |
| text | 文本串 | 2^16-1 |
时间日期
| java.util.Date | ||
|---|---|---|
| date | YYYY-MM-DD | 日期 |
| time | HH:mm：ss | 时间格式 |
| datetime | YYYY-MM-DD HH:mm：ss | 最常用的时间格式 |
| timestamp | 时间戳，1970.1.1到现在的毫秒数 | |
| year | 年份表示 |
null
- 没有值，未知
- 注意，不能使用NULL进行运算，结果为NULL
2.3数据库的字段属性==（重点）==
Unsigned:(无符号)
- 无符号的整数
- 声明了该列不能为负数
zerofill:
- 0填充
- 不足的位数没用0来填充，int（3），5 —> 005
自增:
- 通常理解为自增，自动在上一条的记录上+1（默认)
- 通常用来设计唯一的主键，必须是整数类型
- 可以自定义设置主键自增的起始值和步长
在Navicat中设置主键自增的起始值和步长
非空：NULL not null
- 假设设置为not null，如果不给他赋值，就会报错
- NULL，如果不填写值，默认为null
默认:
- 设置默认的值
- sex，默认值为男，如果不指定该列的值，则会用默认的值
2.4创建数据库表(重点)
-- 目标: 创建一个school数据库
-- 创建学生表(列,字段) 使用SQL 创建
-- 学号int 登录密码varchar(20) 姓名,性别,生日(datetime),家庭住址,email
-- 注意点:使用英文(),标的名称 和字段 尽量使用``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用,单引号括起来!
-- 所有的语句后面+,(英文的),最后一个语句可以不加
-- PRIMARY KEY 主键,一般一个表只有一个唯一的主键
CREATE TABLE IF NOT EXISTS `student` (
`id` INT ( 4 ) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(15) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`adress` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
);ENGINE=INNODB DEFAULT CHARSET("utf-8");
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列表型 [属性] [索引] [注释],
`字段名` 列表型 [属性] [索引] [注释],
......
`字段名` 列表型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE DATABASE school --查看创建数据库的语句
show create table student --查看student数据表的定义语句
DESC student --获取一张表的具体结构
2.5数据表的类型
--关于数据库引擎
/*
INNODB 默认引擎
MYISAM 早期使用
*/
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大,约为2倍MYISAM |
常规使用操作:
- MYISAM 节约空间,速度快
- INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在位置
所有的数据库文件都存在data目录下
本质还是文件的存储
MySQL引擎在物理文件上的区别
- InnoDB 在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1 文件
2.6修改删除表
修改
--修改表名 : ALTER TABLE 旧表名 RENAME as 新表名
ALTER TABLE school 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 age1
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hqQqNMXW-1659518091578)(…/JavaWeb.assets/微信截图_20210830105753.png)]
删除
--删除表(if表存在再删除)
drop table if exists teacher
所有的创建和删除操作尽量加上判断,以免报错
注意点:
- `` 字段名,使用这个包裹
- 注释: – /**/
- sql关键字大写小写不敏感,建议写小写
- 所有的符号用英文
2.7创建视图
创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。
- 查看用户是否具有创建视图的权限
创建视图需要具有 CREATE VIEW 的权限，同时应该具有查询涉及的列的 SELECT 权限。可以使用 SELECT 语句来查询这些权限信息，查询语法如下:
SELECT Select_priv, Create_view_priv FROM mysql.user WHERE user='root';
-- Select_priv 属性表示用户是否具有 SELECT 权限，Y 表示拥有 SELECT 权限，N 表示没有。
-- Create_view_priv 属性表示用户是否具有 CREATE VIEW权限； mysql.user 表示 MySQL 数据库下面的 user 表。
-- "用户名" 参数表示要查询是否拥有 DROP 权限的用户，该参数需要用单引号引起来。
【例】查询 MySOL 中，root 用户是否具有创建视图的权限，SQL语句如下:
二. 创建视图
语法如下：
CREATE [ALGORITHM={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW view_name [( column_list )]
AS SELECT_statement
[ WITH | [ CASCADED | LOCAL ] CHECK OPTION];
`ALGORITHM 是可选参数，表示视图选择的算法。ALGORTHIM参数的取值有3个，分别是UNDEFINED、MERGE和TEMPTABLE。UNDEFINED 表示 MySQL 将自动选择算法M；MERGE 表示将使用的视图语句与视图定义合并，使得视图定义的某一部分取代语句对应的部分； TEMPTABLE 表示将视图的结果存入临时表，然后用临时表来执行语句。
view_name 参数表示要创建的视图名称。
column_list 是可选参数，指定视图中各个属性的名词，默认情况下与 SELECT 语句中查询的属性相同。
SELECT_statement 参数是个完整的查询语句，表示从某个表中查出某些满足条件的记录，将这些记录导入视图中。
WITH CHECK OPTION 是可选参数，表示更新视图时要保证在该视图的权限范围之内。
CASCADED 与 LOCAL 为可选参数，CASCADED 为默认值。表示更新视图时要满足所有相关视图和表的条件；LOCAL 表示更新视图时满足该视图本身定义的条件即可.`
视图属于数据库。在默认情况下，将在当前数据库中创建新视图。要想在给定数据库中明确创建视图，创建时应将视图名称设置为 db_name.view_name。
. 在单表上创建视图
【例】在 departments 表格上创建一个名为 view_depa 的视图。
创建视图，SQL语句如下：
create view view_depa as
select dept_no,dept_name from departments;
查询视图:
select * from view_depa
2. 在多表上创建视图
create view view_add(emp_no,dept_no,salary)
as
select d.emp_no,dept_no,salary
from dept_emp as d,salaries as s
where d.emp_no = s.emp_no
3.MySQL数据管理
3.1外键(了解即可)
方式一:在创建表的时候添加约束
create table `grade`(
`gradeid` int(10) not null auto_increment comment '年级id',
`dradename` varchar(50) not null comment '年级名称',
primary key (`gradeid`)
)engine=innodb
-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用) references 引用
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(15) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`gradeid` int(10) not null comment '学生年级',
`adress` varchar(100) DEFAULT NULL COMMENT '家庭地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
key `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` foreign key (`gradeid`) references `grade`(`gradeid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3
方式二:创建表成功后,添加外键约束
-- 创建表之后添加外键关系
alter table `student` add constraint `FK_gradeid` foreign key (`gradeid`) references `grade`(`gradeid`);
--alter table 表 add constraint 约束名 foreign key (作为外键的列) references 哪个表 (哪个字段);
以上的操作都是物理外键,数据库级别的外键,我们不建议使用(了解即可 )
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
3.2DML语言(全部记住)
**数据库的意义:**数据存储,数据管理
DML语言:数据操作语言
- Insert
- update
- delete
3.3添加
insert
-- 插入语句(添加)
-- insert into 表名([字段1,字段2,字段3]) values('值1'),('值2'),('值3')...
insert into `grade`(`gradename`) value('大四')
--由于主键自增,我们可以省略 (如果不写表的字段,它就会一一匹配)
insert into `grade` value('大三') -- 会报错
--一般写插入语句,我们一定要数据和字段一一对应
----给一个字段插入多个值
insert into `grade`(`gradename`) values('大一'),('大二')
--插入的数据如果存在则忽略,用 IGNORE
insert ignore into `grade`(`gradename`) values('大一'),('大二')
insert into `student`(`name`) value('张三')
insert into `student` (`name`,`pwd`,`sex`)
values('李四','111111','女')
insert into `student` (`name`,`pwd`,`sex`)
values('yujian','gaoyuajin','男'),('李流','11sfdss','女')
语法: insert into 表名([字段1,字段2,字段3]) values('值1'),('值2'),('值3')...
注意事项:
1. 字段与字段之间用,隔开
2. 字段可以省略,但是后面的值必须一一对应,不能少
3. 可以同时插入多条数据,values后面的值需要,隔开 `values(),(),()`
3.4修改
update 修改谁 (条件) set原来的值=新值
-- 修改学院的名字,带了条件
update `student` set `name` = '狂' where id=2;
-- 不指定条件的情况下,会改动所有的表
update `student` set `name` = '狂'
-- 语法:update 表名 set colnum_name = value where [条件]
-- 修改多个属性,逗号隔开
update `student` set `name` = '狂',`sex`='妖' where id=2;
-- 语法:update 表名 set colnum_name = value,[colnum_name = value,...] where [条件]
条件:where子句 运算符 id等于,大于某个值,
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | id<>2 | true |
| > | |||
| < | |||
| >= | |||
| <= | |||
| between num and | 在某个范围内 | [2,5] | |
| and | 我和你&& | ||
| or | 我或你|| | ||
--通过多个条件定位数据
update `student` set `name`='长江7号' where`name`='狂'and sex='女'
语法: update 表名 set colnum_name = value,[colnum_name = value,…] where [条件]
注意:
- colnum_name 是数据库的列,尽量带上``
- 条件:筛选的条件,如果没有指定,会修改所有的列
- value,也可以是一个变量
- 多个设置属性之间,要用英文逗号隔开
update `student` set `birthday`=current_time where `name`='长江7号' and `sex`='女'
3.5删除
delete 命令
语法:delete from 表名 [where 条件]
-- 删除数据(避免这样子写,会全部删除)
delete from `student`
-- 删除指定数据
delete from `student`where `id`=2
truncate 命令
作用:完全清空一个数据库表,表的结构和索引约束不会变
-- 清空student表
truncate table `student`
delete 与 truncate 的区别
-
相同点:都能删除数据,都不会删除表结构
-
不同点:
- truncate 重新设置 自增器 (计数器回归零)
- truncate 不会影响事务
了解即可,delete删除的问题,重启数据库,现象
- InnoDB 自增列会从1开始 (存在内存当中,断电即失)
- MyISAM 继续从上一个自增量开始(存在文件中的,不会丢失)
4.DQL查询数据(最重点)
4.1 DQL
(Data Query Language:数据查询语言)
- 所有的查询操作都用它 Select
- 简单的查询,复杂的查询
- 数据库最核心的语言
- 使用频率最高的语句
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查询出来的结果中重复的数据,重复的数据只显示一条
-- 查询一下有哪些同学参加了考试,成绩
select * from result -- 查询全部的考试成绩
select `StudentNo` from `result` -- 查询 有哪些同学参加了考试
select distinct `StudentNo` from `result`-- 发现重复数据,去重 distinct不同的
数据库的列(表达式)
select version()-- 查看系统的版本(函数)
select 100*3-1 as 计算结果 -- 用来计算(计算表达式)
select @@auto_increment_increment -- 查询自增的步长
--学员考试+1分查看
select `StudentNo` as 学号,`StudentResult`+1 as 更新成绩 from result
数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量…
select 表达式 from 表
4.3where条件子句
作用:检索数据中符合条件的值
搜索的条件由一个或多个表达式组成,结果为 布尔值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与 |
| or || | a or b a||b | 逻辑或 |
| Not != | not a ! a | 逻辑非 |
-- ======where======
select `studentno` ,`Studentresult` from `result`
-- 查询考试成绩在 95~100 分之间
-- 第一种方法
select `studentno` ,`Studentresult` from `result` where `studentresult`>=95 an`studentresult`<=100
-- 第二种方法
-- 模糊查询(区间)
select `studentno` ,`Studentresult` from `result` where `studentresult`between 95 and 100
-- 除了1000号学生以外的成绩
-- 第一种方法
select `studentno` ,`Studentresult` from `result` where studentno!=1000
-- 第二种方法
select `studentno` ,`Studentresult` from `result` where not studentno=1000
模糊查询 : 比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 如果操作符为null,则结尾为真 |
| is not null | 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在a1,a2…其中的某个值当中,则结果为真 |
-- ========= 模糊查询 ==========
-- 查询姓张的同学
-- like结合 %(代表0到任意一个字符) _(一个字符)
select `studentno` ,`studentname` from `student` where studentname like '张%'
--查询姓张,名只有一个字的同学
select studentno,studentname from student where studentname like '张_'
--查询姓张,名只有2个字的同学
select studentno,studentname from student where studentname like '张__'
-- ========== in(具体的一个值或多个值) ==========
-- 查询 1000,1001,1002号学生
select studentno,studentname from student where studentno in(1000,1001,1002)
-- 查询在北京的学生
-- 方法一
select studentno,studentname,address from student where address in('北京')
-- 方法二
select studentno,studentname,address from student where address like '北京%'
-- ==========null 和 not null==========
--查询地址为空的学生 null或''
select studentno,studentname from student where address is null or address=''
4.4联表查询
JOIN 对比
-- ========== 联表查询 jion ==========
-- 查询参加了考试的同学(学号,姓名,科目表号,分数)
-- 查询参加考试学生的(学号,姓名,科目编号,分数)
select *from student
select *from result
/*
思路:
1. 分析需求,分析查询的字段来自哪些表,(连接查询)
2,确定使用哪种连接查询? 7种
确定交叉点(这2个表中哪个数据是相同的)
判断的条件:学生表中的 studentno = 成绩表的 studentno
*/
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
where s.studentno = r.studentno
-- rightjoin
select s.studentno,studentname,subjectno,studentresult
from student as s
right join result as r
on s.studentno = r.studentno
-- leftjoin
select s.studentno,studentname,subjectno,studentresult
from student as s
left join result as r
on s.studentno = r.studentno
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配,就返回行 |
| left join | 会从左表返回所有的值,即使右表中没有匹配 |
| right join | 会从右表返回所有的值,即使左表中没有匹配 |
- join (连接的表) on (判断条件) 连接查询
- where 等值查询
-- 查询缺考的同学
select s.studentno,studentname,subjectno,studentresult
from student as s
left join result as r
on s.studentno = r.studentno
where studentresult is null
- 三表查询
-- 三表查询
select s.studentno,studentname,subjectname,studentresult
from student as s
right join `result` as r
on s.studentno = r.studentno
inner join `subject` as sub
on r.subjectno = sub.subjectno
- 我要查询哪些数据 …
- 从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
- 假设存在一种多张表查询,慢慢来,先查询两张表,再慢慢增加
自连接
自己的表和自己的表连接,核心:一张表拆为2张一样的表
父类
| pid | categoryid | categoryName |
|---|---|---|
| 1 | 2 | 信息技术 |
| 1 | 3 | 软件开发 |
| 1 | 5 | 美术设计 |
子类
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
操作: 查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
--查询父子信息(把一张表拆为2张一模一样的表)
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`,`grade`
where student.GradeId = grade.GradeID
--方法二
select `studentno`,`studentname`,`gradename`
from `student` as s
inner join grade as g
on s.GradeId = g.GradeId
-- 查询科目所属的年级(科目名称,年级名称)
-- 方法一
select `subjectname`,`gradename` from `subject` as s,`grade` as g
where s.gradeid = g.GradeId
--方法二
select `subjectname`,`gradename` from `subject` as s
inner join grade as g
on s.GradeId = g.GradeId
-- 查询参加了数据库结构-1考试的同学(学号,姓名,分数)
select s.studentno,StudentName,StudentResult
from subject as sub
inner join result as r
on sub.SubjectNo = r.SubjectNo
inner join student as s
on s.StudentNo = r.StudentNo
where sub.SubjectNo =13
4.5分页和排序
排序
-- 排序:升序asc,降序desc
-- 查询参加了数据库结构-1考试的同学(学号,姓名,分数)
-- order by 通过哪个字段排序,怎么排
-- 查询的结果根据降序排序
select s.studentno,StudentName,StudentResult
from subject as sub
inner join result as r
on sub.SubjectNo = r.SubjectNo
inner join student as s
on s.StudentNo = r.StudentNo
where sub.SubjectNo =13
order by `studentresult` desc
分页
-- 分页,每页只显示3条数据
-- 语法: limit 起始位置,页面大小
-- limit 0,3 1~3
-- limit 1,3 2~4
select s.studentno,StudentName,StudentResult
from subject as sub
inner join result as r
on sub.SubjectNo = r.SubjectNo
inner join student as s
on s.StudentNo = r.StudentNo
where sub.SubjectNo =13
order by `studentresult` desc
limit 3,3
-- 第一页 limit 0,3
-- 第二页 limit 3,3
-- 第三页 limit 6,3
-- 第N页, limit (n-1)*pageSize,pageSize
语法: limit(查询起始下标,pageSize)
-- 思考
-- 查询 Java第一学期 课程成绩排名前三的学生,并且分数大于80分的学生信息(学号,姓名,课程名称,分数)
select s.StudentNo,StudentName,SubjectName,StudentResult
from student as s
inner join result as r
on s.StudentNo = r.StudentNo
inner join `subject` as sub
on sub.SubjectNo = r.SubjectNo
where sub.SubjectNo=9 and r.StudentResult >80
ORDER BY `StudentResult` DESC
limit 0,3
4.6子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
where(select * from)
-- 1.查询Java程序设计-1的所有考试结果(学号,科目名,成绩),降序排序
SELECT StudentNo,SubjectName,StudentResult
from result as r
inner join `subject` as sub
on r.SubjectNo = sub.SubjectNo
WHERE sub.SubjectNo=9
order by StudentResult desc
-- 分数不低于80分同学的学号和姓名
select DISTINCT s.studentno,studentname
from student as s
inner join result as r
on s.StudentNo = r.StudentNo
where StudentResult>=80
-- 在这个基础上增加一个科目,Java程序设计-1
select DISTINCT s.studentno,studentname,SubjectNo,StudentResult
from student as s
inner join result as r
on s.StudentNo = r.StudentNo
where StudentResult>=80 and SubjectNo = (select Subjectno from `subject`where SubjectName='Java程序设计-1')
-- 练习:查询Java程序设计-1 前3名同学的成绩信息(学号,姓名,分数)
-- 使用子查询
select s.studentno,StudentName,StudentResult
from student as s
inner join result as r
on s.studentno = r.StudentNo and Subjectno = (select SubjectNo from subject
where subjectname = 'Java程序设计-1')
order by StudentResult desc
limit 0,3
4.7分组和过滤
group by 不能和where一起使用
当你分组时,having要在分组之后再写
-- 查询标题课程的平均分和最高分和最低分,平均分大于70分的
-- 核心:(根据不同的课程分组)
select ANY_VALUE(subjectname) as 科目名,AVG(`StudentResult`) as 平均分, MAX(`studentresult`) as 最高分, MIN(`studentresult`) as 最低分
from result as r
inner join `subject` as sub
on r.`SubjectNo` = sub.`SubjectNo`
group by r.SubjectNo -- 提供什么字段来分组
having AVG(studentresult)>=90
4.8select小结
5MySQL函数
5.1常用函数
-- 数学运算
select abs(-8) -- 绝对值
select CEILING(9.4) -- 向上取整
select floor(9.4) -- 向下取整
select rand() -- 返回0~1的随机数
select sign(10) -- 判断一个数的符号 负数返回-1,正数返回1,0为0
-- 字符串函数
select char_length('即使再小的帆,也能远航') -- 字符串长度
select concat('我','爱','你们') -- 拼接字符串
select insert('我爱编程helloworld',1,2,'超级热爱') -- 插入,从某个位置开始替换某个长度
select lower('KKKKKSDASD'); -- 转小写
select upper('aaaaaa') -- 转大写
select instr('你好中国','你') -- 返回第一次出现的索引
select replace('你好中国,我爱你','中国','家庭') -- 替换出现的指定的字符串
select substr('你好中国,我爱你',3,5) -- 返回指定的子字符串(第二个参数:从第几个开始,第三个参数:表示截取多少字符)
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()
5.2聚合函数(常用)
| 函数名 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| … |
-- 都能统计表的数据(想查询一个表中有多少个记录,就使用这个count())
select count(studentname)from student; -- count(指定列),会忽略所有的null值
select count(*)from student; -- count(*),不会忽略ull值 ,本质 计算行数
select count(1)from result; -- count(1),不会忽略所有的null值 , 本质 计算行数
select sum(studentresult) as 总和 from result
select avg(studentresult) as 总和 from result
select max(studentresult) as 总和 from result
select min(studentresult) as 总和 from result
5.3数据库级别的MD5加密(拓展)
什么是MD5:
- 主要增强算法复杂度和不可逆性
- MD5不可逆,具体的值的md5是一样的
- MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密的前值
create table `testmd5`(
`id` int(4) not null,
`name` varchar(20) not null,
`pwd` varchar(11) not null,
primary key(`id`)
)engine=innodb default charset=utf8
-- 明文密码
insert into testmd5 values(1,'小天','123456'),(2,'lwx','123456'),(3,'lqs','123456'),(4,'金姑姑','123456'),(5,'遇见','123456'),(6,'天坛','123456'),(7,'王战','123456')
-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd); --加密全部的密码
-- 插入的时候加密
insert into testmd5 values(8,'小老虎',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行md5加密,然后对加密后的值
select * from testmd5 where `name`='小天' and pwd=MD5('123456')
5.4窗口函数
窗口：记录集合
窗口函数：在满足某些条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同，窗口大小都是固定的，称为静态窗口；有的函数则相反，不同的记录对应着不同的窗口，称为滑动窗口。
1. 窗口函数和普通聚合函数的区别：
①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。
②聚合函数也可以用于窗口函数。
2. 窗口函数的基本用法：
函数名 OVER 子句
over关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4中语法来设置窗口。
①window_name：给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读；
②PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；
③ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；
④FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。
3.按功能划分可将MySQL支持的窗口函数分为如下几类：
①序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
- 用途：显示分区中的当前行号
- 应用场景：查询每个学生的分数最高的前3门课程
ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY score)
select StudentNo,
ROW_NUMBER() over (partition by StudentNo order by StudentResult desc) as result_order,
SubjectNo,StudentResult
from result
where StudentNo = 1000
对于StudentNo = 1000的同学，有两门课程的成绩均为100，序号随机排了1和2。但很多情况下二者应该是并列第一，则他的成绩为85的这门课的序号可能是第2名，也可能为第3名。
这时候，ROW_NUMBER()就不能满足需求，需要RANK()和DENSE_RANK()出场，它们和ROW_NUMBER()非常类似，只是在出现重复值时处理逻辑有所不同。
dense_rank() OVER (PARTITION BY stu_id ORDER BY score)
select StudentNo,
dense_rank() over (partition by StudentNo order by StudentResult desc) as result_order,
SubjectNo,StudentResult
from result
where StudentNo = 1000
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dgpcGGet-1659521240283)(../JavaWeb.assets/image-20211201153240777.png)]](https://i-blog.csdnimg.cn/blog_migrate/81b6d0bd2e44cd3c475d2cd305ad4568.png)
rank() OVER (PARTITION BY stu_id ORDER BY score)
select StudentNo,
rank() over (partition by StudentNo order by StudentResult desc) as result_order,
SubjectNo,StudentResult
from result
where StudentNo = 1000
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TTL2GSEQ-1659521262558)(../JavaWeb.assets/image-20211201153331477.png)]](https://i-blog.csdnimg.cn/blog_migrate/ce5fbe915e8c3800c8c59cf17020d8b1.png)
ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2
②分布函数：PERCENT_RANK()、CUME_DIST()
PERCENT_RANK()
- 用途：每行按照公式
(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数 - 应用场景：不常用
select StudentNo,
rank() over w as rk,
percent_rank() over w as prk,
SubjectNo,StudentResult
from result
where StudentNo = 1000
WINDOW w as (partition by StudentNo order by StudentResult desc)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k1jbBN7V-1659521301985)(../JavaWeb.assets/image-20211201154626202.png)]](https://i-blog.csdnimg.cn/blog_migrate/f7c8e738b727a6cb01508a3b79c6bed4.png)
CUME_DIST()
- 用途：分组内小于、等于当前rank值的行数 / 分组内总行数
- 应用场景：查询小于等于当前成绩（score）的比例
cd1：没有分区，则所有数据均为一组，总行数为8
cd2：按照lesson_id分成了两组，行数各为4
select StudentNo,SubjectNo,StudentResult,
cume_dist() over (order by StudentResult) as cd1,
cume_dist() over (partition by SubjectNo order by StudentResult) as cd2
from result
where SubjectNo in(1,2)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k5yqItzW-1659521323122)(../JavaWeb.assets/image-20211201155424499.png)]](https://i-blog.csdnimg.cn/blog_migrate/a2c0a18b56b4b10557abf59413981ded.png)
③前后函数：LAG(expr,n)、LEAD(expr,n)
- 用途：返回位于当前行的前n行（
LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值 - 应用场景：查询前1名同学的成绩和当前同学成绩的差值
内层SQL先通过
LAG()函数得到前1名同学的成绩，外层SQL再将当前同学和前1名同学的成绩做差得到成绩差值diff。
④头尾函数：FIRST_VALUE(expr)、LAST_VALUE(expr)
- 用途：返回第一个（
FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值 - 应用场景：截止到当前成绩，按照日期排序查询第1个和最后1个同学的分数
⑤其它函数：NTH_VALUE(expr, n)、NTILE(n)
NTH_VALUE(expr,n)
- 用途：返回窗口中第n个
expr的值。expr可以是表达式，也可以是列名 - 应用场景：截止到当前成绩，显示每个同学的成绩中排名第2和第3的成绩的分数
NTILE(n)
- 用途：将分区中的有序数据分为n个等级，记录等级数
- 应用场景：将每门课程按照成绩分成3组
NTILE(n)函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到n个并行的进程分别计算，此时就可以用NTILE(n)对数据进行分组（由于记录数不一定被n整除，所以数据不一定完全平均），然后将不同桶号的数据再分配。
4. 聚合函数作为窗口函数：
- 用途：在窗口中每条记录动态地应用聚合函数（
SUM()、AVG()、MAX()、MIN()、COUNT()），可以动态计算在指定的窗口内的各种聚合函数值 - 应用场景：截止到当前时间，查询
stu_id=1的学生的累计分数、分数最高的科目、分数最低的科目
6.事务
6.1什么是事务
要么都成功,要么都失败
一一一一一一一一一一一一一一一一一一一一一一一一一一
1、SQL执行 A给B转账 A 1000 —>200 B 200
2、SQL执行 B收到A的钱 A 800 B 400
一一一一一一一一一一一一一一一一一一一一一一一一一一
将一组SQL放在一个批次中去执行
事务原则: ACID原则 原子性,一致性,隔离性,持久性 (脏读,幻读…)
参考博客:(3条消息) 事务ACID理解_dengjili的专栏-CSDN博客_acid
原子性（Atomicity）
要么都成功,要么都失败
举例:如果a向b转钱,要么转成功了,要么转失败了
一致性（Consistency）
事务前后的数据完整性要保证一致
举例:a有1000元,b有200元,a向b转账200元,a变成800元,b变成400元,2者的总钱数始终是1200元
持久性（Durability） --事务提交
事务一但提交则不可逆,被持久到数据库中
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
隔离所导致的一些问题
-
脏读：
指一个事务读取了另外一个事务未提交的数据。
-
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
-
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
（一般是行影响，如下图所示：多了一行）
执行事务
-- =============== 事务 ===============
-- 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`)
)engine=innodb default charset =utf8
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; -- 回滚
7.0索引
MySQL官方对索引的定义:索引(Index)是帮助MySQL高效获取数据的数据结构,提取句子主干,就可以得到索引的本质:索引是数据结构
7.1索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引 (PRIMARY KEY)
- 唯一的标识,主键不可重复,只能有一个列作为主键
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
- 常规索引(KEY / INDEX)
- 默认的,index / key 来设置
- 全文索引(FullText)
- 在特定的数据库引擎下才有,MyISAM
- 快速定位数据
基础语法
-- 索引的使用
-- 1.在创建表的时候,给字段增加索引
-- 2.创建完毕后,增加索引
-- 显示所有的索引信息
show index from student
-- 增加一个索引 索引名(列名)
alter table `student` add fulltext studentname(`studentname`)
-- explain 分析sql执行的状况
explain select * from student -- 非全文索引
explain select * from student where match(studentname) against('张')
7.2 测试索引
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '',
`email` varchar(50) NOT NULL,
`phone` varchar(20) DEFAULT '',
`gender` tinyint(4) unsigned DEFAULT '0',
`password` varchar(100) NOT NULL ,
`age` tinyint(4) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 插入100万条数据
DELIMITER $$ -- 写函数之前必须要写,这是标志
create function mock_data()
returns int
DETERMINISTIC
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',i),'1543510611@qq.com',
concat('18',floor(rand()*((999999999-100000000)+100000000))),
floor(rand()*2),UUID(),floor(rand()*100));
set i = i+1;
end while;
return i;
end;
select *from app_user where`name`= '用户i9999';
-- id_表名_字段名
-- create idnex 索引名 on 表(字段)
create index id_app_user_name on app_user(`name`);
explain select *from `app_user` where `name` = '用户i9999';
- 添加索引时:只需查询1条数据
- 当删除索引之后,就要一条条的查询
索引在小范围数据的时候,用处不大,但在大量数据的时候,用处十分明显
7.3索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash类型的索引
Btree:InnoDB的默认
阅读:CodingLabs - MySQL索引背后的数据结构及算法原理
8.权限管理和数据库备份
8.1用户管理
SQL 命令操作
用户表:
-- 创建用户 create user 用户名 identified by '密码'
create user yujian identified by '123456'
-- 修改密码 (修改当前用户密码)
alter user 'yujian'@'localhost' identified by '123456'
alter user 'yujian'@'%' identified by '123456'
-- 重命名 rename user 旧名 to 新名
rename user yujian to yujian1
-- 用户授权
-- all privileges 除了给别人授权,其他权限都可以
grant all privileges on *.* to yujian1
-- 查询权限
show grants for yujian1 -- 查看指定用户权限
show grants for root@localhost
-- 撤销权限 revoke 哪些权限,在哪个库撤销,给谁撤销
revoke all privileges on *.* from yujian1
-- 删除用户
drop user yujian
8.2MySQL备份
为什么要备份:
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份的关系
- 直接拷贝物理文件
- 在可视化工具中手动导出
- 使用命令行导出 musqldump 命令行使用
- 导出
# dump一张表
# mysqldump -h主机 -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# dump多张表
# mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表2 表3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student result subject >D:/a.sql
# dump数据库
# mysqldump -h主机 -u用户名 -p密码 数据库 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql
- 导入
# 导入
# 先登录的情况下,切换到指定数据库
# source 备份文件
source d:/a.sql
#或
mysql -u用户名 -p密码 库名<备份文件
假设你要备份数据库,防止数据丢失
9.规范数据库设计
9.1为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据插入和删除都会麻烦,异常[屏蔽使用物理外键]
- 程序的性能差
良好的数据库设计
-
节省内存空间
-
保存数据的完整性
-
方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求:分析业务和需求处理的数据库的需求
- 概要设计:设计关系图E-R图
设计数据库的步骤:(个人博客)
-
收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文字分类,谁创建的)
- 文章表(文章的信息)
- 评论表
- 友链表(友链信息)
- 自定义表(系统信息,某个关键的字,或者一些主字段) key : value
- 说说表(发表心情…id…content…create_time)
-
标识实体(把需求落地到每个字段)
-
标识实体之间的关系
- 写博客:user -->blog
- 创建分类:user -->category
- 关注:user -->user
- 友链:links
- 评论: user–>user-blog
9.2三大范式
为什么需要数据规范化
-
信息重复
-
更新异常
-
插入异常
- 无法正常显示信息
-
删除异常
- 丢失有效信息
三大范式
第一范式（1NF):要求数据库表的每一列都是不可分割的原子数据项
原子性 : 保证每一列内容不可再分
第二范式（2NF)
前提 : 满足第一范式
每张表只描述一件事情
第三范式（3NF）
前提:满足第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关
规范性和性能的问题
关联查询的表不得超过3张表
- 考虑商业化的需求和目标,数据库的性能更加重要
- 在规范性能的问题的时候,需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段.(从多表查询变成单标查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
10.JDBC(重点)
10.1数据库驱动
驱动:声卡,显卡,数据库
我们的程序会通过一个数据库驱动,和数据库打交道!
10.2JDBC
SUN公司为了简化开发人员的(对数据库统一)操作,提供了一个(Java操作数据库的)规范,俗称:JDBC
对于开发人员,只需要掌握JDBC接口操作即可
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-8.0.26.jar
10.3第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,zhansan,123456,zs@sina.com,1980-12-04),
(2,lisi,123456,lisi@sina.com,1981-12-04),
(3,wangwu,123456,wangwu@sina.com,1979-12-04)
1.创建一个普通的项目
2.导入数据库驱动
- 在java项目目录下,新建一个bin目录,将jdbc的jar包放进去
- 将jar包添加到项目的库里面
- 编写测试代码
package com.yujian.lession;
import java.sql.*;
//我的第一个jdbc程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法,加载驱动
//2.用户信息和url
// useUnicode=true&characterEncoding=utf8&useSSL=true
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&setServerTimeZone=GM";
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 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 = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connect.setAutoCommit();
url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&setServerTimeZone=GM";
//mysql -- 3306
//jdbc:mysql://主机地址:端口号?参数1&参数2&参数3
Statement 指向SQL的对象 PrepareStatement 执行SQL的对象
String sql = "select *from users"; //编写SQL
statement.executeQuery(sql); //查询操作返回 ResultSet
statement.execute(sql); //执行任何SQL
statement.executeUpdate(sql); //更新,插入,删除,都是用这个,返回一个受影响的行数
ResultSet 查询的结果集 : 封装了所有的查询结果
获取指定的数据类型
resultSet.getObject();//在不知道列类型的情况使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getDouble();
遍历,指针
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
释放资源
//6.释放连接
resultSet.close();
statement.close();
connection.close();
10.4statement对象
jdbc中的statement对象用于数据库发送SQL语句,想完成数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可.
Statement对象的executeUpdate方法,用于向数据库发送增,删,改的sql语句,executeUpdate执行完毕后,将会返回一个整数(即增删改查语句导致了数据库几行数据发生了变化).
Statement.executeUpdate方法用于数据库发送查询语句,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
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 user where id = 1";
ResultSet rs = st.executeUpdate(sql);
while(rs.next){
//根据获取列的数据类型,分别调用rs的相应方法映射java对象中
}
代码实现
1.提取工具类
package com.yujian.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1.驱动只用加载一次
Class.forName(driver);
} catch (IOException | ClassNotFoundException 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 throwables) {
throwables.printStackTrace();
}
}
if (st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
2.编写增删改的方法 : excuteUpdate
insert
package com.yujian.lesson02;
import com.yujian.lesson02.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(4,'yujian','123456','1543510611@qq.com','2021-8-31')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功!!!");
}
JdbcUtils.release(conn,st,rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
delete
package com.yujian.lesson02;
import com.yujian.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelet {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//获取数据库连接
conn = JdbcUtils.getConnection();
//获取SQL的执行对象
st = conn.createStatement();
//需要执行的sql语句
String sql = "delete from users where id= 4";
int i = st.executeUpdate(sql);
//判断是否执行成功
if(i>0){
System.out.println("删除成功");
}
//关闭
JdbcUtils.release(conn,st,rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
update
package com.yujian.lesson02;
import com.yujian.lesson02.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 {
//1.获取连接
conn = JdbcUtils.getConnection();
//获取SQL执行的对象
st = conn.createStatement();
String sql = "update users set NAME = 'yujian' where id=1";
int i = st.executeUpdate(sql);
//判断是否执行完毕
if (i>0){
System.out.println("更新成功");
}
//关闭
JdbcUtils.release(conn,st,rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
3.查询
package com.yujian.lesson02;
import com.yujian.lesson02.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 {
//1.获取连接
conn = JdbcUtils.getConnection();
//2.获取SQL执行对象
st = conn.createStatement();
//3.编写SQL代码
String sql = "select *from users where id = 1";
//查询返回一个结果集
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println("name:" + rs.getString("NAME")+
",email:"+rs.getString("email")+
",birth"+ rs.getString("birthday"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入问题
sql存在漏洞,会被攻击导致数据泄露 SQL会被拼接 or
package com.yujian.lesson02;
import com.yujian.lesson02.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("'or'1=1","123456");
}
//登录业务
public static void login(String username,String password){
//因为这些资源要被释放掉,所有写在外面
Connection conn =null;
Statement st = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JdbcUtils.getConnection();
//2.获取SQL执行对象
st = conn.createStatement();
//3.编写SQL代码
//select *from users where `NAME`='yujian' and`PASSWORD` = '123456'
//select *from users where `NAME`='yujian'or'1=1' and`PASSWORD` = '123456'
String sql = "select *from users where `NAME` ='"+username+"'and `PASSWORD`='"+password+"'";
//查询返回一个结果集
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println("name:" + rs.getString("NAME")+
",email:"+rs.getString("email")+
",birth"+ rs.getString("birthday"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.5PreparedStatement对象
PreparedStatement可以防止sql注入,并且效率更高!
- 新增
package com.yujian.lesson03;
import com.sun.xml.internal.bind.v2.model.core.ID;
import com.yujian.lesson02.utils.JdbcUtils;
import java.sql.*;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JdbcUtils.getConnection();
//区别
//使用? 占位符代替参数
String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
pst = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
pst.setInt(1,4);
pst.setString(2,"gao");
pst.setString(3,"123456");
pst.setString(4,"1543510611@qq.com");
/*
注意点:sql.Date 数据库
util.Date Java
*/
pst.setDate(5,new Date(new java.util.Date().getTime()));
//执行
int i = pst.executeUpdate();
if (i>0){
System.out.println("添加成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
- 删除
package com.yujian.lesson03;
import com.yujian.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JdbcUtils.getConnection();
//2.预处理sql代码
String sql= "delete from users where id = ?";
//3.预编译SQL
pst = conn.prepareStatement(sql);
//手动添加值
pst.setInt(1,4);
int i = pst.executeUpdate();
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
- 更新
package com.yujian.lesson03;
import com.yujian.lesson02.utils.JdbcUtils;
import jdk.nashorn.internal.scripts.JD;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst =null;
ResultSet rs = null;
try {
//1.获取连接
conn = JdbcUtils.getConnection();
//2.预处理sql代码
String sql = "update users set NAME = ? where id=? ";
//3.预编译SQL
pst = conn.prepareStatement(sql);
//4.手动给参数赋值
pst.setString(1,"gao");
pst.setInt(2,1);
//判断是否有数据更新
int i = pst.executeUpdate();
if (i>0){
System.out.println("更新成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
- 查询
package com.yujian.lesson03;
import com.yujian.lesson02.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 pst = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JdbcUtils.getConnection();
//2.预编写sql语句
String sql = "select *from users where id=?";
//3.预编译SQL
pst = conn.prepareStatement(sql);
//4.手动给参数赋值
pst.setInt(1,1);
//5.获取返回集
rs = pst.executeQuery();
//因为只查询一条语句,所以可以用下面这种写法
if (rs.next()){
System.out.println("id:"+rs.getInt("id")+
",name:"+rs.getString("NAME")+
",email:"+rs.getString("email"));
}
// while (rs.next()){
// System.out.println("id:"+rs.getInt("id")+
// ",name:"+rs.getString("NAME")+
// ",email:"+rs.getString("email"));
// }
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
- 防止SQL注入
package com.yujian.lesson03;
import com.yujian.lesson02.utils.JdbcUtils;
import java.sql.*;
public class SQL注入 {
public static void main(String[] args) {
login("gao","123456");
// login("'or'1=1","123456");
}
//登录业务
public static void login(String username,String password){
//因为这些资源要被释放掉,所有写在外面
Connection conn =null;
PreparedStatement st= null;
ResultSet rs = null;
try {
//1.获取连接
conn = JdbcUtils.getConnection();
String sql = "select *from users where `NAME` =? and `password`=?";
st = conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
//查询返回一个结果集
rs = st.executeQuery();
while (rs.next()){
System.out.println("name:" + rs.getString("NAME")+
",email:"+rs.getString("email")+
",birth"+ rs.getString("birthday"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.6使用IDEA连接数据库
连接成功后,可以选择数据库
如何在设置
10.7事务
要么都成功,要么都失败
ACID原则
原子性:要么全部完成,要么都不完成
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一但提交不可逆,持久化到数据库了
隔离性的问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表数据发生了改变
幻读(虚读):在一个事务内,读取到了别插入的数据,导致前后读入的数据不一致
代码实现
1.开启事务 conn.setAutoCommit(false);
2.一组业务执行完毕,提交事务
3.可以再catch语句中显示的定义,回滚语句,但默认失败会自动回滚
package com.yujian.lesson04;
import com.yujian.lesson02.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 pst = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JdbcUtils.getConnection();
//2.关闭数据库的自动提交,会自动开启事务
conn.setAutoCommit(false); //开启事务
//sql代码
String sql1 = "update account set money = money-100 where name = 'A'";
pst = conn.prepareStatement(sql1);
pst.executeUpdate();
String sql2 = "update account set money = money+100 where name = 'B'";
pst = conn.prepareStatement(sql2);
pst.executeUpdate();
//业务完毕,提交事务
conn.commit();
System.out.println("业务执行成功");
} catch (SQLException throwables) {
//不写也可以,如果失败,则默认回滚
// try {
// conn.rollback(); //如果失败则回滚事务
// } catch (SQLException e) {
// e.printStackTrace();
// }
throwables.printStackTrace();
}
finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
10.8数据库连接池
数据库连接—执行完毕—释放
连接—释放(十分浪费系统资源)
池化技术:准备一些预先的资源,过来就连接预先准备好的
最小连接数:(自己的需求) 5/10
最大连接数:业务最高承载上限(自己定)
对待超时;100ms
编写连接池,实现一个接口 DateSource
开源数据源实现
DBCP
C3P0
Druid
使用了这些数据库编写数据库连接代码连接池之后,我们在项目开发中就不需要
DBCP
需要用到的jar包:commons-dbcp.jar , commons-pool.jar
package com.yujian.lesson05;
import com.yujian.lesson05.utils.JdbcUtils_DBCP;
import java.sql.*;
public class TestDBCP {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JdbcUtils_DBCP.getConnection();
//区别
//使用? 占位符代替参数
String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
pst = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
pst.setInt(1,4);
pst.setString(2,"gao");
pst.setString(3,"123456");
pst.setString(4,"1543510611@qq.com");
/*
注意点:sql.Date 数据库
util.Date Java
*/
pst.setDate(5,new Date(new java.util.Date().getTime()));
//执行
int i = pst.executeUpdate();
if (i>0){
System.out.println("添加成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils_DBCP.release(conn,pst,rs);
}
}
}
扫一扫
763
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
