狂神说阶段三——MySQL

原视频网站
https://www.bilibili.com/video/BV1NJ411J79W?p=7&t=17

目录

1.初始MySQL

javaEE:企业级java开发 Web
前端 （页面：展示数据）
后台 （连接点，连接数据库JDBC，连接前端：控制视图跳转，给前端传递数据）
数据库 （存数据 txt,excel...）

只会写代码，学好数据库，基本混饭吃（大部分程序员）
操作系统，数据结构与算法，当一个不错的程序员
离散数学，数字电路，体系结构，编译原理，实战经验。高级程序员，不怕被干掉的

1.1 为什么学习数据库

  1. 岗位需求
  2. 现在的世界，大数据时代，得数据者得天下（如精准营销）
  3. 被迫需求：存数据(例去IOE"去掉IBM的小型机、Oracle数据库、EMC存储设备")
  4. 数据库是所有软件体系中最核心的存在 DBA （数据库管理员）

1.2 什么是数据库

数据库（DB DataBase)
概念： 数据仓库、是一个软件、安装在操作系统（windows,linux，mac...）之上
作用：存储数据，管理数据

1.3 数据库分类

  • 关系型数据库：SQL
    • MySQL,Oracle,Sql Server,DB2,SQLlite
    • 通过表和表之间，行和列之间的关系进行数据的存储，学生信息，考勤表...
  • 非关系数据库: （NoSQL）Not Only
    • Redis,MonDB
    • 对象存储，通过对象滋生的属性来决定
  • DBMS数据库管理系统
    • 数据库管理软件，方便获取和维护管理我们的数据
    • MySQL 本质上是数据库管理系统，可以管理我们的数据

1.4 MySQL简介

(看简介推荐去百度百科,然后抽取核心）
MySQL是一个关系型数据库管理系统
前世：瑞典MySQL AB 公司开发
今生：Oracle 旗下产品
开源的，体积小、速度快、总体拥有成本低。（招人成本低，所以必须会）
中小型网站，或者大型网站（集群）
https://www.mysql.com/
安装建议（用的是稳定版5.7）
1.尽量不要使用exe,会修改注册表（卸载，可能写在不干净）
2. 尽可能使用压缩包安装
mysql5.7 64位下载地址:
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

1.5 安装

安装步骤
1、下载后得到zip压缩包,解压

2、把这个包放到自己电脑环境目录下

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

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

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

[mysqld]
# 目录一定要换成自己的
basedir = D:\Environment\mysql-5.7.19\
datadir = D:\Environment\mysql-5.7.19\data\
port=3306
skip-grant-tables

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

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

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

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

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

9、刷新权限

flush privileges;

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

11、重启mysql即可正常使用

net stop mysql
net start mysql

12、连接上测试出现以下结果就安装好了
安装不成功执行 sc delete mysql 然后再重装

1.6 安装SQLyog（可视化工具）

  1. 安装注册,默认的4个数据库一个都不要动
  2. 新建一个数据库
    鼠标右键点击操作，每一个sqlyog的操作本质上都是执行的sql语句，都可以在历史记录中查看
  3. 新建一张表student
    id,name,age

1.7 连接数据库

命令行连接
所有语句都使用；结尾

mysql -uroot -p123456 --连接数据库
update mysql.user set authentication_string=password('123456') where user='root'and Host = 'localhost';
--修改用户密码
flush privileges;--刷新权限
show databases;--查看所有的数据库
use DBname;--切换数据库
show tables;--查看数据库中所有表
describe student;--显示数据库中所有的表的信息
create database dbname;--创建数据库
exit;--退出
--单行注释（sql的注释）
/*
多行注释
*/

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

2. 操作数据库

操作数据库-》操作表-》操作表中数据
MySQL不区分大小写

2.1 操作数据库(了解)

1.创建数据库
中括号项表示可选，大括号项表示必选

CREATE DATABASE [IF NOT EXISTS] westos

2.删除数据库

DROP DATABASE [IF EXISTS] westos

3.使用数据库
如果表名或者字段名带有特殊字符，需要带`反引号

USE `school`
select `user` FROM student
  1. 查看数据库
show DATABASES--查看所有的数据库

学习思路：

  • 对比 sqlyog可视化操作，查看历史纪录里的sql语句来学习
  • 固定语法或关键字必须要记住

2.2 数据类型

2.2.1 数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的数据 3个字节
  • int 标准整数 4个字节（常用的）
  • big 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节
    注：int(M)M表示显示的宽度，不是实际存储范围
  • decimal 字符串形式的浮点数 金融计算的时候，一般用decimal（常用）

2.2.2 字符串

  • char 固定大小的字符串 0~255
  • varchar 可变大小的字符串 0~65535（用来存常用的变量,对应java中string）
  • tinytext 微型文本 2^8-1
  • text 文本串 2^16-1 (保存大文本)
    注 varchar(M)表示实际存储的字符数

2.2.3 时间日期

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

2.2.4 null

  • 没有值，未知
    **注意，不要使用NULL来运算，结果一定是NULL

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

  • Unsigned:
    • 无符号的**整数*
    • 声明了该列不能声明为负数
  • zerofill:
    • 0填充的
    • 不足的位数使用0来填充，int(3) （3表示显示宽度为3，和存储的范围大小无关）输入值为5，则实际存储为005
  • 自增
    • 自动在上一条记录的基础上+1（默认+1，也可以自定义主键自增的起始值和步长）
    • 通常用来设计唯一的主键index，必须是整数类型
  • 非空 not null
    • 假设设置为not null，不赋值就会报错
    • NULL，如果不填写值，默认就是NULL
  • 默认:
    • 设置默认的值，如果不指定该列的值，则使用默认值
  • 扩展
    每张表必须存在以下5个字段，未来做项目用的
    id 主键
    version 每条记录都有个版本，用来做乐观锁的
    is_delete 伪删除 标记为1后只有管理员才能查看被删除的数据
    gmt_create 创建时间 该记录的创建时间
    gmt_update 修改时间

2.4 创建数据库表（重点）

--注意点使用英文符号{}，表的名称和字段尽量使用``反引号包起来
-- AUTO_INCREMENT自增
-- 字符串使用单引号括起来(双引号也可以，用的少)
-- 所有的语句后加英文逗号，最后一个不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
CREATE TABLE IF NOT EXISTS`student`{
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHA(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

格式：

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

如果不记得怎么写，可以查看已存在的数据库或表的创建语句

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

2.5 数据表的类型

数据库引擎
INNODB 默认使用，MYISAM 早些年使用的。二者区别如下：

类型 MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大
  • 常规使用操作
    • MYISAM 节约空间，速度较快
    • INNODB 安全性高，事务的处理，多表多用户操作
  • 在物理空间存在的位置
    所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
    本质还是文件的存储
    MySQL引擎在物理文件上的区别
    • InnoDB在数据库表中只有一个*.frm文件以及上级目录下的ibdata1文件
    • MYISAM对应文件
      • *.frm--表结构定义的文件
      • *.MYD 数据文件（data)
      • *.MYI 索引文件（index)
  • 设置数据库表的字符集编码
    CHARSET=utf8
    不设置使用默认字符集Latin1编码，不支持中文
    可以修改my.ini配置编码 character-set-server=utf8,但最好每次创建表时都加一句CHARSET=uft8.这样这张表拿到别人那没设置配置文件也可以用

2.6 修改删除表

修改

--修改表名: ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
--增加表的字段: ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
--修改表的字段（重命名，修改约束）
-- 修改约束：ALTER TABLE 表名 MODIFY 字段名 新列属性 
-- 重命名： ALTER TABLE 表名 CHANGE 旧名字  新名字 列属性[]
ALTER TABLE teacher MODIFY age VARCHAR(11) --修改约束
ALTER TABLE teacher CHANGE age age1 INT(1) --字段重命名
--删除表的字段: ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1
--删除表
DROP TABLE IF EXISTS teacher

注：* change用来修改字段名，modify用来修改约束
* 所有创建和删除操作尽量加上判断，以免报错
* ``字段名用反引号包裹（防止和关键字重复）
* 注释-- /* */
* sql 关键字大小写不敏感，建议使用小写，方便阅读
* 所有符号都用英文

3 MySQL数据管理

3.1 外键 （了解即可）

删除表A，如果表A（主表）存在表B（从表）的外键引用，先要把表B删除，才能删除表A（被引用表）

  • 创建外键方式一
CREATE TABLE `grade`{
  `gradeid` INT(10)  NOT NULL AUTO_INCREMENT COMMENT '年级id'，
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称'，
   PRIMARY KEY (`gradeid`)
} ENGIN = INNODB DEFAULT CHARSET =utf8
--学生表的gradeid字段要去引用年级表的gradeid字段
--定义外键
--给这个外键添加约束（执行引用)
CREATE TABLE IF NOT EXISTS`student`{
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHA(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10)  NOT NULL AUTO_INCREMENT COMMENT '学生年级',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
}ENGINE=INNODB DEFAULT CHARSET=utf8
  • 方式二
    创建表的时候没有外键关系
    ALERT TABLE 表名 ADD CONSTAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCE 被引用表名 (被引用字段)
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`)

以上操作都是数据库级别的物理外键，不建议使用。（避免数据库过多造成困扰，这里了解即可）
最佳实践

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

3.2 DML语言（全部记住)

数据库存在意义:数据存储，数据管理
DML语言： 数据操作语言
insert, update,delete

3.2.1 insert

INSERT INTO 表名（[字段名1，字段2，字段3]）values ('值1','值2','值3'),('值1','值2','值3')
插入语句一定要字段和值一一对应

INSERT INTO `grade` (`gradename`) VALUES('大三')
--一次插入多行
INSERT INTO `grade` (`gradename`) VALUES('大三')('大四')
--多行多字段(下例两行)
INSERT INTO student(`name`,`pwd`,`sex`) VALUES('李四','12324','男'),('王五','232434','女')

注意事项：

  1. 字段和字段之间使用 英文逗号隔开
  2. 字段是可以省略的，但是表里后面的值要都列出来，一一对应
  3. 可以同时插入多条数据，VALUES后面的值，需要使用逗号隔开 value(),()...
  • update
  • delete

3.2.2 修改

语法(关键字大写)
UPDATE 表名 SET column_name = value [, column_name2 = value..] WHERE[条件]

--加where条件，修改指定的行
UPDATE `student` SET `name` ='xiaoming' WHERE id=1;
--不加where条件，修改的是整张表
UPDATE `student` SET `name`='xiaohong' ;
--同时修改多个字段
UPDATE `student` SET `name`='xiaofang', email='232323@qq.com' WHERE id=1;
--多个条件定位数据
UPDATE `student` SET `name`='长江7好' WHERE `name`='xiaoming` AND sex ='女';
--value可以是个变量
UPDATE `student` SET `birthday`=CIRRENT_TIME WHERE `name`='长江7好' AND sex ='女'

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

  • column_name 是数据库的列，尽量带上``
  • 条件，筛选的条件，如果没有指定则会修改所有的列
  • value 可以是个具体的值，也可以是个变量（通常是时间）
  • 设置多个属性，用英文逗号隔开（java操作，通常用trim把最后一个逗号剪掉）

3.2.3 删除

语法
DELETE FROM 表名 WHERE

--避免这种写法删除整张表，要带条件
DELETE FROM `student`;

--删除指定数据
DELETE FROM `student` WHERE id=1;
``
**TRANCATE清空命令**
作用：完全清空一个表，表的结构和约束不会变
```sql
TRANCATE `student`
操作符 含义
‘=’ 等于
<>或者!= 不等于
>
<
<=
>=
BETWEEN…AND.. [a,b]闭合范围内
AND && 两个条件同时成立
OR

** DELETE和TRANCATE的区别**

  • 相同点都能删除数据，而且都不会删除表结构
  • 不同点
    • TRANCATE 不同于delete重新设置自增列，计数器会归零，不会影响事务（推荐）
      DELETE删除的问题，重启数据库现象
  • INNODB 自增列重新从1开始（存在内存中，断电即失）
  • MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

4 DQL查询数据（最重点）

(Data Query Launguage)

  • 所有的拆线呢操作都用它，Select
  • 简单的查询，复杂的查询都能做
  • 使用频率最高的最核心的语言
SELECT [ALL|DISTINCT]
{
*|table.*|table.field1[as alias1][,table.field2[as alias2]][,....]]}--查哪些字段
From table_name [as table_alias] --从哪个表查
      [left|right|inner join table_name2]--联合查询
      [WHERE...]--指定结果需要满足的条件
      [GROUP BY..]--指定结果按照哪几个字段来分组
      [HAVING]--过滤分组的记录必须满足的次要条件
      [ORDER BY...]--指定查询记录按一个或多个条件排序
      [LIMIT {[offset,]row_count|row_countOFFSET offset}];
      --指定查询记录从哪条至哪条，启示值，页面行数  
}

中括号可选，大括号必选,注意遵守先后顺序

4.1 SELECT语法

语法：
SELECT 字段1 ... FROM 表名
有时候，列的名字不是那么的见名知意，我们起别名，AS 字段名 as 别名 表名 AS 别名

--查询全部的学生
SELECT * FROM student
--查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
--别名，给查询结果字段起别名,结果列名就是这个别名，给表起别名用于多表查询
SELECT `StudentNo` AS 学号, `StudentName` AS 学生姓名 FROM student AS s
--函数 CONCAT(a,b)字符串拼接
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student

4.2 去重

使用distinct 去除SELECT** 查询结果**中重复的数据，重复就只显示一条
注意distinct只区别目标字段，所有目标字段有一个不重复就是不同的

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

4.3 数据库列表达式

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

SELECT VERSION()         --查询系统版本（函数）
SELECT 100*3-1 AS 计算结果 --用来计算（表达式）
SELECT @@auto_increment_increment --查询自增的步长（变量）
--学员考试成绩+1分查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result

4.4 where条件子句

作用：检索数据中符合条件的值
搜素条件由一个或者多个表达式组成，结果为布尔值，尽量使用英文关键字,可读性提高

  • 逻辑运算符
运算符 语法
and && a and b , a && b
or a or b, a
Not ! not a ! A 
SELECT studentNo, `StudentResult` FROM result
-- 查询考试成绩在95-100之间的
SELECT studentNo, `StudentResult` FROM result WHERE StuentResult>=95 AND StudentResult<=100
--使用between andd（区间）
SELECT studentNo, `StudentResult` FROM result WHERE StudentResult BETWEEN 95 AND 100
-- 除了1000号学生之外的同学的成绩
SELECT studentNo,`StudentResult` FROM result WHERE studentNo!=1000;
SELECT studentNo,`StudentResult` FROM result WHERE NOT studentNo=1000;
  • 模糊查询：比较运算符
运算符 语法
IS NULL a is null 为真
IS NOT NULL a is not null 为真
BETWEEN a between b and c, a在[b,c]为真
Like a like b,如果a能够匹配b 则结果为真
In a in(a1,a2,a3..),a 在后面的集合里，则结果为真
like结合%（%代表0到任意个字符）_（代表一个字符）, in后面是具体的值不和符号结合使用 
--查询姓刘的同学
SELECT `StudentNo`,`StudentName` FROM 'student' WHERE StudentName LIKE '刘%'
--查询姓刘的同学，名字后面只有一个字
SELECT 'StudentNo','StudentName' FROM 'student' WHERE StudentName LIKE '刘_'
--查询姓刘的同学，名字后面有两个字
SELECT 'StudentNo','StudentName' FROM 'student' WHERE StudentName LIKE '刘__'
--查询名字中带有'嘉'字的
SELECT 'StudentNo','StudentName' FROM 'student' WHERE StudentName LIKE '%嘉%'
--==IN具体的一个或多个值===
---查询1001,1002,1003号学员
SELECT 'StudentNo','StudentName' FROM 'student' WHERE StudentNo IN (1001,1002,1003)

--==null not null==
--查询地址为空的学生 null ''
SELECT 'StudentNo','StudentName' FROM 'student' WHERE address='' OR address IS NULL
--查询有出生日期的同学 不为空
SELECT 'StudentNo','StudentName' FROM 'student' WHERE 'BornDate' IS NOT NULL

4.5 联表查询

Join .. on

7 Join 操作符 描述
1 left join … on左连接 左表的全部，右表不满足的列补空NULL
2 right join 右连接 右表的全部，左表不满足的列补空
3 left join B.key is null 只保留左表特有的数据，右表里没有
4 inner join 内连接 只输出左右表均存在的记录（交集）
5 right join A.key is null 只保留右表特有的数据
6 full outer join/union 合并左，右外连接结果集 获取左右表所有记录，各自没有时补空
7 "full outer join key is null 从合并左右外连接结果集里查找a.key=null 或者B.key=null" 获取两表连接交集的补集
  • where 是用来做筛选条件的
  • join...on才是连接查询，通常使用on。以left join ...on举例，以左表作为主表，根据on条件拼接满足条件的右表查询出来的数据，如果没有满足条件的数据，就拼接null.
    也就是on后的条件非真，左表的数据仍然保留，是软条件。对于where来说就是硬性条件，如果where引导的条件为false，怎结果集中不显示改行。
  • left join, right join以一端为主表，inner join取交集，full outter join取并集
    写sql语句思路：
  1. 确定要查哪些数据select ...
  2. 从那几张表种查， FROM表， XXX Join连接的表， on 交叉条件
  3. 如存在多表查询，慢慢来，先查询两张表然后再逐步增加连表
---查询参加了考试的同学(学号，姓名，科目编号，分数）
/*思路
1.分析需求，查询的字段来自哪些表，多表需要联合查询
2.确定使用过那种连接查询？7 种
确定交叉点，这两个表中哪个数据是相同的
判断的条件：学生表中的studentNo = 成绩表中的studentNo
*/
--Inner join,此时where 和on等效，但是在其他查询里是不一样的
SELECT s.studentNO,studentName,SubjectNo,StudentResult FROM student AS s
INNER JOIN result AS r WHERE s.studentNO = r.studentNO
--Right JOIN
SELECT s.studentNO,studentName,SubjectNo,StudentResult FROM student s RIGHT JOIN
result r ON s.studentNO=r.studentNO
--left JOIN
SELECT s.studentNO,studentName,SubjectNo,StudentResult FROM student s LEFTJOIN
result r ON s.studentNO=r.studentNO
--查询缺考的同学
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s LEFT JOIN result r ON s.studentNO = r.studentNO WHERE StudentResult IS NULL
--查询参加考试的同学信息：学号，学生姓名student，科目名subject，分数score（三表查询）
SELECT s.studentNo,studentName,SubjectName,'StudentResult' FROM student s RIGHT JOIN
result ON r.studentNo=s.studentNo INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo

4.6 自连接查询

自己的表和自己连接，通常用在一张表中有父子，上下层级关系.

  `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')
}
INSERT INTO `category` (`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术').
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息'）；
}

原始的单表如下，是个树形结构

categoryid pid categoryName
2 1 信息技术
3 1 软件开发
4 3 数据库
5 1 美术设计
6 3 web开发
7 5 ps技术
8 2 办公信息
可以拆成如下两张表
父类（pid为1的）：
categoryid categoryName
---------- ------------
2 信息技术
3 软件开发
5 美术设计
子类：
pid categoryid categoryName
---- ---------- ------------
3 4 数据库
3 6 web开发
5 7 ps技术
2 8 办公信息
操作--查询父类对应的子类关系
父类 子类
---- -----
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 ps技术 
--查询父子关系：把一张表看作两张一摸一样的表即可
SELECT a.`categoryName` AS '父栏目'，b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b WHERE a.`categoryid` = b.`pid`

4.7 排序ORDER BY和分页LIMIT

--排序 升序 ASC,降序DESC
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'
ORDER BY `StudentResult` DESC --降序排列

--分页，缓解数据库压力，给人更好的体验
--（百度图片没使用分页，而是用瀑布流）

--语法：limit 起始值，页面显示的数据行数大小
--LIMIT 0,5 1~5
--LIMIT 1,5 2~6
--LIMIT 6,5 第二页
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'
ORDER BY `StudentResult` DESC
LIMIT 6,5
/*
网页应用：当前curPage，总数据量,页面的大小pageSize（展示多少条数据）
firstIndex=(curpage-1)*pageSize
lastIndex=curPage*PageSize
pages=(total+pageSize-1)/pageSize
*/
--查询JAVA 第一学年课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s INNER JOIN result r ON s.studentNo=r.studentNo
INNER JOIN subject sub ON r.SubjectNo=sub.SubjectNo
Where subjectName='JAVA 第一学年' AND StudentResult>80
ORDER BY StudentResult DESC
LIMIT 0,10

4.8 子查询

where(子句判断条件是计算出来的）
本质：在where 语句种嵌套一个子查询， where(select * from)
通常子查询效率要比连表查询效率低

--1. 查询数据库结构-1的所有考试结果（学号，科目编号，成绩）降序排列
--方式一使用连接查询
SELECT s.`StudentNo`,'SubjectNo','StudentResult'
FROM result r 
INNER JOIN subject sub ON r.SubjectNo=sub.SubjectNo
WHERE SubjectName='数据库结构-1' 
ORDER BY StudentResult DESC
--方式二使用子查询（由里及外,相当于小学生分步计算）
--查询所有数据库结构-1的学生学号
SELECT SubjectNo FROM `subject` WHERE SubjectName='数据库结构-1'
--组合子查询条件
SELECT `StudentNo`,`SubjectNo`,`StudentResult` FROM student
WHERE SubjectNo = (
SELECT SubjectNo FROM `subject` WHERE SubjectName='数据库结构-1')
ORDER BY StudentResult DESC

--分数不小于80分的学生的学号和姓名
SELECT s.StudentNo, StudentName FROM student 
INNER JOIN result r ON r.StudentNo = s.StudentNo
WHERE `StudentResult`>=80 AND `SubjectNo` = 
(SELECT SubjectNo FROM `subject` WHERE `SubjectName`='高等数学-2')
--再改造
SELECT StudentNo, StudentName FROM student 
WHERR studentNo IN (SELECT StudentNo from result 
WHERE StudentResult>=80 AND subjectNo = (
SELECT subjectNo from subject WHERE subjectName='高等数学-2'))

4.9 过滤和分组

  • 当时用GROUP BY后，不能再使用where来限制条件筛选，而是使用HAVING来进行分组筛选
  • GOURP BY 一般结合聚合函数使用（多个输入一个输出，如AVG平均值，MAX最大值),因为分组后有的列入数学对应多个学生的分数，需要写在一个单元格里，不再是多个单元格
--查询不同课程的平均分，最高分，最低分
-- 根据不同的课程分组
SELECT `SubjectName`, AVG(StudentResult), MAX(StudentResult),MIN(StudentResult)
FROM result r INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo
GOURP BY `r.subjectNo` --通过什么分组
--增加条件 查询不同课程的平均分，最高分，最低分，且要求平均分大于80分
SELECT `SubjectName`, AVG(StudentResult) AS 平均分, MAX(StudentResult),MIN(StudentResult)
FROM result r INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo
GOURP BY `r.subjectNo`
HAVING 平均分>80

实际应用中sql查询跨表，跨数据库，本地数据库远程数据库等等，复杂度增加（淘宝页面千人千面）

5 MySQL函数

官网https://dev.mysql.com/doc/refman/5.7/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) --判断符号，正数返回1，负数返回-1
---字符串函数
SELECT CHAR_LENGTH('即使在校的帆,也能远航')--字符串长度
SELECT CONCAT('世界','之大','无奇','不有')
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱')--插入替换，我爱-->超级热爱
SELECT UPPER('hello')
SELECT LOWER('HEllo')
SELECT INSTR('hello world','r')--返回第一次出现的字串的索引
SELECT REPLACE('坚持就是胜利'，'坚持'，'努力')--替换指定字符串
SELECT SUBSTR('坚持就是胜利',4,1)--截取字符串
SELECT REVERSE('坚持就是胜利')--反转
--举例
SELECT REPLACE(studentname,'周'，'邹'）FROM student
WHERE studentName LIKE '周%'
--时间和日期函数
SELECT CURRENT_DATE()--获取当前日期
SELECT NOW()--获取当前时间
SELECT LOCALTIME()--获取本地时间
SELECT SYSDATE() --获取系统时间
SELECT YEAR(NOW())--获取年（时分秒类似）
--系统
SELECT SYSTEM_USER()
SELECT USER() --同上获取系统当前用户
SELECT VERSION()

5.2 聚合函数（常用）

函数名称 描述
COUNT() 计数
SUM() 求和
AVG() 平均值
MAX() 最大值
MIN() 最小值
想查询一个表中有多少记录，就使用COUNT
注意COUNT(*),COUNT(1),COUNT(列名)都会统计行数，但略有不同
  • COUNT(列名）会进行非NULL判断，只有非NULL才会进入统计
    COUNT(*),COUNT(1)都统计所有行数，不论是否是NULL
  • COUNT(1)可以想象成表有个固定字段1，对其进行统计
  • COUNT(*)则会根据Mysql的优化自动指定到效率高的字段上
  • 有主键的情况下COUNT(主键)查索引速度最快
--COUNT(*) COUNT(1) COUNT(列名)
SELECT COUNT(studentname） FROM student；--Count指定列
SELECT COUNT(*) FROM student；
SELECT COUNT(1) FROM student;

SELECT SUM(`StudentResult`) AS '总和' FROM result
SELECT AVG('StudentResult`) AS '平均分' FROM result
SELECT MAX(`StudentResult`) AS '最高分' FROM result
SELECT MIN(`StudentResult`) AS '最低分' FROM result

5.3 数据库级别的MD5加密（扩展)

什么事MD5? 不可逆的算法，具体值对应的md5值是一致的。
MD5破解网站的原理，背后有一个字典，输出MD5加密后的值，查表返回加密前的值

--测试MD5加密
CREATE TABLE 'testmd5'{
  `id` INT(4) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `pwd` VARCHAR(50) NOT NULL,
    PRIMIARY KEY(`id`)
} ENGINE=INNODB DEFAULT CHARSET=utf8
--明文密码
INSERT INTO testmd5 VALUES (1,'zhangsan','123456'),(2,'lisi','123434'),
(3,'wangwu','1234346')
--加密
UPDATE testmd5 SET pwd=MD5(pwd) --加密全部的密码
--插入的时候加密
INSERT INTO testmd5 VALUES (4,'xiaoming',MD5('34343434'))
--如何校验：将用户传递进来的密码，进行md5加密，比较加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

6. 事务

6.1 什么是事务

  • 事务原则：ACID 原子性（Atomicity），一致性(Consisitency),隔离性（Isolation),持久性（Durability)
    • 原子性：要么都成功，要么都失败,一组SQL放在一个批次中执行
---------
1. SQL 执行 A给B转账 A:1000->200 B:200
2. SQL 执行 B收到A的钱 A:800  B:400
---------
  • 一致性：事务操作前后数据的完整性是保持一致的，如A,B账户钱的总和在转帐前后保持一致
  • 持久性： 事务一旦提交就不可逆不受事故影响了。假设出现事故，如断电，事务没有提交，数据库回复到原有状态；事务已经提交了，持久化到数据库，重启后仍然是持久化后的状态。
  • 隔离性：多个用户并发访问数据库时，数据库为每个用户开启单独的事务，事务间相互隔离，排除其他事务对本次事务的影响
  • 事务的隔离级别（隔离所产生的问题）
    • 脏读：一个事务读取另外一个事务未提交的数据
    • 不可重复读：在一个事务内读取表的某一行数据，多次读取结果不一样（如第二次读之前，有人转钱了，账户余额变多了），这个不一定是错误，只是某些场合不对。
    • 幻读（虚读） 指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致（如多了一行）
      参考链接：https://blog.csdn.net/dengjili/article/details/82468576

6.2 处理事务sql

--mysql是默认开启事务自动提交的
SET autocommit = 0 /*关闭自动提交*/
SET autocommit = 1 /*开启自动提交*/
--手动处理事务
SET autocommit = 0
--事务开启
START TANSACTION -- 标记一个事务的开启，从这个之后的sql都在同一个事务内
INSERT XX
INSERT XX
--提交：持久化（成功）
COMMIT
--回滚：回到原来的样子（失败）
ROLLBACK
--事务结束
SET autocommit = 1
--保存点了解，（玩游戏挂掉，回到相应的关卡）
SAVEPOINT 保存点名 --设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名--回滚到保存点
RELEASE SAVEPOINT 保存点名--撤销保存点

例

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf_8_general_ci--核对COLLATE
USE shop
CREATE TABLE `account` {
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `money` DECIMA(9,2) NOT NULL.
  PRIMARY KEY ('id')
}ENGINE=INNODE DEFAULT CHASET=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 索引

https://blog.csdn.net/wufuhuai/article/details/79631466 （讲的比较深的mysql原理，待看）

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

7.1 索引的分类

  • 主键索引（PRIMARY KEY)
    • 唯一标识，主键不可重复，表中只能有一个列作为主键，不能为null
  • 唯一索引 (UNIQUE KEY)
    • 被标识的列值不可以重复，在一个表中带唯一索引的列可以有多个
      主键一定是唯一性索引，但是唯一索引不一定是主键
  • 常规索引 (KEY/Index)
    • 默认的用Index/Key关键字来设置
  • 全文索引（FullText)
    • 在特定的数据库引擎下才有,Myisam，作用快速定位数据,用关键字FULLTEXT INDEX

7.2 索引的使用

  • 创建索引两种方法
    1.在创建表的时候CREATE给字段增加索引（如前面创建表的例子）
    2.创建完毕后，增加索引 ALTER TABLE 表名 ADD 索引类型 INDEX 索引名（列名）
    3. 在表上加索引，索引命名方式 id_表名_字段名
    语法： CREATE INDEX 索引名 on 表（字段）
--显示所有的索引信息
SHOW INDEX FROM student
--增加一个全文索引 (索引名）列名,查询的时候有索引的列会快一点
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);
  • 索引创建完使用，是指查询的时候自动使用，加快搜索
  • explain分析sql执行的情况
    语法：explain+SQL语句
-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘')

explain的使用参考https://blog.csdn.net/jiadajing267/article/details/81269067

  • 测试索引
    给app_user表加入1000000条测试数据
--插入100万的数据(写sql函数了解即可)
DELIMITER $$ --写函数之前必须要写的标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i<num DO
    --插入语句
    --currenttime, update time 不用手动写，勾选了自动更新
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',i),'23243434@qq.com',CONCAT('18',FLOOR(RAND()*(999999999-100000000))+100000000)),
FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
    SET i = i+1;
  END WHILE
  RETURN I;
END;

创建索引，索引命名id_表名_字段名
比较加上索引和没加索引查询速度

SELECT * FROM app_user WHERE `name`='用户9999';--1sec左右的执行时间
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';--查了9000多条数据

--id_表名_字段名
--CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
--此时再查用户9999速度飞起
SELECT * FROM app_user WHERE `name`='用户9999';--0.001 sec
--rows为1行，相当于唯一定位了，key value查找
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';

索引再小数据量的时候，差别不大，但是大数据时十分明显

7.3 索引原则

  • 索引不是越多越好

  • 不要对经常变动的数据加索引

  • 小数据量的表不需要加索引

  • 索引一般加在常用来查询的字段上

  • 索引的数据结构
    Hash类型的索引
    Btree:InnoDB的默认数据结构

--业务级的mysql
--运维级的mysql 负载均衡，mysql底层

8.权限管理和备份

8.1 用户管理

  • SQLyog 可是化管理
  • SQL 命令操作
    在数据库目录下有张user用户表，增加删除用户就是对该表进行增删改查
--创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER xiaoming IDENTIFIED BY '123456'
--修改当前用户的密码
SET PASSWORD = PASSWORD('1232434')
--修改指定用户密码
SET PASSWORD FOR xiaoming= PASSWORD('12323233')
--重命名 RENAME USER 原名字 TO 新名字
RENAME USER xiaoming TO xiaolan
--用户授权 ALL PRIVILEGES 全部的权限(除了给别人授权). 库.表
GRANT ALL PRIVILEGES ON *.* TO xiaolan
--查询权限
SHOW GRANTS FOR xiaolan --查看指定用户的权限
--root的权限查出来是GRAN ALL PRIVILEGES ON *.* TO 'root@localhost' WITH GRANT OPTION
SHOW GRANTS FOR root@localhost
--撤销权限 REVOKE 哪些权限，在哪个库表，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM xiaolan
--删除用户
DROP USER xiaolan

8.2 数据库备份

为什么要备份:

  • 保证数据不丢失
  • 数据转移
    MySQL数据备份的方式
  • 直接拷贝物理文件 data文件夹
  • 在Sqlyog这种可视化工具中手动导出
    右键想要导出的表或数据库->转储到sql,选结构和数据，其他默认
  • 使用命令行在终端上导出 mysqldump （对于windows系统指的是cmd弹出的终端）
# ======导出======
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表名>物理位置
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# dump多张表
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表名1 表名2 表名3>物理位置
mysqldump -hlocalhost -uroot -p123456 school student result>D:/b.sql
# dump数据库
mysqldump -hlocalhost -uroot -p123456 school >D:/db.sql
#======导入=======
# 先登录
mysql -uroot -p123456
# 切换到指定的数据库
use school
# 导入，登录的情况下切换到指定的数据库，source备份的文件
source d:/a.sql
# 第二种导入方法
mysql -u用户名 -p密码 库名<备份文件

9 规范数据库设计

9.1 为什么需要设计

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

  • 数据冗余，浪费空间

  • 数据库插入和删除都会比较麻烦，异常【屏蔽使用物理外键】

  • 程序的性能差
    良好的数据库设计：

  • 节省内存空间的

  • 保证数据库的完整性

  • 方便我们开发系统的
    软件开发中，关于数据库的设计

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

  • 概要设计：设计关系图E-R图
    设计数据库的步骤：（个人博客）

  • 收集信息，分析需求

    • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
    • 分类表（文章分类，谁创建的）
    • 文章表（文章的信息）
    • 友链表（友链信息）
    • 自定义表（系统信息，某个关键的字，或者一些主字段 key:value）可选项
    • 说说表（发表心情..id...content...create_time)
      comments表，user_id_parent回复评论形式，查询时单表自联接

      links表

      user表

      user_follow表（中间表）

      category分类表

      blog表

  • 标识实体，实现功能时表与表之间的关系（把需求落地到每个字段）

    • 写博客：user->blog
    • 创建分类：user->category
    • 关注：user->user
    • 友链：links
    • 评论：user->user->blog
      常见的系统:博客，论坛，CRM管理系统
      注：数据库里字段使用下划线_分隔，尽量不要使用驼峰命名，因为数据库不区分大小写

9.2 三大范式

  • 为什么需要数据规范化？
    • 信息重复(如两张表里都写username 和userid)
    • 更新异常
    • 插入异常
      • 无法正常显示数据（插入信息不完整，多张表重复字段，漏插入）
    • 删除异常
      • 丢失有效的信息
  • 三大范式
    （注：检索概念的时候，搜那些有例子的博客，方便理解，不要纯文本）
    参考 https://www.cnblogs.com/wsg25/p/9615100.html
    • 第一范式（1NF）
      原子性：保证每一列不可以再分
    • 第二范式（2NF)
      满足第一范式的前提下，每张表只描述一件事情，产品，或者订单是两张不同的表，不能混在一起谈
    • 第三范式
      满足一二范式的前提下，确保数据表中的每一列数据都和主键直接相关，而不能间接相关
  • 规范性和性能的问题
    关联查询的表不能超过三张
    • 考虑商业化的需求和目标，（成本，用户体验等考虑）数据库的性能更加重要
    • 在规范性能的问题的时候，需要适当的考虑一下规范性
    • 故意给某些表增加一些冗余的字段，从多表查询变成单表查询
      如订单-商品id，还增加了商品信息
    • 故意增加一些计算列（从大数据量计算，降为小数据两的查询）

10. JDBC(重点)

10.1 数据库驱动

类似于声卡显卡，数据库也需要有安装驱动,我们的程序会通过数据库驱动来和数据库交互

10.2 JDBC

SUN公司为了简化开发人员对数据库的统一操作，提供了一个java操作数据库的规范JDBC.这些规范的具体实现由数据库厂商实现。
（注：在架构中没有什么是加一层解决不了的）

  • 主要涉及的包
    java.sql
    javax.sql
    导入的数据库驱动包：
    mysql-connector-java-5.1.47.jar
    下载该包的时候一般去maven仓库下载 maver repository
    官网也可以下载，但是用的少 官网下载地址

10.3 第一个JDBC程序

1.创建测试数据库

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,'zhansan','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. 创建一个普通项目，把驱动包mysql-connector-java-5.1.47.jar放在创建的lib目录下，右键add as library添加进来
  2. 编写测试代码

步骤总结：

  1. 加载驱动 Class.forName
  2. 用户信息及url
  3. 连接数据库DriverManager->connection
  4. 获得执行sql的对象->statement
  5. 执行sql,可能得到返回结果
  6. 关闭结果集resultSet,Statement,connection释放资源
public class JdbcFirstDemo{
	public static void main(String[]args){
		//1.加载驱动
		Class.forName("com.mysql.jdbc.Driver");//固定写法
		//2.用户信息和url
		//jdbc:mysql://serverIP:3306/databaseName?
		//useUnicode支持中文编码，characterEncoding设置字符集，useSSL安全连接
		String url =
"jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=true“;
		String username="root";
		String password="123456";
		
		//3.连接成功，返回数据库对象connection
		Connection connection = DriverManager.getConnection(url,username,password);
		
		//4.执行SQL对象statement
		Statement statement = connection.createStatement();
		
		//5.去执行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("pwd"));
			System.out.println("email="+resultSet.getObject("email"));
			System.out.println("birth="+resultSet.getObject("birth"));
			System.out.println("=======================")
		}
		//6.释放连接
		resultSet.close();
		statement.close();
		connection.close();
		
	}
		
}

解释：

  1. 加载数据库驱动
    Class.forName("com.mysql.jdbc.Driver")对应静态代码块，加载类直接执行DriverManager.registerDriver(new com.mysql.jdbc.Driver())
    推荐用Class.forName这种写法
  2. URL
    String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
    协议//主机地址:3306(mysql默认端口号)/数据库名？参数1&参数2&参数3
    扩展oralce--1521写法
    jdbc:oracle:thin:@localhost:1521:sid
  3. connection
    connection代表数据库，可以设置自动提交，事务提交，事务回滚
    connection.rollback()
    connection.commit()
    connection.setAutoCommit();
  4. statement执行SQL类（PrepareStatement也是）
    executeQuery()//返回查询结果集Resultset
    execute()//执行任何sql，里面有判断的过程，效率更低一点
    executeUpdate()//增删改都用它，返回受影响的行数
    executeBatch()//执行批处理多个sql
  5. ResultSet查询的结果集
    封装了所有的查询结果，拿结果时可以指定数据类型
    getObject(),getString(),getInt(),getfloat()...
    在不知道数据库列对应的数据类型时用getObject，知道的情况就用指定的类型
    遍历，指针resultSet.next()
  6. 释放资源
    resultSet.close();
    statement.close();
    connection.close();

10.4 statement对象

statement对象用于向数据库发送增删改查语句
executeQuery()返回查询结果集ResultSet
executeUpdate增删改，返回受影响改变的行数，当返回值大于零说明执行成功

  • 提取工具类
    • db.properties
driver = com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=xiaoming
password=123456
  • JdbcUtils.java工具类
    static块load properties文件，加载驱动
    提取获取连接的方法
    提取释放资源的方法
public class JdbcUtils{
	private static String driver = null;
	private static String url = null;
	private static String name = null;
	private static String password = null;
	static {
		try{
		//在src目录下的资源都可以用类反射加载
		InputStream in =
		JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
		Properties properties = new Properties（）;
		properties.load(in);
		
		driver = properties.getProperty(driver);
		url = properties.getProperty(url);
		name = properties.getProperty(name);
		password = properties.getProperty(password);
		
		//1.驱动只用加载一次
		Class.forName(driver);
		}catch(Exception){
			e.printStackTrace();
		}
	}
	//获取连接
	public static void getConnection() throw SQLException{
		return DriverManager.getConnection(url,name,password);
	}
	//释放资源
	public static void release(Connection conn, Statement st, ResultSet rs){
		if(rs！=null){
			try{
				rs.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(st！=null){
			try{
				st.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(conn！=null){
			try{
				conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
}
  • 测试编写增删改查的代码
    变的时sql语句和执行语句，查询略
    在finally里释放资源
public class TestInsert{
	public static void main(String[] args){
	Connection conn = null;
	Statement st = null;
	ResultSet rs = null;
		try{
			conn= JdbcUtils.getConnection();
			st = conn.createStatement();
			String sql =" Insert INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
			VALUES(4，‘xiaoming','1232434','123243343@qq.com’，‘2001-3-4’)";
			/* 删除的sql
			String sql ="DETELET FROM users WHERE id=4”；
			*/
			int i = st.executeUpdate(sql);
			if(i>0){
				System.out.println("插入成功");
			}
		
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			JdbcUtils.release(conn,st,rs)
		}
	}

}
  • SQL注入问题
    本质上输入的sql存在恶意拼接(or,and version()>0)，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句。实现非授权的任意查询
	public static void login(String username, String password){
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try{
			conn= JdbcUtils.getConnection();
			st = conn.createStatement();
			//对应组合的sql,sql注入时如下
		//SELECT * from users WHERE `name`= ' ' OR ‘1=1’ AND `password`=' ’ or '1=1';
String sql ="SELECT * from users WHERE `name`= '"+username+"' AND `password`='"+ password+"'";
			rs =st.executeQuery(sql);
			while(rs.next()){
				System.out.println(rs.getString(name));
			}
		
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			JdbcUtils.release(conn,st,rs)
		}		
	}
	public static void main(String args[]){
		//正常登录
		//login("xiaoming","123456");
		
		//sql注入
		login(" 'or 1=1"," 'or 1=1");
		
	}

10.5 PreparedStatement对象

PreparedStatement(sql)预编译的可以防止注入，且效率更高

  • 编写sql先用?占位符来代表值，
  • 预编译preparedStatement
  • 赋值
  • 再执行
public class TestInsert(){
	public static void main(String[] args){
		Connection conn = null;
		PreparedStatement pst =null;
		
		try{
			conn = JdbcUtils.getConnection();
			//区别，先使用?占位符代替参数
			String sql ="Insert INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
			VALUES(?,?,?,?,?)"
			//参数预编译sql,先写sql然后不执行
			pst = conn.prepareStatement(sql);
			//手动给参数赋值
			pst.setInt(1,4);
			pst.setString(2,"xiaohong");
			pst.setString(3,"1232434");
			pst.setString(4,"1232398@qq.com");
			//注意 sql.Date数据库里的时间，参数是时间戳
			//util.Date java里的时间
			pst.setDate(5,new java.sql.Date(new Date().getTime()));
			
			//执行,不用传参
			int i = pst.executeUpdate();
			if(i>0){
				System.out.println("插入成功");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			
		}	
	}
}

10.6 使用IDEA连接数据库

前提工程里已经导好了数据库驱动包connector

  1. 点击IDEA右侧Database标签->点击+加号->选择MySQL数据库
    添加数据库具体信息Host，User，Password，和URL，使用Test Connection 进行连接测试
    点击apply OK, 第一次连接比较耗时
  2. 连接成功mysql后，添加具体的数据库
    点击Database标签里的设置按钮(弹出Data Sources and Drivers)->Schemas(里面勾选数据库)->apply->OK
  3. 查看数据库表
    双击添加的数据库里面的表，即可展示里面的内容
  4. 更新数据
    直接再打开的表里修改，注意点击提交按钮
  5. IDEA里执行命令行写sql
    打开database标签里的console,输入sql语句运行即可
  6. 如果连接sql出现问题，可以查看驱动配置是否有问题
    参考 https://blog.csdn.net/weixin_44199123/article/details/106296349

10.7 事务

代码实现

  1. 开启事务 conn.setAutoCommit(false);
  2. 一组业务执行完毕，提交事务connection.submit
  3. 可以在catch里显示定义回滚，但是默认失败就会回滚的
public class TestTransaction1{
	public static void main(String[] args){
		Connection conn = null;
		PrepareStatement st = null;
		ResultSet rs = null;
		
		try{
			conn = JdbcUtils.getConnection();
			//关闭数据库自动提交,会自动开启事务
			conn.setAutoCommit(false);
			String sql1 = "UPDATE account SET money = money-100 where name ='A'";
			st = conn.prepareStatement(sql1);
			st.executedUpdate();
			
			int x = 1/0;//报错，测试两个操作在同一个事务里，回滚
			
			String sql2 = "UPDATE account SET money = money+100 where name ='B'";
			st = conn.prepareStatement(sql2);
			st.executedUpdate();
			
			//业务完毕，提交事务
			conn.commit();
			System.out.println("成功");
		}catch(Exception e){
			//如果不手动回滚，也可以自动回滚
			try{
				//如果失败回滚
				conn.rollback();
			}catch(SQLException e1){
				e1.printStackTrace();
			}
			e.printStacTrace();
		}finally{
			JdbcUtils.release(con,st,rs);
		}
	}
}

10.8 DBCP-C3P0连接池

数据库连接--执行--释放，十分耗费资源
池化计数：预先准备一些资源，后面就直接取用就行
编写连接池实现JDK提供接口DataSource

  • 开源数据实现DataSource
  • DBCP
  • C3P0
  • Druid:阿里巴巴
    使用了这些数据库连接池后，我们在项目开发中就不需要编写加载数据库驱动的代码了
  • DBCP
    • 导入相关jar包，add as libary
      commons-dbcp-1.4.jar
      commons-pool-1.6.jar
    • 导入配置文件dbcpconfig.properties
#连接设置 这里面的名字是DBCP数据源中定义好的,可以只写这4条后面的都不要
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

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

#最大连接数量
maxActive=50

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

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

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

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

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

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
  • 写JdbcUtils_DBCP.java工具类
public class JdbcUtils_DBCP{

	private DataSource dataSource=null;
	static {
		try{
		//加载jdbc对应的properties文件
		InputStream in =
		JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
		Properties properties = new Properties();
		properties.load(in);

		//创建数据源 工厂模式-->创建
		dataSource = BasicDataSourceFactory.creatDataSource(properties);
		}catch(Exception){
			e.printStackTrace();
		}
		
	}
	//获取连接
	public static void getConnection() throw SQLException{
		//从数据源中获取连接
		return dataSource.getConnection();
	}
	
	//释放资源
	public static void release(Connection conn, Statement st, ResultSet rs){
		if(rs！=null){
			try{
				rs.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(st！=null){
			try{
				st.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(conn！=null){
			try{
				conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
}
  • 测试类和前面一样，只是工具类换成刚创建的JdbcUtils_DBCP
	public static void main(String[] args){
	Connection conn = null;
	Statement st = null;
	ResultSet rs = null;
		try{
			conn= JdbcUtils_DBCP.getConnection();
			st = conn.createStatement();
			String sql ="Insert INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
			VALUES(4，‘xiaoming','1232434','123243343@qq.com’，‘2001-3-4’)";
			/* 删除的sql
			String sql ="DETELET FROM users WHERE id=4”；
			*/
			int i = st.executeUpdate(sql);
			if(i>0){
				System.out.println("插入成功");
			}
		
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			JdbcUtils_DBCP.release(conn,st,rs)
		}
	}
  • C3P0
  1. 导入jar包
    c3p0-0.9.5.5.jar
    mchange-commons-java-0.2.19.jar
  2. C3P0配置文件c3p0-config.xml(注意使用xml配置，且名字只能是这个，放在classpath下自动加载)
    IDEA里放在项目的src目录下
    xml里可以写多套数据源配置，ComboPooledDataSource ds=new ComboPooledDataSource()，不写参数代表使用默认数据源
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    c3p0的缺省（默认）配置
    如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"无参使用默认数据源-->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;uesSSL=true&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <!--如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");
      使用指定的数据源-->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;uesSSL=true&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>

</c3p0-config>

对应工具类

public class JdbcUtils_C3P0{

	private static comboPooledDataSource dataSource=null;
	static {
		try{
			//代码版配置 一般不这么配
			/*
			dataSource = new ComboPooledDataSource();
			dataSource.setDriverClass();
			dataSource.setUser();
			dataSource.setPassword();
			dataSource.setJdbcUrl();
			*/
			//xml版配置
			dataSource = new ComboPooledDataSource("MySQL");
		}catch(Exception){
			e.printStackTrace();
		}
		
	}
	//获取连接
	public static void getConnection() throw SQLException{
		//从数据源中获取连接
		return dataSource.getConnection();
	}
	
	//释放资源
	public static void release(Connection conn, Statement st, ResultSet rs){
		if(rs！=null){
			try{
				rs.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(st！=null){
			try{
				st.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(conn！=null){
			try{
				conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
}

测试类 类似上面的略

  • 总结
    无论使用什么数据源，本质上是一样的，dataSource及方法getConnection不会变

扩展
Apacha开源项目网站:Durid（阿里贡献的），maven, tomcat等等

posted @ 2021-07-01 16:19  晒网达人  阅读(183)  评论(0)  编辑  收藏  举报