devbox
知新_RL
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL从删库到跑路_mysql删库跑路代码

作者：知新_RL | 2024-07-17 16:03:58

赞

踩

mysql删库跑路代码

MySQL从删库到跑路

1、初识MySQL

  • JavaEE：企业级java开发 Web
  • 前端（页面：展示，数据）
  • 后台（连接点：连接数据库JDBC，连接前端（控制，控制视图跳转，和给前台传数据））
  • 数据库（存取数据：TXT 、Excel、word）

只会写代码，学好数据库，基本混饭吃

操作系统，数据结构与算法！开始搬砖

离散数学 数字电路 编译原理

1.1、为什么学数据库

  1. 岗位需求
  2. 大数据时代、得数据者的天下；
  3. 被迫需求：存数据 去IOE

4、数据库是所有软件体系中最核心的存在 DBA

1.2、什么是数据库

  • 数据库（DB，DataBase）
    1. 概念：数据库（Database ）：是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。（数据仓库 软件 安装在操作系统之上（Linux widowns mac） SQL ，可以存储大量的数据，500万！）
    2. 作用存储数据 管理数据

1.3、数据库分类

  • 关系型数据库：SQL
  1. MySQL Oracle SqlServer DBZ SQLlite
  2. 通过表和表之间，行和列之间的关系进行数据的存取
  • 非关系型数据库：NoSQL
  1. Redis MongoDB
  2. 非关系型数据 对象存储 通过对象的自身的属性来决定，

DBMS（数据管理系统）

  • 数据库的管理软件：科学有效的管理我们的数据、维护和获取数据
  • MySQL：数据库管理系统！

1.4、MySQL简介

image-20200731213419022

MySQL是一个关系型数据库管理系统

前世：瑞典MySQL AB公司

今生：属于Oracle旗下产品

MySQL是最流行的开放源码SQL数据库管理系统的应用软件之一

开源的数据库软件

体积小 速度快 总体拥有成本低 招人成本比较低 所有人必须会

中小网站 或者大型网站 集群！

官网：https://www.mysql.com/

安装建议

  1. 尽量不要使用exe 注册表 卸载的时候不方便

  2. 尽可能使用压缩包安装

1.5、安装mysql

网上教程贼多自己去找 也挺香的

安装步骤

1、下载后得到zip压缩包.

2、解压到自己想要安装到的目录，本人解压到的是D:\Environment\mysql-5.7.19

3、添加环境变量：我的电脑->属性->高级->环境变量

选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹

  • 1

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

[mysqld]
basedir=D:\Program Files\mysql-5.7\
datadir=D:\Program Files\mysql-5.7\data\
port=3306
skip-grant-tables

  • 1
  • 2
  • 3
  • 4
  • 5

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

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

7、然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）

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

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

  • 1

9、刷新权限

flush privileges;

  • 1

10、修改 my.ini文件删除最后一句skip-grant-tables

11、重启mysql即可正常使用

net stop mysql
net start mysql

  • 1
  • 2

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

image-20200808074047223

一步步去做 , 理论上是没有任何问题的 .

如果您以前装过,现在需要重装,一定要将环境清理干净 .

好了,到这里大家都装好了,因为刚接触,所以我们先不学习命令.

这里给大家推荐一个工具 : SQLyog .

即便有了可视化工具,可是基本的DOS命名大家还是要记住!

1.6、SQLyog安装

可手动操作,管理MySQL数据库的软件工具

特点 : 简洁 , 易用 , 图形化

使用SQLyog管理工具自己完成以下操作 :

  • 连接本地MySQL数据库

  • 新建MySchool数据库

    • 字段
    • GradeID : int(11) , Primary Key (pk)
    • GradeName : varchar(50)
    • 数据库名称MySchool
    • 新建数据库表(grade)

在历史记录中可以看到相对应的数据库操作的语句 .

连接数据库

打开MySQL命令窗口

  • 在DOS命令行窗口进入 安装目录\mysql\bin
  • 可设置环境变量，设置了环境变量，可以在任意目录打开！

连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码

注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !

几个基本的数据库操作命令 :

update user set password=password('123456')where user='root'; 修改密码
flush privileges; 刷新数据库
show databases; 显示所有数据库
use dbname；打开某个数据库
show tables; 显示数据库mysql中所有的表
describe user; 显示表mysql数据库中user表的列信息
create database name; 创建数据库
use databasename; 选择数据库

exit; 退出Mysql
? 命令关键词 : 寻求帮助
-- 表示注释

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

1.7、命令行连接数据库

mysql -u root  -p --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host 'localhost';--修改用户密码
flush privileges;--刷新权限
----------------------------------------
--所有的语句都使用分号结尾
show databases;--查看所有的数据库
use  数据库名;--切换数据库
show tables;--查看数据库中所有的表
describe student;--查看表的信息
 create  database westos;--创建一个数据库  westos
 exit;--退出连接
 --单行注释（SQL本来的注释 有些地方也可以 用#）
 /*
 多行注释
 */

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ai7vwJje-1596981632128)(mysqlnote.assets/image-20200801085707359.png)]

2、数据库操作

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

mysql的大小写不区分

2.1、操作数据库（了解）

  1. 创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;

  • 1

2、删除数据库

 DROP DATABASE  IF EXISTS westos;

  • 1

3、使用数据库

use  school

  • 1

4、查看数据库

show dabases ;--查看所有的数据库

  • 1

2.2、数据库的列类型

数值

  • tinyint 十分 小的数据 一个字节
  • smallint 较小的字节 2个字节
  • mediumint 中等大小的数据 3个字节
  • int 标椎的整数 4个字节 常用的
  • big 较大的数据 8个字节
  • float 浮点数 4字节
  • double 浮点数 8字节
  • decimal 字符串形式的浮点数 金融计算的项目使用

字符串

  1. char 字符串固定大小的 0~255
  2. varchar 可变大小的字符串 0~65535 常用的 String
  3. tinytext 微型文本 2^8-1
  4. text 文本串 2^16-1

时间日期

java.util.Date

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

null

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

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

  • Unsigned：
  1. 无符号的整数
  2. 声明了该列不能声明为负数
  • zerofill
  1. 0填充
  2. 不足的位数使用0填充 int（3） 5–005
  • 自增
  1. 通常理解为自增 自动在上一条记录的基础上+1（默认）
  2. 通常用来设计唯一的主键~index 必须整数类型
  3. 可以自定义步长
  • 非空 NULL not null
  1. 假设设置为not null 如果不给它赋值 机会报错
  2. null 如果不填写 默认就是null
  • 默认
  1. 设置默认值
  2. sex，默认值为男 ，如果不指定该列的值 就默认为男

2.4、创建数据库表

-- 目标创建一个school数据库
-- 创建一个学生表（列 字段）  使用SQl创建
-- 学号 int 登录密码  varcahr(20) 姓名， 性别   varchar（2）  出生日期（datetime） 家庭住址   email
-- 注意点使用 英文（）  表的名称 和 字段尽量使用''括起来
-- AUTO_INCREMENT自增
--  所有语句后面加英文逗号,最后一个不用加
use  school
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  COMMIT  '家庭住址',
`email`   VARCHAR(50)  DEFAULT   NULL  COMMENT  '邮箱',
PRIMARY  KEY (`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

格式：

CREATE TABLE [ IF NOT  EXISTS]  '表名'(
'字段名' 列类型  [属性] [索引] [注释],
  '字段名' 列类型  [属性] [索引] [注释],
    '字段名' 列类型  [属性] [索引] [注释],
    ......
    '字段名' 列类型  [属性] [索引] [注释]
)[表的类型]  [表的字符集] [注释];

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

常用命令

 show create database school;--查看创建数据库的语句
 show  create  table student;--查看创建表的语句
 DESC student;--显示表的结构

  • 1
  • 2
  • 3

2.5、数据表的类型

/*
INNODB 默认使用

MYISAM  早些年使用
*/

  • 1
  • 2
  • 3
  • 4
  • 5
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键不支持支持
全文索引支持不支持
表空间大小较小较大 约为 MYISAM的两倍

经验 ( 适用场合 ) :

  • 适用 MyISAM : 节约空间及相应速度
  • 适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

数据表的存储位置

  • MySQL数据表以文件方式存放在磁盘中

    • 包括表文件 , 数据文件 , 以及数据库的选项文件
    • 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .

  • 注意 :

    • * . frm – 表结构定义文件

    • * . MYD – 数据文件 ( data )

    • * . MYI – 索引文件 ( index )

    • InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件

    • MyISAM类型数据表对应三个文件 :

      img

设置数据表字符集

我们可为数据库,数据表,数据列设定不同的字符集，设定方法 :

  • 创建时通过命令来设置 , 如 : CREATE TABLE 表名()CHARSET = utf8;
  • 如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定

2.6、修改删除表

​ 修改

-- 修改表名
alter table 旧表  rename  as  新表;
-- 增加表的字段
alter  table  表名  add  字段名 字段列属性;
-- 修改表的字段（重命名   修改约束）
alter  table  表名  modify  已有字段名  字段列属性;  --  修改约束
alter  table 表名    change   原字段  新字段  字段列属性;-- 字段重命名   


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

删除

-- 删除表的字段
alter  table  表名 drop  需要删除的字段名
-- 删除表
DROP TABLE IF EXISTS exis 表名;

  • 1
  • 2
  • 3
  • 4

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

注意

  • `` 字段名 使用这个包裹！ 安全
  • 注释 – 或 /**/
  • sql关键字大小写不敏感
  • 所有的符号 用英文 别用中文

3、Mysql数据办理

3.1 外键（知道即可 花里胡哨 没什么用）

image-20200802214637675 
--  年级表
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
   给这个外键添加  约束   （执行引用）
*/
 CREATE TABLE `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 AUTO_INCREMENT 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;
-- 创建表的时候  没有外键关系
alter  `student`
ADD   CONSTRAINT `FK_gradeid`  FOREIGN KEY  (`gradeid`)  REFERENCES  `grade` (`gradeid`);
--不想用   麻烦  ！！！！！！！！~！！！

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

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

最佳实践

  • 数据库就是单纯的表 只用来存数据 只有行（数据）和列（字段）
  • 我们想使用多张表 用程序实现 ！！！！！！

3.2 DML语言（有时间背下来 没时间也要背）

数据库意义：数据存储 数据管理

DML语言：数据操作语言！

  • insert
  • update
  • delete

3.3 添加

--插入语句（添加）
insert  into  表名 (字段名1,字段名2,字段名3....) values('值1','值2','值3'...);
-- 由于主键自增  可以直接插入  不过必须插入全部字段
insert  into  表名 values('值1','值2','值3'...);-- 不建议使用
-- 插入多个值
insert  into 表名  (字段名1,字段名2,字段名3....) values('值1','值2','值3'),('值12','值22','值32')...;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

注意事项：

  1. 字段和字段之间用英文逗号隔开
  2. 字段可以是省略的 但后面的值必须要一一对应
  3. 可以同时插入多条数据 values 后面的值 使用英文逗号 隔开即可

3.4 修改

update 修改谁 （条件） set 原来的值=新值

-- 修改student 表中学员的名字
update  `student` set  `name`='狂神' where  id=1;
update  `student` set  `name`='狂神';-- 不指定条件的情况下  会改动  表中的所有该字段
--  修改多个值  用英文逗号  隔开
-- 语法
update  表名  set  colnum=value where 条件;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

条件：where子句 运算符

操作符会返回布尔值

操作符含义范围结果
=等于5=6false
<>或!=不等于5！=6或 5 <>6true
>大于
<小于
>=大于等于
<=小于等于
between…and…在某个范围内[2,5]
and&&
or||

注意

  • colnum_name 是数据的列 尽量带上
  • 条件 筛选的条件 如果没有指定 则会修改所有的列
  • value 是一个具体的值 也可以是一个 变量
  • 多个设置的属性之间用英文逗号隔开

3.5 删除

delete 命令

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

-- 删除数据   操作表  student
-- 删除指定数据
delete  from  `student`  where  id=1;
-- 删除全部数据
delete  from  `student`

  • 1
  • 2
  • 3
  • 4
  • 5

TRUNCATE 命令

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

--  清空表  student
TRUNCATE  `student`;

  • 1
  • 2

delete && TRUNCATE 区别

  • 相同点 ：都能删除数据 都不会删除表的结构
  • 不同
  1. TRUNCATE 重新设置 自增列 计数器会归零
  2. TRUNCATE 不会影响事务
--测试  delete   && TRUNCATE  区别
 CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `coll` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 insert  into  `test` (`coll`) values('1'),('2'),('3');
 delete  from  `test`;  --不会删除自增
 TRUNCATE  table `tesst`;-- 自增会归零

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

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

  • INNODB 自增列从1开始（存在内存当中 断电即失）
  • MyISAM 继续从上一个自增量开始 （存在文件中的 不会丢失的）

4、DQL查询数据（最重要！！）

4.1、DQL

（Data Query LANGUAGE：数据查询语言）

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

SELECT 语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

测试用例！

-- 创建一个数据库
create  database  `school`;
-- 创建一张表
use `school`;
 create  table  `grade`(
     `GradeID` int(4) not null auto_increment  comment '年级编号',
       `GradeName` varchar(50) not  null comment '年级名称',
      primary  key (`GradeID`)
 )ENGINE=INNODB AUTO_INCREMENT=6  DEFAULT CHARSET=utf8;
-- 添加数据
insert  into `grade` (`GradeID`, `GradeName`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 创建reult  表
create table  `result`(
`StudentNo` int(4) not null  comment '学号',
 `SubjectNo` int(4) not  null comment  '课程编号',
    `ExamDate`  DATETIME not null comment '考试日期',
    `StudentResult` int(4) not  null  comment '考试成绩',
    key  `SubjectNo`(`SubjectNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 添加数据 
insert  into  `result` (`StudentNo`,`SubjectNo`,  `ExamDate`,`StudentResult`) values(1000,1,'2013-11-11 16:00:00',66);
-- 创建 student 表
create  table  `student`(
   `StudentNo` int(4) not null  comment '学号',
    `LoginPwd` varchar(20) not null,
    `StudentName` varchar(20) DEFAULT  NULL comment '学生姓名',
    `Sex` TINYINT(1) DEFAULT NULL  comment  '性别，取值0或1',
    `GradeID` int(4) DEFAULT NULL comment '年级编号',
    `Phone` varchar(50) not null comment '联系电话  允许为空',
    `Address` varchar(255) not null  comment '地址  允许为空',
    `BornDate`  DATETIME DEFAULT null comment '出生时间',
    `Email` varchar(50) not  null comment '邮箱账号   允许为空',
    `IdentityCard` varchar(18)  DEFAULT  null  comment '身份证',
    primary key(`StudentNo`),
    UNIQUE key `IdentityCard` (`IdentityCard`),
    key `Email` (`Email`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 添加数据 
insert  into  `student` (`StudentNo`,`LoginPwd`,`StudentName`,`Sex`, `GradeID`,`Phone` ,`Address`,`BornDate`, `Email`, `IdentityCard`) values(1001,'123456','李四',1,3,'18786506942','山海','1999-12-11 00:00:00','test@qq.com','522428199912110812'),(1002,'123456','李林',0,3,'18786506942','西安','1999-02-11 00:00:00','test@qq.com','522428199912110832'),(1003,'123456','韩立',1,3,'1878655542','北京','1999-02-11 00:00:00','test@qq.com','412428199912110812');
-- subject  表
create  table `subject`(
  `SubjectNO`  int(4) not null auto_increment comment '课程标号',
    `SubjectName` varchar(50) DEFAULT NULL comment '课程名称',
    `ClassHour` int(4) DEFAULT NULL comment '学时',
    `GradeID`  int(4)  DEFAULT null comment  '年级编号',
    primary key (`SubjectNO`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 添加数据 
insert  into  subject (`SubjectNO`, `SubjectName`,`ClassHour`,`GradeID`)  values (1,'高等数学-1',110,1),(2,'高等数学-2',100,2),(3,'高等数学-3',130,3),(4,'高等数学-4',130,4),(5,'C语言-1',110,1),(6,'C语言-2',100,2),(7,'C语言-3',120,3),(8,'C语言-4',130,4);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49

4.2、指定查询字段

查询所有的学生

语法：select 字段1… from 表 ；

select * from  student;

  • 1
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM  student;

  • 1
  • 2

别名 ,给结果起一个 名字 可以给字段取别名 也可以给表取 别名

SELECT `StudentNo` AS 学号,`StudentName` AS 名字 FROM  student;

  • 1

函数 Concat(a,b)

SELECT CONCAT('姓名：',StudentName) AS 新名字  FROM student;


  • 1
  • 2
image-20200803214347995

有的时候，列的名字不是吧那么见名知意 我们可以起别名 AS 字段 as 别名 或者 表 as 表别名

去重 distinct

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

-- 查询有哪些同学参加了考试
SELECT * FROM result;-- 查询全部的成绩
-- 查询有哪些同学参加了考试
SELECT `StudentNo` FROM  result;
--  发现重复 去重  
SELECT DISTINCT `StudentNo` FROM  result;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

数据库的列 （表达式）

-- 查看  mysql的版本
SELECT VERSION();
SELECT  100*3-1 AS 计算结果;--用来计算
SELECT @@auto_increment_increment; --查询自增的步长
-- 学员成绩 +1分 查看
SELECT `StudentNo`,`StudentResult` +1 AS '提分后' FROM  result

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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

select 表达式 form 表

4.3、where 条件子句

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

****搜索的条件由一个或者多个表达式组成 一般返回结果是一个 布尔值

逻辑运算符

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

尽量使用英文字母

--  ===========WHERE ==============
SELECT studentNo,`StudentResult` FROM  result;-- 列不区分大小写
--  查询成绩在95~100之间的
  SELECT studentNo,`StudentResult` FROM  result  WHERE  StudentResult>=95 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号学生之外的成绩  NOT
     SELECT studentNo,`StudentResult` FROM  result  WHERE  studentNo !=1000;
     SELECT studentNo,`StudentResult` FROM  result  WHERE NOT studentNo =1000;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

模糊查询

运算符语法描述
IS NULLa is null如果操作符结果为null则结果为true
IS NOT NULLa is not null如果操作符结果为null则结果为 false
BETWEENa BETWEEN b and c如果a在 b和c之间，则结果为真
LIKEa LIKE bSQL 匹配 a如果匹配到b 则结果为真
ina in(a1,a2,a3…)如果a在a1，a2，a3其中的某一个值中 则结果为真
模糊查询
   --  查询名字里面  韩 字开头de
--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,Address FROM student  WHERE  Address IN('北京');
    --  =========== NULL  NOT NULL=======================================================
-- 查询  Address 为null的同学
    SELECT StudentNo,StudentName FROM student  WHERE Address ='' OR Address  IS NULL;
    -- 查询有出生日期的学生  BornDate  不为空
     SELECT StudentNo,StudentName,BornDate  FROM student WHERE BornDate IS NOT NULL ;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

4.4、联表查询

JOIN 对比

image-20200804092941606!(mysqlnote.assets/image-20200804093124085.png)

image-20200804093244808 
-- 查询参加了考试的同学（学号   姓名   科目编号    分数）
  SELECT * FROM  student;
  SELECT * FROM  result;
/*
思路
1、分析需求  分析查询的字段来自哪些表  （连接查询）
2、确定使用哪种连接查询   ？  七种
确定交叉点（这两个表中  哪个数据是相同的）

判断的条件  ：student.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 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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
操作描述
INNNER JOIN如果表中至少有一个匹配，就返回匹配的值
LEFT JION会从左表中返回所有的值 即使右表中没有匹配
RGIGHT JION会从右表中返回所有的值 即使左表中没有匹配
--  拓展  （查询了参加考试的同学信息   学号  学生  姓名   科目名     分数）
/*
思路
1、分析需求  ;分析查询的字段 来自哪些表   student  result  sunject  （连接查询）
2、确定使用哪种连接查询   ？  七种
确定交叉点（这两个表中  哪个数据是相同的）
判断的条件：student.StudentNo=result.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
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

自连接

/*
自连接
   数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
    查询父栏目名称和其他子栏目名称
*/
-- 创建一个表
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','办公信息');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

自连接就是自己的表和自己的表连接 核心：一张表拆成两张一样的表

父类

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

子类表

pidcategoryidcategoryName
34数据库
28办公信息
36web开发
57ps技术

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

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计ps技术
-- 查询父子信息
  SELECT  a.categoryName AS '父栏目',b.categoryName  AS '子栏目'  
FROM `category` AS a,`category` AS b
WHERE a.categoryid=b.pid;

  • 1
  • 2
  • 3
  • 4
-- C查询学员所属的年级（学号  学生的姓名  年级名称）
  SELECT  `StudentNo`,`StudentName`,`GradeName`
  FROM  `student` AS s
  INNER JOIN `grade` AS g
  ON s.GradeID=g.GradeID;

  • 1
  • 2
  • 3
  • 4
  • 5
-- 查询科目所属的年级（科目名称  年级名称）
  SELECT  SubjectName,GradeName 
   FROM  grade as g
   INNER JOIN  `subject` AS sub
   ON  g.GradeID=sub.GradeID;

  • 1
  • 2
  • 3
  • 4
  • 5

4.5、分页和排序

测试

/*============== 排序 ================
语法 : ORDER BY
   ORDER BY 语句用于根据指定的列对结果集进行排序。
   ORDER BY 语句默认按照ASC升序对记录进行排序。
   如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。 */
-- 查询 数据库结构-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='数据库结构-1'
ORDER BY StudentResult DESC
/*============== 分页 ================
语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好处 : (用户体验,网络传输,查询压力)
推导:
   第一页 : limit 0,5
   第二页 : limit 5,5
   第三页 : limit 10,5
   ......
   第N页 : limit (pageNo-1)*pageSzie,pageSzie
   [pageNo:页码,pageSize:单页面显示条数]   
*/
-- 每页显示5条数据
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='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
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='JAVA第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46

4.6、子查询

/*============== 子查询 ================
什么是子查询?
   在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
   嵌套查询可由多个子查询组成,求解的方式是由里及外;
   子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;

-- 查询课程为 高等数学-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 subjectname = '高等数学-2' AND StudentResult>=80

-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.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'
)

-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
   SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
       SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
  )
)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56

4.7、分组和过滤

--  查询不同成绩的平均分  最高分  最低分
-- 核心：根据不同的课程分组  
  SELECT  SubjectName ,AVG(StudentResult),MAX(StudentResult),MIN(StudentResult)
   FROM  result r
   INNER JOIN  `subject` sub
   ON  r.SubjectNo = sub.`SubjectNo`
   GROUP BY  r.SubjectNo;-- 通过什么字段来分组0

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

5、Mysql函数

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

5.1、常用函数

花里胡哨

-- 数学运算
   SELECT  ABS(-8);-- 绝对值
   SELECT CEILING(9.4);-- 向上取整
   SELECT FLOOR(9.4);-- 向下取整
   SELECT RAND();-- 返回一个0~1之间的一个随机数
   SELECT SIGN(-56);-- 判断一个数的符号
   -- 字符串函数
   SELECT CHAR_LENGTH('即使再小的帆也能返航');-- 返回字符串长度
   SELECT CONCAT('我','哎','你们');-- 拼接字符串
   SELECT INSERT('我爱编程HelloWrold',2,2,'超级热爱')-- 查询替换 从某个位置 替换某个长度
   SELECT LOWER('AAAAA');-- 转小写
   SELECT  UPPER('aaaaa');-- 转大写
   SELECT  INSTR('dfsgdfgdfgd','d');-- 查找某个字符串第一次出现的索引

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

时间日期

-- 时间日期
SELECT CURRENT_DATE();-- 获取当前日期
SELECT  CURRENT_TIME();-- 获取当前时间
SELECT  NOW();-- 获取 当前日期 时间
SELECT LOCALTIME(); -- 获取本地时间

  • 1
  • 2
  • 3
  • 4
  • 5

5.2、聚合函数（常用）

函数名称描述
COUNT()计数
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
--  都能够统计表中的数据  COUNT()
  SELECT  COUNT(StudentName) FROM  student;-- 统计人数  COUNT(字段)  会忽略 Null 值
  SELECT  COUNT(*) FROM  student; -- COUNT(*)   不会忽略 Null 值
  SELECT  COUNT(1) FROM  student;  -- 不会忽略 Null 值
SELECT  SUM(`StudentResult`)  AS  总和  FROM  result;-- 求和
SELECT  AVG(`StudentResult`)  AS  平均分  FROM  result;-- 平均分
SELECT  MAX(`StudentResult`)  AS   最高分  FROM  result;-- 最高得分
SELECT  MIN(`StudentResult`)  AS   最低分  FROM  result;-- 最低得分


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

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

什么是MD5

​ MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。

  • 主要增强算法复杂度和不可逆性
  • MD5不可逆 具体的值 MD5是一样
  • MD5破解的原理 ：背后有一个字典 MD5加密后的值 加密前的值
--  ===================测试MD5 加密====================
CREATE TABLE `testmd5`(
`id`  INT(4)  NOT NULL,
`name`  VARCHAR(20)  NOT  NULL,
`pwd`   VARCHAR(50)  not NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT  CHARSET=utf8;
--  明文密码
INSERT  INTO testmd5  
VALUES(1,'张三','123456'),
(2,'zhaoda','123456'),
(3,'lisi','123456'),
(4,'wangwu','123456');
-- 加密
UPDATE  testmd5 SET pwd=MD5(pwd) WHERE id=1;

UPDATE  testmd5 SET pwd=MD5(pwd);-- 加密全部密码
-- 插入的时候加密
   INSERT  INTO testmd5  
VALUES(6,'小明',MD5('123456'));
  select * from  testmd5;
-- 如何校验：将用户传递进来的密码  进行md5加密  然后对比加密后的值

SELECT *FROM testmd5  WHERE name='小明' AND pwd=MD5('123456');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

6、事务

6.1、什么是事务

要么都成功 要么都失败

  • 事务就是将一组SQL语句放在同一批次内去执行
  • 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
  • MySQL事务处理只支持InnoDB和BDB数据表类型

事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。

  1. SQL 执行 A给B转账 A 1000 --> B 200
  2. SQL 执行 B收到A的钱 A 800 --> B400

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

INNODB支持

事务原则：ACID原则 原子性 一致性 隔离性 永久性 （脏读 幻读 不可重复读）

原子性(Atomic)

  • 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性(Consist)

  • 一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

隔离性(Isolated)

  • 隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性(Durable)

  • 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

隔离导致的一些问题

  • 脏读

​ 指一个事务读取了另外一个事务未提交的数据

  • 不可重复读

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

  • 幻读（虚读）

​ 是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。。。。

事务的执行流程

image-20200806214909011

执行事务

-- ==========事务====================
-- mysql默认开启事务自动提交的
  SET  autoccommit = 0;/*关闭*/
  SET  autoccommit = 1;/*开启（默认）*/
  -- s手动处理事务
  SET  autoccommit = 0; -- 关闭自动提交

--  事务开启
  START TRANSACTION  -- 标记一个事务的开始，从这个开始的sql  都在同一个事务中
  INSERT xx
   INSERT  yy

--  提交  持久化（成功）
 COMMIT
-- 回滚   回到原来的样子（失败）

ROLLBACK
--  事务结束
 SET  autoccommit = 1;-- 开启自动提交

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

模拟场景

-- 转账

CREATE  DATABASE   `shop`  CHARACTER  SET  utf8   COLLATE  utf8_general_ci;
  USE  shop;
  CREATE  TABLE  `account`(
   `id`  INT(3) NOT NULL  AUTO_INCREMENT,
    `name`  VARCHAR(20) NOT NULL,
   `money`   DECIMAL(9,2)  NOT  NULL,
   PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8;

INSERT into `account` (`name`,`money`) VALUES('A',2000.00),('B',10000);

-- 模拟转账

  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; -- 恢复默认值

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

7、索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构 。

提取句子主干 就可以得到索引的本质 ：索引是数据结构

7.1、索引的分类

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

  • 主键索引（PRIMARY KRY）
  1. 唯一标识 主键不可以重复 只能有一个列作为主键
  • 唯一索引 （UNIQUDE KEY）
  1. ​ 避免重复的列出现 唯一索引可以重复 多个列 都可以标识为唯一索引
  • 常规索引 （KEY/index）
  1. 默认的 可以用 index 或者 key关键字
  • 全文索引 （FULLTEXT）
  1. 在特定的数据库引擎下才有 MySAM
  2. 快速定位数据

基础语法

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
--  2、创建完毕后  增加索引
--  显示所有索引信息
SHOW  INDEX  FROM  `student`; 

--  增加一个索引 (索引名：列名)
  ALTER TABLE school.`student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
 --  EXPLAIN 分析SQL执行的状况
    EXPLAIN SELECT * FROM student;  -- 非全文索引
    EXPLAIN  SELECT * FROM  student WHERE MATCH(StudentName)  AGAINST('韩')
  

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

7.2、测试索引

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';
-- 插入100w条数据
 DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$ -- 写函数之前必须要写   标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
    -- 插入语句
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '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();

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

索引效率测试

无索引

SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);

  • 1

测试普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

7.3、索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加在查找条件的字段

索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

8、数据库备份

8.1、权限管理

使用SQLyog 创建用户，并授予权限演示

img

基本命令

/* 用户和权限管理 */ ------------------
用户信息表：mysql.user

-- 刷新权限
FLUSH PRIVILEGES

-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
  - 只能创建用户，不能赋予权限。
  - 用户名，注意引号：如 'user_name'@'192.168.1.1'
  - 密码也需引号，纯数字密码也要加引号
  - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD

-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- 设置密码
SET PASSWORD = PASSWORD('密码')    -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码')    -- 为指定用户设置密码

-- 删除用户 DROP USER kuangshen2
DROP USER 用户名

-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges 表示所有权限
  - *.* 表示所有库的所有表
  - 库名.表名 表示某库下面的某表

-- 查看权限   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
   -- 查看当前用户权限
  SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();

-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38

权限解释

-- 权限列表
ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
ALTER    -- 允许使用ALTER TABLE
ALTER ROUTINE    -- 更改或取消已存储的子程序
CREATE    -- 允许使用CREATE TABLE
CREATE ROUTINE    -- 创建已存储的子程序
CREATE TEMPORARY TABLES        -- 允许使用CREATE TEMPORARY TABLE
CREATE USER        -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW        -- 允许使用CREATE VIEW
DELETE    -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE        -- 允许用户运行已存储的子程序
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX     -- 允许使用CREATE INDEX和DROP INDEX
INSERT    -- 允许使用INSERT
LOCK TABLES        -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES    -- 未被实施
RELOAD    -- 允许使用FLUSH
REPLICATION CLIENT    -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE    -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT    -- 允许使用SELECT
SHOW DATABASES    -- 显示所有数据库
SHOW VIEW    -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用mysqladmin shutdown
SUPER    -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE    -- 允许使用UPDATE
USAGE    -- “无权限”的同义词
GRANT OPTION    -- 允许授予权限


/* 表维护 */

-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

8.2、Mysql备份

为什么要备份

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

MySQL数据库备份的方式

  • 直接拷贝物理文件
image-20200807124358535

  • 利用Navicat for MySQL这类的可视化工具导出（ 花里胡哨 百度它不香吗）

  • 使用命令行导出 mysqldump 命令行（小黑窗口）

    -- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

可以-w携带备份条件

-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
　　source 备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

mysqldump客户端

作用 :

  • 转储数据库
  • 搜集数据库进行备份
  • 将数据转移到另一个SQL服务器,不一定是MySQL服务器

9、数据库设计

9.1、为什么需要设计数据库

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

糟糕的数据库设计

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

良好的数据库设计：

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

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

  • 需求分析 ：分析业务和需要处理的数据库需求

  • 概要设计 设计相关的 E-R图

  • 设计数据库步骤

    • 收集信息

      • 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.

    • 标识实体[Entity]

      • 标识数据库要管理的关键对象或实体,实体一般是名词

    • 标识每个实体需要存储的详细信息[Attribute]

    • 标识实体之间的关系[Relationship]

9.2、三大范式

为什么需要数据规范化？？

  • 信息重复
  • 更新异常
  • 插入异常

​ 无法正常显示信息

  • 删除异常

​ 丢失有效的信息

三大范式

**第一范式 (1st NF)**

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

**第二范式(2nd NF)**

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

**第三范式(3rd NF)**

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

规范化和性能的关系

为满足某种商业目标 , 数据库性能比规范化数据库更重要

在数据规范化的同时 , 要综合考虑数据库的性能

通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间

通过在给定的表中插入计算列,以方便查询

10、JDBC（重点）

10.1、数据库驱动

驱动：声卡，显卡， 数据库

image-20200807182747204

我们通过数据库驱动 和数据库打交道！！！

10.2、JDBC

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

  • 这些规范的实现由具体的厂商去做

  • 对于开发人员来说，只需要掌握JDBC接口的操作即可

image-20200807183653348

java.sql

javax.sql

还需要导入数据库驱动包[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OJLom8GM-1596981632139)(mysqlnote.assets/image-20200807183917832.png)]

Maven仓库中下载

image-20200807184445108

官网下载（MAven 比较香 官网太难找）

image-20200807185229027

10.3、编写第一个JDBC程序（删库到跑路的快乐也不过如此）

  1. 创建一个普通项目（没啥说的）
image-20200807190008627

2、创建一个测试数据库

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,'zhangsan','123456','zin@.com','1980-12-04'),
(2,'lisi','123456','lisi@.com','1980-12-04'),
(3,'wangwu','123456','wang@.com','1980-12-04');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

3、导入数据库驱动

  • 在项目下新建一个lib目录
image-20200807192438459
  • 将驱动复制到lib中
  • 将驱动包添加到项目的类库中
image-20200807192901966

4、编写测试代码

package com.kuang.lesson01;
import java.sql.*;
//我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        1、加载驱动
         Class.forName("com.mysql.cj.jdbc.Driver");//固定写法，加载驱动
//        2、用户信息和  url
        String url="jdbc:mysql://localhost:3306/jdbcStudy?userUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT";
        String username="root";
        String password="123456";
//        3、连接成功  数据库对象  Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
//        4、执行SQL的对象  Statement 执行SQL 的对象
        Statement statement = connection.createStatement();
//        5、执行SQL的对象执行SQL  可能存在结果  查看结果
        String   sql="select  * from  users";
        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("password="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
            System.out.println("============================");
        }
//     6、关闭数据库的连接  释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32

步骤总结

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

DriverManager

 //DriverManager.registerDriver(new  com.mysql.cj.jdbc.Driver());
         Class.forName("com.mysql.cj.jdbc.Driver");//固定写法，加载驱动
        Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
        connection.rollback();
        connection.commit();
        connection.setAutoCommit();

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

URL

 String url="jdbc:mysql://localhost:3306/jdbcStudy?userUnicode= true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT";
 //协议:端口号/数据库名？参数1&参数2.。。
 //jdbc:mysql://主机地址:端口号/数据库名？参数1&参数2.。。
//oracle --1521
//jdbc:oracle:thin:@localhost:1521:sid

  • 1
  • 2
  • 3
  • 4
  • 5
Statement 执行SQL的对象        connection.prepareStatement()执行SQL的对象

  • 1
      String   sql="select  * from  users";//编写SQL  
      statement.executeQuery();//查询  返回一个结果集
        statement.execute();//执行任何SQL
        statement.executeUpdate();//增删改SQL  都行  返回受影响的行数

  • 1
  • 2
  • 3
  • 4
ResultSet  查询的结果集   分装了所有的查询结果

  • 1

获得指定的数据类型

       resultSet.getObject();//在不知列类型的情况下使用
         //如果知道列类型 就用相应的类型
        resultSet.getString();
        resultSet.getInt();
        resultSet.getFloat()；
         .....

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

遍历，指针

       resultSet.beforeFirst();//移动到最前面
        resultSet.afterLast();//移动到最后
        resultSet.next();//移动到下一个

  • 1
  • 2
  • 3

释放资源

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

  • 1
  • 2
  • 3

10.4、statement对象

jdbc中的statement对象用于向数据库发送SQL语句，向完成对数据库增删改查，只要通过这个对象发送增删改查操作即可。

Statement对象的execteUpdate()方法，用于向数据库发送增删改查的语句，execteUpdate()执行完后将会返回一个整数（即对几条数据进行了操作）。

Statement.executeQuery()方法用于向数据库发送查询语句，executeQuery()方法将返回一个结果集ResultSet对象

CRUD操作-create

使用executeUpdate（String sql）方法完成数据库添加操作：

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

CRUD操作-delete

使用executeUpdate（String sql）方法完成数据库添加操作：

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

CRUD操作-update

使用executeUpdate（String sql）方法完成数据库更新加操作：

Statement st=com.createStatement();
String sql="update user set name='' where name=''";
int  num=st.executeUpDate(sql);
if(num>0){
    System.out.print("更新成功");
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

CRUD操作-read

使用executeUpdate（String sql）方法完成数据库读加操作：

Statement st=com.createStatement();
String sql="select *from user wher id=1";
ResultSet rs=st.executeUpdate(sql);
while(){
    //根据获取的数据类型  分别调用rs的相应方法映射到java对象中
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

代码实现

1、提取工具类

db.properties

diver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?userUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
username=root
password=123456

  • 1
  • 2
  • 3
  • 4

package com.kuang.lesson02.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
    private static String diver=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);
            diver = properties.getProperty("diver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
//            1、驱动只用加载一次
            Class.forName(diver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
//    获取连接
  public  static Connection getConnection() throws SQLException {
      return DriverManager.getConnection(url, username, password);
  }
//    释放连接资源
    public static void release(Connection com, 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 (com!=null){
            try {
                com.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53

2、编写增删改的方法

// 增删改  只需要改SQL语句
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\n" +"(4,'kuanmgshen','123456','245456@','1990-02-01')";
           int i= st.executeUpdate(sql);
               if(i>0){
                   System.out.println("成功插入了"+i+"行数据");
               }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

查询

//查询
public class TestRead {
    public static void main(String[] args) {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn= JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得SQL的执行对象
            String  sql="select * from  users";
             rs=st.executeQuery(sql);
             while (rs.next()){
                 System.out.println("id="+rs.getInt("id"));
                 System.out.println("name="+rs.getString("name"));
                 System.out.println("password="+rs.getObject("password"));
                 System.out.println("email="+rs.getObject("email"));
                 System.out.println("==============================");
             }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

SQL注入问题

SQL存在漏洞，会被攻击导致数据泄露！！！！！！！！

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

10.5、PrePareStatementd对象

PrePareStatementd可以防止sql注入效率更高

1、新增

package com.kuang.lession03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

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

        try {
             conn = JdbcUtils.getConnection();
//             区别
//            使用占位符代替参数
            String  sql="INSERT  INTO users(id,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?)";
           st= conn.prepareStatement(sql);//预编译sql
//            手动给参数复制
            st.setInt(1,4);
            st.setString(2,"kuangshen");
            st.setString(3,"123456");
            st.setString(4,"123456@qq.com");
//            注意点 sql.Date 数据库
//            util.Date java    new Date().getTime())获得时间戳
           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);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39

2、删除

package com.kuang.lession03;

import com.kuang.lesson02.utils.JdbcUtils;

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

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
//            手动给参数复制
         st.setInt(1,4);
//           执行
            int i=st.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

3、更新

package com.kuang.lession03;

import com.kuang.lesson02.utils.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=?";
            st= conn.prepareStatement(sql);//预编译sql
//            手动给参数复制
            st.setString(1,"kuang");
            st.setInt(2,4);

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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37

4、查询

package com.kuang.lession03;

import com.kuang.lesson02.utils.JdbcUtils;

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

public class TestRead {
    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
//            手动给参数复制
         st.setObject(1,1);


//           执行
          rs=st.executeQuery();
          while (rs.next()){
              System.out.println("id="+rs.getObject("id"));
              System.out.println("name="+rs.getObject("name"));
              System.out.println("password="+rs.getObject("password"));
              System.out.println("email="+rs.getObject("email"));
              System.out.println("birthday="+rs.getObject("birthday"));
          }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42

5、防止SQL注入

package com.kuang.lesson02;

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {
    // login("lisi","123456");
        login("''or 1=1","123456");
    }
    //登录业务
    public  static  void login(String username,String  password){
        Connection conn=null;
//        PreparedStatement  防止注入的本质  就是把传递进来的参数当做字符
//        加入其中出现转义字符 比如说 ‘’就被转义了
        PreparedStatement st=null;
        ResultSet rs=null;
        try {
            conn= JdbcUtils.getConnection();//获取数据库连接
            String  sql="select * from  users where `name`=? and `password`=?";
            st=conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);
            rs=st.executeQuery();

            while (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getObject("password"));
                System.out.println("===================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

10.6、使用idea连接数据库(没啥好说的)

1、连接数据

image-20200808175116853 image-20200808175900787

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

image-20200808180324167 image-20200808180648674

10.7、事务

要么都成功 要么都失败！！！！

ACID原则

  • 原子性

要么全部完成要么都不完成

  • 一致性

总数不变

  • 隔离性

多个进程互不干扰

  • 持久性

一旦提交不可逆 持久化到数据库

隔离性的问题

  • 脏读

​ 一个事务读取了另外一个没有提交的事务

  • 不可重复读

    ​ 在同一个事务内，重复读取表中的数据，表数据发生了改变

  • 幻读（虚读）

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

代码实现

1、开启事务 ： conn.setAutoCommit(false);

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

3、可以在catch语句中显示的定义 回滚语句 但默认失败 自动回滚

数据库

create table account(
                        id int primary key auto_increment,
                        name varchar(40),
                        money float

);
insert into account(name, money) VALUES ('A',1000);
insert into account(name, money) VALUES ('B',1000);
insert into account(name, money) VALUES ('C',1000);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

业务逻辑代码

package com.kuang.lession04;

import com.kuang.lesson02.utils.JdbcUtils;

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

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement 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);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42

10.8、数据库连接池

数据库连接-----执行完毕—释放

连接—释放 十分浪费系统资源

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

  • 最小连接数 依情况而定（常用：100 最小连接数：100）

  • 最大连接数： 业务做高承载上限

  • 排队等候

  • 等待超时

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

开放数据源实现

DBCP

c3p0

Druid:阿里巴巴

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

DBCP

需要用到的jar包

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g2lMrRXe-1596981632142)(mysqlnote.assets/image-20200808215429045.png)]

C3P0

需要导入的jar包

image-20200809082516855

结论

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/840802
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号