Mysql笔记

最新推荐文章于 2024-03-07 19:59:53 发布
最新推荐文章于 2024-03-07 19:59:53 发布
阅读量1.7k 收藏 2
点赞数
分类专栏： Mysql 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46208220/article/details/127039379
版权

MySQL

1.初识MySQL

javaEE:企业级java开发

前端（页面：展示，数据）

后台（连接点 ：连接数据库JDBC，链接前端（控制，控制视图跳转，和给前端传递数据））

数据库（存数据，Txt,Excel,word）

1.1什么是数据库（DB）

概念： 数据仓库，软件，安装在操作系统之上（windows,linux,mac…）

​ 之上！SQL语句，可以存储大量数据

作用：数据存储，管理数据

1.2数据库分类

  • 关系型数据库(SQL)：
    • MySQL，Qracle，SqlServer，DB2，SQLLite
    • 通过表和表之间，行和列之间的关系进行数据的存储
  • 非关系型数据库(NoSQL) Not Only ：
    • Redis，MongDB
    • 对象存储，通过对象自身的属性来决定

DBMS(数据库管理系统)

  • 数据库管理软件，科学有效管理数据，维护和获取数据
  • MySQL，数据库管理系统

1.3MySQL

  • 关系型数据库管理系统
  • 使用SQL语言
  • 前世：瑞典MySQL AB公司 今生：属于Oracle旗下的产品
  • MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
  • 开源数据库软件
  • 体积小，速度快，总体拥有成本低
  • 中小型网站或者大型网站（可以做集群） （阿里买云存储数据库）
  • 5.7（稳定） 8.0 两个版本数据库驱动不一样
  • 安装建议
    • 尽量不要使用exe，因为很容易卸载不干净导致卸载失败
    • 尽可能使用压缩包zip安装

1.4安装MySQL

  1. 解压压缩包

  2. 把这个包放入电脑环境目录下

  3. 添加环境变量

    1. 我的电脑–>属性–>高级环境变量

    2. 选择系统变量PATH在其后面添加你的mysql安装文件下面的bin文件夹

    3. 在mysql-5.7.19下新建 my.ini文件

    4. 编辑my.ini文件注意替换路径位置

      目录一定要换成自己的

      data文件不需要新建会自己生成

    5. 启动管理员模式的CMD，并将路径切换到mysql下的bin目录，然后输入mysqld -install(安装mysql)

    6. 再输入 mysqld --initialize-insecurre --user=mysql初始化数据文件

    7. 然后再启动mysql然后用命令 mysql -u root -p进入mysql管理界面（密码可以为空）（-p之后不要加空格！）

    8. 进入界面后更改root密码

      update mysql.user set authentication_string=password(‘123456’) where user = ‘root’ and Host = ‘localhost’;（sql语句后面一定要加分号！）

      最后输入 flush privileges ; 刷新权限

    9. 修改my.ini文件删除最后一句skip-grant-tables 加个#就行

    10. 重启mysql即可正常使用

      net stop mysql

      net start mysql

    11. 连接上测试出现以下结果就安装好了

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iGyhK5IE-1664093911040)(E:\UniversityFile\STUDY\markdown\java笔记\图片\mysql\mysql2)]

1.5连接数据库

命令行连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6GkdIVLM-1664093911041)(E:\UniversityFile\STUDY\markdown\java笔记\图片\mysql\mysql3)]

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


update mysql.user set authentication_string=password('123456') where user = 'root' and Host = 'localhost';--修改用户密码

 flush privileges ; --刷新权限
 
 show databases; --展示所有数据库
 
 user 数据库名; --切换数据库
 
 show tables; --查看该数据库中所有的表
 
 describe 表名; --查看表的所有信息


--sql的单行注释
/* */sql的多行注释

数据库xxxx语言 CRUD增删改查

DDL 定义

DML 操作

DQL 查询

DCL 控制

2.操作数据库

操作数据库 操作数据库中的表 操作数据库中表的数据

mysql关键字不区分大小写

2.1操作数据库（了解）

  1. 创建数据库

    IF NOT EXISTS加上防止数据库名重复导致的报错

    CREATE  DATABASE IF NOT EXISTS `数据库名字`

  2. 删除数据库

    DROP DATABASE IF EXISTS  `数据库名字`

  3. 使用数据库

USE `数据库名`
USE '使用关键词命名的数据库名'
  1. 查看数据库
SHOW DATABASES  --查看所有数据库

学习思路：

  • 对照sqlyog的历史记录查看sql
  • 固定的语法或关键字必须记住

2.2Sqlyog

在这里插入图片描述

在这里插入图片描述

2.3数据库的列类型

数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等小的数据 3个字节
  • int 标准的数据 4个字节 常用
  • big 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节
  • decimal 字符串形式的浮点数 金融计算一般用这个适合算钱

字符串

  • char 字符串 0-255
  • varchar 字符串 0-65535 常用的String
  • tinytext 微型文本 2^8-1
  • text 文本串 2^16-1 保存大文本

时间和日期

Java.util.Date

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

null

  • 没有值：未知
  • 注意，不要使用NULL进行运算，如果使用了结果一定为Null

2.4数据库的字段属性（重点）

Unsigned:

  • 无符号整数
  • 不能声明为负数

zerofill:

  • 0填充的
  • 不足的位数，使用0来填充，int(长度为3) 写入5 显示005

自增：

  • 通常理解为自增，自动再上一条记录的基础上+1（默认）

  • 通常用来设计唯一的主键-index ,必须是整数类型

  • 可以自定义设计主键自增的起始值和步长
    在这里插入图片描述

非空 Null not null：

  • 假设设置为not null,如果不给它赋值，就会报错!
  • Null,如果不赋值，默认为null！

默认default:

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

项目里，每张表里都必须存在的五个字段，表示一个记录存在的意义：

id 主键

version 乐观锁

is_delete 伪删除

gmt_create 创建时间

gmt_upddate 修改时间

2.5创建数据库(重点)

-- 注意点，使用英文(),表的名称和字段尽量使用``（这个是点非单引号）
-- 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 `file`  -- 查看创建数据库的语句
SHOW CREATE TABLE `student`  -- 查看创建表的语句
DESC `student`  -- 显示表的结构

2.6数据表的类型

在这里插入图片描述

  • InnoDB

​ 默认使用

  • MyISAM

​ 早些年使用的

MyISAMInnoDB
事务支持不支持支持
数据行锁定不支持 表锁支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约为MyISAMliangbei

常规操作：

  • MyISAM 节约空间，速度较快

  • InnoDB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有数据库文件都存在data目录下

本质还是文件的存储

MySQL引擎在物理文件的区别

  • InnoDB在数据库表中只有一个*.frm文件，以及上一级目录的ibdata1文件
  • MyISAM 对应文件
    • *.frm 表结构的定义文件
    • *.MYD 数据文件(data)
    • *.MYI 索引文件（index）
  • 地方

设置数据库表的字符集编码

 CHARSET=utf8

不设置，会是mysql默认的字符集编码Latin1（不支持中文）

可以在my.ini中配置默认的编码

character-set-server=utf8

2.7修改删除表

修改

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

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

-- 修改表的字段 （重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 属性
ALTER TABLE `student1` MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 属性
ALTER TABLE `student1` CHANGE age age1 INT(11) -- 重命名
-- modify 不能用来重命名，只能修改字段类型和约束
-- change 用来字段重命名，不能修改字段类型和约束

删除

-- 删除表的字段
-- ALTER TABLE 表名 DROP 删除的字段名
ALTER TABLE `student1` DROP age1

-- 删除表
-- DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS student1

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

注意点：

  • `` 所有字段名用点符号包裹
  • 注释 --（记得加一个空格） /**/
  • sql关键字大小写不敏感，建议写小写
  • 所有符号用英文

3.MySQL数据管理

3.1外键（了解即可）

方式一 在创建表的时候，增加约束（麻烦比较复杂）

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

--  KEY `FK_gradeid` (`gradeid`),
-- CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
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

删除有外键关系的表的时候，必须先删除引用别人的表（从表）

方式二 创建表成功后添加外键约束

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 表名([字段名1，字段名2，字段名3])values('值1','值2','值3')

-- 由于主键自增我i们可以省略（如果不写表的字段，他就会一一匹配）
-- 一般写插入语句，我们一定要数据和字段一一对应
INSERT INTO `grade`(`gradename`) VALUES ('大三')

-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大三'),('大四')

INSERT INTO `student` VALUES (2,'李四','1234556','男','2002-2-3',3,'地址','email')

注意事项：

  • 字段和字段之间使用英文逗号隔开
  • 字段可以省略，但是后面的值必须一一对应，不能少
  • 可以同时插入多条数据，VALUES后面的值需要使用逗号隔开VALUES(),(),()

3.4修改

update 修改（条件） set

语法：UPDATE 表名 SET 列名1 = 具体的值 WHERE 条件

UPDATE 表名 SET 列名1 = 具体的值 ,列名2 = 具体的值 …列名n = 具体的值 WHERE 条件

-- 修改学员名字、带了条件
UPDATE `student` SET `name` = 'lmj' WHERE id = 2 

-- 不指定条件的情况下，会改变所有的表
UPDATE `student` SET `name` = 'lmj' 

-- 修改多个属性
UPDATE `student` SET `name` ='lbyby',`email`='1234@qq.com' WHERE id =2

-- 通过多个条件定位数据

UPDATE `student` SET `name` = 'lmj' WHERE id = 2 AND `name`='lbyby'

条件：where 子句 运算符 id等于某个值，大于某个值，在某个区间内修改

操作符会返回布尔值

操作符含义范围结果
=等于5=6false
<>或!=不等于5!=6true
>大于
<小于
>=大于等于
<=小于等于
BETWEEN…AND…Z在某个范围内BETWEEN 2 AND. 5【2，5】
AND我和你&&5>1 and 1>2false
OR我或你 ||5>1 or 1>2true

注意：

  • 列名是数据库的列尽量带上``
  • 条件，筛选的条件,如果没有指定，则会修改所有的列
  • value,是一个具体的值，也可以是一个变量 例如可以是时间（CURRENT_TIME）前提是该列的类型本身就是日期
  • 多个设置的属性之间，使用英文逗号隔开

3.5删除

delete

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

-- 删除数据（避免这样写会全部删除）
DELETE FROM `student1` 

-- 删除数据
DELETE FROM `student1` WHERE id=1

truncate

语法：TRUNCATE TABLE 表名

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

-- 清空student1表
TRUNCATE `student1`

delete和truncate区别

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


INSERT INTO `text` (`coll`) VALUES('123'),('23'),('3')

-- 不会影响自增
DELETE FROM `text`

-- 自增会归0
TRUNCATE TABLE `text`

delete删除的问题，重启数据库，现象

  • innoDB 自增列会从1开始（存在内存中 重启会清零）
  • MyISAM 继续从上一个自增量开始（存在文件中的不会丢失）

4.DQL查询数据（最重点）

4.1 DQL（Date Query LANGUAGE:数据查询语言）

  • 所有的查询操作都用它
  • 简单的查询，复杂的查询都能做
  • 数据库最核心语言
  • 使用频率最高

4.2 指定查询字段

语法：SELECT 字段 FROM 表

有的时候列名可以起别名（AS） 字段名 AS 别名 表名 AS 别名

-- 查询全部学生 
-- SELECT 字段 FROM 表
SELECT * FROM result

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 别名，给结果起一个名字 可以给字段取别名，也可以给表取别名
SELECT `studentno` AS 学号 , `studentname` AS 学生姓名 FROM student AS s

-- 函数Concat(a,b)拼接
SELECT CONCAT('姓名',`studentname`) AS 新姓名 FROM student

去重 distinct

作用：去除SELECT语句查询出来结果中重复的数据，只显示一条

-- 查询一下哪些同学参加了考试，成绩
SELECT * FROM result -- 查询全部的考试成绩
-- 查询有哪些同学参加了考试
SELECT `studentno` FROM result
-- 发现重复数据 去重
SELECT DISTINCT `studentno` FROM result

数据库的列（表达式）

-- 查询系统版本（函数）
SELECT VERSION() 
-- 用来计算（表达式）
SELECT 100*3-1 AS 计算结果
-- 查询自增的步长（变量）
SELECT @@auto_increment_increment


-- 学员考试成绩+1分后查看
SELECT `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 
SELECT `studentno`,`studentresult` FROM result 
WHERE `studentresult` >= 95 && `studentresult` <= 100 
-- 模糊查询（区间）
SELECT `studentno`,`studentresult` FROM result 
WHERE `studentresult` BETWEEN 95AND 100

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

模糊查询：比较运算符

运算符语法描述
IS NULLa is null如果操作符为null则结果为真
IS NOT NULLa is not null如果操作符为 not null则结果为真
BETWEEN ANDa between b and c若a在b 和c之间则结果为真
Likea like bSQL匹配 如果a匹配到b,则结果为真
INa in (a1,a2,a3…)如果a在a1,a2,a3…其中某一点中结果为真

  • -like 结合 %（0到任意一个字符） _（一个字符）

    (% _ 只能在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 '%真%'

-- in（具体的一个或多个的值）
-- 查询1001 1002 1003号学员信息
SELECT `studentno`,`studentname` FROM student
WHERE `studentno` IN ('1001','1002','1003')

-- 查询在上海的学生
SELECT `studentno`,`studentname` FROM student
WHERE `address` IN ('上海')

--  null   not null
-- 查询地址为空的学生
SELECT `studentno`,`studentname` FROM student
WHERE `address`='' OR `address` IS NULL

-- 查询有出生日期的同学
SELECT `studentno`,`studentname` FROM student
WHERE `borndate` IS NOT NULL 

-- 查询没有出生日期的同学
SELECT `studentno`,`studentname` FROM student
WHERE `borndate` IS NULL

4.4 联表查询 join

join 对比

join （连接的表） on(判断条件)

a left join b 以a表为基准

a right join b 以b表为基准

在这里插入图片描述
在这里插入图片描述

-- 联表查询
-- 查询参加了考试的同学（学号 姓名 科目编号 分数）
/*思路
1. 分析需求，分析查询的字段来自哪些表（连接查询）
2.确定使用哪些连接查询  7种
确定交叉点 （两个表中哪些数据是相同的）
判断的条件（学生表中的`studentno`== result表中的`studentno`）
*/
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s INNER JOIN `result` AS r
WHERE s.`studentno`=r.`studentno`

-- right join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student`  s RIGHT JOIN `result` r
ON s.`studentno`=r.`studentno`

-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student`  s LEFT JOIN `result` r
ON s.`studentno`=r.`studentno`

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

-- 查询参加考试的同学的信息（学号，学生姓名，科目名称，分数）
-- （student,result subject）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s RIGHT JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`

-- 查询学员所在的年级（学号  学生的姓名  年级名称）
SELECT `studentno`,`studentname`,`gradename`
FROM `student` s INNER JOIN `grade` g
WHERE s.`gradeid`=g.`gradeid`

-- 查询科目所属的年级
SELECT `subjectname`,`gradename`
FROM `subject` s INNER JOIN `grade` g
WHERE s.`gradeid` = g.`gradeid`

-- 查询参加了 数据库结构-1考试的同学信息（学号姓名，科目名，分数）
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'
操作描述
inner join如果表中至少有一个匹配
left join即使右表中没有匹配也会从左表中返回所有的值
right join即使左表中没有匹配也会从右表中返回所有的值
  1. 我要查询哪些数据select
  2. 从那几个表中查 from 表 xxxjoin 连接的表 on 交叉条件
  3. 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表

父类

categoryidcategoryname
2信息技术
3软件开发
5美术设计

子类

pid（父id）categcategoryidcategoryname
3(对应软件开发)4数据库
2（对应信息技术）8办公信息
3（对应软件开发）6web开发
5（对应美术设计）7ps技术

操作：查询父类对应的子类Id

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计ps技术
-- 查询父子信息  把一张表看成一样的表

SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category`AS b
WHERE a.`categoryid`=b.`pid`

4.5分页limit和排序order by

排序 ASC升序 DESC降序

语法： ORDER BY 通过哪个字段排序 怎么排

-- ASC升序  DESC降序
-- ORDER BY 通过哪个字段排序  怎么排
-- 查询的结果根据成绩降序排序
SELECT s.`studentno`,`studentname`,`subjectname`, `studentresult`
FROM `student` s INNER JOIN  `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE `subjectname`='数据库结构-1'
ORDER BY `studentresult` DESC

分页

语法：LIMIT a,b 从第a个数据开始，每页显示b个数据

分页原因：

  1. 缓解数据库压力
  2. 给人体验更好
  3. 瀑布流

第一页 0-5

第二页 5-5

第三页10-5

第n页 （n-1）*pagesize （起始值）, pagesize（页面大小）

n:当前页 数据总数/页面大小=总页数

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

4.6子查询和嵌套查询

where(值是这个值是计算出来的)

本质：在where语句中嵌套一个子查询语句

where(select*from)

-- 1.查询数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT `studentno`,`subjectname`,`studentresult`
FROM `result` r INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE `subjectname`= '数据库结构-1'
ORDER BY `studentresult` DESC

-- 方式二：使用子查询
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result` 
WHERE `subjectno` = (
-- 查询所有数据库结构-1的学生学号
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 `studentresult` >=80

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

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

SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN(
SELECT `studentno` FROM `result`
WHERE `studentresult`>=80 AND `subjectno` =(
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-2'
)
)

4.7 分组以及过滤

-- 查询不同课程 平均分 最高分 最低分 平均分大于80的
-- 核心：根据不同课程分组
SELECT `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小结

SELECT [ALL |DISTINCT]
{*|table.* |[tabke.field1[as alias11][]....]}
FROM table_name {as table_alies}
[left |right |inner  join table_name] -- 联合查询
[WHERE...]-- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段进行分组
[HAVING ...] -- 过滤分组的记录需要满足的次要条件
[ORDER BY ..] -- 指定查询记录按一个或多个条件排序
[LIMIT ()] -- 指定查询从几条到几条

5、MySQ常用函数

官网https://dev.mysql.com/doc/refman/8.0/en/

5.1、常用函数

-- 常用函数  ，聚合函数

-- 数学运算
SELECT ABS(-8)  -- 绝对值
SELECT CEILING(6.4) -- 向上 取整
SELECT FLOOR(6.4) -- 向下取整
SELECT RAND()  -- 返回一个0-1之间的随机数
SELECT SIGN(8)  -- 判断一个数的符号

-- 字符串函数
SELECT CHAR_LENGTH('即使在校的人也能逃跑')  -- 字符串长度
SELECT CONCAT('我'，,'爱','咖啡') -- 拼接字符串
SELECT INSERT('我爱编程',1,2,'超级热爱')  -- 查询，从某个位置开始替换某个长度
SELECT LOWER('LiuBaiYi') -- 转为小写字母
SELECT UPPER('LiuBaiYi') -- 转为大写字母
SELECT INSTR('liubaiyi','b') -- 返回第一次出现的字串的索引
SELECT  REPLACE('liubaiyi','liu','yoyoyoyoyo') -- 替换出现的指定字符串
SELECT SUBSTRING('坚持就能成功坚持就能成功',4,6) -- 从源字符串第4个开始截取长度为6的字符串
                                        -- SUBSTRING（源字符串，截取的位置，截取的长度）
SELECT REVERSE('abcdefg') -- 反转字符串
                                         
 -- 查询姓刘的同学 
 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() 可以查询一个表中有多少记录

执行效率上

COUNT(指定列) > COUNT(1) > COUNT(*)

-- 都能统计表中的数据
-- COUNT(指定列)   会忽略所有的null值
SELECT COUNT(`studentname`) FROM `student` 
 -- COUNT(*)  不会忽略null值 本质计算行数
SELECT COUNT(*) FROM `student` 
 -- COUNT(1)  不会忽略null值 用时短 本质计算行数
SELECT COUNT(1) FROM `student` 

SELECT SUM(`studentresult`) AS '总和' FROM `result`
SELECT AVG(`studentresult`) AS '平均分' FROM `result`
SELECT MAX(`studentresult`) AS '最高分' FROM `result`
SELECT MIN(`studentresult`) AS '最低分' FROM `result`

-- 查询不同课程 平均分 最高分 最低分 平均分大于80的
-- 核心：根据不同课程分组
SELECT `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

5.3、数据库级别的MD5加密（扩展）

什么是MD5？

主要增强算法和不可逆性

MD5不可逆，具体的值的MD5值是一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

注意：加密一次和多次加密后的加密文不一样

使用：MD5(要加密的数据)

-- 加密
UPDATE `testmd5` SET `pwd`=MD5(pwd) WHERE id=1
-- 加密全部的密码
UPDATE `testmd5` SET `pwd`=MD5(pwd)
-- 插入信息的时候加密
INSERT INTO `testmd5` VALUES (6,'哈奥好',MD5('123456'))
 
-- 如何校验：将用户传递进来的密码进行md5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name`='王五' AND pwd = MD5('123456')

6、事物 Transaction

6.1 什么是事物

要么都成功，要么都失败

将一组sql放到一个批次中去执行

事物原则：ACID原则 原子性atomicity，一致性consistency，隔离性isolation，持久性durability

​ （脏读，幻读，不可重复读）

  • 原子性atomicity
    • 针对同一个事物，例如一个过程中有两个步骤，原子性表示，这两个步骤要么一起成功，要么一起失败，不能只发生其中一个
  • 一致性consistency
    • 最终一致性
    • 针对一个事物操作前后的数据完整性保证一致
  • 隔离性isolation
    • 多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离
  • 持久性durability
    • 表示事物结束后的数据不随着外界原因导致数据丢失
    • 事物没有提交，则恢复到原状
    • 事务提交，则持久化到数据库
    • 事物一旦提交就不可逆了

隔离所导致的一些问题

脏读：一个事务读取了另外一个事物未提交的数据

不可重复读：在一个事务内读取表中的一行数据，多次读取结果不同（不一定是错误，只是某些场合不对）

幻读：在一个事务内读取到了别的事物插入的数据，导致前后读取不一致（一般是行影响，多了一行）

执行事务

-- mysql是默认开启事务自动提交的
SET autocommit = 0/*关闭*/
SET autocommit = 1/*开启（默认）*/

-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事物的开始，从这个以后的sql，都在同一个事务内

INSERT xx
INSERT xx

-- 提交：持久化（成功）
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; -- 回滚事务，若没提交前回滚，事务恢复
SET autocommit=1 ;  -- 恢复自动提交

COMMIT;-- 提交事务 ，就被持久化了 回滚不起作用
ROLLBACK; – 回滚事务，若没提交前回滚，事务恢复

7、索引

索引（Index）是帮助MySQL高效获取数据的数据结构，提取句子主干，就可以得到索引的本质：索引是数据结构

索引在小数据的时候区别不大，在大数据的时候区别十分明显

7.1、索引的分类

在一个表中主键索引只能有一个，唯一索引可以有多个

  • 主键索引：PRIMARY KEY
    • 唯一的标识，主键不可重复，只能有一个列作为主键
  • 唯一索引：UNIQUE KEY
    • 避免重复的列出现，唯一索引可以重复，多个列都可以表示为唯一索引
  • 常规索引：INDEX KEY / KEY
    • 默认的，index,key关键字来设置
  • 全文索引：FullText
    • 在特定的数据库引擎上才有，MyISAM
    • 快速定位数据
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.在下黄健完毕后，增加索引
-- 3. CREATE INDEX 索引名 ON 表名(字段名)

-- 显示所有索引信息
SHOW INDEX FROM student

-- 增加一个全文索引   索引名（列名）
ALTER TABLE`school`.`student` ADD FULLTEXT INDEX `studentname`(`studentname`);

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM `student`; -- 非全文索引

EXPLAIN SELECT * FROM `student` WHERE MATCH (`studentname`) AGAINST('刘')

7.2、测试索引

生成一百万条数据

-- 因为mysql的设置默认是不允许创建函数，所以使用该语句 但是重启会失效
SET GLOBAL log_bin_trust_function_creators = 1;
-- 插入100万数据.
DELIMITER $$ -- 写函数之前必须要写，标志

CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
   DECLARE num INT DEFAULT 1000000;
   DECLARE i INT DEFAULT 0;
   
   WHILE i<num DO
   -- 插入语句
     INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`,`password`,`age`)
     VALUES(CONCAT('用户',i),'123@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'
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999' 
-- rows 993314 查了993314才查到

-- 索引命名方式 id_表名_字段名
-- CREATE INDEX 索引名 ON 表名(字段名)
CREATE INDEX id_app_user_name ON `app_user`(`name`);

SELECT * FROM `app_user` WHERE `name`='用户9999';
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999' 
-- rows 1 第一条就查到了

7,3、索引原则

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

索引的数据结构

Hash类型的索引

Btree：innoDB默认的数据结构

阅读：https://blog.codinglabs.org/articles/theory-of-mysql-index.html

8、权限管理和备份

8.1、用户管理

sql yog可视化管理

点击小人
在这里插入图片描述

新建用户

在这里插入图片描述

SQL命令操作

用户表：mysql.user

本质：用户操作本质是对这张表进行增删改查

-- 创建用户
-- CREATE USER 表名 IDENTIFIED BY 密码
CREATE USER lmj IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456')

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR lmj = PASSWORD('123456')

-- 给用户重命名
-- rename user 旧名字 to 新名字
RENAME USER lmj TO lmjlmj

-- 用户授权  ALL PRIVILEGES全部的权限,库，表  
-- （*.*）（全部的库全部的表） 但是仍然不具有GRANT权限 无法给别人授权
GRANT ALL PRIVILEGES ON *.* TO lmjlmj

-- 查看权限
-- 查看指定用户权限
SHOW GRANTS FOR lmjlmj;
-- 查看root用户权限
SHOW GRANTS FOR root@localhost
-- 撤销权限
-- revoke 哪些权限  在哪个库，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM lmjlmj

-- 删除用户
DROP USER lmjlmj

8.2、MySQL备份

为什么备份：

  • 保证重要数据不丢失
  • 数据转移 A—>B

MySQL数据库备份方式

  • 直接拷贝物理文件
  • 在Sqlyog这种可视化工具中手动导出
    在这里插入图片描述
    在这里插入图片描述
-- mysqldump -h主机 -u用户名 -p密码 数据库名 ->物理磁盘位置
-- mysqldump -h主机 -u用户名 -p密码 数据库名 数据表名 ->物理磁盘位置
mysqldump -hlocalhost -uroot -p123456 数据库名 数据表名 ->D:/a.sql

-- 导出多张表
 mysqldump -h主机 -u用户名 -p密码 数据库名 表一 表二 表三 ->物理磁盘位置
 
 -- 登陆的情况下导入sql语句
 -- 如果要导入表就先切换到指定数据库  
 -- 导入数据库的话 直接导入就行
 use 数据库名
 source sql文件物理磁盘路径

在这里插入图片描述

9.规范数据库设计

9.1、为什么需要设计

数据库比较复杂的时候

良好的设计：

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

软件开发中，数据库的设计

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

设计数据库的步骤（个人博客）：

  • 收集信息，分析需求
    • 用户表（用户登陆注销，用户个人信息；写博客，创建分类）user
    • 分类表（文章分类，谁创建的）category
    • 文章表（文章的信息）comment
    • 友链表（友链信息）links
    • 自定义表（系统信息，某个关键字，或者一些著字段）key:value
    • 说说表（发表心情…id…content…create_time）
  • 标识实体（把需求落地到每个字段）、
  • 标识实体之间的关系
    • 写博客：user --> blog
    • 创建分类：user --> category
    • 关注：user --> user
    • 友链：links
    • 评论表：user-user-blog

9.2、三大范式

为什么需要数据规范化？

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

三大范式

  • 第一范式（1NF)：

    • 要求数据库表的每一列都是不可分割的原子数据项

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

    • 例子

在这里插入图片描述

  • 第二范式(2NF)：

    • 满足第一范式， 实体的属性完全函数依赖于主关键字

    • 需要确保数据库表的每一列都和主键相关，而不能只与主键的某一部分相关

    • 每张表只描述一个事情

    在这里插入图片描述
    在这里插入图片描述

    Y这一列的货物名称就完全依赖于主属性X的取值，共同决定，但是Z这一列只由主属性货物的类型这一列决定，是部分依赖，就不满足第二范式，如果要满足第二范式，就需要将这个表拆分为两个表。

  • **第三范式(3NF)： **

    • 满足第一范式和第二范式，任何非主属性不依赖于其它非主属性

    • 需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

在这里插入图片描述

商品ID字段依赖于订单ID，商品的颜色和商品的储存依赖于商品ID，所以订单ID字段和商品颜色，商品尺寸存在一个传递依赖，所以，不满足于第三范式。

规范性和性能的问题

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

  • 考虑商业化的需求和目标（成本，用户体验） 数据库性能更加重要
  • 在规范性能问题的时候，需要考虑一下规范性
  • 故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）
  • 故意增加一些计算列（从大数据量减为小数据量的查询，增加索引）

10、JDBC（重点）

10.1、数据库驱动

驱动：声卡，显卡，数据库
在这里插入图片描述

应用程序通过数据库驱动连接数据库

10.2、JDBC

SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库的）规范，俗称JDBC

这些规范的实现由具体的厂商完成

对于开发人员来说。只需要掌握JDBC接放入操作入口即可

在这里插入图片描述

java.sql

javax.sql

还需要导入一个数据库驱动

mysql-connector-java .jar

我的jar包下载路径：

C:\Users\ASUS.m2\repository\mysql\mysql-connector-java

10.3 第一个JDBC程序

  1. 创建一个普通项目

  2. 在这里插入图片描述

package com.liu.lession01;


import java.sql.*;

//我的第一个JDBC程序
public class jdbcFirstDemo01 {
     public static void main(String[] args) throws ClassNotFoundException, SQLException {
          //1.加载驱动
          //固定写法，加载驱动
          Class.forName("com.mysql.jdbc.Driver");
          //2.用户信息和url
          //?useUnicode=true&characterEncoding=utf8&useSSL=true设定字符集编码&设定字符集为utf-8&使用安全的连接
          String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
          String username = "root";
          String password ="123456";
          //3，连接成功，数据库对象 Connection 代表数据库
          Connection connection =DriverManager.getConnection(url,username,password);
          //4.执行SQL的对象
          Statement statement = connection.createStatement();
          //5.执行DQL的对象去执行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. 加载驱动Class.forName
  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,username,password);
//connection代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.setAutoCommit();
connection.commit();
connection.rollback();

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql默认端口号为3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
//oracle--1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement执行类 执行SQL的对象 prepareStatement

String sql = "SELECT * FROM `users`";//编写sql语句

statement.executeQuery();//查询操作 返回ResultSet
statement.execute();//执行任何sql  效率会低一点
statement.executeUpdate();//更新 插入 删除， 返回受影响的行数

ResultSet查询的结果集：封装了所有的查询结果

获得指定的数据类型

resultSet.getObject();//在不知道类型的情况下使用
resultSet.getString()；//如果知道类型就是用指定类型
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate()；
    
    
    
resultSet.next();//最常用 移动到下一个数据
resultSet.beforeFirst();//移动到最前面 从第一个数据开始查
resultSet.afterLast();//移动到最后面
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行

释放资源

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

10.4、Statement对象

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

Statement的executeUpdate(); 更新 插入 删除， 返回受影响的行数

Statement的executeQuery(); 查询操作 返回ResultSet

CRUD create

添加操作

Statement st = connection.createStatement();
String sql1 = "insert into....";
int num = st.executeUpdate(sql1);
if (num>0){
     System.out.println("插入成功");
}

CRUD delete

删除操作

Statement st = connection.createStatement();
String sql1 = "delete from ....";
int num = st.executeUpdate(sql1);
if (num>0){
     System.out.println("删除成功");
}

CRUD update

修改操作

Statement st = connection.createStatement();
String sql1 = "update user  ....";
int num = st.executeUpdate(sql1);
if (num>0){
     System.out.println("修改成功");
}

CRUD read

查询操作

Statement st = connection.createStatement();
String sql1 = "update user  ....";
int num = st.executeQuery(sql1);
if (num>0){
     System.out.println("查询成功");
}

代码实现

  1. 提取jdbuctils

    dp.properties文件里的内容

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

jdbuctils

package com.liu.lesson02.util;

import com.mysql.cj.protocol.Resultset;

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

public class jdbcUtils {

    private static String driver =null;
    private static String url =null;
    private static String username =null;
    private static String password =null;
    static{
        try{
            InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver =properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            //1.驱动只需要加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
       return DriverManager.getConnection(url,username,password);
    }
    //释放资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException 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();
            }
        }
    }

}

使用

新增

package com.liu.lesson02.util;

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 connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = jdbcUtils.getConnection();//获取数据库连接
            st = connection.createStatement();
            String sql ="INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(5,'hahaxi','123456','qwe@qq.com','2002-02-12')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
           jdbcUtils.release(connection,st,rs);
        }
    }
}

修改

package com.liu.lesson02.util;

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 connection = null;
        Statement st = null;
        ResultSet rs  = null;
        try {
            connection = jdbcutilsexercise.getConnection();
            st = connection.createStatement();
            String sql = "UPDATE `users` SET`name`='zhangzhang' WHERE id=6";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("修改成功!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcutilsexercise.release(connection,st,rs);
        }
    }
}

删除

package com.liu.lesson02.util;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = jdbcutilsexercise.getConnection();
            st = connection.createStatement();
            String sql = "delete from `users` where id=6";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcutilsexercise.release(connection,st,rs);
        }
    }
}

查询

package com.liu.lesson02.util;

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

public class TestRead {
    public static void main(String[] args) {
        Connection connection = null;
        Statement st =null;
        ResultSet rs = null;
        try {
            connection = jdbcutilsexercise.getConnection();
            st = connection.createStatement();
            String sql = "SELECT * FROM `users`";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println("name:"+rs.getObject("NAME"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcutilsexercise.release(connection,st,rs);
        }
    }
}

SQL注入

sql存在漏洞，会被攻击导致数据泄漏 SQL会被拼接

例子：

package com.liu.lesson02.util;

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

public class SQLzhuru {
    public static void main(String[] args) {
       // login("zhangsan","123456"); 正常登录
       login(" ' or' 1=1"," ' or' 1=1");
       //拼起来的语句是  name为空或1=1一直成立  就会把所有数据都查出来造成数据泄露
       //SELECT  * FROM `users` WHERE `NAME`='' or '1=1' AND `PASSWORD`='' or '1=1'";
    }
    //登陆业务
    public static void login(String username,String password){
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = jdbcUtils.getConnection();//获取数据库连接
            st = connection.createStatement();
            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"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            jdbcUtils.release(connection,st,rs);
        }
    }
}

// login(“zhangsan”,“123456”); 正常登录
login(" ’ or’ 1=1"," ’ or’ 1=1");
//拼起来的语句是 name为空或1=1一直成立 就会把所有数据都查出来造成数据泄露
//SELECT * FROM users WHERE NAME=‘’ or ‘1=1’ AND PASSWORD=‘’ or ‘1=1’";

10.5、PreparedStatement

PreparedStatement可以防止sql注入，并且效率更高

防止sql注入的本质：把传递进来的参数当作字符，假设其中存在转义字符会直接把他忽略 例如 ’

  1. 新增

    package com.liu.lesson03;

import com.liu.lesson02.util.jdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn =null;
        PreparedStatement st = null;
        try {
            conn = jdbcUtils.getConnection();
            //使用？占位符代替参数
            String sql ="INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(?,?,?,?,?)";
            //预编译sql 先写sql不执行
            st = conn.prepareStatement(sql);
            //设置参数
            st.setInt(1,6);
            st.setString(2,"aaa");
            st.setString(3,"123456");
            st.setString(4,"aaa@qq.com");
            //注意点 ：sql.Date是数据库的
            // new Date().getTime()获得时间戳 这里的的Date是util包下的
            st.setDate(5,new java.sql.Date(new Date().getTime()));

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

  2. 删除

    package com.liu.lesson03;

import com.liu.lesson02.util.jdbcUtils;

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

public class Testdelete {
    public static void main(String[] args) {
        Connection conn =null;
        PreparedStatement st = null;
        try {
            conn = jdbcUtils.getConnection();
            //使用？占位符代替参数
            String sql ="delete from `users` where id=?";
            //预编译sql 先写sql不执行
            st = conn.prepareStatement(sql);
            //设置参数
            st.setInt(1,6);
            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,null);
        }
    }
}

  3. 更新

    package com.liu.lesson03;

import com.liu.lesson02.util.jdbcUtils;

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


public class TestUpdate {
    public static void main(String[] args) {
        Connection conn =null;
        PreparedStatement st = null;
        try {
            conn = jdbcUtils.getConnection();
            //使用？占位符代替参数
            String sql ="update `users` set `NAME`=? WHERE `id` =?;";
            //预编译sql 先写sql不执行
            st = conn.prepareStatement(sql);
            //设置参数
            st.setString(1,"zhangda");
            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);
        }
    }
}

  4. 查询

package com.liu.lesson03;

import com.liu.lesson02.util.jdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {
        Connection conn =null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnection();
            String sql = "Select * from `users` where id = ?";
            st = conn.prepareStatement(sql);
            st.setInt(1,1);
            //执行
            rs = st.executeQuery();
            while (rs.next()){
                System.out.println("name:"+rs.getString("NAME"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);
        }
    }
}

10.6、使用IDEA连接数据库

在这里插入图片描述

输入用户名密码 点击apply

在这里插入图片描述

连接成功后可以选择数据库

![在这里插入图片描述](https://img-blog.csdnimg.cn/8c8aa8dca2cc4d5ebee250afacc4a8a6.png#pic_center在这里插入图片描述

双击数据库 或者表即可查看

修改后需要点击绿色的箭头进行保存
在这里插入图片描述

点击倒数第二个符号就可以查询

在这里插入图片描述

注意事项：

  • 在这里插入图片描述

    东西不要随便删

  • 导入相应的数据库驱动包idea才可以连接数据库成功
    在这里插入图片描述

10.8、事务

要么都成功，要么都失败

ACID原则

原子性：要么都完成，要么都不完成

一致性：总数不变

隔离性：多个进程互不干扰

持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

脏读：一个事务读取了另一个没有提交的事务

不可重复读：同一事物内重复读取表中的数据，表的数据发生了改变

幻读：在一个事务内读取到了别人插入的数据，导致前后读取不一致

代码实现

  1. 开启事务

    conn.setAutoCommit(false);

  2. 一组业务执行完毕提交事务

  3. 可以在catch语句中显示的定义回滚语句，但默认失败就会回滚

package com.liu.lesson04;

import com.liu.lesson02.util.jdbcUtils;

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

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnection();
            //关闭数据库的自动提交功能，自动开启事务
            conn.setAutoCommit(false);
            String sql1 = "update account set money = money-100 where name ='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            int x = 1/0;//万能报错语句
            //这句话会触发回滚事务

            String sql2 = "update account set money = money+100 where name ='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //业务完毕提交事务
            conn.commit();
            System.out.println("操作成功");


        } catch (SQLException throwables) {
            try {
                //如果失败就回滚事务
                //这句话不写 失败了也会自动回滚
                conn.rollback();

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

10.9、数据库连接池

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

池化技术：准备一些预先的资源，过来就连接预先准备好的

最小连接数

最大连接数

等待超时

编写连接池，实现一个接口DataSource

开源数据源实现

DBCP

C3P0

Druid：阿里巴巴

使用了这些数据库连接池之后，在项目开发之中就不需要编写连接数据库的代码了

DBCP

需要用到的jar包

commons-dbcp-1.4 commons-pool-1.6

C3P0

需要用到的jar包

c3p0-0.9.5.5 、 mchange-commons-java-0.2.19

结论

无论使用什么数据源，本质是一样的DataSource接口不会变 方法就不会变

冰美式少加冰呀
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql笔记 mysql笔记
04-24
mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记
MySQL笔记.md
11-23
MySQL笔记
mysql各版本jar包下载
qq_44439734的博客
02-23 1万+
下载地址：https://dev.mysql.com/downloads/connector/j/ 进入以下页面，依次点击图示，可下载该版本的jar包。 如果是其他版本的jar包，点击Archives，进入可选择所需版本下载，解压即可。
【java】Java连接mysql数据库及mysql驱动jar包下载和使用
DreamSun的博客
03-06 9015
jar包下载及导入：将下载好的压缩包进行解压：解压之后下图就是连接数据库所用到的jar包：将jar包复制粘贴到IDEA所用的项目下，放置jar包的目录可以是自己新建的，也可以和项目同在一个目录下。
关于MySQL架包jar下载步骤——新手版
最新发布
m0_74849460的博客
03-07 692
很高兴你阅读我的文章，如上文章皆基于个人的学习和理解，如有错误，可在评论区留言交流学习。4、解压并把解压后的文件夹中的到对应idea中的。1、选择对应jar包版本和对应的系统。中（lib包就是自己建的文件夹）,用于idea中的java项目。本次的jar包选择是。
MySQL官网下载mysql的jar包步骤
weixin_73692078的博客
05-08 3650
追风而去，执长剑纵马，执妙笔生花，我王某人在此邀请诸位与我共身！“少年没有乌托邦，心向远方自明朗”以后想进行数据库编程时，就把这个。在这个文件里面有一个。
mysql jar包下载（直接链接下载）
hjc的博客
05-10 1万+
​ mysql-connector-java-8.0.23.jar的下载链接： 124.223.62.164/mysql.zip ​
mysql8安装和驱动jar包下载
NHB456789的博客
11-12 1688
mysql> \qBye➜ vim /etc/my.cnf ##这里注释掉skip-grant-tables。
Mysql笔记markdown
02-02
入门到进阶
MySQL笔记(狂神说java)
09-25
MySQL笔记(狂神说java)
mysql笔记mysql笔记
11-19
mysql笔记
mysql各版本jar包下载地址
shuda356421的专栏
09-23 1056
mysql5我们可以能使用的连接数据库jar包是mysql-connector-java-5.1.25.jar这是一个5版本系列的jar包。但是当我们使用的是mysql8的时候我们使用的jar包可能是mysql-connector-java-8.0.21.jar这么一个8版本的jar包。对不同的mysql指定不同的jar包，其jdbc下载网址为：Central Repository: mysql/mysql-connector-java。查看自己mysql数据库版本的方法我放在自己的上一篇博客里面了。
下载MySQL的JDBC驱动jar包详细步骤
快乐学习
10-20 2万+
1.浏览器搜索MySQL，进入官网 ​2.选择DOWNLOADS ​3.选择这个 ​4.选择Connector/J ​5.选择想要的版本，在第二步选择Platform Independent，再第三步选择这个zip文件 6.点击这个下载到本地文件夹就好了 7.在文件夹中就可以看到这个MySQL的jdbc的jar包了。（idea里面配置jdbc可以看我的下一篇文章）
mysql：Java通过驱动包（jar包）连接MySQL数据库---步骤总结及验证
热门推荐
鄙人kunzhi96，感恩遇见！
01-15 14万+
Java 如何使用 JDBC 连接 MySQL 数据库 一、下载驱动包 Java 连接 MySQL 需要驱动包，官网下载地址为MySQL驱动包官网下载 还是建议大家下载以前的版本 这是我选择的版本 解压后得到jar库文件 1.在工程目录中创建lib文件夹，将下载好的JDBC放到该文件夹下，如下图所示： 2. 右键工程名，点击最下方—Propertise ，在java build path中的...
mysql8的驱动jar包
编程界明世隐的博客
01-27 1万+
之前我一直用Mysql5，最近用Mysql8的时候，用JDBC、C3P0等会出现连接不上数据库。 解决办法： 需要更换jar包 新增：mchange-commons-java-0.2.15.jar 更换，将mysql5驱动换高版本：mysql-connector-java-8.0.11.jar 如果使用了c3p0，需将c3p0的jar包更换为：c3p0-0.9.5.2.jar 网上去找这几个驱动也不是很好找， 可以到我的公众号《编程界明世隐》 回复《mysql驱动》下载。 JDBC drive
【JDBC】idea添加mysql-jar包（很轻松）
ebb29bbe的博客
09-14 7722
简单易懂教程，手把手教你为idea项目导入jar包，加载驱动。
Mysql-jar包教程
weixin_53073287的博客
05-05 2342
Mysql-jar包教程
jdbc 简介
Darklovy
04-14 298
JDBC 指 Java 数据库连接，是一种标准Java应用编程接口（ JAVA API），用来连接 Java 编程语言和广泛的数据库。 JDBC API 库包含下面提到的每个任务，都是与数据库相关的常用用法。 制作到数据库的连接。 创建 SQL 或 MySQL 语句。 执行 SQL 或 MySQL 查询数据库。 查看和修改所产生的记录。 从根本上来说，JDBC 是一种规范，它提供了一套完...
mysql中jar包是啥_MySQL_JDBC_jar包的下载与使用（Windows）
weixin_35901163的博客
01-19 2123
1. 下载(2) 选择操作系统：Platform Independent(3) 文件后缀名为 .tar.gz 的是Linux/IOS的压缩包；后缀为 .zip 的是Windows下的压缩包，根据系统选择下载。(4) Oracle提供了两个版本的jdbc驱动包，一个是5.x.xx，另一个是8.x.xx，这两个版本的区别是：1) 5.x.xx是给MySQL 5及以前版本提供的驱动包；而8.x.xx是给...
csdn mysql笔记
08-02
在CSND上，MySQL笔记是指关于MySQL数据库的学习和使用的笔记和教程。这些笔记包含了MySQL数据库的基本概念、安装配置、SQL语句的使用、数据表的设计和管理、索引使用、数据备份和恢复等方面的知识。学习MySQL笔记...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值