mysql

mysql

1、数据库的初识

1、什么是数据库

• 数据库（DB DataBase）

• 按照数据结构来组织、存储和管理数据的仓库

• 是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

2、数据库的分类

关系型数据库(SQL)：

• 存储方式

  • 关系模型可以简单理解为二维表格模型，
  • 一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织

• 常见的关系型数据库

  • mysql
  • Oracle
  • SqlServer
  • DB2
  • SQLlite

非关系新数据库(NoSQL) NoSQL=Not Only SQL

• 存储方式：对象存储

  • 键值存储—>原理：哈希表
  • 列存储
  • 文档存储：

• 常见的非关系型数据库

  • 键值存储：Tokyo Cabinet/Tyrant， Redis， Voldemort， Oracle BDB
  • 列存储：Cassandra， HBase， Riak.
  • 文档存储：Json,CouchDB， MongoDb,SequoiaDB

3、DBMS(数据库管理系统)

• 数据库管理软件(mysql、oracle)
• 操纵和管理数据库

4、mySql

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

• 前世：瑞典MySQL AB公司

• 今生：属于Oracle公司

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

• 免费

• 体积小，速度快，成本低

• 使用于：中小型网站，或者大型网站（通过集群实现）

5、安装mysql

1、解压

2、环境变量：选择path,在其后面添加你的mysql安装文件下面的bin文件夹

3、在D:\mysql-8.0.22-winx64加压目录下新建my.ini

[mysql]

# 设置mysql客户端默认字符集

default-character-set=utf8

[mysqld]

#设置3306端口

port = 3306

# 设置mysql的安装目录

basedir="D:\mysql-8.0.22-winx64"   这里地址记得加引号，不然不能正常启动。

# 设置mysql数据库的数据的存放目录

datadir="D:\mysql-8.0.22-winx64\data"

# 允许最大连接数

max_connections=200

# 服务端使用的字符集默认为8比特编码的latin1字符集

character-set-server=utf8

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

5、启动管理员模式下的cmd,进入mysql安装目录下的bin目录，然后输入mysqlId-install(安装mysql)

6、输入mysql初始化命令：mysqld --initialize-insecure --user=mysql

7、启动mysql服务： net start mysql

8、启动mysql命令：mysql -u root -p进入mysql管理页面（第一次密码为空）

9、修改密码

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

10、退出mysql ：exit;

11、结束mysql服务：net stop mysql

建议：

使用安装包安装，这样安装解压都简单

不要使用看客户端安装，安装卸载都很麻烦

二、数据库常用命令

1、命令

mysql>show databases; --查看所有的数据库

mysql> use db; --使用db数据库

mysql> describe user; --查看所有user表的所有的信息

mysql> exit; --退出mysql连接
Bye

– (SQL单行注释)

/*

（SQL）多行注释

*/

2、数据库语言

DDL:（data definition language）数据定义语言

DML:（data Manipulation language）数据操纵语言

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

DCL:（data control language）数据控制语言

三、操作数据库

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

注意：

• mysql关键字，不区分大小写

1、操作数据库(重点)

1、创建数据库

CREATE DATABASE IF NOT EXISTS test01;--如果不存在test01数据库就创建

2、删除数据库

DROP DATABASE IF  EXISTS tests01; --如果存在test01数据库就删除

3、使用数据库

--如果你的表明或者字段名是一个特殊字符，就需要``符号
use `test01`;

4、查看所有的数据库

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

2、数据库的列类型

数值

• tinyint -----------十分小的数据 ---------1个字节
• smallint ---------较小的数据-------------2个字节
• mediunint------中等大小的字节-------3个字节
• int---------------- -标准的整数------------4个字节 常用int
• bigint--------------较大的数据------------8个字节
• float----------------浮点数------------------4个字节
• double------------浮点数-------------------8个字节
• decimal------------字符串形式的浮点数，金融计算的时候，一般使用

字符串

• char----------------字符串固定大小-----------0~255

• varchar------------可变字符串----------------0~65535 常用变量 String

• tingtext------------微型文本-------------------2^8-1

• text------------------文本串----------------------2^16-1 保存大文本

时间日期

java.util.Date

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

null

• 没有值，未知
• 注意，不要使用NULL进行运算 ，结果为NULL没有什么意义

3、数据库表的字段属性

Unsigned:

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

zerofill:

• 0填充的
• 不足的位数，使用0来填充，int (3) ,5, 005

自增：

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

Null：

• 如果不填值，就是默认为null

not null：

• 如果不给它赋值，就会报错

default：

• 如果不指定该列的值，设置默认的值

扩展

/*
每一个表，都必须存在以下五个字段！未来做项目用的，表示一个记录存在意义

id 主键
`version`  乐观锁
is_deletd  伪删除
gmt_create  创建时间
gmt_update  修改时间
*/

4、create表(重点)

1、案例

USE `test01`;

/*
注意点，表的名称和字段尽量使用``括起来
auto_increment   自增

*/
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4)  NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATE DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱 ',
PRIMARY KEY(`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

DROP TABLE `test01`.`student`; 

2、创建表的格式

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

3、查看表的一些语句

SHOW CREATE DATABASE test   ----查看test数据库的创建语句
SHOW CREATE TABLE student  ----查看student表的创建语句
DESC student  ------查看student的详细结构

5、数据库引擎

1、

SHOW ENGINES  ---查看数据库的引擎语句
/*
关于数据库的引擎
1、INNODB【重点：推荐】：支持事务，多表多用户，支持行锁定，默认
2、MYISAM【重点】：节约空间，速度较快，安全性高，支持表锁定
3、MEMORY存储引擎
*/

2、四种引擎的区别

3、在物理空间上的存储位置

• 所有的数据库文件都在D:\mysql-8.0.22-winx64\data，这里的一个文件夹对应一个数据库，
• 本质还是文件的存储

4、mysql引擎在物理文件上的区别

• InnDB:载数据库表中只有一个*.frm文件，

• 以及上级目录下的ibadta1文件
  
  

• MYISAM 对应文件有

  • *.sdi：表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件 （index）
    

6、设置数据库的字符集编码

mysql的默认编码是Latin1，不支持中文

1、直接在创建表的时候：加入

 CHARSET=utf8

2、在my.ini文件的mysql核心配置文件中设置下面代码【不建议使用】，这样容错率低，需要在不同的电脑中设置这个属性

character-set-server=utf8

7、修改删除表

1、修改表

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

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

-- 修改表的字段 （重命名，修改约束！）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE student1 MODIFY age VARCHAR(12) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧的字段名 性的字段名 列属性[]
ALTER TABLE student1 CHANGE age age1 INT(12)    -- 字段重命名

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

2、删除表

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

3、总结

-- 注意
-- 所有的创建和删除操作尽量加上判断，以免报错
-- sql关键字大小写不敏感，建议大家写小写

四、外键（了解）

方式一：

1、在学生表中 ，用学生表的 gradeid 字段引用 年级表的gradeid字段，达到学生表与年级表的互联

 `gradeid` INT(10) NOT NULL COMMENT '学生的年级'

2、定义外键key

KEY FK_gradeid(`gradeid`)

3、给这个外键添加约束（执行引用） references

 CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)

案例：年级表引用学生表

-- 定义一个年级表
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(30) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

DROP TABLE IF EXISTS student1;

-- 定义一个学生表


CREATE TABLE IF NOT EXISTS `student`(

`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`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` DATE DEFAULT 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`) -- 给这个外键添加约束(执行引用) references

)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

方式二

• 创建表的时候没有外键关系，添加表成功后，增加外键约束

1、公式

ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表 （里面的需要关联的字段）

-  ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

2、案例：年级表引用学生表

-- 定义一个年级表

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

-- 定义一个学生表
CREATE TABLE IF NOT EXISTS `student`(

`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`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` DATE DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱 ',
PRIMARY KEY(`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

-- 创建表的时候没有外键关系，在外面添加外键
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 foreign key(作为外键的列) references 那个表（里面的需要关联的字段）
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

注意：

1、删除带有外键的表时，需要首先删除引用的表，然后再删除自己的表，不然删不掉

2、以上的外键都时物理外键，不建议使用

3、数据库就是单纯的表，只用来存数据，只有行和列

4、我们想使用多张表的数据，想使用外键（采用程序实现比较好，阿里手册有写到）

五、DML语言（重要）

1、insert

1、语法

语法：insert into 表 (字段1，字段2…) values (‘值1’,‘值2’,…)

/*
插入数据
语法：insert into 表 (字段1，字段2....) values ('值1','值2',..)
*/
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES (1,'xoxo'); 
INSERT INTO `grade` (`gradename`)           VALUES ('xixi');
INSERT INTO `grade`                         VALUES (6,'opop'),(7,'momo');

2、总结：

1、字段时可以省略的，但是后面的值必须要一一对应
2、如果没有写字段，后面的值就都要填入(适用于插入全部字段)
3、可以同时插入多个数据, values后面的值，需要使用逗号隔开如：values (),()…

2、update

1、语法：

update 表名 set [字段名1=value]，[字段名2=value]，… where [条件]

UPDATE student SET `name`='nono' WHERE id=1;
-- and
UPDATE student SET gradeid=1 WHERE id=2 AND sex='男';
-- or
UPDATE student SET birthday=CURRENT_TIME WHERE pwd='222222' OR id=3;
-- BETWEEN 2 AND 3  ==   [2,3]
UPDATE student SET `name`='xoxo' WHERE id BETWEEN 2 AND 3;
-- 改变全部的sex字段的值，不设置条件【不推荐使用】
UPDATE student SET sex='女';
-- >=
UPDATE student SET sex='男' WHERE id>=2;
-- <>
UPDATE student SET sex='女' WHERE id<>1 AND `name`!= 'xoxo';

2、条件

操作符	含义	范围
=	等于
<>或者！=	不等于
>
<
>=
<=
between 2 and 3	在2到3之间	[2,3]
and	&&
or	||

3、总结：

1、条件没有指定，则是修改该字段中所有的值

2、set 后面的value，既可以时一个值，也可以是一个变量

UPDATE student SET birthday=CURRENT_TIME WHERE pwd='222222' OR id=3;

3、删除

1、语法

delete    from      表名     [where 条件]	 -- 清空一张表，不会影响索引
drop      table     表名                    -- 删除表结构，和表数据
truncate  表名                              -- 清空一张表，索引归零

-- 删除表结构，和表数据
DROP TABLE student01;

INSERT INTO student01(`name`) VALUES('rr'),('tt'),('nn'),('mm');

-- 删除表数据，不删除表结构
DELETE FROM student01 WHERE id=1;

-- 不会影响自动增量的值
DELETE FROM student01;

-- 自增量归零
TRUNCATE TABLE `student01`;

2、总结

​ 1、在速度上，一般来说，drop> truncate > delete。

​ 2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

​ 3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

 如果想删除表，当然用drop； 

 如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

 如果和事务有关，或者想触发trigger，还是用delete；

 如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

3、扩展

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

• InnDB 自增列会从1开始（数据储存在内存中，断电即失）
• MyISAM 继续从上一个增量开始（存在文件中，不会丢失）

六、DQL查询数据(最重点)

1、select完整语法

2、DQL(SELECT查询)

1、查询所有属性

-- 1、查询所有属性
SELECT * FROM student;

2、查询指定属性

-- 2、查询指定属性
SELECT `studentname`,`address` FROM student;

3、起别名（AS）

-- 3、起别名 字段名 as 别名 或  表名 as 别名
SELECT `studentname` AS 学生姓名,`address` AS 地址 FROM student AS s;

4、使用函数

-- 4、函数 Concat(a,b)
SELECT CONCAT('姓名：',studentname) AS 学生姓名 FROM student;

5、去重(distinct)

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

-- 查询有哪些同学参加了考试
SELECT `studentno` FROM result 

-- 去重,发现重复的学号去重
SELECT DISTINCT `studentno` FROM result; 

6、数据库列的表达式

-- 查询数据库的版本
SELECT VERSION()
 
-- 用来进行计算
SELECT 100*3-4 AS 计算结果

-- 查询增的步长
SELECT @@auto_increment_increment 

-- 所有学生的成绩+1
SELECT DISTINCT `studentno`,`studentresult` AS 提分后 FROM result

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

select 表达式 from 表

2、where条件字句

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

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

1、wehere运算符

运算符	说明
=	等于
!=	不等于，某些数据库系统也写作 <>
>	大于
<	小于
>=	大于或等于
<=	小于或等于
BETWEEN … AND …	介于某个范围之内，例：WHERE age BETWEEN 20 AND 30
NOT BETWEEN …AND …	不在某个范围之内
a BETWEEN b AND c	如果a在b和c之间，则结果为真
IN(项1,项2,…)	在指定项内，例：WHERE city IN(‘beijing’,‘shanghai’)
NOT IN(项1,项2,…)	不在指定项内
LIKE	搜索匹配，常与模式匹配符配合使用
NOT LIKE	LIKE的反义
IS NULL	空值判断符
IS NOT NULL	非空判断符
NOT、AND、OR	逻辑运算符，分别表示否、并且、或，用于多个逻辑连接。 优先级：NOT > AND > OR
%	模式匹配符，表示任意字串，例：WHERE username LIKE '%user’

2、逻辑运算符

where：

-- 查询所有的学生，学生分数
SELECT `studentno`,`studentresult` FROM result;

-- 查询考试成绩在70~90分之间
-- BETWEEN..AND..
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult BETWEEN 70 AND 90;
-- AND
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult>=70 AND studentresult<=90;
-- &&
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult>=70 && studentresult<=90;

-- 查询出来1000号学生成绩以外的学生成绩
-- !=或者<>
SELECT studentno,studentresult FROM result
WHERE studentno!=1000;
-- not
SELECT studentno,studentresult FROM result
WHERE NOT studentno=1000;

3、模糊查询

1、各种模糊查询

-- 模糊查询
==============================LIKE===============================

-- 查询所有姓刘的同学 
-- %代表任意字符（0~+oo）
SELECT studentname FROM`student`
WHERE studentname LIKE '赵%';

-- 查询所有姓赵的两个字的同学
-- _代表一个字符
SELECT studentname FROM student
WHERE studentname LIKE '赵_';

-- 查询所有名字中带有一字儿的同学
SELECT `studentname` FROM student
WHERE studentname LIKE '%一%';

-- 查询所有以大写字母T开头的学生姓名
-- BINARY关键字区分大小写
SELECT studentname FROM student
WHERE studentname LIKE BINARY 'T%';

-- 查询以%结尾的学生的姓名
-- 如果查询内容中包含通配符，可以使用“\”转义符
SELECT studentname FROM student
WHERE studentname LIKE '%\%';
=================================IN(具体的一个或者多个值)===================

-- 查询所有1001，1002，1003号学生的名字
SELECT studentname,studentno FROM student
WHERE studentno IN(1001,1002,1003);

-- 查询所有在陕西西安和陕西宝鸡的同学
SELECT studentno,address FROM student
WHERE address IN('陕西西安','陕西宝鸡');

=====================================IS NULL=============================
-- 查询密码为空的学生姓名
SELECT studentname,loginpwd FROM student
WHERE loginpwd IS NULL OR loginpwd ='';

====================================IS NOT NULL==========================
-- 查询密码不为空的学生姓名
SELECT studentname,loginpwd FROM student
WHERE loginpwd IS NOT NULL OR loginpwd !=''; `student`

2、注意点

1、%代表任意字符（0~+oo）

2、_代表一个字符

3、BINARY关键字区分大小写

4、如果查询内容中包含通配符，可以使用“\”转义符

5、IN(具体的一个或者多个值)

4、连表查询

1、inner(内连接，等值连接)

• 含义：取得两个表中存在连接匹配关系的记录。

• 求两个表的交集

语法：

select 字段名1，字段名2...  -- 这些字段中必须有一个是A,B表中都有的字段
from 表A 
inner join 表B
where/on  条件            -- 条件：就是A,B表中相同的属性相等

2、left join(左连接)

• 就是求两个表的交集外+左表剩下的数据
• 含义:左连接从左表(s)产生一套完整的记录,与匹配的记录(右表®) .如果没有匹配,右侧将包含null。
• 以左表的数据为准

语法：

select 字段1，字段2，...       -- 这些字段中必须有一个是A，B表中都有的字段
from 表A
left join 表B
on 判断条件						-- 条件：就是A,B表中相同的属性相等
WHERE 条件

3、right join

• 同理左连接RIGHT JOIN就是求两个表的交集外+右表剩下的数据
• 含义：右连接从右表®产生一套完整的记录,与匹配的记录(左表(s)) .如果没有匹配,右侧将包含null。
• 以右表的数据为准

语法：

select 字段1，字段2，...       -- 这些字段中必须有一个是A，B表中都有的字段
from 表A
right join 表B
on 判断条件						-- 条件：就是A,B表中相同的属性相等
WHERE 条件

4、解题思路，练习

/*
查询参加了考试的同学（学号，姓名，科目编号，分数）
联表查询
1、分析需求，分析查询的字段来自那些表，（连接查询）
2、确定使用那种连接查询？ 7种
3、确定交叉点（这两个表中那个数据式相同的）
判断的条件：学生表中的studentno=成绩表中的studentno
*/
==============================INNER JOIN==============================
-- INNER JOIN（内连接,或等值连接）
-- 含义：取得两个表中存在连接匹配关系的记录。
-- 求两个表的交集
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
INNER JOIN result r
WHERE s.studentno=r.`studentno`;
==============================RIGHT JOIN==============================
-- right join
-- 同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。
-- 含义：右连接从右表(r)产生一套完整的记录,与匹配的记录(左表(s)) .如果没有匹配,右侧将包含null。
-- 以右表的数据为准

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

==============================LEFT JOIN==============================

-- LEFT JOIN（左连接）：就是求两个表的交集外加左表剩下的数据
-- 含义:左连接从左表(s)产生一套完整的记录,与匹配的记录(右表(r)) .如果没有匹配,右侧将包含null。
-- 以左表的数据为准
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno=r.`studentno`;

=======================右连接应用=======================================
-- 查询缺考的同学
 SELECT s.studentno,studentname,studentresult,subjectno
 FROM student s
 LEFT JOIN result r
 ON s.`studentno` = r.`studentno`
 WHERE studentresult IS NULL;
 ========================三表查询应用+解题思路=============================
/*
-- 思考三表查询
-- 问题：查询参加考试的同学的信息：学号，学生姓名，科目名，分数
-- 分析
	1、根据属性，确定表，student,result,subject
	2、确定连接：要考试的学生，所以student,result表进行右查询
		     
	3、确定交叉点：studentno,sbujectno
	4、确定判断条件studentno相等，subject相等
*/
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`= sub.subjectno

==========================查询学生所属的年级===============================
-- 查询学生所属的年级（学号，学生姓名,年级名称）
/*
1、确定表
2、确定连接
3、确定判断条件
*/
SELECT studentno,studentname,gradename
FROM student s
INNER JOIN grade g
ON s.`gradeid`= g.`gradeid`;
==========================查询科目所属的年级===============================
-- 查询科目所属的年级（科目名称，年级名称）

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

注意：

• join （连接的表）on(判断条件) ：连接查询
• where ：等值查询

5、自连接（了解）

• 自己和自己连接
• 核心：一张表拆分为两张的表

总表

父类表：找到pid=1,就是父类表的所有属性

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

子类表：除过父类属性，剩余的就是子类表的属性

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

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

• 通过父类表的子id==子类表的父id,找到表

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web技术
美术设计	ps技术

6、自连接练习

/*
--============================自连接====================================
问题：查询每个子技术对应的父类的技术
分析：1、确定父技术名，子技术名 
      2、确定父表，子表
      3、确定条件
关键：就是需要将这一张表看成两张表来用
*/

SELECT c1.categoryname AS 父技术, c2.categoryname AS 子技术
FROM category c1,category c2
WHERE c1.`categoryid` = c2.`pid`;

5、分页和排序

1、排序

• 升序 ASC（默认），降序 DESC

2、排序语法

select 字段1，字段2，...
from 表
where 条件
oorder by 字段1 [ASC|DESC],字段2 [ASC|DESC]...             -- 多个字段需要排序时，采用逗号分开

3、排序练习

================分页、排序==========================


--------------------------单条件排序------------------------------------------
-- 查询参加考试的学生信息，学号，姓名，成绩，科目名字
-- 按照科目降序

SELECT s.studentno,studentname,studentresult,`subjectname`
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno` 
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
ORDER BY studentresult DESC
---------------------------多条件排序--------------------------------------------
-- 查询参加考试的学生信息，学号，学生姓名，成绩，科目名字
-- 按照科目降序，学号降序的方式展示

SELECT s.studentno,studentname,studentresult,`subjectname`
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE 1=1
ORDER BY studentresult DESC,studentno DESC

4、分页

/*
分页   页面起始下标，pageSize   
第一页：limit 0    ,5       (1-1)*5
第二页：limit 1    ,5       (2-1)*5
第三页：limit 2    ,5       (3-1)*5 
第四页：limit 3    ,5       (4-1)*5
第n页 ：limit (n-1),5       (5-1)*5
语法
【页面大小：pageSize】
【每一页的起始值：(n-1)*pageSize】
【当前页：n】
【总页数：数据总数/pageSize】
*/

5、分页语法

select 字段1，字段2，...
from 表
where 条件
limit 起始下表，一页的数据大小

6、分页练习

================================================分页1================================================


-- 查询参加考试学生信息：学号，学生姓名，成绩
-- 按照，成绩前五个
SELECT s.studentno,studentname,studentresult
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
ORDER BY studentresult DESC
LIMIT 0,5  -- 下标从0开始一页显示5个数据
----------------------------------------------分页2---------------------------------------

-- 查询参加考试的学生信息，学号，学生姓名，学生成绩，年级名字，科目名字
-- 一页存5条数据，时间在大二，成绩排名前5，学生成绩大于80

SELECT s.studentno,studentname,studentresult,gradename,subjectname
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON s.`gradeid`=sub.`gradeid`
INNER JOIN grade g
ON s.`gradeid`=g.gradeid
WHERE studentresult>80 AND gradename='大二'
ORDER BY studentresult DESC
LIMIT 0,5 -- 下标从0开始一页显示5个数据

7、子查询和嵌套查询

定义：

• 子查询允许把一个查询嵌套在另一个查询当中。

• 子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。

• 子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、limit、join和union等；

本质：

• 在where语句中嵌套一个子查询语句

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

-- 1、查询高等数学-1的所有考试结果（学号，科目编号，成绩）降序排列

-------------------------------------- 方式一：联表查询--------------------------------------------------
SELECT s.studentno,r.subjectno,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC

---------------------------------------- 方式二：使用子查询--------------------------------------------------
SELECT studentno,subjectno,studentresult
FROM result 
WHERE subjectno=(SELECT subjectno FROM `subject` WHERE subjectname='高等数学-1' )

ORDER BY studentresult DESC


-- 分数不小于80分的学生学号，姓名
-- 查询的课程为高等数学-1
-- -------------------------------------方式一：联表查询-----------------------------------------------------
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`= sub.`subjectno`
WHERE studentresult>=80 AND subjectname='高等数学-1'


-- ---------------------------------------方式二：联表结合子查询-----------------------------------------
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
WHERE studentresult>=80 
AND `subjectno`=(SELECT `subjectno` FROM `subject` WHERE subjectname='高等数学-1')

-- ------------------------------------------方式三：嵌套子查询--------------------------------------------
SELECT studentno,studentname
FROM student
WHERE studentno 
IN (SELECT studentno FROM result WHERE studentresult>=80
AND subjectno=(SELECT `subjectno` FROM `subject` WHERE subjectname='高等数学-1')
 )

8、分组

语法：

select 字段1，字段2，...
from 表1
group  by  需要分组的字段
having 需要过滤的条件==相当于where,但是这里不能用where

测试：

================分组========================
-- 按照学号分组，查询课程的名字，平均分，最高分，最低分
SELECT subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM `subject` sub 
INNER JOIN  result r
ON sub.`subjectno`=r.`subjectno`
GROUP BY sub.subjectname -- 通过什么字段来分组
HAVING  AVG(studentresult>80)-- having加过滤的条件

七、mysql函数

MySQL 数值型函数

函数名称	作 用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值

MySQL 字符串函数

函数名称	作 用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

MySQL 日期和时间函数

函数名称	作 用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

MySQL 聚合函数

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

================================聚合函数=================================
COUNT -- 本质：都是为了计算行数
-- 都没统计表中的数据（想查询一个表中有多少个记录，就用count（））
SELECT COUNT(loginpwd) FROM student -- count(字段)，会忽略所有的null,空
SELECT COUNT(*) FROM student -- 不会忽略所有的null
SELECT COUNT(1) FROM student -- 不会忽略所有的null

SELECT SUM(studentresult) AS 总分 FROM result

SELECT AVG(studentresult) AS 总分 FROM result
SELECT MAX(studentresult) AS 总分 FROM result
SELECT MIN(studentresult) AS 总分 FROM result

MySQL 流程控制函数

函数名称	作用
IF	判断，流程控制
IFNULL	判断是否为空
CASE	搜索语句

八、MD5加密

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

-- 明文密码
INSERT INTO testmd5 VALUES
(1,'张三','12345'),
(2,'王五','23456'),
(3,'李六','34567')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1

INSERT INTO testmd5 VALUES(4,'asdf','123456789')
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=4
-- 校验：将用户传来的密码进行MD5加密
SELECT * FROM testmd5
WHERE `name`='asdf' AND pwd=MD5('123456789')

九、事务(Transaction)

1、定义：

• 事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
• 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
• 事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

2、事务四大特征(ACID)

• 原子性(Atomicity)：事务是最小单位，不可再分，要么都成功，要么都失败
• 一致性（Consistency）：事务前后，数据的完整性一致
• 隔离性(isolation)：事务A和事务B之间具有隔离性
• 持久性(Consistency)：事务一旦提交则不可逆，被持久化到数据库中(内存的数据持久到硬盘文件中)

3、事务隔离导致的一些问题

1.脏读

• 一个事务读取了另一个事务未提交的数据
• 而这个数据是有可能会回滚的

2.不可重复读

• 一个事务内两个相同的查询却返回了不同数据
• 这是由于查询时系统中同时有其他事务修改的提交而引起的

3. 幻读

• 一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行
• 而同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据
• 那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样

4.丢失更新

• 两个事务同时读取同一条记录，A 先修改记录，B 也修改记录（B不知道A修改过）
• B 提交数据后，B 的修改结果覆盖了 A 的修改结果

4、转账案例（sql）

---------------- 模拟转账-----------------------------

-- 关闭自动提交
SET autocommit = 0;
-- 开启一个事务
START TRANSACTION

UPDATE acount SET money=money-500 WHERE id=1;
UPDATE acount SET money=money+500 WHERE id=2; 
 
 -- 提交事务，一旦提交，数据就会被持久化
 COMMIT;
 -- 回滚
 ROLLBACK;
 
 
 -- 业务完成后,开启自动提交事务
 SET autocommit =1

5、代码实现(java)

• 关闭数据库的自动提交,开启事务 conn.setAutoCommit(false);
• 一组业务执行完毕，提交事务
• 在catch中显示的定义回滚语句，但默认失败就会回滚

public class TestTransaction {
    static Connection conn=null;
    static PreparedStatement pps=null;
    static ResultSet rs=null;
    @Test
    public void test() throws SQLException {
        try {
            //获取数据库的链接
            Connection conn = JdbcUtils.getConnection();
            //关闭数据库的自动提交，就会默认通知数据库开启事务，false:开启
            /*
            * 开启事务之后，他们就时一起成功一起失败
            * */
            conn.setAutoCommit(false);
            //3、编写sql1
            String sql1 = "update account set money=money-100 where id=1";
            //4、预编译sql，执行更新
            conn.prepareStatement(sql1).executeUpdate();
            /*
            * 由于这里出现了错误，所以id=1的减了100，但是id=2的100却没有加，这就需要提交事务，让他们一起成功，一起失败
            * */
            int i=1/0;
            //5、编写sql2
            String sql2 = "update account set money=money+100 where id=2";
            conn.prepareStatement(sql2).executeUpdate();
            conn.commit();//程序没有问题，就提交事务

        } catch (Exception e) {
            //Can't call rollback when autocommit=true
            //只要上面的程序出现错误，就通知数据库，回滚事务
            //这个回滚事务，一定要和开始事务一起使用，才可以回滚成功，不进行修改数据
            //事务的回滚是默认的
            conn.rollback();
            e.printStackTrace();
        }finally {
            JdbcUtils.closesp(conn,pps,rs);
        }
    }
}

十、索引

1、定义

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

2、索引的分类

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

• 主键索引(PRIMARY KEY)

  • 是一种特殊的唯一索引，不允许有空值。(主键约束，就是一个主键索引)

  ALTER TABLE table_name ADD PRIMARY KEY ( column )
  

• 唯一索引（UNIQUE KEY）

  • 索引列中的值必须是唯一的，但是允许为空值。

  ALTER TABLE table_name ADD UNIQUE ( column )
  

• 普通索引（KEY/INDEX）默认

  • MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点

  ALTER TABLE table_name ADD INDEX index_name ( column )
  

• 全文索引（FULLText）

  • 在特定的数据库引擎下才有：MyISAM
  • 快速定位数据
  • 只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

  ALTER TABLE table_name ADD FULLTEXT ( column)
  

• 组合索引：

  • 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

  ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
  

3、基本语法

 -- 索引的使用
 -- 1、在创建表的时候给字段添加索引
 -- 2、创建表完毕后，增加索引
 
 -- 显示所有的索引信息
 SHOW INDEX FROM student
 
 -- 增加一个全文索引
 ALTER TABLE student ADD FULLTEXT INDEX studentname(studentname);
 
 -- EXPLAIN分析sql执行的情况
 EXPLAIN SELECT * FROM student; -- 非全文索引
 EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');
 
 -- 设置索引
 -- id_表名_需要设置的字段名
 -- create index 索引名 on 表（字段） 
CREATE INDEX id_app_user_name ON app_user(`name`); 

4、测试sql索引

----------------------------------------------创建表--------------------------------------------------------- 
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
`eamil` VARCHAR(50) DEFAULT  NULL,
`phone` VARCHAR(20) DEFAULT NULL,
`gender` TINYINT(4) UNSIGNED DEFAULT NULL,
`password` VARCHAR(100)  DEFAULT NULL,
`age` TINYINT(4) DEFAULT NULL,
`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
------------------------------------------------插入一百万条数据---------------------------------------------------
-- 插入100万数据.
SET GLOBAL log_bin_trust_function_creators=1; -- 开启创建函数功能
/*
  第一个语句 delimiter 将 mysql 解释器命令行的结束符由”;” 改成了”$$”，
  让存储过程内的命令遇到”;” 不执行
*/
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;$$
 ------------------------------------ 测试索引【在这里我们可以看到索引的优点】 ------------------------------------
SELECT mock_data() -- 执行此函数 生成一百万条数据 总耗时: 55.455 sec
 
 -- 没有设置索引
 SELECT * FROM app_user WHERE `name`='用户999999'  -- 总耗时: 0.373 sec

 -- 分析没有设置索引的sql
 -- 总耗时: 0.373 sec、查询了995314行数据
 EXPLAIN  SELECT * FROM app_user WHERE `name`='用户999999' -- 
 
 -- 设置索引后
 -- id_表名_需要设置的字段名
 -- create index 索引名 on 表（字段） 
CREATE INDEX id_app_user_name ON app_user(`name`); 
 
-- 总耗时: 0.002 sec、查询了一条数据 
EXPLAIN  SELECT * FROM app_user WHERE `name`='用户999999'  
 

设置索引前

设置索引后

5、索引的原则

1. 单表数据太少，索引反而会影响速度；更新非常频繁的数据不适宜建索引
2. where后的条件，order by ,group by 等这样过滤时，后面的字段最好加上索引。根据实际情况，选择PRIMARY KEY、UNIQUE、INDEX等索引，但是不是越多越好，要适度
3. 联合查询，子查询等多表操作时关连字段要加索引

ps：数据量特别大的时候，最好不要用联合查询，即使你做了索引，一次查询只能用到一个索引

6、索引的底层

十一、权限管理和备份

1、可视化

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

2、sql命令

-- 创建用户 create user 用户名 IDENTIFIED by '密码'
CREATE USER  sh IDENTIFIED BY '123456'

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

-- 修改密码(修改指定用户密码)
SET PASSWORD FOR sh=PASSWORD('12345') 
 
 -- 重命名 
 RENAME USER sh TO sh2
 
 -- 用户授权
 -- ALL PRIVILEGES 全部的权限,库.表
 -- 处理给别人授权，其他都能干
 GRANT ALL PRIVILEGES ON *.* TO sh2
 
 
 -- 查看权限
 SHOW GRANTS FOR sh2  -- 查看指定用户的权限
 SHOW GRANTS FOR root@localhost
 
 
-- root用户权限：

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

-- 删除用户
DROP USER sh
 

十二、数据库的备份

1、采用sqlyong可视化数据库，备份数据库的表

1、右键

2、sql转储

2、采用命令行备份表

• 导出一张表

# mysqldump -h主机名 -u用户名 -p密码 数据库名 表名 > 物理磁盘位置/文件名 
mysqldump -hlocalhost -uroot -p123456 school student >C:/Users/航航/Desktop/student.sql

• 导出多张表

# mysqldump -h主机名 -u用户名 -p密码 数据库名 表名1 表名2 > 物理磁盘位置/文件名 
mysqldump -hlocalhost -uroot -p123456 school student subject >C:/Users/航航/Desktop/school.sql

• 导出数据库

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

3、采用命令行导入表

首先需要登录数据库，不然也不知道给哪里导

mysql -u root -p123456

使用一个数据库

use school

• 导入一张表

#登录的情况下，切换到指定数据库
# source 备份文件
source C:/Users/航航/Desktop/student.sql

十三、规范化数据库的设计

一、数据库的设计

1、为什么需要设计

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

糟糕的数据库设计

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

良好的数据库设计

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

2、数据库的设计步骤

• 分析需求：
  • 分析业务和处理 的数据库的需求
• 逻辑设计：
  • 设计E-R图
• 物理设计：
  • 根据数据库自身的特点 将逻辑设计转化为物理设计
• 维护和优化
  • 新的需求进行建表
  • 索引优化
  • 大表拆分

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

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客）
  • 分类表（文章的分类，创建者，…）
  • 文章表（文章的信息…）
  • 友链表（友链表中的属性）
• 标记实体（把实体落到书库的每个字段）
• 标记实体之间的关系
  • 写博客：user—>blog
  • 创建分类：user—>category
  • 关注：user—>user
  • 友链：links
  • 评论：user—>---->blog

二、三大范式

为什么要使用数据的规范化？

• 信息重复

• 更新异常

• 插入异常

  • 无法正常显示信息

• 删除异常

  • 丢失有效信息

  1、三大范式

  

  1、第一范式（1NF）

  • 每一列（字段）都是不可分割的原子数据项（保证每一列不可分割）

  2、第二范式

  • 规范化：

    • 在1NF基础上，非码属性必须完全依赖于候选码（主键）（在1NF基础上消除非主属性对主码的部分函数依赖）
    • 非主属性：非主码
    • 部分函数依赖： A–>B 如果A是一个属性组，则B属性值的确定只需要依赖A属性组的中某一些的属性值（第二范式就是消除这个）

  • 通俗化：

    • 前提：满足第一范式
    • 含义：每张表只描述一件事情（不可再分割）

  3、第三范式

  • 规范化：
    • 在2NF的基础上，任何的非主属性不依赖于其他非主属性 （在第二范式基础上消除传递依赖）
    • 传递函数依赖：A – >B , B – >C 如果通过A属性（属性组）的值，可以确定唯一的B属性的值，再通过B属性（属性组）的值，可以唯一确定C属性的值，那么称C传递依赖于A
  • 通俗化：
    • 前提：满足第一范式，第二范式
    • 含义：足数据表中的每一列都和主键直接相关，而不能间接相关

  4、鲍依斯-科得范式（BCNF是3NF的改进形式）

  • 规范化：
    • 在满足 3NF，任何非主属性不能对主键子集依赖（消除 3NF 主属性对码的部分和传递函数依赖）

  • 通俗化：

三、规范化和性能的问题

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

十四、数据库连接池

1、对比

• 原来：
  • 官方：数据库连接----------执行--------------释放（不停的来连接不停的释放，就会增大系统资源的开销，不好）
  • 通俗：例如银行的办理业务，对待一个业务，开门-------办理业务完毕----------关门（就相当于这种）
• 现在：
  • 官方：池化技术：准备一些预先的资源，有需求时，过来就连接预先准备好的资源
  • 通俗：对待业务，开门----业务员一直在------办理业务完毕------业务员还在-----关门
  • 官方：数据库连接池（Connection pooling）是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对池中的连接进行申请，使用，释放。
  • 个人理解：创建数据库连接是一个很耗时的操作，也容易对数据库造成安全隐患。所以，在程序初始化的时候，集中创建多个数据库连接，并把他们集中管理，供程序使用，可以保证较快的数据库读写速度，还更加安全可靠。

2、连接池的实现

1、本质：编写一个连接池，实现一个接口 DataSource

2、开源数据源实现

DBCP

• jar包

  • commons-dbcp

  <!--DBCP连接池-->
      <dependency>
          <groupId>org.apache.commons</groupId>
          <artifactId>commons-dbcp2</artifactId>
          <version>2.7.0</version>
      </dependency>
  

  • commons-pool

  <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-pool2 -->
      <dependency>
          <groupId>org.apache.commons</groupId>
          <artifactId>commons-pool2</artifactId>
          <version>2.7.0</version>
      </dependency>
  

• dbcpConfig.properties文件

#连接设置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC
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=gbk

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

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

• 代码实现

package com.sh.utils;

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

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/*
* DBCP数据库连接池工具类
* */
public class JdbcUtils_DBCP {
    private static DataSource dataSource=null;
    private static Properties properties =null;
    static {
        try {
            //记载dbcp配置文件文件
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpConfig.properties");
            properties = new Properties();
            //加载文件到流
            properties.load(in);
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws Exception {

        return dataSource.getConnection();
    }
    public static void close(Connection conn, Statement st, ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }if(st!=null){
                st.close();
            }if (conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

C3PO

• jar

  • c3p0-0.9.5

  <!--C3P0连接池-->
      <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
      <dependency>
          <groupId>com.mchange</groupId>
          <artifactId>c3p0</artifactId>
          <version>0.9.5.5</version>
      </dependency>
  

  • mchange-commons-java-0.2.19

   <!-- https://mvnrepository.com/artifact/com.mchange/mchange-commons-java -->
      <dependency>
          <groupId>com.mchange</groupId>
          <artifactId>mchange-commons-java</artifactId>
          <version>0.2.15</version>
      </dependency>
  

• c3p0-config.xml配置文件

<c3p0-config>
    <!--使用默认的配置读取数据库连接池对象 -->
    <default-config>
        <!--  连接参数 -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <!-- 连接池参数 -->
        <!--初始化申请的连接数量-->
        <property name="initialPoolSize">5</property>
        <!--最大的连接数量-->
        <property name="maxPoolSize">10</property>
        <!--超时时间-->
        <property name="checkoutTimeout">3000</property>
    </default-config>

        <named-config name="mysql">
            <!--  连接参数 -->
            <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
            <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC</property>
            <property name="user">root</property>
            <property name="password">123456</property>

            <!-- 连接池参数 -->
            <property name="initialPoolSize">5</property>
            <property name="maxPoolSize">8</property>
            <property name="checkoutTimeout">1000</property>
        </named-config>
</c3p0-config>

• 代码实现

package com.sh.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

/*
* C3P0数据库连接池工具类
* */
public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource dataSource=null;
    static {
            /*
            * 代码版配置
            * */
         /*
         dataSource = new ComboPooledDataSource();
         dataSource.setDriverClass();
         dataSource.setUser();
         dataSource.setPassword();
         dataSource.setJdbcUrl();

         dataSource.setMaxPoolSize();
         dataSource.setMinPoolSize();
         */

        /*
            * 使用C3P0的配置文件c3p0-config.xml不用加载配置文件
            *
            * */
            //创建数据源  工厂模式---->创建
        dataSource = new ComboPooledDataSource("mysql");
    }
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    public static void close(Connection conn, Statement st, ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }if(st!=null){
                st.close();
            }if (conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Druid：阿里巴巴

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

3、使用数据库连接池的优势和其工作原理

1、连接池的优势

连接池用于创建和管理数据库连接的缓冲池技术，缓冲池中的连接可以被任何需要他们的线程使用。当一个线程需要用JDBC对一个数据库操作时，将从池中请求一个连接。当这个连接使用完毕后，将返回到连接池中，等待为其他的线程服务。

连接池的主要优点有以下三个方面。

第一、减少连接创建时间。连接池中的连接是已准备好的、可重复使用的，获取后可以直接访问数据库，因此减少了连接创建的次数和时间。

第二、简化的编程模式。当使用连接池时，每一个单独的线程能够像创建一个自己的JDBC连接一样操作，允许用户直接使用JDBC编程技术。

第三、控制资源的使用。如果不使用连接池，每次访问数据库都需要创建一个连接，这样系统的稳定性受系统连接需求影响很大，很容易产生资源浪费和高负载异常。连接池能够使性能最大化，将资源利用控制在一定的水平之下。连接池能控制池中的连接数量，增强了系统在大量用户应用时的稳定性。

2、连接池的工作原理

下面，简单的阐述下连接池的工作原理。

连接池技术的核心思想是连接复用，通过建立一个数据库连接池以及一套连接使用、分配和管理策略，使得该连接池中的连接可以得到高效、安全的复用，避免了数据库连接频繁建立、关闭的开销。

连接池的工作原理主要由三部分组成，分别为连接池的建立、连接池中连接的使用管理、连接池的关闭。

第一、连接池的建立。一般在系统初始化时，连接池会根据系统配置建立，并在池中创建了几个连接对象，以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭，这样避免了连接随意建立和关闭造成的系统开销。Java中提供了很多容器类可以方便的构建连接池，例如Vector、Stack等。

第二、连接池的管理。连接池管理策略是连接池机制的核心，连接池内连接的分配和释放对系统的性能有很大的影响。其管理策略是：

• 当客户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户使用；如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，如果没达到就重新创建一个连接给请求的客户；如果达到就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给客户。

• 当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过就从连接池中删除该连接，否则保留为其他客户服务。

  该策略保证了数据库连接的有效复用，避免频繁的建立、释放连接所带来的系统资源开销。

第三、连接池的关闭。当应用程序退出时，关闭连接池中所有的连接，释放连接池相关的资源，该过程正好与创建相反。

		  Class();
         dataSource.setUser();
         dataSource.setPassword();
         dataSource.setJdbcUrl();

         
dataSource.setMaxPoolSize();
         dataSource.setMinPoolSize();
         */

        /*
            * 使用C3P0的配置文件c3p0-config.xml不用加载配置文件
            *
            * */
            //创建数据源  工厂模式---->创建
        dataSource = new ComboPooledDataSource("mysql");
    }
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    public static void close(Connection conn, Statement st, ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }if(st!=null){
                st.close();
            }if (conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}