21：MySQL详解 || 那些你知道和不知道的操作全都有

MySQL详解

一、初识MySQL

JavaEE：企业级Java开发 Web

前端：页面展示 数据。

数据库：存数据（txt，excel，word，早期用程序存数据）

后台：连接数据库 ，前期用JDBC，后期用框架实现；连接前端：前期Servelet，后面用框架，控制视图的跳转给前端传数据。

只会写代码。基本的码农

学好数据库，增删改查。基本混饭吃

学习操作系统，数据结构与算法。不错的程序员

离散数学，数字电路，体系结构，编译原理，实战经验。高级程序员

1. 为什么学习数据库

  1. 岗位需求
  2. 大数据时代，得数据者得天下
  3. 被迫需求，所有人都需要存数据
  4. 数据库是所有软件体系中最核心的存在DBA：数据库管理员 专门研究数据库

2. 什么是数据库

数据库（DB，DataBase）

概念：数据仓库，是一个软件，安装在操作系统上的，window，linux，mac等等，SQL语句，可以存储大量的数据，500万以下。

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

3. 数据库分类

关系型数据库（SQL）：狭义理解来说就是行列

  • 代表：MySQL，Oracle，SQLServer，DB2，SQLlite
  • 通过表和表之间，行和列之间的关系来进行数据的存储

非关系型数据库（NoSQL：not only SQL）：狭义理解为键值{key：value}

  • Redis，MongoDB
  • 对象存储，通过对象的自身的属性来决定

DBMS：数据库管理系统

  • 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据。
  • 比如说：excel的xls文件，文件在电脑上，数据是存在的，但是没有相对应的软件，是打不开的。
  • MySQL本质是数据库管理系统，它是可以管理数据的。

4. MySQL简介

image-20220331162751058

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

5.7版本的更加稳定

5. 安装MySQL及相关软件

教程网上一搜一大把

尽量不要出错

安装建议：

  1. 尽量不要使用exe，不然会进入注册表
  2. 尽可能使用压缩包安装

6. 简单的使用

记得多查看历史日志

  1. 新建表

    连接成功之后，右键新建数据库，推荐配置：

    image-20220331180052139

    在数据库的表上右键新建表就可以操作了

  2. 查看表并修改

    image-20220331180247500

    记得保存

7. 连接数据库

  1. 在可视化界面中：新建连接之后双击即可重写连接

  2. 命令行连接

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

    image-20220331180806978

8. 一些基本的命令行操作

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; -- 修改密码
flush pricileges; -- 刷新权限
-- ------------所有的语句都使用 ; 结尾--------------------------
show databases;
use school -- 切换数据库 use 数据库名

show tables; -- 查看这个数据库中所有的表
describe student; -- 显示数据库中指定的表的信息  desc也可以
create database westos; -- 创建一个数据库 

exit -- 退出连接

-- 单行注释使用--加空格  或者 # 
# 也是单行注释
/*
这是多行注释
*/

数据库语言类型（CRUD增删改查）：

  • DDL：数据库定义语言
  • DML：数据库操作管理语言
  • DQL：数据库查询语言
  • DCL：数据库控制语言

二、操作数据库

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

MySQL关键字不区分大小写

1. 操作数据库

  1. 创建数据库

    CREATE DATABASE [IF NOT EXISTS] westos #中括号的意思是可选 exists:存在  如果不存在 就创建这个数据库

  2. 删除数据库

    DROP DATABASE [IF EXISTS] westos

  3. 使用数据库

    #tab键上边的符号，如果你的表名或者字段名是一个特殊字符，就需要带这个符号
USE `school`

  4. 查看数据库

    SHOW DATABASES -- 查看所有的数据库

  • 可以对比一下可视化操作和SQL语句

  • 固定的语法和关键字需要记住

2. 数据库的列类型

  1. 数值

    • tinyint 十分小的数据 1个字节
    • smallint 较小的数据 2个字节
    • mediumint 中等大小的数据 3个字节
    • **int 标准的整数 4个字节 ** 对应Java中的 int
    • bigint 较大的数据 8个字节
    • float 浮点数 4个字节
    • double 浮点数 8个字节
    • decimal 字符串形式的浮点数 金融计算的时候

  2. 字符串

    • char 保存固定长度的字符串（可包含字母、数字以及特殊字符）最多 255 个字符。
    • **varchar 可变字符串 最多 255 个字符 如果值的长度大于 255，则被转换为 TEXT 类型。对应java中的String ** 是最常用的变量
    • tinytext 微型文本 存放最大长度为 255 个字符的字符串。
    • text 文本串 存放最大长度为 65,535 个字符的字符串。

  3. 日期

    java.util.Date

    • date ： yyyy-MM-dd 日期格式（大写MM是为了区分 分和月）
    • time ： HH：mm：ss 时间格式（大写HH是为了区分12还是24小时制 大写为24 小写为12）

    sql：

    • datetime ： yyyy-MM-dd HH:mm:ss 最常用的时间格式
    • timestamp ：时间戳 全球唯一 1970.1.1到现在的毫秒数
    • year 年份表示

  4. null

    • 没有值，未知
    • 不要使用NULL计算，不然结果一定为NULL

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

  1. UNsigned：
    • 无符号的整数
    • 声明了该列不能为负数
  2. zerofill：
    • 用0填充不足的位数
    • 比如 int 长度为3 这时候输入5 会变为005
  3. 自增：
    • autoincrement
    • 自动在上一条记录的基础上+1（默认）
    • 通常用来设计唯一的主键 index 必须是整数类型
    • 可以自定义设计主键自增的起始值和步长
  4. 非空：
    • NULL not null
    • 假设设置为not null ，如果不给它赋值，就会报错!
    • 不勾选此选项，如果不填写值，默认值为null
  5. 默认：
    • default
    • 设置默认的值
    • sex，默认值为男，如果不指定该列的值，就会有默认的值。

拓展：常用的字段（每一个表都必须存在以下五个字段，未来做项目用的，表示一个记录存在的意义）：

  • id 主键 自增
  • version 版本 乐观锁
  • is_delete 伪删除
  • gmt_create 创建时间
  • gmt_update 修改时间

4. 创建数据库表（SQL语句）

-- 注意点：使用英文括号，标的名称和字段尽量用 `` 括起来 
-- 字符串使用英文单引号括起来
-- 所有的语句后面加英文逗号，最后一行不用加
-- PRIMARY KEY 主键，一般单独一行方便查看，而且一个表只有一个唯一的主键 
CREATE TABLE
IF NOT EXISTS `student1` (
	`id` INT (4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR (30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR (20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR (2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR (100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR (50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

格式总结：

CREATE TABLE
[IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ......
    `字段名` 列类型 [属性] [索引] [注释]
)[ENGINE=引擎 DEFAULT CHARSET=字符集]

常用命令：

-- 查看创建数据库的语句 
SHOW CREATE DATABASE school  
-- 查看创建表的语句 
SHOW CREATE TABLE student1  
-- 显示表的结构
DESC student1

5. 数据表的类型

image-20220405173504448

目前只需要记住其中两个：

  1. InnoDB
    • 默认使用
  2. MyISAM
    • 早些年使用
MyIASM InnoDB
事务支持 不支持 支持
数据行锁定 不支持 （是整个表锁定） 支持（只会把行锁定）
外键约束 不支持 支持
全文索引 支持 不支持（5.6.4版本提供了英文支持）
表空间的大小 较小 较大（约为MyISAM的两倍）

注意：InnoDB 在 MySQL 5.6 版本开始支持全文索引。原因是innodb 从1.2.x 开始才增加了全文索引支持。而MySQL5.6版本中innodb的版本才升级到 1.2.x。所以，mysql5.5版本的innodb不支持全文索引。

常规使用操作：

  1. MyISAM 节约空间，速度较快
  2. InnoDB 安全性高，事务的处理，支持外键，多表多用户操作

数据库在物理空间存在的位置：

  • 所有的数据库文件都存在data目录下
  • 本质还是文件的存储

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

  • InnoDB 在数据库表中只有一个.frm文件，以及上级目录下的ibdata1文件夹（MySQL在8.0取消了frm文件，元数据都存储在表空间）
  • MyISAM对应文件
    • .frm文件：表结构的定义文件、
    • .MYD文件：数据文件（表的具体data）
    • .MYI文件：索引文件（index）

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

CHARSET=utf8mb4

不设置的话，会是MySQL默认的字符集编码~（不支持中文），MySQL默认的编码是Latin1，这个编码是可以修改的。

  1. 在创表的时候就声明编码

  2. 在my.ini中配置默认的编码（通用性很低，不推荐，换个没有配置过的电脑就G了）

    charset-set-server=utf8mb4

6. 修改和删除数据表字段

  1. 修改表

    -- 修改表名  ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1 

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

-- 修改表的字段(重命名，修改约束) 
-- MODIFY 能修改字段类型和约束，不能用来重命名  ALTER TABLE teacher1 MODIFY age VARCHAR(11)
-- CHANGE可以用来字段重命名，也可以用来修改约束~~注意：如果要用change修改字段类型和约束,要加上新字段名，新字段名和旧字段名一样就可以修改约束了。
ALTER TABLE teacher1 MODIFY age VARCHAR(11)  -- 修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名

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

  2. 删除表

    -- 删除表(如果表存在)
DROP TABLE IF EXISTS teacher1

    所有的创建和删除操作，尽量加上判断，防止报错

注意点：

  • 字段名，使用反引号 `` 包裹
  • 注释用 -- 或者/* */ 少用#
  • SQL关键字大小写不敏感，看个人喜好（单词大写就跟新的一样，真的绝了）
  • 符号用英文

三、MySQL的数据管理

1. 外键（了解）

外键就相当于，我这张表中的某个字段可以直接引用另外一张表的主键，从而使得两张表产生联系。被引用的表在数据库中无法直接被删除，所以需要先删除引用别人的表（从表），再删除被引用的表（主表）。注意：外键在的表是从表。

image-20220407161332372

外键设置条件：

  1. 要设置外键的字段不能是这个表的主键
  2. 该外键所引用的字段必须是对应表的主键
  3. 两个字段必须具有相同的数据类型和约束

外键设置方式：

  • 方式一：在创建表的时候，增加外键（比较麻烦）

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


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

-- 设置条件：
-- 1. 要设置外键的字段不能是这个表的主键
-- 2. 该外键所引用的字段必须是对应表的主键
-- 3. 两个字段必须具有相同的数据类型和约束 
CREATE TABLE `student1` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(gradeid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

  • 方式二：创建表的时候没有添加外键关系

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


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

-- 设置条件：
-- 1. 要设置外键的字段不能是这个表的主键
-- 2. 该外键所引用的字段必须是对应表的主键
-- 3. 两个字段必须具有相同的数据类型和约束 
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 COMMENT '学生的年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin


-- 创建表的时候没有添加外键关系
-- ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(作为外键的字段名) REFERENCES 被引用的表(被引用表中的字段名);
ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

注意：以上的操作都是物理外键，是数据库级别的外键，不建议使用。

  1. 建表太麻烦，而且很乱，不容易删除表
  2. 数据库过多会造成困扰

建议：

  1. 数据库就是单纯的表，只用来存数据，只有行数据和列（字段）
  2. 想使用外键操作，会用程序去实现，即逻辑外键

2. DML语言（增删改需要记住）

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

DML语言：数据操作语言

  • insert
  • update
  • delete

3. 添加

INSERT

在某一个表中添加数据

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


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 COMMENT '学生的年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

-- 插入语句（添加） INSERT
-- 由于主键自增所以插入的时候不用写主键id的插入
INSERT INTO `grade`(gradename) VALUES('大四');

-- 但是就算主键字段省略，也不能不写其他字段
-- INSERT INTO `grade` VALUES('大三'); 会报错
-- 因为默认插入的话会一一对应插入，类似下面
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES('001','002');
-- 一般写插入语句，我们一定要字段和值一一对应
 
-- 一次插入多行
INSERT INTO `grade`(`gradename`) VALUES('大一'),('大二');

-- 一行插入多个字段
INSERT INTO `student`(`name`,`pwd`,`gradeid`) VALUES ('wangmazi','123456','192');

-- 多行插入多个字段
INSERT INTO `student`(`name`,`pwd`,`gradeid`) VALUES ('zhangsan','1234567','192'),('lisi','12345678','1922');


INSERT INTO `student`(`name`,`pwd`,`sex`,`birthday`,`gradeid`,`address`,`email`) VALUES('xiaohong','111','女','2000-1-10','01','江苏','123'),('xiaoming','222','男','1999-10-8','02','北京','321');

注意：

  1. 多行插入和单行插入
  2. 要插入的字段是可以省略的，但是注意必须要一一对应，做到每一个存在的字段都不能少

4. 修改

UPDATE

修改表中的某个数据，根据某个条件修改某个值

-- 带选择条件
UPDATE `student` SET `name`='Laxsilence' WHERE `id`=1;  

-- 不指定条件的情况下，会把表中所有行的name字段都修改了
-- （千万小心，要是一些关键的表，这样操作就炸了）
UPDATE `student` SET `name`='Laxsilence';

-- 修改多个属性
UPDATE `student` SET `name`='Liu',`email`='15156165@1213' WHERE id=1;

重点：条件判断

where子句可以写很复杂的语句，可以使用运算符，目前常用的是：id 等于或大于小于某个值，或者是在某个区间内

操作符 含义 范围 结果
= 等于 id = 5 改变id = 5的行
<> 或 != 不等于 id<>6 除了id=6之外的都改变
> ... ... ...
< ... ... ...
>= ... ... ...
<= ... ... ...
between 某个之间 id between 2 and 5 在2和5之间的（闭合区间）改变
and 我和你 两个条件都成立 id <5 and age=16 id小于5并且年龄等于16的
or 我或你 两个条件成立一个 id>1 or age>12 id大于1或者年龄大于12 
UPDATE `student` SET `name`='Liu',`email`='15156165@1213' WHERE id=1;
UPDATE `student` SET `name`='jiang' WHERE id>2 and `name`='Laxsilence';
UPDATE `student` SET `birthday`=CURRENT_TIMESTAMP() WHERE id>3 and `name`='jiang';

注意：value可以是一个具体的值，也可以是变量或方法，如果设置多个字段，需要用英文逗号隔开这些字段。

5. 删除

delete

从某个表中删除数据

-- 删除数据
DELETE FROM `student` -- 如果没有条件的话，就会清空整个表里面的数据（千万不要）
DELETE FROM `student` WHERE id=1;

truncate

完全清空一个数据库表，表的结构和索引约束也不会变。（delete也不会变）

TRUNCATE TABLE `student`

delete 和 truncate 的区别：

  • 相同点：都能删除数据，都不会删除表结构
  • 不同点：
    • truncate会重新设置 自增列 计数器会归零
    • truncate不会影响事务
    • delete 不会影响自增，如果继续增加数据的话，就会在原来自增量的基础上继续增加

了解：delete删除的问题，删除完毕之后，重启数据库，会有以下现象：

  1. InnoDB 自增列会重新从1开始，因为这个引擎是存在内存中的，所以断电即失
  2. MyISAM 继续从上一个自增量开始，是存在文件中的，不会丢失

四、DQL查询数据（MySQL重点）

1. DQL 及 Select 完整语法

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

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

Select完整的语法，注意 [ ] 代表可选的，{ } 代表必选的

注意：语法顺序严格按照下面顺序，顺序不能随意颠倒

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2p[as alias2]][,...]]}
FROM table_name[as table_alias]
	[left | right | inner join table_name2] -- 联合查询
	[on ...]-- 临时表条件
	[where ...] -- 最后结果过滤条件
	[group by ...]-- 指定结果按照哪几个字段来分组
	[having] -- 过滤分组的记录必须满足的次要条件
	[order by...]-- 指定查询记录按照一个或多个条件进行排序
	[limit {[offset,]row_count | row_countOFFSET offset}];-- 指定查询的记录从哪条至哪条

但是SQL的执行顺序是如下的：

在SQLServer中，select语句的执行顺序是：

  1. FROM JOIN ON
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT DISTINCT TOP()
  6. ORDER BY

2. 指定查询字段（最简单的Select语句）

用到的一些表创建语句：

CREATE DATABASE IF NOT EXISTS `school1`;
-- 创建一个school数据库
USE `school1`;

-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) 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,'预科班');

-- 创建成绩表
DROP TABLE IF EXISTS result;
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',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

INSERT INTO result(studentno,subjectno,examdate,studentresult)
VALUES
(1001,1,'2013-11-11 16:00:00',85),
(1001,2,'2013-11-12 16:00:00',70),
(1001,3,'2013-11-11 09:00:00',68),
(1001,4,'2013-11-13 16:00:00',98),
(1001,5,'2013-11-14 16:00:00',58);

-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE student(
studentno INT(4) NOT NULL COMMENT '学号',
loginpwd VARCHAR(20) DEFAULT NULL,
studentname VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
sex TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
gradeid INT(11) 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=MYISAM DEFAULT CHARSET=utf8;

-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO student (studentno,loginpwd,studentname,sex,gradeid,phone,address,borndate,email,identitycard)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 创建科目表
DROP TABLE IF EXISTS SUBJECT;
CREATE TABLE SUBJECT(
subjectno INT(11) 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 AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 插入科目数据
INSERT INTO SUBJECT(subjectno,subjectname,classhour,gradeid)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

简单的查询语句：

-- 查询全部的学生  select 字段 from 表
SELECT * FROM student;
SELECT * from result;

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

-- 别名 给查出来的结果的列名换一个名字 AS（AS可以省略）
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student;

-- 表也可以起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表1;

-- 函数的使用 CONCAT(str1,str2,...)拼接字符串
SELECT CONCAT('姓名:',StudentName) AS 拼接名字 FROM student;

语法：SELECT 字段,... FROM 表

注意：如果列名字不是很通俗易懂，可以自己起一个别名

其他简单用法：

  1. 去重

    distinct：去除select查询出来的结果中重复的数据，重复的数据只显示一条

    -- 查询有哪些同学参加了考试，有成绩
SELECT * FROM result; -- 查询全部的考试成绩
SELECT `StudentNO` FROM result; -- 查询有哪些同学参加了考试

-- 发现重复数据，需要去重
SELECT DISTINCT `StudentNO` FROM result;

  2. 数据库中的表达式

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

-- 学员考试成绩 +1 分
SELECT `StudentNo`,`StudentResult`+1 AS 加分后 FROM result

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

    语法升级：SELECT 表达式 FROM 表

3. where 条件子句 和 模糊查询

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

搜索的条件由一个或者多个表达式组成，结果为布尔值。

官网：https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

  1. 逻辑运算符

    运算符 语法 描述
    and && a and b a&&b 逻辑与，两个都为真，结果为真
    or || a or b a||b 逻辑或，有一个为真，则结果为真
    not ！ not a !a 逻辑非，真为假，假为真

    推荐使用英文字母，提高可读性

    -- ================================ where =========================================
SELECT `studentNO`,`StudentResult` FROM result;

-- 查询考试成绩在 95~100 分之间的(AND)
SELECT `studentNO`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- 查询考试成绩在 95~100 分之间的(&&)
SELECT `studentNO`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
-- between ... and（区间）
SELECT `studentNO`,`StudentResult` FROM result
WHERE `StudentResult` BETWEEN 95 AND 100;

-- 除了1000号学生之外的同学的成绩
SELECT `studentNO`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
SELECT `studentNO`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;

  2. 模糊查询：本质是比较运算符

    运算符 语法 描述
    IS NULL a is null 如果操作符为null，结果为真
    IS NOT NULL a is not null 如果操作不为null，结果为真
    BETWEEN a between b and c 若a 在b和c 之间，则结果为真
    LIKE a like b SQL匹配，如果a匹配b，则结果为真
    IN a in (a1,a2,a3...) 假设a在a1，或者a2...其中的某一个值中，结果为真

    注意：LIKE 区分大小写

    -- ================================ 模糊查询  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 '赵__';


-- 查询名字中间有强字的同学
SELECT `StudentNO`,`StudentName` FROM `Student`
WHERE `StudentName` LIKE '%强%';

-- ================================ in （具体的一个或多个值 不能模糊匹配，不能使用% _）=========================================

-- 查询1001 1000 号学员信息
SELECT `StudentNO`,`StudentName` FROM `Student`
WHERE StudentNo IN (1001,1000);

-- 查询在北京的学员的信息
SELECT `StudentNO`,`StudentName` FROM `Student`
WHERE address IN ('北京','安徽');

-- ================================ NULL   NOT NULL=========================================

-- 查询地址为空的学生  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. 联表查询（JOIN ON）以及 自连接查询

  1. JOIN ON

    共有七种，即七种JOIN理论：

    image-20220408151231159

    -- ====================联表查询 JOIN ================

-- 查询参加了考试的同学（学号，姓名，科目的编号，分数），发现成绩表里没有姓名，这时候就需要联表查询
/*
思路：
1. 分析需求，分析查询的字段来自哪些表，如果超过一张表就需要联表查询
2. 确定使用哪种联表查询，共有七种，需要确定交叉点，这两个表中哪个数据是相同的（StudentNo）
	判断的条件：学生表中的StudentNo = 成绩表中的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;-- WHERE可以对on条件里的内容再次进行过滤  

-- LEFT JOIN
SELECT s.`studentNo`,`studentName`,`subjectNo`,`studentResult`
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno;-- WHERE可以对on条件里的内容再次进行过滤
    操作 描述
    Inner Join 以两个表都有的值为基准
    Left Join 从左表中返回所有的值，即使右表中没有匹配
    Right Join 从右表中返回所有的值，即使左表中没有匹配 
    -- 查询缺考得同学
SELECT s.`studentNo`,`studentName`,`subjectNo`,`studentResult`
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentResult IS NULL; 

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

-- 我要查询哪些数据 SELECT ...
-- 从哪几个表中查 FROM 表 xxxx JOIN 连接的表 ON 交叉条件
-- 假设存在多张表查询，慢慢来，先查询两张表，再慢慢增加

-- from a LEFT JOIN B  以a为基准

    on 和where条件的放置详解
    总结：

    1. 对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面

    2. 对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面。

    数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

    在使用left jion时，on和where条件的区别如下：

    1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

    2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

    假设有两张表：

    • 表1：tab2

      id size
      1 10
      2 20
      3 30

    • 表2：tab2

      size name
      10 AAA
      20 BBB
      20 CCC

    两条SQL:

    select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)
    image-20220408160301134

    其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

    总结:
    在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里

    原文链接：https://blog.csdn.net/itguangit/article/details/83011781

    -- 查询学员所属的年级（学号，姓名，年级名称）
SELECT studentno,studentName,gradename
FROM student s
INNER JOIN grade g
ON s.gradeid = g.gradeid;
-- 查询科目所属的年级(科目名，年级名)
SELECT subjectname,gradename
FROM `subject` sub
INNER JOIN grade g
ON sub.gradeid=g.gradeid;
-- 查询参加了数据库结构考试的同学的信息（学号，学生姓名，科目名，分数）
SELECT r.studentno,studentname,subjectName,studentresult
FROM result r
LEFT JOIN `subject` sub
ON r.subjectno=sub.subjectno
LEFT JOIN student s
ON r.studentno=s.studentno
WHERE subjectname LIKE '%数据库%';

  2. 自连接查询

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

    建表语句：

    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','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');

    image-20220408161444967

    对这张表进行拆解：

    • （父类）因为categoryid没有为1的，所以pid为1的是父类：

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

    • （子类）子类的pid 指向父类的 categoryid：

      pid categoryid categoryname
      3 4 数据库
      2 8 办公信息
      3 6 web开发
      5 7 美术设计

    操作：查询父类对应的子类关系（就是要得到以下的表）

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

5. 分页（LIMIT）和排序（ORDER BY）

  1. 排序（也可以利用前端排序）

    ORDER BY 排序：升序 ASC，降序 DESC
    ORDER BY 通过哪个字段排序 怎么排

    -- ORDER BY 排序：升序 ASC，降序 DESC
-- ORDER BY 通过哪个字段排序 怎么排
-- 查询参加了数据库结构考试的同学的信息（学号，学生姓名，科目名，分数）

-- 按照降序排序  
SELECT r.studentno,studentname,subjectName,studentresult
FROM result r
LEFT JOIN `subject` sub
ON r.subjectno=sub.subjectno
LEFT JOIN student s
ON r.studentno=s.studentno
WHERE subjectname LIKE '%数据库%'
ORDER BY studentresult DESC;

-- 按照升序排序  
SELECT r.studentno,studentname,subjectName,studentresult
FROM result r
LEFT JOIN `subject` sub
ON r.subjectno=sub.subjectno
LEFT JOIN student s
ON r.studentno=s.studentno
WHERE subjectname LIKE '%数据库%'
ORDER BY studentresult ASC;

  2. 分页

    总结 ：假设每页显示五条

    1. 第一页：limit 0,5
    2. 第二页：limit 5,5
    3. 第三页：limit 10,5
    4. 第 N 页：limit (N-1)*5,5

    规律：

    1. 某一页的分页公式：limit (n-1)*pagesize,pagesize (n代表当前页，pagesize代表该页显示的数据量，即页面大小)
    2. 总页数：数据总数/页面大小（记得做一个向上取整的处理）
    -- 分页 LIMIT
-- 假设有100万条数据
-- 为什么要分页？
--  1. 缓解数据库压力
--  2. 给用户更好的体验  
-- 目前还有一种处理叫瀑布流，一次加载一点，但能一直无限刷下去

-- 分页，每页只显示五条数据
-- LIMIT n,m  起始行（首项为0），显示m条数据
-- LIMIT 0,5   显示第1~5行数据
-- LIMIT 1,5   显示第2~6行数据
-- LIMIT 6,5   （第二页的数据）
-- 网页应用：显示当前页，总页数，一页显示多少条数据
-- 按照降序排序 
SELECT r.studentno,studentname,subjectName,studentresult
FROM result r
LEFT JOIN `subject` sub
ON r.subjectno=sub.subjectno
LEFT JOIN student s
ON r.studentno=s.studentno
WHERE subjectname LIKE '%数据库%'
ORDER BY studentresult DESC
LIMIT 0,1;

-- 总结 ：假设每页显示五条
-- 第一页：limit 0,5
-- 第二页：limit 5,5
-- 第三页：limit 10,5
-- 第 N 页：limit (N-1)*5,5
-- 规律：
--   1. 某一页的分页公式：limit (n-1)*pagesize,pagesize (n代表当前页，pagesize代表该页显示的数据量，即页面大小)
--   2. 总页数：数据总数/页面大小（记得做一个向上取整的处理）

综合小练习：

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

6. 分组（Order By）和 过滤（Having）

-- ========================分组========================

-- 查询不同课程的平均分，最高分，最低分
-- 核心：根据不同的课程分组
SELECT subjectName,AVG(studentResult) AS 平均分,Max(studentResult) AS 最高分,MIN(studentResult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectNo = sub.subjectno -- 如果不在后面进行分组，就只能查出一行数据，且这行数据的所有值都是相对于整体而言的
-- WHERE AVG(studentresult)>=80  会发现这里用where会报错，where条件不能过滤聚合函数
GROUP BY r.subjectNo -- 通过什么字段来分组
Having 平均分>80 -- 函数进行过滤用having

7. where 和 having的区别

SQL的执行顺序是如下的：

select语句的执行顺序是：

  1. FROM JOIN ON
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT DISTINCT TOP()
  6. ORDER BY

select语句在where子句执行的时候，取别名的语句还没执行，即该别名不存在，所以where不能使用别名，而order by的时候别名就已经命名好了。

同时，如果在from子句中指定了表别名，那么它所在的select 语句中其他子句都必须使用表别名来代替原始的表名，因为from子句是select语句中最先被执行的。

where和having的区别：

先看一段代码：

1.
SELECT name,SUM(score) FROM table_name
GROUP BY name
HAVING name='小明';

2.
SELECT name,SUM(score) FROM table_name
WHERE name='小明'
GROUP BY name;

这两段语句得到的结果是一样的。

那么他们两者的区别在哪呢？

首先，Where 子句是用来指定 "行" 的条件的，而Having 子句是指定 “组” 的条件的，即

Where 子句 = 指定行所对应的条件

Having 子句 = 指定组所对应的条件

因此，2 语句会比较合适。

WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。

HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。

其次，当在Where子句和Having子句中都可以使用的条件，从语句的执行效率来看，最好写在Where子句中。

此外，Where子句中不能使用聚合函数，而Having子句中可以。

两者的执行顺序：

where>聚合函数(sum,min,max,avg,count)>having

若须引入聚合函数来对group by 结果进行过滤 则只能用having。

select sum(score) from student  where sex='man' group by name having sum(score)>210

注意事项 ：

  1. where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
  2. where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
  3. having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

在执行速度上来说：

在使用Count函数等对表中的数据进行聚合操作时，DBMS内部会进行排序处理，而排序操作会增加机器的负担，减少排序的行数，可以增加处理速度。

使用Where子句指定条件时，由于排序之前就对数据进行了过滤，所以能够减少排序的数据量。但是Having子句是在排序之后才对数据进行分组的，因此与前者相比，需要排序的数据量就要多得多。

使用Where子句更具速度优势的另一个理由是，可以对Where子句指定条件所对应的列创建索引，这样可以大幅提高处理速度。

8. 子查询（嵌套查询）

之前我们一直写的 where 语句是固定的，是具体的，具体到是否相等，是否在某个区间

那么，怎么让where后面的语句是计算出来的呢

本质：where语句中嵌套一个子查询语句 where(select * from ...)

-- ======子嵌套=========
-- 查询数据库结构课程的所有考试结果（学号，科目编号，成绩），并进行降序排列

-- 方法一：连接查询
SELECT studentno,subjectname,studentResult
FROM result r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname LIKE '%数据库%'
ORDER BY studentresult DESC;

-- 方法二：子查询（先查询里面的，再查询外面的，由里及外执行）
SELECT studentno,subjectno,studentResult
FROM result 
WHERE subjectno=(
	-- 查询数据库这么科目的编号
	SELECT subjectno
	FROM `subject`
	WHERE subjectname = '数据库结构-1'
)ORDER BY studentresult DESC;


-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.studentNo,studentname
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
WHERE r.studentresult >= 80;
-- 在这个基础上，增加一个科目，高等数学-2
SELECT DISTINCT s.studentNo,studentname
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
WHERE r.studentresult >= 80 
AND r.subjectno=(
	SELECT subjectno 
	FROM `subject`
	WHERE subjectname='高等数学-2'
);

-- 查询高等数学-2的考试分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.studentNo,studentname
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE sub.subjectname = '高等数学-2'
AND r.studentresult >=80 ;

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


-- 查询c语言-1的前五名同学的成绩的信息（学号，姓名，分数 ）
SELECT s.studentno,studentname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
WHERE r.subjectno = (
	SELECT subjectno FROM `subject`
	WHERE subjectname = 'C语言-1'
)
ORDER BY studentresult DESC
LIMIT 0,5;

9. Select 小结

select语句小结：

image-20220408221213556

在业务层面，数据库可能需要跨表，甚至跨地区，比如下面这个例子，一个页面，可能用到了很多种数据库：

image-20220408221625885

五、MySQL 函数

官网：https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

1. 常用函数

-- ====================== 常用函数=====================
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4)-- 向上取整
SELECT FLOOR(9.4)-- 向下取整
SELECT RAND() -- 返回一个[0,1)的随机数
SELECT SIGN(15) -- 判断一个数的符号 0:0  负数:-1 正数:1

-- 字符串函数
SELECT CHAR_LENGTH('当时年少春衫薄') -- 字符串长度
SELECT CONCAT('骑马','倚','斜桥')-- 拼接
-- 插入或替换字符串  `INSERT`(str,要插在第几个位置,覆盖后面几位（0的话就是纯插入）,要插入的新字符串)
SELECT INSERT('满楼红招',4,0,'袖') 
SELECT LOWER('abcABC')  -- 大写转小写
SELECT UPPER('abcABC') -- 小写转大写
SELECT INSTR('DearLiu','i') -- 查询某个字符出现在字符串中的位置
SELECT REPLACE('总有焦虑惶惶','总有','少年') -- 替换指定字符串
SELECT SUBSTRING('心中是万丈豪情热血' FROM 4 FOR 4) -- 截取返回指定的字符串 （字符串，从哪里开始截取，截取的长度）
SELECT REVERSE('年少的欢喜是你') -- 反转字符串 
-- 查询姓赵的同学把赵换成邹
SELECT REPLACE(studentname,'赵','邹') FROM student
WHERE studentname LIKE '赵%'

-- 时间和日期函数（重要）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() -- 获取本地时间
SELECT SYSDATE() -- 获取系统时间
SELECT YEAR(NOW()) -- 获取年
SELECT MONTH(NOW()) -- 获取月
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER() -- 获取系统用户
SELECT USER() -- 同上
SELECT VERSION() -- 获取MySQL版本

2. 聚合函数（常用）

官网：https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

函数名称 描述
COUNT（） 计数
SUM（） 求和
AVG（） 平均值
MAX（） 最大值
MIN（） 最小值
... ... 
-- ================聚合函数==============
SELECT COUNT(studentname) FROM student; -- COUNT（字段） 查询有多少个学生
SELECT COUNT(*) FROM student; -- COUNT（*）计算行数 
SELECT COUNT(1) FROM student; -- COUNT（1）计算行数 
SELECT SUM(studentResult) AS 总和 FROM result
SELECT AVG(studentResult) AS 平均分 FROM result
SELECT MAX(studentResult) AS 最高分 FROM result
SELECT MIN(studentResult) AS 最高分 FROM result

这三个都能够统计表中的数据，count(*) 和 count(1)和count(列名)区别 如下：

注意：count(*) 和 count(1)基本没有差别

执行效果上 ：

  1. count(*)包括了所有的列，相当于行数，在统计结果的时候， 不会忽略列值为NULL
  2. count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候， 不会忽略列值为NULL
  3. count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数， 即某个字段值为NULL时，不统计。

执行效率上：

  1. 列名为主键，count(列名)会比count(1)快
  2. 列名不为主键，count(1)会比count(列名)快
  3. 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
  4. 如果有主键，则 select count（主键）的执行效率是最优的
  5. 如果表只有一个字段，则 select count（*）最优。

3. 数据库级别的MD5加密（拓展）

JS 里面 使用过MD5 ，数据库中也有MD5，比如user表中的密码就是加密过的。

MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性。MD5算法因其普遍、稳定、快速的特点，仍广泛应用于普通数据的加密保护领域。

MD5不可逆！但两个相同的MD5对应的值是一样的。比如说，所有的123456经过一次加密后的值肯定都是一样的。

MD5破解网站的原理：背后有一个字典，输入MD5加密后的值，如果巧合循环到了，就会返回加密前的值。（暴力破解）

百度百科：

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。MD5由美国密码学家罗纳德·李维斯特（Ronald Linn Rivest）设计，于1992年公开，用以取代MD4算法。这套算法的程序在 RFC 1321 标准中被加以规范。1996年后该算法被证实存在弱点，可以被加以破解，对于需要高度安全性的数据，专家一般建议改用其他算法，如SHA-2。2004年，证实MD5算法无法防止碰撞（collision），因此不适用于安全性认证，如SSL公开密钥认证或是数字签名等用途。

-- ===================测试MD5加密=========
CREATE TABLE `testmd5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

-- 明文密码
INSERT INTO `testmd5` VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1 -- 只加密id为1的密码
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id-- 加密所有密码

-- 插入的时候就加密
INSERT INTO `testmd5` VALUES(4,'xiaoming',MD5('123456'))

-- 如何检验用户输入的密码是正确的,将用户传递进来的密码，进行md5加密，然后比对加密后的值
 SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5(MD5('123456')) -- 这里用两次加密后的值比较，是因为前面加密了两次

六、事务

1. 什么是事务

要么都成功，要么都失败

  • SQL执行，A给B转账 A：800 B:200 A--->B: 200

  • SQL执行，B收到A的钱 A: 600 B:400

如上例子：

  • 如果1执行成功了，2执行失败，那么A的钱少了，B的钱没有多；

  • 如果2执行成功了，1执行失败，那么A的钱没有少，B的钱变多了。

由此可见，这两条语句必须要求，要么都执行成功，要么都执行失败！

事务是一系列的动作，它们综合在一起才是一个完整的工作单元，这些动作必须全部完成，如果有一个失败的话，那么事务就会回滚到最开始的状态，仿佛什么都没发生过一样。在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

以下内容部分参考自博客：https://blog.csdn.net/dengjili/article/details/82468576/

  1. 事务的原则：

    ACID原则：原子性，一致性，隔离性，持久性 （脏读，幻读...）

    原子性是事务的基础，持久性和隔离性是手段，一致性是目的。

    谈到事务一般都是以下四点

    • 原子性（Atomicity）

      原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

      在转账问题中，原子性表示，这两个步骤一起成功，或者一起失败，不能只发生其中一个动作。就是说要么都成功，要么都失败

    • 一致性（Consistency）

      事务前后数据的完整性必须保持一致。（最终一致性）

      在转账问题中，一致性表示，事务完成后，符合逻辑运算，操作前，总数为1000，操作后还是1000。

      就是说，事务前后的数据完整性要保持一致

    • 隔离性（Isolation）

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

      转账问题中：如果还有一个事务

      1. SQL执行，C：1000 B:200 C--->B：100
      2. SQL执行，B 收到C的100 C：900 B：300

      则事务1：A向B转账200，事务2：C向B转账100

      但是两个事务是互不干扰的，各自执行各自的事务内容。

    • 持久性（Durability）

      持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

      在转账问题中：如果在操作前（事务还没有提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该是A：800，B：200；如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为A：600，B：400。

      就是说，事务没有提交，恢复到原状；事务已经提交，就持久化到数据库，事务一旦提交，就不可逆。

  2. 隔离所导致的一些问题：

    • 脏读：

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

      image-20220409153832022

      如果第二个事务读取到B的值是第一个事务还未提交的数据的话，那么最终结果就是：在第一个事务中，B的值是400，第二个事务中B的值是300，就会出现数据错误的情况。

    • 不可重复读：

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

      不可重复读是读取的数据被修改了

      image-20220409154216324

    • 幻读（虚读）

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

      幻读是读取的数据数量发生变化了，读取到了新插入的数据

      image-20220409154453250

    • 第1类丢失更新：

      两个事务均进行更新操作，相互影响，某一事务撤销影响最终结果的准确性。

    • 第2类丢失更新：

      事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失。为了避免此问题，可以使用Repeatable Read隔离级别，或者查询和更新操作用where、set price=price+10等类型语句。

  3. 事务的四种隔离级别：

    以下内容部分来源：https://blog.csdn.net/qq_36792191/article/details/104022886

    数据库事务的隔离级别有4种，由低到高分别为Read Uncommited、Read Commited、Repeatable Read、Serializable。并发数据访问时可能会出现以下问题，3类数据读取问题（脏读、不可重复读、幻读）和2类数据更新问题（第1类丢失更新和第2类丢失更新）。

    • Read Uncommited，读未提交，即一个事务可以读取另一个未提交事务的数据；并发操作会导致三类问题都出现。
    • Read Commited，读已提交，即一个事务要等到另一个事务提交后才能读取数据；解决脏读问题；并发操作会导致不可重复读
    • Repeatable Read，重复读，即开始读取数据（事务开启）时，不再允许修改操作；解决幻读、不可重复读问题；并发操作会导致幻读（对应insert操作）（MySQL默认级别）
    • Serializable，序列化，最高的事务隔离级别，该级别下，事务串行化顺序执行；避免脏读、不可重复读与幻读；但是该级别效率低下，比较消耗数据库性能，一般不用。

2. 测试事务实现转账

-- ======================事务======================
-- 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 utf8mb4 COLLATE utf8mb4_bin

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

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; -- 恢复自动提交

七、索引

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

通过索引可以更快速的获取数据库的结果。

创建索引其实就是在内存中重新创建B+树。

索引的优势在于：

  • 提高查询效率。
  • 降低数据排序的成本。

缺点在于：

  • 索引会占用磁盘空间。
  • 索引会降低更新表的效率。因为在更新数据时，要额外维护索引文件。

索引的底层研究，可以参考此篇博客：https://blog.csdn.net/MortShi/article/details/121940296

以及这一篇：https://www.csdn.net/tags/MtTaEgzsNzIwMDcxLWJsb2cO0O0O.html

最推荐这一篇：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

1. 索引的分类

MYSQL索引有四种PRIMARY、INDEX、UNIQUE、FULLTEXT， 这四种都是单列索引，也就是他们都是作用于单个一列，所以也称单列索引；但是所以一个索引也可以作用于多个列上，称为组合索引或复合索引。

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

  • 主键索引（PRIMARY KEY）
    • 唯一的标识，主键是不可重复的，非空且唯一，一张表只能有一个主键，但主键可以由多个列组合而成，叫联合主键（但极其不推荐）。
  • 唯一索引（UNIQUE KEY）
    • 唯一索引可以重复，有多个列被标识为唯一索引，声明唯一索引的列意味着这一列中每一行的数据都不能重复，但是可以为空。
  • 常规索引（KEY / INDEX）
    • 默认的，index或key设置，没有任何约束。
  • 全文索引（FULLTEXT）
    • 全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
    • 快速定位数据

索引的创建三种方式：

-- 索引的使用
-- 1. 在创建表的时候给字段增加索引
-- 2. 创建完毕后，增加索引

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

-- 增加一个索引的三种方式：
-- 1. 在创建表的时候添加索引
-- 2. ALTER 修改表添加索引
-- 3. 直接创建索引create
ALTER TABLE school1.`student` ADD FULLTEXT INDEX `studentName`(`studentName`);-- 方式2
CREATE INDEX id_app_user_name ON app_user(`name`)-- 方式3

-- EXPLAIN 分析SQL执行的状况
EXPLAIN SELECT * FROM student  -- 非全文索引
EXPLAIN SELECT * FROM student  WHERE MATCH(studentName) AGAINST('赵')

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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

-- ======================插入100万条数据
DELIMITER $$ -- 写函数之前必须要写这行当做标注（自定义函数）
CREATE FUNCTION mock_data()
RETURNS INT DETERMINISTIC
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
		VALUES (CONCAT('用户',i),'215465456@12132',CONCAT('151',FLOOR((RAND()*(999999999-100000000))+1000000000)),FLOOR((RAND()*2)),UUID(),FLOOR(RAND()*100));
		SET i = i+1;
	END WHILE;
	RETURN i;
END;
SELECT mock_data();


EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- 查了992262行，用时1.165s

-- 创建索引，给表中的字段添加索引
CREATE INDEX id_app_user_name ON app_user(`name`)
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- 查了1行，用时0.000s

虽然以及能够明显感觉到索引的效果，但是索引在小数据量的时候，用处还不是很大，但是当数据特别大的时候，有无索引的区别就很明显了。

3. 索引原则

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

索引的数据结构：

推荐阅读超级好文：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

  • Hash类型的索引：
  • B+树，InnoDB默认的索引结构

八、视图

参考博客：https://blog.csdn.net/weixin_40228200/article/details/122020400

1. 视图详解

MySQL引入了视图的概念，所谓视图，其实就是一张虚拟的数据表，该数据表与其他普通数据表一样，有列和属性。

视图和普通的数据表唯一不同的是，视图中本身没有数据，视图中的数据都是从各个数据表中select查询的结果，对视图中数据的插入和修改本质上也是对原始数据表的插入和修改，因此，我们可以修改视图的数据，但是不能删除视图中的数据和向视图内插入数据。

正确的使用视图，可以使得我们在业务场景中更好的实现我们的需求，总得来看，视图有以下优点：

  1. 视图的创建，通常来源于SQL语句，对于很复杂的联表查询SQL语句，如果经常使用，那么写起来也很麻烦，在这种情况下，就可以直接创建视图，这样就把我们联表查询的语句转换成对视图内容的查询，简化了我们的操作。
  2. 视图可以提供一定的安全性，例如在开发过程中，一个数据表中存在了一些敏感数据和非敏感数据，那么对于不可信任的用户，就可以创建一个视图，该视图只包含非敏感数据，并且将不可信用户的操作引导视图上，这样即可以使得不可信用户能够满足正常的业务需求，同时还可以增强系统的安全性。

2. MySQL视图使用

MySQL视图的创建，完全是以select语句的执行为基础的，MySQL视图创建的MySQL命令格式如下：

create view 【视图名】 as 【select语句】;

创建视图SQL语句示例如下：

create view student_grade as select * from student s left join grade g on s.id=g.Sid;

视图在创建后，执行命令，show tables，可以查询到刚才我们创建的视图，它是作为一个表存在的，如下所示：

image-20220409190449018

如果我们向视图内添加或者删除数据，会被MySQL所拒绝。更新视图数据会被允许，但是其本质是对视图对应的数据表的修改，如下所示：

image-20220409190519629

最后，如果我们想删除上述视图，可以执行命令：

alter view student_grade;

九、权限管理和备份

1. 用户管理

  • 可视化界面
    image-20220409181252411

  • SQL命令操作

    用户表：mysql数据库下的user表

    增加用户的本质就是对这张表进行增删改查

    里面有各种权限，也对应着可视化界面中可以赋予的各种权限

     -- =========创建用户============

-- 创建用户
CREATE USER dearLiu IDENTIFIED BY '123456'

-- 修改用户密码
SET PASSWORD = PASSWORD('111111')-- 这个语句已经被淘汰了
ALTER USER 'dearLiu'@'%' IDENTIFIED BY '111111'

-- 重命名
RENAME USER 'dearLiu'@'localhost' TO 'dearLiuWa'@'localhost'

-- 授予全部权限 (ALL PRIVILEGES 除了给别人授权这个权限，其他权限都可以赋予)
GRANT ALL PRIVILEGES ON *.* TO dearLiuWa

-- 查询权限
SHOW GRANT FOR dearLiuWa -- 查看指定用户的权限
SHOW GRANT FOR root@localhost -- 查看root权限

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM dearLiuWa 

-- 删除用户
DROP USER dearLiuWa

2. MySQL 备份

为什么要备份：

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

MySQL 数据库备份的方式

  • 直接拷贝物理文件，data文件

  • 可视化界面，在表或者数据库上右键，点击备份或导出

    image-20220409183625579

  • 使用命令行导出 mysqldump （在命令行使用，不是可视化界面）

    mysql自5.7版本开始，考虑到mysql的安全性而更改对保护机制，在mysqldump备份数据库对时候不能直接在命令行上书写数据库的密码

    -- 使用新的mysqldump命令进行备份
mysqldump  --defaults-extra-file=/etc/my.cnf -hlocalhost -uroot -proot 数据库名 [表名1 表名2 ...] > D:/test.sql


-- 导入/恢复数据库
-- 登陆情况下切换到指定的数据库 导入
source d:/a.sql

要备份数据库，防止数据丢失，或者把数据库导出sql供别人使用。

十、规范数据库设计

1. 为什么需要设计

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

糟糕的数据库设计：

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

良好的数据库设计：

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

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

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

个人博客举例：

  • 收集信息，分析需求

    • 用户表（用户登陆注销，用户的个人信息，写博客，创建分类）
    • 分类表（文章分类，谁创建的）
    • 文章表（文章的信息）
    • 评论表
    • 说说表
    • 友链表（友链信息）
    • 自定义表（系统信息，某个关键的字，或者一些主字段） key ： value

  • 标识实体类（把需求落实到每个字段）

    建立数据库

  • 标识实体之间的关系

    • 写博客： user --> blog
    • 创建分类：user --> category
    • ...

十一、三大范式

1. 为什么需要数据规范化

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

2. 三大范式

以下内容来源于博客：https://www.cnblogs.com/yun---meng/p/12783304.html

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

而通常我们用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF），也就是本文要讲的“三大范式”。

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。{原子性}

举例说明：

img

在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

img

可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）；

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）{每一列对应一件事}

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

举例说明：

img

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，

但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，

这样就不满足第二范式的要求，调整如下，需分成两个表：

img

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

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

举例说明：

img

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

而不是主键“学号”，所以需做如下调整：

img

这样以来，就满足了第三范式的要求。

三大范式简述：

  1. 第一范式的目标是确保每列都是不可再分的最小数据单元
  2. 第二范式每要求一个表只描述一件事情
  3. 第三范式要求表中各列必须和主键直接相关，不能间接相关

规范性和性能的问题：

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

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

十二、JDBC（重点）

用Java操作数据库

1. 数据库驱动

驱动：声卡，显卡

数据库可以在电脑直接用，但是应用程序无法直接连接到数据库，中间需要驱动，不同的数据库有不同的驱动，这些驱动是由各自数据库厂商提供的。

我们的程序会通过数据库驱动和数据库打交道。

2. JDBC

因为不同的数据库之间有不同的驱动，所以SUN公司为了简化开发人员的操作（对数据库进行统一），提供了一个（Java操作数据库的）规范，俗称JDBC（Java DataBase Connection），这些规范的实现由具体的厂商去做。

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

事实上，在架构里面没有什么是加一层解决不了的！

这个时候，程序通过JDBC和不同的数据库驱动打交道，而数据库驱动去操作数据库。

学习JDBC，主要学习两个包

  1. java.sql
  2. javax.sql

还需要导入一个数据库驱动包：mysql-connector-java-5.1.47.jar

下载jar包可以去官网或者maven仓库下载（后续就不需要下载了，直接使用maven进行管理）

3. 第一个JDBC程序

准备工作：创建测试数据库

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60),
 birthday DATE
);

 INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04')

  1. 使用IDEA创建一个普通项目

  2. 导入数据库驱动，即导入jar包

    • 第一步：在项目上右键，创建文件夹，命名为lib

      image-20220410180519518

    • 第二步：将jar包，粘贴到lib目录中

      image-20220410180637181

    • 第三步：右键lib目录，点击add as library

      image-20220410180738273

      成功之后，jar包可以展开：

      image-20220410180819376

  3. 编写测试代码（记得断开可视化界面的所有连接）

    • 总的结构目录：

      image-20220410181112980

    • 编写代码

      package xyz.Laxsilence.lesson1;

import java.sql.*;

/**
 * @ClassName JdbcFirstDemo
 * @Description TODO 我的第一个JDBC程序
 * @Author 刘英俊
 * @Date 2022/4/10 18:10
 * @Version 1.0
 **/
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法
        // 2. 连接信息(用户信息和url)
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
        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，可能存在结果，可以查看结果
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);//查询就执行query，注意其他三个，删除，查询，更新都是更新，都叫updata,查询完会返回一个结果集合
        while(resultSet.next()){//查询完会返回一个结果集合，这个结果集中封装了所有我们查询出来的结果
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("NAME"));
            System.out.println("pws=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("bitrh=" + resultSet.getObject("birthday"));
            System.out.println("=========================================================");
        }
// 6. 释放连接(最早的最后释放)
        resultSet.close();
        statement.close();
        connection.close();
    }
}

代码步骤总结：

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

4. JDBC中的对象分析

  1. 加载驱动部分解释

    // 1. 加载驱动
	//本来的写法：DriverManger.registerDriver(new com.mysql.jdbc.Driver());
        Class.forName("com.mysql.jdbc.Driver");//固定写法

    点击Driver类，发现里面就是静态代码块用来注册驱动

    package com.mysql.jdbc;

import java.sql.DriverManager;
import java.sql.SQLException;

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

    使用反射Class.forName调用会默认对类进行初始化，类初始化的时候其实就已经执行这段静态代码块了，所以不需要去new，new的话反而相当于注册了两次。

    所以使用反射这种写法。

  2. URL

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

//不同的驱动不同的写法

//mysql 默认的端口号是3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3...
//可以把jdbc:mysql看成http这种协议

//oracle 默认的端口号是1521
//jdbc:oracle:thin:@localhost:1521:sid

  3. 连接成功，获取数据库驱动对象

    Connection connection = DriverManager.getConnection(url, username, password);

//connection  代表一个数据库
//数据库设置自动提交
//事务提交
//回滚
//数据库级别干的事情...
connection.rollback();
connection.commit();
connection.setAutoCommit();

  4. 执行SQL的对象

    // 4. 执行SQL的对象 statement 就是执行sql的对象
Statement statement = connection.createStatement();

    除了Statement 还有一个PrepareStatement也是执行SQL的对象

    statement.excuteQuery();//查询操作
statement.execute();//执行所有SQL，有个判断的过程，效率会降低一些
statement.excuteUpdate();//更新，插入，删除都使用这个，返回值为匹配到的行数
statement.excuteBatch();//执行多条SQL

  5. 编写SQL

    String sql = "SELECT * FROM users";

    最好在可视化界面里面编写测试，然后复制到Java中

  6. 查询的结果集对象

    这个结果集封装了所有的查询结果

    使用方式：

    1. 获得指定的数据类型

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

    2. 遍历操作

      while(resultSet.next()){}
resultSet.beforeFirst();//移动指针到最前面，从第一个数据开始查
resultSet.afterLast();//移动到最后
resultSet.next();//移动到下一个数据
resultSet.previous;//移动到前一行
resultSet.absoulte(row);//移动到指定行

  7. 释放连接，很消耗资源，一定要关掉

    //释放连接(最早的最后释放)
        resultSet.close();
        statement.close();
        connection.close();

5. statement对象

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

Statement对象的excuteUpdate方法，用于向数据库发送增删改的SQL语句，excuteUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）

Statement.excuteQuery方法用于向数据库发送查询语句，excuteQuery方法返回代表查询结果的ResultSet对象。

  1. CRUD操作-create

    Statement st = conn.createStatement();
String sql = "insert into user(...,) values(...,)";
int num = st.excuteUpdate(sql);
if(num>0){
    System.out.println("插入成功!");
}

  2. CRUD操作-delete

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

  3. CRUD操作-update

    Statement st = conn.createStatement();
String sql = "update user set name='' where id=1";
int num = st.excuteUpdate(sql);
if(num>0){
    System.out.println("修改成功!");
}

  4. CRUD操作-Retrieve

    Statement st = conn.createStatement();
String sql = "select * from user";
ResultSet rs = st.excuteQuery(sql);
while(rs.next()){
    //根据数据类型进行映射
}

使用代码实现CRUD

  1. 提取工具类

    只有SQL和结果在变化，其他语句都没有变化，可以提取为工具类，提高效率，放在Utils下。

    另外用户信息和URL可以放在配置文件中，减少内容的重复。

    在utils中调用配置文件：

    package xyz.Laxsilence.lesson02.utils;

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

/**
 * @ClassName JdbcUtils
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 19:45
 * @Version 1.0
 **/
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 connection, Statement statement, ResultSet resultSet){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

  2. 测试插入代码

    package xyz.Laxsilence.lesson02;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @ClassName TestInsert
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 20:02
 * @Version 1.0
 **/
public class TestInsert {
    public static void main(String[] args){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            //JDBC 插入数据的时候，就算是全部插入，也要写上所有要插入的字段名，不能省略
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (4,'DearLiu','123456','1132@1213','2021-11-11')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

  3. 测试删除代码

    package xyz.Laxsilence.lesson02;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

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

/**
 * @ClassName TestDelete
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 20:16
 * @Version 1.0
 **/
public class TestDelete {
    public static void main(String[] args){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            //JDBC 插入数据的时候，就算是全部插入，也要写上所有要插入的字段名，不能省略
            String sql = "DELETE FROM users WHERE id = 4";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

  4. 测试修改代码

    package xyz.Laxsilence.lesson02;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

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

/**
 * @ClassName TestUpdate
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 20:17
 * @Version 1.0
 **/
public class TestUpdate {
    public static void main(String[] args){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            //JDBC 插入数据的时候，就算是全部插入，也要写上所有要插入的字段名，不能省略
            String sql = "UPDATE users SET `NAME`='Laxsilence',`email`='212@12' WHERE id=1";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("修改成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

  5. 测试查询代码

    package xyz.Laxsilence.lesson02;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

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

/**
 * @ClassName TestRetrieve
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 20:21
 * @Version 1.0
 **/
public class TestRetrieve {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "SELECT * FROM users WHERE id = 1";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

6. SQL注入问题

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

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

本质：SQL可以拼接，拼接关键字 OR 会导致语句不安全

package xyz.Laxsilence.lesson02;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

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

/**
 * @ClassName SQL
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 20:26
 * @Version 1.0
 **/
public class SQLZhuRu {
    public static void main(String[] args) {
        //正常登陆，不报错
//        login("Laxsilence","123456");
        //SQL注入，通过or欺骗服务器，将获得所有的数据
        login("' or '1=1","' or '123456");
    }
    //登陆业务
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            //正常登陆：SELECT * FROM users WHERE `NAME`='Laxsilence' AND `password`= '123456'
            //SQL注入：SELECT * FROM users WHERE `NAME`='' or '1=1' AND `password`= '' or '123456'
            String sql = "SELECT * FROM users WHERE `NAME`='"+username+"' AND `password`= '"+password+"'";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
                System.out.println("==============================");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

7. PrepareStatement对象

PrepareStatement可以防止SQL注入，并且效率更好！

所以一般使用它来进行增删改查。

  1. 插入

    package xyz.Laxsilence.lesson03;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

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

/**
 * @ClassName TestInsert
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 20:45
 * @Version 1.0
 **/
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement psm = null;
        try {
            conn = JdbcUtils.getConnection();
            //跟 Statement 的区别
            // 1. 使用？占位符代替参数
            String sql = "INSERT INTO users(`id`,`NAME`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
            psm = conn.prepareStatement(sql);//里面需要一个预编译的sql，就是先写SQL，然后不执行
            //2. 手动给参数赋值
            psm.setInt(1,5);//id赋值
            psm.setString(2,"dearLiu1");
            psm.setString(3,"12345678");
            psm.setString(4,"1554@1155");
            //注意点:两个Date注意包名
            // 数据库  java.sql.Date()
            //java   java.util.Date()
            psm.setDate(5,new java.sql.Date(new Date().getTime()));

            // 3. 执行SQL
            int i = psm.executeUpdate();
            if (i>0){
                System.out.println("执行成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,psm,null);
        }
    }
}

  2. 删除

    package xyz.Laxsilence.lesson03;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

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

/**
 * @ClassName TestDelete
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 20:59
 * @Version 1.0
 **/
public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement psm = null;
        try {
            conn = JdbcUtils.getConnection();
            //跟 Statement 的区别
            // 1. 使用？占位符代替参数
            String sql = "DELETE FROM users WHERE id=?";
            psm = conn.prepareStatement(sql);//里面需要一个预编译的sql，就是先写SQL，然后不执行
            //2. 手动给参数赋值
            psm.setInt(1,5);

            // 3. 执行SQL
            int i = psm.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,psm,null);
        }
    }
}

  3. 修改

    package xyz.Laxsilence.lesson03;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

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

/**
 * @ClassName TestUpdate
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 20:59
 * @Version 1.0
 **/
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement psm = null;
        try {
            conn = JdbcUtils.getConnection();
            //跟 Statement 的区别
            // 1. 使用？占位符代替参数
            String sql = "UPDATE users SET `NAME`=? WHERE id=?";
            psm = conn.prepareStatement(sql);//里面需要一个预编译的sql，就是先写SQL，然后不执行
            //2. 手动给参数赋值
            psm.setString(1,"刘英俊");
            psm.setInt(2,1);

            // 3. 执行SQL
            int i = psm.executeUpdate();
            if (i>0){
                System.out.println("执行成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,psm,null);
        }
    }
}

  4. 查询

    package xyz.Laxsilence.lesson03;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

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

/**
 * @ClassName TestRetrieve
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 21:04
 * @Version 1.0
 **/
public class TestRetrieve {
    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();
            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

  5. 避免SQL注入的原理和测试

    package xyz.Laxsilence.lesson03;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

import java.sql.*;

/**
 * @ClassName SQLZhuRuNo
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 21:08
 * @Version 1.0
 **/
public class SQLZhuRuNo {
    public static void main(String[] args) {
        //正常登陆，不报错
//        login("刘英俊","123456");
        //SQL注入
        login(" '' or '1=1'"," '' or '123456'");//PrepareStatement防止了SQL注入
    }
    //登陆业务
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            //PrepareStatement防止了SQL注入,本质是：把传递进来的参数当做字符，用引号包裹起来
            //假设其中存在转义字符就直接忽略，比如''会被直接转义
            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.getString("password"));
                System.out.println("==============================");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

    为什么它这样处理就能预防SQL注入提高安全性呢？其实是因为SQL语句在程序运行前已经进行了预编译，在程序运行时第一次操作数据库之前，SQL语句已经被数据库分析，编译和优化，对应的执行计划也会缓存下来并允许数据库已参数化的形式进行查询，当运行时动态地把参数传给PreprareStatement时，即使参数里有敏感字符如 or '1=1’也数据库会作为一个参数一个字段的属性值来处理而不会作为一个SQL指令，如此，就起到了SQL注入的作用了！

    原文链接：https://blog.csdn.net/qq_43842093/article/details/121344931

    PrepareStatement 会对参数的每一位字符进行遍历，发现出现换行符、引号、斜杠等特殊字符，就会进行转义。转义后上述的sql注入将会失效。
    转义前：select * from user where name = ‘张三’ or 1=’1’；
    转义后： select * from user where name = ‘张三\’ or 1 = \’1’；
    显然，转义后的sql语句查询结果为空。除非有奇葩名字叫“张三’ or 1 = ’1”。

    原文链接：https://blog.csdn.net/weixin_43894816/article/details/120187267

8. 如何将IDEA当做管理数据库的可视化工具

使用教程：

  1. 解决Database侧边栏不显示的问题

    image-20220410212548026

  2. 选择MySQL

    image-20220410212802764

  3. 一些版本问题

    有可能出现连接问题，Driver问题。

    image-20220410215321984

  4. 测试连接是否成功

    image-20220410213239611

  5. 勾选要展示的数据库

    image-20220410213440401

  6. 查看表，双击想要查看的表即可

    image-20220410214103874

  7. 调出底下SQL控制台

    image-20220410214054617

  8. 更新数据

    修改完数据，一定要点击提交

    image-20220410214207171

  9. 如何写SQL语句

    image-20220410214352334

    image-20220410214431132

  10. 切换数据库

    image-20220410214537255

实例演示：

  1. 建表语句

     /*创建账户表*/
 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);

  2. 执行

    image-20220410215002615

9. JDBC 操作事务

要么都成功，要么都失败

ACID原则：原子性，一致性，隔离性，持久性

用Java实现转账事务模拟：

  1. 测试事务成功

    package xyz.Laxsilence.lesson04;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

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

/**
 * @ClassName TestTransaction1
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 21:58
 * @Version 1.0
 **/
public class TestTransaction1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            //1. 关闭数据库的自动提交功能,数据库中还要开启事务，但是Java中一句话就执行了这两个语句
            conn.setAutoCommit(false);//关闭自动提交，并开启事务

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

            //2. 业务完毕，提交事务
            conn.commit();
            System.out.println("成功");
        } catch (SQLException e) {
            try{
                conn.rollback(); //如果失败则回滚事务
            }catch (SQLException e1){
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

  2. 测试事务失败

    package xyz.Laxsilence.lesson04;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;

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

/**
 * @ClassName TestTransaction1
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 21:58
 * @Version 1.0
 **/
public class TestTransaction2 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            //1. 关闭数据库的自动提交功能,数据库中还要开启事务，但是Java中一句话就执行了这两个语句
            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();

            //2. 业务完毕，提交事务
            conn.commit();
            System.out.println("成功");
        } catch (SQLException e) {
            try{
                conn.rollback(); //如果失败则回滚事务,默认会回滚，因为by zero 中断了程序，所以这句话并没有执行，但是还是发生了回滚
            }catch (SQLException e1){
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10. 数据库连接池

数据库连接 --> 执行完毕 --> 释放（从连接到释放是十分浪费系统资源的），所以出现了池化技术。

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

比如说：要执行十条语句，以前的做法好像买十辆自行车，然后用完又卖掉

使用池化技术就类似于共享单车：有几百量共享单车，你借走了十辆，用完还回池子了。

再举个例子：没用池化技术的时候：银行开门，然后服务你一个人，然后关门，来人之后又要开门...重复下去；但是用来池化技术之后：银行开门，有个业务员在等待客户，来客户了直接服务，最后等到没有客户了，服务器要关闭了，才关门。

所以在池化技术过程中，需要设置以下东西：

  1. 最小连接数：常用连接数在100个左右，这个最小连接数就设为100
  2. 最大连接数：业务最高承载上限，超过最大连接数的就需要等待
  3. 等待超时：如果等待的人等到某个时间了，就给他发送一个异常，说不要再等待了

编写连接池只需要实现一个接口：DataSource

关于这个接口的开源数据源实现（拿来即用）：

  • DBCP
  • C3P0
  • Druid：阿里巴巴

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

  1. DBCP

    要用到两个包：commons-dbcp-1.4.jar以及commons-pools-1.6.jar

    将他们导入到IDEA中的lib中

    • 配置文件dbcpconfig.properties

      #连接设置 关键字不要改动，这些是定义好的数据源要读取的
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

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

#最大连接数量
maxActive=50

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

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

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

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

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

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

    • 工具类

      package xyz.Laxsilence.lesson05.utils;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

/**
 * @ClassName JdbcUtils
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 19:45
 * @Version 1.0
 **/
public class JdbcUtils_DBCP {

    private static DataSource dataSource = null;

    static{
        try{
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);
            //创建数据源  工厂模式：创建对象
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }
    //释放连接资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

    • 测试插入

      package xyz.Laxsilence.lesson05;

import xyz.Laxsilence.lesson02.utils.JdbcUtils;
import xyz.Laxsilence.lesson05.utils.JdbcUtils_DBCP;

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

/**
 * @ClassName TestDBCP
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 23:47
 * @Version 1.0
 **/
public class TestDBCP {
    public static void main(String[] args){
        Connection conn = null;
        Statement st = null;
        try{
            conn = JdbcUtils_DBCP.getConnection();
            st = conn.createStatement();
            //JDBC 插入数据的时候，就算是全部插入，也要写上所有要插入的字段名，不能省略
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (4,'DearLiu','123456','1132@1213','2021-11-11')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(conn,st,null);
        }
    }
}

  2. C3P0

    要用到两个包：mchange-commons-java-0.2.19.jar以及c3p0-0.9.5.5.jar

    将他们导入到IDEA中的lib中

    • 配置文件：

      <?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    c3p0的缺省（默认）配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省（默认）
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>


    <!--
    c3p0的命名配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就表示使用的是name是MySQL
    -->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

    • 工具类

      package xyz.Laxsilence.lesson05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @ClassName JdbcUtils
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 19:45
 * @Version 1.0
 **/
public class JdbcUtils_c3p0 {

    private static ComboPooledDataSource dataSource = null;

    static{
        try{
            //创建数据源  工厂模式：创建对象

            // 配置文件写法
            dataSource = new ComboPooledDataSource("MySQL");

           //代码配置 不推荐
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();


        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }
    //释放连接资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

    • 实现类

      package xyz.Laxsilence.lesson05;

import xyz.Laxsilence.lesson05.utils.JdbcUtils_DBCP;
import xyz.Laxsilence.lesson05.utils.JdbcUtils_c3p0;

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

/**
 * @ClassName TestDBCP
 * @Description TODO
 * @Author 刘英俊
 * @Date 2022/4/10 23:47
 * @Version 1.0
 **/
public class TestC3P0 {
    public static void main(String[] args){
        Connection conn = null;
        Statement st = null;
        try{
            conn = JdbcUtils_c3p0.getConnection();
            st = conn.createStatement();
            //JDBC 插入数据的时候，就算是全部插入，也要写上所有要插入的字段名，不能省略
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (5,'DearLiuWa','123456','1132@1213','2021-11-11')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_c3p0.release(conn,st,null);
        }
    }
}

  3. Druid后面会详细学习

结论：

无论使用什么数据源，本质还是一样的，都是实现类DataSource接口，实现了getConnection方法。

11. 小插曲 聊聊Apache

道阻且长

image-20220411003307377

posted @ 2022-04-11 00:44  Laxsilence  阅读(112)  评论(1)    收藏  举报