MySQL(小步笔记)

目录

初始MySQl：

为什么学习mysql：

什么是数据库：

数据库分类:

DBMS(数据库管理系统):

MySQl简介:

 MySQL下载：

MySQL命令行：

基础命令行操作:

数据库四种语言:

          操作数据库

数据库的列类型:

数据库的字段属性(重点）

创建数据库表练习:

MYISAM与INNODB:

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

修改删除表:

 MySQL数据管理:

 外键(了解):

 DML语言(全部记住):

添加：

 修改:

删除:

DQL数据查询语言(最重点):

 指定查询字段:

 where条件子句:

联表查询join ON:

分页和排序:

 子查询和嵌套查询:

MySQL函数:

事务

什么是事务?

事务原则(ACLD):

隔离所导致的一些问题:

索引:

索引的分类: 

索引原则

用户管理: 

 MySQL备份:

规范数据库设计:

三大范式: 

为什么需要数据规范化?

删除异常(丢失有效信息)

三大范式:

规范性和性能问题 

数据库驱动和JDBC(重点) 

数据库驱动:

JDBC

 第一个JDBC程序:

DriverManager

URL

Statement执行SQL对象：

遍历指针:

 释放资源

---

初始MySQl：

前言:java开发大型基本分为,javaSE(java基础),javaME(移动开发目前已经转为Andorid),javaEE(企业级开发),而java企业级开发又分为：javaWeb(页面开发)

• 前端(页面展示数据)
• 后台(连接点：JDBC，链接前端(控制,控制视图跳转,给前端传输数据))
• 数据库(存储数据,Txt,Excel,word)

为什么学习mysql：

现在的程序员不管是做什么首先数据库是必须会的一项技能,并且写程序也需要一个存储数据的地方,同时也可以分为一下三点:

• 岗位需要
• 大数据时代,的数据者得天下
• 被迫需求:存数据
• 数据库是所有软件中最核心的存在 DBA

什么是数据库：

数据库(DB,DataBase)

概念：数据的仓库,软件,安装与操作系统(windows,Linux )之上,SQL可以储存大量数据,500万一下可以直接存储,如果500万以上则需要做一下优化.

作用:存储数据,管理数据

数据库分类:

数据库大型分为:

关系型数据库:(SQL)

• MySQL，Oracle，Sql Server，DB2，SQLlite
• 通过表和表之间,行和列之间的关系进行存储叫做关系型数据库如:学员信息表,考勤表....

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

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

DBMS(数据库管理系统):

DBMS(数据库管理系统)是用来操作数据库里的数据,而MySQL数据库就是一个数据库管理系统是一种操纵和管理数据库的大型软件可以有效，用于建立、使用和维护数据库，简称DBMS.

MySQl简介:

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

前世:由瑞典MySQL AB 公司开发

今生:属于 Oracle 旗下产品

优点:MySQL是最好的RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件之一

特点:体积小、速度快、总体拥有成本低，尤其是开放源码这一特点.同时适用于中小型网站,但也同样适用于大型网站,应为该数据库支持集群.

 MySQL下载：

MySQL安装（详细，适合小白）_在下是小白的博客-CSDN博客_mysql安装

建议:不要使用exe文件安装不然文件会往注册表里跑,建议下载压缩文件解压.

MySQL命令行：

当配置好环境变量就可以启动mysql服务了,可以通过Dos命令行进行启动如:

net start (mysql服务名);

net start mysql0815 //启动mysql服务dos命令
net stop mysql0815  //关闭mysql服务dos命令
sc delete mysql;    //清空mysql服务

启动完成之后就可以登录数据库了,命令如下；

登录第一种方式:mysql -h 主机名(localhost) -P 端口号 -u 用户名 -p 密码
本机登录第二种方式:mysql -u 用户 -p 密码

第一种如:mysql -h localhost -p 3306 -u root -p 98526
第二种如:mysql -u root -p 98526

扩展:

修改密码命令:

--修改用户密码命令在mysql中这表示注释
update mysql.user set authentication_string=password('123456')
where 
user="root" 
and 
Host = 'localhost'; 

刷新权限命令:

flush privileges; --刷新权限

基础命令行操作:

登录之后则会进入mysql命令行,就可以输入SQL语句了：

查看所有数据库命令:

show databases； --查看所有数据库

切换数据库命令:

use school; --切换数据库 use 数据库名称;

查看当前自己所在数据库:

select database(); --查看当前所在数据库

查看当前数据库所有的表:

show tables; --查看数据库所有的表

查看表中的信息:

describe test; --查看指定表 describe 表名;

 创建一个数据库:

create database westos; --创建一个数据库 create database 创键名称;

退出mysql链接:

exit; --退出MySQL链接

MySQL的两种注释:

MySQL中的单行注释
--这里表示注释的内容

MySql的多行注释
/*注释内容*/

数据库四种语言:

DML:数据库操作语言

DQL:数据库查询语言

DCL:数据库控制语言

DDL:数据库定义语言

操作数据库

如果记不住的这些命令的话大可以把他拆分为三个阶段:

操作数据库>操作数据库种的表>操作数据库表中的信息

注意:MySQL的关键字不区分大小写

操作数据库:

1. 创建数据库

   --创建数据库[]里的内容表示如果不存在该数据库则创建数据库,也可以不写
   create database [if not exists] westos
    

2. 删除数据库

   --该语句表示删除hello数据库
   drop database hello;
   --该语句表示如果存在该数据库则删除
   drop database [if exists] hello;

3. 使用数据库

   //进入数据库,如果数据库名是关键字则需要用`加入转义
   use `text`;

4. 查询数据库

   //查看所有数据库
   show databases;

数据库的列类型:

数值类型:

• tinyint         十分小的数据              1个字节
• smallint       较小的数                     2个字节
• mediumint  中等大小的字节           3个字节
• int              标准的整数                  4个字节（常用的）
• big              较大的数据                  8个字节
• float            浮点数                         4个字节
• double        浮点数                         8个字节 （具有精度问题）
• decimal       字符串形式的浮点数    一般金融计算使用decimal

字符串类型:

 char          固定大小0~255

varchar      可变字符串0~65535(常用对应java String)

tinytext       微型文本   2^8-1

text             文本串      2^16-1

时间日期: 

date            YYYY-MM-DD    日期

time            HH：mm：ss      时间格式

datetime    YYYY-MM-DD  HH:mm:SS  最常用时间格式

timestamp 时间戳，1970.1.1到现在的毫秒数(较为常用)

year            表示年份 

null: 空,未知

注意:不要使用null进行计算,否则结果也为null 

数据库的字段属性(重点）

Unsigned:

• 表示无符号整数
• 声明了该列不能声明为负数
• 只有整型可以用

Zerofill:

• 零填充
• 不足的字段使用零来填充如：int(3) ,5...005

自增:

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

 非空(NULL not null):

• 假设为not null如果不给他赋值则会替换为空(不是null同java中的"");
• NULL如果不写值则默认null

默认:

•  默认值
• 如果不指定该列的值则默认为天下的值

拓展:每一个表都必须存在一下五个字段！未来做项目用表示一个记录存在的意义.

• id 主键
• `version`       乐观锁
• is_delete       伪删除
• gmt_create    创建时间
• gmt_ update  修改时间

创建数据库表练习:

练习：创建一个school数据库,创建学生表(列,字段)使用SQL创建,学号int,登录密码varchar(20),姓名性别varchar(2),出生日期(datatime),家庭住址,email

/*创建一个school数据库
 ,创建学生表(列,字段)使用SQL创建,
 学号int,登录密码varchar(20),姓名性别varchar(2),出生日期(datatime),家庭住址,email
*/
CREATE DATABASE IF NOT EXISTS `school`;
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名'  COMMENT '姓名',
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB  DEFAULT CHARSET=utf8;

注意事项:

• 需要使用英文(),表的名称和字段尽量使用``括起来
• 字符串使用单引号括起来！
• 所有的语句后面加英文的逗号,最后一个不用加
• PRIMARY KEY（``）主键一般一个表只有一个主键 

格式:[]里面的表示可写可不写(可选)

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

常用命令: 

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

MYISAM与INNODB:

-----关于数据库引擎-----
INNODB 默认使用该引擎~
MYISAM 早些年使用的引擎

MYISAM与INNODB的区别:

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大,约两倍

 常规使用操作:

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

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

所有数据都存放在data目录下,本质还是文件的存储!

每一个数据库都对应这一个文件.

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

 INNODB:在数据表中只有一个*.frm文件，以及上级目录下的ibdata1文件

路径:C:\ProgramData\MySQL\MySQL Server 5.5\data

MYISAM对应文件:

• *.frm        表结构的定义文件
• *.MYD     数据文件(Data) 
• *.MYI       索引文件(index)

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

CHARSET=utf8

不设置的话,会是mysql默认默认字符集编码Latin1,不支持中文。

同时也可以在my.ini下配置编码.

修改删除表:

修改表名:

ALTER TABLE tang RENAME AS ming;--修改表名 alter table 旧表名 rename as 新表名;

表上增加字段:

ALTER TABLE ming ADD `age` INT(3);--增加字段 alter table 表名 add 字段名 类型 [属性];

修改表的字段:

-- 修改表的字段
ALTER TABLE ming MODIFY age VARCHAR(3); --只能修改约束
ALTER TABLE ming CHANGE age agel INT(3); -- 可以修改名字不能修改约束(但可以修改长度)

删除表的字段:

ALTER TABLE `ming` DROP `tang`; -- 格式:alter table 表名 drop 删除表中的字段

删除表:

DROP TABLE [IF EXISTS] `student`; -- 删除表这里表示如果存在则删除，drop table 表名;

提示:所有删除或者创建操作尽量加上判断以免报错!

注意点:

• ``所有的字段名尽量使用这个符号包裹.
• 注释尽量使用-- 和/**/,单行注释尽量不要使用#号,因为-- 才是SQL语句的标准注释
• sql关键字大小写不敏感,建议使用小写
• 所有的符号全部使用英文!

 MySQL数据管理:

 外键(了解):

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

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


CREATE DATABASE IF NOT EXISTS `school`;

CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名'  COMMENT '姓名',
	`password` 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`),
	-- 添加外键
	-- 表示学生表的gradeid字段要去引用年纪表的gradeid
	-- 1.定义外键key`ming`
	-- 2.给这个外键添加约束(执行引用)
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `KF_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB  DEFAULT CHARSET=utf8;

删除有外键关系表的时候,必须先删除引用别人的表(从表),在删除被引用的表(主表).

方式二：创建表之后在加入外键(较为简洁)

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

-- 格式:alter table 表 add constraint 约束名 foreign key(作为外键的列)  references 到那个表(那个字段)

该操作都是物理级别的外键,是数据库级别的,不建议使用!

最佳实现:

• 把数据库当作单纯的表只有行(数据)和列(字段).
• 如果需要使用在数据库中需要使用外键,使用程序进行链接.

 DML语言(全部记住):

 DML:数据库操作语言

• insert
• updata
• delete

添加：

insert:表示插入(添加)

-- 插入单个字段
INSERT INTO `grade`(`gradename`) VALUES ('小步');

-- 插入多个字段 
INSERT INTO `grade`(`gradename`) VALUES ('小七'),('小八');

-- 插入单行信息
INSERT INTO `student`(`name`,`password`) VALUES ('小步','123456');

-- 插入多行信息
INSERT INTO `student`(`name`,`password`,`sex`) VALUES ('汤','123456','男'),('明','123456','男');

语法:insert into 表名([字段名1,字段名二,字段三,...]) values ('值一'),('值二')...

注意事项: 

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

 修改:

update 修该谁 (条件) set 原来的值=新值

-- 修改学员名字带了条件
UPDATE `student` SET `name`='铺盖' WHERE id=1;

-- 修改学员名字不带条件(修改全部名字)
UPDATE `student` SET `name`='tang';

-- 修改学员多个属性，使用逗号隔开
UPDATE `student` SET `name`='小步',`password`='123456798',`sex`='女' WHERE id=1;

条件:where 字句 表示运算符,定位某个区间内的内容,使用操作符来定位,返回值为布尔类型....

操作符	含义	例子	结果
=	等于	6=8	false
<>或者!=	不等于	6<>8	true
>	大于	6>8	false
<	小于	6<8	true
=>	等于且大于	6=>8	false
<=	小于且等于	6<=8	true
BETWEEN...AND...	从哪里到哪里	BETWEEN 1 AND 9	[1,9]
ADD	并且(&&)	id=1 AND name='女'	无
OR	或者(||)	id=1 OR name='女'	无

语法：UPDATE 表名 SET name='长江一号' WHERE [条件] 

删除:

delete命令

-- 删除数据(避免这样写)
DELETE FROM `student`;
-- 删除指定数据
DELETE FROM `student` WHERE id=1;

TRUNCATE 命令

作用:清空所有的数据,但表的结构和索引约束不会发生改变!

-- 清空表
TRUNCATE `student`;

DELETE与TRUNCATE区别:

相同点:都能删除数据，都不会删除表的结构

不同点:

• TRUNCATE 重新设置 自增列 计数器会归零
• TRUNCATE 不会影响事务

-- 测试delete 和 TRUNCATE的区别
CREATE TABLE `test`(
	`id` INT(4)  NOT NULL  AUTO_INCREMENT,
	`name` VARCHAR(4)  NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 查看表
SELECT * FROM `test`;
-- 插入数据
INSERT INTO `test`(`name`) VALUE ('小'),('步');

-- 试用delete删除数据
DELETE FROM `test`; -- 不会影响自增

-- 使用TRUNCATE清空数据
TRUNCATE `test`; -- 自增会从零开始

了解:DELETE删除问题,重启数据库现象
INNODB：自增会从1开始(存在内存当中,断电既丢失)

MYISAM：继续从上一个自增量开始(存在在文件当中,断电不会丢失)

DQL数据查询语言(最重点):

DQL（Data query language）:数据库查询语言

• 所有查询操作都用他，Select
• 简单查询,和复杂查询他都能做
• 数据库种最核心的语言,最重要的语句
• 使用频率最高的语句

 指定查询字段:

有的时候，列明并不是那么的见名知意,就可以通过AS起别名来查看如:

-- 查询表中全部信息
SELECT * FROM student;

-- 查询表中指定信息,并个给结果起别名
SELECT `id` AS '编号',`name` AS '姓名' FROM `student`;

-- 函数 Concat(a,b)
SELECT CONCAT('姓名:',`name`) AS 新名字 FROM `student`;

去重：

使用关键字DISTINCT进行去除重复操作,去掉SELECT查询出来的重复字段，只显示一条

-- 查询全部
SELECT * FROM `result`;

-- 查询单个字段
SELECT `student` FROM `result`;

-- 去重查询单个字段
SELECT DISTINCT `student` FROM `result`;

数据库的列表达式:

-- 查询数据库版本
SELECT VERSION();

-- 用select来计算计算
SELECT 10*3-9 AS '计算结果';

-- 查询自增步长
SELECT @@auto_increment_increment;

-- 查询学生成绩+1后的成绩
SELECT `student`,`fraction`+1 AS '成绩' FROM `result`;

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

select 表达式 from 表名;

 where条件子句:

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

逻辑运算符:

运算符	语法	描述
and &&	a and b 同 a&&b	并且的意思
or ||	a or b 同 a||b	或者的意思
Not !	Not a 同 !a	取反的意思

尽量使用英文字符

-- 模糊查询
SELECT `student`,`subject` FROM `result` WHERE`student` BETWEEN 0 AND 2; 

-- 查询除了student为1的信息
SELECT `student`,`subject`,`date`,`fraction` FROM `result` WHERE NOT `student`=1;
-- 同
SELECT `student`,`subject`,`date`,`date` FROM `result` WHERE `student`!=1;

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果为null则为true
is not null	a is not null	如果为不为null则为true
BETWEEN	a between b and c	如果a在b和c之间则为真
Like	a like b	SQL匹配,如果匹配成功则为true
In	a in(b,c,d,e,f)	假设a在b,或者c,或者d,或者e,或f，其中的某一个值则为true

练习:

-- ===========LIKE关键字=============

-- 查询所有人
SELECT `id`,`name` FROM `student`;

-- 查询姓刘的人
SELECT `id`,`name` FROM `student` WHERE `name` LIKE ('刘%');

-- 查询名字只有两个子并且姓刘的人
SELECT `id`,`name` FROM `student` WHERE `name` LIKE ('刘_');

-- 查询名字是三个字并且姓刘的人
SELECT `id`,`name` FROM `student` WHERE `name` LIKE('刘__');

-- 查询名字中有嘉子的人
SELECT `id`,`name` FROM `student` WHERE `name` LIKE('%嘉%');

-- ========= in关键字(只能是一个或者多个值) ===========
-- 查询所有人
SELECT `id`,`name` FROM `student`;

-- 查询3，4，8，6，9号学员
SELECT `id`,`name` FROM `student` WHERE `id` IN('3','4','8','6','9');

-- 查询在2022-8-17来的学生
SELECT `id`,`name`,`date` FROM `student` WHERE `date` IN('2022-8-17');

-- 查询成绩为空的学生
SELECT `id`,`name`,`achievement` FROM `student` WHERE `achievement` IS NULL;

-- 查询成绩不为空的学生
SELECT `id`,`name`,`age`,`date`,`achievement` FROM `student` WHERE `achievement` IS NOT NULL;

联表查询join ON:

 使用链表查询可以查询出两个表的字段,或者是相同的值等等如:

-- =========== 联表查询 ==========
-- 查询全部
SELECT * FROM `student`;
SELECT * FROM `region`;

SELECT xs.`id`,xs.`name`,xs.`age`,dz.`address` FROM `student` AS `xs`
INNER JOIN `region` AS dz
ON xs.`id`=dz.`id`;

SELECT xs.`id`,xs.`name`,xs.`age`,dz.`address` FROM `student` AS xs 
RIGHT JOIN `region` AS dz ON xs.`id`=dz.`id`;

SELECT xs.`id`,xs.`name`,xs.`age`,dz.`address` FROM `student` AS xs
LEFT JOIN `region` AS dz
ON dz.id=xs.id;

操作	描述
inner join	如果表中至少有一个匹配则返回
right join	会从左表中返回所有的值,即使右表中没有匹配
left join	会从右表中返回所有的值,即使左表没有匹配

join on与where的区别：

-- join 链接的表 on(判断条件) 连接查询

-- where 等值查询

 -- 思考题(查询学生的id,namm,address)
 /*
 1.分析需求分析查询字段来自哪些表 
 2.确定使用那种链接? 7种
 3.确定交叉点(表中那个字段是相同的)
 */
 
 SELECT xs.`id`,xs.`name`,dz.`examination` FROM `student` AS xs
 LEFT JOIN `region` AS dz
 ON
 xs.id=dz.`id`;

自链接: 

自己的表和自己的表链接，核心:一张表拆为两张一样的表即可如:

-- 查询父子信息 把一张表看为两张一模一样的表
SELECT a.`categoryName`,b.`categoryName` 
FROM `category` AS a , `category` AS b
WHERE
a.`category`=b.`pid`;

-- 查询学生的编号,姓名,成绩(与`category`表id相同的学生)
SELECT `id`,`name`,`achievement` FROM
`student` AS a
INNER JOIN `category` AS b
WHERE
b.`category`=a.`id`;

分页和排序:

排序:

-- 排序:升序 asc:降序 desc
--  查询学生成绩信息,根据成绩排序
-- ORDER BY 通过那个字段排序,怎么排
SELECT `id`,`name`,`achievement` 
FROM
`student`
WHERE 
`achievement` IS NOT NULL 
ORDER BY `achievement` DESC;

分页：

-- 为什么要分页:1.缓解数据库压力 2.给人更好的体验
-- 相对应的还有瀑布流如:百度的图片
-- 分页:每页只显示五条数据
-- 语法:limit 起始页,页面的大小
SELECT `id`,`name`,`achievement` FROM `student`
WHERE
`achievement` IS NOT NULL
ORDER BY `achievement`ASC
LIMIT 0,5;

 语法:limit(起始值,页面大小);

 子查询和嵌套查询:

-- =======子查询======

-- 查询分数不小于80分的学生学号和姓名
SELECT DISTINCT a.`Address`,a.`name`,b.`scores` FROM  `studentnumber` AS a
INNER JOIN `studentscores` AS b
ON a.`name`=b.`name`
WHERE b.`scores`>80;

-- 查询分数不小于80分的学生学号和姓名并且是软件工程的
SELECT a.`Address`,a.`name`,b.`scores`,c.`Major`
FROM `studentnumber` AS a
INNER JOIN `studentscores` AS b
ON a.`name`=b.`name`
INNER JOIN `studentmajor` AS c
ON c.`name`=b.`name`
WHERE 
c.`Major`='软件工程' AND b.`scores`>80;

-- 使用子查询解决查询分数不小于80分的学生学号和姓名并且是电竞的
SELECT DISTINCT a.`id`,a.`Address`,a.`name`,b.`scores`FROM `studentnumber` AS a
INNER JOIN `studentscores` AS b
ON a.`name`=b.`name`
WHERE b.`scores`>70 AND
a.id=(
	SELECT `id` FROM `studentmajor`
	WHERE `Major`='电竞'
)

-- 查询课程为高等数学并且分数不小于70 分的人
SELECT `id`,`name`,`scores` FROM `studentscores`
WHERE
id=(
	SELECT `id` FROM `studentmajor`
	WHERE `Major`='高等数学'
)
AND
`scores`>70;

如果使用子查询出现：Subquery returns more than 1 row则可以使用ANY关键字如:

-- 查询java软件工程前3名的学员成绩信息(学号,姓名,分数)
SELECT a.`Address`,a.`name`,b.`scores` FROM `studentnumber` AS a
INNER JOIN `studentscores` AS b
ON a.`name`=b.`name`
WHERE
a.`name`=ANY(SELECT `name` FROM `studentmajor` WHERE `Major`='软件工程') 
ORDER BY b.`scores` DESC
LIMIT 0,3;

MySQL函数:

官网地址：MySQL ：： MySQL 5.7 参考手册 ：： 12.1 内置函数和运算符参考

 常用函数:

-- ========= 常用函数 =========
SELECT ABS(-8); -- 绝对值
SELECT CEILING(8.1);  -- 向上取整,测试结果:正数则向上取整,如果负数则去掉小数返回一个整负数
SELECT FLOOR(8.9); -- 向下取整,测试结果:正数则向下取整,如果为负数则向上取整返回负数
SELECT RAND(); -- 返回一个0-1之间的随机数
SELECT SIGN(10); -- 判断一个数的符号 0返回0,如果负数则返回-1,正数则返回1

-- 字符串函数
SELECT CHAR_LENGTH('即使在小的帆也能远航'); -- 返回字符串长度
SELECT CONCAT('小','步'); -- 拼接字符串
SELECT INSERT('我爱Hello World',1,2,'热爱'); -- 替换,从某个位置开始到某个位置替换
SELECT LOWER('TangMingYong'); -- 转换为小写
SELECT UPPER('TangMingYong'); -- 转换为大写
SELECT INSTR('TangMingYong','n'); -- 返回子字符串n第一次出现的位置
SELECT REPLACE('你好','你','呃..'); -- 替换指定字符串
SELECT SUBSTR('嘿你好终于回来看我了',6,4); -- 表示从第六个字符开始截取4个字符
SELECT REVERSE('我太南了'); -- 反转字符串

-- 查询姓小的同学并且将姓替换为大
SELECT REPLACE(`name`,'小','大') FROM `studentscores` WHERE `name` 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版本

聚合函数(常用):

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
.........	........

-- ========== 聚合函数 ===========
-- 都能够统计表中的数据（查询一个表中有多少的记录则会用COUNT()）
SELECT COUNT(`name`) FROM `studentscores`; -- COUNT（字段）会忽略所有的null值
SELECT COUNT(*) FROM `studentscores`; -- COUNT(*)  不会忽略null值 本质:计算行数
SELECT COUNT(1) FROM `studentscores`; -- COUNT(1)  不会忽略所有的null值 本质:计算行数

SELECT SUM(`scores`) AS '总和' FROM `studentscores`;
SELECT AVG(`scores`) AS '平均值' FROM `studentscores`;
SELECT MAX(`scores`) AS '最高分' FROM `studentscores`;
SELECT MIN(`scores`) AS '最低分' FROM `studentscores`;

-- 查询不同课程的平均分,最高分,最低分
-- 核心:(根据不同的课程分组)
SELECT SubjectName, AVG(StudentResult) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN subjet sub
ON r.Subjectno = sub.SubjectNo
GROUP BY r.Subjectno  -- 通过字段来分组
HAVING 平均分>80;

数据库MD5加密：

什么时MD5？

MD5是一种加密型的算法,注意特点为:算法的复杂度,和不可逆性.

-- ===== 测试md5加密 =====
CREATE TABLE IF NOT EXISTS `User`(
	`id` INT(10) NOT NULL COMMENT '编号',
	`name` VARCHAR(5) NOT NULL COMMENT '名字',
	`pwd` VARCHAR(20) NOT NULL COMMENT '密码'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入明文密码
INSERT INTO `user` VALUE (1,'小步','123456'),(2,'小七','123456'),(3,'小八','123456');

-- 加密一个
UPDATE `user` SET `pwd`=MD5(`pwd`) WHERE id=1;

-- 全部加密
UPDATE `user` SET `pwd`=MD5(`pwd`) ;

-- 查看表
SELECT * FROM `user`;

-- 插入数据时加密 
INSERT INTO `user` VALUE (4,'小九',MD5('123456'));
INSERT INTO `user` VALUE (5,'小十',MD5('abc98526'));

-- 如何校验:将用户传进来的密码，进行MD5加密，然后比对加密后的值
SELECT * FROM `user` WHERE id=1 AND pwd='12346';

事务

什么是事务?

事务要么成功要么失败

例如:有两条SQL语句,a给b转账,b收到a的转账,如果a给b转账的时候服务器崩了则事务失败了,所以需要将两条sql语句放在一个批次中去执行.

事务原则(ACLD):

ACLD原则:原子性,一致性,持久性,隔离性     (脏读,幻读.....)

原子性（Atomicity）:

原子性表示两个步骤要么一起执行成功,要么一起执行失败,不能只发生其中一个动作

一致性（Consistency）:

针对一个事务前后状态一致(前后数据完整性保持一致)比如:a和b互相转账,a有100元,b有300元,不论怎么转都不会超过400元，

持久性（Durability）：

假设如果服务器断电,事务如果没有提交则回复到原样,如果已经提交则持久到数据库,事务一旦提交则不可逆. 

隔离性（Isolation）:

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

隔离所导致的一些问题:

查看:事务ACID理解_dengjili的专栏-CSDN博客_acid

脏读：

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

不可重复读：

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

虚读(幻读)

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

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


-- 手动处理事务

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

-- 提交:数据库持久化(成功!)
COMMIT

-- 回滚：回到原来的样子(失败)
ROLLBACK 

-- 事务结束
SET autocommit = 1 ;/*开启自动提交(默认的)*/


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

-- 转账
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci 
USE shop

CREATE TABLE IF NOT EXISTS `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `account`(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00);

-- 模拟转账事务
SET autocommit = 0; /*关闭自动提交*/
START TRANSACTION -- 开启一个事务(一组事务)

UPDATE `account` SET `money`=`money`-500 WHERE `name`='A';
UPDATE `account` SET `money`=`money`+500 WHERE `name`='B';

COMMIT; -- 提交事务(会持久化)
ROLLBACK;-- 回滚

SET autocommit = 1;/*开启自动提交*/

索引:

MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构,提取句子主干就可以得到索引的本质:索引是数据结构.

索引的分类: 

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

主键索引:

唯一的标识主键不可重复,只能有一个列作为主键

唯一索引:

避免重复的列出现,唯一索引可以重复，多个列都可以标识位 唯一索引

常规索引：

默认的index,key关键字来设置

全文索引:

在特定的数据库引擎下才有，MyISAM，用来快速定位数据

 添加外部索引将在内存中创建一个空间专门存放对应的索引,会极大的提升查询效率

-- 添加外部索引 
CREATE INDEX `user` ON `user`(`name`);

索引原则

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

索引的数据结构: 

hash 类型的索引

Btree : InnoDB的默认数据结构

推荐文章:CodingLabs - MySQL索引背后的数据结构及算法原理

用户管理: 

mysql的用户表在:mysql.user

本质:对这张表进行增删改查

-- =========== 用户管理命令 ==========
-- 创建用户	CREATE USER 用户名 IDENTIFIED BY '密码';
CREATE USER xiaobu IDENTIFIED BY '123456';

-- 修改密码(修改当前用户密码)
SET PASSWORD =PASSWORD('98526');

-- 修改密码(修改指定用户密码)
SET PASSWORD FOR xiaobu = PASSWORD('123456');

-- 重命名 RENAME USER  原来的名字 TO  新的名字
RENAME USER xiaobu TO tangmingyong

-- 用户授权 授予用户全部的权限(除了给别人授权)
GRANT ALL PRIVILEGES ON *.* TO tangmingyong;

-- 查看权限
SHOW GRANTS FOR tangmingyong;
SHOW GRANTS FOR root@localhost; -- 查看管理员权限

-- 撤销权限 revoke 哪些权限，在那个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM tangmingyong;

-- 删除用户
DROP USER tangmingyong;

 MySQL备份:

• 保证数据的不丢失
• 数据转移 

MySQl数据备份方式: 

• 直接拷贝物理文件
• 在可视化工具中手动导出
• 使用命令行导出 mysqldump 命令行使用 

使用命令行mysqldump导出:

-- mysqldump -h 主机 -u 用户名 -p 密码  数据库  表名 > 导出的磁盘地址
mysqldump -h localhost -u root -p98526 test USER >D:/a.sql

-- 导入
-- 登录的情况下切换指定数据库 格式:source 磁盘文件地址
source D:/a.sql

规范数据库设计:

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

糟糕的数据库设计:

• 数据冗余,浪费空间
• 数据插入和删除都会很麻烦/异常[避免使用物理外键]
• 程序的性能差

良好的数据库设计:

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

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

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

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

收集信息分析需求:

• 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
• 分类表(文章分类,谁创建的)
• 文章表(文章的信息)
• 友链表(友链信息)
• 评论表()
• 自定义表(系统信息) 

标识实体(把需求落地到每个字段) 

标识实体之间的关系:

• 写博客:user-->blog
• 创建分类:user-->category
• 关注:user-->user
• 友联:links
• 评论:user-user-blog

三大范式: 

为什么需要数据规范化?

• 信息重复
• 更新异常
• 插入异常(无法正常显示信息)

• 删除异常(丢失有效信息)

三大范式:

第一范式(1NF):

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

第二范式(2NF):

前提:满足第一范式

每张表只描述一件事情

第三范式(3NF):

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

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

规范性和性能问题 

阿里规范:关联查询表不得超过三张

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

数据库驱动和JDBC(重点) 

数据库驱动:

驱动:声卡,显卡,数据库.

我们会通过数据库驱动和数据库进行交互

JDBC

Sun公司为了简化开发人员(对数据库)的操作,提供了一个(java操作数据库的)规范,俗称JDBC这些规范的具体实现由厂商去做.

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

需要用到JDK自带的类:java.sql，javax.sql

需要导入的包:mysql-connector-java-5.1.47.jar

 第一个JDBC程序:

创建测试数据库

-- 创建数据库
-- 创建数据库
CREATE DATABASE IF NOT EXISTS `jdbcStudy`;

-- 创建数据库中的表
CREATE TABLE IF NOT EXISTS `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,'张三','123456','zs@123456.qq,com','2021-8-7'),
(2,'李四','123456','ls@123456.qq,com','1965-8-7'),
(3,'王五','123456','ww@123456.qq,com','2024-8-7');

1.创建一个项目

2.导入一个数据库驱动(导入jar包)

3.编写java链接测试代码

package com.text;

import java.sql.*;

/**
 * 我的第一个JDBC程序
 */
public class test67 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        /*
         *  characterEncoding=utf8&  -- 设置字符集为utf8
         * serverTimezone=UTC   -- 设置时区(存在时区问题)
         * 尽量使用 serverTimezone=Asia/Shanghai(时区)
         */
        //2.用户信息和URL
        String url="jdbc:mysql://localhost:3306/jdbcstudy?characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user="root";
        String password="98526";

        //3.链接成功，数据库对象  Connection代表数据库
        Connection connection = DriverManager.getConnection(url, user, 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()){ //ResultSet是采用链表形式的所以使用next()方法判断下一个还有没有数据
            System.out.println("编号-->"+resultSet.getInt("id")+"||密码-->"+resultSet.getObject("password")+"||电子邮件-->"+resultSet.getObject("email")+"||生日-->"+resultSet.getObject("birthday"));
        }

        //6.释放链接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结:

• 1.加载驱动（com.mysql.jdbc.Driver）
• 2.链接数据库 获取数据库对象 (Connection)
• 3.获得执行sql对象 (Statement)
• 4.获取返回的结果集 (ResultSet)
• 5.释放链接

DriverManager

//加载驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());-- 最开始使用这种方式加载驱动不推荐因为会加载两次
Class.forName("com.mysql.jdbc.Driver"); //推荐使用这种方式加载驱动

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

//Connection 表示数据库
connection.setAutoCommit(true); //设置自动提交
connection.rollback(); //设置事务回滚
connection.commit();   //事务提交

URL

/*
 * characterEncoding=utf8&  -- 设置字符集为utf8
 * serverTimezone=UTC   -- 设置时区(存在时区问题)
 * 尽量使用 serverTimezone=Asia/Shanghai(时区)
 */
String url="jdbc:mysql://localhost:3306/jdbcstudy?characterEncoding=utf8&serverTimezone=Asia/Shanghai";

 URL格式为: jdbc:mysql://ip地址:端口号/数据库名?字符编码&时区;

Statement执行SQL对象：

//执行SQL的对象  Statement 代表SQL的对象
Statement statement = connection.createStatement();

statement.execute(""); //表示执行所有的sql语句
statement.executeQuery("") //表示执行查询的sql语句
statement.executeUpdate(""); //跟新,插入,删除都是用这个，返回一个受印象的行数
statement.executeBatch(); //批量执行sql

 ResultSet结果集:

 ResultSet封装的所有的查询结果

 获得指定数据类型：

ResultSet resultSet = statement.executeQuery(sql);//返回结果集
//不知道列类型情况下使用
resultSet.getObject("");
//指定列类型情况下使用
resultSet.getInt("");
resultSet.getByte("");
resultSet.getShort("");
resultSet.getLong("");
resultSet.getString("");
resultSet.getDate("");
resultSet.getDouble("");
...

遍历指针:

ResultSet resultSet = statement.executeQuery(sql);//返回结果集
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast();  //移动到最后面
resultSet.next();       //移动到下一个
resultSet.previous();   //移动到前一行
resultSet.absolute();   //移动到指定行

 释放资源

 用完必须关闭资源,不然会极大的消耗性能 

resultSet.close();
statement.close();
connection.close();