MySQL学习笔记

JAVA 专栏收录该内容
13 篇文章 1 订阅

1. MySQL

JavaEE : 企业级Java开发 Wed
前段(页面:展示数据)
后台(连接点:连接数据库JDBC,连接前段(控制,控制视图跳转,给前端传递数据))
数据库(存数据,Txt,Excel,word)
只会写代码 ↓(一般程序员),需要学好数据库 ↑
操作系统,数据结构与算法!(不错的程序员)
离散数据,数字电路,体系结构,编译原理 + 实战经验 (优秀程序员)

1.1 为什么要学习数据库

  1. 岗位需求
  2. 现在的世界,大数据时代~,得数据库者得天下
  3. 被迫需求:存数据
  4. 数据库是所有软件体系中最核心的存在 DBA

1.2 什么是数据库

数据库(DB ,DataBase)
概念:数据仓库,软件,安装在操作系统(window , linux , mac …)之上!SQL , 可以存储大量的数据。500万以上需要优化
作用:存储数据,管理数据

1.3 数据库分类

关系型数据库:(SQL)

  • MySQL,Oracle,Sql Server,DB2,SQLite
  • 通过表和表之间,行和列之间的关系进行数据的存储,学员信息表,考勤表,…
    非关系型数据库:{key:value}

非关系型数据库:(NoSQL) Not Only

  • Redis,MongDB
  • 非关系型数据库,对象存储,通过对象的自身的属性来决定

DBMS(数据库管理系统)

  1. 数据库的管理软件,科学有效的管理我们的数据。维护和获取数据;
  2. MySQL,数据库管理系统

1.4、MySQL 简介

  1. List item
  2. MySQL 是一个关系型数据库管理系统,
  3. 前世:瑞典 MySQL AB 公司
  4. 今生: Oracle 旗下产品
  5. MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一
  6. 开源的数据库软件
  7. 体积小、速度快、总体拥有成本低,招人成本比较低,所有人必须会
  8. 中小型网站,或者大型网站,集群

1.5、安装MySQL

  1. 解压到自己电脑的环境目录包下Environment
  2. 配置环境变量,我的电脑 -> 属性 -> 高级 -> 环境变量 -> PATH
  3. 新建 mysql 配置文件 my.ini
[mysqld]
# 目录要换成自己的,data后面\不知道写不写
basedir=D:\Environment\mysql-8.0.26\
datadir=D:\Environment\mysql-8.0.26\data
port=3306
skip-grant-tables

tip:在mysql-5.7.19中需要这样设置,但是在mysql-8.0.26中,需要将其删除,否则mysql启动后会自动关闭,导致第七步无法继续。
在这里插入图片描述

  1. 启动管理员模式下的CMD,并将路径切换至mysql下的bin目录,然后输入mysqld -install(安装mysql)
    cd /d D:\Environment\mysql-5.7.34\bin文件地址
    mysqld -install
    在这里插入图片描述

5.初始化数据文件

mysqld --initialize-insecure --user=mysql

6.启动mysql

net start mysql

7.进入mysql管理界面:-u(用户名),-p(密码,不可以有空格)由于my.ini中skip-grant-tables程序跳过了密码

mysql -u root -p

8.进入mysql 通过命令行修改密码(mysql-5.7.19成功,mysql-5.7.30这步失败了)

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

9.刷新权限

flush privileges;

10.注释掉my.ini中#skip-grant-tables
11.关闭mysql
exit退出
net stop mysql 停止服务
12.重启mysql
net start mysql 登录
mysql -u root -p123456 输入账号密码
最后登录成功
在这里插入图片描述
如果安装失败
清空服务sc delete mysql,然后重装

1.6 安装SQLyog

1.安装SQLyog,然后利用注册码进行注册:
在这里插入图片描述
Tip:若出现配置新连接报错:错误号码 2058,分析是 mysql 密码加密方法变了。
解决方法:windows 下cmd 登录 mysql -u root -p 登录你的 mysql 数据库,然后执行这条SQL:
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘123456’;
然后在重新配置SQLyog的连接,则可连接成功了,就OK了。

2.新建一个数据库:
在这里插入图片描述
每一个sqlyog的执行操作,本质就是对应了一个sql,可以在软件的历史记录中查看

  1. 新建一张表 student:
    在这里插入图片描述
    4.填写表的信息
    在这里插入图片描述

1.7 连接数据库

命令行连接(所有语句都是用;结尾):

  1. 连接数据库:mysql -u root -p123456
  2. 修改用户密码:
    ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘123456’;
  3. 刷新权限flush privileges;
  4. 查看所有数据库:show databases;
  5. 切换数据库 use 数据库名:use school;
  6. 查看数据库中所有的表:show tables;
  7. 查看数据库中所有表的信息:describe student;
  8. 创建一个数据库westos:create database westos;
  9. 退出连接:exit
  10. 强行终止 ctrl+c
  11. – 单行注释(SQL本来的注释)
  12. /* 多行注释 */

数据库XX语言:CRUD增删改查
DDL 定义
DML 操作
DQL 查询
DCL 控制

2 操作数据库

操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
MySQL关键字不区分大小写

2.1 操作数据库

1.创建数据库

CREATE DATABASE IF NOT EXISTS westos;

2.删除数据库

DROP DATABASE IF EXISTS westos;

3.使用数据库

-- tab 键的上面,如果你的表名或字段名是一个特殊字符,需要带 ``
USE `school`

4.查看数据库

SHOW DATABASES;

学习思路:

  • 对照sqllog可视化历史记录查看sql
  • 固定的语法或关键字必须要强行记住!

2.2 数据库的列类型

2.2.1 数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小 3个字节
  • int 标准的整数 4个字节(常用
    • 输入位数为显示宽度,4位 1 = 0001
  • bigint 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节 (精度问题)
  • decimal 字符串形式的浮点数,金融计算的时候,一般用

2.2.2 字符串

  • char 字符串固定大小 0-255
  • varchar 可变字符串 0-65535(常用 String)
  • tinytext 微型文本 2^8 - 1
  • text 文本串 2^16 - 1 (保存大文本)

2.2.3 时间日期​

  • java.util.Date
    • date YYYY-MM-DD 日期
    • time HH:mm:ss 时间格式
    • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
    • timestamp 时间戳 1970.1.1到现在的毫秒数
    • year 年份表示

2.2.4 null

  • 没有值,未知
  • 注意,不要使用null进行运算,结果为null

2.3 数据库的字段类型(重点)

1.unsigned

  • 无符号的整数
  • 声明该列不能声明负数

2.zerofill

  • 0填充的
  • 不足位数用0 填充,10的长度(1 = 0000000001 )

3.自增 Auto_increment

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

4.非空 NULL not Null

  • 假设设置为 not null,如果不给他赋值,就会报错
  • NULL 如果不填写,默认为NULL

5.默认 default

  • 设置默认的值!
  • sex,默认值为男,如果不指定该列的值,则会有默认的值!

拓展:
每个表都必须存在以下五个字段!
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_updata 修改时间

2.4 创建数据库表(重点)

-- 目标:创建一个schoo1数据库
-- 创建学生表 (列 , 字段) 使用SQL 创建
-- 学号int,登录密码varchar(20),姓名、性别varchar(2),出生日期(datatime),家庭住址,emai1

-- 注意点,使用英文(),表的名称和字段尽量使用括起来
-- 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=utf8

格式:

CREATE TABLE [IF NOT EXISTS] `表名``字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
...
`字段名` 列类型 [属性] [索引] [注释][表类型] [表的字符集设置] [注释]

常用命令:
SHOW CREATE DATABASE school – 查看创建数据库的语句
SHOW CREATE TABLE student – 查看student数据表的定义语句
DESC student – 显示表的结构

2.5 数据表的类型

关于数据库的引擎

  • INNODB 默认使用
  • MYISAM 早些年使用
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间的大小较小较大(约为2倍)

常规使用操作:

  • MYISAM 节约空间,速度较快,
  • INNODB 安全性高,事务处理,多表多用户操作

在物理空间存在的位置:

  • 所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
  • 本质还是文件的存储

MySQL 引擎在物理文件上的区别:

  • innoDB 在数据库表中,只有一个*.frm文件,以及上级目录下的ibdata1文件(MySQL8.0取消frm文件,元数据都存储到表空间)
  • MYISAM 对应的文件
    *.frm - 表结构的定义文件
    *. MYD - 数据文件(data)
    *.MYI - 索引文件(index)

设置数据库字符集编码

  • CHARTSET=UTF8
  • 设置的话,会是mysql默认的字符集编码
  • MySQL 的默认编码是Latin1,不支持中文
  • 可以在my.ini中配置默认的编码 character-set-server=utf8(8.0在C盘的隐藏文件中)

2.6 修改删除表

修改 ALTER TABLE

  • 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
    ALTER TABLE student RENAME AS student1

  • 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
    ALTER TABLE student1 ADD age INT(11)

  • 修改表的字段(重命名,修改约束)

    • ALTER TABLE 表名 MODIFY 字段名 列属性 [ ] – 修改约束 MODIFY
      ALTER TABLE student1 MODIFY age VARCHAR(11)
    • ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性 [ ] – 字段重命名 CHANGE
      ALTER TABLE student1 CHANGE age age1 INT(1)
  • 删除表的字段 ALTER TABLE 表名 DROP 字段名
    ALTER TABLE student1 DROP age1

删除

  • 删除表(如果表存在再删除)
    DROP TABLE (IF EXISTS) student1

所有的创建和删除操作尽量加上判断,以免报错

注意点:

  • `` 字段名,使用这个包裹
  • 注释 – /**/
  • sql 关键字大小写不敏感,建议写小写
  • 所有的符号全部用英文

3 MySQL数据管理

3.1 外键(了解即可)

作用:保持数据一致性,完整性,主要目的是控制存储在外键表中的数据。 使两张表形成关联,外键只能引用外表中的列的值。

方式一:在创建表的时候,增加约束(麻烦,比较复杂)

CREATE TABLE `grade`(
   `gradeid`INT(10)NOT NULL AUTO_INCREMENT COMMENT '年级id',
   `gradename`VARCHAR(50)NOT NULL COMMENT '年级名称',
   PRIMARY KEY(`gradeid`)
)ENGINE INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid字段,要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用)  references引用

CREATE TABLE IF NOT EXISTS `student`(
      `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
      `name` VARCHAR(30)NOT NULL DEFAULT '匿名' COMMENT '姓名',
      `pwd` VARCHAR(20)NOT NULL DEFAULT '123456'COMMENT '密码',
      `sex` VARCHAR(2)NOT NULL DEFAULT '女' COMMENT '性别',
      `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
      `gradeid` INT(10)NOT NULL COMMENT'学生的年级',
      `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
      `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

删除有外键关系的表的时候,必须先删除引用的表(从表),再删除被引用的表(主表)
方式二: 创建表成功后,添加外键

CREATE TABLE `grade`(
   `gradeid`INT(10)NOT NULL AUTO_INCREMENT COMMENT '年级id',
   `gradename`VARCHAR(50)NOT NULL COMMENT '年级名称',
   PRIMARY KEY(`gradeid`)
)ENGINE INNODB DEFAULT CHARSET=utf8


-- 学生表的gradeid字段,要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用)  ref`grade`erences引用


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=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

格式:ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) 引用到哪个表的哪个字段

TIP:以上的操作都是物理外键,数据库级别外键,不建议使用。(避免数据库过多造成困扰,这里了解即可)

最佳实践

  • 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
  • 我们想使用多张表的数据,想使用外键(程序去实现)

3.2 DML语言(全记住)

数据库意义:数据存储,数据管理
DML语言:数据操作语言

  • Insert 添加
  • update 修改
  • delete 删除

3.3 添加 insert

语法: insert into 表名([字段名1],[字段名2],[字段名2])values(‘值1’),(‘值2’),(‘值3’),…)
例: INSERT INTO grade(gradename) VALUES (‘大四’)

由于主键自增我们可以省略(如果不写表的字段,他就会一一匹配)

INSERT INTO `grade` VALUES('大三')  报错
INSERT INTO `grade` (`gradeid`,`gradename`)VALUES('大三')  相当于这种情况

一般写插入语句,我们一定要数据和字段一一对应

  • 插入多个字段
INSERT INTO `grade` (`gradename`) VALUES ('大二'),('大一')
INSERT INTO `student`(`name`)VALUES('张三')
INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES('李四','aaaaa','男'),('张三','bbbbbb','男')

注意事项:

  1. 字段和字段之间用英文符号隔开
  2. 字段可以省略,但是后面的值必须一一对应 INSERT INTO studentVALUES(‘李四’,‘aaaaa’,‘男’),
  3. 可以同时插入多条数据,VALUES 后面的值需要使用(逗号)隔开即可
    values(),(),…

3.4 修改 update

语法: update 表名 set colnumname1 = new value1, conlnumid = new value2 where 限制范围

-- 修改学员id为1的学员名字
UPDATE `student` SET `name`= 'CHAIN' WHERE id = 1
-- 不指定的情况下,修改所有学员的名字
UPDATE `student` SET `name`= 'CHAIN' 
UPDATE `student` SET `name`='CHAIN',email = '23456789qq@.com' WHERE id =1;

条件:where 子句 运算符 (id 等于 某个值,)(大于某个值,)(在某个区间内修改…)

操作符含义范围结果
=等于5 = 6false
<> 或 !=不等于5 <> 6true
>大于5 > 6false
<小于5 < 6true
>=大于等于6,7 >= 6true
<=小于等于5,6 <= 6true
BETWEEN ** and **在某个范围内[2 , 5] 3true
AND我和你 &&5 > 1 and 1 > 2false
OR我或你 ||5 > 1 or 1 > 2true

通过多个条件定位数据:

UPDATE `student` SET `name`= 'CHAIN' WHERE pwd = '123456' AND sex = '男'

注意:

  • column_name 是数据库的列,尽量带上``
  • 条件,是筛选的条件,如果没有指定,则会修改所有的列
  • value 是一个具体的值,也可以是一个变量 CURRENT_TIME时间
    • 多个设置的属性之间,使用英文逗号隔开
 UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='长江' AND SEX = '女'

3.5 删除

delete 命令:

语法: delete from 表名 [where 条件]

-- 删除数据 (避免这样写,会全部删除)
DELETE FROM `student`
-- 删除指定
DELETE FROM `student` where id= 1

TRUNCATE 命令:

作用:完全清空一个数据库,表的结构和索引约束不会变

-- 清空 student 表
TRUNCATE 'student'

delete 和 TRUNCATE 区别:

  • 相同点: 都能删除数据,都不会删除表结构
  • 不同点:
    • TRUNCATE 重新设置自增列 计数器会归零
    • DELETE 不会影响事务
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll`VARCHAR(20) NOT NULL,
PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO`test`(`coll`)VALUES('1'),('2'),('3')
DELETE FROM `test`   -- 不会影响自增
TRUNCATE TABLE `test`  -- 自增会归零

了解即可:DELETE删除的问题,重启数据库,现象

  • innoDB 自增列会从1开始(存在内存当中,断电即失,8.0修复了这个问题)
  • MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)

4 DQL查询数据(最重点)

4.1 DQL

(Data Query Language:数据查询语言)

  • 所有的查询操作都用它 Select
  • 简单的查询,复杂的查询它都能做
  • 数据库中最核心的语言,最重要的语句
  • 使用频率最高的语言
    注意:
  • 多个程序运行报错时,可以一段一段运行,由于运行顺序是cpu随机选择的,可能出现还未创建表格就已经开始写字段了,所以报错

4.2 指定查询字段

Select 完整语法:
顺序由严格要求:
在这里插入图片描述

-- 查询全部学生  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

数据库的列(表达式)

SELECT VERSION()  -- 查询系统版本(函数)
SELECT 100*20 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逻辑 非,真为假,假为真!
-- 查询考试成绩在95-100分之间
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` >=95 AND `studentresult`<=100

-- AND  &&
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` >=95 && `studentresult`<=100

-- 模糊查询(区间)
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT studentno,`studentresult` FROM result
WHERE studentno !=1000;	

-- != not
SELECT studentno,`studentresult` FROM result
WHERE NOT studentno =1000;	

模糊查询:比较运算符

运算符语法描述
IS NULLa is null如果操作符为 null, 结果为真
IS NOT NULLa is not null如果操作符为 not null, 结果为真
BETWEENa between b and c若 a 在 b 和 c 之间,则结果为真
LIKEa like bSQL匹配,如果 a 匹配到 b ,则结果为真
INa in (a1,a2,a3…)假设 a 在 a1,a2,a3… 其中的某一个中,则结果为真
  1. IS NULL 和 IS NOT NULL
-- 查询地址为空的学生 null
SELECT `studentno`,`studentname` FROM `student`
WHERE address = '' OR address IS NULL

-- 查询有出生日期的同学 不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL
  1. BETWEEN
在这里插入代码片
  1. LIKE
-- like结合 %(代表0到任意个字符)  _(一个下划线代表一个字符)
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '刘%'

--  查询姓刘的同学,名字后面只有一个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '刘_'

-- 查询名字中间有嘉字的同学  %%
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '%嘉%'
  1. IN
-- 查询1001,1002,1003号学员
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN(1001,1002,1003)

-- 查询在北京的学生  IN代表具体的一个或多个值
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN ('北京朝阳')

4.4 联表查询

在这里插入图片描述

-- 联表查询

-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student
SELECT * FROM result
/*思路
1.分析需求,分析查询的字段来自哪些表,(连接查询)
2.确定使用哪种连接查询? 7种
确定交叉点(这两个表中,哪个数据是相同的)
判断的条件:学生表中的studentno = 成绩表中的studentno
*/
-- join on 连接查询	 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  WHERE可以在inner join中
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno

-- LEFT JOIN
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 jion也会从右表中返回所有的值,即使左表中没有匹配,
--FROM a LEFT JOIN b 左为准

–FROM a RIGHT JOIN b 右为准
FROM为左表,JOIN为右表,根据需要以哪张表为基准来决定使用left还是right

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

-- 查询参加考试的同学信息:学号,学生姓名,科目名,分数
/*思路
1.分析需求,分析查询的字段来自哪些表,student result subject(连接查询)
2.确定使用哪种连接查询? 7种
确定交叉点(这两个表中,哪个数据是相同的)
判断的条件:学生表中的studentno = 成绩表中的studentno
*/
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

思路

  1. 我要查询哪些数据 SELECT …
  2. 从哪几个表中查 FROM 表 xxx JOIN 连接的表 ON 交叉条件
  3. 假设存在一中多张表查询,先查询两章表,然后再慢慢增加
-- 查询学员所属的年纪(学号,学生的姓名,年纪名称)
SELECT studentno,studentname,`gradename`
FROM student s
INNER JOIN `grade` g
ON s.gradeid = g.gradeid

-- 查询科目所属的年级(科目名称,年级别名称)
SELECT `subjectname`,`gradename`
FROM grade g
INNER JOIN `subject` s
ON g.gradeid = s.gradeid

-- 查询参加数据结构考试的同学信息:学号,学生姓名,科目名,分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname`='数据结构'

自连接(了解)

CREATE TABLE `category` (
	`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
	`pid` INT(10) NOT NULL COMMENT '父id',
	`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
	PRIMARY KEY(`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET = utf8;

INSERT INTO `category`(`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','PS技术'),
('8','2','办公信息');

自己的表跟自己的表连接,核心:一张表拆为两张一样的表
父类

categoryidcategoryName
2信息技术
3软件开发
5美术设计
子类
pidcategoryidcategoryName
34数据库
28办公信息
36web开发
57ps技术
操作:查询父类对应子类关系
父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计ps技术

4 分页和排序

排序 order by

-- 排序 : 升序 ASC  ,降序 DESC
-- ORDER BY 通过哪个字段排序,怎么排
-- 查询的结果根据成绩升序 降序
-- 查询了参加 数据库结构-1 考试的同学信息: 学号,学生名字,科目名,分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname`='数据结构'
ORDER BY studentresult ASC

分页
为什么要分页:

  • 缓解数据库压力,给人的体验更好
  • 还有一种不分页的 瀑布流
-- 分页,每页显示五条数据
-- 语法:   limit 起始值,页面的大小
--         limit     0,5     1-5
--         limit     1,5     2-6
--   	   limit     6,5   第二页该
-- 网页应用 : 当前页 ,总的页数 ,页面的大小
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='数据结构-1'
ORDER BY StudentResult ASC
LIMIT 0,5

公式:

– 第一页 limit 0,5
– 第二页 limit 5,5
– 第三页 limit 10,5
– 第N页 limit (n-1)* pageSize,pageSize
– [pageSize 代表页面大小]
– (n-1)* pageSize :起始值
– n : 当前页
– 数据总数/页面大小 = 总页数

语法: limit ( 查询起始下标,pagesize )

-- 查询 JAVA第一学年 课程成绩排名前十的学生,并且分数要大于80 的学生信息(学号,姓名,课程名称,分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM result r
INNER JOIN student s
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE `studentresult` >= 80 AND`subjectname`='JAVA第一学年'
ORDER BY `studentresult` DESC
LIMIT 0,10

4.6 子查询

where(这个值是计算出来的)
本质: 在where语句中嵌套一个子查询语句

-- 1.查询 数据库结构-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 r
WHERE `subjectno`=(
          SELECT subjectno FROM `subject`
          WHERE `subjectname` = '数据结构-1'
)
ORDER BY `studentresult` DESC


-- 查询分数不小于80分的学生的学号和姓名
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` >= 80

-- 在这个基础上增加一个科目  高等数学-2

SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` >= 80 AND `subjectno`= (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-2'
)

-- 查询课程为 高等数学-2 且分数不小于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 r.`studentresult` >= 80 AND sub.`subjectname` = '高等数学-2' 

-- 再改造(由里及外)
SELECT `studentno`,`studentname` FROM `student`WHERE `studentno`=(
SELECT `studentno` FROM `result` WHERE `studentresult`>80 AND `subjectno`=(
SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-2'
)
)

练习: 查询 C语言-1 前五名同学的成绩的信息(学号,姓名,分数)

SELECT s.`studentno`,`studentname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE `subjectname` = 'C语言-1'
ORDER BY `studentresult` DESC
LIMIT 0,5

4.7 分组和过滤

-- 查询不同课程的平均分,最高分,最低分,.平均分大于80
-- 核心:(根据不同的课程分组)
SELECT any_value(`subjectname`),AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`

GROUP BY r.`subjectno`          -- 通过什么字段来分组
HAVING 	AVG(`studentresult`)>= 80  -- 分组过滤的记录必须满足的次要条件

4.8 SELECT小结

在这里插入图片描述

5 MySQL函数

5.1 常用函数

  • 数学运算
    SELECT ABS(-8) – 绝对值(8)
    SELECT CEILING(9.4) – 向上取整(10)
    SELECT FLOOR(9.4) – 向下取整(9)
    SELECT RAND() – 返回0-1随机数
    SELECT SIGN(-10) – 判断一个数的符号 负数返回-1 正数返回1
  • 字符串函数
    SELECT CHAR_LENGTH(‘2323232’) – 返回字符串长度
    SELECT CONCAT(‘我’,‘233’) – 拼接字符串
    SELECT INSERT(‘java’,1,2,‘cccc’) – 从某个位置开始替换某个长度(第1个开始替换2个)
    SELECT UPPER(‘abc’) --转大写
    SELECT LOWER(‘ABC’) --转小写
    SELECT INSTR(‘kuangshen’,‘h’) – 返回指定值索引
    SELECT REPLACE(‘坚持就能成功’,‘坚持’,‘努力’) – 替换指定字符串
    SELECT SUBSTR(‘坚持就能成功’,3,2) – 返回指定的字符串(第3个开始返回2个)
    SELECT REVERSE(‘坚持就能成功’) – 反转字符串
  • 查询姓 周 的同学 ,改成 邹
    SELECT REPLACE(studentname,‘周’,‘邹’) FROM student
    WHERE studentname LIKE ‘周%’
    时间跟日期函数(记住)
    SELECT CURRENT_DATE() – 获取当前日期
    SELECT CURDATE() – 获取当前日期
    SELECT NOW() – 获取当前日期和时间
    SELECT LOCATIME() – 获取本地日期时间
    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()最小值

5.3 数据库级别MD5加密(拓展)

什么是MD5
MD5不可逆,具体的MD5是一样的
MD5破解原理,背后有一个字典,将以前比较简单的 MD5加密后的值 和 加密前的值 进行比对,复制密码不容易实现破局

6 事务

6.1 什么是事务

要么都成功,要么都失败

SQL执行, A给B转账 A 1000–> 200 B200
SQL 执行, B收到A的钱 A800 — B400

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

事务原则 : ACID原则 原子性,一致性,隔离性,持久性 (脏读,幻读…)

参考博客链接:https://blog.csdn.net/dengjili/article/details/82468576
原子性(Atomicity)

  • 要么都成功,要么都失败

一致性(Consistency)

  • 事务前后的数据最终完整性要保持一致

持久性(Durability)–事务提交

  • 事务没有提交,恢复到原样
  • 事务一旦提交就不可逆转,被持久化到数据库中

隔离性(Isolation)

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

执行事务
在这里插入图片描述

SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启(默认的)

-- 手动处理事务
SET autocommit =0 -- 关闭自动提交

-- 事务开启

START TRANSACTION -- 标记一个事务的开始,从这个之后的SQP都在同一个事务内

INSERT XX
INSERT XX

-- 提交 : 持久化(成功提交)
COMMIT 

-- 回滚:  回到原来的样子(失败了回滚)
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交,恢复本来样子

-- 了解
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 撤销保存点

模拟转账场景:

CREATE DATABASE shop CHARSET 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'
UPDATE account SET money = money +500 WHERE `name`='B'

COMMIT; -- 提交事务
ROLLBACK;   -- 回滚
SET autocommit = 1; -- 恢复默认值

7 索引

MySQL官方堆索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
提取句子主干,就可以得到索引的本质:索引的数据结构。
在一个表中,主键索引只能有一个,唯一索引可以有多个

  • 主键索引 (PRIMARY KEY)
    • 唯一的标识,主键不可重复只能有一个列作为主键
  • 唯一索引 (UNIQUE KEY)
    • 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
  • 常规索引(KEY/INDEX)
    • 默认的,index,key关键字来设置
  • 全文索引(FULLTEXT)
    • 在特点的数据库引擎下才有,MyISAM
    • 快速定位数据
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT'性别(0:男;1:女)',
`password` VARCHAR(100) NOT NULL DEFAULT '' COMMENT'密码',
`age` TINYINT(4) DEFAULT '0' COMMENT'年龄',
`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 COMMENT'app用户表'

-- 插入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),'24736743@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 mock_data();


SELECT * FROM app_user WHERE `name`='用户9999' -- 接近1EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'  -- rows查询991749条记录才查到,所以比较慢

-- id_表名_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`); -- 0.001 s
SELECT * FROM app_user WHERE `name`='用户9999' -- 加索引的耗时0.001EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'  -- rows查询一条记录

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

7.1 索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上

索引的数据结构

8 权限管理和备份

8.1 用户管理

SQLyog 可视化管理

在这里插入图片描述
创建用户

-- mysql的用户表在 mysql.user 表中
USE mysql;

-- username:用户名称
-- %:是通配符指的是任意IP,这个位置也可以指定特定的ip,或者localhost代表本机才可以登录
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password'; 

-- 查看新创建的用户
SELECT * FROM USER WHERE USER = 'username';

-- 查看权限
SHOW GRANTS FOR 'username'@'localhost';

授权

  • 超级用户授权
-- grant:授权
-- all privileges:所有的权限
-- on *.*:在哪个数据库的那个表
-- to username@localhost:对哪个用户的哪个主机
-- with grant option:是不是将username用户自己本身的权限赋给其他账户
grant all privileges on *.* to 'username'@'localhost' with grant option;
-- 立即生效
flush privileges;
  • 普通用户授权
-- usage:无权限,当你想创建一个没有权限的用户时候,指定usage
-- show:查看的权限,赋权限报错了,我也不知道咋回事
-- view:视图的权限(mysql8.0+赋权限出错)ERROR 3619 (HY000): Illegal privilege level specified for VIEW,如下图所示。
-- create temporary tables:创建临时表的权限
-- excute:执行的权限
grant usage,select,insert,update,delete,create temporary tables,execute on *.* to username@localhost;

8.2 MySQL备份

为什么要备份:

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

MySQL数据库备份的方式

  • 直接拷贝 物理文件
  • 在SQLyog这种可视化工具中手动导出
    • 在想要导出的表或者库中,右键选择备份和导出
  • 使用命令行导出 mysqldump 命令行使用
# mysqldump -h 主机  -u 用户名  -p密码   数据库  表名 > 物流磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# mysqldump -h 主机  -u 用户名  -p密码   数据库  表1 表2 表3 > 物流磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student result >D:/b.sql

# mysqldump -h 主机  -u 用户名  -p密码   数据库  > 物流磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school  >D:/c.sql

# 导入表
# 登录后,切换到指定的数据库
# source 备份文件
USE school;	
source d:/a.sql

#不登录
mysql -u用户名 -p密码 库名 < 备份文件

假设你要备份数据库,防止数据丢失
把数据库给朋友,sql文件给别人即可

9 规范数据库设计

9.1 为什么需要设计

当数据库比较复杂的时候,我们就需要设计了

1.糟糕的数据库设计:

  • 数据冗余,浪费空间
  • 数据库插入和删除都会麻烦,异常【屏蔽使用物理外键】
  • 程序的性能差

2.良好的数据库设计:

  • 节省内存空间
  • 保证数据库的完整性
  • 方便我们开发系统

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

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

设计数据库的步骤(个人博客举例)

  • 收集信息,分析需求
    • 用户表
      用户id,用户名,用户密码,电话…
    • 分类表
      分类id,文章名称,文章作者id…
    • 文章表
      文章id,文章标题,作者id,分类id,文章内容,创建时间,修改时间,点赞…
    • 评论表
      评论id,所属文章id,评论人id,评论内容,评论时间,回复人的id…
    • 友链表
      友链id,网站名称,网站链接,排序
    • 自定义表
      系统信息,某个关键的字,或者某些主字段
    • 说说表
      发表心情 … id…content…create_time
  • 标识实体(把需求落地到每个字段)
  • 标识实体之间的关系
    • 写博客:user–>blog
    • 创建分类:user–>category
    • 关注:user–>user
    • 友链:links
    • 评论:user–>user->blog

练习辅助:bbs,crm,Ant Design Pro

9.2 三大范式

为什么需要数据规范化?

  • 信息重复
  • 更新异常
  • 插入异常
  • 删除异常
    • 无法正常显示异常
  • 删除异常
    • 丢失有效的信息

三大范式
第一范式(1NF)

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

第二范式(2NF)

前提:满足第一范式

每张表只描述一件事情

第三范式(3NF)

前提:满足第一范式 和 第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。

(规范数据库的设计)

规范性和性能的问题

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

  1. 考虑商业化的需求和目标(成本和用户体验) 数据库的性能更加重要
  2. 再规范性能的问题的时候,需要适当的考虑一下,规范性
  3. 故意给某些表加一些冗余的字段(从多表查询中变成单表)
  4. 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)

10 JDBC(重点)

10.1 数据库驱动

驱动: 声卡,显卡,数据库
在这里插入图片描述
我们的程序会通过数据库驱动,和数据库打交道!
但是如果每一种数据库就要写一种驱动,那会产生大量的工作,所以出现出现了JDBC

10.2 JDBC

SUN 公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范 俗称 JDBC这些规范的实现由具体的厂商去做
对于开发人员来说,我们只需要掌握JDBC的接口操作即可
在这里插入图片描述
需要使用的包:
java.sql
javax.sql
还需要导入数据库驱动包 mysql-connector-java-5.1.47.jar
8. 在这里插入图片描述

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.导入数据库驱动,创建lib目录,将驱动复制到lib目录中,添加库中 Add as Library…
3.编写测试代码

package com.CHAIN.lesson1;
import java.sql.*;

/**
 * @author CHAIN
 * 我的第一个JDBC程序
 */
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,8.0以上版本自动加载驱动
        //2. 用户信息和url
        //useUnicode=true&characterEncoding=utf8&&useSSL=false --》 安全链接
        String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8";//8.0最后需要加上时区
        String name = "root";
        String password = "123456";
        //3. 连接成功,返回数据库对象  connection代表数据库
        Connection connection = DriverManager.getConnection(url, name, password);
        //4. 执行SQL的对象 statement 执行SQL的对象
        Statement statement = connection.createStatement();

        //5. 执行SQL的对象 去执行SQL   可能存在结果,查看返回结果
        String sql="SELECT * FROM users";
        //执行sql
        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"));
        }
        //6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结:

  1. 加载驱动
  2. 连接数据库 DriverManager
  3. 获取执行SQL的对象 Statement
  4. 获得返回的结果集
  5. 释放连接

DriverManager

//推荐使用第二种,第二种中已经包含第一种
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法


Connection connection= DriverManager.getConnection(url,name,password);
//connection代表数据库,数据库对象
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8";

//mysql 默认3306
//协议://主机地址:端口号/数据库名?参数1&参数2&参数3&参数4

//Oracle -- 1521
//jdbc:oralce:thin:@localhost:1521:sid

statement 执行SQL的对象
statement 和 PrepareStatement 都是执行SQL的对象

String sql="SELECT * FROM users";//编写Sql

statement.executeQuery(); 	//查询操作,返回 ResultSet
statement.execute();		//执行任何SQL (执行所有SQL所以效率相对来说低)
statement.executeUpdate();	//更新,插入,删除,都是用这个。返回一个受影响的行数

ResultSet 查询的结果集:封装了所有的查询结果
获得指定的数据类型

ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询的结果
        resultSet.getObject();//在不知道列类型下使用
        resultSet.getString();//如果知道则指定使用
        resultSet.getInt();
        resultSet.getFloat();
        resultSet.getDate();

遍历,指针

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

释放内存

     resultSet.close();
    statement.close();
    connection.close(); //耗资源,用完关掉

10.4 statement对象

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

  • Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sq|语句, executeUpdate执行完后, 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
  • Statement.executeQuery方法用于向数据库发生查询语句,executeQuery方法返回代表查询结果的ResultSet对象
    CRUD操作-create
    使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
 Statement statement = connection.createStatement();
        String sql = "insert into user(...) values(...)";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("插入成功");
        }

CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作,示例操作:

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

CURD操作-update
使用executeUpdate(String sql)方法完成数据修改操作,示例操作:

Statement statement = connection.createStatement();
        String sql = "select * from  user where id =1";
        ResultSet rs= statement.executeQuery(sql);
        if(rs.next()){
            System.out.println("");
        }

CURD操作-read
使用executeQuery(String sql)方法完成数据查询操作,示例操作:

代码实现
1.提取工具类

  • 配置类
    一般会把配置类单独写个db.properties
driver=com.mysql.cj.jdbc.Driver
url =jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username = root
password = 123456
  • 创建工具类
package com.CHAIN.lesson2.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author CHAIN
 */
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 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.编写增删改的方法 exectueUpdate

  • 添加
package com.CHAIN.lesson2;

import com.CHAIN.lesson2.utils.JdbcUtils;

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

/**
 * @author CHAIN
 */
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,'CHAIN','123456','123456@qq.com','2020-01-01')";
            int i = st.executeUpdate(sql);//受影响的行数
            if (i > 0) {
                System.out.println("插入成功!");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}
  • 删除
package com.CHAIN.lesson2;

import com.CHAIN.lesson2.utils.JdbcUtils;

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

/**
 * @author CHAIN
 */
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);
        }
    }
}
package com.CHAIN.lesson2;

import com.CHAIN.lesson2.utils.JdbcUtils;

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

/**
 * @author CHAIN
 */
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 ='CHAIN' ,`email` = '123123@qq.com' 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

SQL注入问题
sql存在漏洞,会被攻击导致数据泄露 SQL会被拼接 or

package com.CHAIN.lesson2;

import com.CHAIN.lesson2.utils.JdbcUtils;

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

/**
 * @author CHAIN
 */
public class SqlInject {
    public static void main(String[] args) {
     login(" 'or '1=1"," 'or ' 1=1" );  //SQL注入
    }
    // 登录业务
    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` = 'CHAIN' AND `PASSWORD` = '123456'; 保证下面String语句和这个相匹配,注意双引号和单引号的位置
            //SELECT * FROM users WHERE `NAME` = ' ' or '1=1' AND `PASSWORD` = '' or '1=1';
            String sql =  "SELECT * FROM users WHERE `NAME` = '"+username+"' AND `PASSWORD` = '"+password+"'";
            rs = st.executeQuery(sql);   // 查询完毕,返回一个结果
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

    }

10.5 PreparedStatement对象

PreparedStatement 可以防止SQL注入 ,效率更高

  1. 新增
package com.CHAIN.lesson03;

import com.CHAIN.lesson2.utils.JdbcUtils;

import java.sql.*;

/**
 * @author CHAIN
 */
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`) value(?,?,?,?,?)";
            st = conn.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
            //手动给参数赋值
            st.setInt(1,11);
            st.setString(2,"CHIAN");
            st.setString(3,"321123");
            st.setString(4,"456654@qq.com");
            //注意:
            //sql.Date() 数据库    setDeta()需要的是 java.sql.Date(),但是无法直接获取
            //util.Deta Java  	  需要 Java的 new Deta().getTime() 时间戳 转换
            //参考博客:https://www.cnblogs.com/satire/p/13995067.html
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
            // 执行
           int i = st.executeUpdate();
            if (i > 0 ){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }

    }
}

  1. 删除
package com.CHAIN.lesson03;

import com.CHAIN.lesson2.utils.JdbcUtils;

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

/**
 * @author CHAIN
 */
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,11);

            // 执行
            int i = st.executeUpdate();
            if (i > 0 ){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }

    }
}

  1. 更新
package com.CHAIN.lesson03;

import com.CHAIN.lesson2.utils.JdbcUtils;

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

/**
 * @author CHAIN
 */
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,"QWE");
            st.setInt(2,1);

            // 执行
            int i = st.executeUpdate();
            if (i > 0 ){
                System.out.println("更新成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }

    }
}

  1. 查询
package com.CHAIN.lesson03;

import com.CHAIN.lesson2.utils.JdbcUtils;

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

/**
 * @author CHAIN
 */
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();
            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}
  1. SQL注入
package com.CHAIN.lesson03;

import com.CHAIN.lesson2.utils.JdbcUtils;

import java.sql.*;

/**
 * @author CHAIN
 */
public class TestInject {
    public static void main(String[] args) {
        login("' 'or 1 = 1"," 'or ' 1=1" );  //SQL注入
    }
    // 登录业务
    public  static  void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();  //获取数据库连接
            // PreparedStatement防止SQL注入的本质,把传递进来的参数当做字符
            //假设其中存在转义字符,比如`会被直接转义
            String sql =  "SELECT * FROM users WHERE `NAME` =? AND `PASSWORD` =?"; // Mybatis
            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);
            
            rs = st.executeQuery();   // 查询完毕,返回一个结果
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

prepareStatement() 防止 SQL 注入的本质,就是把传递进来的参数当做字符
假设其中存在转义字符,比如说 ’ 会被直接转义

10.6 使用IDEA连接数据库

注意:提前导入工具包
在这里插入图片描述

  • 社区版本安装database插件:
    file ->settings ->plugins 选中marketplace 搜Database,安装完毕重启
    在view - >tool window->DB browser
    在这里插入图片描述

  • 连接数据库
    选择Custom进行创建
    在这里插入图片描述
    输入账号密码,还有URL:jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC
    选择数据库类型:MySQL
    在这里插入图片描述

  • 查看表信息:双击数据库目录打开
    在这里插入图片描述

  • 更新数据:输入需要更改的信息
    在这里插入图片描述

  • 编写sql代码的地方
    在这里插入图片描述

10.7 JDBC事务

要么都成功,要么都失败
ACID原则

  • 原子性:要么全部完成,要么都不完成
  • 一致性:结果总数不变
    *** 隔离性:多个进程互不干扰**
  • 持久性:一旦提交不可逆,持久化到数据库了
    隔离性的问题
  • 脏读: 一个事务读取了另一个没有提交的事务
  • 不可重复读:在同一个事务内,重复读取表中的数据,表发生了改变
  • 虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
    代码实现
  1. 开启事务conn.setAutoCommit(false);
  2. 一组业务执行完毕,提交事务
  3. 可以在catch语句中显示的定义回滚语句,但是默认失败也会回滚
package com.CHAIN.lesson;

import com.CHAIN.lesson2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author CHAIN
 */
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 (Exception e) {
            //如果不写,若失败,也会回滚
            try {
                conn.rollback();//如果失败则回滚事务
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.8 数据库连接池

数据库连接----执行完毕----释放
连接–释放 (十分浪费资源)
池化技术: 准备一些预先的资源,过来就连接预先准备好的
常用连接数:10

  • 最少连接数:10
  • 最大连接数 : 15 业务最高承载上限
  • 排队等待,等待超时:100ms

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

开源数据源实现(拿来即用)

  • DBCP
  • C3P0
  • Druid:阿里巴巴

使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了
DBCP
配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

#!-- 初始化连接 --
initialSize=10

#最大连接数量
maxActive=50

#!-- 最大空闲连接 --
maxIdle=20

#!-- 最小空闲连接 --
minIdle=5

#!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】
#注意:user 与 password 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

需要用到的jar包commons-dbcp-1.4.jarcommons-pool-1.6.jar
DBCP拥有自己的配置文件,数据名字都是固定的,不能随便修改
创建DBCP工具类,只需要修改两步
使用只需要激活conn = JdbcUtils_DBCP.getConnection();

package com.CHAIN.lesson05.utils;

import com.CHAIN.lesson2.utils.JdbcUtils;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
 * @author CHAIN
 */
public class JdbcUtils_DBCP {
    private static DataSource dataSource = null;

    static {
        try{

            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);
            //1.创建数据源 工厂模式 -> 创建对象
            dataSource = BasicDataSourceFactory.createDataSource(properties);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws Exception{
        //2.从数据源中获取连接
        return dataSource.getConnection();
    }
    //释放资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {

        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();
            }
        }
    }
}

使用只需要激活conn = JdbcUtils_DBCP.getConnection();JdbcUtils_DBCP.release(conn,st,rs);
C3P0
需要的jar包:c3p0-0.9.5.5.jarmchange-commons-java-0.2.19.jar
C3P0拥有自己的配置文件,可以添加不同的数据源
创建C3P0工具类
只需要改一个位置

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class C3P0 {
    private static DataSource dataSource = null;
   
    static {
        try{
            
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);
            //1.创建数据源 工厂模式 -> 创建对象
           dataSource = BasicDataSourceFactory.createDataSource(properties);
            

    } catch (Exception e) {
        e.printStackTrace();
    }
}
//获取连接
public static Connection getConnection() throws Exception{
    //2.从数据源中获取连接
    return dataSource.getConnection();	
}
//释放资源
public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {

    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();
    }
    }
}
}

结论:

  • 无论使用什么数据源,本质是不变的,DateSource接口不会变,方法就不会变
  • Apache 软件基金会
  • 有自己的配置文件,数据名字都是固定的,不能随便修改
  • 创建DBCP工具类,只需要修改两步
  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值