1、初识MySqL

JavaEE ：企业级Java开发 Web

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

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

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

1.1、为什么学习数据库

1. 岗位需求
2. 现在的时间，大数据时代，得数据者得天下
3. 被迫 需求：存数据
4. 数据库是所以软件体系中最核心的存在DBA

1.2、什么是数据库

数据库(DB DataBase)

概念：数据仓库，软件，安装在操作系统上！SQL可以存储大量数据，500w。

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

1.3、数据库分类

关系型数据库 （SQL）

• MySQL，Oracle，Sql Sever，DB2，SQLlite
• 通过表跟表之间，行跟列之间的挂你先进行数据存储

非关系型数据库：（NoSQL） Not Only Sql

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

DBMS（数据库管理系统）

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

1.4、MySQL

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

之前属于瑞典MySQL AB公司后属于Oracle

MySQL是最好的关系型数据库管理软件之一

体积小速度快，

安装建议：

1. 尽量不要使用exe，注册表很难卸载清楚
2. 使用压缩包下载，

1.5、安装MySQL

1. 下载压缩包
2. 解压到目录
3. 再系统环境变量修改PATH添加文件夹中的bin路径
4. my.ini修改 ```properties [mysqld]

设置3306端口

port=3306

设置mysql的安装目录

basedir=C:\mysql-8.0.13

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

datadir=C:\mysql-8.0.13\Data

允许最大连接数

max_connections=200

允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统

max_connect_errors=10

服务端使用的字符集默认为UTF8

character-set-server=utf8

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

default-storage-engine=INNODB

默认使用“mysql_native_password”插件认证

default_authentication_plugin=mysql_native_password

[mysql]

设置mysql客户端默认字符集

default-character-set=utf8

[client]

设置mysql客户端连接服务端时默认使用的端口

port=3306

default-character-set=utf8

5.  再bin目录下打开管理员模式CMD运行<br />`mysqld --install`<br />安装MySQL服务 
5.  初始化MySQL（末尾会产生随机密码记住）<br />`mysql：mysqld --initialize --console` 
5.  开启服务<br />`net start mysql` 
5.  登录<br />`mysql -u root -p`<br />密码是刚才那个随机的 
5.  修改密码 改为root<br />`ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';` 
5.  关闭数据库<br />`net stop mysql` 
`sc delete mysql清除mysql`
如果过程中出问题可以使用这个清除服务
<a name="krZ1o"></a>
## 1.6、安装SQLyog
1. 连接MySQL
1. 新建数据库
1. 新建表
1. 查看表
1. 添加记录
<a name="R4FvD"></a>
## 1.7、连接数据库
命令行连接
```sql
mysql -uroot -proot  --连接数据库
update mysql.user set authentication_string=password('新密码') where user='用户名' and Host ='localhost' --修改用户名密码
flush privileges     --刷新权限
-------------------------------------------
--所有语句都是用';'结尾
show database  --查看所有数据库
user school  --切换数据库 user 数据库名
Database changed 
show tables   --查看数据库中所有表
describe student   --显示表中的所有信息（表结构）
create database 数据库名;  --创建一个数据库
exit  --退出连接
--单行注释(SQL的本来的注释)
/*
多行注释
*/

数据库xxx语言 CRUD增删改查 CV程序员 API程序员 CRUD程序员

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

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

mysql关键字不区分大小写

2.1、操作数据库

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] database1;

2、删除数据库

DROP DATABASE [IF EXISTS] database1;

3、使用数据库

USE school

--如果表明或者字段名是一个特殊字符 虽然可以正常查询，但``可以让他不以关键字显示

SELECT `user `  FROM student

4、查看数据库

SHOW DATABASES     查看所有数据库

学习思路：

• 对照SQLyog可视化，查看历史记录sql语句
• 固定语法和关键字需要记住
• 
  

2.2、数据库的列类型

数值

• tinyint 十分小 1B
• smallint 较小的数据 2B
• mediumint 中等大小的数据 3B
• int 标准整数 4B （常用）
• big 较大的数据据 8B
• float 浮点数 4B
• double 浮点数 8B
• decimal 字符串形式浮点数

字符串

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

时间日期

java.until.Date

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

null

• 没有值 未知
• 不要用null进行运算结果为null

2.3、数据库的字段属性(重点)

unsigned：

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

zerofill：

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

就比如int（3）3表示的的是显示长度，而不是数据长度。那个数字大小不影响int表示的大小。

varchar（n）不一样他就是数据长度

自增

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

非空 null not null

• 假设设置为not null，不赋值就会报错
• bull，如果不填写值，默认就是null

默认：

• 设置默认的值
• sex，默认值 为 男，如果不设置值就是默认值

2.4、创建数据库表

-- 目标；创建一个school数据库
-- 创建学生表（列，字段）使用sql创建
-- 学号int 登录密码varchar(20) 姓名，性别varchar(2) 出生日期(datetime) 家庭住址varchar email

-- 注意点，使用英文() ，表的名称和字段尽量使用 `` 括起来
-- AUTO_INCREMENT 自增
-- 字符串的使用 单引号括起来！
-- 所有的语句后面加,(英文符号),最后一个不用加
-- PRIMARY KEY， 一般一个表只有一个唯一的主键

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

)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE IF NOT EXISTS `表明`(
    `字段名` 列类型 [属性] [索引] [注释] ,
    `字段名` 列类型 [属性] [索引] [注释] ,
    `字段名` 列类型 [属性] [索引] [注释] ,
    ……

)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE school        --  查看创建数据库语句
SHOW CREATE TABLE student      --  查看studnet数据表的定义语句
DESC studnet              -- 显示表的结构
（查询的时候不要复制后面的数据）

2.5、数据表的类型

-- 关于数据库引擎
/*
INNODB    默认使用
MYISAM    早些年使用
*/

常规使用操作：

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

在物理空间存在的位置

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

本质还是文件的存储！

MySQL引擎在物理文件上的去拜

• InnoDB在数据库表种只有一个*.frm文件，以及上级目录下的idata1文件
• MYISAM对应文件
  • *.frm -表结构的定义我呢见
  • *.MYD 数据文件(data)
  • *.MYI 索引文件（index）

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

CHARSET=utf8

change用来重命名字段，不能修改字段类型和约束

modify不用来字段重命名，只能修改字段类型和约束

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

MySQL的默认编码是Latin1，不支持中午

在my.ini种配置默认编码

character-set-sever=utf8

修改

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE school RENAME AS school1

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

-- 修改表的字段(重命名，修改约束) 
-- ALTER TABLE 表名 MODIFY 字段名 列属性[] 
ALTER TABLE school1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[] 
ALTER TABLE school1 CHANGE age age1 INT(1) -- 字段重命名

删除

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

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

所有的创建和删除尽量加上判断存在或者不存在。

注意点:

• ``字段名，使用这个包裹
• 注释 — /**/
• sql关键字大小写不明敏感 建议大家写小写

3、MySQL数据管理

3.1、外键(了解即可)

方式一、在创建表的时候增加约束

USE school;
-- 年级表

CREATE TABLE IF NOT EXISTS `grade`(
    `gradeid` INT NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)

)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

-- 学生表的gradeid 字段要去引用年纪表的 gradeid
-- 定义外键 FOREIGN_KEY
-- 给这和外键添加约束（执行引用）references 引用
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd`  VARBINARY(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex`  VARCHAR(20) NOT NULL DEFAULT '女' COMMENT '性别',
    `gradeid` INT NOT NULL COMMENT '学生年级',
    `birthday`  DATETIME 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`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

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

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

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


-- ALTER TABLE `表` ADD CONSTRAINT `约束名` FOREIGN KEY (`作为外键的列`) REFERENCES `哪个表` (`哪个字段`);

最佳实践

• 数据库只是单纯的表，只用来存数据，只有行（数据）和列(字段)
• 我们想使用多张表的数据，想使用外键(程序去实现)

3.2、DML语言(全部记住)

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

DML语言：数据操作语言

• insert
• update
• delete

3.3、添加

insert

-- 插入语句（添加）
-- insert into 表名([字段名1，字段名2，字段名3]) values('值1','值2','值3')

-- 由于主键自增我们可以省略(如果不写表的字段，他就会一一匹配)

INSERT INTO `grade`(`gradename`) VALUES('大三')

-- 一般写插入语句，我们一定要数据和字段一一对应

-- 插入多个字段

INSERT INTO `grade`(`gradename`) 
VALUES('大二'),('大一')

INSERT INTO `student`(`NAME`) VALUES('张三')

INSERT INTO `student`(`NAME`,`pwd`,`sex`,`gradeid`) VALUES('张三','aaa','男','1')

INSERT INTO `student`(`NAME`,`pwd`,`sex`,`gradeid`) 
VALUES('李四','aaa','男','2'),('王五','aaa','男','3')

语法：insert into 表名([字段名1，字段名2，字段名3]) values('值1','值2','值3')

注意事项：

1. 字段和字段之间使用，英文逗号隔开
2. 字段是可以圣库俄的，但后面的值必须要一一对象，不能少
3. 可以同时插入多条数据，values后面的值，需要使用，隔开即可values(),()

3.4、修改

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

use school;

-- 修改学员名字
UPDATE `student` set `name`='狂神' where id= 1;

UPDATE `student` set `name`='长江七号' ;

UPDATE `student` set `name`='长江八号',`email`='2673640568@qq.com' WHERE `NAME`='长江七号' ;

UPDATE `student` set `birthday`=CURRENT_TIME WHERE `NAME`='长江八号' and sex='女';

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

操作符会返回布尔值

语法：UPDATE 表名 set column_name = value,[column_name = value,column_name = value……] where [条件]

主要：

• column_name 是数据库的列，尽量带上``
• 条件，筛选的条件，如果没有指定，则会修改所有的列
• value，是一个具体的值，也可以是一个变量

UPDATE `student` set `birthday`=CURRENT_TIME WHERE `NAME`='长江八号' and sex='女';

3.5、删除

delete 命令

语法:``

-- 删除数据    (避免这样写)
DELETE FROM `student`;

-- 删除指定数据
DELETE FROM `student` where `id` =1;

TRINCATE命令

作用：完全清空一个数据表，索引和约束不会消失

-- 清空 student 类
TRUNCATE `student`;

Delete 和 TRUNCATE 区别

• 相同的：都能删除数据，都不会删除表结果
• 不同：
  • TRUNCATE 重新设置自增列 计数器归零
  • TRUNCATE 不会影响事务

use school;
CREATE TABLE `test`(

    `id` int(4) not null AUTO_INCREMENT,
    `coll` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

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

TRUNCATE TABLE `test` -- 自增会归零

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

• innoDB 自增列会从1开始(存在内存中，断电即失)
• MyISAM 继续从上一个自增量开始(存在文件中不会丢失)

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

4.1、DQL

(Data Query LANGUAGE:数据查询语言)

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

4.2、指定查询字段

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

所用数据库创建

create database if not exists `school`;
-- 创建一个school数据库
use `school`;-- 创建学生表
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;

-- 创建年级表
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;

-- 创建科目表
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;

-- 创建成绩表
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;

数据库创建

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

SELECT *from student



-- 查询指定字段

SELECT `studentno`,`studentname` FROM student


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

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

语法：select 字段…… from 表名

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

去重 distinct

-- 查询一下哪些同学参加了考试,成绩

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

数据库的列 (表达式)

SELECT VERSION() -- 查询系统版本
SELECT 100*3-1 AS 计算结果 -- 用来计算
SELECT @@auto_increment_increment -- 查询自增步长(变量)

-- 学员考试成绩 +1分查看
SELECT `studentno` ,`studentresult`+1 AS `提分后` from result

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

select 表达式 from 表

4.3、where条件子句

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

逻辑运算符

尽量使用英文字母

use school;


-- ===========WHERE===================

SELECT `studentno`,`studentresult`FROM `result`;


-- 查询考试成绩在95


SELECT `studentno`,`studentresult`
FROM `result`
where studentresult>=95 AND studentresult<=100;


-- 模糊查询(区间)

SELECT `studentno`,`studentresult`
FROM `result`
WHERE studentresult BETWEEN 95 and 100;


-- 除了学号为1000之外的同学成绩

SELECT `studentno`,`studentresult`
FROM `result`
WHERE NOT studentno = 1000 ;

模糊查询 : 比较运算符

use school;

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

-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符) _(一个字符)

SELECT `studentno`,`studentname`
FROM `student`
WHERE studentname like '刘%';


-- 查询名字只有一个字的姓刘的同学

SELECT `studentno`,`studentname`
FROM `student`
WHERE studentname like '刘__';

-- 查询名字中有强字的同学

SELECT `studentno`,`studentname`
FROM `student`
WHERE studentname like '%强%';


-- =============in(具体的一个或多个值)==============
-- 查询1001，1002，1003
SELECT `studentno`,`studentname`
FROM `student`
WHERE studentno in (1001,1002,1003);


-- ===========null  not null================


-- 查询地址为空的的同学
SELECT `studentno`,`studentname`
FROM `student`
WHERE address='' OR address is NULL;


-- 查询有出生日期的同学 不为空
SELECT `studentno`,`studentname`
FROM `student`
WHERE borndate is NULL;


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

4.4、联表查询、

on where 为啥左右连接的时候不能用where可以用on

use school;

-- =============联表查询 join=====================

-- 查询了参加了考试的同学(学号，姓名，科目编号，分数)

SELECT * 
from student;
SELECT *
FROM result;


/*
1.分析需求，分析查询的字段来自哪些表(连接查询)
2.确定使用哪种连接的查询？七种
确定交叉点(这两个表中的哪个数据是相同的)
判断的条件：学生表中的 studentno = 成绩表 studentno

*/

-- join ON  连接查询
-- where 等值查询

-- INNER JOIN

SELECT    s.studentno,s.studentname,subjectno,studentresult
FROM student AS s INNER JOIN result AS r
WHERE s.studentno =r.studentno;

-- LEFT JOIN
SELECT    s.studentno,s.studentname,subjectno,studentresult
FROM student AS s LEFT JOIN result AS r
on s.studentno =r.studentno;

-- RIGHT JOIN
SELECT    s.studentno,s.studentname,subjectno,studentresult
FROM student AS s RIGHT JOIN result AS r
on s.studentno =r.studentno;


-- 查询缺考的同学

-- LEFT JOIN
SELECT    s.studentno,s.studentname,subjectno,studentresult
FROM student AS s LEFT JOIN result AS r
on s.studentno =r.studentno and r.studentresult =NULL;


-- 参加了考试同学的信息，学号，姓名，科目号，分数
SELECT    s.studentno,s.studentname,subjectname,studentresult
FROM student AS s RIGHT JOIN result AS r
on s.studentno =r.studentno 
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno

-- 我要查询哪些数据select
-- 从那几个表中查 from 表 xxx join 连接的表 on 交叉的条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表，再慢慢来

自连接

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

所用数据库

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技术');

-- 查询父子信息：把一张表堪为两个一模一样的表

SELECT a.`categoryname` AS '父' ,b.`categoryname` '子'
from category a,category b
WHERE a.categoryid=b.pid;


--  查询科目所属的年级(科目名称，年级名称)
SELECT `subjectname`,`gradename`
from `subject` sub INNER JOIN `grade` g
on sub.`gradeid`=g.`gradeid`;


-- 查询了参加 数据库结构-1 考试的同学信息：学号，学生姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
from student s INNER JOIN `result` r
on s.studentno=r.studentno
INNER JOIN `subject` sub
on r.`subjectno` =sub.`subjectno`
WHERE subjectname='数据库结构-1';

4.5、分页和排序

排序

-- ==============分页和排序===================

-- 排序： 升序 ASC 降序DESC
-- ORDER BY 通过哪个字段排序，怎么排序
-- 查询结果根据成绩降序升序
SELECT    s.studentno,s.studentname,subjectno,studentresult
FROM student AS s INNER JOIN result AS r
WHERE s.studentno =r.studentno
ORDER BY  `studentresult` ;

分页

-- 为什么要分页
-- 环节数据库压力，给人的体验更好 瀑布流可以一直向下刷
-- LIMIT a，b
-- 从第a个数据开始数b个。数据从0开始 例如(n-1)*b,b  每页b个第n页

SELECT    s.studentno,s.studentname,subjectno,studentresult
FROM student AS s INNER JOIN result AS r
WHERE s.studentno =r.studentno
ORDER BY  `studentresult` 
limit 2,2

语法：LIMIT a，b

        `从第a个数据开始数b个。数据从0开始 例如(n-1)*b,b 每页b个第n页`

4.6、子查询

where(条件的值是计算出来的)

本质:在where里查询嵌套语句

-- 查询JAVA第一学年 课程成绩排名前十的同学，并且分数要大于80的学生信息(学号，姓名，课程名称，分数)
USE school;
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
on s.studentno =r.studentno
INNER JOIN `subject` sub
on sub.`subjectno` =r.`subjectno`
WHERE subjectname='JAVA第一学年' AND studentresult>=80
ORDER BY studentresult
LIMIT 0,10;


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

-- 方式一：使用子查询
SELECT `studentno`,`studentno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='数据结构-1'
ORDER BY studentresult DESC;

-- 方式二：使用子查询
SELECT `studnetno`,`subjectno`,`studentrsult`
FROM `result`
WHERE studentno =(
    SELECT subjectno 
    FROM `subject`
    WHERE subjectname='数据库结构-1';
)
ORDER BY studentresult DESC;

-- 查询所有数据库-1的学生学号
SELECT subjectno FROM `subject` WHERE subjectname='数据库结构-1';


-- 分数不小于80分的学生的学号和姓名

SELECT s.`studentno`,s.`studentname`
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
WHERE `studentresult`>=80;

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

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

-- 查询课程为高等数学-2，且分数不小于80的同学的学号和姓名
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 `subjectname`='高等数学-2' AND studentresult>=80

-- 使用子查询

-- 由里及外
SELECT studentno,studentname FROM student where studentno in(
    SELECT studentno FROM result WHERE studentresult>80 AND subjectno =(
        SELECT subjectno FROM `subject` where `subjectname` ='高等数学-2'
     )

)

4.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分
-- 核心 :(根据不同的课程分组)
SELECT subjectname,avg(studentresult) as 平均分,max(studentresult) AS 最高分,min(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
GROUP BY r.subjectno  -- 通过什么字段来分组
HAVING 平均分>80

5、MYSQL函数

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

5.1、常用函数

-- 数学运算

SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取证
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的数
SELECT SIGN(10) --  判断一个数字的符号


-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串的长度
SELECT CONCAT('我','爱','CODE') -- 拼接字符串
SELECT INSERT('i love CODE',3,4,'love java')  -- 查询,从某个位置开始替换某个长度
SELECT LOWER('BIG')            -- 小写字母
SELECT UPPER('little')     -- 大写字母
SELECT INSTR('LIMITwool','wool') -- 返回第一次出现的子串的所有
SELECT REPLACE('坚持就能成功','就能','不一定') -- 替换出现的指定字符串
SELECT SUBSTR('Betray your effort',8,4) -- 返回指定的字符串
SELECT REVERSE('Betray your effort') -- 反转

-- 查询姓周的同学，姓改为邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'

-- 事件日期函数(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW()     -- 获取现在时间
SELECT LOCALTIME() -- 获取本地时间
SELECT SYSDATE()  -- 获取系统时间

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5.2、聚合函数(常用)

-- ==============聚合函数=================
-- 都能够统计表中数据(想查询一个表中由多少个记录，就会使用count())

SELECT COUNT(studentname) FROM student  -- count(字段)，会忽略null值
SELECT Count(*) FROM student  -- count(*) 不会忽略null 本质 计算行数 包含所有列 
SELECT COUNT(1) FROM student     -- count(1) 不会忽略null 忽略所有列

SELECT SUM(`studentresult`) AS sum from result
SELECT AVG(`studentresult`) AS avg from result
SELECT MAX(`studentresult`) AS max from result
SELECT MIN(`studentresult`) AS min from result

5.3、数据库级别的MD5加密(拓展)

什么是MD5？

主要增强算法复杂度和不可逆性？

MD5不可逆

CREATE TABLE testmd5 (
    `id` int 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

UPDATE testmd5 SET pwd=MD5(pwd);

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

-- 如何校验：将用户传递进来的密码进行md5加墨，然后对比加密后的值

SELECT *FROM testmd5 WHERE `name`='xiaoming' and `pwd`=md5('123456')

6、事务

6.1、什么是事务

要么都成功，要么都失败

1、SQL执行,A给B转账 A 1000 —200—>B 200

2、SQL执行,B收到A的钱 A 800 ——>B 400

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

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

原子性（Atomicity）
原子性,是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

隔离所导致的一些问题

脏读：

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

不可重复读：

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

虚读(幻读):

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

执行事务

-- =====================事务===============


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


-- 手动处理事务
set autocommit = 0 -- 关闭自动提交


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

insert xx
insert xx


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


-- 事务结束
set autocommit = 1 -- 开启自动提交


-- 了解
SAVEPOINT 保存点名  -- 设置一个事务保存点
ROLLBACK to SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销指定的保存点名

-- 转账

CREATE DATABASE shop CHARACTER set utf8 COLLATE utf8_general_ci
use shop

CREATE TABLE `account`(
    `id` int 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',1000.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; -- 恢复默认值

7、索引

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

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

7.1、索引的分类

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

• 主键索引 （PRIMARY KEY）
  • 唯一的标识，主键不可重复，只能由一个列作为主键
• 唯一索引 （UNIQUE KEY）
  • 避免重复的列出现，唯一索引可以重复，多个列都可以表示为唯一索引
• 常规索引 （KEY/INDEX）
  • 默认的，index,key关键字来设置
• 全文索引 （FullText）
  • 在特定的数据库引擎下采样，MyISAM
  • 快速定位数据

基础语法

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

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

-- 增加一个全文索引    (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`) -- 报错没查出来

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

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

7.2、测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

use school
delimiter $$
set global log_bin_trust_function_creators=TRUE;
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2),'123');
SET i=i+1;
END WHILE;
RETURN i;
END;
USE school
SELECT mock_data() -- 执行此函数 生成一百万条数据

log_bin_trust_function_creators=TRUE;

SELECT * FROM app_user WHERE `name` ='用户999999'

-- id_表名_字段名
-- CREATE INDEX 索引名 on 表 (字段)
CREATE INDEX id_app_user_name ON app_user(`name`)

SELECT * FROM app_user WHERE `name` ='用户999999'

-- 加了索引之后速度更快了
-- 没加索引时候是便利，加了索引之后是定位

-- drop index 索引名 on 表名; -- 删除索引
drop index id_app_user_name on app_user

索引在小数据量的时候用处不大，大数据量的时候却别明显

7.3、索引原则

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

索引的数据结构

Hash类型的索引

Btree：InnoDB默认的数据结构

8、权限管理和库份

8.1、用户管理

Navicat可视化管理

略

SQL命令操作

用户表：mysql.user

本质：读这张表进行增删改查

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

-- 删除用户
drop user 用户名

-- 修改密码（修改当前用户密码）

set password =PASSWORD('123456')


-- 修改密码（修改指定用户密码）

SET PASSWORD for littlewool =PASSWORD('123456')

-- 重命名 RENAME USER 原来名字 to 新的名字
RENAME user littlewool to littlewool2

-- 用户授权 all PRIVILEGES 全部的权限，库，表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO littlewool2

-- 查询权限
SHOW grants FOR littlewool2  -- 查看指定用户权限
SHOW grants for root@localhost

-- root 用户权限：GRANT all privileges on *·* to 'root'@'localhost' with grant option

-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES on *.* FROM littlewool2

8.2、MySQL备份

为什么要备份：

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

MySQL数据库备份方式

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

mysqldump -hlocalhost -uroot -proot school student >D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置/文件名

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 >物理磁盘位置/文件名

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 >物理磁盘位置/文件名

# 导入
# 登陆的情况下 切换到指定数据库
#source 备份文件路径
source d:/a.sql

mysql -u用户名 -p密码 库名<备份文件

9、规范数据库设计

9.1、为什么需要设计

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

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都很发吗，异常[屏蔽使用物理外键]
• 程序性能差

良好的数据库设计：

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

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

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

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

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表 （文章分类，谁创建的）
  • 文章表 （文章的信息）
  • 友联表 （友联信息）
  • 自定义表（系统信息，某个关键字，或者一些主字段）key：value
  • 说说表 （发表心情 ，id ，conternt，createtime）
• 标识实体（把需求落实到每个字段）
• 标识实体之间的挂你先
  • 写博客：user—>blog
  • 创建分类：user—>category
  • 关注：user—>user
  • 友链：links
  • 评论：user-user-blog

9.2、三大范式

为什么需要数据规范化？

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

三大范式

第一范式（1NF）：

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

第二范式（2NF）：

前提：满足第一范式

消除部分依赖，要求一张表中的每一列都完全依赖于主键（针对于组合主键），也就是不会出现某一列只和部分主键相关

第三范式（3NF）：

前提：满足第二范式

消除传递依赖，要求一张表中的每一列都和主键是直接依赖的，不是间接依赖（重点）

规范性和性能问题

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

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

— 业务级别MySQL学习

— 运维级别的MySQL

10、JDBC(重点)

10.1、Driver

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

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

10.2、JDBC

SUN公司为了简化开发人员（对数据库的统一）操作，提供了一个（Java操作数据库）规范，俗称JDBC，这些规范的实现由具体的厂商去做

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

java.sql

javax.sql

还需要导入一个数据库驱动包 mysql-connector-java-8.0.11

10.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、创建一个普通项目

2、导入驱动 创建lib目录，驱动移动到lib目录下，lib目录右击add as library

3、编写测试代码

package com.littlewool.jdbc;

import com.mysql.cj.jdbc.Driver;

import java.sql.*;

//我的第一个jdbc程序
public class jdbcLesson1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");//固定写法加在驱动
        //2.用户信息和url
        String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8";
        String username="root";
        String password="root";
        //3.连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);
        //4.执行SQL的对象
        Statement statement = connection.createStatement();
        //5.执行SQL的对象去执行SQL，可能存在结果
        String sql="SELECT *FROM users";

        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集

        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
            System.out.println("=====================================================");
        }
        //6.释放链接

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

步骤总结：

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

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Class.forName("com.mysql.cj.jdbc.Driver");//固定写法加在驱动

//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚

URL

String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8";

//mysql -- 3306
//jdbc:mysql://主机地址:端口号/数据库名？参数1&参数2&参数3

//oralce --1512
//jdbc:oracle:thin:@localhost:1512:sid

Statement执行SQK的对象 PrepareStatement

String sql="SELECT *FROM users"; //编写SQL



        statement.executeQuery() //查询操作返回ResultSet
        statement.execute(); //执行任何SQL 有判断过程，速度较低
        statement.executeUpdate(); //更新，插入，删除，都是用这个，返回一个受影响的行数

ResultSet查询的结果集

获得指定的类型数据

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

遍历，指针

resultSet.beforeFirst();//移动到最前
        resultSet.afterLast();//移动到最后
        resultSet.next();//移动到下一个
        resultSet.previous();//移动到前一个
        resultSet.absolute();//移动到指定行

释放资源

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

10.4 statement

若java程序想要对数据库数据进行增删改查，只需要执行statement，这样可以向数据库发送sql语句。使用executeUpdate方法适合增删改操作，返回的结果是一个被影响的行数，也就是增删改的操作对数据库表几行数据发生了变化

statement.executeQuery()用于查询数据库表的数据，返回的是一个结果集，数据被封装到里面

CRUD操作 delete

         Statement statement = connection.createStatement();
         String sql = "DELETE from users WHERE id=2 ";
         int i = statement.executeUpdate(sql);
         if (i > 0){
             System.out.println("删除成功");
         }

CRUD操作 insert

Statement statement = connection.createStatement();



 String sql = "INSERT INTO users(`name`,`password`,`email`,`birthday`) VALUES('zhangsan','232322','zhangsan@163.com','1995-08-06'),('lisi','232322','lisi@163.com','2002-08-06'),('wangwu','232322','wangwu@163.com','1989-08-06'),('hanqing','232322','1438617560@qq.com','1995-08-06') ";



         int i = statement.executeUpdate(sql);
         if (i > 0){
             System.out.println("添加成功");
    }

CRUD操作 update

Statement statement = connection.createStatement();
         String sql = "UPDATE users set `name`= '汉青' WHERE id=9 ";
         int i = statement.executeUpdate(sql);
  if (i > 0){
System.out.println("修改成功");
   }

CRUD操作 Retrive

 Statement statement = connection.createStatement();
         String sql = "select * from users WHERE id=1";
         int i = statement.executeQuery(sql);
         if (i > 0){
             System.out.println("查找成功");
         }

代码实现

1、提取工具类

package com.littlewool.jdbc.lesson2.utils;

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

public class JdbcUtils {

    private static String driver=null;
    private static String url=null;
    private static String username=null;
    private static String password=null;

    static {

        try {

            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url =properties.getProperty("url");
            username =properties.getProperty("username");
            password =properties.getProperty("password");
            //1.驱动只用加载一次
            Class.forName(driver);


        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    public  static  void release(Connection conn, Statement state, ResultSet rs) throws SQLException {
        if(rs!=null){
            rs.close();
        }
        if (state!=null){
            state.close();
        }
        if(conn!=null){
            conn.close();
        }
    }
}

2、编写增删改方法，executeuptate

增

package com.littlewool.jdbc.lesson2;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) throws SQLException {

        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;


        try {
            connection = JdbcUtils.getConnection();//获取数据库链接
            statement=connection.createStatement();
            String sql="INSERT into users(id,`name`,`PASSWORD`,`email`,`birthday`)VALUES(4,'kuang','123456','littlewool@163.com','2020-01-01')";

            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("Insert successfully！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }

    }

}

删

package com.littlewool.jdbc.lesson2;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) throws SQLException {

        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;


        try {
            connection = JdbcUtils.getConnection();//获取数据库链接
            statement=connection.createStatement();
            String sql="DELETE FROM users WHERE id=4;";

            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("Delete successfully！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }

    }

}

改

package com.littlewool.jdbc.lesson2;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) throws SQLException {

        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;


        try {
            connection = JdbcUtils.getConnection();//获取数据库链接
            statement=connection.createStatement();
            String sql="UPDATE users SET `name`='shab' WHERE id=4";

            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("Update successfully！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }

    }

}

3、查询

package com.littlewool.jdbc.lesson2;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) throws SQLException {

        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;


        try {
            connection = JdbcUtils.getConnection();//获取数据库链接
            statement=connection.createStatement();
            String sql = "select * from users WHERE id=1";

            resultSet= statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }

    }

}

SQL注入问题

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

package com.littlewool.jdbc.lesson2;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

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

public class SqlInjection {
    public static void main(String[] args) throws SQLException {
        //login("littlewool","123456");
        login("'or'1=1","123456");
    }

    public static void login(String username,String password) throws SQLException {

        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;


        try {
            connection = JdbcUtils.getConnection();//获取数据库链接
            statement=connection.createStatement();
            String sql = "select * from users WHERE `name`='"+username+"'and `password`='"+password+"'";

            resultSet= statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
                System.out.println("============================================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }

    }

}

10.5、PreparedStatement对象

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

代码实现

1、增

package com.littlewool.jdbc.lesson3;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

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

public class PreparedInsert {

    public static void main(String[] args) throws SQLException {
        PreparedStatement st =null;
        Connection con =null;


        try {
            con = JdbcUtils.getConnection();

            //区别
            //使用？占位符代替参数
            String sql="INSERT into users(id,`name`,`PASSWORD`,`email`,`birthday`)VALUES(?,?,?,?,?)";

            st =con.prepareStatement(sql);//预编译sql，先写sql，然后不执行

            //手动给参数赋值

            st.setInt(1,4);//id
            st.setString(2,"littlewool4");//name
            st.setString(3,"123456");//password
            st.setString(4,"996649790@qq.com");//email
            //注意点：  sql.Date 数据库  java.sql.Date()
            //         util.Date java new Date().getTime 获得时间戳
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));


            //执行
            int i=st.executeUpdate();
            if (i>0){
                System.out.println("insert successfully");

            }







        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,null);
        }


    }
}

2、删

package com.littlewool.jdbc.lesson3;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

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

public class PreparedDelete {

    public static void main(String[] args) throws SQLException {
        PreparedStatement st = null;
        Connection con = null;


        try {
            con = JdbcUtils.getConnection();

            //区别
            //使用？占位符代替参数
            String sql = "delete from users where id=?";
            st = con.prepareStatement(sql);//预编译sql，先写sql，然后不执行
            //手动给参数赋值
            st.setInt(1, 4);
           //执行
            int i = st.executeUpdate();
            if (i > 0) {
                System.out.println("Delete successfully");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(con, st, null);
        }


    }
}

3、改

package com.littlewool.jdbc.lesson3;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

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

public class PreparedUpdate {

    public static void main(String[] args) throws SQLException {
        PreparedStatement st = null;
        Connection con = null;


        try {
            con = JdbcUtils.getConnection();

            //区别
            //使用？占位符代替参数
            String sql = "update users set `name` =?  where `id`=?";
            st = con.prepareStatement(sql);//预编译sql，先写sql，然后不执行
            //手动给参数赋值
            st.setString(1, "update");
            st.setInt(2, 4);//id
            //执行
            int i = st.executeUpdate();
            if (i > 0) {
                System.out.println("Update successfully");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(con, st, null);
        }


    }
}

4、查

package com.littlewool.jdbc.lesson3;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

import java.sql.*;

public class PreparedRetrieve {

    public static void main(String[] args) throws SQLException {
        PreparedStatement st = null;
        Connection con = null;
        ResultSet resultSet=null;


        try {
            con = JdbcUtils.getConnection();

            //区别
            //使用？占位符代替参数
            String sql = "Select * from users where `id`=? ";
            st = con.prepareStatement(sql);//预编译sql，先写sql，然后不执行
            //手动给参数赋值
            st.setInt(1,1 );

            //执行
            resultSet= st.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(con, st, null);
        }


    }
}

5、防止注入

package com.littlewool.jdbc.lesson3;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

import java.sql.*;

public class AvoidSqlInjection {
    public static void main(String[] args) throws SQLException {
//        login("littlewool","123456");
        login("''or1=1","123456");
    }

    public static void login(String username,String password) throws SQLException {

        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;


        try {
            connection = JdbcUtils.getConnection();//获取数据库链接
            //PreparedStatement防止sql注入的本质，把传递进来的参数当作字符
            //其中如果存在转义字符会被直接转义
            String sql = "select * from users WHERE `name`=? and `password`=? ";

            statement=connection.prepareStatement(sql);
            statement.setString(1,username);
            statement.setString(2,password);
            resultSet= statement.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
                System.out.println("============================================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }

    }

}

10.6使用IDEA连接数据库

1.点击右侧Database如果没有点击左下角小电脑
这里如果让你下载Driver，记得在这个页面的左边Drivers：下面列出的MySQL

2.输入相应的信息

3.可以在连接之后修改相应的信息

可以调整选择显示的数据库和表

4.修改完数据之后需要点击submit才能提交

5.如果连接不上数据库驱动有问题可以调整驱动

测试用的sql语句

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);

10.7、事务

要么都成功要么都失败

ACID

A(Atomicity):原子性，一个事务要一起完成，要么一起失败，不能因为中途一些错误导致数据一部分写入成功，一部分写入失败，失败就要回滚。

C(Consistency):一致性，指的是数据状态一致性，就比如一次性操作三个数据状态为1的数据，给他们加2，一致性就代表三个数据都要等于3，如果一个等于3，二个还等于1就是违反一致性。

I(Isolation):隔离性，指的是事务之前的隔离性，比如A事务和B事务同时在运行，它们直接不能产生联系和影响，不然就是违反隔离性，隔离性产生如下四种隔离级别：

D（Durability）:持久性 ，很好理解了，就是数据一旦提交就比如实现持久化了，继续后续系统发生了故障、断电断网数据都不会丢失。

隔离性导致的问题：

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

不可重复读：在用一个事务内，重复读取表中的数据，表数据法案神格改变

虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致

代码实现

1、开启事务connection。setAutonCommit（false）(关闭自动提交会自动开启事务)

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

3、可以在catch语句中回滚，但默认失败就会回滚

package com.littlewool.jdbc.lesson4;

import com.littlewool.jdbc.lesson2.utils.JdbcUtils;

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

public class TestTransaction {


    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
            conn =JdbcUtils.getConnection();
            //关闭数据库的自动提交 会自动开启事务
            conn.setAutoCommit(false);

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

            //测试回滚
            int x=1/0;

            String sql2="update account set money=money+100 where  name = 'B'";
            statement=conn.prepareStatement(sql2);
            statement.executeUpdate();
            //业务完毕，提交事务
            conn.commit();
            System.out.println("Success");

        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

            e.printStackTrace();

        }finally {
            JdbcUtils.release(conn,statement,resultSet);
        }

    }

}

10.8、数据库连接池

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

连接—释放 十分浪费资源

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

———-开门—业务员：等待—-服务

常用连接数：10

最小连接数：10

最大连接数：15

等待超时 ：10ms

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

开源数据源实现

DBCP

C3P0

Druid：阿里巴巴

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

DBCP

需要用到的jar包

commons-pool2-2.11.1
commons-dbcp2-2.9.0
commons-logging-1.2

C3P0

需要用到的jar包

mchange-commons-java-0.2.20

c3p0-0.9.5.5

结论

无论使用什么数据源，datasource接口不会变